

<?php 
ini_set('max_execution_time', 0); //300 seconds = 5 minutes 
date_default_timezone_set("Asia/Bangkok");
// echo $_SESSION['company_id'];
include('../include/connectDB.php'); 
include('../include/connectDB_Bill.php'); 
include('INSERT_LOG.php');

$currentdate = date('Y-m-d');

$yearY = date("Y"); 
$yearM = date("m"); 
$yearD = date("d");
$htime = date("H:i:sa");
$current_time = $yearD.'.'.$yearM.'.'.($yearY+543);
$s_trime = $yearY.$yearM.$yearD;
$DateStart=date($s_trime);
$strtime = strtotime($DateStart);
$caltime=strtotime("-3 Month",$strtime);
// $caltime=strtotime("-30 day",$strtime);
$ResultDate=date("Y-m-d", $caltime);
$yearY = substr(date("$ResultDate"), 0,-6); 
$yearM = substr(date("$ResultDate"), 5,-3); 
$yearD = substr(date("$ResultDate"), -2); 
$s_trime = $yearD.'.'.$yearM.'.'.($yearY+543);  
// // $s_trime = "20.05.2564"; 

// //----sql
$st_sql_tdate = date('Y-m-d');
$st_sql_DateStart=date($st_sql_tdate);
$st_sql_strtime = strtotime($st_sql_DateStart);
$st_sql_caltime=strtotime("-3 Month",$st_sql_strtime);
// $st_sql_caltime=strtotime("-30 day",$st_sql_strtime);
$st_sql_ResultDate=date("Y-m-d", $st_sql_caltime);
$st_sql_ResultDate;
$en_sql_ResultDate = $st_sql_tdate; 
// //----sql

// //DELETE//----sql ใช้กับที่ดึงวันที่จาก DOC_CODE
// $YM1 = date("Y")+543;
// $en1_del_tdate = date($YM1.'-m-d');
// $st_del_tdate = date('Y-m-d');
// $st_del_DateStart=date($st_del_tdate);
// $st_del_strtime = strtotime($st_del_DateStart);
// $st_sql_caltime=strtotime("-2 Month",$st_del_strtime);
// // $st_sql_caltime=strtotime("-30 day",$st_del_strtime);
// $st_del_ResultDate=date("Y-m-d", $st_sql_caltime);
// $key_del = explode("-", $st_del_ResultDate);
// $str_del = (($key_del[0])+543).($key_del[1]).($key_del[2]);
// //----sql
// // str_replace("-","",$st_del_tdate);
// $start_del = substr($str_del,2,4);
// $end_del = substr(str_replace("-","",$en1_del_tdate),2,4);
?>



<?php

// $sql_del = "delete from SITEA_C1_HEADER";
// $sql_del = "DELETE from SITEA_C1_HEADER where SUBSTRING(DOC_CODE, LEN(BR_CODE)+1, 4) between '".$start_del."' and '".$end_del."'  ";
$sql_del = "delete from SITEA_C1_HEADER where doc_date between '$st_sql_ResultDate' and '$en_sql_ResultDate' ";
$stm_del = sqlsrv_prepare ($ConnYS, $sql_del);
if (!$stm_del) {
    throw new Exception("sql_del  plan#". $sql_del); 
}
if (!sqlsrv_execute($stm_del)) {
    throw new Exception("sql_del นำเข้า plan ไม่สำเหร็จ#" . $sql_del);
}

