<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Loan_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('loan_report');
		$this->collect_base_info();
	}
	
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$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[""] = "---- Select Role ----";
		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;
		
		$loan_type_query = 'select label_name,view_name from cw_form_setting  where prime_module_id = "employees" and loan_check = 1 ORDER BY input_for,field_sort asc';
		$loan_type_data   = $this->db->query("CALL sp_a_run ('SELECT','$loan_type_query')");
		$loan_type_result = $loan_type_data->result();
		$loan_type_data->next_result();
		$loan_type_list[""] = "---- Select Type ----";
		foreach($loan_type_result as $loan_type_rslt){
			$label_name  = $loan_type_rslt->label_name;
			$view_name   = $loan_type_rslt->view_name;
			$loan_type_list[$label_name] = $view_name;
		}
		$data['loan_type_list']   = $loan_type_list;
		
		$this->load->view("$this->control_name/manage",$data);
	}
	
	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_loan_details(){
		$process_month     = $this->input->post('process_month');
		$process_type      = $this->input->post('process_type');
		$process_emp_code  = $this->input->post('process_emp_code');
		$process_role      = $this->input->post('process_role');
		$loan_type         = $this->input->post('loan_type');
		
		if((int)$process_type === 1){
			$loan_qry = ' and employee_code = "'.$process_emp_code.'"';
		}else
		if((int)$process_type === 2){
			$loan_qry = ' and role = "'.$process_role.'"';
		}else{
			$loan_qry = "";
		}
		if($loan_type){
			$loan_total                 = $loan_type."_total";
			$loan_installments          = $loan_type."_installments";
			$loan_install_count         = $loan_type."_instal_count";
			$loan_balance               = $loan_type."_balance";
			$loan_total_head            = ucwords(str_replace("_"," ",$loan_total));
			$loan_installments_head     = ucwords(str_replace("_"," ",$loan_installments));
			$loan_install_count_head    = ucwords(str_replace("_"," ",$loan_install_count));
			$loan_balance_head          = ucwords(str_replace("_"," ",$loan_balance));
			$loan_type_head             = ucwords(str_replace("_"," ",$loan_type));
		}
		$category_qry    = 'select * from cw_category where trans_status = 1';
		$category_data   = $this->db->query("CALL sp_a_run ('SELECT','$category_qry')");
		$category_result = $category_data->result_array();
		$category_data->next_result();
		$category_rslt = array_reduce($category_result, function ($result, $arr) {
		    $result[$arr['prime_category_id']] = $arr['category_name'];
		    return $result;
		}, array()); 
		$loan_details_qry  = 'select * from cw_transactions where '.$loan_type.' > 0 and trans_status = 1 and transactions_month ="'.$process_month.'"'.$loan_qry;
		$loan_details_info   = $this->db->query("CALL sp_a_run ('SELECT','$loan_details_qry')");
		$loan_details_result = $loan_details_info->result();
		$loan_details_info->next_result();
		if($loan_details_result){
			$tr_line = "";
			$i =1;	
			foreach($loan_details_result as $loan_rslt){
				$employee_code           = $loan_rslt->employee_code;
				$category_name           = $category_rslt[$loan_rslt->role];
				$process_month           = $loan_rslt->process_month;
				$emp_name                = $loan_rslt->emp_name;
				$loan_total_val          = $loan_rslt->$loan_total;
				$loan_installments_val   = $loan_rslt->$loan_installments;
				$loan_install_count_val  = $loan_rslt->$loan_install_count;
				$loan_balance_val        = $loan_rslt->$loan_balance;
				$loan_amt_val            = $loan_rslt->$loan_type;
				$tr_line .= "<tr><td onclick=pdfGeneration('$employee_code','$loan_type');>View</td><td>$i</td><td>$category_name</td><td>$employee_code</td><td>$emp_name</td><td>$process_month</td><td>$loan_amt_val</td><td>$loan_total_val</td><td>$loan_installments_val</td><td>$loan_install_count_val</td><td>$loan_balance_val</td></tr>";
				$i++;
			}
		}else{
			$tr_line = "<tr><td/><td/><td/><td/><td>No data available</td><td/><td/><td/><td/><td/></tr>";
		}
		$table_content = "<div style='background-color: #f2f2f2;'>
							<table class='table table-striped table-bordered' id='details_list'>
								<thead>
									<tr>
										<th>Option</th>
										<th>Si.No</th>
										<th>Category</th>
										<th>Emp Code</th>
										<th>Employee Name</th>
										<th>Process Month</th>
										<th>$loan_type_head</th>
										<th>$loan_total_head</th>
										<th>$loan_installments_head</th>
										<th>$loan_install_count_head</th>
										<th>$loan_balance_head</th>
									</tr>
								</thead>
								<tbody>
									$tr_line
								</tbody>
								</table>
							</div>";
		echo json_encode(array('success' => TRUE, 'message' => "See Loan Report",'table_content'=>$table_content));
	}
	public function pdfgeneration(){
		$employee_code     = $this->input->post('employee_code');
		$loan_type         = $this->input->post('loan_type');
		$loan_details_qry  = 'select * from cw_loan_installment where loan_type ="'.$loan_type.'" and emp_code="'.$employee_code.'" and trans_status = 1';
		$loan_details_info   = $this->db->query("CALL sp_a_run ('SELECT','$loan_details_qry')");
		$loan_details_result = $loan_details_info->result();
		$loan_details_info->next_result();
		print_r($loan_details_result);
	}
}
?>