Care2007/modules/weberp/BankMatching.php

   1 <?php
   2 /* $Revision: 1.13 $ */
   3
   4 $PageSecurity = 7;
   5
   6 include("includes/session.inc");
   7
   8 if (($_GET["Type"]=='Receipts') OR ($_POST["Type"]=='Receipts')){
   9         $Type = 'Receipts';
  10         $TypeName =_('Receipts');
  11         $title = _('Bank Account Deposits Matching');
  12 } elseif (($_GET["Type"]=='Payments') OR ($_POST["Type"]=='Payments')) {
  13         $Type = 'Payments';
  14         $TypeName =_('Payments');
  15         $title = _('Bank Account Payments Matching');
  16 } else {
  17         prnMsg(_('This page must be called with a bank transaction type') . '. ' . _('It should not be called directly'),'error');
  18         include ('includes/footer.inc');
  19         exit;
  20 }
  21
  22 include('includes/header.inc');
  23
  24 if (isset($_POST['Update']) AND $_POST['RowCounter']>1){
  25         for ($Counter=1;$Counter <= $_POST['RowCounter']; $Counter++){
  26                 if ($_POST["Clear_" . $Counter]==True){
  27                         /*Update the banktrans recoord to match it off */
  28                         $sql = "UPDATE banktrans SET amountcleared=(amount/exrate)
  29                                         WHERE banktransid=" . $_POST["BankTrans_" . $Counter];
  30                         $ErrMsg =  _('Could not match off this payment beacause');
  31                         $result = DB_query($sql,$db,$ErrMsg);
  32
  33                 } elseif (is_numeric((float) $_POST["AmtClear_" . $Counter]) AND (($_POST["AmtClear_" . $Counter]<0 AND $Type=='Payments') OR ($Type=='Receipts' AND ($_POST["AmtClear_" . $Counter]>0)))){
  34                         /*if the amount entered was numeric and negative for a payment or positive for a receipt */
  35                         $sql = "UPDATE banktrans SET amountcleared=" .  $_POST["AmtClear_" . $Counter] . "
  36                                         WHERE banktransid=" . $_POST["BankTrans_" . $Counter];
  37
  38                         $ErrMsg = _('Could not update the amount matched off this bank transaction because');
  39                         $result = DB_query($sql,$db,$ErrMsg);
  40
  41                 } elseif ($_POST["Unclear_" . $Counter]==True){
  42                         $sql = "UPDATE banktrans SET amountcleared = 0
  43                                         WHERE banktransid=" . $_POST["BankTrans_" . $Counter];
  44                         $ErrMsg =  _('Could not unclear this bank transaction because');
  45                         $result = DB_query($sql,$db,$ErrMsg);
  46                 }
  47         }
  48         /*Show the updated position with the same criteria as previously entered*/
  49         $_POST["ShowTransactions"] = True;
  50 }
  51
  52
  53 echo "<FORM ACTION='". $_SERVER['PHP_SELF'] . "?" . SID . "' METHOD=POST>";
  54
  55 echo "<INPUT TYPE=HIDDEN Name=Type Value=$Type>";
  56
  57 echo '<TABLE><TR>';
  58 echo '<TD ALIGN=RIGHT>' . _('Bank Account') . ':</TD><TD COLSPAN=3><SELECT name="BankAccount">';
  59
  60 $sql = "SELECT accountcode, bankaccountname FROM bankaccounts";
  61 $resultBankActs = DB_query($sql,$db);
  62 while ($myrow=DB_fetch_array($resultBankActs)){
  63         if ($myrow["accountcode"] == $_POST['BankAccount']){
  64              echo "<OPTION SELECTED Value='" . $myrow['accountcode'] . "'>" . $myrow['bankaccountname'];
  65         } else {
  66              echo "<OPTION Value='" . $myrow['accountcode'] . "'>" . $myrow['bankaccountname'];
  67         }
  68 }
  69
  70 echo '</SELECT></TD></TR>';
  71
  72 if (!isset($_POST['BeforeDate']) OR !Is_Date($_POST['BeforeDate'])){
  73    $_POST['BeforeDate'] = Date($_SESSION['DefaultDateFormat']);
  74 }
  75 if (!isset($_POST['AfterDate']) OR !Is_Date($_POST['AfterDate'])){
  76    $_POST['AfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date("m")-3,Date("d"),Date("y")));
  77 }
  78
  79 echo '<TR><TD>' . _('Show') . ' ' . $TypeName . ' ' . _('before') . ':</TD>
  80         <TD><INPUT TYPE=TEXT NAME="BeforeDate" SIZE=12 MAXLENGTH=12 Value="' . $_POST['BeforeDate'] . '"></TD>';
  81 echo '<TD>' . _('but after') . ':</TD>
  82         <TD><INPUT TYPE=TEXT NAME="AfterDate" SIZE=12 MAXLENGTH=12 Value="' . $_POST['AfterDate'] . '"></TD></TR>';
  83 echo '<TR><TD COLSPAN=3>' . _('Choose outstanding') . ' ' . $TypeName . ' ' . _('only or all') . ' ' . $TypeName . ' ' . _('in the date range') . ':</TD>
  84         <TD><SELECT NAME="Ostg_or_All">';
  85
  86 if ($_POST["Ostg_or_All"]=='All'){
  87         echo '<OPTION SELECTED Value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range');
  88         echo '<OPTION Value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only');
  89 } else {
  90         echo '<OPTION Value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range');
  91         echo '<OPTION SELECTED Value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only');
  92 }
  93 echo '</SELECT></TD></TR>';
  94
  95 echo '<TR><TD COLSPAN=3>' . _('Choose to display only the first 20 matching') . ' ' . $TypeName . ' ' . _('or all') . ' ' . $TypeName . ' ' . _('meeting the criteria') . ':</TD><TD><SELECT NAME="First20_or_All">';
  96 if ($_POST["First20_or_All"]=='All'){
  97         echo '<OPTION SELECTED Value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range');
  98         echo '<OPTION Value="First20">' . _('Show only the first 20') . ' ' . $TypeName;
  99 } else {
 100         echo '<OPTION Value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range');
 101         echo '<OPTION SELECTED Value="First20">' . _('Show only the first 20') . ' ' . $TypeName;
 102 }
 103 echo '</SELECT></TD></TR>';
 104
 105
 106 echo '</TABLE><CENTER><INPUT TYPE=SUBMIT NAME="ShowTransactions" VALUE="' . _('Show selected') . ' ' . $TypeName . '">';
 107 echo "<P><A HREF='$rootpath/BankReconciliation.php?" . SID . "'>" . _('Show reconciliation') . '</A>';
 108 echo '<HR>';
 109
 110 $InputError=0;
 111 if (!Is_Date($_POST['BeforeDate'])){
 112         $InputError =1;
 113         prnMsg(_('The date entered for the field to show') . ' ' . $TypeName . ' ' . _('before') . ', ' . _('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
 114 }
 115 if (!Is_Date($_POST['AfterDate'])){
 116         $InputError =1;
 117         prnMsg( _('The date entered for the field to show') . ' ' . $Type . ' ' . _('after') . ', ' . _('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
 118 }
 119
 120 if ($InputError !=1 AND isset($_POST["BankAccount"]) AND $_POST["BankAccount"]!="" AND isset($_POST["ShowTransactions"])){
 121
 122         $SQLBeforeDate = FormatDateForSQL($_POST['BeforeDate']);
 123         $SQLAfterDate = FormatDateForSQL($_POST['AfterDate']);
 124
 125         if ($_POST["Ostg_or_All"]=='All'){
 126                 if ($Type=='Payments'){
 127                         $sql = "SELECT banktransid,
 128                                         ref,
 129                                         amountcleared,
 130                                         transdate,
 131                                         amount/exrate as amt,
 132                                         banktranstype
 133                                 FROM banktrans
 134                                 WHERE amount <0
 135                                 AND transdate >= '". $SQLAfterDate . "'
 136                                 AND transdate <= '" . $SQLBeforeDate . "'
 137                                 AND bankact=" .$_POST["BankAccount"] . "
 138                                 ORDER BY banktransid";
 139
 140                 } else { /* Type must == Receipts */
 141                         $sql = "SELECT banktransid,
 142                                         ref,
 143                                         amountcleared,
 144                                         transdate,
 145                                         amount/exrate as amt,
 146                                         banktranstype
 147                                 FROM banktrans
 148                                 WHERE amount >0
 149                                 AND transdate >= '". $SQLAfterDate . "'
 150                                 AND transdate <= '" . $SQLBeforeDate . "'
 151                                 AND bankact=" .$_POST['BankAccount'] . "
 152                                 ORDER BY banktransid";
 153                 }
 154         } else { /*it must be only the outstanding bank trans required */
 155                 if ($Type=='Payments'){
 156                         $sql = "SELECT banktransid,
 157                                         ref,
 158                                         amountcleared,
 159                                         transdate,
 160                                         amount/exrate as amt,
 161                                         banktranstype
 162                                 FROM banktrans
 163                                 WHERE amount <0
 164                                 AND transdate >= '". $SQLAfterDate . "'
 165                                 AND transdate <= '" . $SQLBeforeDate . "'
 166                                 AND bankact=" .$_POST["BankAccount"] . "
 167                                 AND  ABS(amountcleared - (amount / exrate)) > 0.009
 168                                 ORDER BY banktransid";
 169                 } else { /* Type must == Receipts */
 170                         $sql = "SELECT banktransid,
 171                                         ref,
 172                                         amountcleared,
 173                                         transdate,
 174                                         amount/exrate as amt,
 175                                         banktranstype
 176                                 FROM banktrans
 177                                 WHERE amount >0
 178                                 AND transdate >= '". $SQLAfterDate . "'
 179                                 AND transdate <= '" . $SQLBeforeDate . "'
 180                                 AND bankact=" .$_POST["BankAccount"] . "
 181                                 AND  ABS(amountcleared - (amount / exrate)) > 0.009
 182                                 ORDER BY banktransid";
 183                 }
 184         }
 185         if ($_POST["First20_or_All"]!='All'){
 186                 $sql = $sql . " LIMIT 20";
 187         }
 188
 189         $ErrMsg = _('The payments with the selected criteria could not be retrieved because');
 190         $PaymentsResult = DB_query($sql, $db, $ErrMsg);
 191
 192         $TableHeader = '<TR><TD class="tableheader">'. _('Ref'). '</TD>
 193                          <TD class="tableheader">' . $TypeName . '</TD>
 194                          <TD class="tableheader">' . _('Date') . '</TD>
 195                          <TD class="tableheader">' . _('Amount') . '</TD>
 196                          <TD class="tableheader">' . _('Outstanding') . '</TD>
 197                          <TD COLSPAN=3 ALIGN=CENTER class="tableheader">' . _('Clear') . ' / ' . _('Unclear') . '</TD>
 198                 </TR>';
 199         echo '<TABLE CELLPADDING=2 BORDER=2>' . $TableHeader;
 200
 201
 202         $j = 1;  //page length counter
 203         $k=0; //row colour counter
 204         $i = 1; //no of rows counter
 205
 206         while ($myrow=DB_fetch_array($PaymentsResult)) {
 207
 208                 $DisplayTranDate = ConvertSQLDate($myrow['transdate']);
 209                 $Outstanding = $myrow['amt']- $myrow['amountcleared'];
 210                 if (ABS($Outstanding)<0.009){ /*the payment is cleared dont show the check box*/
 211
 212                         printf("<tr bgcolor='#CCCEEE'>
 213                                 <td>%s</td>
 214                                 <td>%s</td>
 215                                 <td>%s</td>
 216                                 <td ALIGN=RIGHT>%s</td>
 217                                 <td ALIGN=RIGHT>%s</td>
 218                                 <td COLSPAN=2 ALIGN=CENTER>%s</td>
 219                                 <td ALIGN=CENTER><INPUT TYPE='checkbox' NAME='Unclear_%s'><INPUT TYPE=HIDDEN NAME='BankTrans_%s' VALUE=%s></TD>
 220                                 </tr>",
 221                                 $myrow['ref'],
 222                                 $myrow['banktranstype'],
 223                                 $DisplayTranDate,
 224                                 number_format($myrow['amt'],2),
 225                                 number_format($Outstanding,2),
 226                                 _('Unclear'),
 227                                 $i,
 228                                 $i,
 229                                 $myrow['banktransid']);
 230
 231                 } else{
 232                         if ($k==1){
 233                                 echo "<tr bgcolor='#CCCCCC'>";
 234                                 $k=0;
 235                         } else {
 236                                 echo "<tr bgcolor='#EEEEEE'>";
 237                                 $k=1;
 238                         }
 239
 240                         printf("<td>%s</td>
 241                                 <td>%s</td>
 242                                 <td>%s</td>
 243                                 <td ALIGN=RIGHT>%s</td>
 244                                 <td ALIGN=RIGHT>%s</td>
 245                                 <td ALIGN=CENTER><INPUT TYPE='checkbox' NAME='Clear_%s'><INPUT TYPE=HIDDEN NAME='BankTrans_%s' VALUE=%s></td>
 246                                 <td COLSPAN=2><INPUT TYPE='text' MAXLENGTH=15 SIZE=15 NAME='AmtClear_%s'></td>
 247                                 </tr>",
 248                                 $myrow['ref'],
 249                                 $myrow['banktranstype'],
 250                                 $DisplayTranDate,
 251                                 number_format($myrow['amt'],2),
 252                                 number_format($Outstanding,2),
 253                                 $i,
 254                                 $i,
 255                                 $myrow['banktransid'],
 256                                 $i
 257                         );
 258                 }
 259
 260                 $j++;
 261                 If ($j == 12){
 262                         $j=1;
 263                         echo $TableHeader;
 264                 }
 265         //end of page full new headings if
 266                 $i++;
 267         }
 268         //end of while loop
 269
 270         echo '</TABLE><INPUT TYPE=HIDDEN NAME="RowCounter" VALUE=' . $i . '><INPUT TYPE=SUBMIT NAME="Update" VALUE="' . _('Update Matching') . '"></CENTER>';
 271
 272 }
 273
 274 echo '</FORM>';
 275 include('includes/footer.inc');
 276 ?>