<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class IncExpReportModel 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 LoadCstRecMod($fund_cust_id)
	{
		return DB::table('cst_customer')->where('cst_code',$fund_cust_id)->where('cst_type','Member')->first();
	}
	public function TotPaidBalMod()
	{
		return DB::select( DB::raw("SELECT trn_transaction.trn_id,grd_grade.grd_grade,grd_grade.grd_prefix, ftyp_fundtype.ftyp_fundtype as ftype, grfn_gradefund.grfn_paycycle as paycycle,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE trn_id=trn_transaction.trn_id  AND bil_status='Active' ) AS totalamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE paid_trn_id=trn_transaction.trn_id AND bil_type in ('Payment','PREPAY') AND bil_status='Active' ) AS paidamt FROM trn_transaction LEFT JOIN grfn_gradefund ON trn_transaction.grfn_id=grfn_gradefund.grfn_id LEFT JOIN grd_grade ON grd_grade.grd_id=trn_transaction.grd_id LEFT JOIN ftyp_fundtype ON ftyp_fundtype.ftyp_id=trn_transaction.ftyp_id WHERE trn_transaction.trn_status='Active' AND trn_transaction.trn_type in ('Daily','Weekly','Monthly','Quarterly','Semiannually','Yearly','One time') ORDER BY trn_transaction.trn_id"));
	}
	public function fundtypewiserpt($id)
	{
		return DB::select( DB::raw("SELECT ftyp_fundtype.ftyp_fundtype,(SELECT ifnull(SUM(bil_billrec.bil_amt),0) FROM `bil_billrec` WHERE bil_billrec.fy_id='1' AND bil_billrec.bil_type in ('Daily','Weekly','Monthly','Quarterly','Semiannually','Yearly','One time') AND bil_billrec.ftyp_id=ftyp_fundtype.ftyp_id AND bil_billrec.bil_status='Active' ) as totamt , (SELECT ifnull(SUM(bil_billrec.bil_amt),0) FROM `bil_billrec` WHERE bil_billrec.fy_id='1' AND bil_billrec.bil_type in ('Payment','Advance') AND bil_billrec.ftyp_id=ftyp_fundtype.ftyp_id AND bil_billrec.bil_status='Active' ) as paidamt FROM ftyp_fundtype where ftyp_fundtype.ftyp_status='Active' AND ftyp_fundtype.ftyp_module='Jamath'"));
	}
	public function loadfundtypesingle($ftyp_id)
	{
		return DB::table('ftyp_fundtype')->where('ftyp_id',$ftyp_id)->first();
	}
	//Check Customer Balance starts here
	public function CheckBal($ftyp_id,$trn_type)
	{
		return DB::select( DB::raw("SELECT ifnull(sum(bil_amt),0) as totalamt FROM `bil_billrec` WHERE bil_type='$trn_type' AND ftyp_id='$ftyp_id' AND bil_status='Active'"));
	}
	//Check Customer Balance ends here
	//Transaction Report starts here
	public function TransactionReport($id)
	{
		$sql = "SELECT grd_grade.grd_prefix, cst_customer.cst_code, cst_customer.cst_name, trn_transaction.trn_id, trn_transaction.trn_billno, trn_transaction.trn_type, trn_transaction.trn_date, trn_transaction.trn_note, grd_grade.grd_grade, grd_grade.grd_prefix, ftyp_fundtype.ftyp_fundtype, (SELECT grd_grade.grd_prefix FROM cst_customer LEFT JOIN grd_grade ON cst_customer.grd_id=grd_grade.grd_id WHERE cst_customer.cst_id=trn_transaction.cst_id) as cstgrdprefix, (SELECT sum(`bil_amt`) FROM `bil_billrec` WHERE `trn_id`= trn_transaction.trn_id AND bil_billrec.bil_status='Active' ) billamt FROM `trn_transaction` LEFT JOIN grfn_gradefund ON trn_transaction.grfn_id=grfn_gradefund.grfn_id LEFT JOIN grd_grade ON grd_grade.grd_id=grfn_gradefund.grd_id LEFT JOIN ftyp_fundtype ON trn_transaction.ftyp_id=ftyp_fundtype.ftyp_id LEFT JOIN cst_customer ON cst_customer.cst_id=trn_transaction.cst_id WHERE trn_transaction.fy_id='1' AND trn_transaction.trn_status='Active' ";
		if($id['cst_id'] != 0)
		{
			$sql = $sql . " AND trn_transaction.cst_id='" . $id['cst_id'] . "'";
		}
		if($id['grd_id'] != "")
		{
			$sql = $sql . " AND (SELECT grd_grade.grd_id FROM cst_customer LEFT JOIN grd_grade ON cst_customer.grd_id=grd_grade.grd_id WHERE cst_customer.cst_id=trn_transaction.cst_id)='" . $id['grd_id'] . "'";
		}
		if($id['ftyp_id'] != "")
		{
			$sql = $sql . " AND trn_transaction.ftyp_id='" . $id['ftyp_id'] . "'";
		}
		if($id['grfn_paycycle'] != "")
		{
			if($id['grfn_paycycle'] == 'Onetime')
			{
				$paycycle = 'One time';
			}
			else
			{
				$paycycle = $id['grfn_paycycle'];
			}
			$sql = $sql . " AND trn_transaction.trn_type='" . $paycycle . "'";
		}
		if($id['fdate'] != "")
		{
			$sql = $sql . " AND ( trn_transaction.trn_date BETWEEN '$id[fdate]' AND '$id[tdate]' ) ";
		}
		else
		{
			$dt = date("Y-m-d");
			if($id['btnsubmitall'] != "")
			{
				
			}
			else
			{
			$sql = $sql . " AND ( trn_transaction.trn_date BETWEEN '$dt' AND '$dt' ) ";
			}
		}
		$sql = $sql . " ORDER BY trn_transaction.trn_date, trn_transaction.trn_id asc";
		return DB::select( DB::raw($sql));
	}
	//Transaction Report ends here
}