From 417e2403d19af2c5817c992acf1b748cc7e90bf8 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 29 Aug 2013 21:13:53 +0200 Subject: [PATCH] Customer Balances Report, Supplier Balances Report: balances in home currency were calculated using wrong exchange rates. --- reporting/rep101.php | 40 +++++++++++----------------------------- reporting/rep201.php | 30 ++++++++---------------------- 2 files changed, 19 insertions(+), 51 deletions(-) diff --git a/reporting/rep101.php b/reporting/rep101.php index 6bff595..df34fe3 100644 --- a/reporting/rep101.php +++ b/reporting/rep101.php @@ -30,34 +30,19 @@ include_once($path_to_root . "/sales/includes/db/customers_db.inc"); // trial_inquiry_controls(); print_customer_balances(); -function get_open_balance($debtorno, $to, $convert) +function get_open_balance($debtorno, $to) { if($to) $to = date2sql($to); $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)"; - if ($convert) - $sql .= " * rate"; - $sql .= ", 0)) AS charges, + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges, SUM(IF(t.type <> ".ST_SALESINVOICE." AND t.type <> ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)"; - if ($convert) - $sql .= " * rate"; - $sql .= " * -1, 0)) AS credits, - SUM(t.alloc"; - if ($convert) - $sql .= " * rate"; - $sql .= ") AS Allocated, + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits, + SUM(t.alloc) AS Allocated, SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc)"; - if ($convert) - $sql .= " * rate"; - $sql .= ", - ((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc)"; - if ($convert) - $sql .= " * rate"; - $sql .= ")) AS OutStanding + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc), + ((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc))) AS OutStanding FROM ".TB_PREF."debtor_trans t WHERE t.debtor_no = ".db_escape($debtorno) ." AND t.type <> ".ST_CUSTDELIVERY; @@ -164,18 +149,19 @@ function print_customer_balances() if (!$convert && $currency != $myrow['curr_code']) continue; $accumulate = 0; + $rate = $convert ? get_exchange_rate_to_home_currency($myrow['curr_code'], Today()) : 1; $bal = get_open_balance($myrow['debtor_no'], $from, $convert); $init[0] = $init[1] = 0.0; - $init[0] = round2(abs($bal['charges']), $dec); - $init[1] = round2(Abs($bal['credits']), $dec); - $init[2] = round2($bal['Allocated'], $dec); + $init[0] = round2(abs($bal['charges']*$rate), $dec); + $init[1] = round2(Abs($bal['credits']*$rate), $dec); + $init[2] = round2($bal['Allocated']*$rate, $dec); if ($show_balance) { $init[3] = $init[0] - $init[1]; $accumulate += $init[3]; } else - $init[3] = round2($bal['OutStanding'], $dec); + $init[3] = round2($bal['OutStanding']*$rate, $dec); $res = get_transactions($myrow['debtor_no'], $from, $to); if ($no_zeros && db_num_rows($res) == 0) continue; @@ -211,10 +197,6 @@ function print_customer_balances() if ($trans['type'] == ST_SALESINVOICE) $rep->DateCol(3, 4, $trans['due_date'], true); $item[0] = $item[1] = 0.0; - if ($convert) - $rate = $trans['rate']; - else - $rate = 1.0; if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT) $trans['TotalAmount'] *= -1; if ($trans['TotalAmount'] > 0.0) diff --git a/reporting/rep201.php b/reporting/rep201.php index 6a9f2c2..d0b886b 100644 --- a/reporting/rep201.php +++ b/reporting/rep201.php @@ -27,30 +27,18 @@ include_once($path_to_root . "/gl/includes/gl_db.inc"); print_supplier_balances(); -function get_open_balance($supplier_id, $to, $convert) +function get_open_balance($supplier_id, $to) { $to = date2sql($to); $sql = "SELECT SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)"; - if ($convert) - $sql .= " * rate"; - $sql .= ", 0)) AS charges, + (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS charges, SUM(IF(".TB_PREF."supp_trans.type <> ".ST_SUPPINVOICE." AND ".TB_PREF."supp_trans.type <> ".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)"; - if ($convert) - $sql .= "* rate"; - $sql .= ", 0)) AS credits, - SUM(".TB_PREF."supp_trans.alloc"; - if ($convert) - $sql .= " * rate"; - $sql .= ") AS Allocated, + (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS credits, + SUM(".TB_PREF."supp_trans.alloc) AS Allocated, SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.", (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc), - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))"; - if ($convert) - $sql .= " * rate"; - $sql .= ") AS OutStanding + (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))) AS OutStanding FROM ".TB_PREF."supp_trans WHERE ".TB_PREF."supp_trans.tran_date < '$to' AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' GROUP BY supplier_id"; @@ -154,7 +142,7 @@ function print_supplier_balances() if (!$convert && $currency != $myrow['curr_code']) continue; $accumulate = 0; - $bal = get_open_balance($myrow['supplier_id'], $from, $convert); + $bal = get_open_balance($myrow['supplier_id'], $from); $init[0] = $init[1] = 0.0; $init[0] = round2(abs($bal['charges']), $dec); $init[1] = round2(Abs($bal['credits']), $dec); @@ -191,6 +179,8 @@ function print_supplier_balances() while ($trans=db_fetch($res)) { if ($no_zeros && floatcmp(abs($trans['TotalAmount']), $trans['Allocated']) == 0) continue; + $rate = $convert ? get_exchange_rate_to_home_currency($myrow['curr_code'], Today()) : 1; + $rep->NewLine(1, 2); $rep->TextCol(0, 1, $systypes_array[$trans['type']]); $rep->TextCol(1, 2, $trans['reference']); @@ -198,10 +188,6 @@ function print_supplier_balances() if ($trans['type'] == ST_SUPPINVOICE) $rep->DateCol(3, 4, $trans['due_date'], true); $item[0] = $item[1] = 0.0; - if ($convert) - $rate = $trans['rate']; - else - $rate = 1.0; if ($trans['TotalAmount'] > 0.0) { $item[0] = round2(abs($trans['TotalAmount']) * $rate, $dec); -- 2.30.2