From a67b7a07178dd92bb8e8bf60b6f7b474a1cbf433 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Fri, 29 Apr 2022 10:56:51 +0200 Subject: [PATCH] Customer balances reports: fixed balance sums for prepayment invoices. --- reporting/includes/doctext.inc | 2 +- reporting/rep101.php | 9 +++++---- reporting/rep102.php | 4 ++-- reporting/rep108.php | 5 +++-- reporting/rep115.php | 2 +- 5 files changed, 12 insertions(+), 10 deletions(-) diff --git a/reporting/includes/doctext.inc b/reporting/includes/doctext.inc index 4b87fa6d..16db8df5 100644 --- a/reporting/includes/doctext.inc +++ b/reporting/includes/doctext.inc @@ -244,7 +244,7 @@ _("Our Order No") => '', _("Delivery Date") => '', ); - $this->headers = array(_("Trans Type"), _("#"), _("Date"), _("DueDate"), _("Charges"), + $this->headers = array(_("Trans Type"), _("#"), _("Date"), _("DueDate"), _("Debits"), _("Credits"), _("Allocated"), _("Outstanding")); } diff --git a/reporting/rep101.php b/reporting/rep101.php index fd87944f..a9bd1609 100644 --- a/reporting/rep101.php +++ b/reporting/rep101.php @@ -34,7 +34,7 @@ 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(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,"; + -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,"; @@ -42,7 +42,7 @@ function get_open_balance($debtorno, $to) $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) * - (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding + (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; @@ -73,7 +73,8 @@ function get_transactions($debtorno, $from, $to) GROUP BY trans_type_to, trans_no_to) alloc_to"; $sql = "SELECT trans.*, - (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, + 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, 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 @@ -133,7 +134,7 @@ function print_customer_balances() $cols = array(0, 95, 140, 200, 250, 320, 385, 450, 515); - $headers = array(_('Trans Type'), _('#'), _('Date'), _('Due Date'), _('Charges'), _('Credits'), + $headers = array(_('Trans Type'), _('#'), _('Date'), _('Due Date'), _('Debits'), _('Credits'), _('Allocated'), _('Outstanding')); if ($show_balance) diff --git a/reporting/rep102.php b/reporting/rep102.php index 92b56466..a63ac9c5 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -35,9 +35,9 @@ function get_invoices($customer_id, $to, $all=true) // Revomed allocated from sql if ($all) - $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)"; + $value = "IF(prep_amount, prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)"; else - $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc)"; + $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)"; $due = "IF (type=".ST_SALESINVOICE.", due_date, tran_date)"; diff --git a/reporting/rep108.php b/reporting/rep108.php index 970d5724..abb11e7f 100644 --- a/reporting/rep108.php +++ b/reporting/rep108.php @@ -38,7 +38,8 @@ function getTransactions($debtorno, $date, $show_also_allocated) trans.reference, trans.tran_date, trans.due_date, - (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) AS TotalAmount, alloc AS Allocated, + IF(prep_amount, prep_amount, ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) AS TotalAmount, + alloc AS Allocated, ((trans.type = ".ST_SALESINVOICE.") AND due_date < '$date') AS OverDue FROM ".TB_PREF."debtor_trans trans LEFT JOIN ".TB_PREF."voided as v @@ -48,7 +49,7 @@ function getTransactions($debtorno, $date, $show_also_allocated) AND ABS(ABS(ov_amount) + ov_gst + ov_freight + ov_freight_tax + ov_discount) > ". FLOAT_COMP_DELTA; if (!$show_also_allocated) - $sql .= " AND ABS(ABS(ov_amount) + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc) > ". FLOAT_COMP_DELTA; + $sql .= " AND ABS(IF(prep_amount, prep_amount, 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,"No transactions were returned"); diff --git a/reporting/rep115.php b/reporting/rep115.php index 0ed75193..484746b6 100644 --- a/reporting/rep115.php +++ b/reporting/rep115.php @@ -35,7 +35,7 @@ function get_open_balance($debtorno, $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(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,"; + -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.")), -- 2.30.2