$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)";
+ $sign = "IF(trans.`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
if ($all)
$value = "IFNULL($sign*(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount),0)";
else
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.","
- .TB_PREF."payment_terms terms,"
+ LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY."
+ LEFT JOIN ".TB_PREF."payment_terms terms ON debtor.payment_terms = terms.id,"
.TB_PREF."credit_status credit_status
WHERE
- debtor.payment_terms = terms.terms_indicator
- AND debtor.credit_status = credit_status.id";
+ debtor.credit_status = credit_status.id";
if ($customer_id)
$sql .= " AND debtor.debtor_no = ".db_escape($customer_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 .= " 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";