<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class LoanAccPanelModel 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 ('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,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 chequebounce 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', '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', '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'],'ltr_chq_bounce_id'=>$id['ltr_chq_bounce_id']]);
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
	public function LoanPaymentUpdateMod($id)
	{
		$insertLoanApplicant=DB::table('ltr_loantransaction')->where('ltr_id',$id['ltr_id'])->update(['ltr_status'=>'Cancelled']);
		$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'],'ltr_chq_bounce_id'=>$id['ltr_chq_bounce_id'],'ltr_del_id'=>$id['ltr_id']]);
		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 viewLoanRptSingle($id)
	{
		return DB::select( DB::raw("select * from ltr_loantransaction where lins_id='$id' ORDER BY ltr_transdt"));
	}
	public function viewLoanTransid($id)
	{
		return DB::select( DB::raw("select * from ltr_loantransaction where ltr_id='$id'"));
	}
	public function LoanPaymentInsertCancelMod($id)
	{
		$insertLoanApplicant=DB::table('ltr_loantransaction')
							->where('ltr_id',$id['ltr_id'])
							->update(['ltr_status'=>'Cancelled','ltr_cancellationtype'=>$id['ltr_cancellationtype'],'ltr_cancellationreason'=>$id['ltr_cancellationreason']]);
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
	public function viewRPTSingleTrans($lacc_id)
	{
		return DB::select( DB::raw("select * from ltr_loantransaction where lacc_id='$lacc_id' ORDER BY ltr_transdt"));
	}
	//Delay payment penalty code starts here
	public function AddDelayPaymentPenalty($id)
	{
		$lacc_id= $id['lacc_id'];
		$lins_id = $id['lins_id'];
		$daysdifference = $id['daysdifference'];
		$delaypmt_chargedate = "";
		$data['chkDelayPayment'] = DB::select( DB::raw("select * from ltr_loantransaction where lacc_id='$lacc_id' AND ltr_transtype='Delay-Payment' AND lins_id='$lins_id' ORDER BY ltr_transdt DESC limit 0,1"));
		foreach($data['chkDelayPayment'] as $LoanApplicantRec1)
		{
			$delaypmt_chargedate = $LoanApplicantRec1->ltr_transdt;
		}		
		
		if($delaypmt_chargedate == "")
		{
			$delaysum = $id['ltr_amt'] * $id['delaypmt_charge']/100;
			$totaldelaysum = $delaysum /365 * $daysdifference;
		}
		else
		{
			$currentdt = strtotime(date("Y-m-d")); //Current day
			$installmentdt = strtotime($delaypmt_chargedate); //Installment day
			$datediff = $currentdt - $installmentdt;				
			$daysdifference =  round($datediff / (60 * 60 * 24)) ;
			$delaysum = $id['ltr_amt'] * $id['delaypmt_charge']/100;
			$totaldelaysum = $delaysum /365 * $daysdifference;
		}
			
		$note = "Delay Payment penalty for  ". $daysdifference ." days..";
		$dt = date("Y-m-d");
		
		if($daysdifference > 0)
		{
		$insertLoanApplicant=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$lins_id,'ltr_transdt'=>$dt,'ltr_transtype'=>'Delay-Payment','ltr_billno'=>0,'ltr_amt'=>$totaldelaysum,'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$note,'ltr_status'=>'Active']);
		}
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
	//Delay payment penalty code ends here
	
	public function InsDelayManualPaymentMod($data)
	{
		$lacc_id = $data['lacc_id'];
		$lins_id = $data['lins_id'];
		$dpmtemiamt = $data['dpmtemiamt'];
		$dpmtfdt = $data['dpmtfdt'];
		$dpmttdt = $data['dpmttdt'];
		$dpmtnodays = $data['dpmtnodays'];
		$dpmtdelayinterest = $data['dpmtdelayinterest'];
		$dpmttotpenaltyamt = $data['dpmttotpenaltyamt'];
		$note = "Delay Payment penalty for  ". $dpmtnodays ." days.. | From -" .$dpmtfdt . " | To -" . $dpmttdt . " | Penalty - " . $dpmtdelayinterest . "%";
		$insertLoanApplicant=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$lacc_id,'lins_id'=>$lins_id,'ltr_transdt'=>$dpmttdt,'ltr_transtype'=>'Delay-Payment','ltr_billno'=>0,'ltr_amt'=>$dpmttotpenaltyamt,'ltr_paymenttype'=>'','ltr_chqno'=>'','ltr_bank'=>'','ltr_note'=>$note,'ltr_status'=>'Active']);
		return $data=['status' => 'Success','entry_type' => 'Insert'];
	}
	
	//Coding to update delay payment starts here
	public function DelayPaymentUpdateMod($id)
	{
		$DelayAmountCharge=DB::table('lacc_loanaccount')->where('lacc_id',$id['lacc_id'])->update(['dpmt_charge'=>$id['dpmt_charge']]);
		return $data=['status' => 'Success','entry_type' => 'Update'];
	}
	//Coding to update delay payment ends here	
	//Coding to lins_loaninstallment starts here
	public function viewloansingleinstallmentdetail($id)
	{
		return DB::select( DB::raw("select * from lins_loaninstallment where lins_id='$id'"));
	}
	//Coding to lins_loaninstallment ends here
	//Coding to Loan EMI No. starts here
	public function viewloansingleEMIdetail($id)
	{
		return $lins_rec = DB::table('lins_loaninstallment')->select('lins_no')->where('lins_id',$id)->first();
	}
	//Coding to Loan EMI No. starts here
	//Coding to Edit Payment starts here
	public function ModEditPaymentCtrl($id)
	{
		return DB::select( DB::raw("select * from ltr_loantransaction where ltr_id='$id'"));
	}
	//Coding to Edit Payment ends here
}