<?php
class Dashboard_Model extends CI_Model
{
	//MASTER DETAIL
	private $tableName = array(
		'user_type' => 'tms_user_type',
		"action" => "tms_action_master",
		"shift" => "tms_shift_master",
		"user" => "tms_user_master",
		"company" => "tms_company_master",
		"priority" => "tms_priority_master",
		"emailAccount" => "tms_email_account",
		"pagination" => "tms_pagination_master",
		"category" => "tms_category_master",
		"sub-category" => "tms_subcategory_master",
		"status" => "tms_status_master",
		"rule" => "tms_rule_master",
		"rule_condition" => "tms_rule_condition",
		"queue" => "tms_queue_master",
		"template" => "tms_template_master",
		"escalation-matrix" => "tms_escalationmatrix_master",
		"user_log" => "tms_log_master",
		"login_log" => "tms_login_log",
		"ticket" => "tms_new_ticket",
		"field-group" => "tms_field_group",
		"conversation-type" => "tms_conversation_type_master",
		"queue_status" => "tms_queue_status"
	);

	function __construct()
	{
		parent::__construct();
	}


	function getTotalQueueRecord($filter)
	{
		$this->db->select('CONCAT(UPPER(SUBSTRING(t1.status_name, 1, 1)), LOWER(SUBSTRING(t1.status_name, 2))) AS status_name, t1.colour_code, COUNT(t2.ticket_unique_id) as ticket_count');
		$this->db->from('tms_queue_status_' . $filter->queue_id . ' t1');
		$this->db->join('tms_ticket_master_' . $filter->queue_id . ' t2', 't2.status_id = t1.status_id', 'left');
		$this->db->group_by('t1.status_name, t1.colour_code');
		$this->db->order_by('t1.status_name', 'ASC');
		$data = $this->db->get();
		$ret_data = array(
			'records' => $data->result_array()
		);

		return $ret_data;
	}

	function getQueueRecord($filter)
	{
		$from = date("Y-m-d H:i:s", strtotime($filter->from_date));
        $to = date("Y-m-d H:i:s", strtotime($filter->to_date));
		$query = $this->db->query("CALL getDashboardData('".$from."', '".$to."','".$filter->queue_id."')");
		     $ret_data = array(
				'records' => $query->result_array()
		);
		return $ret_data;
	}

	// function getQueueStatusRecord($queue_status, $queue_id,$assign_to,$full_control)
	// {
	// 	$sql = "CALL queueStatusWiseReport('" . $queue_status . "', '" . $queue_id . "','".$assign_to."','".$full_control."');";
	// 	$multiQuery = $this->db->conn_id->multi_query($sql);

	// 	$result1 = [];
	// 	$result2 = [];

	// 	if ($multiQuery) {
	// 		$result = $this->db->conn_id->store_result();

	// 		if ($result) {
	// 			$result1 = $result->fetch_all(MYSQLI_ASSOC);
	// 			$result->free();
	// 		}

	// 		while ($this->db->conn_id->more_results() && $this->db->conn_id->next_result()) {
	// 			$result = $this->db->conn_id->store_result();

	// 			if ($result) {
	// 				$result2 = $result->fetch_all(MYSQLI_ASSOC);
	// 				$result->free();
	// 			}
	// 		}
	// 	}

	// 	$ret_data = array(
	// 		'queue_records' => $result1,
	// 		'status_records' => $result2
	// 	);
	
	// 	return $ret_data;
	// }


	function getQueueStatusRecord($queue_status, $queue_id, $assign_to, $full_control)
	{
		$sql = "CALL queueStatusWiseReport('" . $queue_status . "', '" . $queue_id . "','" . $assign_to . "','" . $full_control . "','" . $this->allTicket . "');";

		$multiQuery = $this->db->conn_id->multi_query($sql);

		// $result_sets = array();

		if ($multiQuery) {
			do {
				if ($result = $this->db->conn_id->store_result()) {
					$result_sets[] = $result->fetch_all(MYSQLI_ASSOC);
					$result->free();
				}
			} while ($this->db->conn_id->more_results() && $this->db->conn_id->next_result());
		}

		
		

		// Output the organized data

		return $result_sets;
	}




	function getDashboardCardRecord($user_id, $queue_id)
	{
		// $this->db->select('COUNT(t1.ticket_unique_id) as count,CONCAT(UPPER(SUBSTRING(t2.status_name, 1, 1)), LOWER(SUBSTRING(t2.status_name, 2))) AS status_name,t2.colour_code');
		// $this->db->from('tms_ticket_master_' . $queue_id . ' t1');
		// $this->db->join('tms_queue_status_' . $queue_id . ' t2', 't2.status_id = t1.status_id', 'left');
		// $this->db->where('t1.assigned_to=' . $user_id);
		// $this->db->group_by('t2.status_name,t2.colour_code');
		// $data = $this->db->get();
		// $ret_data = array(
		// 	'records' => $data->result_array()
		// );

		// return $ret_data;
	}

