In this post I’m writing about how to prompt users about the expiry dates of products without the use of any hardware (sensors). In this example, expiry dates are stored in the database and the user is prompted (based on configurable settings) that a product has some set days to expiry.
A very big assumption is made here
- 1. Products purchased first, are sold first
- 2. Quantity of products bought in one purchase have the same expiry date. For example, if you are buying 30 cartons of milk, the expiry date of all the cartons are the same
Now, lets go on with the design. We are using three (3) tables for our design namely, products table, purchase table and purchasedetail table. Note that the columns of the tables are just for the purpose of the expiry dates implementation
1) Products Table
3) PurchaseDetails Table
What we will be doing here is, we record the expiry date of a products when making the purchase. thus one product can have as many expiry dates as possible in the purchase details table based on the purchases made.
Note:when products are purchased , the quantity bought is added to the unitsinstock in the products table. Also when sales are made the neccesary deductions are made.
To prompt for expiry dates the key columns we will be using are the “unitsinStock” column in “products” table and the “quantitybought” column in “purchasedetails” table.
First we write a query to select records from the purchase details table. we select products with expiry dates greater than today and within the range of today + the set number of days to remind user
Example “select * from purchasedetails where Expirydates > today() and expirydate<=enddate” (enddate=today + the set number of days to give prompt)
Form the list of record (s) selected, using a loop , we first compare the last purchase record’s(purchaseID is auto generated number, so the highest PurchaseID value) quantity bought with the quantity in stock of that product.
IF the quantity bought (purchases details) is greater than the units in stock (products) , prompt that the number of that product(using units in stock) is expiring. You can add the date the purchase was made, from which of suppliers and the employee that handled the transaction from the Purchases table to the prompt and you end.
ELSE IF the units in stock (product) is greater than quantity bought (purchases details), this time prompt with the quantity bought as the number of products expiring. Then subtract the quantity bought from the units in stock with the remainder becoming the units in stock. mathematically unitsinstock=unitsinstock-quantitybought. Then move the the next purchase record from the list.The loop start again.
Note: another termination condition is you can loop until the unitinstock is less or equal to zero.
Note: you can build a string of the prompts in the loop and show it after the loop
Please let me have your comments……….