Care2007/modules/weberp/CustomerReceipt.php.orig

   1 <?php
   2
   3 /* $Revision: 1.16 $ */
   4
   5 include('includes/DefineReceiptClass.php');
   6
   7 $PageSecurity = 3;
   8 include('includes/session.inc');
   9
  10 $title = _('Receipt Entry');
  11
  12 include('includes/header.inc');
  13 include('includes/SQL_CommonFunctions.inc');
  14
  15 $msg='';
  16
  17
  18 if (isset($_POST['CommitBatch'])){
  19
  20  /* once all receipts items entered, process all the data in the
  21   session cookie into the DB creating a single banktrans for the whole amount
  22   of all receipts in the batch and DebtorTrans records for each receipt item
  23   all DebtorTrans will refer to a single banktrans. A GL entry is created for
  24   each GL receipt entry and one for the debtors entry and one for the bank
  25   account debit
  26
  27   NB allocations against debtor receipts are a seperate exercice
  28
  29   first off run through the array of receipt items $_SESSION['ReceiptBatch']->Items and
  30   if GL integrated then create GL Entries for the GL Receipt items
  31   and add up the non-GL ones for posting to debtors later,
  32   also add the total discount total receipts*/
  33
  34    $PeriodNo = GetPeriod($_SESSION['ReceiptBatch']->DateBanked,$db);
  35
  36    if ($_SESSION['CompanyRecord']==0){
  37         prnMsg(_('The company has not yet been set up properly') . ' - ' . _('this information is needed to process the batch') . '. ' . _('Processing has been cancelled'),'error');
  38         include('includes/footer.inc');
  39         exit;
  40    }
  41
  42    /*Make an array of the defined bank accounts */
  43    $SQL = "SELECT accountcode FROM bankaccounts";
  44    $result = DB_query($SQL,$db);
  45    $BankAccounts = array();
  46    $i=0;
  47    while ($Act = DB_fetch_row($result)){
  48         $BankAccounts[$i]= $Act[0];
  49         $i++;
  50    }
  51
  52    /*Start a transaction to do the whole lot inside */
  53    $SQL = "BEGIN";
  54    $result = DB_query($SQL,$db);
  55
  56    $BatchReceiptsTotal = 0;
  57    $BatchDiscount = 0;
  58    $BatchDebtorTotal = 0;
  59
  60    foreach ($_SESSION['ReceiptBatch']->Items as $ReceiptItem) {
  61
  62             if ($ReceiptItem->GLCode !=''){
  63                 if ($_SESSION['CompanyRecord']['gllink_debtors']==1){ /* then enter a GLTrans record */
  64                          $SQL = "INSERT INTO gltrans (type,
  65                                                 typeno,
  66                                                 trandate,
  67                                                 periodno,
  68                                                 account,
  69                                                 narrative,
  70                                                 amount) ";
  71                          $SQL= $SQL . "VALUES (12,
  72                                                 " . $_SESSION['ReceiptBatch']->BatchNo . ",
  73                                                 '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
  74                                                 " . $PeriodNo . ",
  75                                                 " . $ReceiptItem->GLCode . ",
  76                                                 '" . DB_escape_string($ReceiptItem->Narrative) . "',
  77                                                 " . -$ReceiptItem->Amount/$_SESSION['ReceiptBatch']->ExRate . ")";
  78                          $ErrMsg = _('Cannot insert a GL entry for the receipt because');
  79                          $DbgMsg = _('The SQL that failed to insert the receipt GL entry was');
  80                          $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
  81
  82                 }
  83
  84                 /*check to see if this is a GL posting to another bank account (or the same one)
  85                 if it is then a matching payment needs to be created for this account too */
  86
  87                 if (in_array($ReceiptItem->GLCode, $BankAccounts)) {
  88
  89                         $PaymentTransNo = GetNextTransNo( 1, $db);
  90                         $SQL="INSERT INTO banktrans (transno,
  91                                                         type,
  92                                                         bankact,
  93                                                         ref,
  94                                                         exrate,
  95                                                         transdate,
  96                                                         banktranstype,
  97                                                         amount,
  98                                                         currcode) ";
  99                         $SQL= $SQL . "VALUES (" . $PaymentTransNo . ",
 100                                                 1,
 101                                                 " . $ReceiptItem->GLCode . ",
 102                                                 '" . _('Act Transfer') .' - ' . DB_escape_string($ReceiptItem->Narrative) . "',
 103                                                 " . $_SESSION['ReceiptBatch']->ExRate . " ,
 104                                                 '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 105                                                 '" . $_SESSION['ReceiptBatch']->ReceiptType . "',
 106                                                 " . -$ReceiptItem->Amount . ",
 107                                                 '" . $_SESSION['ReceiptBatch']->Currency . "'
 108                                         )";
 109
 110                         $DbgMsg = _('The SQL that failed to insert the bank transaction was');
 111                         $ErrMsg = _('Cannot insert a bank transaction using the SQL');
 112                         $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 113                 }
 114
 115             } else {
 116                    /*Accumulate the total debtors credit including discount */
 117                    $BatchDebtorTotal = $BatchDebtorTotal + (($ReceiptItem->Discount + $ReceiptItem->Amount)/$_SESSION['ReceiptBatch']->ExRate);
 118                    /*Create a DebtorTrans entry for each customer deposit */
 119
 120                    $SQL = "INSERT INTO debtortrans (transno,
 121                                                         type,
 122                                                         debtorno,
 123                                                         branchcode,
 124                                                         trandate,
 125                                                         prd,
 126                                                         reference,
 127                                                         tpe,
 128                                                         rate,
 129                                                         ovamount,
 130                                                         ovdiscount,
 131                                                         invtext) ";
 132                    $SQL = $SQL . "VALUES (" . $_SESSION['ReceiptBatch']->BatchNo . ",
 133                                                 12,
 134                                                 '" . $ReceiptItem->Customer . "',
 135                                                 '',
 136                                                 '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 137                                                 " . $PeriodNo . ",
 138                                                 '" . DB_escape_string($_SESSION['ReceiptBatch']->ReceiptType  . " " . $ReceiptItem->Narrative) . "',
 139                                                 '',
 140                                                 " . $_SESSION['ReceiptBatch']->ExRate . ",
 141                                                 " . -$ReceiptItem->Amount . ",
 142                                                 " . -$ReceiptItem->Discount . ",
 143                                                 '" . $ReceiptItem->PayeeBankDetail . "'
 144                                         )";
 145                 $DbgMsg = _('The SQL that failed to insert the customer receipt transaction was');
 146                 $ErrMsg = _('Cannot insert a receipt transaction against the customer because') ;
 147                 $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 148
 149                 $SQL = "UPDATE debtorsmaster SET lastpaiddate = '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 150                                                 lastpaid=" . $ReceiptItem->Amount ."
 151                                         WHERE debtorsmaster.debtorno='" . $ReceiptItem->Customer . "'";
 152
 153                 $DbgMsg = _('The SQL that failed to update the date of the last payment received was');
 154                 $ErrMsg = _('Cannot update the customer record for the date of the last payment received because');
 155                 $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 156
 157             }
 158             $BatchDiscount = $BatchDiscount + $ReceiptItem->Discount/$_SESSION['ReceiptBatch']->ExRate;
 159             $BatchReceiptsTotal = $BatchReceiptsTotal + $ReceiptItem->Amount/$_SESSION['ReceiptBatch']->ExRate;
 160
 161    }
 162    if ($_SESSION['CompanyRecord']['gllink_debtors']==1){ /* then enter GLTrans records for discount, bank and debtors */
 163
 164         if ($BatchReceiptsTotal!=0){
 165                 /* Bank account entry first */
 166                 $SQL="INSERT INTO gltrans (type,
 167                                         typeno,
 168                                         trandate,
 169                                         periodno,
 170                                         account,
 171                                         narrative,
 172                                         amount)
 173                         VALUES (12,
 174                                 " . $_SESSION['ReceiptBatch']->BatchNo . ",
 175                                 '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 176                                 " . $PeriodNo . ",
 177                                 " . $_SESSION['ReceiptBatch']->Account . ",
 178                                 '" . DB_escape_string($_SESSION['ReceiptBatch']->Narrative) . "',
 179                                 " . $BatchReceiptsTotal . "
 180                         )";
 181                 $DbgMsg = _('The SQL that failed to insert the GL transaction fro the bank account debit was');
 182                 $ErrMsg = _('Cannot insert a GL transaction for the bank account debit');
 183                 $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 184
 185                 if ($BatchDebtorTotal!=0){
 186                         /* Now Credit Debtors account with receipts + discounts */
 187                         $SQL="INSERT INTO gltrans ( type,
 188                                                 typeno,
 189                                                 trandate,
 190                                                 periodno,
 191                                                 account,
 192                                                 narrative,
 193                                                 amount)
 194                                 VALUES (12,
 195                                         " . $_SESSION['ReceiptBatch']->BatchNo . ",
 196                                         '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 197                                         " . $PeriodNo . ",
 198                                         " . $_SESSION['CompanyRecord']['debtorsact'] . ",
 199                                         '" . DB_escape_string($_SESSION['ReceiptBatch']->Narrative) . "',
 200                                         " . -$BatchDebtorTotal . "
 201                                 )";
 202                         $DbgMsg = _('The SQL that failed to insert the GL transaction for the debtors account credit was');
 203                         $ErrMsg = _('Cannot insert a GL transaction for the debtors account credit');
 204                         $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 205
 206                 }
 207
 208                 if ($BatchDiscount!=0){
 209                         /* Now Debit Discount account with discounts allowed*/
 210                         $SQL="INSERT INTO gltrans ( type,
 211                                                 typeno,
 212                                                 trandate,
 213                                                 periodno,
 214                                                 account,
 215                                                 narrative,
 216                                                 amount)
 217                                 VALUES (12,
 218                                         " . $_SESSION['ReceiptBatch']->BatchNo . ",
 219                                         '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 220                                         " . $PeriodNo . ",
 221                                         " . $_SESSION['CompanyRecord']['pytdiscountact'] . ",
 222                                         '" . DB_escape_string($_SESSION['ReceiptBatch']->Narrative) . "',
 223                                         " . $BatchDiscount . "
 224                                 )";
 225                         $DbgMsg = _('The SQL that failed to insert the GL transaction for the payment discount debit was');
 226                         $ErrMsg = _('Cannot insert a GL transaction for the payment discount debit');
 227                         $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 228                 }
 229         }
 230    }
 231
 232    /*now enter the BankTrans entry */
 233
 234    $SQL="INSERT INTO banktrans (type,
 235                                 transno,
 236                                 bankact,
 237                                 ref,
 238                                 exrate,
 239                                 transdate,
 240                                 banktranstype,
 241                                 amount,
 242                                 currcode)
 243         VALUES (12,
 244                 " . $_SESSION['ReceiptBatch']->BatchNo . ",
 245                 " . $_SESSION['ReceiptBatch']->Account . ",
 246                 '" . DB_escape_string($_SESSION['ReceiptBatch']->Narrative) . "',
 247                 " . $_SESSION['ReceiptBatch']->ExRate . ",
 248                 '" . FormatDateForSQL($_SESSION['ReceiptBatch']->DateBanked) . "',
 249                 '" . $_SESSION['ReceiptBatch']->ReceiptType . "',
 250                 " . ($BatchReceiptsTotal * $_SESSION["ReceiptBatch"]->ExRate) . ",
 251                 '" . $_SESSION['ReceiptBatch']->Currency . "'
 252         )";
 253         $DbgMsg = _('The SQL that failed to insert the bank account transaction was');
 254         $ErrMsg = _('Cannot insert a bank transaction');
 255         $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
 256
 257    $ErrMsg = _('Cannot commit the changes');
 258    $DbgMsg = _('The SQL that failed was');
 259    $result = DB_query('COMMIT',$db,$ErrMsg,$DbgMsg,true);
 260
 261    echo '<P>';
 262
 263    prnMsg( _('Receipt batch') . ' ' . $_SESSION['ReceiptBatch']->BatchNo . ' ' . _('has been successfully entered into the database'),'success');
 264
 265    echo "<BR><A HREF='" . $rootpath . "/PDFBankingSummary.php?BatchNo=" . $_SESSION['ReceiptBatch']->BatchNo . "'>" . _('Print PDF Batch Summary') . "</A>";
 266    unset($_SESSION['ReceiptBatch']);
 267
 268 } elseif (isset($_POST['BatchInput'])){ //submitted a new batch
 269
 270 /*Need to do a reality check on exchange rate entered initially to ensure sensible to proceed */
 271         if ($_POST['Currency']!=$_SESSION['CompanyRecord']['currencydefault'] AND $_POST['ExRate']==1){
 272                 prnMsg(_('An exchange rate of 1 is only appropriate for receipts in the companies functional currency - enter an appropriate exchange rate'),'error');
 273
 274         } else {
 275
 276                 $_POST['BatchNo'] = GetNextTransNo(12,$db);
 277
 278
 279                 /*if the session already has a $_SESSION['ReceiptBatch'] set up ... lose it
 280                 and start a fresh! */
 281                 if (isset($_SESSION['ReceiptBatch'])){
 282                 unset($_SESSION['ReceiptBatch']);
 283                 }
 284                 $_SESSION['ReceiptBatch'] = new Receipt_Batch;
 285                 $_SESSION['ReceiptBatch']->BatchNo = $_POST['BatchNo'];
 286                 $_SESSION['ReceiptBatch']->Account = $_POST['BankAccount'];
 287                         if (!Is_Date($_POST['DateBanked'])){
 288                         $_POST['DateBanked'] = Date($_SESSION['DefaultDateFormat']);
 289                         }
 290                 $_SESSION['ReceiptBatch']->DateBanked = $_POST['DateBanked'];
 291                 $_SESSION['ReceiptBatch']->ExRate = $_POST['ExRate'];
 292                 $_SESSION['ReceiptBatch']->ReceiptType = $_POST['ReceiptType'];
 293                 $_SESSION['ReceiptBatch']->Currency = $_POST['Currency'];
 294                 $_SESSION['ReceiptBatch']->Narrative = $_POST['BatchNarrative'];
 295                 $_SESSION['ReceiptBatch']->ID = 1;
 296
 297                 $SQL = "SELECT bankaccountname FROM bankaccounts WHERE accountcode=" . $_POST['BankAccount'];
 298                 $result= DB_query($SQL,$db,'','',false,false);
 299
 300                 if (DB_error_no($db) !=0) {
 301                         prnMsg(_('The bank account name cannot be retrieved because') . ' - ' . DB_error_msg($db),'error');
 302                         if ($debug==1) {
 303                                 echo '<BR>' . _('SQL used to retrieve the bank account name was') . '<BR>' . $sql;
 304                         }
 305                         include ('includes/footer.inc');
 306                         exit;
 307                 } elseif (DB_num_rows($result)==1){
 308                         $myrow = DB_fetch_row($result);
 309                         $_SESSION['ReceiptBatch']->BankAccountName = $myrow[0];
 310                         unset($result);
 311                 } elseif (DB_num_rows($result)==0){
 312                         prnMsg( _('The bank account number') . ' ' . $_POST['BankAccount'] . ' ' . _('is not set up as a bank account'),'error');
 313                         include ('includes/footer.inc');
 314                         exit;
 315                 }
 316         }
 317
 318 } elseif (isset($_GET['Delete'])){
 319   /* User hit delete the receipt entry from the batch */
 320    $_SESSION['ReceiptBatch']->remove_receipt_item($_GET['Delete']);
 321 } elseif (isset($_POST['Process'])){ //user hit submit a new entry to the receipt batch
 322
 323    $_SESSION['ReceiptBatch']->add_to_batch($_POST['amount'],
 324                                                 $_POST['CustomerID'],
 325                                                 $_POST['discount'],
 326                                                 $_POST['Narrative'],
 327                                                 $_POST['GLCode'],
 328                                                 $_POST['PayeeBankDetail'],
 329                                                 $_POST['CustomerName']);
 330
 331    /*Make sure the same receipt is not double processed by a page refresh */
 332    $Cancel = 1;
 333
 334 }
 335
 336 if (isset($Cancel)){
 337    unset($_SESSION['CustomerRecord']);
 338    unset($_POST['CustomerID']);
 339    unset($_POST['CustomerName']);
 340    unset($_POST['amount']);
 341    unset($_POST['discount']);
 342    unset($_POST['Narrative']);
 343    unset($_POST['PayeeBankDetail']);
 344 }
 345
 346 if (isset($_POST['Search'])){
 347 /*Will only be true if clicked to search for a customer code */
 348
 349         If ($_POST['Keywords'] AND $_POST['CustCode']) {
 350                 $msg=_('Customer name keywords have been used in preference to the customer code extract entered');
 351         }
 352         If ($_POST['Keywords']=="" AND $_POST['CustCode']=="") {
 353                 $msg=_('At least one Customer Name keyword OR an extract of a Customer Code must be entered for the search');
 354         } else {
 355                 If (strlen($_POST['Keywords'])>0) {
 356                         //insert wildcard characters in spaces
 357
 358                         $i=0;
 359                         $SearchString = "%";
 360                         while (strpos($_POST['Keywords'], " ", $i)) {
 361                                 $wrdlen=strpos($_POST['Keywords']," ",$i) - $i;
 362                                 $SearchString=$SearchString . substr($_POST['Keywords'],$i,$wrdlen) . "%";
 363                                 $i=strpos($_POST['Keywords']," ",$i) +1;
 364                         }
 365                         $SearchString = $SearchString . substr($_POST['Keywords'],$i)."%";
 366                         $SQL = "SELECT debtorsmaster.debtorno,
 367                                         debtorsmaster.name
 368                                 FROM debtorsmaster
 369                                 WHERE debtorsmaster.name " . LIKE . " '$SearchString'
 370                                 AND debtorsmaster.currcode= '" . $_SESSION['ReceiptBatch']->Currency . "'";
 371
 372                 } elseif (strlen($_POST['CustCode'])>0){
 373                         $SQL = "SELECT debtorsmaster.debtorno,
 374                                         debtorsmaster.name
 375                                 FROM debtorsmaster
 376                                 WHERE debtorsmaster.debtorno " . LIKE . " '%" . $_POST['CustCode'] . "%'
 377                                 AND debtorsmaster.currcode= '" . $_SESSION['ReceiptBatch']->Currency . "'";
 378                 }
 379
 380                 $result = DB_query($SQL,$db,'','',false,false);
 381                 if (DB_error_no($db) !=0) {
 382                         prnMsg(_('The searched customer records requested cannot be retrieved because') . ' - ' . DB_error_msg($db),'error');
 383                         if ($debug==1){
 384                                 prnMsg(_('SQL used to retrieve the customer details was') . '<BR>' . $sql,'error');
 385                         }
 386                 } elseif (DB_num_rows($result)==1){
 387                         $myrow=DB_fetch_array($result);
 388                         $Select = $myrow["debtorno"];
 389                         unset($result);
 390                 } elseif (DB_num_rows($result)==0){
 391                         prnMsg( _('No customers containing the selected text and who trade in the currency of this receipt batch could be found') . ' - ' . _('please alter your search criteria and try again'),'info');
 392                 }
 393
 394         } //one of keywords or custcode was more than a zero length string
 395 } //end of if search
 396
 397 If (isset($_POST['Select'])){
 398         $Select = $_POST['Select'];
 399 }
 400
 401 If (isset($Select)) {
 402 /*will only be true if a customer has just been selected by clicking on the customer or only one
 403 customer record returned by the search - this record is then auto selected */
 404
 405         $_POST['CustomerID']=$Select;
 406         /*need to get currency sales type - payment discount percent and GL code
 407         as well as payment terms and credit status and hold the lot as session variables
 408         the receipt held entirely as session variables until the button clicked to process*/
 409
 410
 411         if (isset($_SESSION['CustomerRecord'])){
 412            unset($_SESSION['CustomerRecord']);
 413         }
 414
 415         $SQL = "SELECT debtorsmaster.name,
 416                         debtorsmaster.pymtdiscount,
 417                         debtorsmaster.currcode,
 418                         currencies.currency,
 419                         currencies.rate,
 420                         paymentterms.terms,
 421                         debtorsmaster.creditlimit,
 422                         holdreasons.dissallowinvoices,
 423                         holdreasons.reasondescription,
 424                         SUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc) AS balance,
 425                         SUM(CASE WHEN paymentterms.daysbeforedue > 0  THEN
 426                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)) >= paymentterms.daysbeforedue  THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 427                         ELSE
 428                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1','MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))','DAY') . ")) >= 0 THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 429                         END) AS due,
 430                         SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
 431                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue   AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 432                         ELSE
 433                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') ."), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 434                         END) AS overdue1,
 435                         SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
 436                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 437                         ELSE
 438                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1','MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))','DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
 439                         END) AS overdue2
 440                         FROM debtorsmaster,
 441                                 paymentterms,
 442                                 holdreasons,
 443                                 currencies,
 444                                 debtortrans
 445                         WHERE debtorsmaster.paymentterms = paymentterms.termsindicator
 446                         AND debtorsmaster.currcode = currencies.currabrev
 447                         AND debtorsmaster.holdreason = holdreasons.reasoncode
 448                         AND debtorsmaster.debtorno = '" . $_POST['CustomerID'] . "'
 449                         AND debtorsmaster.debtorno = debtortrans.debtorno
 450                         GROUP BY debtorsmaster.name,
 451                                 debtorsmaster.pymtdiscount,
 452                                 debtorsmaster.currcode,
 453                                 currencies.currency,
 454                                 currencies.rate,
 455                                 paymentterms.terms,
 456                                 debtorsmaster.creditlimit,
 457                                 paymentterms.daysbeforedue,
 458                                 paymentterms.dayinfollowingmonth,
 459                                 debtorsmaster.creditlimit,
 460                                 holdreasons.dissallowinvoices,
 461                                 holdreasons.reasondescription";
 462
 463
 464         $ErrMsg = _('The customer details could not be retrieved because');
 465         $DbgMsg = _('The SQL that failed was');
 466         $CustomerResult = DB_query($SQL,$db,$ErrMsg, $DbgMsg);
 467
 468         if (DB_num_rows($CustomerResult)==0){
 469
 470                 /*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
 471
 472                 $NIL_BALANCE = True;
 473
 474                 $SQL = "SELECT debtorsmaster.name,
 475                                 debtorsmaster.pymtdiscount,
 476                                 currencies.currency,
 477                                 currencies.rate,
 478                                 paymentterms.terms,
 479                                 debtorsmaster.creditlimit,
 480                                 debtorsmaster.currcode,
 481                                 holdreasons.dissallowinvoices,
 482                                 holdreasons.reasondescription
 483                         FROM debtorsmaster,
 484                                 paymentterms,
 485                                 holdreasons,
 486                                 currencies
 487                         WHERE debtorsmaster.paymentterms = paymentterms.termsindicator
 488                         AND debtorsmaster.currcode = currencies.currabrev
 489                         AND debtorsmaster.holdreason = holdreasons.reasoncode
 490                         AND debtorsmaster.debtorno = '" . $_POST['CustomerID'] . "'";
 491
 492                 $ErrMsg = _('The customer details could not be retrieved because');
 493                 $DbgMsg = _('The SQL that failed was');
 494                 $CustomerResult = DB_query($SQL,$db,$ErrMsg, $DbgMsg);
 495
 496         } else {
 497                 $NIL_BALANCE = False;
 498         }
 499
 500         $_SESSION['CustomerRecord'] = DB_fetch_array($CustomerResult);
 501
 502         if ($NIL_BALANCE==True){
 503                 $_SESSION['CustomerRecord']['balance']=0;
 504                 $_SESSION['CustomerRecord']['due']=0;
 505                 $_SESSION['CustomerRecord']['overdue1']=0;
 506                 $_SESSION['CustomerRecord']['overdue2']=0;
 507         }
 508 } /*end of if customer has just been selected  all info required read into $_SESSION['CustomerRecord']*/
 509
 510 /*set up the form whatever */
 511
 512
 513 echo "<FORM ACTION=" . $_SERVER['PHP_SELF'] . " METHOD=POST>";
 514
 515 /*this block of ifs decides whether to show the new batch entry screen or not
 516 based on the settings for $_POST['BatchNo'] and $_POST['BankAccount'] if they have already been
 517 selected ie the form has called itself at least once then the page saves the variable
 518 settings in a cookie. */
 519
 520 if (isset($_SESSION['ReceiptBatch'])){
 521
 522    /*show the batch header details and the entries in the batch so far */
 523
 524    echo "<FONT SIZE=3 COLOR=BLUE>" . $_SESSION['ReceiptBatch']->ReceiptType . " " . _('Batch') . ": " . $_SESSION['ReceiptBatch']->BatchNo . " - " . _('Banked into the') . " " . $_SESSION['ReceiptBatch']->BankAccountName . " " . _('on') . " " . $_SESSION['ReceiptBatch']->DateBanked . "</FONT>";
 525
 526    echo "<TABLE WIDTH=100% BORDER=1><TR>
 527                 <TD class='tableheader'>" . _('Amount') .'<BR>' . _('Received') . "</TD>
 528                 <TD class='tableheader'>" . _('Discount') . "</TD>
 529                 <TD class='tableheader'>" . _('Customer') . "</TD>
 530                 <TD class='tableheader'>" . _('GL Code') . "</TD>
 531         </TR>";
 532
 533    $BatchTotal = 0;
 534
 535    foreach ($_SESSION['ReceiptBatch']->Items as $ReceiptItem) {
 536
 537             echo "<TR>
 538                         <TD ALIGN=RIGHT>" . number_format($ReceiptItem->Amount,2) . "</TD>
 539                         <TD ALIGN=RIGHT>" . number_format($ReceiptItem->Discount,2) . "</TD>
 540                         <TD>" . $ReceiptItem->CustomerName . "</TD>
 541                         <TD>" . $ReceiptItem->GLCode . "</TD>
 542                         <TD><a href='" . $_SERVER['PHP_SELF'] . '?' . SID . '&Delete=' . $ReceiptItem->ID . "'>" . _('Delete') . '</a></TD>
 543                 </TR>';
 544
 545             $BatchTotal= $BatchTotal + $ReceiptItem->Amount;
 546
 547    }
 548
 549    echo "<TR><TD ALIGN=RIGHT><B>" . number_format($BatchTotal,2) . "</B></TD></TR></TABLE>";
 550
 551
 552 } else {
 553   /*need to enter batch no or select a bank account and bank date*/
 554
 555         echo '<CENTER><FONT SIZE=4><B><U>' . _('Set up a New Batch') . '</B></U></FONT>';
 556         echo "<INPUT TYPE='hidden' name='BatchNo'value='" . $_POST['BatchNo'] . "'>";
 557         echo "<P><TABLE>";
 558
 559         $SQL = "SELECT bankaccountname,
 560                         bankaccounts.accountcode
 561                 FROM bankaccounts,
 562                         chartmaster
 563                 WHERE bankaccounts.accountcode=chartmaster.accountcode";
 564
 565
 566         $ErrMsg = _('The bank accounts could not be retrieved because');
 567         $DbgMsg = _('The SQL used to retrieve the bank acconts was');
 568         $AccountsResults = DB_query($SQL,$db,$ErrMsg,$DbgMsg);
 569
 570         echo '<TR><TD>' . _('Bank Account') . ":</TD><TD><SELECT name='BankAccount'>";
 571
 572         if (DB_num_rows($AccountsResults)==0){
 573                 echo '</SELECT></TD></TR></TABLE><P>';
 574                 prnMsg(_('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'),'info');
 575                 include('includes/footer.inc');
 576                  exit;
 577         } else {
 578                 while ($myrow=DB_fetch_array($AccountsResults)){
 579                       /*list the bank account names */
 580                         if ($_POST['BankAccount']==$myrow['accountcode']){
 581                                 echo "<OPTION SELECTED VALUE='" . $myrow['accountcode'] . "'>" . $myrow['bankaccountname'];
 582                         } else {
 583                                 echo "<OPTION VALUE='" . $myrow['accountcode'] . "'>" . $myrow['bankaccountname'];
 584                         }
 585                 }
 586                 echo "</SELECT></TD></TR>";
 587         }
 588
 589         $_POST['DateBanked'] = Date($_SESSION['DefaultDateFormat']);
 590
 591         echo '<TR><TD>' . _('Date Banked') . ":</TD><TD><INPUT TYPE='text' name='DateBanked' maxlength=10 size=11 value='" . $_POST['DateBanked'] . "'></TD></TR>";
 592         echo '<TR><TD>' . _('Currency') . ":</TD><TD><SELECT name='Currency'>";
 593
 594         if (!isset($_POST['Currency'])){
 595           /* find out what the functional currency of the company is */
 596
 597                 $SQL = "SELECT currencydefault FROM companies WHERE coycode=1";
 598                 $result=DB_query($SQL,$db);
 599                 $myrow=DB_fetch_row($result);
 600                 $_POST['Currency']=$myrow[0];
 601                 unset($result);
 602         }
 603
 604         $SQL = "SELECT currency, currabrev, rate FROM currencies";
 605         $result=DB_query($SQL,$db);
 606         if (DB_num_rows($result)==0){
 607            echo '</SELECT></TD></TR>';
 608            prnMsg(_('No currencies are defined yet') . '. ' . _('Receipts cannot be entered until a currency is defined'),'warn');
 609
 610         } else {
 611                 while ($myrow=DB_fetch_array($result)){
 612                     if ($_POST['Currency']==$myrow['currabrev']){
 613                         echo "<OPTION SELECTED value=" . $myrow['currabrev'] . '>' . $myrow['currency'];
 614                     } else {
 615                         echo "<OPTION value=" . $myrow['currabrev'] . '>' . $myrow['currency'];
 616                     }
 617                 }
 618                 echo '</SELECT></TD></TR>';
 619         }
 620
 621         if (!isset($_POST['ExRate'])){
 622              $_POST['ExRate']=1;
 623         }
 624         echo '<TR><TD>' . _('Exchange Rate') . ":</TD><TD><INPUT TYPE='text' name='ExRate' maxlength=10 size=12 value='" . $_POST['ExRate'] . "'></TD></TR>";
 625         echo '<TR><TD>' . _('Receipt Type') . ":</TD><TD><SELECT name=ReceiptType>";
 626
 627         include('includes/GetPaymentMethods.php');
 628 /* The array ReceiptTypes is defined from the setup tab of the main menu under payment methods - the array is populated from the include file GetPaymentMethods.php */
 629
 630         foreach ($ReceiptTypes as $RcptType) {
 631              if ($_POST['ReceiptType']==$RcptType){
 632                    echo "<OPTION SELECTED Value='$RcptType'>$RcptType";
 633              } else {
 634                    echo "<OPTION Value='$RcptType'>$RcptType";
 635              }
 636         }
 637         echo "</SELECT></TD></TR>";
 638
 639         echo '<TR><TD>' . _('Narrative') . ":</TD><TD><INPUT TYPE='text' name='BatchNarrative' maxlength=50 size=52 value='" . $_POST['BatchNarrative'] . "'></TD></TR>";
 640
 641         echo "</TABLE>";
 642
 643         echo "<CENTER><INPUT TYPE=SUBMIT Name='BatchInput' Value='" . _('Accept') . "'></CENTER>";
 644 }
 645
 646 /*this next block of ifs deals with what information to display for input into the form
 647 the info depends on where the user is up to ie the first stage is to select a bank
 648 account, currency being banked and a batch number - or start a new batch by leaving the batch no blank
 649 and a date for the banking. The second stage is to select a customer or GL account.
 650 Finally enter the amount */
 651
 652
 653 /*if a customer has been selected (and a receipt batch is underway)
 654 then set out the customers account summary */
 655
 656 if (isset($_SESSION['CustomerRecord']) AND isset($_POST['CustomerID']) AND $_POST['CustomerID']!="" AND isset($_SESSION['ReceiptBatch'])){
 657 /*a customer is selected  */
 658
 659         echo "<BR><CENTER><FONT SIZE=4>" . $_SESSION['CustomerRecord']['name'] . ' </FONT></B> - (' . _('All amounts stated in') . ' ' . $_SESSION['CustomerRecord']['currency'] . ')</CENTER><BR><B><FONT COLOR=BLUE>' . _('Terms') . ': ' . $_SESSION['CustomerRecord']['terms'] . "<BR>" . _('Credit Limit') . ": </B></FONT> " . number_format($_SESSION['CustomerRecord']['creditlimit'],0) . '  <B><FONT COLOR=BLUE>' . _('Credit Status') . ':</B></FONT> ' . $_SESSION['CustomerRecord']['reasondescription'];
 660
 661         if ($_SESSION['CustomerRecord']['dissallowinvoices']!=0){
 662            echo '<BR><FONT COLOR=RED SIZE=4><B>' . _('ACCOUNT ON HOLD') . '</FONT></B><BR>';
 663         }
 664
 665         echo "<TABLE WIDTH=100% BORDER=1>
 666                 <TR>
 667                         <TD class='tableheader'>" . _('Total Balance') . "</TD>
 668                         <TD class='tableheader'>" . _('Current') . "</TD>
 669                         <TD class='tableheader'>" . _('Now Due') . "</TD>
 670                         <TD class='tableheader'>" . $_SESSION['PastDueDays1'] . '-' . $_SESSION['PastDueDays2'] . ' ' . _('Days Overdue') . "</TD>
 671                         <TD class='tableheader'>" . _('Over') . ' ' . $_SESSION['PastDueDays2'] . ' ' . _('Days Overdue') . '</TD>
 672                 </TR>';
 673
 674         echo "<TR>
 675                 <TD ALIGN=RIGHT>" . number_format($_SESSION['CustomerRecord']['balance'],2) . "</TD>
 676                 <TD ALIGN=RIGHT>" . number_format(($_SESSION['CustomerRecord']['balance'] - $_SESSION['CustomerRecord']['due']),2) . "</TD>
 677                 <TD ALIGN=RIGHT>" . number_format(($_SESSION['CustomerRecord']['due']-$_SESSION['CustomerRecord']['overdue1']),2) . "</TD>
 678                 <TD ALIGN=RIGHT>" . number_format(($_SESSION['CustomerRecord']['overdue1']-$_SESSION['CustomerRecord']['overdue2']) ,2) . "</TD>
 679                 <TD ALIGN=RIGHT>" . number_format($_SESSION['CustomerRecord']['overdue2'],2) . "</TD>
 680         </TR>
 681         </TABLE>";
 682
 683         echo "<CENTER><TABLE>";
 684
 685         $DisplayDiscountPercent = number_format($_SESSION['CustomerRecord']['pymtdiscount']*100,2) . "%";
 686
 687         echo "<INPUT TYPE='hidden' name='CustomerID' value=" . $_POST['CustomerID'] . ">";
 688         echo "<INPUT TYPE='hidden' name='CustomerName' value='" . $_SESSION['CustomerRecord']['name'] . "'>";
 689
 690 }
 691
 692 if (isset($_POST['GLEntry']) AND isset($_SESSION['ReceiptBatch'])){
 693 /* Set up a heading for the transaction entry for a GL Receipt */
 694
 695         echo '<BR><CENTER><FONT SIZE=4>' . _('General Ledger Receipt Entry') . '</FONT><TABLE>';
 696
 697         /*now set up a GLCode field to select from avaialble GL accounts */
 698         echo '<TR><TD>' . _('GL Account') . ":</TD><TD><SELECT name='GLCode'>";
 699         $SQL = "SELECT accountcode, accountname FROM chartmaster ORDER BY accountcode";
 700         $result=DB_query($SQL,$db);
 701         if (DB_num_rows($result)==0){
 702            echo '</SELECT>' . _('No General ledger accounts have been set up yet') . ' - ' . _('receipts cannot be entered against GL accounts until the GL accounts are set up') . '</TD></TR>';
 703         } else {
 704                 while ($myrow=DB_fetch_array($result)){
 705                     if ($_POST['GLCode']==$myrow["accountcode"]){
 706                         echo "<OPTION SELECTED value=" . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' - ' . $myrow['accountname'];
 707                     } else {
 708                         echo '<OPTION value=' . $myrow['accountcode'] . '>' . $myrow['accountcode'] . ' - ' . $myrow['accountname'];
 709                     }
 710                 }
 711                 echo '</SELECT></TD></TR>';
 712         }
 713
 714 }
 715
 716 /*if either a customer is selected or its a GL Entry then set out
 717 the fields for entry of receipt amt, disc, payee details, narrative */
 718
 719 if (((isset($_SESSION['CustomerRecord'])
 720         AND isset($_POST['CustomerID'])
 721         AND $_POST['CustomerID']!="")
 722                 OR isset($_POST['GLEntry']))
 723                 AND isset($_SESSION['ReceiptBatch'])){
 724
 725         echo '<TR><TD>' . _('Amount of Receipt') . ":</TD>
 726                 <TD><INPUT TYPE='text' name='amount' maxlength=12 size=13 value='" . $_POST['amount'] . "'></TD>
 727         </TR>";
 728
 729         if (!isset($_POST['GLEntry'])){
 730                 echo '<TR><TD>' . _('Amount of Discount') . ":</TD>
 731                         <TD><INPUT TYPE='text' name='discount' maxlength=12 size=13 value='" . $_POST['discount'] . "'> " . _('agreed prompt payment discount is') . ' ' . $DisplayDiscountPercent . '</TD></TR>';
 732         } else {
 733                 echo "<INPUT TYPE='HIDDEN' NAME='discount' Value=0>";
 734         }
 735
 736         echo '<TR><TD>' . _('Payee Bank Details') . ":</TD>
 737                 <TD><INPUT TYPE='text' name='PayeeBankDetail' maxlength=22 size=20 value='" . $_POST['PayeeBankDetail'] . "'></TD></TR>";
 738         echo '<TR><TD>' . _('Narrative') . ":</TD>
 739                 <TD><INPUT TYPE='text' name='Narrative' maxlength=30 size=32 value='" . $_POST['Narrative'] . "'></TD></TR>";
 740         echo "</TABLE>";
 741         echo "<INPUT TYPE=SUBMIT name=Process value='" . _('Accept') . "'><INPUT TYPE=SUBMIT name=Cancel value='" . _('Cancel') . "'>";
 742
 743 } elseif (isset($_SESSION['ReceiptBatch']) && !isset($_POST['GLEntry'])){
 744
 745       /*Show the form to select a customer */
 746         echo '<B>';
 747
 748         echo $msg;
 749         echo '<BR><U>' . _('Select A Customer') . '</U></B>';
 750         echo '<TABLE CELLPADDING=3 COLSPAN=4>';
 751         echo '<TR><TD>' . _('Text in the') . ' ' . '<B>' . _('name') . '</B>:</TD>';
 752         echo "<TD><INPUT TYPE='Text' NAME='Keywords' SIZE=20 MAXLENGTH=25></TD>";
 753         echo '<TD><FONT SIZE=3><B>OR</B></FONT></TD>';
 754         echo '<TD>' . _('Text extract in the customer') . ' ' . '<B>' . _('code') . '</B>:</TD>';
 755         echo "<TD><INPUT TYPE='Text' NAME='CustCode' SIZE=15 MAXLENGTH=18></TD>";
 756         echo '</TR></TABLE>';
 757         echo '<CENTER>';
 758         echo "<INPUT TYPE=SUBMIT NAME='Search' VALUE='" . _('Search Now') . "'>";
 759         echo "<BR><BR><INPUT TYPE=SUBMIT NAME='GLEntry' VALUE='" . _('Enter A GL Receipt') . "'>";
 760
 761         if (count($_SESSION['ReceiptBatch']->Items) > 0){
 762                 echo "<BR><BR><INPUT TYPE=SUBMIT NAME='CommitBatch' VALUE='" . _('Accept and Process Batch') . "'></CENTER>";
 763         }
 764
 765         If ($result) {
 766
 767                 echo '<CENTER><TABLE CELLPADDING=2 COLSPAN=7 BORDER=2>';
 768                 $TableHeader = "<TR><TD class='tableheader'>" . _('Code') . "</TD><TD class='tableheader'>" . _('Customer Name') . '</TD></TR>';
 769                 echo $TableHeader;
 770                 $j = 1;
 771                 $k = 0; //row counter to determine background colour
 772
 773                 while ($myrow=DB_fetch_array($result)) {
 774
 775                         if ($k==1){
 776                                 echo "<tr bgcolor='#CCCCCC'>";
 777                                 $k=0;
 778                         } else {
 779                                 echo "<tr bgcolor='#EEEEEE'>";
 780                                 $k=1;
 781                         }
 782
 783                         printf("<td><FONT SIZE=1><INPUT TYPE=SUBMIT NAME='Select' VALUE='%s'</FONT></td><td>%s</td></tr>", $myrow['debtorno'],$myrow['name']);
 784
 785                         $j++;
 786                         If ($j == 11){
 787                                 $j=1;
 788                                 echo $TableHeader;
 789
 790                         }
 791         //end of page full new headings if
 792                 }
 793         //end of while loop
 794
 795                 echo '</TABLE></CENTER>';
 796
 797         }
 798         //end if results to show
 799 }
 800
 801 echo '</form>';
 802 include('includes/footer.inc');
 803 ?>