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 = 'SA_RECONCILE'; 14$path_to_root = ".."; 15include($path_to_root . "/includes/db_pager.inc"); 16include_once($path_to_root . "/includes/session.inc"); 17 18include_once($path_to_root . "/includes/date_functions.inc"); 19include_once($path_to_root . "/includes/ui.inc"); 20include_once($path_to_root . "/includes/data_checks.inc"); 21 22include_once($path_to_root . "/gl/includes/gl_db.inc"); 23include_once($path_to_root . "/includes/banking.inc"); 24 25$js = ""; 26if ($use_popup_windows) 27 $js .= get_js_open_window(800, 500); 28if ($use_date_picker) 29 $js .= get_js_date_picker(); 30 31add_js_file('reconcile.js'); 32 33page(_($help_context = "Reconcile Bank Account"), false, false, "", $js); 34 35check_db_has_bank_accounts(_("There are no bank accounts defined in the system.")); 36 37function 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// 49function 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 60function systype_name($dummy, $type) 61{ 62 global $systypes_array; 63 64 return $systypes_array[$type]; 65} 66 67function trans_view($trans) 68{ 69 return get_trans_view_str($trans["type"], $trans["trans_no"]); 70} 71 72function gl_view($row) 73{ 74 return get_gl_view_str($row["type"], $row["trans_no"]); 75} 76 77function fmt_debit($row) 78{ 79 $value = $row["amount"]; 80 return $value>=0 ? price_format($value) : ''; 81} 82 83function fmt_credit($row) 84{ 85 $value = -$row["amount"]; 86 return $value>0 ? price_format($value) : ''; 87} 88 89function fmt_person($row) 90{ 91 return payment_person_name($row["person_type_id"],$row["person_id"]); 92} 93 94$update_pager = false; 95function update_data() 96{ 97 global $Ajax, $update_pager; 98 99 unset($_POST["beg_balance"]); 100 unset($_POST["end_balance"]); 101 $Ajax->activate('summary'); 102 $update_pager = true; 103} 104//--------------------------------------------------------------------------------------------- 105// Update db record if respective checkbox value has changed. 106// 107function change_tpl_flag($reconcile_id) 108{ 109 global $Ajax; 110 111 if (!check_date() 112 && check_value("rec_".$reconcile_id)) // temporary fix 113 return false; 114 115 if (get_post('bank_date')=='') // new reconciliation 116 $Ajax->activate('bank_date'); 117 118 $_POST['bank_date'] = date2sql(get_post('reconcile_date')); 119 $reconcile_value = check_value("rec_".$reconcile_id) 120 ? ("'".$_POST['bank_date'] ."'") : 'NULL'; 121 122 update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'], 123 input_num('end_balance'), $_POST['bank_account']); 124 125 $Ajax->activate('reconciled'); 126 $Ajax->activate('difference'); 127 return true; 128} 129 130if (!isset($_POST['reconcile_date'])) { // init page 131 $_POST['reconcile_date'] = new_doc_date(); 132// $_POST['bank_date'] = date2sql(Today()); 133} 134 135if (list_updated('bank_account')) { 136 $Ajax->activate('bank_date'); 137 update_data(); 138} 139if (list_updated('bank_date')) { 140 $_POST['reconcile_date'] = 141 get_post('bank_date')=='' ? Today() : sql2date($_POST['bank_date']); 142 update_data(); 143} 144if (get_post('_reconcile_date_changed')) { 145 $_POST['bank_date'] = check_date() ? date2sql(get_post('reconcile_date')) : ''; 146 $Ajax->activate('bank_date'); 147 update_data(); 148} 149 150$id = find_submit('_rec_'); 151if ($id != -1) 152 change_tpl_flag($id); 153 154if (isset($_POST['Reconcile'])) { 155 set_focus('bank_date'); 156 foreach($_POST['last'] as $id => $value) 157 if ($value != check_value('rec_'.$id)) 158 if(!change_tpl_flag($id)) break; 159 $Ajax->activate('_page_body'); 160} 161 162//------------------------------------------------------------------------------------------------ 163start_form(); 164start_table(TABLESTYLE_NOBORDER); 165start_row(); 166bank_accounts_list_cells(_("Account:"), 'bank_account', null, true); 167 168bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'), 169 'bank_date', null, true, _("New")); 170end_row(); 171end_table(); 172 173$result = get_max_reconciled(get_post('reconcile_date'), $_POST['bank_account']); 174 175if ($row = db_fetch($result)) { 176 $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]); 177 $total = $row["total"]; 178 if (!isset($_POST["beg_balance"])) { // new selected account/statement 179 $_POST["last_date"] = sql2date($row["last_date"]); 180 $_POST["beg_balance"] = price_format($row["beg_balance"]); 181 $_POST["end_balance"] = price_format($row["end_balance"]); 182 if (get_post('bank_date')) { 183 // if it is the last updated bank statement retrieve ending balance 184 185 $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']); 186 if($row) { 187 $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]); 188 } 189 } 190 } 191} 192 193echo "<hr>"; 194 195div_start('summary'); 196 197start_table(TABLESTYLE); 198$th = array(_("Reconcile Date"), _("Beginning<br>Balance"), 199 _("Ending<br>Balance"), _("Account<br>Total"),_("Reconciled<br>Amount"), _("Difference")); 200table_header($th); 201start_row(); 202 203date_cells("", "reconcile_date", _('Date of bank statement to reconcile'), 204 get_post('bank_date')=='', 0, 0, 0, null, true); 205 206amount_cells_ex("", "beg_balance", 15); 207 208amount_cells_ex("", "end_balance", 15); 209 210$reconciled = input_num('reconciled'); 211$difference = input_num("end_balance") - input_num("beg_balance") - $reconciled; 212 213amount_cell($total); 214amount_cell($reconciled, false, '', "reconciled"); 215amount_cell($difference, false, '', "difference"); 216 217end_row(); 218end_table(); 219div_end(); 220echo "<hr>"; 221//------------------------------------------------------------------------------------------------ 222 223if (!isset($_POST['bank_account'])) 224 $_POST['bank_account'] = ""; 225 226$sql = get_sql_for_bank_account_reconcile($_POST['bank_account'], get_post('reconcile_date')); 227 228$act = get_bank_account($_POST["bank_account"]); 229display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); 230 231 $cols = 232 array( 233 _("Type") => array('fun'=>'systype_name', 'ord'=>''), 234 _("#") => array('fun'=>'trans_view', 'ord'=>''), 235 _("Reference"), 236 _("Date") => 'date', 237 _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'), 238 _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'), 239 _("Person/Item") => array('fun'=>'fmt_person'), 240 array('insert'=>true, 'fun'=>'gl_view'), 241 "X"=>array('insert'=>true, 'fun'=>'rec_checkbox') 242 ); 243 $table =& new_db_pager('trans_tbl', $sql, $cols); 244 245 $table->width = "80%"; 246 display_db_pager($table); 247 248br(1); 249submit_center('Reconcile', _("Reconcile"), true, '', null); 250 251end_form(); 252 253//------------------------------------------------------------------------------------------------ 254 255end_page(); 256 257?>