Care2007/modules/weberp/StockStatus.php

   1 <?php
   2
   3 /* $Revision: 1.19 $ */
   4
   5
   6 $PageSecurity = 2;
   7
   8 include('includes/session.inc');
   9
  10 $title = _('Stock Status');
  11
  12 include('includes/header.inc');
  13
  14 if (isset($_GET['StockID'])){
  15         $StockID = trim(strtoupper($_GET['StockID']));
  16 } elseif (isset($_POST['StockID'])){
  17         $StockID = trim(strtoupper($_POST['StockID']));
  18 }
  19
  20 echo "<A HREF='" . $rootpath . '/SelectProduct.php?' . SID . "'>" . _('Back to Items') . '</A><BR>';
  21
  22 $result = DB_query("SELECT description,
  23                            units,
  24                            mbflag,
  25                            decimalplaces,
  26                            serialised,
  27                            controlled
  28                     FROM
  29                            stockmaster
  30                     WHERE
  31                            stockid='$StockID'",
  32                            $db,
  33                            _('Could not retrieve the requested item'),
  34                            _('The SQL used to retrieve the items was'));
  35
  36 $myrow = DB_fetch_row($result);
  37
  38 $DecimalPlaces = $myrow[3];
  39 $Serialised = $myrow[4];
  40 $Controlled = $myrow[5];
  41
  42 echo '<CENTER><BR><FONT COLOR=BLUE SIZE=3><B>' . $StockID . ' - ' . $myrow[0] . ' </B>  (' . _('In units of') . ' ' . $myrow[1] . ')</FONT>';
  43 $Its_A_KitSet_Assembly_Or_Dummy =False;
  44 if ($myrow[2]=='K'){
  45         $Its_A_KitSet_Assembly_Or_Dummy =True;
  46         prnMsg( _('This is a kitset part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  47 } elseif ($myrow[2]=='A'){
  48         $Its_A_KitSet_Assembly_Or_Dummy =True;
  49         prnMsg(_('This is an assembly part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  50 } elseif ($myrow[2]=='D'){
  51         $Its_A_KitSet_Assembly_Or_Dummy =True;
  52         prnMsg( _('This is an dummy part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  53 }
  54
  55 echo '<HR><FORM ACTION="' . $_SERVER['PHP_SELF'] . '?'. SID . '" METHOD=POST>';
  56 echo _('Stock Code') . ':<input type=text name="StockID" size=21 value="' . $StockID . '" maxlength=20>';
  57
  58 echo ' <INPUT TYPE=SUBMIT NAME="ShowStatus" VALUE="' . _('Show Stock Status') . '"><HR>';
  59
  60 $sql = "SELECT locstock.loccode,
  61                locations.locationname,
  62                locstock.quantity,
  63                locstock.reorderlevel,
  64                locations.managed
  65                FROM locstock,
  66                     locations
  67                WHERE locstock.loccode=locations.loccode AND
  68                      locstock.stockid = '" . $StockID . "'
  69                ORDER BY locstock.loccode";
  70
  71 $ErrMsg = _('The stock held at each location cannot be retrieved because');
  72 $LocStockResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
  73
  74 echo '<TABLE CELLPADDING=2 BORDER=0>';
  75
  76 if ($Its_A_KitSet_Assembly_Or_Dummy == True){
  77         $tableheader = '<TR>
  78                         <TD class="tableheader">' . _('Location') . '</TD>
  79                         <TD class="tableheader">' . _('Demand') . '</TD>
  80                         </TR>';
  81 } else {
  82         $tableheader = '<TR>
  83                         <TD class="tableheader">' . _('Location') . '</TD>
  84                         <TD class="tableheader">' . _('Quantity On Hand') . '</TD>
  85                         <TD class="tableheader">' . _('Re-Order Level') . '</FONT></TD>
  86                         <TD class="tableheader">' . _('Demand') . '</TD>
  87                         <TD class="tableheader">' . _('Available') . '</TD>
  88                         <TD class="tableheader">' . _('On Order') . '</TD>
  89                         </TR>';
  90 }
  91 echo $tableheader;
  92 $j = 1;
  93 $k=0; //row colour counter
  94
  95 while ($myrow=DB_fetch_array($LocStockResult)) {
  96
  97         if ($k==1){
  98                 echo '<tr bgcolor="#CCCCCC">';
  99                 $k=0;
 100         } else {
 101                 echo '<tr bgcolor="#EEEEEE">';
 102                 $k=1;
 103         }
 104
 105         $sql = "SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
 106                  FROM salesorderdetails,
 107                       salesorders
 108                  WHERE salesorders.orderno = salesorderdetails.orderno AND
 109                  salesorders.fromstkloc='" . $myrow['loccode'] . "' AND
 110                  salesorderdetails.completed=0 AND
 111                  salesorders.quotation=0 AND
 112                  salesorderdetails.stkcode='" . $StockID . "'";
 113
 114         $ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 115         $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 116
 117         if (DB_num_rows($DemandResult)==1){
 118           $DemandRow = DB_fetch_row($DemandResult);
 119           $DemandQty =  $DemandRow[0];
 120         } else {
 121           $DemandQty =0;
 122         }
 123
 124         //Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
 125         $sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
 126                  FROM salesorderdetails,
 127                       salesorders,
 128                       bom,
 129                       stockmaster
 130                  WHERE salesorderdetails.stkcode=bom.parent AND
 131                        salesorders.orderno = salesorderdetails.orderno AND
 132                        salesorders.fromstkloc='" . $myrow['loccode'] . "' AND
 133                        salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0 AND
 134                        bom.component='" . $StockID . "' AND stockmaster.stockid=bom.parent AND
 135                        stockmaster.mbflag='A'
 136                        AND salesorders.quotation=0";
 137
 138         $ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 139         $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 140
 141         if (DB_num_rows($DemandResult)==1){
 142                 $DemandRow = DB_fetch_row($DemandResult);
 143                 $DemandQty += $DemandRow[0];
 144         }
 145
 146         //Also the demand for the item as a component of works orders
 147
 148         $sql = "SELECT SUM(qtypu*(woitems.qtyreqd - woitems.qtyrecd)) AS woqtydemo
 149                                 FROM woitems INNER JOIN worequirements
 150                                 ON woitems.stockid=worequirements.parentstockid
 151                                 INNER JOIN workorders
 152                                 ON woitems.wo=workorders.wo
 153                                 AND woitems.wo=worequirements.wo
 154                                 WHERE workorders.loccode='" . $myrow['loccode'] . "'
 155                                 AND worequirements.stockid='" . $StockID . "'
 156                                 AND workorders.closed=0";
 157
 158         $ErrMsg = _('The workorder component demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 159         $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 160
 161         if (DB_num_rows($DemandResult)==1){
 162                 $DemandRow = DB_fetch_row($DemandResult);
 163                 $DemandQty += $DemandRow[0];
 164         }
 165
 166         if ($Its_A_KitSet_Assembly_Or_Dummy == False){
 167
 168                 $sql = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) AS qoo
 169                         FROM purchorderdetails
 170                         INNER JOIN purchorders ON purchorderdetails.orderno=purchorders.orderno
 171                         WHERE purchorders.intostocklocation='" . $myrow['loccode'] . "' AND
 172                         purchorderdetails.itemcode='" . $StockID . "'";
 173                 $ErrMsg = _('The quantity on order for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 174                 $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
 175
 176                 if (DB_num_rows($QOOResult)==1){
 177                         $QOORow = DB_fetch_row($QOOResult);
 178                         $QOO =  $QOORow[0];
 179                 } else {
 180                         $QOO = 0;
 181                 }
 182
 183                 //Also the on work order quantities
 184                 $sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
 185                                 FROM woitems INNER JOIN workorders
 186                                 ON woitems.wo=workorders.wo
 187                                 WHERE workorders.closed=0
 188                                 AND workorders.loccode='" . $myrow['loccode'] . "'
 189                                 AND woitems.stockid='" . $StockID . "'";
 190                 $ErrMsg = _('The quantity on work orders for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 191                 $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
 192
 193                 if (DB_num_rows($QOOResult)==1){
 194                         $QOORow = DB_fetch_row($QOOResult);
 195                         $QOO +=  $QOORow[0];
 196                 }
 197
 198                 echo '<TD>' . $myrow['locationname'] . '</TD>';
 199
 200                 printf("<td ALIGN=RIGHT>%s</td>
 201                         <td ALIGN=RIGHT>%s</td>
 202                         <td ALIGN=RIGHT>%s</td>
 203                         <td ALIGN=RIGHT>%s</td>
 204                         <td ALIGN=RIGHT>%s</td>",
 205                         number_format($myrow['quantity'], $DecimalPlaces),
 206                         number_format($myrow['reorderlevel'], $DecimalPlaces),
 207                         number_format($DemandQty, $DecimalPlaces),
 208                         number_format($myrow['quantity'] - $DemandQty, $DecimalPlaces),
 209                         number_format($QOO, $DecimalPlaces)
 210                         );
 211
 212                 if ($Serialised ==1){ /*The line is a serialised item*/
 213
 214                         echo '<TD><A target="_blank" HREF="' . $rootpath . '/StockSerialItems.php?' . SID . '&Serialised=Yes&Location=' . $myrow['loccode'] . '&StockID=' .$StockID . '">' . _('Serial Numbers') . '</A></TD></TR>';
 215                 } elseif ($Controlled==1){
 216                         echo '<TD><A target="_blank" HREF="' . $rootpath . '/StockSerialItems.php?' . SID . '&Location=' . $myrow['loccode'] . '&StockID=' .$StockID . '">' . _('Batches') . '</A></TD></TR>';
 217                 }
 218
 219         } else {
 220         /* It must be a dummy, assembly or kitset part */
 221
 222                 printf("<td>%s</td>
 223                         <td ALIGN=RIGHT>%s</td>
 224                         </tr>",
 225                         $myrow['locationname'],
 226                         number_format($DemandQty, $DecimalPlaces)
 227                         );
 228         }
 229         $j++;
 230         If ($j == 12){
 231                 $j=1;
 232                 echo $tableheader;
 233         }
 234 //end of page full new headings if
 235 }
 236 //end of while loop
 237 echo '</TABLE><HR>';
 238
 239 if (isset($_GET['DebtorNo'])){
 240         $DebtorNo = trim(strtoupper($_GET['DebtorNo']));
 241 } elseif (isset($_POST['DebtorNo'])){
 242         $DebtorNo = trim(strtoupper($_POST['DebtorNo']));
 243 } elseif (isset($_SESSION['CustomerID'])){
 244         $DebtorNo=$_SESSION['CustomerID'];
 245 }
 246
 247 if ($DebtorNo) { /* display recent pricing history for this debtor and this stock item */
 248
 249         $sql = "SELECT stockmoves.trandate,
 250                                 stockmoves.qty,
 251                                 stockmoves.price,
 252                                 stockmoves.discountpercent
 253                         FROM stockmoves
 254                         WHERE stockmoves.debtorno='" . $DebtorNo . "'
 255                                 AND stockmoves.type=10
 256                                 AND stockmoves.stockid = '" . $StockID . "'
 257                                 AND stockmoves.hidemovt=0
 258                         ORDER BY stockmoves.trandate DESC";
 259
 260         /* only show pricing history for sales invoices - type=10 */
 261
 262         $ErrMsg = _('The stock movements for the selected criteria could not be retrieved because') . ' - ';
 263         $DbgMsg = _('The SQL that failed was') . ' ';
 264
 265         $MovtsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
 266
 267         $k=1;
 268         while ($myrow=DB_fetch_array($MovtsResult)) {
 269           if ($LastPrice != $myrow['price'] or $LastDiscount != $myrow['discount']) { /* consolidate price history for records with same price/discount */
 270             if ($qty) {
 271                 $DateRange=ConvertSQLDate($FromDate);
 272                 if ($FromDate != $ToDate) {
 273                         $DateRange .= ' - ' . ConvertSQLDate($ToDate);
 274                 }
 275                 $PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
 276                 $k++;
 277                 if ($k > 9) break; /* 10 price records is enough to display */
 278                 if ($myrow['trandate'] < FormatDateForSQL(time() - 366*86400))
 279                   break; /* stop displaying pirce history more than a year old once we have at least one  to display */
 280             }
 281                 $LastPrice = $myrow['price'];
 282                 $LastDiscount = $myrow['discount'];
 283             $ToDate = $myrow['trandate'];
 284                 $qty = 0;
 285           }
 286           $qty += $myrow['qty'];
 287           $FromDate = $myrow['trandate'];
 288         }
 289         if ($qty) {
 290                 $DateRange = ConvertSQLDate($FromDate);
 291                 if ($FromDate != $ToDate) {
 292                         $DateRange .= ' - '.ConvertSQLDate($ToDate);
 293                 }
 294                 $PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
 295         }
 296         if ($PriceHistory) {
 297           echo '<p>' . _('Pricing history for sales of') . ' ' . $StockID . ' ' . _('to') . ' ' . $DebtorNo;
 298           echo '<TABLE CELLPADDING=2 BORDER=0>';
 299           $tableheader = "<TR>
 300                         <TD CLASS='tableheader'>" . _('Date Range') . "</TD>
 301                         <TD CLASS='tableheader'>" . _('Quantity') . "</TD>
 302                         <TD CLASS='tableheader'>" . _('Price') . "</TD>
 303                         <TD CLASS='tableheader'>" . _('Discount') . "</TD>
 304                         </TR>";
 305
 306           $j = 0;
 307           $k = 0; //row colour counter
 308
 309           foreach($PriceHistory as $ph) {
 310                 $j--;
 311                 If ($j < 0 ){
 312                         $j = 11;
 313                         echo $tableheader;
 314                 }
 315
 316                 if ($k==1){
 317                         echo "<TR BGCOLOR='#CCCCCC'>";
 318                         $k=0;
 319                 } else {
 320                         echo "<TR BGCOLOR='#EEEEEE'>";
 321                         $k=1;
 322                 }
 323
 324                         printf("<TD>%s</TD>
 325                         <TD ALIGN=RIGHT>%s</TD>
 326                         <TD ALIGN=RIGHT>%s</TD>
 327                         <TD ALIGN=RIGHT>%s%%</TD>
 328                         </TR>",
 329                         $ph[0],
 330                         number_format($ph[1],$DecimalPlaces),
 331                         number_format($ph[2],2),
 332                         number_format($ph[3]*100,2)
 333                         );
 334           }
 335          echo '</TABLE>';
 336          }
 337         //end of while loop
 338         else {
 339           echo '<p>'._('No history of sales of') . ' ' . $StockID . ' ' . _('to') . ' ' . $DebtorNo;
 340         }
 341 }
 342 //end of displaying price history for a debtor
 343 echo '<hr>';
 344
 345 echo '<A HREF="' . $rootpath . '/StockMovements.php?' . SID . '&StockID=' . $StockID . '">' . _('Show Movements') . '</A>';
 346 echo '<BR><A HREF="' . $rootpath . '/StockUsage.php?' . SID . '&StockID=' . $StockID . '">' . _('Show Usage') . '</A>';
 347 echo '<BR><A HREF="' . $rootpath . '/SelectSalesOrder.php?' . SID . '&SelectedStockItem=' . $StockID . '">' . _('Search Outstanding Sales Orders') . '</A>';
 348 echo '<BR><A HREF="' . $rootpath . '/SelectCompletedOrder.php?' . SID . '&SelectedStockItem=' . $StockID . '">' . _('Search Completed Sales Orders') . '</A>';
 349 if ($Its_A_KitSet_Assembly_Or_Dummy ==False){
 350         echo '<BR><A HREF="' . $rootpath . '/PO_SelectOSPurchOrder.php?' .SID . '&SelectedStockItem=' . $StockID . '">' . _('Search Outstanding Purchase Orders') . '</A>';
 351 }
 352
 353 echo '</FORM></CENTER>';
 354 include('includes/footer.inc');
 355
 356 ?>