+function get_sql_for_supplier_allocation_inquiry($from, $to, $filter, $supplier_id, $all=false)
+{
+ $date_after = date2sql($from);
+ $date_to = date2sql($to);
+
+ $sql = "SELECT
+ trans.type,
+ trans.trans_no,
+ trans.reference,
+ supplier.supp_name,
+ trans.supp_reference,
+ trans.tran_date,
+ trans.due_date,
+ supplier.curr_code,
+ (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS TotalAmount,
+ trans.alloc AS Allocated,
+ ((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
+ trans.supplier_id
+ FROM "
+ .TB_PREF."supp_trans as trans, "
+ .TB_PREF."suppliers as supplier
+ WHERE supplier.supplier_id = trans.supplier_id
+ AND trans.tran_date >= '$date_after'
+ AND trans.tran_date <= '$date_to'";
+
+ if ($supplier_id != ALL_TEXT)
+ $sql .= " AND trans.supplier_id = ".db_escape($supplier_id);
+
+ if ($filter != ALL_TEXT)
+ {
+ if (($filter == '1') || ($filter == '2'))
+ {
+ $sql .= " AND trans.type = ".ST_SUPPINVOICE." ";
+ }
+ elseif ($filter == '3')
+ {
+ $sql .= " AND trans.type = ".ST_SUPPAYMENT." ";
+ }
+ elseif (($filter == '4') || ($filter == '5'))
+ {
+ $sql .= " AND trans.type = ".ST_SUPPCREDIT." ";
+ }
+
+ if (($filter == '2') || ($filter == '5'))
+ {
+ $today = date2sql(Today());
+ $sql .= " AND trans.due_date < '$today' ";
+ }
+ }
+
+ if (!$all)
+ {
+ $sql .= " AND (round(abs(ov_amount + ov_gst + ov_discount) - alloc,6) != 0) ";
+ }
+ return $sql;
+}