748 lines
45 KiB
PHP
Executable File
748 lines
45 KiB
PHP
Executable File
|
|
<?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){
|
|
$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()){
|
|
$d['timestamp']=date("Y-m-d H:i:s", strtotime($rw['dates'].' '.$rw['times']));
|
|
$d['id']=$rw['fingerid'];
|
|
echo "start time : ".date("Y-m-d H:i:s")." Process dates ".$d['timestamp'].", FingerId : ".$rw['fingerid']." \n";
|
|
|
|
$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;
|
|
}
|
|
|
|
public function fn_idxdept(){
|
|
|
|
$qry="update tbl_attendanceemployee a, tbl_employee e set a.idxdept = e.idxdept
|
|
where e._idx=a.idxemployee and a.idxdept=0";
|
|
$this->executeSP($qry);
|
|
$qry="update tbl_attendancelist a, tbl_employee e set a.idxdept = e.idxdept
|
|
where e._idx=a.idxemployee and a.idxdept=0";
|
|
$this->executeSP($qry);
|
|
return true;
|
|
}
|
|
}
|
|
|
|
$cl = new LogController();
|
|
$rtn =$cl->index();
|
|
$rtn =$cl->fn_idxdept();
|
|
|
|
#if(isset($_REQUEST['isgetdata'])){
|
|
# echo "indx";
|
|
# $rtn = $cl->index();
|
|
#}
|
|
#else{
|
|
# $rtn = $cl->fn_curl();
|
|
#}
|
|
|
|
echo $rtn;
|