<?php
include "../../../src/config.php";


$query_officers = "SELECT 
u.id, 
u.code, 
u.designation,
u.name, 
u.shortname,
u.institution,
i.shortname as institution_shortname, 
i.codename as institution_codename, 
l.assigned, 
count(*) as count, 
sum(l.state = 1) as notupdated, 
sum(l.state in (20,30)) as pending, 
sum(l.state in (10,11, 50)) as completed,
round(sum(l.state in (10,11,50))*100/count(*),1) as progress
FROM
mis_letters as l,
users as u,
institutions as i
WHERE 
l.assigned = u.id AND
u.institution = i.id AND
year(l.received) > 2023
GROUP BY l.assigned
ORDER BY progress desc";

$response = datatable::query($query_officers);

echo json_encode($response);
