1<?php 2// BankMatching.php 3// Allows payments and receipts to be matched off against bank statements. 4 5include('includes/session.php'); 6$Title = _('Bank Matching'); 7$ViewTopic = 'GeneralLedger'; 8$BookMark = 'BankMatching'; 9 10include('includes/header.php'); 11 12if ((isset($_GET['Type']) AND $_GET['Type']=='Receipts') 13 OR (isset($_POST['Type']) AND $_POST['Type']=='Receipts')) { 14 15 $Type = 'Receipts'; 16 $TypeName =_('Receipts'); 17 echo '<p class="page_title_text"><img alt="" src="'.$RootPath.'/css/'.$Theme. 18 '/images/bank.png" title="' . 19 _('Bank Matching') . '" /> ' .// Icon title. 20 _('Bank Account Matching - Receipts') . '</p>';// Page title. 21 22} elseif ((isset($_GET['Type']) AND $_GET['Type']=='Payments') 23 OR (isset($_POST['Type']) AND $_POST['Type']=='Payments')) { 24 25 $Type = 'Payments'; 26 $TypeName =_('Payments'); 27 echo '<p class="page_title_text"><img alt="" src="'.$RootPath.'/css/'.$Theme. 28 '/images/bank.png" title="' . 29 _('Bank Matching') . '" /> ' .// Icon title. 30 _('Bank Account Matching - Payments') . '</p>';// Page title. 31 32} else { 33 34 prnMsg(_('This page must be called with a bank transaction type') . '. ' . _('It should not be called directly'),'error'); 35 include ('includes/footer.php'); 36 exit; 37} 38 39if (isset($_GET['Account'])) { 40 $_POST['BankAccount']=$_GET['Account']; 41 $_POST['ShowTransactions']=true; 42 $_POST['Ostg_or_All']='Ostg'; 43 $_POST['First20_or_All']='All'; 44} 45 46if (isset($_POST['Update']) AND $_POST['RowCounter']>1) { 47 for ($Counter=1;$Counter <= $_POST['RowCounter']; $Counter++) { 48 if (isset($_POST['Clear_' . $Counter]) AND $_POST['Clear_' . $Counter]==True) { 49 /*Get amount to be cleared */ 50 $SQL = "SELECT amount, 51 exrate 52 FROM banktrans 53 WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'"; 54 $ErrMsg = _('Could not retrieve transaction information'); 55 $result = DB_query($SQL,$ErrMsg); 56 $MyRow=DB_fetch_array($result); 57 $AmountCleared = round($MyRow[0] / $MyRow[1],2); 58 /*Update the banktrans recoord to match it off */ 59 $SQL = "UPDATE banktrans SET amountcleared= ". $AmountCleared . " 60 WHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'"; 61 $ErrMsg = _('Could not match off this payment because'); 62 $result = DB_query($SQL,$ErrMsg); 63 64 } elseif ((isset($_POST['AmtClear_' . $Counter]) 65 AND filter_number_format($_POST['AmtClear_' . $Counter])<0 66 AND $Type=='Payments') 67 OR ($Type=='Receipts' AND isset($_POST['AmtClear_' . $Counter]) 68 AND filter_number_format($_POST['AmtClear_' . $Counter])>0)) { 69 70 /*if the amount entered was numeric and negative for a payment or positive for a receipt */ 71 72 $SQL = "UPDATE banktrans SET amountcleared=" . filter_number_format($_POST['AmtClear_' . $Counter]) . " 73 WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'"; 74 75 $ErrMsg = _('Could not update the amount matched off this bank transaction because'); 76 $result = DB_query($SQL,$ErrMsg); 77 78 } elseif (isset($_POST['Unclear_' . $Counter]) 79 AND $_POST['Unclear_' . $Counter]==True) { 80 81 $SQL = "UPDATE banktrans SET amountcleared = 0 82 WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'"; 83 $ErrMsg = _('Could not unclear this bank transaction because'); 84 $result = DB_query($SQL,$ErrMsg); 85 } 86 } 87 /*Show the updated position with the same criteria as previously entered*/ 88 $_POST['ShowTransactions'] = True; 89} 90 91echo '<div class="page_help_text">' . _('Use this screen to match webERP Receipts and Payments to your Bank Statement. Check your bank statement and click the check-box when you find the matching transaction.') . '</div><br />'; 92 93echo '<form action="'. htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">'; 94echo '<div>'; 95echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 96 97echo '<input type="hidden" name="Type" value="' . $Type . '" />'; 98 99echo '<table class="selection"> 100 <tr> 101 <td align="left">' . _('Bank Account') . ':</td> 102 <td colspan="3"><select tabindex="1" autofocus="autofocus" name="BankAccount">'; 103 104$SQL = "SELECT 105 bankaccounts.accountcode, 106 bankaccounts.bankaccountname, 107 bankaccounts.currcode 108 FROM bankaccounts, bankaccountusers 109 WHERE bankaccounts.accountcode=bankaccountusers.accountcode 110 AND bankaccountusers.userid = '" . $_SESSION['UserID'] ."' 111 ORDER BY bankaccounts.bankaccountname"; 112$resultBankActs = DB_query($SQL); 113while ($MyRow=DB_fetch_array($resultBankActs)) { 114 // Lists bank accounts order by bankaccountname 115 echo '<option', 116 ((isset($_POST['BankAccount']) and $_POST['BankAccount'] == $MyRow['accountcode']) ? ' selected="selected"' : '' ), 117 ' value="', $MyRow['accountcode'], '">', $MyRow['bankaccountname'], ' - ', $MyRow['currcode'], '</option>'; 118} 119echo '</select></td> 120 </tr>'; 121 122if (!isset($_POST['BeforeDate']) OR !Is_Date($_POST['BeforeDate'])) { 123 $_POST['BeforeDate'] = Date($_SESSION['DefaultDateFormat']); 124} 125if (!isset($_POST['AfterDate']) OR !Is_Date($_POST['AfterDate'])) { 126 $_POST['AfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m')-3,Date('d'),Date('y'))); 127} 128 129// Change to allow input of FROM DATE and then TO DATE, instead of previous back-to-front method, add datepicker 130echo '<tr> 131 <td>' . _('Show') . ' ' . $TypeName . ' ' . _('from') . ':</td> 132 <td><input tabindex="3" type="text" name="AfterDate" class="date" size="12" maxlength="10" required="required" onchange="isDate(this, this.value, '."'".$_SESSION['DefaultDateFormat']."'".')" value="' . $_POST['AfterDate'] . '" /></td> 133 </tr>'; 134 135echo '<tr> 136 <td>' . _('to') . ':</td> 137 <td><input tabindex="2" type="text" name="BeforeDate" class="date" size="12" maxlength="10" required="required" onchange="isDate(this, this.value, '."'".$_SESSION['DefaultDateFormat']."'".')" value="' . $_POST['BeforeDate'] . '" /></td> 138 </tr>'; 139echo '<tr> 140 <td colspan="3">' . _('Choose outstanding') . ' ' . $TypeName . ' ' . _('only or all') . ' ' . $TypeName . ' ' . _('in the date range') . ':</td> 141 <td><select tabindex="4" name="Ostg_or_All">'; 142 143if ($_POST['Ostg_or_All']=='All') { 144 echo '<option selected="selected" value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>'; 145 echo '<option value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only') . '</option>'; 146} else { 147 echo '<option value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>'; 148 echo '<option selected="selected" value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only') . '</option>'; 149} 150echo '</select></td> 151 </tr>'; 152 153echo '<tr> 154 <td colspan="3">' . _('Choose to display only the first 20 matching') . ' ' . $TypeName . ' ' . _('or all') . ' ' . $TypeName . ' ' . _('meeting the criteria') . ':</td> 155 <td><select tabindex="5" name="First20_or_All">'; 156if ($_POST['First20_or_All']=='All') { 157 echo '<option selected="selected" value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>'; 158 echo '<option value="First20">' . _('Show only the first 20') . ' ' . $TypeName . '</option>'; 159} else { 160 echo '<option value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>'; 161 echo '<option selected="selected" value="First20">' . _('Show only the first 20') . ' ' . $TypeName . '</option>'; 162} 163 164echo '</select></td> 165 </tr>'; 166 167echo '</table> 168 <br /> 169 <div class="centre"> 170 <input tabindex="6" type="submit" name="ShowTransactions" value="' . _('Show selected') . ' ' . $TypeName . '" />'; 171 172if (isset($_POST['BankAccount'])) { 173 echo '<p><a href="' . $RootPath . '/BankReconciliation.php?Account=' . $_POST['BankAccount'] . '">' . _('Show reconciliation') . '</a></p>'; 174} 175 176echo '</div>'; 177 178$InputError=0; 179if (!Is_Date($_POST['BeforeDate'])) { 180 $InputError =1; 181 prnMsg(_('The date entered for the field to show') . ' ' . $TypeName . ' ' . _('before') . ', ' . 182 _('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' . 183 $_SESSION['DefaultDateFormat'],'error'); 184} 185if (!Is_Date($_POST['AfterDate'])) { 186 $InputError =1; 187 prnMsg( _('The date entered for the field to show') . ' ' . $Type . ' ' . _('after') . ', ' . 188 _('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' . 189 $_SESSION['DefaultDateFormat'],'error'); 190} 191 192if ($InputError !=1 193 AND isset($_POST['BankAccount']) 194 AND $_POST['BankAccount']!='' 195 AND isset($_POST['ShowTransactions'])) { 196 197 $SQLBeforeDate = FormatDateForSQL($_POST['BeforeDate']); 198 $SQLAfterDate = FormatDateForSQL($_POST['AfterDate']); 199 200 $BankResult = DB_query("SELECT decimalplaces, 201 currcode 202 FROM bankaccounts INNER JOIN currencies 203 ON bankaccounts.currcode=currencies.currabrev 204 WHERE accountcode='" . $_POST['BankAccount'] . "'"); 205 $BankRow = DB_fetch_array($BankResult); 206 $CurrDecimalPlaces = $BankRow['decimalplaces']; 207 $CurrCode = $BankRow['currcode']; 208 209 if ($_POST['Ostg_or_All']=='All') { 210 if ($Type=='Payments') { 211 $SQL = "SELECT banktransid, 212 ref, 213 amountcleared, 214 transdate, 215 amount/exrate as amt, 216 banktranstype 217 FROM banktrans 218 WHERE amount < 0 219 AND transdate >= '". $SQLAfterDate . "' 220 AND transdate <= '" . $SQLBeforeDate . "' 221 AND bankact='" . $_POST['BankAccount'] . "' 222 ORDER BY transdate"; 223 224 } else { /* Type must == Receipts */ 225 $SQL = "SELECT banktransid, 226 ref, 227 amountcleared, 228 transdate, 229 amount/exrate as amt, 230 banktranstype 231 FROM banktrans 232 WHERE amount > 0 233 AND transdate >= '". $SQLAfterDate . "' 234 AND transdate <= '" . $SQLBeforeDate . "' 235 AND bankact='" . $_POST['BankAccount'] . "' 236 ORDER BY transdate"; 237 } 238 } else { /*it must be only the outstanding bank trans required */ 239 if ($Type=='Payments') { 240 $SQL = "SELECT banktransid, 241 ref, 242 amountcleared, 243 transdate, 244 amount/exrate as amt, 245 banktranstype 246 FROM banktrans 247 WHERE amount < 0 248 AND transdate >= '". $SQLAfterDate . "' 249 AND transdate <= '" . $SQLBeforeDate . "' 250 AND bankact='" . $_POST['BankAccount'] . "' 251 AND ABS(amountcleared - (amount / exrate)) > 0.009 252 ORDER BY transdate"; 253 } else { /* Type must == Receipts */ 254 $SQL = "SELECT banktransid, 255 ref, 256 amountcleared, 257 transdate, 258 amount/exrate as amt, 259 banktranstype 260 FROM banktrans 261 WHERE amount > 0 262 AND transdate >= '". $SQLAfterDate . "' 263 AND transdate <= '" . $SQLBeforeDate . "' 264 AND bankact='" . $_POST['BankAccount'] . "' 265 AND ABS(amountcleared - (amount / exrate)) > 0.009 266 ORDER BY transdate"; 267 } 268 } 269 if ($_POST['First20_or_All']!='All') { 270 $SQL = $SQL . " LIMIT 20"; 271 } 272 273 $ErrMsg = _('The payments with the selected criteria could not be retrieved because'); 274 $PaymentsResult = DB_query($SQL, $ErrMsg); 275 276 echo '<table cellpadding="2" class="selection"> 277 <thead> 278 <tr> 279 <th class="ascending">' . _('Cheque No') . '</th> 280 <th class="ascending">' . _('Ref') . '</th> 281 <th class="ascending">' . _('Date') . '</th> 282 <th class="ascending">' . $TypeName . '</th> 283 <th class="ascending">' . _('Amount') . '</th> 284 <th class="ascending">' . _('Outstanding') . '</th> 285 <th colspan="3">' . _('Clear') . ' / ' . _('Unclear') . '</th> 286 </tr> 287 </thead> 288 <tbody>'; 289 290 $i = 1; //no of rows counter 291 292 while ($MyRow=DB_fetch_array($PaymentsResult)) { 293 294 $DisplayTranDate = ConvertSQLDate($MyRow['transdate']); 295 $Outstanding = $MyRow['amt']- $MyRow['amountcleared']; 296 if (ABS($Outstanding)<0.009) { /*the payment is cleared dont show the check box*/ 297 298 printf('<tr class="striped_row"> 299 <td>%s</td> 300 <td>%s</td> 301 <td>%s</td> 302 <td class="number">%s</td> 303 <td class="number">%s</td> 304 <td colspan="2">%s</td> 305 <td><input type="checkbox" name="Unclear_%s" /><input type="hidden" name="BankTrans_%s" value="%s" /></td> 306 </tr>', 307 $MyRow['ref'], 308 $MyRow['banktranstype'], 309 $DisplayTranDate, 310 locale_number_format($MyRow['amt'],$CurrDecimalPlaces), 311 locale_number_format($Outstanding,$CurrDecimalPlaces), 312 _('Unclear'), 313 $i, 314 $i, 315 $MyRow['banktransid']); 316 317 } else{ 318 printf('<tr class="striped_row"> 319 <td>%s</td> 320 <td>%s</td> 321 <td>%s</td> 322 <td class="number">%s</td> 323 <td class="number">%s</td> 324 <td><input type="checkbox" name="Clear_%s" /><input type="hidden" name="BankTrans_%s" value="%s" /></td> 325 <td colspan="2"><input type="text" maxlength="15" size="15" class="number" name="AmtClear_%s" /></td> 326 </tr>', 327 $MyRow['ref'], 328 $MyRow['banktranstype'], 329 $DisplayTranDate, 330 locale_number_format($MyRow['amt'],$CurrDecimalPlaces), 331 locale_number_format($Outstanding,$CurrDecimalPlaces), 332 $i, 333 $i, 334 $MyRow['banktransid'], 335 $i 336 ); 337 } 338 $i++; 339 } 340 //end of while loop 341 echo '</tbody> 342 </table> 343 <br /> 344 <div class="centre"> 345 <input type="hidden" name="RowCounter" value="' . $i . '" /> 346 <input type="submit" name="Update" value="' . _('Update Matching') . '" /> 347 </div>'; 348} 349echo '</div>'; 350echo '</form>'; 351include('includes/footer.php'); 352?> 353
