<?php

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

    function process()
    {
        /**************************************************************************
        *===================Support Tickets Closed Report=========================*
        *                                                                         *
        * This report will take all your support tickets and report those results.*
        * Author: Kevin Irving                                                    *
        * Created: Janurary 2005                                                  *
        *                                                                         *
        * Modified by: Duane Charter                                              *
        * Added link to the actual support ticket and correct problems            *
        * with the percentages.                                                   *
        *                                                                         *
        * Modified by: Jorge Gaviria                                              *
        * This report only will take all tickets closed and report results with   *
        * the percentages.                                                        *
        *                                                                         *
        * Modified by: Juan David                                                 *
        * Removed redundant code and modified the code in order to loads more     *
        * efficiently                                                             *
        ***************************************************************************/
        if (isset($_REQUEST['viewimage']) && $_REQUEST['viewimage'] == 1) {
            require_once 'classes/jpgraph/src/jpgraph.php';
            include ("classes/jpgraph/src/jpgraph_bar.php");
            include ("classes/jpgraph/src/jpgraph_line.php");
            $lArrayMonth = array();
            $lArrayLastWeek = array();
            $lArrayFillColor = array();
            
            // Create the graph.
            $graph = new Graph(320,150,"auto");
            $graph->SetScale("textlin");
            $graph->SetFrame(false);
            
            //*****************************
            //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
            
            // Create the bar plot
            $bplotLastYear = new BarPlot($lArrayMonth[1]);
            $bplotLastYear->SetFillColor("#cccccc");
            
            $bplot = new BarPlot($lArrayMonth[0]);
            $bplot->SetFillColor("orange");
            $bplot->value->HideZero();
            
            // values
            // (only update setting if it's different)
            if (isset($_GET['showvalues']) && $_GET['showvalues'] == 0) {
                if ($this->user->getShowDashboardGraphTotals() == 1) {
                    $this->user->setShowDashboardGraphTotals(0);
                    $this->user->save();
                }
            } else {
                if (!$this->user->getShowDashboardGraphTotals()) {
                    $this->user->setShowDashboardGraphTotals(1);
                    $this->user->save();
                }
                $bplot->value->Show();
            }
            
            // legend
            // (only update setting if it's different)
            if (isset($_GET['showlegend']) && $_GET['showlegend'] == 0) {
                if ($this->user->getShowDashboardGraphLegend() == 1) {
                    $this->user->setShowDashboardGraphLegend(0);
                    $this->user->save();
                }
            } else {
                if (!$this->user->getShowDashboardGraphLegend()) {
                    $this->user->setShowDashboardGraphLegend(1);
                    $this->user->save();
                }
                $bplotLastYear->SetLegend($lastYear);
                $bplot->SetLegend($thisYear);
            }
            
            // Create the grouped bar plot
            $gbplot = new GroupBarPlot(array($bplotLastYear,$bplot));
            $gbplot->SetWidth(0.9);
            $graph->Add($gbplot);
            
            $txt =new Text($this->user->lang("Closed Tickets"));
            $txt->Pos(55,0);
            $txt->SetFont(FF_FONT1,FS_BOLD);
            $txt->SetColor("black");
            $graph->AddText($txt);
            
            $graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD);
            $graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD);
            
            // Display the graph
            $graph->xaxis->SetTickLabels($month_minus);
            $graph->xaxis->HideTicks();
            $graph->SetMarginColor('white');
            $graph->ygrid->SetFill(true,'#EFEFEF@0.5','#BBCCFF@0.5');
            $graph->xgrid->Show();
            $graph->legend->SetShadow('gray@0.4',3);
            $graph->legend->SetPos(0.0,0.0,'right','top');
            $graph->yaxis->scale->SetGrace(30);
            
            $graph->Stroke();
            exit;
        }
        
        echo "<p><img src='index.php?fuse=reports&amp;report=Support_Tickets_Closed&amp;type=Support&amp;viewimage=1'></p>";
        
        //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;
    }
}
?>
