From a9abd72df4aa0cbc4fb6f422f2a1412421c435fc Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 9 Nov 2008 18:06:55 +0000 Subject: [PATCH] Prepared to paged view of allocations. --- sales/includes/db/cust_trans_db.inc | 33 ++-------------- sales/includes/db/custalloc_db.inc | 59 ++++++++++++++++++++++------- 2 files changed, 49 insertions(+), 43 deletions(-) diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index b91a2795..3ee406dd 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -135,7 +135,7 @@ function get_customer_trans($trans_id, $trans_type) if ($trans_type == systypes::cust_payment()) { // it's a payment so also get the bank account $sql .= ", ".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name, - ".TB_PREF."bank_trans_types.name AS BankTransType "; + ".TB_PREF."bank_accounts.account_type AS BankTransType "; } if ($trans_type == 10 OR $trans_type == 11 OR $trans_type == 13) { @@ -154,7 +154,7 @@ function get_customer_trans($trans_id, $trans_type) if ($trans_type == systypes::cust_payment()) { // it's a payment so also get the bank account - $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts, ".TB_PREF."bank_trans_types "; + $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts"; } if ($trans_type == 10 OR $trans_type == 11 OR $trans_type == 13) { @@ -170,8 +170,7 @@ function get_customer_trans($trans_id, $trans_type) // it's a payment so also get the bank account $sql .= " AND ".TB_PREF."bank_trans.trans_no =$trans_id AND ".TB_PREF."bank_trans.type=$trans_type - AND ".TB_PREF."bank_trans_types.id = ".TB_PREF."bank_trans.bank_trans_type_id - AND ".TB_PREF."bank_accounts.account_code=".TB_PREF."bank_trans.bank_act "; + AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; } if ($trans_type == 10 OR $trans_type == 11 OR $trans_type == 13) { // it's an invoice so also get the shipper @@ -203,32 +202,6 @@ function get_customer_trans($trans_id, $trans_type) //---------------------------------------------------------------------------------------- -function get_customer_transactions($extra_fields=null, $extra_conditions=null, $extra_tables=null) -{ - $sql = "SELECT ".TB_PREF."debtor_trans.*, - ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total, - ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.address, - ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtor_trans.version "; - - if ($extra_fields) - $sql .= ", $extra_fields "; - - $sql .= " FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master "; - if ($extra_tables) - $sql .= ",$extra_tables "; - - $sql .= " WHERE ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no"; - - if ($extra_conditions) - $sql .= " AND $extra_conditions "; - - $sql .= " ORDER BY trans_no"; - - return db_query($sql, "Cannot retreive debtor transactions"); -} - -//---------------------------------------------------------------------------------------- - function exists_customer_trans($type, $type_no) { $sql = "SELECT trans_no FROM ".TB_PREF."debtor_trans WHERE type=$type diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc index 8062faf1..44bdcb23 100644 --- a/sales/includes/db/custalloc_db.inc +++ b/sales/includes/db/custalloc_db.inc @@ -82,6 +82,39 @@ function clear_cust_alloctions($type, $type_no, $date="") db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no"); } +//---------------------------------------------------------------------------------------- + +function get_customer_transactions($extra_fields=null, $extra_conditions=null, $extra_tables=null) +{ + $sql = "SELECT trans.trans_no, + trans.type, + trans.tran_date, + trans.due_date, + trans.reference, + trans.alloc, + ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total, + debtor.name AS DebtorName, debtor.address, + debtor.curr_code, + trans.version "; + + if ($extra_fields) + $sql .= ", $extra_fields "; + + $sql .= " FROM ".TB_PREF."debtor_trans as trans, " + .TB_PREF."debtors_master as debtor"; + if ($extra_tables) + $sql .= ",$extra_tables "; + + $sql .= " WHERE trans.debtor_no=debtor.debtor_no"; + + if ($extra_conditions) + $sql .= " AND $extra_conditions "; + + $sql .= " ORDER BY trans_no"; + + return db_query($sql, "Cannot retreive debtor transactions"); +} + //------------------------------------------------------------------------------------------------------------- @@ -94,10 +127,10 @@ function get_allocatable_from_cust_transactions($customer_id, $settled) } $cust_sql = ""; if ($customer_id != null) - $cust_sql = " AND ".TB_PREF."debtor_trans.debtor_no = $customer_id"; + $cust_sql = " AND trans.debtor_no = $customer_id"; return get_customer_transactions("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled", - "(type=12 OR type=11 OR type=2) AND (".TB_PREF."debtor_trans.ov_amount > 0) " . $settled_sql . $cust_sql); + "(type=12 OR type=11 OR type=2) AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql); } //------------------------------------------------------------------------------------------------------------- @@ -106,21 +139,21 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ { if ($trans_no != null and $type != null) { - return get_customer_transactions("amt", "".TB_PREF."debtor_trans.trans_no = ".TB_PREF."cust_allocations.trans_no_to - AND ".TB_PREF."debtor_trans.type = ".TB_PREF."cust_allocations.trans_type_to - AND ".TB_PREF."cust_allocations.trans_no_from=$trans_no - AND ".TB_PREF."cust_allocations.trans_type_from=$type - AND ".TB_PREF."debtor_trans.debtor_no=$customer_id", - "".TB_PREF."cust_allocations"); + return get_customer_transactions("amt", "trans.trans_no = alloc.trans_no_to + AND trans.type = alloc.trans_type_to + AND alloc.trans_no_from=$trans_no + AND alloc.trans_type_from=$type + AND trans.debtor_no=$customer_id", + "".TB_PREF."cust_allocations as alloc"); } else { return get_customer_transactions(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0 - AND ".TB_PREF."debtor_trans.type != " . systypes::cust_payment() . " - AND ".TB_PREF."debtor_trans.type != " . systypes::bank_deposit() . " - AND ".TB_PREF."debtor_trans.type != 11 - AND ".TB_PREF."debtor_trans.type != 13 - AND ".TB_PREF."debtor_trans.debtor_no=$customer_id"); + AND trans.type != " . systypes::cust_payment() . " + AND trans.type != " . systypes::bank_deposit() . " + AND trans.type != 11 + AND trans.type != 13 + AND trans.debtor_no=$customer_id"); } } -- 2.30.2