Care2007/modules/weberp/PDFDeliveryDifferences.php

   1 <?php
   2
   3 /* $Revision: 1.13 $ */
   4
   5 $PageSecurity = 3;
   6 include ('includes/session.inc');
   7 include('includes/SQL_CommonFunctions.inc');
   8
   9 $InputError=0;
  10
  11 if (isset($_POST['FromDate']) AND !Is_Date($_POST['FromDate'])){
  12         $msg = _('The date from must be specified in the format') . ' ' . $_SESSION['DefaultDateFormat'];
  13         $InputError=1;
  14 }
  15 if (isset($_POST['ToDate']) AND !Is_Date($_POST['ToDate'])){
  16         $msg =  _('The date to must be specified in the format') . ' ' .  $_SESSION['DefaultDateFormat'];
  17         $InputError=1;
  18 }
  19
  20 if (!isset($_POST['FromDate']) OR !isset($_POST['ToDate']) OR $InputError==1){
  21
  22      $title = _('Delivery Differences Report');
  23      include ('includes/header.inc');
  24
  25      echo "<FORM METHOD='post' action='" . $_SERVER['PHP_SELF'] . '?' . SID . "'>";
  26      echo '<CENTER><TABLE><TR><TD>' . _('Enter the date from which variances between orders and deliveries are to be listed') . ":</TD><TD><INPUT TYPE=text NAME='FromDate' MAXLENGTH=10 SIZE=10 VALUE='" . Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m')-1,0,Date('y'))) . "'></TD></TR>";
  27      echo '<TR><TD>' . _('Enter the date to which variances between orders and deliveries are to be listed') . ":</TD><TD><INPUT TYPE=text NAME='ToDate' MAXLENGTH=10 SIZE=10 VALUE='" . Date($_SESSION['DefaultDateFormat']) . "'></TD></TR>";
  28      echo '<TR><TD>' . _('Inventory Category') . '</TD><TD>';
  29
  30      $sql = "SELECT categorydescription, categoryid FROM stockcategory WHERE stocktype<>'D' AND stocktype<>'L'";
  31      $result = DB_query($sql,$db);
  32
  33
  34      echo "<SELECT NAME='CategoryID'>";
  35      echo "<OPTION SELECTED VALUE='All'>" . _('Over All Categories');
  36
  37      while ($myrow=DB_fetch_array($result)){
  38         echo "<OPTION VALUE='" . $myrow['categoryid'] . "'>" . $myrow['categorydescription'];
  39      }
  40
  41
  42      echo '</SELECT></TD></TR>';
  43
  44      echo '<TR><TD>' . _('Inventory Location') . ":</TD><TD><SELECT NAME='Location'>";
  45      echo "<OPTION SELECTED VALUE='All'>" . _('All Locations');
  46
  47      $result= DB_query('SELECT loccode, locationname FROM locations',$db);
  48      while ($myrow=DB_fetch_array($result)){
  49         echo "<OPTION VALUE='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
  50      }
  51      echo '</SELECT></TD></TR>';
  52
  53      echo '<TR><TD>' . _('Email the report off') . ":</TD><TD><SELECT NAME='Email'>";
  54      echo "<OPTION SELECTED VALUE='No'>" . _('No');
  55      echo "<OPTION VALUE='Yes'>" . _('Yes');
  56      echo "</SELECT></TD></TR></TABLE><INPUT TYPE=SUBMIT NAME='Go' VALUE='" . _('Create PDF') . "'></CENTER>";
  57
  58      if ($InputError==1){
  59         prnMsg($msg,'error');
  60      }
  61      include('includes/footer.inc');
  62      exit;
  63 } else {
  64         include('includes/ConnectDB.inc');
  65 }
  66
  67 if ($_POST['CategoryID']=='All' AND $_POST['Location']=='All'){
  68         $sql= "SELECT invoiceno,
  69                         orderdeliverydifferenceslog.orderno,
  70                         orderdeliverydifferenceslog.stockid,
  71                         stockmaster.description,
  72                         quantitydiff,
  73                         trandate,
  74                         orderdeliverydifferenceslog.debtorno,
  75                         orderdeliverydifferenceslog.branch
  76                 FROM orderdeliverydifferenceslog INNER JOIN stockmaster
  77                         ON orderdeliverydifferenceslog.stockid=stockmaster.stockid
  78                 INNER JOIN debtortrans ON orderdeliverydifferenceslog.invoiceno=debtortrans.transno
  79                         AND debtortrans.type=10
  80                         AND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'
  81                         AND trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
  82
  83 } elseif ($_POST['CategoryID']!='All' AND $_POST['Location']=='All') {
  84         $sql= "SELECT invoiceno,
  85                         orderdeliverydifferenceslog.orderno,
  86                         orderdeliverydifferenceslog.stockid,
  87                         stockmaster.description,
  88                         quantitydiff,
  89                         trandate,
  90                         orderdeliverydifferenceslog.debtorno,
  91                         orderdeliverydifferenceslog.branch
  92                 FROM orderdeliverydifferenceslog INNER JOIN stockmaster
  93                         ON orderdeliverydifferenceslog.stockid=stockmaster.stockid
  94                         INNER JOIN debtortrans ON orderdeliverydifferenceslog.invoiceno=debtortrans.transno
  95                         AND debtortrans.type=10
  96                         AND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'
  97                         AND trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'
  98                         AND categoryid='" . $_POST['CategoryID'] ."'";
  99
 100 } elseif ($_POST['CategoryID']=='All' AND $_POST['Location']!='All') {
 101         $sql = "SELECT invoiceno,
 102                         orderdeliverydifferenceslog.orderno,
 103                         orderdeliverydifferenceslog.stockid,
 104                         stockmaster.description,
 105                         quantitydiff,
 106                         trandate,
 107                         orderdeliverydifferenceslog.debtorno,
 108                         orderdeliverydifferenceslog.branch
 109                 FROM orderdeliverydifferenceslog INNER JOIN stockmaster
 110                         ON orderdeliverydifferenceslog.stockid=stockmaster.stockid
 111                         INNER JOIN debtortrans
 112                                 ON orderdeliverydifferenceslog.invoiceno=debtortrans.transno
 113                                 INNER JOIN salesorders
 114                                         ON orderdeliverydifferenceslog.orderno=salesorders.orderno
 115                 WHERE debtortrans.type=10
 116                 AND salesorders.fromstkloc='". $_POST['Location'] . "'
 117                 AND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 118                 AND trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 119
 120 } elseif ($_POST['CategoryID']!='All' AND $_POST['location']!='All'){
 121
 122         $sql = "SELECT invoiceno,
 123                         orderdeliverydifferenceslog.orderno,
 124                         orderdeliverydifferenceslog.stockid,
 125                         stockmaster.description,
 126                         quantitydiff,
 127                         trandate,
 128                         orderdeliverydifferenceslog.debtorno,
 129                         orderdeliverydifferenceslog.branch
 130                 FROM orderdeliverydifferenceslog INNER JOIN stockmaster
 131                         ON orderdeliverydifferenceslog.stockid=stockmaster.stockid
 132                         INNER JOIN debtortrans
 133                                 ON orderdeliverydifferenceslog.invoiceno=debtortrans.transno
 134                                 AND debtortrans.type=10
 135                                 INNER JOIN salesorders
 136                                         ON orderdeliverydifferenceslog.orderno = salesorders.orderno
 137                 WHERE salesorders.fromstkloc='" . $_POST['Location'] . "'
 138                 AND categoryid='" . $_POST['CategoryID'] . "'
 139                 AND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 140                 AND trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'";
 141 }
 142
 143 $Result=DB_query($sql,$db,'','',false,false); //dont error check - see below
 144
 145 if (DB_error_no($db)!=0){
 146         $title = _('Delivery Differences Log Report Error');
 147         include('includes/header.inc');
 148         prnMsg( _('An error occurred getting the variances between deliveries and orders'),'error');
 149         if ($debug==1){
 150                 prnMsg( _('The SQL used to get the variances between deliveries and orders that failed was') . "<BR>$SQL",'error');
 151         }
 152         include ('includes/footer.inc');
 153         exit;
 154 } elseif (DB_num_rows($Result)==0){
 155         $title = _('Delivery Differences Log Report Error');
 156         include('includes/header.inc');
 157         prnMsg( _('There were no variances between deliveries and orders found in the database within the period from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate'] . '. ' . _('Please try again selecting a different date range'),'info');
 158         if ($debug==1) {
 159                 prnMsg( _('The SQL that returned no rows was') . '<BR>' . $sql,'error');
 160         }
 161         include('includes/footer.inc');
 162         exit;
 163 }
 164
 165 include('includes/PDFStarter.php');
 166
 167 /*PDFStarter.php has all the variables for page size and width set up depending on the users default preferences for paper size */
 168
 169 $pdf->addinfo('Title',_('Variances Between Deliveries and Orders'));
 170 $pdf->addinfo('Subject',_('Variances Between Deliveries and Orders from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate']);
 171
 172 $line_height=12;
 173 $PageNumber = 1;
 174
 175 $TotalDiffs = 0;
 176
 177 include ('includes/PDFDeliveryDifferencesPageHeader.inc');
 178
 179 while ($myrow=DB_fetch_array($Result)){
 180
 181       $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,40,$FontSize,$myrow['invoiceno'], 'left');
 182       $LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,40,$FontSize,$myrow['orderno'], 'left');
 183       $LeftOvers = $pdf->addTextWrap($Left_Margin+80,$YPos,200,$FontSize,$myrow['stockid'] . ' - ' . $myrow['description'], 'left');
 184
 185       $LeftOvers = $pdf->addTextWrap($Left_Margin+280,$YPos,50,$FontSize,number_format($myrow['quantitydiff']), 'right');
 186       $LeftOvers = $pdf->addTextWrap($Left_Margin+335,$YPos,50,$FontSize,$myrow['debtorno'], 'left');
 187       $LeftOvers = $pdf->addTextWrap($Left_Margin+385,$YPos,50,$FontSize,$myrow['branch'], 'left');
 188       $LeftOvers = $pdf->addTextWrap($Left_Margin+435,$YPos,50,$FontSize,ConvertSQLDate($myrow['trandate']), 'left');
 189
 190       $YPos -= ($line_height);
 191       $TotalDiffs++;
 192
 193       if ($YPos - (2 *$line_height) < $Bottom_Margin){
 194           /*Then set up a new page */
 195               $PageNumber++;
 196               include ('includes/PDFDeliveryDifferencesPageHeader.inc');
 197       } /*end of new page header  */
 198 } /* end of while there are delivery differences to print */
 199
 200
 201 $YPos-=$line_height;
 202 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,200,$FontSize,_('Total number of differences') . ' ' . number_format($TotalDiffs), 'left');
 203
 204 if ($_POST['CategoryID']=='All' AND $_POST['Location']=='All'){
 205         $sql = "SELECT COUNT(salesorderdetails.orderno)
 206                         FROM salesorderdetails INNER JOIN debtortrans
 207                                 ON salesorderdetails.orderno=debtortrans.order_
 208                         WHERE debtortrans.trandate>='" . FormatDateForSQL($_POST['FromDate']) . "'
 209                         AND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 210
 211 } elseif ($_POST['CategoryID']!='All' AND $_POST['Location']=='All') {
 212         $sql = "SELECT COUNT(salesorderdetails.orderno)
 213                 FROM salesorderdetails INNER JOIN debtortrans
 214                         ON salesorderdetails.orderno=debtortrans.order_ INNER JOIN stockmaster
 215                         ON salesorderdetails.stkcode=stockmaster.stockid
 216                 WHERE debtortrans.trandate>='" . FormatDateForSQL($_POST['FromDate']) . "'
 217                 AND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'
 218                 AND stockmaster.categoryid='" . $_POST['CategoryID'] . "'";
 219
 220 } elseif ($_POST['CategoryID']=='All' AND $_POST['Location']!='All'){
 221
 222         $sql = "SELECT COUNT(salesorderdetails.orderno)
 223                 FROM salesorderdetails INNER JOIN debtortrans
 224                         ON salesorderdetails.orderno=debtortrans.order_ INNER JOIN salesorders
 225                         ON salesorderdetails.orderno = salesorders.orderno
 226                 WHERE debtortrans.trandate>='". FormatDateForSQL($_POST['FromDate']) . "'
 227                 AND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'
 228                 AND salesorders.fromstkloc='" . $_POST['Location'] . "'";
 229
 230 } elseif ($_POST['CategoryID'] !='All' AND $_POST['Location'] !='All'){
 231
 232         $sql = "SELECT COUNT(salesorderdetails.orderno)
 233                 FROM salesorderdetails INNER JOIN debtortrans ON salesorderdetails.orderno=debtortrans.order_
 234                         INNER JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno
 235                         INNER JOIN stockmaster ON salesorderdetails.stkcode = stockmaster.stockid
 236                 WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
 237                 AND categoryid='" . $_POST['CategoryID'] . "'
 238                 AND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 239                 AND trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'";
 240
 241 }
 242 $Errmsg = _('Could not retrieve the count of sales order lines in the period under review');
 243 $result = DB_query($sql,$db,$ErrMsg);
 244
 245
 246 $myrow=DB_fetch_row($result);
 247 $YPos-=$line_height;
 248 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,200,$FontSize,_('Total number of order lines') . ' ' . number_format($myrow[0]), 'left');
 249
 250 $YPos-=$line_height;
 251 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,200,$FontSize,_('DIFOT') . ' ' . number_format((1-($TotalDiffs/$myrow[0])) * 100,2) . '%', 'left');
 252
 253
 254 $pdfcode = $pdf->output();
 255 $len = strlen($pdfcode);
 256 header('Content-type: application/pdf');
 257 header('Content-Length: ' . $len);
 258 header('Content-Disposition: inline; filename=DeliveryDifferences.pdf');
 259 header('Expires: 0');
 260 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 261 header('Pragma: public');
 262
 263 $pdf->stream();
 264
 265 if ($_POST['Email']=='Yes'){
 266         if (file_exists($_SESSION['reports_dir'] . '/DeliveryDifferences.pdf')){
 267                 unlink($_SESSION['reports_dir'] . '/DeliveryDifferences.pdf');
 268         }
 269         $fp = fopen( $_SESSION['reports_dir'] . '/DeliveryDifferences.pdf','wb');
 270         fwrite ($fp, $pdfcode);
 271         fclose ($fp);
 272
 273         include('includes/htmlMimeMail.php');
 274
 275         $mail = new htmlMimeMail();
 276         $attachment = $mail->getFile($_SESSION['reports_dir'] . '/DeliveryDifferences.pdf');
 277         $mail->setText(_('Please find herewith delivery differences report from') . ' ' . $_POST['FromDate'] .  ' '. _('to') . ' ' . $_POST['ToDate']);
 278         $mail->addAttachment($attachment, 'DeliveryDifferences.pdf', 'application/pdf');
 279         $mail->setFrom($_SESSION['CompanyRecord']['coyname'] . '<' . $_SESSION['CompanyRecord']['email'] .'>');
 280
 281         /* $DelDiffsRecipients defined in config.php */
 282         $result = $mail->send($DelDiffsRecipients);
 283 }
 284
 285 ?>