1<?php 2/* This script is an utility to change an inventory item code. */ 3/* It uses function ChangeFieldInTable($TableName, $FieldName, $OldValue, 4 $NewValue) from .../includes/MiscFunctions.php.*/ 5 6include ('includes/session.php'); 7$Title = _('UTILITY PAGE Change A Stock Code');// Screen identificator. 8$ViewTopic = 'SpecialUtilities'; // Filename in ManualContents.php's TOC. 9$BookMark = 'Z_ChangeStockCode'; // Anchor's id in the manual's html document. 10include('includes/header.php'); 11echo '<p class="page_title_text"><img alt="" src="'.$RootPath.'/css/'.$Theme. 12 '/images/inventory.png" title="' . 13 _('Change An Inventory Item Code') . '" /> ' .// Icon title. 14 _('Change An Inventory Item Code') . '</p>';// Page title. 15 16include('includes/SQL_CommonFunctions.inc'); 17 18if (isset($_POST['ProcessStockChange'])){ 19 20 $InputError =0; 21 22 $_POST['NewStockID'] = mb_strtoupper($_POST['NewStockID']); 23 24/*First check the stock code exists */ 25 $result=DB_query("SELECT stockid FROM stockmaster WHERE stockid='" . $_POST['OldStockID'] . "'"); 26 if (DB_num_rows($result)==0){ 27 prnMsg(_('The stock code') . ': ' . $_POST['OldStockID'] . ' ' . _('does not currently exist as a stock code in the system'),'error'); 28 $InputError =1; 29 } 30 31 if (ContainsIllegalCharacters($_POST['NewStockID'])){ 32 prnMsg(_('The new stock code to change the old code to contains illegal characters - no changes will be made'),'error'); 33 $InputError =1; 34 } 35 36 if ($_POST['NewStockID']==''){ 37 prnMsg(_('The new stock code to change the old code to must be entered as well'),'error'); 38 $InputError =1; 39 } 40 41 42/*Now check that the new code doesn't already exist */ 43 $result=DB_query("SELECT stockid FROM stockmaster WHERE stockid='" . $_POST['NewStockID'] . "'"); 44 if (DB_num_rows($result)!=0){ 45 echo '<br /><br />'; 46 prnMsg(_('The replacement stock code') . ': ' . $_POST['NewStockID'] . ' ' . _('already exists as a stock code in the system') . ' - ' . _('a unique stock code must be entered for the new code'),'error'); 47 $InputError =1; 48 } 49 50 51 if ($InputError ==0){ // no input errors 52 53 DB_IgnoreForeignKeys(); 54 $result = DB_Txn_Begin(); 55 echo '<br />' . _('Adding the new stock master record'); 56 $sql = "INSERT INTO stockmaster (stockid, 57 categoryid, 58 description, 59 longdescription, 60 units, 61 mbflag, 62 actualcost, 63 lastcost, 64 materialcost, 65 labourcost, 66 overheadcost, 67 lowestlevel, 68 discontinued, 69 controlled, 70 eoq, 71 volume, 72 grossweight, 73 barcode, 74 discountcategory, 75 taxcatid, 76 serialised, 77 perishable, 78 decimalplaces, 79 pansize, 80 shrinkfactor, 81 nextserialno, 82 netweight, 83 lastcostupdate) 84 SELECT '" . $_POST['NewStockID'] . "', 85 categoryid, 86 description, 87 longdescription, 88 units, 89 mbflag, 90 actualcost, 91 lastcost, 92 materialcost, 93 labourcost, 94 overheadcost, 95 lowestlevel, 96 discontinued, 97 controlled, 98 eoq, 99 volume, 100 grossweight, 101 barcode, 102 discountcategory, 103 taxcatid, 104 serialised, 105 perishable, 106 decimalplaces, 107 pansize, 108 shrinkfactor, 109 nextserialno, 110 netweight, 111 lastcostupdate 112 FROM stockmaster 113 WHERE stockid='" . $_POST['OldStockID'] . "'"; 114 115 $DbgMsg = _('The SQL statement that failed was'); 116 $ErrMsg =_('The SQL to insert the new stock master record failed'); 117 $result = DB_query($sql,$ErrMsg,$DbgMsg,true); 118 echo ' ... ' . _('completed'); 119 120 ChangeFieldInTable("locstock", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 121 ChangeFieldInTable("stockmoves", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 122 ChangeFieldInTable("loctransfers", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 123 ChangeFieldInTable("mrpdemands", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 124 125 //check if MRP tables exist before assuming 126 $sql = "SELECT * FROM mrpparameters"; 127 $result = DB_query($sql, '', '', false, false); 128 if (DB_error_no() == 0) { 129 $result = DB_query("SELECT COUNT(*) FROM mrpplannedorders",'','',false,false); 130 if (DB_error_no()==0) { 131 ChangeFieldInTable("mrpplannedorders", "part", $_POST['OldStockID'], $_POST['NewStockID']); 132 } 133 134 $result = DB_query("SELECT * FROM mrprequirements" ,'','',false,false); 135 if (DB_error_no()==0){ 136 ChangeFieldInTable("mrprequirements", "part", $_POST['OldStockID'], $_POST['NewStockID']); 137 } 138 139 $result = DB_query("SELECT * FROM mrpsupplies" ,'','',false,false); 140 if (DB_error_no()==0){ 141 ChangeFieldInTable("mrpsupplies", "part", $_POST['OldStockID'], $_POST['NewStockID']); 142 } 143 } 144 ChangeFieldInTable("salesanalysis", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 145 ChangeFieldInTable("orderdeliverydifferenceslog", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 146 ChangeFieldInTable("prices", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 147 ChangeFieldInTable("salesorderdetails", "stkcode", $_POST['OldStockID'], $_POST['NewStockID']); 148 ChangeFieldInTable("purchorderdetails", "itemcode", $_POST['OldStockID'], $_POST['NewStockID']); 149 ChangeFieldInTable("purchdata", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 150 ChangeFieldInTable("shipmentcharges", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 151 ChangeFieldInTable("stockcheckfreeze", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 152 ChangeFieldInTable("stockcounts", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 153 ChangeFieldInTable("grns", "itemcode", $_POST['OldStockID'], $_POST['NewStockID']); 154 ChangeFieldInTable("contractbom", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 155 ChangeFieldInTable("bom", "component", $_POST['OldStockID'], $_POST['NewStockID']); 156 ChangeFieldInTable("bom", "parent", $_POST['OldStockID'], $_POST['NewStockID']); 157 ChangeFieldInTable("stockrequestitems", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 158 ChangeFieldInTable("stockdescriptiontranslations", "stockid", $_POST['OldStockID'], $_POST['NewStockID']);// Updates the translated item titles (StockTitles) 159 ChangeFieldInTable("custitem", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 160 ChangeFieldInTable("pricematrix", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 161 ChangeFieldInTable("pickreqdetails", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 162 163 echo '<br />' . _('Changing any image files'); 164 $SupportedImgExt = array('png','jpg','jpeg'); 165 foreach ($SupportedImgExt as $ext) { 166 $file = $_SESSION['part_pics_dir'] . '/' . $_POST['OldStockID'] . '.' . $ext; 167 if (file_exists ($file) ) { 168 if (rename($file, 169 $_SESSION['part_pics_dir'] . '/' .$_POST['NewStockID'] . '.' . $ext)) { 170 echo ' ... ' . _('completed'); 171 } else { 172 echo ' ... ' . _('failed'); 173 } 174 } else { 175 echo ' .... ' . _('no image to rename'); 176 } 177 } 178 179 ChangeFieldInTable("stockitemproperties", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 180 ChangeFieldInTable("worequirements", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 181 ChangeFieldInTable("worequirements", "parentstockid", $_POST['OldStockID'], $_POST['NewStockID']); 182 ChangeFieldInTable("woitems", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 183 ChangeFieldInTable("salescatprod", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 184 ChangeFieldInTable("stockserialitems", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 185 ChangeFieldInTable("stockserialmoves", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 186 ChangeFieldInTable("offers", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 187 ChangeFieldInTable("tenderitems", "stockid", $_POST['OldStockID'], $_POST['NewStockID']); 188 ChangeFieldInTable("prodspecs", "keyval", $_POST['OldStockID'], $_POST['NewStockID']); 189 ChangeFieldInTable("qasamples", "prodspeckey", $_POST['OldStockID'], $_POST['NewStockID']); 190 191 DB_ReinstateForeignKeys(); 192 193 $result = DB_Txn_Commit(); 194 195 echo '<br />' . _('Deleting the old stock master record'); 196 $sql = "DELETE FROM stockmaster WHERE stockid='" . $_POST['OldStockID'] . "'"; 197 $ErrMsg = _('The SQL to delete the old stock master record failed'); 198 $result = DB_query($sql,$ErrMsg,$DbgMsg,true); 199 echo ' ... ' . _('completed'); 200 echo '<p>' . _('Stock Code') . ': ' . $_POST['OldStockID'] . ' ' . _('was successfully changed to') . ' : ' . $_POST['NewStockID']; 201 202 // If the current SelectedStockItem is the same as the OldStockID, it updates to the NewStockID: 203 if ($_SESSION['SelectedStockItem'] == $_POST['OldStockID']) { 204 $_SESSION['SelectedStockItem'] = $_POST['NewStockID']; 205 } 206 207 } //only do the stuff above if $InputError==0 208} 209 210echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 211echo '<div class="centre">'; 212echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 213 214echo '<br /> 215 <table> 216 <tr> 217 <td>' . _('Existing Inventory Code') . ':</td> 218 <td><input type="text" name="OldStockID" size="20" maxlength="20" /></td> 219 </tr> 220 <tr> 221 <td>' . _('New Inventory Code') . ':</td> 222 <td><input type="text" name="NewStockID" size="20" maxlength="20" /></td> 223 </tr> 224 </table> 225 226 <input type="submit" name="ProcessStockChange" value="' . _('Process') . '" /> 227 </div> 228 </form>'; 229 230include('includes/footer.php'); 231 232?> 233