<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class LoanArrearModel 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 viewloanapplicantionrecord($id)
	{
		$currentdate = date("Y-m-d");
		$startdate= date("Y-m-01");
		$enddate= date("Y-m-31");
		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, IFNULL(( select  ( CASE 
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (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') AND ltr_transdt<=(select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle'))) WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 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') AND ltr_transdt<=(select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1))
        ELSE 0 END) AS previousbalance), 0 ) as LoanOutstandingEMIamt, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (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') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Principle', 'Interest') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')))
        ELSE 0    END) AS Currentemi),0)  AS Currentemi, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Delay payment', 'Delay-Payment') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Delay payment', 'Delay-Payment') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle'))) ELSE 0    END) AS Currentemi),0)  AS penalinterest, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Cheque Bounce', 'Case', 'Others') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Cheque Bounce', 'Case', 'Others') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle'))) ELSE 0    END) AS Currentemi),0)  AS legalothercharge from `lacc_loanaccount` left join `br_branch` on `br_branch`.`br_id` = `lacc_loanaccount`.`br_id` where `lacc_status` = 'Active'"));
	}
		public function viewloanarreartermination($id)
	{
		$currentdate = date("Y-m-d");
		$startdate= date("Y-m-01");
		$enddate= date("Y-m-31");
		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, IFNULL(( select  ( CASE 
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (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') AND ltr_transdt<=(select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt > '$currentdate') and `ltr_transtype` in ('Principle'))) WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 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') AND ltr_transdt<=(select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1))
        ELSE 0 END) AS previousbalance), 0 ) as LoanOutstandingEMIamt, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt > '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (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') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt > '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Principle', 'Interest') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')))
        ELSE 0    END) AS Currentemi),0)  AS Currentemi, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Delay payment', 'Delay-Payment') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Delay payment', 'Delay-Payment') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle'))) ELSE 0    END) AS Currentemi),0)  AS penalinterest, IFNULL((select  (    CASE         WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Cheque Bounce', 'Case', 'Others') AND (ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt BETWEEN '$startdate' AND '$currentdate') and `ltr_transtype` in ('Principle')) AND  (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt >= '$startdate' ) and `ltr_transtype` in ('Principle') order by ltr_transdt ASC limit 1,1) ) )
        WHEN IFNULL((select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle')),0)  != 0 THEN (select sum( `ltr_amt` ) as aggregate1 from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' and `ltr_transtype` in ('Cheque Bounce', 'Case', 'Others') AND ltr_transdt BETWEEN (select ltr_transdt as frmdt from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND (ltr_transdt < '$enddate') and `ltr_transtype` in ('Principle') order by frmdt desc limit 1,1) AND  (select ltr_transdt as frmdt  from `ltr_loantransaction` where `lacc_id` =lacc_loanaccount.lacc_id and `ltr_status` = 'Active' AND ltr_transdt >'$currentdate' AND ltr_transdt <='$enddate' and `ltr_transtype` in ('Principle'))) ELSE 0    END) AS Currentemi),0)  AS legalothercharge from `lacc_loanaccount` left join `br_branch` on `br_branch`.`br_id` = `lacc_loanaccount`.`br_id` where `lacc_status` = 'Active'"));
	}
}