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