<?php
/**
* @version 2.0.0
* @package RSTickets! Pro 2.0.0
* @copyright (C) 2010 www.rsjoomla.com
* @license GPL, http://www.gnu.org/licenses/gpl-2.0.html
*/

defined('_JEXEC') or die('Restricted access');


class RsticketsproModelReports extends JModelAdmin
{
	protected $_db;
	protected $_departments;
	protected $_department_names;
	protected $_staff_names;
	
	public $min = 0;
	public $avg = 0;
	public $max = 0;
	public $count = 0;
	public $total = 0;
	public $viewin = '';
	
	public function __construct() {
		parent::__construct();
		
		// large databases need lots of memory
		if ($limit = ini_get('memory_limit')) {
			if (substr($limit, -1) == 'M') {
				if ((int) $limit < 192) {
					ini_set('memory_limit', '192M');
				}
			}
		}
		
		$this->_db = JFactory::getDbo();
		
		$this->_db->setQuery("SET SQL_BIG_SELECTS=1");
		$this->_db->execute();
		
		JFactory::getLanguage()->load('plg_system_rsticketsproreports', JPATH_ADMINISTRATOR);
		
		$this->_getDepartments();
		$this->_getStaff();
	}
	
	protected function _getDepartments() {
		$this->_db->setQuery('SELECT * FROM '.$this->_db->qn('#__rsticketspro_departments').' ORDER BY '.$this->_db->qn('ordering').'  ASC');
		$this->_departments = $this->_db->loadObjectList();
		
		$this->_department_names = array();
		foreach ($this->_departments as $department)
			$this->_department_names[$department->id] = JText::_($department->name);
	}
	
	protected function _getStaff() {
		$what = RSTicketsProHelper::getConfig('show_user_info');
		
		$this->_db->setQuery('SELECT '.$this->_db->qn('s.user_id').', '.$this->_db->qn('u.'.$what).' FROM '.$this->_db->qn('#__rsticketspro_staff','s').' LEFT JOIN '.$this->_db->qn('#__users','u').' ON '.$this->_db->qn('s.user_id').' = '.$this->_db->qn('u.id').'');
		$staff_names = $this->_db->loadObjectList();
		
		foreach ($staff_names as $staff)
			$this->_staff_names[$staff->user_id] = $staff->{$what};
		
		$this->_staff_names[0] = JText::_('RST_UNASSIGNED');
	}
	
	
	public function getCountDepartments() {
		return count($this->_departments);
	}
	
	public function getCountStaff() {
		return count($this->_staff_names);
	}
	
	public function getDepartments() {
		return $this->_departments;
	}
	
	public function getStaff() {
		$what = RSTicketsProHelper::getConfig('show_user_info');
		$this->_db->setQuery('SELECT '.$this->_db->qn('s').'.*, '.$this->_db->qn('u.'.$what,'thealias').' FROM '.$this->_db->qn('#__rsticketspro_staff','s').' LEFT JOIN '.$this->_db->qn('#__users','u').' ON '.$this->_db->qn('u.id').' = '.$this->_db->qn('s.user_id').' ');
		$staff = $this->_db->loadObjectList();
		
		$unassigned = new stdClass();
		$unassigned->thealias = $unassigned->username = $unassigned->name = $unassigned->email = JText::_('RST_UNASSIGNED');
		$unassigned->user_id = '00'; // say hurray to Joomla! changing how everything works for the millionth time!
		$staff[] = $unassigned;
		
		return $staff;
	}
	
	public function getPriority() {
		$this->_db->setQuery('SELECT * FROM '.$this->_db->qn('#__rsticketspro_priorities').' ORDER BY '.$this->_db->qn('ordering').' ASC');
		return $this->_db->loadObjectList();
	}
	
