webERP PHP Cross Reference Customer Relationship Management

Source: /PDFOrdersInvoiced.php - 416 lines - 16988 bytes - Summary - Text - Print

   1  <?php
   2  
   3  /* $Id: PDFOrdersInvoiced.php 6458 2013-11-30 11:54:03Z exsonqu $*/
   4  
   5  include  ('includes/session.inc');
   6  $Title = _('Orders Invoiced Report');
   7  
   8  $InputError=0;
   9  
  10  if (isset($_POST['FromDate']) AND !Is_date($_POST['FromDate'])){
  11      $msg = _('The date from must be specified in the format') . ' ' . $DefaultDateFormat;
  12      $InputError=1;
  13      unset($_POST['FromDate']);
  14  }
  15  if (isset($_POST['ToDate']) AND !Is_date($_POST['ToDate'])){
  16      $msg = _('The date to must be specified in the format') . ' ' . $DefaultDateFormat;
  17      $InputError=1;
  18      unset($_POST['ToDate']);
  19  }
  20  if (isset($_POST['FromDate']) and isset($_POST['ToDate']) and Date1GreaterThanDate2($_POST['FromDate'], $_POST['ToDate'])){
  21      $msg = _('The date to must be after the date from');
  22      $InputError=1;
  23      unset($_POST['ToDate']);
  24      unset($_POST['FromoDate']);
  25  }
  26  
  27  if (!isset($_POST['FromDate']) OR !isset($_POST['ToDate']) OR $InputError==1){
  28      include  ('includes/header.inc');
  29      if ($InputError==1){
  30          prnMsg($msg,'error');
  31      }
  32  
  33      echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . $Title . '" alt="" />' . ' '
  34          . _('Orders Invoiced Report') . '</p>';
  35  
  36      echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
  37      echo '<div>';
  38      echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
  39          <table class="selection">
  40          <tr>
  41              <td>' . _('Enter the date from which orders are to be listed') . ':</td>
  42              <td><input type="text" required="required" autofocus="autofocus" class="date" alt="' .$_SESSION['DefaultDateFormat'] .'" name="FromDate" maxlength="10" size="10" value="' . Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m'),Date('d')-1,Date('y'))) . '" /></td>
  43          </tr>
  44          <tr>
  45              <td>' . _('Enter the date to which orders are to be listed') . ':</td>
  46              <td><input type="text" required="required" class="date" alt="' .$_SESSION['DefaultDateFormat'] . '" name="ToDate" maxlength="10" size="10" value="' . Date($_SESSION['DefaultDateFormat']) . '" /></td>
  47          </tr>
  48          <tr>
  49              <td>' . _('Inventory Category') . '</td>
  50              <td>';
  51  
  52      $sql = "SELECT categorydescription, categoryid FROM stockcategory WHERE stocktype<>'D' AND stocktype<>'L'";
  53      $result = DB_query($sql,$db);
  54  
  55      echo '<select required="required" name="CategoryID">';
  56      echo '<option selected="selected" value="All">' . _('Over All Categories') . '</option>';
  57  
  58      while ($myrow=DB_fetch_array($result)){
  59      echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
  60      }
  61      echo '</select></td>
  62          </tr>
  63          <tr>
  64              <td>' . _('Inventory Location') . ':</td>
  65              <td><select required="required" name="Location">
  66                  <option selected="selected" value="All">' . _('All Locations') . '</option>';
  67  
  68      $result= DB_query("SELECT loccode, locationname FROM locations",$db);
  69      while ($myrow=DB_fetch_array($result)){
  70          echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
  71      }
  72      echo '</select></td>
  73          </tr>
  74          </table>
  75          <br />
  76          <div class="centre">
  77              <input type="submit" name="Go" value="' . _('Create PDF') . '" />
  78          </div>
  79          </div>
  80      </form>';
  81  
  82      include ('includes/footer.inc');
  83      exit;
  84  } else {
  85      include ('includes/PDFStarter.php');
  86      $pdf->addInfo('Title',_('Orders Invoiced Report'));
  87      $pdf->addInfo('Subject',_('Orders from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate']);
  88      $line_height=12;
  89      $PageNumber = 1;
  90      $TotalDiffs = 0;
  91  }
  92  
  93  if ($_POST['CategoryID']=='All' AND $_POST['Location']=='All'){
  94      $sql= "SELECT salesorders.orderno,
  95                    salesorders.debtorno,
  96                    salesorders.branchcode,
  97                    salesorders.customerref,
  98                    salesorders.orddate,
  99                    salesorders.fromstkloc,
 100                    salesorders.printedpackingslip,
 101                    salesorders.datepackingslipprinted,
 102                    salesorderdetails.stkcode,
 103                    stockmaster.description,
 104                    stockmaster.units,
 105                    stockmaster.decimalplaces,
 106                    debtorsmaster.name,
 107                    custbranch.brname,
 108                    locations.locationname,
 109                    SUM(salesorderdetails.quantity) AS totqty,
 110                    SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced
 111                 FROM salesorders
 112                   INNER JOIN salesorderdetails
 113                   ON salesorders.orderno = salesorderdetails.orderno
 114                   INNER JOIN stockmaster
 115                   ON salesorderdetails.stkcode = stockmaster.stockid
 116                   INNER JOIN debtorsmaster
 117                   ON salesorders.debtorno=debtorsmaster.debtorno
 118                   INNER JOIN custbranch
 119                   ON custbranch.debtorno=salesorders.debtorno
 120                   AND custbranch.branchcode=salesorders.branchcode
 121                   INNER JOIN locations
 122                   ON salesorders.fromstkloc=locations.loccode
 123               WHERE orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 124                    AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 125  
 126  
 127  } elseif ($_POST['CategoryID']!='All' AND $_POST['Location']=='All') {
 128      $sql= "SELECT salesorders.orderno,
 129                    salesorders.debtorno,
 130                    salesorders.branchcode,
 131                    salesorders.customerref,
 132                    salesorders.orddate,
 133                    salesorders.fromstkloc,
 134                    salesorders.printedpackingslip,
 135                    salesorders.datepackingslipprinted,
 136                    salesorderdetails.stkcode,
 137                    stockmaster.description,
 138                    stockmaster.units,
 139                    stockmaster.decimalplaces,
 140                    debtorsmaster.name,
 141                    custbranch.brname,
 142                    locations.locationname,
 143                    SUM(salesorderdetails.quantity) AS totqty,
 144                    SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced
 145               FROM salesorders
 146                   INNER JOIN salesorderdetails
 147                   ON salesorders.orderno = salesorderdetails.orderno
 148                   INNER JOIN stockmaster
 149                   ON salesorderdetails.stkcode = stockmaster.stockid
 150                   INNER JOIN debtorsmaster
 151                   ON salesorders.debtorno=debtorsmaster.debtorno
 152                   INNER JOIN custbranch
 153                   ON custbranch.debtorno=salesorders.debtorno
 154                   AND custbranch.branchcode=salesorders.branchcode
 155                   INNER JOIN locations
 156                   ON salesorders.fromstkloc=locations.loccode
 157               WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'
 158                    AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 159                    AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 160  
 161  } elseif ($_POST['CategoryID']=='All' AND $_POST['Location']!='All') {
 162      $sql= "SELECT salesorders.orderno,
 163                    salesorders.debtorno,
 164                    salesorders.branchcode,
 165                    salesorders.customerref,
 166                    salesorders.orddate,
 167                    salesorders.fromstkloc,
 168                    salesorders.printedpackingslip,
 169                    salesorders.datepackingslipprinted,
 170                    salesorderdetails.stkcode,
 171                    stockmaster.description,
 172                    stockmaster.units,
 173                    stockmaster.decimalplaces,
 174                    debtorsmaster.name,
 175                    custbranch.brname,
 176                    locations.locationname,
 177                    SUM(salesorderdetails.quantity) AS totqty,
 178                    SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced
 179               FROM salesorders
 180                   INNER JOIN salesorderdetails
 181                   ON salesorders.orderno = salesorderdetails.orderno
 182                   INNER JOIN stockmaster
 183                   ON salesorderdetails.stkcode = stockmaster.stockid
 184                   INNER JOIN debtorsmaster
 185                   ON salesorders.debtorno=debtorsmaster.debtorno
 186                   INNER JOIN custbranch
 187                   ON custbranch.debtorno=salesorders.debtorno
 188                   AND custbranch.branchcode=salesorders.branchcode
 189                   INNER JOIN locations
 190                   ON salesorders.fromstkloc=locations.loccode
 191               WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
 192                    AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 193                    AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 194  
 195  } elseif ($_POST['CategoryID']!='All' AND $_POST['location']!='All'){
 196  
 197      $sql= "SELECT salesorders.orderno,
 198                    salesorders.debtorno,
 199                    salesorders.branchcode,
 200                    salesorders.customerref,
 201                    salesorders.orddate,
 202                    salesorders.fromstkloc,
 203                    salesorderdetails.stkcode,
 204                    stockmaster.description,
 205                    stockmaster.units,
 206                    stockmaster.decimalplaces,
 207                    debtorsmaster.name,
 208                    custbranch.brname,
 209                    locations.locationname,
 210                    SUM(salesorderdetails.quantity) AS totqty,
 211                    SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced
 212                   INNER JOIN locations
 213                   ON salesorders.fromstkloc=locations.loccode
 214              FROM salesorders
 215                   INNER JOIN salesorderdetails
 216                   ON salesorders.orderno = salesorderdetails.orderno
 217                   INNER JOIN stockmaster
 218                   ON salesorderdetails.stkcode = stockmaster.stockid
 219                   INNER JOIN debtorsmaster
 220                   ON salesorders.debtorno=debtorsmaster.debtorno
 221                   INNER JOIN custbranch
 222                   ON custbranch.debtorno=salesorders.debtorno
 223                   AND custbranch.branchcode=salesorders.branchcode
 224              WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'
 225                    AND salesorders.fromstkloc ='" . $_POST['Location'] . "'
 226                    AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'
 227                    AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
 228  }
 229  
 230  if ($_SESSION['SalesmanLogin'] != '') {
 231      $sql .= " AND salesorders.salesperson='" . $_SESSION['SalesmanLogin'] . "'";
 232  }
 233  
 234  $sql .= " GROUP BY salesorders.orderno,
 235                      salesorders.debtorno,
 236                      salesorders.branchcode,
 237                      salesorders.customerref,
 238                      salesorders.orddate,
 239                      salesorders.fromstkloc,
 240                      salesorderdetails.stkcode,
 241                      stockmaster.description,
 242                      stockmaster.units,
 243                      stockmaster.decimalplaces
 244              ORDER BY salesorders.orderno";
 245  
 246  $Result=DB_query($sql,$db,'','',false,false); //dont trap errors here
 247  
 248  if (DB_error_no($db)!=0){
 249      include ('includes/header.inc');
 250      prnMsg(_('An error occurred getting the orders details'),'',_('Database Error'));
 251      if ($debug==1){
 252          prnMsg( _('The SQL used to get the orders that failed was') . '<br />' . $sql, '',_('Database Error'));
 253      }
 254      include  ('includes/footer.inc');
 255      exit;
 256  } elseif (DB_num_rows($Result)==0){
 257        include ('includes/header.inc');
 258      prnMsg(_('There were no orders found in the database within the period from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' '. $_POST['ToDate'] . '. ' . _('Please try again selecting a different date range'), 'warn');
 259      if ($debug==1) {
 260          prnMsg(_('The SQL that returned no rows was') . '<br />' . $sql,'',_('Database Error'));
 261      }
 262      include ('includes/footer.inc');
 263      exit;
 264  }
 265  
 266  include  ('includes/PDFOrdersInvoicedPageHeader.inc');
 267  
 268  $OrderNo =0; /*initialise */
 269  $AccumTotalInv =0;
 270  $AccumOrderTotal =0;
 271  
 272  while ($myrow=DB_fetch_array($Result)){
 273  
 274      if($OrderNo != $myrow['orderno']){
 275          if ($AccumOrderTotal !=0){
 276              $LeftOvers = $pdf->addTextWrap($Left_Margin+250,$YPos,120,$FontSize,_('Total Invoiced for order') . ' ' . $OrderNo , 'left');
 277              $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,80,$FontSize,locale_number_format($AccumOrderTotal,$_SESSION['CompanyRecord']['decimalplaces']), 'right');
 278              $YPos -= ($line_height);
 279              $AccumOrderTotal =0;
 280          }
 281  
 282          $pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
 283  
 284          $YPos -= $line_height;
 285          /*Set up headings */
 286          /*draw a line */
 287  
 288          $LeftOvers = $pdf->addTextWrap($Left_Margin+2,$YPos,40,$FontSize,_('Order'), 'left');
 289          $LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,150,$FontSize,_('Customer'), 'left');
 290          $LeftOvers = $pdf->addTextWrap($Left_Margin+190,$YPos,110,$FontSize,_('Branch'), 'left');
 291          $LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,_('Customer Ref'), 'left');
 292          $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,60,$FontSize,_('Ord Date'), 'left');
 293          $LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,80,$FontSize,_('Location'), 'left');
 294  
 295          $YPos-=$line_height;
 296  
 297          /*draw a line */
 298          $pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
 299          $pdf->line($XPos, $YPos-$line_height*2,$XPos, $YPos+$line_height*2);
 300          $pdf->line($Page_Width-$Right_Margin, $YPos-$line_height*2,$Page_Width-$Right_Margin, $YPos+$line_height*2);
 301  
 302          $YPos -= ($line_height);
 303          if ($YPos - (2 *$line_height) < $Bottom_Margin){
 304              /*Then set up a new page */
 305              $PageNumber++;
 306              include  ('includes/PDFOrdersInvoicedPageHeader.inc');
 307          } /*end of new page header  */
 308      }
 309  
 310      if ($myrow['orderno']!=$OrderNo OR $NewPage){
 311  
 312          $LeftOvers = $pdf->addTextWrap($Left_Margin+2,$YPos,40,$FontSize,$myrow['orderno'], 'left');
 313          $LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,150,$FontSize,html_entity_decode($myrow['name']), 'left');
 314          $LeftOvers = $pdf->addTextWrap($Left_Margin+190,$YPos,110,$FontSize,$myrow['brname'], 'left');
 315  
 316          $LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,$myrow['customerref'], 'left');
 317          $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,60,$FontSize,ConvertSQLDate($myrow['orddate']), 'left');
 318          $LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,80,$FontSize,$myrow['locationname'], 'left');
 319  
 320          if (isset($PackingSlipPrinted)) {
 321              $LeftOvers = $pdf->addTextWrap($Left_Margin+400,$YPos,100,$FontSize,$PackingSlipPrinted, 'left');
 322          }
 323  
 324          $YPos -= ($line_height);
 325          $pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos);
 326          $YPos -= ($line_height);
 327  
 328      }
 329      $OrderNo = $myrow['orderno'];
 330      /*Set up the headings for the order */
 331      $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,_('Code'), 'left');
 332      $LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,_('Description'), 'left');
 333      $LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,_('Ordered'), 'right');
 334      $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,_('Invoiced'), 'right');
 335      $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Outstanding'), 'left');
 336      $YPos -= ($line_height);
 337      $NewPage = false;
 338  
 339      $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,$myrow['stkcode'], 'left');
 340      $LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,$myrow['description'], 'left');
 341      $LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,locale_number_format($myrow['totqty'],$myrow['decimalplaces']), 'right');
 342      $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,locale_number_format($myrow['totqtyinvoiced'],$myrow['decimalplaces']), 'right');
 343  
 344      if ($myrow['totqty']>$myrow['totqtyinvoiced']){
 345          $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,locale_number_format($myrow['totqty']-$myrow['totqtyinvoiced'],$myrow['decimalplaces']), 'right');
 346      } else {
 347          $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Complete'), 'left');
 348      }
 349  
 350      $YPos -= ($line_height);
 351      if ($YPos - (2 *$line_height) < $Bottom_Margin){
 352          /*Then set up a new page */
 353          $PageNumber++;
 354          include  ('includes/PDFOrdersInvoicedPageHeader.inc');
 355      } /*end of new page header  */
 356  
 357  
 358      /*OK now get the invoices where the item was charged */
 359      $sql = "SELECT debtortrans.order_,
 360                      systypes.typename,
 361                      debtortrans.transno,
 362                       stockmoves.price *(1-stockmoves.discountpercent) AS netprice,
 363                      -stockmoves.qty AS quantity
 364                  FROM debtortrans INNER JOIN stockmoves
 365                      ON debtortrans.type = stockmoves.type
 366                      AND debtortrans.transno=stockmoves.transno
 367                      INNER JOIN systypes ON debtortrans.type=systypes.typeid
 368                  WHERE debtortrans.order_ ='" . $OrderNo . "'
 369                  AND stockmoves.stockid ='" . $myrow['stkcode'] . "'";
 370  
 371      $InvoicesResult =DB_query($sql,$db);
 372      if (DB_num_rows($InvoicesResult)>0){
 373          $LeftOvers = $pdf->addTextWrap($Left_Margin+150,$YPos,90,$FontSize,_('Transaction Number'), 'center');
 374          $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,_('Quantity'), 'center');
 375          $LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,_('Price'), 'center');
 376          $LeftOvers = $pdf->addTextWrap($Left_Margin+380,$YPos,60,$FontSize,_('Total'), 'centre');
 377          $YPos -= ($line_height);
 378      }
 379  
 380      while ($InvRow=DB_fetch_array($InvoicesResult)){
 381  
 382          $ValueInvoiced = $InvRow['netprice']*$InvRow['quantity'];
 383  
 384          $LeftOvers = $pdf->addTextWrap($Left_Margin+150,$YPos,90,$FontSize,$InvRow['typename'] . ' ' . $InvRow['transno'], 'left');
 385          $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,locale_number_format($InvRow['quantity'],$myrow['decimalplaces']), 'right');
 386          $LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,locale_number_format($InvRow['netprice'],$_SESSION['CompanyRecord']['decimalplaces']), 'right');
 387          $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,80,$FontSize,locale_number_format($ValueInvoiced,$_SESSION['CompanyRecord']['decimalplaces']), 'right');
 388  
 389           $YPos -= ($line_height);
 390  
 391           if ($YPos - (2 *$line_height) < $Bottom_Margin){
 392              /*Then set up a new page */
 393              $PageNumber++;
 394              include  ('includes/PDFOrdersInvoicedPageHeader.inc');
 395          } /*end of new page header  */
 396          $AccumOrderTotal += $ValueInvoiced;
 397          $AccumTotalInv += $ValueInvoiced;
 398      }
 399  
 400  
 401       $YPos -= ($line_height);
 402       if ($YPos - (2 *$line_height) < $Bottom_Margin){
 403          /*Then set up a new page */
 404              $PageNumber++;
 405           include  ('includes/PDFOrdersInvoicedPageHeader.inc');
 406       } /*end of new page header  */
 407  } /* end of while there are invoiced orders to print */
 408  
 409  $YPos -= ($line_height);
 410  $LeftOvers = $pdf->addTextWrap($Left_Margin+260,$YPos,100,$FontSize,_('GRAND TOTAL INVOICED'), 'right');
 411  $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,80,$FontSize,locale_number_format($AccumTotalInv,$_SESSION['CompanyRecord']['decimalplaces']), 'right');
 412  $YPos -= ($line_height);
 413  
 414  $pdf->OutputD($_SESSION['DatabaseName'] . '_OrdersInvoiced_' . date('Y-m-d') . '.pdf');
 415  $pdf->__destruct();
 416  ?>

title

Description

title

Description

title

Description

title

title

Body