<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Minimum_wages_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('minimum_wages_report');
	}

	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		//PAGE INFO FUNCTION
		$this->page_info();	
		$data['encKey']        = $this->generateKey();	
		$data['module_sts']    = (int)$this->module_sts;
		$data['quick_link']    = $this->quick_link;
		$data['pick_list']     = $this->pick_list;
		$data['form_info']     = $this->form_info;
		$data['table_head']    = $this->table_head;
		$data['fliter_list']   = $this->fliter_list;
		$data['freeze_list']   = $this->freeze_list;
		$this->load->view("$this->control_name/manage",$data);
	}
	
	public function get_minimum_wage_report(){
		$encString         = file_get_contents('php://input');
		$_POST             = $this->cryptoDecrypt($encString);
		if(!$_POST){
			echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
			exit(0);
		}
		$report_type    = $this->input->post('report_type');
		$process_month  = $this->input->post('from_date');
		$to_dates       = $this->input->post('to_date');    
		$start_date     = date("Y-m-d",strtotime('01-'.$this->input->post('from_date')));
		$end_date       = date("Y-m-t",strtotime('01-'.$this->input->post('from_date')));
		$from_date      = date("d-m-Y", strtotime('01-'.$process_month));
		$to_date        = date("t-m-Y", strtotime('01-'.$to_dates));
		//QUERY FOR GET LABEL NAME
		$column_name_qry = 'SELECT prime_form_id,label_name,view_name FROM cw_form_setting';
			$column_info     = $this->db->query("CALL sp_a_run('SELECT','$column_name_qry')");
			$column_result   = $column_info->result_array();
			$column_info->next_result();
		
			$column_arr = array_column($column_result, 'view_name','label_name');
			
		//REPORT TYPE 1 IS GET MONTH YEAR
		//REPORT TYPE 2 IS FROM_DATE TO DATE
		if($report_type   === '1'){

			$details_report_query = $this->db->query("CALL sp_min_wage_report('$process_month', '$start_date', '$end_date')");
			$report_result        = $details_report_query->result_array();
			$details_report_query->next_result();

			// $where_condition  = ' AND DATE_FORMAT(cw_sap_minimum_wage.app_from_date,"%Y-%m") >= "'.$from_date.'" 
			// AND DATE_FORMAT(cw_sap_minimum_wage.app_to_date,"%Y-%m")<="'.$to_date.'" GROUP BY cw_transactions_fms.employee_code';

			//-----------------------START DETAILS REPORT QUERY---------------------------//
			// $where_condition     = ' and  DATE_FORMAT(cw_sap_minimum_wage.app_from_date,"%Y-%m-%d") =  "'.$from_date.'"  GROUP BY cw_transactions_fms.employee_code';

			// $details_report_query   ='SELECT cw_transactions_fms.employee_code AS trans_Employee_Code,cw_transactions_fms.emp_name AS trans_Employee_Name,cw_transactions_fms.position AS trans_Designation,cw_transactions_fms.personal_code AS trans_Personal_Area,cw_sap_wbs.wbs_project_id,cw_sap_project.pro_desc,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc,cw_transactions_fms.transactions_month AS trans_Process_month,cw_transactions_fms.pd AS trans_Paid_Days,cw_transactions_fms.md AS trans_Month_days,cw_transactions_fms.fbasic AS trans_Fixed_Basic,cw_transactions_fms.fgross AS trans_Fixed_Gross,cw_transactions_fms.fda AS trans_Fixed_DA,cw_sap_minimum_wage.app_from_date,cw_sap_minimum_wage.app_to_date,cw_sap_minimum_wage.min_wage_type,cw_sap_minimum_wage.state,cw_sap_minimum_wage.state_min_wage,cw_sap_minimum_wage.mw_zone,cw_sap_minimum_wage.index_key,cw_sap_minimum_wage.industry_type,cw_sap_minimum_wage.category_index_no,cw_sap_minimum_wage.category_name,cw_sap_minimum_wage.basic_amt,cw_sap_minimum_wage.da_amt,cw_sap_minimum_wage.hra_amt,cw_sap_minimum_wage.ot_amt,cw_sap_minimum_wage.total_amt,(cw_transactions_fms.fbasic + cw_transactions_fms.pd_basic) AS Trans_Basic_Amount,(cw_transactions_fms.fda + cw_transactions_fms.pd_other) AS Trans_DA_Amount,(cw_transactions_fms.fhra + cw_transactions_fms.pd_hra) AS Trans_HRA_Amount,(cw_transactions_fms.fother + cw_transactions_fms.pd_other) AS Trans_Other_Amount,cw_transactions_fms.egross AS Trans_Earned_Salary_gross,cw_transactions_fms.ebasic AS Trans_Earned_Salary_basic,cw_transactions_fms.eda AS Trans_Earned_Salary_DA  FROM cw_transactions_fms INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_transactions_fms.wbs_element  INNER JOIN cw_sap_project ON cw_sap_project.pro_id = cw_sap_wbs.wbs_project_id INNER JOIN cw_sap_activity ON cw_sap_activity.prime_sap_activity_id = cw_transactions_fms.activity_no INNER JOIN cw_sap_minimum_wage ON cw_sap_minimum_wage.state = cw_sap_wbs.wbs_state AND cw_sap_wbs.wbs_zone = cw_sap_minimum_wage.mw_zone  AND cw_sap_project.pro_industry_type = cw_sap_minimum_wage.index_key AND cw_sap_minimum_wage.app_from_date <= DATE_FORMAT(STR_TO_DATE("'.$prc_month.'" , "%d-%m-%Y"),"%Y-%m-%d") AND cw_sap_minimum_wage.app_to_date >=  DATE_FORMAT(STR_TO_DATE("'.$last_date.'", "%d-%m-%Y"),"%Y-%m-%d") WHERE cw_transactions_fms.transactions_month = "'.$start_date.'" GROUP BY prime_transactions_fms_id;';
			// echo $details_report_query; die;
			// $report_info     = $this->db->query("CALL sp_a_run('SELECT','$details_report_query')");
			// $report_result   = $report_info->result_array();
			// $report_info->next_result();
			//---------------------------------END DETAILS REPORT QUERY------------------------------------//
		}else{
			// $where_condition  = ' AND DATE_FORMAT(cw_sap_minimum_wage.app_from_date,"%Y-%m-%d") >= "'.$from_date.'" 
			// AND DATE_FORMAT(cw_sap_minimum_wage.app_to_date,"%Y-%m-%d")<="'.$to_date.'" GROUP BY cw_transactions_fms.employee_code';
			$summary_report_query = 'SELECT cw_transactions_fms.employee_code AS trans_Employee_Code,cw_transactions_fms.emp_name AS trans_Employee_Name,cw_sap_project.pro_desc,cw_transactions_fms.personal_code AS trans_Personal_Area,cw_sap_wbs.wbs_project_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc,SUM(cw_transactions_fms.pd) AS trans_Paid_Days,SUM(cw_transactions_fms.egross)AS trans_egross	FROM cw_transactions_fms INNER JOIN cw_sap_wbs ON cw_sap_wbs.wbs_id = cw_transactions_fms.wbs_element INNER JOIN cw_sap_project ON cw_sap_project.pro_id = cw_sap_wbs.wbs_project_id INNER JOIN cw_sap_activity ON cw_sap_activity.prime_sap_activity_id = cw_transactions_fms.activity_no	INNER JOIN cw_sap_minimum_wage ON cw_sap_minimum_wage.state = cw_sap_wbs.wbs_state AND cw_sap_wbs.wbs_zone = cw_sap_minimum_wage.mw_zone AND cw_sap_project.pro_industry_type = cw_sap_minimum_wage.index_key AND cw_sap_minimum_wage.app_from_date <= DATE_FORMAT(STR_TO_DATE("'.$from_date.'" , "%d-%m-%Y"),"%Y-%m-%d") AND cw_sap_minimum_wage.app_to_date >=  DATE_FORMAT(STR_TO_DATE("'.$to_date.'", "%d-%m-%Y"),"%Y-%m-%d") WHERE cw_transactions_fms.trans_status=1 GROUP BY prime_transactions_fms_id';
			$summary_info     = $this->db->query("CALL sp_a_run('SELECT','$summary_report_query')");
			$report_result   = $summary_info->result_array();
			$summary_info->next_result();
			//cw_transactions_fms.transactions_month = '02-2023'
		}
		$rslt_arr   = array();
		foreach($report_result as $report_key => $report_arr){
			foreach($report_arr as $column_key => $column_val){	
				$col_key  = $column_arr[$column_key];
				if (array_key_exists($column_key,$column_arr)){
					$rslt_arr[$report_key][$col_key] = $column_val;
				}else{
					$form_name = str_replace('_', ' ', $column_key);
					$rslt_arr[$report_key][$form_name] = $column_val;
				}
			}
		}
		if($rslt_arr){
			echo json_encode(array("success"=>true,'message'=>"data successfully downloaded","json_data"=>$rslt_arr));
		}else{
			echo json_encode(array("success"=>false,"message"=>"No data found"));
		}
	}
}
?>