From 772bf0b1831da4239fd643c3608a57f9e59d345c Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Tue, 10 Nov 2015 23:24:35 +0100 Subject: [PATCH] Further cleanups in sql statements. --- includes/ui/ui_lists.inc | 6 +- inventory/includes/db/items_db.inc | 9 +-- reporting/includes/reports_classes.inc | 83 ++++++++++++--------- sales/includes/db/customers_db.inc | 64 ++++++++--------- sales/includes/db/sales_order_db.inc | 99 +++++++++++++------------- sales/includes/sales_db.inc | 9 +-- taxes/db/item_tax_types_db.inc | 8 ++- taxes/db/tax_types_db.inc | 38 +++++----- 8 files changed, 168 insertions(+), 148 deletions(-) diff --git a/includes/ui/ui_lists.inc b/includes/ui/ui_lists.inc index 4fc34b91..0d15e749 100644 --- a/includes/ui/ui_lists.inc +++ b/includes/ui/ui_lists.inc @@ -1367,7 +1367,7 @@ function workcenter_list_row($label, $name, $selected_id=null, $all_option=false function bank_accounts_list($name, $selected_id=null, $submit_on_change=false, $spec_option=false) { - $sql = "SELECT ".TB_PREF."bank_accounts.id, bank_account_name, bank_curr_code, inactive + $sql = "SELECT id, bank_account_name, bank_curr_code, inactive FROM ".TB_PREF."bank_accounts"; return combo_input($name, $selected_id, $sql, 'id', 'bank_account_name', @@ -1400,9 +1400,9 @@ function bank_accounts_list_row($label, $name, $selected_id=null, $submit_on_cha function cash_accounts_list_row($label, $name, $selected_id=null, $submit_on_change=false, $all_option=false) { - $sql = "SELECT ".TB_PREF."bank_accounts.id, bank_account_name, bank_curr_code, inactive + $sql = "SELECT id, bank_account_name, bank_curr_code, inactive FROM ".TB_PREF."bank_accounts - WHERE ".TB_PREF."bank_accounts.account_type=".BT_CASH; + WHERE account_type=".BT_CASH; if ($label != null) echo "$label\n"; diff --git a/inventory/includes/db/items_db.inc b/inventory/includes/db/items_db.inc index ed69376e..18f441e3 100644 --- a/inventory/includes/db/items_db.inc +++ b/inventory/includes/db/items_db.inc @@ -64,7 +64,7 @@ function add_item($stock_id, $description, $long_description, $category_id, db_query($sql, "The item could not be added"); $sql = "INSERT INTO ".TB_PREF."loc_stock (loc_code, stock_id) - SELECT ".TB_PREF."locations.loc_code, ".db_escape($stock_id) + SELECT loc_code, ".db_escape($stock_id) ." FROM ".TB_PREF."locations"; db_query($sql, "The item locstock could not be added"); @@ -98,9 +98,10 @@ function delete_item($stock_id) function get_item($stock_id) { - $sql = "SELECT ".TB_PREF."stock_master.*,".TB_PREF."item_tax_types.name AS tax_type_name - FROM ".TB_PREF."stock_master,".TB_PREF."item_tax_types - WHERE ".TB_PREF."item_tax_types.id=".TB_PREF."stock_master.tax_type_id + $sql = "SELECT item.*, taxtype.name AS tax_type_name + FROM ".TB_PREF."stock_master item," + .TB_PREF."item_tax_types taxtype + WHERE taxtype.id=item.tax_type_id AND stock_id=".db_escape($stock_id); $result = db_query($sql,"an item could not be retreived"); diff --git a/reporting/includes/reports_classes.inc b/reporting/includes/reports_classes.inc index 0c32c420..b7cccecc 100644 --- a/reporting/includes/reports_classes.inc +++ b/reporting/includes/reports_classes.inc @@ -315,50 +315,60 @@ class BoxReports case 'INVOICE': $IV = $type_shortcuts[ST_SALESINVOICE]; $ref = ($SysPrefs->print_invoice_no() == 1 ? "trans_no" : "reference"); - $sql = "SELECT concat(".TB_PREF."debtor_trans.trans_no, '-', - ".TB_PREF."debtor_trans.type) AS TNO, concat('$IV ', ".TB_PREF."debtor_trans.$ref,' ', ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."debtor_trans WHERE type=".ST_SALESINVOICE." AND ".TB_PREF."debtors_master.debtor_no=".TB_PREF."debtor_trans.debtor_no ORDER BY ".TB_PREF."debtor_trans.trans_no DESC"; + $sql = "SELECT concat(debtor_trans.trans_no, '-', debtor_trans.type) AS TNO, + concat('$IV ', debtor_trans.$ref,' ', debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."debtor_trans debtor_trans + WHERE type=".ST_SALESINVOICE." AND debtor.debtor_no=debtor_trans.debtor_no + ORDER BY debtor_trans.trans_no DESC"; return combo_input($name, '', $sql, 'TNO', 'IName',array('order'=>false)); case 'CREDIT': $CN = $type_shortcuts[ST_CUSTCREDIT]; $ref = ($SysPrefs->print_invoice_no() == 1 ? "trans_no" : "reference"); - $sql = "SELECT concat(".TB_PREF."debtor_trans.trans_no, '-', - ".TB_PREF."debtor_trans.type) AS TNO, concat('$CN ', ".TB_PREF."debtor_trans.$ref,' ', ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."debtor_trans WHERE type=".ST_CUSTCREDIT." AND ".TB_PREF."debtors_master.debtor_no=".TB_PREF."debtor_trans.debtor_no ORDER BY ".TB_PREF."debtor_trans.trans_no DESC"; + $sql = "SELECT concat(debtor_trans.trans_no, '-', debtor_trans.type) AS TNO, + concat('$CN ', debtor_trans.$ref,' ', debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."debtor_trans debtor_trans + WHERE type=".ST_CUSTCREDIT." AND debtor.debtor_no=debtor_trans.debtor_no + ORDER BY debtor_trans.trans_no DESC"; return combo_input($name, '', $sql, 'TNO', 'IName',array('order'=>false)); case 'DELIVERY': $DN = $type_shortcuts[ST_CUSTDELIVERY]; - $sql = "SELECT - concat(".TB_PREF."debtor_trans.trans_no, '-', ".TB_PREF."debtor_trans.type) AS TNO, concat(".TB_PREF."debtor_trans.trans_no, ' $DN ', - ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."debtor_trans - WHERE type=".ST_CUSTDELIVERY." AND ".TB_PREF."debtors_master.debtor_no=". - TB_PREF."debtor_trans.debtor_no ORDER BY ".TB_PREF."debtor_trans.trans_no DESC"; + $sql = "SELECT concat(debtor_trans.trans_no, '-', debtor_trans.type) AS TNO, + concat(debtor_trans.trans_no, ' $DN ', debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."debtor_trans debtor_trans + WHERE type=".ST_CUSTDELIVERY." AND debtor.debtor_no=debtor_trans.debtor_no + ORDER BY debtor_trans.trans_no DESC"; return combo_input($name, '', $sql, 'TNO', 'IName',array('order'=>false)); case 'ORDERS': $ref = ($SysPrefs->print_invoice_no() == 1 ? "order_no" : "reference"); - $sql = "SELECT ".TB_PREF."sales_orders.order_no, concat(".TB_PREF."sales_orders.$ref, '-', - ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."sales_orders WHERE ".TB_PREF."debtors_master.debtor_no=".TB_PREF."sales_orders.debtor_no - AND ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." ORDER BY ".TB_PREF."sales_orders.order_no DESC"; + $sql = "SELECT sorder.order_no, concat(sorder.$ref, '-', debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."sales_orders sorder + WHERE debtor.debtor_no=sorder.debtor_no AND sorder.trans_type=".ST_SALESORDER + ." ORDER BY sorder.order_no DESC"; return combo_input($name, '', $sql, 'order_no', 'IName',array('order'=>false)); case 'QUOTATIONS': $ref = ($SysPrefs->print_invoice_no() == 1 ? "order_no" : "reference"); - $sql = "SELECT ".TB_PREF."sales_orders.order_no, concat(".TB_PREF."sales_orders.$ref, '-', - ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."sales_orders WHERE ".TB_PREF."debtors_master.debtor_no=".TB_PREF."sales_orders.debtor_no - AND ".TB_PREF."sales_orders.trans_type=".ST_SALESQUOTE." ORDER BY ".TB_PREF."sales_orders.order_no DESC"; + $sql = "SELECT sorder.order_no, concat(sorder.$ref, '-', debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."sales_orders sorder + WHERE debtor.debtor_no=sorder.debtor_no AND sorder.trans_type=".ST_SALESQUOTE + ." ORDER BY sorder.order_no DESC"; return combo_input($name, '', $sql, 'order_no', 'IName',array('order'=>false)); case 'PO': $ref = ($SysPrefs->print_invoice_no() == 1 ? "order_no" : "reference"); - $sql = "SELECT ".TB_PREF."purch_orders.order_no, concat(".TB_PREF."purch_orders.$ref, '-', - ".TB_PREF."suppliers.supp_name) as IName - FROM ".TB_PREF."suppliers, ".TB_PREF."purch_orders WHERE ".TB_PREF."suppliers.supplier_id=".TB_PREF."purch_orders.supplier_id ORDER BY ".TB_PREF."purch_orders.order_no DESC"; + $sql = "SELECT po.order_no, concat(po.$ref, '-', supplier.supp_name) as IName + FROM ".TB_PREF."suppliers supplier," + .TB_PREF."purch_orders po + WHERE supplier.supplier_id=po.supplier_id + ORDER BY po.order_no DESC"; return combo_input($name, '', $sql, 'order_no', 'IName',array('order'=>false)); case 'REMITTANCE': @@ -366,18 +376,25 @@ class BoxReports $SP = $type_shortcuts[ST_SUPPAYMENT]; $CN = $type_shortcuts[ST_SUPPCREDIT]; $ref = ($SysPrefs->print_invoice_no() == 1 ? "trans_no" : "reference"); - $sql = "SELECT concat(".TB_PREF."supp_trans.trans_no, '-', - ".TB_PREF."supp_trans.type) AS TNO, concat(".TB_PREF."supp_trans.$ref, if (type=".ST_BANKPAYMENT.", ' $BP ', if (type=".ST_SUPPAYMENT.", ' $SP ', ' $CN ')), ".TB_PREF."suppliers.supp_name) as IName - FROM ".TB_PREF."suppliers, ".TB_PREF."supp_trans WHERE (type=".ST_BANKPAYMENT." OR type=".ST_SUPPAYMENT." OR type=".ST_SUPPCREDIT.") AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id ORDER BY ".TB_PREF."supp_trans.trans_no DESC"; + $sql = "SELECT concat(trans.trans_no, '-',trans.type) AS TNO, + concat(trans.$ref, IF(type=".ST_BANKPAYMENT.", ' $BP ', IF(type=".ST_SUPPAYMENT.", ' $SP ', ' $CN ')), supplier.supp_name) as IName + FROM ".TB_PREF."suppliers supplier, " + .TB_PREF."supp_trans trans + WHERE type IN(".ST_BANKPAYMENT.",".ST_SUPPAYMENT.",".ST_SUPPCREDIT.") + AND supplier.supplier_id=trans.supplier_id + ORDER BY trans.trans_no DESC"; return combo_input($name, '', $sql, 'TNO', 'IName',array('order'=>false)); case 'RECEIPT': $BD = $type_shortcuts[ST_BANKDEPOSIT]; $CP = $type_shortcuts[ST_CUSTPAYMENT]; $ref = ($SysPrefs->print_invoice_no() == 1 ? "trans_no" : "reference"); - $sql = "SELECT concat(".TB_PREF."debtor_trans.trans_no, '-', - ".TB_PREF."debtor_trans.type) AS TNO, concat(".TB_PREF."debtor_trans.$ref, if (type=".ST_BANKDEPOSIT.", ' $BD ', ' $CP '), ".TB_PREF."debtors_master.name) as IName - FROM ".TB_PREF."debtors_master, ".TB_PREF."debtor_trans WHERE (type=".ST_BANKDEPOSIT." OR type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT.") AND ".TB_PREF."debtors_master.debtor_no=".TB_PREF."debtor_trans.debtor_no ORDER BY ".TB_PREF."debtor_trans.trans_no DESC"; + $sql = "SELECT concat(trans.trans_no, '-', trans.type) AS TNO, + concat(trans.$ref, IF(type=".ST_BANKDEPOSIT.", ' $BD ', ' $CP '), debtor.name) as IName + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."debtor_trans trans + WHERE type IN(".ST_BANKDEPOSIT.",".ST_CUSTPAYMENT.",".ST_CUSTCREDIT.") AND debtor.debtor_no=trans.debtor_no + ORDER BY trans.trans_no DESC"; return combo_input($name, '', $sql, 'TNO', 'IName',array('order'=>false)); case 'ITEMS': @@ -390,9 +407,11 @@ class BoxReports return stock_purchasable_items_list($name, null, true); case 'WORKORDER': - $sql = "SELECT ".TB_PREF."workorders.id, concat(".TB_PREF."workorders.id, '-', - ".TB_PREF."stock_master.description) as IName - FROM ".TB_PREF."stock_master, ".TB_PREF."workorders WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id ORDER BY ".TB_PREF."workorders.id DESC"; + $sql = "SELECT wo.id, concat(wo.id, '-', item.description) as IName + FROM ".TB_PREF."stock_master item," + .TB_PREF."workorders wo + WHERE item.stock_id=wo.stock_id + ORDER BY wo.id DESC"; return combo_input($name, '', $sql, 'id', 'IName',array('order'=>false)); case 'LOCATIONS': diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 3f7e6598..74456aad 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -77,35 +77,30 @@ function get_customer_details($customer_id, $to=null, $all=true) $value = "IFNULL($sign*(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc),0)"; $due = "IF (trans.type=10, trans.due_date, trans.tran_date)"; - $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms, - ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description, - - Sum(IFNULL($value,0)) AS Balance, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2 - - FROM ".TB_PREF."debtors_master - LEFT JOIN ".TB_PREF."debtor_trans trans ON - trans.tran_date <= '$todate' AND ".TB_PREF."debtors_master.debtor_no = trans.debtor_no AND trans.type <> 13 -, - ".TB_PREF."payment_terms, - ".TB_PREF."credit_status - - WHERE - ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator - AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id - AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id)." "; + $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, debtor.credit_limit, + credit_status.dissallow_invoices, credit_status.reason_description, + Sum(IFNULL($value,0)) AS Balance, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2 + FROM ".TB_PREF."debtors_master debtor + LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> 13," + .TB_PREF."payment_terms terms," + .TB_PREF."credit_status credit_status + WHERE + debtor.payment_terms = terms.terms_indicator + AND debtor.credit_status = credit_status.id + AND debtor.debtor_no = ".db_escape($customer_id); if (!$all) - $sql .= "AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA." "; - $sql .= "GROUP BY - ".TB_PREF."debtors_master.name, - ".TB_PREF."payment_terms.terms, - ".TB_PREF."payment_terms.days_before_due, - ".TB_PREF."payment_terms.day_in_following_month, - ".TB_PREF."debtors_master.credit_limit, - ".TB_PREF."credit_status.dissallow_invoices, - ".TB_PREF."credit_status.reason_description"; + $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA; + $sql .= " GROUP BY + debtor.name, + terms.terms, + terms.days_before_due, + terms.day_in_following_month, + debtor.credit_limit, + credit_status.dissallow_invoices, + credit_status.reason_description"; $result = db_query($sql,"The customer details could not be retrieved"); $customer_record = db_fetch($result); @@ -137,11 +132,11 @@ function get_customer_name($customer_id) function get_customer_habit($customer_id) { - $sql = "SELECT ".TB_PREF."debtors_master.pymt_discount, - ".TB_PREF."credit_status.dissallow_invoices - FROM ".TB_PREF."debtors_master, ".TB_PREF."credit_status - WHERE ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id - AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id); + $sql = "SELECT debtor.pymt_discount, credit_status.dissallow_invoices + FROM ".TB_PREF."debtors_master debtor," + .TB_PREF."credit_status credit_status + WHERE debtor.credit_status = credit_status.id + AND debtor.debtor_no = ".db_escape($customer_id); $result = db_query($sql, "could not query customers"); @@ -189,7 +184,8 @@ function get_customer_by_ref($reference) function get_customer_currency($customer_id=null, $branch_id=null) { - $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master cust + $sql = "SELECT curr_code + FROM ".TB_PREF."debtors_master cust LEFT JOIN ".TB_PREF."cust_branch branch ON branch.debtor_no=cust.debtor_no WHERE " .(isset($branch_id) ? "branch_code = ".db_escape($branch_id) : "cust.debtor_no = ".db_escape($customer_id)); diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 9a683f1e..b41107b8 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -221,24 +221,24 @@ function update_sales_order($order) function get_sales_order_header($order_no, $trans_type) { - $sql = "SELECT sorder.*, " - ."cust.name, " - ."cust.curr_code, " - ."cust.address, " - ."loc.location_name, " - ."cust.discount, " - ."stype.sales_type, " - ."stype.id AS sales_type_id, " - ."stype.tax_included, " - ."stype.factor, " - ."ship.shipper_name, " - ."tax_group.name AS tax_group_name , " - ."tax_group.id AS tax_group_id, " - ."cust.tax_id," - ."sorder.alloc," - ."IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid," - ."sorder.prep_amount>0 as prepaid" - ." FROM ".TB_PREF."sales_orders sorder + $sql = "SELECT sorder.*, + cust.name, + cust.curr_code, + cust.address, + loc.location_name, + cust.discount, + stype.sales_type, + stype.id AS sales_type_id, + stype.tax_included, + stype.factor, + ship.shipper_name, + tax_group.name AS tax_group_name, + tax_group.id AS tax_group_id, + cust.tax_id, + sorder.alloc, + IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid, + sorder.prep_amount>0 as prepaid + FROM ".TB_PREF."sales_orders sorder LEFT JOIN (SELECT trans_no_to, sum(amt) ord_allocs FROM ".TB_PREF."cust_allocations WHERE trans_type_to=".ST_SALESORDER." AND trans_no_to=".db_escape($order_no)." GROUP BY trans_no_to) allocs ON sorder.trans_type=".ST_SALESORDER." AND allocs.trans_no_to=sorder.order_no @@ -278,20 +278,19 @@ function get_sales_order_header($order_no, $trans_type) //---------------------------------------------------------------------------------------- function get_sales_order_details($order_no, $trans_type) { - $sql = "SELECT id, stk_code, unit_price, " - .TB_PREF."sales_order_details.description," - .TB_PREF."sales_order_details.quantity, - discount_percent, - qty_sent as qty_done, " - .TB_PREF."stock_master.units," - .TB_PREF."stock_master.mb_flag," - .TB_PREF."stock_master.material_cost + " - .TB_PREF."stock_master.labour_cost + " - .TB_PREF."stock_master.overhead_cost AS standard_cost - FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master - WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id - AND order_no =" . db_escape($order_no) - ." AND trans_type = " . db_escape($trans_type) . " ORDER BY id"; + $sql = "SELECT id, stk_code, unit_price, + line.description, + line.quantity, + discount_percent, + qty_sent as qty_done, + item.units, + item.mb_flag, + item.material_cost + item.labour_cost + item.overhead_cost AS standard_cost + FROM ".TB_PREF."sales_order_details line," + .TB_PREF."stock_master item + WHERE line.stk_code = line.stock_id + AND order_no =".db_escape($order_no) + ." AND trans_type = ".db_escape($trans_type) . " ORDER BY id"; return db_query($sql, "Retreive order Line Items"); } @@ -407,8 +406,8 @@ function get_customer_to_order($customer_id) { // Now check to ensure this account is not on hold */ $sql = "SELECT cust.name, - cust.address, " - .TB_PREF."credit_status.dissallow_invoices, + cust.address, + credit_status.dissallow_invoices, cust.sales_type AS salestype, cust.dimension_id, cust.dimension2_id, @@ -423,11 +422,11 @@ function get_customer_to_order($customer_id) { -1, 1) * (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount),0)) as cur_credit FROM ".TB_PREF."debtors_master cust LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.type!=".ST_CUSTDELIVERY." AND trans.debtor_no = cust.debtor_no," - .TB_PREF."credit_status, " + .TB_PREF."credit_status credit_status," .TB_PREF."sales_types stype WHERE cust.sales_type=stype.id - AND cust.credit_status=".TB_PREF."credit_status.id - AND cust.debtor_no = ".db_escape($customer_id) + AND cust.credit_status=credit_status.id + AND cust.debtor_no=".db_escape($customer_id) ." GROUP by cust.debtor_no"; $result =db_query($sql,"Customer Record Retreive"); @@ -437,19 +436,19 @@ function get_customer_to_order($customer_id) { function get_branch_to_order($customer_id, $branch_id) { // the branch was also selected from the customer selection so default the delivery details from the customer branches table cust_branch. The order process will ask for branch details later anyway - $sql = "SELECT ".TB_PREF."cust_branch.br_name, " - .TB_PREF."cust_branch.br_address, " - .TB_PREF."cust_branch.br_post_address, " - ." default_location, location_name, default_ship_via, " - .TB_PREF."tax_groups.name AS tax_group_name, " - .TB_PREF."tax_groups.id AS tax_group_id - FROM ".TB_PREF."cust_branch, " - .TB_PREF."tax_groups, " - .TB_PREF."locations - WHERE ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id - AND ".TB_PREF."locations.loc_code=default_location - AND ".TB_PREF."cust_branch.branch_code=".db_escape($branch_id)." - AND ".TB_PREF."cust_branch.debtor_no = ".db_escape($customer_id); + $sql = "SELECT branch.br_name, + branch.br_address, + branch.br_post_address, + default_location, location_name, default_ship_via, + tax_group.name AS tax_group_name, + tax_group.id AS tax_group_id + FROM ".TB_PREF."cust_branch branch," + .TB_PREF."tax_groups tax_group," + .TB_PREF."locations location + WHERE branch.tax_group_id = tax_group.id + AND location.loc_code=default_location + AND branch.branch_code=".db_escape($branch_id)." + AND branch.debtor_no=".db_escape($customer_id); return db_query($sql,"Customer Branch Record Retreive"); } diff --git a/sales/includes/sales_db.inc b/sales/includes/sales_db.inc index d5c0e487..949ee79e 100644 --- a/sales/includes/sales_db.inc +++ b/sales/includes/sales_db.inc @@ -200,14 +200,15 @@ function update_parent_line($doc_type, $line_id, $qty_dispatched, $auto=false) // function get_location(&$cart) { - $sql = "SELECT ".TB_PREF."locations.* FROM ".TB_PREF."stock_moves," - .TB_PREF."locations". + $sql = "SELECT location.* + FROM ".TB_PREF."stock_moves move," + .TB_PREF."locations location". " WHERE type=".db_escape($cart->trans_type). " AND trans_no=".key($cart->trans_no). " AND qty!=0 ". - " AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code"; - $result = db_query($sql, 'Retreiving inventory location'); + " AND location.loc_code=move.loc_code"; + $result = db_query($sql, 'Retreiving inventory location'); if (db_num_rows($result)) { return db_fetch($result); diff --git a/taxes/db/item_tax_types_db.inc b/taxes/db/item_tax_types_db.inc index 61959f81..809f9f9e 100644 --- a/taxes/db/item_tax_types_db.inc +++ b/taxes/db/item_tax_types_db.inc @@ -62,9 +62,11 @@ function get_item_tax_type($id) function get_item_tax_type_for_item($stock_id) { - $sql = "SELECT ".TB_PREF."item_tax_types.* FROM ".TB_PREF."item_tax_types,".TB_PREF."stock_master WHERE - ".TB_PREF."stock_master.stock_id=".db_escape($stock_id)." - AND ".TB_PREF."item_tax_types.id=".TB_PREF."stock_master.tax_type_id"; + $sql = "SELECT item_tax_type.* + FROM ".TB_PREF."item_tax_types item_tax_type," + .TB_PREF."stock_master item + WHERE item.stock_id=".db_escape($stock_id)." + AND item_tax_type.id=item.tax_type_id"; $result = db_query($sql, "could not get item tax type"); diff --git a/taxes/db/tax_types_db.inc b/taxes/db/tax_types_db.inc index 90d431fe..ce73935f 100644 --- a/taxes/db/tax_types_db.inc +++ b/taxes/db/tax_types_db.inc @@ -31,15 +31,16 @@ function update_tax_type($type_id, $name, $sales_gl_code, $purchasing_gl_code, $ function get_all_tax_types($all=false) { - $sql = "SELECT ".TB_PREF."tax_types.*, - Chart1.account_name AS SalesAccountName, - Chart2.account_name AS PurchasingAccountName - FROM ".TB_PREF."tax_types, ".TB_PREF."chart_master AS Chart1, - ".TB_PREF."chart_master AS Chart2 - WHERE ".TB_PREF."tax_types.sales_gl_code = Chart1.account_code - AND ".TB_PREF."tax_types.purchasing_gl_code = Chart2.account_code"; - - if (!$all) $sql .= " AND !".TB_PREF."tax_types.inactive"; + $sql = "SELECT tax_type.*, + Chart1.account_name AS SalesAccountName, + Chart2.account_name AS PurchasingAccountName + FROM ".TB_PREF."tax_types tax_type," + .TB_PREF."chart_master AS Chart1," + .TB_PREF."chart_master AS Chart2 + WHERE tax_type.sales_gl_code = Chart1.account_code + AND tax_type.purchasing_gl_code = Chart2.account_code"; + + if (!$all) $sql .= " AND !tax_type.inactive"; return db_query($sql, "could not get all tax types"); } @@ -52,13 +53,14 @@ function get_all_tax_types_simple() function get_tax_type($type_id) { - $sql = "SELECT ".TB_PREF."tax_types.*, - Chart1.account_name AS SalesAccountName, - Chart2.account_name AS PurchasingAccountName - FROM ".TB_PREF."tax_types, ".TB_PREF."chart_master AS Chart1, - ".TB_PREF."chart_master AS Chart2 - WHERE ".TB_PREF."tax_types.sales_gl_code = Chart1.account_code - AND ".TB_PREF."tax_types.purchasing_gl_code = Chart2.account_code AND id=".db_escape($type_id); + $sql = "SELECT tax_type.*, + Chart1.account_name AS SalesAccountName, + Chart2.account_name AS PurchasingAccountName + FROM ".TB_PREF."tax_types tax_type," + .TB_PREF."chart_master AS Chart1," + .TB_PREF."chart_master AS Chart2 + WHERE tax_type.sales_gl_code = Chart1.account_code + AND tax_type.purchasing_gl_code = Chart2.account_code AND id=".db_escape($type_id); $result = db_query($sql, "could not get tax type"); return db_fetch($result); @@ -101,9 +103,9 @@ function is_tax_gl_unique($gl_code, $gl_code2=-1, $selected_id=-1) { $purch_code = $gl_code2== -1 ? $gl_code : $gl_code2; $sql = "SELECT count(*) FROM " - .TB_PREF."tax_types + .TB_PREF."tax_types WHERE (sales_gl_code=" .db_escape($gl_code) - ." OR purchasing_gl_code=" .db_escape($purch_code). ")"; + ." OR purchasing_gl_code=" .db_escape($purch_code). ")"; if ($selected_id != -1) $sql .= " AND id!=".db_escape($selected_id); -- 2.30.2