From 1b9208f6334e4371fa433d3b6cdc506ae0f95f98 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 11 May 2022 16:45:04 +0200 Subject: [PATCH] Customer balances reports: further fixes and cleanups. --- reporting/rep101.php | 9 +- reporting/rep102.php | 21 +---- reporting/rep115.php | 135 +++++++++++------------------ sales/includes/db/customers_db.inc | 49 ++++++----- 4 files changed, 85 insertions(+), 129 deletions(-) diff --git a/reporting/rep101.php b/reporting/rep101.php index a9bd1609..667604c8 100644 --- a/reporting/rep101.php +++ b/reporting/rep101.php @@ -171,12 +171,9 @@ function print_customer_balances() $rate = $convert ? get_exchange_rate_from_home_currency($myrow['curr_code'], Today()) : 1; $bal = get_open_balance($myrow['debtor_no'], $from); $init = array(); - $bal['charges'] = isset($bal['charges']) ? $bal['charges'] : 0; - $bal['credits'] = isset($bal['credits']) ? $bal['credits'] : 0; - $bal['Allocated'] = isset($bal['Allocated']) ? $bal['Allocated'] : 0; - $bal['OutStanding'] = isset($bal['OutStanding']) ? $bal['OutStanding'] : 0; + $init[0] = round2(abs($bal['charges']*$rate), $dec); - $init[1] = round2(Abs($bal['credits']*$rate), $dec); + $init[1] = round2(abs($bal['credits']*$rate), $dec); $init[2] = round2($bal['Allocated']*$rate, $dec); if ($show_balance) { @@ -238,7 +235,7 @@ function print_customer_balances() } else { - $item[1] = round2(Abs($trans['TotalAmount']) * $rate, $dec); + $item[1] = round2(abs($trans['TotalAmount']) * $rate, $dec); $rep->AmountCol(5, 6, $item[1], $dec); $accumulate -= $item[1]; $item[2] = round2($trans['Allocated'] * $rate, $dec) * -1; diff --git a/reporting/rep102.php b/reporting/rep102.php index a63ac9c5..fe7ff84a 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -33,12 +33,9 @@ function get_invoices($customer_id, $to, $all=true) $PastDueDays1 = get_company_pref('past_due_days'); $PastDueDays2 = 2 * $PastDueDays1; - // Revomed allocated from sql - if ($all) - $value = "IF(prep_amount, prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)"; - else - $value = "(IF(prep_amount, prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)-alloc)"; - $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT,ST_JOURNAL))."), -1, 1)"; + $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; + $value = "$sign*(IF(trans.prep_amount, trans.prep_amount, + ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)) ".($all ? '' : "- trans.alloc").")"; $due = "IF (type=".ST_SALESINVOICE.", due_date, tran_date)"; $sql = "SELECT type, reference, tran_date, @@ -52,10 +49,8 @@ function get_invoices($customer_id, $to, $all=true) WHERE type <> ".ST_CUSTDELIVERY." AND debtor_no = $customer_id AND tran_date <= '$todate' - AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > " . FLOAT_COMP_DELTA; + AND ABS(IF(trans.prep_amount, trans.prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) ".($all ? '' : '-trans.alloc').") > " . FLOAT_COMP_DELTA; - if (!$all) - $sql .= "AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc) > " . FLOAT_COMP_DELTA; $sql .= "ORDER BY tran_date"; return db_query($sql, "The customer transactions could not be retrieved"); @@ -195,14 +190,6 @@ function print_aged_customer_analysis() $rep->TextCol(1, 2, $trans['reference'], -2); $rep->DateCol(2, 3, $trans['tran_date'], true, -2); - if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT) - { - $trans['Balance'] *= -1; - $trans['Due'] *= -1; - $trans['Overdue1'] *= -1; - $trans['Overdue2'] *= -1; - } - foreach ($trans as $i => $value) $trans[$i] = (float)$trans[$i] * $rate; $str = array($trans["Balance"] - $trans["Due"], diff --git a/reporting/rep115.php b/reporting/rep115.php index 484746b6..d75e8cf5 100644 --- a/reporting/rep115.php +++ b/reporting/rep115.php @@ -31,33 +31,36 @@ print_customer_balances(); function get_open_balance($debtorno, $to) { + if($to) + $to = date2sql($to); + $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0), + -abs(IF(t.prep_amount, t.prep_amount, t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)), 0)) AS charges,"; + + $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0), + abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,"; + + $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.")), t.alloc * -1, t.alloc)) AS Allocated,"; + + $sql .= "SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0), 1, -1) * + (IF(t.prep_amount, t.prep_amount, abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)) - abs(t.alloc))) AS OutStanding + FROM ".TB_PREF."debtor_trans t + WHERE t.debtor_no = ".db_escape($debtorno) + ." AND t.type <> ".ST_CUSTDELIVERY; if ($to) - $to = date2sql($to); - - $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0), - -abs(IF(prep_amount, prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)), 0)) AS charges,"; - $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.") AND t.ov_amount>0), - abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,"; - $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type IN (".ST_JOURNAL." , ".ST_BANKPAYMENT.")), - t.alloc * -1, t.alloc)) AS Allocated,"; - $sql .= "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) * - (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding - FROM ".TB_PREF."debtor_trans t - WHERE t.debtor_no = ".db_escape($debtorno) - ." AND t.type <> ".ST_CUSTDELIVERY; - if ($to) - $sql .= " AND t.tran_date < '$to'"; - $sql .= " GROUP BY debtor_no"; + $sql .= " AND t.tran_date < '$to'"; + $sql .= " GROUP BY debtor_no"; $result = db_query($sql,"No transactions were returned"); return db_fetch($result); } -function get_transactions($debtorno, $from, $to, $only_rec) +function get_transactions($debtorno, $from, $to) { $from = date2sql($from); $to = date2sql($to); + $sign = "IF(trans.type IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; + $allocated_from = "(SELECT trans_type_from as trans_type, trans_no_from as trans_no, date_alloc, sum(amt) amount FROM ".TB_PREF."cust_allocations alloc @@ -72,8 +75,9 @@ function get_transactions($debtorno, $from, $to, $only_rec) GROUP BY trans_type_to, trans_no_to) alloc_to"; $sql = "SELECT trans.*, comments.memo_, - (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, - IFNULL(alloc_from.amount, alloc_to.amount) AS Allocated, + $sign*IF(trans.prep_amount, trans.prep_amount, trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) + AS TotalAmount, + $sign*IFNULL(alloc_from.amount, alloc_to.amount) AS Allocated, ((trans.type = ".ST_SALESINVOICE.") AND trans.due_date < '$to') AS OverDue FROM ".TB_PREF."debtor_trans trans LEFT JOIN ".TB_PREF."voided voided ON trans.type=voided.type AND trans.trans_no=voided.id @@ -156,7 +160,6 @@ function print_customer_balances() else $nozeros = _('No'); $cols = array(0, 100, 130, 190, 250, 320, 385, 450, 515); - //$cols = array(0, 70, 140, 180, 230, 270, 350, 445, 495, 555); $headers = array(_('Name'), '', '', _('Open Balance'), _('Debit'), _('Credit'), '', _('Balance')); @@ -208,99 +211,65 @@ function print_customer_balances() $result = db_query($sql, "The customers could not be retrieved"); - $tot_cur_cr = $tot_cur_db = 0; + $tot_cur_cr = $tot_cur_db = $tot_open = $tot_bal = 0; while ($myrow = db_fetch($result)) { if (!$convert && $currency != $myrow['curr_code']) continue; - $accumulate = 0; $rate = $convert ? get_exchange_rate_from_home_currency($myrow['curr_code'], Today()) : 1; - $bal = get_open_balance($myrow['debtor_no'], $from, $convert); + $bal = get_open_balance($myrow['debtor_no'], $from); $init = array(); - $bal['charges'] = isset($bal['charges']) ? $bal['charges'] : 0; - $bal['credits'] = isset($bal['credits']) ? $bal['credits'] : 0; - $bal['Allocated'] = isset($bal['Allocated']) ? $bal['Allocated'] : 0; - $bal['OutStanding'] = isset($bal['OutStanding']) ? $bal['OutStanding'] : 0; - $init[0] = round2(abs($bal['charges'] * $rate), $dec); - $init[1] = round2(Abs($bal['credits'] * $rate), $dec); - $init[2] = round2($bal['Allocated'] * $rate, $dec); - $init[3] = $init[0] - $init[1]; - $accumulate += $init[3]; - - $res = get_transactions($myrow['debtor_no'], $from, $to, false); - - $total = array(0,0,0,0); - for ($i = 0; $i < 4; $i++) - { - $total[$i] += $init[$i]; - $grandtotal[$i] += $init[$i]; - } + $curr_db = $bal ? round2(abs($bal['charges'] * $rate), $dec) : 0; // db + $curr_cr = $bal ? round2(abs($bal['credits'] * $rate), $dec) : 0; // cr +// $curr_alloc = $bal ? round2($bal['Allocated'] * $rate, $dec) : 0; // allocated + $curr_open = $curr_db-$curr_cr; // balance + $tot_open += $curr_open; + + $res = get_transactions($myrow['debtor_no'], $from, $to); if (db_num_rows($res) == 0 && !$no_zeros) { - $rep->TextCol(0, 2, $myrow['name']); - $rep->AmountCol(3, 4, $init[3], $dec); - $rep->AmountCol(7, 8, $init[3], $dec); - //$rep->Line($rep->row - 2); + $rep->TextCol(0, 2, $myrow['name']); + $rep->AmountCol(3, 4, $curr_open, $dec); + $rep->AmountCol(7, 8, $curr_open, $dec); $rep->NewLine(1); continue; } - $curr_cr = $curr_db = 0; + $curr_db = $curr_cr = 0; while ($trans = db_fetch($res)) //Detail starts here { - $item[0] = $item[1] = 0.0; - //modified below by faisal - if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT) - $trans['TotalAmount'] *= -1; + if ($trans['TotalAmount'] > 0.0) - { - $item[0] = round2(abs($trans['TotalAmount']) * $rate, $dec); - $accumulate += $item[0]; - $curr_db += $item[0]; - $tot_cur_db += $item[0]; - $item[2] = round2($trans['Allocated'] * $rate, $dec); - } - else - { - $item[1] = round2(Abs($trans['TotalAmount']) * $rate, $dec); - $accumulate -= $item[1]; - $curr_cr += $item[1]; - $tot_cur_cr +=$item[1]; - $item[2] = round2($trans['Allocated'] * $rate, $dec) * -1; - } - - if ($trans['type'] == ST_JOURNAL || $trans['type'] == ST_SALESINVOICE || $trans['type'] == ST_BANKPAYMENT) - $item[3] = $item[0] - $item[2]; + $curr_db += round2(($trans['TotalAmount']) * $rate, $dec); else - $item[3] = -$item[1] - $item[2]; - - for ($i = 0; $i < 4; $i++) - { - $total[$i] += $item[$i]; - $grandtotal[$i] += $item[$i]; - } - $total[3] = $total[0] - $total[1]; + $curr_cr += -round2(($trans['TotalAmount']) * $rate, $dec); } - if ($no_zeros && $total[3] == 0.0 && $curr_db == 0.0 && $curr_cr == 0.0) continue; + + $tot_cur_db += $curr_db; + $tot_cur_cr += $curr_cr; + + if ($no_zeros && $curr_open == 0.0 && $curr_db == 0.0 && $curr_cr == 0.0) continue; $rep->TextCol(0, 2, $myrow['name']); - $rep->AmountCol(3, 4, $total[3] + $curr_cr - $curr_db, $dec); + $rep->AmountCol(3, 4, $curr_open, $dec); $rep->AmountCol(4, 5, $curr_db, $dec); $rep->AmountCol(5, 6, $curr_cr, $dec); - $rep->AmountCol(7, 8, $total[3], $dec); - //$rep->Line($rep->row - 2); + $rep->AmountCol(7, 8, $curr_open+$curr_db-$curr_cr, $dec); $rep->NewLine(1); + } + $rep->Line($rep->row + 4); $rep->NewLine(); $rep->fontSize += 2; $rep->TextCol(0, 3, _('Grand Total')); $rep->fontSize -= 2; - $grandtotal[3] = $grandtotal[0] - $grandtotal[1]; - $rep->AmountCol(3, 4, $grandtotal[3] - $tot_cur_db + $tot_cur_cr, $dec); + $tot_bal = $tot_open+$tot_cur_db-$tot_cur_cr; + + $rep->AmountCol(3, 4, $tot_open, $dec); $rep->AmountCol(4, 5, $tot_cur_db, $dec); $rep->AmountCol(5, 6, $tot_cur_cr, $dec); - $rep->AmountCol(7, 8, $grandtotal[3], $dec); + $rep->AmountCol(7, 8, $tot_bal, $dec); $rep->Line($rep->row - 6, 1); $rep->NewLine(); $rep->End(); diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 96e17cef..1707ef90 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -73,40 +73,43 @@ function get_customer_details($customer_id, $to=null, $all=true) $past1 = get_company_pref('past_due_days'); $past2 = 2 * $past1; - $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, IF(type=".ST_JOURNAL.",IF(ov_amount>=0,-1,1), 1))"; - $value = "$sign*(IF(trans.prep_amount, trans.prep_amount, + $sign = "IF(trans.type IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; + $value = "$sign*(IF(trans.prep_amount, trans.prep_amount, ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)) ".($all ? '' : "- trans.alloc").")"; + $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)"; - $due = "IF (trans.type=10, trans.due_date, trans.tran_date)"; - $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, + $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, debtor.credit_limit, credit_status.dissallow_invoices, credit_status.reason_description, - Sum(IFNULL($value,0)) AS Balance, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2 + Sum($sign*IFNULL($value,0)) AS Balance, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0)) AS Due, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$sign*$value,0)) AS Overdue1, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$sign*$value,0)) AS Overdue2 FROM ".TB_PREF."debtors_master debtor - LEFT JOIN ".TB_PREF."debtor_trans trans ON - trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> 13 AND trans.type <> 14, - ".TB_PREF."payment_terms terms, - ".TB_PREF."credit_status credit_status + LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ". ST_CUSTDELIVERY.", + ".TB_PREF."payment_terms terms, + ".TB_PREF."credit_status credit_status WHERE debtor.payment_terms = terms.terms_indicator - AND debtor.credit_status = credit_status.id - AND debtor.debtor_no = ".db_escape($customer_id)." "; + AND debtor.credit_status = credit_status.id"; + + if ($customer_id) + $sql .= " AND debtor.debtor_no = ".db_escape($customer_id); + if (!$all) - $sql .= "AND ABS($value) >= 0.004 "; - $sql .= "GROUP BY - debtor.name, - terms.terms, - terms.days_before_due, - terms.day_in_following_month, - debtor.credit_limit, - credit_status.dissallow_invoices, - credit_status.reason_description"; + $sql .= " AND ABS(IF(trans.prep_amount, trans.prep_amount, ABS(trans.ov_amount) + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA; + + $sql .= " GROUP BY + debtor.name, + terms.terms, + terms.days_before_due, + terms.day_in_following_month, + debtor.credit_limit, + credit_status.dissallow_invoices, + credit_status.reason_description"; $result = db_query($sql,"The customer details could not be retrieved"); -- 2.30.2