/
StockQuantityByDate.php
178 lines (147 loc) · 5.78 KB
/
StockQuantityByDate.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
<?php
/* $Id: StockQuantityByDate.php 6941 2014-10-26 23:18:08Z daintree $ */
include('includes/session.inc');
$Title = _('Stock On Hand By Date');
include('includes/header.inc');
echo '<p class="page_title_text">
<img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Inventory') .
'" alt="" /><b>' . $Title. '</b>
</p>';
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
$sql = "SELECT categoryid, categorydescription FROM stockcategory";
$resultStkLocs = DB_query($sql);
echo '<table class="selection">
<tr>
<td>' . _('For Stock Category') . ':</td>
<td><select name="StockCategory">
<option value="All">' . _('All') . '</option>';
while ($myrow=DB_fetch_array($resultStkLocs)){
if (isset($_POST['StockCategory']) AND $_POST['StockCategory']!='All'){
if ($myrow['categoryid'] == $_POST['StockCategory']){
echo '<option selected="selected" value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
} else {
echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
}
}else {
echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
}
}
echo '</select></td>';
$sql = "SELECT locations.loccode, locationname FROM locations
INNER JOIN locationusers ON locationusers.loccode=locations.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1";
$resultStkLocs = DB_query($sql);
echo '<td>' . _('For Stock Location') . ':</td>
<td><select name="StockLocation"> ';
while ($myrow=DB_fetch_array($resultStkLocs)){
if (isset($_POST['StockLocation']) AND $_POST['StockLocation']!='All'){
if ($myrow['loccode'] == $_POST['StockLocation']){
echo '<option selected="selected" value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
} else {
echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
}
} elseif ($myrow['loccode']==$_SESSION['UserStockLocation']){
echo '<option selected="selected" value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
$_POST['StockLocation']=$myrow['loccode'];
} else {
echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
}
}
echo '</select></td>';
if (!isset($_POST['OnHandDate'])){
$_POST['OnHandDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m'),0,Date('y')));
}
echo '<td>' . _('On-Hand On Date') . ':</td>
<td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="OnHandDate" size="12" maxlength="12" value="' . $_POST['OnHandDate'] . '" /></td></tr>';
echo '<tr>
<td colspan="6">
<div class="centre">
<input type="submit" name="ShowStatus" value="' . _('Show Stock Status') .'" />
</div></td>
</tr>
</table>
</div>
</form>';
$TotalQuantity = 0;
if(isset($_POST['ShowStatus']) AND Is_Date($_POST['OnHandDate'])) {
if ($_POST['StockCategory']=='All') {
$sql = "SELECT stockid,
description,
decimalplaces
FROM stockmaster
WHERE (mbflag='M' OR mbflag='B')";
} else {
$sql = "SELECT stockid,
description,
decimalplaces
FROM stockmaster
WHERE categoryid = '" . $_POST['StockCategory'] . "'
AND (mbflag='M' OR mbflag='B')";
}
$ErrMsg = _('The stock items in the category selected cannot be retrieved because');
$DbgMsg = _('The SQL that failed was');
$StockResult = DB_query($sql, $ErrMsg, $DbgMsg);
$SQLOnHandDate = FormatDateForSQL($_POST['OnHandDate']);
echo '<br />
<table class="selection">';
$tableheader = '<tr>
<th>' . _('Item Code') . '</th>
<th>' . _('Description') . '</th>
<th>' . _('Quantity On Hand') . '</th>
</tr>';
echo $tableheader;
while ($myrows=DB_fetch_array($StockResult)) {
$sql = "SELECT stockid,
newqoh
FROM stockmoves
WHERE stockmoves.trandate <= '". $SQLOnHandDate . "'
AND stockid = '" . $myrows['stockid'] . "'
AND loccode = '" . $_POST['StockLocation'] ."'
ORDER BY stkmoveno DESC LIMIT 1";
$ErrMsg = _('The stock held as at') . ' ' . $_POST['OnHandDate'] . ' ' . _('could not be retrieved because');
$LocStockResult = DB_query($sql, $ErrMsg);
$NumRows = DB_num_rows($LocStockResult, $db);
$j = 1;
$k=0; //row colour counter
while ($LocQtyRow=DB_fetch_array($LocStockResult)) {
if ($k==1){
echo '<tr class="OddTableRows">';
$k=0;
} else {
echo '<tr class="EvenTableRows">';
$k=1;
}
if($NumRows == 0){
printf('<td><a target="_blank" href="' . $RootPath . '/StockStatus.php?%s">%s</a></td>
<td>%s</td>
<td class="number">%s</td></tr>',
'StockID=' . mb_strtoupper($myrows['stockid']),
mb_strtoupper($myrows['stockid']),
$myrows['description'],
0);
} else {
printf('<td><a target="_blank" href="' . $RootPath . '/StockStatus.php?%s">%s</a></td>
<td>%s</td>
<td class="number">%s</td></tr>',
'StockID=' . mb_strtoupper($myrows['stockid']),
mb_strtoupper($myrows['stockid']),
$myrows['description'],
locale_number_format($LocQtyRow['newqoh'],$myrows['decimalplaces']));
$TotalQuantity += $LocQtyRow['newqoh'];
}
$j++;
if ($j == 12){
$j=1;
echo $tableheader;
}
//end of page full new headings if
}
}//end of while loop
echo '<tr>
<td>' . _('Total Quantity') . ': ' . $TotalQuantity . '</td>
</tr>
</table>';
}
include('includes/footer.inc');
?>