- $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(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.")),
- t.alloc * -1, t.alloc)) AS Allocated,";
- $sql .= "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
- (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;
- if ($to)
- $sql .= " AND t.tran_date < '$to'";
- $sql .= " GROUP BY debtor_no";