<?php 
    header('Content-Type: application/json');
    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("-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

    // $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 sbc
                    from SITEA_BILLRETURN_CUST  sbc
                    inner join SITEA_BILLRETURN_DRIVER   sbd
                    on sbc.inv_no=sbd.inv_no
                    and sbc.br_code=sbd.br_code
                where sbd.bill_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 distinct
                    sbc.BR_CODE
                    ,sbc.INV_NO
                    ,sbc.RETURN_DATE
                    ,sbc.RETURN_TIME
                    ,sbc.CREATE_BY
                    ,sbc.PERSON_RETURN_ID
                    from SITEA_BILLRETURN_CUST  sbc
                    inner join SITEA_BILLRETURN_DRIVER   sbd on sbc.inv_no=sbd.inv_no and sbc.br_code=sbd.br_code
                    where sbd.bill_date  between '$s_trime' and '$current_time' and  UPPER(sbc.INV_NO) !='SAMPLE' order by  sbd.RETURN_DATE DESC  ";
                    // where sbd.bill_date  between '$s_trime' and '$current_time'  order by  sbd.RETURN_DATE DESC  ";

                    // where scv.c1_doccode in  (SELECT sch.DOC_CODE from  sitea_c1_header sch where  sch.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' 
 

    $rst1 = ibase_query($connFB, $sql1);
    // die("Stop");
    $x = 0;
    while($arr1   = ibase_fetch_object($rst1)) {
        $data[] = array( 
                        "BR_CODE"            => $arr1->BR_CODE
                        ,"INV_NO"             => $arr1->INV_NO
                        ,"RETURN_DATE"        => $arr1->RETURN_DATE
                        ,"RETURN_TIME"        => $arr1->RETURN_TIME
                        ,"CREATE_BY"          => $arr1->CREATE_BY
                        ,"PERSON_RETURN_ID"   => $arr1->PERSON_RETURN_ID
                    );
        $x++;
    }
// echo "Success".$x;
// INSERT_SYNLOG($connLog,"","","SUCCESS");
// echo "19";

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

                $BR_CODE            = $data[$i]["BR_CODE"];
                $INV_NO             = $data[$i]["INV_NO"];
                $RETURN_DATE        = $data[$i]["RETURN_DATE"];
                $RETURN_TIME        = $data[$i]["RETURN_TIME"];
                $CREATE_BY          = $data[$i]["CREATE_BY"];
                $PERSON_RETURN_ID   = $data[$i]["PERSON_RETURN_ID"];

                if($RETURN_DATE!=""){
                    $RETURN_DATE = CONVERT_DATE543($RETURN_DATE);
                    $RETURN_DATE = " '".$RETURN_DATE."' ";
                }else{
                    $RETURN_DATE ="NULL";
                }
                
                $sql_plan = "INSERT into  SITEA_BILLRETURN_CUST (BR_CODE ,INV_NO ,RETURN_DATE ,RETURN_TIME ,CREATE_BY ,PERSON_RETURN_ID )  
                                    values ('".$BR_CODE."', '".$INV_NO."', $RETURN_DATE, '".$RETURN_TIME."', '".$CREATE_BY."', '".$PERSON_RETURN_ID."') "; 
                $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_BILLRETURN_CUST",$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_BILLRETURN_CUST";
            INSERT_SYNLOG($connLog,"ไม่บันทึกดาต้าเบส",$TABLE,"FAIL");
        }
        sqlsrv_close($ConnYS); 
    }

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;
  }

// write_text("SITEA_BILLRETURN_CUST",$current_time,$htime);
// write_text2("....................................");

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

// function write_text2($text){
//     $t_string = "$text";
//       $strFileName = "LogSynDB.txt";
//       $objFopen = fopen($strFileName, 'a');
//       $strText1 = "$t_string\r\n";
//     fwrite($objFopen, $strText1);
//   }
                
 ?>
	







