webERP PHP Cross Reference Customer Relationship Management

Source: /StockStatus.php - 409 lines - 15476 bytes - Summary - Text - Print

   1  <?php
   2  
   3  /* $Id: StockStatus.php 6338 2013-09-28 05:10:46Z daintree $*/
   4  
   5  include ('includes/session.inc');
   6  
   7  $Title = _('Stock Status');
   8  
   9  include ('includes/header.inc');
  10  
  11  if (isset($_GET['StockID'])){
  12      $StockID = trim(mb_strtoupper($_GET['StockID']));
  13  } elseif (isset($_POST['StockID'])){
  14      $StockID = trim(mb_strtoupper($_POST['StockID']));
  15  } else {
  16      $StockID = '';
  17  }
  18  
  19  if (isset($_POST['UpdateBinLocations'])){
  20      foreach ($_POST as $PostVariableName => $Bin) {
  21          if (mb_substr($PostVariableName,0,11) == 'BinLocation') {
  22              $sql = "UPDATE locstock SET bin='" . strtoupper($Bin) . "' WHERE loccode='" . mb_substr($PostVariableName,11) . "' AND stockid='" . $StockID . "'";
  23              $result = DB_query($sql, $db);
  24          }
  25      }
  26  }
  27  $result = DB_query("SELECT description,
  28                             units,
  29                             mbflag,
  30                             decimalplaces,
  31                             serialised,
  32                             controlled
  33                      FROM stockmaster
  34                      WHERE stockid='".$StockID."'",
  35                      $db,
  36                      _('Could not retrieve the requested item'),
  37                      _('The SQL used to retrieve the items was'));
  38  
  39  $myrow = DB_fetch_array($result);
  40  
  41  $DecimalPlaces = $myrow['decimalplaces'];
  42  $Serialised = $myrow['serialised'];
  43  $Controlled = $myrow['controlled'];
  44  
  45  echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Inventory') .
  46      '" alt="" /><b>' . ' ' . $StockID . ' - ' . $myrow['description'] . ' : ' . _('in units of') . ' : ' . $myrow['units'] . '</b></p>';
  47  
  48  $Its_A_KitSet_Assembly_Or_Dummy =False;
  49  if ($myrow[2]=='K'){
  50      $Its_A_KitSet_Assembly_Or_Dummy =True;
  51      prnMsg( _('This is a kitset part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  52  } elseif ($myrow[2]=='A'){
  53      $Its_A_KitSet_Assembly_Or_Dummy =True;
  54      prnMsg(_('This is an assembly part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  55  } elseif ($myrow[2]=='D'){
  56      $Its_A_KitSet_Assembly_Or_Dummy =True;
  57      prnMsg( _('This is an dummy part and cannot have a stock holding') . ', ' . _('only the total quantity on outstanding sales orders is shown'),'info');
  58  }
  59  
  60  echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
  61  echo '<div class="centre"><input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
  62  echo _('Stock Code') . ':<input type="text" data-type="no-illegal-chars" title ="'._('Input the stock code to inquire upon. Only alpha-numeric characters are allowed in stock codes with no spaces punctuation or special characters. Underscore or dashes are allowed.').'" placeholder="'._('Alpha-numeric only').'" required="required" name="StockID" size="21" value="' . $StockID . '" maxlength="20" />';
  63  
  64  echo ' <input type="submit" name="ShowStatus" value="' . _('Show Stock Status') . '" />';
  65  
  66  $sql = "SELECT locstock.loccode,
  67                  locations.locationname,
  68                  locstock.quantity,
  69                  locstock.reorderlevel,
  70                  locstock.bin,
  71                  locations.managed
  72          FROM locstock INNER JOIN locations
  73          ON locstock.loccode=locations.loccode
  74          WHERE locstock.stockid = '" . $StockID . "'
  75          ORDER BY locations.locationname";
  76  
  77  $ErrMsg = _('The stock held at each location cannot be retrieved because');
  78  $DbgMsg = _('The SQL that was used to update the stock item and failed was');
  79  $LocStockResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
  80  
  81  echo '<br />
  82          <table class="selection"><tbody>';
  83  
  84  if ($Its_A_KitSet_Assembly_Or_Dummy == True){
  85      $TableHeader = '<tr>
  86                          <th class="ascending">' . _('Location') . '</th>
  87                          <th class="ascending">' . _('Demand') . '</th>
  88                      </tr>';
  89  } else {
  90      $TableHeader = '<tr>
  91                          <th class="ascending">' . _('Location') . '</th>
  92                          <th class="ascending">' . _('Bin Location') . '</th>
  93                          <th class="ascending">' . _('Quantity On Hand') . '</th>
  94                          <th class="ascending">' . _('Re-Order Level') . '</th>
  95                          <th class="ascending">' . _('Demand') . '</th>
  96                          <th class="ascending">' . _('In Transit') . '</th>
  97                          <th class="ascending">' . _('Available') . '</th>
  98                          <th class="ascending">' . _('On Order') . '</th>
  99                      </tr>';
 100  }
 101  echo $TableHeader;
 102  $j = 1;
 103  $k=0; //row colour counter
 104  
 105  while ($myrow=DB_fetch_array($LocStockResult)) {
 106  
 107      if ($k==1){
 108          echo '<tr class="EvenTableRows">';
 109          $k=0;
 110      } else {
 111          echo '<tr class="OddTableRows">';
 112          $k=1;
 113      }
 114  
 115      $sql = "SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
 116              FROM salesorderdetails INNER JOIN salesorders
 117              ON salesorders.orderno = salesorderdetails.orderno
 118              WHERE salesorders.fromstkloc='" . $myrow['loccode'] . "'
 119              AND salesorderdetails.completed=0
 120              AND salesorders.quotation=0
 121              AND salesorderdetails.stkcode='" . $StockID . "'";
 122  
 123      $ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 124      $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 125  
 126      if (DB_num_rows($DemandResult)==1){
 127        $DemandRow = DB_fetch_row($DemandResult);
 128        $DemandQty =  $DemandRow[0];
 129      } else {
 130        $DemandQty =0;
 131      }
 132  
 133      //Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
 134      $sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
 135              FROM salesorderdetails INNER JOIN salesorders
 136              ON salesorders.orderno = salesorderdetails.orderno
 137              INNER JOIN bom
 138              ON salesorderdetails.stkcode=bom.parent
 139              INNER JOIN stockmaster
 140              ON stockmaster.stockid=bom.parent
 141              WHERE salesorders.fromstkloc='" . $myrow['loccode'] . "'
 142              AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
 143              AND bom.component='" . $StockID . "'
 144              AND stockmaster.mbflag='A'
 145              AND salesorders.quotation=0";
 146  
 147      $ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 148      $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 149  
 150      if (DB_num_rows($DemandResult)==1){
 151          $DemandRow = DB_fetch_row($DemandResult);
 152          $DemandQty += $DemandRow[0];
 153      }
 154  
 155      //Also the demand for the item as a component of works orders
 156  
 157      $sql = "SELECT SUM(qtypu*(woitems.qtyreqd - woitems.qtyrecd)) AS woqtydemo
 158              FROM woitems INNER JOIN worequirements
 159              ON woitems.stockid=worequirements.parentstockid
 160              INNER JOIN workorders
 161              ON woitems.wo=workorders.wo
 162              AND woitems.wo=worequirements.wo
 163              WHERE workorders.loccode='" . $myrow['loccode'] . "'
 164              AND worequirements.stockid='" . $StockID . "'
 165              AND workorders.closed=0";
 166  
 167      $ErrMsg = _('The workorder component demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 168      $DemandResult = DB_query($sql,$db,$ErrMsg,$DbgMsg);
 169  
 170      if (DB_num_rows($DemandResult)==1){
 171          $DemandRow = DB_fetch_row($DemandResult);
 172          $DemandQty += $DemandRow[0];
 173      }
 174  
 175      if ($Its_A_KitSet_Assembly_Or_Dummy == False){
 176  
 177          $sql="SELECT SUM(purchorderdetails.quantityord*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END) -
 178                              purchorderdetails.quantityrecd*(CASE WHEN purchdata.conversionfactor IS NULL THEN 1 ELSE purchdata.conversionfactor END))
 179              FROM purchorders LEFT JOIN purchorderdetails
 180              ON purchorders.orderno=purchorderdetails.orderno
 181              LEFT JOIN purchdata ON purchorders.supplierno=purchdata.supplierno
 182                  AND purchorderdetails.itemcode=purchdata.stockid
 183              WHERE purchorderdetails.itemcode='" . $StockID . "'
 184              AND purchorders.intostocklocation='" . $myrow['loccode'] . "'
 185              AND (purchorders.status<>'Cancelled'
 186              AND purchorders.status<>'Pending'
 187              AND purchorders.status<>'Rejected'
 188              AND purchorders.status<>'Completed')";
 189          $ErrMsg = _('The quantity on order for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 190          $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
 191  
 192          if (DB_num_rows($QOOResult)==1){
 193              $QOORow = DB_fetch_row($QOOResult);
 194              $QOO =  $QOORow[0];
 195          } else {
 196              $QOO = 0;
 197          }
 198  
 199          //Also the on work order quantities
 200          $sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo
 201                  FROM woitems INNER JOIN workorders
 202                  ON woitems.wo=workorders.wo
 203                  WHERE workorders.closed=0
 204                  AND workorders.loccode='" . $myrow['loccode'] . "'
 205                  AND woitems.stockid='" . $StockID . "'";
 206          $ErrMsg = _('The quantity on work orders for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
 207          $QOOResult = DB_query($sql,$db,$ErrMsg, $DbgMsg);
 208  
 209          if (DB_num_rows($QOOResult)==1){
 210              $QOORow = DB_fetch_row($QOOResult);
 211              $QOO +=  $QOORow[0];
 212          }
 213  
 214          $InTransitSQL="SELECT SUM(shipqty-recqty) as intransit
 215                          FROM loctransfers
 216                          WHERE stockid='" . $StockID . "'
 217                              AND shiploc='".$myrow['loccode']."'";
 218          $InTransitResult=DB_query($InTransitSQL, $db);
 219          $InTransitRow=DB_fetch_array($InTransitResult);
 220          if ($InTransitRow['intransit']!='') {
 221              $InTransitQuantityOut=-$InTransitRow['intransit'];
 222          } else {
 223              $InTransitQuantityOut=0;
 224          }
 225  
 226          $InTransitSQL="SELECT SUM(-shipqty+recqty) as intransit
 227                          FROM loctransfers
 228                          WHERE stockid='" . $StockID . "'
 229                              AND recloc='".$myrow['loccode']."'";
 230          $InTransitResult=DB_query($InTransitSQL, $db);
 231          $InTransitRow=DB_fetch_array($InTransitResult);
 232          if ($InTransitRow['intransit']!='') {
 233              $InTransitQuantityIn=-$InTransitRow['intransit'];
 234          } else {
 235              $InTransitQuantityIn=0;
 236          }
 237  
 238          if (($InTransitQuantityIn+$InTransitQuantityOut) < 0) {
 239              $Available = $myrow['quantity'] - $DemandQty + ($InTransitQuantityIn+$InTransitQuantityOut);
 240          } else {
 241              $Available = $myrow['quantity'] - $DemandQty;
 242          }
 243  
 244          echo '<td>' . $myrow['locationname'] . '</td>
 245                <td><input type="text" name="BinLocation' . $myrow['loccode'] . '" value="' . $myrow['bin'] . '" maxlength="10" size="11" onchange="ReloadForm(UpdateBinLocations)"/></td>';
 246  
 247          printf('<td class="number">%s</td>
 248                  <td class="number">%s</td>
 249                  <td class="number">%s</td>
 250                  <td class="number">%s</td>
 251                  <td class="number">%s</td>
 252                  <td class="number">%s</td>',
 253                  locale_number_format($myrow['quantity'], $DecimalPlaces),
 254                  locale_number_format($myrow['reorderlevel'], $DecimalPlaces),
 255                  locale_number_format($DemandQty, $DecimalPlaces),
 256                  locale_number_format($InTransitQuantityIn+$InTransitQuantityOut, $DecimalPlaces),
 257                  locale_number_format($Available, $DecimalPlaces),
 258                  locale_number_format($QOO, $DecimalPlaces)
 259                  );
 260  
 261          if ($Serialised ==1){ /*The line is a serialised item*/
 262  
 263              echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Serialised=Yes&amp;Location=' . $myrow['loccode'] . '&amp;StockID=' .$StockID . '">' . _('Serial Numbers') . '</tr>';
 264          } elseif ($Controlled==1){
 265              echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Location=' . $myrow['loccode'] . '&amp;StockID=' .$StockID . '">' . _('Batches') . '</a></td></tr>';
 266          }else{
 267              echo '</tr>';
 268          }
 269  
 270      } else {
 271      /* It must be a dummy, assembly or kitset part */
 272  
 273          printf('<td>%s</td>
 274                  <td class="number">%s</td>
 275                  </tr>',
 276                  $myrow['locationname'],
 277                  locale_number_format($DemandQty, $DecimalPlaces));
 278      }
 279  //end of page full new headings if
 280  }
 281  //end of while loop
 282  echo '</tbody><tr>
 283          <td></td>
 284          <td><input type="submit" name="UpdateBinLocations" value="' . _('Update Bins') . '" /></td>
 285      </tr>
 286      </table>';
 287  
 288  if (isset($_GET['DebtorNo'])){
 289      $DebtorNo = trim(mb_strtoupper($_GET['DebtorNo']));
 290  } elseif (isset($_POST['DebtorNo'])){
 291      $DebtorNo = trim(mb_strtoupper($_POST['DebtorNo']));
 292  } elseif (isset($_SESSION['CustomerID'])){
 293      $DebtorNo=$_SESSION['CustomerID'];
 294  }
 295  
 296  if ($DebtorNo) { /* display recent pricing history for this debtor and this stock item */
 297  
 298      $sql = "SELECT stockmoves.trandate,
 299                  stockmoves.qty,
 300                  stockmoves.price,
 301                  stockmoves.discountpercent
 302              FROM stockmoves
 303              WHERE stockmoves.debtorno='" . $DebtorNo . "'
 304                  AND stockmoves.type=10
 305                  AND stockmoves.stockid = '" . $StockID . "'
 306                  AND stockmoves.hidemovt=0
 307              ORDER BY stockmoves.trandate DESC";
 308  
 309      /* only show pricing history for sales invoices - type=10 */
 310  
 311      $ErrMsg = _('The stock movements for the selected criteria could not be retrieved because') . ' - ';
 312      $DbgMsg = _('The SQL that failed was');
 313  
 314      $MovtsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
 315  
 316      $k=1;
 317      while ($myrow=DB_fetch_array($MovtsResult)) {
 318        if ($LastPrice != $myrow['price']
 319              OR $LastDiscount != $myrow['discount']) { /* consolidate price history for records with same price/discount */
 320          if (isset($qty)) {
 321              $DateRange=ConvertSQLDate($FromDate);
 322              if ($FromDate != $ToDate) {
 323                  $DateRange .= ' - ' . ConvertSQLDate($ToDate);
 324               }
 325              $PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
 326              $k++;
 327              if ($k > 9) {
 328                    break; /* 10 price records is enough to display */
 329                  }
 330              if ($myrow['trandate'] < FormatDateForSQL(DateAdd(date($_SESSION['DefaultDateFormat']),'y', -1))) {
 331                break; /* stop displaying pirce history more than a year old once we have at least one  to display */
 332                 }
 333          }
 334          $LastPrice = $myrow['price'];
 335          $LastDiscount = $myrow['discountpercent'];
 336          $ToDate = $myrow['trandate'];
 337          $qty = 0;
 338        }
 339        $qty += $myrow['qty'];
 340        $FromDate = $myrow['trandate'];
 341      }
 342      if (isset($qty)) {
 343          $DateRange = ConvertSQLDate($FromDate);
 344          if ($FromDate != $ToDate) {
 345                 $DateRange .= ' - '.ConvertSQLDate($ToDate);
 346          }
 347          $PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
 348      }
 349      if (isset($PriceHistory)) {
 350        echo '<br />
 351              <table class="selection">
 352              <tr>
 353                  <th colspan="4"><font color="navy" size="2">' . _('Pricing history for sales of') . ' ' . $StockID . ' ' . _('to') . ' ' . $DebtorNo . '</font></th>
 354              </tr><tbody>';
 355        $TableHeader = '<tr>
 356                          <th class="ascending">' . _('Date Range') . '</th>
 357                          <th class="ascending">' . _('Quantity') . '</th>
 358                          <th class="ascending">' . _('Price') . '</th>
 359                          <th class="ascending">' . _('Discount') . '</th>
 360                      </tr>';
 361  
 362        $j = 0;
 363        $k = 0; //row colour counter
 364  
 365        foreach($PriceHistory as $PreviousPrice) {
 366          $j--;
 367          if ($j < 0 ){
 368              $j = 11;
 369              echo $TableHeader;
 370          }
 371  
 372          if ($k==1){
 373              echo '<tr class="EvenTableRows">';
 374              $k=0;
 375          } else {
 376              echo '<tr class="OddTableRows">';
 377              $k=1;
 378          }
 379  
 380              printf('<td>%s</td>
 381                      <td class="number">%s</td>
 382                      <td class="number">%s</td>
 383                      <td class="number">%s%%</td>
 384                      </tr>',
 385                      $ph[0],
 386                      locale_number_format($PreviousPrice[1],$DecimalPlaces),
 387                      locale_number_format($PreviousPrice[2],$_SESSION['CompanyRecord']['decimalplaces']),
 388                      locale_number_format($PreviousPrice[3]*100,2));
 389        }
 390       echo '</tbody></table>';
 391       }
 392      //end of while loop
 393      else {
 394        echo '<p>' . _('No history of sales of') . ' ' . $StockID . ' ' . _('to') . ' ' . $DebtorNo;
 395      }
 396  }//end of displaying price history for a debtor
 397  
 398  echo '<br /><a href="' . $RootPath . '/StockMovements.php?StockID=' . $StockID . '">' . _('Show Movements') . '</a>
 399      <br /><a href="' . $RootPath . '/StockUsage.php?StockID=' . $StockID . '">' . _('Show Usage') . '</a>
 400      <br /><a href="' . $RootPath . '/SelectSalesOrder.php?SelectedStockItem=' . $StockID . '">' . _('Search Outstanding Sales Orders') . '</a>
 401      <br /><a href="' . $RootPath . '/SelectCompletedOrder.php?SelectedStockItem=' . $StockID . '">' . _('Search Completed Sales Orders') . '</a>';
 402  if ($Its_A_KitSet_Assembly_Or_Dummy ==False){
 403      echo '<br /><a href="' . $RootPath . '/PO_SelectOSPurchOrder.php?SelectedStockItem=' . $StockID . '">' . _('Search Outstanding Purchase Orders') . '</a>';
 404  }
 405  
 406  echo '</div></form>';
 407  include ('includes/footer.inc');
 408  
 409  ?>

title

Description

title

Description

title

Description

title

title

Body