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

// Overall Progress
$lettercounts = analyst::letters_by_forwarded();

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

$response["summary"] = datatable::query($query)["data"];
// ==============================================================================================================================

$query_late = "SELECT
count(*) as all_count,
sum(d.duration < 14) as less14,
sum(d.duration > 14) as 2to3weeks,
sum(d.duration > 30) as monthold
from (SELECT DATEDIFF(NOW(), l.received) as duration FROM `mis_letters` as l) as d;";


$late_response = datatable::query($query_late)["data"][0];
$response["late"] = [
    "allcount" => 14,
    "data" => [
        [
            "name" => "සති දෙකකට අඩු",
            "count" => $late_response["less14"]
        ],
        [
            "name" => "සති දෙකකට වැඩි",
            "count" => $late_response["2to3weeks"]
        ],
        [
            "name" => "මාසයකට වැඩි",
            "count" => $late_response["monthold"]
        ]
    ]
];


echo json_encode($response);