	public function getReportData() {
		$input		= JFactory::getApplication()->input;
		$db			= JFactory::getDbo();
		
		$from		= $input->getString('from','');
		$to			= $input->getString('to','');
		$report		= $input->getString('report',''); 
		$filter 	= array();
		$where 		= '';
		$return 	= array();
		
		// Unit
		$unit = $input->getString('unit','');
		
		switch ($unit) {
			default:
			case 'day':
				$format = 'Y-m-d';
				$step 	= 86400;
			break;
			
			case 'month':
				$format = 'Y-m';
				$step 	= 86400*30;
			break;
			
			case 'year':
				$format = 'Y';
				$step 	= 86400*30*12;
			break;
			
			case 'quarter':
				$format = 'Y-m';
			break;
		}
		
		// From and To
		if ($from || $to) {
			$startDate = JFactory::getDate($from)->toSql();
			$stopDate  = JFactory::getDate($to)->toSql();
			
			if ($from && $to) {
				$filter['date'] = ' AND ('.$this->_db->qn('date').' > '.$this->_db->q($startDate).' AND '.$this->_db->qn('date').' < '.$this->_db->q($stopDate).')';
				$where .= $filter['date'];
			} elseif ($from) {
				$filter['date'] = ' AND '.$this->_db->qn('date').' > '.$this->_db->q($startDate).'';
				$where .= $filter['date'];
			} elseif ($to) {
				$filter['date'] = ' AND '.$this->_db->qn('date').' < '.$this->_db->q($stopDate).'';
				$where .= $filter['date'];
			}
		}

		$departments = $input->get('departments',array(),'array');
		$departments = array_map('intval', $departments);
		if (!empty($departments)) {
			$filter['department_id'] = ' AND '.$this->_db->qn('department_id').' IN ('.implode(',', $departments).')';
			$where .= $filter['department_id'];
		}
		
		$staff = $input->get('staff',array(),'array');
		$staff = array_map('intval', $staff);
		if (!empty($staff)) {
			$filter['staff_id'] = ' AND '.$this->_db->qn('staff_id').' IN ('.implode(',', $staff).')';
			$where .= $filter['staff_id'];
		}
		
		$priority = $input->get('priority',array(),'array');
		$priority = array_map('intval', $priority);
		if (!empty($priority)) {
			$filter['priority_id'] = ' AND '.$this->_db->qn('priority_id').' IN ('.implode(',', $priority).')';
			$where .= $filter['priority_id'];
		}
		
		$user_id = $input->getInt('user_id',0);
		if (!empty($user_id)) {
			$filter['customer_id'] = ' AND '.$this->_db->qn('customer_id').' = '.(int) $user_id;
			$where .= $filter['customer_id'];
		}
		
		// Fill the graph with dates
		if (!$from) {
			$query = $db->getQuery(true)
				->select('MAX('.$db->qn('date').')')
				->from($db->qn('#__rsticketspro_tickets'))
				->where('1 '.$where);
			$from = $db->setQuery($query)->loadResult();
		}
		
		if (!$to) {
			$query = $db->getQuery(true)
				->select('MIN('.$db->qn('date').')')
				->from($db->qn('#__rsticketspro_tickets'))
				->where('1 '.$where);
			$to = $db->setQuery($query)->loadResult();
		}
		
		if ($unit != 'quarter') {
			$startDate = JFactory::getDate($from)->toUnix();
			$stopDate  = JFactory::getDate($to)->toUnix();
			
			for ($date = $startDate; $date < $stopDate; $date = $date + $step) {
				if ($unit == 'quarter') {
					$format = $this->getQuarter(JFactory::getDate($date)->format('m'));
					echo JFactory::getDate($date)->format('Y-m-d').' => '.$format.'<br />';
				}
				
				$return['units'][JFactory::getDate($date)->format($format)] = JFactory::getDate($date)->format($format);
			}
		} else {
			$startDate = JFactory::getDate($from)->format($this->getQuarter(JFactory::getDate($from)->format('m')));
			$stopDate = JFactory::getDate($to)->format($this->getQuarter(JFactory::getDate($to)->format('m')));
			
			list($startYear, $startQuarter) = explode(' Q', $startDate);
			list($stopYear, $stopQuarter) = explode(' Q', $stopDate);
			
			for ($year = $startYear; $year <= $stopYear; $year++) {
				for ($quarter = 1; $quarter <= 4; $quarter++) {
					if (($year == $startYear && $quarter >= $startQuarter) || ($year > $startYear && $year < $stopYear) || ($year == $stopYear && $quarter <= $stopQuarter)) {
						$return['units']["$year Q$quarter"] = "$year Q$quarter";
					}
				}
			}
		}
		
		// ordering
		$orderby = ' ORDER BY '.$this->_db->qn('date').' ASC';
		
		$viewby = $this->getViewBy();
		
		switch ($report)
		{
			// number of submitted tickets
			case 'report_1':
				// query
				$query = 'SELECT '.$this->_db->qn('department_id').', '.$this->_db->qn('date').' FROM '.$this->_db->qn('#__rsticketspro_tickets').' WHERE 1';
				$query = $query.$where.$orderby;
				$this->_db->setQuery($query);
				
				$tickets = $this->_db->loadObjectList();
				
				if (empty($departments)) {
					foreach ($departments as $department) {
						$departments[] = $id;
					}
				}
				
				foreach ($departments as $department) {
					$department = $this->getDepartmentName($department);
					$return['departments'][$department] = array();
				}
				
				foreach ($tickets as $ticket) {
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
					$department = $this->getDepartmentName($ticket->department_id);
					@$return['units'][$date] = $date;
					@$return['departments'][$department][$date] += 1;
					@$return['totals'][$date] += 1;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					$this->min = $this->max = max($return['totals']);
					
					foreach ($return['units'] as $date) {
						foreach ($departments as $department) {
							$department = $this->getDepartmentName($department);
							if (empty($return['departments'][$department][$date])) {
								$return['departments'][$department][$date] = 0;
								if (empty($return['totals'][$date])) {
									$return['totals'][$date] = 0;
								}
							}
						}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['departments'] as $return_department => $return_values)
						ksort($return['departments'][$return_department]);
					
					// total
					$this->total = array_sum($return['totals']);
				
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
			
			// ticket solving time
			case 'report_2':
				// select
				$select = $viewby == 'department' ? 'department_id' : 'staff_id';
				
				// query
				$query = 'SELECT UNIX_TIMESTAMP('.$this->_db->qn('last_reply').') - UNIX_TIMESTAMP('.$this->_db->qn('date').') AS diff, '.$this->_db->qn($select).', '.$this->_db->qn('date').' FROM '.$this->_db->qn('#__rsticketspro_tickets').' WHERE '.$this->_db->qn('status_id').' = 2';
				$query = $query.$where.$orderby;
				$this->_db->setQuery($query);
				$tickets = $this->_db->loadObjectList();
				
				$this->viewin = $input->getInt('viewin');
				if (!$this->viewin)
					$this->viewin = 60;
				
				if ($viewby == 'department') {
					if (empty($departments)) {
						foreach ($this->_department_names as $id => $name) {
							$departments[] = $id;
						}	
					}
					
					foreach ($departments as $department) {
						$department = $this->getDepartmentName($department);
						$return['viewby'][$department] = array();
					}
				}
				
				if ($viewby == 'staff') {
					if (empty($staff) && $viewby == 'staff') {
						foreach ($this->_staff_names as $id => $name) {
							$staff[] = $id;
						}
					}
					
					foreach ($staff as $staff_id) {
						$staff_id = $this->getStaffName($staff_id);
						$return['viewby'][$staff_id] = array();
					}
				}
				
				foreach ($tickets as $ticket) {
					$ticket->diff = $ticket->diff / $this->viewin;
					
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
						
					$viewby_unit = $viewby == 'department' ? $this->getDepartmentName($ticket->department_id) : $this->getStaffName($ticket->staff_id);
					@$return['units'][$date] = $date;
					if (empty($return['viewby'][$viewby_unit][$date]))
						$return['viewby'][$viewby_unit][$date] = $ticket->diff;
					else
						$return['viewby'][$viewby_unit][$date] = ($return['viewby'][$viewby_unit][$date] + $ticket->diff) / 2;
						
					if (empty($return['totals'][$date]))
						$return['totals'][$date] = $ticket->diff / 2;
					else
						$return['totals'][$date] = ($return['totals'][$date] + $ticket->diff) / 2;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					// max
					$this->min = $this->max = max($return['totals']);
				
					foreach ($return['units'] as $date) {
						if ($viewby == 'department')
							foreach ($departments as $department) {
								$department = $this->getDepartmentName($department);
								if (empty($return['viewby'][$department][$date])) {
									$return['viewby'][$department][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
									
								}
							}
						
						if ($viewby == 'staff')
							foreach ($staff as $staff_id) {
								$staff_id = $this->getStaffName($staff_id);
								if (empty($return['viewby'][$staff_id][$date])) {
									$return['viewby'][$staff_id][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
								}
							}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['viewby'] as $return_viewby => $return_values)
						ksort($return['viewby'][$return_viewby]);
					
					// total
					$this->total = count($return['totals']);
					
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
			
			// number of replies
			case 'report_3':
				// select
				$select = $viewby == 'department' ? 'department_id' : 'staff_id';
				
				// query
				$query = 'SELECT '.$this->_db->qn('t.'.$select).', '.$this->_db->qn('m.date').' FROM '.$this->_db->qn('#__rsticketspro_ticket_messages','m').' LEFT JOIN '.$this->_db->qn('#__rsticketspro_tickets','t').' ON  '.$this->_db->qn('m.ticket_id').' = '.$this->_db->qn('t.id').' WHERE 1';
				
				if (!empty($filter['date'])) {
					$filter['date'] = str_replace($this->_db->qn('date'), $this->_db->qn('m.date'), $filter['date']);
					$where = " ".implode(" ", $filter);
				}
				
				$orderby = str_replace($this->_db->qn('date'), $this->_db->qn('m.date'), $orderby);
				$query = $query.$where.$orderby;
				$this->_db->setQuery($query);
				$tickets = $this->_db->loadObjectList();
				
				if ($viewby == 'department') {
					if (empty($departments))
						foreach ($this->_department_names as $id => $name)
							$departments[] = $id;
					foreach ($departments as $department) {
						$department = $this->getDepartmentName($department);
						$return['viewby'][$department] = array();
					}
				}
				
				if ($viewby == 'staff') {
					if (empty($staff) && $viewby == 'staff')
						foreach ($this->_staff_names as $id => $name)
							$staff[] = $id;
					foreach ($staff as $staff_id) {
						$staff_id = $this->getStaffName($staff_id);
						$return['viewby'][$staff_id] = array();
					}
				}
				
				foreach ($tickets as $ticket) {
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
					
					$viewby_unit = $viewby == 'department' ? $this->getDepartmentName($ticket->department_id) : $this->getStaffName($ticket->staff_id);
					@$return['units'][$date] = $date;
					@$return['viewby'][$viewby_unit][$date] += 1;
					@$return['totals'][$date] += 1;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					// max
					$this->min = $this->max = max($return['totals']);
					
					foreach ($return['units'] as $date) {
						if ($viewby == 'department')
							foreach ($departments as $department) {
								$department = $this->getDepartmentName($department);
								if (empty($return['viewby'][$department][$date])) {
									$return['viewby'][$department][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
									
								}
							}
						
						if ($viewby == 'staff')
							foreach ($staff as $staff_id) {
								$staff_id = $this->getStaffName($staff_id);
								if (empty($return['viewby'][$staff_id][$date])) {
									$return['viewby'][$staff_id][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
									
								}
							}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['viewby'] as $return_viewby => $return_values)
						ksort($return['viewby'][$return_viewby]);
					
					// total
					$this->total = array_sum($return['totals']);
				
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
			
			// feedback
			case 'report_4':
				// select
				$select = $viewby == 'department' ? 'department_id' : 'staff_id';
				
				// query
				$query = 'SELECT '.$this->_db->qn($select).', '.$this->_db->qn('feedback').', '.$this->_db->qn('date').' FROM '.$this->_db->qn('#__rsticketspro_tickets').' WHERE '.$this->_db->qn('status_id').' = 2';
				$query = $query.$where.$orderby;
				$this->_db->setQuery($query);
				$tickets = $this->_db->loadObjectList();
				
				if ($viewby == 'department') {
					if (empty($departments))
						foreach ($this->_department_names as $id => $name)
							$departments[] = $id;
					foreach ($departments as $department) {
						$department = $this->getDepartmentName($department);
						$return['viewby'][$department] = array();
					}
				}
				
				if ($viewby == 'staff') {
					if (empty($staff) && $viewby == 'staff')
						foreach ($this->_staff_names as $id => $name)
							$staff[] = $id;
					foreach ($staff as $staff_id) {
						$staff_id = $this->getStaffName($staff_id);
						$return['viewby'][$staff_id] = array();
					}
				}
				
				foreach ($tickets as $ticket) {
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
						
					$viewby_unit = $viewby == 'department' ? $this->getDepartmentName($ticket->department_id) : $this->getStaffName($ticket->staff_id);
					@$return['units'][$date] = $date;
					if (empty($return['viewby'][$viewby_unit][$date]))
						$return['viewby'][$viewby_unit][$date] = $ticket->feedback;
					else
						$return['viewby'][$viewby_unit][$date] = ($return['viewby'][$viewby_unit][$date] + $ticket->feedback) / 2;
						
					if (empty($return['totals'][$date]))
						$return['totals'][$date] = $ticket->feedback / 2;
					else
						$return['totals'][$date] = ($return['totals'][$date] + $ticket->feedback) / 2;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					// max
					$this->min = $this->max = max($return['totals']);
					
					foreach ($return['units'] as $date) {
						if ($viewby == 'department')
							foreach ($departments as $department) {
								$department = $this->getDepartmentName($department);
								if (empty($return['viewby'][$department][$date])) {
									$return['viewby'][$department][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
									
								}
							}
						
						if ($viewby == 'staff')
							foreach ($staff as $staff_id) {
								$staff_id = $this->getStaffName($staff_id);
								if (empty($return['viewby'][$staff_id][$date])) {
									$return['viewby'][$staff_id][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
									
								}
							}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['viewby'] as $return_viewby => $return_values)
						ksort($return['viewby'][$return_viewby]);
					
					// total
					$this->total = count($return['totals']);
				
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
			
			// response time
			case 'report_5':
				// select
				$select = $viewby == 'department' ? 'department_id' : 'staff_id';
				
				// query
				$query = 'SELECT '.$this->_db->qn('t.'.$select).', UNIX_TIMESTAMP('.$this->_db->qn('m2.date').') - UNIX_TIMESTAMP('.$this->_db->qn('m.date').') AS diff, '.$this->_db->qn('m.id').', '.$this->_db->qn('m2.id').', '.$this->_db->qn('m.date').' FROM '.$this->_db->qn('#__rsticketspro_ticket_messages','m').', '.$this->_db->qn('#__rsticketspro_ticket_messages','m2').' LEFT JOIN '.$this->_db->qn('#__rsticketspro_tickets','t').' ON '.$this->_db->qn('ticket_id').' = '.$this->_db->qn('t.id').' WHERE '.$this->_db->qn('m.ticket_id').' = '.$this->_db->qn('m2.ticket_id').' AND '.$this->_db->qn('m2.date').' > '.$this->_db->qn('m.date').' AND '.$this->_db->qn('m.user_id').' != '.$this->_db->qn('m2.user_id').'';
				
				if (!empty($filter['date'])) {
					$filter['date'] = str_replace($this->_db->qn('date'), $this->_db->qn('m.date'), $filter['date']);
					$where = " ".implode(" ", $filter);
				}
				
				$orderby = str_replace($this->_db->qn('date'), $this->_db->qn('m.date'), $orderby);
				$groupby = ' GROUP BY '.$this->_db->qn('m.ticket_id').' ';
				$query = $query.$where.$groupby.$orderby;
				$this->_db->setQuery($query);
				$tickets = $this->_db->loadObjectList();
				
				$this->viewin = $input->getInt('viewin');
				if (!$this->viewin)
					$this->viewin = 60;
					
				if ($viewby == 'department') {
					if (empty($departments))
						foreach ($this->_department_names as $id => $name)
							$departments[] = $id;
					foreach ($departments as $department) {
						$department = $this->getDepartmentName($department);
						$return['viewby'][$department] = array();
					}
				}
				if ($viewby == 'staff') {
					if (empty($staff) && $viewby == 'staff')
						foreach ($this->_staff_names as $id => $name)
							$staff[] = $id;
					foreach ($staff as $staff_id) {
						$staff_id = $this->getStaffName($staff_id);
						$return['viewby'][$staff_id] = array();
					}
				}
				
				foreach ($tickets as $ticket) {
					$ticket->diff = $ticket->diff / $this->viewin;
					
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
						
					$viewby_unit = $viewby == 'department' ? $this->getDepartmentName($ticket->department_id) : $this->getStaffName($ticket->staff_id);
					@$return['units'][$date] = $date;
					if (empty($return['viewby'][$viewby_unit][$date]))
						$return['viewby'][$viewby_unit][$date] = $ticket->diff;
					else
						$return['viewby'][$viewby_unit][$date] = ($return['viewby'][$viewby_unit][$date] + $ticket->diff) / 2;
						
					if (empty($return['totals'][$date]))
						$return['totals'][$date] = $ticket->diff / 2;
					else
						$return['totals'][$date] = ($return['totals'][$date] + $ticket->diff) / 2;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					// max
					$this->min = $this->max = max($return['totals']);
					
					foreach ($return['units'] as $date) {
						if ($viewby == 'department')
							foreach ($departments as $department) {
								$department = $this->getDepartmentName($department);
								if (empty($return['viewby'][$department][$date])) {
									$return['viewby'][$department][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
								}
							}
						
						if ($viewby == 'staff')
							foreach ($staff as $staff_id) {
								$staff_id = $this->getStaffName($staff_id);
								if (empty($return['viewby'][$staff_id][$date])) {
									$return['viewby'][$staff_id][$date] = 0;
									if (empty($return['totals'][$date]))
										$return['totals'][$date] = 0;
								}
							}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['viewby'] as $return_viewby => $return_values)
						ksort($return['viewby'][$return_viewby]);
					
					// total
					$this->total = array_sum($return['totals']);
				
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
			
			// assigned tickets
			case 'report_6':
				// query
				$query = 'SELECT '.$this->_db->qn('staff_id').', '.$this->_db->qn('date').' FROM '.$this->_db->qn('#__rsticketspro_tickets').' WHERE 1';
				$query = $query.$where.$orderby;
				$this->_db->setQuery($query);
				$tickets = $this->_db->loadObjectList();
				
				if (empty($staff))
					foreach ($this->_staff_names as $id => $name)
						$staff[] = $id;
				foreach ($staff as $staff_id) {
					$staff_id = $this->getStaffName($staff_id);
					$return['staff'][$staff_id] = array();
				}
				
				foreach ($tickets as $ticket) {
					if ($unit == 'quarter')
						$format = $this->getQuarter(JFactory::getDate($ticket->date)->format('m'));
					
					$date = JFactory::getDate($ticket->date)->format($format);
					
					$staff_name = $this->getStaffName($ticket->staff_id);
					@$return['units'][$date] = $date;
					@$return['staff'][$staff_name][$date] += 1;
					@$return['totals'][$date] += 1;
					
					unset($ticket);
				}
				
				if (!empty($return['totals'])) {
					// max
					$this->min = $this->max = max($return['totals']);
					foreach ($return['units'] as $date) {	
						foreach ($staff as $staff_id) {
							$staff_id = $this->getStaffName($staff_id);
							if (empty($return['staff'][$staff_id][$date])) {
								$return['staff'][$staff_id][$date] = 0;
								if (empty($return['totals'][$date]))
									$return['totals'][$date] = 0;
							}
						}
						
						// min
						if (!empty($return['totals'][$date]))
							$this->min = min($this->min, $return['totals'][$date]);
					}
					
					foreach ($return['staff'] as $return_staff => $return_values)
						ksort($return['staff'][$return_staff]);
					
					// total
					$this->total = array_sum($return['totals']);
				
					// avg
					$this->avg = floor(array_sum($return['totals'])/count($return['totals']));
				}
			break;
		}
		
		unset($return['totals']);
		return $return;
	}
	
	public function getDepartmentName($id) {
		return @$this->_department_names[$id];
	}
	
	public function getStaffName($id) {
		return @$this->_staff_names[$id];
	}
	
	public function getQuarter($m) {
		$q = (int)floor($m / 3.1) + 1;
		return "Y Q$q";
	}
	
	public function getMin() {
		$unit = JFactory::getApplication()->input->getString('unit','');
		return $this->getNumberFormat($this->min).' '.$this->getViewIn().' '.' / '.JText::_('RST_'.$unit);
	}
	
	public function getAvg() {
		$unit = JFactory::getApplication()->input->getString('unit','');
		return $this->getNumberFormat($this->avg).' '.$this->getViewIn().' '.' / '.JText::_('RST_'.$unit);
	}
	
	public function getMax() {
		$unit = JFactory::getApplication()->input->getString('unit','');
		return $this->getNumberFormat($this->max).' '.$this->getViewIn().' '.' / '.JText::_('RST_'.$unit);
	}
	
	public function getTotal() {
		return $this->getNumberFormat($this->total);
	}
	
	public function getNumberFormat($number) {
		return number_format($number, 2, '.', '');
	}
	
	public function getViewIn() {
		$report = JFactory::getApplication()->input->getString('report','');
		
		switch ($report) {
			case 'report_1':
			case 'report_6':
				return JText::_('RST_TICKETS');
			break;
			
			case 'report_2':
			case 'report_5':
				switch ($this->viewin) {
					default:
					case 60:
						return JText::_('RST_MINUTES');
					break;
					
					case 3600:
						return JText::_('RST_HOURS');
					break;
					
					case 86400:
						return JText::_('RST_DAYS');
					break;
				}
			break;
			
			case 'report_3':
				return JText::_('RST_TICKET_REPLIES');
			break;
			
			case 'report_4':
				return JText::_('RST_STARS');
			break;
		}
	}
	
	public function getViewBy() {
		return JFactory::getApplication()->input->getString('viewby','');
	}
	
	public function getIE() {
		if (preg_match("#MSIE#i", $_SERVER['HTTP_USER_AGENT']) && !preg_match("#MSIE 9#", $_SERVER['HTTP_USER_AGENT']))
			return true;
		
		return false;
	}
	
	protected function getColor($i) {
		$colors = array('#3366FF', '#6633FF', '#CC33FF', '#FF33CC', '#33CCFF', '#003DF5', '#002EB8', '#FF3366', '#33FFCC', '#B88A00', '#F5B800', '#FF6633', '#33FF66', '#66FF33', '#CCFF33', '#FFCC33', '#002080', '#200080', '#600080', '#800060', '#006080', '#002FBD', '#003EFA', '#800020', '#008060', '#FABB00', '#BD8E00', '#802000', '#008020', '#208000', '#608000', '#806000');
		
		if ($i > count($colors) - 1) {
			$t = floor($i / (count($colors) - 1));
			$i = floor($i - $t);
		}
		
		return $colors[$i];
	}
	
	public function getRSPlain()
	{
		return new RsticketsproAdapterPlain('com-rsticketspro-reports');
	}
	
	public function getOptions()
	{
		return array('time_period' => JText::_('RST_REPORTS_TIME_PERIOD'), 
			'department' => JText::_('RST_REPORTS_DEPARTMENT'), 
			'staff' => JText::_('RST_REPORTS_STAFF'), 
			'customer' => JText::_('RST_REPORTS_CUSTOMER'), 
			'priority' => JText::_('RST_REPORTS_PRIORITY')
		);
	}

	protected function escape($value)
	{
		return htmlspecialchars($value, ENT_COMPAT, 'utf-8');
	}

    public function getForm($data = array(), $loadData = true)
    {
        // Get the form.
        $form = $this->loadForm('com_rsticketspro.reports', 'reports', array('control' => '', 'load_data' => $loadData));

        if (empty($form))
        {
            return false;
        }

        // Departments
        $departments = $this->getDepartments();
        $xmlDepartments = '<fieldset name="departments" label="'.JText::_('RST_REPORTS_DEPARTMENT').'">';
        foreach ($departments as $i => $department) {
            // add the department field
            $xmlDepartments .= '<field name="departments[]" id="departments'.$i.'" type="checkbox" label="'.$this->escape($department->name).'" description="" class="checkbox inline" labelclass="checkbox inline" value="'.$department->id.'" default="'.$department->id.'" checked="true" />';

            // add the proper color field
            $default = $this->getColor($i);
            $xmlDepartments .= '<field name="color_department_'.$i.'" type="color" description="" label="" default="'.$this->escape($default).'" />';

        }
        $xmlDepartments .= '</fieldset>';
        $xmlDepartments = new SimpleXMLElement($xmlDepartments);
        $form->setField($xmlDepartments);

        // Staff
        $staff = $this->getStaff();
        $xmlStaff = '<fieldset name="staff" label="'.JText::_('RST_REPORTS_STAFF').'">';
        foreach ($staff as $i => $person) {
            // add the staff field
            $xmlStaff .= '<field name="staff[]" id="staff'.$i.'" type="checkbox" label="'.$this->escape($person->thealias).'" description="" class="checkbox inline" labelclass="checkbox inline" value="'.$person->user_id.'" default="'.$person->user_id.'" checked="true" />';

            // add the proper color field
            $default = $this->getColor($i);
            $xmlStaff .= '<field name="color_staff_'.$i.'" type="color" description="" label="" default="'.$this->escape($default).'" />';
        }
        $xmlStaff .= '</fieldset>';
        $xmlStaff = new SimpleXMLElement($xmlStaff);
        $form->setField($xmlStaff);

        // Priority
        $priorites 	= $this->getPriority();

        $options	= '';
        $checked	= array();
        foreach ($priorites as $priority) {
            $options .= '<option value="'.$priority->id.'">'.$this->escape($priority->name).'</option>';
            $checked[] = $priority->id;
        }

        $element = new SimpleXMLElement('<fieldset name="priority" label="'.JText::_('RST_REPORTS_PRIORITY').'"><field name="priority" type="checkboxes" label="" description="" class="checkbox inline" checked="'.implode(',', $checked).'">'.$options.'</field></fieldset>');
        $form->setField($element);

        return $form;
    }

    protected function loadFormData()
    {
        return array();
    }
}