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 $page_security = 'SA_RECONCILE';
14 include($path_to_root . "/includes/db_pager.inc");
15 include_once($path_to_root . "/includes/session.inc");
17 include_once($path_to_root . "/includes/date_functions.inc");
18 include_once($path_to_root . "/includes/ui.inc");
19 include_once($path_to_root . "/includes/data_checks.inc");
21 include_once($path_to_root . "/gl/includes/gl_db.inc");
22 include_once($path_to_root . "/includes/banking.inc");
25 if ($SysPrefs->use_popup_windows)
26 $js .= get_js_open_window(800, 500);
27 if (user_use_date_picker())
28 $js .= get_js_date_picker();
30 add_js_file('reconcile.js');
32 page(_($help_context = "Reconcile Bank Account"), false, false, "", $js);
34 check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
36 function check_date() {
37 if (!is_date(get_post('reconcile_date'))) {
38 display_error(_("Invalid reconcile date format"));
39 set_focus('reconcile_date');
45 // This function can be used directly in table pager
46 // if we would like to change page layout.
48 function rec_checkbox($row)
50 $name = "rec_" .$row['id'];
51 $hidden = 'last['.$row['id'].']';
52 $value = $row['reconciled'] != '';
54 // save also in hidden field for testing during 'Reconcile'
55 return is_closed_trans($row['type'], $row['trans_no']) ? "--" : checkbox(null, $name, $value, true, _('Reconcile this transaction'))
56 . hidden($hidden, $value, false);
59 function systype_name($dummy, $type)
61 global $systypes_array;
63 return $systypes_array[$type];
66 function trans_view($trans)
68 return get_trans_view_str($trans["type"], $trans["trans_no"]);
71 function gl_view($row)
73 return get_gl_view_str($row["type"], $row["trans_no"]);
76 function fmt_debit($row)
78 $value = $row["amount"];
79 return $value>=0 ? price_format($value) : '';
82 function fmt_credit($row)
84 $value = -$row["amount"];
85 return $value>0 ? price_format($value) : '';
88 function bank_fee($row)
90 $value = -$row["charge"];
91 return $value > 0 ? price_format($value) : '';
94 function fmt_person($trans)
96 return get_counterparty_name($trans["type"], $trans["trans_no"]);
99 function fmt_memo($row)
101 $value = $row["memo_"];
105 function update_data()
109 unset($_POST["beg_balance"]);
110 unset($_POST["end_balance"]);
111 $Ajax->activate('summary');
113 //---------------------------------------------------------------------------------------------
114 // Update db record if respective checkbox value has changed.
116 function change_tpl_flag($reconcile_id)
121 && check_value("rec_".$reconcile_id)) // temporary fix
124 if (get_post('bank_date')=='') // new reconciliation
125 $Ajax->activate('bank_date');
127 $_POST['bank_date'] = date2sql(get_post('reconcile_date'));
128 $reconcile_value = check_value("rec_".$reconcile_id)
129 ? ("'".$_POST['bank_date'] ."'") : 'NULL';
131 update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'],
132 input_num('end_balance'), $_POST['bank_account']);
134 $Ajax->activate('reconciled');
135 $Ajax->activate('difference');
139 function set_tpl_flag($reconcile_id)
143 if (check_value("rec_".$reconcile_id))
146 if (get_post('bank_date')=='') // new reconciliation
147 $Ajax->activate('bank_date');
149 $_POST['bank_date'] = date2sql(get_post('reconcile_date'));
150 $reconcile_value = ("'".$_POST['bank_date'] ."'");
152 update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'],
153 input_num('end_balance'), $_POST['bank_account']);
155 $Ajax->activate('reconciled');
156 $Ajax->activate('difference');
159 if (!isset($_POST['reconcile_date'])) { // init page
160 $_POST['reconcile_date'] = new_doc_date();
161 // $_POST['bank_date'] = date2sql(Today());
164 if (list_updated('bank_account')) {
165 $Ajax->activate('bank_date');
168 if (list_updated('bank_date')) {
169 $_POST['reconcile_date'] =
170 get_post('bank_date')=='' ? Today() : sql2date($_POST['bank_date']);
173 if (get_post('_reconcile_date_changed')) {
174 $_POST['bank_date'] = check_date() ? date2sql(get_post('reconcile_date')) : '';
175 $Ajax->activate('bank_date');
179 $id = find_submit('_rec_');
181 change_tpl_flag($id);
184 if (isset($_POST['Reconcile'])) {
185 set_focus('bank_date');
186 foreach($_POST['last'] as $id => $value)
187 if ($value != check_value('rec_'.$id))
188 if(!change_tpl_flag($id)) break;
190 $Ajax->activate('_page_body');
193 if (isset($_POST['ReconcileAll'])) {
194 set_focus('bank_date');
195 foreach($_POST['last'] as $id => $value)
198 $Ajax->activate('_page_body');
201 //------------------------------------------------------------------------------------------------
203 start_table(TABLESTYLE_NOBORDER);
205 bank_accounts_list_cells(_("Account:"), 'bank_account', null, true);
207 bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'),
208 'bank_date', null, true, _("New"));
212 $result = get_max_reconciled(get_post('reconcile_date'), $_POST['bank_account']);
214 if ($row = db_fetch($result)) {
215 $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]);
216 $total = $row["total"];
217 if (!isset($_POST["beg_balance"])) { // new selected account/statement
218 $_POST["last_date"] = sql2date($row["last_date"]);
219 $_POST["beg_balance"] = price_format($row["beg_balance"]);
220 $_POST["end_balance"] = price_format($row["end_balance"]);
221 if (get_post('bank_date')) {
222 // if it is the last updated bank statement retrieve ending balance
224 $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']);
226 $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]);
234 div_start('summary');
236 start_table(TABLESTYLE);
237 $th = array(_("Reconcile Date"), _("Beginning<br>Balance"),
238 _("Ending<br>Balance"), _("Account<br>Total"),_("Reconciled<br>Amount"), _("Difference"));
242 date_cells("", "reconcile_date", _('Date of bank statement to reconcile'),
243 get_post('bank_date')=='', 0, 0, 0, null, true);
245 amount_cells_ex("", "beg_balance", 15);
247 amount_cells_ex("", "end_balance", 15);
249 $reconciled = input_num('reconciled');
250 $difference = input_num("end_balance") - input_num("beg_balance") - $reconciled;
253 amount_cell($reconciled, false, '', "reconciled");
254 amount_cell($difference, false, '', "difference");
260 //------------------------------------------------------------------------------------------------
262 if (!isset($_POST['bank_account']))
263 $_POST['bank_account'] = "";
265 $sql = get_sql_for_bank_account_reconcile(get_post('bank_account'), get_post('reconcile_date'));
267 $act = get_bank_account($_POST["bank_account"]);
268 display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
272 _("Type") => array('fun'=>'systype_name', 'ord'=>''),
273 _("#") => array('fun'=>'trans_view', 'ord'=>''),
276 _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'),
277 _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'),
278 _("Bank fees") => array('align'=>'right','insert'=>true, 'fun'=>'bank_fee'),
279 _("Person/Item") => array('fun'=>'fmt_person'),
280 _("Memo") => array('fun'=>'fmt_memo'),
281 array('insert'=>true, 'fun'=>'gl_view'),
282 "X"=>array('insert'=>true, 'fun'=>'rec_checkbox')
284 $table =& new_db_pager('trans_tbl', $sql, $cols);
286 $table->width = "80%";
287 display_db_pager($table);
291 submit('Reconcile', _("Reconcile"), true, '', null);
292 submit('ReconcileAll', _("Reconcile All"), true, '');
296 //------------------------------------------------------------------------------------------------