<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class GlobalTransReportModel 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 TransactionReport($id)
	{
		//Fiscal Year
		$data['fiscalyear'] = DB::table('fyr_fiscalyear')->whereRaw('? between fyr_startdate and fyr_enddate', [date('Y-m-d')])->where([["fyr_status","=",'Active']])->get();
		$fyrid = $data['fiscalyear'][0]->fyr_id;
		$sql = "SELECT trn_transaction.trn_id, trn_transaction.trn_billno, bil_billrec.bil_type, trn_transaction.trn_type, bil_billrec.bil_amt, bil_billrec.bil_date, trn_transaction.trn_date, cst_customer.cst_code,cst_customer.cst_name, grd_grade.grd_prefix, trn_transaction.trn_note, ftyp_fundtype.ftyp_fundtype FROM bil_billrec LEFT JOIN trn_transaction ON bil_billrec.trn_id=trn_transaction.trn_id LEFT JOIN cst_customer ON cst_customer.cst_id=bil_billrec.cst_id LEFT JOIN grd_grade ON grd_grade.grd_id=cst_customer.grd_id LEFT JOIN ftyp_fundtype ON ftyp_fundtype.ftyp_id=bil_billrec.ftyp_id WHERE bil_billrec.bil_status='Active' AND bil_billrec.bil_amt>0 ";
		$sql = $sql . " AND bil_billrec.bil_type IN (";
		if(null !== $id['arrother_listmod'])
		{
			if ( null !== $id['arrjamath_listmod'])
			{
				$sql = $sql . "'Advance','Payment',";
			}
			if ( null !== $id['arrspecialmember_listmod'])
			{
				$sql = $sql . "'Special','sPayment',";
			}
			if ( null !== $id['arrincome_listmod'])
			{
				$sql = $sql . "'Income',";
			}
			if ( null !== $id['arrexpense_listmod'])
			{
				$sql = $sql . "'Expense',";
			}
			if ( null !== $id['arrloan_listmod'])
			{
				$sql = $sql . "'Loan',";
			}
			$arrrecs = $id['arrother_listmod'];
			for($i=0;$i<count($id['arrother_listmod']);$i++)
			{
				$sql = $sql . "'" . $arrrecs[$i] . "',";
			}
		}
		else
		{
			
			if ( null !== $id['submit'] )
			{
				$sql = $sql . "'Expense','Income','Loan','Payment','Advance','sPayment','Special'";
			}
			else
			{
				$sql = $sql . "'Expense','Income','Loan','Payment','Advance','sPayment','Special','Matrimonial'";
			}
				$sql = $sql . ","; //to delete one character
		}
		$sql= substr_replace($sql ,"", -1);
		$sql = $sql . " ) ";
		if ( null !== $id['arrjamath_listmod'] ||  null !== $id['arrspecialmember_listmod'] ||  null !== $id['arrincome_listmod'] ||  null !== $id['arrexpense_listmod'] ||  null !== $id['arrloan_listmod'] )
		{
			$sql = $sql . " AND bil_billrec.ftyp_id IN (";
			if (null !== $id['arrjamath_listmod'])
			{
				$arrrecs = $id['arrjamath_listmod'];
				for($i=0;$i<count($id['arrjamath_listmod']);$i++)
				{
					$sql = $sql . "'" . $arrrecs[$i] . "',";
				}
			}
			if (null !== $id['arrspecialmember_listmod'])
			{
				$arrrecs = $id['arrspecialmember_listmod'];
				for($i=0;$i<count($id['arrspecialmember_listmod']);$i++)
				{
					$sql = $sql . "'" . $arrrecs[$i] . "',";
				}
			}
			if (null !== $id['arrincome_listmod'])
			{
				$arrrecs = $id['arrincome_listmod'];
				for($i=0;$i<count($id['arrincome_listmod']);$i++)
				{
					$sql = $sql . "'" . $arrrecs[$i] . "',";
				}
			}
			if (null !== $id['arrexpense_listmod'])
			{
				$arrrecs = $id['arrexpense_listmod'];
				for($i=0;$i<count($id['arrexpense_listmod']);$i++)
				{
					$sql = $sql . "'" . $arrrecs[$i] . "',";
				}
			}
			if (null !== $id['arrloan_listmod'])
			{
				$arrrecs = $id['arrloan_listmod'];
				for($i=0;$i<count($id['arrloan_listmod']);$i++)
				{
					$sql = $sql . "'" . $arrrecs[$i] . "',";
				}
			}
			/*
			if(null !== $id['arrother_listmod'])
			{
				$arrrecs = $id['arrother_listmod'];
				for($i=0;$i<count($id['arrother_listmod']);$i++)
				{
					$sql = $sql . "'0',";
				}
			}
			else
			{
			$sql = $sql . "'0',";
			}
			*/
			$sql= substr_replace($sql ,"", -1);
			$sql = $sql . " ) ";
		}
		$sql = $sql . " AND trn_transaction.fy_id='$fyrid' AND trn_transaction.trn_status='Active' ";
		if($id['fdate'] != "")
		{
			$sql = $sql . " AND ( bil_billrec.bil_date BETWEEN '$id[fdate]' AND '$id[tdate]' ) ";
		}
		else
		{
			$dt = date("Y-m-d");
			if($id['submit'] != "")
			{
				
			}
			else
			{
			$sql = $sql . " AND ( bil_billrec.bil_date BETWEEN '$dt' AND '$dt' ) ";
			}
		}
		$sql = $sql . " ORDER BY bil_billrec.bil_date, bil_billrec.bil_id asc";
		return DB::select( DB::raw($sql));
	}
	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 SummaryTransRpt($id)
	{
		$dt = $id['tdate'];
		$sql = "select (SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE  bil_type in ('Payment','Advance','PREPAY','sPayment','sAdvance','sPREPAY','Loan','Income','Matrimonial') AND bil_status='Active' AND bil_billrec.bil_date<'$dt') AS prepaidamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE  bil_type in ('Payment','Advance','PREPAY','sPayment','sAdvance','sPREPAY','Loan','Income','Matrimonial') AND bil_status='Active' AND bil_billrec.bil_date='$dt') AS currpaidamt, (SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE  bil_type in ('Expense') AND bil_status='Active' AND bil_billrec.bil_date<'$dt') AS preexpamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE  bil_type in ('Expense') AND bil_status='Active' AND bil_billrec.bil_date='$dt') AS currexpamt from `cst_customer` left join `grd_grade` on `grd_grade`.`grd_id` = `cst_customer`.`grd_id` where cst_customer.cst_status != 'Deleted' AND cst_customer.cst_type='Member' LIMIT 1";
		return DB::select( DB::raw($sql));
	}
}