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