

<?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'); 

$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);
$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"; 
?>


<?php

$sql_del = "delete from HR_EM_HEADER";
    $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
            EM_ID
            ,EM_CODE
            ,CONTRACT_CODE
            ,CONTRACT_DATE
            ,CONTRACT_TYPE
            ,POSITION_ID
            ,BRANCH_ID
            ,SEX
            ,FIRSTNAME
            ,LASTNAME
            ,NICKNAME
            ,BIRTHDAY
            ,AGE
            ,NATIONALITY
            ,RACE
            ,RELIGION
            ,CARD_NO
            ,CARD_BY
            ,CARD_PROVINCE
            ,CARD_DATE
            ,CARD_EXPIRE
            ,ADDRESS1
            ,DISTRICT1
            ,PROVINCE1
            ,ZIPCODE1
            ,COUNTRY1
            ,TEL1
            ,ADDRESS2
            ,DISTRICT2
            ,PROVINCE2
            ,ZIPCODE2
            ,COUNTRY2
            ,IMEI
            ,DETAILS
            ,EM_STATUS
            ,IMAGE_PATH
            ,MASTER_COURSE
            ,VEHICLE_PLATE
            ,OWNER_FIRSTNAME
            ,OWNER_LASTNAME
            ,PAYMENT_METHOD
            ,CARD_IMAGE_PART
            ,LICENSE_IMAGE_PART
            ,LOGIN_USERNAME
            ,LOGIN_PASSWORD
            ,LICENSE_CARD_NO
            ,LICENSE_CARD_DATE
            ,LICENSE_CARD_EXPIRE
            ,LOG_INSERT
            ,LOG_UPDATE
            from  HR_EM_HEADER "; 
        // 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_assoc($rst1)) {
   
    $EM_ID                      = $arr1["EM_ID"];
    $EM_CODE                    = $arr1["EM_CODE"];
    $CONTRACT_CODE              = $arr1["CONTRACT_CODE"];
    $CONTRACT_DATE              = $arr1["CONTRACT_DATE"];
    $CONTRACT_TYPE              = $arr1["CONTRACT_TYPE"];
    $POSITION_ID                = $arr1["POSITION_ID"];
    $BRANCH_ID                  = $arr1["BRANCH_ID"];
    $SEX                        = $arr1["SEX"];
    $FIRSTNAME                  = $arr1["FIRSTNAME"];
    $LASTNAME                   = $arr1["LASTNAME"];
    $NICKNAME                   = $arr1["NICKNAME"];
    $BIRTHDAY                   = $arr1["BIRTHDAY"];
    $AGE                        = $arr1["AGE"];
    $NATIONALITY                = $arr1["NATIONALITY"];
    $RACE                       = $arr1["RACE"];
    $RELIGION                   = $arr1["RELIGION"];
    $CARD_NO                    = $arr1["CARD_NO"];
    $CARD_BY                    = $arr1["CARD_BY"];
    $CARD_PROVINCE              = $arr1["CARD_PROVINCE"];
    $CARD_DATE                  = $arr1["CARD_DATE"];
    $CARD_EXPIRE                = $arr1["CARD_EXPIRE"];
    $ADDRESS1                   = $arr1["ADDRESS1"];
    $DISTRICT1                  = $arr1["DISTRICT1"];
    $PROVINCE1                  = $arr1["PROVINCE1"];
    $ZIPCODE1                   = $arr1["ZIPCODE1"];
    $COUNTRY1                   = $arr1["COUNTRY1"];
    $TEL1                       = $arr1["TEL1"];
    $ADDRESS2                   = $arr1["ADDRESS2"];
    $DISTRICT2                  = $arr1["DISTRICT2"];
    $PROVINCE2                  = $arr1["PROVINCE2"];
    $ZIPCODE2                   = $arr1["ZIPCODE2"];
    $COUNTRY2                   = $arr1["COUNTRY2"];
    $IMEI                       = $arr1["IMEI"];
    $DETAILS                    = $arr1["DETAILS"];
    $EM_STATUS                  = $arr1["EM_STATUS"];
    $IMAGE_PATH                 = $arr1["IMAGE_PATH"];
    $MASTER_COURSE              = $arr1["MASTER_COURSE"];
    $VEHICLE_PLATE              = $arr1["VEHICLE_PLATE"];
    $OWNER_FIRSTNAME            = $arr1["OWNER_FIRSTNAME"];
    $OWNER_LASTNAME             = $arr1["OWNER_LASTNAME"];
    $PAYMENT_METHOD             = $arr1["PAYMENT_METHOD"];
    $CARD_IMAGE_PART            = $arr1["CARD_IMAGE_PART"];
    $LICENSE_IMAGE_PART         = $arr1["LICENSE_IMAGE_PART"];
    $LOGIN_USERNAME             = $arr1["LOGIN_USERNAME"];
    $LOGIN_PASSWORD             = $arr1["LOGIN_PASSWORD"];
    $LICENSE_CARD_NO            = $arr1["LICENSE_CARD_NO"];
    $LICENSE_CARD_DATE          = $arr1["LICENSE_CARD_DATE"];
    $LICENSE_CARD_EXPIRE        = $arr1["LICENSE_CARD_EXPIRE"];
    $LOG_INSERT                 = $arr1["LOG_INSERT"];
    $LOG_UPDATE                 = $arr1["LOG_UPDATE"];

  if($CONTRACT_DATE!=""){
    $CONTRACT_DATE = CONVERT_DATE543($CONTRACT_DATE);
    $CONTRACT_DATE = " '".$CONTRACT_DATE."' ";
  }else{
    $CONTRACT_DATE ="NULL";
  }
  if($BIRTHDAY!=""){
     $BIRTHDAY = CONVERT_DATE543($BIRTHDAY);
     $BIRTHDAY = " '".$BIRTHDAY."' ";
    }else{
      $BIRTHDAY ="NULL";
    }
  if($CARD_DATE!=""){
    $CARD_DATE = CONVERT_DATE543($CARD_DATE);
    $CARD_DATE = " '".$CARD_DATE."' ";
  }else{
    $CARD_DATE ="NULL";
  }
 if($CARD_EXPIRE!=""){
    $CARD_EXPIRE = CONVERT_DATE543($CARD_EXPIRE);
    $CARD_EXPIRE = " '".$CARD_EXPIRE."' ";
  }else{
    $CARD_EXPIRE ="NULL";
  }
 if($LICENSE_CARD_DATE!=""){
    $LICENSE_CARD_DATE = CONVERT_DATE543($LICENSE_CARD_DATE);
    $LICENSE_CARD_DATE = " '".$LICENSE_CARD_DATE."' ";
  }else{
    $LICENSE_CARD_DATE ="NULL";
  }
 if($LICENSE_CARD_EXPIRE!=""){
    $LICENSE_CARD_EXPIRE = CONVERT_DATE543($LICENSE_CARD_EXPIRE);
    $LICENSE_CARD_EXPIRE = " '".$LICENSE_CARD_EXPIRE."' ";
  }else{
    $LICENSE_CARD_EXPIRE ="NULL";
  }


        if ($ConnYS) {
            sqlsrv_begin_transaction($ConnYS);
                try {

                     $sql_brname = "INSERT into  HR_EM_HEADER (EM_ID ,EM_CODE ,CONTRACT_CODE ,CONTRACT_DATE ,CONTRACT_TYPE ,POSITION_ID  ,BRANCH_ID  ,SEX  ,FIRSTNAME  ,LASTNAME  ,NICKNAME  ,BIRTHDAY  ,AGE  ,NATIONALITY  
                                                                ,RACE  ,RELIGION  ,CARD_NO  ,CARD_BY, CARD_DATE, CARD_EXPIRE, ADDRESS1, DISTRICT1, PROVINCE1, ZIPCODE1, COUNTRY1, TEL1, ADDRESS2
                                                                ,DISTRICT2, PROVINCE2, ZIPCODE2, COUNTRY2, IMEI, DETAILS, EM_STATUS, IMAGE_PATH, MASTER_COURSE, VEHICLE_PLATE, OWNER_FIRSTNAME
                                                                ,OWNER_LASTNAME, PAYMENT_METHOD, CARD_IMAGE_PART, LICENSE_IMAGE_PART, LOGIN_USERNAME, LOGIN_PASSWORD, LICENSE_CARD_NO, LICENSE_CARD_DATE
                                                                ,LICENSE_CARD_EXPIRE, LOG_INSERT, LOG_UPDATE)  
                                         values ('".$EM_ID."', '".$EM_CODE."', '".$CONTRACT_CODE."', $CONTRACT_DATE, '".$CONTRACT_TYPE."', '".$POSITION_ID."', '".$BRANCH_ID."', '".$SEX."', '".$FIRSTNAME."', '".$LASTNAME."'
                                                , '".$NICKNAME."', $BIRTHDAY, '".$AGE."', '".$NATIONALITY."', '".$RACE."', '".$RELIGION."', '".$CARD_NO."', '".$CARD_BY."', $CARD_DATE
                                                ,   $CARD_EXPIRE, '".$ADDRESS1."', '".$DISTRICT1."', '".$PROVINCE1."', '".$ZIPCODE1."', '".$COUNTRY1."', '".$TEL1."', '".$ADDRESS2."', '".$DISTRICT2."', '".$PROVINCE2."'
                                                , '".$ZIPCODE2."', '".$COUNTRY2."', '".$IMEI."', '".$DETAILS."', '".$EM_STATUS."', '".$IMAGE_PATH."', '".$MASTER_COURSE."', '".$VEHICLE_PLATE."', '".$OWNER_FIRSTNAME."'
                                                , '".$OWNER_LASTNAME."', '".$PAYMENT_METHOD."', '".$CARD_IMAGE_PART."', '".$LICENSE_IMAGE_PART."', '".$LOGIN_USERNAME."', '".$LOGIN_PASSWORD."', '".$LICENSE_CARD_NO."', $LICENSE_CARD_DATE
                                                ,   $LICENSE_CARD_EXPIRE, '".$LOG_INSERT."', '".$LOG_UPDATE."') "; 
                            // echo $sql_brname."<br>".$x;
                        $stm_plan = sqlsrv_prepare ($ConnYS, $sql_brname);
                        if (!$stm_plan) {
                            throw new Exception("sql_brname  plan#". $sql_brname); 
                        }
                        if (!sqlsrv_execute($stm_plan)) {
                            throw new Exception("sql_brname นำเข้า plan ไม่สำเหร็จ#" . $sql_brname);
                        }
       
                        // sqlsrv_rollback($ConnYS);

                sqlsrv_commit($ConnYS);
                // echo "Success";
            } catch (Exception $ex) {
                sqlsrv_rollback($ConnYS);
                 sqlsrv_close($ConnYS);
                echo $ex."ไม่บันทึกดาต้าเบส";
            }
        }
        $x++;
    }


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

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

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 CONVERT_DATE($VAL_DATE){
  $key_vol = explode("-", $VAL_DATE);
      $strY = trim($key_vol[0]);
      $strM = trim($key_vol[1]);
      $strD = trim($key_vol[2]);
  $resulte_date = $strY.'-'.$strM.'-'.$strD;   
return $resulte_date;
}

function write_text($HR_EM_HEADER,$current_time,$htime){
    $t_string = "$HR_EM_HEADER".'  '."$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);
// }
                
 ?>
	







