Care2007/modules/weberp/PO_SelectOSPurchOrder.php

   1 <?php
   2
   3 /* $Revision: 1.12 $ */
   4
   5 $PageSecurity = 2;
   6
   7 include('includes/session.inc');
   8
   9 $title = _('Search Outstanding Purchase Orders');
  10
  11 include('includes/header.inc');
  12
  13
  14 if (isset($_GET['SelectedStockItem'])){
  15         $SelectedStockItem=trim($_GET['SelectedStockItem']);
  16 } elseif (isset($_POST['SelectedStockItem'])){
  17         $SelectedStockItem=trim($_POST['SelectedStockItem']);
  18 }
  19
  20 if (isset($_GET['OrderNumber'])){
  21         $OrderNumber=trim($_GET['OrderNumber']);
  22 } elseif (isset($_POST['OrderNumber'])){
  23         $OrderNumber=trim($_POST['OrderNumber']);
  24 }
  25
  26 if (isset($_GET['SelectedSupplier'])){
  27         $SelectedSupplier=trim($_GET['SelectedSupplier']);
  28 } elseif (isset($_POST['SelectedSupplier'])){
  29         $SelectedSupplier=trim($_POST['SelectedSupplier']);
  30 }
  31
  32 echo '<FORM ACTION="' . $_SERVER['PHP_SELF'] . '?' . SID . '" METHOD=POST>';
  33
  34
  35 If ($_POST['ResetPart']){
  36      unset($SelectedStockItem);
  37 }
  38
  39 If (isset($OrderNumber) && $OrderNumber!='') {
  40         if (!is_numeric($OrderNumber)){
  41                   echo '<BR><B>' . _('The Order Number entered') . ' <U>' . _('MUST') . '</U> ' . _('be numeric') . '.</B><BR>';
  42                   unset ($OrderNumber);
  43         } else {
  44                 echo _('Order Number') . ' - ' . $OrderNumber;
  45         }
  46 } else {
  47         If ($SelectedSupplier) {
  48                 echo _('For supplier') . ': ' . $SelectedSupplier . ' ' . _('and') . ' ';
  49                 echo '<input type=hidden name="SelectedSupplier" value=' . $SelectedSupplier . '>';
  50         }
  51         If ($SelectedStockItem) {
  52                  echo _('for the part') . ': ' . $SelectedStockItem . ' ' . _('and') . ' <input type=hidden name="SelectedStockItem" value="' . $SelectedStockItem . '">';
  53         }
  54 }
  55
  56 if ($_POST['SearchParts']){
  57
  58         If ($_POST['Keywords'] AND $_POST['StockCode']) {
  59                 echo _('Stock description keywords have been used in preference to the Stock code extract entered') . '.';
  60         }
  61         If ($_POST['Keywords']) {
  62                 //insert wildcard characters in spaces
  63                 $i=0;
  64                 $SearchString = '%';
  65                 while (strpos($_POST['Keywords'], ' ', $i)) {
  66                         $wrdlen=strpos($_POST['Keywords'],' ',$i) - $i;
  67                         $SearchString=$SearchString . substr($_POST['Keywords'],$i,$wrdlen) . '%';
  68                         $i=strpos($_POST['Keywords'],' ',$i) +1;
  69                 }
  70                 $SearchString = $SearchString. substr($_POST['Keywords'],$i).'%';
  71
  72                 $SQL = "SELECT stockmaster.stockid,
  73                                 stockmaster.description,
  74                                 SUM(locstock.quantity) AS qoh,
  75                                 stockmaster.units,
  76                                 SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS qord
  77                         FROM stockmaster INNER JOIN locstock
  78                                 ON stockmaster.stockid = locstock.stockid
  79                                 INNER JOIN purchorderdetails
  80                                         ON stockmaster.stockid=purchorderdetails.itemcode
  81                         WHERE purchorderdetails.completed=0
  82                         AND stockmaster.description " . LIKE . " '$SearchString'
  83                         AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
  84                         GROUP BY stockmaster.stockid,
  85                                 stockmaster.description,
  86                                 stockmaster.units
  87                         ORDER BY stockmaster.stockid";
  88
  89
  90          } elseif ($_POST['StockCode']){
  91                 $SQL = "SELECT stockmaster.stockid,
  92                                 stockmaster.description,
  93                                 SUM(locstock.quantity) AS qoh,
  94                                 SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS qord,
  95                                 stockmaster.units
  96                         FROM stockmaster INNER JOIN locstock
  97                                 ON stockmaster.stockid = locstock.stockid
  98                                 INNER JOIN purchorderdetails
  99                                         ON stockmaster.stockid=purchorderdetails.itemcode
 100                         WHERE purchorderdetails.completed=0
 101                         AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
 102                         AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
 103                         GROUP BY stockmaster.stockid,
 104                                 stockmaster.description,
 105                                 stockmaster.units
 106                         ORDER BY stockmaster.stockid";
 107
 108          } elseif (!$_POST['StockCode'] AND !$_POST['Keywords']) {
 109                 $SQL = "SELECT stockmaster.stockid,
 110                                 stockmaster.description,
 111                                 SUM(locstock.quantity) AS qoh,
 112                                 stockmaster.units,
 113                                 SUM(purchorderdetails.quantityord-purchorderdetails.quantityrecd) AS qord
 114                         FROM stockmaster INNER JOIN locstock
 115                                 ON stockmaster.stockid = locstock.stockid
 116                                 INNER JOIN purchorderdetails
 117                                         ON stockmaster.stockid=purchorderdetails.itemcode
 118                         WHERE purchorderdetails.completed=0
 119                         AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
 120                         GROUP BY stockmaster.stockid,
 121                                 stockmaster.description,
 122                                 stockmaster.units
 123                         ORDER BY stockmaster.stockid";
 124          }
 125
 126         $ErrMsg = _('No stock items were returned by the SQL because');
 127         $DbgMsg = _('The SQL used to retrieve the searched parts was');
 128         $StockItemsResult = DB_query($SQL,$db, $ErrMsg, $DbgMsg);
 129 }
 130
 131
 132 /* Not appropriate really to restrict search by date since user may miss older ouststanding orders
 133         $OrdersAfterDate = Date("d/m/Y",Mktime(0,0,0,Date("m")-2,Date("d"),Date("Y")));
 134 */
 135
 136 if ($OrderNumber=='' OR !isset($OrderNumber)){
 137
 138         echo _('order number') . ': <INPUT type=text name="OrderNumber" MAXLENGTH =8 SIZE=9>  ' . _('Into Stock Location') . ':<SELECT name="StockLocation"> ';
 139         $sql = 'SELECT loccode, locationname FROM locations';
 140         $resultStkLocs = DB_query($sql,$db);
 141         while ($myrow=DB_fetch_array($resultStkLocs)){
 142                 if (isset($_POST['StockLocation'])){
 143                         if ($myrow['loccode'] == $_POST['StockLocation']){
 144                                 echo '<OPTION SELECTED Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
 145                         } else {
 146                                 echo '<OPTION Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
 147                         }
 148                 } elseif ($myrow['loccode']== $_SESSION['UserStockLocation']){
 149                         echo '<OPTION SELECTED Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
 150                 } else {
 151                         echo '<OPTION Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
 152                 }
 153         }
 154
 155         echo '</SELECT>  <INPUT TYPE=SUBMIT NAME="SearchOrders" VALUE="' . _('Search Purchase Orders') . '">';
 156         echo '&nbsp;&nbsp;<a href="' . $rootpath . '/PO_Header.php?' .SID . '&NewOrder=Yes">' . _('Add Purchase Order') . '</a>';
 157 }
 158
 159 $SQL='SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription';
 160 $result1 = DB_query($SQL,$db);
 161
 162 ?>
 163
 164 <HR>
 165 <FONT SIZE=1><?php echo _('To search for purchase orders for a specific part use the part selection facilities below'); ?> </FONT>
 166 <INPUT TYPE=SUBMIT NAME="SearchParts" VALUE="<?php echo _('Search Parts Now'); ?>">
 167 <INPUT TYPE=SUBMIT NAME="ResetPart" VALUE="<?php echo _('Show All'); ?>">
 168 <TABLE>
 169 <TR>
 170 <TD><FONT SIZE=1><?php echo _('Select a stock category'); ?>:</FONT>
 171 <SELECT NAME="StockCat">
 172 <?php
 173 while ($myrow1 = DB_fetch_array($result1)) {
 174         if ($myrow1['categoryid']==$_POST['StockCat']){
 175                 echo "<OPTION SELECTED VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
 176         } else {
 177                 echo "<OPTION VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
 178         }
 179 }
 180 ?>
 181 </SELECT>
 182 <TD><FONT SIZE=1><?php echo _('Enter text extracts in the'); ?>  <B><?php echo _('description'); ?></B>:</FONT></TD>
 183 <TD><INPUT TYPE="Text" NAME="Keywords" SIZE=20 MAXLENGTH=25></TD></TR>
 184 <TR><TD></TD>
 185 <TD><FONT SIZE 3><B><?php echo _('OR'); ?> </B></FONT><FONT SIZE=1><?php echo _('Enter extract of the'); ?> <B><?php echo _('Stock Code'); ?></B>:</FONT></TD>
 186 <TD><INPUT TYPE="Text" NAME="StockCode" SIZE=15 MAXLENGTH=18></TD>
 187 </TR>
 188 </TABLE>
 189
 190 <HR>
 191
 192 <?php
 193
 194 If ($StockItemsResult) {
 195
 196         echo '<TABLE CELLPADDING=2 COLSPAN=7 BORDER=2>';
 197         $TableHeader =  '<TR><TD class="tableheader">' . _('Code') . '</TD>
 198                         <TD class="tableheader">' . _('Description') . '</TD>
 199                         <TD class="tableheader">' . _('On Hand') . '</TD>
 200                         <TD class="tableheader">' . _('Orders') . '<BR>' . _('Outstanding') . '</TD>
 201                         <TD class="tableheader">' . _('Units') . '</TD>
 202                         </TR>';
 203         echo $TableHeader;
 204         $j = 1;
 205         $k=0; //row colour counter
 206
 207         while ($myrow=DB_fetch_array($StockItemsResult)) {
 208
 209                 if ($k==1){
 210                         echo '<tr bgcolor="#CCCCCC">';
 211                         $k=0;
 212                 } else {
 213                         echo '<tr bgcolor="#EEEEEE">';
 214                         $k=1;
 215                 }
 216
 217                 printf("<td><INPUT TYPE=SUBMIT NAME='SelectedStockItem' VALUE='%s'</td>
 218                         <td>%s</td>
 219                         <td ALIGN=RIGHT>%s</td>
 220                         <td ALIGN=RIGHT>%s</td>
 221                         <td>%s</td></tr>",
 222                         $myrow['stockid'],
 223                         $myrow['description'],
 224                         $myrow['qoh'],
 225                         $myrow['qord'],
 226                         $myrow['units']);
 227
 228                 $j++;
 229                 If ($j == 12){
 230                         $j=1;
 231                         echo $TableHeader;
 232                 }
 233 //end of page full new headings if
 234         }
 235 //end of while loop
 236
 237         echo '</TABLE>';
 238
 239 }
 240 //end if stock search results to show
 241   else {
 242
 243         //figure out the SQL required from the inputs available
 244
 245         if (isset($OrderNumber) && $OrderNumber !='') {
 246                 $SQL = 'SELECT purchorders.orderno,
 247                                 suppliers.suppname,
 248                                 purchorders.orddate,
 249                                 purchorders.initiator,
 250                                 purchorders.requisitionno,
 251                                 purchorders.allowprint,
 252                                 suppliers.currcode,
 253                                 SUM(purchorderdetails.unitprice*purchorderdetails.quantityord) AS ordervalue
 254                         FROM purchorders,
 255                                 purchorderdetails,
 256                                 suppliers
 257                         WHERE purchorders.orderno = purchorderdetails.orderno
 258                         AND purchorders.supplierno = suppliers.supplierid
 259                         AND purchorderdetails.completed=0
 260                         AND purchorders.orderno='. $OrderNumber .'
 261                         GROUP BY purchorders.orderno,
 262                                 suppliers.suppname,
 263                                 purchorders.orddate,
 264                                 purchorders.initiator,
 265                                 purchorders.requisitionno,
 266                                 purchorders.allowprint,
 267                                 suppliers.currcode';
 268         } else {
 269
 270               /* $DateAfterCriteria = FormatDateforSQL($OrdersAfterDate); */
 271
 272                 if (isset($SelectedSupplier)) {
 273
 274                         if (isset($SelectedStockItem)) {
 275                                 $SQL = "SELECT purchorders.orderno,
 276                                                 suppliers.suppname,
 277                                                 purchorders.orddate,
 278                                                 purchorders.initiator,
 279                                                 purchorders.requisitionno,
 280                                                 purchorders.allowprint,
 281                                                 suppliers.currcode,
 282                                                 SUM(purchorderdetails.unitprice*purchorderdetails.quantityord) AS ordervalue
 283                                         FROM purchorders,
 284                                                 purchorderdetails,
 285                                                 suppliers
 286                                         WHERE purchorders.orderno = purchorderdetails.orderno
 287                                         AND purchorders.supplierno = suppliers.supplierid
 288                                         AND purchorderdetails.completed=0
 289                                         AND purchorderdetails.itemcode='". $SelectedStockItem ."'
 290                                         AND purchorders.supplierno='" . $SelectedSupplier ."'
 291                                         AND purchorders.intostocklocation = '". $_POST['StockLocation'] . "'
 292                                         GROUP BY purchorders.orderno,
 293                                                 suppliers.suppname,
 294                                                 purchorders.orddate,
 295                                                 purchorders.initiator,
 296                                                 purchorders.requisitionno,
 297                                                 purchorders.allowprint,
 298                                                 suppliers.currcode";
 299                         } else {
 300                                 $SQL = "SELECT purchorders.orderno,
 301                                                 suppliers.suppname,
 302                                                 purchorders.orddate,
 303                                                 purchorders.initiator,
 304                                                 purchorders.requisitionno,
 305                                                 purchorders.allowprint,
 306                                                 suppliers.currcode,
 307                                                 SUM(purchorderdetails.unitprice*purchorderdetails.quantityord) AS ordervalue
 308                                         FROM purchorders,
 309                                                 purchorderdetails,
 310                                                 suppliers
 311                                         WHERE purchorders.orderno = purchorderdetails.orderno
 312                                         AND purchorders.supplierno = suppliers.supplierid
 313                                         AND purchorderdetails.completed=0
 314                                         AND purchorders.supplierno='" . $SelectedSupplier ."'
 315                                         AND purchorders.intostocklocation = '". $_POST['StockLocation'] . "'
 316                                         GROUP BY purchorders.orderno,
 317                                                 suppliers.suppname,
 318                                                 purchorders.orddate,
 319                                                 purchorders.initiator,
 320                                                 purchorders.requisitionno,
 321                                                 purchorders.allowprint,
 322                                                 suppliers.currcode";
 323                         }
 324                 } else { //no supplier selected
 325                         if (isset($SelectedStockItem)) {
 326                                 $SQL = "SELECT purchorders.orderno,
 327                                                 suppliers.suppname,
 328                                                 purchorders.orddate,
 329                                                 purchorders.initiator,
 330                                                 purchorders.requisitionno,
 331                                                 purchorders.allowprint,
 332                                                 suppliers.currcode,
 333                                                 SUM(purchorderdetails.unitprice*purchorderdetails.quantityord) AS ordervalue
 334                                         FROM purchorders,
 335                                                 purchorderdetails,
 336                                                 suppliers
 337                                         WHERE purchorders.orderno = purchorderdetails.orderno
 338                                         AND purchorders.supplierno = suppliers.supplierid
 339                                         AND purchorderdetails.completed=0
 340                                         AND purchorderdetails.itemcode='". $SelectedStockItem ."'
 341                                         AND purchorders.intostocklocation = '". $_POST['StockLocation'] . "'
 342                                         GROUP BY purchorders.orderno,
 343                                                 suppliers.suppname,
 344                                                 purchorders.orddate,
 345                                                 purchorders.initiator,
 346                                                 purchorders.requisitionno,
 347                                                 purchorders.allowprint,
 348                                                 suppliers.currcode";
 349                         } else {
 350                                 $SQL = "SELECT purchorders.orderno,
 351                                                 suppliers.suppname,
 352                                                 purchorders.orddate,
 353                                                 purchorders.initiator,
 354                                                 purchorders.requisitionno,
 355                                                 purchorders.allowprint,
 356                                                 suppliers.currcode,
 357                                                 SUM(purchorderdetails.unitprice*purchorderdetails.quantityord) AS ordervalue
 358                                         FROM purchorders,
 359                                                 purchorderdetails,
 360                                                 suppliers
 361                                         WHERE purchorders.orderno = purchorderdetails.orderno
 362                                         AND purchorders.supplierno = suppliers.supplierid
 363                                         AND purchorderdetails.completed=0
 364                                         AND purchorders.intostocklocation = '". $_POST['StockLocation'] . "'
 365                                         GROUP BY purchorders.orderno,
 366                                                 suppliers.suppname,
 367                                                 purchorders.orddate,
 368                                                 purchorders.initiator,
 369                                                 purchorders.requisitionno,
 370                                                 purchorders.allowprint,
 371                                                 suppliers.currcode";
 372                         }
 373
 374                 } //end selected supplier
 375         } //end not order number selected
 376
 377         $ErrMsg = _('No orders were returned by the SQL because');
 378         $PurchOrdersResult = DB_query($SQL,$db,$ErrMsg);
 379
 380         /*show a table of the orders returned by the SQL */
 381
 382         echo '<TABLE CELLPADDING=2 COLSPAN=7 WIDTH=100%>';
 383         $TableHeader = '<TR><TD class="tableheader">' . _('Modify') .
 384                        '</TD><TD class="tableheader">' . _('Receive') .
 385                         '</TD><TD class="tableheader">' . _('Print') .
 386                         '</TD><TD class="tableheader">' . _('Supplier') .
 387                         '</TD><TD class="tableheader">' . _('Currency') .
 388                         '</TD><TD class="tableheader">' . _('Requisition') .
 389                         '</TD><TD class="tableheader">' . _('Order Date') .
 390                         '</TD><TD class="tableheader">' . _('Initiator') .
 391                         '</TD><TD class="tableheader">' . _('Order Total') .
 392                         '</TD></TR>';
 393         echo $TableHeader;
 394         $j = 1;
 395         $k=0; //row colour counter
 396         while ($myrow=DB_fetch_array($PurchOrdersResult)) {
 397
 398
 399                 if ($k==1){ /*alternate bgcolour of row for highlighting */
 400                         echo '<tr bgcolor="#CCCCCC">';
 401                         $k=0;
 402                 } else {
 403                         echo '<tr bgcolor="#EEEEEE">';
 404                         $k++;
 405                 }
 406
 407                 $ModifyPage = $rootpath . "/PO_Header.php?" . SID . "ModifyOrderNumber=" . $myrow["orderno"];
 408                 $ReceiveOrder = $rootpath . "/GoodsReceived.php?" . SID . "PONumber=" . $myrow["orderno"];
 409                 if ($myrow["allowprint"]==1){
 410                         $PrintPurchOrder = '<A target="_blank" HREF="' . $rootpath . '/PO_PDFPurchOrder.php?' . SID . 'OrderNo=' . $myrow['orderno'] . '">' . _('Print Now') . '</A>';
 411                 } else {
 412                         $PrintPurchOrder = '<FONT COLOR=GREY>' . _('Printed') . '</FONT>';
 413                 }
 414                 $FormatedOrderDate = ConvertSQLDate($myrow['orddate']);
 415                 $FormatedOrderValue = number_format($myrow['ordervalue'],2);
 416
 417                 printf("<td><A HREF='%s'>%s</A></FONT></td>
 418                         <td><A HREF='%s'>" . _('Receive') . "</A></td>
 419                         <td>%s</td>
 420                         <td>%s</td>
 421                         <td>%s</FONT></td>
 422                         <td>%s</FONT></td>
 423                         <td>%s</FONT></td>
 424                         <td>%s</FONT></td>
 425                         <td ALIGN=RIGHT>%s</FONT></td>
 426                         </tr>",
 427                         $ModifyPage,
 428                         $myrow['orderno'],
 429                         $ReceiveOrder,
 430                         $PrintPurchOrder,
 431                         $myrow['suppname'],
 432                         $myrow['currcode'],
 433                         $myrow['requisitionno'],
 434                         $FormatedOrderDate,
 435                         $myrow['initiator'],
 436                         $FormatedOrderValue);
 437
 438                 $j++;
 439                 If ($j == 12){
 440                         $j=1;
 441                          echo $TableHeader;
 442                 }
 443         //end of page full new headings if
 444         }
 445         //end of while loop
 446
 447         echo '</TABLE>';
 448 }
 449
 450 echo '</form>';
 451 include('includes/footer.inc');
 452 ?>