$sql1 = " SELECT
            C1ID
            ,BR_CODE
            ,DOC_CODE
            ,DOC_DATE
            ,DOC_TIME
            ,LOAD_PLACE
            ,LOAD_TRIPTYPE
            ,LOAD_TRANSTYPE
            ,DOC_STATUS
            ,REMARK
            ,DELIVERY_STATE
            ,END_DELIVERY_DATE
            ,EX_READY
            ,SUM_QTY
            ,SUM_WEIGHT
            ,SUM_CAPACITY
            ,SCAN_DOC_STATUS
            ,HUB_BR_CODE
            ,DELIVERY_TYPE
        from sitea_c1_header where doc_date between '$s_trime' and '$current_time' ";
        // where substring(DOC_CODE from char_length(BR_CODE)+1 for 4) between '".$start_del."' and '".$end_del."' 
        // where doc_date between '20.05.2563' and '20.06.2564' 
    // echo $sql1;
    $rst1 = ibase_query($connFB, $sql1);
    // die("Stop");
    $x = 0;
    while($arr1         = ibase_fetch_object($rst1)) {

        $data[] = array( 
                     "C1ID"                       => $arr1->C1ID
                    ,"BR_CODE"                    => $arr1->BR_CODE
                    ,"DOC_CODE"                   => $arr1->DOC_CODE
                    ,"DOC_DATE"                   => $arr1->DOC_DATE
                    ,"DOC_TIME"                   => $arr1->DOC_TIME
                    ,"LOAD_PLACE"                 => $arr1->LOAD_PLACE
                    ,"LOAD_TRIPTYPE"              => $arr1->LOAD_TRIPTYPE
                    ,"LOAD_TRANSTYPE"             => $arr1->LOAD_TRANSTYPE
                    ,"DOC_STATUS"                 => $arr1->DOC_STATUS
                    ,"REMARK"                     => $arr1->REMARK
                    ,"DELIVERY_STATE"             => $arr1->DELIVERY_STATE
                    ,"END_DELIVERY_DATE"          => $arr1->END_DELIVERY_DATE
                    ,"EX_READY"                   => $arr1->EX_READY
                    ,"SUM_QTY"                    => $arr1->SUM_QTY
                    ,"SUM_WEIGHT"                 => $arr1->SUM_WEIGHT
                    ,"SUM_CAPACITY"               => $arr1->SUM_CAPACITY
                    ,"SCAN_DOC_STATUS"            => $arr1->SCAN_DOC_STATUS
                    ,"HUB_BR_CODE"                => $arr1->HUB_BR_CODE
                    ,"DELIVERY_TYPE"              => $arr1->DELIVERY_TYPE
                );
    $x++;

}

    echo insert_sqlServer($ConnYS,$data,$current_time,$htime,$connLog);

    function  insert_sqlServer($ConnYS,$data,$current_time,$htime,$connLog){

        sqlsrv_begin_transaction($ConnYS);
        try {

            for($i=0;$i<sizeof($data);$i++){
                
                $C1ID                       = $data[$i]["C1ID"];
                $BR_CODE                    = $data[$i]["BR_CODE"];
                $DOC_CODE                   = $data[$i]["DOC_CODE"];
                $DOC_DATE                   = $data[$i]["DOC_DATE"];
                $DOC_TIME                   = $data[$i]["DOC_TIME"];
                $LOAD_PLACE                 = $data[$i]["LOAD_PLACE"];
                $LOAD_TRIPTYPE              = $data[$i]["LOAD_TRIPTYPE"];
                $LOAD_TRANSTYPE             = $data[$i]["LOAD_TRANSTYPE"];
                $DOC_STATUS                 = $data[$i]["DOC_STATUS"];
                $REMARK                     = $data[$i]["REMARK"];
                $DELIVERY_STATE             = $data[$i]["DELIVERY_STATE"];
                $END_DELIVERY_DATE          = $data[$i]["END_DELIVERY_DATE"];
                $EX_READY                   = $data[$i]["EX_READY"];
                $SUM_QTY                    = $data[$i]["SUM_QTY"];
                $SUM_WEIGHT                 = $data[$i]["SUM_WEIGHT"];
                $SUM_CAPACITY               = $data[$i]["SUM_CAPACITY"];
                $SCAN_DOC_STATUS            = $data[$i]["SCAN_DOC_STATUS"];
                $HUB_BR_CODE                = $data[$i]["HUB_BR_CODE"];
                $DELIVERY_TYPE              = $data[$i]["DELIVERY_TYPE"];

                if($DOC_DATE!=""){
                    $DOC_DATE = CONVERT_DATE543($DOC_DATE);
                    $DOC_DATE = " '".$DOC_DATE."' ";
                }else{
                $DOC_DATE ="NULL";
                }
                
                if($END_DELIVERY_DATE!=""){
                    $END_DELIVERY_DATE = END_DELIVERY_DATE($END_DELIVERY_DATE);
                    $END_DELIVERY_DATE = " '".$END_DELIVERY_DATE."' ";
                }else{
                $END_DELIVERY_DATE ="NULL";
                }

               
                $sql_plan = "INSERT into  SITEA_C1_HEADER (C1ID ,BR_CODE ,DOC_CODE ,DOC_DATE ,DOC_TIME ,LOAD_PLACE ,LOAD_TRIPTYPE ,LOAD_TRANSTYPE ,DOC_STATUS 
                                ,REMARK ,DELIVERY_STATE ,END_DELIVERY_DATE ,EX_READY ,SUM_QTY ,SUM_WEIGHT ,SUM_CAPACITY ,SCAN_DOC_STATUS ,HUB_BR_CODE ,DELIVERY_TYPE)  
                    values ('".$C1ID."', '".$BR_CODE."', '".$DOC_CODE."', $DOC_DATE, '".$DOC_TIME."', '".$LOAD_PLACE."', '".$LOAD_TRIPTYPE."'
                            , '".$LOAD_TRANSTYPE."',  '".$DOC_STATUS."', '".$REMARK."', '".$DELIVERY_STATE."', $END_DELIVERY_DATE, '".$EX_READY."'
                            , '".$SUM_QTY."', '".$SUM_WEIGHT."', '".$SUM_CAPACITY."', '".$SCAN_DOC_STATUS."', '".$HUB_BR_CODE."', '".$DELIVERY_TYPE."') "; 
                            // echo $sql_plan."<br>".$x;
                $stm_plan = sqlsrv_prepare ($ConnYS, $sql_plan);
                if (!$stm_plan) {
                    throw new Exception("sql_plan  plan#". $sql_plan); 
                }
                if (!sqlsrv_execute($stm_plan)) {
                    throw new Exception("sql_plan นำเข้า plan ไม่สำเหร็จ#" . $sql_plan);
                }
            }

            sqlsrv_commit($ConnYS);
            // sqlsrv_rollback($ConnYS);
            $result = array("STATUS" => "1", "RESULT" => 'Seuccess :'.$i);
            write_text("SITEA_C1_HEADER",$current_time,$htime);
            return json_encode($result,JSON_UNESCAPED_UNICODE);
        } catch (Exception $ex) {
            $result = "";
            sqlsrv_rollback($ConnYS);
            sqlsrv_close($ConnYS);
            $result = array("STATUS" => "0", "RESULT" => explode('#', $ex->getMessage())); 
            write_text($ex->getMessage(),$current_time,$htime);
            return json_encode($result,JSON_UNESCAPED_UNICODE);
            $TABLE = "SITEA_C1_HEADER";
            INSERT_SYNLOG($connLog,"ไม่บันทึกดาต้าเบส",$TABLE,"FAIL");
        } 
        sqlsrv_close($ConnYS);
    }


