500000) { echo "size"; $uploadOk = false; } // Allow certain file formats if ($xlsxFileType != "xlsx") { $uploadOk = false; } */ // Check if $uploadOk is set to 0 by an error if ($uploadOk) { //if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"][0], $xlsx_file)) { convertCSV($fileName); //} } function convertCSV($fileName){ $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); if(contains($fileName,"ELP")){ $reader->setLoadSheetsOnly(["ELP (增設)"]); $spreadsheet = $reader->load("../../XLSX/".$fileName); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); $writer->save("../../CSV/ELP.csv"); $file = "../../CSV/ELP.csv"; importArray("ELP", $file); $reader->setLoadSheetsOnly(["EOW-1(1230~"]); $spreadsheet = $reader->load("../../XLSX/".$fileName); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); $writer->save("../../CSV/OW.csv"); $file = "../../CSV/OW.csv"; importArray("OW", $file); } } function importArray($option,$csv_file) { $wellName = []; $m_date = []; $m_value = [[]]; $wellName_count = 0; $m_date_count = 0; $m_value_count_a = -1; $m_value_count_b = 0; $row_count = 0; $csv_row_count = 0; $encounter = false; $handle = fopen($csv_file, 'r'); while (($data = fgetcsv($handle)) !== false) { foreach ($data as $row) { //echo 'data:'.$data.' row'.$row.'
'; //if ($row != "" && $csv_row_count > 0) { if($csv_row_count > 0){ if (contains($csv_file, "OW") || contains($csv_file, "ELP") || contains($csv_file, "RS") || contains($csv_file, "TI") || contains($csv_file, "SM") || contains($csv_file, "SBM.csv") || contains($csv_file, "VG") || contains($csv_file, "HM")) { if (contains($row, "/")) { $arr = explode("(", $row); $row = $arr[0]; $date_arr = explode("/",$row); $date_temp = $date_arr[0] + 1911; $m_date[$m_date_count] = $date_temp.'/'.$date_arr[1].'/'.$date_arr[2]; $m_date_count++; } else if (contains($row, $option)) { $encounter = true; $wellName[$wellName_count] = $row; $wellName[$wellName_count] = str_replace("-", "_", $wellName[$wellName_count]); $wellName_count++; $row_count++; if ($row_count > 0) { $m_value_count_a++; $m_value_count_b = 0; } }else { if($row == "" && $csv_row_count > 1){ $m_value[$m_value_count_a][$m_value_count_b] = null; } $m_value[$m_value_count_a][$m_value_count_b] = $row; $m_value_count_b++; } } else if (contains($csv_file, "SID") || contains($csv_file, "SIS")) { if (contains($row, "/")) { $row_count++; if ($row_count > 0) { $m_value_count_a++; $m_value_count_b = 0; } $date_arr = explode("/",$row); $date_temp = $date_arr[0] + 1911; $m_date[$m_date_count] = $date_temp.'/'.$date_arr[1].'/'.$date_arr[2]; $m_date_count++; } else if ($m_value_count_a > -1) { $m_value[$m_value_count_a][$m_value_count_b] = $row; $m_value_count_b++; } } else if (contains($csv_file, "SB.csv")) { if (contains($row, "/")) { if (substr_count($row, "/") == 1) { //turns 1/x string into float $row = str_replace(" ", "", $row); $pieces = explode("/", $row); $value = $pieces[0] / $pieces[1]; $m_value[$m_value_count_a][$m_value_count_b] = $value; $m_value_count_b++; } else if (substr_count($row, "/") == 2) { $date_arr = explode("/",$row); $date_temp = $date_arr[0] + 1911; $m_date[$m_date_count] = $date_temp.'/'.$date_arr[1].'/'.$date_arr[2]; $m_date_count++; } } else if (contains($row, $option)) { $wellName[$wellName_count] = $row; $wellName[$wellName_count] = str_replace("-", "_", $wellName[$wellName_count]); $wellName_count++; $row_count++; if ($row_count > 0) { $m_value_count_a++; $m_value_count_b = 0; } } else { $m_value[$m_value_count_a][$m_value_count_b] = $row; $m_value_count_b++; } } }/* else if ($row == "" && $csv_row_count > 0) { if ((contains($csv_file, "SM") || contains($csv_file, "HM")) && $encounter == true) { $m_value[$m_value_count_a][$m_value_count_b] = "null"; $m_value_count_b++; } }*/ $csv_row_count++; } } print_r($m_value); import($wellName, $m_date, $m_value, $csv_file, $m_value_count_a, $m_value_count_b, $option); fclose($handle); } function import($wellName, $m_date, $m_value, $csv_file, $m_value_count_a, $m_value_count_b, $option) { include "connectSQL.php"; $count = 0; $is_succeeded = true; if (contains($csv_file, "ELP") || contains($csv_file, "OW") || contains($csv_file, "RS") || contains($csv_file, "TI") || contains($csv_file, "SM") || contains($csv_file, "SB.csv") || contains($csv_file, "SBM.csv") || contains($csv_file, "VG") || contains($csv_file, "HM")) { $tableName = $option."data"; //truncate table $sqlTruncate = "TRUNCATE TABLE C3.dbo." . $tableName; $fetchResult = sqlsrv_query($conn, $sqlTruncate); if ($fetchResult == false) { echo "TF"; } else { echo "TS"; } //insert value for ($b = 0; $b < $m_value_count_b; $b++) { $str = insertTable($m_date, $m_value, $b, $m_value_count_a); if ($str != "") { $sqlInsert = "INSERT INTO C3.dbo." . $tableName . " VALUES (" . $str . ")"; print_r($sqlInsert); $fetchResult = sqlsrv_query($conn, $sqlInsert); if ($fetchResult == false) { $is_succeeded = false; } }else{ break; } } if ($is_succeeded) { echo "IS"; $sqlInsert = "insert into C3.dbo.DataTime (update_time) values (SYSDATETIME());"; $fetchResult = sqlsrv_query($conn, $sqlInsert); } else { echo "IF"; } } else if (contains($csv_file, "SID") || contains($csv_file, "SIS")) { if (contains($csv_file, "SID")) { $tableName = "SIDdata"; } else if (contains($csv_file, "SIS")) { $tableName = "SISdata"; } //delete data $sqlDelete = "DELETE FROM [C3].[dbo].[" . $tableName . "] WHERE e_name = '" . $option . "'"; $fetchResult = sqlsrv_query($conn, $sqlDelete); if ($fetchResult == false) { echo "DF"; } else { echo "DS"; } //insert data for ($b = 0; $b <= $m_value_count_a; $b++) { $str = insertTableSIDSIS($m_value, $b); $strColumnName = getColumnName($m_value, $b); $sqlInsert = "INSERT INTO C3.dbo." . $tableName . " (e_name, m_date, " . $strColumnName . ") VALUES ('" . $option . "','" . $m_date[$b] . "', " . $str . " )"; $fetchResult = sqlsrv_query($conn, $sqlInsert); if ($fetchResult == false) { $is_succeeded = false; } } if($is_succeeded){ echo "IS"; $sqlInsert = "insert into C3.dbo.DataTime (update_time) values (SYSDATETIME());"; $fetchResult = sqlsrv_query($conn, $sqlInsert); }else{ echo "IF"; } } sqlsrv_close($conn); //header("Location: ../../index.php"); } function contains($str, $target) { if (stripos($str, $target) !== false) { return true; } return false; } function insertTable($date, $value, $b, $count) { if ($date[$b] != "") { $str = "'" . $date[$b] . "', "; for ($i = 0; $i <= $count; $i++) { if($value[$i][$b] == null){ $str .= "null"; } $str .= $value[$i][$b]; if ($i != $count) { $str .= ", "; } } return $str; } } function insertTableSIDSIS($value, $count) { $str = ""; for ($i = 0; $i < count($value[$count]); $i++) { $str .= $value[$count][$i]; if ($i != count($value[$count]) - 1) { $str .= ", "; } } return $str; } function getColumnName($value, $count) { $str = "depth_0, "; for ($i = 0; $i < count($value[$count]) - 1; $i++) { $str .= "depth_" . ($i + 1); if ($i != count($value[$count]) - 2) { $str .= ", "; } } return $str; } //header("Location: ../../index.php"); ?>