	function dbFreeRes()
	{
		$conn = $this->db->conn_id;
		do {
			if ($result = mysqli_store_result($conn)) {
				mysqli_free_result($result);
			}
		} while (mysqli_more_results($conn) && mysqli_next_result($conn));
	}


	// function getDashboardDetails($childId,$data,$full_control){
        
	// 	$from_date = !empty($data['from_date'])?date("Y-m-d", strtotime($data['from_date'])):'';
    //     $to_date = !empty($data['to_date'])?date("Y-m-d", strtotime($data['to_date'])):'';
	// 	$condition = 'created_date >'.'"'.$from_date.'" AND created_date <'.'"'.$to_date.'"';
	// 	if($full_control== '1'){
	// 		$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$data['queue_id']."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','1')")->result_array();
	// 	}else{
	// 		$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$data['queue_id']."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','0')")->result_array();
			
	// 	}
		
	// 	$this->dbFreeRes();
	// 	if($full_control == '1'){
	// 		$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$data['queue_id']."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','1')")->result_array();

	// 	}else{
	// 		$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$data['queue_id']."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','0')")->result_array();
	// 	}
	// 	$this->dbFreeRes();
	// 	$categoryTable=$this->db->query("CALL getCategoryWiseTicketCount('".$data['queue_id']."', '',  '".$childId."', '".$from_date."', '".$to_date."', '".$full_control."')");

	// 	$this->dbFreeRes();
	// 	$uniqueCategories = array();
    //     $mainData=array();

	// 	$outputArray = [];
	// 	$statusNames = array_values(array_unique(array_column($category, 'status_name')));

	// 	foreach ($category as $item) {
	// 		$categoryName = $item['category_name'];
	// 		$statusName = $item['status_name'];
			
	// 		if (!isset($outputArray[$categoryName])) {
	// 			$outputArray[$categoryName] = [
	// 				'category_name' => $categoryName,
	// 				'status_name'=> $statusName
	// 			];
	// 		}
			
	// 		$outputArray[$categoryName]['statuses'][] = [
	// 			'status_name' => $statusName,
	// 			'data' => $item
	// 		];
	// 	}

	// 	$resultArray = array_values($outputArray); 

		
		
    //     $categorytable= array(
	// 		'result'=> $resultArray,
	// 		// 'category'=>$categoryNames,
	// 		'status' => $statusNames
	// 	);
		
		
	// 	$ret_data = array(
	// 		'records' => $query,
	// 		'category' => $category,
	// 		'categoryTable'=>$categorytable,
	// 		'full_control'=>$full_control
	// 	);
	// 	return $ret_data;
	// }


	// function getDashboardDetails($childId,$data,$full_control,$queue_id){
        
	// 	$from_date = !empty($data['from_date'])?date("Y-m-d", strtotime($data['from_date'])):'';
    //     $to_date = !empty($data['to_date'])?date("Y-m-d", strtotime($data['to_date'])):'';
	// 	$condition = 'created_date >'.'"'.$from_date.'" AND created_date <'.'"'.$to_date.'"';
	// 	if($full_control== '1'){
	// 		$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat_test_20240214('".$queue_id."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','1')")->result_array();
	// 	}else{
	// 		$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat_test_20240214('".$queue_id."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','0')")->result_array();
			
	// 	}
		
	// 	$this->dbFreeRes();
	// 	if($full_control == '1'){
	// 		$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat_test_20240214('".$queue_id."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','1')")->result_array();

	// 	}else{
	// 		$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat_test_20240214('".$queue_id."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','0')")->result_array();

	// 	}
	// 	$this->dbFreeRes();
	// 	$categoryTable=$this->db->query("CALL getTicketCountWithInTatOutOfTat_test_20240214('".$queue_id."', '',  '".$childId."', '".$from_date."', '".$to_date."', '".$full_control."')");

	// 	$this->dbFreeRes();
	// 	$uniqueCategories = array();
    //     $mainData=array();

	// 	$outputArray = [];
	// 	$statusNames = array_values(array_unique(array_column($category, 'status_name')));
    //      $data=array();
		 
	// 	foreach ($query as $key => $value) {
	// 		if($value['queue_id'] == ''){
    //           $data['all_record'][] = $value;
	// 		}else if($value['queue_id'] != ''){
	// 			if($value['queue_id'] == $value['queue_id']){
	// 				$data['queue_wise_record'][$value['queue_name']][] = $value;
	// 			}
			  
	// 		}
	// 		// $data['all_data'] = $query;
	// 	}

	// 	$categoryData=array();
	// 	foreach ($category as $item) {

