<?php

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

    function process()
    {
        /**************************************************************************
        *===================Support Tickets Service Rating Report=================*
        *                                                                         *
        * This report will take all your support tickets Rating                   *
        * and report those results.                                               *
        *                                                                         *
        ***************************************************************************/
        $leyends=array($this->user->lang('No Rate'),$this->user->lang('Excellent'),$this->user->lang('Good enough'),$this->user->lang('Not great'),$this->user->lang('Poor'));
        
        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
            //*****************************
            if(isset($_GET['year']) && isset($_GET['month'])){
                $year=$_GET['year'];
                $month=$_GET['month'];
            }else{
                $year=date("Y");
                $month = date("m");
            }
                
            $month_minus_TS = mktime (0,0,0,$month,1,  $year);
            $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 AND rate>0 ";
            $result = $this->db->query($query);
            $countRates = $result->fetch();   
            for($y=0; $y<5; $y++){
                    $query = "SELECT COUNT(id) FROM troubleticket WHERE (MONTH(lastlog_datetime) = MONTH('$minus_date')) AND (YEAR(lastlog_datetime) = YEAR('$minus_date')) AND status=-1 AND rate='$y' ";
                    $result = $this->db->query($query);
                    $tArray = $result->fetch();
                    $lArrayMonth[$y][] = $countRates[0]? ($tArray[0]/$countRates[0])*100 : 0;
            }
            
            $month_minus = array();
            $month_minus[] = $this->user->lang(date("M",$month_minus_TS));

            //*******************
            //Done with Last Year
            //*******************
            
            $lArrayFillColor=array('#F08080','orange','gold','#4C7FBD','#cccccc');
                        
            // Create the bar plot
            $bplot = new BarPlot($lArrayMonth[0]);
            $bplot->SetFillColor($lArrayFillColor[0]);
            $bplot->value->HideZero();
            
            $b1plot = new BarPlot($lArrayMonth[1]);
            $b1plot->SetFillColor($lArrayFillColor[1]);
            $b1plot->value->HideZero();
            
            $b2plot = new BarPlot($lArrayMonth[2]);
            $b2plot->SetFillColor($lArrayFillColor[2]);
            $b2plot->value->HideZero();
            
            $b3plot = new BarPlot($lArrayMonth[3]);
            $b3plot->SetFillColor($lArrayFillColor[3]);
            $b3plot->value->HideZero();
            
            $b4plot = new BarPlot($lArrayMonth[4]);
            $b4plot->SetFillColor($lArrayFillColor[4]);
            $b4plot->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();
                $b1plot->value->Show();
                $b2plot->value->Show();
                $b3plot->value->Show();
                $b4plot->value->Show();
                
                $b1plot->value->SetFormat('%0.0f%%');
                $b2plot->value->SetFormat('%0.0f%%');
                $b3plot->value->SetFormat('%0.0f%%');
                $b4plot->value->SetFormat('%0.0f%%');
                

            }
            
            // 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();
                }
                
                $bplot->SetLegend($leyends[0]);
                $b1plot->SetLegend($leyends[1]);
                $b2plot->SetLegend($leyends[2]);
                $b3plot->SetLegend($leyends[3]);
                $b4plot->SetLegend($leyends[4]);
            }
            

            // Create the grouped bar plot
            $gbplot = new GroupBarPlot(array($b1plot,$b2plot,$b3plot,$b4plot));
            $gbplot->SetWidth(0.3);
            $graph->Add($gbplot);
            
            $txt =new Text($this->user->lang("Service Rating"));
            $txt->Pos(0,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->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->legend->SetColumns(1);
            $graph->yaxis->scale->SetGrace(20);
            
            $graph->Stroke();
    
            exit;
        }
        
         //Display Report
          echo "\n\n<script type='text/javascript'>";
        
          echo "\n\n";
          echo "function ChangeDate(strYear,strMonth){
                location.href='index.php?fuse=reports&view=ViewReport&report=Support_Service_Rating&type=Support&year='+strYear+'&month='+strMonth;
                }";
          echo "\n\n";
          echo "</script>";
          echo "\n\n";
          $thisYear=date("Y");
          $thisMonth=date("m");
          if(isset($_GET['year']))
            $currentYear=$_GET['year'];
          else $currentYear=$thisYear;
          if(isset($_GET['month']))
            $currentMonth = $_GET['month'];
          else $currentMonth = $thisMonth;
          echo "<form id='reportdropdown' method='GET'>";
          echo "<table border=0 cellpadding=2 cellspacing=0 width=454>";     
          echo "<tr><td align=right><b>".$this->user->lang('Select Year')."</b>:&nbsp;&nbsp;";
          echo "<select id='year' name='year' onChange='ChangeDate(this.value,$currentMonth);'>";
        
          //Create based on number of years of data available
          for($x=0;$x<4;$x++){
              $value=$thisYear-$x;
        	  if($currentYear == "$value"){
        		echo "<option value='$value' SELECTED>$value&nbsp;&nbsp;&nbsp;</option>";
        	  }else{
        		echo "<option value='$value'>$value&nbsp;&nbsp;&nbsp;</option>";
        	  }
          }
          echo "</select></td></tr>";
          echo "<tr><td align=right><b>".$this->user->lang('Select Month')."</b>:&nbsp;&nbsp;";
          echo "<select id='month' name='month' onChange='ChangeDate($currentYear,this.value);'>";
          
          for($x=$thisMonth;$x>0;$x--){
              $month_minus_TS = mktime (0,0,0,$x,1, $currentYear);
        	  if($currentMonth == "$x"){
        		echo "<option value='$x' SELECTED>".$this->user->lang(date("M",$month_minus_TS))."&nbsp;&nbsp;&nbsp;</option>";
        	  }else{
        		echo "<option value='$x'>".$this->user->lang(date("M",$month_minus_TS))."&nbsp;&nbsp;&nbsp;</option>";
        	  }
          }
          echo "</select></td></tr></table>";
          echo "</form>";
          echo '<br><br>';
        
        echo "<p><img src='index.php?fuse=reports&amp;report=Support_Service_Rating&amp;type=Support&year=".$currentYear."&amp;month=$currentMonth&amp;viewimage=1'></p>";
        //Class Initialize
        $this->SetDescription($this->user->lang('Displays Service Rating.(click on a date to view Rating tickets for that year and month)'));
        
        //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'];
        }
        $sql ="SELECT COUNT(*) AS total FROM troubleticket WHERE status = -1 AND rate>0";
        $result = $this->db->query($sql);
        if($row = $result->fetch()){
            $totalrating = $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 AND rate>0 "              
              ."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 Rating'),$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, troubleticket_type.name, t.subject, t.datesubmitted, t.lastlog_datetime, t.rate  "
                      ."FROM troubleticket t "
                      ."LEFT JOIN users ON ( users.id = t.assignedtoid ) "
                      ."LEFT JOIN troubleticket_type ON ( troubleticket_type.id = t.messagetype ) "
                      ."WHERE SUBSTRING( t.lastlog_datetime, 1, 4 ) =? "
                      ."AND t.status = -1 and t.rate>0 "
                      ."ORDER BY t.lastlog_datetime, t.messagetype";
                    
                $result = $this->db->query($sql, $_GET['year']);
                    
            
                //initialize
                $prevMonth = "-1";
                $monthTotal = 0;
                $yearTotal = 0;
                $avgrate=0;
                while (list($assignedToFirst,$assignedToLast,$ttID,$ttType,$ttSubject,$ttDateSubmitted,$ttLastLog_DateTime,$ttRate) = $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,"",$this->user->lang('Average Rate'),$this->user->lang($leyends[round($avgrate/$monthTotal)]));
                            //add previous group before getting next group
                            $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Ticket ID'),$this->user->lang('Ticket Type'),$this->user->lang('Subject'),$this->user->lang('Assigned To'),$this->user->lang('Total Elapsed'),$this->user->lang('Rate')));
                            unset($aGroup);
                        }
                        $aGroup = array();
                        $monthTotal = 0;
                        $avgrate=0;
                        $prevMonth = $thisMonth;
                        $prevMonthName = date("F Y",strtotime($ttLastLog_DateTime));
                    }
                     
                    $avgrate+=$ttRate;
                    $monthTotal++;
                    $yearTotal ++;
                    $linkedTicket = "<a href=index.php?fuse=support&view=ViewTicketDetails&ticketID=".$ttID.">".$ttID."</a>";
                    
                    $timeElapsed = $this->getDateDifference($ttDateSubmitted, $ttLastLog_DateTime);
                    
                    $aGroup[] = array($linkedTicket,$this->user->lang($ttType),$ttSubject,$assignedTo,$timeElapsed," ".$this->user->lang($leyends[$ttRate]));
                }
                
                //add final group
                if (isset($aGroup)){
                    $aGroup[] = array("--------","-------","--------","--------","--------","--------");
                    $aGroup[] = array("",$this->user->lang('Total This Month'),$monthTotal,"",$this->user->lang('Average Rate'),$this->user->lang($leyends[round($avgrate/$monthTotal)]));
                    $this->Add($aGroup,"$prevMonthName",array($this->user->lang('Ticket ID'),$this->user->lang('Ticket Type'),$this->user->lang('Subject'),$this->user->lang('Assigned To'),$this->user->lang('Total Elapsed'),$this->user->lang('Rate')));
                    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();
        } 
        
            echo '<br><br>';
        
 
     
    }

    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;
    }
}
?>
