<?php
class Beneficiary_model extends CI_Model
{
	public function saveProject($project_data,$app_proj){
		if(!empty($app_proj)){
			extract($app_proj);
    		$this->db->where('id', $app_proj['projectid']);
			$this->db->update('projects',$project_data);
			return $app_proj['projectid'];
		}else{
			$this->db->insert('projects', $project_data);
			$id = $this->db->insert_id();
			return $id;
		}
	}


	public function removeProject($data){

		$this->db->where('id', $data['projectid']);
    	$this->db->delete('projects');

		$this->db->where('project_id', $data['projectid']);
		$this->db->where('application_num', $data['appnumber']);
    	$this->db->delete('applications_projects');
		return true;
	}


	public function getProjectByUser($userid,$declare=FALSE){
		$column= 'projects.id as projectid,
					projects.*,
					applications.id as formid,
					applications.application_num as application_num,
					applications.application_num,
					';
		$this->db->select('*');
		$this->db->from('projects');
		$this->db->join('applications', 'projects.beneficiary_user_id = applications.beneficiary_user_id');
		$this->db->join('applications_projects', 'applications_projects.application_num = applications.application_num and applications_projects.project_id = projects.id');
		$this->db->where('projects.beneficiary_user_id',$userid);
		if($declare){
			$this->db->where('applications.declaration','1');
		}
		$this->db->order_by('projects.id','ASC');
		return $this->db->get()->result();
	}


	public function getApplicationNum($userid){
		return $this->db->select("application_num")->where('beneficiary_user_id',$userid)->order_by('id','ASC')->get('applications')->result();
	}


	public function applicationDetails($userid){
		return $this->db->select("*")->where('beneficiary_user_id', $userid)->order_by('id','ASC')->get('applications')->result();
	}


