<?php

class reports
{

    public static function total_case_count()
    {
        $data =  database::query("SELECT count(*) as count FROM cases");
        if ($data['success']) {
            return $data['data'][0]["count"];
        } else {
            return false;
        }
    }


    public static function total_completed_percentage()
    {

        $data =  database::query("SELECT round(sum(l.state IN (10,11,50))/count(*)*100) as completed_percentage FROM mis_letters as l where year(l.received) > 2023");
        if ($data['success']) {
            return $data['data'][0]["completed_percentage"];
        } else {
            return false;
        }
    }


    public static function daily_average()
    {
        $data =  database::query("SELECT round(count(id)/count(DISTINCT received),2) as average FROM mis_letters as l where year(l.received) > 2023");
        if ($data['success']) {
            return $data['data'][0]["average"];
        } else {
            return false;
        }
    }

    public static function count_by_day()
    {

        $data =  database::query("SELECT year(received) as y, month(received) as m,day(received) as d,count(id) as count FROM `mis_letters` GROUP by d,m,y");

        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }


    public static function daily_count($date)
    {

        $data =  database::query("SELECT count(id) as count FROM mis_letters WHERE received = :date", ["date" => $date]);
        if ($data['success']) {
            return $data['data'][0]["count"];
        } else {
            return false;
        }
    }

    public static function monthly_count()
    {

        $data =  database::query("SELECT year(received) as y, MONTHNAME(received) as m, count(id) as count FROM `mis_letters` GROUP by y,m");

        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function letters_by_forwarded()
    {

        $data =  database::query("SELECT i.name, i.shortname,i.codename, count(l.id) as count FROM mis_letters as l LEFT JOIN institutions as i on l.forwarded = i.id where year(l.received) > 2022 group by l.forwarded order by count desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function letters_updateprogress()
    {

        $data =  database::query("SELECT i.name, i.shortname,i.codename, count(l.id) as count, round(sum(l.state != 1)/count(l.id)*100) as unknown_percentage FROM mis_letters as l, institutions as i WHERE l.forwarded = i.id group by l.forwarded order by unknown_percentage desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function letters_complete_progress()
    {

        $data =  database::query("SELECT i.name, i.shortname, i.codename, count(l.id) as count, round(sum(l.state IN(50))/count(l.id)*100) as unknown_percentage FROM mis_letters as l, institutions as i WHERE l.forwarded = i.id group by l.forwarded order by unknown_percentage desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }


    public static function letters_by_received_method()
    {

        $data =  database::query("SELECT src.name, count(l.id) as count FROM mis_letters as l, mis_letter_sources as src where l.source = src.id group by l.source order by count desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function letters_by_state()
    {

        $data =  database::query("SELECT s.id, s.name, count(*) as count FROM mis_letters as l, mis_letter_states as s where l.state = s.id group by l.state");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }


    public static function letters_by_assigned()
    {

        $data =  database::query("SELECT u.name, u.shortname, count(l.id) as count FROM mis_letters as l, users as u where l.assigned = u.id group by l.assigned order by count desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function letters_by_recorded()
    {

        $data =  database::query("SELECT u.name, u.shortname, u.code, count(l.id) as count FROM mis_letters as l, users as u where l.user = u.id group by l.user order by count desc");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function count_by_institution()
    {

        $data =  database::query("select i.ins_id, ins_name, x.count from (SELECT relevant_ins, count(*) as count FROM `cases` group by relevant_ins) as x, institutions as i where i.ins_id = x.relevant_ins");
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }
}
