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

class LogController
{
    // $ppHCMTT#2024

    public function fn_getconn(){
        $pdo = new PDO("mysql:host=192.168.1.4;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){
        $rtn=false;
        $pdo = $this->fn_getconn();
        $statement = $pdo->prepare($qry);
        if($statement->execute()){
            $rtn=true;
        }
        return $rtn;
    }

    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(){
        $argv=$_SERVER['argv'];
        // echo $argv[1];return false;
        $qry="select * from tbl_attendancelogs 
              where cast(dates as date)=cast(now() as date) and isprocess=0
              order by _idx asc";
        $rs=$this->selectquerySP($qry);
        if($rs){
            $d=array();
            while($rw=$rs->fetch()){      
                echo "start time : ".date("Y-m-d H:i:s")."\n";
                $d['timestamp']=date("Y-m-d H:i:s", strtotime($rw['dates'].' '.$rw['times']));
                $d['id']=$rw['fingerid'];
                $res=$this->fn_setattendance($d, $rw['deviceip']);
                if($res){
                    $qryupdate="update tbl_attendancelogs set isprocess=1,processdate=now() where _idx='".$rw['_idx']."'";
                    // echo $qryupdate."\n";
                    $this->executeSP($qryupdate);
                }
                echo "End time : ".date("Y-m-d H:i:s")."\n\n";

            }
        }

    }

    public function fn_setattendance($data, $ip){
        $rtn=false;
        $months=(int)date("m", strtotime($data['timestamp']));
        $years=(int)date("Y", strtotime($data['timestamp']));
        $timestamp=(int)date("Hi", strtotime($data['timestamp']));
        $timestampH=(int)date("H", strtotime($data['timestamp']));
        $timestampstr=date("H:i", strtotime($data['timestamp']));
        $datestimes=date("Y-m-d H:i:s", strtotime($data['timestamp']));
	    $dates=date("Y-m-d", strtotime($data['timestamp']));
	    $datesadd1=date("Y-m-d", strtotime($data['timestamp'].' +1 day'));
        $totallateperemp=0;
        $totallateperempr2=0;
        $swhereshift="";
        $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                     t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
              from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
              where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."'
                    and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
              order by t._idx asc limit 1";
        // echo $qry;return false;
        
        if($timestamp>2300){
            $timestampH=0;
            $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                            t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                    from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                    where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."'
                            and (unix_timestamp(concat('".$dates."',' ','$timestampstr'))>=unix_timestamp(concat('$dates',' ',t.starttimestr)) 
                            and unix_timestamp(concat('".$dates."',' ','$timestampstr'))<=unix_timestamp(concat('".$datesadd1."',' ',t.thresholdtimestr)))
                    order by t._idx desc limit 1";

        }
        
        // if($data['id']==78){
        //     echo $qry."\n\n";
        // }
        // echo $qry;return false;
        $sp=$this->fn_getfieldsvalues("idxdept,idxemployee,attendancename", $qry);
        //$idxdeptemp=$sp[0];$idxemployee=$sp[1];
	    $idxdeptemp=(isset($sp[0]) ? $sp[0] : 0);$idxemployee=(isset($sp[1]) ? $sp[1] : 0);
        $attendancenameemp=(isset($sp[2]) ? $sp[2] : '');
        if($idxdeptemp==33||$idxdeptemp==34){
            // $sp=$this->fn_getfieldsvalues("shiftcategory,idxemployee","select shiftcategory,idxemployee from tbl_lojaschedule where cast(dates as date)=cast('$dates' as date) and idxemployee='$idxemployee' and isdeleted=0");
            // $shiftcode=(isset($sp[0]) ? $sp[0] : "");$idxemployee=(isset($sp[1]) ? $sp[1] : 0);
            // $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
            //                 t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
            //       from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
            //       where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' and t.shiftcode='$shiftcode'
            //               and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
            //       order by t._idx asc limit 1";
            $qryshift="select shiftcategory,idxemployee from tbl_lojaschedule where cast(dates as date)=cast('$dates' as date) and idxemployee='$idxemployee' and isdeleted=0";
            $rsshift=$this->selectquerySP($qryshift);
            $shiftcode="";$limit="limit 1";
            if($rsshift){
                while($rwshift=$rsshift->fetch()){
                    $shiftcode.="'".$rwshift['shiftcategory']."',";
                }
                if($shiftcode){
                    $shiftcode=substr($shiftcode,0,strlen($shiftcode)-1);
                }
            }

            $swhereshiftcode="and t.shiftcode=''";
            if($shiftcode){
                $swhereshiftcode="and t.shiftcode in($shiftcode)";
            }
            
            $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                            t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                  from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                  where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                          and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
                  order by t._idx asc limit 1"; 
            if($timestamp>=2300){
                $timestampH=0;
                $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                                t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                    from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                    where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                            and (unix_timestamp(concat('".$dates."',' ','$timestampstr'))>=unix_timestamp(concat('$dates',' ',t.starttimestr)) 
                            and unix_timestamp(concat('".$dates."',' ','$timestampstr'))<=unix_timestamp(concat('".$datesadd1."',' ',t.thresholdtimestr)))
                    order by t._idx desc limit 1"; 
            }
        }
        
        if($idxdeptemp==30){
            // $sp=$this->fn_getfieldsvalues("shiftcategory,idxemployee","select shiftcategory,idxemployee from tbl_callcenterschedule where cast(dates as date)=cast('$dates' as date) and idxemployee='$idxemployee' and isdeleted=0");
            // $shiftcode=(isset($sp[0]) ? $sp[0] : "");$idxemployee=(isset($sp[1]) ? $sp[1] : 0);
            // $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
            //                 t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
            //       from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
            //       where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' and t.shiftcode='$shiftcode'
            //               and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
            //       order by t._idx asc limit 1";
            $qryshift="select shiftcategory,idxemployee from tbl_callcenterschedule 
                       where (cast(dates as date)=cast('$dates' as date) or cast(datesout as date)=cast('$dates' as date)) and idxemployee='$idxemployee' and isdeleted=0";
            // echo $qryshift;return false;
            // if($idxemployee==115){
            //     echo $qryshift."\n";
            // }
            // if($attendancenameemp=='OUT'){
            //     $qryshift="select shiftcategory,idxemployee from tbl_callcenterschedule 
            //                where (cast(datesout as date)=cast('$dates' as date)) and idxemployee='$idxemployee' and isdeleted=0";

            // }
            $rsshift=$this->selectquerySP($qryshift);
            $shiftcode="";$limit="limit 1";
            if($rsshift){
                while($rwshift=$rsshift->fetch()){
                    $shiftcode.="'".$rwshift['shiftcategory']."',";
                }
                if($shiftcode){
                    $shiftcode=substr($shiftcode,0,strlen($shiftcode)-1);
                }
            }

            $swhereshiftcode="and t.shiftcode=''";
            if($shiftcode){
                $swhereshiftcode="and t.shiftcode in($shiftcode)";
            }
            
            $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                            t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                  from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                  where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                          and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
                  order by t._idx asc limit 1";
            // echo $qry."\n";return false;
            if($timestamp>=2300){
                $timestampH=0;        
                $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                                t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                    from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                    where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                          and (unix_timestamp(concat('".$dates."',' ','$timestampstr'))>=unix_timestamp(concat('$dates',' ',t.starttimestr)) 
                          and unix_timestamp(concat('".$dates."',' ','$timestampstr'))<=unix_timestamp(concat('".$datesadd1."',' ',t.thresholdtimestr)))

                    order by t._idx desc limit 1"; 
            }

            // if($idxemployee==115){
            //     echo $qry."\n";
            // }
        }
	
	    if($idxdeptemp==22){
            $qryshift="select shiftcategory,idxemployee from tbl_supervisaoschedule where cast(dates as date)=cast('$dates' as date) and idxemployee='$idxemployee' and isdeleted=0";
            $rsshift=$this->selectquerySP($qryshift);
            $shiftcode="";$limit="limit 1";
            if($rsshift){
                while($rwshift=$rsshift->fetch()){
                    $shiftcode.="'".$rwshift['shiftcategory']."',";
                }
                if($shiftcode){
                    $shiftcode=substr($shiftcode,0,strlen($shiftcode)-1);
                }
            }

            $swhereshiftcode="and t.shiftcode=''";
            if($shiftcode){
                $swhereshiftcode="and t.shiftcode in($shiftcode)";
            }
            
            // $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
            //                 t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
            //       from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
            //       where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
            //               and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
            //       order by t._idx asc limit 1";   
            $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                            t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                  from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                  where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                          and ('$timestamp'>=t.starttime and '$timestamp'<=t.thresholdtime)
                  order by t.idxshift desc limit 1";   
            if($timestamp>=2300){
                $timestampH=0;        
                // $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                //                 t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                //     from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                //     where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                //             and (unix_timestamp(concat('".$dates."',' ','$timestampstr'))>=unix_timestamp(concat('$dates',' ',t.starttimestr)) 
                //             and unix_timestamp(concat('".$dates."',' ','$timestampstr'))<=unix_timestamp(concat('".$datesadd1."',' ',t.thresholdtimestr)))
                //     order by t._idx desc limit 1"; 
                $qry="select e._idx idxemployee,e.nik,e.employeename,e.fingerid,t.idxdept,t.deptname,t.attendancename,t.starttimestr,t.starttime,
                                t.endtimestr,t.endtime,t.thresholdtimestr,t.thresholdtime,t.shiftcode
                    from tbl_employee e inner join tbl_attendancethreshold t on e.idxdept=t.idxdept
                    where e.isdeleted=0 and t.isdeleted=0 and e.fingerid='".$data['id']."' $swhereshiftcode
                            and (unix_timestamp(concat('".$dates."',' ','$timestampstr'))>=unix_timestamp(concat('$dates',' ',t.starttimestr)) 
                            and unix_timestamp(concat('".$dates."',' ','$timestampstr'))<=unix_timestamp(concat('".$datesadd1."',' ',t.thresholdtimestr)))
                    order by t.idxshift desc limit 1"; 
            }

        }

        // echo $qry."\n\nnndd";
        $rs=$this->selectquerySP($qry);
        if($rs){
            while($rw=$rs->fetch()){
                $islate=0;$shiftcode=$rw['shiftcode'];
                // if($rw['idxdept']==30||$rw['idxdept']==22){
                if((int)$rw['idxdept']==30){
                    $qrysc="select distinct t.*
                            from tbl_attendancethreshold t inner join tbl_callcenterschedule s on t.idxshift=s.idxshift and t.idxdept='".$rw['idxdept']."'
                            where s.idxemployee='".$rw['idxemployee']."' and t.attendancename='IN'
                                  and cast(s.dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                                  and t.shiftcode='".$rw['shiftcode']."' and t.isdeleted=0 and s.isdeleted=0 order by t._idx desc limit 1";
                    // echo $qrysc."\n";return false;
                    $endtimecc=$this->fn_getfieldvalue("endtime",$qrysc);
                    $endtimestrcc=$this->fn_getfieldvalue("endtimestr",$qrysc);
                    if($endtimecc){
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='IN'){
                            $date1=strtotime($dates." ".$endtimestrcc);
                            $date2=strtotime($dates." ".$timestampstr);
                            $totallateperemp=round(abs($date2 - $date1) / 60,2);
                            // $totallateperemp=(int)$timestamp-(int)$endtimecc;
                            $islate=1;
                        }
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='R2'){
                            // $totallateperempr2=(int)$timestamp-(int)$endtimecc;
                            $date1=strtotime($dates." ".$endtimestrcc);
                            $date2=strtotime($dates." ".$timestampstr);
                            if($date2>$date1){
                                $totallateperempr2=round(abs($date2 - $date1) / 60,2);
                            }                       
                        }
                    }
                }
                elseif((int)$rw['idxdept']==22){
                    // $qrysc="select distinct t.*
                    //         from tbl_attendancethreshold t inner join tbl_supervisaoschedule s on t.idxshift=s.idxshift and t.idxdept='".$rw['idxdept']."'
                    //         where s.idxemployee='".$rw['idxemployee']."' and t.attendancename='".$rw['attendancename']."'
                    //               and cast(s.dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                    //               and t.shiftcode='".$rw['shiftcode']."' and t.isdeleted=0 and s.isdeleted=0 order by t._idx desc limit 1";
                    $qrysc="select distinct t.*
                            from tbl_attendancethreshold t inner join tbl_supervisaoschedule s on t.idxshift=s.idxshift and t.idxdept='".$rw['idxdept']."'
                            where s.idxemployee='".$rw['idxemployee']."' and t.attendancename='".$rw['attendancename']."'
                                  and cast(s.dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                                  $swhereshiftcode and t.isdeleted=0 and s.isdeleted=0 order by t.idxshift desc limit 1";
                    $endtimecc=$this->fn_getfieldvalue("endtime",$qrysc);
                    $endtimeccstr=$this->fn_getfieldvalue("endtimestr",$qrysc);
                    if($endtimecc){
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='IN'){
                            // $totallateperemp=(int)$timestamp-(int)$endtimecc;
                            $date1=strtotime($dates." ".$endtimeccstr);
                            $date2=strtotime($dates." ".$timestampstr);
                            if($date2>$date1){
                                $totallateperempr2=round(abs($date2 - $date1) / 60,2);
                            }

                            $islate=1;
                        }
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='R2'){
                            // $totallateperempr2=(int)$timestamp-(int)$endtimecc;    
                            $date1=strtotime($dates." ".$endtimeccstr);
                            $date2=strtotime($dates." ".$timestampstr);
                            if($date2>$date1){
                                $totallateperempr2=round(abs($date2 - $date1) / 60,2);
                            }

                  
                        }
                    }
                }
                elseif((int)$rw['idxdept']==33||(int)$rw['idxdept']==34){
                    $qrysc="select distinct t.*
                            from tbl_attendancethreshold t inner join tbl_lojaschedule s on t.idxshift=s.idxshift and t.idxdept='".$rw['idxdept']."'
                            where s.idxemployee='".$rw['idxemployee']."' and t.attendancename='IN'
                                  and cast(s.dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                                  and t.shiftcode='".$rw['shiftcode']."' and t.isdeleted=0 and s.isdeleted=0 order by t._idx desc limit 1";
                    $endtimecc=$this->fn_getfieldvalue("endtime",$qrysc);
                    $endtimeccstr=$this->fn_getfieldvalue("endtimestr",$qrysc);
                    if($endtimecc){
                        // if($data['id']=='329'){
                        //     echo (int)$timestamp." ".$endtimecc." ".$rw['attendancename']."\n";
                        // }
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='IN'){
                            // $totallateperemp=(int)$timestamp-(int)$endtimecc;
                            $date1=strtotime($dates." ".$endtimeccstr);
                            $date2=strtotime($dates." ".$timestampstr);
                            $totallateperemp=round(abs($date2 - $date1) / 60,2);

                            $islate=1;
                        }
                        if((int)$timestamp>(int)$endtimecc&&$rw['attendancename']=='R2'){
                            // $totallateperempr2=(int)$timestamp-(int)$endtimecc;                        
                            $date1=strtotime($dates." ".$endtimeccstr);
                            $date2=strtotime($dates." ".$timestampstr);
                            $totallateperempr2=round(abs($date2 - $date1) / 60,2);


                        }
                    }
                }
                else{
                    if((int)$timestamp>(int)$rw['endtime']&&$rw['attendancename']=='IN'){
                        // $totallateperemp=(int)$timestamp-(int)$rw['endtime'];
                        $date1=strtotime($dates." ".$rw['endtimestr']);
                        $date2=strtotime($dates." ".$timestampstr);
                        $totallateperemp=round(abs($date2 - $date1) / 60,2);


                        $islate=1;
                    }
                    if((int)$timestamp>(int)$rw['endtime']&&$rw['attendancename']=='R2'){
                        // $totallateperempr2=(int)$timestamp-(int)$rw['endtime'];                        
                        $date1=strtotime($dates." ".$rw['endtimestr']);
                        $date2=strtotime($dates." ".$timestampstr);
                        if($date2>$date1){
                            $totallateperempr2=round(abs($date2 - $date1) / 60,2);
                        }


                    }
                }
                // $qryexists="select _idx,deviceid,deviceidcheckout from tbl_attendanceemployee
                //             where trim(fingerid)=trim('".$data['id']."') and cast(dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                //                  and isdeleted=0 and attendancename='".$rw['attendancename']."'
                //             order by _idx asc limit 1";
                // $idx=$this->fn_getfieldvalue("_idx",$qryexists);
                $qryexists="select _idx,deviceid,deviceidcheckout,datestimes from tbl_attendanceemployee
                            where trim(fingerid)=trim('".$data['id']."') and cast(dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                                 and isdeleted=0 and attendancename='".$rw['attendancename']."' and shiftcode='".$rw['shiftcode']."'
                            order by _idx asc limit 1";
                // echo $qryexists."\n";return false;
                $sp=$this->fn_getfieldsvalues("_idx,deviceid,deviceidcheckout,datestimes",$qryexists);
                //$idx=$sp[0];$deviceidcheckin=$sp[1];$deviceidcheckout=$sp[2];
		        $idx=(isset($sp[0]) ? $sp[0] : 0);$deviceidcheckin=(isset($sp[1]) ? $sp[1] : '');
                $deviceidcheckout=(isset($sp[2]) ? $sp[2] : '');
                $timesexists=(isset($sp[3]) ? (int)date("Hi",strtotime($sp[3])) : 0);
                $timesexistsstr=(isset($sp[3]) ? date("H:i",strtotime($sp[3])) : 0);
                // echo $idx." ".$deviceidcheckin." ".$deviceidcheckout."\n";return false;

                // $deviceidcheckin="";$deviceidcheckout="";
                // $qry="select deviceid from tbl_deviceinfo where trim(deviceip)=trim('$ip') and isdeleted=0 order by _idx desc limit 1";
                // $deviceid=$this->fn_getfieldvalue("deviceid",$qry);

                if($idx){
                    $islate=0;
                    if((int)$timesexists>(int)$rw['endtime']&&$rw['attendancename']=='IN'){
                        // $totallateperemp=(int)$timesexists-(int)$rw['endtime'];
                        $date1=strtotime($dates." ".$rw['endtimestr']);
                        $date2=strtotime($dates." ".$timesexistsstr);
                        $totallateperemp=round(abs($date2 - $date1) / 60,2);

                        $islate=1;
                    }
                    if((int)$timesexists>(int)$rw['endtime']&&$rw['attendancename']=='R2'){
                        // $totallateperempr2=(int)$timesexists-(int)$rw['endtime'];
                        $date1=strtotime($dates." ".$rw['endtimestr']);
                        $date2=strtotime($dates." ".$timesexistsstr);
                        if($date2>$date1){
                            $totallateperempr2=round(abs($date2 - $date1) / 60,2);
                        }
                        

                    }

                    if(!$totallateperemp){$totallateperemp=0;}
                    if(!$totallateperempr2){$totallateperempr2=0;}
                    
                    // $qry="update tbl_attendanceemployee
                    //       set udt=now(),islates='$islate',totallateperemp='$totallateperemp'
                    //       where _idx='$idx' and deviceid<>'MANUAL'";
                    // $qry="update tbl_attendanceemployee
                    //       set udt=now(),islates='$islate',totallateperemp='$totallateperemp',totallateperempr2='$totallateperempr2'
                    //       where _idx='$idx' and deviceid<>'MANUAL'";
                    $qry="update tbl_attendanceemployee
                          set udt=now(),islates='$islate'
                          where _idx='$idx' and deviceid<>'MANUAL'";
                    // echo $qry."\n";return false;
                }
                else{
                    // if(!$deviceidcheckin||is_null($deviceidcheckin)){$deviceidcheckin=$deviceid;}

                    // $swheredeviceid="deviceid='".$deviceid."',";
                    // if($rw['attendancename']=='OUT'){
                    //     if(!$deviceidcheckout||is_null($deviceidcheckout)){$deviceidcheckout=$deviceid;}                        
                    //     $swheredeviceid="deviceidcheckout='".$deviceidcheckout."',";
                    //     // ==== Get Device ID Checkin
                    //     $qrygetdevicecheckin="select deviceid from tbl_attendanceemployee
                    //                           where idxemployee='".$rw['idxemployee']."' and attendancename='IN'
                    //                                 and cast(dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                    //                           order by _idx desc limit 1";
                    //     $deviceidcheckin=$this->fn_getfieldvalue("deviceid",$qrygetdevicecheckin);
                    // }
		            $swheredeviceid="";
                    // $qry="insert into tbl_attendanceemployee
                    //         set fingerid='".$data['id']."',fingerip='".$ip."',".$swheredeviceid."idxemployee='".$rw['idxemployee']."',
                    //             nik='".$rw['nik']."',employeename='".$rw['employeename']."',dates='".date("Y-m-d", strtotime($data['timestamp']))."',
                    //             times='".date("H:i:s", strtotime($data['timestamp']))."',datestimes='$datestimes',attendancename='".$rw['attendancename']."',thresholdtime='".$rw['thresholdtime']."',
                    //             thresholdtimestr='".$rw['thresholdtimestr']."',endtime='".$rw['endtime']."',shiftcode='".$shiftcode."',
                    //             totallateperemp='$totallateperemp',totallateperempr2='$totallateperempr2',months='$months',years='$years',islates='$islate',iby='".$rw['nik']."',idt=now()";
                    $qry="insert into tbl_attendanceemployee
                            set fingerid='".$data['id']."',fingerip='".$ip."',".$swheredeviceid."idxemployee='".$rw['idxemployee']."',
                                nik='".$rw['nik']."',idxdept='".$rw['idxdept']."',employeename='".$rw['employeename']."',dates='".date("Y-m-d", strtotime($data['timestamp']))."',
                                times='".date("H:i:s", strtotime($data['timestamp']))."',datestimes='$datestimes',attendancename='".$rw['attendancename']."',thresholdtime='".$rw['thresholdtime']."',
                                thresholdtimestr='".$rw['thresholdtimestr']."',endtime='".$rw['endtime']."',shiftcode='".$shiftcode."',
                                months='$months',years='$years',islates='$islate',iby='".$rw['nik']."',idt=now()";

                }
                // echo $qry."\n";
                // if($deviceid=='172.16.110.251'){
		   //echo $qry."\n";
                // }
                if($this->executeSP($qry)){
                    // if($rw['attendancename']=='OUT'){
                    //     $qry="update tbl_attendanceemployee set deviceid='$deviceidcheckin',deviceidcheckout='$deviceidcheckout'
                    //           where trim(fingerid)=trim('".$data['id']."') and cast(dates as date)=cast('".date("Y-m-d", strtotime($data['timestamp']))."' as date)
                    //                 and isdeleted=0";
                    //     //$this->executeSP($qry);
                    // }
                    if($this->attendancelistsync($rw['idxemployee'],$dates)){
                        $rtn=true;
                    }
                    
                }

            }
        }

        return $rtn;
    }
    
    public function attendancelistsync($idxemployee,$dates)
    {
        $rtn=false;
        // $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";
        $qry="select distinct * from vw_attendanceemployeesync  
              where idxemployee='$idxemployee' and cast(dates as date)=cast('$dates' as date)                                                                             
              order by idxemployee,datestimes,sorts asc";
        // echo $qry;return false;
        $qryduration="";
        $rs=$this->selectquerySP($qry);
        if($rs){
            
            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']."'";
                    $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']."',
                                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";
        }
        $rtn=true;
        return $rtn;
    }
}

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

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

echo $rtn;
