<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class IncomeExpenseReportModel 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)
	{
		$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 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, 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='$fyrid' AND trn_transaction.trn_status='Active' ";
		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['submit'] != "")
			{
				
			}
			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));
	}
	public function LoadSLRecMod($trntype)
	{
		//Maximum Expense Bill Number
		$dbill=DB::table('trn_transaction')->where('trn_type',$trntype)->orderBy('trn_billno', 'desc')->first();
		if(is_null($dbill))
		{
			return $billno=1;
		}
		else
		{
			return $billno=$dbill->trn_billno + 1;
		}
	}
	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)
	{
		$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;
		return DB::select( DB::raw("SELECT ftyp_fundtype.ftyp_fundtype,ftyp_fundtype.ftyp_module,(SELECT ifnull(SUM(bil_billrec.bil_amt),0) FROM `bil_billrec` WHERE bil_billrec.fy_id='$fyrid' AND bil_billrec.ftyp_id=ftyp_fundtype.ftyp_id) as totamt , (0) as paidamt FROM ftyp_fundtype where ftyp_fundtype.ftyp_status='Active' AND (ftyp_fundtype.ftyp_module='Income' or ftyp_fundtype.ftyp_module='Expense' or ftyp_fundtype.ftyp_module='Loan')"));
	}
	public function billgenerate($id)
	{
		$user = auth()->user();
		$Username =  $user->usr_id;
		$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;
		//###Insert Customer record
		$insertcustomer=DB::table('cst_customer')->insertGetId(['cst_type'=>$id['billtype'],'cst_name'=>$id['name'],'cst_status'=>'Active']);
		//###Transaction table insertion code
		$transaction=DB::table('trn_transaction')->insertGetId(['cst_id'=>$insertcustomer, 'fy_id'=>$fyrid, 'grd_id'=>0, 'grfn_id'=>0, 'ftyp_id'=>$id['ftyp_id'], 'trn_type'=>$id['billtype'], 'trn_billno'=>$id['billno'], 'trn_amt'=>0, 'trn_date'=>$id['billdate'], 'trn_note'=>$id['bil_note'], 'trn_paytype'=>'', 'usr_name'=>$Username, 'trn_status'=>'Active']);
		//##Bill Record insertion
		$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>$id['billtype'],'trn_id'=>$transaction,'cst_id'=>$insertcustomer,'fy_id'=>$fyrid,'ftyp_id'=>$id['ftyp_id'],'bil_amt'=>$id['txtsubtotal'],'bil_date'=>$id['billdate'],'bil_note'=>$id['bil_note'],'usr_name'=>$Username,'paid_trn_id'=>0,'bil_status'=>'Active']);
		//###Item insertion record	starts here		
		$itemname = $id['itemname'];
		$itemcost = $id['itemcost'];
		$itemquantity = $id['itemquantity'];
		$slno=1;
		for($i=0;$i<count($itemname);$i++)
		{
			$totamt = $itemcost[$i] * $itemquantity[$i];
			$biltransaction=DB::table('bilr_bilreceipt')->insertGetId(['trn_id'=>$transaction,'bilr_slno'=>$slno,'bilr_ftype'=>$itemname[$i],'bilr_paycycle'=>'','bilr_totbalamt'=>$itemcost[$i],'bilr_paidamt'=>$itemquantity[$i],'bilr_balamt'=>$totamt,'bilr_status'=>'Active']);
			$slno = $slno + 1;
		}
		//###Item insertion record	ends here		
		return $data=['status' => 'Success','bil_id' => $transaction];
	}
	//Load Transaction detail starts here
	public function transactionrpt($id)
	{
		$trn_transaction=DB::table('trn_transaction')->where('trn_id',$id)->first();		
		return $trn_transaction;
	}
	//Load Transaction detail ends here
	public function LoadSingleCustMod($fund_cust_id)
	{			
		return DB::table('cst_customer')->where('cst_id',$fund_cust_id)->first();
	}
	public function LoadCustGrade($grdid)
	{			
		return DB::table('grd_grade')->where('grd_id',$grdid)->first();
	}
	//Load Print Receipt starts here
	public function billreceiptmod($id)
	{
		return DB::table('bilr_bilreceipt')->where('trn_id',$id)->orderBy('bilr_slno')->get();
	}
	//Load Print Receipt ends here
	//Check Customer Balance starts here
	public function CheckCustomerBal($cstid)
	{
		return DB::select( DB::raw("select *,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE cst_id=cst_customer.cst_id AND bil_type in ('Daily','Weekly','Monthly','Quarterly','Semiannually','Yearly','One time') AND bil_status='Active') AS totalamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE cst_id=cst_customer.cst_id AND  bil_type in ('Payment') AND bil_status='Active') AS paidamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE cst_id=cst_customer.cst_id AND  bil_type in ('Advance') AND bil_status='Active') AS advamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE cst_id=cst_customer.cst_id AND  bil_type in ('PREPAY') AND bil_status='Active') AS prepayamt from `cst_customer` left join `grd_grade` on `grd_grade`.`grd_id` = `cst_customer`.`grd_id`  where cst_customer.cst_id='$cstid'"));
	}
	//Check Customer Balance ends here
}