https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18723 --- Comment #46 from Victor Grousset/tuxayo <victor.grousset@biblibre.com> --- Hi :) Here is an SQL query to detect orders and items (items.price) that were affected by this bug. And 2 others to fix these cases. And finally a large price check query that should point any case of price difference > 5 (this is arbitrary) between prices that should be close to each other (unitprice, unitprice_tax_excluded, ecost, items.price, etc) The check queries were applied to a dozen of instances that were reporting price issue with acquisition receiving and thus the budget and funds total And the fix queries in there final version were applied to a bit more than half of them. === BZ 18723 specific price check query === Should only detect cases related to this bug. All values except ecost are x100 time bigger - unitprice - items.price - unitprice_tax_excluded - unitprice_tax_included - tax_value_on_receiving ``` SELECT invoicenumber, link.ordernumber, items.itemnumber, ecost, items.price, unitprice, unitprice_tax_excluded, unitprice_tax_included, tax_value_on_receiving, unitprice_tax_excluded/ecost as ratio FROM aqorders LEFT JOIN aqinvoices ON aqinvoices.invoiceid = aqorders.invoiceid LEFT JOIN aqorders_items as link ON link.ordernumber=aqorders.ordernumber LEFT JOIN items ON link.itemnumber=items.itemnumber WHERE unitprice_tax_excluded / ecost BETWEEN 50 AND 150 AND unitprice_tax_included / ecost BETWEEN 50 AND 150 AND unitprice / ecost BETWEEN 50 AND 150 ORDER BY ratio DESC; -- BETWEEN 50 AND 150 to get the differences between order cost and final cost. -- This is arbitrary. ``` === BZ 18723 specific data fix queries === Caveat, this might be not exhaustive, there might be remaining deriving data that isn't updated. divide by 100 - unitprice - items.price - unitprice_tax_excluded - unitprice_tax_included - tax_value_on_receiving (don't forget this one) ``` UPDATE aqorders SET unitprice=unitprice / 100, unitprice_tax_excluded=unitprice_tax_excluded / 100, unitprice_tax_included=unitprice_tax_included / 100, tax_value_on_receiving=tax_value_on_receiving / 100 WHERE -- we get only the x100 cases unitprice_tax_excluded / ecost BETWEEN 50 AND 150 AND unitprice_tax_included / ecost BETWEEN 50 AND 150 AND unitprice / ecost BETWEEN 50 AND 150; ``` ``` UPDATE items set price=price/100 -- wider criteria than the issue detection request -- because a JOIN in the update would have made it less readable and error prone. -- so it might fix more cases than the only those related to bug 18723 WHERE price/replacementprice between 50 and 150; -- we get only the x100 cases -- caveat; we have seen replacementprice being NULL and thus the fix doesn't happen -- we consider this to not be related to bug 18723 -- and it will be caught be the general analysis query. ``` Hopefully we can finally have a relief about this issue. === general price check query === Compare all the values in a systematic way. It can be summarized by A not more than 5x B B not more than 5x C C not more than 5x A 5 is arbitrary, any feedback welcome. ``` SELECT invoicenumber, link.ordernumber, items.itemnumber, ecost, items.replacementprice AS items_replacementprice, aqorders.replacementprice AS aqorders_replacementprice, items.price, unitprice, unitprice_tax_excluded, unitprice_tax_included, tax_value_on_receiving, unitprice_tax_excluded/ecost as ratio FROM aqorders LEFT JOIN aqinvoices ON aqinvoices.invoiceid = aqorders.invoiceid LEFT JOIN aqorders_items as link ON link.ordernumber=aqorders.ordernumber LEFT JOIN items ON link.itemnumber=items.itemnumber WHERE unitprice_tax_excluded / unitprice_tax_included > 5 OR unitprice_tax_included / unitprice > 5 OR unitprice / aqorders.replacementprice > 5 OR aqorders.replacementprice / ecost > 5 OR ecost / items.price > 5 OR items.price / unitprice_tax_excluded > 5 ORDER BY ratio DESC; ``` Thanks Arthur for you help in finally getting rid of this! -- You are receiving this mail because: You are watching all bug changes.