From: <ex...@us...> - 2015-04-01 01:38:40
|
Revision: 7250 http://sourceforge.net/p/web-erp/reponame/7250 Author: exsonqu Date: 2015-04-01 01:38:38 +0000 (Wed, 01 Apr 2015) Log Message: ----------- 01/04/15 Thumb: Add a Z_ImportCustbranch.php script to import customer branches. Added Paths: ----------- trunk/Z_ImportCustbranch.php Added: trunk/Z_ImportCustbranch.php =================================================================== --- trunk/Z_ImportCustbranch.php (rev 0) +++ trunk/Z_ImportCustbranch.php 2015-04-01 01:38:38 UTC (rev 7250) @@ -0,0 +1,558 @@ +<?php +/* $Id: Z_ImportCustbranch.php 6068 2015-03-26 16:04:22Z exson $*/ + +include('includes/session.inc'); +$Title = _('Import Debtors And branches'); +include('includes/header.inc'); +include('includes/SQL_CommonFunctions.inc'); + +if(!isset($_POST['UpdateIfExists'])) { + $_POST['UpdateIfExists']=0; +}else{ + $_POST['UpdateIfExists']=1; +} + +// If this script is called with a file object, then the file contents are imported +// If this script is called with the gettemplate flag, then a template file is served +// Otherwise, a file upload form is displayed +$FieldHeadings = array( + 'branchcode',//0 + 'debtorno',//1 + 'brname',//2 + 'braddress1',//3 + 'braddress2',//4 + 'braddress3',//5 + 'braddress4',//6 + 'braddress5',//7 + 'braddress6',//8 + 'lat',//9 + 'lng',//10 + 'estdeliverydays',//11 + 'area',//12 + 'salesman',//13 + 'fwddate',//14 + 'phoneno',//15 + 'faxno',//16 + 'contactname',//17 + 'email',//18 + 'defaultlocation',//19 + 'taxgroupid',//20 + 'defaultshipvia',//21 + 'deliverblind',//22 + 'disabletrans',//23 + 'brpostaddr1',//24 + 'brpostaddr2',//25 + 'brpostaddr3',//26 + 'brpostaddr4',//27 + 'brpostaddr5',//28 + 'specialinstructions',//29 + 'custbranchcode',//30 +); + +if (isset($_FILES['userfile']) and $_FILES['userfile']['name']) { //start file processing + + //initialize + $FieldTarget = count($FieldHeadings); + $InputError = 0; + + //check file info + $FileName = $_FILES['userfile']['name']; + $TempName = $_FILES['userfile']['tmp_name']; + $FileSize = $_FILES['userfile']['size']; + //get file handle + $FileHandle = fopen($TempName, 'r'); + //get the header row + $headRow = fgetcsv($FileHandle, 10000, ","); + //check for correct number of fields + if ( count($headRow) != count($FieldHeadings)) { + prnMsg (_('File contains '. count($headRow). ' columns, expected '. count($FieldHeadings). '. Try downloading a new template.'),'error'); + fclose($FileHandle); + include('includes/footer.inc'); + exit; + } + $Salesmen=array(); + $sql = "SELECT salesmancode + FROM salesman"; + $result=DB_query($sql); + while ($myrow = DB_fetch_array($result)) { + $Salesmen[]=$myrow['salesmancode']; + } + $Areas=array(); + $sql = "SELECT areacode + FROM areas"; + $result=DB_query($sql); + while ($myrow = DB_fetch_array($result)) { + $Areas[]=$myrow['areacode']; + } + $Locations=array(); + $sql = "SELECT loccode + FROM locations"; + $result=DB_query($sql); + while ($myrow = DB_fetch_array($result)) { + $Locations[]=$myrow['loccode']; + } + $Shippers=array(); + $sql = "SELECT shipper_id + FROM shippers"; + $result=DB_query($sql); + while ($myrow = DB_fetch_array($result)) { + $Shippers[]=$myrow['shipper_id']; + } + $Taxgroups=array(); + $sql = "SELECT taxgroupid + FROM taxgroups"; + $result=DB_query($sql); + while ($myrow = DB_fetch_array($result)) { + $Taxgroups[]=$myrow['taxgroupid']; + } + + //test header row field name and sequence + $head = 0; + foreach ($headRow as $headField) { + if ( mb_strtoupper($headField) != mb_strtoupper($FieldHeadings[$head])) { + prnMsg (_('File contains incorrect headers ('. mb_strtoupper($headField). ' != '. mb_strtoupper($header[$head]). '. Try downloading a new template.'),'error'); + fclose($FileHandle); + include('includes/footer.inc'); + exit; + } + $head++; + } + + //start database transaction + DB_Txn_Begin(); + + //loop through file rows + $row = 1; + $UpdatedNum=0; + $InsertNum=0; + $ExistDebtorNos=array(); + $NotExistDebtorNos=array(); + $ExistedBranches = array(); + while ( ($filerow = fgetcsv($FileHandle, 10000, ",")) !== FALSE ) { + + //check for correct number of fields + $fieldCount = count($filerow); + if ($fieldCount != $FieldTarget) { + prnMsg (_($FieldTarget. ' fields required, '. $fieldCount. ' fields received'),'error'); + fclose($FileHandle); + include('includes/footer.inc'); + exit; + } + + // cleanup the data (csv files often import with empty strings and such) + foreach ($filerow as &$value) { + $value = trim($value); + } + $_POST['BranchCode']=$filerow[0]; + $_POST['DebtorNo']=$filerow[1]; + $_POST['BrName']=$filerow[2]; + $_POST['BrAddress1']=$filerow[3]; + $_POST['BrAddress2']=$filerow[4]; + $_POST['BrAddress3']=$filerow[5]; + $_POST['BrAddress4']=$filerow[6]; + $_POST['BrAddress5']=$filerow[7]; + $_POST['BrAddress6']=$filerow[8]; + $Latitude=$filerow[9]; + $Longitude=$filerow[10]; + $_POST['SpecialInstructions']=$filerow[29]; + $_POST['EstDeliveryDays']=$filerow[11]; + $_POST['FwdDate']=$filerow[14]; + $_POST['Salesman']=$filerow[13]; + $_POST['PhoneNo']=$filerow[15]; + $_POST['FaxNo']=$filerow[16]; + $_POST['ContactName']=$filerow[17]; + $_POST['Area']=$filerow[12]; + $_POST['Email']=$filerow[18]; + $_POST['TaxGroup']=$filerow[20]; + $_POST['DefaultLocation']=$filerow[19]; + $_POST['BrPostAddr1']=$filerow[24]; + $_POST['BrPostAddr2']=$filerow[25]; + $_POST['BrPostAddr3']=$filerow[26]; + $_POST['BrPostAddr4']=$filerow[27]; + $_POST['BrPostAddr5']=$filerow[28]; + $_POST['DisableTrans']=$filerow[23]; + $_POST['DefaultShipVia']=$filerow[21]; + $_POST['CustBranchCode']=$filerow[30]; + $_POST['DeliverBlind']=$filerow[22]; + + $i=0; + + if (ContainsIllegalCharacters($_POST['BranchCode']) OR mb_strstr($_POST['BranchCode'],' ') OR mb_strstr($_POST['BranchCode'],'-')) { + $InputError = 1; + prnMsg(_('The Branch code cannot contain any of the following characters')." - & \' < >",'error'); + $Errors[$i] = 'BranchCode'; + $i++; + } + if (ContainsIllegalCharacters($_POST['DebtorNo'])) { + $InputError = 1; + prnMsg(_('The Debtor No cannot contain any of the following characters')." - & \' < >",'error'); + $Errors[$i] = 'Debtor No'; + $i++; + } + if (mb_strlen($_POST['BranchCode'])==0 OR mb_strlen($_POST['BranchCode'])>10) { + $InputError = 1; + prnMsg(_('The Branch code must be at least one character long and cannot be more than 10 characters'),'error'); + $Errors[$i] = 'BranchCode'; + $i++; + } + for ($c=1;$c<7;$c++) { //Branch address validataion + $Lenth = 40; + if($c == 4) { + $Lenth = 50; + } + if($c == 5) { + $Lenth = 20; + } + if (isset($_POST['BrAddress'.$c]) AND mb_strlen($_POST['BrAddress'.$c])>$Lenth) { + $InputError = 1; + prnMsg(_('The Branch address1 must be no more than') . ' ' . $Lenth . ' '. _('characters'),'error'); + $Errors[$i] = 'BrAddress'.$c; + $i++; + } } + if($Latitude !== null AND !is_numeric($Latitude)) { + $InputError = 1; + prnMsg(_('The latitude is expected to be a numeric'),'error'); + $Errors[$i] = 'Latitude'; + $i++; + } + if($Longitude !== null AND !is_numeric($Longitude)) { + $InputError = 1; + prnMsg(_('The longitude is expected to be a numeric'),'error'); + $Errors[$i] = 'Longitued'; + $i++; + } + if (!is_numeric($_POST['FwdDate'])) { + $InputError = 1; + 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'); + $Errors[$i] = 'FwdDate'; + $i++; + } + if ($_POST['FwdDate'] >30) { + $InputError = 1; + prnMsg(_('The date (in the month) after which invoices are charged to the following month should be a number less than 31'),'error'); + $Errors[$i] = 'FwdDate'; + $i++; + } + if (!is_numeric(filter_number_format($_POST['EstDeliveryDays']))) { + $InputError = 1; + prnMsg(_('The estimated delivery days is expected to be a number and a recognised number has not been entered'),'error'); + $Errors[$i] = 'EstDeliveryDays'; + $i++; + } + if (filter_number_format($_POST['EstDeliveryDays']) >60) { + $InputError = 1; + 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'); + $Errors[$i] = 'EstDeliveryDays'; + $i++; + } + if(empty($_POST['Salesman']) OR !in_array($_POST['Salesman'],$Salesmen,true)) { + $InputError = 1; + prnMsg(_('The salesman not empty and must exist.'),'error'); + $Errors[$i] = 'Salesman'; + $i++; + } + if($_POST['PhoneNo'] !== null AND preg_match('/[^\d+()\s-]/',$_POST['PhoneNo'])) { + $InputError = 1; + prnMsg(_('The phone no should not contains characters other than digital,parenthese,space,minus and plus sign'),'error'); + $Errors[$i] = 'Phone No'; + $i++; + } + if($_POST['FaxNo'] !== null AND preg_match('/[^\d+()\s-]/',$_POST['FaxNo'])) { + $InputError = 1; + prnMsg(_('The fax no should not contains characters other than digital,parenthese,space,minus and plus sign'),'error'); + $Errors[$i] = 'FaxNo'; + $i++; + } + if($_POST['ContactName'] !== null AND mb_strlen($_POST['ContactName']) > 30) { + $InputError = 1; + prnMsg(_('The contact name must not be over 30 characters'),'error'); + $Errors[$i] = 'ContactName'; + $i++; + } + if($_POST['Email'] !== null AND !filter_var($_POST['Email'],FILTER_VALIDATE_EMAIL)) { + $InputError = 1; + prnMsg(_('The email address is not valid'),'error'); + $Errors[$i] = 'Email'; + $i++; + } + + if(ContainsIllegalCharacters($_POST['BrName']) OR mb_strlen($_POST['BrName']) >40) { + $InputError = 1; + prnMsg(_('The Branch code cannot contain any of the following characters')." - & \' < >" .' ' . _('Or length is over 40'),'error'); + $Errors[$i] = 'BrName'; + $i++; + } + if(empty($_POST['Area']) OR !in_array($_POST['Area'],$Areas,true)) { + $InputError = 1; + prnMsg(_('The sales area not empty and must exist.'),'error'); + $Errors[$i] = 'Area'; + $i++; + } + if(empty($_POST['DefaultLocation']) OR !in_array($_POST['DefaultLocation'],$Locations,true)) { + $InputError = 1; + prnMsg(_('The default location not empty and must exist.'),'error'); + $Errors[$i] = 'DefaultLocation'; + $i++; + } + if(empty($_POST['DefaultShipVia']) OR !in_array($_POST['DefaultShipVia'],$Shippers,true)) { + $InputError = 1; + prnMsg(_('The default shipper not empty and must exist.'),'error'); + $Errors[$i] = 'DefaultShipVia'; + $i++; + } + if(empty($_POST['TaxGroup']) OR !in_array($_POST['TaxGroup'],$Taxgroups,true)) { + $InputError = 1; + prnMsg(_('The taxgroup not empty and must exist.'),'error'); + $Errors[$i] = 'TaxGroup'; + $i++; + } + if(!isset($_POST['DeliverBlind']) OR ($_POST['DeliverBlind'] !=1 AND $_POST['DeliverBlind'] != 2)) { + $InputError = 1; + prnMsg(_('The Deliver Blind must be set as 2 or 1'),'error'); + $Errors[$i] = 'DeliverBlind'; + $i++; + } + if(!isset($_POST['DisableTrans']) OR ($_POST['DisableTrans'] != 0 AND $_POST['DisableTrans'] != 1)) { + $InputError = 1; + prnMsg(_('The Disable Trans status should be 0 or 1'),'error'); + $Errors[$i] = 'DisableTrans'; + $i++; + } + for($c=1;$c<6;$c++) { + $Lenth = 40; + if($c == 4) { + $Lenth = 50; + } + if($c == 5) { + $Lenth = 20; + } + if (isset($_POST['BrPostAddr'.$c]) AND mb_strlen($_POST['BrPostAddr'.$c])>$Lenth) { + $InputError = 1; + prnMsg(_('The Branch Post Address') . ' ' . $c . ' ' . _('must be no more than') . ' ' . $Lenth . ' '. _('characters'),'error'); + $Errors[$i] = 'BrPostAddr'.$c; + $i++; + } + + } + if(isset($_POST['CustBranchCode']) AND mb_strlen($_POST['CustBranchCode']) > 30) { + $InputError = 1; + prnMsg(_('The Cust branch code for EDI must be less than 30 characters'),'error'); + $Errors[$i] = 'CustBranchCode'; + $i++; + } + + if ($InputError !=1) { + if (DB_error_no() ==0) { + + if(in_array($_POST['DebtorNo'],$NotExistDebtorNos,true)) { + continue; + }else{ + $sql = "SELECT 1 + FROM debtorsmaster + WHERE debtorno='".$_POST['DebtorNo']."' LIMIT 1"; + $result=DB_query($sql); + $DebtorExists=(DB_num_rows($result)>0); + if ($DebtorExists) { + $ExistDebtorNos[]=$_POST['DebtorNo']; + }else{ + $NotExistDebtorNos[]=$_POST['DebtorNo']; + prnMsg(_('The Debtor No') . $_POST['DebtorNo'] . ' ' . _('has not existed, and its branches data cannot be imported'),'error'); + include('includes/footer.inc'); + exit; + } + } + $sql = "SELECT 1 + FROM custbranch + WHERE debtorno='".$_POST['DebtorNo']."' AND + branchcode='".$_POST['BranchCode']."' LIMIT 1"; + $result=DB_query($sql); + $BranchExists=(DB_num_rows($result)>0); + if ($BranchExists AND $_POST['UpdateIfExists']!=1) { + $ExistedBranches[] = array('debtor'=>$_POST['DebtorNo'], + 'branch'=>$_POST['BranchCode']); + $UpdatedNum++; + }else{ + + if (!isset($_POST['EstDeliveryDays'])) { + $_POST['EstDeliveryDays']=1; + } + if (!isset($Latitude)) { + $Latitude=0.0; + $Longitude=0.0; + } + if ($BranchExists) { + $UpdatedNum++; + $sql = "UPDATE custbranch SET brname = '" . $_POST['BrName'] . "', + braddress1 = '" . $_POST['BrAddress1'] . "', + braddress2 = '" . $_POST['BrAddress2'] . "', + braddress3 = '" . $_POST['BrAddress3'] . "', + braddress4 = '" . $_POST['BrAddress4'] . "', + braddress5 = '" . $_POST['BrAddress5'] . "', + braddress6 = '" . $_POST['BrAddress6'] . "', + lat = '" . $Latitude . "', + lng = '" . $Longitude . "', + specialinstructions = '" . $_POST['SpecialInstructions'] . "', + phoneno='" . $_POST['PhoneNo'] . "', + faxno='" . $_POST['FaxNo'] . "', + fwddate= '" . $_POST['FwdDate'] . "', + contactname='" . $_POST['ContactName'] . "', + salesman= '" . $_POST['Salesman'] . "', + area='" . $_POST['Area'] . "', + estdeliverydays ='" . filter_number_format($_POST['EstDeliveryDays']) . "', + email='" . $_POST['Email'] . "', + taxgroupid='" . $_POST['TaxGroup'] . "', + defaultlocation='" . $_POST['DefaultLocation'] . "', + brpostaddr1 = '" . $_POST['BrPostAddr1'] . "', + brpostaddr2 = '" . $_POST['BrPostAddr2'] . "', + brpostaddr3 = '" . $_POST['BrPostAddr3'] . "', + brpostaddr4 = '" . $_POST['BrPostAddr4'] . "', + brpostaddr5 = '" . $_POST['BrPostAddr5'] . "', + disabletrans='" . $_POST['DisableTrans'] . "', + defaultshipvia='" . $_POST['DefaultShipVia'] . "', + custbranchcode='" . $_POST['CustBranchCode'] ."', + deliverblind='" . $_POST['DeliverBlind'] . "' + WHERE branchcode = '".$_POST['BranchCode']."' AND debtorno='".$_POST['DebtorNo']."'"; + + } else { + $InsertNum++; + $sql = "INSERT INTO custbranch (branchcode, + debtorno, + brname, + braddress1, + braddress2, + braddress3, + braddress4, + braddress5, + braddress6, + lat, + lng, + specialinstructions, + estdeliverydays, + fwddate, + salesman, + phoneno, + faxno, + contactname, + area, + email, + taxgroupid, + defaultlocation, + brpostaddr1, + brpostaddr2, + brpostaddr3, + brpostaddr4, + brpostaddr5, + disabletrans, + defaultshipvia, + custbranchcode, + deliverblind) + VALUES ('" . $_POST['BranchCode'] . "', + '" . $_POST['DebtorNo'] . "', + '" . $_POST['BrName'] . "', + '" . $_POST['BrAddress1'] . "', + '" . $_POST['BrAddress2'] . "', + '" . $_POST['BrAddress3'] . "', + '" . $_POST['BrAddress4'] . "', + '" . $_POST['BrAddress5'] . "', + '" . $_POST['BrAddress6'] . "', + '" . $Latitude . "', + '" . $Longitude . "', + '" . $_POST['SpecialInstructions'] . "', + '" . filter_number_format($_POST['EstDeliveryDays']) . "', + '" . $_POST['FwdDate'] . "', + '" . $_POST['Salesman'] . "', + '" . $_POST['PhoneNo'] . "', + '" . $_POST['FaxNo'] . "', + '" . $_POST['ContactName'] . "', + '" . $_POST['Area'] . "', + '" . $_POST['Email'] . "', + '" . $_POST['TaxGroup'] . "', + '" . $_POST['DefaultLocation'] . "', + '" . $_POST['BrPostAddr1'] . "', + '" . $_POST['BrPostAddr2'] . "', + '" . $_POST['BrPostAddr3'] . "', + '" . $_POST['BrPostAddr4'] . "', + '" . $_POST['BrPostAddr5'] . "', + '" . $_POST['DisableTrans'] . "', + '" . $_POST['DefaultShipVia'] . "', + '" . $_POST['CustBranchCode'] ."', + '" . $_POST['DeliverBlind'] . "')"; + } + + //run the SQL from either of the above possibilites + $ErrMsg = _('The branch record could not be inserted or updated because'); + $result = DB_query($sql, $ErrMsg); + + if (DB_error_no() ==0) { + prnMsg( _('New branch of debtor') .' ' .$_POST['DebtorNo'] . ' ' ._('with branch code') .' ' . $_POST['BranchCode'] . ' ' . $_POST['BrName'] . ' '. _('has been passed validation'),'info'); + } else { //location insert failed so set some useful error info + $InputError = 1; + prnMsg(_($result),'error'); + } + } + } else { //item insert failed so set some useful error info + $InputError = 1; + prnMsg(_($result),'error'); + } + + } + + if ($InputError == 1) { //this row failed so exit loop + break; + } + + $row++; + } + + if ($InputError == 1) { //exited loop with errors so rollback + prnMsg(_('Failed on row '. $row. '. Batch import has been rolled back.'),'error'); + DB_Txn_Rollback(); + } else { //all good so commit data transaction + DB_Txn_Commit(); + if($_POST['UpdateIfExists']==1) { + prnMsg( _('Updated brances total:') .' ' . $UpdatedNum .' '._('Insert branches total:'). $InsertNum,'success' ); + }else{ + prnMsg( _('Exist branches total:') .' ' . $UpdatedNum .' '._('Inserted branches total:'). $InsertNum,'info'); + if($UpdatedNum){ + echo ' <p>' . _('Branches not updated').'</p> + <table class="selection"> + <tr><th>'._('Debtor No').'</th><th>' . _('Branch Code').'</th></tr>'; + foreach($ExistedBranches as $key=>$value){ + echo '<tr><td>'.$value['debtor'].'</td><td>'.$value['branch'].'</td></tr>'; + } + echo '</table>'; + } + } + } + + fclose($FileHandle); + +} elseif ( isset($_POST['gettemplate']) OR isset($_GET['gettemplate'])) { //download an import template + + echo '<br /><br /><br />"'. implode('","',$FieldHeadings). '"<br /><br /><br />'; + +} else { //show file upload form + + prnMsg(_('Please ensure that your csv file is encoded in UTF-8, otherwise the input data will not store correctly in database'),'warn'); + + echo ' + <br /> + <a href="Z_ImportCustbranch.php?gettemplate=1">Get Import Template</a> + <br /> + <br />'; + echo '<form action="Z_ImportCustbranch.php" method="post" enctype="multipart/form-data">'; + echo '<div class="centre">'; + echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; + + echo '<input type="hidden" name="MAX_FILE_SIZE" value="1000000" />' . + _('Upload file') . ': <input name="userfile" type="file" /> + <input type="submit" value="' . _('Send File') . '" />'; + echo '<br/>',_('Update if Customer Branch exists'),':<input type="checkbox" name="UpdateIfExists">'; + echo'</div> + </form>'; + +} + + +include('includes/footer.inc'); +?> |