BIM-Monitor/script/php/getData2.php

<?php
include "connectSQL.php";
if (isset($_GET['q'])) {

    echo "<table id='dataTable' class='display' width='100%'>";
    $wellName = $_GET['q'];
    //ELP's only
    $wellNameA = str_replace("A", "-1A", $wellName);

    if(contains($wellName, "ELP") ||contains($wellName, "OW") || contains($wellName, "SM") || contains($wellName, "SB") || contains($wellName, "SBM") || contains($wellName, "VG") || contains($wellName, "HM")){

        if(contains($wellName, "OW")){
            $sqlOW = "SELECT * FROM [C3].[dbo].[OWdata]";
            $fetchResult = sqlsrv_query($conn, $sqlOW);
        }else if(contains($wellName, "SM")){
            $sqlSM = "SELECT * FROM [C3].[dbo].[SMdata]";
            $fetchResult = sqlsrv_query($conn, $sqlSM);
        }else if(contains($wellName, "SBM")){
            $sqlSBM = "SELECT * FROM [C3].[dbo].[SBMdata]";
            $fetchResult = sqlsrv_query($conn, $sqlSBM);
        }else if(contains($wellName, "SB")){
            $sqlSB = "SELECT * FROM [C3].[dbo].[SBdata]";
            $fetchResult = sqlsrv_query($conn, $sqlSB);
        }else if(contains($wellName, "VG")){
            $sqlVG = "SELECT * FROM [C3].[dbo].[VGdata]";
            $fetchResult = sqlsrv_query($conn, $sqlVG);
        }else if(contains($wellName, "HM")){
            $sqlHM = "SELECT * FROM [C3].[dbo].[HMdata]";
            $fetchResult = sqlsrv_query($conn, $sqlHM);
        }else if(contains($wellName, "ELP")){
			$sqlELP = "SELECT * FROM [C3].[dbo].[ELPdata]";
			$fetchResult = sqlsrv_query($conn, $sqlELP);
		}
        //Table's header and footer
        echo "<thead>
                <tr>
                    <th>測量日期</th>
                    <th>".str_replace("_", "-", $wellName)."</th>
                </tr>
              </thead>
              <tfoot>
                <tr>
                    <th>測量日期</th>
                    <th>".str_replace("_", "-", $wellName)."</th>
                </tr>
              </tfoot>";

    }else if(contains($wellName, "SID") || contains($wellName, "SIS")){

        $fetchResult = fetchSISSID($wellName, $conn);

        while($row = sqlsrv_fetch_array($fetchResult)){
            $columnCount = count(array_filter($row))/2-1;
        }

        $str = tHeaderFooter($columnCount);

        echo "
            <thead>
                <tr>
                    <th>測量日期</th>
                    <th>觀測深度0</th>
                    ".$str."
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>測量日期</th>
                    <th>觀測深度0</th>
                    ".$str."
                </tr>
            </tfoot>";

        $fetchResult = fetchSISSID($wellName, $conn);

    }else if(contains($wellName, "TI")){
        $sqlTI = "SELECT * FROM [C3].[dbo].[TIdata]";
        $fetchResult = sqlsrv_query($conn, $sqlTI);

        echo "<thead>
                <tr>
                    <th>測量日期</th>
                    <th>".$wellName."(1~3向)</th>
                    <th>".$wellName."(2~4向)</th>
                </tr>
              </thead>
              <tfoot>
                <tr>
                    <th>測量日期</th>
                    <th>".$wellName."(1~3向)</th>
                    <th>".$wellName."(2~4向)</th>
                </tr>
              </tfoot>";
    }else if(contains($wellName, "RS")){
        $sqlRS = "SELECT * FROM [C3].[dbo].[RSdata]";
        $fetchResult = sqlsrv_query($conn, $sqlRS);
        $wellName = substr($wellName, 0 , -2);
        echo "<thead>
                <tr>
                    <th>測量日期</th>
                    <th>".$wellName."-A</th>
                    <th>".$wellName."-B</th>
                    <th>".$wellName."-C</th>
                    <th>".$wellName."-D</th>
                    <th>".$wellName."-E</th>
                    <th>".$wellName."-F</th>
                </tr>
              </thead>
              <tfoot>
                <tr>
                    <th>測量日期</th>
                    <th>".$wellName."-A</th>
                    <th>".$wellName."-B</th>
                    <th>".$wellName."-C</th>
                    <th>".$wellName."-D</th>
                    <th>".$wellName."-E</th>
                    <th>".$wellName."-F</th>
                </tr>
              </tfoot>";
    }
    
    echo "<tbody>";

    while($row = sqlsrv_fetch_array($fetchResult)){
        
        echo "<tr>";

        echo "<td>";
        echo $row["m_date"];
        echo "</td>";

        if(contains($wellName, "OW") || contains($wellName, "ELP") || contains($wellName, "SM") || contains($wellName, "SB") || contains($wellName, "SBM") || contains($wellName, "VG") || contains($wellName, "HM")){
            $columnName = str_replace("-", "_", $wellName);
            $columnNameA = str_replace("-", "_", $wellNameA);

            fontHead($row[$columnName], $wellName);
            echo $row[$columnName];
            fontFoot($row[$columnName], $wellName);

        }else if(contains($wellName, "SID") || contains($wellName, "SIS")){
            
            displayValue($row, $wellName, $columnCount);
            
        }else if(contains($wellName, "TI")){
            $columnName = str_replace("-", "_", $wellName);
            $columnName13 = $columnName;
            $columnName24 = $columnName;

            fontHead($row[$columnName13], $wellName);
            echo $row[$columnName13];
            fontFoot($row[$columnName13], $wellName);

            fontHead($row[$columnName24], $wellName);
            echo $row[$columnName24];
            fontFoot($row[$columnName24], $wellName);
        }else if(contains($wellName, "RS")){
            $columnName = $wellName;
            $columnNameA = $columnName."_A";
            $columnNameB = $columnName."_B";
            $columnNameC = $columnName."_C";
            $columnNameD = $columnName."_D";
            $columnNameE = $columnName."_E";
            $columnNameF = $columnName."_F";

            fontHead($row[$columnNameA], $wellName);
            echo $row[$columnNameA];
            fontFoot($row[$columnNameA], $wellName);

            fontHead($row[$columnNameB], $wellName);
            echo $row[$columnNameB];
            fontFoot($row[$columnNameB], $wellName);

            fontHead($row[$columnNameC], $wellName);
            echo $row[$columnNameC];
            fontFoot($row[$columnNameC], $wellName);

            fontHead($row[$columnNameD], $wellName);
            echo $row[$columnNameD];
            fontFoot($row[$columnNameD], $wellName);

            fontHead($row[$columnNameE], $wellName);
            echo $row[$columnNameE];
            fontFoot($row[$columnNameE], $wellName);

            fontHead($row[$columnNameF], $wellName);
            echo $row[$columnNameF];
            fontFoot($row[$columnNameF], $wellName);
        }
        
        echo "</tr>";
    }

    echo "</tbody>";
    echo "</table>";
    sqlsrv_close($conn);
}
    //Turn the illegal value's color red(Header)
    function fontHead($row, $wellName){
        $e_value = 0;
        $e_value_n = 0;
        if(contains($wellName, "OW")){
            $e_value = -2;
        }else if(contains($wellName, "ELP")){
            $e_value = -7.9;
        }else if(contains($wellName, "SID") || contains($wellName, "SIS")){
            $e_value = 5.2;
        }else if(contains($wellName, "TI")){
            $e_value = 0.00106;
            $e_value_n = -0.00106;
        }else if(contains($wellName, "RS")){
            $e_value = 2000;
            $e_value_n = -2000;
        }else if(contains($wellName, "SM")){
            $e_value = 25;
        }else if(contains($wellName, "SBM")){
            $e_value = 9.5;
            $e_value_n = -9.5;
        }else if(contains($wellName, "SB")){
            $e_value = 0.0008;
            $e_value_n = -0.0008;
        }else if(contains($wellName, "VG")){
            $e_value = 110;
        }else if(contains($wellName, "HM")){
            $e_value = 25;
        }

		echo "<td>";
    }

    //Turn the illegal value's color red(Footer)
    function fontFoot($row, $wellName){
        $e_value = 0;
        $e_value_n = 0;
        if(contains($wellName, "OW")){
            $e_value = -2;
        }else if(contains($wellName, "ELP")){
            $e_value = -7.9;
        }else if(contains($wellName, "SID") || contains($wellName, "SIS")){
            $e_value = 5.2;
        }else if(contains($wellName, "TI")){
            $e_value = 0.00106;
            $e_value_n = -0.00106;
        }else if(contains($wellName, "RS")){
            $e_value = 2000;
            $e_value_n = -2000;
        }else if(contains($wellName, "SM")){
            $e_value = 25;
        }else if(contains($wellName, "SBM")){
            $e_value = 9.5;
            $e_value_n = -9.5;
        }else if(contains($wellName, "SB")){
            $e_value = 0.0008;
            $e_value_n = -0.0008;
        }else if(contains($wellName, "VG")){
            $e_value = 110;
        }else if(contains($wellName, "HM")){
            $e_value = 25;
        }

        if(contains($wellName, "OW") || contains($wellName, "ELP") || contains($wellName, "SIS") || contains($wellName, "SID") || contains($wellName, "SM") || contains($wellName, "VG") || contains($wellName, "HM")){
            if($row > $e_value){
                echo "</font>";
                echo "</td>";
            }else{
                echo "</td>";
            }
        }else if(contains($wellName, "TI") || contains($wellName, "RS") || contains($wellName, "SBM") || contains($wellName, "SB")){
            if($row > $e_value || $row < $e_value_n){
                echo "</font>";
                echo "</td>";
            }else{
                echo "</td>";
            }
        }

    }

    //Check if string contains your designated word
    function contains($str, $target)
    {
        if (stripos($str, $target) !== false){
            return true;
        }
        return false;
    }

    function fetchSISSID($wellName, $conn){
        if(contains($wellName, "SID")){
            $sqlSID = "SELECT * FROM [C3].[dbo].[SIDdata] WHERE e_name = '".$wellName."'";
            $fetchResult = sqlsrv_query($conn, $sqlSID);
        }else if(contains($wellName, "SIS")){
            $sqlSIS = "SELECT * FROM [C3].[dbo].[SISdata] WHERE e_name = '".$wellName."'";
            $fetchResult = sqlsrv_query($conn, $sqlSIS);
        }
        return $fetchResult;
    }

    //SIS SID's header footer
    function tHeaderFooter($columnCount){
        $str = "";
        for($i = 1; $i < $columnCount; $i++){
            $str .= "<th>觀測深度-".($i/2)."</th>";
        }
        return $str;
    }

    //SIS SID's value display
    function displayValue($row, $wellName, $columnCount){
        for($i = 0; $i < $columnCount; $i++){
            $str = "depth_".($i);
            fontHead($row[$str], $wellName);
            echo $row[$str];
            fontFoot($row[$str], $wellName);
        }
    }