<?php
class Bank_model extends CI_Model
{
	public function saveProjectFinance( $data ){
		$this->db->insert('bank_financed', $data);
		$id = $this->db->insert_id();
		return $id;
	}



	public function get_personal_financed($userid){
		return $this->db->select("bank_financed.*, zed_sector.name as sectorname")
				->from('bank_financed')
				->join('zed_sector', 'zed_sector.sectorid = bank_financed.sector_id')
				->where('bank_financed.created_by', $userid)
				->order_by('bank_financed.id','ASC')
				->get()->result();
	}

	function get_bankloan_app_status($status = 'sanctioned') {
		$this->db->select('bank_applications.*');
		$this->db->from('users');
		$this->db->join('bank_applications', 'bank_applications.bank_code = users.bank_code');
		$this->db->where('bank_applications.status', $status);
		$this->db->where('users.id', $this->session->user_id);
		$query = $this->db->get();
		return $query->result_array();
	}


	function get_bank_fianaced_project() {
		$this->db->select('bank_financed.*');
		$this->db->from('users');
		$this->db->join('bank_financed', 'bank_financed.bank_code = users.bank_code');
		$this->db->where('users.id', $this->session->user_id);
		$query = $this->db->get();
		return $query->result_array();
	}


	function get_bank_app_statuscount() {
		$userid = $this->session->user_id;
		$query ="SELECT status, COUNT(*) as total FROM bank_applications where created_by = $userid GROUP BY status";
		$sql = $this->db->query($query);
	   return $sql->result_array();
	 }



	public function totalSubscribedApps() {
		$column= 'applications.id';
		$this->db->select($column);
		$this->db->from('applications');
		$this->db->join('users', 'applications.beneficiary_user_id = users.id');
		$this->db->join('bank_applications', 'bank_applications.application_id = applications.application_num');
		$this->db->where('bank_applications.created_by', $this->session->user_id);
		$this->db->where('applications.pmu_approve_status','approved');
		return $this->db->get()->num_rows();
	}


	public function getBankSubscribedApps(){
		$column= 'users.id as userid, bank_applications.id as baid,
				  applications.id as appid,
				  applications.*,
				  (select count(*) from applications_projects where applications_projects.application_num = applications.application_num) as countprojects,
				  users.*';
		$this->db->select($column);
		$this->db->from('applications');
		$this->db->join('users', 'applications.beneficiary_user_id = users.id');
		$this->db->join('bank_applications', 'bank_applications.application_id = applications.application_num');
		$this->db->where('bank_applications.created_by', $this->session->user_id);
		$this->db->where('applications.pmu_approve_status','approved')->order_by('applications.id','ASC');
		return $this->db->get()->result();
	}

	/* Bank page Profile Started Here */


	function status_name() {
		return [
			'processstart'	=>	'Process Start',
			'receipt'	=>	'Receipt of Application',
			'scrutiny'	=>	'Scrutiny of Application for completeness',
			'processing'	=>	'Processing',
			'shared'	=>	'Terms Sheet decided and draft Term Sheet shared with borrower',
			'study'	=>	'KYC & CIBIL check, market feedback, Balance Sheet study',
			'sanctioned'	=>	'On approval from borrower, Loan Sanction',
			'loandocumentation'	=>	'Loan Documentation',
			'issuesanctionletter'	=>	'Issue of Sanction Letter',
			'feedeposit'	=>	'Processing Fees deposit by borrower',
			'disbursed'	=>	'Disbursement',
			'rejected'	=>	'Rejected',
			'queryraised'	=>	'Query Raised',
		
		];
	}

	// Total TER

	function total_ter() {
		$this->db->where('eval_link_static !=', '');
		$query = $this->db->get('applications');
		return $query->num_rows();
		
	}


	public function get_application_bystatus($status = 'none', $secstatus = '') {
		if(!empty($this->session->company->bank_code)) {
		
			echo $this->session->company->bank_code;
			$column= 'users.id as userid, applications.id as appid, applications.*, (select count(*) from applications_projects where 
			applications_projects.application_num = applications.application_num) as countprojects, users.*';
			$this->db->select($column);
			$this->db->from('applications');
			$this->db->join('users', 'applications.beneficiary_user_id = users.id');
			$this->db->where('applications.status', $status);
			$this->db->where('applications.financed_bank', $this->session->company->bank_code);
			
			return $this->db->get()->result();
		}
	}




}