<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class EMIreportModel 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 viewloaninstallmentids($id)
	{
		return DB::table('lins_loaninstallment')->where('lins_id',$id)->get();
	}
	public function viewLoanEMIstartDate($id)
	{
		return DB::select( DB::raw("select min(lins_id) from lins_loaninstallment where lacc_id='$id'"));
	}
	public function viewLoanEMIendDate($id)
	{
		return DB::select( DB::raw("select max(lins_id) from lins_loaninstallment where lacc_id='$id'"));
	}
	public function viewloanapplicantion($id)
	{
		return DB::table('lacc_loanaccount')
		->select('lacc_loanaccount.*','loanbranch.br_name as br_name','chitbranch.br_name as chitbr')
		->leftjoin('br_branch as loanbranch','loanbranch.br_id','=','lacc_loanaccount.br_id')
		->leftjoin('br_branch as chitbranch','chitbranch.br_id','=','lacc_loanaccount.lacc_chitbrid')
		->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', '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 sumChequeBouncecharge, 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', 'Others')), 0 )  as sumcaseothercharge from `lins_loaninstallment` where lins_loaninstallment.`lacc_id` = $id order by lins_loaninstallment.`lins_no` asc"));
	}
	public function LoanPaymentRecords($id)
	{		
		return DB::select( DB::raw("select a.ltr_id, lins_loaninstallment.lins_no, a.lins_id, a.ltr_billno, a.ltr_transdt,a.ltr_amt, ifnull((select sum(ltr_amt) from ltr_loantransaction as b WHERE b.lacc_id='$id' AND b.lins_id=a.lins_id AND b.ltr_transtype='Principle' AND b.ltr_status='Active'),0) as principleamt, ifnull((select sum(ltr_amt) from ltr_loantransaction as b WHERE b.lacc_id='$id' AND b.lins_id=a.lins_id AND b.ltr_transtype='Interest' AND b.ltr_status='Active'),0) as Interestamt, ifnull((select sum(ltr_amt) from ltr_loantransaction as b WHERE b.lacc_id='$id' AND b.lins_id=a.lins_id AND (b.ltr_transtype='Delay payment' || b.ltr_transtype='Delay-Payment') AND b.ltr_status='Active'),0) as penaltyamt, ifnull((select sum(ltr_amt) from ltr_loantransaction as b WHERE b.lacc_id='$id' AND b.lins_id=a.lins_id AND (b.ltr_transtype='Cheque Bounce') AND b.ltr_status='Active'),0) as chqbounce, ifnull((select sum(ltr_amt) from ltr_loantransaction as b WHERE b.lacc_id='$id' AND b.lins_id=a.lins_id AND (b.ltr_transtype='Others' || b.ltr_transtype='Case') AND b.ltr_status='Active'),0) as othercharges, ltr_paymenttype,ltr_chqno,ltr_bank from ltr_loantransaction as a LEFT JOIN lins_loaninstallment ON a.lins_id=lins_loaninstallment.lins_id WHERE a.ltr_transtype='Payment' AND a.lacc_id='$id' AND a.ltr_status='Active' ORDER BY a.ltr_transdt,a.ltr_id") );
	}
	public function LastPaymentInstallmentId($id)
	{
		return DB::select( DB::raw("select max(a.lins_id) as maxlinsid from ltr_loantransaction as a WHERE a.ltr_transtype='Payment' AND a.lacc_id='$id' AND a.ltr_status='Active'") );		
	}
	public function LoanNextInstallments($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', '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 sumChequeBouncecharge, 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', 'Others')), 0 )  as sumcaseothercharge from `lins_loaninstallment` where lins_loaninstallment.`lacc_id` = $id order by lins_loaninstallment.`lins_no` asc"));
	}
	public function LoanChqBounceRecordsMod($id)
	{
		return DB::select( DB::raw("select a.ltr_id, a.ltr_transdt as cancellationdate, a.ltr_amt as cancellationcharge, b.ltr_billno, b.ltr_transdt, b.ltr_amt as chequeamt, b.ltr_chqno, b.ltr_bank , c.lins_no from ltr_loantransaction as a LEFT JOIN ltr_loantransaction as b ON a.ltr_chq_bounce_id=b.ltr_id LEFT JOIN lins_loaninstallment as c ON b.lins_id=c.lins_id WHERE a.ltr_transtype='Cheque Bounce' AND a.lacc_id='$id' AND a.ltr_status='Active' ORDER BY a.ltr_transdt"));
	}
	public function LoanExpenseRecordsMod($id)
	{
		return DB::select( DB::raw("select a.ltr_id, c.lins_no, a.ltr_transtype, a.ltr_transdt as expensedate, a.ltr_amt as expenseamt, a.ltr_note from ltr_loantransaction as a LEFT JOIN lins_loaninstallment as c ON a.lins_id=c.lins_id WHERE a.ltr_transtype in ( 'Case', 'Delay payment', 'Delay-Payment', 'Others') AND a.lacc_id='$id' AND a.ltr_status='Active' ORDER BY  a.ltr_transdt"));
	}
	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 LoanAllCharges($id)
	{
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Cheque Bounce', 'Case', 'Delay payment','Delay-payment', 'Others'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	public function LoanTotalInterest($id)
	{
		return $ltr_loantransaction = DB::table('ltr_loantransaction')
										->where('lacc_id','=',$id)
										->where('ltr_status','Active')
										->whereIn('ltr_transtype', ['Interest'])
										->sum(DB::Raw('IFNULL( `ltr_amt` , 0 )'));
	}
	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($linsid)
	{
		return DB::select( DB::raw("select ltr_loantransaction.ltr_billno, ltr_loantransaction.ltr_transdt, lins_loaninstallment.lins_date, lins_loaninstallment.lins_amt,lins_loaninstallment.lins_iamt,(SELECT ifnull(sum(ltr_amt),0) FROM ltr_loantransaction WHERE lins_id='$linsid' AND ltr_transtype='Cheque Bounce') AS bankcharges,(SELECT ifnull(sum(ltr_amt),0) FROM ltr_loantransaction WHERE lins_id='$linsid' AND ltr_transtype='Delay payment') AS latefee,(SELECT ifnull(sum(ltr_amt),0) FROM ltr_loantransaction WHERE lins_id='$linsid' AND (ltr_transtype='Case' OR ltr_transtype='Others')) as caseotherchrge, ltr_paymenttype,ltr_chqno from ltr_loantransaction LEFT JOIN lins_loaninstallment on ltr_loantransaction.lins_id=lins_loaninstallment.lins_id WHERE ltr_loantransaction.lins_id='$linsid' and ltr_loantransaction.ltr_transtype='Payment' AND ltr_loantransaction.ltr_status='Active' ORDER BY `ltr_loantransaction`.`ltr_billno`"));
	}
	//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'];
	}
	
	public function CurrentInstallmentIDMod($id)
	{
		$currentdt = date("Y-m-d");
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lins_id','lins_date')->where('lacc_id',$id)->where('lins_date','>=',$currentdt)->where('lins_status','Pending')->first();
		if($lins_loaninstallment_rec == "")
		{
			$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lins_id','lins_date')->where('lacc_id',$id)->where('lins_status','Pending')->orderBy('lins_date', 'DESC')->first();
		}
		return $lins_loaninstallment_rec;
	}
	
	//Delay payment penalty code ends here
	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 AllInstRecordsMod($id,$linsid)
	{
		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' AND lins_loaninstallment.lins_id < '$linsid' order by lins_loaninstallment.`lins_no` asc") );
	}
	public function viewArrear($lacc_id,$lins_id)
	{
		$currentdt = date("Y-m-d");
		$lins_loaninstallment_rec = DB::table('lins_loaninstallment')->select('lins_id','lins_date')->where('lacc_id',$lacc_id)->where('lins_date','>=',$currentdt)->where('lins_status','Pending')->first();
		if($lins_loaninstallment_rec == "")
		{
			return $ArrearRec = DB::select( DB::raw("SELECT (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Principle')) as totprinciple,(SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Interest')) as totinterest, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Case')) as totcase, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ( 'Delay payment', 'Delay-payment')) as totdelpay, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Others')) as totothers, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Cheque Bounce')) as totchqbounce, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Payment')) as totpaid, 0 as currentprinciple, 0 as currentinterest, 0 as currentcase, 0 as currentdelpay, 0 as currentothers,0 as currentchqbounce, 0 as currentpaid") );
		}
		else
		{
			return $ArrearRec = DB::select( DB::raw("SELECT (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Principle')) as totprinciple,(SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Interest')) as totinterest, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Case')) as totcase, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ( 'Delay payment', 'Delay-payment')) as totdelpay, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Others')) as totothers, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Cheque Bounce')) as totchqbounce, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id<'$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Payment')) as totpaid, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Principle')) as currentprinciple,(SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Interest')) as currentinterest, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ('Case')) as currentcase, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in ( 'Delay payment', 'Delay-payment')) as currentdelpay, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Others')) as currentothers, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Cheque Bounce')) as currentchqbounce, (SELECT IFNULL(sum(ltr_amt),0) from ltr_loantransaction WHERE ltr_loantransaction.lacc_id='$lacc_id' AND ltr_loantransaction.lins_id='$lins_id' AND ltr_loantransaction.ltr_status='Active' AND ltr_loantransaction.ltr_transtype in (  'Payment')) as currentpaid") );
		}
		return $ArrearRec;		
	}
	public function viewAllInstRec($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 viewloanapplicantionx($id)
	{
		return DB::table('lacc_loanaccount')
		->leftjoin('br_branch','br_branch.br_id','=','lacc_loanaccount.br_id')
		->where('lacc_id',$id)
		->get();
	}	
	public function viewLoanInstallmentRecordsx($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 LoanTotalPaidx($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 )'));
	}
		//Delay payment penalty code starts here
	public function AddDelayPaymentPenaltyx($id)
	{
		$lacc_id= $id['lacc_idx'];
		$lins_id = $id['lins_idx'];
		$daysdifference = $id['daysdifferencex'];
		$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
}