micwallace
commented
7 years ago If you can reproduce the same behaviour on the demo site, I'll be happy to investigate further: https://demo.wallacepos.com/
I haven't been able to reproduce the wrong figured on the tax report. Although it would help if I knew which tax setting you are using.
No point changing the report functions. If there are multiple entries in the sale_items table then that should be fixed (haven't checked yet but I don't think that's happening). All the sales stuff happens in WposSales.php. Does this happen in a certain scenario? For instance if you save an order before completing the sale?
amol-beast
nyugoh
KenyanGeek
I wrote a Python script to check the validity of the Admin->Reports->Tax Breakdown. I found the WallacePos report always exceeding from my script report. On further looking deep, I found that somehow the sale_items table is having multiple entries for each item per saleid. This is making the report wrong.
Context
Process
I don't know what situation created this problem. I am keeping an eye out to what steps will create this. I have written a test script, which is checking the db every 30min for this activity in Prod. I will inform once I find the source of the problem
Expected result
Ideally there should be only one entry for each sold item per saleid in sale_items Table
Current result
For instance:- select * from sale_items where saleid=120 Result:-
id, saleid, storeditemid, saleitemid, qty, name, description, taxid, tax, tax_incl, tax_total, cost, unit_original, unit, price, refundqty
'423', '120', '4906', 'cb1952df', '2', 'CONCH', '', '4', '{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false}', '0', '60.00', '400.00', '600.00', '600.00', '1260.00', '0' '439', '120', '4906', 'cb1952df', '2', 'CONCH', '', '4', '{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false}', '0', '60.00', '400.00', '600.00', '600.00', '1260.00', '0' '452', '120', '4906', 'cb1952df', '2', 'CONCH', '', '4', '{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false}', '0', '60.00', '400.00', '600.00', '600.00', '1260.00', '0' '454', '120', '4906', 'cb1952df', '2', 'CONCH', '', '4', '{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false}', '0', '60.00', '400.00', '600.00', '600.00', '1260.00', '0' '455', '120', '4906', 'cb1952df', '2', 'CONCH', '', '4', '{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false}', '0', '60.00', '400.00', '600.00', '600.00', '1260.00', '0'
Table sales for id=120 SQL Statement:- select * from sales where id =120
id, ref, type, channel, data, userid, deviceid, locationid, custid, discount, rounding, cost, total, balance, status, processdt, duedt, dt
'120', '1499351854142-12-5621', 'sale', 'pos', '{\"ref\":\"1499351854142-12-5621\",\"userid\":\"1\",\"devid\":\"12\",\"locid\":\"7\",\"custid\":\"0\",\"custemail\":\"\",\"notes\":\"\",\"discount\":\"0\",\"rounding\":\"0.00\",\"cost\":\"800.00\",\"subtotal\":\"1200.00\",\"total\":\"1260.00\",\"numitems\":2,\"processdt\":1499351854142,\"items\":[{\"ref\":\"cb1952df\",\"sitemid\":\"4906\",\"qty\":2,\"name\":\"CONCH\",\"unit\":\"600.00\",\"taxid\":\"4\",\"tax\":{\"total\":60,\"values\":{\"4\":60},\"inclusive\":false},\"price\":\"1260.00\",\"desc\":\"\",\"hsncode\":\"508\",\"cost\":\"400\",\"unit_original\":\"600\",\"alt_name\":\"\",\"id\":\"455\"}],\"payments\":[{\"method\":\"cash\",\"amount\":\"1260.00\",\"tender\":\"1260.00\",\"change\":\"0.00\",\"id\":\"298\",\"processdt\":1499351854142}],\"tax\":\"60.00\",\"taxdata\":{\"4\":\"60.00\"},\"id\":\"120\",\"dt\":\"2017-07-07 00:41:36\",\"balance\":0,\"status\":1}', '1', '12', '7', '0', '0', '0.00', '800.00', '1260.00', '0.00', '1', '1499351854142', '0', '2017-07-07 00:37:39'
Due to multiple entries in sale_items table, the Tax Breakdown report is generating excessive values(due to summing repeated values)
Apparently when I run this sql statement:- select count(saleid),saleid,id from sale_items group by saleid order by count(saleid) desc I find this result(top 5):-
count(saleid), saleid, id
'1904', '137', '6634' '1818', '131', '3085' '1790', '128', '1596' '582', '122', '914' '546', '139', '8413' '444', '118', '311'
Possible Fix
Yet to find. Possible Workaround:- This statement in library\wpos\models\db\SaleItemsModel.php line 219 $sql = "SELECT i., COALESCE(i.price-(i.price(s.discount/100)), 0) AS itemtotal, COALESCE((i.price(s.discount/100)/i.qty)i.refundqty, 0) AS refundtotal, s.ref as ref, s.discount as discount FROM sale_items AS i LEFT JOIN sales AS s ON i.saleid=s.id WHERE (s.processdt>= :stime AND s.processdt<= :etime) ".($novoids?'AND s.status!=3':'');
Instead of doing Inner join, we should just return items from the sales table, and adjust it in library\wpos\models\db\SaleItemsModel.php line 234 funtion getTaxStats()
I am trying to find the root of the problem Please let me know if in any other way I can help. Thanks