1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
<?php
include 'config.php';
// Check if the user is logged in
if ((!isSet($_SESSION['loginname'])) || ($loggin <> '1')) {
header("Location: login.php");
exit;
}
$nowdate = date("Y-m-d H:i:s");
if (!empty($_POST["excelfile_name"])) {
include 'phpexcel/PHPExcel.php';
include 'phpexcel/PHPExcel/Writer/Excel2007.php';
require_once 'phpexcel/PHPExcel/IOFactory.php';
$file_name = $_POST["excelfile_name"];
$reader = PHPExcel_IOFactory::createReader('Excel2007'); // 讀取2007 excel 檔案
$PHPExcel = $reader->load("excel_file/$file_name"); // 檔案名稱
$sheet = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
//$highestRow = $sheet->getHighestRow(); // 取得總列數
$highestRow = $sheet->getHighestDataRow(); // 取得總列數
$highestColumn = 3; // set column
//echo "==".$highestRow."<br />";
// 一次讀取一列
for ($excel_row = 2; $excel_row <= $highestRow; $excel_row++) {
for ($excel_column = 0; $excel_column <= $highestColumn; $excel_column++) {
$val = $sheet->getCellByColumnAndRow($excel_column, $excel_row)->getValue();
// $val . ',';
if ($excel_column == 0) { //region
$file_region = $val;
} else if ($excel_column == 1) { //series_code
$file_series_code = $val;
} else if ($excel_column == 2) { //model_code
$file_model_code = $val;
} else if ($excel_column == 3) { //serial_number
$file_serial_number = $val;
} else {
}
}
$sql = "select * from series series, model model where model.series_id = series.series_id and series.series_code = ? and model.model_code = ? and series.region = ? and model.region = ? and series.deleted=? and model.deleted=? group by model.model_id ";
if (!($sth = $dbh->prepare($sql))) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
$parameter = array($file_series_code,$file_model_code, $file_region, $file_region, "0", "0" );
if (!$sth->execute($parameter)) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
if($sth->rowCount() > 0){//make sure model and series exist
$sql = "select * from serial_number where series_code=? and model_code=? and serial_number =? and region = ? and deleted=?";
if (!($sth = $dbh->prepare($sql))) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
$parameter = array($file_series_code,$file_model_code, $file_serial_number, $file_region, "0");
if (!$sth->execute($parameter)) {
throw new Exception('[' . $sth->errorCode() . ']: ' . print_r($sth->errorInfo()));
}
//$row = $sth->fetch(PDO::FETCH_ASSOC);
if($sth->rowCount() == 0){//new serial number
$sql2 = "insert into serial_number (region, series_code, model_code, serial_number, createby, createdate) values (?, ?, ?, ?, ?, ?)";
if (!($sth2 = $dbh->prepare($sql2))) {
throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
}
$parameter2 = array($file_region, $file_series_code, $file_model_code, $file_serial_number, $_SESSION['cmsloginid'], $nowdate);
if (!$sth2->execute($parameter2)) {
throw new Exception('[' . $sth2->errorCode() . ']: ' . print_r($sth2->errorInfo()));
}
}
}
}
unlink("excel_file/$file_name");
header("Location: import_serial_number_index.php?msg=Update Serial Number Successfully");
}
?>
|