<?php
/**
 * RosteringAPI.php — Unified Rostering Backend API
 * Replaces: 7x CheckEmployee-timeclash*.php + all xcrud CRUD
 * All responses: JSON
 */
ob_start();
error_reporting(E_ALL & ~E_NOTICE);
$PageSecurity = 0;
include('includes/session.inc');
ob_end_clean();
header('Content-Type: application/json');

$userid = isset($_SESSION['UserID']) ? $_SESSION['UserID'] : '';
if (empty($userid)) { die(json_encode(array('error'=>'Not authenticated'))); }

$conn = new mysqli('localhost','sooggoo','4Compassion*','evergreentest');
if ($conn->connect_error) { die(json_encode(array('error'=>'DB connection failed'))); }
$conn->set_charset('utf8');

$action = isset($_GET['action']) ? $_GET['action'] : (isset($_POST['action']) ? $_POST['action'] : '');

// ═══ PERMISSIONS ═══
function getUserPerms($uid) {
    $p = array('depts'=>array(),'sectors'=>array(),'admin'=>false);
    if (in_array($uid, array('admin','mosheed','sharukh','ravneet','sachin','ravinesh','pchand','rajnal'))) { $p['admin']=true; return $p; }
    $m = array(
        'kelera'=>'11','filiti'=>'11','jona'=>'11','zubia'=>'11','sureta'=>'11','eugene'=>'11',
        'seni'=>'2:23','tarusila'=>'2:23','sala'=>'2','asenacacagi'=>'2',
        'liti'=>'2:24','maca'=>'2:24','ronald'=>'2:24',
        'annie'=>'1','salata'=>'1','jokaveti'=>'1','auliasi'=>'1','alumita'=>'1','alisi'=>'1',
        'asenaca'=>'1','lavenia'=>'1','marenda'=>'1','kalesim'=>'1','controlroom'=>'1','ravai'=>'1',
        'lilian'=>'1:4','emi1'=>'1:2','kalesi'=>'1:27',
        'ragnesh'=>'3','rowen'=>'8','rramlu'=>'21',
        'ilimo'=>'1,4','semi'=>'1,4','installssuva'=>'9','installswest'=>'10',
        'schand'=>'6','charles'=>'7','akumar'=>'7','saula'=>'4'
    );
    if (isset($m[$uid])) {
        $parts = explode(':', $m[$uid]);
        $p['depts'] = explode(',', $parts[0]);
        if (isset($parts[1])) $p['sectors'] = explode(',', $parts[1]);
    }
    return $p;
}
function permWhere($p, $t='g') {
    if ($p['admin']) return '1=1';
    $c = array();
    if (!empty($p['depts'])) { $d=implode(',',array_map('intval',$p['depts'])); $c[]="$t.department IN ($d)"; }
    if (!empty($p['sectors'])) { $s="'".implode("','",array_map('intval',$p['sectors']))."'"; $c[]="$t.sectors IN ($s)"; }
    return $c ? implode(' AND ',$c) : '1=1';
}
$perms = getUserPerms($userid);

// ═══ DAY MAP ═══
$DM = array(
    'mon'=>array('i'=>'montime_in','o'=>'montime_out','t'=>'mon_total','a'=>'mon_absent'),
    'tue'=>array('i'=>'tuestime_in','o'=>'tuestime_out','t'=>'tues_total','a'=>'tues_absent'),
    'wed'=>array('i'=>'wedtime_in','o'=>'wedtime_out','t'=>'wed_total','a'=>'wed_absent'),
    'thu'=>array('i'=>'thurstime_in','o'=>'thurstime_out','t'=>'thurs_total','a'=>'thurs_absent'),
    'fri'=>array('i'=>'fritime_in','o'=>'fritime_out','t'=>'fri_total','a'=>'fri_absent'),
    'sat'=>array('i'=>'sattime_in','o'=>'sattime_out','t'=>'sat_total','a'=>'sat_absent'),
    'sun'=>array('i'=>'sunttime_in','o'=>'sunttime_out','t'=>'sun_total','a'=>'sun_absent'),
    'mon2'=>array('i'=>'mon2ttime_in','o'=>'mon2time_out','t'=>'mon2_total','a'=>null)
);

