Our website [login to view URL] recently developed a bug where products stock qty changed through some unknown action. e.g some deactivated products suddenly had 50pcs instock. As you can imagine this has caused us problems when people have purchased the stock and we have to supply as we don't have the stock on our shelves.
The job is to locate the problem, recreate the problem in a test environment (we have a beta website for testing purposes) and fix the problem with a 3 month guarantee. i.e if we find that the same problem happens again within 3 months you need to correct the code.
## Deliverables
We have a Classic ASP website running MSSQL express. There is no option in Express to monitor transactions at the db level outside of the code.
If we could do this then we could monitor the change of the qtock qty of a particular record in the following ways:
1. According to the code of the website
2. According to independant transactions of the db
And then we could compare the results and locate the discrepancies and use this to focus into the problem with the code.
A junior coder has tried his best (with limited time) to solve the problem without success.
We have documentation on the website that I could send you.
I will forward you all his finding upon request. Here is an example:
Sir I have been thinking on this, since you reported it, to devise at least a way to find out products having suspicious stock level.
One can only trace a mismatch either by chance, or on the basis of personal perception about a certain product's stock level.
What I could do through quering the db, is to compare the qty in hand field of each record with our stock log.
In stock log, we can pick latest value of closing stock field, and compare it with qtyinHand field actually being used.
I have made a query and found many records which had different values, (see attached). There are 700+ such records, thats why i have doubts about stock log.
Sir, an entry in stock log is made in three ways:
By sale
By Invoice
By Stock Update
The first two entries are reliable as they have some reference points where we can confirm the transaction (Order ID, Invoice No).
But the log record through Stock Update seems to be suspicious, as there is no way to cross match the record.
A user can enter a qty in stock update at his whim.
So this was the only way to check and assuming that stock log is correct, we can consider these products (attached) as suspicious.
My RESPONSE:
So I guess we need to put some checks in place for the stock log to avoid this problem.