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?>