forked from timschofield/webERP
-
Notifications
You must be signed in to change notification settings - Fork 89
/
BOMExtendedQty.php
342 lines (304 loc) · 12.8 KB
/
BOMExtendedQty.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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
<?php
// BOMExtendedQty.php - Quantity Extended Bill of Materials
include('includes/session.php');
if (isset($_POST['PrintPDF'])) {
include('includes/PDFStarter.php');
$pdf->addInfo('Title',_('Quantity Extended BOM Listing'));
$pdf->addInfo('Subject',_('Quantity Extended BOM Listing'));
$FontSize=9;
$PageNumber=1;
$line_height=12;
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,$Right_Margin);
if (!$_POST['Quantity'] or !is_numeric(filter_number_format($_POST['Quantity']))) {
$_POST['Quantity'] = 1;
}
$result = DB_query("DROP TABLE IF EXISTS tempbom");
$result = DB_query("DROP TABLE IF EXISTS passbom");
$result = DB_query("DROP TABLE IF EXISTS passbom2");
$sql = "CREATE TEMPORARY TABLE passbom (
part char(20),
extendedqpa double,
sortpart text) DEFAULT CHARSET=utf8";
$ErrMsg = _('The SQL to create passbom failed with the message');
$result = DB_query($sql,$ErrMsg);
$sql = "CREATE TEMPORARY TABLE tempbom (
parent char(20),
component char(20),
sortpart text,
level int,
workcentreadded char(5),
loccode char(5),
effectiveafter date,
effectiveto date,
quantity double) DEFAULT CHARSET=utf8";
$result = DB_query($sql,_('Create of tempbom failed because'));
// First, find first level of components below requested assembly
// Put those first level parts in passbom, use COMPONENT in passbom
// to link to PARENT in bom to find next lower level and accumulate
// those parts into tempbom
// This finds the top level
$sql = "INSERT INTO passbom (part, extendedqpa, sortpart)
SELECT bom.component AS part,
(" . filter_number_format($_POST['Quantity']) . " * bom.quantity) as extendedqpa,
CONCAT(bom.parent,bom.component) AS sortpart
FROM bom
WHERE bom.parent ='" . $_POST['Part'] . "'
AND bom.effectiveafter <= '" . date('Y-m-d') . "'
AND bom.effectiveto > '" . date('Y-m-d') . "'";
$result = DB_query($sql);
$LevelCounter = 2;
// $LevelCounter is the level counter
$sql = "INSERT INTO tempbom (
parent,
component,
sortpart,
level,
workcentreadded,
loccode,
effectiveafter,
effectiveto,
quantity)
SELECT bom.parent,
bom.component,
CONCAT(bom.parent,bom.component) AS sortpart,"
. $LevelCounter . " as level,
bom.workcentreadded,
bom.loccode,
bom.effectiveafter,
bom.effectiveto,
(" . filter_number_format($_POST['Quantity']) . " * bom.quantity) as extendedqpa
FROM bom
WHERE bom.parent ='" . $_POST['Part'] . "'
AND bom.effectiveafter <= '" . date('Y-m-d') . "'
AND bom.effectiveto > '" . date('Y-m-d') . "'";
$result = DB_query($sql);
//echo "<br />sql is $sql<br />";
// This while routine finds the other levels as long as $ComponentCounter - the
// component counter finds there are more components that are used as
// assemblies at lower levels
$ComponentCounter = 1;
while ($ComponentCounter > 0) {
$LevelCounter++;
$sql = "INSERT INTO tempbom (
parent,
component,
sortpart,
level,
workcentreadded,
loccode,
effectiveafter,
effectiveto,
quantity)
SELECT bom.parent,
bom.component,
CONCAT(passbom.sortpart,bom.component) AS sortpart,
" . $LevelCounter . " as level,
bom.workcentreadded,
bom.loccode,
bom.effectiveafter,
bom.effectiveto,
(bom.quantity * passbom.extendedqpa)
FROM bom,passbom
WHERE bom.parent = passbom.part
AND bom.effectiveafter <= '" . date('Y-m-d') . "'
AND bom.effectiveto > '" . date('Y-m-d') . "'";
$result = DB_query($sql);
$result = DB_query("DROP TABLE IF EXISTS passbom2");
$result = DB_query("ALTER TABLE passbom RENAME AS passbom2");
$result = DB_query("DROP TABLE IF EXISTS passbom");
$sql = "CREATE TEMPORARY TABLE passbom (part char(20),
extendedqpa decimal(10,3),
sortpart text) DEFAULT CHARSET=utf8";
$result = DB_query($sql);
$sql = "INSERT INTO passbom (part,
extendedqpa,
sortpart)
SELECT bom.component AS part,
(bom.quantity * passbom2.extendedqpa),
CONCAT(passbom2.sortpart,bom.component) AS sortpart
FROM bom
INNER JOIN passbom2
ON bom.parent = passbom2.part
WHERE bom.effectiveafter <= '" . date('Y-m-d') . "'
AND bom.effectiveto > '" . date('Y-m-d') . "'";
$result = DB_query($sql);
$sql = "SELECT COUNT(bom.parent) AS components
FROM bom
INNER JOIN passbom
ON bom.parent = passbom.part
GROUP BY passbom.part";
$result = DB_query($sql);
$myrow = DB_fetch_array($result);
$ComponentCounter = $myrow['components'];
} // End of while $ComponentCounter > 0
if (DB_error_no() !=0) {
$Title = _('Quantity Extended BOM Listing') . ' - ' . _('Problem Report');
include('includes/header.php');
prnMsg( _('The Quantiy Extended BOM Listing could not be retrieved by the SQL because') . ' ' . DB_error_msg(),'error');
echo '<br /><a href="' .$RootPath .'/index.php">' . _('Back to the menu') . '</a>';
if ($debug==1){
echo '<br />' . $sql;
}
include('includes/footer.php');
exit;
}
$Tot_Val=0;
$fill = false;
$pdf->SetFillColor(224,235,255);
$sql = "SELECT tempbom.component,
SUM(tempbom.quantity) as quantity,
stockmaster.description,
stockmaster.decimalplaces,
stockmaster.mbflag,
(SELECT
SUM(locstock.quantity) as invqty
FROM locstock
INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
WHERE locstock.stockid = tempbom.component
GROUP BY locstock.stockid) AS qoh,
(SELECT
SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as netqty
FROM purchorderdetails INNER JOIN purchorders
ON purchorderdetails.orderno=purchorders.orderno
INNER JOIN locationusers ON locationusers.loccode=purchorders.intostocklocation AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
WHERE purchorderdetails.itemcode = tempbom.component
AND purchorderdetails.completed = 0
AND (purchorders.status = 'Authorised' OR purchorders.status='Printed')
GROUP BY purchorderdetails.itemcode) AS poqty,
(SELECT
SUM(woitems.qtyreqd - woitems.qtyrecd) as netwoqty
FROM woitems INNER JOIN workorders
ON woitems.wo = workorders.wo
INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
WHERE woitems.stockid = tempbom.component
AND workorders.closed=0
GROUP BY woitems.stockid) AS woqty
FROM tempbom INNER JOIN stockmaster
ON tempbom.component = stockmaster.stockid
INNER JOIN locationusers ON locationusers.loccode=tempbom.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1
GROUP BY tempbom.component,
stockmaster.description,
stockmaster.decimalplaces,
stockmaster.mbflag";
$result = DB_query($sql);
$ListCount = DB_num_rows($result);
while ($myrow = DB_fetch_array($result)){
// Parameters for addTextWrap are defined in /includes/class.pdf.php
// 1) X position 2) Y position 3) Width
// 4) Height 5) Text 6) Alignment 7) Border 8) Fill - True to use SetFillColor
// and False to set to transparent
$Difference = $myrow['quantity'] - ($myrow['qoh'] + $myrow['poqty'] + $myrow['woqty']);
if (($_POST['Select'] == 'All') or ($Difference > 0)) {
$YPos -=$line_height;
$FontSize=8;
// Use to alternate between lines with transparent and painted background
if ($_POST['Fill'] == 'yes'){
$fill=!$fill;
}
$pdf->addTextWrap($Left_Margin+1,$YPos,90,$FontSize,$myrow['component'],'',0,$fill);
$pdf->addTextWrap(140,$YPos,30,$FontSize,$myrow['mbflag'],'',0,$fill);
$pdf->addTextWrap(170,$YPos,140,$FontSize,$myrow['description'],'',0,$fill);
$pdf->addTextWrap(310,$YPos,50,$FontSize,locale_number_format($myrow['quantity'],$myrow['decimalplaces']),'right',0,$fill);
$pdf->addTextWrap(360,$YPos,40,$FontSize,locale_number_format($myrow['qoh'],$myrow['decimalplaces']),'right',0,$fill);
$pdf->addTextWrap(400,$YPos,40,$FontSize,locale_number_format($myrow['poqty'],$myrow['decimalplaces']),'right',0,$fill);
$pdf->addTextWrap(440,$YPos,40,$FontSize,locale_number_format($myrow['woqty'],$myrow['decimalplaces']),'right',0,$fill);
$pdf->addTextWrap(480,$YPos,50,$FontSize,locale_number_format($Difference,$myrow['decimalplaces']),'right',0,$fill);
}
if ($YPos < $Bottom_Margin + $line_height){
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,$Right_Margin);
}
} /*end while loop */
$FontSize =10;
$YPos -= (2*$line_height);
if ($YPos < $Bottom_Margin + $line_height){
PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,$Right_Margin);
}
if ($ListCount == 0) {
$Title = _('Print Indented BOM Listing Error');
include('includes/header.php');
prnMsg(_('There were no items for the selected assembly'),'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include('includes/footer.php');
exit;
} else {
$pdf->OutputD($_SESSION['DatabaseName'] . '_BOM_Extended_Qty_' . date('Y-m-d').'.pdf');
$pdf->__destruct();
}
} else { /*The option to print PDF was not hit so display form */
$Title=_('Quantity Extended BOM Listing');
include('includes/header.php');
echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p><br />';
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
<div>
<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<table class="selection">
<tr>
<td>' . _('Part') . ':</td>
<td><input type="text" autofocus="autofocus" required="required" name="Part" size="20" title="' . _('Enter the item code that you wish to display the extended bill of material for') . '" /></td>
</tr>
<tr>
<td>' . _('Quantity') . ':</td>
<td><input type="text" class="number" required="required" name="Quantity" size="4" /></td>
</tr>
<tr>
<td>' . _('Selection Option') . ':</td>
<td><select name="Select">
<option selected="selected" value="All">' . _('Show All Parts') . '</option>
<option value="Shortages">' . _('Only Show Shortages') . '</option>
</select></td>
</tr>
<tr>
<td>' . _('Print Option') . ':</td>
<td><select name="Fill">
<option selected="selected" value="yes">' . _('Print With Alternating Highlighted Lines') . '</option>
<option value="no">' . _('Plain Print') . '</option>
</select></td>
</tr>
</table>
<br />
<br />
<div class="centre">
<br />
<input type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
</div>
</div>
</form>';
include('includes/footer.php');
} /*end of else not PrintPDF */
function PrintHeader(&$pdf,&$YPos,&$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,
$Page_Width,$Right_Margin) {
/*PDF page header for BOMExtendedQTY report */
if ($PageNumber>1){
$pdf->newPage();
}
$line_height=12;
$FontSize=9;
$YPos= $Page_Height-$Top_Margin-5;
$pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,$_SESSION['CompanyRecord']['coyname']);
$YPos -=$line_height;
$pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,_('Extended Quantity BOM Listing For ')
. mb_strtoupper($_POST['Part']));
$pdf->addTextWrap($Page_Width-$Right_Margin-140,$YPos,160,$FontSize,_('Printed') . ': ' .
Date($_SESSION['DefaultDateFormat']) . ' ' . _('Page') . ' ' . $PageNumber,'left');
$YPos -=$line_height;
$pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,_('Build Quantity: ') . locale_number_format($_POST['Quantity'],'Variable'),'left');
$YPos -=(2*$line_height);
/*set up the headings */
$Xpos = $Left_Margin+1;
$pdf->addTextWrap(310,$YPos,50,$FontSize,_('Build'), 'center');
$pdf->addTextWrap(360,$YPos,40,$FontSize,_('On Hand'), 'right');
$pdf->addTextWrap(400,$YPos,40,$FontSize,_('P.O.'), 'right');
$pdf->addTextWrap(440,$YPos,40,$FontSize,_('W.O.'), 'right');
$YPos -=$line_height;
$pdf->addTextWrap($Xpos,$YPos,90,$FontSize,_('Part Number'), 'left');
$pdf->addTextWrap(140,$YPos,30,$FontSize,_('M/B'), 'left');
$pdf->addTextWrap(170,$YPos,140,$FontSize,_('Part Description'), 'left');
$pdf->addTextWrap(310,$YPos,50,$FontSize,_('Quantity'), 'right');
$pdf->addTextWrap(360,$YPos,40,$FontSize,_('Quantity'), 'right');
$pdf->addTextWrap(400,$YPos,40,$FontSize,_('Quantity'), 'right');
$pdf->addTextWrap(440,$YPos,40,$FontSize,_('Quantity'), 'right');
$pdf->addTextWrap(480,$YPos,50,$FontSize,_('Shortage'), 'right');
$YPos =$YPos - (2*$line_height);
$PageNumber++;
} // End of PrintHeader function
?>