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