<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Income_matching_new  extends Action_controller{	
	public function __construct(){
		parent::__construct('income_matching_new');
	}
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$headers = array('earnings'=>"Earnings", 'matching_components'=>"Matching Components", 'excemption_component'=>"Excemption_Component", 'formula'=>"formula", 'gross_section'=>"Gross Section", 'projection'=>"projection", 'tax_deduction_mode'=>"tax_deduction_mode", 'order_no'=>"order_no",); 
		$get_match_columns = 'SELECT order_no,prime_form_id,prime_module_id,	CONCAT(label_name," - ",view_name) as view_name,label_name FROM `cw_form_setting` join cw_income_matching WHERE prime_module_id = "employees" and transaction_type IN (2,3)';
		$match_column_info   = $this->db->query("CALL sp_a_run 		('SELECT','$get_match_columns')");
		$match_column_result = $match_column_info->result();
		$match_column_info->next_result();
		if($match_column_result){
			$pick_key   = array_column($match_column_result,"label_name");
			$pick_val   = array_column($match_column_result,"view_name");
			$match_pick = array_combine( $pick_key, $pick_val);
			$match_pick =  $match_pick;
		}
		$data['earning_columns']   = $match_pick;
		$data['table_head']    = $headers;
		$this->load->view("$this->control_name/manage",$data);
	}
	//LOAD TABEL WITH FILTERS
	public function search(){
		$dec_data         = $this->cryptoDecrypt($_POST['Payload']);
		$_POST            = $dec_data['data'];
		if(!$_POST){
			echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
			exit(0);
		}	
		$this->search_info();
		$module_sts       = (int)$this->module_sts;
		if(!$module_sts){
			echo json_encode(array('success' => FALSE, 'message' => "Search  Info Query Process Error..!"));
			exit(0);
		}else{
			$draw         = $this->input->post('draw');
			$start        = $this->input->post('start');
			$per_page     = $this->input->post('length');
			$order        = $this->input->post('order');
			$order_col    = $this->input->post('columns');
			$search       = $this->input->post('search');
			$column       = $order[0]['column'];
			$order_sor    = $order[0]['dir'];
			$order_col    = $order_col[$column]['data'];
			$search       = trim($search['value']);
			$filter_count = count($fliter_label ?? []);
			$fliter_query = "";
			$common_search = "";
			//Search in DataTable
			if ($search) {
	    		$common_search = ' earnings  like "'.$search.'%" or order_no like "'.$search.'%" or  formula like "'.$search.'%" or cw_tax_sub_section.tax_act_details like "'.$search.'%"  or
	    		cw_status_mode.status_mode_value like "'.$search.'%" or matching_components like "'.$search.'%"  ';
			}
			if($common_search){
				$common_search = ltrim($common_search,' or ');
				$common_search = " and ($common_search)";
				$common_search = str_replace("(,","(",$common_search);
				$common_search = str_replace("()","(0)",$common_search);
			}
			//FETCH RECORDS DATA
			$search_total  = $this->db->query("SELECT COUNT(*) AS allcount FROM cw_income_matching ");
			$search_total_info  = $search_total->result();
			$total_count        = $search_total_info[0]->allcount;
			$search_count  =$this->db->query("SELECT COUNT(*) AS allcount FROM cw_income_matching WHERE financial_setting_id = ".$this->financial_info[0]->prime_financial_setting_id." and trans_status = 1 ");

			$search_info        = $search_count->result();
			$filtered_count     = $search_info[0]->allcount;

			$search_query = 'SELECT 
			prime_income_matching_id, earnings, cw_status_mode.status_mode_value AS projection, matching_components, cw_tax_sub_section.tax_act_details AS excemption_component,formula, (CASE WHEN tax_deduction_mode = 1 THEN "Normal" ELSE "" END) AS tax_deduction_mode, 
			(CASE WHEN gross_section = 1 THEN "17(1)" WHEN gross_section = 2 THEN "17(2)" WHEN gross_section = 3 THEN "17(3)" ELSE "" END) AS gross_section, order_no FROM cw_income_matching JOIN cw_status_mode ON cw_status_mode.prime_status_mode_id = cw_income_matching.trans_status LEFT JOIN cw_tax_sub_section ON cw_tax_sub_section.prime_tax_sub_section_id = cw_income_matching.excemption_component WHERE 
			cw_income_matching.financial_setting_id = "'.$this->financial_info[0]->prime_financial_setting_id.'" ';
			$search_query      .= "$common_search ORDER BY  $order_col $order_sor";
			if((int)$per_page !== -1){
				$search_query  .= " LIMIT  $start,$per_page";
			}	

			$search_data  =$this->db->query("CALL sp_a_run('SELECT','$search_query')");
			$search_result = $search_data->result();

			$search_data->next_result();
			echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
		}
	}	
	//LOAD MODEL PAGE VIEW WITH DATA
	public function view($form_view_id=-1){
		$data['primeId']     = $form_view_id;
		if($form_view_id !== -1){
			//Decrypt prime id from URL
			$decRslt          = $this->cryptoDecrypt(base64_decode(urldecode($form_view_id)));
			$form_view_id     = $decRslt['prime_id'];
			if(!$form_view_id){
				echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
				exit(0);
			}
		}
		//VIEW INFO FUNCTION
		$this->view_info($form_view_id);
		$data['encKey']                 = $this->generateKey();	
		$data['module_sts']             = (int)$this->module_sts;					
		// Excemption Component!
		$get_columns = 'SELECT prime_tax_sub_section_id,tax_act_details FROM cw_tax_sub_section';
		$column_info   = $this->db->query("CALL sp_a_run ('SELECT','$get_columns')");
		$column_result = $column_info->result();
		$column_info->next_result();
		$columns[""] = "---- Select Column ----";
		foreach($column_result as $column){
			$prime_section_id = $column->prime_tax_sub_section_id;
			$tax_act_details = $column->tax_act_details;
			$columns[$this->xss_clean($prime_section_id)] = $this->xss_clean($tax_act_details);		
		}		
		$data['columns'] = $columns;		
		// projection!
		$status_mode='SELECT prime_status_mode_id,status_mode_value AS projection FROM cw_status_mode';		
		$main_column_info   = $this->db->query("CALL sp_a_run ('SELECT','$status_mode')");
		$main_column_result = $main_column_info->result();
		$main_column_info->next_result();
		$projection_columns[""] = "---- Select Column ----";
		foreach($main_column_result as $column){
			$prime_id   = $column->prime_status_mode_id;
			$projection = $column->projection;
			$projection_columns[$this->xss_clean($prime_id)] = $this->xss_clean($projection);   	       
		}       
		$data['projection_columns'] = $projection_columns;
		//Get Row data!
		$previous_data_query  = 'SELECT * from cw_income_matching where trans_status = 1 and prime_income_matching_id = "'.$form_view_id.'"';	
		$previous_data   = $this->db->query("CALL sp_a_run ('SELECT','$previous_data_query')");
		$previous_result = $previous_data->result();
		$num_rows        = $previous_data->num_rows();
		$previous_data->next_result();
		$data['edit_data'] = $previous_result;
		$form_arr = $this->get_form_columns($form_view_id);
		$data['earning_columns_withtax'] = $form_arr['earning_columns_withtax'];
		$data['earning_columns']         = $form_arr['earning_columns'];
		$data['ded_columns']             = $form_arr['ded_columns'];
		$ded_columns = array_filter($ded_columns);
		$this->load->view("$this->control_name/form",$data);
	}
	//SAVE MODEL PAGE TO DB !
	public function save($form_id) {
    	$this->search_info();
    	$prime_qry_key = "";
    	$prime_qry_value = "";
    	$prime_upd_query = "";
    	$this->prime_table = "cw_income_matching";
    	$this->prime_id = "prime_income_matching_id";
    	$earnings = $this->input->post('earnings');
		// Check if the form id is zero
		if ($form_id == 0) {
			$qry = "SELECT MAX(order_no) AS max_order FROM cw_income_matching";
			$order_qry = $this->db->query($qry);
			$rslt = $order_qry->result();
			$max_order = $rslt[0]->max_order;
    		foreach ($_POST as $key => $value) {
        		if ($key === 'order_no') {
        			$new_order = $max_order + 1; // Increment the max_order by 1
            		$value = $new_order; // Append the new order_no
        		}
        			$prime_qry_key .= $key . ",";
        			$prime_qry_value .= '"' . $value . '",';
        			$prime_upd_query .= $key . ' = "' . $value . '",';
    		}
		}else {
    		// Use the existing order_id
    		foreach ($_POST as $key => $value) {
        		$prime_qry_key .= $key . ",";
        		$prime_qry_value .= '"' . $value . '",';
        		$prime_upd_query .= $key . ' = "' . $value . '",';
    		}
		}
    	// Check if the data already exists
    	$exist_qry = 'SELECT * FROM cw_income_matching WHERE financial_setting_id = "'.$this->financial_info[0]->prime_financial_setting_id.'" AND earnings = "'.$earnings.'" AND 	trans_status = 1';
    	$exist_data = $this->db->query($exist_qry);
    	$num_rows = $exist_data->num_rows();
    	if ($num_rows > 0) {
    	    $exist_result = $exist_data->result();
    	    $id = $exist_result[0]->prime_income_matching_id;
    	}
    	if ($num_rows > 0 && $id !== $form_id) {
    	    echo json_encode(array('success' => false, 'message' => 'Already Exist'));
    	}else{
    	    $created_on = date("Y-m-d h:i:s");
    	    if((int)$form_id === 0){
    	        if (!$this->check_earning_already_exists($earnings)) {
    	            $prime_qry_key .= "financial_setting_id,trans_created_by,trans_created_date";
    	            $prime_qry_value .= '"' . $this->financial_info[0]->prime_financial_setting_id . '","' . $this->logged_id . '","' . $created_on . '"';
    	            $prime_insert_query = "INSERT INTO cw_income_matching ($prime_qry_key) VALUES ($prime_qry_value)";
    	            $insert_info = $this->db->query($prime_insert_query);
    	            $insert_id = $this->db->insert_id();
    	            echo json_encode(array('success' => true, 'message' => "Successfully added", 'insert_id' => $insert_id));
    	        }else {
    	            echo json_encode(array('success' => false, 'message' => "Earning Already Exists"));
    	        }
    	    }else{
    	        if (!$this->check_earning_already_exists($earnings, $form_id)){
    	           		$prime_upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
    	            	$prime_update_query = 'UPDATE cw_income_matching SET ' . $prime_upd_query . ' WHERE prime_income_matching_id = "' . $form_id . '"';
    	            	$this->db->query($prime_update_query);
    	            	echo json_encode(array('success' => true, 'message' => "Successfully updated", 'insert_id' => $form_id));
				}
			}
		}
	}
	//CHECK EARNING IS ALREADY EXISTS FOR INCOME MATCHING
	public function check_earning_already_exists($earnings,$form_id = -1){
		$is_exist_qry  = 'SELECT * FROM cw_income_matching where earnings = "'.$earnings.'" and trans_status = 1 and financial_setting_id = "'.$this->financial_info[0]->prime_financial_setting_id.'"';
		if((int)$form_id > 0){
			$is_exist_qry .= " and prime_income_matching_id != $form_id";
		}
		$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
		$exist_count   = $is_exist_data->num_rows();
		$is_exist_data->next_result();
		if((int)$exist_count > 0){
			return TRUE;
		}else{
			return FALSE;
		}
	}
	//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
	public function delete(){
		$delete_ids          = implode(",",$this->xss_clean($this->input->post('delete_ids')));
		$created_on          = date("Y-m-d h:i:s");
		$prime_upd_query    .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
		$prime_update_query  = 'UPDATE cw_income_matching SET trans_status = 0,'. $prime_upd_query .' WHERE '. prime_income_matching_id .' in ('. $delete_ids .')';		
		if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
			echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
		}else{
			echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
		}
	}
	//CHECK UNIQUE FIELD STATUS
	public function check_delete_status(){
		$check_delete_query  = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and  trans_status = 1 ';
		$check_delete_info   = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
		$check_delete_rlst   = $check_delete_info->row();
		$check_delete_info->next_result();
		$unique_info         = explode(",",$check_delete_rlst->unique_field);
		if(in_array('1', $unique_info ?? [])){
			return TRUE;
		}else{
			return FALSE;
		}
	}
	//EXCEMPTION CHECK !
	public function examption_check(){
		$excemption_component  = $this->input->post('excemption_component');
		$excem_match_query   = 'select count(*) as rslt_count from cw_income_matching where trans_status = 1 and excemption_component = "'.$excemption_component.'"';
		$excem_match_info    = $this->db->query("CALL sp_a_run ('SELECT','$excem_match_query')");
		$excem_match_result  = $excem_match_info->result();
		$excem_match_info->next_result();
		$excem_match_count  = $excem_match_result[0]->rslt_count;
		if($excem_match_count > 0){
			echo json_encode(array('success' => FALSE, 'message' => "Already Examption is applied, Choose another one?"));
		}else{
			echo json_encode(array('success' => TRUE, 'message' => "Examption components is choosed"));
		}
	}
	//MATCHING CHECK !
	public function matching_check(){
		$matching_components  = $this->input->post('matching_component');
		$match_query   = 'select count(*) as rslt_count from cw_income_matching where trans_status = 1 and matching_components = "'.$matching_components.'"';
		$match_info    = $this->db->query("CALL sp_a_run ('SELECT','$match_query')");
		$match_result  = $match_info->result();
		$match_info->next_result();
		$match_count  = $match_result[0]->rslt_count;
		if($match_count > 0){
			echo json_encode(array('success' => FALSE, 'message' => "Already Components is Matched, Choose another one?"));
		}else{
			echo json_encode(array('success' => TRUE, 'message' => "Matching components is choosed"));
		}
	}
	//EARNING COMPONENTS CHECK !
	public function earning_check(){
		$earning_component  = $this->input->post('earning_component');
		$earn_match_query   = 'select count(*) as rslt_count from cw_income_matching where trans_status = 1 and formula like  "%'.$earning_component.'%"';

		$earn_match_info    = $this->db->query("CALL sp_a_run ('SELECT','$earn_match_query')");
		$earn_match_result  = $earn_match_info->result();
		$earn_match_info->next_result();
		$earn_match_count  = $earn_match_result[0]->rslt_count;
		if($earn_match_count > 0){
			echo json_encode(array('success' => FALSE, 'message' => "Already Components is Mapped, Choose another one?"));
		}else{
			echo json_encode(array('success' => TRUE, 'message' => "Can Process"));
		}
	}
	//IMPORT FILE VIEW INFORMATION
	public function import(){
		$data['encKey']        = $this->generateKey();
		$data['module_id']     = $this->control_name;		
		$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
		$excel_format   = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
		$excel_result    = $excel_format->result();
		$excel_format->next_result();
		$excel_format_drop[""] = "---- Excel Format ----";
		foreach($excel_result as $excel){
			$prime_excel_format_id = $excel->prime_excel_format_id;
			$excel_name            = $excel->excel_name;
			$excel_format_drop[$prime_excel_format_id] = $excel_name;
		}
		$data['excel_format_drop'] = $excel_format_drop;
		
		$this->load->view("$this->control_name/import",$data);
	}
	//Get pick columns
	public function get_form_columns($form_view_id){
		$form_arr = array();
		// Check Exist Earned column
		$not_in_prime_id 	 = "";
		if((int)$form_view_id > 0){
			$not_in_prime_id = ' and cw_income_matching.prime_income_matching_id != "'.$form_view_id.'"';
		}
		$exist_matching_qry  = 'SELECT group_concat(earnings) as exist_col from cw_income_matching where trans_status = 1 '.$not_in_prime_id.'';
		$exist_matching_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_matching_qry')");
		$exist_matching_rslt = $exist_matching_info->result();
		$exist_matching_info->next_result();
		$exist_col 		   = $exist_matching_rslt[0]->exist_col;
		$exist_column      = "";
		if($exist_col){
			$exist_column  = implode('","',explode(',', $exist_col));
		}
		$get_earn_columns = 'SELECT prime_form_id,prime_module_id,CONCAT(label_name," - ",view_name) as view_name,label_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and taxable_check = 1 and transaction_type = 2 and label_name not in("'.$exist_column.'")';
		$earn_column_info   = $this->db->query("CALL sp_a_run ('SELECT','$get_earn_columns')");
		$earn_column_result = $earn_column_info->result();
		$earn_column_info->next_result();
		if($earn_column_result){
			$pick_key   = array_column($earn_column_result, "label_name");
			$pick_val   = array_column($earn_column_result, "view_name");
			$earn_pick = array_combine( $pick_key, $pick_val);
			$earn_pick = array("" => "---- Select Column ----") + $earn_pick;
		}
		$form_arr['earning_columns_withtax']   = $earn_pick;
		//Get all the Earning Columns without tax column
		$get_match_columns = 'SELECT prime_form_id,prime_module_id,CONCAT(label_name," - ",view_name) as view_name,label_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and transaction_type = 2 and taxable_check != 1';
		$match_column_info   = $this->db->query("CALL sp_a_run ('SELECT','$get_match_columns')");
		$match_column_result = $match_column_info->result();
		$match_column_info->next_result();
		if($match_column_result){
			$pick_key   = array_column($match_column_result, "label_name");
			$pick_val   = array_column($match_column_result, "view_name");
			$match_pick = array_combine( $pick_key, $pick_val);
			$match_pick = array("" => "---- Select Column ----") + $match_pick;
		}
		$form_arr['earning_columns']   = $match_pick;
		//Get All Deduction Component
		$get_ded_columns = 'SELECT prime_form_id,prime_module_id,CONCAT(label_name," - ",view_name) as view_name,label_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and transaction_type = 3';
		$ded_column_info   = $this->db->query("CALL sp_a_run ('SELECT','$get_ded_columns')");
		$ded_column_result = $ded_column_info->result();
		$ded_column_info->next_result();
		if($ded_column_result){
			$pick_key   = array_column($ded_column_result, "label_name");
			$pick_val   = array_column($ded_column_result, "view_name");
			$ded_pick = array_combine( $pick_key, $pick_val);
			$ded_pick = array("" => "---- Select Column ----") + $ded_pick;
		}
		$form_arr['ded_columns']   = $ded_pick;
		return $form_arr;
	}
	// SORT OPEARTION
	public function update_sortorder(){
		$idsInOrder      = $this->input->post('idsInOrder');	
		// foreach($idsInOrder as $order){
		// 	if($order){
		// 		$order = explode("_",$order);		
		// 		$prime_income_matching_id = $order[1];
		// 		$table_data = array('prime_form_id'   => $prime_income_matching_id);
		// 	}
		// }
		// print_r($table_data);
		// die;
		// print_r($idsInOrder); die;
		// $id_info         = $this->input->post('id_info');
		$logged_id       = $this->session->userdata('logged_id');
		$prime_ids       = json_encode($idsInOrder);
		echo $prime_ids;
		die;
		$info   = $this->db->query("CALL sp_tbl_sorting ('$prime_ids','cw_income_matching',prime_income_matching_id','$logged_id')");
		$result = $info->result();
		// echo "CALL sp_tbl_sorting ('$prime_ids','cw_income_matching',prime_income_matching_id','$logged_id')"; 
		// $id_info = explode("-",$id_info);
		// $input_for       = $id_info[1];
		// $sort_order = 0;
		/*foreach($idsInOrder as $order){
			if($order){
				$sort_order++;
				$order = explode("_",$order);		
				$prime_income_matching_id = $order[1];

				$table_data = array(
					'prime_form_id'   => $prime_income_matching_id,
					// 'input_for'       => $order_no,
					'sort_order'      => $sort_order,
				);
				print_r( $table_data);
				die;
				$table_data = json_encode($table_data);
				$info   = $this->db->query("CALL sp_tbl_sorting ('$prime_ids','cw_income_matching',prime_income_matching_id','$logged_id')");
				$result = $info->result();
				$info->next_result();
			}
		}*/
		echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
	}
}
?>