+ $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");
+}
+
+//-------------------------------------------------------------------------------------------------------------
+
+function get_allocatable_from_supp_transactions($supplier_id, $trans_no=null, $type=null)
+{
+ $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,
+ supplier.address,
+ amt,
+ alloc.discount,
+ supp_reference
+ FROM ".TB_PREF."supp_trans as trans,"
+ .TB_PREF."suppliers as supplier,"
+ .TB_PREF."supp_allocations as alloc
+ WHERE trans.supplier_id=supplier.supplier_id
+ AND trans.trans_no = alloc.trans_no_from
+ AND trans.type = alloc.trans_type_from
+ AND trans.supplier_id = alloc.person_id";
+
+ if ($trans_no != null and $type != null)