Care2007/modules/weberp/SuppPaymentRun.php

   1 <?php
   2 /* $Revision: 1.12 $ */
   3
   4 $PageSecurity = 5;
   5
   6 Class Allocation {
   7         Var $TransID;
   8         Var $Amount;
   9
  10         function Allocation ($TransID, $Amount){
  11                 $this->TransID = $TransID;
  12                 $this->Amount = $Amount;
  13         }
  14 }
  15
  16 include('includes/session.inc');
  17 include('includes/SQL_CommonFunctions.inc');
  18 include('includes/GetPaymentMethods.php');
  19
  20 If ((isset($_POST['PrintPDF']) OR isset($_POST['PrintPDFAndProcess']))
  21         AND isset($_POST['FromCriteria'])
  22         AND strlen($_POST['FromCriteria'])>=1
  23         AND isset($_POST['ToCriteria'])
  24         AND strlen($_POST['ToCriteria'])>=1
  25         AND is_numeric($_POST['ExRate'])){
  26
  27 /*then print the report */
  28
  29         $RefCounter = 0;
  30         include('includes/PDFStarter.php');
  31         $pdf->addinfo('Title',_('Payment Run Report'));
  32         $pdf->addinfo('Subject',_('Payment Run') . ' - ' . _('suppliers from') . ' ' . $_POST['FromCriteria'] . ' to ' . $_POST['ToCriteria'] . ' in ' . $_POST['Currency'] . ' ' . _('and Due By') . ' ' .  $_POST['AmountsDueBy']);
  33
  34         $PageNumber=1;
  35         $line_height=12;
  36
  37   /*Now figure out the invoice less credits due for the Supplier range under review */
  38
  39         include ('includes/PDFPaymentRunPageHeader.inc');
  40
  41         $sql = "SELECT suppliers.supplierid,
  42                         SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance
  43                 FROM suppliers,
  44                         paymentterms,
  45                         supptrans,
  46                         systypes
  47                 WHERE systypes.typeid = supptrans.type
  48                 AND suppliers.paymentterms = paymentterms.termsindicator
  49                 AND suppliers.supplierid = supptrans.supplierno
  50                 AND supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0
  51                 AND supptrans.duedate <='" . FormatDateForSQL($_POST['AmountsDueBy']) . "'
  52                 AND supptrans.hold=0
  53                 AND suppliers.currcode = '" . $_POST['Currency'] . "'
  54                 AND supptrans.supplierNo >= '" . $_POST['FromCriteria'] . "'
  55                 AND supptrans.supplierno <= '" . $_POST['ToCriteria'] . "'
  56                 GROUP BY suppliers.supplierid
  57                 HAVING SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) > 0
  58                 ORDER BY suppliers.supplierid";
  59
  60         $SuppliersResult = DB_query($sql,$db);
  61
  62         $SupplierID ='';
  63         $TotalPayments = 0;
  64         $TotalAccumDiffOnExch = 0;
  65
  66
  67         if (isset($_POST['PrintPDFAndProcess'])){
  68                 $ProcessResult = DB_query('begin',$db);
  69         }
  70
  71         while ($SuppliersToPay = DB_fetch_array($SuppliersResult)){
  72
  73                 $sql = "SELECT suppliers.supplierid,
  74                                 suppliers.suppname,
  75                                 systypes.typename,
  76                                 paymentterms.terms,
  77                                 supptrans.suppreference,
  78                                 supptrans.trandate,
  79                                 supptrans.rate,
  80                                 supptrans.transno,
  81                                 supptrans.type,
  82                                 (supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,
  83                                 (supptrans.ovamount + supptrans.ovgst ) AS trantotal,
  84                                 supptrans.diffonexch,
  85                                 supptrans.id
  86                         FROM suppliers,
  87                                 paymentterms,
  88                                 supptrans,
  89                                 systypes
  90                         WHERE systypes.typeid = supptrans.type
  91                         AND suppliers.paymentterms = paymentterms.termsindicator
  92                         AND suppliers.supplierid = supptrans.supplierno
  93                         AND supptrans.supplierno = '" . $SuppliersToPay['supplierid'] . "'
  94                         AND supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0
  95                         AND supptrans.duedate <='" . FormatDateForSQL($_POST['AmountsDueBy']) . "'
  96                         AND supptrans.hold=0
  97                         AND suppliers.currcode = '" . $_POST['Currency'] . "'
  98                         AND supptrans.supplierno >= '" . $_POST['FromCriteria'] . "'
  99                         AND supptrans.supplierno <= '" . $_POST['ToCriteria'] . "'
 100                         ORDER BY supptrans.supplierno,
 101                                 supptrans.type,
 102                                 supptrans.transno";
 103
 104                 $TransResult = DB_query($sql,$db,'','',false,false);
 105                 if (DB_error_no($db) !=0) {
 106                         $title = _('Payment Run') . ' - ' . _('Problem Report') . '.... ';
 107                         include('includes/header.inc');
 108                         echo '<BR>' . _('The details of supplier invoices due could not be retrieved because') . ' - ' . DB_error_msg($db);
 109                         echo '<BR><A HREF="' . $rootpath . '/index.php">' . _('Back to the menu') . '</A>';
 110
 111                         if ($debug==1){
 112                                 echo '<BR>' . _('The SQL that failed was') . ' ' . $sql;
 113                         }
 114                         include('includes/footer.inc');
 115                         exit;
 116                 }
 117
 118                 unset($Allocs);
 119                 $Allocs = array();
 120                 $AllocCounter =0;
 121
 122                 while ($DetailTrans = DB_fetch_array($TransResult)){
 123
 124                         if ($DetailTrans['supplierid'] != $SupplierID){ /*Need to head up for a new suppliers details */
 125
 126                                 if ($SupplierID!=''){ /*only print the footer if this is not the first pass */
 127                                         include('includes/PDFPaymentRun_PymtFooter.php');
 128                                 }
 129                                 $SupplierID = $DetailTrans['supplierid'];
 130                                 $SupplierName = $DetailTrans['suppname'];
 131                                 if (isset($_POST['PrintPDFAndProcess'])){
 132                                         $SuppPaymentNo = GetNextTransNo(22, $db);
 133                                 }
 134                                 $AccumBalance = 0;
 135                                 $AccumDiffOnExch = 0;
 136                                 $LeftOvers = $pdf->addTextWrap($Left_Margin,
 137                                                                         $YPos,
 138                                                                         450-$Left_Margin,
 139                                                                         $FontSize,
 140                                                                         $DetailTrans['supplierid'] . ' - ' . $DetailTrans['suppname'] . ' - ' . $DetailTrans['terms'],
 141                                                                         'left');
 142
 143                                 $YPos -= $line_height;
 144                         }
 145
 146                         $DislayTranDate = ConvertSQLDate($DetailTrans['trandate']);
 147
 148                         $LeftOvers = $pdf->addTextWrap($Left_Margin+15, $YPos, 340-$Left_Margin,$FontSize,$DislayTranDate . ' - ' . $DetailTrans['typename'] . ' - ' . $DetailTrans['suppreference'], 'left');
 149
 150                         /*Positive is a favourable */
 151                         $DiffOnExch = ($DetailTrans['balance'] / $DetailTrans['rate']) -  ($DetailTrans['balance'] / $_POST['ExRate']);
 152
 153                         $AccumBalance += $DetailTrans['balance'];
 154                         $AccumDiffOnExch += $DiffOnExch;
 155
 156
 157                         if (isset($_POST['PrintPDFAndProcess'])){
 158
 159                                 /*Record the Allocations for later insertion once we have the ID of the payment SuppTrans */
 160
 161                                 $Allocs[$AllocCounter] = new Allocation($DetailTrans['id'],$DetailTrans['balance']);
 162                                 $AllocCounter++;
 163
 164                                 /*Now update the SuppTrans for the allocation made and the fact that it is now settled */
 165
 166                                 $SQL = "UPDATE supptrans SET settled = 1,
 167                                                                 alloc = " . $DetailTrans['trantotal'] . ",
 168                                                                 diffonexch = " . ($DetailTrans['diffonexch'] + $DiffOnExch)  . "
 169                                         WHERE type = " . $DetailTrans['type'] . '
 170                                         AND transno = ' . $DetailTrans['transno'];
 171
 172                                 $ProcessResult = DB_query($SQL,$db,'','',false,false);
 173                                 if (DB_error_no($db) !=0) {
 174                                         $title = _('Payment Processing - Problem Report') . '.... ';
 175                                         include('includes/header.inc');
 176                                         echo '<BR>' . _('None of the payments will be processed since updates to the transaction records for') . ' ' .$SupplierName . ' ' . _('could not be processed because') . ' - ' . DB_error_msg($db);
 177                                         echo '<BR><A HREF="' . $rootpath . '/index.php">' . _('Back to the menu') . '</A>';
 178                                         if ($debug==1){
 179                                                 echo '<BR>' . _('The SQL that failed was') . $SQL;
 180                                         }
 181                                         $SQL= 'rollback';
 182                                         $ProcessResult = DB_query($SQL,$db);
 183
 184                                         include('includes/footer.inc');
 185                                         exit;
 186                                 }
 187                         }
 188
 189                         $LeftOvers = $pdf->addTextWrap(340, $YPos,60,$FontSize,number_format($DetailTrans['balance'],2), 'right');
 190                         $LeftOvers = $pdf->addTextWrap(405, $YPos,60,$FontSize,number_format($DiffOnExch,2), 'right');
 191
 192                         $YPos -=$line_height;
 193                         if ($YPos < $Bottom_Margin + $line_height){
 194                                 $PageNumber++;
 195                                 include('includes/PDFPaymentRunPageHeader.inc');
 196                         }
 197                 } /*end while there are detail transactions to show */
 198         } /* end while there are suppliers to retrieve transactions for */
 199
 200         if ($SupplierID!=''){
 201
 202                 include('includes/PDFPaymentRun_PymtFooter.php');
 203
 204                 $ProcessResult = DB_query('COMMIT',$db,'','',false,false);
 205                 if (DB_error_no($db) !=0) {
 206                         $title = _('Payment Processing - Problem Report') . '.... ';
 207                         include('includes/header.inc');
 208                         echo '<BR>' . _('None of the payments will be processed') . '. ' . _('Unfortunately there was a problem committing the changes to the database because') . ' - ' . DB_error_msg($db);
 209                         echo '<BR><A HREF="' . $rootpath . '/index.php">' . _('Back to the menu') . '</A>';
 210                         if ($debug==1){
 211                                 echo '<BR>' . _('The SQL that failed was') . $SQL;
 212                         }
 213                         $SQL= 'rollback';
 214                         $ProcessResult = DB_query($SQL,$db);
 215                         include('includes/footer.inc');
 216                         exit;
 217                 }
 218
 219                 $LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 340-$Left_Margin,$FontSize,_('Grand Total Payments Due'), 'left');
 220                 $LeftOvers = $pdf->addTextWrap(340, $YPos, 60,$FontSize,number_format($TotalPayments,2), 'right');
 221                 $LeftOvers = $pdf->addTextWrap(405, $YPos, 60,$FontSize,number_format($TotalAccumDiffOnExch,2), 'right');
 222
 223         }
 224
 225         $pdfcode = $pdf->output();
 226         $len = strlen($pdfcode);
 227         header('Content-type: application/pdf');
 228         header('Content-Length: ' . $len);
 229         header('Content-Disposition: inline; filename=PaymentRun.pdf');
 230         header('Expires: 0');
 231         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 232         header('Pragma: public');
 233
 234         $pdf->stream();
 235
 236         header('Expires: 0');
 237         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 238         header('Pragma: public');
 239
 240
 241 } else { /*The option to print PDF was not hit */
 242
 243         $title=_('Payment Run');
 244         include('includes/header.inc');
 245
 246         if (isset($_POST['Currency']) AND !is_numeric($_POST['ExRate'])){
 247                 echo '<BR>' . _('To process payments for') . ' ' . $_POST['Currency'] . ' ' . _('a numeric exchange rate applicable for purchasing the currency to make the payment with must be entered') . '. ' . _('This rate is used to calculate the difference in exchange and make the necessary postings to the General ledger if linked') . '.';
 248         }
 249
 250         /* show form to allow input     */
 251
 252         echo '<FORM ACTION="' . $_SERVER['PHP_SELF'] . '?' . SID . '" METHOD="POST"><CENTER><TABLE>';
 253
 254         if (strlen($_POST['FromCriteria'])<1){
 255                 $DefaultFromCriteria = '1';
 256         } else {
 257                 $DefaultFromCriteria = $_POST['FromCriteria'];
 258         }
 259         if (strlen($_POST['ToCriteria'])<1){
 260                 $DefaultToCriteria = 'zzzzzzz';
 261         } else {
 262                 $DefaultToCriteria = $_POST['ToCriteria'];
 263         }
 264         echo '<TR><TD>' . _('From Supplier Code') . ':</FONT></TD>
 265             <TD><input Type=text maxlength=6 size=7 name=FromCriteria value="' . $DefaultFromCriteria . '"></TD></TR>';
 266         echo '<TR><TD>' . _('To Supplier Code') . ':</TD>
 267             <TD><input Type=text maxlength=6 size=7 name=ToCriteria value="' . $DefaultToCriteria . '"></TD></TR>';
 268
 269
 270         echo '<TR><TD>' . _('For Suppliers Trading in') . ':</TD><TD><SELECT name="Currency">';
 271         $sql = 'SELECT currency, currabrev FROM currencies';
 272         $result=DB_query($sql,$db);
 273
 274         while ($myrow=DB_fetch_array($result)){
 275         if ($myrow['currabrev'] == $_SESSION['CompanyRecord']['currencydefault']){
 276                         echo '<OPTION SELECTED Value="' . $myrow['currabrev'] . '">' . $myrow['currency'];
 277         } else {
 278                 echo '<OPTION Value="' . $myrow['currabrev'] . '">' . $myrow['currency'];
 279         }
 280         }
 281         echo '</SELECT></TD></TR>';
 282
 283         if (!is_numeric($_POST['ExRate'])){
 284                 $DefaultExRate = '1';
 285         } else {
 286                 $DefaultExRate = $_POST['ExRate'];
 287         }
 288         echo '<TR><TD>' . _('Exchange Rate') . ':</TD>
 289             <TD><INPUT TYPE=text name="ExRate" MAXLENGTH=11 SIZE=12 VALUE=' . $DefaultExRate . '></TD></TR>';
 290
 291         if (!isset($_POST['AmountsDueBy'])){
 292                 $DefaultDate = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m')+1,0 ,Date('y')));
 293         } else {
 294                 $DefaultDate = $_POST['AmountsDueBy'];
 295         }
 296
 297         echo '<TR><TD>' . _('Payments Due To') . ':</TD>
 298             <TD><INPUT TYPE=text name="AmountsDueBy" MAXLENGTH=11 SIZE=12 VALUE=' . $DefaultDate . '></TD></TR>';
 299
 300         $SQL = 'SELECT bankaccountname, accountcode FROM bankaccounts';
 301
 302         $AccountsResults = DB_query($SQL,$db,'','',false,false);
 303
 304         if (DB_error_no($db) !=0) {
 305                  echo '<BR>' . _('The bank accounts could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db);
 306                  if ($debug==1){
 307                         echo '<BR>' . _('The SQL used to retrieve the bank acconts was') . ':<BR>' . $SQL;
 308                  }
 309                  exit;
 310         }
 311
 312         echo '<TR><TD>' . _('Pay From Account') . ':</TD><TD><SELECT name="BankAccount">';
 313
 314         if (DB_num_rows($AccountsResults)==0){
 315                  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') . '.';
 316                  include('includes/footer.inc');
 317                  exit;
 318         } else {
 319                 while ($myrow=DB_fetch_array($AccountsResults)){
 320                       /*list the bank account names */
 321
 322                         if ($_POST['BankAccount']==$myrow['accountcode']){
 323                                 echo '<OPTION SELECTED VALUE="' . $myrow['accountcode'] . '">' . $myrow['bankaccountname'];
 324                         } else {
 325                                 echo '<OPTION VALUE="' . $myrow['accountcode'] . '">' . $myrow['bankaccountname'];
 326                         }
 327                 }
 328                 echo '</SELECT></TD></TR>';
 329         }
 330
 331         echo '<TR><TD>' . _('Payment Type') . ':</TD><TD><SELECT name=PaytType>';
 332
 333 /* The array PaytTypes is set up in config.php for user modification
 334 Payment types can be modified by editing that file */
 335
 336         foreach ($PaytTypes as $PaytType) {
 337
 338              if ($_POST['PaytType']==$PaytType){
 339                    echo '<OPTION SELECTED Value="' . $PaytType . '">' . $PaytType;
 340              } else {
 341                    echo '<OPTION Value="' . $PaytType . '">' . $PaytType;
 342              }
 343         }
 344         echo '</SELECT></TD></TR>';
 345
 346         if (!is_numeric($_POST['Ref'])){
 347                 $DefaultRef = '1';
 348         } else {
 349                 $DefaultRef = $_POST['Ref'];
 350         }
 351
 352         echo '<TR><TD>' . _('Starting Reference no (eg chq no)') . ':</TD>
 353             <TD><INPUT TYPE=text name="Ref" MAXLENGTH=11 SIZE=12 VALUE=' . $_POST['Ref'] . '></TD></TR>';
 354
 355         echo '</TABLE><INPUT TYPE=Submit Name="PrintPDF" Value="' . _('Print PDF Only') . '">
 356                 <INPUT TYPE=Submit Name="PrintPDFAndProcess" Value="' . _('Print and Process Payments') . '"></CENTER>';
 357
 358         include ('includes/footer.inc');
 359 } /*end of else not PrintPDF */
 360 ?>