
<?php
include('zklib/ZKLib.php');

class LogController
{
    // $ppHCMTT#2024

    public function fn_getconn(){
        $pdo = new PDO("mysql:host=localhost;dbname=dbhcm", "apphcm", "\$ppHCMTT#2024");
        return $pdo;
    }
    public function selectquerySP($sql){
        // $pdo = new PDO("mysql:host=".DB_HOST_NAME.";dbname=".DB_IMS, DB_USER_NAME, DB_USER_PASS);
        $pdo = $this->fn_getconn(); //new PDO("mysql:host=localhost;dbname=dbhcm", "apphcm", "\$pp");
        $q = $pdo->query($sql);
        $q->setFetchMode(PDO::FETCH_ASSOC);
        return $q;
    }

    public function executeSP($qry){
        try {
            $rtn=false;
            $pdo = $this->fn_getconn();
            $statement = $pdo->prepare($qry);
            if($statement->execute()){
                $rtn=true;
            }
            return $rtn;            
        } catch (PDOException $e) {            
            return true;
        }
        
    }

    public function fn_getfieldvalue($fieldname,$qry){
        $rs=$this->selectquerySP($qry);
        $rtn="";
        if($rs){
            // $rw=$rs->fetch();
            // $rtn=$rw[$fieldname];
            while($rw=$rs->fetch()){
                $rtn=$rw[$fieldname];
            }
        }
        return $rtn;
    }
    
    public function fn_getfieldsvalues($fieldname,$qry){
        $rs=$this->selectquerySP($qry);
        $rtn="";
        if($rs){
            // $rw=$rs->fetch();
            // $rtn=$rw[$fieldname];
            while($rw=$rs->fetch()){
                $d=explode(",",$fieldname);
                for($i=0;$i<count($d);$i++){
                    $rtn.=$rw[$d[$i]].",";
                }
                
            }
            // echo $rtn;return false;
            $rtn=substr($rtn,0,strlen($rtn)-1);
        }
        return explode(",",$rtn);
    }

    public function fn_rowisexists($qry){
        $rs=$this->selectquerySP($qry);
        $rtn=false;
        if($rs){
            // $rw=$rs->fetch();
            // $rtn=$rw[$fieldname];
            while($rw=$rs->fetch()){
                $rtn=true;                
            }
        }
        return $rtn;
    }


