From d07b6ec5fd2bbfbd24335406131fc30f7862dbeb Mon Sep 17 00:00:00 2001 From: Joe Date: Tue, 28 Feb 2023 11:00:12 +0100 Subject: [PATCH] Bug 5676: Currency stored in MySQL DOUBLE Type causes strange error. Fixed by explicitly SQL ROUND to price dec in updating allocations. --- purchasing/includes/db/suppalloc_db.inc | 3 ++- sales/includes/db/custalloc_db.inc | 3 ++- 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/purchasing/includes/db/suppalloc_db.inc b/purchasing/includes/db/suppalloc_db.inc index 228f7b1c..b67708cd 100644 --- a/purchasing/includes/db/suppalloc_db.inc +++ b/purchasing/includes/db/suppalloc_db.inc @@ -52,12 +52,13 @@ function get_supp_trans_allocation_balance($trans_type, $trans_no) // function update_supp_trans_allocation($trans_type, $trans_no, $person_id) { + $dec = user_price_dec(); $sql = "UPDATE `".TB_PREF.($trans_type==ST_PURCHORDER ? 'purch_orders' : 'supp_trans')."` trans, (SELECT person_id, sum(amt) amt from ".TB_PREF."supp_allocations WHERE person_id=".db_escape($person_id)." AND ((trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no).") OR (trans_type_from=".db_escape($trans_type)." AND trans_no_from=".db_escape($trans_no)."))) allocated SET - trans.alloc=IFNULL(allocated.amt, 0) + trans.alloc=IFNULL(ROUND(allocated.amt, $dec), 0) WHERE trans.supplier_id=person_id AND " . ($trans_type==ST_PURCHORDER ? "trans.order_no=".db_escape($trans_no) : "trans.type=".db_escape($trans_type)." AND trans.trans_no=".db_escape($trans_no)); diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc index ea7db7da..eadcbdad 100644 --- a/sales/includes/db/custalloc_db.inc +++ b/sales/includes/db/custalloc_db.inc @@ -46,13 +46,14 @@ function get_cust_allocation($trans_id) // function update_debtor_trans_allocation($trans_type, $trans_no, $person_id) { + $dec = user_price_dec(); $sql = "UPDATE `".TB_PREF.($trans_type==ST_SALESORDER ? 'sales_orders' : 'debtor_trans')."` trans, (SELECT sum(amt) amt FROM ".TB_PREF."cust_allocations WHERE person_id=".db_escape($person_id)." AND ((trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no).") OR (trans_type_from=".db_escape($trans_type)." AND trans_no_from=".db_escape($trans_no)."))) allocated SET - trans.alloc=IFNULL(allocated.amt,0) + trans.alloc=IFNULL(ROUND(allocated.amt, $dec),0) WHERE trans.debtor_no=".db_escape($person_id)." AND ".($trans_type==ST_SALESORDER ? "trans.trans_type=".db_escape($trans_type)." AND order_no=".db_escape($trans_no) : "trans.type=".db_escape($trans_type)." AND trans_no=".db_escape($trans_no)); -- 2.30.2