From: Janusz Dobrowolski Date: Tue, 21 Jun 2022 09:54:28 +0000 (+0200) Subject: Additional bugfixes for prepayment handling in sales reports (Customer Balances,... X-Git-Url: https://delta.frontaccounting.com/gitweb/?p=fa-stable.git;a=commitdiff_plain;h=f7093d7dde32fcccbe14b828ccdcf80bc652d7c3 Additional bugfixes for prepayment handling in sales reports (Customer Balances, Aged Customer Analysys, Customer Statements and Sales Invoice) --- diff --git a/includes/dashboard.inc b/includes/dashboard.inc index f666f26f..c8636644 100644 --- a/includes/dashboard.inc +++ b/includes/dashboard.inc @@ -788,10 +788,12 @@ function customer_aging($today, $width="33") $today1 = date2sql($today); $past1 = get_company_pref('past_due_days'); $past2 = 2 * $past1; - // removed - debtor_trans.alloc from all summations + $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; - $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - - trans.alloc)*trans.rate),0)"; + + $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)) - trans.alloc)*trans.rate"; + $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)"; $sql = "SELECT Sum($value) AS Balance, Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due, @@ -804,7 +806,7 @@ function customer_aging($today, $width="33") WHERE debtor.payment_terms = terms.terms_indicator AND debtor.credit_status = credit_status.id"; - $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA; + $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; $result = db_query($sql,"The customer details could not be retrieved"); $row = db_fetch($result); diff --git a/reporting/rep102.php b/reporting/rep102.php index fe7ff84a..76cbe1a5 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -34,22 +34,24 @@ function get_invoices($customer_id, $to, $all=true) $PastDueDays2 = 2 * $PastDueDays1; $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, - $sign*$value as Balance, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0) AS Due, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$sign*$value,0) AS Overdue1, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$sign*$value,0) 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."debtor_trans trans WHERE type <> ".ST_CUSTDELIVERY." AND debtor_no = $customer_id AND tran_date <= '$todate' - 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; + AND ABS($value) > " . FLOAT_COMP_DELTA; $sql .= "ORDER BY tran_date"; diff --git a/reporting/rep107.php b/reporting/rep107.php index 0ac5f03b..1362d5d1 100644 --- a/reporting/rep107.php +++ b/reporting/rep107.php @@ -61,8 +61,6 @@ print_invoices(); function print_invoices() { global $path_to_root, $SysPrefs; - - $show_this_payment = true; // include payments invoiced here in summary include_once($path_to_root . "/reporting/includes/pdf_report.inc"); @@ -143,8 +141,11 @@ function print_invoices() // calculate summary start row for later use $summary_start_row = $rep->bottomMargin + (15 * $rep->lineHeight); + $show_this_payment = $rep->formData['prepaid'] == 'partial'; // include payments invoiced here in summary + if ($rep->formData['prepaid']) { + $result = get_sales_order_invoices($myrow['order_']); $prepayments = array(); while($inv = db_fetch($result)) diff --git a/sales/customer_invoice.php b/sales/customer_invoice.php index e4ab5158..3bf5a6bb 100644 --- a/sales/customer_invoice.php +++ b/sales/customer_invoice.php @@ -649,10 +649,10 @@ if ($prepaid) $allocs += $pmt['amt']; } } - label_row(_("Payments received:"), implode(',', $list)); label_row(_("Invoiced here:"), price_format($_SESSION['Items']->prep_amount), 'class=label'); - label_row(_("Left to be invoiced:"), price_format($_SESSION['Items']->get_trans_total()-max($_SESSION['Items']->prep_amount, $allocs)), 'class=label'); + label_row($_SESSION['Items']->payment_terms['days_before_due'] == -1 ? _("Left to be invoiced:") : _("Invoiced so far:"), + price_format($_SESSION['Items']->get_trans_total()-max($_SESSION['Items']->prep_amount, $allocs)), 'class=label'); } textarea_row(_("Memo:"), 'Comments', null, 50, 4); diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 1707ef90..036253cd 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -78,14 +78,13 @@ function get_customer_details($customer_id, $to=null, $all=true) 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)"; - $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, debtor.credit_limit, credit_status.dissallow_invoices, credit_status.reason_description, - 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 + 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 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 <> ". ST_CUSTDELIVERY.", diff --git a/sales/includes/db/sales_invoice_db.inc b/sales/includes/db/sales_invoice_db.inc index ccbcc0eb..a80232ec 100644 --- a/sales/includes/db/sales_invoice_db.inc +++ b/sales/includes/db/sales_invoice_db.inc @@ -84,7 +84,6 @@ function write_sales_invoice(&$invoice) } else { $prepaid_factor = 1; } - // write_customer_trans have to be called after optional void_cust_allocations above $invoice_no = write_customer_trans(ST_SALESINVOICE, $trans_no, $invoice->customer_id, $invoice->Branch, $date_, $invoice->reference, $items_total, 0, @@ -300,18 +299,14 @@ function get_cust_prepayment_invoice_factor($trans_no) */ function prepaid_invoice_remainder($order) { - $sql = "SELECT so.total - IFNULL(SUM(inv.prep_amount),0) FROM " - .TB_PREF."sales_orders so, - ".TB_PREF."debtor_trans inv, - ".TB_PREF."payment_terms pt + $sql = "SELECT so.total - SUM(IFNULL(inv.prep_amount,0)) FROM " + .TB_PREF."sales_orders so + LEFT JOIN ".TB_PREF."payment_terms pt ON so.payment_terms=pt.terms_indicator AND pt.days_before_due = -1 + LEFT JOIN ".TB_PREF."debtor_trans inv ON inv.type=".ST_SALESINVOICE ." AND inv.order_=so.order_no WHERE so.order_no=".db_escape($order) ." AND so.trans_type=".ST_SALESORDER - ." AND inv.type=".ST_SALESINVOICE - ." AND inv.order_=so.order_no" - ." AND so.payment_terms=pt.terms_indicator" - ." AND inv.payment_terms=pt.terms_indicator" - ." AND pt.days_before_due = -1"; - + ." AND so.payment_terms=pt.terms_indicator"; + $result = db_fetch(db_query($sql, "cannot find final invoice value")); return $result[0] ? $result[0] : 0; }