gl/bank_account_reconcile.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 /* Author Rob Mallon */
  13 $page_security = 8;
  14 $path_to_root="..";
  15 include($path_to_root . "/includes/db_pager.inc");
  16 include_once($path_to_root . "/includes/session.inc");
  17
  18 include_once($path_to_root . "/includes/date_functions.inc");
  19 include_once($path_to_root . "/includes/ui.inc");
  20 include_once($path_to_root . "/includes/data_checks.inc");
  21
  22 include_once($path_to_root . "/gl/includes/gl_db.inc");
  23 include_once($path_to_root . "/includes/banking.inc");
  24
  25 $js = "";
  26 if ($use_popup_windows)
  27         $js .= get_js_open_window(800, 500);
  28 if ($use_date_picker)
  29         $js .= get_js_date_picker();
  30
  31 add_js_file('reconcile.js');
  32
  33 page(_("Reconcile Bank Account"), false, false, "", $js);
  34
  35 check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
  36
  37 function check_date() {
  38         if (!is_date(get_post('reconcile_date'))) {
  39                 display_error(_("Invalid reconcile date format"));
  40                 set_focus('reconcile_date');
  41                 return false;
  42         }
  43         return true;
  44 }
  45 //
  46 //      This function can be used directly in table pager
  47 //      if we would like to change page layout.
  48 //
  49 function rec_checkbox($row)
  50 {
  51         $name = "rec_" .$row['id'];
  52         $hidden = 'last['.$row['id'].']';
  53         $value = $row['reconciled'] != '';
  54
  55 // save also in hidden field for testing during 'Reconcile'
  56         return checkbox(null, $name, $value, true, _('Reconcile this transaction'))
  57                 . hidden($hidden, $value, false);
  58 }
  59
  60 function systype_name($dummy, $type)
  61 {
  62         return systypes::name($type);
  63 }
  64
  65 function trans_view($trans)
  66 {
  67         return get_trans_view_str($trans["type"], $trans["trans_no"]);
  68 }
  69
  70 function gl_view($row)
  71 {
  72         return get_gl_view_str($row["type"], $row["trans_no"]);
  73 }
  74
  75 function fmt_debit($row)
  76 {
  77         $value = $row["amount"];
  78         return $value>=0 ? price_format($value) : '';
  79 }
  80
  81 function fmt_credit($row)
  82 {
  83         $value = -$row["amount"];
  84         return $value>0 ? price_format($value) : '';
  85 }
  86
  87 function fmt_person($row)
  88 {
  89         return payment_person_types::person_name($row["person_type_id"],$row["person_id"]);
  90 }
  91
  92 $update_pager = false;
  93 function update_data()
  94 {
  95         global $Ajax, $update_pager;
  96
  97         unset($_POST["beg_balance"]);
  98         unset($_POST["end_balance"]);
  99         $Ajax->activate('summary');
 100         $update_pager = true;
 101 }
 102 //---------------------------------------------------------------------------------------------
 103 // Update db record if respective checkbox value has changed.
 104 //
 105 function change_tpl_flag($reconcile_id)
 106 {
 107         global  $Ajax;
 108
 109         if (!check_date())
 110                 return false;
 111
 112         if (get_post('bank_date')=='')  // new reconciliation
 113                 $Ajax->activate('bank_date');
 114
 115         $_POST['bank_date'] = date2sql(get_post('reconcile_date'));
 116         $reconcile_value = check_value("rec_".$reconcile_id)
 117                                                 ? ("'".$_POST['bank_date'] ."'") : 'NULL';
 118         $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=".db_escape($reconcile_value)
 119         ." WHERE id=".db_escape($reconcile_id);
 120
 121         db_query($sql, "Can't change reconciliation status");
 122         // save last reconcilation status (date, end balance)
 123     $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
 124                         .date2sql($_POST["reconcile_date"])."',
 125             ending_reconcile_balance=".input_num("end_balance")
 126                         ." WHERE id=".db_escape($_POST["bank_account"]);
 127
 128         $result = db_query($sql2,"Error updating reconciliation information");
 129         $Ajax->activate('reconciled');
 130         $Ajax->activate('difference');
 131         return true;
 132 }
 133
 134 if (!isset($_POST['reconcile_date'])) { // init page
 135         $_POST['reconcile_date'] = Today();
 136 //      $_POST['bank_date'] = date2sql(Today());
 137 }
 138
 139 if (list_updated('bank_account')) {
 140     $Ajax->activate('bank_date');
 141         update_data();
 142 }
 143 if (list_updated('bank_date')) {
 144         $_POST['reconcile_date'] =
 145                 get_post('bank_date')=='' ? Today() : sql2date($_POST['bank_date']);
 146         update_data();
 147 }
 148 if (get_post('_reconcile_date_changed')) {
 149         $_POST['bank_date'] = check_date() ? date2sql(get_post('reconcile_date')) : '';
 150     $Ajax->activate('bank_date');
 151         update_data();
 152 }
 153
 154 $id = find_submit('_rec_');
 155 if ($id != -1)
 156         change_tpl_flag($id);
 157
 158 if (isset($_POST['Reconcile'])) {
 159         set_focus('bank_date');
 160         foreach($_POST['last'] as $id => $value)
 161                 if ($value != check_value('rec_'.$id))
 162                         if(!change_tpl_flag($id)) break;
 163     $Ajax->activate('_page_body');
 164 }
 165
 166 //------------------------------------------------------------------------------------------------
 167 start_form();
 168 start_table("class='tablestyle_noborder'");
 169 start_row();
 170 bank_accounts_list_cells(_("Account:"), 'bank_account', null, true);
 171
 172 bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'),
 173         'bank_date', null, true, _("New"));
 174 end_row();
 175 end_table();
 176
 177 $date = date2sql(get_post('reconcile_date'));
 178
 179 $sql = "SELECT MAX(reconciled) as last_date,
 180                  SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
 181                  SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
 182                  SUM(amount) as total
 183         FROM ".TB_PREF."bank_trans trans
 184         WHERE bank_act=".db_escape($_POST['bank_account']);
 185 //      ." AND trans.reconciled IS NOT NULL";
 186
 187 $result = db_query($sql,"Cannot retrieve reconciliation data");
 188
 189 if ($row = db_fetch($result)) {
 190         $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]);
 191         $total = $row["total"];
 192         if (!isset($_POST["beg_balance"])) { // new selected account/statement
 193                 $_POST["last_date"] = sql2date($row["last_date"]);
 194                 $_POST["beg_balance"] = price_format($row["beg_balance"]);
 195                 $_POST["end_balance"] = price_format($row["end_balance"]);
 196                 if (get_post('bank_date')) {
 197                         // if it is the last updated bank statement retrieve ending balance
 198                         $sql = "SELECT ending_reconcile_balance
 199                                 FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($_POST['bank_account'])
 200                                 . " AND last_reconciled_date=".db_escape($_POST['bank_date']);
 201                         $result = db_query($sql,"Cannot retrieve last reconciliation");
 202                         $row = db_fetch($result);
 203                         if($row) {
 204                                 $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]);
 205                         }
 206                 }
 207         }
 208 }
 209
 210 echo "<hr>";
 211
 212 div_start('summary');
 213
 214 start_table($table_style);
 215 $th = array(_("Reconcile Date"), _("Beginning<br>Balance"),
 216         _("Ending<br>Balance"), _("Account<br>Total"),_("Reconciled<br>Amount"), _("Difference"));
 217 table_header($th);
 218 start_row();
 219
 220 date_cells("", "reconcile_date", _('Date of bank statement to reconcile'),
 221         null, 0, 0, 0, null, true);
 222
 223 amount_cells_ex("", "beg_balance", 15);
 224
 225 amount_cells_ex("", "end_balance", 15);
 226
 227 $reconciled = input_num('reconciled');
 228 $difference = input_num("end_balance") - input_num("beg_balance") - $reconciled;
 229
 230 amount_cell($total);
 231 amount_cell($reconciled, false, '', "reconciled");
 232 amount_cell($difference, false, '', "difference");
 233
 234 end_row();
 235 end_table();
 236 div_end();
 237 echo "<hr>";
 238 //------------------------------------------------------------------------------------------------
 239
 240 if (!isset($_POST['bank_account']))
 241     $_POST['bank_account'] = "";
 242
 243 $sql = "SELECT  type, trans_no, ref, trans_date,
 244                                 amount, person_id, person_type_id, reconciled, id
 245                 FROM ".TB_PREF."bank_trans
 246                 WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . "
 247                         AND (reconciled IS NULL OR reconciled='". $date ."')
 248                 ORDER BY trans_date,".TB_PREF."bank_trans.id";
 249 // or   ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
 250
 251 $act = get_bank_account($_POST["bank_account"]);
 252 display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
 253
 254         $cols =
 255         array(
 256                 _("Type") => array('fun'=>'systype_name', 'ord'=>''),
 257                 _("#") => array('fun'=>'trans_view', 'ord'=>''),
 258                 _("Reference"),
 259                 _("Date") => 'date',
 260                 _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'),
 261                 _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'),
 262             _("Person/Item") => array('fun'=>'fmt_person'),
 263                 array('insert'=>true, 'fun'=>'gl_view'),
 264                 "X"=>array('insert'=>true, 'fun'=>'rec_checkbox')
 265            );
 266         $table =& new_db_pager('trans_tbl', $sql, $cols);
 267         if ($update_pager) {
 268                 $table->set_sql($sql);
 269                 $table->set_columns($cols);
 270         }
 271         $table->width = "60%";
 272         display_db_pager($table);
 273
 274 br(1);
 275 submit_center('Reconcile', _("Reconcile"), true, '', null);
 276
 277 end_form();
 278
 279 //------------------------------------------------------------------------------------------------
 280
 281 end_page();
 282
 283 ?>