<?php

class stats
{
    private static function response($query)
    {
        $data = database::query($query);
        if ($data['success']) {
            return $data['data'][0]["count"];
        } else {
            return "X";
        }
    }

    public static function online()
    {
        return self::response("Select count(id) as count from users as u where (u.lastseen > NOW() - INTERVAL 15 MINUTE)");
    }

    public static function delayed_letters()
    {
        return self::response("SELECT count(*) as count from mis_letters as l where (l.state is null or l.state in (1,2,3,20,30)) and DATEDIFF(CURDATE(),l.date) > 30");
    }



    public static function delayed_letters_by_division()
    {

        $data = database::query("SELECT l.forwarded, i.name, i.shortname, i.codename, SUM(DATEDIFF(CURDATE(),l.date) > 7) as 1week, SUM(DATEDIFF(CURDATE(),l.date) > 14) as 2weeks, SUM(DATEDIFF(CURDATE(),l.date) > 21) as 3weeks, SUM(DATEDIFF(CURDATE(),l.date) > 30) as month, SUM(DATEDIFF(CURDATE(),l.date) > 60) as 2months from mis_letters as l, institutions as i where year(l.received) > 2023 AND l.forwarded = i.id AND (l.state is null or l.state in (1,2,3,20,30)) group by l.forwarded order by 2weeks desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function delayed_letters_all()
    {

        $data = database::query("SELECT SUM(DATEDIFF(CURDATE(),l.date) > 7) as 1week, SUM(DATEDIFF(CURDATE(),l.date) > 14) as 2weeks, SUM(DATEDIFF(CURDATE(),l.date) > 21) as 3weeks, SUM(DATEDIFF(CURDATE(),l.date) > 30) as month, SUM(DATEDIFF(CURDATE(),l.date) > 60) as 2months from mis_letters as l where year(l.received) > 2023 AND (l.state is null or l.state in (1,2,3,20,30))");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }
}
