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