php rental stock checker.
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:
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?
all dates are in the form Y-m-d
This two queries can be easely merged in to one.
This might help you getting dates between 2 dates from mysql..
Here is an example of what might be in the database:
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??:
|All times are GMT. The time now is 05:49 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0