<?php

$r = array();
require_once __DIR__ . '/db_config.php';

if (isset($_POST["companyname"])) {
$con = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD,DB_DATABASE) or die(mysql_error());
$companyname = $_POST['companyname'];
$date = $_POST['date'];

//$companyname ="zkitchen";

$query="SELECT t.employeemailid as timesheet_id,a.`location`,a.`state`,aa.emailid as checkin_id,aaa.emailid as checkout_id,a.atmid,a.`sitename`,a.`atmaddress`,t.timesheetdate,aa.facility_id,aa.date,aaa.facility_id_out,aaa.date,
s.employeename,s.emailid,( CASE WHEN t.timesheetdate IS NOT NULL THEN 'timesheet' WHEN aa.date IS NOT NULL THEN 'check_in' WHEN aaa.date IS NOT NULL THEN 'check_out' ELSE 'Abscent' END ) as status,
SUM(a.atmid) as total_count,SUM((CASE WHEN (t.timesheetdate IS NULL AND aa.date IS NULL AND aaa.date IS NULL) THEN 1 ELSE 0 END )) as absent_count,
s.employeename,s.emailid,( CASE WHEN t.timesheetdate IS NOT NULL THEN t.timesheetdate WHEN aa.date IS NOT NULL THEN aa.checkintime WHEN aaa.date IS NOT NULL THEN aaa.checkouttime END ) as status_time,t.starttime,t.endtime
FROM atmdetails a 
LEFT JOIN timesheet t ON t.facility_id=a.atmid AND t.timesheetdate='$date'
LEFT JOIN attendance aa ON aa.facility_id=a.atmid AND aa.date='$date'
LEFT JOIN attendance aaa ON aaa.facility_id_out=a.atmid AND aaa.date='$date' 
LEFT JOIN signupdetails s ON  s.emailid=aa.emailid OR s.emailid=t.employeemailid OR aaa.emailid=s.emailid
WHERE a.`companyname`='$companyname' AND a.atmid NOT IN ('Registered_address','Head Office') 
group by a.atmid
ORDER BY status DESC";


$result = mysqli_query($con,$query);
if(!empty($result) && mysqli_num_rows($result) > 0){
    while ($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
        array_push($r, $row);
    }
}
mysqli_close($con);               
echo json_encode($r);
}
?>