<?php
if (!defined("ROOT_PATH"))
{
	header("HTTP/1.1 403 Forbidden");
	exit;
}
class pjAdminReports extends pjAdmin
{
		
	public function pjActionIndex()
	{
		$this->checkLogin();
		
		if ($this->isAdmin() || $this->isEditor())
		{
			$date_from = date('Y-m-d', time() - 30 * 86400 );
			$date_to = date('Y-m-d');
			
			$this->set('date_from', $date_from);
			$this->set('date_to', $date_to);
			
			$this->appendJs('pjAdminReports.js');
		} else {
			$this->set('status', 2);
		}
	}
	public function pjActionGenerate()
	{
		$this->setAjax(true);
	
		if ($this->isXHR())
		{
			if (isset($_POST['generate_report']))
			{
				$date_from = pjUtil::formatDate($_POST['date_from'], $this->option_arr['o_date_format']);
				$date_to = pjUtil::formatDate($_POST['date_to'], $this->option_arr['o_date_format']);
				
				$pjTicketModel = pjTicketModel::factory();
				$pjReplyModel = pjReplyModel::factory();
				
				$tickets_received = $pjTicketModel->where("(DATE(t1.created) BETWEEN '$date_from' AND '$date_to')")->findCount()->getData();
				$ticket_id_arr = $pjTicketModel->findAll()->getDataPair(null, 'id');
				
				$client_replies = 0;
				$support_replies = 0;
				$ratings_received = 0;
				$average_rating = 0;
				$user_arr = array();
				$department_arr = array();
				if(!empty($ticket_id_arr))
				{
					$client_replies = $pjReplyModel->whereIn('ticket_id', $ticket_id_arr)->where("(t1.client_id IS NOT NULL)")->findCount()->getData();
					$support_replies = $pjReplyModel->reset()->whereIn('ticket_id', $ticket_id_arr)->where("(t1.user_id IS NOT NULL)")->findCount()->getData();
					$ratings_received = $pjReplyModel->reset()->whereIn('ticket_id', $ticket_id_arr)->where("(t1.user_id IS NOT NULL)")->where("(t1.rate IS NOT NULL)")->findCount()->getData();
					$rating_arr= $pjReplyModel->findAll()->getData();
					$total_ratings = 0;
					foreach($rating_arr as $k => $v)
					{
						$total_ratings += (int) $v['rate'];
					}
					if($ratings_received > 0)
					{
						$average_rating = number_format($total_ratings / $ratings_received, 2);
					} 
					
					$user_arr = pjUserModel::factory()
						->select("t1.*,
								(SELECT COUNT(DISTINCT TR.ticket_id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN(".join(",", $ticket_id_arr).") AND `TR`.user_id=t1.id ) AS tickets,
								(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN(".join(",", $ticket_id_arr).") AND `TR`.user_id=t1.id ) AS replies,
								(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN(".join(",", $ticket_id_arr).") AND `TR`.user_id=t1.id AND `TR`.rate IS NOT NULL ) AS ratings,
								(SELECT SUM(TR.rate)/COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN(".join(",", $ticket_id_arr).") AND `TR`.user_id=t1.id AND `TR`.rate IS NOT NULL ) AS avg_rating")
						->where('t1.status', 'T')
						->orderBy("t1.name ASC")
						->findAll()
						->getData();
					
					$tbl_name = pjTicketModel::factory()->getTable();
					
					$department_arr = pjDepartmentModel::factory()
						->select("t1.id, t1.status, t2.content AS name,
								(SELECT COUNT(DISTINCT TR.ticket_id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id=t1.id )) AS tickets,
								(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id=t1.id )) AS replies,
								(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id=t1.id ) AND `TR`.rate IS NOT NULL ) AS ratings,
								(SELECT SUM(TR.rate)/COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id=t1.id )AND `TR`.rate IS NOT NULL ) AS avg_rating")
						->join('pjMultiLang', "t2.foreign_id = t1.id AND t2.model = 'pjDepartment' AND t2.locale = '".$this->getLocaleId()."' AND t2.field = 'name'", 'left')
						->where('t1.status', 'T')
						->orderBy("name ASC")
						->findAll()
						->getData();
					
					$info_arr = $pjTicketModel->reset()->prepare("SELECT 1,
						(SELECT COUNT(DISTINCT TR.ticket_id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id IS NULL )) AS tickets,
						(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id IS NULL )) AS replies,
						(SELECT COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id IS NULL ) AND `TR`.rate IS NOT NULL ) AS ratings,
						(SELECT SUM(TR.rate)/COUNT(TR.id) FROM `".$pjReplyModel->getTable()."` AS `TR` WHERE `TR`.ticket_id IN( SELECT TK.id FROM `".$tbl_name."` AS `TK` WHERE `TK`.id IN(".join(",", $ticket_id_arr).") AND `TK`.department_id IS NULL)AND `TR`.rate IS NOT NULL ) AS avg_rating		
					")->exec(array('user_id' => $this->getUserId()))->getData();
					
					$this->set('info_arr', $info_arr);
					
				}
				
				$this->set('tickets_received', $tickets_received);
				$this->set('client_replies', $client_replies);
				$this->set('support_replies', $support_replies);
				$this->set('ratings_received', $ratings_received);
				$this->set('average_rating', $average_rating);
				
				$this->set('user_arr', $user_arr);
				$this->set('department_arr', $department_arr);
			}
		}
	}
}
?>