Care2007/modules/weberp/SupplierBalsAtPeriodEnd.php

   1 <?php
   2
   3 $PageSecurity = 2;
   4 include('includes/session.inc');
   5
   6
   7 /* $Revision: 1.12 $ */
   8
   9 If (isset($_POST['PrintPDF'])
  10         AND isset($_POST['FromCriteria'])
  11         AND strlen($_POST['FromCriteria'])>=1
  12         AND isset($_POST['ToCriteria'])
  13         AND strlen($_POST['ToCriteria'])>=1){
  14
  15         include('includes/PDFStarter.php');
  16
  17         $FontSize=12;
  18         $pdf->addinfo('Title',_('Supplier Balance Listing'));
  19         $pdf->addinfo('Subject',_('Supplier Balances'));
  20
  21         $PageNumber=0;
  22         $line_height=12;
  23
  24
  25       /*Now figure out the aged analysis for the Supplier range under review */
  26
  27         $SQL = "SELECT suppliers.supplierid,
  28                         suppliers.suppname,
  29                         currencies.currency,
  30                         SUM((supptrans.ovamount + supptrans.ovgst - supptrans.alloc)/supptrans.rate) AS balance,
  31                         SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS fxbalance,
  32                         SUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN
  33         (supptrans.ovamount + supptrans.ovgst)/supptrans.rate ELSE 0 END)
  34          AS afterdatetrans,
  35                 Sum(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "'
  36                                 AND (supptrans.type=22 OR supptrans.type=21) THEN
  37                supptrans.diffonexch ELSE 0 END)
  38          AS afterdatediffonexch,
  39                         Sum(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN
  40         supptrans.ovamount + supptrans.ovgst ELSE 0 END
  41         ) AS fxafterdatetrans
  42         FROM suppliers,
  43                 currencies,
  44                 supptrans
  45         WHERE suppliers.currcode = currencies.currabrev
  46                 AND suppliers.supplierid = supptrans.supplierno
  47                 AND suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
  48                 AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
  49         GROUP BY suppliers.supplierid,
  50                 suppliers.suppname,
  51                 currencies.currency";
  52
  53         $SupplierResult = DB_query($SQL,$db);
  54
  55         if (DB_error_no($db) !=0) {
  56                 $title = _('Supplier Balances') . ' - ' . _('Problem Report');
  57                 include('includes/header.inc');
  58                 prnMsg(_('The Supplier details could not be retrieved by the SQL because') . ' ' . DB_error_msg($db),'error');
  59                 echo "<BR><A HREF='$rootpath/index.php?" . SID . "'>" . _('Back to the menu') . '</A>';
  60                 if ($debug==1){
  61                         echo "<BR>$SQL";
  62                 }
  63                 include('includes/footer.inc');
  64                 exit;
  65         }
  66
  67         include ('includes/PDFSupplierBalsPageHeader.inc');
  68
  69         $TotBal=0;
  70
  71         While ($SupplierBalances = DB_fetch_array($SupplierResult,$db)){
  72
  73                 $Balance = $SupplierBalances['balance'] - $SupplierBalances['afterdatetrans'] + $SupplierBalances['afterdatediffonexch'];
  74                 $FXBalance = $SupplierBalances['fxbalance'] - $SupplierBalances['fxafterdatetrans'];
  75
  76                 if (ABS($Balance)>0.009 OR ABS($FXBalance)>0.009) {
  77
  78                         $DisplayBalance = number_format($SupplierBalances['balance'] - $SupplierBalances['afterdatetrans'],2);
  79                         $DisplayFXBalance = number_format($SupplierBalances['fxbalance'] - $SupplierBalances['fxafterdatetrans'],2);
  80
  81                         $TotBal += $Balance;
  82
  83                         $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,220-$Left_Margin,$FontSize,$SupplierBalances['supplierid'] . ' - ' . $SupplierBalances['suppname'],'left');
  84                         $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right');
  85                         $LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayFXBalance,'right');
  86                         $LeftOvers = $pdf->addTextWrap(350,$YPos,100,$FontSize,$SupplierBalances['currency'],'left');
  87
  88
  89                         $YPos -=$line_height;
  90                         if ($YPos < $Bottom_Margin + $line_height){
  91                         include('includes/PDFSupplierBalsPageHeader.inc');
  92                         }
  93                 }
  94         } /*end Supplier aged analysis while loop */
  95
  96         $YPos -=$line_height;
  97         if ($YPos < $Bottom_Margin + (2*$line_height)){
  98                 $PageNumber++;
  99                 include('includes/PDFSupplierBalsPageHeader.inc');
 100         }
 101
 102         $DisplayTotBalance = number_format($TotBal,2);
 103
 104         $LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayTotBalance,'right');
 105
 106         $buf = $pdf->output();
 107         $len = strlen($buf);
 108
 109         header('Content-type: application/pdf');
 110         header('Content-Length: ' . $len);
 111         header('Content-Disposition: inline; filename=SupplierBals.pdf');
 112         header('Expires: 0');
 113         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 114         header('Pragma: public');
 115
 116         $pdf->stream();
 117
 118 } else { /*The option to print PDF was not hit */
 119
 120         $title=_('Creditor Balances At A Period End');
 121         include('includes/header.inc');
 122
 123         if (strlen($_POST['FromCriteria'])<1 || strlen($_POST['ToCriteria'])<1) {
 124
 125         /*if $FromCriteria is not set then show a form to allow input   */
 126
 127                 echo '<FORM ACTION=' . $_SERVER['PHP_SELF'] . " METHOD='POST'><CENTER><TABLE>";
 128
 129                 echo '<TR><TD>' . _('From Supplier Code') . ":</FONT></TD>
 130                         <TD><input Type=text maxlength=6 size=7 name=FromCriteria value='1'></TD></TR>";
 131                 echo '<TR><TD>' . _('To Supplier Code') . ":</TD>
 132                         <TD><input Type=text maxlength=6 size=7 name=ToCriteria value='zzzzzz'></TD></TR>";
 133
 134                 echo '<TR><TD>' . _('Balances As At') . ":</TD>
 135                         <TD><SELECT Name='PeriodEnd'>";
 136
 137                 $sql = 'SELECT periodno, lastdate_in_period FROM periods';
 138
 139                 $ErrMsg = _('Could not retrieve period data because');
 140                 $Periods = DB_query($sql,$db,$ErrMsg);
 141
 142                 while ($myrow = DB_fetch_array($Periods,$db)){
 143
 144                         echo '<OPTION VALUE=' . $myrow['lastdate_in_period'] . '>' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']);
 145
 146                 }
 147         }
 148
 149         echo '</SELECT></TD></TR>';
 150
 151
 152         echo "</TABLE><INPUT TYPE=Submit Name='PrintPDF' Value='" . _('Print PDF') . "'></CENTER>";
 153
 154         include('includes/footer.inc');
 155 } /*end of else not PrintPDF */
 156
 157 ?>