1<?php 2// POReport.php 3// Inquiry on Purchase Orders 4 5// If Date Type is Order, the main file is purchorderdetails 6// If Date Type is Delivery, the main file is grns 7 8include('includes/session.php'); 9$ViewTopic = '';/* ?????????? */ 10$BookMark = 'POReport'; 11$Title = _('Purchase Order Report'); 12include('includes/header.php'); 13 14# Sets default date range for current month 15if (!isset($_POST['FromDate'])){ 16 $_POST['FromDate']=Date($_SESSION['DefaultDateFormat'], mktime(0,0,0,Date('m'),1,Date('Y'))); 17} 18if (!isset($_POST['ToDate'])){ 19 $_POST['ToDate'] = Date($_SESSION['DefaultDateFormat']); 20} 21 22if (isset($_POST['submit']) or isset($_POST['submitcsv'])) { 23 if (isset($_POST['PartNumber'])){ 24 $PartNumber = trim(mb_strtoupper($_POST['PartNumber'])); 25 } elseif (isset($_GET['PartNumber'])){ 26 $PartNumber = trim(mb_strtoupper($_GET['PartNumber'])); 27 } 28 29 # Part Number operator - either LIKE or = 30 $PartNumberOp = $_POST['PartNumberOp']; 31 32 if (isset($_POST['SupplierId'])){ 33 $SupplierId = trim(mb_strtoupper($_POST['SupplierId'])); 34 } elseif (isset($_GET['SupplierId'])){ 35 $SupplierId = trim(mb_strtoupper($_GET['SupplierId'])); 36 } 37 38 $SupplierIdOp = $_POST['SupplierIdOp']; 39 40 $SupplierNameOp = $_POST['SupplierNameOp']; 41 42 // Save $_POST['SummaryType'] in $SaveSummaryType because change $_POST['SummaryType'] when 43 // create $sql 44 $SaveSummaryType = $_POST['SummaryType']; 45} 46 47if (isset($_POST['SupplierName'])){ 48 $SupplierName = trim(mb_strtoupper($_POST['SupplierName'])); 49} elseif (isset($_GET['SupplierName'])){ 50 $SupplierName = trim(mb_strtoupper($_GET['SupplierName'])); 51} 52 53// Had to add supplierid to SummaryType when do summary by name because there could be several accounts 54// with the same name. Tried passing 'suppname,supplierid' in form, but it only read 'suppname' 55if (isset($_POST['SummaryType']) and $_POST['SummaryType'] == 'suppname') { 56 $_POST['SummaryType'] = "suppname, suppliers.supplierid"; 57} 58 59if (isset($_POST['submit'])) { 60 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . 61 '" alt="" />' . ' ' . $Title . '</p>'; 62 submit($PartNumber,$PartNumberOp,$SupplierId,$SupplierIdOp,$SupplierName,$SupplierNameOp,$SaveSummaryType); 63} else if (isset($_POST['submitcsv'])) { 64 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . 65 '" alt="" />' . ' ' . $Title . '</p>'; 66 submitcsv($PartNumber,$PartNumberOp,$SupplierId,$SupplierIdOp,$SupplierName,$SupplierNameOp,$SaveSummaryType); 67} else { 68 echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . 69 '" alt="" />' . $Title . '</p>'; 70 display(); 71} 72 73 74//####_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT#### 75function submit($PartNumber,$PartNumberOp,$SupplierId,$SupplierIdOp,$SupplierName,$SupplierNameOp,$SaveSummaryType) { 76 77 global $RootPath; 78 //initialize no input errors 79 $InputError = 0; 80 81 /* actions to take once the user has clicked the submit button 82 ie the page has called itself with some user input */ 83 84 //first off validate inputs sensible 85 86 if (!Is_Date($_POST['FromDate'])) { 87 $InputError = 1; 88 prnMsg(_('Invalid From Date'),'error'); 89 } 90 if (!Is_Date($_POST['ToDate'])) { 91 $InputError = 1; 92 prnMsg(_('Invalid To Date'),'error'); 93 } 94 95 # Add more to WHERE statement, if user entered something for the part number,supplierid, name 96 $WherePart = ' '; 97 if (mb_strlen($PartNumber) > 0 && $PartNumberOp == 'LIKE') { 98 $PartNumber = $PartNumber . '%'; 99 } else { 100 $PartNumberOp = '='; 101 } 102 if (mb_strlen($PartNumber) > 0) { 103 $WherePart = " AND purchorderdetails.itemcode " . $PartNumberOp . " '" . $PartNumber . "' "; 104 } else { 105 $WherePart=' '; 106 } 107 108 $WhereSupplierID = ' '; 109 if ($SupplierIdOp == 'LIKE') { 110 $SupplierId = $SupplierId . '%'; 111 } else { 112 $SupplierIdOp = '='; 113 } 114 if (mb_strlen($SupplierId) > 0) { 115 $WhereSupplierID = " AND purchorders.supplierno " . $SupplierIdOp . " '" . $SupplierId . "' "; 116 } else { 117 $WhereSupplierID=' '; 118 } 119 120 $WhereSupplierName = ' '; 121 if (mb_strlen($SupplierName) > 0 AND $SupplierNameOp == 'LIKE') { 122 $SupplierName = $SupplierName . '%'; 123 } else { 124 $SupplierNameOp = '='; 125 } 126 if (mb_strlen($SupplierName) > 0) { 127 $WhereSupplierName = " AND suppliers.suppname " . $SupplierNameOp . " '" . $SupplierName . "' "; 128 } else { 129 $WhereSupplierName=' '; 130 } 131 132 if (mb_strlen($_POST['OrderNo']) > 0) { 133 $WhereOrderNo = " AND purchorderdetails.orderno = '" . $_POST['OrderNo'] . "' "; 134 } else { 135 $WhereOrderNo=' '; 136 } 137 138 $WhereLineStatus = ' '; 139 # Had to use IF statement instead of comparing 'linestatus' to $_POST['LineStatus'] 140 #in WHERE clause because the WHERE clause didn't recognize 141 # that had used the IF statement to create a field called linestatus 142 if ($_POST['LineStatus'] != 'All') { 143 if ($_POST['DateType'] == 'Order') { 144 $WhereLineStatus = " AND IF(purchorderdetails.quantityord = purchorderdetails.qtyinvoiced || 145 purchorderdetails.completed = 1,'Completed','Open') = '" . $_POST['LineStatus'] . "'"; 146 } else { 147 $WhereLineStatus = " AND IF(grns.qtyrecd - grns.quantityinv <> 0,'Open','Completed') = '" 148 . $_POST['LineStatus'] . "'"; 149 } 150 } 151 152 153 $WhereCategory = ' '; 154 if ($_POST['Category'] != 'All') { 155 $WhereCategory = " AND stockmaster.categoryid = '" . $_POST['Category'] . "'"; 156 } 157 158 if ($InputError !=1) { 159 $FromDate = FormatDateForSQL($_POST['FromDate']); 160 $ToDate = FormatDateForSQL($_POST['ToDate']); 161 if ($_POST['ReportType'] == 'Detail') { 162 if ($_POST['DateType'] == 'Order') { 163 $sql = "SELECT purchorderdetails.orderno, 164 purchorderdetails.itemcode, 165 purchorderdetails.deliverydate, 166 purchorders.supplierno, 167 purchorders.orddate, 168 purchorderdetails.quantityord, 169 purchorderdetails.quantityrecd, 170 purchorderdetails.qtyinvoiced, 171 (purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 172 (purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 173 IF(purchorderdetails.quantityord = purchorderdetails.qtyinvoiced || 174 purchorderdetails.completed = 1,'Completed','Open') as linestatus, 175 suppliers.suppname, 176 stockmaster.decimalplaces, 177 stockmaster.description 178 FROM purchorderdetails 179 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 180 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 181 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 182 WHERE purchorders.orddate >='$FromDate' 183 AND purchorders.orddate <='$ToDate' 184 $WherePart 185 $WhereSupplierID 186 $WhereSupplierName 187 $WhereOrderNo 188 $WhereLineStatus 189 $WhereCategory 190 ORDER BY " . $_POST['SortBy']; 191 } else { 192 // Selects by delivery date from grns 193 $sql = "SELECT purchorderdetails.orderno, 194 purchorderdetails.itemcode, 195 grns.deliverydate, 196 purchorders.supplierno, 197 purchorders.orddate, 198 purchorderdetails.quantityord as quantityrecd, 199 grns.qtyrecd as quantityord, 200 grns.quantityinv as qtyinvoiced, 201 (grns.qtyrecd * purchorderdetails.unitprice) as extprice, 202 (grns.qtyrecd * grns.stdcostunit) as extcost, 203 IF(grns.qtyrecd - grns.quantityinv <> 0,'Open','Completed') as linestatus, 204 suppliers.suppname, 205 stockmaster.decimalplaces, 206 stockmaster.description 207 FROM grns 208 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 209 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 210 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 211 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 212 WHERE grns.deliverydate >='$FromDate' 213 AND grns.deliverydate <='$ToDate' 214 $WherePart 215 $WhereSupplierID 216 $WhereSupplierName 217 $WhereOrderNo 218 $WhereLineStatus 219 $WhereCategory 220 ORDER BY " . $_POST['SortBy']; 221 } 222 } else { 223 // sql for Summary report 224 $orderby = $_POST['SummaryType']; 225 // The following is because the 'extprice' summary is a special case - with the other 226 // summaries, you group and order on the same field; with 'extprice', you are actually 227 // grouping on the stkcode and ordering by extprice descending 228 if ($_POST['SummaryType'] == 'extprice') { 229 $_POST['SummaryType'] = 'itemcode'; 230 $orderby = 'extprice DESC'; 231 } 232 if ($_POST['DateType'] == 'Order') { 233 if ($_POST['SummaryType'] == 'extprice' || $_POST['SummaryType'] == 'itemcode') { 234 $sql = "SELECT purchorderdetails.itemcode, 235 SUM(purchorderdetails.quantityord) as quantityord, 236 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 237 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 238 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 239 stockmaster.decimalplaces, 240 stockmaster.description 241 FROM purchorderdetails 242 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 243 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 244 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 245 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 246 WHERE purchorders.orddate >='$FromDate' 247 AND purchorders.orddate <='$ToDate' 248 $WherePart 249 $WhereSupplierID 250 $WhereSupplierName 251 $WhereOrderNo 252 $WhereLineStatus 253 $WhereCategory 254 GROUP BY " . $_POST['SummaryType'] . 255 ',stockmaster.decimalplaces, 256 stockmaster.description 257 ORDER BY ' . $orderby; 258 } elseif ($_POST['SummaryType'] == 'orderno') { 259 $sql = "SELECT purchorderdetails.orderno, 260 purchorders.supplierno, 261 SUM(purchorderdetails.quantityord) as quantityord, 262 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 263 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 264 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 265 suppliers.suppname 266 FROM purchorderdetails 267 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 268 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 269 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 270 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 271 WHERE purchorders.orddate >='$FromDate' 272 AND purchorders.orddate <='$ToDate' 273 $WherePart 274 $WhereSupplierID 275 $WhereSupplierName 276 $WhereOrderNo 277 $WhereLineStatus 278 $WhereCategory 279 GROUP BY " . $_POST['SummaryType'] . 280 ',purchorders.supplierno, 281 suppliers.suppname 282 ORDER BY ' . $orderby; 283 } elseif ($_POST['SummaryType'] == 'supplierno' || $_POST['SummaryType'] == 'suppname,suppliers.supplierid') { 284 $sql = "SELECT purchorders.supplierno, 285 SUM(purchorderdetails.quantityord) as quantityord, 286 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 287 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 288 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 289 suppliers.suppname 290 FROM purchorderdetails 291 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 292 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 293 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 294 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 295 WHERE purchorders.orddate >='$FromDate' 296 AND purchorders.orddate <='$ToDate' 297 $WherePart 298 $WhereSupplierID 299 $WhereSupplierName 300 $WhereOrderNo 301 $WhereLineStatus 302 $WhereCategory 303 GROUP BY " . $_POST['SummaryType'] . 304 ",purchorders.supplierno, 305 suppliers.suppname 306 ORDER BY " . $orderby; 307 } elseif ($_POST['SummaryType'] == 'month') { 308 $sql = "SELECT EXTRACT(YEAR_MONTH from purchorders.orddate) as month, 309 CONCAT(MONTHNAME(purchorders.orddate),' ',YEAR(purchorders.orddate)) as monthname, 310 SUM(purchorderdetails.quantityord) as quantityord, 311 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 312 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 313 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost 314 FROM purchorderdetails 315 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 316 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 317 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 318 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 319 WHERE purchorders.orddate >='$FromDate' 320 AND purchorders.orddate <='$ToDate' 321 $WherePart 322 $WhereSupplierID 323 $WhereSupplierName 324 $WhereOrderNo 325 $WhereLineStatus 326 $WhereCategory 327 GROUP BY " . $_POST['SummaryType'] . 328 ", monthname 329 ORDER BY " . $orderby; 330 } elseif ($_POST['SummaryType'] == 'categoryid') { 331 $sql = "SELECT SUM(purchorderdetails.quantityord) as quantityord, 332 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 333 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 334 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 335 stockmaster.categoryid, 336 stockcategory.categorydescription 337 FROM purchorderdetails 338 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 339 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 340 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 341 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 342 WHERE purchorders.orddate >='$FromDate' 343 AND purchorders.orddate <='$ToDate' 344 $WherePart 345 $WhereSupplierID 346 $WhereSupplierName 347 $WhereOrderNo 348 $WhereLineStatus 349 $WhereCategory 350 GROUP BY " . $_POST['SummaryType'] . 351 ", categorydescription 352 ORDER BY " . $orderby; 353 } 354 } else { 355 // Selects by delivery date from grns 356 if ($_POST['SummaryType'] == 'extprice' || $_POST['SummaryType'] == 'itemcode') { 357 $sql = "SELECT purchorderdetails.itemcode, 358 SUM(grns.qtyrecd) as quantityord, 359 SUM(grns.quantityinv) as qtyinvoiced, 360 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 361 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 362 stockmaster.description 363 FROM grns 364 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 365 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 366 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 367 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 368 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 369 WHERE grns.deliverydate >='$FromDate' 370 AND grns.deliverydate <='$ToDate' 371 $WherePart 372 $WhereSupplierID 373 $WhereSupplierName 374 $WhereOrderNo 375 $WhereLineStatus 376 $WhereCategory 377 GROUP BY " . $_POST['SummaryType'] . 378 ", stockmaster.description 379 ORDER BY " . $orderby; 380 } elseif ($_POST['SummaryType'] == 'orderno') { 381 $sql = "SELECT purchorderdetails.orderno, 382 purchorders.supplierno, 383 SUM(grns.qtyrecd) as quantityord, 384 SUM(grns.quantityinv) as qtyinvoiced, 385 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 386 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 387 suppliers.suppname 388 FROM grns 389 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 390 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 391 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 392 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 393 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 394 WHERE grns.deliverydate >='$FromDate' 395 AND grns.deliverydate <='$ToDate' 396 $WherePart 397 $WhereSupplierID 398 $WhereSupplierName 399 $WhereOrderNo 400 $WhereLineStatus 401 $WhereCategory 402 GROUP BY " . $_POST['SummaryType'] . 403 ', purchorders.supplierno, 404 suppliers.suppname 405 ORDER BY ' . $orderby; 406 } elseif ($_POST['SummaryType'] == 'supplierno' || $_POST['SummaryType'] == 'suppname,suppliers.supplierid') { 407 $sql = "SELECT purchorders.supplierno, 408 SUM(grns.qtyrecd) as quantityord, 409 SUM(grns.quantityinv) as qtyinvoiced, 410 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 411 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 412 suppliers.suppname 413 FROM grns 414 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 415 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 416 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 417 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 418 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 419 WHERE grns.deliverydate >='$FromDate' 420 AND grns.deliverydate <='$ToDate' 421 $WherePart 422 $WhereSupplierID 423 $WhereSupplierName 424 $WhereOrderNo 425 $WhereLineStatus 426 $WhereCategory 427 GROUP BY " . $_POST['SummaryType'] . 428 ', purchorders.supplierno, 429 suppliers.suppname 430 ORDER BY ' . $orderby; 431 } elseif ($_POST['SummaryType'] == 'month') { 432 $sql = "SELECT EXTRACT(YEAR_MONTH from purchorders.orddate) as month, 433 CONCAT(MONTHNAME(purchorders.orddate),' ',YEAR(purchorders.orddate)) as monthname, 434 SUM(grns.qtyrecd) as quantityord, 435 SUM(grns.quantityinv) as qtyinvoiced, 436 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 437 SUM(grns.qtyrecd * grns.stdcostunit) as extcost 438 FROM grns 439 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 440 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 441 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 442 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 443 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 444 WHERE grns.deliverydate >='$FromDate' 445 AND grns.deliverydate <='$ToDate' 446 $WherePart 447 $WhereSupplierID 448 $WhereSupplierName 449 $WhereOrderNo 450 $WhereLineStatus 451 $WhereCategory 452 GROUP BY " . $_POST['SummaryType'] . 453 ',monthname 454 ORDER BY ' . $orderby; 455 } elseif ($_POST['SummaryType'] == 'categoryid') { 456 $sql = "SELECT stockmaster.categoryid, 457 stockcategory.categorydescription, 458 SUM(grns.qtyrecd) as quantityord, 459 SUM(grns.quantityinv) as qtyinvoiced, 460 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 461 SUM(grns.qtyrecd * grns.stdcostunit) as extcost 462 FROM grns 463 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 464 LEFT JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 465 LEFT JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 466 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 467 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 468 WHERE grns.deliverydate >='$FromDate' 469 AND grns.deliverydate <='$ToDate' 470 $WherePart 471 $WhereSupplierID 472 $WhereSupplierName 473 $WhereOrderNo 474 $WhereLineStatus 475 $WhereCategory 476 GROUP BY " . $_POST['SummaryType'] . 477 ",categorydescription 478 ORDER BY " . $orderby; 479 } 480 } 481 } // End of if ($_POST['ReportType'] 482 //echo "<br/>$sql<br/>"; 483 $ErrMsg = _('The SQL to find the parts selected failed with the message'); 484 $result = DB_query($sql,$ErrMsg); 485 $ctr = 0; 486 $TotalQty = 0; 487 $TotalExtCost = 0; 488 $TotalExtPrice = 0; 489 $TotalInvQty = 0; 490 491 // Create array for summary type to display in header. Access it with $SaveSummaryType 492 $Summary_Array['orderno'] = _('Order Number'); 493 $Summary_Array['itemcode'] = _('Part Number'); 494 $Summary_Array['extprice'] = _('Extended Price'); 495 $Summary_Array['supplierno'] = _('Customer Number'); 496 $Summary_Array['suppname'] = _('Customer Name'); 497 $Summary_Array['month'] = _('Month'); 498 $Summary_Array['categoryid'] = _('Stock Category'); 499 500 // Create array for sort for detail report to display in header 501 $Detail_Array['purchorderdetails.orderno'] = _('Order Number'); 502 $Detail_Array['purchorderdetails.itemcode'] = _('Part Number'); 503 $Detail_Array['suppliers.supplierid,purchorderdetails.orderno'] = _('Supplier Number'); 504 $Detail_Array['suppliers.suppname,suppliers.supplierid,purchorderdetails.orderno'] = _('Supplier Name'); 505 506 // Display Header info 507 echo '<table class="selection">'; 508 if ($_POST['ReportType'] == 'Summary') { 509 $SortBy_Display = $Summary_Array[$SaveSummaryType]; 510 } else { 511 $SortBy_Display = $Detail_Array[$_POST['SortBy']]; 512 } 513 echo '<tr><th colspan="2">' . _('Header Details') . '</th></tr>'; 514 echo '<tr><td>' . _('Purchase Order Report') . '</td> 515 <td>' . $_POST['ReportType'] . ' ' . _('By') . ' '.$SortBy_Display . '</td></tr>'; 516 echo '<tr><td>' . _('Date Type') . '</td> 517 <td>' . $_POST['DateType'] . '</td></tr>'; 518 echo '<tr><td>' . _('Date Range') . '</td> 519 <td>' . $_POST['FromDate'] . ' ' . _('To') . ' ' . $_POST['ToDate'] . '</td></tr>'; 520 if (mb_strlen(trim($PartNumber)) > 0) { 521 echo '<tr><td>' . _('Part Number') . '</td> 522 <td>' . $_POST['PartNumberOp'] . ' ' . $_POST['PartNumber'] . '</td></tr>'; 523 } 524 if (mb_strlen(trim($_POST['SupplierId'])) > 0) { 525 echo '<tr><td>' . _('Supplier Number') . '</td> 526 <td>' . $_POST['SupplierIdOp'] . ' ' . $_POST['SupplierId'] . '</td></tr>'; 527 } 528 if (mb_strlen(trim($_POST['SupplierName'])) > 0) { 529 echo '<tr><td>' . _('Supplier Name') . '</td> 530 <td>' . $_POST['SupplierNameOp'] . ' ' . $_POST['SupplierName'] . '</td></tr>'; 531 } 532 echo '<tr><td>' . _('Line Item Status') . '</td> 533 <td>' . $_POST['LineStatus'] . '</td></tr>'; 534 echo '<tr><td>' . _('Stock Category') . '</td> 535 <td>' . $_POST['Category'] . '</td></tr></table>'; 536 537 if ($_POST['ReportType'] == 'Detail') { 538 echo '<br /><table class="selection" width="98%">'; 539 if ($_POST['DateType'] == 'Order') { 540 echo '<tr><th>' . _('Order No') . '</th> 541 <th>' . _('Part Number') . '</th> 542 <th>' . _('Order Date') . '</th> 543 <th>' . _('Supplier No') . '</th> 544 <th>' . _('Supplier Name') . '</th> 545 <th>' . _('Order Qty') . '</th> 546 <th>' . _('Qty Received') . '</th> 547 <th>' . _('Extended Cost') . '</th> 548 <th>' . _('Extended Price') . '</th> 549 <th>' . _('Invoiced Qty') . '</th> 550 <th>' . _('Line Status') . '</th> 551 <th>' . _('Item Due') . '</th> 552 <th>' . _('Part Description') . '</th> 553 </tr>'; 554 555 $linectr = 0; 556 557 while ($myrow = DB_fetch_array($result)) { 558 $linectr++; 559 560 // Detail for both DateType of Order 561 printf('<tr class="striped_row"> 562 <td><a href="'. $RootPath . '/PO_OrderDetails.php?OrderNo=%s">%s</a></td> 563 <td>%s</td> 564 <td>%s</td> 565 <td>%s</td> 566 <td>%s</td> 567 <td>%s</td> 568 <td class="number">%s</td> 569 <td class="number">%s</td> 570 <td class="number">%s</td> 571 <td class="number">%s</td> 572 <td>%s</td> 573 <td>%s</td> 574 <td>%s</td> 575 </tr>', 576 $myrow['orderno'], 577 $myrow['orderno'], 578 $myrow['itemcode'], 579 ConvertSQLDate($myrow['orddate']), 580 $myrow['supplierno'], 581 $myrow['suppname'], 582 locale_number_format($myrow['quantityord'],$myrow['decimalplaces']), 583 locale_number_format($myrow['quantityrecd'],$myrow['decimalplaces']), 584 locale_number_format($myrow['extcost'],2), 585 locale_number_format($myrow['extprice'],2), 586 locale_number_format($myrow['qtyinvoiced'],$myrow['decimalplaces']), 587 $myrow['linestatus'], 588 ConvertSQLDate($myrow['deliverydate']), 589 $myrow['description']); 590 $LastDecimalPlaces = $myrow['decimalplaces']; 591 $TotalQty += $myrow['quantityord']; 592 $TotalExtCost += $myrow['extcost']; 593 $TotalExtPrice += $myrow['extprice']; 594 $TotalInvQty += $myrow['qtyinvoiced']; 595 } //END WHILE LIST LOOP 596 // Print totals 597 printf('<tr> 598 <td>%s</td> 599 <td>%s</td> 600 <td>%s</td> 601 <td>%s</td> 602 <td>%s</td> 603 <td class="number">%s</td> 604 <td class="number">%s</td> 605 <td class="number">%s</td> 606 <td class="number">%s</td> 607 <td>%s</td> 608 <td>%s</td> 609 </tr>', 610 _('Totals'), 611 _('Lines - ') . $linectr, 612 ' ', 613 ' ', 614 ' ', 615 locale_number_format($TotalQty,2), 616 locale_number_format($TotalExtCost,2), 617 locale_number_format($TotalExtPrice,2), 618 locale_number_format($TotalInvQty,2), 619 ' ', 620 ' '); 621 } else { 622 // Header for Date Type of Delivery Date 623 echo '<tr> 624 <th>' . _('Order No') . '</th> 625 <th>' . _('Part Number') . '</th> 626 <th>' . _('Order Date') . '</th> 627 <th>' . _('Supplier No') . '</th> 628 <th>' . _('Supplier Name') . '</th> 629 <th>' . _('Order Qty') . '</th> 630 <th>' . _('Received') . '</th> 631 <th>' . _('Extended Cost') . '</th> 632 <th>' . _('Extended Price') . '</th> 633 <th>' . _('Invoiced Qty') . '</th> 634 <th>' . _('Line Status') . '</th> 635 <th>' . _('Delivered') . '</th> 636 <th>' . _('Part Description') . '</th> 637 </tr>'; 638 639 $linectr = 0; 640 641 while ($myrow = DB_fetch_array($result)) { 642 $linectr++; 643 644 // Detail for both DateType of Ship 645 // In sql, had to alias grns.qtyrecd as quantityord so could use same name here 646 printf('<tr class="striped_row"> 647 <td>%s</td> 648 <td>%s</td> 649 <td>%s</td> 650 <td>%s</td> 651 <td>%s</td> 652 <td class="number">%s</td> 653 <td class="number">%s</td> 654 <td class="number">%s</td> 655 <td class="number">%s</td> 656 <td class="number">%s</td> 657 <td>%s</td> 658 <td>%s</td> 659 <td>%s</td> 660 </tr>', 661 $myrow['orderno'], 662 $myrow['itemcode'], 663 ConvertSQLDate($myrow['orddate']), 664 $myrow['supplierno'], 665 $myrow['suppname'], 666 locale_number_format($myrow['quantityrecd'],$myrow['decimalplaces']), 667 locale_number_format($myrow['quantityord'],$myrow['decimalplaces']), 668 locale_number_format($myrow['extcost'],2), 669 locale_number_format($myrow['extprice'],2), 670 locale_number_format($myrow['qtyinvoiced'],$myrow['decimalplaces']), 671 $myrow['linestatus'], 672 ConvertSQLDate($myrow['deliverydate']), 673 $myrow['description']); 674 675 $LastDecimalPlaces = $myrow['decimalplaces']; 676 $TotalQty += $myrow['quantityord']; 677 $TotalExtCost += $myrow['extcost']; 678 $TotalExtPrice += $myrow['extprice']; 679 $TotalInvQty += $myrow['qtyinvoiced']; 680 } //END WHILE LIST LOOP 681 // Print totals 682 printf('<tr> 683 <td>%s</td> 684 <td>%s</td> 685 <td>%s</td> 686 <td>%s</td> 687 <td>%s</td> 688 <td class="number">%s</td> 689 <td class="number">%s</td> 690 <td class="number">%s</td> 691 <td class="number">%s</td> 692 <td>%s</td> 693 <td>%s</td> 694 </tr>', 695 _('Totals'), 696 _('Lines - ') . $linectr, 697 ' ', 698 ' ', 699 ' ', 700 locale_number_format($TotalQty,$LastDecimalPlaces), 701 locale_number_format($TotalExtCost,2), 702 locale_number_format($TotalExtPrice,2), 703 locale_number_format($TotalInvQty,$LastDecimalPlaces), 704 ' ', 705 ' '); 706 } 707 echo '</table>'; 708 } else { 709 // Print summary stuff 710 echo '<br /><table class="selection" width="98%">'; 711 $summarytype = $_POST['SummaryType']; 712 // For SummaryType 'suppname' had to add supplierid to it for the GROUP BY in the sql, 713 // but have to take it away for $myrow[$summarytype] to be valid 714 // Set up description based on the Summary Type 715 if ($summarytype == "suppname,suppliers.supplierid") { 716 $summarytype = "suppname"; 717 $description = 'supplierno'; 718 $summaryheader = _('Supplier Name'); 719 $descriptionheader = _('Supplier Number'); 720 } 721 if ($summarytype == 'itemcode' || $summarytype == 'extprice') { 722 $description = 'description'; 723 $summaryheader = _('Part Number'); 724 $descriptionheader = _('Part Description'); 725 } 726 if ($summarytype == 'supplierno') { 727 $description = 'suppname'; 728 $summaryheader = _('Supplier Number'); 729 $descriptionheader = _('Supplier Name'); 730 } 731 if ($summarytype == 'orderno') { 732 $description = 'supplierno'; 733 $summaryheader = _('Order Number'); 734 $descriptionheader = _('Supplier Number'); 735 } 736 if ($summarytype == 'categoryid') { 737 $description = 'categorydescription'; 738 $summaryheader = _('Stock Category'); 739 $descriptionheader = _('Category Description'); 740 } 741 $summarydesc = $summaryheader; 742 if ($orderby == 'extprice DESC') { 743 $summarydesc = _('Extended Price'); 744 } 745 if ($summarytype == 'month') { 746 $description = 'monthname'; 747 $summaryheader = _('Month'); 748 $descriptionheader = _('Month'); 749 } 750 printf('<tr> 751 <th>%s</th> 752 <th>%s</th> 753 <th>%s</th> 754 <th>%s</th> 755 <th>%s</th> 756 <th>%s</th> 757 </tr>', 758 _($summaryheader), 759 _($descriptionheader), 760 _('Quantity'), 761 _('Extended Cost'), 762 _('Extended Price'), 763 _('Invoiced Qty')); 764 765 $suppname = ' '; 766 $linectr = 0; 767 768 while ($myrow = DB_fetch_array($result)) { 769 $linectr++; 770 if ($summarytype == 'orderno') { 771 $suppname = $myrow['suppname']; 772 } 773 774 printf('<tr class="striped_row"> 775 <td>%s</td> 776 <td>%s</td> 777 <td class="number">%s</td> 778 <td class="number">%s</td> 779 <td class="number">%s</td> 780 <td class="number">%s</td> 781 </tr>', 782 $myrow[$summarytype], 783 $myrow[$description], 784 $myrow['quantityord'], 785 locale_number_format($myrow['extcost'],2), 786 locale_number_format($myrow['extprice'],2), 787 $myrow['qtyinvoiced'], 788 $suppname); 789 $TotalQty += $myrow['quantityord']; 790 $TotalExtCost += $myrow['extcost']; 791 $TotalExtPrice += $myrow['extprice']; 792 $TotalInvQty += $myrow['qtyinvoiced']; 793 } //END WHILE LIST LOOP 794 // Print totals 795 printf('<tr> 796 <td>%s</td> 797 <td>%s</td> 798 <td class="number">%s</td> 799 <td class="number">%s</td> 800 <td class="number">%s</td> 801 <td class="number">%s</td> 802 </tr>', 803 _('Totals'), 804 _('Lines - ') . $linectr, 805 locale_number_format($TotalQty,2), 806 locale_number_format($TotalExtCost,2), 807 locale_number_format($TotalExtPrice,2), 808 locale_number_format($TotalInvQty,2), 809 ' '); 810 echo '</table>'; 811 } // End of if ($_POST['ReportType'] 812 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 813 echo '<div>'; 814 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 815 echo '<input type="hidden" name="ReportType" value="'.$_POST['ReportType'].'" />'; 816 echo '<input type="hidden" name="DateType" value="'.$_POST['DateType'].'" />'; 817 echo '<input type="hidden" name="FromDate" value="'.$_POST['FromDate'].'" />'; 818 echo '<input type="hidden" name="ToDate" value="'.$_POST['ToDate'].'" />'; 819 echo '<input type="hidden" name="PartNumberOp" value="'.$_POST['PartNumberOp'].'" />'; 820 echo '<input type="hidden" name="PartNumber" value="'.$_POST['PartNumber'].'" />'; 821 echo '<input type="hidden" name="SupplierIdOp" value="'.$_POST['SupplierIdOp'].'" />'; 822 echo '<input type="hidden" name="SupplierId" value="'.$_POST['SupplierId'].'" />'; 823 echo '<input type="hidden" name="SupplierNameOp" value="'.$_POST['SupplierNameOp'].'" />'; 824 echo '<input type="hidden" name="SupplierName" value="'.$_POST['SupplierName'].'" />'; 825 echo '<input type="hidden" name="OrderNo" value="'.$_POST['OrderNo'].'" />'; 826 echo '<input type="hidden" name="LineStatus" value="'.$_POST['LineStatus'].'" />'; 827 echo '<input type="hidden" name="Category" value="'.$_POST['Category'].'" />'; 828 echo '<input type="hidden" name="SortBy" value="'.$_POST['SortBy'].'" />'; 829 echo '<input type="hidden" name="SummaryType" value="'.$_POST['SummaryType'].'" />'; 830 echo '<br /><div class="centre"><input type="submit" name="submitcsv" value="' . _('Export as csv file') . '" /></div>'; 831 echo '</div> 832 </form>'; 833 } // End of if inputerror != 1 834} // End of function submit() 835 836//####_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT_SUBMIT#### 837function submitcsv( $PartNumber, 838 $PartNumberOp, 839 $SupplierId, 840 $SupplierIdOp, 841 $SupplierName, 842 $SupplierNameOp, 843 $SaveSummaryType) { 844 845 //initialize no input errors 846 $InputError = 0; 847 848 /* actions to take once the user has clicked the submit button 849 ie the page has called itself with some user input */ 850 851 //first off validate inputs sensible 852 853 if (!Is_Date($_POST['FromDate'])) { 854 $InputError = 1; 855 prnMsg(_('Invalid From Date'),'error'); 856 } 857 if (!Is_Date($_POST['ToDate'])) { 858 $InputError = 1; 859 prnMsg(_('Invalid To Date'),'error'); 860 } 861 862 # Add more to WHERE statement, if user entered something for the part number,supplierid, name 863 $WherePart = ' '; 864 if (mb_strlen($PartNumber) > 0 && $PartNumberOp == 'LIKE') { 865 $PartNumber = $PartNumber . '%'; 866 } else { 867 $PartNumberOp = '='; 868 } 869 if (mb_strlen($PartNumber) > 0) { 870 $WherePart = " AND purchorderdetails.itemcode " . $PartNumberOp . " '" . $PartNumber . "' "; 871 } else { 872 $WherePart=' '; 873 } 874 875 $WhereSupplierID = ' '; 876 if ($SupplierIdOp == 'LIKE') { 877 $SupplierId = $SupplierId . '%'; 878 } else { 879 $SupplierIdOp = '='; 880 } 881 if (mb_strlen($SupplierId) > 0) { 882 $WhereSupplierID = " AND purchorders.supplierno " . $SupplierIdOp . " '" . $SupplierId . "' "; 883 } else { 884 $WhereSupplierID=' '; 885 } 886 887 $WhereSupplierName = ' '; 888 if (mb_strlen($SupplierName) > 0 && $SupplierNameOp == 'LIKE') { 889 $SupplierName = $SupplierName . '%'; 890 } else { 891 $SupplierNameOp = '='; 892 } 893 if (mb_strlen($SupplierName) > 0) { 894 $WhereSupplierName = " AND suppliers.suppname " . $SupplierNameOp . " '" . $SupplierName . "' "; 895 } else { 896 $WhereSupplierName=' '; 897 } 898 899 if (mb_strlen($_POST['OrderNo']) > 0) { 900 $WhereOrderNo = " AND purchorderdetails.orderno = '" . $_POST['OrderNo'] . "' "; 901 } else { 902 $WhereOrderNo=' '; 903 } 904 905 $WhereLineStatus = ' '; 906 # Had to use IF statement instead of comparing 'linestatus' to $_POST['LineStatus'] 907 #in WHERE clause because the WHERE clause didn't recognize 908 # that had used the IF statement to create a field called linestatus 909 if ($_POST['LineStatus'] != 'All') { 910 if ($_POST['DateType'] == 'Order') { 911 $WhereLineStatus = " AND IF(purchorderdetails.quantityord = purchorderdetails.qtyinvoiced || 912 purchorderdetails.completed = 1,'Completed','Open') = '" . $_POST['LineStatus'] . "'"; 913 } else { 914 $WhereLineStatus = " AND IF(grns.qtyrecd - grns.quantityinv <> 0,'Open','Completed') = '" 915 . $_POST['LineStatus'] . "'"; 916 } 917 } 918 919 920 $WhereCategory = ' '; 921 if ($_POST['Category'] != 'All') { 922 $WhereCategory = " AND stockmaster.categoryid = '" . $_POST['Category'] . "'"; 923 } 924 925 if ($InputError !=1) { 926 $FromDate = FormatDateForSQL($_POST['FromDate']); 927 $ToDate = FormatDateForSQL($_POST['ToDate']); 928 if ($_POST['ReportType'] == 'Detail') { 929 if ($_POST['DateType'] == 'Order') { 930 $sql = "SELECT purchorderdetails.orderno, 931 purchorderdetails.itemcode, 932 purchorderdetails.deliverydate, 933 purchorders.supplierno, 934 purchorders.orddate, 935 purchorderdetails.quantityrecd, 936 purchorderdetails.quantityord, 937 purchorderdetails.qtyinvoiced, 938 (purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 939 (purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 940 IF(purchorderdetails.quantityord = purchorderdetails.qtyinvoiced || 941 purchorderdetails.completed = 1,'Completed','Open') as linestatus, 942 suppliers.suppname, 943 stockmaster.decimalplaces, 944 stockmaster.description 945 FROM purchorderdetails 946 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 947 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 948 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 949 WHERE purchorders.orddate >='$FromDate' 950 AND purchorders.orddate <='$ToDate' 951 $WherePart 952 $WhereSupplierID 953 $WhereSupplierName 954 $WhereOrderNo 955 $WhereLineStatus 956 $WhereCategory 957 ORDER BY " . $_POST['SortBy']; 958 } else { 959 // Selects by delivery date from grns 960 $sql = "SELECT purchorderdetails.orderno, 961 purchorderdetails.itemcode, 962 grns.deliverydate, 963 purchorders.supplierno, 964 purchorders.orddate, 965 purchorderdetails.quantityord as quantityrecd, 966 grns.qtyrecd as quantityord, 967 grns.quantityinv as qtyinvoiced, 968 (grns.qtyrecd * purchorderdetails.unitprice) as extprice, 969 (grns.qtyrecd * grns.stdcostunit) as extcost, 970 IF(grns.qtyrecd - grns.quantityinv <> 0,'Open','Completed') as linestatus, 971 suppliers.suppname, 972 stockmaster.decimalplaces, 973 stockmaster.description 974 FROM grns 975 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 976 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 977 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 978 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 979 WHERE grns.deliverydate >='$FromDate' 980 AND grns.deliverydate <='$ToDate' 981 $WherePart 982 $WhereSupplierID 983 $WhereSupplierName 984 $WhereOrderNo 985 $WhereLineStatus 986 $WhereCategory 987 ORDER BY " . $_POST['SortBy']; 988 } 989 } else { 990 // sql for Summary report 991 $orderby = $_POST['SummaryType']; 992 // The following is because the 'extprice' summary is a special case - with the other 993 // summaries, you group and order on the same field; with 'extprice', you are actually 994 // grouping on the stkcode and ordering by extprice descending 995 if ($_POST['SummaryType'] == 'extprice') { 996 $_POST['SummaryType'] = 'itemcode'; 997 $orderby = 'extprice DESC'; 998 } 999 if ($_POST['DateType'] == 'Order') { 1000 if ($_POST['SummaryType'] == 'extprice' || $_POST['SummaryType'] == 'itemcode') { 1001 $sql = "SELECT purchorderdetails.itemcode, 1002 SUM(purchorderdetails.quantityord) as quantityord, 1003 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 1004 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 1005 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 1006 stockmaster.decimalplaces, 1007 stockmaster.description 1008 FROM purchorderdetails 1009 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1010 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1011 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1012 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1013 WHERE purchorders.orddate >='$FromDate' 1014 AND purchorders.orddate <='$ToDate' 1015 $WherePart 1016 $WhereSupplierID 1017 $WhereSupplierName 1018 $WhereOrderNo 1019 $WhereLineStatus 1020 $WhereCategory 1021 GROUP BY " . $_POST['SummaryType'] . 1022 ",stockmaster.decimalplaces, 1023 stockmaster.description 1024 ORDER BY " . $orderby; 1025 } elseif ($_POST['SummaryType'] == 'orderno') { 1026 $sql = "SELECT purchorderdetails.orderno, 1027 purchorders.supplierno, 1028 SUM(purchorderdetails.quantityord) as quantityord, 1029 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 1030 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 1031 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 1032 suppliers.suppname 1033 FROM purchorderdetails 1034 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1035 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1036 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1037 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1038 WHERE purchorders.orddate >='$FromDate' 1039 AND purchorders.orddate <='$ToDate' 1040 $WherePart 1041 $WhereSupplierID 1042 $WhereSupplierName 1043 $WhereOrderNo 1044 $WhereLineStatus 1045 $WhereCategory 1046 GROUP BY " . $_POST['SummaryType'] . 1047 ",purchorders.supplierno, 1048 suppliers.suppname 1049 ORDER BY " . $orderby; 1050 } elseif ($_POST['SummaryType'] == 'supplierno' || $_POST['SummaryType'] == 'suppname,suppliers.supplierid') { 1051 $sql = "SELECT purchorders.supplierno, 1052 SUM(purchorderdetails.quantityord) as quantityord, 1053 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 1054 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 1055 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 1056 suppliers.suppname 1057 FROM purchorderdetails 1058 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1059 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1060 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1061 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1062 WHERE purchorders.orddate >='$FromDate' 1063 AND purchorders.orddate <='$ToDate' 1064 $WherePart 1065 $WhereSupplierID 1066 $WhereSupplierName 1067 $WhereOrderNo 1068 $WhereLineStatus 1069 $WhereCategory 1070 GROUP BY " . $_POST['SummaryType'] . 1071 ",purchorders.supplierno, 1072 suppliers.suppname 1073 ORDER BY " . $orderby; 1074 } elseif ($_POST['SummaryType'] == 'month') { 1075 $sql = "SELECT EXTRACT(YEAR_MONTH from purchorders.orddate) as month, 1076 CONCAT(MONTHNAME(purchorders.orddate),' ',YEAR(purchorders.orddate)) as monthname, 1077 SUM(purchorderdetails.quantityord) as quantityord, 1078 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 1079 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 1080 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost 1081 FROM purchorderdetails 1082 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1083 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1084 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1085 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1086 WHERE purchorders.orddate >='$FromDate' 1087 AND purchorders.orddate <='$ToDate' 1088 $WherePart 1089 $WhereSupplierID 1090 $WhereSupplierName 1091 $WhereOrderNo 1092 $WhereLineStatus 1093 $WhereCategory 1094 GROUP BY " . $_POST['SummaryType'] . 1095 ", monthname 1096 ORDER BY " . $orderby; 1097 } elseif ($_POST['SummaryType'] == 'categoryid') { 1098 $sql = "SELECT SUM(purchorderdetails.quantityord) as quantityord, 1099 SUM(purchorderdetails.qtyinvoiced) as qtyinvoiced, 1100 SUM(purchorderdetails.quantityord * purchorderdetails.unitprice) as extprice, 1101 SUM(purchorderdetails.quantityord * purchorderdetails.stdcostunit) as extcost, 1102 stockmaster.categoryid, 1103 stockcategory.categorydescription 1104 FROM purchorderdetails 1105 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1106 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1107 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1108 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1109 WHERE purchorders.orddate >='$FromDate' 1110 AND purchorders.orddate <='$ToDate' 1111 $WherePart 1112 $WhereSupplierID 1113 $WhereSupplierName 1114 $WhereOrderNo 1115 $WhereLineStatus 1116 $WhereCategory 1117 GROUP BY " . $_POST['SummaryType'] . 1118 ", categorydescription 1119 ORDER BY " . $orderby; 1120 } 1121 } else { 1122 // Selects by delivery date from grns 1123 if ($_POST['SummaryType'] == 'extprice' || $_POST['SummaryType'] == 'itemcode') { 1124 $sql = "SELECT purchorderdetails.itemcode, 1125 SUM(grns.qtyrecd) as quantityord, 1126 SUM(grns.quantityinv) as qtyinvoiced, 1127 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 1128 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 1129 stockmaster.description 1130 FROM grns 1131 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 1132 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1133 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1134 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1135 LEFT JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1136 WHERE grns.deliverydate >='$FromDate' 1137 AND grns.deliverydate <='$ToDate' 1138 $WherePart 1139 $WhereSupplierID 1140 $WhereSupplierName 1141 $WhereOrderNo 1142 $WhereLineStatus 1143 $WhereCategory 1144 GROUP BY " . $_POST['SummaryType'] . 1145 ", stockmaster.description 1146 ORDER BY " . $orderby; 1147 } elseif ($_POST['SummaryType'] == 'orderno') { 1148 $sql = "SELECT purchorderdetails.orderno, 1149 purchorders.supplierno, 1150 SUM(grns.qtyrecd) as quantityord, 1151 SUM(grns.quantityinv) as qtyinvoiced, 1152 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 1153 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 1154 suppliers.suppname 1155 FROM grns 1156 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 1157 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1158 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1159 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1160 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1161 WHERE grns.deliverydate >='$FromDate' 1162 AND grns.deliverydate <='$ToDate' 1163 $WherePart 1164 $WhereSupplierID 1165 $WhereSupplierName 1166 $WhereOrderNo 1167 $WhereLineStatus 1168 $WhereCategory 1169 GROUP BY " . $_POST['SummaryType'] . 1170 ", purchorders.supplierno, 1171 suppliers.suppname 1172 ORDER BY " . $orderby; 1173 } elseif ($_POST['SummaryType'] == 'supplierno' || $_POST['SummaryType'] == 'suppname,suppliers.supplierid') { 1174 $sql = "SELECT purchorders.supplierno, 1175 SUM(grns.qtyrecd) as quantityord, 1176 SUM(grns.quantityinv) as qtyinvoiced, 1177 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 1178 SUM(grns.qtyrecd * grns.stdcostunit) as extcost, 1179 suppliers.suppname 1180 FROM grns 1181 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 1182 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1183 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1184 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1185 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1186 WHERE grns.deliverydate >='$FromDate' 1187 AND grns.deliverydate <='$ToDate' 1188 $WherePart 1189 $WhereSupplierID 1190 $WhereSupplierName 1191 $WhereOrderNo 1192 $WhereLineStatus 1193 $WhereCategory 1194 GROUP BY " . $_POST['SummaryType'] . 1195 ", purchorders.supplierno, 1196 suppliers.suppname 1197 ORDER BY " . $orderby; 1198 } elseif ($_POST['SummaryType'] == 'month') { 1199 $sql = "SELECT EXTRACT(YEAR_MONTH from purchorders.orddate) as month, 1200 CONCAT(MONTHNAME(purchorders.orddate),' ',YEAR(purchorders.orddate)) as monthname, 1201 SUM(grns.qtyrecd) as quantityord, 1202 SUM(grns.quantityinv) as qtyinvoiced, 1203 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 1204 SUM(grns.qtyrecd * grns.stdcostunit) as extcost 1205 FROM grns 1206 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 1207 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1208 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1209 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1210 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1211 WHERE grns.deliverydate >='$FromDate' 1212 AND grns.deliverydate <='$ToDate' 1213 $WherePart 1214 $WhereSupplierID 1215 $WhereSupplierName 1216 $WhereOrderNo 1217 $WhereLineStatus 1218 $WhereCategory 1219 GROUP BY " . $_POST['SummaryType'] . 1220 ",monthname 1221 ORDER BY " . $orderby; 1222 } elseif ($_POST['SummaryType'] == 'categoryid') { 1223 $sql = "SELECT stockmaster.categoryid, 1224 stockcategory.categorydescription, 1225 SUM(grns.qtyrecd) as quantityord, 1226 SUM(grns.quantityinv) as qtyinvoiced, 1227 SUM(grns.qtyrecd * purchorderdetails.unitprice) as extprice, 1228 SUM(grns.qtyrecd * grns.stdcostunit) as extcost 1229 FROM grns 1230 LEFT JOIN purchorderdetails ON grns.podetailitem = purchorderdetails.podetailitem 1231 INNER JOIN purchorders ON purchorders.orderno=purchorderdetails.orderno 1232 INNER JOIN suppliers ON purchorders.supplierno = suppliers.supplierid 1233 LEFT JOIN stockmaster ON purchorderdetails.itemcode = stockmaster.stockid 1234 INNER JOIN stockcategory ON stockcategory.categoryid = stockmaster.categoryid 1235 WHERE grns.deliverydate >='$FromDate' 1236 AND grns.deliverydate <='$ToDate' 1237 $WherePart 1238 $WhereSupplierID 1239 $WhereSupplierName 1240 $WhereOrderNo 1241 $WhereLineStatus 1242 $WhereCategory 1243 GROUP BY " . $_POST['SummaryType'] . 1244 ",categorydescription 1245 ORDER BY " . $orderby; 1246 } 1247 } 1248 } // End of if ($_POST['ReportType'] 1249 //echo "<br/>$sql<br/>"; 1250 $ErrMsg = _('The SQL to find the parts selected failed with the message'); 1251 $result = DB_query($sql,$ErrMsg); 1252 $ctr = 0; 1253 $TotalQty = 0; 1254 $TotalExtCost = 0; 1255 $TotalExtPrice = 0; 1256 $TotalInvQty = 0; 1257 $FileName = $_SESSION['reports_dir'] .'/POReport.csv'; 1258 $FileHandle = fopen($FileName, 'w'); 1259 // Create array for summary type to display in header. Access it with $SaveSummaryType 1260 $Summary_Array['orderno'] = _('Order Number'); 1261 $Summary_Array['itemcode'] = _('Part Number'); 1262 $Summary_Array['extprice'] = _('Extended Price'); 1263 $Summary_Array['supplierno'] = _('Customer Number'); 1264 $Summary_Array['suppname'] = _('Customer Name'); 1265 $Summary_Array['month'] = _('Month'); 1266 $Summary_Array['categoryid'] = _('Stock Category'); 1267 1268 // Create array for sort for detail report to display in header 1269 $Detail_Array['purchorderdetails.orderno'] = _('Order Number'); 1270 $Detail_Array['purchorderdetails.itemcode'] = _('Part Number'); 1271 $Detail_Array['suppliers.supplierid,purchorderdetails.orderno'] = _('Supplier Number'); 1272 $Detail_Array['suppliers.suppname,suppliers.supplierid,purchorderdetails.orderno'] = _('Supplier Name'); 1273 1274 // Display Header info 1275 if ($_POST['ReportType'] == 'Summary') { 1276 $SortBy_Display = $Summary_Array[$SaveSummaryType]; 1277 } else { 1278 $SortBy_Display = $Detail_Array[$_POST['SortBy']]; 1279 } 1280 fprintf($FileHandle, '"'. _('Purchase Order Report') . '","' . $_POST['ReportType'] . ' '._('By').' '.$SortBy_Display ."\n"); 1281 fprintf($FileHandle, '"'. _('Date Type') . '","' . $_POST['DateType'] . '"'. "\n"); 1282 fprintf($FileHandle, '"'. _('Date Range') . '","' . $_POST['FromDate'] . ' ' . _('To') . ' ' . $_POST['ToDate'] . '"'."\n"); 1283 if (mb_strlen(trim($PartNumber)) > 0) { 1284 fprintf($FileHandle, '"'. _('Part Number') . '","' . $_POST['PartNumberOp'] . ' ' . $_POST['PartNumber'] . '"'."\n"); 1285 } 1286 if (mb_strlen(trim($_POST['SupplierId'])) > 0) { 1287 fprintf($FileHandle, '"'. _('Supplier Number') . '","' . $_POST['SupplierIdOp'] . ' ' . $_POST['SupplierId'] . '"'."\n"); 1288 } 1289 if (mb_strlen(trim($_POST['SupplierName'])) > 0) { 1290 fprintf($FileHandle, '"'. _('Supplier Name') . '","' . $_POST['SupplierNameOp'] . ' ' . $_POST['SupplierName'] . '"'."\n"); 1291 } 1292 fprintf($FileHandle, '"'._('Line Item Status') . '","' . $_POST['LineStatus'] . '"'."\n"); 1293 fprintf($FileHandle, '"'. _('Stock Category') . '","' . $_POST['Category'] . '"'."\n"); 1294 1295 if ($_POST['ReportType'] == 'Detail') { 1296 if ($_POST['DateType'] == 'Order') { 1297 fprintf($FileHandle, '"%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s"'."\n", 1298 _('Order No'), 1299 _('Part Number'), 1300 _('Order Date'), 1301 _('Supplier No'), 1302 _('Supplier Name'), 1303 _('Order Qty'), 1304 _('Qty Received'), 1305 _('Extended Cost'), 1306 _('Extended Price'), 1307 _('Invoiced Qty'), 1308 _('Line Status'), 1309 _('Item Due'), 1310 _('Part Description')); 1311 $linectr = 0; 1312 while ($myrow = DB_fetch_array($result)) { 1313 $linectr++; 1314 // Detail for both DateType of Order 1315 fprintf($FileHandle, '"%s","%s","%s","%s","%s",%s,%s,%s,%s,%s,"%s","%s","%s"'."\n", 1316 $myrow['orderno'], 1317 $myrow['itemcode'], 1318 ConvertSQLDate($myrow['orddate']), 1319 $myrow['supplierno'], 1320 str_replace(',',' ',$myrow['suppname']), 1321 round($myrow['quantityord'],$myrow['decimalplaces']), 1322 round($myrow['quantityrecd'],$myrow['decimalplaces']), 1323 round($myrow['extcost'],2), 1324 round($myrow['extprice'],2), 1325 round($myrow['qtyinvoiced'],$myrow['decimalplaces']), 1326 $myrow['linestatus'], 1327 ConvertSQLDate($myrow['deliverydate']), 1328 str_replace(',',' ',$myrow['description'])); 1329 $LastDecimalPlaces = $myrow['decimalplaces']; 1330 $TotalQty += $myrow['quantityord']; 1331 $TotalExtCost += $myrow['extcost']; 1332 $TotalExtPrice += $myrow['extprice']; 1333 $TotalInvQty += $myrow['qtyinvoiced']; 1334 } //END WHILE LIST LOOP 1335 // Print totals 1336 fprintf($FileHandle, '"%s","%s","%s","%s","%s",%s,%s,%s,%s,"%s","%s"'."\n", 1337 'Totals', 1338 _('Lines - ') . $linectr, 1339 ' ', 1340 ' ', 1341 ' ', 1342 round($TotalQty,2), 1343 round($TotalExtCost,2), 1344 round($TotalExtPrice,2), 1345 round($TotalInvQty,2), 1346 ' ', 1347 ' '); 1348 } else { 1349 // Header for Date Type of Delivery Date 1350 fprintf($FileHandle, '"%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s"'."\n", 1351 _('Order No'), 1352 _('Part Number'), 1353 _('Order Date'), 1354 _('Supplier No'), 1355 _('Supplier Name'), 1356 _('Order Qty'), 1357 _('Received'), 1358 _('Extended Cost'), 1359 _('Extended Price'), 1360 _('Invoiced Qty'), 1361 _('Line Status'), 1362 _('Delivered'), 1363 _('Part Description')); 1364 $linectr = 0; 1365 while ($myrow = DB_fetch_array($result)) { 1366 $linectr++; 1367 // Detail for both DateType of Ship 1368 // In sql, had to alias grns.qtyrecd as quantityord so could use same name here 1369 fprintf($FileHandle, '"%s","%s","%s","%s","%s",%s,%s,%s,%s,%s,"%s","%s","%s"'."\n", 1370 $myrow['orderno'], 1371 $myrow['itemcode'], 1372 ConvertSQLDate($myrow['orddate']), 1373 $myrow['supplierno'], 1374 str_replace(',',' ',$myrow['suppname']), 1375 round($myrow['quantityrecd'],$myrow['decimalplaces']), 1376 round($myrow['quantityord'],$myrow['decimalplaces']), 1377 round($myrow['extcost'],2), 1378 round($myrow['extprice'],2), 1379 round($myrow['qtyinvoiced'],$myrow['decimalplaces']), 1380 $myrow['linestatus'], 1381 ConvertSQLDate($myrow['deliverydate']), 1382 str_replace(',',' ',$myrow['description'])); 1383 $LastDecimalPlaces = $myrow['decimalplaces']; 1384 $TotalQty += $myrow['quantityord']; 1385 $TotalExtCost += $myrow['extcost']; 1386 $TotalExtPrice += $myrow['extprice']; 1387 $TotalInvQty += $myrow['qtyinvoiced']; 1388 } //END WHILE LIST LOOP 1389 // Print totals 1390 fprintf($FileHandle, '"%s","%s","%s","%s","%s",%s,%s,%s,%s,"%s","%s"'."\n", 1391 'Totals', 1392 _('Lines - ') . $linectr, 1393 ' ', 1394 ' ', 1395 ' ', 1396 round($TotalQty,$LastDecimalPlaces), 1397 round($TotalExtCost,2), 1398 round($TotalExtPrice,2), 1399 round($TotalInvQty,$LastDecimalPlaces), 1400 " ", 1401 " "); 1402 } 1403 } else { 1404 // Print summary stuff 1405 $summarytype = $_POST['SummaryType']; 1406 // For SummaryType 'suppname' had to add supplierid to it for the GROUP BY in the sql, 1407 // but have to take it away for $myrow[$summarytype] to be valid 1408 // Set up description based on the Summary Type 1409 if ($summarytype == 'suppname,suppliers.supplierid') { 1410 $summarytype = 'suppname'; 1411 $description = 'supplierno'; 1412 $summaryheader = _('Supplier Name'); 1413 $descriptionheader = _('Supplier Number'); 1414 } 1415 if ($summarytype == 'itemcode' || $summarytype == 'extprice') { 1416 $description = 'description'; 1417 $summaryheader = _('Part Number'); 1418 $descriptionheader = _('Part Description'); 1419 } 1420 if ($summarytype == 'supplierno') { 1421 $description = 'suppname'; 1422 $summaryheader = _('Supplier Number'); 1423 $descriptionheader = _('Supplier Name'); 1424 } 1425 if ($summarytype == 'orderno') { 1426 $description = 'supplierno'; 1427 $summaryheader = _('Order Number'); 1428 $descriptionheader = _('Supplier Number'); 1429 } 1430 if ($summarytype == 'categoryid') { 1431 $description = 'categorydescription'; 1432 $summaryheader = _('Stock Category'); 1433 $descriptionheader = _('Category Description'); 1434 } 1435 $summarydesc = $summaryheader; 1436 if ($orderby == 'extprice DESC') { 1437 $summarydesc = _('Extended Price'); 1438 } 1439 if ($summarytype == 'month') { 1440 $description = 'monthname'; 1441 $summaryheader = _('Month'); 1442 $descriptionheader = _('Month'); 1443 } 1444 fprintf($FileHandle, '"%s","%s","%s","%s","%s","%s"'."\n", 1445 _($summaryheader), 1446 _($descriptionheader), 1447 _('Quantity'), 1448 _('Extended Cost'), 1449 _('Extended Price'), 1450 _('Invoiced Qty')); 1451 1452 $suppname = ' '; 1453 $linectr = 0; 1454 while ($myrow = DB_fetch_array($result)) { 1455 $linectr++; 1456 if ($summarytype == 'orderno') { 1457 $suppname = $myrow['suppname']; 1458 } 1459 fprintf($FileHandle, '"%s","%s",%s,%s,%s,%s,"%s"'."\n", 1460 $myrow[$summarytype], 1461 $myrow[$description], 1462 round($myrow['quantityord'],$myrow['decimalplaces']), 1463 round($myrow['extcost'],2), 1464 round($myrow['extprice'],2), 1465 round($myrow['qtyinvoiced'],$myrow['decimalplaces']), 1466 $suppname); 1467 print '<br/>'; 1468 $LastDecimalPlaces = $myrow['decimalplaces']; 1469 $TotalQty += $myrow['quantityord']; 1470 $TotalExtCost += $myrow['extcost']; 1471 $TotalExtPrice += $myrow['extprice']; 1472 $TotalInvQty += $myrow['qtyinvoiced']; 1473 } //END WHILE LIST LOOP 1474 // Print totals 1475 fprintf($FileHandle, '"%s","%s",%s,%s,%s,%s,"%s"'."\n", 1476 'Totals', 1477 _('Lines - ') . $linectr, 1478 round($TotalQty,$LastDecimalPlaces), 1479 round($TotalExtCost,2), 1480 round($TotalExtPrice,2), 1481 round($TotalInvQty,$LastDecimalPlaces), 1482 ' '); 1483 } // End of if ($_POST['ReportType'] 1484 fclose($FileHandle); 1485 echo '<div class="centre"><p>' . _('The report has been exported as a csv file.') . '</p>'; 1486 echo '<p><a href="' . $FileName . '">' . _('click here') . '</a> ' . _('to view the file') . '</div></p>'; 1487 1488 } // End of if inputerror != 1 1489} // End of function submitcvs() 1490 1491 1492function display() //####DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_DISPLAY_##### 1493{ 1494// Display form fields. This function is called the first time 1495// the page is called. 1496 1497 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 1498 echo '<div>'; 1499 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" /> 1500 <table class="selection"> 1501 <tr> 1502 <td>' . _('Report Type') . ':</td> 1503 <td><select required="required" autofocus="autofocus" name="ReportType"> 1504 <option selected="selected" value="Detail">' . _('Detail') . '</option> 1505 <option value="Summary">' . _('Summary') . '</option> 1506 </select></td> 1507 <td> </td> 1508 </tr> 1509 <tr> 1510 <td>' . _('Date Type') . ':</td> 1511 <td><select required="required" name="DateType"> 1512 <option selected="selected" value="Order">' . _('Order Date') . '</option> 1513 <option value="Delivery">' . _('Delivery Date') . '</option> 1514 </select></td> 1515 <td> </td> 1516 </tr> 1517 <tr> 1518 <td>' . _('Date Range') . ':</td> 1519 <td><input type="text" required="required" class="date" name="FromDate" size="11" maxlength="10" value="' . $_POST['FromDate'] .'" /> ' . _('To') . ': 1520 <input type="text" required="required" class="date" name="ToDate" size="11" maxlength="10" value="' . $_POST['ToDate'] . '" /></td> 1521 </tr> 1522 <tr> 1523 <td>' . _('Part Number') . ':</td> 1524 <td><select name="PartNumberOp"> 1525 <option selected="selected" value="Equals">' . _('Equals') . '</option> 1526 <option value="LIKE">' . _('Begins With') . '</option> 1527 </select> 1528 1529 <input type="text" name="PartNumber" size="20" maxlength="20" value="'; 1530 if (isset($_POST['PartNumber'])) { 1531 echo $_POST['PartNumber'] . '" /></td> 1532 </tr>'; 1533 } else { 1534 echo '" /></td> 1535 </tr>'; 1536 } 1537 1538 echo '<tr> 1539 <td>' . _('Supplier Number') . ':</td> 1540 <td><select name="SupplierIdOp"> 1541 <option selected="selected" value="Equals">' . _('Equals') . '</option> 1542 <option value="LIKE">' . _('Begins With') . '</option> 1543 </select> 1544 1545 <input type="text" name="SupplierId" size="10" maxlength="10" value="'; 1546 if (isset($_POST['SupplierId'])) { 1547 echo $_POST['SupplierId'] . '" /></td> 1548 </tr>'; 1549 } else { 1550 echo '" /></td> 1551 </tr>'; 1552 } 1553 1554 echo '<tr> 1555 <td>' . _('Supplier Name') . ':</td> 1556 <td><select name="SupplierNameOp"> 1557 <option selected="selected" value="LIKE">' . _('Begins With') . '</option> 1558 <option value="Equals">' . _('Equals') . '</option> 1559 </select> 1560 1561 <input type="text" name="SupplierName" size="30" maxlength="30" value="'; 1562 if (isset($_POST['SupplierName'])) { 1563 echo $_POST['SupplierName'] . '" /></td> 1564 </tr>'; 1565 } else { 1566 echo '" /></td> 1567 </tr>'; 1568 } 1569 1570 echo '<tr> 1571 <td>' . _('Order Number') . ':</td> 1572 <td>' . _('Equals').': <input type="text" name="OrderNo" size="10" maxlength="10" value="'; 1573 if (isset($_POST['OrderNo'])) { 1574 echo $_POST['OrderNo'] . '" /></td> 1575 </tr>'; 1576 } else { 1577 echo '" /></td> 1578 </tr>'; 1579 } 1580 1581 echo '<tr> 1582 <td>' . _('Line Item Status') . ':</td> 1583 <td><select name="LineStatus"> 1584 <option selected="selected" value="All">' . _('All') . '</option> 1585 <option value="Completed">' . _('Completed') . '</option> 1586 <option value="Open">' . _('Not Completed') . '</option> 1587 </select></td> 1588 <td> </td> 1589 </tr> 1590 <tr> 1591 <td>' . _('Stock Categories') . ':</td> 1592 <td><select name="Category">'; 1593 $sql="SELECT categoryid, categorydescription FROM stockcategory"; 1594 $CategoryResult= DB_query($sql); 1595 echo '<option selected="selected" value="All">' . _('All Categories') . '</option>'; 1596 While ($myrow = DB_fetch_array($CategoryResult)){ 1597 echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>'; 1598 } 1599 echo '</select></td> 1600 </tr> 1601 <tr> 1602 <td> </td> 1603 </tr> 1604 <tr> 1605 <td>' . _('Sort By') . ':</td> 1606 <td><select name="SortBy"> 1607 <option selected="selected" value="purchorderdetails.orderno">' . _('Order Number') . '</option> 1608 <option value="purchorderdetails.itemcode">' . _('Part Number') . '</option> 1609 <option value="suppliers.supplierid,purchorderdetails.orderno">' . _('Supplier Number') . '</option> 1610 <option value="suppliers.suppname,suppliers.supplierid,purchorderdetails.orderno">' . _('Supplier Name') . '</option> 1611 </select></td> 1612 <td> </td> 1613 </tr> 1614 <tr> 1615 <td> </td> 1616 </tr> 1617 <tr> 1618 <td>' . _('Summary Type') . ':</td> 1619 <td><select name="SummaryType"> 1620 <option selected="selected" value="orderno">' . _('Order Number') . '</option> 1621 <option value="itemcode">' . _('Part Number') . '</option> 1622 <option value="extprice">' . _('Extended Price') . '</option> 1623 <option value="supplierno">' . _('Supplier Number') . '</option> 1624 <option value="suppname">' . _('Supplier Name') . '</option> 1625 <option value="month">' . _('Month') . '</option> 1626 <option value="categoryid">' . _('Stock Category') . '</option> 1627 </select></td> 1628 <td> </td> 1629 </tr> 1630 <tr> 1631 <td> </td> 1632 </tr> 1633 <tr> 1634 <td colspan="4"><div class="centre"><input type="submit" name="submit" value="' . _('Run Inquiry') . '" /></div></td> 1635 </tr> 1636 <tr> 1637 <td colspan="4"><div class="centre"><input type="submit" name="submitcsv" value="' . _('Export as csv file') . '" /></div></td> 1638 </tr> 1639 </table> 1640 <br/> 1641 </div> 1642 </form>'; 1643 1644} // End of function display() 1645 1646 1647include('includes/footer.php'); 1648?> 1649
