<?php

/**
* @package Reports
*/
class Support_Tickets_Closed extends Report
{
    var $m_hasgraph = true;

    function process()
    {


		if (!isset($_REQUEST['passedyear'])){
            $currentYear = date("Y");
        }else{
            $currentYear = $_REQUEST['passedyear'];
        }
        
        $thisYear = Date("Y");

        //DETERMINE IF THIS IS A GRAPH CALL
        if (!get_magic_quotes_gpc()) {
            $graphdata = addslashes(@$_GET['graphdata']);
        } else {
            $graphdata = @$_GET['graphdata'];    
        }     


        if($graphdata){
            $lArrayMonth = array();
            $lArrayLastWeek = array();
            $lArrayFillColor = array();
            
            //*****************************
            //Get count of users based on minus x from tomonth that have signed up
            //*****************************
            
            for($y=0; $y<=1; $y++){
                for($m=6; $m>=0; $m--){
                    $month_minus_TS = mktime (0,0,0,date("m")-$m,1,  date("Y")-$y);
                    $minus_date = strftime("%Y-%m-%d",$month_minus_TS); //used for the sql
                    $query = "SELECT COUNT(id) FROM troubleticket WHERE (MONTH(lastlog_datetime) = MONTH('$minus_date')) AND (YEAR(lastlog_datetime) = YEAR('$minus_date')) AND status=-1";
                    $result = $this->db->query($query);
                    $tArray = $result->fetch();
                    $lArrayMonth[$y][] = $tArray[0];
                }
            }
            
            $month_minus = array();
            for($m=6; $m>=0; $m--){
                $month_minus_TS = mktime (0,0,0,date("m")-$m,1,  date("Y")-$y);
                //set month name based on language file
                $month_minus[] = $this->user->lang(date("M",$month_minus_TS));
            }

            //*******************
            //Done with Last Year
            //*******************
            
            $lArrayFillColor[count($lArrayMonth[0])-1]='orange';
            
            
            $thisYear = mktime (0,0,0,date("m"),1,  date("Y"));
            $thisYear = strftime("%Y",$thisYear); //used for the legend
            
            $lastYear = mktime (0,0,0,date("m"),1,  date("Y")-1);
            $lastYear = strftime("%Y",$lastYear); //used for the legend
            

            //*******************
            //Done with Last Year
            //*******************
            
            $lArrayFillColor[count($lArrayMonth[0])-1]='orange';
            
            
            $thisYear = mktime (0,0,0,date("m"),1,  date("Y"));
            $thisYear = strftime("%Y",$thisYear); //used for the legend
            
            $lastYear = mktime (0,0,0,date("m"),1,  date("Y")-1);
            $lastYear = strftime("%Y",$lastYear); //used for the legend
              
              
            //print_r($lArrayMonth);  
            echo $this->GraphData($lArrayMonth,$month_minus,$thisYear, $lastYear); 
            exit;
        }
                
        //create a lazy loading chart so that our showgraph will call ajax 
        //call to retrieve chart and populate it after load.  This should 
        //increase performance.         
        $url = "index.php?noheaderfooter=1&graphdata=1&passedyear=".$currentYear."&view=".$_REQUEST['view']."&fuse=".$_REQUEST['fuse']."&report=".urlencode($_REQUEST['report'])."&type=".urlencode($_REQUEST['type']);
        $this->ShowGraph(0,$url);
        
        //Class Initialize
        $this->SetDescription($this->user->lang('Displays all support tickets closed.(click on a date to view all tickets for that year)'));
        
        //Select the total tickets for use later
        $sql = "SELECT COUNT(*) AS total FROM troubleticket WHERE status = -1";
        $result = $this->db->query($sql);
        
        while($row = $result->fetch()){
            $totaltickets = $row['total'];
        }
        unset($result);
        
        //Now we query for all support tickets all time
        $sql = "SELECT "
              ."DATE_FORMAT( lastlog_datetime, '%Y' ) AS date_opened, "
              ."lastlog_datetime AS date_unformatted, "
              ."COUNT(*), "
              ."CONCAT(FORMAT((COUNT(*)/$totaltickets)*100,0),'%') AS percent "
              ."FROM troubleticket "              
              ."WHERE status = -1 "              
              ."GROUP BY DATE_FORMAT( lastlog_datetime, '%Y' ) "
              ."ORDER BY DATE_FORMAT( lastlog_datetime, '%Y' ) ASC";
            
        $result = $this->db->query($sql);
        if($result->getNumRows() >0) {
        
            $prev_year = false;
            
            while (list($year,$unformatted,$tickets,$ticketsPercent) = $result->fetch()){
                
                if ($prev_year == false) $diff = 'n/a';
                else if ($prev_year > $tickets) $diff = "<font color='green'>".($prev_year - $tickets)."</font>";
                else if ($prev_year < $tickets) $diff = "<font color='red'>".($tickets - $prev_year)."</font>";
                else $diff = 0;
                
                $basePage = $_SERVER["REQUEST_URI"];
                $nPos = strpos($basePage, "&year=");
                if ( $nPos === false ) {
                    $basePage .= "&year=";
                } else {
                    $basePage = substr($basePage, 0, $nPos) . "&year=";
                }
                $yearLi = substr($unformatted, 0, 4);
                $year = "<a href=\"" . $basePage . $yearLi . "\">" . $year . "</a>";
                $aGroup[] = array($year,$tickets,$diff,$ticketsPercent);
                $prev_year = $tickets;
            }
            
            //add final group
            if (isset($aGroup)){
                $this->Add($aGroup,"",array($this->user->lang('Year'),$this->user->lang('Tickets Closed'),$this->user->lang('Difference'),$this->user->lang('Total Percent')));
                unset($aGroup);
            }
            
            if(@$_GET['year']) {
            //Now we query for all support tickets in this year
                $sql = "SELECT users.firstname, users.lastname, t.id, t.subject, t.datesubmitted, t.lastlog_datetime "
                      ."FROM troubleticket t "
                      ."LEFT JOIN users ON ( users.id = t.assignedtoid ) "
                      ."WHERE SUBSTRING( t.lastlog_datetime, 1, 4 ) =? "
                      ."AND t.status = -1 "
                      ."ORDER BY t.lastlog_datetime";
                    
                $result = $this->db->query($sql, $_GET['year']);
                    
            
                //initialize
                $prevMonth = "-1";
                $monthTotal = 0;
                $yearTotal = 0;
                while (list($assignedToFirst,$assignedToLast,$ttID,$ttSubject,$ttDateSubmitted,$ttLastLog_DateTime) = $result->fetch()){
                    $thisMonth = date("n",strtotime($ttLastLog_DateTime));
                    $assignedTo = $assignedToFirst . " " . $assignedToLast;
                     
                    if($prevMonth!=$thisMonth){
                        if (isset($aGroup)){
                            
                            $aGroup[] = array("--------","-------","--------","--------","--------","--------");
                            $aGroup[] = array("",$this->user->lang('Total This Month'),$monthTotal,"","","");
                            //add previous group before getting next group
                            $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Ticket ID'),$this->user->lang('Subject'),$this->user->lang('Assigned To'),$this->user->lang('Date Submitted'),$this->user->lang('Date Closed'),$this->user->lang('Total Elapsed')));
                            unset($aGroup);
                        }
                        $aGroup = array();
                        $monthTotal = 0;
                        $prevMonth = $thisMonth;
                        $prevMonthName = date("F Y",strtotime($ttLastLog_DateTime));
                    }
                     
                     
                    $monthTotal++;
                    $yearTotal ++;
                    $linkedTicket = "<a href=index.php?fuse=support&view=ViewTicketDetails&ticketID=".$ttID.">".$ttID."</a>";
                    
                    $timeElapsed = $this->getDateDifference($ttDateSubmitted, $ttLastLog_DateTime);
                    
                    $aGroup[] = array($linkedTicket,$ttSubject,$assignedTo,date('j M Y',strtotime($ttDateSubmitted)),date('j M Y',strtotime($ttLastLog_DateTime)),$timeElapsed);
                }
            
                //add final group
                if (isset($aGroup)){
                    $aGroup[] = array("--------","-------","--------","--------","--------","--------");
                    $aGroup[] = array("",$this->user->lang('Total This Month'),$monthTotal,"","","");
                    $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Ticket ID'),$this->user->lang('Subject'),$this->user->lang('Assigned To'),$this->user->lang('Date Submitted'),$this->user->lang('Date Closed'),$this->user->lang('Total Elapsed')));
                    unset($aGroup);
                }
            
                $aGroup[] = array("","","<b>".$yearTotal."</b>","","","");
                $this->Add($aGroup,$this->user->lang('Total for')." $_GET[year]",array("","","","","",""));
                
                $this->Show();
            
            } else {
            
                //Display Report
                $this->Show();
            
            }
        } else {
            $aGroup[] = array($this->user->lang('No tickets closed'));
            $this->Add($aGroup,"",array(""));
            $this->Show();
        }
    }

