Care2007/modules/weberp/BankReconciliation.php

   1 <?php
   2
   3 /* $Revision: 1.7 $ */
   4
   5 $PageSecurity = 7;
   6
   7 include ('includes/session.inc');
   8
   9 $title = _('Bank Reconciliation');
  10
  11 include('includes/header.inc');
  12
  13 echo '<FORM METHOD="POST" ACTION="' . $_SERVER["PHP_SELF"] . '?' . SID . '">';
  14
  15 echo '<CENTER><TABLE>';
  16
  17 $SQL = 'SELECT bankaccountname, accountcode FROM bankaccounts';
  18
  19
  20
  21 $ErrMsg = _('The bank accounts could not be retrieved by the SQL because');
  22 $DbgMsg = _('The SQL used to retrieve the bank acconts was');
  23 $AccountsResults = DB_query($SQL,$db,$ErrMsg,$DbgMsg);
  24
  25 echo '<TR><TD>' . _('Bank Account') . ':</TD><TD><SELECT name="BankAccount">';
  26
  27 if (DB_num_rows($AccountsResults)==0){
  28          echo '</SELECT></TD></TR></TABLE><P>' . _('Bank Accounts have not yet been defined') . '. ' . _('You must first') . "<A HREF='" . $rootpath . "/BankAccounts.php'>" . _('define the bank accounts') . '</A>' . ' ' . _('and general ledger accounts to be affected') . '.';
  29         include('includes/footer.inc');
  30         exit;
  31 } else {
  32         while ($myrow=DB_fetch_array($AccountsResults)){
  33                 /*list the bank account names */
  34                 if ($_POST["BankAccount"]==$myrow["accountcode"]){
  35                         echo '<OPTION SELECTED VALUE="' . $myrow["accountcode"] . '">' . $myrow["bankaccountname"];
  36                 } else {
  37                         echo '<OPTION VALUE="' . $myrow["accountcode"] . '">' . $myrow["bankaccountname"];
  38                 }
  39         }
  40         echo '</SELECT></TD></TR>';
  41 }
  42
  43 /*Now do the posting while the user is thinking about the bank account to select */
  44
  45 include ('includes/GLPostings.inc');
  46
  47 echo '</TABLE><P><INPUT TYPE=SUBMIT Name="ShowRec" Value="' . _('Show bank reconciliation statement') . '"></CENTER>';
  48
  49
  50 if (isset($_POST['ShowRec']) AND $_POST['ShowRec']!=''){
  51
  52 /*Get the balance of the bank account concerned */
  53
  54         $sql = "SELECT MAX(period) FROM chartdetails WHERE accountcode=" . $_POST['BankAccount'];
  55         $PrdResult = DB_query($sql, $db);
  56         $myrow = DB_fetch_row($PrdResult);
  57         $LastPeriod = $myrow[0];
  58
  59
  60         $SQL = "SELECT bfwd+actual AS balance FROM chartdetails WHERE period=$LastPeriod AND accountcode=" . $_POST["BankAccount"];
  61
  62         $ErrMsg = _('The bank account balance could not be returned by the SQL because');
  63         $BalanceResult = DB_query($SQL,$db,$ErrMsg);
  64
  65         $myrow = DB_fetch_row($BalanceResult);
  66         $Balance = $myrow[0];
  67
  68         echo '<CENTER><TABLE><TR><TD COLSPAN=6><B>' . _('Current bank account balance as at') . ' ' . Date($_SESSION['DefaultDateFormat']) . '</B></TD><TD VALIGN=BOTTOM ALIGN=RIGHT><B>' . number_format($Balance,2) . '</B></TD></TR>';
  69
  70         $SQL = "SELECT amount/exrate AS amt,
  71                         amountcleared,
  72                         (amount/exrate)-amountcleared as outstanding,
  73                         ref,
  74                         transdate,
  75                         systypes.typename,
  76                         transno
  77                 FROM banktrans,
  78                         systypes
  79                 WHERE banktrans.type = systypes.typeid
  80                 AND banktrans.bankact=" . $_POST["BankAccount"] . "
  81                 AND amount < 0
  82                 AND ABS((amount/exrate)-amountcleared)>0.009";
  83
  84         echo '<TR></TR>'; /*Bang in a blank line */
  85
  86         $ErrMsg = _('The unpresented cheques could not be retrieved by the SQL because');
  87         $UPChequesResult = DB_query($SQL, $db, $ErrMsg);
  88
  89         echo '<TR><TD COLSPAN=6><B>' . _('Add back unpresented cheques') . ':</B></TD></TR>';
  90
  91         $TableHeader = '<TR>
  92                         <TD class="tableheader">' . _('Date') . '</TD>
  93                         <TD class="tableheader">' . _('Type') . '</TD>
  94                         <TD class="tableheader">' . _('Number') . '</TD>
  95                         <TD class="tableheader">' . _('Reference') . '</TD>
  96                         <TD class="tableheader">' . _('Orig Amount') . '</TD>
  97                         <TD class="tableheader">' . _('Outstanding') . '</TD>
  98                         </TR>';
  99
 100         echo $TableHeader;
 101
 102         $j = 1;
 103         $k=0; //row colour counter
 104         $TotalUnpresentedCheques =0;
 105
 106         while ($myrow=DB_fetch_array($UPChequesResult)) {
 107                 if ($k==1){
 108                         echo "<tr bgcolor='#CCCCCC'>";
 109                         $k=0;
 110                 } else {
 111                         echo "<tr bgcolor='#EEEEEE'>";
 112                         $k++;
 113                 }
 114
 115                 printf("<td>%s</td>
 116                         <td>%s</td>
 117                         <td>%s</td>
 118                         <td>%s</td>
 119                         <td ALIGN=RIGHT>%01.2f</td>
 120                         <td ALIGN=RIGHT>%01.2f</td>
 121                         </tr>",
 122                         ConvertSQLDate($myrow['transdate']),
 123                         $myrow['typename'],
 124                         $myrow['transno'],
 125                         $myrow['ref'],
 126                         $myrow['amt'],
 127                         $myrow['outstanding']);
 128
 129                 $TotalUnpresentedCheques +=$myrow['outstanding'];
 130
 131                 $j++;
 132                 If ($j == 18){
 133                         $j=1;
 134                         echo $TableHeader;
 135                 }
 136         }
 137         //end of while loop
 138         echo '<TR></TR><TR><TD COLSPAN=6>' . _('Total of all unpresented cheques') . '</TD><TD ALIGN=RIGHT>' . number_format($TotalUnpresentedCheques,2) . '</TD></TR>';
 139
 140         $SQL = "SELECT amount/exrate AS amt,
 141                         amountcleared,
 142                         (amount/exrate)-amountcleared as outstanding,
 143                         ref,
 144                         transdate,
 145                         systypes.typename,
 146                         transno
 147                 FROM banktrans,
 148                         systypes
 149                 WHERE banktrans.type = systypes.typeid
 150                 AND banktrans.bankact=" . $_POST["BankAccount"] . "
 151                 AND amount > 0
 152                 AND ABS((amount/exrate)-amountcleared)>0.009";
 153
 154         echo '<TR></TR>'; /*Bang in a blank line */
 155
 156         $ErrMsg = _('The uncleared deposits could not be retrieved by the SQL because');
 157
 158         $UPChequesResult = DB_query($SQL,$db,$ErrMsg);
 159
 160         echo '<TR><TD COLSPAN=6><B>' . _('Less deposits not cleared') . ':</B></TD></TR>';
 161
 162         $TableHeader = '<TR>
 163                         <TD class="tableheader">' . _('Date') . '</TD>
 164                         <TD class="tableheader">' . _('Type') . '</TD>
 165                         <TD class="tableheader">' . _('Number') . '</TD>
 166                         <TD class="tableheader">' . _('Reference') . '</TD>
 167                         <TD class="tableheader">' . _('Orig Amount') . '</TD>
 168                         <TD class="tableheader">' . _('Outstanding') . '</TD>
 169                         </TR>';
 170
 171         echo '<TR>' . $TableHeader;
 172
 173         $j = 1;
 174         $k=0; //row colour counter
 175         $TotalUnclearedDeposits =0;
 176
 177         while ($myrow=DB_fetch_array($UPChequesResult)) {
 178                 if ($k==1){
 179                         echo "<tr bgcolor='#CCCCCC'>";
 180                         $k=0;
 181                 } else {
 182                         echo "<tr bgcolor='#EEEEEE'>";
 183                         $k++;
 184                 }
 185
 186                 printf("<td>%s</td>
 187                         <td>%s</td>
 188                         <td>%s</td>
 189                         <td>%s</td>
 190                         <td ALIGN=RIGHT>%01.2f</td>
 191                         <td ALIGN=RIGHT>%01.2f</td>
 192                         </tr>",
 193                         ConvertSQLDate($myrow['transdate']),
 194                         $myrow['typename'],
 195                         $myrow['transno'],
 196                         $myrow['ref'],
 197                         $myrow['amt'],
 198                         $myrow['outstanding']
 199                 );
 200
 201                 $TotalUnclearedDeposits +=$myrow['outstanding'];
 202
 203                 $j++;
 204                 If ($j == 18){
 205                         $j=1;
 206                         echo $TableHeader;
 207                 }
 208         }
 209         //end of while loop
 210         echo '<TR></TR><TR><TD COLSPAN=6>' . _('Total of all uncleared deposits') . '</TD><TD ALIGN=RIGHT>' . number_format($TotalUnclearedDeposits,2) . '</TD></TR>';
 211
 212         echo '<TR></TR><TR><TD COLSPAN=6><B>' . _('Bank statement balance should be') . '</B></TD><TD ALIGN=RIGHT>' . number_format(($Balance - $TotalUnpresentedCheques -$TotalUnclearedDeposits),2) . '</TD></TR>';
 213
 214         echo '</TABLE>';
 215 }
 216 echo '<P><A HREF="' . $rootpath . '/BankMatching.php?' . SID . '&Type=Payments">' . _('Match off cleared payments') . '</A>';
 217 echo '<BR><A HREF="' . $rootpath . '/BankMatching.php?' . SID . '&Type=Receipts">' . _('Match off cleared deposits') . '</A>';
 218 echo '</form>';
 219 include('includes/footer.inc');
 220 ?>