From 3faff3b471c0953bc1d11a6c00308779787a55f2 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sat, 27 Dec 2014 21:01:24 +0100 Subject: [PATCH] Cleanup: various random sql queries found in UI files moved to database interface files --- admin/crm_categories.php | 11 +- admin/db/company_db.inc | 23 ++++ admin/db/transactions_db.inc | 159 ++++++++++++++++++++++++ admin/inst_theme.php | 23 +--- gl/includes/db/gl_db_accounts.inc | 20 ++- gl/includes/db/gl_db_bank_accounts.inc | 15 ++- gl/includes/db/gl_db_rates.inc | 19 +++ includes/banking.inc | 65 +--------- includes/db/crm_contacts_db.inc | 13 ++ includes/db/references_db.inc | 44 +++++++ includes/main.inc | 1 - includes/references.inc | 44 ------- includes/systypes.inc | 83 ------------- includes/types.inc | 2 +- manufacturing/work_order_entry.php | 11 +- purchasing/includes/db/po_db.inc | 32 +++++ purchasing/includes/db/suppliers_db.inc | 11 ++ purchasing/includes/ui/po_ui.inc | 39 +----- reporting/includes/doctext.inc | 5 +- sales/customer_invoice.php | 7 +- sales/includes/db/customers_db.inc | 13 +- sales/includes/db/sales_order_db.inc | 10 ++ sales/inquiry/sales_orders_view.php | 18 +-- sales/sales_order_entry.php | 9 +- 24 files changed, 379 insertions(+), 298 deletions(-) delete mode 100644 includes/systypes.inc diff --git a/admin/crm_categories.php b/admin/crm_categories.php index 372faa71..59166a3a 100644 --- a/admin/crm_categories.php +++ b/admin/crm_categories.php @@ -52,20 +52,11 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') } } -function key_in_crm_contacts($id) // extra function for testing foreign concatenated key. Joe 02.09.2013. -{ - $row = get_crm_category($id); - $sql = "SELECT COUNT(*) FROM ".TB_PREF."crm_contacts WHERE type='".$row['type']."' AND action='".$row['action']."'"; - $result = db_query($sql, "check relations for crm_contacts failed"); - $contacts = db_fetch($result); - return $contacts[0]; -} - if ($Mode == 'Delete') { $cancel_delete = 0; - if (key_in_crm_contacts($selected_id)) + if (is_crm_category_used($selected_id)) { $cancel_delete = 1; display_error(_("Cannot delete this category because there are contacts related to it.")); diff --git a/admin/db/company_db.inc b/admin/db/company_db.inc index d0f85e40..e1ff68aa 100644 --- a/admin/db/company_db.inc +++ b/admin/db/company_db.inc @@ -193,4 +193,27 @@ function key_in_foreign_table($id, $tables, $stdkey) return $count[0]; } +//--------------------------------------------------------------------------------------------- +// +// Resets $theme references in users records to 'default'. +// +function clean_user_themes($theme) +{ + global $db_connections, $db, $installed_extensions; + + $comp = user_company(); + + foreach ($db_connections as $n => $conn) { + $db = $_SESSION["wa_current_user"]->set_db_connection($n); + $sql = "UPDATE {$conn['tbpref']}users SET theme='default' WHERE theme='$theme'"; + if (!db_query($sql, 'Cannot update user theme settings')) + return false; + } + $db = $_SESSION["wa_current_user"]->set_db_connection($comp); + + $_SESSION['wa_current_user']->prefs->theme = 'default'; + return true; +} + + ?> \ No newline at end of file diff --git a/admin/db/transactions_db.inc b/admin/db/transactions_db.inc index 19d22970..f59934fb 100644 --- a/admin/db/transactions_db.inc +++ b/admin/db/transactions_db.inc @@ -64,4 +64,163 @@ function transaction_exists($trans_type, $trans_no) { return db_num_rows(db_query(get_sql_for_view_transactions($trans_type, $trans_no, $trans_no, $dummy))); } + +// +// Returns counterparty (supplier/customer) name for selected transaction. +// +function get_counterparty_name($trans_type, $trans_no, $full=true) +{ + switch($trans_type) + { + case ST_SALESORDER: + case ST_SALESQUOTE: + $sql = "SELECT order.customer_id as person_id, debtor.name as name + FROM ".TB_PREF."sales_orders order, ".TB_PREF."debtors_master debtor + WHERE order_no=".db_escape($trans_no)." AND trans_type=".db_escape($trans_type) + ." AND order.debtor_no=debtor.debtor_no"; + break; + + case ST_RECEIPTINVOICE : + case ST_SALESINVOICE : + case ST_CUSTCREDIT : + case ST_CUSTPAYMENT : + case ST_CUSTDELIVERY : + $sql = "SELECT trans.debtor_no as person_id, debtor.name as name + FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master debtor + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.debtor_no=debtor.debtor_no"; + break; + + case ST_PURCHORDER : + $sql = "SELECT order.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."purch_orders order, ".TB_PREF."suppliers supp + WHERE order_no=".db_escape($trans_no) + ." AND order.supplier_id=supp.supplier_id"; + break; + + case ST_SUPPINVOICE : + case ST_SUPPCREDIT : + case ST_SUPPAYMENT : + $sql = "SELECT trans.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."supp_trans trans, ".TB_PREF."suppliers supp + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.supplier_id=supp.supplier_id"; + break; + + case ST_SUPPRECEIVE : + $sql = "SELECT trans.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."grn_batch trans, ".TB_PREF."suppliers supp + WHERE id=".db_escape($trans_no) + ." AND trans.supplier_id=supp.supplier_id"; + break; + + case ST_BANKPAYMENT : + case ST_BANKDEPOSIT : + $sql = "SELECT trans.debtor_no as person_id, debtor.name as name + FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master debtor + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.debtor_no=debtor.debtor_no + UNION + SELECT trans.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."supp_trans trans, ".TB_PREF."suppliers supp + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.supplier_id=supp.supplier_id"; + break; + + case ST_JOURNAL: // FIXME - this one can have multiply counterparties of various types depending on person_type_id + + default: + /* // internal operations + case ST_WORKORDER : + case ST_INVADJUST : // GRN/DN returns ? + case ST_BANKTRANSFER : + case ST_LOCTRANSFER : + case ST_MANUISSUE : + case ST_MANURECEIVE : + case ST_COSTUPDATE : + */ + return null; + } + + $result = db_query($sql, 'cannot retrieve counterparty name'); + if (db_num_rows($result)) + { + $row = db_fetch($result); + return sprintf("[%05s] %s", $row['person_id'], $row['name']); + } + + return ''; +} + + +//----------------------------------------------------------------------------------------- +// Returns next transaction number. +// Used only for transactions stored in tables without autoincremented key. +// + +function get_next_trans_no ($trans_type){ + + $st = get_systype_db_info($trans_type); + + if (!($st && $st[0] && $st[2])) { + // this is in fact internal error condition. + display_error('Internal error: invalid type passed to get_next_trans_no()'); + return 0; + } + $sql1 = "SELECT MAX(`$st[2]`) as last_no FROM $st[0]"; + if ($st[1] != null) + $sql1 .= " WHERE `$st[1]`=".db_escape($trans_type); + + // check also in voided transactions (some transactions like location transfer are removed completely) + $sql2 = "SELECT MAX(`id`) as last_no FROM ".TB_PREF."voided WHERE `type`=".db_escape($trans_type); + + $sql = "SELECT max(last_no) last_no FROM ($sql1 UNION $sql2) a"; + $result = db_query($sql,"The next transaction number for $trans_type could not be retrieved"); + $myrow = db_fetch_row($result); + + return $myrow[0] + 1; +} + +//----------------------------------------------------------------------------- + +function get_systype_db_info($type) +{ + switch ($type) + { + case ST_JOURNAL : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); + case ST_BANKPAYMENT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case ST_BANKDEPOSIT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case 3 : return null; + case ST_BANKTRANSFER : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case ST_SALESINVOICE : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTCREDIT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTPAYMENT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTDELIVERY : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_LOCTRANSFER : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); + case ST_INVADJUST : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); + case ST_PURCHORDER : return array("".TB_PREF."purch_orders", null, "order_no", "reference", "ord_date"); + case ST_SUPPINVOICE : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPCREDIT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPAYMENT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPRECEIVE : return array("".TB_PREF."grn_batch", null, "id", "reference", "delivery_date"); + case ST_WORKORDER : return array("".TB_PREF."workorders", null, "id", "wo_ref", "released_date"); + case ST_MANUISSUE : return array("".TB_PREF."wo_issues", null, "issue_no", "reference", "issue_date"); + case ST_MANURECEIVE : return array("".TB_PREF."wo_manufacture", null, "id", "reference", "date_"); + case ST_SALESORDER : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); + case 31 : return array("".TB_PREF."service_orders", null, "order_no", "cust_ref", "date"); + case ST_SALESQUOTE : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); + case ST_DIMENSION : return array("".TB_PREF."dimensions", null, "id", "reference", "date_"); + case ST_COSTUPDATE : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); + } + + display_db_error("invalid type ($type) sent to get_systype_db_info", "", true); +} + +function get_systypes() +{ + $sql = "SELECT * FROM ".TB_PREF."sys_types"; + $result = db_query($sql, "could not query systypes table"); + return $result; +} + ?> diff --git a/admin/inst_theme.php b/admin/inst_theme.php index af640e88..01f6ca1a 100644 --- a/admin/inst_theme.php +++ b/admin/inst_theme.php @@ -25,28 +25,9 @@ include_once($path_to_root . "/admin/db/maintenance_db.inc"); include_once($path_to_root . "/includes/ui.inc"); //--------------------------------------------------------------------------------------------- -// If theme is used in customer record set to default -// -function clean_user_themes($id) -{ - global $db_connections, $db, $installed_extensions; - - $theme = $installed_extensions[$id]['package']; - $comp = user_company(); - - foreach ($db_connections as $n => $conn) { - $db = $_SESSION["wa_current_user"]->set_db_connection($n); - $sql = "UPDATE {$conn['tbpref']}users SET theme='default' WHERE theme='$theme'"; - if (!db_query($sql, 'Cannot update user theme settings')) - return false; - } - $db = $_SESSION["wa_current_user"]->set_db_connection($comp); - - $_SESSION['wa_current_user']->prefs->theme = 'default'; - return true; -} -if (($id = find_submit('Delete', false)) && clean_user_themes($id)) +if (($id = find_submit('Delete', false)) + && clean_user_themes($installed_extensions[$id]['package'])) { $extensions = get_company_extensions(); $theme = $extensions[$id]['package']; diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index ce11a325..00b8fbe3 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -227,4 +227,22 @@ function gl_account_in_bank_accounts($acc) $myrow = db_fetch_row($result); return ($myrow[0] > 0); } -?> \ No newline at end of file + +//---------------------------------------------------------------------------------- +// Check if given account is used by any bank_account. +// Returns id of first bank_account using account_code, null otherwise. +// +// Keep in mind that direct posting to bank account is depreciated +// because we have no way to select right bank account if +// there is more than one using given gl account. +// +function is_bank_account($account_code) +{ + $sql= "SELECT id FROM ".TB_PREF."bank_accounts WHERE account_code=".db_escape($account_code); + $result = db_query($sql, "checking account is bank account"); + if (db_num_rows($result) > 0) { + $acct = db_fetch($result); + return $acct['id']; + } else + return false; +} diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index a4ffd986..5facce63 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -308,7 +308,7 @@ function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_da } //--------------------------------------------------------------------------------------------- -function get_default_bank_account($curr) +function get_default_bank_account($curr=null) { /* default bank account is selected as first found account from: . default account in $curr if any @@ -317,6 +317,8 @@ function get_default_bank_account($curr) . first defined account in home currency */ $home_curr = get_company_pref('curr_default'); + if (!isset($curr)) + $curr = $home_curr; $sql = "SELECT b.*, b.bank_curr_code='$home_curr' as fall_back FROM " .TB_PREF."bank_accounts b" @@ -376,4 +378,15 @@ function identify_bank_account($account_number) return null; } +//---------------------------------------------------------------------------------- + +function get_bank_account_currency($id) +{ + $sql= "SELECT bank_curr_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); + $result = db_query($sql, "retreive bank account currency"); + + $myrow = db_fetch_row($result); + return $myrow[0]; +} + ?> \ No newline at end of file diff --git a/gl/includes/db/gl_db_rates.inc b/gl/includes/db/gl_db_rates.inc index c37eaa03..9a64fcfa 100644 --- a/gl/includes/db/gl_db_rates.inc +++ b/gl/includes/db/gl_db_rates.inc @@ -33,6 +33,25 @@ function get_date_exchange_rate($curr_code, $date_) return $row[0]; } +/* + Return last exchange rate of $currency not later than $date. +*/ +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)." + AND date_ <= '$date' GROUP BY rate_buy ORDER BY date_ Desc LIMIT 1"; + + $result = db_query($sql, "could not query exchange rates"); + + + if (db_num_rows($result) == 0) + return false; + + return db_fetch_row($result); +} + //--------------------------------------------------------------------------------------------- function update_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) diff --git a/includes/banking.inc b/includes/banking.inc index 53af3598..515da8b0 100644 --- a/includes/banking.inc +++ b/includes/banking.inc @@ -11,25 +11,6 @@ ***********************************************************************/ include_once($path_to_root . "/gl/includes/gl_db.inc"); -//---------------------------------------------------------------------------------- -// Check if given account is used by any bank_account. -// Returns id of first bank_account using account_code, null otherwise. -// -// Keep in mind that direct posting to bank account is depreciated -// because we have no way to select right bank account if -// there is more than one using given gl account. -// -function is_bank_account($account_code) -{ - $sql= "SELECT id FROM ".TB_PREF."bank_accounts WHERE account_code=".db_escape($account_code); - $result = db_query($sql, "checking account is bank account"); - if (db_num_rows($result) > 0) { - $acct = db_fetch($result); - return $acct['id']; - } else - return false; -} - //---------------------------------------------------------------------------------- function is_company_currency($currency) @@ -46,54 +27,15 @@ function get_company_currency() //---------------------------------------------------------------------------------- -function get_bank_account_currency($id) -{ - $sql= "SELECT bank_curr_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); - $result = db_query($sql, "retreive bank account currency"); - - $myrow = db_fetch_row($result); - return $myrow[0]; -} - -//---------------------------------------------------------------------------------- - -function get_customer_currency($customer_id) -{ - $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id); - - $result = db_query($sql, "Retreive currency of customer $customer_id"); - - $myrow=db_fetch_row($result); - return $myrow[0]; -} - -//---------------------------------------------------------------------------------- - -function get_supplier_currency($supplier_id) -{ - $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id = ".db_escape($supplier_id); - - $result = db_query($sql, "Retreive currency of supplier $supplier_id"); - - $myrow=db_fetch_row($result); - return $myrow[0]; -} - -//---------------------------------------------------------------------------------- - function get_exchange_rate_from_home_currency($currency_code, $date_) { if ($currency_code == get_company_currency() || $currency_code == null) return 1.0000; - $date = date2sql($date_); - - $sql = "SELECT rate_buy, max(date_) as date_ FROM ".TB_PREF."exchange_rates WHERE curr_code = ".db_escape($currency_code)." - AND date_ <= '$date' GROUP BY rate_buy ORDER BY date_ Desc LIMIT 1"; - $result = db_query($sql, "could not query exchange rates"); + $rate = get_last_exchange_rate($currency_code, $date_); - if (db_num_rows($result) == 0) + if (!$rate) { // no stored exchange rate, just return 1 display_error( @@ -102,8 +44,7 @@ function get_exchange_rate_from_home_currency($currency_code, $date_) return 1.000; } - $myrow = db_fetch_row($result); - return $myrow[0]; + return $rate['rate_buy']; } //---------------------------------------------------------------------------------- diff --git a/includes/db/crm_contacts_db.inc b/includes/db/crm_contacts_db.inc index 8623c8e0..a226e496 100644 --- a/includes/db/crm_contacts_db.inc +++ b/includes/db/crm_contacts_db.inc @@ -300,4 +300,17 @@ function get_crm_contact($id) return $ret; } +/* + Check for whether category is used in contacts. +*/ +function is_crm_category_used($id) +{ + $row = get_crm_category($id); + $sql = "SELECT COUNT(*) FROM ".TB_PREF."crm_contacts WHERE type='".$row['type']."' AND action='".$row['action']."'"; + $result = db_query($sql, "check relations for crm_contacts failed"); + $contacts = db_fetch($result); + return $contacts[0]; +} + + ?> \ No newline at end of file diff --git a/includes/db/references_db.inc b/includes/db/references_db.inc index ca086541..87aa6515 100644 --- a/includes/db/references_db.inc +++ b/includes/db/references_db.inc @@ -76,4 +76,48 @@ function get_next_reference($type) return $row[0]; } +//---------------------------------------------------------------------------- +// +// Check if reference was not used so far (for other transaction than $trans_no) +// +function is_new_reference($ref, $type, $trans_no=0) +{ + $db_info = get_systype_db_info($type); + $db_name = $db_info[0]; + $db_type = $db_info[1]; + $db_trans = $db_info[2]; + $db_ref = $db_info[3]; + + $ref = db_escape(trim($ref)); + $type = db_escape($type); + + if ($db_ref == null) { // journal or bank trans store references in refs table + $db_name = TB_PREF."refs"; + $db_type = 'type'; + $db_trans = 'id'; + $db_ref = 'reference'; + } + + if ($db_type != null) { + $sql = "SELECT $db_ref FROM $db_name tbl + LEFT JOIN ".TB_PREF."voided v ON + tbl.$db_type=v.type AND tbl.$db_trans=v.id + WHERE $db_ref=$ref AND ISNULL(v.id) + AND tbl.$db_type=$type"; + } else { + $sql = "SELECT $db_ref ref FROM $db_name tbl + LEFT JOIN ".TB_PREF."voided v ON + v.type=$type AND tbl.$db_trans=v.id + WHERE $db_ref=$ref AND ISNULL(v.id)"; + } + if ($trans_no) + $sql .= " AND tbl.`$db_trans` != ".db_escape($trans_no); + + $result = db_query($sql, "could not test for unique reference"); + + return (db_num_rows($result) == 0); + +} + + ?> \ No newline at end of file diff --git a/includes/main.inc b/includes/main.inc index 018fb2b0..2477a6c0 100644 --- a/includes/main.inc +++ b/includes/main.inc @@ -12,7 +12,6 @@ include_once($path_to_root . "/includes/db/connect_db.inc"); include_once($path_to_root . "/includes/types.inc"); -include_once($path_to_root . "/includes/systypes.inc"); include_once($path_to_root . "/includes/references.inc"); include_once($path_to_root . "/includes/db/comments_db.inc"); include_once($path_to_root . "/includes/db/sql_functions.inc"); diff --git a/includes/references.inc b/includes/references.inc index cd01ab6c..38bd84cd 100644 --- a/includes/references.inc +++ b/includes/references.inc @@ -100,48 +100,4 @@ class references } } -//---------------------------------------------------------------------------- -// -// Check if reference was not used so far (for other transaction than $trans_no) -// -function is_new_reference($ref, $type, $trans_no=0) -{ - $db_info = get_systype_db_info($type); - $db_name = $db_info[0]; - $db_type = $db_info[1]; - $db_trans = $db_info[2]; - $db_ref = $db_info[3]; - - $ref = db_escape(trim($ref)); - $type = db_escape($type); - - if ($db_ref == null) { // journal or bank trans store references in refs table - $db_name = TB_PREF."refs"; - $db_type = 'type'; - $db_trans = 'id'; - $db_ref = 'reference'; - } - - if ($db_type != null) { - $sql = "SELECT $db_ref FROM $db_name tbl - LEFT JOIN ".TB_PREF."voided v ON - tbl.$db_type=v.type AND tbl.$db_trans=v.id - WHERE $db_ref=$ref AND ISNULL(v.id) - AND tbl.$db_type=$type"; - } else { - $sql = "SELECT $db_ref ref FROM $db_name tbl - LEFT JOIN ".TB_PREF."voided v ON - v.type=$type AND tbl.$db_trans=v.id - WHERE $db_ref=$ref AND ISNULL(v.id)"; - } - if ($trans_no) - $sql .= " AND tbl.`$db_trans` != ".db_escape($trans_no); - - $result = db_query($sql, "could not test for unique reference"); - - return (db_num_rows($result) == 0); - -} - - ?> \ No newline at end of file diff --git a/includes/systypes.inc b/includes/systypes.inc deleted file mode 100644 index c1c17a4a..00000000 --- a/includes/systypes.inc +++ /dev/null @@ -1,83 +0,0 @@ -. -***********************************************************************/ - -//----------------------------------------------------------------------------------------- -// Returns next transaction number. -// Used only for transactions stored in tables without autoincremented key. -// - -function get_next_trans_no ($trans_type){ - - $st = get_systype_db_info($trans_type); - - if (!($st && $st[0] && $st[2])) { - // this is in fact internal error condition. - display_error('Internal error: invalid type passed to get_next_trans_no()'); - return 0; - } - $sql1 = "SELECT MAX(`$st[2]`) as last_no FROM $st[0]"; - if ($st[1] != null) - $sql1 .= " WHERE `$st[1]`=".db_escape($trans_type); - - // check also in voided transactions (some transactions like location transfer are removed completely) - $sql2 = "SELECT MAX(`id`) as last_no FROM ".TB_PREF."voided WHERE `type`=".db_escape($trans_type); - - $sql = "SELECT max(last_no) last_no FROM ($sql1 UNION $sql2) a"; - $result = db_query($sql,"The next transaction number for $trans_type could not be retrieved"); - $myrow = db_fetch_row($result); - - return $myrow[0] + 1; -} - -//----------------------------------------------------------------------------- - -function get_systype_db_info($type) -{ - switch ($type) - { - case ST_JOURNAL : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); - case ST_BANKPAYMENT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); - case ST_BANKDEPOSIT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); - case 3 : return null; - case ST_BANKTRANSFER : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); - case ST_SALESINVOICE : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTCREDIT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTPAYMENT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTDELIVERY : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_LOCTRANSFER : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); - case ST_INVADJUST : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); - case ST_PURCHORDER : return array("".TB_PREF."purch_orders", null, "order_no", "reference", "ord_date"); - case ST_SUPPINVOICE : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPCREDIT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPAYMENT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPRECEIVE : return array("".TB_PREF."grn_batch", null, "id", "reference", "delivery_date"); - case ST_WORKORDER : return array("".TB_PREF."workorders", null, "id", "wo_ref", "released_date"); - case ST_MANUISSUE : return array("".TB_PREF."wo_issues", null, "issue_no", "reference", "issue_date"); - case ST_MANURECEIVE : return array("".TB_PREF."wo_manufacture", null, "id", "reference", "date_"); - case ST_SALESORDER : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); - case 31 : return array("".TB_PREF."service_orders", null, "order_no", "cust_ref", "date"); - case ST_SALESQUOTE : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); - case ST_DIMENSION : return array("".TB_PREF."dimensions", null, "id", "reference", "date_"); - case ST_COSTUPDATE : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); - } - - display_db_error("invalid type ($type) sent to get_systype_db_info", "", true); -} - -function get_systypes() -{ - $sql = "SELECT * FROM ".TB_PREF."sys_types"; - $result = db_query($sql, "could not query systypes table"); - return $result; -} - -?> \ No newline at end of file diff --git a/includes/types.inc b/includes/types.inc index cd4e3dc3..5b7b4a7a 100644 --- a/includes/types.inc +++ b/includes/types.inc @@ -140,7 +140,7 @@ function payment_person_name($type, $person_id, $full=true) { // // Returns counterparty (supplier/customer) name for selected transaction. // -function get_counterparty_name($trans_type, $trans_no, $full=true) +function get_counterparty_name($trans_type, $trans_no) { switch($trans_type) { diff --git a/manufacturing/work_order_entry.php b/manufacturing/work_order_entry.php index b9cfcba0..4466355d 100644 --- a/manufacturing/work_order_entry.php +++ b/manufacturing/work_order_entry.php @@ -424,24 +424,23 @@ else date_row(_("Date") . ":", 'date_', '', true); hidden('RequDate', ''); - $sql = "SELECT DISTINCT account_code FROM ".TB_PREF."bank_accounts"; - $rs = db_query($sql,"could not get bank accounts"); - $r = db_fetch_row($rs); + $bank_act = get_default_bank_account(); if (!isset($_POST['Labour'])) { $_POST['Labour'] = price_format(0); - $_POST['cr_lab_acc'] = $r[0]; + $_POST['cr_lab_acc'] = $bank_act['account_code']; } + amount_row($wo_cost_types[WO_LABOUR], 'Labour'); gl_all_accounts_list_row(_("Credit Labour Account"), 'cr_lab_acc', null); if (!isset($_POST['Costs'])) { $_POST['Costs'] = price_format(0); - $_POST['cr_acc'] = $r[0]; + $_POST['cr_acc'] = $bank_act['account_code']; } amount_row($wo_cost_types[WO_OVERHEAD], 'Costs'); gl_all_accounts_list_row(_("Credit Overhead Account"), 'cr_acc', null); - + } if (get_post('released')) diff --git a/purchasing/includes/db/po_db.inc b/purchasing/includes/db/po_db.inc index b33a2fa0..dac2ed5e 100644 --- a/purchasing/includes/db/po_db.inc +++ b/purchasing/includes/db/po_db.inc @@ -9,6 +9,38 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ +// ------------------------------------------------------------------------------ + +function get_supplier_details_to_order(&$order, $supplier_id) +{ + $sql = "SELECT curr_code, supp_name, tax_group_id, supp.tax_included, supp.tax_algorithm, + supp.credit_limit - Sum(IFNULL(IF(trans.type=".ST_SUPPCREDIT.", -1, 1) + * (ov_amount + ov_gst + ov_discount),0)) as cur_credit, + terms.terms, terms.days_before_due, terms.day_in_following_month + FROM ".TB_PREF."suppliers supp + LEFT JOIN ".TB_PREF."supp_trans trans ON supp.supplier_id = trans.supplier_id + LEFT JOIN ".TB_PREF."payment_terms terms ON supp.payment_terms=terms.terms_indicator + WHERE supp.supplier_id = ".db_escape($supplier_id)." + GROUP BY + supp.supp_name"; + + $result = db_query($sql, "The supplier details could not be retreived"); + $myrow = db_fetch($result); + + $order->credit = $myrow["cur_credit"]; + $order->terms = array( + 'description' => $myrow['terms'], + 'days_before_due' => $myrow['days_before_due'], + 'day_in_following_month' => $myrow['day_in_following_month'] ); + + $_POST['supplier_id'] = $supplier_id; + $_POST['supplier_name'] = $myrow["supp_name"]; + $_POST['curr_code'] = $myrow["curr_code"]; + + $order->set_supplier($supplier_id, $myrow["supp_name"], $myrow["curr_code"], + $myrow["tax_group_id"], $myrow["tax_included"], $myrow["tax_algorithm"]); +} + //---------------------------------------------------------------------------------------- function delete_po($po) diff --git a/purchasing/includes/db/suppliers_db.inc b/purchasing/includes/db/suppliers_db.inc index cab8eace..6b7d0cde 100644 --- a/purchasing/includes/db/suppliers_db.inc +++ b/purchasing/includes/db/suppliers_db.inc @@ -187,3 +187,14 @@ function update_supp_tax_algorithm($supplier_id, $tax_algorithm) . " WHERE supplier_id=".db_escape($supplier_id); return db_query($sql, "cannot update supplier's tax calculation algorithm"); } + +function get_supplier_currency($supplier_id) +{ + $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id = ".db_escape($supplier_id); + + $result = db_query($sql, "Retreive currency of supplier $supplier_id"); + + $myrow=db_fetch_row($result); + return $myrow[0]; +} + diff --git a/purchasing/includes/ui/po_ui.inc b/purchasing/includes/ui/po_ui.inc index ba604609..152df8f2 100644 --- a/purchasing/includes/ui/po_ui.inc +++ b/purchasing/includes/ui/po_ui.inc @@ -60,37 +60,6 @@ function copy_to_cart() $cart->tax_overrides[$id] = user_numeric($_POST['mantax'][$id]); } } } -// ------------------------------------------------------------------------------ - -function get_supplier_details_to_order(&$order, $supplier_id) -{ - $sql = "SELECT curr_code, supp_name, tax_group_id, supp.tax_included, supp.tax_algorithm, - supp.credit_limit - Sum(IFNULL(IF(trans.type=".ST_SUPPCREDIT.", -1, 1) - * (ov_amount + ov_gst + ov_discount),0)) as cur_credit, - terms.terms, terms.days_before_due, terms.day_in_following_month - FROM ".TB_PREF."suppliers supp - LEFT JOIN ".TB_PREF."supp_trans trans ON supp.supplier_id = trans.supplier_id - LEFT JOIN ".TB_PREF."payment_terms terms ON supp.payment_terms=terms.terms_indicator - WHERE supp.supplier_id = ".db_escape($supplier_id)." - GROUP BY - supp.supp_name"; - - $result = db_query($sql, "The supplier details could not be retreived"); - - $myrow = db_fetch($result); - $order->credit = $myrow["cur_credit"]; - $order->terms = array( - 'description' => $myrow['terms'], - 'days_before_due' => $myrow['days_before_due'], - 'day_in_following_month' => $myrow['day_in_following_month'] ); - - $_POST['supplier_id'] = $supplier_id; - $_POST['supplier_name'] = $myrow["supp_name"]; - $_POST['curr_code'] = $myrow["curr_code"]; - - $order->set_supplier($supplier_id, $myrow["supp_name"], $myrow["curr_code"], - $myrow["tax_group_id"], $myrow["tax_included"], $myrow["tax_algorithm"]); -} //--------------------------------------------------------------------------------------------------- @@ -209,13 +178,9 @@ function display_po_header(&$order) { /*If this is the first time the form loaded set up defaults */ - //$_POST['StkLocation'] = $_SESSION['UserStockLocation']; - $sql = "SELECT delivery_address, phone FROM ".TB_PREF."locations WHERE loc_code=".db_escape($_POST['StkLocation']); - $result = db_query($sql,"could not get location info"); - - if (db_num_rows($result) == 1) + $loc_row = get_item_location(get_post('StkLocation')); + if ($loc_row) { - $loc_row = db_fetch($result); $_POST['delivery_address'] = $loc_row["delivery_address"]; $Ajax->activate('delivery_address'); $_SESSION['PO']->Location = $_POST['StkLocation']; diff --git a/reporting/includes/doctext.inc b/reporting/includes/doctext.inc index de3082a9..be391fde 100644 --- a/reporting/includes/doctext.inc +++ b/reporting/includes/doctext.inc @@ -34,10 +34,7 @@ if (!in_array($this->formData['doctype'], array(ST_STATEMENT, ST_WORKORDER))) { - $id = $this->formData['payment_terms']; - $sql = "SELECT terms, days_before_due FROM ".TB_PREF."payment_terms WHERE terms_indicator=".db_escape($id); - $result = db_query($sql,"could not get paymentterms"); - $row = db_fetch($result); + $row = get_payment_terms($this->formData['payment_terms']); $Payment_Terms = _("Payment Terms") . ': ' . $row["terms"]; if ($this->formData['doctype'] == ST_SALESINVOICE && $this->formData['prepaid']) $this->formData['prepaid'] = ($row['days_before_due'] >= 0) ? 'final' : 'partial'; diff --git a/sales/customer_invoice.php b/sales/customer_invoice.php index 945ce9f1..09d405eb 100644 --- a/sales/customer_invoice.php +++ b/sales/customer_invoice.php @@ -66,12 +66,7 @@ if (isset($_GET['AddedID'])) { hyperlink_params("$path_to_root/sales/inquiry/sales_deliveries_view.php", _("Select Another &Delivery For Invoicing"), "OutstandingOnly=1"); - $sql = "SELECT trans_type_from, trans_no_from FROM ".TB_PREF."cust_allocations - WHERE trans_type_to=".ST_SALESINVOICE." AND trans_no_to=".db_escape($invoice_no); - $result = db_query($sql, "could not retrieve customer allocation"); - $row = db_fetch($result); - - if ($row === false) + if (!db_num_rows(get_allocatable_from_cust_transactions(null, $invoice_no, $trans_type))) hyperlink_params("$path_to_root/sales/customer_payments.php", _("Entry &customer payment for this invoice"), "SInvoice=".$invoice_no); diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 3b936a33..61425b21 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -186,4 +186,15 @@ function get_customer_by_ref($reference) return db_fetch($result); } -?> \ No newline at end of file +//---------------------------------------------------------------------------------- + +function get_customer_currency($customer_id) +{ + $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id); + + $result = db_query($sql, "Retreive currency of customer $customer_id"); + + $myrow=db_fetch_row($result); + return $myrow[0]; +} + diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index d02df7bd..6ac1e9dd 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -596,3 +596,13 @@ function is_sales_order_started($order_no) $result = db_fetch(db_query($sql, "cannot retrieve sales invoices for sales order")); return $result[0]; } + +//--------------------------------------------------------------------------------------------- +// +// Mark/unmark sales order as template. +// +function sales_order_set_template($id, $status) +{ + $sql = "UPDATE ".TB_PREF."sales_orders SET type = ".db_escape($status)." WHERE order_no=".db_escape($id); + db_query($sql, "Can't change sales order type"); +} diff --git a/sales/inquiry/sales_orders_view.php b/sales/inquiry/sales_orders_view.php index ae9a666e..7955d6c4 100644 --- a/sales/inquiry/sales_orders_view.php +++ b/sales/inquiry/sales_orders_view.php @@ -201,27 +201,17 @@ function invoice_prep_link($row) "/sales/customer_invoice.php?InvoicePrepayments=" .$row['order_no'], ICON_DOC) : ''; } -//--------------------------------------------------------------------------------------------- -// Update db record if respective checkbox value has changed. -// -function change_tpl_flag($id) +$id = find_submit('_chgtpl'); +if ($id != -1) { - global $Ajax; - - $sql = "UPDATE ".TB_PREF."sales_orders SET type = !type WHERE order_no=$id"; - - db_query($sql, "Can't change sales order type"); + sales_order_set_template($id, check_value('chgtpl'.$id)); $Ajax->activate('orders_tbl'); } -$id = find_submit('_chgtpl'); -if ($id != -1) - change_tpl_flag($id); - if (isset($_POST['Update']) && isset($_POST['last'])) { foreach($_POST['last'] as $id => $value) if ($value != check_value('chgtpl'.$id)) - change_tpl_flag($id); + sales_order_set_template($id, !check_value('chgtpl'.$id)); } $show_dates = !in_array($_POST['order_view_mode'], array('OutstandingOnly', 'InvoiceTemplates', 'DeliveryTemplates')); diff --git a/sales/sales_order_entry.php b/sales/sales_order_entry.php index f74d4ead..add2b709 100644 --- a/sales/sales_order_entry.php +++ b/sales/sales_order_entry.php @@ -220,13 +220,10 @@ if (isset($_GET['AddedID'])) { submenu_print(_("&Print Sales Invoice"), ST_SALESINVOICE, $invoice."-".ST_SALESINVOICE, 'prtopt'); submenu_print(_("&Email Sales Invoice"), ST_SALESINVOICE, $invoice."-".ST_SALESINVOICE, null, 1); set_focus('prtopt'); - - $sql = "SELECT trans_type_from, trans_no_from FROM ".TB_PREF."cust_allocations - WHERE trans_type_to=".ST_SALESINVOICE." AND trans_no_to=".db_escape($invoice); - $result = db_query($sql, "could not retrieve customer allocation"); - $row = db_fetch($result); + + $row = db_fetch(get_allocatable_from_cust_transactions(null, $invoice, ST_SALESINVOICE)); if ($row !== false) - submenu_print(_("Print &Receipt"), $row['trans_type_from'], $row['trans_no_from']."-".$row['trans_type_from'], 'prtopt'); + submenu_print(_("Print &Receipt"), $row['type'], $row['trans_no']."-".$row['type'], 'prtopt'); display_note(get_gl_view_str(ST_SALESINVOICE, $invoice, _("View the GL &Journal Entries for this Invoice")),0, 1); -- 2.30.2