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?>