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