Care2007/modules/weberp/AgedSuppliers.php

   1 <?php
   2 /* $Revision: 1.9 $ */
   3
   4 $PageSecurity = 2;
   5 include('includes/session.inc');
   6
   7 If (isset($_POST['PrintPDF'])
   8         AND isset($_POST['FromCriteria'])
   9         AND strlen($_POST['FromCriteria'])>=1
  10         AND isset($_POST['ToCriteria'])
  11         AND strlen($_POST['ToCriteria'])>=1){
  12
  13
  14         include('includes/PDFStarter.php');
  15
  16         $FontSize=12;
  17         $pdf->addinfo('Title',_('Aged Supplier Listing'));
  18         $pdf->addinfo('Subject',_('Aged Suppliers'));
  19
  20         $PageNumber=0;
  21         $line_height=12;
  22
  23       /*Now figure out the aged analysis for the Supplier range under review */
  24
  25         if ($_POST['All_Or_Overdues']=='All'){
  26                 $SQL = "SELECT suppliers.supplierid, suppliers.suppname, currencies.currency, paymentterms.terms,
  27         SUM(supptrans.ovamount + supptrans.ovgst  - supptrans.alloc) as balance,
  28         SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  29                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue  THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  30         ELSE
  31                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  32         END) AS due,
  33         Sum(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  34                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  35         ELSE
  36                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') ."), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  37         END) AS overdue1,
  38         Sum(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  39                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue     AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  40         ELSE
  41                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  42         END) AS overdue2
  43         FROM suppliers, paymentterms, currencies, supptrans WHERE suppliers.paymentterms = paymentterms.termsindicator
  44         AND suppliers.currcode = currencies.currabrev
  45         AND suppliers.supplierid = supptrans.supplierno
  46         AND suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
  47         AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
  48         AND  suppliers.currcode ='" . $_POST['Currency'] . "'
  49         GROUP BY suppliers.supplierid,
  50                 suppliers.suppname,
  51                 currencies.currency,
  52                 paymentterms.terms,
  53                 paymentterms.daysbeforedue,
  54                 paymentterms.dayinfollowingmonth
  55         HAVING Sum(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) <>0";
  56
  57         } else {
  58
  59               $SQL = "SELECT suppliers.supplierid,
  60                         suppliers.suppname,
  61                         currencies.currency,
  62                         paymentterms.terms,
  63                         SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,
  64                         SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  65                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue  THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  66                         ELSE
  67                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  68                         END) AS due,
  69                         Sum(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  70                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  71                         ELSE
  72                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  73                         END) AS overdue1,
  74                         SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
  75                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue     AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  76                         ELSE
  77                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
  78                         END) AS overdue2
  79                         FROM suppliers,
  80                                 paymentterms,
  81                                 currencies,
  82                                 supptrans
  83                         WHERE suppliers.paymentterms = paymentterms.termsindicator
  84                         AND suppliers.currcode = currencies.currabrev
  85                         and suppliers.supplierid = supptrans.supplierno
  86                         AND suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
  87                         AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
  88                         AND suppliers.currcode ='" . $_POST['Currency'] . "'
  89                         GROUP BY suppliers.supplierid,
  90                                 suppliers.suppname,
  91                                 currencies.currency,
  92                                 paymentterms.terms,
  93                                 paymentterms.daysbeforedue,
  94                                 paymentterms.dayinfollowingmonth
  95                         HAVING Sum(IF (paymentterms.daysbeforedue > 0,
  96                         CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END,
  97                         CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END)) > 0";
  98
  99         }
 100
 101         $SupplierResult = DB_query($SQL,$db,'','',False,False); /*dont trap errors */
 102
 103         if (DB_error_no($db) !=0) {
 104           $title = _('Aged Supplier Account Analysis') . ' - ' . _('Problem Report') ;
 105           include("includes/header.inc");
 106           prnMsg(_('The Supplier details could not be retrieved by the SQL because') .  ' ' . DB_error_msg($db),'error');
 107            echo "<BR><A HREF='$rootpath/index.php?" . SID . "'>" . _('Back to the menu') . '</A>';
 108            if ($debug==1){
 109                 echo "<BR>$SQL";
 110            }
 111            include('includes/footer.inc');
 112            exit;
 113         }
 114
 115         include ('includes/PDFAgedSuppliersPageHeader.inc');
 116         $TotBal = 0;
 117         $TotDue = 0;
 118         $TotCurr = 0;
 119         $TotOD1 = 0;
 120         $TotOD2 = 0;
 121
 122         While ($AgedAnalysis = DB_fetch_array($SupplierResult,$db)){
 123
 124                 $DisplayDue = number_format($AgedAnalysis['due']-$AgedAnalysis['overdue1'],2);
 125                 $DisplayCurrent = number_format($AgedAnalysis['balance']-$AgedAnalysis['due'],2);
 126                 $DisplayBalance = number_format($AgedAnalysis['balance'],2);
 127                 $DisplayOverdue1 = number_format($AgedAnalysis['overdue1']-$AgedAnalysis['overdue2'],2);
 128                 $DisplayOverdue2 = number_format($AgedAnalysis['overdue2'],2);
 129
 130                 $TotBal += $AgedAnalysis['balance'];
 131                 $TotDue += ($AgedAnalysis['due']-$AgedAnalysis['overdue1']);
 132                 $TotCurr += ($AgedAnalysis['balance']-$AgedAnalysis['due']);
 133                 $TotOD1 += ($AgedAnalysis['overdue1']-$AgedAnalysis['overdue2']);
 134                 $TotOD2 += $AgedAnalysis['overdue2'];
 135
 136                 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,220-$Left_Margin,$FontSize,$AgedAnalysis['supplierid'] . ' - ' . $AgedAnalysis['suppname'],'left');
 137                 $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right');
 138                 $LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayCurrent,'right');
 139                 $LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayDue,'right');
 140                 $LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayOverdue1,'right');
 141                 $LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayOverdue2,'right');
 142
 143                 $YPos -=$line_height;
 144                 if ($YPos < $Bottom_Margin + $line_height){
 145                       include('includes/PDFAgedSuppliersPageHeader.inc');
 146                 }
 147
 148                 if ($_POST['DetailedReport']=='Yes'){
 149
 150                    $FontSize=6;
 151                    /*draw a line under the Supplier aged analysis*/
 152                    $pdf->line($Page_Width-$Right_Margin, $YPos+10,$Left_Margin, $YPos+10);
 153
 154                    $sql = "SELECT systypes.typename, supptrans.suppreference, supptrans.trandate,
 155                            (supptrans.ovamount + supptrans.ovgst - supptrans.alloc) as balance,
 156                            CASE WHEN paymentterms.daysbeforedue > 0 THEN
 157                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue  THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 158                            ELSE
 159                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 160                            END AS due,
 161                            CASE WHEN paymentterms.daysbeforedue > 0 THEN
 162                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue        AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 163                            ELSE
 164                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1','MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 165                            END AS overdue1,
 166                            CASE WHEN paymentterms.daysbeforedue > 0 THEN
 167                                 CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 168                            ELSE
 169                                 CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1','MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
 170                            END AS overdue2
 171                            FROM suppliers,
 172                                 paymentterms,
 173                                 supptrans,
 174                                 systypes
 175                            WHERE systypes.typeid = supptrans.type
 176                            AND suppliers.paymentterms = paymentterms.termsindicator
 177                            AND suppliers.supplierid = supptrans.supplierno
 178                            AND ABS(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) >0.009
 179                            AND supptrans.settled = 0
 180                            AND supptrans.supplierno = '" . $AgedAnalysis["supplierid"] . "'";
 181
 182                     $DetailResult = DB_query($sql,$db,'','',False,False); /*dont trap errors - trapped below*/
 183                     if (DB_error_no($db) !=0) {
 184                         $title = _('Aged Supplier Account Analysis - Problem Report');
 185                         include('includes/header.inc');
 186                         echo '<BR>' . _('The details of outstanding transactions for Supplier') . ' - ' . $AgedAnalysis['supplierid'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg($db);
 187                         echo "<BR><A HREF='$rootpath/index.php'>" . _('Back to the menu') . '</A>';
 188                         if ($debug==1){
 189                            echo '<BR>' . _('The SQL that failed was') . '<BR>' . $sql;
 190                         }
 191                         include('includes/footer.inc');
 192                         exit;
 193                     }
 194
 195                     while ($DetailTrans = DB_fetch_array($DetailResult)){
 196
 197                             $LeftOvers = $pdf->addTextWrap($Left_Margin+5,$YPos,60,$FontSize,$DetailTrans['typename'],'left');
 198                             $LeftOvers = $pdf->addTextWrap($Left_Margin+65,$YPos,50,$FontSize,$DetailTrans['suppreference'],'left');
 199                             $DisplayTranDate = ConvertSQLDate($DetailTrans['trandate']);
 200                             $LeftOvers = $pdf->addTextWrap($Left_Margin+105,$YPos,70,$FontSize,$DisplayTranDate,'left');
 201
 202                             $DisplayDue = number_format($DetailTrans['due']-$DetailTrans['overdue1'],2);
 203                             $DisplayCurrent = number_format($DetailTrans['balance']-$DetailTrans['due'],2);
 204                             $DisplayBalance = number_format($DetailTrans['balance'],2);
 205                             $DisplayOverdue1 = number_format($DetailTrans['overdue1']-$DetailTrans['overdue2'],2);
 206                             $DisplayOverdue2 = number_format($DetailTrans['overdue2'],2);
 207
 208                             $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right');
 209                             $LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayCurrent,'right');
 210                             $LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayDue,'right');
 211                             $LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayOverdue1,'right');
 212                             $LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayOverdue2,'right');
 213
 214                             $YPos -=$line_height;
 215                             if ($YPos < $Bottom_Margin + $line_height){
 216                                 $PageNumber++;
 217                                 include('includes/PDFAgedSuppliersPageHeader.inc');
 218                                 $FontSize=6;
 219                             }
 220                     } /*end while there are detail transactions to show */
 221                     /*draw a line under the detailed transactions before the next Supplier aged analysis*/
 222                    $pdf->line($Page_Width-$Right_Margin, $YPos+10,$Left_Margin, $YPos+10);
 223                    $FontSize=8;
 224                 } /*Its a detailed report */
 225         } /*end Supplier aged analysis while loop */
 226
 227         $YPos -=$line_height;
 228         if ($YPos < $Bottom_Margin + (2*$line_height)){
 229                 $PageNumber++;
 230                 include('includes/PDFAgedSuppliersPageHeader.inc');
 231         } elseif ($_POST['DetailedReport']=='Yes') {
 232                 //dont do a line if the totals have to go on a new page
 233                 $pdf->line($Page_Width-$Right_Margin, $YPos+10 ,220, $YPos+10);
 234         }
 235
 236         $DisplayTotBalance = number_format($TotBal,2);
 237         $DisplayTotDue = number_format($TotDue,2);
 238         $DisplayTotCurrent = number_format($TotCurr,2);
 239         $DisplayTotOverdue1 = number_format($TotOD1,2);
 240         $DisplayTotOverdue2 = number_format($TotOD2,2);
 241
 242         $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayTotBalance,'right');
 243         $LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayTotCurrent,'right');
 244         $LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayTotDue,'right');
 245         $LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayTotOverdue1,'right');
 246         $LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayTotOverdue2,'right');
 247
 248         $YPos -=$line_height;
 249         $pdf->line($Page_Width-$Right_Margin, $YPos ,220, $YPos);
 250
 251         $buf = $pdf->output();
 252         $len = strlen($buf);
 253         header('Content-type: application/pdf');
 254         header("Content-Length: $len");
 255         header('Content-Disposition: inline; filename=AgedSuppliers.pdf');
 256         header('Expires: 0');
 257         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 258         header('Pragma: public');
 259
 260         $pdf->stream();
 261
 262 } else { /*The option to print PDF was not hit */
 263
 264         $title = _('Aged Supplier Analysis');
 265         include('includes/header.inc');
 266
 267         if (strlen($_POST['FromCriteria'])<1 OR strlen($_POST['ToCriteria'])<1) {
 268
 269         /*if $FromCriteria is not set then show a form to allow input   */
 270
 271                 echo "<FORM ACTION='" . $_SERVER['PHP_SELF'] . '?' . SID . "' METHOD='POST'><CENTER><TABLE>";
 272
 273                 echo '<TR><TD>' . _('From Supplier Code') . ":</FONT></TD>
 274                         <TD><input Type=text maxlength=6 size=7 name=FromCriteria value='1'></TD>
 275                 </TR>";
 276                 echo '<TR><TD>' . _('To Supplier Code') . ":</TD>
 277                         <TD><input Type=text maxlength=6 size=7 name=ToCriteria value='zzzzzz'></TD>
 278                 </TR>";
 279
 280                 echo '<TR><TD>' . _('All balances or overdues only') . ':' . "</TD>
 281                         <TD><SELECT name='All_Or_Overdues'>";
 282                 echo "<OPTION SELECTED Value='All'>" . _('All suppliers with balances');
 283                 echo "<OPTION Value='OverduesOnly'>" . _('Overdue accounts only');
 284                 echo '</SELECT></TD></TR>';
 285
 286                 echo '<TR><TD>' . _('For suppliers trading in') . ':' . "</TD>
 287                         <TD><SELECT name='Currency'>";
 288
 289                 $sql = 'SELECT currency, currabrev FROM currencies';
 290                 $result=DB_query($sql,$db);
 291
 292                 while ($myrow=DB_fetch_array($result)){
 293                       if ($myrow['currabrev'] == $_SESSION['CompanyRecord']['currencydefault']){
 294                                 echo "<OPTION SELECTED Value='" . $myrow["currabrev"] . "'>" . $myrow['currency'];
 295                       } else {
 296                               echo "<OPTION Value='" . $myrow['currabrev'] . "'>" . $myrow['currency'];
 297                       }
 298                 }
 299                 echo '</SELECT></TD></TR>';
 300
 301                 echo '<TR><TD>' . _('Summary or Detailed Report') . ':' . "</TD>
 302                         <TD><SELECT name='DetailedReport'>";
 303                 echo "<OPTION SELECTED Value='No'>" . _('Summary Report');
 304                 echo "<OPTION Value='Yes'>" . _('Detailed Report');
 305                 echo '</SELECT></TD></TR>';
 306
 307                 echo "</TABLE><INPUT TYPE=Submit Name='PrintPDF' Value='" . _('Print PDF') . "'></CENTER>";
 308         }
 309         include('includes/footer.inc');
 310 } /*end of else not PrintPDF */
 311
 312 ?>