1<?php 2 3 4// ReorderLevelLocation.php - Report of reorder level by category 5 6include('includes/session.php'); 7 8$Title=_('Reorder Level Location Reporting'); 9include('includes/header.php'); 10 11echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Inventory') . '" alt="" />' . ' ' . _('Inventory Reorder Level Location Report') . '</p>'; 12 13 14//update database if update pressed 15if (isset($_POST['submit'])){ 16 for ($i=1;$i<count($_POST);$i++){ //loop through the returned customers 17 if (isset($_POST['StockID' . $i]) AND is_numeric(filter_number_format($_POST['ReorderLevel'.$i]))){ 18 $SQLUpdate="UPDATE locstock SET reorderlevel = '" . filter_number_format($_POST['ReorderLevel'.$i]) . "', 19 bin = '" . strtoupper($_POST['BinLocation'.$i]) . "' 20 WHERE loccode = '" . $_POST['StockLocation'] . "' 21 AND stockid = '" . $_POST['StockID' . $i] . "'"; 22 $Result = DB_query($SQLUpdate); 23 } 24 } 25} 26 27if (isset($_POST['submit']) OR isset($_POST['Update'])) { 28 29 if ($_POST['NumberOfDays']==''){ 30 header('Location: ReorderLevelLocation.php'); 31 } 32 33 if($_POST['Sequence']==1){ 34 $Sequence="qtyinvoice DESC, locstock.stockid"; 35 }else{ 36 $Sequence="locstock.stockid"; 37 } 38 39 $sql="SELECT locstock.stockid, 40 description, 41 reorderlevel, 42 bin, 43 quantity, 44 decimalplaces, 45 canupd 46 FROM locstock INNER JOIN stockmaster 47 ON locstock.stockid = stockmaster.stockid 48 INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 49 WHERE stockmaster.categoryid = '" . $_POST['StockCat'] . "' 50 AND locstock.loccode = '" . $_POST['StockLocation'] . "' 51 AND stockmaster.discontinued = 0 52 ORDER BY '" . $Sequence . "' ASC"; 53 54 $result = DB_query($sql); 55 56 $SqlLoc="SELECT locationname 57 FROM locations 58 WHERE loccode='".$_POST['StockLocation']."'"; 59 60 $ResultLocation = DB_query($SqlLoc); 61 $Location=DB_fetch_array($ResultLocation); 62 63 echo'<p class="page_title_text"><strong>' . _('Location : ') . '' . $Location['locationname'] . ' </strong></p>'; 64 echo'<p class="page_title_text"><strong>' . _('Number Of Days Sales : ') . '' . locale_number_format($_POST['NumberOfDays'],0) . '' . _(' Days ') . ' </strong></p>'; 65 66 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" id="Update">'; 67 echo '<div>'; 68 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 69 echo '<table>'; 70 echo '<tr> 71 <th>' . _('Code') . '</th> 72 <th>' . _('Description') . '</th> 73 <th>' . _('Total Invoiced') . '<br />' . _('At Location') . '</th> 74 <th>' . _('On Hand') . '<br />' . _('At All Locations') . '</th> 75 <th>' . _('On Hand') . '<br />' ._('At Location') . '</th> 76 <th>' . _('Reorder Level') . '</th> 77 <th>' . _('Bin Location') . '</th> 78 </tr>'; 79 80 $i=1; 81 while ($myrow=DB_fetch_array($result)) { 82 83 //variable for update data 84 85 echo'<input type="hidden" value="' . $_POST['Sequence'] . '" name="Sequence" /> 86 <input type="hidden" value="' . $_POST['StockLocation'] . '" name="StockLocation" /> 87 <input type="hidden" value="' . $_POST['StockCat'] . '" name="StockCat" /> 88 <input type="hidden" value="' . locale_number_format($_POST['NumberOfDays'],0) . '" name="NumberOfDays" />'; 89 90 91 $SqlInv="SELECT SUM(-qty) AS qtyinvoiced 92 FROM stockmoves 93 WHERE stockid='".$myrow['stockid']."' 94 AND (type=10 OR type=11) 95 AND loccode='" . $_POST['StockLocation'] ."' 96 AND trandate >= '" . FormatDateForSQL(DateAdd(Date($_SESSION['DefaultDateFormat']),'d',-filter_number_format($_POST['NumberOfDays']))) . "'"; 97 98 $ResultInvQty = DB_query($SqlInv); 99 $SalesRow=DB_fetch_array($ResultInvQty); 100 101 102 //get On Hand all 103 //find the quantity onhand item 104 $SqlOH="SELECT SUM(quantity) AS qty 105 FROM locstock 106 INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1 107 WHERE stockid='" . $myrow['stockid'] . "'"; 108 $TotQtyResult = DB_query($SqlOH); 109 $TotQtyRow = DB_fetch_array($TotQtyResult); 110 111 echo '<tr class="striped_row"> 112 <td>' . $myrow['stockid'] . '</td> 113 <td>' . $myrow['description'] . '</td> 114 <td class="number">' . locale_number_format($SalesRow['qtyinvoiced'],$myrow['decimalplaces']) . '</td> 115 <td class="number">' . locale_number_format($TotQtyRow['qty'],$myrow['decimalplaces']) . '</td> 116 <td class="number">' . locale_number_format($myrow['quantity'],$myrow['decimalplaces']) . '</td> 117 <td class="number">'; 118 if ($myrow['canupd']==1) { 119 echo '<input type="text" class="number" name="ReorderLevel' . $i .'" maxlength="10" size="10" value="'. locale_number_format($myrow['reorderlevel'],0) .'" /> 120 <input type="hidden" name="StockID' . $i . '" value="' . $myrow['stockid'] . '" /></td> 121 <td><input type="text" name="BinLocation' . $i .'" maxlength="10" size="10" value="'. $myrow['bin'] .'" />'; 122 } else { 123 echo locale_number_format($myrow['reorderlevel'],0) . '</td><td>' . $myrow['bin'] . '</td>'; 124 } 125 126 echo '</td> 127 </tr> '; 128 $i++; 129 } //end of looping 130 echo'<tr> 131 <td class="centre" colspan="7"> 132 <input type="submit" name="submit" value="' . _('Update') . '" /> 133 </td> 134 </tr> 135 </table> 136 </div> 137 </form>'; 138 139 140} else { /*The option to submit was not hit so display form */ 141 142 143 echo '<div class="page_help_text">' . _('Use this report to display the reorder levels for Inventory items in different categories.') . '</div><br />'; 144 145 echo '<br /> 146 <br /> 147 <form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post"> 148 <div>'; 149 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 150 $sql = "SELECT locations.loccode, 151 locationname 152 FROM locations INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1"; 153 $resultStkLocs = DB_query($sql); 154 echo '<table class="selection"> 155 <tr> 156 <td>' . _('Location') . ':</td> 157 <td><select name="StockLocation"> '; 158 159 while ($myrow=DB_fetch_array($resultStkLocs)){ 160 echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>'; 161 } 162 echo '</select></td></tr>'; 163 164 $SQL="SELECT categoryid, 165 categorydescription 166 FROM stockcategory 167 ORDER BY categorydescription"; 168 169 $result1 = DB_query($SQL); 170 171 echo '<tr><td>' . _('Category') . ':</td> 172 <td><select name="StockCat">'; 173 174 while ($myrow1 = DB_fetch_array($result1)) { 175 echo '<option value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'] . '</option>'; 176 } 177 178 echo '</select></td></tr>'; 179 echo '<tr> 180 <td>' . _('Number Of Days Sales') . ':</td> 181 <td><input type="text" class="number" name="NumberOfDays" maxlength="3" size="4" value="0" /></td> 182 </tr>'; 183 echo '<tr> 184 <td>' . _('Order By') . ':</td> 185 <td><select name="Sequence"> 186 <option value="1">' . _('Total Invoiced') . '</option> 187 <option value="2">' . _('Item Code') . '</option> 188 </select></td> 189 </tr>'; 190 echo '</table> 191 <br /> 192 <div class="centre"> 193 <input type="submit" name="submit" value="' . _('Submit') . '" /> 194 </div>'; 195 echo '</div> 196 </form>'; 197 198} /*end of else not submit */ 199include('includes/footer.php'); 200?>