customer.php
<![CDATA[<?php include_once("checklogin.php"); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="css/dataTables.bootstrap4.css">
<link rel="stylesheet" type="text/css" href="css/responsive.dataTables.css">
<title>Lekjew.com</title>
<?php include_once("include/style.php"); ?>
<style>
@charset "utf-8";
/* layout.css Style */
.image-preview-input {
position: relative;
overflow: hidden;
margin: 0px;
color: #000;
background-color: #ffffff;
border-color: #ccc;
}
.image-preview-input:hover{
position: relative;
overflow: hidden;
margin: 0px;
color: #000;
background-color: #e9e9e9;
border-color: #ccc;
}
.image-preview-input input[type=file] {
position: absolute;
top: 0;
right: 0;
margin: 0;
padding: 0;
font-size: 20px;
cursor: pointer;
opacity: 0;
filter: alpha(opacity=0);
}
.image-preview-input-title {
margin-left:2px;
}
input[type=text]:disabled, select[disabled]{background-color:#f9f9f9 !important; border:1px solid #ddd;height:34px !important;}
.cursor{ cursor:pointer;}
</style>
</head>
<body class="adminbody">
<?php include_once("include/header_login.php"); ?>
<div id="main" >
<?php include_once("leftmenu.php"); ?>
<div class="content-page">
<div class="content">
<div class="col-md-12">
<div class="card pd-10">
<div class="col-md-12"><div class="row">
<h3>Customer</h3>
</div></div>
<div class="col-md-4 col-xs-4" id="browsefile">
<form name="frmImportfile" id="frmImportfile" method="post" runat="server" enctype="multipart/form-data">
<div class="input-group image-preview">
<input name="filename" id="filename" type="text" class="form-control" placeholder="Select File Excel" disabled="disabled" >
<span class="input-group-prepend input-group-sm">
<div class="btn btn-primary image-preview-input" style="height:34px;" onClick="ClearFileImport();" > <i class="fa fa-times"></i> </div>
<div class="btn btn-default image-preview-input" style="height:34px;"> Select
<input type="file" name="fileUpload" id="fileUpload" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" onchange="fname();">
</div>
<button type="button" class="btn btn-primary btn-sm" style="height:34px" id="uploadfile">Import</button>
</span>
</div>
</form>
</div>
<div class="col-md-12"><div class="row">
<div class="col-md-4"></div>
<div class="col-md-4">
<div class="progress hide">
<div class="progress-bar progress-bar-striped progress-bar-animated" role="progressbar" aria-valuenow="100" aria-valuemin="0" aria-valuemax="100" style="width: 100%"></div>
</div>
<div id="showdata hide"></div>
<div id="showerror hide"></div>
</div>
</div></div>
<table id="tableA" class="table table-bordered tablec" cellspacing="0" cellpadding="0" border="1" >
<thead>
<tr>
<th>#</th>
<th>Customer</th>
<th>Dealer Code</th>
<th>Dealer Name</th>
<th>Branch</th>
<th>Del. Time</th>
<th>Address</th>
<th>Province</th>
<th>Hub</th>
<th>Latitude</th>
<th>Longitude</th>
<th>Route</th>
<th>Route Group</th>
</tr>
<tr>
<th>#</th>
<th>Customer</th>
<th>Dealer Code</th>
<th>Dealer Name</th>
<th>Branch</th>
<th>Del. Time</th>
<th>Address</th>
<th>Province</th>
<th>Hub</th>
<th>Latitude</th>
<th>Longitude</th>
<th>Route</th>
<th>Route Group</th>
</tr>
</thead>
<tbody id="showtableA">
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
<tfoot class='hide'>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>
</table>
</div>
</div>
</div>
</div>
</div>
<div class="alert alert-success" id="success-alert" style="position:absolute; z-index:2025; bottom:40%; left:45%; width:350px; display:none; ">
<button type="button" class="btn btn-outline-default close" data-dismiss="alert">x</button>
<div class="text-center m-top-20"><h4 id="resultalert"></h4></div>
<br>
</div>
<div class="alert alert-danger" id="fail-alert" style="position:absolute; z-index:2025; bottom:40%; left:45%; width:350px; display:none; ">
<button type="button" class="btn btn-outline-default close" data-dismiss="alert">x</button>
<div class="text-center m-top-20"><h4 id="resultfailalert"></h4></div>
<br>
</div>
</body>
</html>
<?php include_once("include/js.php"); ?>
<script src="js/jquery.dataTables.js"></script>
<script src="js/dataTables.bootstrap4.min.js"></script>
<script type="text/javascript">
RefreshTable();
CallData();
function fname(){
var fname = ($('#fileUpload').val()).replace(/^.*\\/, "");
$('#filename').attr("placeholder", fname);
}
function ClearFileImport(){
$('#frmImportfile')[0].reset();
$('#filename').attr("placeholder","Select File Excel");
}
function showSuccessAlert(data){
$('#resultalert').html(data);
$("#success-alert").fadeTo(2000, 300).slideUp(300, function(){
$("#success-alert").slideUp(300);
});
}
$('#uploadfile').click(function(event){
var form = $('#frmImportfile')[0];
var form_data = new FormData(form);
form_data.append("CREATE_BY", "<?php echo $username;?>");
form_data.append("USERNAME", "LEKJEW241008");
form_data.append("PASSWORD", "LEKJEW@241008");
$.ajax({
type: "POST",
enctype: 'multipart/form-data',
url: "ImportCustomer.php",
data: form_data,
dataType: 'json',
processData: false,
contentType: false,
cache: false,
beforeSend: function() {
$('.progress').removeClass('hide');
$('#showdata').html("กำลังนำข้อมูลเข้า กรุณารอสักครู่");
},
success: function (data){
$('.progress').addClass('hide');
$('#showdata').html("");
var j=0;
var err = "";
if(data.length > 0) {
$.each(data, function(index, value) {
if(value.ERR!="" ){
err = err + "<div class='col-md-12'>"+ value.ERR + "</div>";
}
j++;
});
}
if(j>0){
$('#showerror').html(err);
}else{
showSuccessAlert("สำเร็จ");
CallData();
}
},
error:function(jqXHR, exception){
$('.progress').addClass('hide');
$('#showdata').html("");
var error_msg = '';
if (jqXHR.status === 0) {
error_msg = 'Not connect.\n Verify Network.';
} else if (jqXHR.status == 404) {
error_msg = 'Not Found File ImportCustomer';
} else if (jqXHR.status == 500) {
error_msg = 'Internal Server Error [500].';
} else if (exception === 'parsererror') {
error_msg = 'Requested JSON parse failed.';
} else if (exception === 'timeout') {
error_msg = 'Time out error.';
} else if (exception === 'abort') {
error_msg = 'Ajax request aborted.';
} else {
error_msg = 'Uncaught Error.\n' + jqXHR.responseText;
}
$('#showerror').html(error_msg);
}
});
});
function CallData(){
$.ajax({
url: "customer_data.php",
async: false,
cache: false,
contentType: false,
processData: false,
type: 'post',
success: function(data) {
var tr = "";
var JsonData = JSON.parse(data);
var resL = JsonData.length;
for (var i = 0; i < resL; i++) {
tr=tr+"<tr>" ;
tr=tr+"<td>";
tr=tr + JsonData[i]["CUST_ID"];
tr=tr+"</td>";
tr=tr+"<td>";
tr=tr + JsonData[i]["CUST_GROUP"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_CODE"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_NAME"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_BRANCH"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_TIME"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_ADDRESS"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_PROVINCE"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_REGION"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_LAT"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_LONG"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_ROUTE"];
tr=tr+"</td>";
tr=tr+"<td>";
tr = tr + JsonData[i]["CUST_GROUPROUTE"];
tr=tr+"</td>";
tr=tr+"</tr>" ;
}
$('#showtableA').html('');
$('#tableA').DataTable().destroy();
$('#showtableA').html(tr);
RefreshTable();
},
error:function(data){
}
});
}
function RefreshTable(){
$('#tableA thead tr:eq(0) th').empty();
$('#tableA thead tr:eq(0) th').each( function (i) {
if(i>0){
var title = $(this).text();
$(this).html( '<input type="text" class="form-control search'+i+' section-no-print">' );
$( 'input', this ).on( 'keyup change', function () {
if ( table.column(i).search() !== this.value ) {
table
.column(i)
.search( this.value )
.draw();
}
});
}
});
var table = $('#tableA').DataTable( {
dom: "<'row'<'col-md-1'<'pull-left'>><'col-md-3'<'pull-left'>><'col-md-8'><'col-sm-12'tr>>",
searching: true,
ordering: false,
lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
iDisplayLength: -1,
scrollY: "450px",
scrollCollapse: true,
scrollX: true,
responsive: true,
"pagingType": "full_numbers",
"fnInitComplete": function (oSettings) { $('.select_broker_wrapper').find('.tblBroker_head').hide(); }
});
$('#tableA thead tr:eq(0)').empty();
$('#tableA thead tr:eq(0)').empty();
$('#tableA tfoot tr:eq(0)').empty();
$('.dataTables_scrollBody #tableA tr:eq(0) ').empty();
}
</script>]]> customer_data.php
<![CDATA[<?php
include('connectDBMS.php');
$data = array();
$sql = "SELECT *
FROM customer as p
ORDER BY p.CUST_ID desc
";
$rsc = sqlsrv_query($ConnDB, $sql);
while($rowp = sqlsrv_fetch_object($rsc)) {
$data[] = array( "CUST_ID" => $rowp->CUST_ID
, "CUST_GROUP" => $rowp->CUST_GROUP
, "CUST_CODE" => $rowp->CUST_CODE
, "CUST_NAME" => $rowp->CUST_NAME
, "CUST_BRANCH" => $rowp->CUST_BRANCH
, "CUST_TIME" => $rowp->CUST_TIME
, "CUST_ADDRESS" => $rowp->CUST_ADDRESS
, "CUST_PROVINCE" => $rowp->CUST_PROVINCE
, "CUST_REGION" => $rowp->CUST_REGION
, "CUST_LAT" => $rowp->CUST_LAT
, "CUST_LONG" => $rowp->CUST_LONG
, "CUST_ROUTE" => $rowp->CUST_ROUTE
, "CUST_GROUPROUTE" => $rowp->CUST_GROUPROUTE
);
}
echo json_encode($data, JSON_UNESCAPED_UNICODE);
sqlsrv_close($ConnDB);
?>]]>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;
if($_POST["USERNAME"]=="LEKJEW241008" && $_POST["PASSWORD"]=="LEKJEW@241008"){
if(isset($_FILES['fileUpload']['name'] ) and $_FILES['fileUpload']['name']!="" ){
$fname = $_FILES['fileUpload']['name'];
$ftype = $_FILES['fileUpload']['type'];
$fdata = $_FILES['fileUpload']['tmp_name'];
$filetype = strtolower(strrchr($fname, ".")); //.xlsx, .xls
$filename = str_replace($filetype,"", $fname);
$fname = "ImportCustomer_".date("YmdHis").$filetype;
copy( $fdata, "excelImport/".$fname );
//--------------------- เริ่มอ่าน ไฟล์ excel -----------------------------
require_once 'ClassesExcel/PHPExcel.php';
include 'ClassesExcel/PHPExcel/IOFactory.php';
$inputFileName = "excelImport/$fname";
$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);
}
unlink("excelImport/".$fname);
}else{
$result[] = array("ERR" => "Not File" );
}
}else{
$result[] = array("ERR" => "USERNANE or PASSWORD is incorrect");
}
echo json_encode($result, JSON_UNESCAPED_UNICODE);
?>]]>