<?php
 
    function GetCaseStatistics()
    {
        $arr = array();
        for ($i = 1; $i <= 12; $i++)
        {
            $arr["opened"][$i] = 0;
            $arr["resolved"][$i] = 0;
            $arr["not_resolved"][$i] = 0;
        }
        
        $date1 = mktime(0,0,0,1,1,date("Y"));		//return timestamps for the 01 JAN for the current year
        $date2 = mktime(0,0,0,12,31,date("Y"));     //return timestamps for the 31 DEC for the current year
        $date_condtion = " AND case_date_close >= '".$date1."' AND case_date_close <= '".$date2."' ";

        $sql = "SELECT monthno, sum(opened) as opened, sum(resolved) as resolved, sum(not_resolved) as not_resolved
        FROM (
        SELECT MONTH(FROM_UNIXTIME(case_date_open,'%Y-%m-%d')) as monthno, count(case_id) as opened, 0 as resolved, 0 as not_resolved
        FROM `case`
        WHERE case_date_close >= '".$date1."' AND case_date_close <= '".$date2."'
        GROUP BY MONTH(FROM_UNIXTIME(case_date_open,'%Y-%m-%d'))
        union all
        SELECT MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d')) as monthno, 0 as opened, count(case_id)  as resolved, 0 as not_resolved
        FROM `case`
        WHERE case_status = 'Resolved' 
        ".$date_condtion."
        GROUP BY MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d'))
        union all
        SELECT MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d')) as monthno, 0 as opened, 0 as resolved, count(case_id) as not_resolved
        FROM `case`
        WHERE case_status = 'Not resolved' 
        ".$date_condtion."
        GROUP BY MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d'))
        ) as qry
        GROUP BY monthno";

        $tmprs = mysql_query($sql);

        while ($tmprow = mysql_fetch_array($tmprs))
        {
            $monthno = $tmprow["monthno"];
            $opened = $tmprow["opened"];
            $resolved = $tmprow["resolved"];
            $not_resolved = $tmprow["not_resolved"];
            
            $arr["opened"][$monthno] = $opened;
            $arr["resolved"][$monthno] = $resolved;
            $arr["not_resolved"][$monthno] = $not_resolved;
        }
  
        return json_encode($arr);
    }
    
    
    function GetEarning()
    {
        $arr = array();
        for ($i = 1; $i <= 12; $i++)
        {
            $arr[$i] = 0;
        }
        
        $date1 = mktime(0,0,0,1,1,date("Y"));		//return timestamps for the 01 JAN for the current year
        $date2 = mktime(0,0,0,12,31,date("Y"));     //return timestamps for the 31 DEC for the current year
        $date_condtion = " and case_date_close >= '".$date1."' and case_date_close <= '".$date2."' ";

        
        $sql = "SELECT monthno, sum(earned) as earned, sum(spent) as spent
        FROM (
        SELECT MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d')) as monthno, sum(case_earned) as earned, 0 as spent
        FROM `case`
        WHERE case_status = 'Resolved' 
        ".$date_condtion."
        GROUP BY MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d'))
        union all
        SELECT MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d')), 0 as earned, sum(case_spent) as spent
        FROM `case`
        WHERE case_status in ('Resolved','Not resolved')
        ".$date_condtion."
        GROUP BY MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d'))
        ) as qry
        GROUP BY monthno
        ORDER BY monthno";
        
        $tmprs = mysql_query($sql);

        while ($tmprow = mysql_fetch_array($tmprs))
        {
            $monthno = $tmprow["monthno"];
            $earning = $tmprow["earned"] - $tmprow["spent"];
            $arr[$monthno] = $earning;
        }

        return json_encode($arr);
    }
    
    
    function GetAvgTime()
    {
        $arr = array();
        for ($i = 1; $i <= 12; $i++)
        {
            $arr[$i] = 0;
        }
        
        $date1 = mktime(0,0,0,1,1,date("Y"));		//return timestamps for the 01 JAN for the current year
        $date2 = mktime(0,0,0,12,31,date("Y"));     //return timestamps for the 31 DEC for the current year
        $date_condtion = " and case_date_close >= '".$date1."' and case_date_close <= '".$date2."' ";


        $sql = "SELECT MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d')) AS monthno, AVG(FROM_UNIXTIME(case_date_close-case_date_open,'%Y-%m-%d')) AS avgtime
            	FROM `case`
            	WHERE case_status = 'Resolved' 
           		".$date_condtion."
            	GROUP BY MONTH(FROM_UNIXTIME(case_date_close,'%Y-%m-%d'))";
        //return $sql;
        $tmprs = mysql_query($sql);

        while ($tmprow = mysql_fetch_array($tmprs))
        {
            $monthno = $tmprow["monthno"];
            $avgtime = $tmprow["avgtime"];
            $arr[$monthno] = $avgtime;
        }

        return json_encode($arr);
    }