<?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');
	}

	// 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 = array();
		foreach($role_result as $for){
			$role_id   = $for->prime_category_id;
			$category_name = $for->category_name;
			$process_role[$role_id] = $category_name;
		}
		$type_info   = $this->db->query("CALL sp_a_run ('SELECT','SELECT label_name,view_name FROM cw_form_setting WHERE loan_check = 1;')");
		$type_result = $type_info->result();
		$type_info->next_result();
		$loan_role = array();
		$loan_role[""] = "---select---";
		foreach($type_result as $for){
			$label_name   = $for->label_name;
			$loan_type = $for->view_name;
			$loan_role[$label_name] = $loan_type;
		}
		$data['loan_role'] = $loan_role;
		$data['process_role'] = $process_role;
		$data['key']  = $this->generateKey();
		$this->load->view("$this->control_name/manage",$data);
	}
	//AUTO COMPLETE FUNCTION
	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 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);
	}
	public function get_table_data(){
		$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);
		}
		$process_type    = $this->input->post('process_type');
		$process_emp_id  = $this->input->post('process_emp_id');
		$process_role    = $this->input->post('process_role');
		$process_role    = implode(',', $this->input->post('process_role'));
		$loan_type       = $this->input->post('loan_type');
		$report_type     = $this->input->post('report_type');
		if($process_type == '1'){
			$loan_where_query = 'cw_loan.emp_code ="'.$process_emp_id.'"';
		}else
		if($process_type == '2'){
			$loan_where_query = 'cw_loan.category in ('.$process_role.')';
		}
		//EMPLOYEE NAME AND CO
		$emp_qry        = 'select employee_code,emp_name,loan_date,loan_amount,cw_loan.loan_amount*(interest_rate/100) as interest_amt,cw_loan.loan_type,cw_loan.apply_year,number_of_installment,per_month,loan_foreclose_amount,loan_foreclose_date,prime_loan_id from cw_employees inner join cw_loan on cw_loan.emp_code = cw_employees.employee_code where  '.$loan_where_query.' and cw_loan.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();
        //TABLE DATAS
		$paid_loan_qry    = 'SELECT prime_loan_id,cw_loan.emp_code,cw_loan.loan_type,cw_loan.apply_year,COUNT(*) as paid_months,SUM(cw_loan_installment.install_amount) as paid_amt FROM cw_loan JOIN cw_loan_installment ON cw_loan_installment.loan_id = cw_loan.prime_loan_id WHERE cw_loan.loan_type = "'.$loan_type.'" and  '.$loan_where_query.' and cw_loan.trans_status = 1 and cw_loan_installment.trans_status = 1 and paid_status = 1 group by prime_loan_id order by cw_loan.loan_type';
        	$paid_loan_info   = $this->db->query("CALL sp_a_run ('SELECT','$paid_loan_qry')");
        	$paid_loan_rslt   = $paid_loan_info->result();
        	$paid_loan_info->next_result();
        	$loan_amt_tot = 0;
        	$paid_amt_tot = 0;
        	$paid_arr = array();
        	foreach($paid_loan_rslt as $key => $value){
        		$paid_arr[$value->prime_loan_id] = $value;
        	}
        	$tr_line = "";
        	$tn_line = "";
        	foreach ($emp_rslt as $key => $val){
        		$emp_code      = $val['employee_code'];
        		$emp_name      = $val['emp_name'];
        		$loan_date     = $val['loan_date'];
        		$loan_type     = $val['loan_type']; 
        		$apply_year    = $val['apply_year']; 
        		$loan_amount   = $val['loan_amount']; 
        		$loan_amt_tot  = $loan_amt_tot + $loan_amount;
        		$no_of_install = $val['number_of_installment'];
        		$per_month     = $val['per_month'];
        		$prime_loan_id = $val['prime_loan_id'];
        		$paid_months   = $paid_arr[$prime_loan_id]->paid_months;
        		$paid_amt      = $paid_arr[$prime_loan_id]->paid_amt;
        		$paid_amt_tot  = $paid_amt_tot + $paid_amt;
        		$foreclose_amt = $val['loan_foreclose_amount'];
        		$foreclose_dt  = $val['loan_foreclose_date'];
        		$interest_amt  = $val['interest_amt'];
        		$balance_month = $no_of_install - $paid_months;
        		$balance_amt   = $loan_amount   - $paid_amt;
        		$tr_line      .= "<tr><td>$loan_date</td><td>$loan_type</td><td>$emp_code</td><td>$emp_name</td><td>$loan_amount</td><td>$no_of_install</td><td>$per_month</td><td>$paid_months</td><td>$paid_amt</td><td>$foreclose_dt</td><td>$foreclose_amt</td><td>$interest_amt</td><td>$balance_month</td><td>$balance_amt</td></tr>";
        		$tn_line      = "<tr><td></td><td></td><td></td><td></td><td style='color: red;'>$loan_amt_tot</td><td></td><td></td><td></td><td style='color: red;'>$paid_amt_tot</td><td></td><td></td><td></td><td></td><td></td></tr>";
        	}
        	$view_content = "<table class='table table-striped table-bordered' id='role'>
					<thead>
						<tr>
							<th>Loan Date</th>
							<th>Loan Type</th>
							<th scope='col'>Employee Code</th>
							<th scope='col'>Employee Name</th>	
				        	<th scope='col'>Loan Amount</th>
				        	<th scope='col'>Total Months</th>
				        	<th scope='col'>Amount Per Month</th>
				        	<th scope='col'>Paid Months</th>
				        	<th scope='col'>Paid Amount</th>
				        	<th scope='col'>F.Close Months</th>
				        	<th scope='col'>F.Close Amount</th>
				        	<th scope='col'>Interest Amount</th>
				        	<th scope='col'>Balance Months</th>
				        	<th scope='col'>Balance Amount</th>
						</tr>
					</thead>
					<tbody>
					    $tr_line
					</tbody>
					<tfoot>$tn_line</tfoot>
				</table>";
		echo json_encode(array("success" => TRUE,'loan_rslt'=>$view_content));
	}

}
?>