<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Tvc_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('tvc_report');
		$this->collect_base_info();
	}
	
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		
		$this->load->view("$this->control_name/manage",$data);
	}
	//TVC CALL REPORT TABLE 
	public function pending_details(){
		$start_date = $this->input->post('from_date');
		$end_date   = $this->input->post('to_date');
		$level1    	= $this->input->post('level1');
		$level2   	= $this->input->post('level2');
		$level1_list = implode('","', $level1).'';
		$level2_list = implode('","', $level2).'';
		$lvl1        = implode(",",$level1);
		$lvl2		 = implode(",",$level2); 
		$from_date   = date("Y-m-d", strtotime($start_date));
		$to_date     = date("Y-m-d", strtotime($end_date));
		
		//QUERY FOR TVC DETAILS TABLE
		$pending_query = 'SELECT cw_tvc_call.tvc_call,cw_tvc_call.prime_tvc_call_id,COUNT(*)AS total_count FROM cw_health INNER JOIN cw_tvc_call ON cw_tvc_call.prime_tvc_call_id =cw_health.tvc_call where cw_health.trans_status=1 and cw_health.actual_level_1 IN ("'.$level1_list.'") and cw_health.actual_level_2 IN("'.$level2_list.'") and cw_health.login_date BETWEEN "'.$from_date.'" AND "'.$to_date.'" group by cw_tvc_call.tvc_call';
		$pending_details_info= $this->db->query("CALL sp_a_run ('SELECT','$pending_query')");
		$pending_details_result = $pending_details_info->result_array();
		$pending_details_info->next_result();

		$tvc_query = 'SELECT prime_tvc_call_id,tvc_call from cw_tvc_call where cw_tvc_call.trans_status = 1';
		$tvc_details_info= $this->db->query("CALL sp_a_run ('SELECT','$tvc_query')");
		$tvc_details_result = $tvc_details_info->result_array();
		$tvc_details_info->next_result();

		if(!$pending_details_result){
			echo json_encode(array('success' => False, 'message' => "No data available"));
			exit(0);
		}
		$exist_arr   = array_column($pending_details_result, 'prime_tvc_call_id');
		$pending_details_arr = array_reduce($pending_details_result, function ($result, $arr){
		    $result[$arr['prime_tvc_call_id']] = $arr;
		    return $result;
		}, array());
		$pending_arr = array();
		$tr_line = '<tr>';
		$td_line = '<tr>';
		foreach ($tvc_details_result as $key => $value) {
			if(in_array($value['prime_tvc_call_id'],$exist_arr)){
				$prime_tvc_call_id = $pending_details_arr[$value['prime_tvc_call_id']]['prime_tvc_call_id'];
				$tvc_call          = $pending_details_arr[$value['prime_tvc_call_id']]['tvc_call'];
				$total_count       = $pending_details_arr[$value['prime_tvc_call_id']]['total_count'];
				$pending_arr[$tvc_call]=array('tvc_call_id'=>$prime_tvc_call_id,'count'=>$total_count);

				if($prime_tvc_call_id !=='1'){
					$overall_total = $overall_total + $total_count;
				}
				
			}else{
				$pending_arr[$value['tvc_call']] = array('tvc_call_id'=>$value['prime_tvc_call_id'],'count'=>0);
			}
			$tvc_id  = $value['prime_tvc_call_id'];
			$tr_line .='<td style="text-align:center;">'.$value['tvc_call'].'</td>';
			$td_line .='<td style="text-align:center;cursor:pointer;color:blue;font-weight:bold;" onClick=popup_data("'.$tvc_id.'","'.$lvl1.'","'.$lvl2.'","'.$from_date.'","'.$to_date.'")>'.$pending_arr[$value['tvc_call']]['count'].'</td>';
		}
		$tr_line   .='<td style="text-align:center;">Total Pending</td></tr>';
		$td_line   .= '<td style="text-align:center; cursor:pointer;color:red;" onClick=popup_data("'.$tvc_id.'","'.$lvl1.'","'.$lvl2.'","'.$from_date.'","'.$to_date.'","overall_tot")>'.$overall_total.'</td></tr>';
		$table_info ='<div  class="col-md-12" ><h3>Tvc Report</h3><table class="table table-hover" id="pending_details_table"><thead>'.$tr_line.'</thead><tbody>'.$td_line.'</tbody></table></div>';

		echo json_encode(array('success' => true, 'message' => "success",'pending_table'=>$table_info));
	}

	//POPUP FOR TVC CALL TABLE
	public function show_popup_data(){
		$tvc_id      = $this->input->post('tvc_id');
		$start_date  = $this->input->post('from_date');
		$end_date    = $this->input->post('to_date');
		$level1      = $this->input->post('level1');
		$level2      = $this->input->post('level2');
		$overall_tot = $this->input->post('total');
		$from_date   = date("Y-m-d", strtotime($start_date));
		$to_date     = date("Y-m-d", strtotime($end_date));

		$level1_list = explode(",", $level1);
		$level2_list = explode(",", $level2);
		$lvl1        =  implode('","', $level1_list).'';
		$lvl2		 =  implode('","', $level2_list).'';

		//	OVERALL TOTAL FOR OVERALL PENDING TOTAL COLUMN
		if($overall_tot === 'overall_tot'){
			$where_cond   ='and cw_health.tvc_call !="1" and cw_health.actual_level_1 in ("'.$lvl1.'") and cw_health.actual_level_2 in ("'.$lvl2.'") and cw_health.login_date BETWEEN "'.$from_date.'" AND "'.$to_date.'"';
		}else{
			$where_cond   =' and cw_health.tvc_call="'.$tvc_id.'" and cw_health.actual_level_1 in ("'.$lvl1.'") and cw_health.actual_level_2 in ("'.$lvl2.'") and cw_health.login_date BETWEEN "'.$from_date.'" AND "'.$to_date.'"';
		}
		//QUERY FOR POPUP DATA
		$popup_query ='SELECT cw_employees.employee_name,cw_health_company.company_name,cw_health_product.product_name,cw_health.policy_number,cw_health.proposer_name,l1.employee_name as level1_manager,l2.employee_name as level2_manager,cw_health.policy_from_date,cw_health.policy_to_date,cw_health.remark,cw_tvc_call.tvc_call,CASE	WHEN cw_health.coverage_type =1 THEN "individual" WHEN cw_health.coverage_type =2 THEN "flotter" END AS coverage_type,cw_health.login_date FROM cw_health	INNER JOIN cw_employees ON cw_employees.employee_code = cw_health.employee_code	INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id=cw_health.company	INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id = cw_health.product INNER JOIN cw_tvc_call ON cw_tvc_call.prime_tvc_call_id =  cw_health.tvc_call	LEFT JOIN cw_employees l1 ON l1.employee_code = cw_health.actual_level_1 AND l1.employee_code IN ("'.$lvl1.'") LEFT JOIN cw_employees l2 ON l2.employee_code = cw_health.actual_level_2 AND l2.employee_code IN ("'.$lvl2.'") WHERE cw_health.trans_status=1 '.$where_cond.'';
		$popup_info  = $this->db->query("CALL sp_a_run ('SELECT','$popup_query')");
		$popup_result= $popup_info->result();
		$popup_info->next_result();
		echo json_encode(array('success' => TRUE,'popup_data' => $popup_result));
	}

	//MULTI PICKLIST DATA GET FROM TABLE
	public function get_reporting_list(){
		$start_date  = date("Y-m-d", strtotime($this->input->post('from_date')));
		$end_date    = date("Y-m-d", strtotime($this->input->post('to_date')));
		$logged_role = $this->session->userdata('logged_role');
		$logged_code = $this->session->userdata('employee_code');
		$qry = "";
		if((int)$logged_role === 3 || (int)$logged_role === 5){ //Added for RM and RE Users
			$qry = ' and (cw_health.actual_level_1 = "'.$logged_code.'" || cw_health.actual_level_2 = "'.$logged_code.'")';
		}

		//QUERY FOR LEVEL1 MANAGER
		$first_level_qry='SELECT cw_employees.employee_code,cw_health.prime_health_id,cw_employees.employee_name FROM cw_health INNER JOIN cw_employees ON cw_health.actual_level_1=cw_employees.employee_code 
		WHERE cw_health.trans_status=1 and cw_health.login_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" '.$qry.' group by cw_health.actual_level_1';
		$first_level_info= $this->db->query("CALL sp_a_run ('SELECT','$first_level_qry')");
		$first_level_result = $first_level_info->result();
		$first_level_info->next_result();
		$first_list = "<option disabled='disabled'>---select---</option>";
		foreach($first_level_result as $for){
			$health_id         = $for->prime_health_id;
			$emp_name          = $for->employee_name;
			$first_level_id    = $for->employee_code;
			$first_list       .= "<option value='$first_level_id'> $first_level_id-$emp_name</option>";
		}
		//QUERY FOR LEVEL 2 MANAGER
		$second_level_qry='SELECT cw_employees.employee_code,cw_health.prime_health_id,cw_employees.employee_name FROM cw_health INNER JOIN cw_employees ON cw_health.actual_level_2=cw_employees.employee_code 
		WHERE cw_health.trans_status=1 and cw_health.login_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" '.$qry.' group by cw_health.actual_level_2';
		$second_level_info= $this->db->query("CALL sp_a_run ('SELECT','$second_level_qry')");
		$second_level_result = $second_level_info->result();
		$second_level_info->next_result();
		$second_list = "<option  disabled='disabled'>---select---</option>";
		foreach($second_level_result as $level_2){
			$health_id     = $level_2->prime_health_id;
			$emp_name      = $level_2->employee_name;
			$level2_id 	   = $level_2->employee_code;
			$second_list  .= "<option value='$level2_id'> $level2_id-$emp_name</option>";
		}
		echo json_encode(array('success' => True, 'message' => "Success",'list1'=>$first_list,'list2'=>$second_list));
	}
}
?>