function calcH($ti,$to) {
    if (empty($ti)||empty($to)) return 0;
    $i=strtotime($ti); $o=strtotime($to);
    if ($o<=$i) $o+=86400;
    return round(($o-$i)/3600,2);
}
function recalc($conn,$id) {
    $c='IFNULL(mon_total,0)+IFNULL(tues_total,0)+IFNULL(wed_total,0)+IFNULL(thurs_total,0)+IFNULL(fri_total,0)+IFNULL(sat_total,0)+IFNULL(sun_total,0)+IFNULL(mon2_total,0)';
    $conn->query("UPDATE rosteringtimesheet SET total_hrs=($c) WHERE rstimeid=".intval($id));
}
function calcOT($hrs,$contract) {
    $l1=($contract==9)?9:8; $l2=($contract==9)?13:12;
    $n=0;$h=0;$d=0;
    if ($hrs<=$l1){$n=$hrs;}elseif($hrs<=$l2){$n=$l1;$h=$hrs-$l1;}else{$n=$l1;$h=$l2-$l1;$d=$hrs-$l2;}
    return array('n'=>round($n,2),'h'=>round($h,2),'d'=>round($d,2));
}

// ═══ ROUTER ═══
switch($action) {

case 'get_periods':
    $rows=array();
    $r=$conn->query("SELECT id,period,start_date,end_date,status,created_by FROM calendar_management ORDER BY id DESC LIMIT 30");
    if($r) while($row=$r->fetch_assoc()) $rows[]=$row;
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

case 'get_kpis':
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $pw=permWhere($perms);
    $k=array('locations'=>0,'required'=>0,'assigned'=>0,'unfilled'=>0,'overtime'=>0,'total_hrs'=>0,'clashes'=>0);
    $r=$conn->query("SELECT COUNT(*) c,COALESCE(SUM(req_guards),0) rq FROM guardforceclient g WHERE (g.disable IS NULL OR g.disable=0) AND $pw");
    if($r&&$row=$r->fetch_assoc()){$k['locations']=intval($row['c']);$k['required']=intval($row['rq']);}
    if($cid>0){
        $r=$conn->query("SELECT COUNT(DISTINCT rt.employeeid) a,COALESCE(SUM(rt.total_hrs),0) h FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid JOIN guardforceclient g ON r.clientid=g.clientid WHERE r.id=$cid AND $pw");
        if($r&&$row=$r->fetch_assoc()){$k['assigned']=intval($row['a']);$k['total_hrs']=round(floatval($row['h']),1);}
        $r=$conn->query("SELECT COUNT(*) c FROM(SELECT rt.employeeid FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid JOIN guardforceclient g ON r.clientid=g.clientid WHERE r.id=$cid AND $pw GROUP BY rt.employeeid HAVING SUM(rt.total_hrs)>48)x");
        if($r&&$row=$r->fetch_assoc()) $k['overtime']=intval($row['c']);
    }
    $k['unfilled']=max(0,$k['required']-$k['assigned']);
    echo json_encode(array('ok'=>1,'data'=>$k)); break;

case 'get_filters':
    $depts=array();$sects=array();
    $r=$conn->query("SELECT departmentid,departmentname FROM department ORDER BY departmentname");
    if($r)while($row=$r->fetch_assoc())$depts[]=$row;
    $r=$conn->query("SELECT id,name FROM sectors ORDER BY name");
    if($r)while($row=$r->fetch_assoc())$sects[]=$row;
    echo json_encode(array('ok'=>1,'departments'=>$depts,'sectors'=>$sects)); break;

case 'get_locations':
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $df=isset($_GET['dept'])?$conn->real_escape_string($_GET['dept']):'';
    $sf=isset($_GET['sector'])?$conn->real_escape_string($_GET['sector']):'';
    $q=isset($_GET['search'])?$conn->real_escape_string($_GET['search']):'';
    $pw=permWhere($perms);
    $w="WHERE (g.disable IS NULL OR g.disable=0) AND $pw";
    if($df) $w.=" AND g.department='$df'";
    if($sf) $w.=" AND g.sectors='$sf'";
    if($q) $w.=" AND (g.patrollocation LIKE '%$q%' OR g.customername LIKE '%$q%')";
    $sql="SELECT g.clientid,g.patrollocation,g.customername,g.sectors,g.department,g.req_guards,g.standby_guards,g.base_wage,g.location_type,g.weekly_hours_required,g.portforlio_manager,g.supervisor,g.remark,d.departmentname,s.name sector_name,r.rosterid,r.status roster_status,r.locked roster_locked FROM guardforceclient g LEFT JOIN department d ON g.department=d.departmentid LEFT JOIN sectors s ON g.sectors=s.id LEFT JOIN rostering r ON g.clientid=r.clientid AND r.id=$cid $w ORDER BY g.department,g.sectors,g.patrollocation";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$rows[]=$row;
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

case 'get_roster_data':
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $clid=isset($_GET['clientid'])?intval($_GET['clientid']):0;
    $cw=$clid>0?"AND r.clientid=$clid":'';
    $sql="SELECT rt.rstimeid,rt.rosterid,rt.employeeid,r.clientid,e.first_name,e.last_name,e.job_role,e.contract,e.department emp_dept,e.tin,e.fnpf,rt.montime_in,rt.montime_out,rt.mon_total,rt.mon_absent,rt.tuestime_in,rt.tuestime_out,rt.tues_total,rt.tues_absent,rt.wedtime_in,rt.wedtime_out,rt.wed_total,rt.wed_absent,rt.thurstime_in,rt.thurstime_out,rt.thurs_total,rt.thurs_absent,rt.fritime_in,rt.fritime_out,rt.fri_total,rt.fri_absent,rt.sattime_in,rt.sattime_out,rt.sat_total,rt.sat_absent,rt.sunttime_in,rt.sunttime_out,rt.sun_total,rt.sun_absent,rt.mon2ttime_in,rt.mon2time_out,rt.mon2_total,rt.total_hrs,rt.modified_by,rt.date_modified FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid LEFT JOIN employeemaster e ON rt.employeeid=e.employeeid WHERE r.id=$cid $cw ORDER BY r.clientid,e.first_name";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$rows[]=$row;
    $grouped=array();
    foreach($rows as $row){$c=$row['clientid'];if(!isset($grouped[$c]))$grouped[$c]=array();$grouped[$c][]=$row;}
    echo json_encode(array('ok'=>1,'data'=>$grouped,'flat'=>$rows)); break;

case 'get_pool_guards':
    $clid=intval(isset($_GET['clientid'])?$_GET['clientid']:0);
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $sql="SELECT ag.employeeid,e.first_name,e.last_name,e.job_role,e.contract FROM approved_guards ag JOIN employeemaster e ON ag.employeeid=e.employeeid WHERE ag.clientid=$clid ORDER BY e.first_name";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$rows[]=$row;
    if($cid>0&&count($rows)>0){
        $eids=implode(',',array_map(function($r){return intval($r['employeeid']);},$rows));
        $hrs=array();$r=$conn->query("SELECT rt.employeeid,SUM(rt.total_hrs) t FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid WHERE r.id=$cid AND rt.employeeid IN($eids) GROUP BY rt.employeeid");
        if($r)while($row=$r->fetch_assoc())$hrs[$row['employeeid']]=round(floatval($row['t']),2);
        foreach($rows as &$g)$g['period_hours']=isset($hrs[$g['employeeid']])?$hrs[$g['employeeid']]:0;
    }
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

case 'get_employees':
    $q=isset($_GET['search'])?$conn->real_escape_string($_GET['search']):'';
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $clid=intval(isset($_GET['clientid'])?$_GET['clientid']:0);
    $w="WHERE 1=1";
    if($q)$w.=" AND (e.first_name LIKE '%$q%' OR e.last_name LIKE '%$q%' OR e.employeeid LIKE '%$q%')";
    $sql="SELECT e.employeeid,e.first_name,e.last_name,e.job_role,e.contract,e.department,d.departmentname FROM employeemaster e LEFT JOIN department d ON e.department=d.departmentid $w ORDER BY e.first_name LIMIT 50";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$rows[]=$row;
    if($cid>0&&count($rows)>0){
        $eids=implode(',',array_map(function($r){return intval($r['employeeid']);},$rows));
        $hrs=array();$r=$conn->query("SELECT rt.employeeid,SUM(rt.total_hrs) t FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid WHERE r.id=$cid AND rt.employeeid IN($eids) GROUP BY rt.employeeid");
        if($r)while($row=$r->fetch_assoc())$hrs[$row['employeeid']]=round(floatval($row['t']),2);
        $atLoc=array();
        if($clid>0){$r=$conn->query("SELECT rt.employeeid FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid WHERE r.id=$cid AND r.clientid=$clid AND rt.employeeid IN($eids)");if($r)while($row=$r->fetch_assoc())$atLoc[$row['employeeid']]=1;}
        $pool=array();
        if($clid>0){$r=$conn->query("SELECT employeeid FROM approved_guards WHERE clientid=$clid AND employeeid IN($eids)");if($r)while($row=$r->fetch_assoc())$pool[$row['employeeid']]=1;}
        foreach($rows as &$g){$g['period_hours']=isset($hrs[$g['employeeid']])?$hrs[$g['employeeid']]:0;$g['at_location']=isset($atLoc[$g['employeeid']]);$g['is_pool']=isset($pool[$g['employeeid']]);}
    }
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

case 'create_roster':
    $clid=intval(isset($_POST['clientid'])?$_POST['clientid']:0);
    $cid=intval(isset($_POST['period_id'])?$_POST['period_id']:0);
    if(!$clid||!$cid){echo json_encode(array('error'=>'Missing params'));break;}
    $r=$conn->query("SELECT rosterid FROM rostering WHERE clientid=$clid AND id=$cid");
    if($r&&$r->num_rows>0){$row=$r->fetch_assoc();echo json_encode(array('ok'=>1,'rosterid'=>$row['rosterid']));break;}
    $r=$conn->query("SELECT department,sectors FROM guardforceclient WHERE clientid=$clid");
    $dept='';$sect='';if($r&&$row=$r->fetch_assoc()){$dept=$row['department'];$sect=$row['sectors'];}
    $conn->query("INSERT INTO rostering(clientid,id,status,department,sectors)VALUES($clid,$cid,'pending','$dept','$sect')");
    echo json_encode(array('ok'=>1,'rosterid'=>$conn->insert_id)); break;

case 'add_guard':
    $rid=intval(isset($_POST['rosterid'])?$_POST['rosterid']:0);
    $eid=intval(isset($_POST['employeeid'])?$_POST['employeeid']:0);
    if(!$rid||!$eid){echo json_encode(array('error'=>'Missing params'));break;}
    $r=$conn->query("SELECT rstimeid FROM rosteringtimesheet WHERE rosterid=$rid AND employeeid=$eid");
    if($r&&$r->num_rows>0){echo json_encode(array('error'=>'Already assigned'));break;}
    $conn->query("INSERT INTO rosteringtimesheet(rosterid,employeeid,total_hrs,modified_by,date_modified)VALUES($rid,$eid,0,'$userid',NOW())");
    echo json_encode(array('ok'=>1,'rstimeid'=>$conn->insert_id)); break;

case 'update_shift':
    $id=intval(isset($_POST['rstimeid'])?$_POST['rstimeid']:0);
    $day=isset($_POST['day'])?strtolower($_POST['day']):'';
    $ti=isset($_POST['time_in'])?$conn->real_escape_string($_POST['time_in']):'';
    $to=isset($_POST['time_out'])?$conn->real_escape_string($_POST['time_out']):'';
    $ab=isset($_POST['absent'])?$conn->real_escape_string($_POST['absent']):'';
    if(!$id||!isset($DM[$day])){echo json_encode(array('error'=>'Invalid params'));break;}
    $d=$DM[$day];
    if(!empty($ab)&&$ab!=='None'){
        $sql="UPDATE rosteringtimesheet SET {$d['i']}=NULL,{$d['o']}=NULL,{$d['t']}=0";
        if($d['a'])$sql.=",{$d['a']}='$ab'";
    }else{
        $hrs=calcH($ti,$to);
        $sql="UPDATE rosteringtimesheet SET {$d['i']}='$ti',{$d['o']}='$to',{$d['t']}=$hrs";
        if($d['a'])$sql.=",{$d['a']}='None'";
    }
    $sql.=",modified_by='$userid',date_modified=NOW() WHERE rstimeid=$id";
    $conn->query($sql); recalc($conn,$id);
    $r=$conn->query("SELECT total_hrs FROM rosteringtimesheet WHERE rstimeid=$id");
    $nt=0;if($r&&$row=$r->fetch_assoc())$nt=floatval($row['total_hrs']);
    echo json_encode(array('ok'=>1,'hours'=>isset($hrs)?$hrs:0,'total_hrs'=>$nt)); break;

case 'clear_shift':
    $id=intval(isset($_POST['rstimeid'])?$_POST['rstimeid']:0);
    $day=isset($_POST['day'])?strtolower($_POST['day']):'';
    if(!$id||!isset($DM[$day])){echo json_encode(array('error'=>'Invalid'));break;}
    $d=$DM[$day];
    $sql="UPDATE rosteringtimesheet SET {$d['i']}=NULL,{$d['o']}=NULL,{$d['t']}=0";
    if($d['a'])$sql.=",{$d['a']}='None'";
    $sql.=",modified_by='$userid',date_modified=NOW() WHERE rstimeid=$id";
    $conn->query($sql);recalc($conn,$id);
    $r=$conn->query("SELECT total_hrs FROM rosteringtimesheet WHERE rstimeid=$id");
    $nt=0;if($r&&$row=$r->fetch_assoc())$nt=floatval($row['total_hrs']);
    echo json_encode(array('ok'=>1,'total_hrs'=>$nt)); break;

case 'remove_guard':
    $id=intval(isset($_POST['rstimeid'])?$_POST['rstimeid']:0);
    if(!$id){echo json_encode(array('error'=>'Missing'));break;}
    $conn->query("DELETE FROM rosteringtimesheet WHERE rstimeid=$id");
    echo json_encode(array('ok'=>1)); break;

case 'check_clash':
    $eid=intval(isset($_REQUEST['employeeid'])?$_REQUEST['employeeid']:0);
    $cid=intval(isset($_REQUEST['period_id'])?$_REQUEST['period_id']:0);
    $day=isset($_REQUEST['day'])?strtolower($_REQUEST['day']):'';
    $exRid=intval(isset($_REQUEST['exclude_rosterid'])?$_REQUEST['exclude_rosterid']:0);
    if(!$eid||!$cid||!isset($DM[$day])){echo json_encode(array('clash'=>false));break;}
    $d=$DM[$day]; $ex=$exRid?"AND r.rosterid!=$exRid":'';
    $sql="SELECT r.rosterid,r.clientid,g.patrollocation,rt.{$d['t']} hrs FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid LEFT JOIN guardforceclient g ON r.clientid=g.clientid WHERE rt.employeeid=$eid AND r.id=$cid $ex AND rt.{$d['i']} IS NOT NULL AND rt.{$d['i']}!='' AND rt.{$d['i']}!='0000-00-00 00:00:00' AND rt.{$d['t']}>0";
    $clashes=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$clashes[]=$row;
    echo json_encode(array('clash'=>count($clashes)>0,'data'=>$clashes)); break;

case 'update_status':
    $rid=intval(isset($_POST['rosterid'])?$_POST['rosterid']:0);
    $st=isset($_POST['status'])?$conn->real_escape_string($_POST['status']):'';
    if(!$rid){echo json_encode(array('error'=>'Missing'));break;}
    $conn->query("UPDATE rostering SET status='$st',log=CONCAT(IFNULL(log,''),'\n$userid: $st at ".date('Y-m-d H:i:s')."') WHERE rosterid=$rid");
    echo json_encode(array('ok'=>1)); break;

case 'duplicate_roster':
    $fp=intval(isset($_POST['from_period'])?$_POST['from_period']:0);
    $tp=intval(isset($_POST['to_period'])?$_POST['to_period']:0);
    if(!$fp||!$tp){echo json_encode(array('error'=>'Missing'));break;}
    if(!$perms['admin']){echo json_encode(array('error'=>'Admin only'));break;}
    $r=$conn->query("SELECT rosterid,clientid,department,sectors FROM rostering WHERE id=$fp");
    $cnt=0;
    if($r)while($old=$r->fetch_assoc()){
        $chk=$conn->query("SELECT rosterid FROM rostering WHERE clientid={$old['clientid']} AND id=$tp");
        if($chk&&$chk->num_rows>0)continue;
        $conn->query("INSERT INTO rostering(clientid,id,status,department,sectors)VALUES({$old['clientid']},$tp,'pending','{$old['department']}','{$old['sectors']}')");
        $nrid=$conn->insert_id;
        $ts=$conn->query("SELECT employeeid FROM rosteringtimesheet WHERE rosterid={$old['rosterid']}");
        if($ts)while($emp=$ts->fetch_assoc())$conn->query("INSERT INTO rosteringtimesheet(rosterid,employeeid,total_hrs,modified_by,date_modified)VALUES($nrid,{$emp['employeeid']},0,'$userid',NOW())");
        $cnt++;
    }
    echo json_encode(array('ok'=>1,'count'=>$cnt)); break;

case 'get_employee_report':
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $pw=permWhere($perms);
    $sql="SELECT rt.employeeid,e.first_name,e.last_name,e.contract,e.department,e.job_role,e.tin,e.fnpf,d.departmentname,COUNT(DISTINCT r.clientid) loc_count,SUM(rt.mon_total) mon,SUM(rt.tues_total) tue,SUM(rt.wed_total) wed,SUM(rt.thurs_total) thu,SUM(rt.fri_total) fri,SUM(rt.sat_total) sat,SUM(rt.sun_total) sun,SUM(rt.mon2_total) mon2,SUM(rt.total_hrs) total FROM rosteringtimesheet rt JOIN rostering r ON rt.rosterid=r.rosterid JOIN guardforceclient g ON r.clientid=g.clientid JOIN employeemaster e ON rt.employeeid=e.employeeid LEFT JOIN department d ON e.department=d.departmentid WHERE r.id=$cid AND $pw GROUP BY rt.employeeid ORDER BY e.first_name";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc()){
        $ct=intval($row['contract']);$tn=0;$th=0;$td=0;
        foreach(array('mon','tue','wed','thu','fri','sat','sun') as $dk){
            $dh=floatval($row[$dk]);$ot=calcOT($dh,$ct);$tn+=$ot['n'];$th+=$ot['h'];$td+=$ot['d'];
        }
        $row['normal_hrs']=round($tn,2);$row['time_half_hrs']=round($th,2);$row['double_hrs']=round($td,2);
        $rows[]=$row;
    }
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

