X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fbank_account_reconcile.php;h=81ebc7a0ee06f3b34c552749815ae92d3f7a7098;hb=5a3cbfe6d2df1c8c32edcab8bf93e8a8432a15fc;hp=b91bed9eb612c17f5710c0452af92193f078ff93;hpb=f0f460043b9bcb6153c0f6f82d4a74433efe4ad8;p=fa-stable.git diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index b91bed9e..81ebc7a0 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -9,7 +9,6 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -/* Author Rob Mallon */ $page_security = 'SA_RECONCILE'; $path_to_root = ".."; include($path_to_root . "/includes/db_pager.inc"); @@ -23,14 +22,14 @@ include_once($path_to_root . "/gl/includes/gl_db.inc"); include_once($path_to_root . "/includes/banking.inc"); $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(); add_js_file('reconcile.js'); -page(_("Reconcile Bank Account"), false, false, "", $js); +page(_($help_context = "Reconcile Bank Account"), false, false, "", $js); check_db_has_bank_accounts(_("There are no bank accounts defined in the system.")); @@ -86,20 +85,24 @@ function fmt_credit($row) return $value>0 ? price_format($value) : ''; } -function fmt_person($row) +function fmt_person($trans) { - return payment_person_name($row["person_type_id"],$row["person_id"]); + return get_counterparty_name($trans["type"], $trans["trans_no"]); +} + +function fmt_memo($row) +{ + $value = $row["memo_"]; + return $value; } -$update_pager = false; function update_data() { - global $Ajax, $update_pager; + global $Ajax; unset($_POST["beg_balance"]); unset($_POST["end_balance"]); $Ajax->activate('summary'); - $update_pager = true; } //--------------------------------------------------------------------------------------------- // Update db record if respective checkbox value has changed. @@ -108,7 +111,8 @@ function change_tpl_flag($reconcile_id) { global $Ajax; - if (!check_date()) + if (!check_date() + && check_value("rec_".$reconcile_id)) // temporary fix return false; if (get_post('bank_date')=='') // new reconciliation @@ -117,19 +121,33 @@ function change_tpl_flag($reconcile_id) $_POST['bank_date'] = date2sql(get_post('reconcile_date')); $reconcile_value = check_value("rec_".$reconcile_id) ? ("'".$_POST['bank_date'] ."'") : 'NULL'; - $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value WHERE id=$reconcile_id"; + + 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; +} - db_query($sql, "Can't change reconciliation status"); - // save last reconcilation status (date, end balance) - $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='" - .date2sql($_POST["reconcile_date"])."', - ending_reconcile_balance=".input_num("end_balance") - ." WHERE id=".$_POST["bank_account"]; +function set_tpl_flag($reconcile_id) +{ + global $Ajax; - $result = db_query($sql2,"Error updating reconciliation information"); + 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'); - return true; } if (!isset($_POST['reconcile_date'])) { // init page @@ -156,17 +174,27 @@ $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'); } //------------------------------------------------------------------------------------------------ start_form(); -start_table("class='tablestyle_noborder'"); +start_table(TABLESTYLE_NOBORDER); start_row(); bank_accounts_list_cells(_("Account:"), 'bank_account', null, true); @@ -175,17 +203,7 @@ bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'), end_row(); end_table(); -$date = date2sql(get_post('reconcile_date')); - -$sql = "SELECT MAX(reconciled) as last_date, - SUM(IF(reconciled<='$date', amount, 0)) as end_balance, - SUM(IF(reconciled<'$date', amount, 0)) as beg_balance, - SUM(amount) as total - FROM ".TB_PREF."bank_trans trans - WHERE bank_act=".$_POST['bank_account']; -// ." AND trans.reconciled IS NOT NULL"; - -$result = db_query($sql,"Cannot retrieve reconciliation data"); +$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"]); @@ -196,11 +214,8 @@ if ($row = db_fetch($result)) { $_POST["end_balance"] = price_format($row["end_balance"]); if (get_post('bank_date')) { // if it is the last updated bank statement retrieve ending balance - $sql = "SELECT ending_reconcile_balance - FROM ".TB_PREF."bank_accounts WHERE id=".$_POST['bank_account'] - . " AND last_reconciled_date='".$_POST['bank_date']."'"; - $result = db_query($sql,"Cannot retrieve last reconciliation"); - $row = db_fetch($result); + + $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']); if($row) { $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]); } @@ -212,7 +227,7 @@ echo "
"; div_start('summary'); -start_table($table_style); +start_table(TABLESTYLE); $th = array(_("Reconcile Date"), _("Beginning
Balance"), _("Ending
Balance"), _("Account
Total"),_("Reconciled
Amount"), _("Difference")); table_header($th); @@ -241,13 +256,7 @@ echo "
"; if (!isset($_POST['bank_account'])) $_POST['bank_account'] = ""; -$sql = "SELECT type, trans_no, ref, trans_date, - amount, person_id, person_type_id, reconciled, id - FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "' - AND (reconciled IS NULL OR reconciled='". $date ."') - ORDER BY trans_date,".TB_PREF."bank_trans.id"; -// or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id"; +$sql = get_sql_for_bank_account_reconcile(get_post('bank_account'), get_post('reconcile_date')); $act = get_bank_account($_POST["bank_account"]); display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); @@ -261,21 +270,23 @@ display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); _("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); - $table->width = "60%"; + $table->width = "80%"; display_db_pager($table); br(1); -submit_center('Reconcile', _("Reconcile"), true, '', null); - +echo '
'; +submit('Reconcile', _("Reconcile"), true, '', null); +submit('ReconcileAll', _("Reconcile All"), true, ''); +echo '
'; end_form(); //------------------------------------------------------------------------------------------------ end_page(); -?> \ No newline at end of file