    function getDateDifference($ttDateSubmitted, $ttLastLog_DateTime)
    {
        $returnString = "";
        
        $tWeeks = 0; $tDays = 0; $tHours = 0;
        $datearray = NE_Lib::date_diff_hrs(date('Y-m-d H:i:s',strtotime($ttDateSubmitted)),date('Y-m-d H:i:s',strtotime($ttLastLog_DateTime)));
        
        if ($datearray['h'] > 0) { 
            $tHours += abs($datearray['h']); 
            if($tHours >= 24) {
                $tDays = intval($tHours / 24);
                $tHours -= $tDays*24;
                if($tDays >= 7) {
                    $tWeeks = intval($tDays / 7);
                    $tDays -= $tWeeks*7;	
                }
            }
        }

        if($tWeeks > 0) {
            if($tWeeks > 1) {
                $returnString .= $tWeeks. ' '.$this->user->lang('weeks').' ';
            } else {
                    $returnString .= $tWeeks.' '.$this->user->lang('week' ).' ';
            }
        }
        if($tDays > 0) {
            if($tDays > 1) {
                $returnString .= $tDays. ' '.$this->user->lang('days').' ';
            } else {
                $returnString .= $tDays.' '.$this->user->lang('day').' ';
            }
        }
        if ($tHours > 0) {
            if ($tHours > 1) { 
                $returnString .= $tHours.' '.$this->user->lang('hrs').' '; 
            } else { 
                $returnString .= ' 1 '.$this->user->lang('hr').' '; 
            }
            if ($datearray['m']==1) {
                $returnString .= ' '.abs($datearray['m']).' min';
            } else {
                $returnString .= ' '.abs($datearray['m']).' mins';
            }
        } else {
            if ($datearray['m']==1) {
                $returnString .= abs($datearray['m']).' min';
            } else {
                $returnString .= abs($datearray['m']).' mins';
            }
        }

        return $returnString;
    }
    
