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



/**
 * Support_Tickets_Opened_By_Customer 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_By_Customer extends Report
{
    protected $featureSet = array('accounts', 'support');

    var $m_hasgraph = false;
    
    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {

        // Set the report information
        $this->setName('Support Tickets Opened by Customer', true);
        $this->setVersion("1.1");
        $this->SetDescription($this->user->lang('Displays all support tickets opened. (click on a date to view all tickets for that year)'));

        $clientID = isset($_GET['frmClientID'])? $_GET['frmClientID']: 0;        

        //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 = 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 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=Admin_ViewTicketMessage&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();
        }
    }
}
?>
