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



/**
 * Support_Tickets_Opened 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_Tickets_Opened extends Report
{
    var $m_hasgraph = true;

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        // Set the report information
        $this->setName('Support Tickets Opened', true);
        $this->setVersion("1.1");
        $this->SetDescription($this->user->lang('Displays Stats on the total number of Support tickets opened per year broken down by month'));


        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(datesubmitted) = MONTH('$minus_date')) AND (YEAR(datesubmitted) = YEAR('$minus_date'))";
                    $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


            //print_r($lArrayMonth);
            echo $this->GraphData($lArrayMonth,$month_minus,$thisYear, $lastYear);
            exit;
        }

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

        //echo "<p><img src='index.php?fuse=reports&amp;report=Support_Tickets_Opened&amp;type=Support&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 "
                ."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 ) =? "
                        ."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();
        }
    }

    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 = "";

        $title = "";
        $subcaption = "";

        if($this->viewingFromDashboard()) {
            //do not want to see title in dashboard since we already show from dropdown
            //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="";
        }

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



        $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 .= "<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;


    }

}
?>
