From f44132511cc91b83f20f91af0625d4b112b4c26e Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 5 Nov 2015 14:04:40 +0100 Subject: [PATCH] Global fixes in SQL queries aimed to make them more readible and easier for maintence. --- dimensions/includes/dimensions_db.inc | 22 ++- gl/includes/db/gl_db_account_types.inc | 16 +- gl/includes/db/gl_db_accounts.inc | 116 +++++++++----- gl/includes/db/gl_db_bank_accounts.inc | 16 +- gl/includes/db/gl_db_bank_trans.inc | 41 +++-- gl/includes/db/gl_db_banking.inc | 6 +- gl/includes/db/gl_db_rates.inc | 28 ++-- gl/includes/db/gl_db_trans.inc | 65 ++++---- includes/db/inventory_db.inc | 49 +++--- includes/db/manufacturing_db.inc | 147 ++++++++++-------- inventory/includes/db/items_prices_db.inc | 29 ++-- inventory/includes/db/items_purchases_db.inc | 18 +-- inventory/includes/db/items_trans_db.inc | 17 +- inventory/includes/inventory_db.inc | 13 +- .../includes/db/work_order_issues_db.inc | 43 ++--- .../db/work_order_produce_items_db.inc | 13 +- .../db/work_order_requirements_db.inc | 31 ++-- .../includes/db/work_orders_quick_db.inc | 8 +- purchasing/includes/db/grn_db.inc | 33 ++-- purchasing/includes/db/invoice_db.inc | 48 +++--- purchasing/includes/db/po_db.inc | 23 ++- purchasing/includes/db/supp_trans_db.inc | 31 ++-- reporting/rep102.php | 39 ++--- reporting/rep103.php | 66 ++++---- reporting/rep104.php | 28 ++-- reporting/rep105.php | 50 +++--- reporting/rep108.php | 24 ++- reporting/rep112.php | 26 ++-- reporting/rep201.php | 35 ++--- reporting/rep202.php | 34 ++-- reporting/rep203.php | 23 ++- reporting/rep204.php | 32 ++-- reporting/rep205.php | 19 +-- reporting/rep209.php | 25 +-- reporting/rep210.php | 22 +-- reporting/rep301.php | 61 ++++---- reporting/rep302.php | 39 +++-- reporting/rep303.php | 45 +++--- reporting/rep305.php | 68 ++++---- reporting/rep401.php | 28 ++-- reporting/rep602.php | 12 +- sales/includes/db/branches_db.inc | 28 ++-- sales/includes/db/credit_status_db.inc | 18 +-- sales/includes/db/cust_trans_db.inc | 13 +- sales/includes/db/cust_trans_details_db.inc | 14 +- sales/includes/db/recurrent_invoices_db.inc | 20 ++- 46 files changed, 824 insertions(+), 758 deletions(-) diff --git a/dimensions/includes/dimensions_db.inc b/dimensions/includes/dimensions_db.inc index 15700073..13c91fd4 100644 --- a/dimensions/includes/dimensions_db.inc +++ b/dimensions/includes/dimensions_db.inc @@ -157,11 +157,14 @@ function get_dimension_balance_all($id, $from, $to) { $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT account, ".TB_PREF."chart_master.account_name, sum(amount) AS amt FROM - ".TB_PREF."gl_trans,".TB_PREF."chart_master WHERE - ".TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND - (dimension_id = ".db_escape($id)." OR dimension2_id = ".db_escape($id).") AND - tran_date >= '$from' AND tran_date <= '$to' GROUP BY account"; + $sql = "SELECT account, coa.account_name, sum(amount) AS amt + FROM " + .TB_PREF."gl_trans trans," + .TB_PREF."chart_master coa + WHERE + trans.account = coa.account_code + AND (dimension_id = ".db_escape($id)." OR dimension2_id = ".db_escape($id).") + AND tran_date >= '$from' AND tran_date <= '$to' GROUP BY account"; return db_query($sql, "Transactions could not be calculated"); } @@ -170,9 +173,12 @@ function get_dimension_balance_all($id, $from, $to) function get_dimension_balance($id, $from, $to) { $id = db_escape($id); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE tran_date >= '" . - date2sql($from) . "' AND - tran_date <= '" . date2sql($to) . "' AND (dimension_id = $id OR dimension2_id = $id)"; + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."gl_trans + WHERE + tran_date >= '" . date2sql($from) . "' + AND tran_date <= '" . date2sql($to) . "' + AND (dimension_id = $id OR dimension2_id = $id)"; $res = db_query($sql, "Sum of transactions could not be calculated"); $row = db_fetch_row($res); diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc index 76d41ede..4d6d58e8 100644 --- a/gl/includes/db/gl_db_account_types.inc +++ b/gl/includes/db/gl_db_account_types.inc @@ -22,7 +22,9 @@ function update_account_type($id, $name, $class_id, $parent, $old_id) begin_transaction(); if ($old_id !== $id) { - $sql = "SELECT id FROM ".TB_PREF."chart_types WHERE parent = ".db_escape($old_id); + $sql = "SELECT id + FROM ".TB_PREF."chart_types + WHERE parent = ".db_escape($old_id); $result = db_query($sql, "could not get account type"); @@ -32,19 +34,23 @@ function update_account_type($id, $name, $class_id, $parent, $old_id) ." WHERE id = '".$myrow['id']."'"; db_query($sql, "could not update account type"); } - $sql = "SELECT account_code FROM ".TB_PREF."chart_master WHERE account_type = ".db_escape($old_id); + $sql = "SELECT account_code + FROM ".TB_PREF."chart_master + WHERE account_type = ".db_escape($old_id); $result = db_query($sql, "could not get account"); while ($myrow = db_fetch($result)) { - $sql = "UPDATE ".TB_PREF."chart_master SET account_type=".db_escape($id) + $sql = "UPDATE ".TB_PREF."chart_master + SET account_type=".db_escape($id) ." WHERE account_code = '".$myrow['account_code']."'"; db_query($sql, "could not update account"); } } - $sql = "UPDATE ".TB_PREF."chart_types SET id=".db_escape($id) .", name=".db_escape($name).", - class_id=".db_escape($class_id).", parent=".db_escape($parent) + $sql = "UPDATE ".TB_PREF."chart_types + SET id=".db_escape($id) .", name=".db_escape($name).", + class_id=".db_escape($class_id).", parent=".db_escape($parent) ." WHERE id = ".db_escape($old_id); $ret = db_query($sql, "could not update account type"); diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index 25bc84fc..de487be2 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -36,13 +36,15 @@ function delete_gl_account($code) function get_gl_accounts($from=null, $to=null, $type=null) { - $sql = "SELECT ".TB_PREF."chart_master.*,".TB_PREF."chart_types.name AS AccountTypeName - FROM ".TB_PREF."chart_master,".TB_PREF."chart_types - WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id"; + $sql = "SELECT coa.*, act_type.name AS AccountTypeName + FROM " + .TB_PREF."chart_master coa," + .TB_PREF."chart_types act_type + WHERE coa.account_type=act_type.id"; if ($from != null) - $sql .= " AND ".TB_PREF."chart_master.account_code >= ".db_escape($from); + $sql .= " AND coa.account_code >= ".db_escape($from); if ($to != null) - $sql .= " AND ".TB_PREF."chart_master.account_code <= ".db_escape($to); + $sql .= " AND coa.account_code <= ".db_escape($to); if ($type != null) $sql .= " AND account_type=".db_escape($type); $sql .= " ORDER BY account_code"; @@ -60,11 +62,14 @@ function get_gl_account($code) function is_account_balancesheet($code) { - $sql = "SELECT ".TB_PREF."chart_class.ctype FROM ".TB_PREF."chart_class, " - .TB_PREF."chart_types, ".TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND - ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid - AND ".TB_PREF."chart_master.account_code=".db_escape($code); + $sql = "SELECT act_class.ctype + FROM " + .TB_PREF."chart_class act_class, " + .TB_PREF."chart_types act_type, " + .TB_PREF."chart_master coa + WHERE coa.account_type=act_type.id + AND act_type.class_id=act_class.cid + AND coa.account_code=".db_escape($code); $result = db_query($sql,"could not retreive the account class for $code"); $row = db_fetch_row($result); @@ -88,7 +93,9 @@ function get_gl_account_name($code) function gl_account_in_company_defaults($acc) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sys_prefs WHERE (name='debtors_act' + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."sys_prefs + WHERE (name='debtors_act' OR name='pyt_discount_act' OR name='creditors_act' OR name='bank_charge_act' @@ -115,11 +122,12 @@ function gl_account_in_stock_category($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_category WHERE - dflt_inventory_act=$acc - OR dflt_cogs_act=$acc - OR dflt_adjustment_act=$acc - OR dflt_sales_act=$acc"; + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."stock_category + WHERE dflt_inventory_act=$acc + OR dflt_cogs_act=$acc + OR dflt_adjustment_act=$acc + OR dflt_sales_act=$acc"; $result = db_query($sql,"Couldn't test for existing stock category GL codes"); $myrow = db_fetch_row($result); @@ -130,11 +138,12 @@ function gl_account_in_stock_master($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE - inventory_account=$acc - OR cogs_account=$acc - OR adjustment_account=$acc - OR sales_account=$acc"; + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."stock_master + WHERE inventory_account=$acc + OR cogs_account=$acc + OR adjustment_account=$acc + OR sales_account=$acc"; $result = db_query($sql,"Couldn't test for existing stock GL codes"); $myrow = db_fetch_row($result); @@ -145,7 +154,10 @@ function gl_account_in_tax_types($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_types WHERE sales_gl_code=$acc OR purchasing_gl_code=$acc"; + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."tax_types + WHERE sales_gl_code=$acc + OR purchasing_gl_code=$acc"; $result = db_query($sql,"Couldn't test for existing tax GL codes"); $myrow = db_fetch_row($result); @@ -156,11 +168,12 @@ function gl_account_in_cust_branch($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE - sales_account=$acc - OR sales_discount_account=$acc - OR receivables_account=$acc - OR payment_discount_account=$acc"; + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."cust_branch + WHERE sales_account=$acc + OR sales_discount_account=$acc + OR receivables_account=$acc + OR payment_discount_account=$acc"; $result = db_query($sql,"Couldn't test for existing cust branch GL codes"); $myrow = db_fetch_row($result); @@ -171,10 +184,11 @@ function gl_account_in_suppliers($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE - purchase_account=$acc - OR payment_discount_account=$acc - OR payable_account=$acc"; + $sql= "SELECT COUNT(*) + FROM ".TB_PREF."suppliers + WHERE purchase_account=$acc + OR payment_discount_account=$acc + OR payable_account=$acc"; $result = db_query($sql,"Couldn't test for existing suppliers GL codes"); $myrow = db_fetch_row($result); @@ -185,8 +199,10 @@ function gl_account_in_quick_entry_lines($acc) { $acc = db_escape($acc); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."quick_entry_lines WHERE - dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'"; + $sql= + "SELECT COUNT(*) + FROM ".TB_PREF."quick_entry_lines + WHERE dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'"; $result = db_query($sql,"Couldn't test for existing Quick Entry Line GL codes"); $myrow = db_fetch_row($result); @@ -198,8 +214,13 @@ function gl_account_in_quick_entry_lines($acc) // function is_subledger_account($account) { - $sql = "SELECT 1 FROM ".TB_PREF."cust_branch WHERE receivables_account=".db_escape($account) - ." UNION SELECT -1 FROM ".TB_PREF."suppliers WHERE payable_account=".db_escape($account); + $sql = "SELECT 1 + FROM ".TB_PREF."cust_branch + WHERE receivables_account=".db_escape($account) + ." UNION + SELECT -1 + FROM ".TB_PREF."suppliers + WHERE payable_account=".db_escape($account); $result = db_query($sql,"Couldn't test AR/AP account"); $myrow = db_fetch_row($result); @@ -209,10 +230,14 @@ function is_subledger_account($account) function get_subaccount_data($code_id, $person_id) { $sql = "SELECT debtor_ref as name, branch_code as id - FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no - WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id) - ." UNION SELECT supp_ref as name, '' as id FROM ".TB_PREF."suppliers supp - WHERE supplier_id=".db_escape($person_id)." AND payable_account=".db_escape($code_id); + FROM ".TB_PREF."cust_branch branch + LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no + WHERE branch.receivables_account=".db_escape($code_id) + ." AND d.debtor_no=".db_escape($person_id) + ." UNION SELECT supp_ref as name, '' as id + FROM ".TB_PREF."suppliers supp + WHERE supplier_id=".db_escape($person_id)." + AND payable_account=".db_escape($code_id); $result = db_query($sql, 'cannot retrieve counterparty name'); return db_fetch($result); @@ -220,10 +245,15 @@ function get_subaccount_data($code_id, $person_id) function get_subaccount_name($code_id, $person_id) { - $sql = "SELECT debtor_ref as ref FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no - WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id) - ." UNION SELECT supp_ref as ref FROM ".TB_PREF."suppliers supp - WHERE payable_account=".db_escape($code_id)." AND supplier_id=".db_escape($person_id); + $sql = "SELECT debtor_ref as ref + FROM ".TB_PREF."cust_branch branch + LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no + WHERE branch.receivables_account=".db_escape($code_id) + ." AND d.debtor_no=".db_escape($person_id) + ." UNION SELECT supp_ref as ref + FROM ".TB_PREF."suppliers supp + WHERE payable_account=".db_escape($code_id)." + AND supplier_id=".db_escape($person_id); $result = db_query($sql, 'cannot retrieve counterparty name'); $row = db_fetch($result); diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index ef6793e2..8716c779 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -213,13 +213,10 @@ function get_quick_entry($selected_id) function get_quick_entry_lines($qid) { - $sql = "SELECT ".TB_PREF."quick_entry_lines.*, ".TB_PREF."chart_master.account_name, - ".TB_PREF."tax_types.name as tax_name - FROM ".TB_PREF."quick_entry_lines - LEFT JOIN ".TB_PREF."chart_master ON - ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."chart_master.account_code - LEFT JOIN ".TB_PREF."tax_types ON - ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id + $sql = "SELECT line.*, coa.account_name, taxtype.name as tax_name + FROM ".TB_PREF."quick_entry_lines line + LEFT JOIN ".TB_PREF."chart_master coa ON line.dest_id = coa.account_code + LEFT JOIN ".TB_PREF."tax_types taxtype ON line.dest_id = taxtype.id WHERE qid=".db_escape($qid)." ORDER by id"; @@ -283,10 +280,9 @@ function get_sql_for_bank_account_reconcile($bank_account, $date) $sql = "SELECT type, trans_no, ref, trans_date, amount, person_id, person_type_id, reconciled, id FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " + WHERE bank_act = ".db_escape($bank_account) . " AND (reconciled IS NULL OR reconciled='". date2sql($date) ."') - ORDER BY trans_date,".TB_PREF."bank_trans.id"; -// or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id"; + ORDER BY trans_date, id"; return $sql; } diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index c3725611..e0dbdf28 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -54,7 +54,9 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, function exists_bank_trans($type, $type_no) { - $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=".db_escape($type) + $sql = "SELECT trans_no + FROM ".TB_PREF."bank_trans + WHERE type=".db_escape($type) ." AND trans_no=".db_escape($type_no); $result = db_query($sql, "Cannot retreive a bank transaction"); @@ -96,13 +98,14 @@ function get_bank_trans_for_bank_account($bank_account, $from, $to) { $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT t.* FROM " - .TB_PREF."bank_trans t LEFT JOIN ".TB_PREF."voided v ON t.type=v.type AND t.trans_no=v.id + $sql = "SELECT t.* + FROM ".TB_PREF."bank_trans t + LEFT JOIN ".TB_PREF."voided v ON t.type=v.type AND t.trans_no=v.id WHERE t.bank_act = ".db_escape($bank_account) . " - AND ISNULL(v.date_) - AND trans_date >= '$from' - AND trans_date <= '$to' - AND amount != 0 + AND ISNULL(v.date_) + AND trans_date >= '$from' + AND trans_date <= '$to' + AND amount != 0 ORDER BY trans_date, t.id"; return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved"); @@ -113,9 +116,10 @@ function get_bank_trans_for_bank_account($bank_account, $from, $to) function get_balance_before_for_bank_account($bank_account, $from) { $from = date2sql($from); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act=" - .db_escape($bank_account) . " - AND trans_date < '$from'"; + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."bank_trans + WHERE bank_act=".db_escape($bank_account) . " + AND trans_date < '$from'"; $before_qty = db_query($sql, "The starting balance on hand could not be calculated"); $bfw_row = db_fetch_row($before_qty); return $bfw_row[0]; @@ -124,9 +128,11 @@ function get_balance_before_for_bank_account($bank_account, $from) function get_gl_trans_value($account, $type, $trans_no) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account=" - .db_escape($account)." AND type=".db_escape($type) - ." AND type_no=".db_escape($trans_no); + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."gl_trans + WHERE account=".db_escape($account) + ." AND type=".db_escape($type) + ." AND type_no=".db_escape($trans_no); $result = db_query($sql, "query for gl trans value"); @@ -142,8 +148,9 @@ function void_bank_trans($type, $type_no, $nested=false) if (!$nested) begin_transaction(); - $sql = "UPDATE ".TB_PREF."bank_trans SET amount=0 - WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); + $sql = "UPDATE ".TB_PREF."bank_trans + SET amount=0 + WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); $result = db_query($sql, "could not void bank transactions for type=$type and trans_no=$type_no"); @@ -183,7 +190,9 @@ function check_bank_account_history($delta_amount, $bank_account, $date=null, $u $balance += $delta_amount; - $sql = "SELECT sum(amount) as amount, trans_date FROM ".TB_PREF."bank_trans WHERE bank_act=".db_escape($bank_account); + $sql = "SELECT sum(amount) as amount, trans_date + FROM ".TB_PREF."bank_trans + WHERE bank_act=".db_escape($bank_account); if ($date) { $date = date2sql($date); diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 095dadcd..91a1e97b 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -21,10 +21,10 @@ function add_exchange_variation($trans_no, $date_, $acc_id, $account, // We have to calculate all the currency accounts belonging to the GL account // upto $date_ and calculate with the exchange rates. And then compare with the GL account balance. // 2010-02-23 Joe Hunt with help of Ary Wibowo - $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code + $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code FROM ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts ba WHERE ba.id = bt.bank_act AND ba.account_code = ".db_escape($account)." AND bt.trans_date<='".date2sql($date_)."' - GROUP BY ba.bank_curr_code"; + GROUP BY ba.bank_curr_code"; $result = db_query($sql, "Transactions for bank account $acc_id could not be calculated"); while ($row = db_fetch($result)) { @@ -92,7 +92,7 @@ function get_cust_account_curr_balances($date) { $to = date2sql($date); - $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT." OR t.type = ".ST_JOURNAL.", + $sql = "SELECT SUM(IF(t.type IN(". implode(',', array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, ST_JOURNAL))."), -(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount))) AS amount, dt.debtor_no, dt.name, dt.curr_code, b.receivables_account diff --git a/gl/includes/db/gl_db_rates.inc b/gl/includes/db/gl_db_rates.inc index 0a1abf62..0980cb36 100644 --- a/gl/includes/db/gl_db_rates.inc +++ b/gl/includes/db/gl_db_rates.inc @@ -14,7 +14,7 @@ include_once($path_to_root . "/includes/remote_url.inc"); function get_exchange_rate($rate_id) { $sql = "SELECT * FROM ".TB_PREF."exchange_rates WHERE id=".db_escape($rate_id); - $result = db_query($sql, "could not get exchange rate for $rate_id"); + $result = db_query($sql, "could not get exchange rate for $rate_id"); return db_fetch($result); } @@ -23,9 +23,11 @@ function get_exchange_rate($rate_id) function get_date_exchange_rate($curr_code, $date_) { $date = date2sql($date_); - $sql = "SELECT rate_buy FROM ".TB_PREF."exchange_rates WHERE curr_code=".db_escape($curr_code) - ." AND date_='$date'"; - $result = db_query($sql, "could not get exchange rate for $curr_code - $date_"); + $sql = "SELECT rate_buy + FROM ".TB_PREF."exchange_rates + WHERE curr_code=".db_escape($curr_code) + ." AND date_='$date'"; + $result = db_query($sql, "could not get exchange rate for $curr_code - $date_"); if(db_num_rows($result) == 0) return 0; @@ -40,7 +42,9 @@ function get_last_exchange_rate($curr_code, $date_) { $date = date2sql($date_); - $sql = "SELECT rate_buy, max(date_) as date_ FROM ".TB_PREF."exchange_rates WHERE curr_code = ".db_escape($curr_code)." + $sql = "SELECT rate_buy, max(date_) as date_ + FROM ".TB_PREF."exchange_rates + WHERE curr_code = ".db_escape($curr_code)." AND date_ <= '$date' GROUP BY rate_buy ORDER BY date_ Desc LIMIT 1"; $result = db_query($sql, "could not query exchange rates"); @@ -58,12 +62,12 @@ function update_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) { if (is_company_currency($curr_code)) display_db_error("Exchange rates cannot be set for company currency", "", true); - + $date = date2sql($date_); - + $sql = "UPDATE ".TB_PREF."exchange_rates SET rate_buy=$buy_rate, rate_sell=".db_escape($sell_rate) ." WHERE curr_code=".db_escape($curr_code)." AND date_='$date'"; - + db_query($sql, "could not add exchange rate for $curr_code"); } @@ -75,7 +79,7 @@ function add_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) display_db_error("Exchange rates cannot be set for company currency", "", true); $date = date2sql($date_); - + $sql = "INSERT INTO ".TB_PREF."exchange_rates (curr_code, date_, rate_buy, rate_sell) VALUES (".db_escape($curr_code).", '$date', ".db_escape($buy_rate) .", ".db_escape($sell_rate).")"; @@ -241,9 +245,9 @@ function get_extern_rate($curr_b, $provider = 'ECB', $date) function get_sql_for_exchange_rates($curr) { - $sql = "SELECT date_, rate_buy, id FROM " - .TB_PREF."exchange_rates " + $sql = "SELECT date_, rate_buy, id + FROM ".TB_PREF."exchange_rates " ."WHERE curr_code=".db_escape($curr)." ORDER BY date_ DESC"; - return $sql; + return $sql; } diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 26ad6b2a..34b904f4 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -234,9 +234,9 @@ function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $d if ($to_date != "") $sql .= " AND tran_date < '$to'"; if ($dimension != 0) - $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension)); + $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); if ($dimension2 != 0) - $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); + $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); $result = db_query($sql, "The starting balance for account $account could not be calculated"); @@ -258,9 +258,9 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim if ($to_date != "") $sql .= " AND tran_date <= '$to'"; if ($dimension != 0) - $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension)); + $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); if ($dimension2 != 0) - $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); + $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); $result = db_query($sql, "Transactions for account $account could not be calculated"); @@ -271,30 +271,24 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim //---------------------------------------------------------------------------------------------------- function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) { - $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, - SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance - FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master," - .TB_PREF."chart_types, ".TB_PREF."chart_class - WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND " - .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id - AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; - - if ($account != null) - $sql .= " account=".db_escape($account)." AND"; - if ($dimension != 0) - $sql .= " dimension_id = ".($dimension<0?0:db_escape($dimension))." AND"; - if ($dimension2 != 0) - $sql .= " dimension2_id = ".($dimension2<0?0:db_escape($dimension2))." AND"; $from_date = date2sql($from); - if ($from_incl) - $sql .= " tran_date >= '$from_date' AND"; - else - $sql .= " tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date') AND"; $to_date = date2sql($to); - if ($to_incl) - $sql .= " tran_date <= '$to_date' "; - else - $sql .= " tran_date < '$to_date' "; + + $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, + SUM(IF(amount < 0, -amount, 0)) as credit, + SUM(amount) as balance + FROM ".TB_PREF."gl_trans trans," + .TB_PREF."chart_master coa," + .TB_PREF."chart_types act_type, " + .TB_PREF."chart_class act_class + WHERE trans.account=coa.account_code + AND coa.account_type=act_type.id + AND act_type.class_id=act_class.cid" + ." AND ".($from_incl ? "tran_date >= '$from_date'" : "tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date')") + ." AND ".($to_incl ? "tran_date <= '$to_date'" : "tran_date < '$to_date'") + .($account == null ? '' : " AND account=".db_escape($account)) + .($dimension == 0 ? '' : " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension))) + .($dimension2 == 0 ? '' : " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2))); $result = db_query($sql,"No general ledger accounts were returned"); @@ -309,7 +303,8 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $from = date2sql($from_date); $to = date2sql($to_date); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account); if ($from_date != "") $sql .= " AND tran_date >= '$from' "; @@ -440,15 +435,17 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included function get_trans_tax_details($trans_type, $trans_no) { - $sql = "SELECT ".TB_PREF."trans_tax_details.*, " - .TB_PREF."tax_types.name AS tax_type_name, " - .TB_PREF."trans_tax_details.rate AS effective_rate, " - .TB_PREF."tax_types.rate AS rate - FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types - WHERE trans_type = ".db_escape($trans_type)." + $sql = "SELECT tax_details.*, + tax_type.name AS tax_type_name, + tax_details.rate AS effective_rate, + tax_type.rate AS rate + FROM ".TB_PREF."trans_tax_details tax_details, + ".TB_PREF."tax_types tax_type + WHERE + trans_type = ".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no)." AND (net_amount != 0 OR amount != 0) - AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id"; + AND tax_type.id = tax_details.tax_type_id"; return db_query($sql, "The transaction tax details could not be retrieved"); } diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index 87f045f8..3909e7c7 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -143,10 +143,10 @@ function last_negative_stock_begin_date($stock_id, $to) function get_already_delivered($stock_id, $location, $trans_no) { - $sql = "SELECT ".TB_PREF."stock_moves.qty + $sql = "SELECT qty FROM ".TB_PREF."stock_moves - WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)." - AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)." + WHERE stock_id = ".db_escape($stock_id)." + AND loc_code = ".db_escape($location)." AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no); $result = db_query($sql, "Could not get stock moves"); $row = db_fetch_row($result); @@ -216,7 +216,8 @@ function get_deliveries_from_trans($stock_id, $from) $cost = db_fetch_row($result); // Adjusting QOH valuation - $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves + $sql = "SELECT SUM(qty) + FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id)." AND trans_id<'$from' GROUP BY stock_id"; $result = db_query($sql, "The deliveries could not be updated"); @@ -231,7 +232,8 @@ function get_deliveries_from_trans($stock_id, $from) function get_purchases_from_trans($stock_id, $from) { // Calculate All inward stock moves i.e. qty > 0 - $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves + $sql = "SELECT SUM(qty), SUM(qty*standard_cost) + FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND trans_id>'$from' GROUP BY stock_id"; $result = db_query($sql, "Could not get get_purchases_from_trans"); @@ -386,15 +388,16 @@ function update_stock_move($type, $trans_no, $stock_id, $cost) function get_stock_moves($type, $type_no) { - $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, " - .TB_PREF."stock_master.units,".TB_PREF."locations.location_name," - .TB_PREF."stock_master.material_cost + " - .TB_PREF."stock_master.labour_cost + " - .TB_PREF."stock_master.overhead_cost AS FixedStandardCost - FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master - WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id - AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code - AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id"; + $sql = "SELECT move.*, item.description, item.units, stock.location_name, + item.material_cost + item.labour_cost + item.overhead_cost AS FixedStandardCost + FROM ".TB_PREF."stock_moves move," + .TB_PREF."locations stock," + .TB_PREF."stock_master item + WHERE move.stock_id = item.stock_id + AND stock.loc_code=move.loc_code + AND type=".db_escape($type) + ." AND trans_no=".db_escape($type_no) + ." ORDER BY trans_id"; return db_query($sql, "Could not get stock moves"); } @@ -403,11 +406,12 @@ function get_stock_moves($type, $type_no) function void_stock_move($type, $type_no) { - $sql = "SELECT move.*, supplier.supplier_id from ".TB_PREF."stock_moves move + $sql = "SELECT move.*, supplier.supplier_id + FROM ".TB_PREF."stock_moves move LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id - WHERE move.type=".db_escape($type)." AND move.trans_no=".db_escape($type_no); + WHERE move.type=".db_escape($type)." AND move.trans_no=".db_escape($type_no); $result = db_query($sql, "Could not void stock moves"); while ($row = db_fetch($result)) @@ -427,8 +431,9 @@ function void_stock_move($type, $type_no) $unit_cost, -$row["qty"], sql2date($row["tran_date"])); } } - $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE type=".db_escape($type) - ." AND trans_no=".db_escape($type_no); + $sql = "DELETE FROM ".TB_PREF."stock_moves + WHERE type=".db_escape($type) + ." AND trans_no=".db_escape($type_no); db_query($sql, "The stock movement cannot be delated"); } @@ -436,8 +441,8 @@ function void_stock_move($type, $type_no) function get_location_name($loc_code) { - $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code=" - .db_escape($loc_code); + $sql = "SELECT location_name FROM ".TB_PREF."locations + WHERE loc_code=".db_escape($loc_code); $result = db_query($sql, "could not retreive the location name for $loc_code"); @@ -452,8 +457,8 @@ function get_location_name($loc_code) function get_mb_flag($stock_id) { - $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = " - .db_escape($stock_id); + $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master + WHERE stock_id = ".db_escape($stock_id); $result = db_query($sql, "retreive mb_flag from item"); if (db_num_rows($result) == 0) diff --git a/includes/db/manufacturing_db.inc b/includes/db/manufacturing_db.inc index 6473ba4b..8adddc69 100644 --- a/includes/db/manufacturing_db.inc +++ b/includes/db/manufacturing_db.inc @@ -12,16 +12,14 @@ //---------------------------------------------------------------------------------------- function get_demand_qty($stock_id, $location) { - $sql = "SELECT SUM(".TB_PREF."sales_order_details.quantity - " - .TB_PREF."sales_order_details.qty_sent) AS QtyDemand - FROM ".TB_PREF."sales_order_details, - ".TB_PREF."sales_orders - WHERE ".TB_PREF."sales_order_details.order_no=" - .TB_PREF."sales_orders.order_no AND ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND - ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND "; + $sql = "SELECT SUM(line.quantity - line.qty_sent) AS QtyDemand + FROM ".TB_PREF."sales_order_details line, + ".TB_PREF."sales_orders sorder + WHERE line.order_no=sorder.order_no AND sorder.trans_type=".ST_SALESORDER + ." AND sorder.trans_type=line.trans_type" + ." AND line.stk_code = ".db_escape($stock_id); if ($location != "") - $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND "; - $sql .= TB_PREF."sales_order_details.stk_code = ".db_escape($stock_id); + $sql .= " AND sorder.from_stk_loc =".db_escape($location); $result = db_query($sql,"No transactions were returned"); $row = db_fetch($result); @@ -35,35 +33,44 @@ $qoh_stock = NULL; function load_stock_levels($location) { - global $qoh_stock; $date = date2sql(Today()); - $sql = "SELECT stock_id, SUM(qty) FROM ".TB_PREF."stock_moves WHERE tran_date <= '$date'"; - if ($location != '') $sql .= " AND loc_code = ".db_escape($location); + $qoh_stock = array(); + $sql = "SELECT stock_id, SUM(qty) + FROM ".TB_PREF."stock_moves + WHERE tran_date <= '$date'"; + if ($location != '') + $sql .= " AND loc_code = ".db_escape($location); $sql .= " GROUP BY stock_id"; + $result = db_query($sql, "QOH calculation failed"); - while ($row = db_fetch($result)) { + while ($row = db_fetch($result)) $qoh_stock[$row[0]] = $row[1]; - } + + return $qoh_stock; } // recursion fixed by Tom Moulton. Max 10 recursion levels. function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level=0) { global $bom_list, $qoh_stock; + $demand = 0.0; if ($level > 10) { display_warning("BOM Too many Manufacturing levels deep $level"); return $demand; } // Load all stock levels (stock moves) into $qoh_stock - if ($qoh_stock == NULL) { - $qoh_stock = array(); - load_stock_levels($location); - } - if (empty($qoh_stock[$stock_id])) $stock_qty = 0; - else $stock_qty = $qoh_stock[$stock_id]; - if ($qty <= $stock_qty) return $demand; + if ($qoh_stock == NULL) + $qoh_stock = load_stock_levels($location); + + if (empty($qoh_stock[$stock_id])) + $stock_qty = 0; + else + $stock_qty = $qoh_stock[$stock_id]; + + if ($qty <= $stock_qty) + return $demand; $bom = @$bom_list[$stock_id]; if ($bom == NULL) { $sql = "SELECT parent, component, quantity FROM " @@ -78,7 +85,7 @@ function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level } db_free_result($result); $bom_list[$stock_id] = $bom; - } + } $len = count($bom); $i = 0; while ($i < $len) { @@ -97,20 +104,19 @@ function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level function get_demand_asm_qty($stock_id, $location) { $demand_qty = 0.0; - $sql = "SELECT ".TB_PREF."sales_order_details.stk_code, SUM(".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent) - AS Demmand - FROM ".TB_PREF."sales_order_details, - ".TB_PREF."sales_orders, - ".TB_PREF."stock_master - WHERE ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND - ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND - ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND "; + $sql = "SELECT line.stk_code, SUM(line.quantity-line.qty_sent) AS Demmand + FROM ".TB_PREF."sales_order_details line, + ".TB_PREF."sales_orders sorder, + ".TB_PREF."stock_master item + WHERE sorder.order_no = line.order_no + AND sorder.trans_type=".ST_SALESORDER + ." AND sorder.trans_type=line.trans_type + AND line.quantity-line.qty_sent > 0 + AND item.stock_id=line.stk_code + AND item.mb_flag='M'"; if ($location != "") - $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND "; - $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND - ".TB_PREF."stock_master.stock_id=".TB_PREF."sales_order_details.stk_code AND - ".TB_PREF."stock_master.mb_flag='M' - GROUP BY ".TB_PREF."sales_order_details.stk_code"; + $sql .= " AND sorder.from_stk_loc =".db_escape($location); + $sql .= " GROUP BY line.stk_code"; $result = db_query($sql, "No transactions were returned"); while ($row = db_fetch_row($result)) { $demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location); @@ -120,14 +126,13 @@ function get_demand_asm_qty($stock_id, $location) function get_on_porder_qty($stock_id, $location) { - $sql = "SELECT SUM(".TB_PREF."purch_order_details.quantity_ordered - " - .TB_PREF."purch_order_details.quantity_received) AS qoo - FROM ".TB_PREF."purch_order_details INNER JOIN " - .TB_PREF."purch_orders ON ".TB_PREF."purch_order_details.order_no=".TB_PREF."purch_orders.order_no - WHERE ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id)." "; + $sql = "SELECT SUM(line.quantity_ordered - line.quantity_received) AS qoo + FROM ".TB_PREF."purch_order_details line + INNER JOIN ".TB_PREF."purch_orders po ON line.order_no=po.order_no + WHERE line.item_code=".db_escape($stock_id); if ($location != "") - $sql .= "AND ".TB_PREF."purch_orders.into_stock_location=".db_escape($location)." "; - $sql .= "AND ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id); + $sql .= " AND po.into_stock_location=".db_escape($location); + $sql .= " AND line.item_code=".db_escape($stock_id); $qoo_result = db_query($sql,"could not receive quantity on order for item"); if (db_num_rows($qoo_result) == 1) @@ -144,14 +149,14 @@ function get_on_porder_qty($stock_id, $location) function get_on_worder_qty($stock_id, $location) { - $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) * - (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo - FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders - ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id - WHERE ".TB_PREF."wo_requirements.stock_id=".db_escape($stock_id)." "; + $sql = "SELECT SUM((wo.units_reqd-wo.units_issued) * (req.units_req-req.units_issued)) AS qoo + FROM ".TB_PREF."wo_requirements req + INNER JOIN ".TB_PREF."workorders wo ON req.workorder_id=wo.id + WHERE req.stock_id=".db_escape($stock_id) + ." AND wo.released=1"; if ($location != "") - $sql .= "AND ".TB_PREF."wo_requirements.loc_code=".db_escape($location)." "; - $sql .= "AND ".TB_PREF."workorders.released=1"; + $sql .= " AND req.loc_code=".db_escape($location); + $qoo_result = db_query($sql,"could not receive quantity on order for item"); if (db_num_rows($qoo_result) == 1) { @@ -163,12 +168,14 @@ function get_on_worder_qty($stock_id, $location) $flag = get_mb_flag($stock_id); if ($flag == 'M') { - $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo - FROM ".TB_PREF."workorders - WHERE ".TB_PREF."workorders.stock_id=".db_escape($stock_id)." "; - if ($location != "") - $sql .= "AND ".TB_PREF."workorders.loc_code=".db_escape($location)." "; - $sql .= "AND ".TB_PREF."workorders.released=1"; + $sql = "SELECT SUM((units_reqd-units_issued)) AS qoo + FROM ".TB_PREF."workorders + WHERE stock_id=".db_escape($stock_id) + ." AND released=1"; + + if ($location != "") + $sql .= " AND loc_code=".db_escape($location); + $qoo_result = db_query($sql,"could not receive quantity on order for item"); if (db_num_rows($qoo_result) == 1) { @@ -212,14 +219,18 @@ function delete_bom($selected_id) function get_bom($item) { - $sql = "SELECT ".TB_PREF."bom.*, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription, - ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.mb_flag AS ResourceType, - ".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS standard_cost, units, - ".TB_PREF."bom.quantity * (".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+ ".TB_PREF."stock_master.overhead_cost) AS ComponentCost - FROM (".TB_PREF."workcentres, ".TB_PREF."locations, ".TB_PREF."bom) INNER JOIN ".TB_PREF."stock_master ON ".TB_PREF."bom.component = ".TB_PREF."stock_master.stock_id - WHERE ".TB_PREF."bom.parent = ".db_escape($item)." - AND ".TB_PREF."workcentres.id=".TB_PREF."bom.workcentre_added - AND ".TB_PREF."bom.loc_code = ".TB_PREF."locations.loc_code ORDER BY ".TB_PREF."bom.id"; + $sql = "SELECT bom.*, loc.location_name, + centre.name AS WorkCentreDescription, + item.description, item.mb_flag AS ResourceType, + item.material_cost+item.labour_cost+item.overhead_cost AS standard_cost, units, + bom.quantity * (item.material_cost+ item.labour_cost+ item.overhead_cost) AS ComponentCost + FROM ".TB_PREF."workcentres centre, + ".TB_PREF."locations loc, + ".TB_PREF."bom bom + INNER JOIN ".TB_PREF."stock_master item ON bom.component = item.stock_id + WHERE bom.parent = ".db_escape($item)." + AND centre.id=bom.workcentre_added + AND bom.loc_code = loc.loc_code ORDER BY bom.id"; return db_query($sql, "The bill of material could not be retrieved"); } @@ -228,10 +239,11 @@ function get_bom($item) function get_component_from_bom($selected_id) { - $sql = "SELECT ".TB_PREF."bom.*,".TB_PREF."stock_master.description FROM " - .TB_PREF."bom,".TB_PREF."stock_master + $sql = "SELECT bom.*, item.description + FROM ".TB_PREF."bom bom," + .TB_PREF."stock_master item WHERE id=".db_escape($selected_id)." - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component"; + AND item.stock_id=bom.component"; $result = db_query($sql, "could not get bom"); return db_fetch($result); @@ -249,7 +261,8 @@ function has_bom($item) function is_component_already_on_bom($component, $workcentre_added, $loc_code, $selected_parent) { - $sql = "SELECT component FROM ".TB_PREF."bom + $sql = "SELECT component + FROM ".TB_PREF."bom WHERE parent=".db_escape($selected_parent)." AND component=".db_escape($component) . " AND workcentre_added=".db_escape($workcentre_added) . " diff --git a/inventory/includes/db/items_prices_db.inc b/inventory/includes/db/items_prices_db.inc index 9b2ee373..5a474e7c 100644 --- a/inventory/includes/db/items_prices_db.inc +++ b/inventory/includes/db/items_prices_db.inc @@ -14,8 +14,8 @@ function add_item_price($stock_id, $sales_type_id, $curr_abrev, $price) $sql = "INSERT INTO ".TB_PREF."prices (stock_id, sales_type_id, curr_abrev, price) VALUES (".db_escape($stock_id).", ".db_escape($sales_type_id) .", ".db_escape($curr_abrev).", ".db_escape($price).")"; - - db_query($sql,"an item price could not be added"); + + db_query($sql,"an item price could not be added"); } function update_item_price($price_id, $sales_type_id, $curr_abrev, $price) @@ -23,33 +23,34 @@ function update_item_price($price_id, $sales_type_id, $curr_abrev, $price) $sql = "UPDATE ".TB_PREF."prices SET sales_type_id=".db_escape($sales_type_id).", curr_abrev=".db_escape($curr_abrev).", price=".db_escape($price)." WHERE id=".db_escape($price_id); - - db_query($sql,"an item price could not be updated"); + + db_query($sql,"an item price could not be updated"); } function delete_item_price($price_id) { $sql="DELETE FROM ".TB_PREF."prices WHERE id= ".db_escape($price_id); - db_query($sql,"an item price could not be deleted"); + db_query($sql,"an item price could not be deleted"); } function get_prices($stock_id) { - $sql = "SELECT ".TB_PREF."sales_types.sales_type, ".TB_PREF."prices.* - FROM ".TB_PREF."prices, ".TB_PREF."sales_types - WHERE ".TB_PREF."prices.sales_type_id = ".TB_PREF."sales_types.id + $sql = "SELECT pricelist.sales_type, price.* + FROM ".TB_PREF."prices price, " + .TB_PREF."sales_types pricelist + WHERE price.sales_type_id = pricelist.id AND stock_id=".db_escape($stock_id) - ." ORDER BY curr_abrev, sales_type_id"; - + ." ORDER BY curr_abrev, sales_type_id"; + return db_query($sql,"item prices could not be retreived"); } function get_stock_price($price_id) { $sql = "SELECT * FROM ".TB_PREF."prices WHERE id=".db_escape($price_id); - + $result = db_query($sql,"price could not be retreived"); - + return db_fetch($result); } @@ -58,8 +59,8 @@ function get_stock_price_type_currency($stock_id, $type, $currency) $sql = "SELECT * FROM ".TB_PREF."prices WHERE stock_id=".db_escape($stock_id)." AND sales_type_id=".db_escape($type)." AND curr_abrev=".db_escape($currency); - + $result = db_query($sql,"price could not be retreived"); - + return db_fetch($result); } diff --git a/inventory/includes/db/items_purchases_db.inc b/inventory/includes/db/items_purchases_db.inc index adbead6e..33f765a8 100644 --- a/inventory/includes/db/items_purchases_db.inc +++ b/inventory/includes/db/items_purchases_db.inc @@ -44,10 +44,9 @@ function delete_item_purchasing_data($selected_id, $stock_id) function get_items_purchasing_data($stock_id) { - $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name," - .TB_PREF."suppliers.curr_code - FROM ".TB_PREF."purch_data INNER JOIN ".TB_PREF."suppliers - ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id + $sql = "SELECT pdata.*, supplier.supp_name, supplier.curr_code + FROM ".TB_PREF."purch_data pdata + INNER JOIN ".TB_PREF."suppliers supplier ON pdata.supplier_id=supplier.supplier_id WHERE stock_id = ".db_escape($stock_id); return db_query($sql, "The supplier purchasing details for the selected part could not be retrieved"); @@ -55,14 +54,13 @@ function get_items_purchasing_data($stock_id) function get_item_purchasing_data($selected_id, $stock_id) { - $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name FROM ".TB_PREF."purch_data - INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id - WHERE ".TB_PREF."purch_data.supplier_id=".db_escape($selected_id)." - AND ".TB_PREF."purch_data.stock_id=".db_escape($stock_id); + $sql = "SELECT pdata.*, supplier.supp_name + FROM ".TB_PREF."purch_data pdata + INNER JOIN ".TB_PREF."suppliers supplier ON pdata.supplier_id=supplier.supplier_id + WHERE pata.supplier_id=".db_escape($selected_id)." + AND pdata.stock_id=".db_escape($stock_id); $result = db_query($sql, "The supplier purchasing details for the selected supplier and item could not be retrieved"); return db_fetch($result); } - - diff --git a/inventory/includes/db/items_trans_db.inc b/inventory/includes/db/items_trans_db.inc index b1a7f0e8..c0dff65c 100644 --- a/inventory/includes/db/items_trans_db.inc +++ b/inventory/includes/db/items_trans_db.inc @@ -15,25 +15,24 @@ function stock_cost_update($stock_id, $material_cost, $labour_cost, $overhead_co $last_cost) { $mb_flag = get_mb_flag($stock_id); - + $update_no = -1; if (is_service($mb_flag)) { //display_db_error("Cannot do cost update for Service item : $stock_id", ""); - + //Chaitanya $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)." WHERE stock_id=".db_escape($stock_id); - + db_query($sql,"The cost details for the inventory item could not be updated"); - + return $update_no; - - } - + } + begin_transaction(); - + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost).", labour_cost=".db_escape($labour_cost).", overhead_cost=".db_escape($overhead_cost).", @@ -42,7 +41,7 @@ function stock_cost_update($stock_id, $material_cost, $labour_cost, $overhead_co db_query($sql,"The cost details for the inventory item could not be updated"); $qoh = get_qoh_on_date($stock_id); - + $date_ = Today(); if (!is_date_in_fiscalyear($date_)) $date_ = end_fiscalyear(); diff --git a/inventory/includes/inventory_db.inc b/inventory/includes/inventory_db.inc index 5e4cee82..e922102e 100644 --- a/inventory/includes/inventory_db.inc +++ b/inventory/includes/inventory_db.inc @@ -60,11 +60,12 @@ function get_stock_movements($stock_id, $StockLocation, $BeforeDate, $AfterDate) function calculate_reorder_level($location, $line, &$st_ids, &$st_names, &$st_num, &$st_reorder) { - $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email - FROM ".TB_PREF."loc_stock, ".TB_PREF."locations - WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code - AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "' - AND ".TB_PREF."loc_stock.loc_code = '" . $location . "'"; + $sql = "SELECT stock.*, loc.location_name, loc.email + FROM ".TB_PREF."loc_stock stock," + .TB_PREF."locations loc + WHERE stock.loc_code=loc.loc_code + AND stock.stock_id = '" . $line->stock_id . "' + AND stock.loc_code = '" . $location . "'"; $res = db_query($sql,"a location could not be retreived"); $loc = db_fetch($res); if ($loc['email'] != "") @@ -87,7 +88,7 @@ function calculate_reorder_level($location, $line, &$st_ids, &$st_names, &$st_nu function send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder) { global $path_to_root; - + require_once($path_to_root . "/reporting/includes/class.mail.inc"); $company = get_company_prefs(); $mail = new email($company['coy_name'], $company['email']); diff --git a/manufacturing/includes/db/work_order_issues_db.inc b/manufacturing/includes/db/work_order_issues_db.inc index b055aac5..c374e581 100644 --- a/manufacturing/includes/db/work_order_issues_db.inc +++ b/manufacturing/includes/db/work_order_issues_db.inc @@ -111,27 +111,30 @@ function get_work_order_issues($woid) function get_additional_issues($woid) { - $sql = "SELECT ".TB_PREF."wo_issues.*, ".TB_PREF."wo_issue_items.* - FROM ".TB_PREF."wo_issues, ".TB_PREF."wo_issue_items - WHERE ".TB_PREF."wo_issues.issue_no=".TB_PREF."wo_issue_items.issue_id - AND ".TB_PREF."wo_issues.workorder_id=".db_escape($woid) - ." ORDER BY ".TB_PREF."wo_issue_items.id"; + $sql = "SELECT issue.*, item.* + FROM ".TB_PREF."wo_issues issue, " + .TB_PREF."wo_issue_items item + WHERE issue.issue_no=item.issue_id + AND issue.workorder_id=".db_escape($woid) + ." ORDER BY item.id"; return db_query($sql, "The work order issues could not be retrieved"); } //-------------------------------------------------------------------------------------- function get_work_order_issue($issue_no) { - $sql = "SELECT DISTINCT ".TB_PREF."wo_issues.*, ".TB_PREF."workorders.stock_id, - ".TB_PREF."stock_master.description, ".TB_PREF."locations.location_name, " - .TB_PREF."workcentres.name AS WorkCentreName - FROM ".TB_PREF."wo_issues, ".TB_PREF."workorders, ".TB_PREF."stock_master, " - .TB_PREF."locations, ".TB_PREF."workcentres + $sql = "SELECT DISTINCT issue.*, wo.stock_id, + item.description, loc.location_name, center.name AS WorkCentreName + FROM ".TB_PREF."wo_issues issue," + .TB_PREF."workorders wo," + .TB_PREF."stock_master item," + .TB_PREF."locations loc," + .TB_PREF."workcentres center WHERE issue_no=".db_escape($issue_no)." - AND ".TB_PREF."workorders.id = ".TB_PREF."wo_issues.workorder_id - AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_issues.loc_code - AND ".TB_PREF."workcentres.id = ".TB_PREF."wo_issues.workcentre_id - AND ".TB_PREF."stock_master.stock_id = ".TB_PREF."workorders.stock_id"; + AND wo.id = issue.workorder_id + AND loc.loc_code = issue.loc_code + AND center.id = issue.workcentre_id + AND item.stock_id = wo.stock_id"; $result = db_query($sql, "A work order issue could not be retrieved"); return db_fetch($result); @@ -141,12 +144,12 @@ function get_work_order_issue($issue_no) function get_work_order_issue_details($issue_no) { - $sql = "SELECT ".TB_PREF."wo_issue_items.*," - .TB_PREF."stock_master.description, ".TB_PREF."stock_master.units - FROM ".TB_PREF."wo_issue_items, ".TB_PREF."stock_master + $sql = "SELECT issue.*, item.description, item.units + FROM ".TB_PREF."wo_issue_items issue," + .TB_PREF."stock_master item WHERE issue_id=".db_escape($issue_no)." - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."wo_issue_items.stock_id - ORDER BY ".TB_PREF."wo_issue_items.id"; + AND item.stock_id=issue.stock_id + ORDER BY issue.id"; return db_query($sql, "The work order issue items could not be retrieved"); } @@ -218,7 +221,7 @@ function void_work_order_issue($type_no) if ($total_cost != 0) add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $issue["inventory_account"], 0, 0, $date_.": "._("Reversed the issue to")." ".$stockitem["description"], - -$total_cost); + -$total_cost); //Chaitanya : Shifted below void all related stock moves void_stock_move(ST_MANUISSUE, $type_no); diff --git a/manufacturing/includes/db/work_order_produce_items_db.inc b/manufacturing/includes/db/work_order_produce_items_db.inc index 1d87faef..e935d57d 100644 --- a/manufacturing/includes/db/work_order_produce_items_db.inc +++ b/manufacturing/includes/db/work_order_produce_items_db.inc @@ -81,12 +81,13 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) function get_work_order_produce($id) { - $sql = "SELECT ".TB_PREF."wo_manufacture.*,".TB_PREF."workorders.stock_id, " - .TB_PREF."stock_master.description AS StockDescription - FROM ".TB_PREF."wo_manufacture, ".TB_PREF."workorders, ".TB_PREF."stock_master - WHERE ".TB_PREF."wo_manufacture.workorder_id=".TB_PREF."workorders.id - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id - AND ".TB_PREF."wo_manufacture.id=".db_escape($id); + $sql = "SELECT prod.*, wo.stock_id, item.description AS StockDescription + FROM ".TB_PREF."wo_manufacture prod," + .TB_PREF."workorders wo," + .TB_PREF."stock_master item + WHERE prod.workorder_id=wo.id + AND item.stock_id=wo.stock_id + AND prod.id=".db_escape($id); $result = db_query($sql, "The work order production could not be retrieved"); return db_fetch($result); diff --git a/manufacturing/includes/db/work_order_requirements_db.inc b/manufacturing/includes/db/work_order_requirements_db.inc index b919561b..9422961f 100644 --- a/manufacturing/includes/db/work_order_requirements_db.inc +++ b/manufacturing/includes/db/work_order_requirements_db.inc @@ -11,16 +11,15 @@ ***********************************************************************/ function get_wo_requirements($woid) { - $sql = "SELECT ".TB_PREF."wo_requirements.*, ".TB_PREF."stock_master.description, - ".TB_PREF."stock_master.mb_flag, - ".TB_PREF."locations.location_name, - ".TB_PREF."workcentres.name AS WorkCentreDescription FROM - (".TB_PREF."wo_requirements, ".TB_PREF."locations, " - .TB_PREF."workcentres) INNER JOIN ".TB_PREF."stock_master ON - ".TB_PREF."wo_requirements.stock_id = ".TB_PREF."stock_master.stock_id + $sql = "SELECT req.*, item.description, item.mb_flag, loc.location_name, + center.name AS WorkCentreDescription + FROM (".TB_PREF."wo_requirements req," + .TB_PREF."locations loc," + .TB_PREF."workcentres center) + INNER JOIN ".TB_PREF."stock_master item ON req.stock_id=item.stock_id WHERE workorder_id=".db_escape($woid)." - AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_requirements.loc_code - AND ".TB_PREF."workcentres.id=workcentre"; + AND loc.loc_code = req.loc_code + AND center.id=workcentre"; return db_query($sql, "The work order requirements could not be retrieved"); } @@ -30,20 +29,20 @@ function get_wo_requirements($woid) function create_wo_requirements($woid, $stock_id) { // create Work Order Requirements based on the bom - $result = get_bom($stock_id); - + $result = get_bom($stock_id); + while ($myrow = db_fetch($result)) { - + $sql = "INSERT INTO ".TB_PREF."wo_requirements (workorder_id, stock_id, workcentre, units_req, loc_code) VALUES (".db_escape($woid).", '" . $myrow["component"] . "', '" . $myrow["workcentre_added"] . "', '" . $myrow["quantity"] . "', '" . $myrow["loc_code"] . "')"; - + db_query($sql, "The work order requirements could not be added"); - } + } } //-------------------------------------------------------------------------------------- @@ -61,7 +60,7 @@ function update_wo_requirement_issued($woid, $stock_id, $quantity) { $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = units_issued + ".db_escape($quantity)." WHERE workorder_id = ".db_escape($woid)." AND stock_id = ".db_escape($stock_id); - + db_query($sql, "The work requirements issued quantity couldn't be updated"); } @@ -75,5 +74,3 @@ function void_wo_requirements($woid) db_query($sql, "The work requirements issued quantity couldn't be voided"); } -//-------------------------------------------------------------------------------------- - diff --git a/manufacturing/includes/db/work_orders_quick_db.inc b/manufacturing/includes/db/work_orders_quick_db.inc index 89d8fa47..e7932edc 100644 --- a/manufacturing/includes/db/work_orders_quick_db.inc +++ b/manufacturing/includes/db/work_orders_quick_db.inc @@ -36,7 +36,7 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, if (!isset($labour) || ($labour == "")) $labour = 0; add_labour_cost($stock_id, $units_reqd, $date_, $labour); - + $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, units_issued, stock_id, type, additional_costs, date_, released_date, required_by, released, closed) VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", ".db_escape($units_reqd) @@ -70,7 +70,7 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, add_stock_move(ST_WORKORDER, $bom_item["component"], $woid, $bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, $UnitCost); } - + // ------------------------------------------------------------------------- //Negative Stock Handling $qoh = get_qoh_on_date($stock_id); @@ -124,7 +124,7 @@ function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $advanced $bom_accounts = get_stock_gl_code($bom_item["component"]); $bom_cost = $bom_item["ComponentCost"] * $units_reqd; - + $memo = $bom_item["quantity"] ." * ".$bom_item["description"]; if ($advanced) { @@ -188,5 +188,3 @@ function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $advanced 0, 0, $memo, -$total_cost); } -//-------------------------------------------------------------------------------------- - diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index 7604568f..3b29b75b 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -232,11 +232,13 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) $mcost = update_average_material_cost($supplier, $entered_grn->item_code, $entered_grn->chg_price, $entered_grn->this_quantity_inv, $date); - $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.* - FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items - WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id - AND ".TB_PREF."grn_items.id=".db_escape($entered_grn->id)." - AND ".TB_PREF."grn_items.item_code=".db_escape($entered_grn->item_code); + $sql = "SELECT grn.*, item.* + FROM ".TB_PREF."grn_batch grn," + .TB_PREF."grn_items item + WHERE item.grn_batch_id=grn.id + AND item.id=".db_escape($entered_grn->id)." + AND item.item_code=".db_escape($entered_grn->item_code); + $result = db_query($sql, "Could not retreive GRNS"); $myrow = db_fetch($result); @@ -318,13 +320,14 @@ function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false function get_grn_item_detail($grn_item_no) { - $sql = "SELECT ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price, - ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv AS QtyOstdg, - ".TB_PREF."purch_order_details.std_cost_unit - FROM ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master - WHERE ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code - AND ".TB_PREF."grn_items.id=".db_escape($grn_item_no); + $sql = "SELECT grn.*, po.unit_price, grn.qty_recd - grn.quantity_inv AS QtyOstdg, + po.std_cost_unit + FROM ".TB_PREF."grn_items grn," + .TB_PREF."purch_order_details po," + .TB_PREF."stock_master item + WHERE grn.po_detail_item=po.po_detail_item + AND item.stock_id=grn.item_code + AND grn.id=".db_escape($grn_item_no); $result = db_query($sql, "could not retreive grn item details"); return db_fetch($result); @@ -415,8 +418,10 @@ function exists_grn($grn_batch) function exists_grn_on_invoices($grn_batch) { - $sql = "SELECT ".TB_PREF."supp_invoice_items.id FROM ".TB_PREF."supp_invoice_items,".TB_PREF."grn_items - WHERE ".TB_PREF."supp_invoice_items.grn_item_id=".TB_PREF."grn_items.id + $sql = "SELECT inv.id + FROM ".TB_PREF."supp_invoice_items inv," + .TB_PREF."grn_items grn + WHERE inv.grn_item_id=grn.id AND quantity != 0 AND grn_batch_id=".db_escape($grn_batch); $result = db_query($sql, "Cannot query GRNs"); diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 11ce9f93..3bd10030 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -75,9 +75,11 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi $unit_price = $row[1]; //Added by Rasmus - $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE - ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND " - .TB_PREF."grn_items.id=".db_escape($id); + $sql = "SELECT delivery_date + FROM ".TB_PREF."grn_batch grn," + .TB_PREF."grn_items line + WHERE + grn.id = line.grn_batch_id AND line.id=".db_escape($id); $result = db_query($sql, "The old delivery date from the received record cout not be retrieved"); $row = db_fetch_row($result); $date = $row[0]; @@ -408,17 +410,18 @@ function add_supp_invoice(&$supp_trans) function get_po_invoices_credits($po_number) { - $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type, - ov_amount+ov_discount+ov_gst AS Total, - ".TB_PREF."supp_trans.tran_date - FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, " - .TB_PREF."purch_order_details, ".TB_PREF."purch_orders - WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no - AND ".TB_PREF."supp_invoice_items.supp_trans_type = ".TB_PREF."supp_trans.type - AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item - AND ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."supp_trans.supplier_id - AND ".TB_PREF."purch_orders.order_no = ".TB_PREF."purch_order_details.order_no - AND ".TB_PREF."purch_order_details.order_no = ".db_escape($po_number); + $sql = "SELECT DISTINCT trans.trans_no, trans.type, ov_amount+ov_discount+ov_gst AS Total, + trans.tran_date + FROM ".TB_PREF."supp_trans trans," + .TB_PREF."supp_invoice_items line," + .TB_PREF."purch_order_details poline," + .TB_PREF."purch_orders po + WHERE line.supp_trans_no = trans.trans_no + AND line.supp_trans_type = trans.type + AND line.po_detail_item_id = poline.po_detail_item + AND po.supplier_id = trans.supplier_id + AND po.order_no = poline.order_no + AND poline.order_no = ".db_escape($po_number); return db_query($sql, "The invoices/credits for the po $po_number could not be retreived"); } @@ -447,10 +450,11 @@ function get_tax_overrides($trans_type, $trans_no) function read_supp_invoice($trans_no, $trans_type, &$supp_trans) { - $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name - FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers + $sql = "SELECT trans.*, supp_name + FROM ".TB_PREF."supp_trans trans," + .TB_PREF."suppliers sup WHERE trans_no = ".db_escape($trans_no)." AND type = ".db_escape($trans_type)." - AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id"; + AND sup.supplier_id=trans.supplier_id"; $result = db_query($sql, "Cannot retreive a supplier transaction"); @@ -513,7 +517,9 @@ function read_supp_invoice($trans_no, $trans_type, &$supp_trans) function get_matching_invoice_item($stock_id, $po_item_id) { - $sql = "SELECT *, tran_date FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_trans + $sql = "SELECT *, tran_date + FROM ".TB_PREF."supp_invoice_items," + .TB_PREF."supp_trans WHERE supp_trans_type = ".ST_SUPPINVOICE." AND stock_id = " .db_escape($stock_id)." AND po_detail_item_id = ".db_escape($po_item_id)." AND supp_trans_no = trans_no"; @@ -669,14 +675,14 @@ function remove_not_invoice_item($id) update_average_material_cost($grn["supplier_id"], $myrow["item_code"], $myrow["unit_price"], -$myrow["QtyOstdg"], Today()); - + $price = $myrow['unit_price']; if ($supp['tax_included']) $price = get_tax_free_price_for_item($myrow['item_code'], $myrow['unit_price'], $supp['tax_group_id'], $supp['tax_included']); add_stock_move(ST_SUPPRECEIVE, $myrow["item_code"], $myrow['grn_batch_id'], $grn['loc_code'], sql2date($grn["delivery_date"]), "", -$myrow["QtyOstdg"], $myrow['std_cost_unit'], $price); - + $clearing_act = get_company_pref('grn_clearing_act'); if ($clearing_act) { // otherwise GRN clearing account is not used if (is_inventory_item($myrow['item_code'])) @@ -689,7 +695,7 @@ function remove_not_invoice_item($id) -$myrow['QtyOstdg'] * $price, $grn["supplier_id"], "", 0, _("GRN Removal")); $total += add_gl_trans_supplier(ST_SUPPRECEIVE, $myrow['grn_batch_id'], $date, $clearing_act, 0, 0, -$total, null, "", 0, _("GRN Removal")); - } + } } commit_transaction(); diff --git a/purchasing/includes/db/po_db.inc b/purchasing/includes/db/po_db.inc index aa705cd7..9bc64d4c 100644 --- a/purchasing/includes/db/po_db.inc +++ b/purchasing/includes/db/po_db.inc @@ -169,13 +169,13 @@ function update_po(&$po_obj) function read_po_header($order_no, &$order) { - $sql = "SELECT ".TB_PREF."purch_orders.*, " - .TB_PREF."suppliers.*, " - .TB_PREF."locations.location_name - FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations - WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id - AND ".TB_PREF."locations.loc_code = into_stock_location - AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no); + $sql = "SELECT po.*, supplier.*, loc.location_name + FROM ".TB_PREF."purch_orders po," + .TB_PREF."suppliers supplier," + .TB_PREF."locations loc + WHERE po.supplier_id = supplier.supplier_id + AND loc.loc_code = into_stock_location + AND po.order_no = ".db_escape($order_no); $result = db_query($sql, "The order cannot be retrieved"); @@ -215,14 +215,13 @@ function read_po_items($order_no, &$order, $open_items_only=false) { /*now populate the line po array with the purchase order details records */ - $sql = "SELECT ".TB_PREF."purch_order_details.*, units - FROM ".TB_PREF."purch_order_details - LEFT JOIN ".TB_PREF."stock_master - ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id + $sql = "SELECT poline.*, units + FROM ".TB_PREF."purch_order_details poline + LEFT JOIN ".TB_PREF."stock_master item ON poline.item_code=item.stock_id WHERE order_no =".db_escape($order_no); if ($open_items_only) - $sql .= " AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) "; + $sql .= " AND (poline.quantity_ordered > poline.quantity_received) "; $sql .= " ORDER BY po_detail_item"; diff --git a/purchasing/includes/db/supp_trans_db.inc b/purchasing/includes/db/supp_trans_db.inc index 417f97b4..f49b6c07 100644 --- a/purchasing/includes/db/supp_trans_db.inc +++ b/purchasing/includes/db/supp_trans_db.inc @@ -52,40 +52,43 @@ function write_supp_trans($type, $trans_no, $supplier_id, $date_, $due_date, $re function get_supp_trans($trans_no, $trans_type=-1, $supplier_id=null) { - $sql = "SELECT ".TB_PREF."supp_trans.*, (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst+".TB_PREF."supp_trans.ov_discount) AS Total, - ".TB_PREF."suppliers.supp_name AS supplier_name, ".TB_PREF."suppliers.curr_code AS curr_code "; + $sql = "SELECT trans.*, (trans.ov_amount+trans.ov_gst+trans.ov_discount) AS Total, + supplier.supp_name AS supplier_name, supplier.curr_code AS curr_code "; if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT) { // 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_accounts.bank_curr_code, - ".TB_PREF."bank_accounts.account_type AS BankTransType, ".TB_PREF."bank_trans.amount AS bank_amount, - ".TB_PREF."bank_trans.ref "; + $sql .= ", account.bank_name, account.bank_account_name, account.bank_curr_code, + account.account_type AS BankTransType, bank_trans.amount AS bank_amount, + bank_trans.ref "; } - $sql .= " FROM ".TB_PREF."supp_trans, ".TB_PREF."suppliers "; + $sql .= " FROM ".TB_PREF."supp_trans trans," + .TB_PREF."suppliers supplier"; if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT) { // it's a payment so also get the bank account - $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts"; + $sql .= ", " + .TB_PREF."bank_trans bank_trans," + .TB_PREF."bank_accounts account"; } - $sql .= " WHERE ".TB_PREF."supp_trans.trans_no=".db_escape($trans_no)." - AND ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id"; + $sql .= " WHERE trans.trans_no=".db_escape($trans_no)." + AND trans.supplier_id=supplier.supplier_id"; if (isset($supplier_id)) - $sql .= " AND ".TB_PREF."supp_trans.supplier_id=".db_escape($supplier_id); + $sql .= " AND trans.supplier_id=".db_escape($supplier_id); if ($trans_type > -1) - $sql .= " AND ".TB_PREF."supp_trans.type=".db_escape($trans_type); + $sql .= " AND trans.type=".db_escape($trans_type); if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT) { // it's a payment so also get the bank account - $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_no)." - AND ".TB_PREF."bank_trans.type=".db_escape($trans_type)." - AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; + $sql .= " AND bank_trans.trans_no =".db_escape($trans_no)." + AND bank_trans.type=".db_escape($trans_type)." + AND account.id=bank_trans.bank_act "; } $result = db_query($sql, "Cannot retreive a supplier transaction"); diff --git a/reporting/rep102.php b/reporting/rep102.php index d108f1c1..95d267c3 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -35,39 +35,30 @@ function get_invoices($customer_id, $to, $all=true) // Revomed allocated from sql if ($all) - $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " - .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " - .TB_PREF."debtor_trans.ov_discount)"; - else - $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " - .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " - .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc)"; + $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)"; + else + $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc)"; $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT,ST_JOURNAL))."), -1, 1)"; - $due = "IF (".TB_PREF."debtor_trans.type=".ST_SALESINVOICE.",".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)"; - $sql = "SELECT ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference, - ".TB_PREF."debtor_trans.tran_date, + $due = "IF (type=".ST_SALESINVOICE.", due_date, tran_date)"; + + $sql = "SELECT type, reference, tran_date, $sign*$value as Balance, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0) AS Due, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$sign*$value,0) AS Overdue1, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$sign*$value,0) AS Overdue2 - FROM ".TB_PREF."debtors_master, - ".TB_PREF."debtor_trans + FROM ".TB_PREF."debtor_trans trans + + WHERE type <> ".ST_CUSTDELIVERY." + AND debtor_no = $customer_id + AND tran_date <= '$todate' + AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > " . FLOAT_COMP_DELTA; - WHERE ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY." - AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no - AND ".TB_PREF."debtor_trans.debtor_no = $customer_id - AND ".TB_PREF."debtor_trans.tran_date <= '$todate' - AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " - .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " - .TB_PREF."debtor_trans.ov_discount) > " . FLOAT_COMP_DELTA; if (!$all) - $sql .= "AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " - .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " - .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc) > " . FLOAT_COMP_DELTA; - $sql .= "ORDER BY ".TB_PREF."debtor_trans.tran_date"; + $sql .= "AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc) > " . FLOAT_COMP_DELTA; + $sql .= "ORDER BY tran_date"; - return db_query($sql, "The customer details could not be retrieved"); + return db_query($sql, "The customer transactions could not be retrieved"); } //---------------------------------------------------------------------------------------------------- diff --git a/reporting/rep103.php b/reporting/rep103.php index bf5efdbd..9f1e797b 100644 --- a/reporting/rep103.php +++ b/reporting/rep103.php @@ -29,46 +29,42 @@ print_customer_details_listing(); function get_customer_details_for_report($area=0, $salesid=0) { - $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, - ".TB_PREF."debtors_master.name, - ".TB_PREF."debtors_master.address, - ".TB_PREF."debtors_master.curr_code, - ".TB_PREF."debtors_master.dimension_id, - ".TB_PREF."debtors_master.dimension2_id, - ".TB_PREF."debtors_master.notes, - ".TB_PREF."sales_types.sales_type, - ".TB_PREF."cust_branch.branch_code, - ".TB_PREF."cust_branch.br_name, - ".TB_PREF."cust_branch.br_address, - ".TB_PREF."cust_branch.br_post_address, - ".TB_PREF."cust_branch.area, - ".TB_PREF."cust_branch.salesman, - ".TB_PREF."areas.description, - ".TB_PREF."salesman.salesman_name - FROM ".TB_PREF."debtors_master - INNER JOIN ".TB_PREF."cust_branch - ON ".TB_PREF."debtors_master.debtor_no=".TB_PREF."cust_branch.debtor_no - INNER JOIN ".TB_PREF."sales_types - ON ".TB_PREF."debtors_master.sales_type=".TB_PREF."sales_types.id - INNER JOIN ".TB_PREF."areas - ON ".TB_PREF."cust_branch.area = ".TB_PREF."areas.area_code - INNER JOIN ".TB_PREF."salesman - ON ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code - WHERE ".TB_PREF."debtors_master.inactive = 0"; + $sql = "SELECT debtor.debtor_no, + debtor.name, + debtor.address, + debtor.curr_code, + debtor.dimension_id, + debtor.dimension2_id, + debtor.notes, + pricelist.sales_type, + branch.branch_code, + branch.br_name, + branch.br_address, + branch.br_post_address, + branch.area, + branch.salesman, + area.description, + salesman.salesman_name + FROM ".TB_PREF."debtors_master debtor + INNER JOIN ".TB_PREF."cust_branch branch ON debtor.debtor_no=branch.debtor_no + INNER JOIN ".TB_PREF."sales_types pricelist ON debtor.sales_type=pricelist.id + INNER JOIN ".TB_PREF."areas area ON branch.area = area.area_code + INNER JOIN ".TB_PREF."salesman salesman ON branch.salesman=salesman.salesman_code + WHERE debtor.inactive = 0"; if ($area != 0) { if ($salesid != 0) - $sql .= " AND ".TB_PREF."salesman.salesman_code=".db_escape($salesid)." - AND ".TB_PREF."areas.area_code=".db_escape($area); + $sql .= " AND salesman.salesman_code=".db_escape($salesid)." + AND area.area_code=".db_escape($area); else - $sql .= " AND ".TB_PREF."areas.area_code=".db_escape($area); + $sql .= " AND area.area_code=".db_escape($area); } elseif ($salesid != 0) - $sql .= " AND ".TB_PREF."salesman.salesman_code=".db_escape($salesid); + $sql .= " AND salesman.salesman_code=".db_escape($salesid); $sql .= " ORDER BY description, - ".TB_PREF."salesman.salesman_name, - ".TB_PREF."debtors_master.debtor_no, - ".TB_PREF."cust_branch.branch_code"; + salesman.salesman_name, + debtor.debtor_no, + branch.branch_code"; return db_query($sql,"No transactions were returned"); } @@ -76,7 +72,9 @@ function get_customer_details_for_report($area=0, $salesid=0) function get_contacts_for_branch($branch) { $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type - FROM ".TB_PREF."crm_persons p,".TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch' + FROM ".TB_PREF."crm_persons p," + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND r.type='cust_branch' AND r.entity_id=".db_escape($branch); $res = db_query($sql, "can't retrieve branch contacts"); $results = array(); diff --git a/reporting/rep104.php b/reporting/rep104.php index f73a2c4a..bea243a8 100644 --- a/reporting/rep104.php +++ b/reporting/rep104.php @@ -32,17 +32,17 @@ print_price_listing(); function fetch_items($category=0) { - $sql = "SELECT ".TB_PREF."stock_master.stock_id, ".TB_PREF."stock_master.description AS name, - ".TB_PREF."stock_master.material_cost+".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS Standardcost, - ".TB_PREF."stock_master.category_id,".TB_PREF."stock_master.units, - ".TB_PREF."stock_category.description - FROM ".TB_PREF."stock_master, - ".TB_PREF."stock_category - WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id AND NOT ".TB_PREF."stock_master.inactive"; + $sql = "SELECT item.stock_id, item.description AS name, + item.material_cost+item.labour_cost+item.overhead_cost AS Standardcost, + item.category_id,item.units, + category.description + FROM ".TB_PREF."stock_master item, + ".TB_PREF."stock_category category + WHERE item.category_id=category.category_id AND NOT item.inactive"; if ($category != 0) - $sql .= " AND ".TB_PREF."stock_category.category_id = ".db_escape($category); - $sql .= " ORDER BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_master.stock_id"; + $sql .= " AND category.category_id = ".db_escape($category); + $sql .= " ORDER BY item.category_id, + item.stock_id"; return db_query($sql,"No transactions were returned"); } @@ -51,11 +51,9 @@ function get_kits($category=0) { $sql = "SELECT i.item_code AS kit_code, i.description AS kit_name, c.category_id AS cat_id, c.description AS cat_name, count(*)>1 AS kit FROM - ".TB_PREF."item_codes i - LEFT JOIN - ".TB_PREF."stock_category c - ON i.category_id=c.category_id"; - $sql .= " WHERE !i.is_foreign AND i.item_code!=i.stock_id"; + ".TB_PREF."item_codes i + LEFT JOIN ".TB_PREF."stock_category c ON i.category_id=c.category_id + WHERE !i.is_foreign AND i.item_code!=i.stock_id"; if ($category != 0) $sql .= " AND c.category_id = ".db_escape($category); $sql .= " GROUP BY i.item_code"; diff --git a/reporting/rep105.php b/reporting/rep105.php index db32dffd..752436d8 100644 --- a/reporting/rep105.php +++ b/reporting/rep105.php @@ -35,34 +35,34 @@ function GetSalesOrders($from, $to, $category=0, $location=null, $backorder=0) $fromdate = date2sql($from); $todate = date2sql($to); - $sql= "SELECT ".TB_PREF."sales_orders.order_no, - ".TB_PREF."sales_orders.debtor_no, - ".TB_PREF."sales_orders.branch_code, - ".TB_PREF."sales_orders.customer_ref, - ".TB_PREF."sales_orders.ord_date, - ".TB_PREF."sales_orders.from_stk_loc, - ".TB_PREF."sales_orders.delivery_date, - ".TB_PREF."sales_order_details.stk_code, - ".TB_PREF."stock_master.description, - ".TB_PREF."stock_master.units, - ".TB_PREF."sales_order_details.quantity, - ".TB_PREF."sales_order_details.qty_sent - FROM ".TB_PREF."sales_orders - INNER JOIN ".TB_PREF."sales_order_details - ON (".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no - AND ".TB_PREF."sales_orders.trans_type = ".TB_PREF."sales_order_details.trans_type - AND ".TB_PREF."sales_orders.trans_type = ".ST_SALESORDER.") - INNER JOIN ".TB_PREF."stock_master - ON ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id - WHERE ".TB_PREF."sales_orders.ord_date >='$fromdate' - AND ".TB_PREF."sales_orders.ord_date <='$todate'"; + $sql= "SELECT sorder.order_no, + sorder.debtor_no, + sorder.branch_code, + sorder.customer_ref, + sorder.ord_date, + sorder.from_stk_loc, + sorder.delivery_date, + line.stk_code, + item.description, + item.units, + line.quantity, + line.qty_sent + FROM ".TB_PREF."sales_orders sorder + INNER JOIN ".TB_PREF."sales_order_details line + ON sorder.order_no = line.order_no + AND sorder.trans_type = line.trans_type + AND sorder.trans_type = ".ST_SALESORDER." + INNER JOIN ".TB_PREF."stock_master item + ON line.stk_code = item.stock_id + WHERE sorder.ord_date >='$fromdate' + AND sorder.ord_date <='$todate'"; if ($category > 0) - $sql .= " AND ".TB_PREF."stock_master.category_id=".db_escape($category); + $sql .= " AND item.category_id=".db_escape($category); if ($location != null) - $sql .= " AND ".TB_PREF."sales_orders.from_stk_loc=".db_escape($location); + $sql .= " AND sorder.from_stk_loc=".db_escape($location); if ($backorder) - $sql .= " AND ".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_sent > 0"; - $sql .= " ORDER BY ".TB_PREF."sales_orders.order_no"; + $sql .= " AND line.quantity - line.qty_sent > 0"; + $sql .= " ORDER BY sorder.order_no"; return db_query($sql, "Error getting order details"); } diff --git a/reporting/rep108.php b/reporting/rep108.php index 84a80ca1..b4799541 100644 --- a/reporting/rep108.php +++ b/reporting/rep108.php @@ -32,21 +32,17 @@ print_statements(); function getTransactions($debtorno, $date, $show_also_allocated) { - $sql = "SELECT ".TB_PREF."debtor_trans.*, - (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) - AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated, - ((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.") - AND ".TB_PREF."debtor_trans.due_date < '$date') AS OverDue - FROM ".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.tran_date <= '$date' AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)." - AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY." - AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) > 1e-6"; + $sql = "SELECT *, + (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) AS TotalAmount, alloc AS Allocated, + ((type = ".ST_SALESINVOICE.") AND due_date < '$date') AS OverDue + FROM ".TB_PREF."debtor_trans + WHERE tran_date <= '$date' AND debtor_no = ".db_escape($debtorno)." + AND type <> ".ST_CUSTDELIVERY." + AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > ". FLOAT_COMP_DELTA; + if (!$show_also_allocated) - $sql .= " AND ABS(ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) - alloc) > 1e-6"; - $sql .= " ORDER BY ".TB_PREF."debtor_trans.tran_date"; + $sql .= " AND ABS(ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) - alloc) > ". FLOAT_COMP_DELTA; + $sql .= " ORDER BY tran_date"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep112.php b/reporting/rep112.php index 6cb3148f..a70b78cb 100644 --- a/reporting/rep112.php +++ b/reporting/rep112.php @@ -31,18 +31,20 @@ print_receipts(); //---------------------------------------------------------------------------------------------------- function get_receipt($type, $trans_no) { - $sql = "SELECT ".TB_PREF."debtor_trans.*, - (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax) AS Total, - ".TB_PREF."debtor_trans.ov_discount, - ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.debtor_ref, - ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.payment_terms, " - .TB_PREF."debtors_master.tax_id AS tax_id, - ".TB_PREF."debtors_master.address - FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master - WHERE ".TB_PREF."debtor_trans.debtor_no = ".TB_PREF."debtors_master.debtor_no - AND ".TB_PREF."debtor_trans.type = ".db_escape($type)." - AND ".TB_PREF."debtor_trans.trans_no = ".db_escape($trans_no); + $sql = "SELECT trans.*, + (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax) AS Total, + trans.ov_discount, + debtor.name AS DebtorName, + debtor.debtor_ref, + debtor.curr_code, + debtor.payment_terms, + debtor.tax_id AS tax_id, + debtor.address + FROM ".TB_PREF."debtor_trans trans," + .TB_PREF."debtors_master debtor + WHERE trans.debtor_no = debtor.debtor_no + AND trans.type = ".db_escape($type)." + AND trans.trans_no = ".db_escape($trans_no); $result = db_query($sql, "The remittance cannot be retrieved"); if (db_num_rows($result) == 0) return false; diff --git a/reporting/rep201.php b/reporting/rep201.php index d06adb53..0a818ad5 100644 --- a/reporting/rep201.php +++ b/reporting/rep201.php @@ -31,17 +31,15 @@ function get_open_balance($supplier_id, $to) { $to = date2sql($to); - $sql = "SELECT SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS charges, - SUM(IF(".TB_PREF."supp_trans.type <> ".ST_SUPPINVOICE." AND ".TB_PREF."supp_trans.type <> ".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS credits, - SUM(".TB_PREF."supp_trans.alloc) AS Allocated, - SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc), - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))) AS OutStanding + $sql = "SELECT + SUM(IF(type IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount), 0)) AS charges, + SUM(IF(type NOT IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount), 0)) AS credits, + SUM(alloc) AS Allocated, + SUM(IF(type IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount - alloc), + (ov_amount + ov_gst + ov_discount + alloc))) AS OutStanding FROM ".TB_PREF."supp_trans - WHERE ".TB_PREF."supp_trans.tran_date < '$to' - AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' GROUP BY supplier_id"; + WHERE tran_date < '$to' + AND supplier_id = '$supplier_id' GROUP BY supplier_id"; $result = db_query($sql,"No transactions were returned"); return db_fetch($result); @@ -52,15 +50,14 @@ function getTransactions($supplier_id, $from, $to) $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT ".TB_PREF."supp_trans.*, - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) - AS TotalAmount, ".TB_PREF."supp_trans.alloc AS Allocated, - ((".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE.") - AND ".TB_PREF."supp_trans.due_date < '$to') AS OverDue - FROM ".TB_PREF."supp_trans - WHERE ".TB_PREF."supp_trans.tran_date >= '$from' AND ".TB_PREF."supp_trans.tran_date <= '$to' - AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' AND ".TB_PREF."supp_trans.ov_amount!=0 - ORDER BY ".TB_PREF."supp_trans.tran_date"; + $sql = "SELECT *, + (ov_amount + ov_gst + ov_discount) AS TotalAmount, + alloc AS Allocated, + ((type = ".ST_SUPPINVOICE.") AND due_date < '$to') AS OverDue + FROM ".TB_PREF."supp_trans + WHERE tran_date >= '$from' AND tran_date <= '$to' + AND supplier_id = '$supplier_id' AND ov_amount!=0 + ORDER BY tran_date"; $TransResult = db_query($sql,"No transactions were returned"); diff --git a/reporting/rep202.php b/reporting/rep202.php index 0ea9e5f8..d327f8b9 100644 --- a/reporting/rep202.php +++ b/reporting/rep202.php @@ -37,32 +37,30 @@ function get_invoices($supplier_id, $to, $all=true) // Revomed allocated from sql if ($all) - $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)"; + $value = "(trans.ov_amount + trans.ov_gst + trans.ov_discount)"; else - $value = "IF (".TB_PREF."supp_trans.type=".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type=".ST_BANKDEPOSIT.", - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc), - (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))"; - $due = "IF (".TB_PREF."supp_trans.type=".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type=".ST_SUPPCREDIT.",".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)"; - $sql = "SELECT ".TB_PREF."supp_trans.type, - ".TB_PREF."supp_trans.reference, - ".TB_PREF."supp_trans.tran_date, + $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.", + (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc), + (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc))"; + $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)"; + $sql = "SELECT trans.type, + trans.reference, + trans.tran_date, $value as Balance, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0) AS Due, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$value,0) AS Overdue1, IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$value,0) AS Overdue2 - FROM ".TB_PREF."suppliers, - ".TB_PREF."payment_terms, - ".TB_PREF."supp_trans + FROM ".TB_PREF."suppliers supplier, + ".TB_PREF."supp_trans trans - WHERE ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator - AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id - AND ".TB_PREF."supp_trans.supplier_id = $supplier_id - AND ".TB_PREF."supp_trans.tran_date <= '$todate' - AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) > ".FLOAT_COMP_DELTA." "; + WHERE supplier.supplier_id = trans.supplier_id + AND trans.supplier_id = $supplier_id + AND trans.tran_date <= '$todate' + AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA; if (!$all) - $sql .= "AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) - ".TB_PREF."supp_trans.alloc > ".FLOAT_COMP_DELTA." "; - $sql .= "ORDER BY ".TB_PREF."supp_trans.tran_date"; + $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA; + $sql .= " ORDER BY trans.tran_date"; return db_query($sql, "The supplier details could not be retrieved"); diff --git a/reporting/rep203.php b/reporting/rep203.php index 9b1ad3b4..1422ce25 100644 --- a/reporting/rep203.php +++ b/reporting/rep203.php @@ -32,21 +32,16 @@ function getTransactions($supplier, $date) $date = date2sql($date); $dec = user_price_dec(); - $sql = "SELECT ".TB_PREF."supp_trans.supp_reference, - ".TB_PREF."supp_trans.tran_date, - ".TB_PREF."supp_trans.due_date, - ".TB_PREF."supp_trans.trans_no, - ".TB_PREF."supp_trans.type, - ".TB_PREF."supp_trans.rate, - (ABS(".TB_PREF."supp_trans.ov_amount) + ABS(".TB_PREF."supp_trans.ov_gst) - ".TB_PREF."supp_trans.alloc) AS Balance, - (ABS(".TB_PREF."supp_trans.ov_amount) + ABS(".TB_PREF."supp_trans.ov_gst) ) AS TranTotal + $sql = "SELECT supp_reference, tran_date, due_date, trans_no, type, rate, + (ABS( ov_amount) + ABS( ov_gst) - alloc) AS Balance, + (ABS( ov_amount) + ABS( ov_gst) ) AS TranTotal FROM ".TB_PREF."supp_trans - WHERE ".TB_PREF."supp_trans.supplier_id = '" . $supplier . "' - AND ROUND(ABS(".TB_PREF."supp_trans.ov_amount),$dec) + ROUND(ABS(".TB_PREF."supp_trans.ov_gst),$dec) - - ROUND(".TB_PREF."supp_trans.alloc,$dec) != 0 - AND ".TB_PREF."supp_trans.tran_date <='" . $date . "' - ORDER BY ".TB_PREF."supp_trans.type, - ".TB_PREF."supp_trans.trans_no"; + WHERE supplier_id = '$supplier' + AND ROUND(ABS( ov_amount),$dec) + ROUND(ABS( ov_gst),$dec) - + ROUND( alloc,$dec) != 0 + AND tran_date <='$date' + ORDER BY type, + trans_no"; return db_query($sql, "No transactions were returned"); } diff --git a/reporting/rep204.php b/reporting/rep204.php index ba14f2ce..51fbf012 100644 --- a/reporting/rep204.php +++ b/reporting/rep204.php @@ -29,30 +29,30 @@ print_outstanding_GRN(); function getTransactions($fromsupp) { - $sql = "SELECT ".TB_PREF."grn_batch.id, + $sql = "SELECT grn.id, order_no, - ".TB_PREF."grn_batch.supplier_id, - ".TB_PREF."suppliers.supp_name, - ".TB_PREF."grn_items.item_code, - ".TB_PREF."grn_items.description, + grn.supplier_id, + supplier.supp_name, + item.item_code, + item.description, qty_recd, quantity_inv, std_cost_unit, act_price, unit_price - FROM ".TB_PREF."grn_items, - ".TB_PREF."grn_batch, - ".TB_PREF."purch_order_details, - ".TB_PREF."suppliers - WHERE ".TB_PREF."grn_batch.supplier_id=".TB_PREF."suppliers.supplier_id - AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id - AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item - AND qty_recd-quantity_inv <>0 "; + FROM ".TB_PREF."grn_items item, + ".TB_PREF."grn_batch grn, + ".TB_PREF."purch_order_details poline, + ".TB_PREF."suppliers supplier + WHERE grn.supplier_id=supplier.supplier_id + AND grn.id = item.grn_batch_id + AND item.po_detail_item = poline.po_detail_item + AND qty_recd-quantity_inv!=0"; if ($fromsupp != ALL_TEXT) - $sql .= "AND ".TB_PREF."grn_batch.supplier_id =".db_escape($fromsupp)." "; - $sql .= "ORDER BY ".TB_PREF."grn_batch.supplier_id, - ".TB_PREF."grn_batch.id"; + $sql .= " AND grn.supplier_id =".db_escape($fromsupp); + + $sql .= " ORDER BY grn.supplier_id, grn.id"; return db_query($sql, "No transactions were returned"); } diff --git a/reporting/rep205.php b/reporting/rep205.php index d2f500b3..5de3fd4a 100644 --- a/reporting/rep205.php +++ b/reporting/rep205.php @@ -30,20 +30,11 @@ print_supplier_details_listing(); function get_supplier_details_for_report() { - $sql = "SELECT ".TB_PREF."suppliers.supplier_id, - ".TB_PREF."suppliers.supp_name, - ".TB_PREF."suppliers.address, - ".TB_PREF."suppliers.supp_address, - ".TB_PREF."suppliers.supp_ref, - ".TB_PREF."suppliers.contact, - ".TB_PREF."suppliers.curr_code, - ".TB_PREF."suppliers.dimension_id, - ".TB_PREF."suppliers.dimension2_id, - ".TB_PREF."suppliers.notes, - ".TB_PREF."suppliers.gst_no - FROM ".TB_PREF."suppliers - WHERE inactive = 0 - ORDER BY supp_name"; + $sql = "SELECT supplier_id, supp_name, address, supp_address, supp_ref, + contact, curr_code, dimension_id, dimension2_id, notes, gst_no + FROM ".TB_PREF."suppliers + WHERE inactive = 0 + ORDER BY supp_name"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep209.php b/reporting/rep209.php index c3246a62..0e83fc01 100644 --- a/reporting/rep209.php +++ b/reporting/rep209.php @@ -33,24 +33,25 @@ print_po(); //---------------------------------------------------------------------------------------------------- function get_po($order_no) { - $sql = "SELECT ".TB_PREF."purch_orders.*, ".TB_PREF."suppliers.supp_name, " - .TB_PREF."suppliers.supp_account_no,".TB_PREF."suppliers.tax_included,".TB_PREF."suppliers.gst_no AS tax_id, - ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.payment_terms, ".TB_PREF."locations.location_name, - ".TB_PREF."suppliers.address, ".TB_PREF."suppliers.contact, ".TB_PREF."suppliers.tax_group_id - FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations - WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id - AND ".TB_PREF."locations.loc_code = into_stock_location - AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no); + $sql = "SELECT po.*, supplier.supp_name, supplier.supp_account_no,supplier.tax_included, + supplier.gst_no AS tax_id, + supplier.curr_code, supplier.payment_terms, loc.location_name, + supplier.address, supplier.contact, supplier.tax_group_id + FROM ".TB_PREF."purch_orders po," + .TB_PREF."suppliers supplier," + .TB_PREF."locations loc + WHERE po.supplier_id = supplier.supplier_id + AND loc.loc_code = into_stock_location + AND po.order_no = ".db_escape($order_no); $result = db_query($sql, "The order cannot be retrieved"); return db_fetch($result); } function get_po_details($order_no) { - $sql = "SELECT ".TB_PREF."purch_order_details.*, units - FROM ".TB_PREF."purch_order_details - LEFT JOIN ".TB_PREF."stock_master - ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id + $sql = "SELECT poline.*, units + FROM ".TB_PREF."purch_order_details poline + LEFT JOIN ".TB_PREF."stock_master item ON poline.item_code=item.stock_id WHERE order_no =".db_escape($order_no)." "; $sql .= " ORDER BY po_detail_item"; return db_query($sql, "Retreive order Line Items"); diff --git a/reporting/rep210.php b/reporting/rep210.php index 9f14a1ae..98b4a9b0 100644 --- a/reporting/rep210.php +++ b/reporting/rep210.php @@ -32,16 +32,18 @@ print_remittances(); //---------------------------------------------------------------------------------------------------- function get_remittance($type, $trans_no) { - $sql = "SELECT ".TB_PREF."supp_trans.*, - (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst) AS Total, - ".TB_PREF."supp_trans.ov_discount, - ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.supp_account_no, - ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.payment_terms, ".TB_PREF."suppliers.gst_no AS tax_id, - ".TB_PREF."suppliers.address - FROM ".TB_PREF."supp_trans, ".TB_PREF."suppliers - WHERE ".TB_PREF."supp_trans.supplier_id = ".TB_PREF."suppliers.supplier_id - AND ".TB_PREF."supp_trans.type = ".db_escape($type)." - AND ".TB_PREF."supp_trans.trans_no = ".db_escape($trans_no); + $sql = "SELECT trans.*, + (trans.ov_amount+trans.ov_gst) AS Total, + trans.ov_discount, + supplier.supp_name, supplier.supp_account_no, + supplier.curr_code, supplier.payment_terms, supplier.gst_no AS tax_id, + supplier.address + FROM " + .TB_PREF."supp_trans trans," + .TB_PREF."suppliers supplier + WHERE trans.supplier_id = supplier.supplier_id + AND trans.type = ".db_escape($type)." + AND trans.trans_no = ".db_escape($trans_no); $result = db_query($sql, "The remittance cannot be retrieved"); if (db_num_rows($result) == 0) return false; diff --git a/reporting/rep301.php b/reporting/rep301.php index e2e568c8..0c8ce806 100644 --- a/reporting/rep301.php +++ b/reporting/rep301.php @@ -36,7 +36,9 @@ function get_domestic_price($myrow, $stock_id, $qty, $old_std_cost, $old_qty) if ($myrow['type'] == ST_SUPPRECEIVE) { // Has the supplier invoice increased the receival price? - $sql = "SELECT DISTINCT act_price FROM ".TB_PREF."purch_order_details pod INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no = + $sql = "SELECT DISTINCT act_price + FROM ".TB_PREF."purch_order_details pod + INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no = grn.purch_order_no WHERE grn.id = ".$myrow['trans_no']." AND pod.item_code = '$stock_id'"; $result = db_query($sql, "Could not retrieve act_price from purch_order_details"); $row = db_fetch_row($result); @@ -91,40 +93,41 @@ function getAverageCost($stock_id, $to_date) return 0; return $tot_cost / $count; } - + function getTransactions($category, $location, $date) { $date = date2sql($date); - - $sql = "SELECT ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description AS cat_description, - ".TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.units, - ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive, - ".TB_PREF."stock_moves.loc_code, - SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand, - ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost, - SUM(".TB_PREF."stock_moves.qty) *(".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost) AS ItemTotal - FROM ".TB_PREF."stock_master, - ".TB_PREF."stock_category, - ".TB_PREF."stock_moves - WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id - AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id - AND ".TB_PREF."stock_master.mb_flag<>'D' - AND ".TB_PREF."stock_moves.tran_date <= '$date' - GROUP BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description, "; + + $sql = "SELECT item.category_id, + category.description AS cat_description, + item.stock_id, + item.units, + item.description, item.inactive, + move.loc_code, + SUM(move.qty) AS QtyOnHand, + item.material_cost + item.labour_cost + item.overhead_cost AS UnitCost, + SUM(move.qty) *(item.material_cost + item.labour_cost + item.overhead_cost) AS ItemTotal + FROM " + .TB_PREF."stock_master item," + .TB_PREF."stock_category category," + .TB_PREF."stock_moves move + WHERE item.stock_id=move.stock_id + AND item.category_id=category.category_id + AND item.mb_flag<>'D' + AND move.tran_date <= '$date' + GROUP BY item.category_id, + category.description, "; if ($location != 'all') - $sql .= TB_PREF."stock_moves.loc_code, "; - $sql .= TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.description - HAVING SUM(".TB_PREF."stock_moves.qty) != 0"; + $sql .= "move.loc_code, "; + $sql .= "item.stock_id, + item.description + HAVING SUM(move.qty) != 0"; if ($category != 0) - $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category); + $sql .= " AND item.category_id = ".db_escape($category); if ($location != 'all') - $sql .= " AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location); - $sql .= " ORDER BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_master.stock_id"; + $sql .= " AND move.loc_code = ".db_escape($location); + $sql .= " ORDER BY item.category_id, + item.stock_id"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep302.php b/reporting/rep302.php index 55c766c0..bfb515f1 100644 --- a/reporting/rep302.php +++ b/reporting/rep302.php @@ -31,28 +31,27 @@ print_inventory_planning(); function getTransactions($category, $location) { - $sql = "SELECT ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description AS cat_description, - ".TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive, - IF(".TB_PREF."stock_moves.stock_id IS NULL, '', ".TB_PREF."stock_moves.loc_code) AS loc_code, - SUM(IF(".TB_PREF."stock_moves.stock_id IS NULL,0,".TB_PREF."stock_moves.qty)) AS qty_on_hand - FROM (".TB_PREF."stock_master, - ".TB_PREF."stock_category) - LEFT JOIN ".TB_PREF."stock_moves ON - (".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id) - WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id - AND (".TB_PREF."stock_master.mb_flag='B' OR ".TB_PREF."stock_master.mb_flag='M')"; + $sql = "SELECT item.category_id, + category.description AS cat_description, + item.stock_id, + item.description, item.inactive, + IF(move.stock_id IS NULL, '', move.loc_code) AS loc_code, + SUM(IFNULL(move.stock_id, 0)) AS qty_on_hand + FROM (".TB_PREF."stock_master item," + .TB_PREF."stock_category category) + LEFT JOIN ".TB_PREF."stock_moves move ON item.stock_id=move.stock_id + WHERE item.category_id=category.category_id + AND (item.mb_flag='B' OR item.mb_flag='M')"; if ($category != 0) - $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category); + $sql .= " AND item.category_id = ".db_escape($category); if ($location != 'all') - $sql .= " AND IF(".TB_PREF."stock_moves.stock_id IS NULL, '1=1',".TB_PREF."stock_moves.loc_code = ".db_escape($location).")"; - $sql .= " GROUP BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description, - ".TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.description - ORDER BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_master.stock_id"; + $sql .= " AND IF(move.stock_id IS NULL, '1=1',move.loc_code = ".db_escape($location).")"; + $sql .= " GROUP BY item.category_id, + category.description, + item.stock_id, + item.description + ORDER BY item.category_id, + item.stock_id"; return db_query($sql,"No transactions were returned"); diff --git a/reporting/rep303.php b/reporting/rep303.php index 9794e8c7..231e33d5 100644 --- a/reporting/rep303.php +++ b/reporting/rep303.php @@ -31,38 +31,37 @@ print_stock_check(); function getTransactions($category, $location, $item_like) { - $sql = "SELECT ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description AS cat_description, - ".TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.units, - ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive, - IF(".TB_PREF."stock_moves.stock_id IS NULL, '', ".TB_PREF."stock_moves.loc_code) AS loc_code, - SUM(IF(".TB_PREF."stock_moves.stock_id IS NULL,0,".TB_PREF."stock_moves.qty)) AS QtyOnHand - FROM (".TB_PREF."stock_master, - ".TB_PREF."stock_category) - LEFT JOIN ".TB_PREF."stock_moves ON - (".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id) - WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id - AND (".TB_PREF."stock_master.mb_flag='B' OR ".TB_PREF."stock_master.mb_flag='M')"; + $sql = "SELECT item.category_id, + category.description AS cat_description, + item.stock_id, item.units, + item.description, item.inactive, + IF(move.stock_id IS NULL, '', move.loc_code) AS loc_code, + SUM(IF(move.stock_id IS NULL,0,move.qty)) AS QtyOnHand + FROM (" + .TB_PREF."stock_master item," + .TB_PREF."stock_category category) + LEFT JOIN ".TB_PREF."stock_moves move ON item.stock_id=move.stock_id + WHERE item.category_id=category.category_id + AND (item.mb_flag='B' OR item.mb_flag='M')"; if ($category != 0) - $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category); + $sql .= " AND item.category_id = ".db_escape($category); if ($location != 'all') - $sql .= " AND IF(".TB_PREF."stock_moves.stock_id IS NULL, '1=1',".TB_PREF."stock_moves.loc_code = ".db_escape($location).")"; + $sql .= " AND IF(move.stock_id IS NULL, '1=1',move.loc_code = ".db_escape($location).")"; if($item_like) { $regexp = null; if(sscanf($item_like, "/%s", $regexp)==1) - $sql .= " AND ".TB_PREF."stock_master.stock_id RLIKE ".db_escape($regexp); + $sql .= " AND item.stock_id RLIKE ".db_escape($regexp); else - $sql .= " AND ".TB_PREF."stock_master.stock_id LIKE ".db_escape($item_like); + $sql .= " AND item.stock_id LIKE ".db_escape($item_like); } - $sql .= " GROUP BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_category.description, - ".TB_PREF."stock_master.stock_id, - ".TB_PREF."stock_master.description - ORDER BY ".TB_PREF."stock_master.category_id, - ".TB_PREF."stock_master.stock_id"; + $sql .= " GROUP BY item.category_id, + category.description, + item.stock_id, + item.description + ORDER BY item.category_id, + item.stock_id"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep305.php b/reporting/rep305.php index 8b71e0fb..4cd9229d 100644 --- a/reporting/rep305.php +++ b/reporting/rep305.php @@ -34,25 +34,26 @@ function getTransactions($from, $to) $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT ".TB_PREF."grn_batch.id batch_no, - ".TB_PREF."grn_batch.supplier_id, - ".TB_PREF."purch_order_details.*, - ".TB_PREF."stock_master.description, - ".TB_PREF."grn_items.qty_recd, - ".TB_PREF."grn_items.quantity_inv, - ".TB_PREF."grn_items.id grn_item_id - FROM ".TB_PREF."stock_master, - ".TB_PREF."purch_order_details, - ".TB_PREF."grn_batch, - ".TB_PREF."grn_items - WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."purch_order_details.item_code - AND ".TB_PREF."grn_batch.purch_order_no=".TB_PREF."purch_order_details.order_no - AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id - AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item - AND ".TB_PREF."grn_items.qty_recd>0 - AND ".TB_PREF."grn_batch.delivery_date>='$from' - AND ".TB_PREF."grn_batch.delivery_date<='$to' - ORDER BY ".TB_PREF."stock_master.stock_id, ".TB_PREF."grn_batch.delivery_date"; + $sql = "SELECT grn.id batch_no, + grn.supplier_id, + poline.*, + item.description, + grn_line.qty_recd, + grn_line.quantity_inv, + grn_line.id grn_item_id + FROM " + .TB_PREF."stock_master item," + .TB_PREF."purch_order_details poline," + .TB_PREF."grn_batch grn," + .TB_PREF."grn_items grn_line + WHERE item.stock_id=poline.item_code + AND grn.purch_order_no=poline.order_no + AND grn.id = grn_line.grn_batch_id + AND grn_line.po_detail_item = poline.po_detail_item + AND grn_line.qty_recd>0 + AND grn.delivery_date>='$from' + AND grn.delivery_date<='$to' + ORDER BY item.stock_id, grn.delivery_date"; return db_query($sql,"No transactions were returned"); @@ -61,19 +62,22 @@ function getTransactions($from, $to) function getSuppInvDetails($grn_item_id) { $sql = "SELECT - ".TB_PREF."supp_invoice_items.supp_trans_no inv_no, - ".TB_PREF."supp_invoice_items.quantity inv_qty, - ".TB_PREF."supp_trans.rate, - IF (".TB_PREF."supp_trans.tax_included = 1, ".TB_PREF."supp_invoice_items.unit_price - ".TB_PREF."supp_invoice_items.unit_tax, ".TB_PREF."supp_invoice_items.unit_price) inv_price - FROM ".TB_PREF."grn_items, ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items - WHERE ".TB_PREF."grn_items.id = ".TB_PREF."supp_invoice_items.grn_item_id - AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."supp_invoice_items.po_detail_item_id - AND ".TB_PREF."grn_items.item_code = ".TB_PREF."supp_invoice_items.stock_id - AND ".TB_PREF."supp_trans.type = ".TB_PREF."supp_invoice_items.supp_trans_type - AND ".TB_PREF."supp_trans.trans_no = ".TB_PREF."supp_invoice_items.supp_trans_no - AND ".TB_PREF."supp_invoice_items.supp_trans_type = 20 - AND ".TB_PREF."supp_invoice_items.grn_item_id = ".$grn_item_id." - ORDER BY ".TB_PREF."supp_invoice_items.id asc"; + inv_line.supp_trans_no inv_no, + inv_line.quantity inv_qty, + inv.rate, + IF (inv.tax_included = 1, inv_line.unit_price - inv_line.unit_tax, inv_line.unit_price) inv_price + FROM " + .TB_PREF."grn_items grn_line," + .TB_PREF."supp_trans inv," + .TB_PREF."supp_invoice_items inv_line + WHERE grn_line.id = inv_line.grn_item_id + AND grn_line.po_detail_item = inv_line.po_detail_item_id + AND grn_line.item_code = inv_line.stock_id + AND inv.type = inv_line.supp_trans_type + AND inv.trans_no = inv_line.supp_trans_no + AND inv_line.supp_trans_type = 20 + AND inv_line.grn_item_id = ".$grn_item_id." + ORDER BY inv_line.id asc"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep401.php b/reporting/rep401.php index eac5001e..faab857a 100644 --- a/reporting/rep401.php +++ b/reporting/rep401.php @@ -30,21 +30,21 @@ print_bill_of_material(); function getTransactions($from, $to) { - $sql = "SELECT ".TB_PREF."bom.parent, - ".TB_PREF."bom.component, - ".TB_PREF."stock_master.description as CompDescription, - ".TB_PREF."bom.quantity, - ".TB_PREF."bom.loc_code, - ".TB_PREF."bom.workcentre_added - FROM - ".TB_PREF."stock_master, - ".TB_PREF."bom - WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component - AND ".TB_PREF."bom.parent >= ".db_escape($from)." - AND ".TB_PREF."bom.parent <= ".db_escape($to)." + $sql = "SELECT bom.parent, + bom.component, + item.description as CompDescription, + bom.quantity, + bom.loc_code, + bom.workcentre_added + FROM " + .TB_PREF."stock_master item," + .TB_PREF."bom bom + WHERE item.stock_id=bom.component + AND bom.parent >= ".db_escape($from)." + AND bom.parent <= ".db_escape($to)." ORDER BY - ".TB_PREF."bom.parent, - ".TB_PREF."bom.component"; + bom.parent, + bom.component"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep602.php b/reporting/rep602.php index 5c9b324f..788bedbb 100644 --- a/reporting/rep602.php +++ b/reporting/rep602.php @@ -44,14 +44,14 @@ function get_bank_transactions($from, $to, $account) { $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT ".TB_PREF."bank_trans.*, ".TB_PREF."comments.memo_ - FROM ".TB_PREF."bank_trans LEFT JOIN ".TB_PREF."comments ON - (".TB_PREF."bank_trans.type = ".TB_PREF."comments.type - AND ".TB_PREF."bank_trans.trans_no = ".TB_PREF."comments.id) - WHERE ".TB_PREF."bank_trans.bank_act = '$account' + $sql = "SELECT trans.*, com.memo_ + FROM " + .TB_PREF."bank_trans trans + LEFT JOIN ".TB_PREF."comments com ON trans.type = com.type AND trans.trans_no = com.id + WHERE trans.bank_act = '$account' AND trans_date >= '$from' AND trans_date <= '$to' - ORDER BY trans_date,".TB_PREF."bank_trans.id"; + ORDER BY trans_date,trans.id"; return db_query($sql,"The transactions for '$account' could not be retrieved"); } diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index 80f00a6d..48cd417d 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -81,13 +81,15 @@ function branch_in_foreign_table($customer_id, $branch_code, $table) function get_branch($branch_id) { - $sql = "SELECT ".TB_PREF."cust_branch.*,".TB_PREF."salesman.salesman_name - FROM ".TB_PREF."cust_branch, ".TB_PREF."salesman - WHERE ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code - AND branch_code=".db_escape($branch_id); - + $sql = "SELECT branch.*, salesman.salesman_name + FROM " + .TB_PREF."cust_branch branch," + .TB_PREF."salesman salesman + WHERE branch.salesman=salesman.salesman_code + AND branch_code=".db_escape($branch_id); + $result = db_query($sql, "Cannot retreive a customer branch"); - + return db_fetch($result); } @@ -184,8 +186,10 @@ function get_branch_contacts($branch_code, $action=null, $customer_id=null, $def 'customer.general'); $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type - FROM ".TB_PREF."crm_persons p," - .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND ((r.type='cust_branch' + FROM " + .TB_PREF."crm_persons p," + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND ((r.type='cust_branch' AND r.entity_id=".db_escape($branch_code).')'; if($customer_id) { $sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")"; @@ -216,7 +220,8 @@ function _get_branch_contacts($branch_code, $action=null, $customer_id=null, $de { $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type FROM ".TB_PREF."crm_persons p," - .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch' + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND r.type='cust_branch' AND r.entity_id=".db_escape($branch_code); if ($action) @@ -225,13 +230,14 @@ function _get_branch_contacts($branch_code, $action=null, $customer_id=null, $de if($customer_id) { $sql = "($sql) UNION (SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type FROM ".TB_PREF."crm_persons p," - .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='customer' + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND r.type='customer' AND r.entity_id=".db_escape($customer_id); if ($action) $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')'; $sql .= ')'; } - + $res = db_query($sql, "can't retrieve branch contacts"); $results = array(); $type = ''; diff --git a/sales/includes/db/credit_status_db.inc b/sales/includes/db/credit_status_db.inc index b011b301..c80cdc90 100644 --- a/sales/includes/db/credit_status_db.inc +++ b/sales/includes/db/credit_status_db.inc @@ -13,16 +13,16 @@ function add_credit_status($description, $disallow_invoicing) { $sql = "INSERT INTO ".TB_PREF."credit_status (reason_description, dissallow_invoices) VALUES (".db_escape($description).",".db_escape($disallow_invoicing).")"; - - db_query($sql, "could not add credit status"); + + db_query($sql, "could not add credit status"); } function update_credit_status($status_id, $description, $disallow_invoicing) { $sql = "UPDATE ".TB_PREF."credit_status SET reason_description=".db_escape($description).", dissallow_invoices=".db_escape($disallow_invoicing)." WHERE id=".db_escape($status_id); - - db_query($sql, "could not update credit status"); + + db_query($sql, "could not update credit status"); } function get_all_credit_status($all=false) @@ -31,21 +31,21 @@ function get_all_credit_status($all=false) if (!$all) $sql .= " WHERE !inactive"; return db_query($sql, "could not get all credit status"); -} +} function get_credit_status($status_id) { $sql = "SELECT * FROM ".TB_PREF."credit_status WHERE id=".db_escape($status_id); - + $result = db_query($sql, "could not get credit status"); - + return db_fetch($result); } function delete_credit_status($status_id) { $sql="DELETE FROM ".TB_PREF."credit_status WHERE id=".db_escape($status_id); - - db_query($sql, "could not delete credit status"); + + db_query($sql, "could not delete credit status"); } diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index d6eddbe0..a962ba28 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -250,11 +250,14 @@ function get_customer_trans_order($type, $type_no) function get_customer_details_from_trans($type, $type_no) { - $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."cust_branch.br_name - FROM ".TB_PREF."debtors_master,".TB_PREF."cust_branch,".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.type=".db_escape($type)." AND ".TB_PREF."debtor_trans.trans_no=".db_escape($type_no)." - AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no - AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."debtor_trans.branch_code"; + $sql = "SELECT debtor.name, debtor.curr_code, branch.br_name + FROM " + .TB_PREF."debtors_master debtor," + .TB_PREF."cust_branch branch," + .TB_PREF."debtor_trans trans + WHERE trans.type=".db_escape($type)." AND trans.trans_no=".db_escape($type_no)." + AND debtor.debtor_no = trans.debtor_no + AND branch.branch_code = trans.branch_code"; $result = db_query($sql, "could not get customer details from trans"); return db_fetch($result); diff --git a/sales/includes/db/cust_trans_details_db.inc b/sales/includes/db/cust_trans_details_db.inc index a4c76b11..ffef4e51 100644 --- a/sales/includes/db/cust_trans_details_db.inc +++ b/sales/includes/db/cust_trans_details_db.inc @@ -16,11 +16,13 @@ function get_customer_trans_details($debtor_trans_type, $debtor_trans_no) if (!is_array($debtor_trans_no)) $debtor_trans_no = array( 0=>$debtor_trans_no ); - $sql = "SELECT ".TB_PREF."debtor_trans_details.*, - ".TB_PREF."debtor_trans_details.unit_price+".TB_PREF."debtor_trans_details.unit_tax AS FullUnitPrice, - ".TB_PREF."debtor_trans_details.description As StockDescription, - ".TB_PREF."stock_master.units, ".TB_PREF."stock_master.mb_flag - FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."stock_master + $sql = "SELECT line.*, + line.unit_price+line.unit_tax AS FullUnitPrice, + line.description As StockDescription, + item.units, item.mb_flag + FROM " + .TB_PREF."debtor_trans_details line," + .TB_PREF."stock_master item WHERE ("; $tr=array(); @@ -31,7 +33,7 @@ if (!is_array($debtor_trans_no)) $sql.= ") AND debtor_trans_type=".db_escape($debtor_trans_type)." - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."debtor_trans_details.stock_id + AND item.stock_id=line.stock_id ORDER BY id"; return db_query($sql, "The debtor transaction detail could not be queried"); } diff --git a/sales/includes/db/recurrent_invoices_db.inc b/sales/includes/db/recurrent_invoices_db.inc index a80aac07..7d74dd20 100644 --- a/sales/includes/db/recurrent_invoices_db.inc +++ b/sales/includes/db/recurrent_invoices_db.inc @@ -108,13 +108,15 @@ function recurrent_invoice_ready($selected_id, $date) function recurrent_invoice_count($id) { - $sql1 = "SELECT branch.* FROM ".TB_PREF."recurrent_invoices rec - LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive + $sql1 = "SELECT branch.* + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); - $sql2 = "SELECT branch.* FROM ".TB_PREF."recurrent_invoices rec - LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive + $sql2 = "SELECT branch.* + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); @@ -129,13 +131,15 @@ function check_recurrent_invoice_prices($id) $errors = 0; $inv = get_recurrent_invoice($id); - $sql1 = "SELECT debtor.curr_code FROM ".TB_PREF."recurrent_invoices rec - LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive + $sql1 = "SELECT debtor.curr_code + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); - $sql2 = "SELECT debtor.curr_code FROM ".TB_PREF."recurrent_invoices rec - LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive + $sql2 = "SELECT debtor.curr_code + FROM ".TB_PREF."recurrent_invoices rec + LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id); -- 2.30.2