ImportCustomer.php
<![CDATA[<?php
header('Content-Type: application/json');
include('connectDBMS.php');
$CUST_CREATEDATE = date("Y-m-d H:i:s");
// $CUST_CREATEBY = $_POST["CREATE_BY"];
$CUST_CREATEBY = "Bird";
$dataexcel = array();
$ROUTG = array();
$result = array();
$o = 0;
//--------------------- เริ่มอ่าน ไฟล์ excel -----------------------------
require_once 'ClassesExcel/PHPExcel.php';
include 'ClassesExcel/PHPExcel/IOFactory.php';
$inputFileName = "excelImport/Dealer Master Central and East Update 26.08.2020_editroutename.xlsx";
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$namedData = array();
for ( $i=2; $i <= $highestRow; $i++) {
$dataRow = $objWorksheet->rangeToArray('A'.$i.':'.$highestColumn.$i, null, true, true, true);
$namedData[$r] = $dataRow[$i];
$r++;
}
try {
foreach ($namedData as $excel ){
$CUST_GROUP = iconv_substr(str_replace("'","", trim($excel["A"])),0,15);
$CUST_CODE = iconv_substr(str_replace("'","", trim($excel["B"])),0,15);
$CUST_NAME = iconv_substr(str_replace("'","", trim($excel["C"])),0,150);
$CUST_BRANCH = iconv_substr(str_replace("'","", trim($excel["D"])),0,100);
$CUST_TIME = date( "H:i" , strtotime(PHPExcel_Style_NumberFormat::toFormattedString(trim($excel["E"]), 'H:i')));
$CUST_ADDRESS = iconv_substr(str_replace("'","", trim($excel["F"])),0,250);
$CUST_PROVINCE = iconv_substr(str_replace("'","", trim($excel["G"])),0,80);
$CUST_REGION = iconv_substr(str_replace("'","", trim($excel["H"])),0,80);
$CUST_LAT = iconv_substr(str_replace("'","", trim($excel["I"])),0,15);
$CUST_LONG = iconv_substr(str_replace("'","", trim($excel["J"])),0,20);
$CUST_ROUTE = iconv_substr(str_replace("'","", trim($excel["K"])),0,100);
$CUST_ROUTE_G = iconv_substr(str_replace("'","", trim($excel["L"])),0,100);
if (in_array( $CUST_ROUTE_G, $ROUTG )!==true && $CUST_ROUTE_G!="") {
$ROUTG[] = $CUST_ROUTE_G;
}
$ROUTG_PRV = $ROUTG[$o-1];
$o++;
$dataexcel[] = array(
"CUST_GROUP" => $CUST_GROUP
, "CUST_CODE" => $CUST_CODE
, "CUST_NAME" => $CUST_NAME
, "CUST_BRANCH" => $CUST_BRANCH
, "CUST_TIME" => $CUST_TIME
, "CUST_ADDRESS" => $CUST_ADDRESS
, "CUST_PROVINCE" => $CUST_PROVINCE
, "CUST_REGION" => $CUST_REGION
, "CUST_LAT" => $CUST_LAT
, "CUST_LONG" => $CUST_LONG
, "CUST_ROUTE" => $CUST_ROUTE
, "CUST_ROUTE_G" => $ROUTG_PRV
);
}
$SQLRUNNING = "SELECT (CASE WHEN max(CUST_ID) is null THEN 1 ELSE max(CUST_ID)+1 END) as RUNNING FROM customer";
$RSR = sqlsrv_query($ConnDB, $SQLRUNNING);
$RS = sqlsrv_fetch_object($RSR);
$CUST_ID = $RS->RUNNING;
for($i=0; $i<sizeof($dataexcel); $i++){
$CUST_GROUP = $dataexcel[$i]["CUST_GROUP"];
$CUST_CODE = $dataexcel[$i]["CUST_CODE"];
$CUST_NAME = $dataexcel[$i]["CUST_NAME"];
$CUST_BRANCH = $dataexcel[$i]["CUST_BRANCH"];
$CUST_TIME = $dataexcel[$i]["CUST_TIME"];
$CUST_ADDRESS = $dataexcel[$i]["CUST_ADDRESS"];
$CUST_PROVINCE = $dataexcel[$i]["CUST_PROVINCE"];
$CUST_REGION = $dataexcel[$i]["CUST_REGION"];
$CUST_LAT = $dataexcel[$i]["CUST_LAT"];
$CUST_LONG = $dataexcel[$i]["CUST_LONG"];
$CUST_ROUTE = $dataexcel[$i]["CUST_ROUTE"];
$CUST_ROUTE_G = $dataexcel[$i]["ROUTG_PRV"];
$INSCUST = "INSERT INTO customer ( CUST_ID, CUST_GROUP, CUST_CODE, CUST_NAME, CUST_BRANCH, CUST_TIME, CUST_ADDRESS, CUST_PROVINCE, CUST_REGION, CUST_LAT, CUST_LONG, CUST_ROUTE, CUST_GROUPROUTE, CUST_CREATEDATE, CUST_CREATEBY, CUST_UPDATEDATE, CUST_UPDATEBY) VALUES ";
$INSCUST.= "($CUST_ID, '$CUST_GROUP', '$CUST_CODE', '$CUST_NAME', '$CUST_BRANCH', '$CUST_TIME', '$CUST_ADDRESS', '$CUST_PROVINCE', '$CUST_REGION', '$CUST_LAT', '$CUST_LONG', '$CUST_ROUTE', '$CUST_GROUPROUTE', '$CUST_CREATEDATE', '$CUST_CREATEBY', '$CUST_CREATEDATE', '$CUST_CREATEBY')";
$RINSINSCUST = sqlsrv_prepare($ConnDB, $INSCUST);
if(!$RINSINSCUST ){ throw new Exception("PREPARE ACTION#". $INSCUST); }
if(!sqlsrv_execute($RINSINSCUST )){ throw new Exception("EXECUTE ACTION#" . $INSCUST); }
$CUST_ID++;
}
sqlsrv_commit($ConnDB);
sqlsrv_close($ConnDB);
}catch (Exception $ex) {
$result[] = array( "ERR" => $ex->getMessage() );
sqlsrv_rollback($ConnDB);
sqlsrv_close($ConnDB);
}
echo json_encode($result, JSON_UNESCAPED_UNICODE);
?>]]>