	function get_applications($beneficiary_user_id) {
		$column= "SELECT DISTINCT app.application_num, app.created_on, app.pmu_approve_status, app.status, (SELECT COUNT(*) FROM applications_projects 
		WHERE applications_projects.application_num = app.application_num) as noofproject FROM applications as app 
		LEFT JOIN applications_projects ON app.application_num = applications_projects.application_num WHERE beneficiary_user_id = $beneficiary_user_id ORDER BY app.created_on ASC LIMIT 20" ;
		$sql = $this->db->query($column);
		if($sql->num_rows() > 0) {
			return $sql->result_array();
		}
	}


	function app_status($beneficiary_user_id) {
		$query ="SELECT bas.status, bas.comment, app.application_num, ba.bank_code, bas.created_at FROM application_statuses as bas 
		INNER JOIN  bank_applications as ba ON ba.id = bas.banking_app_id 
		INNER JOIN applications as app ON app.application_num = ba.application_id WHERE app.beneficiary_user_id = $beneficiary_user_id ORDER BY bas.created_at";
		$sql = $this->db->query($query);
		if($sql->num_rows() > 0) {
			return $sql->result_array();
		}
	}

	function get_application_detail($appNumber) {
		$sql =	$this->db->select('applications.*, states.state_name as state, states.district_name as district ')
		->from('applications')
		->join('states', 'states.district_code = applications.company_dist')
		->where('applications.application_num', $appNumber)
		->get();
		if($sql->num_rows() > 0) {
			return $sql->row();
		}
	}


	function get_uploads_detail($appNumber) {
				$this->db->where('application_num', $appNumber);
		$sql =	$this->db->get('applications_uploads');
		if($sql->num_rows() > 0) {
			return $sql->row();
		}
	}




	function get_projects($appnum) {
		$sql =	$this->db->select('projects.*, zed_sector.name as sectorName, states.state_name, states.district_name')
			->from('projects')
			->join('states', 'states.district_code = projects.district_id', 'left')
			->join('zed_sector', 'zed_sector.sectorid = projects.sector_id', 'left')
			->join('applications_projects', 'applications_projects.project_id = projects.id', 'left')
			->where('applications_projects.application_num', $appnum)
			->get();
		if($sql->num_rows() > 0) {
			return $sql->result();
		}
	}


	function no_projects($appnum) {
		$this->db->where('application_num', $appnum);
		$sql =	$this->db->get('applications_projects');
		return $sql->num_rows();
	}


	function get_user_detail($userid) {
	$sql =	$this->db->select('users.*, zed_sector.name as sectorName, natureofproject.name as natureofproject')
			->from('users')
			->join('zed_sector', 'zed_sector.sectorid = users.type_industry', 'left')
			->join('natureofproject', 'natureofproject.id = users.nature_of_project', 'left')
			->where('users.id', $userid)
			->get();
		if($sql->num_rows() > 0) {
			return $sql->row();
		}
	}


	
	public function getAppProjCount($userid){
		$column= 'count(projects.id) as noofprojects,
				applications.application_num as application_num,
				sum(projects.loan_amount) as totalloanamt';
		$this->db->select($column);
		$this->db->from('projects');
		$this->db->join('applications', 'projects.beneficiary_user_id = applications.beneficiary_user_id');
		$this->db->join('applications_projects', 'applications_projects.application_num = applications.application_num and applications_projects.project_id = projects.id');
		$this->db->where('projects.beneficiary_user_id',$userid)->where('applications.declaration','1')->group_by('applications.application_num')->order_by('projects.id','ASC');
		return $this->db->get()->result();
	}


	public function getBankDetails(){
		return $this->db->select("*")->order_by('id','ASC')->get('banks')->result();
	}



	public function updateApplication($app_data,$appno){
		extract($app_data);
    	$this->db->where('application_num', $appno);
		$this->db->update('applications',$app_data);
		return true;
	}


	public function getAppUpload($appno){
		return $this->db->select("*")->where('application_num',$appno)->order_by('id','ASC')->get('applications_uploads')->result();
	}



	public function saveAppUploads($upload_data,$upid){
		if($upid != "" && $upid != null){
			extract($upload_data);
    		$this->db->where('id', $upid);
			$this->db->update('applications_uploads', $upload_data);
			return $upid;
		}else{
			$this->db->insert('applications_uploads', $upload_data);
			$id = $this->db->insert_id();
			return $id;
		}
	}



	public function createApplication($project_data,$project_id){

		$this->db->select( "application_num" );
		$this->db->from( 'applications' );
		$this->db->where( 'beneficiary_user_id',$project_data['beneficiary_user_id'] );
		//$this->db->where( 'project_id',$project_data['project_id'] );
		$this->db->order_by( 'id','ASC' );
		$data = $this->db->get()->result();
		if(empty($data)){
			$this->db->insert('applications', $project_data);
			$id = $this->db->insert_id();
			$app_proj = array(
						"application_num" => $project_data['application_num'],
						"project_id" 	  => $project_id
					);
			$this->db->insert('applications_projects',$app_proj);
			return $project_data['application_num'];
		}else{
			$app_proj = array(
				"application_num" => $data[0]->application_num,
				"project_id" 	  => $project_id
			);
			$this->db->insert('applications_projects',$app_proj);
			return $data[0]->application_num;
		}
	}


	// Willingness Model


	function willing_model() {
		$willing = [
		
			'nodal_officer_name'		=>	$this->input->post('nodal_officer_name'),
			'nodal_officer_email'		=>	$this->input->post('nodal_officer_email'),
			'nodal_officer_phone'		=>	$this->input->post('nodal_officer_phone'),
			'nodal_officer_designation'	=>	$this->input->post('nodal_officer_designation'),
			'company_address'			=>	$this->input->post('company_address'),
			'company_city'				=>	$this->input->post('company_city'),
			'company_state'				=>	$this->input->post('company_state'),
			'company_dist'				=>	$this->input->post('company_dist'),
			'company_pincode'			=>	$this->input->post('company_pincode'),

			'export_business'			=>	$this->input->post('export_business'),
			'npa'						=>	$this->input->post('npa'),
			'nclt'						=>	$this->input->post('nclt'),
			'credit_rating'				=>	$this->input->post('credit_rating'),

		];

		return $willing;
	}

	function create_willingness($userid, $createdby) {
		$willing = $this->willing_model();
		$willing['beneficiary_user_id']	=	$userid;
		$willing['application_num'] 	= 	strtoupper(uniqid('BEE'));
		$willing['status']				=	'pending';
		$willing['created_by']			=	$createdby;

	
		$this->db->insert('applications', $willing);
		if( $this->db->affected_rows() > 0 ) {
		
			 $message2 = $this->load->view("email/new_willingness_email", $willing, true);
			 $success= emailCheck('New Willingness Form', $message2, ['facilitation-centre@beeindia.gov.in', 'Sunita-awadh@darashaw.com', 'sachin-kumar@darashaw.com', 'n.narender@beeindia.gov.in', 'pallavi@geostat.in'], '');
			$this->session->set_flashdata('message', 'Successfull added user detail');
			redirect('beneficiary/projectdetail/'.$willing['application_num']);
		} else {
			$this->session->set_flashdata('error', 'Unable to add willingness form');
			redirect('beneficiary/add/willingnessform', 'refresh');
		}
	}


	function update_willingness($appnumber, $userid) {
		$willing = $this->willing_model();
	//	print_r($willing);
		$willing['update_by'] =	$userid;
		$this->db->where('application_num', $appnumber);
		$this->db->update('applications', $willing);

		$this->session->set_flashdata('message', 'Successfull updated user detail');
		redirect('beneficiary/projectdetail/'.$appnumber);
	}


	function update_application() {

		$appNumber = $this->input->post('application_num');

		$docs = [
			'incorp_cert'	=> 	$this->input->post('incorp_cert'),
			'auditreport'	=>	$this->input->post('auditreport'),
			'relavent_document'	=>	$this->input->post('relavent_document'),
		];

		$this->db->where('application_num', $appNumber);
		$sql =$this->db->get('applications_uploads');
		if($sql->num_rows() > 0) {
			$this->db->where('application_num', $appNumber );
			$this->db->update('applications_uploads', $docs);
		} else {
			$docs['application_num'] = $appNumber;
			$this->db->insert('applications_uploads', $docs);
		}

		$bpref= [
			'bank_preference1'	=>	$this->input->post('bank_preference1'),
			'bank_preference2'	=>	$this->input->post('bank_preference2'),
			'bank_preference3'	=>	$this->input->post('bank_preference3')
		];

		$dec = [
			'declaration'	=> 	$this->input->post('declaration'),
			'bank_preferences' => json_encode($bpref)
		];

		$this->db->where('application_num', $appNumber);
		$this->db->update('applications', $dec);
		
		if( $this->db->affected_rows() > 0 ) {
			redirect('dashboard');
		} else {
			$this->session->set_flashdata('error', 'Unable to add willingness form');
			redirect('dashboard');
		}
	}


	function get_user_detail_app_number($appnumber) {
			$query = "SELECT users.first_name, users.email FROM users INNER JOIN applications ON applications.beneficiary_user_id = users.id WHERE applications.application_num = '$appnumber'";
			$sql = $this->db->query($query);
			if($sql->num_rows() > 0) {
				return $sql->row_array();
			}

	}

}