<?php
date_default_timezone_set("Asia/Taipei");
include("db.php");
function checkUser($username,$password){
    if($username=="admin"&&$password=="123456"){
        return true;
    }else{
        return false;
    }
}
function checkSession(){
    if($_SESSION['user']==""){
        header("Location:index.php");
    }
}
function getOrderIDFromID($id){
    $db=new db();
    $result=$db->query("select orderID from orders where id='{$id}'");
    return $result[0]['orderID'];
}
function getIDFromOrderID($orderID){
    $db=new db();
    //echo $orderID;
    $result=$db->query("select id from orders where orderID='{$orderID}' order by id desc");
    return $result[0]['id'];
}
function insertSigns($id,$type,$picName){
    //echo "insert into signs (ordersId,type,picName) values ('{$id}','{$type}','$picName')";
    $db=new db();
    $result=$db->query("insert into signs (ordersId,type,picName) values ('{$id}','{$type}','$picName')");
}
function updateOrder($id,$orderID,$testDate,$projectName,$applicantName,$businessOwner,$supervisor,$contractor,$sampler,$examinator,$structuralPart,$remark,$productionDate,$testedMaterial,$weather,$materialsUsed,$surfaceTemp,$equipUsed,$materialsName,$paymentCompany,$paymentTax,$paymentAddr,$paymentContactor,$paymentPhone,$paymentFee,$tester){
    $db=new db();
    $db->query("update orders set orderID='{$orderID}',testDate = '{$testDate}',projectName = '{$projectName}',applicantName = '{$applicantName}',businessOwner = '{$businessOwner}',businessOwner = '{$businessOwner}',supervisor = '{$supervisor}',contractor = '{$contractor}',sampler = '{$sampler}',examinator = '{$examinator}',structuralPart = '{$structuralPart}',productionDate = '{$productionDate}',testedMaterial = '{$testedMaterial}',weather = '{$weather}',materialsUsed = '{$materialsUsed}',surfaceTemp = '{$surfaceTemp}',equipUsed = '{$equipUsed}',materialsName = '{$materialsName}',paymentCompany = '{$paymentCompany}',paymentTax = '{$paymentTax}',paymentAddr = '{$paymentAddr}',paymentContactor = '{$paymentContactor}',paymentPhone = '{$paymentPhone}',paymentFee = '{$paymentFee}',tester = '{$tester}',remark = '{$remark}' where id='{$id}'");
}
function insertContactName($id,$contactName,$type){
    $orderID=getOrderIDFromID($id);
    $db=new db();
    $result=$db->query("select * from orders where orderID='{$orderID}'");
    $sampler=explode(" ",$result[0]['sampler']);
    $examinator=explode(" ",$result[0]['examinator']);
    if($examinator==""){
        $examinator[]=$result[0]['examinator'];
    }
    $paymentContactor=explode(" ",$result[0]['paymentContactor']);
    if($paymentContactor[0]==""){
        $paymentContactor[0]=$result[0]['paymentContactor'];
    }
    $tester=explode(" ",$result[0]['tester']);
    switch($type){
        case "0":
            if(!in_array($contactName,$sampler)){
                $sampler[]=$contactName;
                foreach($sampler as $sampler){
                    $samplerLine .= $samplerItem." ";
                }
                $samplerLine=trim($samplerLine);
                $db_insert=new db();
                $db->query("update orders set sampler = '{$samplerLine}' where orderID='{$orderID}'");
            }
        case "1":
            if(!in_array($contactName,$examinator)){
                $examinator[]=$contactName;
                foreach($examinator as $examinatorItem){
                    $examinatorLine .= $examinatorItem." ";
                }
                $examinatorLine=trim($examinatorLine);
                $db_insert=new db();
                $db->query("update orders set examinator = '{$examinatorLine}' where orderID='{$orderID}'");
            }
        case "2":
            if(!in_array($contactName,$paymentContactor)){
                $paymentContactor[]=$contactName;
                foreach($paymentContactor as $paymentContactorItem){
                    $paymentContactorLine .= $paymentContactorItem." ";
                }
                $paymentContactorLine=trim($paymentContactorLine);
                
                $db_insert=new db();
                $db->query("update orders set paymentContactor = '{$paymentContactorLine}' where orderID='{$orderID}'");
            }
        case "3":
            if(!in_array($contactName,$tester)){
                $tester[]=$contactName;
                foreach($tester as $testerItem){
                    $testerLine .= $testerItem." ";
                }
                $testerLine=trim($testerLine);
                $db_insert=new db();
                $db->query("update orders set tester = '{$testerLine}' where orderID='{$orderID}'");
            }
    }
}
function createNewOrder($orderID,$testDate,$projectName,$applicantName,$businessOwner,$supervisor,$contractor,$sampler,$examinator,$structuralPart,$remark,$productionDate,$testedMaterial,$weather,$materialsUsed,$surfaceTemp,$equipUsed,$materialsName,$paymentCompany,$paymentTax,$paymentAddr,$paymentContactor,$paymentPhone,$paymentFee,$tester){
    if($testDate==""){
        $testDate=Date("Y-m-d");
    }
    
    $today=Date("Y-m-d");
    $db=new db();
    if($productionDate==""){
        $result=$db->insert("insert into orders (orderID,testDate,projectName,applicantName,businessOwner,supervisor,contractor,sampler,examinator,structuralPart,remark,productionDate,testedMaterial,weather,materialsUsed,surfaceTemp,equipUsed,materialsName,paymentCompany,paymentTax,paymentAddr,paymentContactor,paymentPhone,paymentFee,tester,insertDate) values ('$orderID','$testDate','$projectName','$applicantName','$businessOwner','$supervisor','$contractor','$sampler','$examinator','$structuralPart','$remark',null,'$testedMaterial','$weather','$materialsUsed','$surfaceTemp','$equipUsed','$materialsName','$paymentCompany','$paymentTax','$paymentAddr','$paymentContactor','$paymentPhone','$paymentFee','$tester','$today')");
    }else{
    $result=$db->insert("insert into orders (orderID,testDate,projectName,applicantName,businessOwner,supervisor,contractor,sampler,examinator,structuralPart,remark,productionDate,testedMaterial,weather,materialsUsed,surfaceTemp,equipUsed,materialsName,paymentCompany,paymentTax,paymentAddr,paymentContactor,paymentPhone,paymentFee,tester,insertDate) values ('$orderID','$testDate','$projectName','$applicantName','$businessOwner','$supervisor','$contractor','$sampler','$examinator','$structuralPart','$remark','$productionDate','$testedMaterial','$weather','$materialsUsed','$surfaceTemp','$equipUsed','$materialsName','$paymentCompany','$paymentTax','$paymentAddr','$paymentContactor','$paymentPhone','$paymentFee','$tester','$today')");
    }
    return $result;
}
function createSignData($ordersID,$type,$picName){
    $db=new db();
    $result=$db->insert("insert into signs (ordersID,type,picName) values ('$ordersID','$type','$picName')");
    return $result;
}
function createTest($ordersID,$serialNum,$testLocat,$productionDate,$kN,$kgf,$length,$width,$area,$strengthKGF,$strengthMPa,$destructionLocat,$destructionMode){
    if($productionDate==""){
        $productionDate=Date("Y-m-d");
    }
    $db=new db();
    $result=$db->insert("insert into tests (ordersID,serialNum,testLocat,productionDate,kN,kgf,length,width,area,strengthKGF,strengthMPa,destructionLocat,destructionMode) values ('$ordersID','$serialNum','$testLocat','$productionDate','$kN','$kgf','$length','$width','$area','$strengthKGF','$strengthMPa','$destructionLocat','$destructionMode')");
    
    $sql="insert into tests (ordersID,serialNum,testLocat,productionDate,kN,kgf,length,width,area,strengthKGF,strengthMPa,destructionLocat,destructionMode) values ('$ordersID','$serialNum','$testLocat','$productionDate','$kN','$kgf','$length','$width','$area','$strengthKGF','$strengthMPa','$destructionLocat','$destructionMode')";
    $f=fopen("createTest.log","a+");
    fwrite($f,$sql."\r\n");
    fclose($f);
    return $result;
}

