[Koha-bugs] [Bug 18723] Dot not recognized as decimal separator on receive

[bugzilla-daemon at bugs.koha-community.org]

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.