1<?php 2/* Import suppliers by csv file */ 3 4include('includes/session.php'); 5$Title = _('Import Items'); 6include('includes/header.php'); 7 8if(isset($_POST['FormID'])) { 9 if(!isset($_POST['UpdateIfExists'])) { 10 $_POST['UpdateIfExists']=0; 11 } else { 12 $_POST['UpdateIfExists']=1; 13 } 14} else { 15 $_POST['UpdateIfExists']=0; 16} 17// If this script is called with a file object, then the file contents are imported 18// If this script is called with the gettemplate flag, then a template file is served 19// Otherwise, a file upload form is displayed 20 21$FieldHeadings = array( 22 'SupplierID',//0 23 'SuppName',//1 24 'Address1',//2 25 'Address2',//3 26 'Address3',//4 27 'Address4',//5 28 'Address5',//6 29 'Address6',//7 30 'Phone',//8 31 'Fax',//9 32 'Email',//10 33 'SupplierType',//11 34 'CurrCode',//12 35 'SupplierSince',//13 36 'PaymentTerms',//14 37 'BankPartics',//15 38 'BankRef',//16 39 'BankAct',//17 40 'Remittance',//18 41 'TaxGroup',//19 42 'FactorID',//20 43 'TaxRef',//21 44 'lat', //22 45 'lng', //23 46); 47 48if(isset($_FILES['userfile']) and $_FILES['userfile']['name']) { //start file processing 49 50 //initialize 51 $FieldTarget = count($FieldHeadings); 52 $InputError = 0; 53 54 //check file info 55 $FileName = $_FILES['userfile']['name']; 56 $TempName = $_FILES['userfile']['tmp_name']; 57 $FileSize = $_FILES['userfile']['size']; 58 59 //get file handle 60 $FileHandle = fopen($TempName, 'r'); 61 62 //get the header row 63 $headRow = fgetcsv($FileHandle, 10000, ","); 64 65 //check for correct number of fields 66 if( count($headRow) != count($FieldHeadings) ) { 67 prnMsg (_('File contains '. count($headRow). ' columns, expected '. count($FieldHeadings). '. Try downloading a new template.'),'error'); 68 fclose($FileHandle); 69 include('includes/footer.php'); 70 exit; 71 } 72 73 //test header row field name and sequence 74 $head = 0; 75 foreach($headRow as $headField) { 76 if( mb_strtoupper($headField) != mb_strtoupper($FieldHeadings[$head]) ) { 77 prnMsg (_('File contains incorrect headers ('. mb_strtoupper($headField). ' != '. mb_strtoupper($header[$head]). '. Try downloading a new template.'),'error'); 78 fclose($FileHandle); 79 include('includes/footer.php'); 80 exit; 81 } 82 $head++; 83 } 84 85 //start database transaction 86 DB_Txn_Begin(); 87 88 //loop through file rows 89 $row = 1; 90 $UpdatedNum=0; 91 $InsertNum=0; 92 while( ($filerow = fgetcsv($FileHandle, 10000, ",")) !== FALSE ) { 93 //check for correct number of fields 94 $fieldCount = count($filerow); 95 if($fieldCount != $FieldTarget) { 96 prnMsg (_($FieldTarget. ' fields required, '. $fieldCount. ' fields received'),'error'); 97 fclose($FileHandle); 98 include('includes/footer.php'); 99 exit; 100 } 101 102 // cleanup the data (csv files often import with empty strings and such) 103 foreach($filerow as &$value) { 104 $value = trim($value); 105 } 106 107 $SupplierID=mb_strtoupper($filerow[0]); 108 $_POST['SuppName']=$filerow[1]; 109 $_POST['Address1']=$filerow[2]; 110 $_POST['Address2']=$filerow[3]; 111 $_POST['Address3']=$filerow[4]; 112 $_POST['Address4']=$filerow[5]; 113 $_POST['Address5']=$filerow[6]; 114 $_POST['Address6']=$filerow[7]; 115 $_POST['Phone']=$filerow[8]; 116 $_POST['Fax']=$filerow[9]; 117 $_POST['Email']=$filerow[10]; 118 $_POST['SupplierType']=$filerow[11]; 119 $_POST['CurrCode']=$filerow[12]; 120 $_POST['SupplierSince']=$filerow[13]; 121 $_POST['PaymentTerms']=$filerow[14]; 122 $_POST['BankPartics']=$filerow[15]; 123 $_POST['BankRef']=$filerow[16]; 124 $_POST['BankAct']=$filerow[17]; 125 $_POST['Remittance']=$filerow[18]; 126 $_POST['TaxGroup']=$filerow[19]; 127 $_POST['FactorID']=$filerow[20]; 128 $_POST['TaxRef']=$filerow[21]; 129 $latitude = $filerow[22]; 130 $longitude = $filerow[23]; 131 //initialise no input errors assumed initially before we test 132 $i=1; 133 /* actions to take once the user has clicked the submit button 134 ie the page has called itself with some user input */ 135 136 if(mb_strlen(trim($_POST['SuppName'])) > 40 137 OR mb_strlen(trim($_POST['SuppName'])) == 0 138 OR trim($_POST['SuppName']) == '') { 139 140 $InputError = 1; 141 prnMsg(_('The supplier name must be entered and be forty characters or less long'),'error'); 142 $Errors[$i]='Name'; 143 $i++; 144 } 145 if(mb_strlen($SupplierID) == 0) { 146 $InputError = 1; 147 prnMsg(_('The Supplier Code cannot be empty'),'error'); 148 $Errors[$i]='ID'; 149 $i++; 150 } 151 if(ContainsIllegalCharacters($SupplierID)) { 152 $InputError = 1; 153 prnMsg(_('The supplier code cannot contain any of the illegal characters') ,'error'); 154 $Errors[$i]='ID'; 155 $i++; 156 } 157 if(mb_strlen($_POST['Phone']) >25) { 158 $InputError = 1; 159 prnMsg(_('The telephone number must be 25 characters or less long'),'error'); 160 $Errors[$i] = 'Telephone'; 161 $i++; 162 } 163 if(mb_strlen($_POST['Fax']) >25) { 164 $InputError = 1; 165 prnMsg(_('The fax number must be 25 characters or less long'),'error'); 166 $Errors[$i] = 'Fax'; 167 $i++; 168 } 169 if(mb_strlen($_POST['Email']) >55) { 170 $InputError = 1; 171 prnMsg(_('The email address must be 55 characters or less long'),'error'); 172 $Errors[$i] = 'Email'; 173 $i++; 174 } 175 if(mb_strlen($_POST['Email'])>0 AND !IsEmailAddress($_POST['Email'])) { 176 $InputError = 1; 177 prnMsg(_('The email address is not correctly formed'),'error'); 178 $Errors[$i] = 'Email'; 179 $i++; 180 } 181 if(mb_strlen($_POST['BankRef']) > 12) { 182 $InputError = 1; 183 prnMsg(_('The bank reference text must be less than 12 characters long'),'error'); 184 $Errors[$i]='BankRef'; 185 $i++; 186 } 187 if(!Is_Date($_POST['SupplierSince'])) { 188 $InputError = 1; 189 prnMsg(_('The supplier since field must be a date in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error'); 190 $Errors[$i]='SupplierSince'; 191 $i++; 192 } 193 194 if($InputError != 1) { 195 196 $SQL_SupplierSince = FormatDateForSQL($_POST['SupplierSince']); 197 198 //first off validate inputs sensible 199 $sql="SELECT COUNT(supplierid) FROM suppliers WHERE supplierid='".$SupplierID."'"; 200 $result=DB_query($sql); 201 $myrow=DB_fetch_row($result); 202 203 $SuppExists = ($myrow[0]>0); 204 205 if($SuppExists AND $_POST['UpdateIfExists']!=1) { 206 $UpdatedNum++; 207 }elseif($SuppExists) { 208 $UpdatedNum++; 209 $supptranssql = "SELECT supplierno 210 FROM supptrans 211 WHERE supplierno='".$SupplierID ."'"; 212 $suppresult = DB_query($supptranssql); 213 $supptrans = DB_num_rows($suppresult); 214 215 $suppcurrssql = "SELECT currcode 216 FROM suppliers 217 WHERE supplierid='".$SupplierID ."'"; 218 $currresult = DB_query($suppcurrssql); 219 $suppcurr = DB_fetch_row($currresult); 220 221 $sql = "UPDATE suppliers SET suppname='" . $_POST['SuppName'] . "', 222 address1='" . $_POST['Address1'] . "', 223 address2='" . $_POST['Address2'] . "', 224 address3='" . $_POST['Address3'] . "', 225 address4='" . $_POST['Address4'] . "', 226 address5='" . $_POST['Address5'] . "', 227 address6='" . $_POST['Address6'] . "', 228 telephone='". $_POST['Phone'] ."', 229 fax = '". $_POST['Fax']."', 230 email = '" . $_POST['Email'] . "', 231 supptype = '".$_POST['SupplierType']."',"; 232 if($supptrans == 0)$sql.="currcode='" . $_POST['CurrCode'] . "',"; 233 $sql.="suppliersince='".$SQL_SupplierSince . "', 234 paymentterms='" . $_POST['PaymentTerms'] . "', 235 bankpartics='" . $_POST['BankPartics'] . "', 236 bankref='" . $_POST['BankRef'] . "', 237 bankact='" . $_POST['BankAct'] . "', 238 remittance='" . $_POST['Remittance'] . "', 239 taxgroupid='" . $_POST['TaxGroup'] . "', 240 factorcompanyid='" . $_POST['FactorID'] ."', 241 lat='" . $latitude ."', 242 lng='" . $longitude ."', 243 taxref='". $_POST['TaxRef'] ."' 244 WHERE supplierid = '".$SupplierID."'"; 245 246 if($suppcurr[0] != $_POST['CurrCode']) { 247 prnMsg( _('Cannot change currency code as transactions already exist'), 'info'); 248 } 249 250 $ErrMsg = _('The supplier could not be updated because'); 251 $DbgMsg = _('The SQL that was used to update the supplier but failed was'); 252 // echo $sql; 253 $result = DB_query($sql, $ErrMsg, $DbgMsg); 254 255 } else { //its a new supplier 256 $InsertNum++; 257 $sql = "INSERT INTO suppliers (supplierid, 258 suppname, 259 address1, 260 address2, 261 address3, 262 address4, 263 address5, 264 address6, 265 telephone, 266 fax, 267 email, 268 supptype, 269 currcode, 270 suppliersince, 271 paymentterms, 272 bankpartics, 273 bankref, 274 bankact, 275 remittance, 276 taxgroupid, 277 factorcompanyid, 278 lat, 279 lng, 280 taxref) 281 VALUES ('" . $SupplierID . "', 282 '" . $_POST['SuppName'] . "', 283 '" . $_POST['Address1'] . "', 284 '" . $_POST['Address2'] . "', 285 '" . $_POST['Address3'] . "', 286 '" . $_POST['Address4'] . "', 287 '" . $_POST['Address5'] . "', 288 '" . $_POST['Address6'] . "', 289 '" . $_POST['Phone'] . "', 290 '" . $_POST['Fax'] . "', 291 '" . $_POST['Email'] . "', 292 '".$_POST['SupplierType']."', 293 '" . $_POST['CurrCode'] . "', 294 '" . $SQL_SupplierSince . "', 295 '" . $_POST['PaymentTerms'] . "', 296 '" . $_POST['BankPartics'] . "', 297 '" . $_POST['BankRef'] . "', 298 '" . $_POST['BankAct'] . "', 299 '" . $_POST['Remittance'] . "', 300 '" . $_POST['TaxGroup'] . "', 301 '" . $_POST['FactorID'] . "', 302 '" . $latitude ."', 303 '" . $longitude ."', 304 '" . $_POST['TaxRef'] . "')"; 305 306 $ErrMsg = _('The supplier') . ' ' . $_POST['SuppName'] . ' ' . _('could not be added because'); 307 $DbgMsg = _('The SQL that was used to insert the supplier but failed was'); 308 309 $result = DB_query($sql, $ErrMsg, $DbgMsg); 310 311 } 312 if(DB_error_no() ==0) { 313 314 } else { //location insert failed so set some useful error info 315 $InputError = 1; 316 } 317 } else { //item insert failed so set some useful error info 318 $InputError = 1; 319 } 320 if($InputError == 1) { //this row failed so exit loop 321 break; 322 } 323 324 $row++; 325 326 } 327 328 if($InputError == 1) { //exited loop with errors so rollback 329 prnMsg(_('Failed on row '. $row. '. Batch import has been rolled back.'),'error'); 330 DB_Txn_Rollback(); 331 } else { //all good so commit data transaction 332 DB_Txn_Commit(); 333 prnMsg( _('Batch Import of') .' ' . $FileName . ' '. _('has been completed. All transactions committed to the database.'),'success'); 334 if($_POST['UpdateIfExists']==1) { 335 prnMsg( _('Updated:') .' ' . $UpdatedNum .' '. _('Insert') . ':' . $InsertNum ); 336 } else { 337 prnMsg( _('Exist:') .' ' . $UpdatedNum .' '. _('Insert') . ':' . $InsertNum ); 338 } 339 340 } 341 342 fclose($FileHandle); 343 344} elseif( isset($_POST['gettemplate']) || isset($_GET['gettemplate']) ) { //download an import template 345 346 echo '<br /><br /><br />"'. implode('","',$FieldHeadings). '"<br /><br /><br />'; 347 348} else { //show file upload form 349 350 prnMsg(_('Please ensure that your csv file charset is UTF-8, otherwise the data will not store correctly in database'),'warn'); 351 352 echo ' 353 <br /> 354 <a href="Z_ImportSuppliers.php?gettemplate=1">Get Import Template</a> 355 <br /> 356 <br />'; 357 echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" enctype="multipart/form-data">'; 358 echo '<div class="centre">'; 359 echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 360 361 echo '<input type="hidden" name="MAX_FILE_SIZE" value="1000000" />' . 362 _('Upload file') . ': <input name="userfile" type="file" /> 363 <input type="submit" value="' . _('Send File') . '" />'; 364 365 echo '<br/>',_('Update if SupplierNo exists'),':<input type="checkbox" name="UpdateIfExists">'; 366 echo '</div> 367 </form>'; 368 369} 370 371 372include('includes/footer.php'); 373?> 374