//----------------------------------------------------------------------------------------
function add_supp_allocation($amount, $trans_type_from, $trans_no_from,
- $trans_type_to, $trans_no_to, $person_id, $date_)
+ $trans_type_to, $trans_no_to, $person_id, $date_, $discount=0)
{
$date = date2sql($date_);
$sql = "INSERT INTO ".TB_PREF."supp_allocations (
- amt, date_alloc,
+ amt, discount, date_alloc,
trans_type_from, trans_no_from, trans_no_to, trans_type_to, person_id)
- VALUES (".db_escape($amount).", '$date', "
+ VALUES (".db_escape($amount).",".db_escape($discount).", '$date', "
.db_escape($trans_type_from).", ".db_escape($trans_no_from).", "
.db_escape($trans_no_to).", ".db_escape($trans_type_to).", ".db_escape($person_id).")";
function delete_supp_allocation($trans_id)
{
$sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE id = ".db_escape($trans_id);
- db_query($sql, "The existing allocation $trans_id could not be deleted");
+ return db_query($sql, "The existing allocation $trans_id could not be deleted");
}
//----------------------------------------------------------------------------------------
$sql .= " AND person_id=".db_escape($person_id);
db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
-// is this necessary?
-// if ($date != "")
-// exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
-// $row['amt'], PT_SUPPLIER, true);
}
//-------------------------------------------------------------------------------------------------------------
return $sql;
}
-function get_allocatable_purch_orders($supplier_id = null, $trans_no=null, $type=null)
+function get_allocatable_purch_orders($supplier_id, $trans_no=null, $type=null)
{
$due_dates = "SELECT order_no, MIN(delivery_date) as date
FROM ".TB_PREF."purch_order_details det
porder.alloc,
due_dates.date as due_date,
supplier.address,
- amt,
- requisition_no as supp_ref
+ ".(isset($trans_no) ? "IF(alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type) . ", alloc.amt, 0) amt" : "alloc.amt").",
+ ".(isset($trans_no) ? "IF(alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type) . ", alloc.discount, 0) discount" : "alloc.discount").",
+ supp_reference as supp_ref,
+ 0, 0
FROM ".TB_PREF."purch_orders as porder
LEFT JOIN ".TB_PREF."suppliers as supplier ON porder.supplier_id = supplier.supplier_id
LEFT JOIN ".TB_PREF."supp_allocations as alloc
LEFT JOIN ($due_dates) due_dates ON due_dates.order_no=porder.order_no
WHERE total>0";
- if ($trans_no != null and $type != null)
- {
- $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)."
- AND alloc.trans_type_from=".db_escape($type);
- }
- else
- {
-// FIXME: change to form below when prepayments terms finally can be selected in PO entry form:
-// $sql .= " AND round(prep_amount) > 0 AND ISNULL(grn.purch_order_no)"; // only sales orders with prepayment level set and no yet received
- $sql .= " AND ISNULL(grn.purch_order_no)"; // only sales orders no yet received
- }
+ $sql .= " AND ( ISNULL(grn.purch_order_no)" // only orders no yet received
+ ."OR (alloc.trans_no_from=".db_escape($trans_no)." AND alloc.trans_type_from=".db_escape($type)."))";
+
if ($supplier_id)
$sql .= " AND porder.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 porder.order_no, grn.purch_order_no";
return $sql;
}
+
//-------------------------------------------------------------------------------------------------------------
+//
+// Returns all allocatable purchase transactions including all settled with selected payment.
+//
+// 1. not fully settled; supplier_id!=0, trans_no/type!=null (for trans edition)
+// 2. for given transaction; supplier_id=null, trans_no/type!=null (for transaction view)
+// 3. for supplier, not fully settled supplier_id!=0, trans_no/type=null (non fully settled documents for supplier exists)
+// 4. for all suppliers, not fully settled supplier_id==0, trans_no/type=null (any non fully settled documents exists)
-function get_allocatable_to_supp_transactions($supplier_id=null, $trans_no=null, $type=null)
+function get_allocatable_to_supp_transactions($supplier_id, $trans_no=null, $type=null)
{
$sql = "SELECT
trans.type,
trans.alloc,
trans.due_date,
trans.supplier_id,
- amt,
- supp_reference
+ 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,"
- .TB_PREF."suppliers as supplier
- WHERE
- trans.supplier_id=supplier.supplier_id";
+ 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 supplier.supplier_id=".db_escape($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);
- if ($trans_no != null and $type != null)
- {
- $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)."
- AND alloc.trans_type_from=".db_escape($type);
- }
- else
- {
- $sql .= " AND round(ov_amount+ov_gst+ov_discount-alloc,6) > 0
- AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT, ST_BANKPAYMENT)).")";
- $sql .= " GROUP BY type, trans_no";
- }
+ $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 ORDER BY trans_no) \nUNION \n($orders)";
+ $sql = "($sql) \nUNION \n($orders) ORDER BY amt, due_date";
- return db_query($sql." ORDER BY due_date", "Cannot retreive alloc to transactions");
+ return db_query($sql, "Cannot retreive alloc to transactions");
}
//-------------------------------------------------------------------------------------------------------------
trans.supplier_id,
supplier.address,
amt,
+ alloc.discount,
supp_reference
FROM ".TB_PREF."supp_trans as trans,"
.TB_PREF."suppliers as supplier,"
AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT,ST_BANKPAYMENT)).")";
$sql .= " GROUP BY type, trans_no";
}
-//_vd($sql);
return db_query($sql." ORDER BY due_date", "Cannot retreive alloc to transactions");
}
projects / fa-stable.git / blobdiff