<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class LoanPreclosureModel 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 viewloanapplicantion($id)
	{
		return DB::table('lacc_loanaccount')
		->leftjoin('br_branch','br_branch.br_id','=','lacc_loanaccount.br_id')
		->where('lacc_id',$id)
		->get();
	}	
	public function viewLoanInstallmentRecords($id)
	{
		return DB::select( DB::raw("select *,IFNULL((SELECT  sum(ltr_amt) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id=lins_loaninstallment.lacc_id AND ltr_loantransaction.lins_id=lins_loaninstallment.lins_id AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Cheque Bounce')), 0 )  as chequebouncecharges, IFNULL((SELECT  sum(ltr_amt) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id=lins_loaninstallment.lacc_id AND ltr_loantransaction.lins_id=lins_loaninstallment.lins_id AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Case')), 0 )  as casecharges, IFNULL((SELECT  sum(ltr_amt) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id=lins_loaninstallment.lacc_id AND ltr_loantransaction.lins_id=lins_loaninstallment.lins_id AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Others')), 0 )  as othercharges, IFNULL((SELECT  sum(ltr_amt) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id=lins_loaninstallment.lacc_id AND ltr_loantransaction.lins_id=lins_loaninstallment.lins_id AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Cheque Bounce', 'Case', 'Delay payment', 'Others')), 0 )  as penaltysum,IFNULL((SELECT  sum(ltr_amt) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id=lins_loaninstallment.lacc_id AND ltr_loantransaction.lins_id=lins_loaninstallment.lins_id AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Delay-payment')), 0 )  as delayamt from `lins_loaninstallment` where lins_loaninstallment.`lacc_id` = $id AND lins_loaninstallment.lins_status='Pending' order by lins_loaninstallment.`lins_no` asc") );
	}
	public function LoancurrentPayable($id)
	{
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lacc_id')->where('lins_id',$id)->first();
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$lins_loaninstallment_rec->lacc_id)
										->where('lins_id','=',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Principle', 'Interest', 'Cheque Bounce', 'Case', 'Delay payment', 'Others'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function LoanpreviousPayable($id)
	{
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lacc_id')->where('lins_id',$id)->first();
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$lins_loaninstallment_rec->lacc_id)
										->where('lins_id','<',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Principle', 'Interest', 'Cheque Bounce', 'Case', 'Delay payment', 'Others'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function LoanCurrentPaid($id)
	{
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lacc_id')->where('lins_id',$id)->first();
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$lins_loaninstallment_rec->lacc_id)
										->where('lins_id','=',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Payment'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function LoanPreviousPaid($id)
	{
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lacc_id')->where('lins_id',$id)->first();
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$lins_loaninstallment_rec->lacc_id)
										->where('lins_id','<',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Payment'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function LoanPaymentInsertMod($id)
	{
		$insertLoanApplicant=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$id['lacc_id'],'lins_id'=>$id['lins_id'],'ltr_transdt'=>$id['ltr_transdt'],'ltr_transtype'=>$id['ltr_transtype'],'ltr_billno'=>$id['ltr_billno'],'ltr_amt'=>$id['ltr_amt'],'ltr_paymenttype'=>$id['ltr_paymenttype'],'ltr_chqno'=>$id['ltr_chqno'],'ltr_bank'=>$id['ltr_bank'],'ltr_note'=>$id['ltr_note'],'ltr_status'=>$id['ltr_status']]);
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
	public function LoanTotalPaid($id)
	{
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Payment'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function UpdateLoanInstallment($id)
	{
		$onlydt= date("Y-m-d");
		$dt = date("d-M-Y h:i A");		
		$lacc_id=$id['lacc_id'];
		
		$arrlins_id=$id['arrlins_id'];
		$arrlins_no=$id['arrlins_no'];
		//
		$arrlins_amt=$id['arrlins_amt'];
		$arrlins_iperc=$id['arrlins_iperc'];
		$arrlins_iamt=$id['arrlins_iamt'];
		$arrdelayamt=$id['arrdelayamt'];
		$arrtotamt=$id['arrtotamt'];
		$arrpaid_amt=$id['arrpaid_amt'];
		$arrbal_amt=$id['arrbal_amt'];
		$arrlins_date=$id['arrlins_date'];
		$arrlins_chqno=$id['arrlins_chqno'];
		$arrlins_note=$id['arrlins_note'];
		$arrchequebouncecharges=$id['arrchequebouncecharges'];
		$arrcasecharges=$id['arrcasecharges'];
		$arrothercharges=$id['arrothercharges'];
		//
		$hidden_arrlins_amt=$id['hidden_arrlins_amt'];
		$hidden_arrlins_iperc=$id['hidden_arrlins_iperc'];
		$hidden_arrlins_iamt=$id['hidden_arrlins_iamt'];
		$hidden_arrdelayamt=$id['hidden_arrdelayamt'];
		$hidden_arrtotamt=$id['hidden_arrtotamt'];
		$hidden_arrpaid_amt=$id['hidden_arrpaid_amt'];
		$hidden_arrbal_amt=$id['hidden_arrbal_amt'];
		$hidden_arrlins_date=$id['hidden_arrlins_date'];
		$hidden_arrlins_chqno=$id['hidden_arrlins_chqno'];
		$hidden_arrlins_note=$id['hidden_arrlins_note'];
		$hidden_arrchequebouncecharges=$id['hidden_arrchequebouncecharges'];
		$hidden_arrcasecharges=$id['hidden_arrcasecharges'];
		$hidden_arrothercharges=$id['hidden_arrothercharges'];
		//
		for($i=0;$i<count($arrlins_id);$i++)
		{
			//Search same or not..
			$lins_loaninstallment_rec1 = DB::table('lins_loaninstallment')->where('lins_id',$arrlins_id[$i])->where('lins_date',$arrlins_date[$i])->where('lins_amt',$arrlins_amt[$i])->where('lins_iperc',$arrlins_iperc[$i])->where('lins_iamt',$arrlins_iamt[$i])->where('lins_chqno',$arrlins_chqno[$i])->where('lins_note',$arrlins_note[$i])->first();
			if(!isset($lins_loaninstallment_rec1))
			{
				$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->where('lins_id',$arrlins_id[$i])->first();
				//Update Loan Installment
				$itemUp=DB::table('lins_loaninstallment')->where('lins_id',$arrlins_id[$i])
				->update(['lins_date'=>$arrlins_date[$i],'lins_amt'=>$arrlins_amt[$i],'lins_iperc'=>$arrlins_iperc[$i],'lins_iamt'=>$arrlins_iamt[$i],'lins_chqno'=>$arrlins_chqno[$i],'lins_note'=>$arrlins_note[$i]]);
				//Insert Loan installment history
				$noteanddt =$lins_loaninstallment_rec->lins_note . "- Updated on -". $dt;
				$insertLoanApplicant=DB::table('lins_loaninstallment')->insertGetId(['lacc_id'=>$lins_loaninstallment_rec->lacc_id,'lins_no'=>$lins_loaninstallment_rec->lins_no,'lins_date'=>$lins_loaninstallment_rec->lins_date,'lins_amt'=>$lins_loaninstallment_rec->lins_amt,'lins_iperc'=>$lins_loaninstallment_rec->lins_iperc,'lins_iamt'=>$lins_loaninstallment_rec->lins_iamt,'lins_chqno'=>$lins_loaninstallment_rec->lins_chqno,'lins_note'=>$noteanddt,'lins_status'=>'Updated']);
			}
			//Principle starts here
			if($arrlins_amt[$i] != $hidden_arrlins_amt[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Principle')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$arrlins_date[$i],'ltr_transtype'=>'Principle','ltr_billno'=>0,'ltr_amt'=>$arrlins_amt[$i],'ltr_paymenttype'=>'','ltr_chqno'=>$arrlins_chqno[$i],'ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//Principle ends here
			//#Cheque Bounce starts here
			if($arrchequebouncecharges[$i] != $hidden_arrchequebouncecharges[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Cheque Bounce')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$onlydt,'ltr_transtype'=>'Cheque Bounce','ltr_billno'=>0,'ltr_amt'=>$arrchequebouncecharges[$i],'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//#Cheque Bounce ends here
			//##Legal case Charges starts here
			if($arrcasecharges[$i] != $hidden_arrcasecharges[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Case')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$onlydt,'ltr_transtype'=>'Case','ltr_billno'=>0,'ltr_amt'=>$arrcasecharges[$i],'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//##Legal case ends here
			//###Other Charges starts here
			if($arrothercharges[$i] != $hidden_arrothercharges[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Others')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$onlydt,'ltr_transtype'=>'Others','ltr_billno'=>0,'ltr_amt'=>$arrothercharges[$i],'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//###Other Charges ends here
			//###Delay Payment Charges starts here
			if($arrdelayamt[$i] != $hidden_arrdelayamt[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Delay-Payment')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$onlydt,'ltr_transtype'=>'Delay-Payment','ltr_billno'=>0,'ltr_amt'=>$arrdelayamt[$i],'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//###Delay Payment Charges ends here
			//###Paid Amount starts here
			if($arrpaid_amt[$i] != $hidden_arrpaid_amt[$i])
			{
				$cancelnote = "Modifications done in Loan settings - ".$dt;
				$qTransUpd=DB::table('ltr_loantransaction')->where('lins_id',$arrlins_id[$i])->where('ltr_transtype','Payment')->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>'LoanSettings','ltr_cancellationreason'=>$cancelnote]);
				$qTransIns=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$arrlins_id[$i],'ltr_transdt'=>$onlydt,'ltr_transtype'=>'Payment','ltr_billno'=>0,'ltr_amt'=>$arrpaid_amt[$i],'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$arrlins_note[$i],'ltr_status'=>'Active']);
			}
			//###Paid Amount ends here			
		}
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
}