<?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("-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 SITEA_C1_BILLRETURN";
    $sql_del = "DELETE scb 
                from SITEA_BILLHEADER  sbh  
                inner join SITEA_C1_BILLRETURN  scb
                on sbh.INV_NO = scb.INV_NO 
                and sbh.BR_CODE=scb.BR_CODE  
                and sbh.DOC_INVTYPE='ของคืน'
                and sbh.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);
    }

    sqlsrv_commit($ConnYS);

    $sql1 = " SELECT 
                    scb.RUNNO
                    ,scb.C1_DOCCODE
                    ,scb.INV_NO
                    ,char_length(scb.INV_NO) as L_INVNO
                    ,scb.BR_CODE
                    ,scb.TRANS_INCOME
                    ,scb.TRANS_PAY
                    from SITEA_BILLHEADER  sbh  
                    inner join SITEA_C1_BILLRETURN  scb
                    on sbh.INV_NO = scb.INV_NO 
                    and sbh.BR_CODE=scb.BR_CODE  
                    and sbh.DOC_INVTYPE='ของคืน'
                    and sbh.doc_date  
                between '$s_trime' and '$current_time' "; 
            // where doc_date between '20.05.2563' and '20.06.2564' 

    $rst1 = ibase_query($connFB, $sql1);

    $x = 0;
    while($arr1  = ibase_fetch_object($rst1)) {

        $data[] = array( 
                        "RUNNO"              => $arr1->RUNNO
                        ,"C1_DOCCODE"         => $arr1->C1_DOCCODE
                        ,"INV_NO"             => $arr1->INV_NO
                        ,"BR_CODE"            => $arr1->BR_CODE
                        ,"TRANS_INCOME"       => $arr1->TRANS_INCOME
                        ,"TRANS_PAY"          => $arr1->TRANS_PAY
                        ,"L_INVNO"            => $arr1->L_INVNO
                    );
 
    $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++){

                $RUNNO              = $data[$i]["RUNNO"];
                $C1_DOCCODE         = $data[$i]["C1_DOCCODE"];
                $INV_NO             = $data[$i]["INV_NO"];
                $BR_CODE            = $data[$i]["BR_CODE"];
                $TRANS_INCOME       = $data[$i]["TRANS_INCOME"];
                $TRANS_PAY          = $data[$i]["TRANS_PAY"];
                $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 );
                }

                $sql_plan = "INSERT into  SITEA_C1_BILLRETURN (RUNNO ,C1_DOCCODE ,INV_NO, BR_CODE,TRANS_INCOME ,TRANS_PAY )  
                                        values ('".$RUNNO."', '".$C1_DOCCODE."', '".$INV_NO."', '".$BR_CODE."', '".$TRANS_INCOME."', '".$TRANS_PAY."') "; 

                $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_BILLRETURN: ".$i,$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_BILLRETURN";
            INSERT_SYNLOG($connLog,"ไม่บันทึกดาต้าเบส",$TABLE,"FAIL");
        } 
        sqlsrv_close($ConnYS);
    }
    
    // echo "Success".$x;
    // echo "14";

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

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

 ?>
	







