<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class LoanApplicantionModel extends Model
{	
	public function QPermission($id)
	{
		$QModules=DB::table('modules')->select('Mid')->where('mUrl',$id['url'])->first();
		$Mid=$QModules->Mid;
		return $Qpermission=DB::table('permission')->where('Mid',$Mid)->first();
	}
	public function LoadNextSerialNo()
	{
		$loanaccount=DB::table('lacc_loanaccount')->orderBy('lacc_id', 'desc')->first();
		$lacc_no= str_replace("NFCPL","",$loanaccount->lacc_no)+1;
		return "NFCPL".$lacc_no;
	}
	public function viewloanapplicant($status)
	{
		return DB::table('lacc_loanaccount')
		->leftjoin('br_branch','br_branch.br_id','=','lacc_loanaccount.br_id')
		->where('lacc_status',$status)
		->get();
	}
	public function viewloanapplicantion($id)
	{
		return DB::table('lacc_loanaccount')
		->leftjoin('br_branch','br_branch.br_id','=','lacc_loanaccount.br_id')
		->where('lacc_status','Pending')
		->where('lacc_id',$id)
		->get();
	}
	public function viewloanapplicantionrecord($id)
	{
		return DB::select( DB::raw("select *, IFNULL(( select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` = lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Principle', 'Interest', 'Cheque Bounce', 'Case', 'Delay payment', 'Delay-Payment', 'Others')), 0 ) as totalamt , IFNULL(( select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` = lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Payment')), 0 ) as paidamt from `lacc_loanaccount` left join `br_branch` on `br_branch`.`br_id` = `lacc_loanaccount`.`br_id` where `lacc_status` = 'Active'"));
	}
		public function insertLoanApplicant($id)
	 	{
			/***************************guarantor*************************/
			$guarantor1= array($id['lacc_guarantor1_name'],$id['lacc_guarantor1_relationship'],$id['lacc_guarantor1_address'],$id['lacc_guarantor1_tel'],$id['lacc_guarantor1_res']);
			$Uguarantor1=serialize($guarantor1);
			
			
			$guarantor2= array($id['lacc_guarantor2_name'],$id['lacc_guarantor2_relationship'],$id['lacc_guarantor2_address'],$id['lacc_guarantor2_tel'],$id['lacc_guarantor2_res']);
			$Uguarantor2=serialize($guarantor2);
			
			$guarantor3= array($id['lacc_guarantor3_name'],$id['lacc_guarantor3_relationship'],$id['lacc_guarantor3_address'],$id['lacc_guarantor3_tel'],$id['lacc_guarantor3_res']);
			$Uguarantor3=serialize($guarantor3);
			
			/***************************Reference*************************/
			
			$Reference1= array($id['lacc_reference1_name'],$id['lacc_reference1_relationship'],$id['lacc_reference1_address'],$id['lacc_reference1_tel'],$id['lacc_resaddr_landmark'],$id['lacc_reference1_res']);
			$UReference1=serialize($Reference1);
			
			$Reference2= array($id['lacc_reference2_name'],$id['lacc_reference2_relationship'],$id['lacc_reference2_address'],$id['lacc_reference2_tel'],$id['lacc_reference2_res']);
			$UReference2=serialize($Reference2);
			
			/***************************Residency*************************/
			
			$Residency= array($id['lacc_resaddr_addr'],$id['lacc_resaddr_city'],$id['lacc_resaddr_pin'],$id['lacc_resaddr_landmark'],$id['lacc_resaddr_fax'],$id['lacc_resaddr_mob'],$id['lacc_resaddr_tel']);
			$Residency1=serialize($Residency);
			
			/***************************Residency*************************/
			
			$CompanyAddress= array($id['lacc_compaddr_addr'],$id['lacc_compaddr_city'],$id['lacc_compaddr_pin'],$id['lacc_compaddr_landmark'],$id['lacc_compaddr_fax'],$id['lacc_compaddr_mob'],$id['lacc_compaddr_tel']);
			$CompanyAddress1=serialize($CompanyAddress);
			
			/***************************permenaennt*************************/
			
			$PermenentAddress= array($id['lacc_permaddr_addr'],$id['lacc_permaddr_city'],$id['lacc_permaddr_pin'],$id['lacc_permaddr_landmark'],$id['lacc_permaddr_fax'],$id['lacc_permaddr_mob'],$id['lacc_permaddr_tel'],$id['lacc_permaddr_chngresyesno']);
			
			$PermenentAddress1=serialize($PermenentAddress);
			/***************************job*************************/
			
			$job= array($id['lacc_jobprofile_yrsatcurrentjob'],$id['lacc_jobprofile_department'],$id['lacc_jobprofile_designation'],$id['lacc_jobprofile_grossincomepm'],$id['lacc_jobprofile_netincomepm'],$id['lacc_jobprofile_otherincsrc']);
			
			$job1=serialize($job);
			/***************************Bank********************************/
			$bank= array($id['lacc_bankac_bankname'],$id['lacc_bankac_branch'],$id['lacc_bankac_accountno'],$id['lacc_bankac_accountsince'],$id['lacc_bankac_accounttype']);
			$bank1=serialize($bank);
			
			/***************************coapplicant********************************/
			$coapplicant= array($id['lacc_coapplicantprofile_name'],$id['lacc_coapplicantprofile_compname'],$id['lacc_coapplicantprofile_monthlyincome'],$id['lacc_coapplicantprofile_netincome'],$id['lacc_coapplicantprofile_officetel'],$id['lacc_coapplicantprofile_relationship'],$id['lacc_coapplicantprofile_qualification'],$id['lacc_coapplicantprofile_occupation']);
			$coapplicant1=serialize($coapplicant);
			
			/***************************self employed********************************/
			$selfempdet= array($id['lacc_selfempdet_natureofbusiness'],$id['lacc_selfempdet_incomepa'],$id['lacc_selfempdet_yearsatpresentbusiness']);
			$selfempdet1=serialize($selfempdet);			  
				
			$updateLoanApplicant=DB::table('lacc_loanaccount')->where('lacc_id','=',$id['editId'])->update(['lacc_custname'=>$id['lacc_custname'],'lacc_swdof'=>$id['lacc_swdof'],'lacc_dob'=>$id['lacc_dob'],'br_id'=>$id['br_id'],'lacc_chitno'=>$id['lacc_chitno'],'lacc_agrno'=>$id['lacc_agrno'],'lacc_pan'=>$id['lacc_pan'],'lacc_oldrefno'=>$id['lacc_oldrefno'],'lacc_securityentry'=>$id['lacc_securityentry'],'lacc_resaddr'=>$Residency1,'lacc_compaddr'=>$CompanyAddress1,'lacc_permaddr'=>$PermenentAddress1,'lacc_jobcategory'=>$id['lacc_jobcategory'],'lacc_gender'=>$id['lacc_gender'],'lacc_maritalst'=>$id['lacc_maritalst'],'lacc_jobprofile'=>$job1,'lacc_selfempdet'=>'','lacc_education'=>$id['lacc_education'],'lacc_profession'=>$id['lacc_profession'],'lacc_residencestatus'=>$id['lacc_residencestatus'],'lacc_monthlyrent'=>$id['lacc_monthlyrent'],'lacc_ihave'=>$id['lacc_ihave'],'lacc_bankac'=>$bank1,'lacc_coapplicantprofile'=>$coapplicant1,'lacc_ufc'=>$id['lacc_ufc'],'lacc_reference1'=>$UReference1,'lacc_reference2'=>$UReference2,'lacc_guarantor1'=>$Uguarantor1,'lacc_guarantor2'=>$Uguarantor2,'lacc_guarantor3'=>$Uguarantor3,'lacc_chitrefno'=>$id['lacc_chitrefno'],'lacc_chitbrid'=>$id['lacc_chitbrid'],'lacc_paidupamt'=>$id['lacc_paidupamt'],'lacc_selfempdet'=>$selfempdet1]);
			return $updateLoanApplicant;
		}
		
		public function referencenumberFindMod($id)
		{
			if($id['editId']==0)
			{
				$referencenumberFind=DB::table('lacc_loanaccount')->where('lacc_no',$id['lacc_oldrefno'])->first();
			}
			else
			{
				$referencenumberFind=DB::table('lacc_loanaccount')->where('lacc_id',$id['editId'])->first();
			}			
			return $referencenumberFind;
		}
		public function loantypeFindFindMod($id)
		{
			$referencenumberFind=DB::table('ltyp_loantypes')->where('ltyp_id',$id['ltyp_id'])->first();			
			return $referencenumberFind;
		}
		
		public function sureDeleteloanApp($id)
		{
			return DB::table('lacc_loanaccount')->where('lacc_id','=',$id['lacc_id'])
			->update(['lacc_status'=>'Cancel']);
		}
		
		public function viewloanprocessingfee($amt)
		{		
			return DB::select( DB::raw("select * from lpf_loanprocessingfee where $amt between lpf_famt and lpf_tamt  limit 1"));
		}
}