<?php
require_once 'classes/UserGateway.php';

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

    function process()
    {
        /**************************************************************************
        *=============Support_Tickets_Opened_By_Customer Report===================*
        *                                                                         *
        * This report will take all your support tickets of a customer and report *
        * those results.                                                          *
        * Author: Juan David                                                      *
        * Created: Octover 2007                                                   *
        ***************************************************************************/
        $clientID = isset($_GET['frmClientID'])? $_GET['frmClientID']: 0;
        $userGateway = new UserGateway();
        
        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(datesubmitted) = MONTH('$minus_date')) AND (YEAR(datesubmitted) = YEAR('$minus_date')) AND (userid = $clientID)";
                    $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("New 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 $userGateway->returnClientDropDown($this->user, $this->settings, $this->customer, 'ViewReport', false, true, 'Support_Tickets_Opened_By_Customer', 'Support', $clientID);
        echo "<p><img src='index.php?fuse=reports&amp;report=Support_Tickets_Opened_By_Customer&amp;type=Support&amp;frmClientID=".$clientID."&amp;viewimage=1'></p>";
        
        //Class Initialize
        $this->SetDescription($this->user->lang('Displays all support tickets opened.(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";
        $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( datesubmitted, '%Y' ) AS date_opened, "
              ."datesubmitted AS date_unformatted, "
              ."COUNT(*), "
              ."CONCAT(FORMAT((COUNT(*)/$totaltickets)*100,0),'%') AS percent "
              ."FROM troubleticket "
              ."WHERE userid = $clientID "
              ."GROUP BY DATE_FORMAT( datesubmitted, '%Y' ) "
              ."ORDER BY DATE_FORMAT( datesubmitted, '%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 Opened'),$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 "
                  ."FROM troubleticket t "
                  ."LEFT JOIN users ON ( users.id = t.assignedtoid ) "
                  ."WHERE SUBSTRING( t.datesubmitted, 1, 4 ) =? "
                  ."AND userid = $clientID "
                  ."ORDER BY t.datesubmitted";
                
            $result = $this->db->query($sql, $_GET['year']);
                
        
            //initialize
            $prevMonth = "-1";
            $monthTotal = 0;
            $yearTotal = 0;
            while (list($assignedToFirst,$assignedToLast,$ttID,$ttSubject,$ttDateSubmitted) = $result->fetch()){
                $thisMonth = date("n",strtotime($ttDateSubmitted));
                $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')));
                        unset($aGroup);
                    }
                    $aGroup = array();
                    $monthTotal = 0;
                    $prevMonth = $thisMonth;
                    $prevMonthName = date("F Y",strtotime($ttDateSubmitted));
                }
                 
                 
                $monthTotal++;
                $yearTotal ++;
                $linkedTicket = "<a href=index.php?fuse=support&view=ViewTicketDetails&ticketID=".$ttID.">".$ttID."</a>";    
                $aGroup[] = array($linkedTicket,$ttSubject,$assignedTo,date('j M Y',strtotime($ttDateSubmitted)));
            }
        
            //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')));
                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 opened'));
            $this->Add($aGroup,"",array(""));
            $this->Show();
        }
    }
}
?>
