From 7840540918bfd37c886877a5d52e3ba0b3373289 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Mon, 9 Apr 2012 10:13:30 +0200 Subject: [PATCH] 0001503: Request - fee account included in bank account table --- gl/bank_transfer.php | 2 +- gl/includes/db/gl_db_bank_accounts.inc | 25 ++++++++++++++++------ gl/includes/db/gl_db_banking.inc | 2 +- gl/manage/bank_accounts.php | 9 ++++++-- purchasing/includes/db/supp_payment_db.inc | 2 +- purchasing/supplier_payment.php | 2 +- sales/customer_payments.php | 2 +- sales/includes/db/payment_db.inc | 2 +- sql/alter2.4.sql | 5 ++++- sql/en_US-demo.sql | 5 +++-- sql/en_US-new.sql | 5 +++-- 11 files changed, 42 insertions(+), 19 deletions(-) diff --git a/gl/bank_transfer.php b/gl/bank_transfer.php index 7e293af1..d99402b4 100644 --- a/gl/bank_transfer.php +++ b/gl/bank_transfer.php @@ -154,7 +154,7 @@ function check_valid_entries() set_focus('charge'); return false; } - if (isset($_POST['charge']) && input_num('charge') > 0 && get_company_pref('bank_charge_act') == '') { + if (isset($_POST['charge']) && input_num('charge') > 0 && get_bank_charge_account($_POST['FromBankAccount']) == '') { display_error(_("The Bank Charge Account has not been set in System and General GL Setup.")); set_focus('charge'); return false; diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index c4318279..c41e8392 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -18,18 +18,18 @@ function clear_dflt_curr_account($curr_code) { function add_bank_account($account_code, $account_type, $bank_account_name, $bank_name, $bank_account_number, $bank_address, $bank_curr_code, - $dflt_curr_act) + $dflt_curr_act, $bank_charge_act) { if($dflt_curr_act) // only one default account for any currency clear_dflt_curr_account($bank_curr_code); $sql = "INSERT INTO ".TB_PREF."bank_accounts (account_code, account_type, bank_account_name, bank_name, bank_account_number, bank_address, - bank_curr_code, dflt_curr_act) + bank_curr_code, dflt_curr_act, bank_charge_act) VALUES (".db_escape($account_code).", ".db_escape($account_type).", " .db_escape($bank_account_name).", ".db_escape($bank_name).", " .db_escape($bank_account_number).",".db_escape($bank_address). - ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).")"; + ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).", ".db_escape($bank_charge_act).")"; db_query($sql, "could not add a bank account for $account_code"); } @@ -37,7 +37,7 @@ function add_bank_account($account_code, $account_type, $bank_account_name, //--------------------------------------------------------------------------------------------- function update_bank_account($id, $account_code, $account_type, $bank_account_name, - $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act) + $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act, $bank_charge_act) { if($dflt_curr_act) // only one default account for any currency clear_dflt_curr_account($bank_curr_code); @@ -47,8 +47,8 @@ function update_bank_account($id, $account_code, $account_type, $bank_account_na bank_account_name=".db_escape($bank_account_name).", bank_name=".db_escape($bank_name).", bank_account_number=".db_escape($bank_account_number).", bank_curr_code=".db_escape($bank_curr_code).", bank_address=".db_escape($bank_address).", - dflt_curr_act=".db_escape($dflt_curr_act) - ." WHERE id = ".db_escape($id); + dflt_curr_act=".db_escape($dflt_curr_act).", bank_charge_act=".db_escape($bank_charge_act)." + WHERE id = ".db_escape($id); db_query($sql, "could not update bank account for $account_code"); } @@ -102,6 +102,19 @@ function get_bank_gl_account($id) //--------------------------------------------------------------------------------------------- +function get_bank_charge_account($id) +{ + $sql = "SELECT bank_charge_act FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); + + $result = db_query($sql, "could not retreive bank account for $id"); + + $bank_account = db_fetch($result); + + return $bank_account['bank_charge_act']; +} + +//--------------------------------------------------------------------------------------------- + function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type) { $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type) diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 0650a0c6..f3f9a995 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -233,7 +233,7 @@ function add_bank_transfer($from_account, $to_account, $date_, if ($charge != 0) { /* Now Debit bank charge account with charges */ - $charge_act = get_company_pref('bank_charge_act'); + $charge_act = get_bank_charge_account($from_account); $total += add_gl_trans($trans_type, $trans_no, $date_, $charge_act, 0, 0, $person_id, $charge, $currency); } diff --git a/gl/manage/bank_accounts.php b/gl/manage/bank_accounts.php index 9dda5cd3..b10c408e 100644 --- a/gl/manage/bank_accounts.php +++ b/gl/manage/bank_accounts.php @@ -48,7 +48,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $_POST['account_type'], $_POST['bank_account_name'], $_POST['bank_name'], $_POST['bank_account_number'], $_POST['bank_address'], $_POST['BankAccountCurrency'], - $_POST['dflt_curr_act']); + $_POST['dflt_curr_act'], $_POST['bank_charge_act']); display_notification(_('Bank account has been updated')); } else @@ -57,7 +57,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') add_bank_account($_POST['account_code'], $_POST['account_type'], $_POST['bank_account_name'], $_POST['bank_name'], $_POST['bank_account_number'], $_POST['bank_address'], - $_POST['BankAccountCurrency'], $_POST['dflt_curr_act']); + $_POST['BankAccountCurrency'], $_POST['dflt_curr_act'], $_POST['bank_charge_act']); display_notification(_('New bank account has been added')); } $Mode = 'RESET'; @@ -94,7 +94,10 @@ if ($Mode == 'RESET') $selected_id = -1; $_POST['bank_name'] = $_POST['bank_account_name'] = ''; $_POST['bank_account_number'] = $_POST['bank_address'] = ''; + $_POST['bank_charge_act'] = get_company_pref('bank_charge_act'); } +if (!isset($_POST['bank_charge_act'])) + $_POST['bank_charge_act'] = get_company_pref('bank_charge_act'); /* Always show the list of accounts */ @@ -152,6 +155,7 @@ if ($selected_id != -1) $_POST['bank_address'] = $myrow["bank_address"]; $_POST['BankAccountCurrency'] = $myrow["bank_curr_code"]; $_POST['dflt_curr_act'] = $myrow["dflt_curr_act"]; + $_POST['bank_charge_act'] = $myrow["bank_charge_act"]; } hidden('selected_id', $selected_id); hidden('account_code'); @@ -186,6 +190,7 @@ if($is_used) else gl_all_accounts_list_row(_("Bank Account GL Code:"), 'account_code', null); +gl_all_accounts_list_row(_("Bank Charges Account:"), 'bank_charge_act', null, true); text_row(_("Bank Name:"), 'bank_name', null, 50, 60); text_row(_("Bank Account Number:"), 'bank_account_number', null, 30, 60); textarea_row(_("Bank Address:"), 'bank_address', null, 40, 5); diff --git a/purchasing/includes/db/supp_payment_db.inc b/purchasing/includes/db/supp_payment_db.inc index f4a0e27f..cec8f351 100644 --- a/purchasing/includes/db/supp_payment_db.inc +++ b/purchasing/includes/db/supp_payment_db.inc @@ -62,7 +62,7 @@ function add_supp_payment($supplier_id, $date_, $bank_account, if ($supp_charge != 0) { - $charge_act = get_company_pref('bank_charge_act'); + $charge_act = get_bank_charge_account($bank_account); $total += add_gl_trans_supplier($trans_type, $payment_id, $date_, $charge_act, 0, 0, $supp_charge, $supplier_id, "", $rate); } diff --git a/purchasing/supplier_payment.php b/purchasing/supplier_payment.php index 6d9992a6..b28259e4 100644 --- a/purchasing/supplier_payment.php +++ b/purchasing/supplier_payment.php @@ -139,7 +139,7 @@ function check_inputs() } if (isset($_POST['charge']) && input_num('charge') > 0) { - $charge_acct = get_company_pref('bank_charge_act'); + $charge_acct = get_bank_charge_account($_POST['bank_account']); if (get_gl_account($charge_acct) == false) { display_error(_("The Bank Charge Account has not been set in System and General GL Setup.")); set_focus('charge'); diff --git a/sales/customer_payments.php b/sales/customer_payments.php index e268dd22..8b4bccec 100644 --- a/sales/customer_payments.php +++ b/sales/customer_payments.php @@ -179,7 +179,7 @@ function can_process() return false; } if (isset($_POST['charge']) && input_num('charge') > 0) { - $charge_acct = get_company_pref('bank_charge_act'); + $charge_acct = get_bank_charge_account($_POST['bank_account']); if (get_gl_account($charge_acct) == false) { display_error(_("The Bank Charge Account has not been set in System and General GL Setup.")); set_focus('charge'); diff --git a/sales/includes/db/payment_db.inc b/sales/includes/db/payment_db.inc index abee06b6..baabf0fe 100644 --- a/sales/includes/db/payment_db.inc +++ b/sales/includes/db/payment_db.inc @@ -72,7 +72,7 @@ function write_customer_payment($trans_no, $customer_id, $branch_id, $bank_accou if ($charge != 0) { /* Now Debit bank charge account with charges */ - $charge_act = get_company_pref('bank_charge_act'); + $charge_act = get_bank_charge_account($bank_account); $total += add_gl_trans_customer(ST_CUSTPAYMENT, $payment_no, $date_, $charge_act, 0, 0, $charge, $customer_id, "Cannot insert a GL transaction for the payment bank charge debit", $rate); diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index d99fdc1f..13f10736 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -41,4 +41,7 @@ ALTER TABLE `0_cust_branch` ADD COLUMN `bank_account` varchar(60) DEFAULT NULL A ALTER TABLE `0_debtor_trans` ADD COLUMN `tax_included` tinyint(1) unsigned NOT NULL default '0' AFTER `payment_terms`; UPDATE `0_debtor_trans` tr, `0_trans_tax_details` td SET tr.tax_included=td.included_in_price - WHERE tr.`type`=td.trans_type AND tr.trans_no=td.trans_no AND td.included_in_price \ No newline at end of file + WHERE tr.`type`=td.trans_type AND tr.trans_no=td.trans_no AND td.included_in_price +ALTER TABLE `0_bank_accounts` ADD COLUMN `bank_charge_act` varchar(15) NOT NULL DEFAULT '' AFTER `id`; +UPDATE `0_bank_accounts` SET `bank_charge_act`=(SELECT `value` FROM 0_sys_prefs WHERE name='bank_charge_act'); + \ No newline at end of file diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 480c14ec..b36a446b 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -102,6 +102,7 @@ CREATE TABLE IF NOT EXISTS `0_bank_accounts` ( `bank_curr_code` char(3) NOT NULL default '', `dflt_curr_act` tinyint(1) NOT NULL default '0', `id` smallint(6) NOT NULL auto_increment, + `bank_charge_act` varchar(15) NOT NULL default '', `last_reconciled_date` timestamp NOT NULL default '0000-00-00 00:00:00', `ending_reconcile_balance` double NOT NULL default '0', `inactive` tinyint(1) NOT NULL default '0', @@ -115,8 +116,8 @@ CREATE TABLE IF NOT EXISTS `0_bank_accounts` ( -- Dumping data for table `0_bank_accounts` -- -INSERT INTO `0_bank_accounts` VALUES('1060', 0, 'Current account', '9999999999', 'Wachovia Bank', '', 'USD', 1, 1, '0000-00-00 00:00:00', 0, 0); -INSERT INTO `0_bank_accounts` VALUES('1065', 3, 'Petty Cash account', 'N/A', 'N/A', '', 'USD', 0, 2, '0000-00-00 00:00:00', 0, 0); +INSERT INTO `0_bank_accounts` VALUES('1060', 0, 'Current account', '9999999999', 'Wachovia Bank', '', 'USD', 1, 1, '5690', '0000-00-00 00:00:00', 0, 0); +INSERT INTO `0_bank_accounts` VALUES('1065', 3, 'Petty Cash account', 'N/A', 'N/A', '', 'USD', 0, 2, '5690', '0000-00-00 00:00:00', 0, 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 89625b72..525806e3 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -102,6 +102,7 @@ CREATE TABLE IF NOT EXISTS `0_bank_accounts` ( `bank_curr_code` char(3) NOT NULL default '', `dflt_curr_act` tinyint(1) NOT NULL default '0', `id` smallint(6) NOT NULL auto_increment, + `bank_charge_act` varchar(15) NOT NULL default '', `last_reconciled_date` timestamp NOT NULL default '0000-00-00 00:00:00', `ending_reconcile_balance` double NOT NULL default '0', `inactive` tinyint(1) NOT NULL default '0', @@ -115,8 +116,8 @@ CREATE TABLE IF NOT EXISTS `0_bank_accounts` ( -- Dumping data for table `0_bank_accounts` -- -INSERT INTO `0_bank_accounts` VALUES('1060', 0, 'Current account', 'N/A', 'N/A', '', 'USD', 1, 1, '0000-00-00 00:00:00', 0, 0); -INSERT INTO `0_bank_accounts` VALUES('1065', 3, 'Petty Cash account', 'N/A', 'N/A', '', 'USD', 0, 2, '0000-00-00 00:00:00', 0, 0); +INSERT INTO `0_bank_accounts` VALUES('1060', 0, 'Current account', 'N/A', 'N/A', '', 'USD', 1, 1, '5690', '0000-00-00 00:00:00', 0, 0); +INSERT INTO `0_bank_accounts` VALUES('1065', 3, 'Petty Cash account', 'N/A', 'N/A', '', 'USD', 0, 2, '5690', '0000-00-00 00:00:00', 0, 0); -- -------------------------------------------------------- -- 2.30.2