<?
header('Access-Control-Allow-Origin: *');
//header('Content-Type: application/json');

$officer = $_GET['o'];
/*
if ($id){
    echo $id;
}
else {
    echo 'All Items';
}
*/


//$mysqli = new mysqli("localhost", "afxtech1_myori", "myori2017", "afxtech1_myori");
include_once("db_connect.php");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
    
//$id = '003';
//$query = "SELECT * FROM reports WHERE status = '0' ORDER BY rpt_txn ";
/* $query = "SELECT 
            officer as 'officer',
            SUM(case when status = '0' then 1 else 0 end) as 'assign',
            SUM(case when status != '0' &&  status != 'C' then 1 else 0 end) as 'inprogress',
            SUM(case when status = 'C' then 1 else 0 end) as 'completed'
        FROM reports 
        WHERE officer in ('officer1', 'officer2') 
        GROUP BY officer ";
*/

$query = "
SELECT 
b.officer as 'officer',
SUM(case when b.status = '0' then 1 else 0 end) AS 'assign',
SUM(case when b.status != '0' &&  b.status != 'C' then 1 else 0 end) AS 'inprogress',
SUM(case when b.status = 'C' then 1 else 0 end) AS 'completed',

(
SELECT count(*)
FROM reports
WHERE officer = b.officer
AND assigndt > 0
AND status IN ('0')
AND TIMESTAMPDIFF(DAY, assigndt,  CURDATE()) > 5
) AS 'aging1',

(
SELECT count(*)
FROM reports
WHERE officer = b.officer
AND assigndt > 0
AND status NOT IN ('0', 'C')
AND TIMESTAMPDIFF(DAY, assigndt,  CURDATE()) > 14
) AS 'aging2'

FROM reports b
WHERE b.officer IN ('officer1', 'officer2') 
GROUP BY b.officer
";
$dbresult = $mysqli->query($query);


//echo 'Hello...here1';
//die();

$mysqli->close();

$data = [];

while($row = $dbresult->fetch_array(MYSQLI_ASSOC)){
    $dt = date_create($row['rpt_date']);
    $data[] = array(
        'officer' => $row['officer'],
        'assign' => $row['assign'],
        'inprogress' => $row['inprogress'],
        'completed' => $row['completed'],
        'aging1' => $row['aging1'],
        'aging2' => $row['aging2'],        
    );
}

if($dbresult){
    $result = json_encode($data);              
}
else {
    $result = "{success:false}";
}

echo $result;

?>
