X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fbank_account_reconcile.php;h=a5738946451ea7385e6890a128de1b3dd48d939b;hb=c2b751f91d79c69bb802b1bff13a3c4bdecb87b0;hp=7b467f58521703e7b4bb59ae86c061d212cee7d7;hpb=74936b5a74214017efe2ba1d0b8f16215873b844;p=fa-stable.git diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index 7b467f58..a5738946 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -1,17 +1,17 @@ . + See the License here . ***********************************************************************/ -/* Author Rob Mallon */ -$page_security = 8; -$path_to_root=".."; +$page_security = 'SA_RECONCILE'; +$path_to_root = ".."; +include($path_to_root . "/includes/db_pager.inc"); include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); @@ -21,205 +21,272 @@ 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) +if ($SysPrefs->use_popup_windows) $js .= get_js_open_window(800, 500); -if ($use_date_picker) +if (user_use_date_picker()) $js .= get_js_date_picker(); -page(_("Reconcile Bank Account"), false, false, "", $js); - -?> - -=0 ? price_format($value) : ''; +} + +function fmt_credit($row) +{ + $value = -$row["amount"]; + return $value>0 ? price_format($value) : ''; +} + +function fmt_person($trans) +{ + return get_counterparty_name($trans["type"], $trans["trans_no"]); +} + +function fmt_memo($row) +{ + $value = $row["memo_"]; + return $value; +} + +function update_data() +{ + global $Ajax; + + unset($_POST["beg_balance"]); + unset($_POST["end_balance"]); + $Ajax->activate('summary'); +} +//--------------------------------------------------------------------------------------------- +// Update db record if respective checkbox value has changed. // -if (get_post('Show')) +function change_tpl_flag($reconcile_id) { - $Ajax->activate('trans_tbl'); + global $Ajax; + + if (!check_date() + && check_value("rec_".$reconcile_id)) // temporary fix + return false; + + if (get_post('bank_date')=='') // new reconciliation + $Ajax->activate('bank_date'); + + $_POST['bank_date'] = date2sql(get_post('reconcile_date')); + $reconcile_value = check_value("rec_".$reconcile_id) + ? ("'".$_POST['bank_date'] ."'") : 'NULL'; + + update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'], + input_num('end_balance'), $_POST['bank_account']); + + $Ajax->activate('reconciled'); + $Ajax->activate('difference'); + return true; } + +function set_tpl_flag($reconcile_id) +{ + global $Ajax; + + if (check_value("rec_".$reconcile_id)) + return; + + if (get_post('bank_date')=='') // new reconciliation + $Ajax->activate('bank_date'); + + $_POST['bank_date'] = date2sql(get_post('reconcile_date')); + $reconcile_value = ("'".$_POST['bank_date'] ."'"); + + update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'], + input_num('end_balance'), $_POST['bank_account']); + + $Ajax->activate('reconciled'); + $Ajax->activate('difference'); +} + +if (!isset($_POST['reconcile_date'])) { // init page + $_POST['reconcile_date'] = new_doc_date(); +// $_POST['bank_date'] = date2sql(Today()); +} + +if (list_updated('bank_account')) { + $Ajax->activate('bank_date'); + update_data(); +} +if (list_updated('bank_date')) { + $_POST['reconcile_date'] = + get_post('bank_date')=='' ? Today() : sql2date($_POST['bank_date']); + update_data(); +} +if (get_post('_reconcile_date_changed')) { + $_POST['bank_date'] = check_date() ? date2sql(get_post('reconcile_date')) : ''; + $Ajax->activate('bank_date'); + update_data(); +} + +$id = find_submit('_rec_'); +if ($id != -1) + change_tpl_flag($id); + + +if (isset($_POST['Reconcile'])) { + set_focus('bank_date'); + foreach($_POST['last'] as $id => $value) + if ($value != check_value('rec_'.$id)) + if(!change_tpl_flag($id)) break; + + $Ajax->activate('_page_body'); +} + +if (isset($_POST['ReconcileAll'])) { + set_focus('bank_date'); + foreach($_POST['last'] as $id => $value) + set_tpl_flag($id); + + $Ajax->activate('_page_body'); +} + //------------------------------------------------------------------------------------------------ -//Added 1/9/09 start_form(); -start_table("class='tablestyle_noborder'"); +start_table(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'); +bank_accounts_list_cells(_("Account:"), 'bank_account', null, true); -submit_cells('Show',_("Show"),'','', false); +bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'), + 'bank_date', null, true, _("New")); 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")); + +$result = get_max_reconciled(get_post('reconcile_date'), $_POST['bank_account']); + +if ($row = db_fetch($result)) { + $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]); + $total = $row["total"]; + if (!isset($_POST["beg_balance"])) { // new selected account/statement + $_POST["last_date"] = sql2date($row["last_date"]); + $_POST["beg_balance"] = price_format($row["beg_balance"]); + $_POST["end_balance"] = price_format($row["end_balance"]); + if (get_post('bank_date')) { + // if it is the last updated bank statement retrieve ending balance + + $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']); + if($row) { + $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]); + } + } + } +} + +echo "
"; + +div_start('summary'); + +start_table(TABLESTYLE); +$th = array(_("Reconcile Date"), _("Beginning
Balance"), + _("Ending
Balance"), _("Account
Total"),_("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"); + +date_cells("", "reconcile_date", _('Date of bank statement to reconcile'), + get_post('bank_date')=='', 0, 0, 0, null, true); + +amount_cells_ex("", "beg_balance", 15); + +amount_cells_ex("", "end_balance", 15); + +$reconciled = input_num('reconciled'); +$difference = input_num("end_balance") - input_num("beg_balance") - $reconciled; + +amount_cell($total); +amount_cell($reconciled, false, '', "reconciled"); +amount_cell($difference, false, '', "difference"); + end_row(); end_table(); +div_end(); 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"); +$sql = get_sql_for_bank_account_reconcile(get_post('bank_account'), get_post('reconcile_date')); -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 + $cols = + array( + _("Type") => array('fun'=>'systype_name', 'ord'=>''), + _("#") => array('fun'=>'trans_view', 'ord'=>''), + _("Reference"), + _("Date") => 'date', + _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'), + _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'), + _("Person/Item") => array('fun'=>'fmt_person'), + _("Memo") => array('fun'=>'fmt_memo'), + array('insert'=>true, 'fun'=>'gl_view'), + "X"=>array('insert'=>true, 'fun'=>'rec_checkbox') + ); + $table =& new_db_pager('trans_tbl', $sql, $cols); -hidden('command', 'Reconcile'); -hidden('Show', 'Show'); -hidden('bank_account', $_POST["bank_account"]); -hidden('TransAfterDate',$_POST["TransAfterDate"]); -hidden('TransToDate',$_POST["TransToDate"]); -hidden('ShowReconciled',$_POST["ShowReconciled"]); + $table->width = "80%"; + display_db_pager($table); -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(); +br(1); +echo '
'; +submit('Reconcile', _("Reconcile"), true, '', null); +submit('ReconcileAll', _("Reconcile All"), true, ''); +echo '
'; end_form(); //------------------------------------------------------------------------------------------------ end_page(); -?> \ No newline at end of file