function createPullTest($testTypeName, $ordersID, $number, $serialNum, $equipment, $type, $info, $forcePercentage, $formOfForce, $direction, $pressureValue, $designLoadKgf, $loadKgf, $destoryLocation) {
    $db = new db();
    $result = $db->insert("INSERT INTO pull_tests (testTypeName, ordersID, number, serialNum, equipment, type, info, forcePercentage, formOfForce, direction, pressureValue, designLoadKgf, loadKgf, destoryLocation) VALUES ('$testTypeName', '$ordersID', '$number', '$serialNum', '$equipment', '$type', '$info', '$forcePercentage', '$formOfForce', '$direction', '$pressureValue', '$designLoadKgf', '$loadKgf', '$destoryLocation')");
    
    return $result;
}

function deleteOrder($id){
    $db=new db();
    $db->query("delete from orders where id='{$id}'");
}
function getOrderDetail($id){
    $db=new db();
    $db->query("select * from orders where id='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getSigns($id,$type){
    $db=new db();
    $db->query("select * from signs where ordersId='{$id}' and type='{$type}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getOrderTestList($id){
    $db=new db();
    $db->query("select * from tests where ordersId='{$id}' order by serialNum");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getOrderPullTestList($id){
    $db=new db();
    $db->query("select * from pull_tests where ordersID='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getPullTestItem($id){
    $db=new db();
    $db->query("select * from pull_tests where id='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getTestItem($id){
    $db=new db();
    $db->query("select * from tests where id='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function deleteTestItem($id){
    $db=new db();
    $db->query("delete from tests where id='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function deletePullTestItem($id){
    $db=new db();
    $db->query("delete from pull_tests where id='{$id}'");
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getOrderList($startDate,$endDate,$projectName){
    $db=new db();
    if($startDate==""){
        $db->query("select orderID from orders where projectName like '%{$projectName}%' order by id desc");
    }else{
        $db->query("select orderID from orders where testDate>= '{$startDate}' and testDate <= '{$endDate}' and projectName like '%{$projectName}%' order by id desc");
    }
    
    $db=deleteRowByNumber($db);
    return $db->result;
}
function getOrderListAllInfo($startDate,$endDate,$projectName){
    $db=new db();
    if($startDate==""){
        $db->query("select * from orders where projectName like '%{$projectName}%' order by id desc");
    }else{
        if($endDate==""){
            $endDate=$startDate;
        }
        $db->query("select * from orders where testDate>= '{$startDate}' and testDate <= '{$endDate}' and projectName like '%{$projectName}%' order by id desc");
    }
    
    $db=deleteRowByNumber($db);
    return $db->result;
}
function deleteRowByNumber($db){
    for($i=0;$i<$db->num;$i++){
        for($j=0;$j<count($db->result[$i]);$j++){
            unset($db->result[$i][$j]);
        }
    }
    return $db;
}
function base64_to_jpeg($base64_string, $output_file) {
    
    // open the output file for writing
    $f = fopen( $output_file, 'w+' ); 

    // split the string on commas
    // $data[ 0 ] == "data:image/png;base64"
    // $data[ 1 ] == <actual base64 string>
    $data = explode( ',', $base64_string );

    // we could add validation here with ensuring count( $data ) > 1
    fwrite( $f, base64_decode( $data[ 1 ] ) );
    fclose( $f ); 
    $split = explode( ';', $data[0] );
    $split =  explode( '/',$split[0]);
    $fileType=$split[1]; 
    return $output_file; 
}
function getDestructionMode($destructionLocat){
    switch($destructionLocat){
        case "A":
           $result="黏著強度";
           break; 
        case "B":
            $result="黏著強度";
            break; 
        case "C":
            $result="抗拉強度";
            break; 
        case "D":
            $result="抗拉強度";
            break; 
        case "E":
            $result="抗拉強度";
            break; 
    }
    return $result;
}
function updateTest($id, $ordersID, $serialNum, $testLocat, $productionDate, $kN, $kgf, $length, $width, $area, $strengthKGF, $strengthMPa, $destructionLocat, $destructionMode){
    $db=new db();
    $db->query("UPDATE tests SET ordersID='{$ordersID}', serialNum='{$serialNum}', testLocat='{$testLocat}', productionDate='{$productionDate}', kN='{$kN}', kgf='{$kgf}', length='{$length}', width='{$width}', area='{$area}', strengthKGF='{$strengthKGF}', strengthMPa='{$strengthMPa}', destructionLocat='{$destructionLocat}', destructionMode='{$destructionMode}' WHERE id='{$id}'");
}
function updatePullTest($id, $testTypeName, $ordersID, $number, $serialNum, $equipment, $type, $info, $forcePercentage, $formOfForce, $direction, $pressureValue, $designLoadKgf, $loadKgf, $destoryLocation){
    $db=new db();
    $db->query("UPDATE pull_tests SET testTypeName='{$testTypeName}', ordersID='{$ordersID}', number='{$number}', serialNum='{$serialNum}', equipment='{$equipment}', type='{$type}', info='{$info}', forcePercentage='{$forcePercentage}', formOfForce='{$formOfForce}', direction='{$direction}', pressureValue='{$pressureValue}', designLoadKgf='{$designLoadKgf}', loadKgf='{$loadKgf}', destoryLocation='{$destoryLocation}' WHERE id='{$id}'");
    $db->query("UPDATE pull_tests SET testTypeName='{$testTypeName}' WHERE ordersID='{$ordersID}'");
}
//新增設備
function addEquipment($name){
    $db=new db();
    $result=$db->query("insert into equipment (name) values ('{$name}')");
}
//刪除設備
function deleteEquipment($id){
    $db=new db();
    $db->query("delete from equipment where id='{$id}'");
}
//取得列表
function getEquipments(){
    $db=new db();
    $result = $db->query("select * from equipment");
    return $result;
}
function getEquipmentByID($id){
    $db=new db();
    $result = $db->query("select * from equipment where id='{$id}'");
    return $result[0]['name'];
}
function setEquipmentNameByID($id, $newName){
    $db = new db();
    
    $query = "UPDATE equipment SET name = '{$newName}' WHERE id = '{$id}'";
    $db->query($query);
}
function getEquipmentIDFromName($name){
    $db=new db();
    $result = $db->query("select id from equipment where name like '{$name}'");
    return $result[0]['id'];
}
function addEquipmentInfo($equipment_id,$psi,$kgf){
    $db=new db();

    // 首先檢查是否存在重複的psi和kgf值
    $checkQuery = "SELECT * FROM equipment_inter WHERE equipment_id = '{$equipment_id}' AND psi = '{$psi}' AND kgf = '{$kgf}'";
    $checkResult = $db->query($checkQuery);
    // 如果結果集中沒有行，那麼就插入新的值
   // 如果結果集中沒有行，那麼就插入新的值
   if (count($checkResult) == 0) {
        if($psi != '' && $kgf != ''){
        
        $insertQuery = "INSERT INTO equipment_inter (equipment_id, psi, kgf) VALUES ('{$equipment_id}', '{$psi}', '{$kgf}')";
        $db->query($insertQuery);
        }
    }
    
}
function getEquipmentInfo($equipment_id){
    $db=new db();
    $result = $db->query("select * from equipment_inter where equipment_id = '{$equipment_id}' order by psi desc");
    return $result;
}
function deleteEquipmentInfo($id){
    $db=new db();
    $result = $db->query("delete from equipment_inter where id = '{$id}'");
    return $result;
}
function linearInterpolation($psi,$values) {
    for($i=0;$i<count($values);$i++){
        if($psi<$values[$i]['psi'] && $psi>$values[$i+1]['psi']){
            $upperKgf=$values[$i]['kgf'];
            $upperPsi=$values[$i]['psi'];
            $lowerKgf=$values[$i+1]['kgf'];
            $lowerPsi=$values[$i+1]['psi'];
            $kgf = $lowerKgf + ($psi-$lowerPsi) * ($upperKgf - $lowerKgf) / ($upperPsi - $lowerPsi);
            break;
        }
    }
    return round($kgf, 2);
}
function linearInterpolationKgf($kgf,$values) {
    
    for($i=0;$i<count($values);$i++){
        if($kgf<$values[$i]['kgf'] && $kgf>$values[$i+1]['kgf']){
            $upperKgf=$values[$i]['kgf'];
            $upperPsi=$values[$i]['psi'];
            $lowerKgf=$values[$i+1]['kgf'];
            $lowerPsi=$values[$i+1]['psi'];
            $psi = $lowerPsi + ($kgf-$lowerKgf) * ($upperPsi - $lowerPsi) / ($upperKgf - $lowerKgf);
            break;
        }
    }
    return round($psi, 2);
}
?>