case 'get_location_report':
    $cid=intval(isset($_GET['period_id'])?$_GET['period_id']:0);
    $pw=permWhere($perms);
    $sql="SELECT g.clientid,g.patrollocation,g.customername,g.req_guards,g.base_wage,g.weekly_hours_required,g.sectors,g.department,d.departmentname,s.name sector_name,r.rosterid,r.status roster_status,COUNT(DISTINCT rt.employeeid) assigned,COALESCE(SUM(rt.total_hrs),0) sched_hrs FROM guardforceclient g LEFT JOIN department d ON g.department=d.departmentid LEFT JOIN sectors s ON g.sectors=s.id LEFT JOIN rostering r ON g.clientid=r.clientid AND r.id=$cid LEFT JOIN rosteringtimesheet rt ON r.rosterid=rt.rosterid WHERE(g.disable IS NULL OR g.disable=0)AND $pw GROUP BY g.clientid ORDER BY g.department,g.patrollocation";
    $rows=array();$r=$conn->query($sql);
    if($r)while($row=$r->fetch_assoc())$rows[]=$row;
    echo json_encode(array('ok'=>1,'data'=>$rows)); break;

default:
    echo json_encode(array('error'=>'Unknown action','actions'=>'get_periods,get_kpis,get_filters,get_locations,get_roster_data,get_pool_guards,get_employees,create_roster,add_guard,update_shift,clear_shift,remove_guard,check_clash,update_status,duplicate_roster,get_employee_report,get_location_report'));
}
$conn->close();