<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Declaration_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('declaration_report');
	}
	
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$this->save_info();
		$role_info   = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
		$role_result = $role_info->result();
		$role_info->next_result();
		$process_role = array();
		foreach($role_result as $for){
			$role_id   = $for->prime_category_id;
			$category_name = $for->category_name;
			$process_role[$role_id] = $category_name;
		}
		$data['process_role'] = $process_role;
		$fin_set_id       = $this->financial_info[0]->prime_financial_setting_id;
        $start_date       = $this->financial_info[0]->start_date;
        $end_date         = $this->financial_info[0]->end_date;
        $data['start_date']	= date('d-m-Y',strtotime($start_date));
		$data['end_date'] 	= date('d-m-Y',strtotime($end_date));
		$this->load->view("$this->control_name/manage",$data);
	}
	
	// AUTOCOMPLETE FOR SERACH EMPLOYEE
	public function emp_suggest(){
		$search_term  = $this->input->post_get('term');
		$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
		$final_data   = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
		$final_result = $final_data->result();
		$final_data->next_result();
		foreach($final_result as $rslt){
			$employee_code = $rslt->employee_code;
			$emp_name      = $rslt->emp_name;
			$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
		}
		if(empty($suggestions)){
			$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
		}
		echo json_encode($suggestions);
	}
	public function get_table_data(){
		$this->save_info();
		$process_type   = $this->input->post('process_type');
		$process_emp_id = $this->input->post('process_emp_id');
		$process_role   = implode(',', $this->input->post('process_role'));
		$effective_month= $this->input->post('effective_month');
		if($process_type == '1'){
			$tax_where_query = 'emp_code ="'.$process_emp_id.'" and trans_status = 1';
		}else
		if($process_type == '2'){
			$tax_where_query = 'category in ('.$process_role.') and trans_status = 1';
		}

		$fin_set_id       = $this->financial_info[0]->prime_financial_setting_id;
        $start_date       = $this->financial_info[0]->start_date;
        $end_date         = $this->financial_info[0]->end_date;
        //For Display in PDF
        $process_month    = $effective_month;
        $process_date     = date("Y-m-d", strtotime("01-" . $process_month));

        $emp_qry 	= 'select employee_code,emp_name from cw_employees where trans_status = 1';
        $emp_info   = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
        $emp_rslt   = $emp_info->result_array();
        $emp_info->next_result();
        $emp_rslt = array_reduce($emp_rslt, function($result, $arr){			
	    	$result[$arr['employee_code']] = $arr['emp_name'];
	    	return $result;
		}, array());

        $process_month_dt	   = "01-".$process_month;    
        $section_dec_query 	   = 'SELECT a.* FROM cw_declaration_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_declaration_entry where ' .$tax_where_query. ' and  finacial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") and trans_status = 1 GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date and a.trans_status = 1';
        
        $section_dec_data      = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
        $section_dec_result    = $section_dec_data->result_array();
        $section_dec_data->next_result();

        $section_qry 	= 'select tax_subsection_column,tax_act_details,tax_section_column,cw_tax_section.tax_section from cw_tax_section inner join cw_tax_sub_section on cw_tax_sub_section.tax_section = cw_tax_section.prime_tax_section_id where cw_tax_section.trans_status = 1 and cw_tax_sub_section.trans_status = 1';
        $section_info   = $this->db->query("CALL sp_a_run ('SELECT','$section_qry')");
        $section_rslt   = $section_info->result_array();
        $section_info->next_result();
        $section_label = array_reduce($section_rslt, function($result, $arr){			
	    	$result[$arr['tax_subsection_column']] = $arr['tax_act_details'];
	    	return $result;
		}, array());
        $tax_subsection_column = array_column($section_rslt, 'tax_subsection_column');

        foreach($section_dec_result as $key =>$val){
			$total  = 0;
			$arr_td = "<td>".$val['tax_house_rent']."</td><td>".$val['childran_elig']."</td>";
			$role_line = "<th>House Rent Paid (Annual)</th><th>No of Children Eligible for Education</th>";
			foreach($tax_subsection_column as $name =>$sec_val){
				$role_line 	   .= "<th>".$section_label[$sec_val]."</th>";
				$arr_td 	   .="<td>".$val[$sec_val]."</td>";
				$employee_name  = $arrear_array[$key][$arrear]['employee_name'];
			}
			if($val['income_tax_type'] === '2'){
				$income_tax_type = "NEW Regime";
			}else{
				$income_tax_type = "OLD Regime";
			}
			$tbl_line .= "<tr><td>".$val['emp_code']."</td><td>".$emp_rslt[$val['emp_code']]."</td><td>".$income_tax_type."</td>$arr_td</tr>";
		}
		$view_content = "<table class='table table-striped table-bordered' id='role'>
							<thead>
								<tr>
									<th scope='col'>Employee Code</th>
									<th scope='col'>Employee Name</th>
									<th scope='col'>Incometax Type</th>
									$role_line
								</tr>
							</thead>
							    $tbl_line
						</table>";
		echo json_encode(array("success" => TRUE,'message' => $view_content,"count" => $count));
	}
}
?>