Question regarding loops and dynamic arrays

[reisende]

Hi everybody.

I am working on an online ordering system for our emlpoyees to order company shirts and I was wondering if there was a way to do something I think would be very helpful to the users.

Each year an employee is given three company shirts, any shirt they order after that is charged to them via payroll deduction. The system I'm working on allows front desk users to enter orders for all employees at their location.

Since shirt orders can be placed for more than one employee at a time I would like to keep a count of how many employees are over their budget and what the amount deducted from payroll will be.

Here is what I have so far:

if rsCart("ITEM_CAT_ID") = 1 then
  payDeductAmnt = payDeductAmnt + rowCost
  shirtCount = shirtCount + rowQty
end if
	  
if rsCart("ITEM_CAT_ID") = 1 and rsCart("PREMPL") <> "" then
  set rsShirtBdgt = Server.CreateObject("ADODB.Recordset") 
  strSQL2 = "SELECT * FROM MO_EMPL_SHIRT_BDGT WHERE PREMPL = '" & rsCart("PREMPL") & "';"
  rsShirtBdgt.Open strSQL2, objConnMarketing
		
  shirtCount = rsShirtBdgt("SHIRT_COUNT")
  shirtBdgt = rsShirtBdgt("SHIRT_BDGT")
		
  shirtCount = shirtCount + rowQty
		
  if shirtCount > shirtBdgt then
    overBudget = overBudget + 1
  end if
		
  rsShirtBdgt.Close
  set rsShirtBdgt = nothing
end if
	 	  
rsCart.MoveNext 
orderTotal = orderTotal + rowCost

This code is part of a higher loop which moves through all cart rows (rsCart.MoveNext)

You can see how I am counting the number of employees over budget, I'm just not sure what to do with it after that and get it to display a list of those users.

Will I need to populate an array dynamically and call it later in the script?

If you guys have any ideas or need more info, let me know.

Thanks.

 

[Tarwn]

I would suggest building a single query to handle this rather then looping and calling queries inside the loop. I'm not entirely sure how your database is setup so I can't help you very far without more information, but it woiuld really speed things up to put it all in a single query, as well as making display of the data easier.

My assumption is that you have at least two tables setup that you need information from. Using some SUM and COUNT functions you should be able to select from the higher table and some COUNT and SUM functions on fields in the lower table. Again, without more info I can't bvery helpful, but if you could give us info on your tables we can probably turn this into a more efficienct solution for you.

-T

 

[reisende]

I was considering using a query to do this, I just wasn't sure if that would be faster or not. I think I'll give that a try and see what I get.

As for the tables I am using a CART_ROWS table which each shirt order is placed into. It contains all the item id, quantity and price info.

I then have a table called SHIRT_BDGT which has a list of each employee (PREMPL), their current shirt count (SHIRT_COUNT) and the budgeted allotment of shirts (SHIRT_BDGT).

I am currently using the employee id (PREMPL) as a key to compare between the CART_ROWS and SHIRT_BDGT tables and check the selected employees' shirt counts.

Say a front desk enters an order for 2 shirts for Joe Smith (SMITJOE). The systems then checks the entry for SMITJOE in the SHIRT_BDGT table and sees he has already ordered 2 shirts (SHIRT_COUNT).

It adds the order quantity and shirt count together to see he is over budget and he'll need to do payroll deduction for 1 shirt.

I have the adding and comparing stuff done, I'm just not sure the best way to return a table like:

The following employees need Payroll Deduction...
SMITJOE 1 Payroll Deduction $16
FRANJAM 2 Payroll Deduction $32

... and so on.

I hope this helps. I can post more code if you like. Thanks.

 

[Tarwn]

Ok, I think I am still missing something. Where does MO_EMPL_SHIRT_BDGT come into it?

I also don't see why you have a shirt count in the SHIRT_BDGT table, why not just have a one-to-many link with CART_ROWS? Right now you would need to update the SHIRT_BDGT table every single time they order something to update the number of shirts...

I think my ideal setup for this would be to have something like:
Employee
PREMPL
SHIRT_BDGT
etc

CART_ROWS
PREEMPL
id
quantity
price

Then you could find out how many shirts employees have simply by:
SELECT Employee.PREMPL, SUM(CART_ROWS.quantity) AS qty, SUM(CART_ROWS.quantity * CART_ROWS.price) AS ttl_price
FROM (Employee INNER JOIN CART_ROWS ON Employee.PREMPL = CART_ROWS.PREMPL)
GROUP BY Employee.PREMPL

Find overages:
SELECT Employee.PREMPL, Employee.SHIRT_BDGT SUM(CART_ROWS.quantity) AS qty, SUM(CART_ROWS.quantity * CART_ROWS.price) AS ttl_price
FROM (Employee INNER JOIN CART_ROWS ON Employee.PREMPL = CART_ROWS.PREMPL)
WHERE SUM(CART_ROWS.quantity) > Employee.SHIRT_BDGT
GROUP BY Employee.PREMPL, Employee.SHIRT_BDGT

If prices are the same for every employee you xcould also calculate the overage amount:
SELECT Employee.PREMPL, Employee.SHIRT_BDGT SUM(CART_ROWS.quantity) AS qty, SUM(CART_ROWS.quantity * CART_ROWS.price) AS ttl_price, ((SUM(CART_ROWS.quantity) - Employee.SHIRT_BDGT) * CART_ROWS.price) as ovrg_amt
FROM (Employee INNER JOIN CART_ROWS ON Employee.PREMPL = CART_ROWS.PREMPL)
WHERE SUM(CART_ROWS.quantity) > Employee.SHIRT_BDGT
GROUP BY Employee.PREMPL, Employee.SHIRT_BDGT

I'm still not 100% crazy about this setup, as it depends on the prices being the same for every shirt, but since your giving them a budget based on number of shirts, rather then on price, it makes determine an overage more difficult. If the budet were dependant on a price then you could simply subtract the SUM(quantity * price) from the budget and come up with the same number, no mater how much the shirt costs varied.

-T

 

[reisende]

The MO_EMPL_SHIRT_BDGT is the same table as SHIRT_BDGT. I just didn't feel like typing the whole name . Sorry.

Unfortunately we base giving out shirts on the employee hire date and not price. Every year on their anniversary they can order up to another 3 shirts. I wrote a Delphi app that resets their shirt count to 0 on their hire date anniversary. That was the only way I could see doing this they way the higher ups want to.

I had the SHIRT_COUNT in there becase we've been using faxes for a while to order shirts and alot of employees have a current shirt count which wouldn't be reflected in the web application if everyone started at 0 (with the app assuming this is the employee's first ever shirt order and basing the count on that).

I know its odd, but I don't set policy (would be great if we could, wouldn't it?)