1<?php
2/* Shows the bill of material indented for each level */
3
4// BOMIndented.php - Indented Bill of Materials
5
6include('includes/session.php');
7
8if (isset($_POST['PrintPDF'])) {
9
10	include('includes/PDFStarter.php');
11	$pdf->addInfo('Title',_('Indented BOM Listing'));
12	$pdf->addInfo('Subject',_('Indented BOM Listing'));
13    $FontSize=9;
14	$PageNumber=1;
15	$line_height=12;
16
17	$sql = "DROP TABLE IF EXISTS tempbom";
18	$result = DB_query($sql);
19	$sql = "DROP TABLE IF EXISTS passbom";
20	$result = DB_query($sql);
21	$sql = "DROP TABLE IF EXISTS passbom2";
22	$result = DB_query($sql);
23	$sql = "CREATE TEMPORARY TABLE passbom (
24				part char(20),
25				sortpart text) DEFAULT CHARSET=utf8";
26	$ErrMsg = _('The SQL to create passbom failed with the message');
27	$result = DB_query($sql,$ErrMsg);
28
29	$sql = "CREATE TEMPORARY TABLE tempbom (
30				parent char(20),
31				component char(20),
32				sortpart text,
33				level int,
34				workcentreadded char(5),
35				loccode char(5),
36				effectiveafter date,
37				effectiveto date,
38				quantity double) DEFAULT CHARSET=utf8";
39	$result = DB_query($sql,_('Create of tempbom failed because'));
40	// First, find first level of components below requested assembly
41	// Put those first level parts in passbom, use COMPONENT in passbom
42	// to link to PARENT in bom to find next lower level and accumulate
43	// those parts into tempbom
44
45	// This finds the top level
46	$sql = "INSERT INTO passbom (part, sortpart)
47			   SELECT bom.component AS part,
48					  CONCAT(bom.parent,bom.component) AS sortpart
49			  FROM bom
50			  WHERE bom.parent ='" . $_POST['Part'] . "'
51              AND bom.effectiveafter <= '" . date('Y-m-d') . "'
52              AND bom.effectiveto > '" . date('Y-m-d') . "'";
53	$result = DB_query($sql);
54
55	$LevelCounter = 2;
56	// $LevelCounter is the level counter
57	$sql = "INSERT INTO tempbom (
58				parent,
59				component,
60				sortpart,
61				level,
62				workcentreadded,
63				loccode,
64				effectiveafter,
65				effectiveto,
66				quantity)
67			  SELECT bom.parent,
68					 bom.component,
69					 CONCAT(bom.parent,bom.component) AS sortpart,
70					 " . $LevelCounter . " AS level,
71					 bom.workcentreadded,
72					 bom.loccode,
73					 bom.effectiveafter,
74					 bom.effectiveto,
75					 bom.quantity
76			  FROM bom
77			  INNER JOIN locationusers ON locationusers.loccode=bom.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
78			  WHERE bom.parent ='" . $_POST['Part'] . "'
79              AND bom.effectiveafter <= '" . date('Y-m-d') . "'
80              AND bom.effectiveto > '" . date('Y-m-d') . "'";
81	$result = DB_query($sql);
82	//echo "<br />sql is $sql<br />";
83	// This while routine finds the other levels as long as $ComponentCounter - the
84	// component counter - finds there are more components that are used as
85	// assemblies at lower levels
86
87	$ComponentCounter = 1;
88	if ($_POST['Levels'] == 'All') {
89		while ($ComponentCounter > 0) {
90			$LevelCounter++;
91			$sql = "INSERT INTO tempbom (
92					parent,
93					component,
94					sortpart,
95					level,
96					workcentreadded,
97					loccode,
98					effectiveafter,
99					effectiveto,
100					quantity)
101				  SELECT bom.parent,
102						 bom.component,
103						 CONCAT(passbom.sortpart,bom.component) AS sortpart,
104						 $LevelCounter as level,
105						 bom.workcentreadded,
106						 bom.loccode,
107						 bom.effectiveafter,
108						 bom.effectiveto,
109						 bom.quantity
110				FROM bom
111                 INNER JOIN passbom ON bom.parent = passbom.part
112				 INNER JOIN locationusers ON locationusers.loccode=bom.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
113				WHERE bom.effectiveafter <= '" . date('Y-m-d') . "'
114				AND bom.effectiveto > '" . date('Y-m-d') . "'";
115			$result = DB_query($sql);
116
117			$sql = "DROP TABLE IF EXISTS passbom2";
118			$result = DB_query($sql);
119
120			$sql = "ALTER TABLE passbom RENAME AS passbom2";
121			$result = DB_query($sql);
122
123			$sql = "DROP TABLE IF EXISTS passbom";
124			$result = DB_query($sql);
125
126			$sql = "CREATE TEMPORARY TABLE passbom (
127								part char(20),
128								sortpart text) DEFAULT CHARSET=utf8";
129			$result = DB_query($sql);
130
131
132			$sql = "INSERT INTO passbom (part, sortpart)
133					   SELECT bom.component AS part,
134							  CONCAT(passbom2.sortpart,bom.component) AS sortpart
135					   FROM bom,passbom2
136					   WHERE bom.parent = passbom2.part
137                       AND bom.effectiveafter <= '" . date('Y-m-d') . "'
138                       AND bom.effectiveto > '" . date('Y-m-d') . "'";
139			$result = DB_query($sql);
140
141
142			$sql = "SELECT COUNT(*) FROM bom,passbom WHERE bom.parent = passbom.part";
143			$result = DB_query($sql);
144
145			$myrow = DB_fetch_row($result);
146			$ComponentCounter = $myrow[0];
147
148		} // End of while $ComponentCounter > 0
149	} // End of if $_POST['Levels']
150
151	if (DB_error_no() !=0) {
152	  $Title = _('Indented BOM Listing') . ' - ' . _('Problem Report');
153	  include('includes/header.php');
154	   prnMsg( _('The Indented BOM Listing could not be retrieved by the SQL because') . ' '  . DB_error_msg(),'error');
155	   echo '<br /><a href="' .$RootPath .'/index.php">' . _('Back to the menu') . '</a>';
156	   if ($debug==1){
157	      echo '<br />' . $sql;
158	   }
159	   include('includes/footer.php');
160	   exit;
161	}
162
163
164    $sql = "SELECT stockmaster.stockid,
165                   stockmaster.description
166              FROM stockmaster
167              WHERE stockid = " . "'" . $_POST['Part'] . "'";
168	$result = DB_query($sql);
169	$myrow = DB_fetch_array($result);
170	$assembly = $_POST['Part'];
171	$assemblydesc = $myrow['description'];
172
173	PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,
174	                   $Right_Margin,$assemblydesc);
175
176    $Tot_Val=0;
177    $sql = "SELECT tempbom.*,
178				stockmaster.description,
179				stockmaster.mbflag,
180				stockmaster.units
181			FROM tempbom,stockmaster
182			WHERE tempbom.component = stockmaster.stockid
183			ORDER BY sortpart";
184	$result = DB_query($sql);
185
186	// $fill is used to alternate between lines with transparent and painted background
187	$fill = false;
188	$pdf->SetFillColor(224,235,255);
189
190	$ListCount = DB_num_rows($result);
191
192	while ($myrow = DB_fetch_array($result)){
193
194		$YPos -=$line_height;
195		$FontSize=8;
196
197		$FormatedEffectiveAfter = ConvertSQLDate($myrow['effectiveafter']);
198		$FormatedEffectiveTo = ConvertSQLDate($myrow['effectiveto']);
199
200
201		if ($_POST['Fill'] == 'yes'){
202		    $fill=!$fill;
203		}
204
205		// Parameters for addTextWrap are defined in /includes/class.pdf.php
206		// 1) X position 2) Y position 3) Width
207		// 4) Height 5) Text 6) Alignment 7) Border 8) Fill - True to use SetFillColor
208		// and False to set to transparent
209		$pdf->addTextWrap($Left_Margin+($myrow['level'] * 5),$YPos,90,$FontSize,$myrow['component'],'left',0,$fill);
210		$pdf->addTextWrap(160,$YPos,20,$FontSize,$myrow['mbflag'],'left',0,$fill);
211		$pdf->addTextWrap(180,$YPos,165,$FontSize,$myrow['description'],'left',0,$fill);
212		$pdf->addTextWrap(345,$YPos,30,$FontSize,$myrow['loccode'],'left',0,$fill);
213		$pdf->addTextWrap(375,$YPos,25,$FontSize,$myrow['workcentreadded'],'left',0,$fill);
214		$pdf->addTextWrap(400,$YPos,45,$FontSize,locale_number_format($myrow['quantity'],'Variable'),'right',0,$fill);
215		$pdf->addTextWrap(445,$YPos,20,$FontSize,$myrow['units'],'left',0,$fill);
216		$pdf->addTextWrap(465,$YPos,50,$FontSize,$FormatedEffectiveAfter,'left',0,$fill);
217		$pdf->addTextWrap(515,$YPos,50,$FontSize,$FormatedEffectiveTo,'left',0,$fill);
218
219		if ($YPos < $Bottom_Margin + $line_height){
220		   PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,
221	                   $Right_Margin,$assemblydesc);
222		}
223
224	} /*end while loop */
225
226	$FontSize =10;
227	$YPos -= (2*$line_height);
228
229	if ($YPos < $Bottom_Margin + $line_height){
230		   PrintHeader($pdf,$YPos,$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,$Page_Width,
231	                   $Right_Margin,$assemblydesc);
232	}
233
234    if ($ListCount == 0) {
235			$Title = _('Print Indented BOM Listing Error');
236			include('includes/header.php');
237			prnMsg(_('There were no items for the selected assembly'),'error');
238			echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
239			include('includes/footer.php');
240			exit;
241	} else {
242		$pdf->OutputD($_SESSION['DatabaseName'] . '_Bill_Of_Material_Indented_' . date('Y-m-d').'.pdf');
243		$pdf->__destruct();
244	}
245
246} else { /*The option to print PDF was not hit so display form */
247
248	$Title=_('Indented BOM Listing');
249	include('includes/header.php');
250        echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p><br />';
251
252	echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
253          <div>
254          <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
255			<table class="selection">';
256	echo '<tr>
257			<td>' . _('Part') . ':</td>
258			<td><input type="text" name="Part" autofocus="autofocus" required="required" data-type="no-illegal-chars" title="' . _('Enter the item code of parent item to list the bill of material for') .  '" size="20" /></td>
259		</tr>
260		<tr>
261			<td>' . _('Levels') . ':</td>
262			<td><select name="Levels">
263				<option selected="selected" value="All">' . _('All Levels') . '</option>
264				<option value="One">' . _('One Level') . '</option>
265				</select>
266			</td>
267		</tr>
268		<tr>
269			<td>' . _('Print Option') . ':</td>
270			<td><select name="Fill">
271				<option selected="selected" value="yes">' . _('Print With Alternating Highlighted Lines') . '</option>
272				<option value="no">' . _('Plain Print') . '</option>
273				</select>
274			</td>
275		</tr>
276		</table>
277		<div class="centre">
278            <br />
279			<input type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
280		</div>
281        </div>
282        </form>';
283
284	include('includes/footer.php');
285
286} /*end of else not PrintPDF */
287
288
289function PrintHeader(&$pdf,&$YPos,&$PageNumber,$Page_Height,$Top_Margin,$Left_Margin,
290                     $Page_Width,$Right_Margin,$assemblydesc) {
291
292	$line_height=12;
293	/*PDF page header for Indented BOM Listing report */
294	if ($PageNumber>1){
295		$pdf->newPage();
296	}
297
298	$FontSize=9;
299	$YPos= $Page_Height-$Top_Margin-5;
300
301	$pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,$_SESSION['CompanyRecord']['coyname']);
302
303	$YPos -=$line_height;
304
305	$pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,_('Indented BOM Listing'));
306	$pdf->addTextWrap($Page_Width-$Right_Margin-105,$YPos,160,$FontSize,_('Printed') . ': ' .
307		 Date($_SESSION['DefaultDateFormat']) . '   ' . _('Page') . ' ' . $PageNumber,'left');
308
309	$YPos -=(2*$line_height);
310
311	/*set up the headings */
312	$Xpos = $Left_Margin+1;
313
314	$pdf->addTextWrap($Xpos,$YPos,90,$FontSize,_('Part Number'), 'left');
315	$pdf->addTextWrap(160,$YPos,20,$FontSize,_('M/B'), 'left');
316	$pdf->addTextWrap(180,$YPos,165,$FontSize,_('Description'), 'center');
317	$pdf->addTextWrap(345,$YPos,30,$FontSize,_('Locn'), 'left');
318	$pdf->addTextWrap(375,$YPos,25,$FontSize,_('WC'), 'left');
319	$pdf->addTextWrap(400,$YPos,45,$FontSize,_('Quantity'), 'right');
320	$pdf->addTextWrap(445,$YPos,20,$FontSize,_('UOM'), 'left');
321	$pdf->addTextWrap(465,$YPos,50,$FontSize,_('From Date'), 'left');
322	$pdf->addTextWrap(515,$YPos,50,$FontSize,_('To Date'), 'left');
323	$YPos =$YPos - $line_height;
324
325	$FontSize=8;
326	$YPos =$YPos - (2*$line_height);
327
328	$pdf->addTextWrap($Left_Margin+1,$YPos,40,$FontSize,_('Assembly').':','',0);
329	$pdf->addTextWrap(85,$YPos,100,$FontSize,mb_strtoupper($_POST['Part']),'',0);
330	$pdf->addTextWrap(185,$YPos,150,$FontSize,$assemblydesc,'',0);
331	$YPos -=(2*$line_height);
332	$Xpos = $Left_Margin+5;
333
334	$PageNumber++;
335
336} // End of PrintHeader function
337
338?>