TalkPHP - View Single Post

hmonkey89 · 09-23-2011, 02:20 PM

Here is an example of what might be in the database:

jobs:

Code:

jobID = 4
startdate = 2012-03-02
enddate = 2012-03-04
-----
jobID = 6
startdate = 2012-03-02
enddate = 2012-03-02

stocklevel:

Code:

stocklevelID = 7
jobID = 4
catID = 3
affectstock = 1
startdate = 2012-03-02
enddate = 2012-03-04
-----
stocklevelID = 8
jobID = 4
catID = 3
affectstock = 1
startdate = 2012-03-02
enddate = 2012-03-02
-----
stocklevelID = 9
jobID = 6
catID = 3
affectstock = 0
startdate = 2012-03-02
enddate = 2012-03-02

stock:

Code:

stockID = 1
catID = 3

categories:

Code:

catID = 3
Name = laptop

job no.4 lasts from 02 march till 04 march 2012. They have hired 1x laptop from 2nd to 4th and 1x on 2nd only. but there is only 1x laptop in stock.
How would i get it to flag up that there is 1x laptop short on the 2nd?

in the stocklevel there is also a laptop for another job on the 2nd but affectstock=0 which means it is only a quote not a confirmed booking so would not be counted when checking if stock is available.

hope this makes more sense as that what i am trying to achieve?

perhaps something along these lines??:

Quote:

select * from stocklevel where jobID = $jobID
get all dates between startdate and enddate
for each $date +1 to $date[catID]

select * from stocklevel where jobID = $jobID groupby catID
select count(*) from stock where catID = $list[catID]
set $total[catID] = $list[count(*)]

for each $date>catID
if (($total[catID] - $date[catID]) < 0)
echo you are $date[catID] short of catID