From b1c9712c6042b26d43ce9c78c77d078ef1906a02 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 16 Feb 2012 10:40:33 +0100 Subject: [PATCH] [0001174] Adding bank account tnumber to customer branch record. --- gl/includes/db/gl_db_bank_accounts.inc | 15 +++++++++++++++ includes/sysnames.inc | 10 +++++++++- includes/types.inc | 7 +++++++ sales/includes/db/branches_db.inc | 12 +++++++----- sales/manage/customer_branches.php | 8 +++++--- sql/alter2.4.sql | 2 ++ sql/en_US-demo.sql | 7 ++++--- sql/en_US-new.sql | 1 + 8 files changed, 50 insertions(+), 12 deletions(-) diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index 1669d200..c4318279 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -347,5 +347,20 @@ function get_bank_account_limit($account, $date, $user=null) return null; return max(0, get_balance_before_for_bank_account($account, add_days($date,1))); // in case of manco made before the check was implemented } + +function identify_bank_account($account_number) +{ + $number = db_escape($account_number); + $sql = "(SELECT id AS id, ".BO_COMPANY." AS type FROM ".TB_PREF."bank_accounts WHERE REPLACE(bank_account_number,' ', '')=$number)"; + $sql .= " UNION (SELECT supplier_id AS id, ".BO_SUPPLIER." AS type FROM ".TB_PREF."suppliers WHERE REPLACE(bank_account,' ', '')=$number)"; + $sql .= " UNION (SELECT branch_code AS id, ".BO_CUSTBRANCH." AS type FROM ".TB_PREF."cust_branch WHERE REPLACE(bank_account,' ', '')=$number)"; + + $result = db_query($sql, 'bank account identification failed'); + + if (db_num_rows($result)) + return db_fetch($result); + else + return null; +} ?> \ No newline at end of file diff --git a/includes/sysnames.inc b/includes/sysnames.inc index 10f05518..c14cccd6 100644 --- a/includes/sysnames.inc +++ b/includes/sysnames.inc @@ -16,7 +16,7 @@ // global $systypes_array, $bank_account_types, $bank_transfer_types, $payment_person_types, $wo_types_array, $wo_cost_types, $class_types, - $quick_actions, $quick_entry_types, $stock_types, $tag_types; + $quick_actions, $quick_entry_types, $stock_types, $tag_types, $bank_owner; $systypes_array = array ( ST_JOURNAL => _("Journal Entry"), @@ -188,5 +188,13 @@ $tax_algorithms = array( TCA_LINES => _("Sum per line taxes"), TCA_TOTALS => _("Taxes from totals") ); +//---------------------------------------------------------------------------------- + +$bank_owner_types = array( + BO_UNKNOWN => _("Unknown"), + BO_COMPANY => _("Company"), + BO_CUSTBRANCH => _("Customer"), + BO_SUPPLIER => _("Supplier") +); ?> \ No newline at end of file diff --git a/includes/types.inc b/includes/types.inc index 460c5193..ec06fbaa 100644 --- a/includes/types.inc +++ b/includes/types.inc @@ -306,6 +306,13 @@ define('PTT_FOLLOWING', 4); define('TCA_TOTALS', 1); // taxes are calculated from respective net totals for all lines define('TCA_LINES', 2); // taxes calculated for every line, then summed +// +// Bank account owner types +// +define('BO_UNKNOWN', 0); +define('BO_COMPANY', 1); +define('BO_CUSTBRANCH', 2); +define('BO_SUPPLIER', 3); include_once($path_to_root . '/includes/sysnames.inc'); diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index 2688d1b3..a9683a27 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -12,12 +12,12 @@ function add_branch($customer_id, $br_name, $br_ref, $br_address, $salesman, $area, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, - $default_ship_via, $notes) + $default_ship_via, $notes, $bank_account) { $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, salesman, area, tax_group_id, sales_account, receivables_account, payment_discount_account, sales_discount_account, default_location, - br_post_address, disable_trans, group_no, default_ship_via, notes) + br_post_address, disable_trans, group_no, default_ship_via, notes, bank_account) VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", " .db_escape($br_ref) . ", " .db_escape($br_address) . ", ".db_escape($salesman) . ", " @@ -32,14 +32,15 @@ function add_branch($customer_id, $br_name, $br_ref, $br_address, $salesman, $ar .db_escape($disable_trans) . ", " .db_escape($group_no) . ", " .db_escape($default_ship_via). ", " - .db_escape($notes).")"; + .db_escape($notes). ", " + .db_escape($bank_account, true).")"; db_query($sql,"The branch record could not be added"); } function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_address, $salesman, $area, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, - $default_ship_via, $notes) + $default_ship_via, $notes, $bank_account) { $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ", branch_ref = " . db_escape($br_ref) . ", @@ -56,7 +57,8 @@ function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_addres disable_trans=".db_escape($disable_trans) . ", group_no=".db_escape($group_no) . ", default_ship_via=".db_escape($default_ship_via) . ", - notes=".db_escape($notes) . " + notes=".db_escape($notes) . ", + bank_account=".db_escape($bank_account, true)." WHERE branch_code =".db_escape($branch_code) . " AND debtor_no=".db_escape($customer_id); db_query($sql,"The branch record could not be updated"); diff --git a/sales/manage/customer_branches.php b/sales/manage/customer_branches.php index 5ab27166..6e61b52b 100644 --- a/sales/manage/customer_branches.php +++ b/sales/manage/customer_branches.php @@ -81,7 +81,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $_POST['br_address'], $_POST['salesman'], $_POST['area'], $_POST['tax_group_id'], $_POST['sales_account'], $_POST['sales_discount_account'], $_POST['receivables_account'], $_POST['payment_discount_account'], $_POST['default_location'], $_POST['br_post_address'], $_POST['disable_trans'], $_POST['group_no'], - $_POST['default_ship_via'], $_POST['notes']); + $_POST['default_ship_via'], $_POST['notes'], $_POST['bank_account']); // update_record_status($_POST['supplier_id'], $_POST['inactive'], // 'cust_branch', 'branch_code'); @@ -93,7 +93,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $_POST['br_address'], $_POST['salesman'], $_POST['area'], $_POST['tax_group_id'], $_POST['sales_account'], $_POST['sales_discount_account'], $_POST['receivables_account'], $_POST['payment_discount_account'], $_POST['default_location'], $_POST['br_post_address'], 0, $_POST['group_no'], - $_POST['default_ship_via'], $_POST['notes']); + $_POST['default_ship_via'], $_POST['notes'], $_POST['bank_account']); $selected_id = db_insert_id(); add_crm_person($_POST['contact_name'], $_POST['contact_name'], '', $_POST['br_post_address'], @@ -204,6 +204,7 @@ function branch_settings($selected_id) { $_POST['payment_discount_account'] = $myrow['payment_discount_account']; $_POST['group_no'] = $myrow["group_no"]; $_POST['notes'] = $myrow["notes"]; + $_POST['bank_account'] = $myrow["bank_account"]; } } @@ -224,7 +225,7 @@ function branch_settings($selected_id) { // We use the Item Sales Account as default! // $_POST['sales_account'] = $company_record["default_sales_act"]; - $_POST['sales_account'] = $_POST['notes'] = ''; + $_POST['sales_account'] = $_POST['notes'] = $_POST['bank_account'] = ''; $_POST['sales_discount_account'] = $company_record['default_sales_discount_act']; $_POST['receivables_account'] = $company_record['debtors_act']; $_POST['payment_discount_account'] = $company_record['default_prompt_payment_act']; @@ -251,6 +252,7 @@ function branch_settings($selected_id) { gl_all_accounts_list_row(_("Sales Discount Account:"), 'sales_discount_account'); gl_all_accounts_list_row(_("Accounts Receivable Account:"), 'receivables_account', null, true); gl_all_accounts_list_row(_("Prompt Payment Discount Account:"), 'payment_discount_account'); + text_row(_("Bank Account Number:"), 'bank_account', null, 30, 60); table_section(2); diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index d9441737..c2fe4c3d 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -33,3 +33,5 @@ UPDATE `0_tax_group_items` tgi WHERE tgi.rate=(SELECT 0_tax_types.rate FROM 0_tax_types, 0_tax_groups WHERE tax_shipping=1 AND tgi.tax_group_id=0_tax_groups.id AND tgi.tax_type_id=0_tax_types.id); ALTER TABLE `0_tax_groups` DROP COLUMN `tax_shipping`; + +ALTER TABLE `0_cust_branch` ADD COLUMN `bank_account` varchar(60) DEFAULT NULL AFTER `notes`; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index d5a6d8ab..aa4d54cb 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -611,6 +611,7 @@ CREATE TABLE IF NOT EXISTS `0_cust_branch` ( `br_post_address` tinytext NOT NULL, `group_no` int(11) NOT NULL default '0', `notes` tinytext NOT NULL, + `bank_account` varchar(60) DEFAULT NULL, `inactive` tinyint(1) NOT NULL default '0', PRIMARY KEY (`branch_code`,`debtor_no`), KEY `branch_code` (`branch_code`), @@ -622,9 +623,9 @@ CREATE TABLE IF NOT EXISTS `0_cust_branch` ( -- Dumping data for table `0_cust_branch` -- -INSERT INTO `0_cust_branch` VALUES(1, 1, 'Beefeater Ltd.', 'Beefeater', '', 1, 1, 'Main Branch', 'DEF', 2, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '', 0); -INSERT INTO `0_cust_branch` VALUES(2, 2, 'Ghostbusters Corp.', 'Ghostbusters', 'Address 1\nAddress 2\nAddress 3', 1, 1, 'Main Branch', 'DEF', 1, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '', 0); -INSERT INTO `0_cust_branch` VALUES(3, 3, 'Brezan', 'Brezan', 'Address 1\nAddress 2\nAddress 3', 1, 1, 'Main Branch', 'DEF', 1, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '', 0); +INSERT INTO `0_cust_branch` VALUES(1, 1, 'Beefeater Ltd.', 'Beefeater', '', 1, 1, 'Main Branch', 'DEF', 2, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '', '', 0); +INSERT INTO `0_cust_branch` VALUES(2, 2, 'Ghostbusters Corp.', 'Ghostbusters', 'Address 1\nAddress 2\nAddress 3', 1, 1, 'Main Branch', 'DEF', 1, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '','', 0); +INSERT INTO `0_cust_branch` VALUES(3, 3, 'Brezan', 'Brezan', 'Address 1\nAddress 2\nAddress 3', 1, 1, 'Main Branch', 'DEF', 1, '', '4510', '1200', '4500', 1, 0, 'Address 1\nAddress 2\nAddress 3', 0, '', '', 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index bcd1539d..f63cf8e3 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -576,6 +576,7 @@ CREATE TABLE IF NOT EXISTS `0_cust_branch` ( `br_post_address` tinytext NOT NULL, `group_no` int(11) NOT NULL default '0', `notes` tinytext NOT NULL, + `bank_account` varchar(60) DEFAULT NULL, `inactive` tinyint(1) NOT NULL default '0', PRIMARY KEY (`branch_code`,`debtor_no`), KEY `branch_code` (`branch_code`), -- 2.30.2