1<?php
2
3include('includes/session.php');
4$Title = _('Import Debtors And branches');
5include('includes/header.php');
6include('includes/SQL_CommonFunctions.inc');
7
8if(!isset($_POST['UpdateIfExists'])) {
9	$_POST['UpdateIfExists']=0;
10}else{
11	$_POST['UpdateIfExists']=1;
12}
13
14// If this script is called with a file object, then the file contents are imported
15// If this script is called with the gettemplate flag, then a template file is served
16// Otherwise, a file upload form is displayed
17$FieldHeadings = array(
18	'branchcode',//0
19	'debtorno',//1
20	'brname',//2
21	'braddress1',//3
22	'braddress2',//4
23	'braddress3',//5
24	'braddress4',//6
25	'braddress5',//7
26	'braddress6',//8
27	'lat',//9
28	'lng',//10
29	'estdeliverydays',//11
30	'area',//12
31	'salesman',//13
32	'fwddate',//14
33	'phoneno',//15
34	'faxno',//16
35	'contactname',//17
36	'email',//18
37	'defaultlocation',//19
38	'taxgroupid',//20
39	'defaultshipvia',//21
40	'deliverblind',//22
41	'disabletrans',//23
42	'brpostaddr1',//24
43	'brpostaddr2',//25
44	'brpostaddr3',//26
45	'brpostaddr4',//27
46	'brpostaddr5',//28
47	'specialinstructions',//29
48	'custbranchcode',//30
49);
50
51if (isset($_FILES['userfile']) and $_FILES['userfile']['name']) { //start file processing
52
53	//initialize
54	$FieldTarget = count($FieldHeadings);
55	$InputError = 0;
56
57	//check file info
58	$FileName = $_FILES['userfile']['name'];
59	$TempName  = $_FILES['userfile']['tmp_name'];
60	$FileSize = $_FILES['userfile']['size'];
61	//get file handle
62	$FileHandle = fopen($TempName, 'r');
63	//get the header row
64	$headRow = fgetcsv($FileHandle, 10000, ",");
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	$Salesmen=array();
73	$sql = "SELECT salesmancode
74				     FROM salesman";
75	$result=DB_query($sql);
76	while ($myrow = DB_fetch_array($result)) {
77		$Salesmen[]=$myrow['salesmancode'];
78	}
79	$Areas=array();
80	$sql = "SELECT areacode
81				     FROM areas";
82	$result=DB_query($sql);
83	while ($myrow = DB_fetch_array($result)) {
84		$Areas[]=$myrow['areacode'];
85	}
86	$Locations=array();
87	$sql = "SELECT loccode
88				     FROM locations";
89	$result=DB_query($sql);
90	while ($myrow = DB_fetch_array($result)) {
91		$Locations[]=$myrow['loccode'];
92	}
93	$Shippers=array();
94	$sql = "SELECT shipper_id
95				     FROM shippers";
96	$result=DB_query($sql);
97	while ($myrow = DB_fetch_array($result)) {
98		$Shippers[]=$myrow['shipper_id'];
99	}
100	$Taxgroups=array();
101	$sql = "SELECT taxgroupid
102				     FROM taxgroups";
103	$result=DB_query($sql);
104	while ($myrow = DB_fetch_array($result)) {
105		$Taxgroups[]=$myrow['taxgroupid'];
106	}
107
108	//test header row field name and sequence
109	$head = 0;
110	foreach ($headRow as $headField) {
111		if ( mb_strtoupper($headField) != mb_strtoupper($FieldHeadings[$head])) {
112			prnMsg (_('File contains incorrect headers ('. mb_strtoupper($headField). ' != '. mb_strtoupper($header[$head]). '. Try downloading a new template.'),'error');
113			fclose($FileHandle);
114			include('includes/footer.php');
115			exit;
116		}
117		$head++;
118	}
119
120	//start database transaction
121	DB_Txn_Begin();
122
123	//loop through file rows
124	$row = 1;
125	$UpdatedNum=0;
126	$InsertNum=0;
127	$ExistDebtorNos=array();
128	$NotExistDebtorNos=array();
129	$ExistedBranches = array();
130	while ( ($filerow = fgetcsv($FileHandle, 10000, ",")) !== FALSE ) {
131
132		//check for correct number of fields
133		$fieldCount = count($filerow);
134		if ($fieldCount != $FieldTarget) {
135			prnMsg (_($FieldTarget. ' fields required, '. $fieldCount. ' fields received'),'error');
136			fclose($FileHandle);
137			include('includes/footer.php');
138			exit;
139		}
140
141		// cleanup the data (csv files often import with empty strings and such)
142		foreach ($filerow as &$value) {
143			$value = trim($value);
144		}
145		$_POST['BranchCode']=$filerow[0];
146		$_POST['DebtorNo']=$filerow[1];
147		$_POST['BrName']=$filerow[2];
148		$_POST['BrAddress1']=$filerow[3];
149		$_POST['BrAddress2']=$filerow[4];
150		$_POST['BrAddress3']=$filerow[5];
151		$_POST['BrAddress4']=$filerow[6];
152		$_POST['BrAddress5']=$filerow[7];
153		$_POST['BrAddress6']=$filerow[8];
154		$Latitude=$filerow[9];
155		$Longitude=$filerow[10];
156		$_POST['SpecialInstructions']=$filerow[29];
157		$_POST['EstDeliveryDays']=$filerow[11];
158		$_POST['FwdDate']=$filerow[14];
159		$_POST['Salesman']=$filerow[13];
160		$_POST['PhoneNo']=$filerow[15];
161		$_POST['FaxNo']=$filerow[16];
162		$_POST['ContactName']=$filerow[17];
163		$_POST['Area']=$filerow[12];
164		$_POST['Email']=$filerow[18];
165		$_POST['TaxGroup']=$filerow[20];
166		$_POST['DefaultLocation']=$filerow[19];
167		$_POST['BrPostAddr1']=$filerow[24];
168		$_POST['BrPostAddr2']=$filerow[25];
169		$_POST['BrPostAddr3']=$filerow[26];
170		$_POST['BrPostAddr4']=$filerow[27];
171		$_POST['BrPostAddr5']=$filerow[28];
172		$_POST['DisableTrans']=$filerow[23];
173		$_POST['DefaultShipVia']=$filerow[21];
174		$_POST['CustBranchCode']=$filerow[30];
175		$_POST['DeliverBlind']=$filerow[22];
176
177		$i=0;
178
179		if (ContainsIllegalCharacters($_POST['BranchCode']) OR mb_strstr($_POST['BranchCode'],' ') OR mb_strstr($_POST['BranchCode'],'-')) {
180			$InputError = 1;
181			prnMsg(_('The Branch code cannot contain any of the following characters')." -  &amp; \' &lt; &gt;",'error');
182			$Errors[$i] = 'BranchCode';
183			$i++;
184		}
185		if (ContainsIllegalCharacters($_POST['DebtorNo'])) {
186			$InputError = 1;
187			prnMsg(_('The Debtor No cannot contain any of the following characters')." - &amp; \' &lt; &gt;",'error');
188			$Errors[$i] = 'Debtor No';
189			$i++;
190		}
191		if (mb_strlen($_POST['BranchCode'])==0 OR mb_strlen($_POST['BranchCode'])>10) {
192			$InputError = 1;
193			prnMsg(_('The Branch code must be at least one character long and cannot be more than 10 characters'),'error');
194			$Errors[$i] = 'BranchCode';
195			$i++;
196		}
197		for ($c=1;$c<7;$c++) { //Branch address validataion
198			$Lenth = 40;
199			if($c == 4) {
200				$Lenth = 50;
201			}
202			if($c == 5) {
203				$Lenth = 20;
204			}
205			if (isset($_POST['BrAddress'.$c]) AND mb_strlen($_POST['BrAddress'.$c])>$Lenth) {
206				$InputError = 1;
207				prnMsg(_('The Branch address1 must be no more than') . ' ' . $Lenth . ' '. _('characters'),'error');
208				$Errors[$i] = 'BrAddress'.$c;
209				$i++;
210		} 		}
211		if($Latitude !== null AND !is_numeric($Latitude)) {
212			$InputError = 1;
213			prnMsg(_('The latitude is expected to be a numeric'),'error');
214			$Errors[$i] = 'Latitude';
215			$i++;
216		}
217		if($Longitude !== null AND !is_numeric($Longitude)) {
218			$InputError = 1;
219			prnMsg(_('The longitude is expected to be a numeric'),'error');
220		       	$Errors[$i] = 'Longitued';
221			$i++;
222		}
223		if (!is_numeric($_POST['FwdDate'])) {
224			$InputError = 1;
225			prnMsg(_('The date after which invoices are charged to the following month is expected to be a number and a recognised number has not been entered'),'error');
226			$Errors[$i] = 'FwdDate';
227			$i++;
228		}
229		if ($_POST['FwdDate'] >30) {
230			$InputError = 1;
231			prnMsg(_('The date (in the month) after which invoices are charged to the following month should be a number less than 31'),'error');
232			$Errors[$i] = 'FwdDate';
233			$i++;
234		}
235		if (!is_numeric(filter_number_format($_POST['EstDeliveryDays']))) {
236			$InputError = 1;
237			prnMsg(_('The estimated delivery days is expected to be a number and a recognised number has not been entered'),'error');
238			$Errors[$i] = 'EstDeliveryDays';
239			$i++;
240		}
241		if (filter_number_format($_POST['EstDeliveryDays']) >60) {
242			$InputError = 1;
243			prnMsg(_('The estimated delivery days should be a number of days less than 60') . '. ' . _('A package can be delivered by seafreight anywhere in the world normally in less than 60 days'),'error');
244			$Errors[$i] = 'EstDeliveryDays';
245			$i++;
246		}
247		if(empty($_POST['Salesman']) OR !in_array($_POST['Salesman'],$Salesmen,true)) {
248			$InputError = 1;
249			prnMsg(_('The salesman not empty and must exist.'),'error');
250			$Errors[$i] = 'Salesman';
251			$i++;
252		}
253		if($_POST['PhoneNo'] !== null AND preg_match('/[^\d+()\s-]/',$_POST['PhoneNo'])) {
254			$InputError = 1;
255			prnMsg(_('The phone no should not contains characters other than digital,parenthese,space,minus and plus sign'),'error');
256			$Errors[$i] = 'Phone No';
257			$i++;
258		}
259		if($_POST['FaxNo'] !== null AND preg_match('/[^\d+()\s-]/',$_POST['FaxNo'])) {
260			$InputError = 1;
261			prnMsg(_('The fax no should not contains characters other than digital,parenthese,space,minus and plus sign'),'error');
262			$Errors[$i] = 'FaxNo';
263			$i++;
264		}
265		if($_POST['ContactName'] !== null AND mb_strlen($_POST['ContactName']) > 30) {
266			$InputError = 1;
267			prnMsg(_('The contact name must not be over 30 characters'),'error');
268			$Errors[$i] = 'ContactName';
269			$i++;
270		}
271		if($_POST['Email'] !== null AND !filter_var($_POST['Email'],FILTER_VALIDATE_EMAIL)) {
272			$InputError = 1;
273			prnMsg(_('The email address is not valid'),'error');
274			$Errors[$i] = 'Email';
275			$i++;
276		}
277
278		if(ContainsIllegalCharacters($_POST['BrName']) OR mb_strlen($_POST['BrName']) >40) {
279			$InputError = 1;
280			prnMsg(_('The Branch code cannot contain any of the following characters')." -  &amp; \' &lt; &gt;" .' ' . _('Or length is over 40'),'error');
281			$Errors[$i] = 'BrName';
282			$i++;
283		}
284		if(empty($_POST['Area']) OR !in_array($_POST['Area'],$Areas,true)) {
285			$InputError = 1;
286			prnMsg(_('The sales area not empty and must exist.'),'error');
287			$Errors[$i] = 'Area';
288			$i++;
289		}
290		if(empty($_POST['DefaultLocation']) OR !in_array($_POST['DefaultLocation'],$Locations,true)) {
291			$InputError = 1;
292			prnMsg(_('The default location not empty and must exist.'),'error');
293			$Errors[$i] = 'DefaultLocation';
294			$i++;
295		}
296		if(empty($_POST['DefaultShipVia']) OR !in_array($_POST['DefaultShipVia'],$Shippers,true)) {
297			$InputError = 1;
298			prnMsg(_('The default shipper not empty and must exist.'),'error');
299			$Errors[$i] = 'DefaultShipVia';
300			$i++;
301		}
302		if(empty($_POST['TaxGroup']) OR !in_array($_POST['TaxGroup'],$Taxgroups,true)) {
303			$InputError = 1;
304			prnMsg(_('The taxgroup not empty and must exist.'),'error');
305			$Errors[$i] = 'TaxGroup';
306			$i++;
307		}
308		if(!isset($_POST['DeliverBlind']) OR ($_POST['DeliverBlind'] !=1 AND $_POST['DeliverBlind'] != 2)) {
309			$InputError = 1;
310			prnMsg(_('The Deliver Blind must be set as 2 or 1'),'error');
311			$Errors[$i] = 'DeliverBlind';
312			$i++;
313		}
314		if(!isset($_POST['DisableTrans']) OR ($_POST['DisableTrans'] != 0 AND $_POST['DisableTrans'] != 1)) {
315			$InputError = 1;
316			prnMsg(_('The Disable Trans status should be 0 or 1'),'error');
317			$Errors[$i] = 'DisableTrans';
318			$i++;
319		}
320		for($c=1;$c<6;$c++) {
321			$Lenth = 40;
322			if($c == 4) {
323				$Lenth = 50;
324			}
325			if($c == 5) {
326				$Lenth = 20;
327			}
328			if (isset($_POST['BrPostAddr'.$c]) AND mb_strlen($_POST['BrPostAddr'.$c])>$Lenth) {
329				$InputError = 1;
330				prnMsg(_('The Branch Post Address') . ' ' . $c . ' ' . _('must be no more than') . ' ' . $Lenth . ' '. _('characters'),'error');
331				$Errors[$i] = 'BrPostAddr'.$c;
332				$i++;
333			}
334
335		}
336		if(isset($_POST['CustBranchCode']) AND mb_strlen($_POST['CustBranchCode']) > 30) {
337			$InputError = 1;
338			prnMsg(_('The Cust branch code for EDI must be less than 30 characters'),'error');
339			$Errors[$i] = 'CustBranchCode';
340			$i++;
341		}
342
343		if ($InputError !=1) {
344			if (DB_error_no() ==0) {
345
346				if(in_array($_POST['DebtorNo'],$NotExistDebtorNos,true)) {
347					continue;
348				}else{
349					$sql = "SELECT 1
350						 FROM debtorsmaster
351						 WHERE debtorno='".$_POST['DebtorNo']."' LIMIT 1";
352					$result=DB_query($sql);
353					$DebtorExists=(DB_num_rows($result)>0);
354					if ($DebtorExists) {
355						$ExistDebtorNos[]=$_POST['DebtorNo'];
356					}else{
357						$NotExistDebtorNos[]=$_POST['DebtorNo'];
358						prnMsg(_('The Debtor No') . $_POST['DebtorNo'] . ' ' . _('has not existed, and its branches data cannot be imported'),'error');
359						include('includes/footer.php');
360						exit;
361					}
362				}
363				$sql = "SELECT 1
364				     FROM custbranch
365           			 WHERE debtorno='".$_POST['DebtorNo']."' AND
366				           branchcode='".$_POST['BranchCode']."' LIMIT 1";
367				$result=DB_query($sql);
368				$BranchExists=(DB_num_rows($result)>0);
369				if ($BranchExists AND $_POST['UpdateIfExists']!=1) {
370					$ExistedBranches[] = array('debtor'=>$_POST['DebtorNo'],
371								'branch'=>$_POST['BranchCode']);
372					$UpdatedNum++;
373				}else{
374
375					if (!isset($_POST['EstDeliveryDays'])) {
376						$_POST['EstDeliveryDays']=1;
377					}
378					if (!isset($Latitude)) {
379						$Latitude=0.0;
380						$Longitude=0.0;
381					}
382					if ($BranchExists) {
383						$UpdatedNum++;
384						$sql = "UPDATE custbranch SET brname = '" . $_POST['BrName'] . "',
385									braddress1 = '" . $_POST['BrAddress1'] . "',
386									braddress2 = '" . $_POST['BrAddress2'] . "',
387									braddress3 = '" . $_POST['BrAddress3'] . "',
388									braddress4 = '" . $_POST['BrAddress4'] . "',
389									braddress5 = '" . $_POST['BrAddress5'] . "',
390									braddress6 = '" . $_POST['BrAddress6'] . "',
391									lat = '" . $Latitude . "',
392									lng = '" . $Longitude . "',
393									specialinstructions = '" . $_POST['SpecialInstructions'] . "',
394									phoneno='" . $_POST['PhoneNo'] . "',
395									faxno='" . $_POST['FaxNo'] . "',
396									fwddate= '" . $_POST['FwdDate'] . "',
397									contactname='" . $_POST['ContactName'] . "',
398									salesman= '" . $_POST['Salesman'] . "',
399									area='" . $_POST['Area'] . "',
400									estdeliverydays ='" . filter_number_format($_POST['EstDeliveryDays']) . "',
401									email='" . $_POST['Email'] . "',
402									taxgroupid='" . $_POST['TaxGroup'] . "',
403									defaultlocation='" . $_POST['DefaultLocation'] . "',
404									brpostaddr1 = '" . $_POST['BrPostAddr1'] . "',
405									brpostaddr2 = '" . $_POST['BrPostAddr2'] . "',
406									brpostaddr3 = '" . $_POST['BrPostAddr3'] . "',
407									brpostaddr4 = '" . $_POST['BrPostAddr4'] . "',
408									brpostaddr5 = '" . $_POST['BrPostAddr5'] . "',
409									disabletrans='" . $_POST['DisableTrans'] . "',
410									defaultshipvia='" . $_POST['DefaultShipVia'] . "',
411									custbranchcode='" . $_POST['CustBranchCode'] ."',
412									deliverblind='" . $_POST['DeliverBlind'] . "'
413								WHERE branchcode = '".$_POST['BranchCode']."' AND debtorno='".$_POST['DebtorNo']."'";
414
415					} else {
416						$InsertNum++;
417						$sql = "INSERT INTO custbranch (branchcode,
418										debtorno,
419										brname,
420										braddress1,
421										braddress2,
422										braddress3,
423										braddress4,
424										braddress5,
425										braddress6,
426										lat,
427										lng,
428										specialinstructions,
429										estdeliverydays,
430										fwddate,
431										salesman,
432										phoneno,
433										faxno,
434										contactname,
435										area,
436										email,
437										taxgroupid,
438										defaultlocation,
439										brpostaddr1,
440										brpostaddr2,
441										brpostaddr3,
442										brpostaddr4,
443										brpostaddr5,
444										disabletrans,
445										defaultshipvia,
446										custbranchcode,
447										deliverblind)
448								VALUES ('" . $_POST['BranchCode'] . "',
449									'" . $_POST['DebtorNo'] . "',
450									'" . $_POST['BrName'] . "',
451									'" . $_POST['BrAddress1'] . "',
452									'" . $_POST['BrAddress2'] . "',
453									'" . $_POST['BrAddress3'] . "',
454									'" . $_POST['BrAddress4'] . "',
455									'" . $_POST['BrAddress5'] . "',
456									'" . $_POST['BrAddress6'] . "',
457									'" . $Latitude . "',
458									'" . $Longitude . "',
459									'" . $_POST['SpecialInstructions'] . "',
460									'" . filter_number_format($_POST['EstDeliveryDays']) . "',
461									'" . $_POST['FwdDate'] . "',
462									'" . $_POST['Salesman'] . "',
463									'" . $_POST['PhoneNo'] . "',
464									'" . $_POST['FaxNo'] . "',
465									'" . $_POST['ContactName'] . "',
466									'" . $_POST['Area'] . "',
467									'" . $_POST['Email'] . "',
468									'" . $_POST['TaxGroup'] . "',
469									'" . $_POST['DefaultLocation'] . "',
470									'" . $_POST['BrPostAddr1'] . "',
471									'" . $_POST['BrPostAddr2'] . "',
472									'" . $_POST['BrPostAddr3'] . "',
473									'" . $_POST['BrPostAddr4'] . "',
474									'" . $_POST['BrPostAddr5'] . "',
475									'" . $_POST['DisableTrans'] . "',
476									'" . $_POST['DefaultShipVia'] . "',
477									'" . $_POST['CustBranchCode'] ."',
478									'" . $_POST['DeliverBlind'] . "')";
479					}
480
481					//run the SQL from either of the above possibilites
482					$ErrMsg = _('The branch record could not be inserted or updated because');
483					$result = DB_query($sql, $ErrMsg);
484
485					if (DB_error_no() ==0) {
486						prnMsg( _('New branch of debtor') .' ' .$_POST['DebtorNo'] . ' ' ._('with branch code') .' ' . $_POST['BranchCode'] . ' ' . $_POST['BrName']  . ' '. _('has been passed validation'),'info');
487					} else { //location insert failed so set some useful error info
488						$InputError = 1;
489						prnMsg(_($result),'error');
490					}
491				}
492			} else { //item insert failed so set some useful error info
493				$InputError = 1;
494				prnMsg(_($result),'error');
495			}
496
497		}
498
499		if ($InputError == 1) { //this row failed so exit loop
500			break;
501		}
502
503		$row++;
504	}
505
506	if ($InputError == 1) { //exited loop with errors so rollback
507		prnMsg(_('Failed on row '. $row. '. Batch import has been rolled back.'),'error');
508		DB_Txn_Rollback();
509	} else { //all good so commit data transaction
510		DB_Txn_Commit();
511		if($_POST['UpdateIfExists']==1) {
512			prnMsg( _('Updated brances total:') .' ' . $UpdatedNum .' '._('Insert branches total:'). $InsertNum,'success' );
513		}else{
514			prnMsg( _('Exist branches total:') .' ' . $UpdatedNum .' '._('Inserted branches total:'). $InsertNum,'info');
515			if($UpdatedNum){
516				echo '	<p>' . _('Branches not updated').'</p>
517					<table class="selection">
518					<tr><th>'._('Debtor No').'</th><th>' . _('Branch Code').'</th></tr>';
519				foreach($ExistedBranches as $key=>$value){
520					echo '<tr><td>'.$value['debtor'].'</td><td>'.$value['branch'].'</td></tr>';
521				}
522				echo '</table>';
523			}
524		}
525	}
526
527	fclose($FileHandle);
528
529} elseif ( isset($_POST['gettemplate']) OR isset($_GET['gettemplate'])) { //download an import template
530
531	echo '<br /><br /><br />"'. implode('","',$FieldHeadings). '"<br /><br /><br />';
532
533} else { //show file upload form
534
535	prnMsg(_('Please ensure that your csv file is encoded in UTF-8, otherwise the input data will not store correctly in database'),'warn');
536
537	echo '
538		<br />
539		<a href="Z_ImportCustbranch.php?gettemplate=1">Get Import Template</a>
540		<br />
541		<br />';
542	echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" enctype="multipart/form-data">';
543    echo '<div class="centre">';
544	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
545
546	echo '<input type="hidden" name="MAX_FILE_SIZE" value="1000000" />' .
547			_('Upload file') . ': <input name="userfile" type="file" />
548			<input type="submit" value="' . _('Send File') . '" />';
549	echo '<br/>',_('Update if Customer Branch exists'),':<input type="checkbox" name="UpdateIfExists">';
550	echo'</div>
551		</form>';
552
553}
554
555
556include('includes/footer.php');
557?>
558