    //         if($item['queue_id'] == ''){
	// 			$categoryData['category_data_all'][] = $item;
	// 		  }else if($item['queue_id'] != ''){
	// 			  if($item['queue_id'] == $item['queue_id']){
	// 				  $categoryData['category_queue_wise_record'][$item['queue_name']][] = $item;
	// 			  }
				
	// 		  }

	// 		// $categoryName = $item['category_name'];
	// 		// $statusName = $item['status_name'];
			
	// 		// if (!isset($outputArray[$categoryName])) {
	// 		// 	$outputArray[$categoryName] = [
	// 		// 		'category_name' => $categoryName,
	// 		// 		'status_name'=> $statusName
	// 		// 	];
	// 		// }
			
	// 		// $outputArray[$categoryName]['statuses'][] = [
	// 		// 	'status_name' => $statusName,
	// 		// 	'data' => $item
	// 		// ];
	// 	}

	// 	$resultArray = array_values($outputArray); 

		
		
    //     $categorytable= array(
	// 		'result'=> $resultArray,
	// 		// 'category'=>$categoryNames,
	// 		'status' => $statusNames
	// 	);
		
		
	// 	$ret_data = array(
	// 		'records' => $data,
	// 		'category' => $categoryData,
	// 		'categoryTable'=>$categorytable,
	// 		'full_control'=>$full_control
	// 	);
	// 	return $ret_data;
	// }



	function getDashboardDetails($childId,$data,$full_control,$queue_id){

        
			$from_date = !empty($data['from_date'])?date("Y-m-d", strtotime($data['from_date'])) . ' 00:00:00':'';
		    $to_date = !empty($data['to_date'])?date("Y-m-d", strtotime($data['to_date'])) . ' 23:59:59':'';

			$condition = 'created_date >'.'"'.$from_date.'" AND created_date <'.'"'.$to_date.'"';
			if($full_control== '1'){
				$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$queue_id."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','1', '".$this->allTicket."')")->result_array();
				//  print_r($this->db->last_query());die;
			}else{
				$query = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$queue_id."','', '".$childId."', '".$full_control."','".$from_date."', '".$to_date."','0','0', '".$this->allTicket."')")->result_array();
				// print_r($this->db->last_query());die;
				
			}

			$this->dbFreeRes();
			if($full_control == '1'){
				$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$queue_id."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','1', '".$this->allTicket."')")->result_array();
				// print_r($this->db->last_query());die;
			}else{
				$category = $this->db->query("CALL getTicketCountWithInTatOutOfTat('".$queue_id."','', '".$childId."', '".$full_control."', '".$from_date."', '".$to_date."','1','0', '".$this->allTicket."')")->result_array();
				// print_r($this->db->last_query());die;
			}
			$this->dbFreeRes();
			// $categoryTable=$this->db->query("CALL getTicketCountWithInTatOutOfTat('".$queue_id."', '',  '".$childId."', '".$from_date."', '".$to_date."', '".$full_control."')");

			$this->dbFreeRes();
			$uniqueCategories = array();
		    $mainData=array();
	
			$outputArray = [];
			$statusNames = array_values(array_unique(array_column($category, 'status_name')));
		     $data=array();
			 
			foreach ($query as $key => $value) {
				if($value['queue_id'] == ''){
		          $data['all_record'][] = $value;
				}else if($value['queue_id'] != ''){
					if($value['queue_id'] == $value['queue_id']){
						$data['queue_wise_record'][$value['queue_name']][] = $value;
					}
				}
				// $data['all_data'] = $query;
			}

	
			$categoryData=array();
			foreach ($category as $item) {
	
		        if($item['queue_id'] == ''){
					$categoryData['category_data_all'][] = $item;
				  }else if($item['queue_id'] != ''){
					  if($item['queue_id'] == $item['queue_id']){
						  $categoryData['category_queue_wise_record'][$item['queue_name']][] = $item;
					  }
					
				  }
	
				// $categoryName = $item['category_name'];
				// $statusName = $item['status_name'];
				
				// if (!isset($outputArray[$categoryName])) {
				// 	$outputArray[$categoryName] = [
				// 		'category_name' => $categoryName,
				// 		'status_name'=> $statusName
				// 	];
				// }
				
				// $outputArray[$categoryName]['statuses'][] = [
				// 	'status_name' => $statusName,
				// 	'data' => $item
				// ];
			}
	
			$resultArray = array_values($outputArray); 
	
			
			
		    $categorytable= array(
				'result'=> $resultArray,
				// 'category'=>$categoryNames,
				'status' => $statusNames
			);
			
			
			$ret_data = array(
				'records' => $data,
				'category' => $categoryData,
				'categoryTable'=>$categorytable,
				'full_control'=>$full_control
			);
			return $ret_data;
		}
}
