<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Other_income_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('other_income_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;
		$data['key'] = $this->generateKey();
		$this->load->view("$this->control_name/manage",$data);
	}
	//AUTO COMPLETE FUNCTION
	public function emp_suggest(){
		$encString     = file_get_contents('php://input');
		$_POST         = $this->cryptoDecrypt($encString);
		if(!$_POST){
			echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
			exit(0);
		}
		$search_term  = $this->input->post_get('term');
		$final_qry    = 'select employee_code,emp_name from cw_employees where trans_status = 1 and prime_employees_id !=1  and (employee_code like ("%'.$search_term.'%")  OR emp_name 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);
	}
	//TABLE DISPLAY
	public function get_table_data(){
		$encString     = file_get_contents('php://input');
		$_POST         = $this->cryptoDecrypt($encString);
		if(!$_POST){
			echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
			exit(0);
		}
		$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 = 'cw_other_income_entry.emp_code ="'.$process_emp_id.'" ';
		}else
		if($process_type == '2'){
			$tax_where_query = 'cw_other_income_entry.category in ('.$process_role.') ';
		}
		$fin_set_id       = $this->financial_info[0]->prime_financial_setting_id;
		$process_month    = $effective_month;
		$process_month_dt	   = "01-".$process_month;  

		//EMPLOYEE NAME AND CO
		$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());
		$section_dec_query 	  = 'SELECT a.*,category_name FROM cw_other_income_entry as a INNER JOIN (SELECT emp_code,cw_category.category_name AS category_name, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_other_income_entry JOIN cw_category ON cw_category.prime_category_id = cw_other_income_entry.category where ' .$tax_where_query. ' and  financial_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 cw_other_income_entry.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 and financial_setting_id = "'.$fin_set_id.'"';

        $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 other_income_column,income_description from cw_other_income where 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['other_income_column']] = $arr['income_description'];
	    	return $result;
		}, array());
        $other_income_column = array_column($section_rslt, 'other_income_column');
		foreach ($section_dec_result as $key => $val) {
    		$total = 0;
    		$role_line = "";  
    		$arr_td = "";    
    		$arr_td = "<td>" . $val['category_name'] . "</td>";
		    $role_line = "<th>Category</th>";
    		foreach ($other_income_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'];
    		}
    		$tbl_line .= "<tr><td>" . $val['emp_code'] . "</td><td>" . $emp_rslt[$val['emp_code']] . "</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>
									$role_line
								</tr>
							</thead>
							    $tbl_line
						</table>";
		echo json_encode(array("success" => TRUE,'message' => $view_content,"count" => $count));
    }
}
?>