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

if (!isset($_GET["institution"])) {
    endrequest("Invalid request");
}

if ($_GET["institution"] != "all" && !isset($_GET["year"])) {
    endrequest("Invalid request");
}

$institution = institutions::get($_GET["institution"]);
if (!$institution) {
    endrequest("Invalid Institution ID");
}

if ($_GET["year"] == "All") {

    // Total Received Letter Count
    $response["received_count"] = letters::receivedcount($institution["id"]);
    // Updated Percentage
    $response["updated_percentage"] = letters::receivedcount($institution["id"]);

    // Completed 
    $response["completed_percentage"] = letters::receivedcount($institution["id"]);

    // Assigned v Non Assigned
    $query = "SELECT
    s.id,
    s.name,
    count(*) as count, 
    l.state,
    round (count(*)/(select count(*) from mis_letters where forwarded = :institution)*100) as percentage
FROM 
    mis_letters as l
RIGHT JOIN mis_letter_states as s on l.state = s.id
WHERE 
    l.forwarded = :institution
GROUP BY l.state
ORDER BY s.id";

    $response["summary"] = datatable::query($query, ["institution" => $_GET["institution"]])["data"];

    // Division ==========================

    $query_division = "SELECT 
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
WHERE
year(l.received) > 2022 AND
l.forwarded = :institution
ORDER BY progress desc";

    $response["division"] = datatable::query($query_division, ["institution" => $institution["id"]]);

    // Officers ==========================

    $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, 
u.state as user_state,
s.name as user_state_name,
IF(u.state = 1,true, null) as is_active,
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,
mis_user_states as s
WHERE 
l.assigned = u.id AND
u.institution = i.id AND
u.state = s.id AND
year(l.received) > 2022 AND
u.institution = :institution
GROUP BY l.assigned
ORDER BY 
    u.state,
    u.code";

    $response["officers"] = datatable::query($query_officers, ["institution" => $institution["id"]]);
} else {

    // Total Received Letter Count
    $response["received_count"] = letters::receivedcount($institution["id"], $_GET["year"]);
    // Updated Percentage
    $response["updated_percentage"] = letters::receivedcount($institution["id"]);

    // Completed 
    $response["completed_percentage"] = letters::receivedcount($institution["id"], $_GET["year"]);

    // Assigned v Non Assigned

    $assigned_query = "select * from mis_letters as l WHERE l.forwarded = :institution and assigned is null";
    $response["not-assigned"] = database::query($assigned_query, ["institution" => $_GET["institution"]])["count"];

    $query = "SELECT
    s.id,
    s.name,
    count(*) as count, 
    l.state,
    round (count(*)/(select count(*) from mis_letters where forwarded = :institution)*100) as percentage
FROM 
    mis_letters as l
RIGHT JOIN mis_letter_states as s on l.state = s.id
WHERE 
    l.forwarded = :institution AND
    year(l.received) = :year
GROUP BY l.state
ORDER BY s.id";

    $response["summary"] = datatable::query($query, ["institution" => $_GET["institution"], "year" => $_GET["year"]])["data"];

    // Division ==========================

    $query_division = "SELECT 
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
WHERE
year(l.received) = :year AND
l.forwarded = :institution
ORDER BY progress desc";

    $response["division"] = datatable::query($query_division, ["institution" => $institution["id"], "year" => $_GET["year"]]);

    // Officers ==========================

    $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, 
u.state as user_state,
s.name as user_state_name,
IF(u.state = 1,true, null) as is_active,
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,
mis_user_states as s
WHERE 
l.assigned = u.id AND
u.institution = i.id AND
u.state = s.id AND
year(l.received) = :year AND
u.institution = :institution
GROUP BY l.assigned
ORDER BY 
    u.state,
    u.code";

    $response["officers"] = datatable::query($query_officers, ["institution" => $institution["id"], "year" => $_GET["year"]]);
}

echo json_encode($response);
