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