From 4a4cafb0a0184fdcc96fcde391f01ce02dbf31b1 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Tue, 20 Apr 2010 12:52:51 +0000 Subject: [PATCH 1/1] Added option for Manual Revaluation of Currency Accounts --- CHANGELOG.txt | 11 +++ admin/company_preferences.php | 9 +- applications/generalledger.php | 3 + gl/includes/db/gl_db_banking.inc | 93 ++++++++++++++------- gl/manage/revaluate_currencies.php | 129 +++++++++++++++++++++++++++++ includes/prefs/sysprefs.inc | 5 ++ sql/alter2.3.sql | 2 + sql/en_US-demo.sql | 6 +- sql/en_US-new.sql | 3 +- 9 files changed, 225 insertions(+), 36 deletions(-) create mode 100644 gl/manage/revaluate_currencies.php diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 3eb5338b..2c8d5239 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,17 @@ Legend: ! -> Note $ -> Affected files +20-Apr-2010 Joe Hunt ++ Added option for Manual Revaluation of Currency Accounts +$ /admin/company_preferences.php + /applications/generalledger.php + /gl/includes/db/gl_db_banking.inc + /gl/manage/revaluate_currencies.php (new file) + /includes/prefs/sysprefs.inc + /sql/en_US-demo.sql + /sql/en_US-new.sql + /sql/alter2.3.sql + 16-Apr-2010 Joe Hunt # Minor fixes in voiding transactions $ /admin/void_transaction.php diff --git a/admin/company_preferences.php b/admin/company_preferences.php index 74a9faec..4270bb8f 100644 --- a/admin/company_preferences.php +++ b/admin/company_preferences.php @@ -110,7 +110,7 @@ if (isset($_POST['update']) && $_POST['update'] != "") 'use_dimension', 'curr_default', 'f_year', 'no_item_list' => 0, 'no_customer_list' => 0, 'no_supplier_list' =>0, 'base_sales', - 'time_zone' => 0, 'add_pct', 'round_to', 'login_tout')) + 'time_zone' => 0, 'add_pct', 'round_to', 'login_tout', 'auto_curr_reval')) ); $_SESSION['wa_current_user']->timeout = $_POST['login_tout']; @@ -151,6 +151,7 @@ $_POST['login_tout'] = $myrow['login_tout']; if ($_POST['add_pct'] == -1) $_POST['add_pct'] = ""; $_POST['round_to'] = $myrow['round_to']; +$_POST['auto_curr_reval'] = $myrow['auto_curr_reval']; $_POST['del_coy_logo'] = 0; start_outer_table(TABLESTYLE2); @@ -170,12 +171,11 @@ text_row_ex(_("GSTNo:"), 'gst_no', 25); currencies_list_row(_("Home Currency:"), 'curr_default', $_POST['curr_default']); fiscalyears_list_row(_("Fiscal Year:"), 'f_year', $_POST['f_year']); - -table_section(2); - text_row_ex(_("Tax Periods:"), 'tax_prd', 10, 10, '', null, null, _('Months.')); text_row_ex(_("Tax Last Period:"), 'tax_last', 10, 10, '', null, null, _('Months back.')); +table_section(2); + label_row(_("Company Logo:"), $_POST['coy_logo']); file_row(_("New Company Logo (.jpg)") . ":", 'pic', 'pic'); check_row(_("Delete Company Logo:"), 'del_coy_logo', $_POST['del_coy_logo']); @@ -191,6 +191,7 @@ check_row(_("Search Item List"), 'no_item_list', null); check_row(_("Search Customer List"), 'no_customer_list', null); check_row(_("Search Supplier List"), 'no_supplier_list', null); label_row("", " "); +check_row(_("Automatic Revaluation Currency Accounts"), 'auto_curr_reval', $_POST['auto_curr_reval']); check_row(_("Time Zone on Reports"), 'time_zone', $_POST['time_zone']); text_row_ex(_("Login Timeout:"), 'login_tout', 10, 10, '', null, null, _('seconds')); label_row(_("Version Id"), $_POST['version_id']); diff --git a/applications/generalledger.php b/applications/generalledger.php index 994be1ae..ce22dec3 100644 --- a/applications/generalledger.php +++ b/applications/generalledger.php @@ -69,6 +69,9 @@ class general_ledger_app extends application "gl/manage/gl_account_types.php?", 'SA_GLACCOUNTGROUP'); $this->add_rapp_function(2, _("GL Account &Classes"), "gl/manage/gl_account_classes.php?", 'SA_GLACCOUNTCLASS'); + $this->add_rapp_function(2, "",""); + $this->add_rapp_function(2, _("&Revaluation of Currency Accounts"), + "gl/manage/revaluate_currencies.php?", 'SA_EXCHANGERATE'); $this->add_extensions(); } diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index cff9e70d..58825018 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -9,11 +9,11 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -function add_exchange_variation($trans_type, $trans_no, $date_, $acc_id, $account, +function add_exchange_variation($trans_no, $date_, $acc_id, $account, $currency, $person_type_id=null, $person_id = "") { if (is_company_currency($currency)) - return; + return false; if ($date_ == null) $date_ = Today(); $for_amount = 0; @@ -37,27 +37,42 @@ function add_exchange_variation($trans_type, $trans_no, $date_, $acc_id, $accoun $diff = $amount - $for_amount; if ($diff != 0) { - if ($trans_type == null) - $trans_type = ST_JOURNAL; if ($trans_no == null) - $trans_no = get_next_trans_no($trans_type); + $trans_no = get_next_trans_no(ST_JOURNAL); if ($person_type_id == null) $person_type_id = PT_MISC; - add_gl_trans($trans_type, $trans_no, $date_, $account, 0, 0, _("Exchange Variance"), + add_gl_trans(ST_JOURNAL, $trans_no, $date_, $account, 0, 0, _("Exchange Variance"), -$diff, null, $person_type_id, $person_id); - add_gl_trans($trans_type, $trans_no, $date_, get_company_pref('exchange_diff_act'), 0, 0, + add_gl_trans(ST_JOURNAL, $trans_no, $date_, get_company_pref('exchange_diff_act'), 0, 0, _("Exchange Variance"), $diff, null, $person_type_id, $person_id); - } + } + return ($diff != 0); } -function add_exchange_variation_all() +function add_exchange_variation_all($date=null, $ref="", $memo) { + global $Refs; + begin_transaction(); + $exchanged = false; $trans_no = get_next_trans_no(ST_JOURNAL); $sql = "SELECT * FROM ".TB_PREF."bank_accounts"; $result = db_query($sql, "could not retreive bank accounts"); while ($myrow = db_fetch($result)) - add_exchange_variation(ST_JOURNAL, $trans_no, null, $myrow['id'], $myrow['account_code'], - $myrow['currency_code']); + { + if (add_exchange_variation($trans_no, $date, $myrow['id'], $myrow['account_code'], + $myrow['bank_curr_code'])) + $exchanged = true; + } + if ($exchanged) + { + add_comments(ST_JOURNAL, $trans_no, $date, $memo); + if ($ref == "") + $ref = $Refs->get_next(ST_JOURNAL); + $Refs->save(ST_JOURNAL, $trans_no, $ref); + add_audit_trail(ST_JOURNAL, $trans_no, $date); + } + commit_transaction(); + return ($exchanged ? $trans_no : 0); } //---------------------------------------------------------------------------------- // Add bank tranfer to database. @@ -69,7 +84,7 @@ function add_exchange_variation_all() function add_bank_transfer($from_account, $to_account, $date_, $amount, $ref, $memo_, $charge=0) { - global $Refs; + global $Refs, $SysPrefs; begin_transaction(); @@ -81,7 +96,8 @@ function add_bank_transfer($from_account, $to_account, $date_, $from_gl_account = get_bank_gl_account($from_account); $to_gl_account = get_bank_gl_account($to_account); - + + $exchanged = false; $total = 0; // do the source account postings $total += add_gl_trans($trans_type, $trans_no, $date_, $from_gl_account, 0, 0, "", @@ -91,10 +107,12 @@ function add_bank_transfer($from_account, $to_account, $date_, $date_, -($amount + $charge), PT_MISC, "", $currency, "Cannot insert a source bank transaction"); - - add_exchange_variation($trans_type, $trans_no, $date_, $from_account, $from_gl_account, - $currency, PT_MISC, ""); - + if ($SysPrefs->auto_currency_revaluation()) + { + $trans_no1 = get_next_trans_no(ST_JOURNAL); + if (add_exchange_variation($trans_no1, $date_, $from_account, $from_gl_account, $currency)) + $exchanged = true; + } if ($charge != 0) { /* Now Debit bank charge account with charges */ @@ -113,11 +131,17 @@ function add_bank_transfer($from_account, $to_account, $date_, $date_, $amount, PT_MISC, "", $currency, "Cannot insert a destination bank transaction"); - $currency = get_bank_account_currency($to_account); - - add_exchange_variation($trans_type, $trans_no, $date_, $to_account, $to_gl_account, - $currency, PT_MISC, ""); + if ($SysPrefs->auto_currency_revaluation()) + { + $currency = get_bank_account_currency($to_account); + if ($exchanged || add_exchange_variation($trans_no1, $date_, $to_account, $to_gl_account, $currency)) + { + $ref1 = $Refs->get_next(ST_JOURNAL); + $Refs->save(ST_JOURNAL, $trans_no1, $ref1); + add_audit_trail(ST_JOURNAL, $trans_no1, $date_); + } + } add_comments($trans_type, $trans_no, $date_, $memo_); $Refs->save($trans_type, $trans_no, $ref); @@ -142,14 +166,14 @@ function add_bank_transfer($from_account, $to_account, $date_, function add_bank_transaction($trans_type, $from_account, $items, $date_, $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true) { - global $Refs; + global $Refs, $SysPrefs; // we can only handle type 1 (payment)and type 2 (deposit) if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT) display_db_error("Invalid type ($trans_type) sent to add_bank_transaction"); $do_exchange_variance = false; - + $exchanged = false; if ($use_transaction) begin_transaction(); @@ -188,7 +212,9 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, else { $trans_no = get_next_trans_no($trans_type); - $do_exchange_variance = true; + $do_exchange_variance = $SysPrefs->auto_currency_revaluation(); + if ($do_exchange_variance) + $trans_no1 = get_next_trans_no(ST_JOURNAL); } // do the source account postings @@ -221,8 +247,11 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, $person_type_id, $person_id, $currency, "Cannot insert a destination bank transaction"); if ($do_exchange_variance) - add_exchange_variation($trans_type, $trans_no, $date_, $is_bank_to, $gl_item->code_id, - $currency, $person_type_id, $person_id); + { + if (add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id, + $currency, $person_type_id, $person_id)) + $exchanged = true; + } } // store tax details if the gl account is a tax account @@ -238,9 +267,15 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, -$total, null, $person_type_id, $person_id); if ($do_exchange_variance) - add_exchange_variation($trans_type, $trans_no, $date_, $from_account, $bank_gl_account, - $currency, $person_type_id, $person_id); - + { + if ($exchanged || add_exchange_variation($trans_no1, $date_, $from_account, $bank_gl_account, + $currency, $person_type_id, $person_id)) + { + $ref1 = $Refs->get_next(ST_JOURNAL); + $Refs->save(ST_JOURNAL, $trans_no1, $ref1); + add_audit_trail(ST_JOURNAL, $trans_no1, $date_); + } + } add_comments($trans_type, $trans_no, $date_, $memo_); $Refs->save($trans_type, $trans_no, $ref); diff --git a/gl/manage/revaluate_currencies.php b/gl/manage/revaluate_currencies.php new file mode 100644 index 00000000..78c5cbbb --- /dev/null +++ b/gl/manage/revaluate_currencies.php @@ -0,0 +1,129 @@ +. +***********************************************************************/ +$page_security = 'SA_EXCHANGERATE'; +$path_to_root = "../.."; +include_once($path_to_root . "/includes/session.inc"); + +include_once($path_to_root . "/includes/date_functions.inc"); +include_once($path_to_root . "/includes/ui.inc"); +include_once($path_to_root . "/includes/banking.inc"); + +$js = ""; +if ($use_date_picker) + $js .= get_js_date_picker(); +page(_($help_context = "Revaluation of Currency Accounts"), false, false, "", $js); + +if (isset($_GET['AddedID'])) +{ + $trans_no = $_GET['AddedID']; + $trans_type = ST_JOURNAL; + + if ($trans_no == 0) + display_notification_centered( _("No Revaluation was needed")); + else + { + display_notification_centered( _("Transfer has been entered")); + + display_note(get_gl_view_str($trans_type, $trans_no, _("&View the GL Journal Entries for this Transfer"))); + } + + //display_footer_exit(); +} + + +//--------------------------------------------------------------------------------------------- +function check_data() +{ + global $Refs; + + if (!is_date($_POST['date'])) + { + display_error( _("The entered date is invalid.")); + set_focus('date'); + return false; + } + if (!is_date_in_fiscalyear($_POST['date'])) + { + display_error(_("The entered date is not in fiscal year.")); + set_focus('date'); + return false; + } + if (!$Refs->is_valid($_POST['ref'])) + { + display_error(_("You must enter a reference.")); + set_focus('ref'); + return false; + } + + if (!is_new_reference($_POST['ref'], ST_JOURNAL)) + { + display_error(_("The entered reference is already in use.")); + set_focus('ref'); + return false; + } + + return true; +} + +//--------------------------------------------------------------------------------------------- + +function handle_submit() +{ + if (!check_data()) + return; + + $trans_no = add_exchange_variation_all($_POST['date'], $_POST['ref'], $_POST['memo_']); + + meta_forward($_SERVER['PHP_SELF'], "AddedID=$trans_no"); + //clear_data(); +} + + +//--------------------------------------------------------------------------------------------- + +function display_reval() +{ + global $Refs; + start_form(); + start_table(TABLESTYLE2); + + if (!isset($_POST['date'])) + $_POST['date'] = Today(); + date_row(_("Date for Revaluation:"), 'date', '', null, 0, 0, 0, null, true); + ref_row(_("Reference:"), 'ref', '', $Refs->get_next(ST_JOURNAL)); + textarea_row(_("Memo:"), 'memo_', null, 40,4); + end_table(1); + + submit_center('submit', _("Revaluate Currencies"), true, false); + end_form(); +} + +//--------------------------------------------------------------------------------------------- + +function clear_data() +{ + unset($_POST['date_']); + unset($_POST['memo_']); +} + +//--------------------------------------------------------------------------------------------- + +if (get_post('submit')) + handle_submit(); + +//--------------------------------------------------------------------------------------------- + +display_reval(); + +end_page(); + +?> diff --git a/includes/prefs/sysprefs.inc b/includes/prefs/sysprefs.inc index 7b738ad3..f930f85d 100644 --- a/includes/prefs/sysprefs.inc +++ b/includes/prefs/sysprefs.inc @@ -66,6 +66,11 @@ class sys_prefs return $this->prefs['default_dim_required']; } + function auto_currency_revaluation() + { + return $this->prefs['auto_curr_reval']; + } + function allocation_settled_allowance() { global $config_allocation_settled_allowance; diff --git a/sql/alter2.3.sql b/sql/alter2.3.sql index 0a52f483..b52bfa47 100644 --- a/sql/alter2.3.sql +++ b/sql/alter2.3.sql @@ -160,3 +160,5 @@ ALTER TABLE `0_cust_branch` ADD COLUMN `rep_lang` char(5) default NULL; ALTER TABLE `0_suppliers` ADD COLUMN `rep_lang` char(5) default NULL; UPDATE `0_suppliers` set `rep_lang`= 'en_GB' WHERE `curr_code`<>(SELECT value FROM `0_sys_prefs` WHERE name='curr_default'); + +INSERT INTO `0_sys_prefs` (name, category, type, length, value) VALUES ('auto_curr_reval','setup.company', 'smallint','6', '1'); diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 4e257bf4..24805377 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -318,7 +318,7 @@ DROP TABLE IF EXISTS `0_chart_types`; CREATE TABLE `0_chart_types` ( `id` varchar(10) NOT NULL, `name` varchar(60) NOT NULL default '', - `class_id` varchart(3) NOT NULL default '', + `class_id` varchar(3) NOT NULL default '', `parent` varchar(10) NOT NULL default '-1', `inactive` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), @@ -710,13 +710,14 @@ CREATE TABLE `0_fiscal_year` ( PRIMARY KEY (`id`), UNIQUE KEY(`begin`), UNIQUE KEY(`end`) -) TYPE=InnoDB AUTO_INCREMENT=3 ; +) TYPE=InnoDB AUTO_INCREMENT=4 ; ### Data of table `0_fiscal_year` ### INSERT INTO `0_fiscal_year` VALUES ('1', '2008-01-01', '2008-12-31', '0'); INSERT INTO `0_fiscal_year` VALUES ('2', '2009-01-01', '2009-12-31', '0'); +INSERT INTO `0_fiscal_year` VALUES ('3', '2010-01-01', '2010-12-31', '0'); ### Structure of table `0_gl_trans` ### @@ -1808,6 +1809,7 @@ INSERT INTO `0_sys_prefs` VALUES ('time_zone', 'setup.company', 'tinyint', '1', INSERT INTO `0_sys_prefs` VALUES ('add_pct', 'setup.company', 'int', '5', '-1'); INSERT INTO `0_sys_prefs` VALUES ('round_to', 'setup.company', 'int', '5', '1'); INSERT INTO `0_sys_prefs` VALUES ('login_tout', 'setup.company', 'smallint', '6', '600'); +INSERT INTO `0_sys_prefs` VALUES ('auto_curr_reval','setup.company', 'smallint','6', '1'); INSERT INTO `0_sys_prefs` VALUES ('past_due_days', 'glsetup.general', 'int', '11', '30'); INSERT INTO `0_sys_prefs` VALUES ('profit_loss_year_act', 'glsetup.general', 'varchar', '15', '9990'); INSERT INTO `0_sys_prefs` VALUES ('retained_earnings_act', 'glsetup.general', 'varchar', '15', '3590'); diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 30f624da..9eda6233 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -669,7 +669,7 @@ CREATE TABLE `0_fiscal_year` ( ### Data of table `0_fiscal_year` ### -INSERT INTO `0_fiscal_year` VALUES ('1', '2008-01-01', '2008-12-31', '0'); +INSERT INTO `0_fiscal_year` VALUES ('1', '2009-01-01', '2009-12-31', '0'); ### Structure of table `0_gl_trans` ### @@ -1587,6 +1587,7 @@ INSERT INTO `0_sys_prefs` VALUES ('time_zone', 'setup.company', 'tinyint', '1', INSERT INTO `0_sys_prefs` VALUES ('add_pct', 'setup.company', 'int', '5', '-1'); INSERT INTO `0_sys_prefs` VALUES ('round_to', 'setup.company', 'int', '5', '1'); INSERT INTO `0_sys_prefs` VALUES ('login_tout', 'setup.company', 'smallint', '6', '600'); +INSERT INTO `0_sys_prefs` VALUES ('auto_curr_reval','setup.company', 'smallint','6', '1'); INSERT INTO `0_sys_prefs` VALUES ('past_due_days', 'glsetup.general', 'int', '11', '30'); INSERT INTO `0_sys_prefs` VALUES ('profit_loss_year_act', 'glsetup.general', 'varchar', '15', '9990'); INSERT INTO `0_sys_prefs` VALUES ('retained_earnings_act', 'glsetup.general', 'varchar', '15', '3590'); -- 2.30.2