<?php

class userstats
{

    private static function response($query, $userid = null)
    {
        $data = database::query($query, ["user" => $userid ? $userid : auth::user_id()]);
        if ($data['success']) {
            return $data['data'][0]["count"];
        } else {
            return "X";
        }
    }

    public static function letters($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_letters as l where l.assigned = :user", $userid);
    }

    public static function active_letters($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_letters as l where l.assigned = :user and (l.state is null or l.state in (1,2,20,30))", $userid);
    }

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

    public static function completed_letters($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_letters as l where l.assigned = :user and l.state in (10,11,50)", $userid);
    }


    public static function updated_letters($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_letters as l where l.assigned = :user and l.state > 1", $userid);
    }

    public static function filed_letters($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_letters as l where l.assigned = :user and l.file is not null", $userid);
    }

    public static function files($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_files as f where f.owner = :user", $userid);
    }

    public static function active_files($userid = null)
    {
        return self::response("SELECT count(*) as count from mis_files as f where f.owner = :user", $userid);
    }

    public static function letters_by_state($userid)
    {

        $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 and l.assigned = :user group by l.state", ["user" => $userid]);
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function active_letters_by_state($userid)
    {

        $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 and l.assigned = :user and l.state in (1,2,3,20,30) group by l.state order by id", ["user" => $userid]);
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function files_by_state($userid)
    {

        $data = database::query("SELECT s.id, s.name, count(*) as count FROM mis_files as f, mis_file_states as s where f.state = s.id and f.owner = :user and f.state in (1,10,20) group by f.state order by id", ["user" => $userid]);
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }

    public static function forwarded_files($userid)
    {

        $data = database::query("SELECT f.id, f.name, f.number FROM mis_files as f where f.state = 10 and f.owner = :user", ["user" => $userid]);
        if ($data['success']) {
            return $data['data'];
        } else {
            return false;
        }
    }
}
