- if ($trans_no != null && $type!= null)
- {
- $sql = get_alloc_supp_sql("amt, supp_reference", "trans.trans_no = alloc.trans_no_to
- AND trans.type = alloc.trans_type_to
- AND alloc.trans_no_from=".db_escape($trans_no)."
- AND alloc.trans_type_from=".db_escape($type)."
- AND trans.supplier_id=".db_escape($supplier_id),
- TB_PREF."supp_allocations as alloc");
- }
- else
- {
- $sql = get_alloc_supp_sql(null, "round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0
- AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT, ST_BANKPAYMENT)).")
- AND trans.supplier_id=".db_escape($supplier_id));
- }
-
- return db_query($sql." ORDER BY due_date", "Cannot retreive alloc to transactions");
+ $sql = "SELECT
+ trans.type,
+ trans.trans_no,
+ IF(trans.supp_reference='',trans.reference,trans.supp_reference) as reference,
+ trans.tran_date,
+ supplier.supp_name,
+ supplier.curr_code,
+ ov_amount+ov_gst+ov_discount AS Total,
+ trans.alloc,
+ trans.due_date,
+ trans.supplier_id,
+ IF(alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type) . ", alloc.amt, 0) amt,
+ IF(alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type) . ", alloc.discount, 0) discount,
+ supp_reference,
+ pmt.early_days,
+ pmt.early_discount
+
+ FROM ".TB_PREF."supp_trans as trans
+ LEFT JOIN ".TB_PREF."suppliers supplier ON trans.supplier_id=supplier.supplier_id
+ LEFT JOIN ".TB_PREF."payment_terms as pmt ON supplier.payment_terms=pmt.id
+ LEFT JOIN ".TB_PREF."supp_allocations as alloc
+ ON trans.trans_no = alloc.trans_no_to AND trans.type = alloc.trans_type_to AND alloc.person_id=trans.supplier_id";
+
+ $sql .= " WHERE (
+ (round(ov_amount+ov_gst+ov_discount-alloc,6) > 0
+ AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT, ST_BANKPAYMENT))."))
+ OR (alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type)."))";
+
+ if ($supplier_id)
+ $sql .= " AND trans.supplier_id=".db_escape($supplier_id);
+ elseif (isset($trans_no) && isset($type))
+ $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type);
+
+ $sql .= " GROUP BY type, trans_no"; // avoids row multiplication for journal entry containing multiply suppliers
+
+ $orders = get_allocatable_purch_orders($supplier_id, $trans_no, $type);
+ $sql = "($sql) \nUNION \n($orders) ORDER BY amt, due_date";
+
+ return db_query($sql, "Cannot retreive alloc to transactions");