From: Joe Hunt Date: Sat, 10 Jan 2009 09:17:58 +0000 (+0000) Subject: Addition of Bank Reconciliation. Author Rob Mallon X-Git-Tag: v2.4.2~19^2~1629 X-Git-Url: https://delta.frontaccounting.com/gitweb/?p=fa-stable.git;a=commitdiff_plain;h=74936b5a74214017efe2ba1d0b8f16215873b844 Addition of Bank Reconciliation. Author Rob Mallon --- diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 12c78db1..699816ad 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,12 @@ Legend: ! -> Note $ -> Affected files +10-Jan-2009 Joe Hunt ++ Addition of Bank Reconciliation. Author Rob Mallon +$ /applications/generalledger.php + /gl/bank_account_reconcile.php (new file) + /sql/alter2.1.sql + 09-Jan-2009 Janusz Dobrowolski + Added hook file for localized functions. $ /includes/lang/language.php diff --git a/applications/generalledger.php b/applications/generalledger.php index 6e3c48ca..1e4468e5 100644 --- a/applications/generalledger.php +++ b/applications/generalledger.php @@ -14,7 +14,8 @@ $this->add_lapp_function(0, _("Bank Account &Transfers"),"gl/bank_transfer.php?"); $this->add_rapp_function(0, _("&Journal Entry"),"gl/gl_journal.php?NewJournal=Yes"); $this->add_rapp_function(0, _("&Budget Entry"),"gl/gl_budget.php?"); - + //Added by Rob Mallon on 1/8/09 + $this->add_rapp_function(0, _("Reconcile Bank Account"),"gl/bank_account_reconcile.php?"); $this->add_module(_("Inquiries and Reports")); $this->add_lapp_function(1, _("Bank Account &Inquiry"),"gl/inquiry/bank_inquiry.php?"); $this->add_lapp_function(1, _("GL Account &Inquiry"),"gl/inquiry/gl_account_inquiry.php?"); diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php new file mode 100644 index 00000000..7b467f58 --- /dev/null +++ b/gl/bank_account_reconcile.php @@ -0,0 +1,225 @@ +. +***********************************************************************/ +/* Author Rob Mallon */ +$page_security = 8; +$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/data_checks.inc"); + +include_once($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/includes/banking.inc"); + +//Added 1/9/09 +if (isset($_POST["command"]) && $_POST["command"]=="Reconcile") { + //echo "Made it\n"; + $reconcile_id = strtok($_POST["reconcile_idtokens"], "|"); + while ($reconcile_id !== false) { + $formfield="reconcile".$reconcile_id; + if (!isset($_POST[$formfield])) + $_POST[$formfield] = 0; + $reconcile_value=$_POST[$formfield]; + $sql="UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value WHERE id=$reconcile_id"; + $result = db_query($sql,"Error setting reconcile flag on trans id $reconcile_id"); + $reconcile_id = strtok("|"); + } + $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='".date2sql($_POST["reconcile_date"])."', + ending_reconcile_balance=".$_POST["reconcile_ending_balance"]." WHERE id=".$_POST["bank_account"]; + $result = db_query($sql2,"Error updating reconciliation information"); +} + +$js = ""; +if ($use_popup_windows) + $js .= get_js_open_window(800, 500); +if ($use_date_picker) + $js .= get_js_date_picker(); +page(_("Reconcile Bank Account"), false, false, "", $js); + +?> + +activate('trans_tbl'); +} +//------------------------------------------------------------------------------------------------ +//Added 1/9/09 +start_form(); +start_table("class='tablestyle_noborder'"); +start_row(); +bank_accounts_list_cells(_("Account:"), 'bank_account', null); + +date_cells(_("From:"), 'TransAfterDate', '', null, -30); +date_cells(_("To:"), 'TransToDate'); +//Added 1/8/09 +if (!isset($_POST['ShowReconciled'])) + $_POST['ShowReconciled'] = 0; +$show_reconciled = $_POST['ShowReconciled']; +yesno_list_cells(_("Show Reconciled:"), 'ShowReconciled'); + +submit_cells('Show',_("Show"),'','', false); +end_row(); +end_table(); +end_form(); +//Added 1/9/09 +$reconcile_sql="SELECT last_reconciled_date,ending_reconcile_balance FROM ".TB_PREF."bank_accounts WHERE id=".$_POST['bank_account']; +$reconcile_result = db_query($reconcile_sql,"Error"); +if ($reconcile_row = db_fetch($reconcile_result)) { + echo "
"; + start_table("class='tablestyle_noborder'"); + start_row(); + label_cell("Last Reconciled Date: "); + label_cell("".sql2date($reconcile_row["last_reconciled_date"]).""); + label_cell("    "); + label_cell("Last Reconciled Ending Balance: "); + amount_cell($reconcile_row["ending_reconcile_balance"],true); + end_row(); + end_table(); +} else { + echo "Error: $reconcile_sql
"; +} +start_form(false,false,"", 'reconcileform'); +start_table($table_style); +$th = array(_("Reconcile Date"), _("Beginning
Balance"), _("Ending
Balance"), _("Reconciled
Amount"), _("Difference")); +table_header($th); +start_row(); +date_cells("", "reconcile_date"); +text_cells("", "reconcile_beginning_balance", $reconcile_row["ending_reconcile_balance"], 7, "", false, "", "", "onchange='handleReconcileDifference(this.form);'"); +text_cells("", "reconcile_ending_balance", 0,7, "", false, "", "", "onchange='handleReconcileDifference(this.form);'"); +text_cells("", "reconcile_amount", 0, 7, "", false, "", "", "READONLY"); +text_cells("", "reconcile_difference", 0, 7, "", false, "", "", "READONLY"); +end_row(); +end_table(); +echo "
"; +//------------------------------------------------------------------------------------------------ + +$date_after = date2sql($_POST['TransAfterDate']); +$date_to = date2sql($_POST['TransToDate']); + +if (!isset($_POST['bank_account'])) + $_POST['bank_account'] = ""; + +//Modified 1/9/09 +$sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans + WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "' + AND trans_date >= '$date_after' + AND trans_date <= '$date_to'"; +//Added line 1/9/09 +if ($show_reconciled=='0') + $sql .= " AND ".TB_PREF."bank_trans.reconciled=0 "; +$sql .= " ORDER BY trans_date,".TB_PREF."bank_trans.id"; + +$result = db_query($sql,"The transactions for '" . $_POST['bank_account'] . "' could not be retrieved"); + +div_start('trans_tbl'); +$act = get_bank_account($_POST["bank_account"]); +display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); + +//Added 1/9/09 + +hidden('command', 'Reconcile'); +hidden('Show', 'Show'); +hidden('bank_account', $_POST["bank_account"]); +hidden('TransAfterDate',$_POST["TransAfterDate"]); +hidden('TransToDate',$_POST["TransToDate"]); +hidden('ShowReconciled',$_POST["ShowReconciled"]); + +start_table($table_style); + +$th = array(_("Type"), _("#"), _("Reference"), _("Date"), + _("Debit"), _("Credit"), _("Person/Item"), "", "X"); +table_header($th); +$idtokens=""; +$k = 0; //row colour counter +while ($myrow = db_fetch($result)) +{ + $idtokens.=$myrow["id"]."|"; + alt_table_row_color($k); + + $trandate = sql2date($myrow["trans_date"]); + //Added 1/9/09 + $reconcile_check_name="reconcile".$myrow["id"]; + label_cell(systypes::name($myrow["type"])); + label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"])); + label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"],$myrow['ref'])); + label_cell($trandate); + display_debit_or_credit_cells($myrow["amount"]); + //amount_cell($running_total); + label_cell(payment_person_types::person_name($myrow["person_type_id"],$myrow["person_id"])); + label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"])); + //Added 1/9/09 I needed the javascript action onclick, so I made my own checkbox + echo ""; + echo ""; + echo ""; + //check_cells('', $reconcile_check_name, $myrow["reconciled"], false); + end_row(); + //Removed by Rob Mallon on 1/8/09 + //if ($j == 12) + //{ + // $j = 1; + // table_header($th); + //} + //$j++; +} +//end of while loop + +end_table(2); +hidden('reconcile_idtokens',$idtokens); +submit_center('Reconcile',_("Reconcile"),true,'', false); + +div_end(); +end_form(); + +//------------------------------------------------------------------------------------------------ + +end_page(); + +?> \ No newline at end of file diff --git a/sql/alter2.1.sql b/sql/alter2.1.sql index 31dbd730..a65277d3 100644 --- a/sql/alter2.1.sql +++ b/sql/alter2.1.sql @@ -68,6 +68,13 @@ ALTER TABLE `0_debtor_trans` ADD `dimension2_id` int(11) NOT NULL default '0'; ALTER TABLE `0_bank_accounts` DROP COLUMN `id`; ALTER TABLE `0_bank_accounts` DROP PRIMARY KEY; ALTER TABLE `0_bank_accounts` ADD `id` SMALLINT(6) AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE `0_bank_accounts` DROP COLUMN `last_reconciled_date`; +ALTER TABLE `0_bank_accounts` ADD `last_reconciled_date` timestamp NOT NULL default '0000-00-00'; +ALTER TABLE `0_bank_accounts` DROP COLUMN `ending_reconcile_balance`; +ALTER TABLE `0_bank_accounts` ADD `ending_reconcile_balance` double NOT NULL default '0'; + +ALTER TABLE `0_bank_trans` DROP COLUMN `reconciled`; +ALTER TABLE `0_bank_trans` ADD `reconciled` tinyint(1) NOT NULL default '0'; ALTER TABLE `0_users` DROP COLUMN `query_size`; ALTER TABLE `0_users` ADD `query_size` TINYINT(1) DEFAULT '10';