1<?php 2 3 4include('includes/session.php'); 5include('includes/SQL_CommonFunctions.inc'); 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') . ' ' . $_SESSION['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') . ' ' . $_SESSION['DefaultDateFormat']; 16 $InputError=1; 17 unset($_POST['ToDate']); 18} 19 20if (!isset($_POST['FromDate']) OR !isset($_POST['ToDate'])){ 21 22 $Title = _('Order Status Report'); 23 include ('includes/header.php'); 24 25 if ($InputError==1){ 26 prnMsg($msg,'error'); 27 } 28 29 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . $Title . '" alt="" />' . ' ' . _('Order Status Report') . '</p>'; 30 31 echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">'; 32 echo '<div> 33 <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" /> 34 <table class="selection"> 35 <tr> 36 <td>' . _('Enter the date from which orders are to be listed') . ':</td> 37 <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> 38 </tr> 39 <tr> 40 <td>' . _('Enter the date to which orders are to be listed') . ':</td> 41 <td><input type="text" required="required" class="date" name="ToDate" maxlength="10" size="11" value="' . Date($_SESSION['DefaultDateFormat']) . '" /></td> 42 </tr> 43 <tr> 44 <td>' . _('Inventory Category') . '</td> 45 <td>'; 46 47 $sql = "SELECT categorydescription, categoryid FROM stockcategory WHERE stocktype<>'D' AND stocktype<>'L'"; 48 $result = DB_query($sql); 49 50 51 echo '<select required="required" name="CategoryID"> 52 <option selected="selected" value="All">' . _('Over All Categories') . '</option>'; 53 54 while ($myrow=DB_fetch_array($result)){ 55 echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>'; 56 } 57 echo '</select></td> 58 </tr> 59 <tr> 60 <td>' . _('Inventory Location') . ':</td> 61 <td><select name="Location"> 62 <option selected="selected" value="All">' . _('All Locations') . '</option>'; 63 64 $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"); 65 while ($myrow=DB_fetch_array($result)){ 66 echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>'; 67 } 68 echo '</select></td></tr>'; 69 70 echo '<tr> 71 <td>' . _('Back Order Only') . ':</td> 72 <td><select name="BackOrders"> 73 <option selected="selected" value="Yes">' . _('Only Show Back Orders') . '</option> 74 <option value="No">' . _('Show All Orders') . '</option> 75 </select></td> 76 </tr> 77 </table> 78 <br /> 79 <div class="centre"> 80 <input type="submit" name="Go" value="' . _('Create PDF') . '" /> 81 </div> 82 </div> 83 </form>'; 84 85 include('includes/footer.php'); 86 exit; 87} else { 88 include('includes/PDFStarter.php'); 89 $pdf->addInfo('Title',_('Order Status Report')); 90 $pdf->addInfo('Subject',_('Orders from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate']); 91 $line_height=12; 92 $PageNumber = 1; 93 $TotalDiffs = 0; 94} 95 96 97if ($_POST['CategoryID']=='All' AND $_POST['Location']=='All'){ 98 $sql= "SELECT salesorders.orderno, 99 salesorders.debtorno, 100 salesorders.branchcode, 101 salesorders.customerref, 102 salesorders.orddate, 103 salesorders.fromstkloc, 104 salesorders.printedpackingslip, 105 salesorders.datepackingslipprinted, 106 salesorderdetails.stkcode, 107 stockmaster.description, 108 stockmaster.units, 109 stockmaster.decimalplaces, 110 salesorderdetails.quantity, 111 salesorderdetails.qtyinvoiced, 112 salesorderdetails.completed, 113 debtorsmaster.name, 114 custbranch.brname, 115 locations.locationname 116 FROM salesorders 117 INNER JOIN salesorderdetails 118 ON salesorders.orderno = salesorderdetails.orderno 119 INNER JOIN stockmaster 120 ON salesorderdetails.stkcode = stockmaster.stockid 121 INNER JOIN debtorsmaster 122 ON salesorders.debtorno=debtorsmaster.debtorno 123 INNER JOIN custbranch 124 ON custbranch.debtorno=salesorders.debtorno 125 AND custbranch.branchcode=salesorders.branchcode 126 INNER JOIN locations 127 ON salesorders.fromstkloc=locations.loccode 128 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 129 WHERE salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "' 130 AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "' 131 AND salesorders.quotation=0"; 132 133} elseif ($_POST['CategoryID']!='All' AND $_POST['Location']=='All') { 134 $sql= "SELECT salesorders.orderno, 135 salesorders.debtorno, 136 salesorders.branchcode, 137 salesorders.customerref, 138 salesorders.orddate, 139 salesorders.fromstkloc, 140 salesorders.printedpackingslip, 141 salesorders.datepackingslipprinted, 142 salesorderdetails.stkcode, 143 stockmaster.description, 144 stockmaster.units, 145 stockmaster.decimalplaces, 146 salesorderdetails.quantity, 147 salesorderdetails.qtyinvoiced, 148 salesorderdetails.completed, 149 debtorsmaster.name, 150 custbranch.brname, 151 locations.locationname 152 FROM salesorders 153 INNER JOIN salesorderdetails 154 ON salesorders.orderno = salesorderdetails.orderno 155 INNER JOIN stockmaster 156 ON salesorderdetails.stkcode = stockmaster.stockid 157 INNER JOIN debtorsmaster 158 ON salesorders.debtorno=debtorsmaster.debtorno 159 INNER JOIN custbranch 160 ON custbranch.debtorno=salesorders.debtorno 161 AND custbranch.branchcode=salesorders.branchcode 162 INNER JOIN locations 163 ON salesorders.fromstkloc=locations.loccode 164 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 165 WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "' 166 AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "' 167 AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "' 168 AND salesorders.quotation=0"; 169 170 171} elseif ($_POST['CategoryID']=='All' AND $_POST['Location']!='All') { 172 $sql= "SELECT salesorders.orderno, 173 salesorders.debtorno, 174 salesorders.branchcode, 175 salesorders.customerref, 176 salesorders.orddate, 177 salesorders.fromstkloc, 178 salesorders.printedpackingslip, 179 salesorders.datepackingslipprinted, 180 salesorderdetails.stkcode, 181 stockmaster.description, 182 stockmaster.units, 183 stockmaster.decimalplaces, 184 salesorderdetails.quantity, 185 salesorderdetails.qtyinvoiced, 186 salesorderdetails.completed, 187 debtorsmaster.name, 188 custbranch.brname, 189 locations.locationname 190 FROM salesorders 191 INNER JOIN salesorderdetails 192 ON salesorders.orderno = salesorderdetails.orderno 193 INNER JOIN stockmaster 194 ON salesorderdetails.stkcode = stockmaster.stockid 195 INNER JOIN debtorsmaster 196 ON salesorders.debtorno=debtorsmaster.debtorno 197 INNER JOIN custbranch 198 ON custbranch.debtorno=salesorders.debtorno 199 AND custbranch.branchcode=salesorders.branchcode 200 INNER JOIN locations 201 ON salesorders.fromstkloc=locations.loccode 202 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 203 WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "' 204 AND salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "' 205 AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "' 206 AND salesorders.quotation=0"; 207 208 209} elseif ($_POST['CategoryID']!='All' AND $_POST['location']!='All'){ 210 211 $sql= "SELECT salesorders.orderno, 212 salesorders.debtorno, 213 salesorders.branchcode, 214 salesorders.customerref, 215 salesorders.orddate, 216 salesorders.fromstkloc, 217 salesorders.printedpackingslip, 218 salesorders.datepackingslipprinted, 219 salesorderdetails.stkcode, 220 stockmaster.description, 221 stockmaster.units, 222 stockmaster.decimalplaces, 223 salesorderdetails.quantity, 224 salesorderdetails.qtyinvoiced, 225 salesorderdetails.completed, 226 debtorsmaster.name, 227 custbranch.brname, 228 locations.locationname 229 FROM salesorders 230 INNER JOIN salesorderdetails 231 ON salesorders.orderno = salesorderdetails.orderno 232 INNER JOIN stockmaster 233 ON salesorderdetails.stkcode = stockmaster.stockid 234 INNER JOIN debtorsmaster 235 ON salesorders.debtorno=debtorsmaster.debtorno 236 INNER JOIN custbranch 237 ON custbranch.debtorno=salesorders.debtorno 238 AND custbranch.branchcode=salesorders.branchcode 239 INNER JOIN locations 240 ON salesorders.fromstkloc=locations.loccode 241 INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 242 WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "' 243 AND salesorders.fromstkloc ='" . $_POST['Location'] . "' 244 AND salesorders.orddate >='" . FormatDateForSQL($_POST['FromDate']) . "' 245 AND salesorders.orddate <='" . FormatDateForSQL($_POST['ToDate']) . "' 246 AND salesorders.quotation=0"; 247} 248 249if ($_POST['BackOrders']=='Yes'){ 250 $sql .= " AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced >0"; 251} 252//Add salesman role control 253if ($_SESSION['SalesmanLogin'] != '') { 254 $sql .= " AND salesorders.salesperson='" . $_SESSION['SalesmanLogin'] . "'"; 255} 256 257$sql .= " ORDER BY salesorders.orderno"; 258 259$Result=DB_query($sql,'','',false,false); //dont trap errors here 260 261if (DB_error_no()!=0){ 262 include('includes/header.php'); 263 echo '<br />' . _('An error occurred getting the orders details'); 264 if ($debug==1){ 265 echo '<br />' . _('The SQL used to get the orders that failed was') . '<br />' . $sql; 266 } 267 include ('includes/footer.php'); 268 exit; 269} elseif (DB_num_rows($Result)==0){ 270 $Title=_('Order Status Report - No Data'); 271 include('includes/header.php'); 272 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'),'info'); 273 include('includes/footer.php'); 274 exit; 275} 276 277include ('includes/PDFOrderStatusPageHeader.inc'); 278 279$OrderNo =0; /*initialise */ 280 281while ($myrow=DB_fetch_array($Result)){ 282 283 $pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos); 284 285 $YPos -= $line_height; 286 /*Set up headings */ 287 /*draw a line */ 288 289 if ($myrow['orderno']!=$OrderNo ){ 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,_('Ord Date'), 'left'); 294 $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,60,$FontSize,_('Location'), 'left'); 295 $LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,80,$FontSize,_('Status'), '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 305 if ($YPos - (2 *$line_height) < $Bottom_Margin){ 306 /*Then set up a new page */ 307 $PageNumber++; 308 include ('includes/PDFOrderStatusPageHeader.inc'); 309 $OrderNo=0; 310 } /*end of new page header */ 311 $YPos -= $line_height; 312 313 $LeftOvers = $pdf->addTextWrap($Left_Margin+2,$YPos,40,$FontSize,$myrow['orderno'], 'left'); 314 $LeftOvers = $pdf->addTextWrap($Left_Margin+40,$YPos,150,$FontSize,html_entity_decode($myrow['name'],ENT_QUOTES,'UTF-8'), 'left'); 315 $LeftOvers = $pdf->addTextWrap($Left_Margin+190,$YPos,110,$FontSize,$myrow['brname'], 'left'); 316 317 $LeftOvers = $pdf->addTextWrap($Left_Margin+300,$YPos,60,$FontSize,ConvertSQLDate($myrow['orddate']), 'left'); 318 $LeftOvers = $pdf->addTextWrap($Left_Margin+360,$YPos,80,$FontSize,$myrow['locationname'], 'left'); 319 320 if ($myrow['printedpackingslip']==1){ 321 $PackingSlipPrinted = _('Printed') . ' ' . ConvertSQLDate($myrow['datepackingslipprinted']); 322 } else { 323 $PackingSlipPrinted =_('Not yet printed'); 324 } 325 326 $LeftOvers = $pdf->addTextWrap($Left_Margin+420,$YPos,100,$FontSize,$PackingSlipPrinted, 'left'); 327 $YPos -= $line_height; 328 $pdf->line($XPos, $YPos,$Page_Width-$Right_Margin, $YPos); 329 330 $YPos -= ($line_height); 331 332 /*Its not the first line */ 333 $OrderNo = $myrow['orderno']; 334 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,_('Code'), 'left'); 335 $LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,_('Description'), 'left'); 336 $LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,_('Ordered'), 'right'); 337 $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,_('Invoiced'), 'right'); 338 $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Outstanding'), 'center'); 339 $YPos -= ($line_height); 340 341 } 342 343 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,$myrow['stkcode'], 'left'); 344 $LeftOvers = $pdf->addTextWrap($Left_Margin+60,$YPos,120,$FontSize,$myrow['description'], 'left'); 345 $LeftOvers = $pdf->addTextWrap($Left_Margin+180,$YPos,60,$FontSize,locale_number_format($myrow['quantity'],$myrow['decimalplaces']), 'right'); 346 $LeftOvers = $pdf->addTextWrap($Left_Margin+240,$YPos,60,$FontSize,locale_number_format($myrow['qtyinvoiced'],$myrow['decimalplaces']), 'right'); 347 348 if ($myrow['quantity']>$myrow['qtyinvoiced']){ 349 $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,locale_number_format($myrow['quantity']-$myrow['qtyinvoiced'],$myrow['decimalplaces']), 'right'); 350 } else { 351 $LeftOvers = $pdf->addTextWrap($Left_Margin+320,$YPos,60,$FontSize,_('Complete'), 'left'); 352 } 353 354 $YPos -= ($line_height); 355 if ($YPos - (2 *$line_height) < $Bottom_Margin){ 356 /*Then set up a new page */ 357 $PageNumber++; 358 include ('includes/PDFOrderStatusPageHeader.inc'); 359 $OrderNo=0; 360 } /*end of new page header */ 361} /* end of while there are delivery differences to print */ 362$pdf->OutputD($_SESSION['DatabaseName'] . '_OrderStatus_' . date('Y-m-d') . '.pdf'); 363$pdf->__destruct(); 364?> 365