[Koha-bugs] [Bug 18723] Dot not recognized as decimal separator on receive
bugzilla-daemon at bugs.koha-community.org
bugzilla-daemon at bugs.koha-community.org
Fri Nov 15 21:04:48 CET 2019
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18723
--- Comment #46 from Victor Grousset/tuxayo <victor.grousset at 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.
More information about the Koha-bugs
mailing list