From 6e3e48e051f3b4c819777df71a09f8c725bb9e81 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Tue, 24 Jun 2008 13:00:29 +0000 Subject: [PATCH] Fixed inconsistencies in customer and supplier aging --- CHANGELOG.txt | 11 +++ purchasing/includes/db/suppliers_db.inc | 117 +++++++---------------- purchasing/includes/ui/invoice_ui.inc | 2 +- reporting/rep102.php | 59 ++---------- reporting/rep202.php | 118 +++++++----------------- sales/includes/db/customers_db.inc | 62 +------------ sales/inquiry/customer_inquiry.php | 4 +- 7 files changed, 94 insertions(+), 279 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index d9dfdd91..041e28be 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,17 @@ Legend: ! -> Note $ -> Affected files +24-Jun-2008 Joe Hunt +# Fixed inconsistencies in customer and supplier aging +$ /sales/includes/db/customers_db.inc + /sales/inquiry/customer_inquiry.php + /purchasing/includes/db/suppliers_db.inc + /reporting/rep102.php + /reporting/rep202.php +# Fixed a bug when trying to back the inv.date and due date in Direct Invoicing. +$ /sales/sales_order_entry.php + + 23-Jun-2008 Janusz Dobrowolski + Ajax additions to sales and purchasing modules /purchasing/po_receive_items.php diff --git a/purchasing/includes/db/suppliers_db.inc b/purchasing/includes/db/suppliers_db.inc index 792a5ec9..69acd8dc 100644 --- a/purchasing/includes/db/suppliers_db.inc +++ b/purchasing/includes/db/suppliers_db.inc @@ -2,77 +2,24 @@ function get_supplier_details($supplier_id, $to=null) { - + if ($to == null) $todate = date("Y-m-d"); - else + else $todate = date2sql($to); $past1 = get_company_pref('past_due_days'); $past2 = 2 * $past1; // removed - supp_trans.alloc from all summations - + + $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)"; + $due = "IF (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21,".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)"; $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."payment_terms.terms, - - Sum(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) AS Balance, - - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= 0 - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - - )) AS Due, - - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past1) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - - )) AS Overdue1, - - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past2) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - - )) AS Overdue2 + + Sum($value) 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 FROM ".TB_PREF."suppliers, ".TB_PREF."payment_terms, @@ -89,71 +36,71 @@ function get_supplier_details($supplier_id, $to=null) ".TB_PREF."payment_terms.terms, ".TB_PREF."payment_terms.days_before_due, ".TB_PREF."payment_terms.day_in_following_month"; - + $result = db_query($sql,"The customer details could not be retrieved"); - + if (db_num_rows($result) == 0) { - + /*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */ - + $nil_balance = true; - + $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.supplier_id, ".TB_PREF."payment_terms.terms FROM ".TB_PREF."suppliers, ".TB_PREF."payment_terms WHERE ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND ".TB_PREF."suppliers.supplier_id = '$supplier_id'"; - + $result = db_query($sql,"The customer details could not be retrieved"); - - } - else + + } + else { $nil_balance = false; } - + $supp = db_fetch($result); - + if ($nil_balance == true) { $supp["Balance"] = 0; $supp["Due"] = 0; $supp["Overdue1"] = 0; $supp["Overdue2"] = 0; - } - + } + return $supp; - + } function get_supplier($supplier_id) { $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; - + $result = db_query($sql, "could not get supplier"); - + return db_fetch($result); } function get_supplier_name($supplier_id) { $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; - + $result = db_query($sql, "could not get supplier"); - + $row = db_fetch_row($result); - + return $row[0]; } function get_supplier_accounts($supplier_id) { $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; - + $result = db_query($sql, "could not get supplier"); - + return db_fetch($result); } diff --git a/purchasing/includes/ui/invoice_ui.inc b/purchasing/includes/ui/invoice_ui.inc index 6b3a0a12..13115116 100644 --- a/purchasing/includes/ui/invoice_ui.inc +++ b/purchasing/includes/ui/invoice_ui.inc @@ -185,8 +185,8 @@ function display_gl_items(&$supp_trans, $mode=0) echo ""; // ouer table - start_form(); div_start('gl_items'); + start_form(); echo ""; $dim = get_company_pref('use_dimension'); diff --git a/reporting/rep102.php b/reporting/rep102.php index e3bf9d3f..41d6379f 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -26,56 +26,16 @@ function get_invoices($costomer_id, $to) $PastDueDays2 = 2 * $PastDueDays1; // Revomed allocated from sql + $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " + .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " + .TB_PREF."debtor_trans.ov_discount)"; + $due = "IF (".TB_PREF."debtor_trans.type=10,".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)"; $sql = "SELECT ".TB_PREF."sys_types.type_name, ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference, ".TB_PREF."debtor_trans.tran_date, - (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount) as Balance, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= ".TB_PREF."payment_terms.days_before_due THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0 THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END - ) AS Due, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays1) THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $PastDueDays1 THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END - ) AS Overdue1, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays2) THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $PastDueDays2 THEN - ".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount - ELSE - 0 - END - ) AS Overdue2 + $value as Balance, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0) AS Due, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays1,$value,0) AS Overdue1, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays2,$value,0) AS Overdue2 FROM ".TB_PREF."debtors_master, ".TB_PREF."payment_terms, @@ -88,10 +48,9 @@ function get_invoices($costomer_id, $to) AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no AND ".TB_PREF."debtor_trans.debtor_no = $costomer_id AND ".TB_PREF."debtor_trans.tran_date <= '$todate' - AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount) > 0.004 + AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) > 0.004 ORDER BY ".TB_PREF."debtor_trans.tran_date"; - return db_query($sql, "The customer details could not be retrieved"); } diff --git a/reporting/rep202.php b/reporting/rep202.php index ce39dd86..ad720ecc 100644 --- a/reporting/rep202.php +++ b/reporting/rep202.php @@ -28,76 +28,28 @@ function get_invoices($supplier_id, $to) $PastDueDays2 = 2 * $PastDueDays1; // Revomed allocated from sql - $sql = "SELECT ".TB_PREF."sys_types.type_name, - ".TB_PREF."supp_trans.reference, - ".TB_PREF."supp_trans.tran_date, - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) as Balance, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= ".TB_PREF."payment_terms.days_before_due - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= 0 - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - ) AS Due, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays1) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $PastDueDays1 - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - ) AS Overdue1, - IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $PastDueDays2) - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $PastDueDays2 - THEN - ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ELSE - 0 - END - ) AS Overdue2 - - FROM ".TB_PREF."suppliers, - ".TB_PREF."payment_terms, - ".TB_PREF."supp_trans, - ".TB_PREF."sys_types - - WHERE ".TB_PREF."sys_types.type_id = ".TB_PREF."supp_trans.type - AND ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator - AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id - AND ".TB_PREF."supp_trans.supplier_id = $supplier_id - AND ".TB_PREF."supp_trans.tran_date <= '$todate' - AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) > 0.004 - ORDER BY ".TB_PREF."supp_trans.tran_date"; + $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)"; + $due = "IF (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21,".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)"; + $sql = "SELECT ".TB_PREF."sys_types.type_name, + ".TB_PREF."supp_trans.reference, + ".TB_PREF."supp_trans.tran_date, + $value as Balance, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0) AS Due, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays1,$value,0) AS Overdue1, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays2,$value,0) AS Overdue2 + + FROM ".TB_PREF."suppliers, + ".TB_PREF."payment_terms, + ".TB_PREF."supp_trans, + ".TB_PREF."sys_types + + WHERE ".TB_PREF."sys_types.type_id = ".TB_PREF."supp_trans.type + AND ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator + AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id + AND ".TB_PREF."supp_trans.supplier_id = $supplier_id + AND ".TB_PREF."supp_trans.tran_date <= '$todate' + AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) > 0.004 + ORDER BY ".TB_PREF."supp_trans.tran_date"; return db_query($sql, "The supplier details could not be retrieved"); @@ -121,8 +73,8 @@ function print_aged_supplier_analysis() { include_once($path_to_root . "reporting/includes/class.graphic.inc"); $pg = new graph(); - } - + } + if ($fromsupp == reserved_words::get_all_numeric()) $from = _('All'); else @@ -150,7 +102,7 @@ function print_aged_supplier_analysis() $headers = array(_('Supplier'), '', '', _('Current'), $nowdue, $pastdue1,$pastdue2, _('Total Balance')); - + $aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right', 'right'); $params = array( 0 => $comments, @@ -175,14 +127,14 @@ function print_aged_supplier_analysis() $nowdue = "1-" . $PastDueDays1 . " " . _('Days'); $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . _('Days'); $pastdue2 = _('Over') . " " . $PastDueDays2 . " " . _('Days'); - + $sql = "SELECT supplier_id, supp_name AS name, curr_code FROM ".TB_PREF."suppliers "; if ($fromsupp != reserved_words::get_all_numeric()) $sql .= "WHERE supplier_id=$fromsupp "; $sql .= "ORDER BY supp_name"; $result = db_query($sql, "The suppliers could not be retrieved"); - - while ($myrow=db_fetch($result)) + + while ($myrow=db_fetch($result)) { if (!$convert && $currency != $myrow['curr_code']) continue; @@ -197,7 +149,7 @@ function print_aged_supplier_analysis() $rate = 1.0; $rep->fontSize -= 2; $supprec = get_supplier_details($myrow['supplier_id'], $to); - foreach ($supprec as $i => $value) + foreach ($supprec as $i => $value) $supprec[$i] *= $rate; $total[0] += ($supprec["Balance"] - $supprec["Due"]); $total[1] += ($supprec["Due"]-$supprec["Overdue1"]); @@ -211,7 +163,7 @@ function print_aged_supplier_analysis() number_format2($supprec["Balance"],$dec)); for ($i = 0; $i < count($str); $i++) $rep->TextCol($i + 3, $i + 4, $str[$i]); - $rep->NewLine(1, 2); + $rep->NewLine(1, 2); if (!$summaryOnly) { $res = get_invoices($myrow['supplier_id'], $to); @@ -224,7 +176,7 @@ function print_aged_supplier_analysis() $rep->TextCol(0, 1, $trans['type_name'], -2); $rep->TextCol(1, 2, $trans['reference'], -2); $rep->TextCol(2, 3, sql2date($trans['tran_date']), -2); - foreach ($trans as $i => $value) + foreach ($trans as $i => $value) $trans[$i] *= $rate; $str = array(number_format2(($trans["Balance"] - $trans["Due"]),$dec), number_format2(($trans["Due"]-$trans["Overdue1"]),$dec), @@ -233,10 +185,10 @@ function print_aged_supplier_analysis() number_format2($trans["Balance"],$dec)); for ($i = 0; $i < count($str); $i++) $rep->TextCol($i + 3, $i + 4, $str[$i]); - } + } $rep->Line($rep->row - 8); $rep->NewLine(2); - } + } } if ($summaryOnly) { @@ -252,8 +204,8 @@ function print_aged_supplier_analysis() if ($graphics && $i < count($total) - 1) { $pg->y[$i] = abs($total[$i]); - } - } + } + } $rep->Line($rep->row - 8); if ($graphics) { diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 637bc07a..c13554df 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -16,68 +16,15 @@ function get_customer_details($customer_id, $to=null) "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " .TB_PREF."debtor_trans.ov_discount)"; - + $due = "IF (".TB_PREF."debtor_trans.type=10,".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)"; $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms, ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description, Sum(".$value.") AS Balance, - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due - THEN - $value - ELSE 0 - END, - - CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0 - THEN - $value - ELSE 0 - END - - )) AS Due, - - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1) - THEN - $value - ELSE - 0 - END, - - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past1) - THEN - $value - ELSE - 0 - END - - )) AS Overdue1, - - Sum(IF (".TB_PREF."payment_terms.days_before_due > 0, - CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due - AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2) - THEN - $value - ELSE - 0 - END, - - CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date, - INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - - DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past2) - THEN - $value - ELSE - 0 - END - - )) AS Overdue2 + 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 FROM ".TB_PREF."debtors_master, ".TB_PREF."payment_terms, @@ -100,7 +47,6 @@ function get_customer_details($customer_id, $to=null) ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description"; - $result = db_query($sql,"The customer details could not be retrieved"); if (db_num_rows($result) == 0) diff --git a/sales/inquiry/customer_inquiry.php b/sales/inquiry/customer_inquiry.php index 0524673d..715de08a 100644 --- a/sales/inquiry/customer_inquiry.php +++ b/sales/inquiry/customer_inquiry.php @@ -150,7 +150,7 @@ function get_transactions() div_start('totals_tbl'); if ($_POST['customer_id'] != "" && $_POST['customer_id'] != reserved_words::get_all()) { - $customer_record = get_customer_details($_POST['customer_id']); + $customer_record = get_customer_details($_POST['customer_id'], $_POST['TransToDate']); display_customer_summary($customer_record); echo "
"; } @@ -160,7 +160,7 @@ div_end(); $result = get_transactions(); //------------------------------------------------------------------------------------------------ -if(get_post('RefreshInquiry')) +if(get_post('RefreshInquiry')) { $Ajax->activate('trans_tbl'); $Ajax->activate('totals_tbl'); -- 2.30.2