Care2007/modules/weberp/SelectWorkOrder.php

   1 <?php
   2
   3 /* $Revision: 1.5 $ */
   4
   5 $PageSecurity = 2;
   6
   7 include('includes/session.inc');
   8 $title = _('Search Work Orders');
   9 include('includes/header.inc');
  10
  11 echo '<FORM ACTION=' . $_SERVER['PHP_SELF'] .'?' .SID . ' METHOD=POST>';
  12
  13
  14 If (isset($_POST['ResetPart'])){
  15      unset($_REQUEST['SelectedStockItem']);
  16 }
  17
  18 If (isset($_REQUEST['WO']) AND $_REQUEST['WO']!='') {
  19         $_REQUEST['WO'] = trim($_REQUEST['WO']);
  20         if (!is_numeric($_REQUEST['WO'])){
  21                   prnMsg(_('The work order number entered MUST be numeric'),'warn');
  22                   unset ($_REQUEST['WO']);
  23                   include('includes/footer.inc');
  24                   exit;
  25         } else {
  26                 echo _('Work Order Number') . ' - ' . $_REQUEST['WO'];
  27         }
  28 } else {
  29         if (isset($_REQUEST['SelectedStockItem'])) {
  30                  echo _('for the item') . ': ' . $_REQUEST['SelectedStockItem'] . ' ' . _('and') . " <input type=hidden name='SelectedStockItem' value='" . $_REQUEST['SelectedStockItem'] . "'>";
  31         }
  32 }
  33
  34 if (isset($_POST['SearchParts'])){
  35
  36         If ($_POST['Keywords'] AND $_POST['StockCode']) {
  37                 echo _('Stock description keywords have been used in preference to the Stock code extract entered');
  38         }
  39         If ($_POST['Keywords']) {
  40                 //insert wildcard characters in spaces
  41                 $i=0;
  42                 $SearchString = '%';
  43                 while (strpos($_POST['Keywords'], ' ', $i)) {
  44                         $wrdlen=strpos($_POST['Keywords'],' ',$i) - $i;
  45                         $SearchString=$SearchString . substr($_POST['Keywords'],$i,$wrdlen) . '%';
  46                         $i=strpos($_POST['Keywords'],' ',$i) +1;
  47                 }
  48                 $SearchString = $SearchString . substr($_POST['Keywords'],$i).'%';
  49
  50                 $SQL = "SELECT stockmaster.stockid,
  51                                 stockmaster.description,
  52                                 SUM(locstock.quantity) AS qoh,
  53                                 stockmaster.units
  54                         FROM stockmaster,
  55                                 locstock
  56                         WHERE stockmaster.stockid=locstock.stockid
  57                         AND stockmaster.description " . LIKE . " '" . $SearchString . "'
  58                         AND stockmaster.categoryid='" . $_POST['StockCat']. "'
  59                         AND stockmaster.mbflag='M'
  60                         GROUP BY stockmaster.stockid,
  61                                 stockmaster.description,
  62                                 stockmaster.units
  63                         ORDER BY stockmaster.stockid";
  64
  65          } elseif (isset($_POST['StockCode'])){
  66                 $SQL = "SELECT stockmaster.stockid,
  67                                 stockmaster.description,
  68                                 sum(locstock.quantity) as qoh,
  69                                 stockmaster.units
  70                         FROM stockmaster,
  71                                 locstock
  72                         WHERE stockmaster.stockid=locstock.stockid
  73                         AND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'
  74                         AND stockmaster.categoryid='" . $_POST['StockCat'] . "'
  75                         AND stockmaster.mbflag='M'
  76                         GROUP BY stockmaster.stockid,
  77                                 stockmaster.description,
  78                                 stockmaster.units
  79                         ORDER BY stockmaster.stockid";
  80
  81          } elseif (!isset($_POST['StockCode']) AND !isset($_POST['Keywords'])) {
  82                 $SQL = "SELECT stockmaster.stockid,
  83                                 stockmaster.description,
  84                                 sum(locstock.quantity) as qoh,
  85                                 stockmaster.units
  86                         FROM stockmaster,
  87                                 locstock
  88                         WHERE stockmaster.stockid=locstock.stockid
  89                         AND stockmaster.categoryid='" . $_POST['StockCat'] ."'
  90                         AND stockmaster.mbflag='M'
  91                         GROUP BY stockmaster.stockid,
  92                                 stockmaster.description,
  93                                 stockmaster.units
  94                         ORDER BY stockmaster.stockid";
  95          }
  96
  97         $ErrMsg =  _('No items were returned by the SQL because');
  98         $DbgMsg = _('The SQL used to retrieve the searched parts was');
  99         $StockItemsResult = DB_query($SQL,$db,$ErrMsg,$DbgMsg);
 100 }
 101
 102 if (isset($_POST['StockID'])){
 103         $StockID = trim(strtoupper($_POST['StockID']));
 104 } elseif (isset($_GET['StockID'])){
 105         $StockID = trim(strtoupper($_GET['StockID']));
 106 }
 107
 108 if (!isset($StockID)) {
 109
 110      /* Not appropriate really to restrict search by date since may miss older
 111      ouststanding orders
 112         $OrdersAfterDate = Date('d/m/Y',Mktime(0,0,0,Date('m')-2,Date('d'),Date('Y')));
 113      */
 114
 115         if ($_REQUEST['WO']=='' OR !$_REQUEST['WO']){
 116
 117                 echo _('Work Order number') . ": <INPUT type=text name='WO' MAXLENGTH =8 SIZE=9>&nbsp " . _('Processing at') . ":<SELECT name='StockLocation'> ";
 118
 119                 $sql = 'SELECT loccode, locationname FROM locations';
 120
 121                 $resultStkLocs = DB_query($sql,$db);
 122
 123                 while ($myrow=DB_fetch_array($resultStkLocs)){
 124                         if (isset($_POST['StockLocation'])){
 125                                 if ($myrow['loccode'] == $_POST['StockLocation']){
 126                                      echo "<OPTION SELECTED Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
 127                                 } else {
 128                                      echo "<OPTION Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
 129                                 }
 130                         } elseif ($myrow['loccode']==$_SESSION['UserStockLocation']){
 131                                  echo "<OPTION SELECTED Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
 132                         } else {
 133                                  echo "<OPTION Value='" . $myrow['loccode'] . "'>" . $myrow['locationname'];
 134                         }
 135                 }
 136
 137                 echo '</SELECT> &nbsp&nbsp';
 138                 echo '<SELECT NAME="ClosedOrOpen">';
 139
 140                 if ($_GET['ClosedOrOpen']=='Closed_Only'){
 141                         $_POST['ClosedOrOpen']='Closed_Only';
 142                 }
 143
 144                 if ($_POST['ClosedOrOpen']=='Closed_Only'){
 145                         echo '<OPTION SELECTED VALUE="Closed_Only">' . _('Closed Work Orders Only');
 146                         echo '<OPTION VALUE="Open_Only">' . _('Open Work Orders Only');
 147                 } else {
 148                         echo '<OPTION VALUE="Closed_Only">' . _('Closed Work Orders Only');
 149                         echo '<OPTION SELECTED VALUE="Open_Only">' . _('Open Work Orders Only');
 150                 }
 151
 152                 echo '</SELECT> &nbsp&nbsp';
 153                 echo "<INPUT TYPE=SUBMIT NAME='SearchOrders' VALUE='" . _('Search') . "'>";
 154         echo '&nbsp;&nbsp;<a href="' . $rootpath . '/WorkOrderEntry.php?' . SID . '">' . _('New Work Order') . '</a>';
 155         }
 156
 157         $SQL='SELECT categoryid,
 158                         categorydescription
 159                 FROM stockcategory
 160                 ORDER BY categorydescription';
 161
 162         $result1 = DB_query($SQL,$db);
 163
 164         echo '<HR>
 165                 <FONT SIZE=1>' . _('To search for work orders for a specific item use the item selection facilities below') . "</FONT>
 166                 <INPUT TYPE=SUBMIT NAME='SearchParts' VALUE='" . _('Search Items Now') . "'>
 167                 <INPUT TYPE=SUBMIT NAME='ResetPart' VALUE='" . _('Show All') . "'>
 168       <TABLE>
 169         <TR>
 170                 <TD><FONT SIZE=1>" . _('Select a stock category') . ":</FONT>
 171                         <SELECT NAME='StockCat'>";
 172
 173         while ($myrow1 = DB_fetch_array($result1)) {
 174                 echo "<OPTION VALUE='". $myrow1['categoryid'] . "'>" . $myrow1['categorydescription'];
 175         }
 176
 177       echo '</SELECT>
 178                 <TD><FONT SIZE=1>' . _('Enter text extract(s) in the description') . ":</FONT></TD>
 179                 <TD><INPUT TYPE='Text' NAME='Keywords' SIZE=20 MAXLENGTH=25></TD>
 180         </TR>
 181         <TR><TD></TD>
 182                 <TD><FONT SIZE 3><B>" . _('OR') . ' </B></FONT><FONT SIZE=1>' . _('Enter extract of the Stock Code') . "</B>:</FONT></TD>
 183                 <TD><INPUT TYPE='Text' NAME='StockCode' SIZE=15 MAXLENGTH=18></TD>
 184         </TR>
 185       </TABLE>
 186       <HR>";
 187
 188 If (isset($StockItemsResult)) {
 189
 190         echo '<TABLE CELLPADDING=2 COLSPAN=7 BORDER=2>';
 191         $TableHeader = "<TR>
 192                                 <TD class='tableheader'>" . _('Code') . "</TD>
 193                                 <TD class='tableheader'>" . _('Description') . "</TD>
 194                                 <TD class='tableheader'>" . _('On Hand') . "</TD>
 195                                 <TD class='tableheader'>" . _('Units') . "</TD>
 196                         </TR>";
 197         echo $TableHeader;
 198
 199         $j = 1;
 200         $k=0; //row colour counter
 201
 202         while ($myrow=DB_fetch_array($StockItemsResult)) {
 203
 204                 if ($k==1){
 205                         echo "<tr bgcolor='#CCCCCC'>";
 206                         $k=0;
 207                 } else {
 208                         echo "<tr bgcolor='#EEEEEE'>";
 209                         $k++;
 210                 }
 211
 212                 printf("<td><INPUT TYPE=SUBMIT NAME='SelectedStockItem' VALUE='%s'</td>
 213                         <td>%s</td>
 214                         <td ALIGN=RIGHT>%s</td>
 215                         <td>%s</td>
 216                         </tr>",
 217                         $myrow['stockid'],
 218                         $myrow['description'],
 219                         $myrow['qoh'],
 220                         $myrow['units']);
 221
 222                 $j++;
 223                 If ($j == 12){
 224                         $j=1;
 225                         echo $TableHeader;
 226                 }
 227 //end of page full new headings if
 228         }
 229 //end of while loop
 230
 231         echo '</TABLE>';
 232
 233 }
 234 //end if stock search results to show
 235   else {
 236
 237         //figure out the SQL required from the inputs available
 238         if ($_POST['ClosedOrOpen']=='Open_Only'){
 239                 $ClosedOrOpen = 0;
 240         } else {
 241                 $ClosedOrOpen = 1;
 242         }
 243         if (isset($_REQUEST['WO']) && $_REQUEST['WO'] !='') {
 244                         $SQL = "SELECT workorders.wo,
 245                                         woitems.stockid,
 246                                         stockmaster.description,
 247                                         woitems.qtyreqd,
 248                                         woitems.qtyrecd,
 249                                         workorders.requiredby
 250                                         FROM workorders
 251                                         INNER JOIN woitems ON workorders.wo=woitems.wo
 252                                         INNER JOIN stockmaster ON woitems.stockid=stockmaster.stockid
 253                                         WHERE workorders.closed=" . $ClosedOrOpen . "
 254                                         AND workorders.wo=". $_REQUEST['WO'] ."
 255                                         ORDER BY workorders.wo,
 256                                                          woitems.stockid";
 257         } else {
 258               /* $DateAfterCriteria = FormatDateforSQL($OrdersAfterDate); */
 259
 260                         if (isset($_REQUEST['SelectedStockItem'])) {
 261                                 $SQL = "SELECT workorders.wo,
 262                                         woitems.stockid,
 263                                         stockmaster.description,
 264                                         woitems.qtyreqd,
 265                                         woitems.qtyrecd,
 266                                         workorders.requiredby
 267                                         FROM workorders
 268                                         INNER JOIN woitems ON workorders.wo=woitems.wo
 269                                         INNER JOIN stockmaster ON woitems.stockid=stockmaster.stockid
 270                                         WHERE workorders.closed=" . $ClosedOrOpen . "
 271                                         AND woitems.stockid='". $_REQUEST['SelectedStockItem'] ."'
 272                                         AND workorders.loccode='" . $_POST['StockLocation'] . "'
 273                                         ORDER BY workorders.wo,
 274                                                          woitems.stockid";
 275                         } else {
 276                                 $SQL = "SELECT workorders.wo,
 277                                         woitems.stockid,
 278                                         stockmaster.description,
 279                                         woitems.qtyreqd,
 280                                         woitems.qtyrecd,
 281                                         workorders.requiredby
 282                                         FROM workorders
 283                                         INNER JOIN woitems ON workorders.wo=woitems.wo
 284                                         INNER JOIN stockmaster ON woitems.stockid=stockmaster.stockid
 285                                         WHERE workorders.closed=" . $ClosedOrOpen . "
 286                                         AND workorders.loccode='" . $_POST['StockLocation'] . "'
 287                                         ORDER BY workorders.wo,
 288                                                          woitems.stockid";
 289                         }
 290         } //end not order number selected
 291
 292         $ErrMsg = _('No works orders were returned by the SQL because');
 293         $WorkOrdersResult = DB_query($SQL,$db,$ErrMsg);
 294
 295         /*show a table of the orders returned by the SQL */
 296
 297         echo '<TABLE CELLPADDING=2 COLSPAN=7 WIDTH=100%>';
 298
 299
 300         $tableheader = "<TR>
 301                                 <TD class='tableheader'>" . _('Modify') . "</TD>
 302                                 <TD class='tableheader'>" . _('Status') . "</TD>
 303                                 <TD class='tableheader'>" . _('Receive') . "</TD>
 304                                 <TD class='tableheader'>" . _('Issue To') . "</TD>
 305                                 <TD class='tableheader'>" . _('Costing') . "</TD>
 306                                 <TD class='tableheader'>" . _('Item') . "</TD>
 307                                 <TD class='tableheader'>" . _('Quantity Required') . "</TD>
 308                                 <TD class='tableheader'>" . _('Quantity Received') . "</TD>
 309                                 <TD class='tableheader'>" . _('Quantity Outstanding') . "</TD>
 310                                 <TD class='tableheader'>" . _('Required Date') . "</TD>
 311                                 </TR>";
 312
 313         echo $tableheader;
 314
 315         $j = 1;
 316         $k=0; //row colour counter
 317         while ($myrow=DB_fetch_array($WorkOrdersResult)) {
 318
 319                 if ($k==1){
 320                         echo "<tr bgcolor='#CCCCCC'>";
 321                         $k=0;
 322                 } else {
 323                         echo "<tr bgcolor='#EEEEEE'>";
 324                         $k++;
 325                 }
 326
 327                 $ModifyPage = $rootpath . "/WorkOrderEntry.php?" . SID . '&WO=' . $myrow['wo'];
 328                 $Status_WO = $rootpath . '/WorkOrderStatus.php?' . SID . '&WO=' .$myrow['wo'] . '&StockID=' . $myrow['stockid'];
 329                 $Receive_WO = $rootpath . '/WorkOrderReceive.php?' . SID . '&WO=' .$myrow['wo'] . '&StockID=' . $myrow['stockid'];
 330                 $Issue_WO = $rootpath . '/WorkOrderIssue.php?' . SID . '&WO=' .$myrow['wo'] . '&StockID=' . $myrow['stockid'];
 331                 $Costing_WO =$rootpath . '/WorkOrderCosting.php?' . SID . '&WO=' .$myrow['wo'];
 332
 333                 $FormatedRequiredByDate = ConvertSQLDate($myrow['requiredby']);
 334
 335
 336                 printf("<td><A HREF='%s'>%s</A></td>
 337                                 <td><A HREF='%s'>" . _('Status') . "</A></td>
 338                                 <td><A HREF='%s'>" . _('Receive') . "</A></td>
 339                                 <td><A HREF='%s'>" . _('Issue To') . "</A></td>
 340                                 <td><A HREF='%s'>" . _('Costing') . "</A></td>
 341                                 <td>%s - %s</td>
 342                                 <td align=right>%s</td>
 343                                 <td align=right>%s</td>
 344                                 <td align=right>%s</td>
 345                                 <td>%s</td>
 346                                 </tr>",
 347                                 $ModifyPage,
 348                                 $myrow['wo'],
 349                                 $Status_WO,
 350                                 $Receive_WO,
 351                                 $Issue_WO,
 352                                 $Costing_WO,
 353                                 $myrow['stockid'],
 354                                 $myrow['description'],
 355                                 $myrow['qtyreqd'],
 356                                 $myrow['qtyrecd'],
 357                                 $myrow['qtyreqd']-$myrow['qtyrecd'],
 358                                 $FormatedRequiredByDate);
 359
 360                 $j++;
 361                 If ($j == 12){
 362                         $j=1;
 363                         echo $tableheader;
 364                 }
 365         //end of page full new headings if
 366         }
 367         //end of while loop
 368
 369         echo '</TABLE>';
 370 }
 371
 372 ?>
 373 </FORM>
 374
 375 <?php }
 376
 377 include('includes/footer.inc');
 378 ?>