<?php

$serverName = "JENYA100\SQLEXPRESS";
$uid = "sa";
$pwd = "sss";
$databaseName = "SSSDB";

$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>$databaseName);

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
echo "<pre>";
$currentdate = date('Y-m-d')." 00:00:00.000";
$tsql = "SELECT [CARDNO]
,[OFFICEPUNCH]
,[P_DAY]
,[ISMANUAL]
,[ReasonCode]
,[MC_NO]
,[INOUT]
,[PAYCODE]
FROM [SSSDB].[dbo].[MachineRawPunch] where OFFICEPUNCH >= '".$currentdate."'";

/* Execute the query. */

$stmt = sqlsrv_query( $conn, $tsql);

if ( $stmt )
{
     echo "Statement executed.<br>\n";
}
else
{
     echo "Error in statement execution.\n";       die( print_r( sqlsrv_errors(), true));
}

/* Iterate through the result set printing a row of data upon each iteration.*/

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))
{
    $attandencearray[substr(trim($row[0]),4)][] = array(
          'datetime1'=>$row[1]->format('Y-m-d H:i:s')
    );

}

// print_r($attandencearray);

$servername = "192.168.1.3";
$username = "root";
$password = "1234";
$dbname = "eattendance";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

foreach($attandencearray as $key=>$attandencevalues){
     // print_r($attandencevalues);
     $userid = $key;
     // echo $userid."</br>";
     array_shift($attandencevalues);
     $k=1;
     foreach($attandencevalues as $innerkey=>$dataattandence){
          if($k % 2 == 0)
          {
               $endtime = date("H:i:s",strtotime($dataattandence['datetime1']));
               array_push($insertarray, $endtime);

               // $checkrecord = "select count(*) from other_breaks where username=".$insertarray[0]." and date='".$insertarray[1]."' and start_time='".$insertarray[2]."' and `end_time`= '".$insertarray[3]."'";
               // $qry = mysqli_query($conn,$checkrecord);

               $res = mysqli_query($conn,"select * from other_breaks where username=".$insertarray[0]." and date='".$insertarray[1]."' and start_time='".$insertarray[2]."' and `end_time`= '".$insertarray[3]."'");

               // print_r($checkrecord);
               // echo"<br>";
               // print_r($res->num_rows);
               // exit;

               if($res->num_rows == 0){
               $insert_query = "insert into other_breaks (`username`,`date`,`start_time`,`end_time`) values ('".$insertarray[0]."','".$insertarray[1]."','".$insertarray[2]."','".$insertarray[3]."')\n";
               mysqli_query($conn,$insert_query);
               $insertarray = array();

               }
          }

          $dateonpunch = date("Y-m-d",strtotime($dataattandence['datetime1']));
          $starttime = date("H:i:s",strtotime($dataattandence['datetime1']));

          $insertarray = array($userid,$dateonpunch,$starttime );
          $k++;
     }
}
?>