<?php
/**
 * Support Tickets Service Rating Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.2
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.2 Updated the report to use Pear Commenting & the new title handing to make app reports consistent.
 ************************************************
 */


/**
 * Support_Service_Rating Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.1
 * @link     http://www.clientexec.com
 */
class Support_Service_Rating extends Report
{
    protected $featureSet = 'support';

    var $m_hasgraph = true;
    var $m_graphType = "MSColumn2D.swf";

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process() 
    {

        // Set the report information
        $this->setName('Support Tickets Service Rating', true);
        $this->setVersion("1.2");
        $this->SetDescription($this->user->lang('This report will take all of your support ticket Ratings and graph the results. '));

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

        //TODO remove from language files
        $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'));

        $lArrayMonth = array();
        $lArrayLastWeek = array();
        $lArrayFillColor = array();

        //*****************************
        //Get count of users based on minus x from tomonth that have signed up
        //*****************************
        if(isset($_GET['year']) ) {
            $year=$_GET['year'];
        }else {
            $year=date("Y");
        }
        if(isset($_GET['month'])) {
            $month=$_GET['month'];
        }else {
            $month = date("m");
        }


        //get all ratings for the year
        $sql = "select MONTH(lastlog_datetime) as month,rate from troubleticket WHERE (YEAR(lastlog_datetime) = ".$year.") AND status = -1 AND rate > 0 ORDER BY lastlog_datetime ASC";

        //echo $sql;
        $result = $this->db->query($sql);

        $firstTime = true;
        $sumCount =0;
        $aMonth = array();
        $aMonths = array();
        $newMonth = 0;
        $lastMonthCount = 0;

        while($row = $result->fetch()) {

            $tMonth = $row['month'];

            if($firstTime) {
                $newMonth = $tMonth;
                $firstTime=false;
            }

            //	$sumCount ++;

            if($newMonth!=$tMonth) {

                $aMonths[$newMonth]["month"] = $newMonth;
                $aMonths[$newMonth]["1"] = $this->ReturnArrayOfRate($aMonth,1,$sumCount);
                $aMonths[$newMonth]["2"] = $this->ReturnArrayOfRate($aMonth,2,$sumCount);
                $aMonths[$newMonth]["3"] = $this->ReturnArrayOfRate($aMonth,3,$sumCount);
                $aMonths[$newMonth]["4"] = $this->ReturnArrayOfRate($aMonth,4,$sumCount);

                $newMonth = $tMonth;
                unset($aMonth);
                $sumCount = 1;
            }else {
                $sumCount++;
            }

            $aMonth[] = $row['rate'];

        }

        //add last month

        if(count($aMonth) > 0) {
            if($sumCount==0) {
                $sumCount++;
            }
            $aMonths[$newMonth]["month"] = $tMonth;
            $aMonths[$newMonth]["1"] = $this->ReturnArrayOfRate($aMonth,1,$sumCount);
            $aMonths[$newMonth]["2"] = $this->ReturnArrayOfRate($aMonth,2,$sumCount);
            $aMonths[$newMonth]["3"] = $this->ReturnArrayOfRate($aMonth,3,$sumCount);
            $aMonths[$newMonth]["4"] = $this->ReturnArrayOfRate($aMonth,4,$sumCount);
        }

        if($graphdata && count($aMonth) > 0) {
            //this supports lazy loading and dynamic loading of graphs
            echo $this->GraphData($aMonths,$year,$month);
            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 {
            if($currentYear!=$thisYear) {
                $currentMonth=12;
            }else {
                $currentMonth = $thisMonth;
            }
        }

        // Output the headers before anything else
        $this->showReportHeader();

        //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&year=".$currentYear."&month=".$currentMonth."&view=".$_REQUEST['view']."&fuse=".$_REQUEST['fuse']."&report=".urlencode($_REQUEST['report'])."&type=".urlencode($_REQUEST['type']);
        $this->ShowGraph(0,$url);

        echo "<br/>";

        //LOAD details for tickets with ratings for the selected year and month
        //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 = mb_substr($basePage, 0, $nPos) . "&year=";
                }
                $yearLi = mb_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=Admin_ViewTicketMessage&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 ReturnArrayOfRate($aMonths,$rate,$totalCount)
    {
        $count=0;
        foreach($aMonths as $mRate) {
            if($mRate == $rate) $count++;
        }

        $avg = ($count/$totalCount);
        $avg =  $avg * 100;
        return ceil($avg);
    }

    /**
     * Function to generate the graph data
     *
     * @return null - direct output
     */
    function GraphData($aMonths,$currentYear,$currentMonth)
    {

        // 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');

        $keys = array_keys($aMonths);
        $ratedata = array();
        $rArray = array();

        //conver 01 to 1
        $currentMonth = intval($currentMonth);

        for($rateindex = 1; $rateindex <=4; $rateindex++) {
            //add the rate for the month to rArray
            /*
            if($this->viewingFromDashboard()){
                    //for dashboard since we are only showing current month if
                    //we do not have any data for that month just return
                    if(array_key_exists($currentMonth,$aMonths)){
                            $rArray[] = $aMonths[$currentMonth][$rateindex];
                    }else{
                            return "<chart></chart>";
                    }
            }else{
             *
            */
            //get first month if it isn't 1 for Jan then fill
            //array up to the first month we have data for
            if($keys[0]!=1) {
                $rArray = array_fill(0,$keys[0]-1,"0");
            }
            foreach($aMonths as $month) {
                $rArray[] = $month[$rateindex];
            }
            //}
            $ratedata[] = $rArray;
            unset($rArray);
        }

        $strXML = "";

        //Create an XML data document in a string variable
        if($this->viewingFromDashboard()) {
            //do not want to see title in dashboard since we already show from dropdown
            $title = "";
            $subcaption = "";
            $clickurl="clickURL='index.php?fuse=reports&report=Support+Service+Rating&type=Support&view=ViewReport&year=".$currentYear."'";
        }else {
            $clickurl="";
            $title = "";
            $subcaption = "For the year ".$currentYear;
        }

        $strXML  = "<chart ".$clickurl." caption='".$title."' subcaption='".$subcaption."' numberSuffix='%' ".$this->linechartDefaults.">";


        $strXML  .= "<categories>\n";
        //if($this->viewingFromDashboard()){
        //	    if($currentMonth > 0) $strXML  .= "<category label='".$this->MonthName($currentMonth,true)."'/>\n";
        //}else{
        if($currentMonth > 0) $strXML  .= "<category label='".$this->MonthName(1,true)."'/>\n";
        if($currentMonth > 1) $strXML  .= "<category label='".$this->MonthName(2,true)."'/>\n";
        if($currentMonth > 2) $strXML  .= "<category label='".$this->MonthName(3,true)."'/>\n";
        if($currentMonth > 3) $strXML  .= "<category label='".$this->MonthName(4,true)."'/>\n";
        if($currentMonth > 4) $strXML  .= "<category label='".$this->MonthName(5,true)."'/>\n";
        if($currentMonth > 5) $strXML  .= "<category label='".$this->MonthName(6,true)."'/>\n";
        if($currentMonth > 6) $strXML  .= "<category label='".$this->MonthName(7,true)."'/>\n";
        if($currentMonth > 7) $strXML  .= "<category label='".$this->MonthName(8,true)."'/>\n";
        if($currentMonth > 8) $strXML  .= "<category label='".$this->MonthName(9,true)."'/>\n";
        if($currentMonth > 9) $strXML  .= "<category label='".$this->MonthName(10,true)."'/>\n";
        if($currentMonth > 10) $strXML  .= "<category label='".$this->MonthName(11,true)."'/>\n";
        if($currentMonth > 11) $strXML  .= "<category label='".$this->MonthName(12,true)."'/>\n";

        //}

        $strXML  .= "</categories>\n";

        $index=1;
        foreach($ratedata as $aRate) {
            switch($index) {
                case "1":
                    $rateName = "Excellent";
                    $color = "33CC00";
                    break;
                case "2":
                    $rateName = "Good Enough";
                    $color = "0066CC";
                    break;
                case "3":
                    $rateName = "Not Great";
                    $color = "FFCC33";
                    break;
                case "4":
                    $rateName = "Poor";
                    $color = "F20000";
                    break;
            }

            $strXML .= "<dataset seriesName='".$rateName."' color='".$color."'>\n";
            foreach($aRate as $rateTotal) {
                if($rateTotal!=0) {
                    $strXML .= "<set value='".$rateTotal."' />\n";
                }else {
                    $strXML .= "<set value='' />\n";
                }
            }
            $strXML .= "</dataset>\n";
            $index++;
        }

        $strXML .= "<styles>";
        $strXML .= "<definition>";
        $strXML .= "    <style name='myToolTipFont' borderColor='dddddd' leftMargin='20' type='font' font='Verdana' size='10' color='333333'/>";
        $strXML .= "    <style name='myLegendFont' type='font' font='Verdana' size='10' color='333333' bold='0' />";
        $strXML .= "</definition>";
        $strXML .= "<application>";
        $strXML .= "    <apply toObject='ToolTip' styles='myToolTipFont' />";
        $strXML .= "    <apply toObject='Legend' styles='myLegendFont' />";
        $strXML .= "</application>";
        $strXML .= "</styles>";

        $strXML .= "</chart>";

        return $strXML;


    }


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