// echo "Success".$x;
// echo "2";

// write_text("SITEA_C1_HEADER",$current_time,$htime);

function CONVERT_DATE543($VAL_DATE){
    $key_vol = explode("-", $VAL_DATE);
    $strY = trim($key_vol[0]);
    $strM = trim($key_vol[1]);
    $strD = trim($key_vol[2]);
    $sub_y = substr($strY,0,2);
    if($sub_y=="25"){
        $resulte_date = ($strY-543).'-'.$strM.'-'.$strD;
     }else{
        $resulte_date = ($strY).'-'.$strM.'-'.$strD;
     }
return $resulte_date;
}

function END_DELIVERY_DATE($END_DELIVERY_DATE){
    $key_str = explode(" ", $END_DELIVERY_DATE);
    $sub_str0 = trim($key_str[0]);
    //----
    $key_vol = explode("-", $sub_str0);
    $strY = trim($key_vol[0]);
    $strM = trim($key_vol[1]);
    $strD = trim($key_vol[2]);
    $resulte_daliverydate = $strY.'-'.$strM.'-'.$strD.' ' .trim($key_str[1]);   
return $resulte_daliverydate;
}

function write_text($SITEA_C1_HEADER,$current_time,$htime){
$t_string = "$SITEA_C1_HEADER".'  '."$current_time".' '."$htime";
  $strFileName = "LogSynDB.txt";
  $objFopen = fopen($strFileName, 'a');
  $strText1 = "$t_string\r\n";
  fwrite($objFopen, $strText1);
}
                
 ?>
	







