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';
15 include($path_to_root . "/includes/db_pager.inc");
16 include_once($path_to_root . "/includes/session.inc");
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");
22 include_once($path_to_root . "/gl/includes/gl_db.inc");
23 include_once($path_to_root . "/includes/banking.inc");
26 if ($use_popup_windows)
27 $js .= get_js_open_window(800, 500);
29 $js .= get_js_date_picker();
31 add_js_file('reconcile.js');
33 page(_($help_context = "Reconcile Bank Account"), false, false, "", $js);
35 check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
37 function check_date() {
38 if (!is_date(get_post('reconcile_date'))) {
39 display_error(_("Invalid reconcile date format"));
40 set_focus('reconcile_date');
46 // This function can be used directly in table pager
47 // if we would like to change page layout.
49 function rec_checkbox($row)
51 $name = "rec_" .$row['id'];
52 $hidden = 'last['.$row['id'].']';
53 $value = $row['reconciled'] != '';
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);
60 function systype_name($dummy, $type)
62 global $systypes_array;
64 return $systypes_array[$type];
67 function trans_view($trans)
69 return get_trans_view_str($trans["type"], $trans["trans_no"]);
72 function gl_view($row)
74 return get_gl_view_str($row["type"], $row["trans_no"]);
77 function fmt_debit($row)
79 $value = $row["amount"];
80 return $value>=0 ? price_format($value) : '';
83 function fmt_credit($row)
85 $value = -$row["amount"];
86 return $value>0 ? price_format($value) : '';
89 function fmt_person($row)
91 return payment_person_name($row["person_type_id"],$row["person_id"]);
94 $update_pager = false;
95 function update_data()
97 global $Ajax, $update_pager;
99 unset($_POST["beg_balance"]);
100 unset($_POST["end_balance"]);
101 $Ajax->activate('summary');
102 $update_pager = true;
104 //---------------------------------------------------------------------------------------------
105 // Update db record if respective checkbox value has changed.
107 function change_tpl_flag($reconcile_id)
112 && check_value("rec_".$reconcile_id)) // temporary fix
115 if (get_post('bank_date')=='') // new reconciliation
116 $Ajax->activate('bank_date');
118 $_POST['bank_date'] = date2sql(get_post('reconcile_date'));
119 $reconcile_value = check_value("rec_".$reconcile_id)
120 ? ("'".$_POST['bank_date'] ."'") : 'NULL';
121 $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
122 ." WHERE id=".db_escape($reconcile_id);
124 db_query($sql, "Can't change reconciliation status");
125 // save last reconcilation status (date, end balance)
126 $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
127 .date2sql($_POST["reconcile_date"])."',
128 ending_reconcile_balance=".input_num("end_balance")
129 ." WHERE id=".db_escape($_POST["bank_account"]);
131 $result = db_query($sql2,"Error updating reconciliation information");
132 $Ajax->activate('reconciled');
133 $Ajax->activate('difference');
137 if (!isset($_POST['reconcile_date'])) { // init page
138 $_POST['reconcile_date'] = new_doc_date();
139 // $_POST['bank_date'] = date2sql(Today());
142 if (list_updated('bank_account')) {
143 $Ajax->activate('bank_date');
146 if (list_updated('bank_date')) {
147 $_POST['reconcile_date'] =
148 get_post('bank_date')=='' ? Today() : sql2date($_POST['bank_date']);
151 if (get_post('_reconcile_date_changed')) {
152 $_POST['bank_date'] = check_date() ? date2sql(get_post('reconcile_date')) : '';
153 $Ajax->activate('bank_date');
157 $id = find_submit('_rec_');
159 change_tpl_flag($id);
161 if (isset($_POST['Reconcile'])) {
162 set_focus('bank_date');
163 foreach($_POST['last'] as $id => $value)
164 if ($value != check_value('rec_'.$id))
165 if(!change_tpl_flag($id)) break;
166 $Ajax->activate('_page_body');
169 //------------------------------------------------------------------------------------------------
171 start_table("class='tablestyle_noborder'");
173 bank_accounts_list_cells(_("Account:"), 'bank_account', null, true);
175 bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'),
176 'bank_date', null, true, _("New"));
180 $date = date2sql(get_post('reconcile_date'));
181 // temporary fix to enable fix of invalid entries made in 2.2RC
182 if ($date == 0) $date = '0000-00-00';
184 $sql = "SELECT MAX(reconciled) as last_date,
185 SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
186 SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
188 FROM ".TB_PREF."bank_trans trans
189 WHERE bank_act=".db_escape($_POST['bank_account']);
190 // ." AND trans.reconciled IS NOT NULL";
192 $result = db_query($sql,"Cannot retrieve reconciliation data");
194 if ($row = db_fetch($result)) {
195 $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]);
196 $total = $row["total"];
197 if (!isset($_POST["beg_balance"])) { // new selected account/statement
198 $_POST["last_date"] = sql2date($row["last_date"]);
199 $_POST["beg_balance"] = price_format($row["beg_balance"]);
200 $_POST["end_balance"] = price_format($row["end_balance"]);
201 if (get_post('bank_date')) {
202 // if it is the last updated bank statement retrieve ending balance
203 $sql = "SELECT ending_reconcile_balance
204 FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($_POST['bank_account'])
205 . " AND last_reconciled_date=".db_escape($_POST['bank_date']);
206 $result = db_query($sql,"Cannot retrieve last reconciliation");
207 $row = db_fetch($result);
209 $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]);
217 div_start('summary');
219 start_table($table_style);
220 $th = array(_("Reconcile Date"), _("Beginning<br>Balance"),
221 _("Ending<br>Balance"), _("Account<br>Total"),_("Reconciled<br>Amount"), _("Difference"));
225 date_cells("", "reconcile_date", _('Date of bank statement to reconcile'),
226 get_post('bank_date')=='', 0, 0, 0, null, true);
228 amount_cells_ex("", "beg_balance", 15);
230 amount_cells_ex("", "end_balance", 15);
232 $reconciled = input_num('reconciled');
233 $difference = input_num("end_balance") - input_num("beg_balance") - $reconciled;
236 amount_cell($reconciled, false, '', "reconciled");
237 amount_cell($difference, false, '', "difference");
243 //------------------------------------------------------------------------------------------------
245 if (!isset($_POST['bank_account']))
246 $_POST['bank_account'] = "";
248 $sql = "SELECT type, trans_no, ref, trans_date,
249 amount, person_id, person_type_id, reconciled, id
250 FROM ".TB_PREF."bank_trans
251 WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . "
252 AND (reconciled IS NULL OR reconciled='". $date ."')
253 ORDER BY trans_date,".TB_PREF."bank_trans.id";
254 // or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
256 $act = get_bank_account($_POST["bank_account"]);
257 display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
261 _("Type") => array('fun'=>'systype_name', 'ord'=>''),
262 _("#") => array('fun'=>'trans_view', 'ord'=>''),
265 _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'),
266 _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'),
267 _("Person/Item") => array('fun'=>'fmt_person'),
268 array('insert'=>true, 'fun'=>'gl_view'),
269 "X"=>array('insert'=>true, 'fun'=>'rec_checkbox')
271 $table =& new_db_pager('trans_tbl', $sql, $cols);
273 $table->width = "80%";
274 display_db_pager($table);
277 submit_center('Reconcile', _("Reconcile"), true, '', null);
281 //------------------------------------------------------------------------------------------------