

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

if( $ConnYS === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$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("-2 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("-2 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
?>


<?php

    // $sql_del = "delete from POD_CF_DETAILS";
    $sql_del = "DELETE from POD_CF_DETAILS where CF_DATE between '".$st_sql_ResultDate."' and '".$en_sql_ResultDate."'";
    $stm_del = sqlsrv_prepare ($ConnYS, $sql_del);
        if (!$stm_del) {
            throw new Exception("sql_del  POD_CF_DETAILS#". $sql_del); 
        }
        if (!sqlsrv_execute($stm_del)) {
            throw new Exception("sql_del POD_CF_DETAILS ไม่สำเหร็จ#" . $sql_del);
        }

    sqlsrv_commit($ConnYS);

    $sql1 = " SELECT
                PODID
                ,C1_DOCCODE
                ,CF_DATE
                ,CF_TIME
                ,VHC_CODE
                ,CF_BY
                ,CF_TYPENAME
                ,CF_REASONNAME
                ,LAT
                ,LNG
                ,INV_NO
                ,char_length(INV_NO) as L_INVNO
                ,BR_CODE
                ,CF_REMARK
                ,DISTANCE_TO
                ,CONFIRM_SUBDISTRICT
                ,CONFIRM_DISTRICT
                ,CONFIRM_PROVINCE
                , SHIPMENT_TIME
                from  POD_CF_DETAILS
                where   CF_DATE  between '$s_trime' and '$current_time' "; 
        // where doc_date between '20.05.2563' and '20.06.2564' 
        // echo $sql1;
        $x=0;
        $rst1 = ibase_query($connFB, $sql1);
        while($arr1         = ibase_fetch_object($rst1)) {
        $data[] = array( 
                        "PODID"                      => $arr1->PODID
                        ,"C1_DOCCODE"                 => $arr1->C1_DOCCODE
                        ,"CF_DATE"                    => $arr1->CF_DATE
                        ,"CF_TIME"                    => $arr1->CF_TIME
                        ,"VHC_CODE"                   => $arr1->VHC_CODE
                        ,"CF_BY"                      => $arr1->CF_BY
                        ,"CF_TYPENAME"                => $arr1->CF_TYPENAME
                        ,"CF_REASONNAME"              => $arr1->CF_REASONNAME
                        ,"LAT"                        => $arr1->LAT
                        ,"LNG"                        => $arr1->LNG
                        ,"INV_NO"                     => $arr1->INV_NO
                        ,"BR_CODE"                    => $arr1->BR_CODE
                        ,"CF_REMARK"                  => $arr1->CF_REMARK
                        ,"DISTANCE_TO"                => $arr1->DISTANCE_TO
                        ,"CONFIRM_SUBDISTRICT"        => $arr1->CONFIRM_SUBDISTRICT
                        ,"CONFIRM_DISTRICT"           => $arr1->CONFIRM_DISTRICT
                        ,"CONFIRM_PROVINCE"           => $arr1->CONFIRM_PROVINCE
                        ,"SHIPMENT_TIME"              => $arr1->SHIPMENT_TIME
                        ,"L_INVNO"                    => $arr1->L_INVNO
                );
        $x++;
    }


// echo "Success".$x;
// echo "5";
// write_text("POD_CF_DETAILS",$current_time,$htime);

    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++){

                $PODID                      = $data[$i]["PODID"];
                $C1_DOCCODE                 = $data[$i]["C1_DOCCODE"];
                $CF_DATE                    = $data[$i]["CF_DATE"];
                $CF_TIME                    = $data[$i]["CF_TIME"];
                $VHC_CODE                   = $data[$i]["VHC_CODE"];
                $CF_BY                      = $data[$i]["CF_BY"];
                $CF_TYPENAME                = $data[$i]["CF_TYPENAME"];
                $CF_REASONNAME              = $data[$i]["CF_REASONNAME"];
                $LAT                        = $data[$i]["LAT"];
                $LNG                        = $data[$i]["LNG"];
                $INV_NO                     = $data[$i]["INV_NO"];
                $BR_CODE                    = $data[$i]["BR_CODE"];
                $CF_REMARK                  = $data[$i]["CF_REMARK"];
                $DISTANCE_TO                = $data[$i]["DISTANCE_TO"];
                $CONFIRM_SUBDISTRICT        = $data[$i]["CONFIRM_SUBDISTRICT"];
                $CONFIRM_DISTRICT           = $data[$i]["CONFIRM_DISTRICT"];
                $CONFIRM_PROVINCE           = $data[$i]["CONFIRM_PROVINCE"];
                $SHIPMENT_TIME              = $data[$i]["SHIPMENT_TIME"];
                $L_INVNO                    = $data[$i]["L_INVNO"];

                if($L_INVNO <=30){
                    $INV_NO = $INV_NO;
                }else{
                    $INV_NO  = substr($INV_NO,1,30);  
                    $INV_NO =  htmlspecialchars( $INV_NO );
                }
                // $CF_TIME = CF_TIME($CF_TIME); 

                if($CF_DATE !=""){
                    $CF_DATE = CF_DATE($CF_DATE);
                }else{
                    $CF_DATE = $CF_DATE;
                }

                $sql_cf = "INSERT into  POD_CF_DETAILS (PODID ,C1_DOCCODE ,CF_DATE ,CF_TIME ,VHC_CODE ,CF_BY  ,CF_TYPENAME  ,CF_REASONNAME  ,LAT  ,LNG  ,INV_NO  ,BR_CODE  ,CF_REMARK  ,DISTANCE_TO  ,CONFIRM_SUBDISTRICT  ,CONFIRM_DISTRICT  ,CONFIRM_PROVINCE  ,SHIPMENT_TIME)  
                            values ('".$PODID."', '".$C1_DOCCODE."', '".$CF_DATE."', '".$CF_TIME."', '".$VHC_CODE."', '".$CF_BY."', '".$CF_TYPENAME."', '".$CF_REASONNAME."', '".$LAT."', '".$LNG."'
                                , '".$INV_NO."', '".$BR_CODE."', '".$CF_REMARK."', '".$DISTANCE_TO."', '".$CONFIRM_SUBDISTRICT."', '".$CONFIRM_DISTRICT."', '".$CONFIRM_PROVINCE."', '".$SHIPMENT_TIME."') "; 
                            // echo $sql_cf."<br>".$x;
                        $stm_cf = sqlsrv_prepare ($ConnYS, $sql_cf);
                        if (!$stm_cf) {
                            throw new Exception("sql_cf  POD_CF_DETAILS#". $sql_cf); 
                        }
                        if (!sqlsrv_execute($stm_cf)) {
                            throw new Exception("sql_cf POD_CF_DETAILS ไม่สำเหร็จ#" . $sql_cf);
                        }
            }

            sqlsrv_commit($ConnYS);
            // sqlsrv_rollback($ConnYS);
            $result = array("STATUS" => "1", "RESULT" => 'Seuccess :'.$i);
            write_text("POD_CF_DETAILS",$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 = "POD_CF_DETAILS";
            INSERT_SYNLOG($connLog,"ไม่บันทึกดาต้าเบส",$TABLE,"FAIL");
        }
        sqlsrv_close($ConnYS); 
    }

    function CF_DATE($CF_DATE){
        $key_vol = explode("-", $CF_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 write_text($POD_CF_DETAILS,$current_time,$htime){
        $t_string = "$POD_CF_DETAILS".'  '."$current_time".' '."$htime";
        $strFileName = "LogSynDB.txt";
        $objFopen = fopen($strFileName, 'a');
        $strText1 = "$t_string\r\n";
        fwrite($objFopen, $strText1);
    }
                
 ?>
	