    function GraphData($aMonths,$monthNames,$thisYear,$lastYear){
            
        // These headers must be set or IE will show an error.
        // IE + SSL = BAD
        // http://www.fusioncharts.com/KB/article.aspx?id=10091
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');
            
        $strXML = "";
        
        $showborder="showborder='0'";
        $bgColor="bgColor='FFFFFF'";
        $title = "";
        $subcaption = "";
        
        if($this->viewingFromDashboard()){
            //do not want to see title in dashboard since we already show from dropdown
            $legend="showLegend='0'";
            $labelstep="labelStep='3'";
            //universal link to the report that can be used in any report
            $clickurl="clickURL='index.php?fuse=".$_REQUEST['fuse']."&report=".urlencode($_REQUEST['report'])."&type=".urlencode($_REQUEST['type'])."&view=".$_REQUEST['view']."'";
        }else{
            $clickurl="";
            $legend="";
            $labelstep="";
        }              
        
        $strXML  = "<chart ".$clickurl." ".$bgColor." ".$showborder." ".$labelstep." ".$legend." useRoundEdges='1' caption='".$title."' subcaption='".$subcaption."' xAxisName='' yAxisName='' yAxisMinValue='' numberPrefix='' showValues='0' alternateHGridColor='FCB541' alternateHGridAlpha='20' divLineColor='FCB541' divLineAlpha='50' canvasBorderColor='666666' baseFontColor='666666' lineColor='FCB541'>";
        
        
        $strXML  .= "<categories>\n";
        foreach($monthNames as $month){
           $strXML  .= "<category label='".$month."'/>\n";
        }
        $strXML  .= "</categories>\n\n"; 
        
        $lastYearXML = "<dataset seriesName='".$lastYear."' color='#DFC329'>\n";
        $thisYearXML = "<dataset seriesName='".$thisYear."' color='#6699FF'>\n";
        
        for($x=0;$x<=6;$x++){
            $lastYearXML .= "<set value='".$aMonths[1][$x]."' />\n";
            $thisYearXML .= "<set value='".$aMonths[0][$x]."' />\n";                
        }     
        
        $lastYearXML .= "</dataset>\n";
        $thisYearXML .= "</dataset>\n";        
                
        if($this->viewingFromDashboard()){ 
        	$strXML .= $thisYearXML;
        }else{
        	$strXML .= $lastYearXML.$thisYearXML;
        }
        
    	$strXML .= "</chart>";
    	
    	

        return $strXML;        
        
        
    }            
    
}
?>