    public function index()
    {
        // $qry="select distinct * from vw_attendanceemployeesync  
        //       where cast(dates as date)=cast(now() as date)                                                                             
        //       order by idxemployee,datestimes,sorts asc";
        $qry="select distinct * from vw_attendanceemployeesync  
              where cast(idt as date)=cast(now() as date)                                                                             
              order by idxemployee,datestimes,sorts asc";
        #echo $qry;
        $qryduration="";
        $rs=$this->selectquerySP($qry);
        if($rs){
            echo "start time : ".date("Y-m-d H:i:s")."\n";
            while($rw=$rs->fetch()){
                $swhereshiftcode="";
                if($rw['shiftcode']&&$rw['idxdept']==22){
                    $swhereshiftcode="and shiftcode='".$rw['shiftcode']."'";
                }
                if($rw['idxdept']==30){  // === only call center department
                    if($rw['attendancename']=='IN'){
                        $qry="insert into tbl_attendancelist
                              set idxattendance='".$rw['_idx']."',idxemployee='".$rw['idxemployee']."',nik='".$rw['nik']."',
                                  employeename='".$rw['employeename']."',title='".$rw['title']."',levelname='".$rw['levelname']."',
                                  officename='".$rw['officename']."',idxdept='".$rw['idxdept']."',divisiname='".$rw['divisiname']."',
                                  serviconame='".$rw['serviconame']."',dates='".$rw['dates']."',checkin='".$rw['datestimes']."',
                                  islates='".$rw['islates']."',months='".$rw['months']."',years='".$rw['years']."',
                                  shiftcode='".$rw['shiftcode']."',lateperemployee='".$rw['lateperemployee']."',lateperemployeer2='".$rw['lateperemployeer2']."',
                                  deviceidcheckin='".$rw['deviceidcheckin']."',
                                  iby='svc',idt='".$rw['datestimes']."'";

                        $qrychk="select _idx from tbl_attendancelist
                                  where idxemployee='".$rw['idxemployee']."' and cast(dates as date)=cast('".date("Y-m-d",strtotime($rw['dates']))."' as date) 
                                        $swhereshiftcode and isdeleted=0 order by _idx desc limit 1";
                        if($this->fn_rowisexists($qrychk)){
                            $qry="";
                        }
                    }   
                    elseif($rw['attendancename']=='OUT'){
                        // $qrychk="select _idx from tbl_attendancelist 
                        //          where idxemployee='".$rw['idxemployee']."' and cast(dates as date)=date_sub('".date("Y-m-d",strtotime($rw['dates']))."', INTERVAL 1 day) and isdeleted=0 order by _idx desc limit 1";
                        $qrychk="select _idx from tbl_attendancelist 
                                 where idxemployee='".$rw['idxemployee']."' 
                                        and cast(checkin as date)=cast('".date("Y-m-d",strtotime($rw['datestimes']))."' as date)
                                        $swhereshiftcode and (checkout is null)
                                        and isdeleted=0 order by _idx desc limit 1";
                        // echo $qrychk;//return false;

                        $_idx=$this->fn_getfieldvalue("_idx",$qrychk);
                        if($_idx){
                            $qry="update tbl_attendancelist 
                                  set checkout='".$rw['datestimes']."',deviceidcheckout='".$rw['deviceidcheckout']."'
                                  where _idx='$_idx' and UNIX_TIMESTAMP('".date("Y-m-d H:i:s",strtotime($rw['datestimes']))."')>UNIX_TIMESTAMP(checkin)";
                            $qryduration="update tbl_attendancelist 
                                            set duration=SEC_TO_TIME(TIMESTAMPDIFF(SECOND,checkin,checkout)) 
                                            where _idx='$_idx'";
                            $qrychk="select _idx from tbl_attendancelist 
                                     where cast(checkout as date)=cast('".date("Y-m-d",strtotime($rw['datestimes']))."' as date) 
                                           $swhereshiftcode and idxemployee='".$rw['idxemployee']."' and isdeleted=0 
                                     order by _idx desc limit 1";
                            if($this->fn_rowisexists($qrychk)){
                                $qry="";$qryduration="";
                            }
                        }
                        else{
                            // $qrychk="select _idx from tbl_attendancelist 
                            //         where idxemployee='".$rw['idxemployee']."' and cast(dates as date)=cast('".date("Y-m-d",strtotime($rw['dates']))."' as date) 
                            //               and isdeleted=0 order by _idx desc limit 1";
                            $qrychk="select _idx from tbl_attendancelist 
                                     where idxemployee='".$rw['idxemployee']."' 
                                           and cast(checkin as date)=date_sub('".date("Y-m-d",strtotime($rw['datestimes']))."', INTERVAL 1 day) and checkout is null
                                           $swhereshiftcode and isdeleted=0 order by _idx desc limit 1";

                            // echo $qrychk;return false;
                            $_idx=$this->fn_getfieldvalue("_idx",$qrychk);
                            if($_idx){
                                $qry="update tbl_attendancelist 
                                      set checkout='".$rw['datestimes']."',deviceidcheckout='".$rw['deviceidcheckout']."'
                                      where _idx='$_idx' and (UNIX_TIMESTAMP('".date("Y-m-d H:i:s",strtotime($rw['datestimes']))."')>UNIX_TIMESTAMP(checkin)) and checkout is null";
                                // $qry="update tbl_attendancelist set checkout='".$rw['datestimes']."' where _idx='$_idx'";
                                $qryduration="update tbl_attendancelist 
                                                set duration=SEC_TO_TIME(TIMESTAMPDIFF(SECOND,checkin,checkout)) 
                                                where _idx='$_idx'";
                                $qrychk="select _idx from tbl_attendancelist 
                                         where cast(checkout as date)=cast('".date("Y-m-d",strtotime($rw['datestimes']))."' as date) 
                                             $swhereshiftcode and idxemployee='".$rw['idxemployee']."' and isdeleted=0 
                                         order by _idx desc limit 1";
                                if($this->fn_rowisexists($qrychk)){
                                    $qry="";$qryduration="";
                                }
                                        
                            }
                        }
                        // echo $qry."\n";
                    }                
                    else{
                        $qry="";
                    }
                    if($qry){
                        $this->executeSP($qry);
                        if($qryduration){
                            // echo $qryduration."\n";
                            $this->executeSP($qryduration);
                        }
                        // if($rw['attendancename']=='OUT'){
                        //     $qry="update tbl_attendancelist set duration='".$rw['datestimes']."' where _idx='$_idx'";
                        // }
                    }
                    
                }
                else{ // === Non call center dept
                    if($rw['attendancename']=='IN'){$attfield="checkin='".$rw['datestimes']."',";}
                    if($rw['attendancename']=='R1'){$attfield="R1='".$rw['datestimes']."',";}
                    if($rw['attendancename']=='R2'){$attfield="R2='".$rw['datestimes']."',lateperemployeer2='".$rw['lateperemployeer2']."',";}
                    if($rw['attendancename']=='OUT'){$attfield="checkout='".$rw['datestimes']."',";}

                    $qry="insert into tbl_attendancelist 
                            set idxattendance='".$rw['_idx']."',idxemployee='".$rw['idxemployee']."',nik='".$rw['nik']."',
                                employeename='".$rw['employeename']."',title='".$rw['title']."',levelname='".$rw['levelname']."',
                                officename='".$rw['officename']."',idxdept='".$rw['idxdept']."',divisiname='".$rw['divisiname']."',
                                serviconame='".$rw['serviconame']."',dates='".$rw['dates']."',".$attfield."
                                islates='".$rw['islates']."',months='".$rw['months']."',years='".$rw['years']."',
                                shiftcode='".$rw['shiftcode']."',lateperemployee='".$rw['lateperemployee']."',lateperemployeer2='".$rw['lateperemployeer2']."',
                                deviceidcheckin='".$rw['deviceidcheckin']."',
                                iby='svc',idt='".$rw['datestimes']."'";

                    $qrychk="select _idx,checkin,checkout,lateperemployee from tbl_attendancelist 
                             where idxemployee='".$rw['idxemployee']."' and cast(dates as date)=cast('".date("Y-m-d",strtotime($rw['dates']))."' as date) 
                                    $swhereshiftcode and isdeleted=0 order by _idx desc limit 1";
                    if($this->fn_rowisexists($qrychk)){
                        // $_idx=$this->fn_getfieldvalue("_idx",$qrychk);
                        $sp=$this->fn_getfieldsvalues("_idx,checkin,checkout,lateperemployee", $qrychk);
                        $_idx=$sp[0];$checkin=$sp[1];$checkout=$sp[2];$lateperemployee=$sp[3];
                        $laterperemployeefield="";

                        // if(!$lateperemployee){
                        //     $laterperemployeefield="lateperemployee='".$rw['lateperemployee']."',";
                        // }

                        $qry="update tbl_attendancelist 
                              set ".$attfield.$laterperemployeefield."uby='svc',udt='".$rw['datestimes']."'
                              where _idx='$_idx'";
                        if($rw['attendancename']=='IN'&&$checkin!=null){
                            $qry="update tbl_attendancelist 
                                  set uby='svc',udt='".$rw['datestimes']."'
                                  where _idx='$_idx'";
                        }
                        if($rw['attendancename']=='OUT'){
                            $qry="update tbl_attendancelist 
                                  set ".$attfield."uby='svc',udt='".$rw['datestimes']."',deviceidcheckout='".$rw['deviceidcheckout']."'
                                  where _idx='$_idx' and  UNIX_TIMESTAMP('".date("Y-m-d H:i:s",strtotime($rw['datestimes']))."')>UNIX_TIMESTAMP(checkin)";                            
                          
                            $idxdept=$rw['idxdept'];
                            if($idxdept==33||$idxdept==34||$idxdept==30||$idxdept==22){
                                $qryduration="update tbl_attendancelist 
                                        set duration=SEC_TO_TIME(TIMESTAMPDIFF(SECOND,checkin,checkout)) 
                                        where _idx='$_idx'";
                            }
                            else{
                                $qryduration="update tbl_attendancelist 
                                              set duration=SEC_TO_TIME(TIMESTAMPDIFF(SECOND,checkin,R1)+TIMESTAMPDIFF(SECOND,R2,checkout)) 
                                              where _idx='$_idx'";

                            }
                        }
                    }

                    if($qry){
                        $this->executeSP($qry);
                        if($qryduration){
                            // echo $qryduration."\n";
                            $this->executeSP($qryduration);
                        }
                    }
                }
            }
            echo "end time : ".date("Y-m-d H:i:s")."\n";
        }
    }
   
}

$cl = new LogController();
$rtn =$cl->index();

#if(isset($_REQUEST['isgetdata'])){
#   echo "indx";
#    $rtn = $cl->index();
#}
#else{
#    $rtn = $cl->fn_curl();
#}

echo $rtn;