php rental stock checker.

hmonkey89 · 09-22-2011, 04:14 PM

I have a database which lists all stock, categories and a job booking system.

Problem is I am trying to add a stock checking feature that highlights stock that might be short for a job so it can be sub-hired in.

There are 4 main db tables that are relevant:

stock
categories
stocklevel
jobs

main fields that are relevant:
stock has stockID, catID which is linked to categories.
stock level has stocklevelID, affectstock, catID, jobID, startdate and enddate.
jobs has jobID, startdate, enddate.

What i need to do is calculate how many items each category has.
work out the dates each stocklevel is out where affectstock=1 and if it is short of stock to throw up a message saying short of x ???s on date ???.

im guessing along the lines of:
select * from stocklevel where jobID = $jobID GROUP BY catID
then for each
count(*) from stock where catID = $catID
get all dates between startdate and enddate

then im guessing i for each date i need to count which items are used on that date?

not quite sure where to go from here? or if i should modify the database slightly to make it easier?

please help

hmonkey89 · 09-22-2011, 04:21 PM

all dates are in the form Y-m-d

core1024 · 09-23-2011, 11:05 AM

This two queries can be easely merged in to one.

Quote:

Originally Posted by hmonkey89

select * from stocklevel where jobID = $jobID GROUP BY catID
then for each
count(*) from stock where catID = $catID

Code:

SELECT stocklevel.*, COUNT(catID) AS numCats
FROM stocklevel
LEFT JOIN stock USING(catID)
WHERE jobID = '$jobID' GROUP BY catID

But English is not what I am best at and I couldn't understand what are you trying to do with the dates

Quote:

Originally Posted by hmonkey89

get all dates between startdate and enddate

startdate and enddate of stocklevel or jobs? What do you want to compare this dates with?

maeltar · 09-23-2011, 01:44 PM

This might help you getting dates between 2 dates from mysql..

http://stackoverflow.com/questions/5...ween-two-dates

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