From 27d2650c63d4c853054945938251bfe884faed74 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 18 Jun 2017 19:45:33 +0200 Subject: [PATCH] Fixed document total presentation in customer/supplier transaction inquiries. --- purchasing/inquiry/supplier_inquiry.php | 14 +++----------- sales/includes/db/cust_trans_db.inc | 18 +++++++++++------- sales/inquiry/customer_inquiry.php | 17 +++-------------- 3 files changed, 17 insertions(+), 32 deletions(-) diff --git a/purchasing/inquiry/supplier_inquiry.php b/purchasing/inquiry/supplier_inquiry.php index b76c949f..023e839e 100644 --- a/purchasing/inquiry/supplier_inquiry.php +++ b/purchasing/inquiry/supplier_inquiry.php @@ -136,17 +136,10 @@ function credit_link($row) : ''; } -function fmt_debit($row) +function fmt_amount($row) { $value = $row["TotalAmount"]; - return $value>0 ? price_format($value) : ''; - -} - -function fmt_credit($row) -{ - $value = -$row["TotalAmount"]; - return $value>0 ? price_format($value) : ''; + return price_format($value); } function prt_link($row) @@ -182,8 +175,7 @@ $cols = array( _("Date") => array('name'=>'tran_date', 'type'=>'date', 'ord'=>'desc'), _("Due Date") => array('type'=>'date', 'fun'=>'due_date'), _("Currency") => array('align'=>'center'), - _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'), - _("Credit") => array('align'=>'right', 'insert'=>true,'fun'=>'fmt_credit'), + _("Amount") => array('align'=>'right', 'fun'=>'fmt_debit'), array('insert'=>true, 'fun'=>'gl_view'), array('insert'=>true, 'fun'=>'credit_link'), array('insert'=>true, 'fun'=>'prt_link'), diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index a962ba28..5cba6490 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -318,33 +318,36 @@ function get_sql_for_customer_inquiry($from, $to, $cust_id = ALL_TEXT, $filter = branch.br_name, debtor.curr_code, debtor.debtor_no, - (trans.ov_amount + trans.ov_gst + trans.ov_freight + IF(prep_amount, prep_amount, trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, "; if ($filter != ALL_TEXT) - $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), "; + $sql .= "@bal := @bal+IF(prep_amount, prep_amount, trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), "; // else // $sql .= "IF(trans.type=".ST_CUSTDELIVERY.",'', IF(trans.type=".ST_SALESINVOICE." OR trans.type=".ST_BANKPAYMENT.",@bal := @bal+ // (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), @bal := @bal- // (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , "; $sql .= "trans.alloc AS Allocated, - ((trans.type = ".ST_SALESINVOICE.") + ((trans.type = ".ST_SALESINVOICE." || trans.type = ".ST_JOURNAL.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue , Sum(line.quantity-line.qty_done) AS Outstanding, - Sum(line.qty_done) AS HasChild + Sum(line.qty_done) AS HasChild, + prep_amount FROM " .TB_PREF."debtor_trans as trans LEFT JOIN ".TB_PREF."debtor_trans_details as line ON trans.trans_no=line.debtor_trans_no AND trans.type=line.debtor_trans_type LEFT JOIN ".TB_PREF."voided as v ON trans.trans_no=v.id AND trans.type=v.type + LEFT JOIN ".TB_PREF."audit_trail as audit ON (trans.type=audit.type AND trans.trans_no=audit.trans_no) + LEFT JOIN ".TB_PREF."users as user ON (audit.user=user.id) LEFT JOIN ".TB_PREF."cust_branch as branch ON trans.branch_code=branch.branch_code," .TB_PREF."debtors_master as debtor WHERE debtor.debtor_no = trans.debtor_no AND trans.tran_date >= '$date_after' - AND trans.tran_date <= '$date_to' - AND trans.branch_code = branch.branch_code - AND ISNULL(v.date_)"; + AND trans.tran_date <= '$date_to'" + // exclude voided transactions and self-balancing (journal) transactions: + . " AND ISNULL(v.date_) AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) != 0"; if ($cust_id != ALL_TEXT) $sql .= " AND trans.debtor_no = ".db_escape($cust_id); @@ -381,6 +384,7 @@ function get_sql_for_customer_inquiry($from, $to, $cust_id = ALL_TEXT, $filter = trans.ov_freight + trans.ov_discount - trans.alloc > 0) "; } } + $sql .= " GROUP BY trans.trans_no, trans.type"; return $sql; diff --git a/sales/inquiry/customer_inquiry.php b/sales/inquiry/customer_inquiry.php index 720edc35..7a1cc62b 100644 --- a/sales/inquiry/customer_inquiry.php +++ b/sales/inquiry/customer_inquiry.php @@ -135,24 +135,14 @@ function gl_view($row) return get_gl_view_str($row["type"], $row["trans_no"]); } -function fmt_debit($row) +function fmt_amount($row) { $value = $row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT || $row['type']==ST_JOURNAL ? -$row["TotalAmount"] : $row["TotalAmount"]; - return $value>=0 ? price_format($value) : ''; - + return price_format($value); } -function fmt_credit($row) -{ - $value = - !($row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT || $row['type']==ST_JOURNAL) ? - -$row["TotalAmount"] : $row["TotalAmount"]; - return $value>0 ? price_format($value) : ''; -} - - function credit_link($row) { global $page_nested; @@ -208,8 +198,7 @@ $cols = array( _("Customer") => array('ord'=>''), _("Branch") => array('ord'=>''), _("Currency") => array('align'=>'center'), - _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'), - _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'), + _("Amount") => array('align'=>'right', 'fun'=>'fmt_amount'), _("RB") => array('align'=>'right', 'type'=>'amount'), array('insert'=>true, 'fun'=>'gl_view'), array('insert'=>true, 'fun'=>'credit_link'), -- 2.30.2