BIM-Monitor/script/php/upload.php

<?php
require '../../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$target_dir = "../../XLSX/";
//$fileName = basename($_FILES["fileToUpload"]["name"][0]);
$fileName = "EOW ELP ELP補設 (11).xlsx";
$xlsx_file = $target_dir . $fileName;
$uploadOk = true;
$xlsxFileType = strtolower(pathinfo($xlsx_file, PATHINFO_EXTENSION));

/*
File too large : size
Not CSV : CSV
Delete succeeded : DS / DF
INSERT SUCCEEDED : IS / IF
TRUNCATE SUCCEEDED : TS / TF
*/

// Check file size
/*
if ($_FILES["fileToUpload"]["size"][0] > 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.'<br>';
            //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");    
?>