<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Seating_capacity_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('seating_capacity_report');
		$this->collect_base_info();
	}
	
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$data['quick_link']    = $this->quick_link;
		$data['table_head']    = $this->table_head;
		$data['master_pick']   = $this->master_pick;
		$data['fliter_list']   = $this->fliter_list;
		$data['freeze_list']   = $this->freeze_list;
		$this->load->view("$this->control_name/manage",$data);
	}
	
	public function get_budget(){
		$emp_array         = "";
		$seating_arr       = "";
		$tr_line           = "";

			//$emp_qry     = 'select count(login_code) as emp_count,cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,cw_seating_capacity.total_count from cw_employees inner join cw_seating_capacity on cw_seating_capacity.band = cw_employees.band inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_band on cw_band.prime_band_id = cw_employees.band where cw_employees.login_code != "" and cw_employees.employee_status = 0 and cw_employees.trans_status = 1 and cw_seating_capacity.branch = cw_employees.branch and cw_seating_capacity.channel_name = cw_employees.channel_name GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.band';
			/*$active_emp_qry     = 'SELECT cw_branch.branch_name,cw_channel.channel_name,
	        SUM(CASE WHEN cw_employees.band = '1' THEN 1 ELSE 0 END) as S,
	        SUM(CASE WHEN cw_employees.band = '2' THEN 1 ELSE 0 END) as M,
	        SUM(CASE WHEN cw_employees.band = '3' THEN 1 ELSE 0 END) as L,
	        SUM(CASE WHEN cw_employees.band = '4' THEN 1 ELSE 0 END) as T
	FROM cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_seating_capacity on cw_seating_capacity.band = cw_employees.band
	GROUP BY cw_employees.branch,cw_employees.channel_name';*/


	    // SUM(CASE WHEN cw_employees.band = 1 THEN 1 ELSE 0 END) as S,
	    // SUM(CASE WHEN cw_employees.band = 2 THEN 1 ELSE 0 END) as M,
	    // SUM(CASE WHEN cw_employees.band = 3 THEN 1 ELSE 0 END) as L,
	    // SUM(CASE WHEN cw_employees.band = 4 THEN 1 ELSE 0 END) as T 

	    //SELECT QUERY FOR GET A ACTIVE EMPLOYEE LIST FROM EMPLOYEES TABLE
		$active_emp_qry     = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,count(*) as count,SUM(CASE WHEN cw_employees.band = 1 THEN 1 ELSE 0 END) as S,
	        SUM(CASE WHEN cw_employees.band = 2 THEN 1 ELSE 0 END) as M,
	        SUM(CASE WHEN cw_employees.band = 3 THEN 1 ELSE 0 END) as L,
	        SUM(CASE WHEN cw_employees.band = 4 THEN 1 ELSE 0 END) as T 
		    from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band where cw_employees.employee_status = 0 and cw_employees.trans_status = 1 GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.band';
		$emp_info           = $this->db->query("CALL sp_a_run ('SELECT','$active_emp_qry')");
		$emp_result         = $emp_info->result_array();
		$emp_info->next_result();

		$emp_arr            = array_reduce($emp_result, function ($result, $arr) {
		    $result[$arr['branch_name']][$arr['channel_name']][$arr['band_value']] = $arr;
		    return $result;
		}, array());

		// echo "<pre>";
		// print_r($emp_arr);
		//SELECT QUERY FOR GET A SEATING CAPACITY COUNT FROM SEATING CAPACITY TABLE
		$budget_capacity_qry  = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,actual_count,
			CASE WHEN cw_budget.band = 1 THEN actual_count ELSE 0 END as S,
		    CASE WHEN cw_budget.band = 2 THEN actual_count ELSE 0 END as M,
		    CASE WHEN cw_budget.band = 3 THEN actual_count ELSE 0 END as L,
		    CASE WHEN cw_budget.band = 4 THEN actual_count ELSE 0 END as T 
		    from cw_budget inner join cw_channel on cw_channel.prime_channel_id = cw_budget.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_budget.branch inner join cw_band on cw_band.prime_band_id = cw_budget.band GROUP BY cw_budget.branch,cw_budget.channel_name,cw_budget.band';
		$budget_capacity_info = $this->db->query("CALL sp_a_run ('SELECT','$budget_capacity_qry')");
		$budget_capacity_rslt = $budget_capacity_info->result_array();
		$budget_capacity_info->next_result();

		$budget_arr        = array_reduce($budget_capacity_rslt, function ($result, $arr) {
		    $result[$arr['branch_name']][$arr['channel_name']][$arr['band_value']] = $arr;
		    return $result;
		}, array());
		// echo "<pre>";
		// print_r($seating_arr);die;
		foreach($budget_arr as  $branch => $channel_arr) {
			foreach($channel_arr as $channel => $band_arr) {
				// $hr_recruiter    = $band_arr[0]['hr_recruiter'];
				// $band_s_value    = $band_arr[0]['band_value'];
				$total_s_count   = (int)$band_arr['S']['S'];
				$active_s_count  = (int)$emp_arr[$branch][$channel]['S']['S'];
				$pen_s_count     = $total_s_count - $active_s_count;
				// $band_m_value    = $band_arr[1]['band_value'];
				$total_m_count   = (int)$band_arr['M']['M'];
				$active_m_count  = (int)$emp_arr[$branch][$channel]['M']['M'];
				$pen_m_count     = $total_m_count - $active_m_count;
				// $band_l_value    = $band_arr[2]['band_value'];
				$total_l_count   = (int)$band_arr['L']['L'];
				$active_l_count  = (int)$emp_arr[$branch][$channel]['L']['L'];
				$pen_l_count     = $total_l_count - $active_l_count;
				// $band_t_value    = $band_arr[3]['band_value'];
				$total_t_count   = (int)$band_arr['T']['T'];
				$active_t_count  = (int)$emp_arr[$branch][$channel]['T']['T'];
				$pen_t_count     = $total_t_count - $active_t_count;

				$tr_line        .=  "<tr>
										<!-- <td>$branch</td><td>$channel</td><td>$hr_recruiter</td><td>$total_s_count</td><td>$active_s_count</td><td>$pen_s_count</td><td>$total_m_count</td><td>$active_m_count</td><td>$pen_m_count</td><td>$total_l_count</td><td>$active_l_count</td><td>$pen_l_count</td><td>$total_t_count</td><td>$active_t_count</td><td>$pen_t_count</td> -->

										<td>$branch</td><td>$channel</td><td>$total_s_count</td><td>$active_s_count</td><td>$pen_s_count</td><td>$total_m_count</td><td>$active_m_count</td><td>$pen_m_count</td><td>$total_l_count</td><td>$active_l_count</td><td>$pen_l_count</td><td>$total_t_count</td><td>$active_t_count</td><td>$pen_t_count</td>
								 	</tr>";		
			}		
		}		

		$table_info     =  "<table id = 'budget_capacity_table' class = 'table table-hover'>
								<thead>
									<tr>
										<th rowspan='2'>BRANCH</th>
									    <th rowspan='2'>CHANNEL</th>
									    <th colspan='3'>S</th>
									    <th colspan='3'>M</th>
									    <th colspan='3'>L</th>
									    <th colspan='3'>T</th>
									</tr>
									<tr>
									    <th>ACTUAL</th>
									    <th>ACHIEVED</th>
									    <th>PENDING</th>
									    <th>ACTUAL</th>
									    <th>ACHIEVED</th>
									    <th>PENDING</th>
									    <th>ACTUAL</th>
									    <th>ACHIEVED</th>
									    <th>PENDING</th>
									    <th>ACTUAL</th>
									    <th>ACHIEVED</th>
									    <th>PENDING</th>
									</tr>								
								</thead>
								<tboady>
									$tr_line
								</tbody>
						   	</table>";

		if($tr_line){
			echo json_encode(array('success' => TRUE,'table_info' => $table_info));
		}else{
			echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
		}		
	}	
}
?>