<?php
/**
 * Support Ticket Totals Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.3
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.0 Initial version by Daniel Jones - Nation Voice Commuincations LLC (NationVoice.com)
 *   1.1 Keith with assistance from Mike & Alejandro (07/2007)
 *   1.2 Juan David (10/2007)
 *   1.3 Updated the report to use Pear Commenting & the new title handing to make app reports consistent.
 ************************************************
 */

require_once 'newedge/classes/NE_GroupsGateway.php';

/**
 * Support_Ticket_Totals Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Jason Yates <jason@clientexec.com>
 * @license  ClientExec License
 * @version  1.3
 * @link     http://www.clientexec.com
 */
class Support_Ticket_Totals extends Report
{
    var $m_hasgraph = false;
    
    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        // Set the report information
        $this->setName('Support Ticket Totals', true);
        $this->setVersion("1.3");
        $this->SetDescription($this->user->lang('Displays Support Stats for Each Level of Staff and calculates various stats per ticket.'));


        $VERSION = urlencode(NE_Registry::getAppVersion());

        if(isset($_GET['startdate'])) {
            $startDateArray = explode('/', $_GET['startdate']);
            $tempStartDate = date("Y-m-d", mktime(0, 0, 0, $startDateArray[0], $startDateArray[1], $startDateArray[2]));
            $temp2StartDate = mktime(0, 0, 0, $startDateArray[0], $startDateArray[1], $startDateArray[2]);
        }else {
            $tempStartDate = date("Y-m-d", mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
            $temp2StartDate = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
        }

        if(isset($_GET['enddate'])) {
            $endDateArray = explode('/', $_GET['enddate']);
            $tempEndDate = date("Y-m-d", mktime(0, 0, 0, $endDateArray[0], $endDateArray[1], $endDateArray[2]));
            $temp2EndDate = mktime(0, 0, 0, $endDateArray[0], $endDateArray[1]+1, $endDateArray[2]);
        }else {
            $tempEndDate = date("Y-m-d");
            $temp2EndDate = mktime(0, 0, 0, date("m"), date("d")+1, date("Y"));
        }
        $amountOfDays = ((($temp2EndDate-$temp2StartDate)/60)/60)/24;

        if ($this->settings->get('Date Format') == 'm/d/Y') {
            $dateFormat = '%m/%d/%Y';
        } else {
            $dateFormat = '%d/%m/%Y';
        }

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

        echo "<br>";
        echo "<form id='reportdropdown' method='GET'>";
        echo "<table border=0 cellpadding=2 cellspacing=0>";
        echo "<tr><td align=center>";
        echo $this->user->lang('Start Date').":&nbsp;&nbsp;";

        echo '<input class=body type=text size=12 MAXLENGTH=10 name="startdate" id="startdate" value=\''.NE_Lib::db_to_form($tempStartDate, $this->settings->get('Date Format'), "/").'\'>';

        echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$this->user->lang('End Date').":&nbsp;&nbsp;";

        echo '<input class=body type=text size=12 MAXLENGTH=10 name="enddate" id="enddate" value=\''.NE_Lib::db_to_form($tempEndDate, $this->settings->get('Date Format'), "/").'\'>';

        echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <input type=button name=search class="button_medium" value=\''.$this->user->lang('search').'\' onclick="ChangeTable(document.getElementById(\'startdate\').value, document.getElementById(\'enddate\').value);">
              &nbsp;&nbsp;';
        echo "</td></tr></table>";
        echo "</form>";
        echo "<br>";
        echo "<br>";
        echo "<br>";


        echo "\n\n<script type='text/javascript'>

                function ChangeTable(strStartDate, strEndDate){
                    location.href='index.php?fuse=reports&report=Support_Ticket_Totals&type=Support&view=ViewReport&startdate='+strStartDate+'&enddate='+strEndDate+'&change=1';
                }
            </script>";
        echo "\n\n";


        if(isset($_GET['change']) && $_GET['change']==1 && $amountOfDays > 0) {
            $queryTypes = "SELECT id, name FROM troubleticket_type WHERE enabled=1 ORDER BY myorder";
            $resultTypes = $this->db->query($queryTypes);
            $types = array();
            $tags = array($this->user->lang('Name'));
            while(list($typeID, $type) = $resultTypes->fetch()) {
                $types[$typeID] = $type;
                $tags[] = $this->user->varLang($type);
            }
            $tags[] = "<font color=green>".$this->user->lang('Total Tickets Closed')."</font>";
            $tags[] = "<font color=green>".$this->user->lang('Tickets/Day')."</font>";
            $tags[] = "<font color=green>".$this->user->lang('Replies/Ticket')."</font>";

            $totalTotal = array();
            foreach($types AS $tType) {
                $totalTotal[$tType] = 0;
                $totalTotal[$tType.'Answers'] = 0;
            }

            $groupGateway = new NE_GroupsGateway();
            $groupIterator = $groupGateway->getAdminGroups();

            $queryTickets = "SELECT t.assignedtoid, t.messagetype, COUNT(t.id) "
                    ."FROM troubleticket t "
                    ."INNER JOIN troubleticket_log "
                    ."ON (t.id = troubleticket_log.troubleticketid AND t.assignedtoid= troubleticket_log.userid) "
                    ."WHERE t.status=-1 "
                    ."AND t.lastlog_datetime >= '$tempStartDate' "
                    ."AND t.lastlog_datetime <= '$tempEndDate 23:59:59' "
                    ."GROUP BY t.assignedtoid, t.messagetype, t.id";
            $resultTickets = $this->db->query($queryTickets);
            $arrayAllInfo = array();
            while(list($assignedtoid, $messagetype, $repliesamount) = $resultTickets->fetch()) {
                if(isset($arrayAllInfo[$assignedtoid][$messagetype])) {
                    $arrayAllInfo[$assignedtoid][$messagetype]['amountReplies'] += $repliesamount;
                    $arrayAllInfo[$assignedtoid][$messagetype]['amountTickets'] += 1;
                }else {
                    $arrayAllInfo[$assignedtoid][$messagetype]['amountReplies'] = $repliesamount;
                    $arrayAllInfo[$assignedtoid][$messagetype]['amountTickets'] = 1;
                }
            }

            while ($group = $groupIterator->fetch()) {
                $query = "SELECT id, firstname, lastname FROM users WHERE groupid=? ORDER BY firstname, lastname";
                $result = $this->db->query($query, $group->getId());
                $aGroup = array();

                $totalLevel = array();
                foreach($types AS $tType) {
                    $totalLevel[$tType] = 0;
                    $totalLevel[$tType.'Answers'] = 0;
                }

                while(list($id, $firstname, $lastname) = $result->fetch()) {
                    $values = array($firstname.' '.$lastname);
                    $total = 0;
                    $totalAnswers = 0;
                    foreach($types AS $tTypeID=>$tType) {
                        $amount = isset($arrayAllInfo[$id][$tTypeID]['amountTickets'])? $arrayAllInfo[$id][$tTypeID]['amountTickets'] : 0;
                        $amountAnswers = isset($arrayAllInfo[$id][$tTypeID]['amountReplies'])? $arrayAllInfo[$id][$tTypeID]['amountReplies'] : 0;

                        $totalLevel[$tType] += $amount;
                        $totalTotal[$tType] += $amount;
                        $totalLevel[$tType.'Answers'] += $amountAnswers;
                        $totalTotal[$tType.'Answers'] += $amountAnswers;
                        $values[] = "$amount ($amountAnswers)";
                        $total += $amount;
                        $totalAnswers += $amountAnswers;
                    }
                    $values[] = "<b>$total ($totalAnswers)</b>";
                    $values[] = '<b>'.round($total/$amountOfDays, 2).'</b>';
                    $RepliesXticket = ($total != 0)? round($totalAnswers/$total, 2) : 'n/a';
                    $values[] = '<b>'.$RepliesXticket.'</b>';

                    $aGroup[] = $values;
                }
                if(count($aGroup) > 0) {
                    $totalLevelValues = array('<font color=green><b>'.$this->user->lang('TOTAL').'</b></font>');
                    $totalTotalLevel = 0;
                    $totalTotalLevelAnswers = 0;
                    foreach($types AS $tType) {
                        $totalLevelValues[] = '<font color=green><b>'.$totalLevel[$tType].' ('.$totalLevel[$tType.'Answers'].')</b></font>';
                        $totalTotalLevel += $totalLevel[$tType];
                        $totalTotalLevelAnswers += $totalLevel[$tType.'Answers'];
                    }

                    $totalLevelValues[] = "<font color=green><b>$totalTotalLevel ($totalTotalLevelAnswers)</b></font>";
                    $totalLevelValues[] = '<font color=green><b>'.round($totalTotalLevel/$amountOfDays, 2).'</b></font>';
                    $RepliesXticket = ($totalTotalLevel != 0)? round($totalTotalLevelAnswers/$totalTotalLevel, 2) : 'n/a';
                    $totalLevelValues[] = '<font color=green><b>'.$RepliesXticket.'</b></font>';
                    $aGroup[] = $totalLevelValues;
                    $this->Add($aGroup,$this->user->varLang($group->getName()),$tags);
                }else {
                    $this->Add(array(array('<font color=red>'.$this->user->lang('no values').'</font>')) ,$this->user->varLang($group->getName()),array(''));
                }
            }

            $totalValues = array('<font color=green><b>'.$this->user->lang('TOTAL').'</b></font>');
            $totalTotalValues = 0;
            $totalTotalValuesAnswers = 0;
            foreach($types AS $tType) {
                $totalValues[] = '<font color=green><b>'.$totalTotal[$tType].' ('.$totalTotal[$tType.'Answers'].')</b></font>';
                $totalTotalValues += $totalTotal[$tType];
                $totalTotalValuesAnswers += $totalTotal[$tType.'Answers'];
            }

            $totalValues[] = "<font color=green><b>$totalTotalValues ($totalTotalValuesAnswers)</b></font>";
            $totalValues[] = '<font color=green><b>'.round($totalTotalValues/$amountOfDays, 2).'</b></font>';
            $RepliesXticket = ($totalTotalValues != 0)? round($totalTotalValuesAnswers/$totalTotalValues, 2) : 'n/a';
            $totalValues[] = '<font color=green><b>'.$RepliesXticket.'</b></font>';
            $aGroup2 = array();
            $aGroup2[] = $totalValues;
            $this->Add($aGroup2,$this->user->varLang('<font color=green>'.$this->user->lang('TOTAL').'</font>'),$tags);
        }else {
            echo $this->user->lang('Displays how many tickets each staff member has closed (and how many times has replied to them) based on a date range.');
            echo '<br><br>';
            if($amountOfDays <= 0) {
                echo '<font color=red>'.$this->user->lang('Please select a valid date range and press SEARCH.').'</font>';
            }else {
                echo '<font color=green>'.$this->user->lang('Please select a date range and press SEARCH.').'</font>';
            }
            echo '<br><br>';
        }

        //Class Initialize
        $this->SetDescription($this->user->lang('Displays how many tickets each staff member has closed (and how many times has replied to them) based on a date range.'));

        //Display Report
        $this->Show();
    }
}
?>
