<?PHP
/**************************************************************************
*===================Trouble Tickets Opened Report=========================*
*                                                                         *
* This report will take all your trouble tickets and report those results.*
* Author: Kevin Irving                                                    *
* Created: Janurary 2005                                                  *
*
* Modified by: Duane Charter                                              *
* Added link to the actual support ticket and correct problems            *
* with the percentages.                                                   *
***************************************************************************/

// don't show if not logged in
if (!isset($_SESSION['userId'])) die('Log in first');

//Class Initialize
$report = new Report();
$report->SetDescription(lang('Displays all trouble 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 = $GLOBALS['db']->query($sql);

while($row = $result->fetch()){
    $totaltickets = $row['total'];
}
unset($result);

//Now we query for all trouble 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 = $GLOBALS['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)){
    $report->Add($aGroup,"",array(lang('Year'),lang('Tickets Opened'),lang('Difference'),lang('Total Percent')));
    unset($aGroup);
}

if(@$_GET['year']) {
//Now we query for all trouble tickets in this year
    $sql = "SELECT
        assignedtoid,
        id,
        subject,
        datesubmitted
        FROM troubleticket
        WHERE SUBSTRING(datesubmitted, 1, 4) = ?
        ORDER BY datesubmitted";
        
    $result = $GLOBALS['db']->query($sql, $_GET['year']);
        

    //initialize
    $prevMonth = "-1";
    $monthTotal = 0;
    $yearTotal = 0;
    while (list($ttAssignedToID,$ttID,$ttSubject,$ttDateSubmitted) = $result->fetch()){
        $thisMonth = date("n",strtotime($ttDateSubmitted));
                
        $sql = "SELECT firstname, lastname FROM users WHERE id=?";
        $assignedTo = $GLOBALS['db']->query($sql, $ttAssignedToID);
        list($assignedToFirst,$assignedToLast) = $assignedTo->fetch();
        $assignedTo = $assignedToFirst . " " . $assignedToLast;
         
        if($prevMonth!=$thisMonth){
            if (isset($aGroup)){
                
                $aGroup[] = array("--------","-------","--------","--------");
                $aGroup[] = array("",lang('Total This Month'),$monthTotal,"");
                //add previous group before getting next group
                $report->Add($aGroup,"$prevMonthName",array(lang('Ticket ID'),lang('Subject'),lang('Assigned To'),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=5&action=5005&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("",lang('Total This Month'),$monthTotal,"");
        $report->Add($aGroup,"$prevMonthName",array(lang('Ticket ID'),lang('Subject'),lang('Assigned To'),lang('Date Submitted')));
        unset($aGroup);
    }

    $aGroup[] = array("","","<b>".$yearTotal."</b>","");
    $report->Add($aGroup,lang('Total for')." $_GET[year]",array("","","",""));
    
    $report->Show();

} else {

    //Display Report
    $report->Show();

}
} else {
    $aGroup[] = array(lang('No tickets opened'));
    $report->Add($aGroup,"",array(""));
    $report->Show();
}


?>
