<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
class MatriRPTModel 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 LoadNextSerialNo()
	{
		$loanaccount=DB::table('lacc_loanaccount')->orderBy('lacc_id', 'desc')->first();
		$lacc_no= str_replace("NFCPL","",$loanaccount->lacc_no)+1;
		return "NFCPL".$lacc_no;
	}
	public function viewMatriCertificateMod()
	{
		return DB::select( DB::raw("select * from `mrg_marriagecertificate` WHERE mrg_status != 'Deleted'"));
	}
	public function viewSingleMatriCertMod($id)
	{
		return DB::table('mrg_marriagecertificate')->where('mrg_id',$id)->first();
	}
	public function viewloanapplicantion($id)
	{
		return DB::table('lacc_loanaccount')
		->leftjoin('br_branch','br_branch.br_id','=','lacc_loanaccount.br_id')
		->where('lacc_status','Pending')
		->where('lacc_id',$id)
		->get();
	}	
	//Import data starts here
	public function importloandataFindMod($id)
	{
		$referencenumberFind=DB::table('tbl_lapp')->where('id',$id['lacc_oldrefno'])->first();		
		return $referencenumberFind;
	}
	//Import data ends here
	public function insertMatriApplicantion($id)
	{
			$user = auth()->user();
			$Username =  $user->usr_id;
			$dt = date("Y-m-d");	
			//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;
		if($id['mrg_id']==0)
		{
			$insertcustomer=DB::table('mrg_marriagecertificate')->insertGetId(['cst_id'=>$id['cst_id'],'mrg_no'=>$id['mrg_no'],'mrg_certificatedate'=>$id['mrg_certificatedate'],'mrg_name'=>$id['mrg_name'],'mrg_dob'=>$id['mrg_dob'],'mrg_father'=>$id['mrg_father'],'mrg_address'=>$id['mrg_address'],'mrg_location'=>$id['mrg_location'],'mrg_marriagedate'=>$id['mrg_marriagedate'],'mrg_marriagetime'=>$id['mrg_marriagetime'],'mrg_cost'=>$id['mrg_cost'],'mrg_status'=>'Active','mrg_membercode'=>$id['mrg_membercode']]);
			//Transaction record starts here..
			$transaction=DB::table('trn_transaction')->insertGetId(['cst_id'=>$id['cst_id'],'fy_id'=>$fyrid,'grd_id'=>0,'grfn_id'=>0,'ftyp_id'=>0,'trn_type'=>'Matrimonial','trn_billno'=>$id['mrg_no'],'link_id'=>$insertcustomer,'trn_amt'=>$id['mrg_cost'],'trn_date'=>$id['mrg_certificatedate'],'trn_note'=>'','trn_paytype'=>'Direct','usr_name'=>$Username,'trn_status'=>'Active']);	
			//Transaction record ends here..
			//bil_billrec starts here..
			$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>'Matrimonial','trn_id'=>$transaction,'cst_id'=>$id['cst_id'],'fy_id'=>$fyrid,'grd_id'=>0,'ftyp_id'=>0,'grfn_id'=>0,'bil_amt'=>$id['mrg_cost'],'bil_date'=>$id['mrg_marriagedate'],'bil_note'=>'','usr_name'=>$Username,'paid_trn_id'=>0,'bil_status'=>'Active']);
			//bil_billrec ends here..
			return $insertcustomer;
		}
		else
		{
			$updfamilyrec = DB::table('mrg_marriagecertificate')->where(['mrg_id'=>$id['mrg_id']])->update(['mrg_status'=>'Deleted']);
			$insertcustomer=DB::table('mrg_marriagecertificate')->insertGetId(['cst_id'=>$id['cst_id'],'mrg_no'=>$id['mrg_no'],'mrg_certificatedate'=>$id['mrg_certificatedate'],'mrg_name'=>$id['mrg_name'],'mrg_dob'=>$id['mrg_dob'],'mrg_father'=>$id['mrg_father'],'mrg_address'=>$id['mrg_address'],'mrg_location'=>$id['mrg_location'],'mrg_marriagedate'=>$id['mrg_marriagedate'],'mrg_marriagetime'=>$id['mrg_marriagetime'],'mrg_cost'=>$id['mrg_cost'],'mrg_status'=>'Active','mrg_membercode'=>$id['mrg_membercode']]);
			return $insertcustomer;
		}
	}
	public function LoadSingleCustMod($cst_id)
	{			
		return DB::table('cst_customer')->where('cst_id',$cst_id)->first();
	}
		public function LoadCustGrade($grdid)
		{			
			return DB::table('grd_grade')->where('grd_id',$grdid)->first();
		}
		
		public function referencenumberFindMod($id)
		{
			if($id['editId']==0)
			{
				$referencenumberFind=DB::table('lacc_loanaccount')->where('lacc_no',$id['lacc_oldrefno'])->first();
			}
			else
			{
				$referencenumberFind=DB::table('lacc_loanaccount')->where('lacc_id',$id['editId'])->first();
			}			
			return $referencenumberFind;
		}
		
		public function loantypeFindFindMod($id)
		{
			$referencenumberFind=DB::table('ltyp_loantypes')->where('ltyp_id',$id['ltyp_id'])->first();			
			return $referencenumberFind;
		}
		
		public function sureDeleteloanApp($id)
		{
			return DB::table('lacc_loanaccount')->where('lacc_id','=',$id['lacc_id'])
			->update(['lacc_status'=>'Cancel']);
		}
		
		public function viewloanprocessingfee($amt)
		{		
			return DB::select( DB::raw("select * from lpf_loanprocessingfee where $amt between lpf_famt and lpf_tamt  limit 1"));
		}
		
	public function createloanac($id)
	{
		$updateLoanApplicantion=DB::table('lacc_loanaccount')->where('lacc_id','=',$id['lacc_id'])->update(['lacc_loanamt'=>$id['lacc_loanamt'],'lacc_tenor'=>$id['lacc_tenor'],'lacc_intrate'=>$id['lacc_intrate'],'lacc_opendt'=>$id['lacc_opendt'],'lpf_id'=>$id['lpf_id'],'lpf_amttype'=>$id['lpf_amttype'],'lpf_amt'=>$id['lpf_amt'],'lacc_lpfprocessingfee'=>$id['lacc_lpfprocessingfee'],'lacc_status'=>'Active','dpmt_charge'=>$id['dpmt_charge'],'lacc_remarks'=>$id['lacc_remarks']]);			
		$arrlins_no=$id['arrlins_no'];
		$arrlins_date=$id['arrlins_date'];	 
		$arrlins_amt=$id['arrlins_amt'];	 
		$arrlins_iperc=$id['arrlins_iperc'];	 
		$arrlins_iamt=$id['arrlins_iamt'];
		$arrlins_chqno=$id['arrlins_chqno'];
		$arrlins_note=$id['arrlins_note'];
		for($i=0;$i<count($arrlins_no);$i++)
		{
			$lins_loaninstallmentno=DB::table('lins_loaninstallment')->insertGetId(['lacc_id'=>$id['lacc_id'],'lins_no'=>$arrlins_no[$i],'lins_date'=>$arrlins_date[$i],'lins_amt'=>$arrlins_amt[$i],'lins_iperc'=>$arrlins_iperc[$i],'lins_iamt'=>$arrlins_iamt[$i],'lins_chqno'=>$arrlins_chqno[$i],'lins_note'=>$arrlins_note[$i],'lins_status'=>'Pending']);
			$installment_amt=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$id['lacc_id'],'lins_id'=>$lins_loaninstallmentno,'ltr_transdt'=>$arrlins_date[$i],'ltr_transtype'=>'Principle','ltr_billno'=>$arrlins_no[$i],'ltr_amt'=>$arrlins_amt[$i],'ltr_status'=>'Active']);
			$interest_amt=DB::table('ltr_loantransaction')->insertGetId(['lacc_id'=>$id['lacc_id'],'lins_id'=>$lins_loaninstallmentno,'ltr_transdt'=>$arrlins_date[$i],'ltr_transtype'=>'Interest','ltr_billno'=>$arrlins_no[$i],'ltr_amt'=>$arrlins_iamt[$i],'ltr_status'=>'Active']);
		}
		$st=1;
		return $data=['status' => 'Success','bil_id' => $st];
	}			
	public function makebillingpaymentMod($id)
	{
		$user = auth()->user();
		$Username =  $user->usr_id;
		$dt = date("Y-m-d");		
		//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;
		//Maximum Bill Number
		$dbill=DB::table('trn_transaction')->orderBy('trn_billno', 'desc')->first();
		$billno=$dbill->trn_billno + 1;
		//Transaction record starts here..
		$transaction=DB::table('trn_transaction')->insertGetId(['cst_id'=>$id['pay_cust_id'],'fy_id'=>$fyrid,'grd_id'=>0,'grfn_id'=>0,'ftyp_id'=>0,'trn_type'=>'Payment','trn_billno'=>$billno,'trn_amt'=>$id['amt_payable'],'trn_date'=>$dt,'trn_note'=>'','trn_paytype'=>'Direct','usr_name'=>$Username,'trn_status'=>'Active']);	
		//Transaction record ends here..
		//###Bill Record Insertion starts here..		
		$arrtrn_id = $id['arrtrn_id'];
		$arrslno = $id['arrslno'];
		$arrftype = $id['arrftype'];
		$arrpaycycle = $id['arrpaycycle'];
		$arrtotbalamt = $id['arrtotbalamt'];
		$arrpaidamt = $id['arrpaidamt'];
		$arrbalamt = $id['arrbalamt'];
		$arrfy_id = $id['arrfy_id'];
		$arrgrd_id = $id['arrgrd_id'];
		$arrgrfn_id = $id['arrgrfn_id'];
		$arrftyp_id = $id['arrftyp_id'];
		for($i=0;$i<count($arrtrn_id);$i++)
		{
			$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>'Payment','trn_id'=>$transaction,'cst_id'=>$id['pay_cust_id'],'fy_id'=>$fyrid,'grd_id'=>$arrgrd_id[$i],'ftyp_id'=>$arrftyp_id[$i],'grfn_id'=>$arrgrfn_id[$i],'bil_amt'=>$arrpaidamt[$i],'bil_date'=>$dt,'bil_note'=>'','usr_name'=>$Username,'paid_trn_id'=>$arrtrn_id[$i],'bil_status'=>'Active']);
			$bilr_bilreceipt=DB::table('bilr_bilreceipt')->insertGetId(['trn_id'=>$transaction,'bilr_slno'=>$arrslno[$i],'bilr_ftype'=>$arrftype[$i],'bilr_paycycle'=>$arrpaycycle[$i],'bilr_totbalamt'=>$arrtotbalamt[$i],'bilr_paidamt'=>$arrpaidamt[$i],'bilr_balamt'=>$arrbalamt[$i],'bilr_status'=>'Active']);	
		}
		//###Bill Record Insertion ends here..
		//###Advance Amount starts ###//
		$adv_amt = $id['adv_amt'];
		$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>'Advance','trn_id'=>$transaction,'cst_id'=>$id['pay_cust_id'],'fy_id'=>$fyrid,'grd_id'=>0,'ftyp_id'=>0,'grfn_id'=>0,'bil_amt'=>$adv_amt,'bil_date'=>$dt,'bil_note'=>'','usr_name'=>$Username,'bil_status'=>'Active']);
		$bilr_bilreceipt=DB::table('bilr_bilreceipt')->insertGetId(['trn_id'=>$transaction,'bilr_ftype'=>'Advance','bilr_paidamt'=>$adv_amt,'bilr_status'=>'Active']);
		//###Advance Amount ends ###//
		
		return $data=['status' => 'Success','transaction_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
	//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
	//###MOVE Advance BALANCE to clear dues starts here
	public function moveAdvBalMod($rex)
	{
		$cstid= $rex[0]->cst_id;
		$advamt = $rex[0]->advamt;
		$prepayamt = $rex[0]->prepayamt;
		//$advamt = $advamt - $prepayamt;
		$paidamt = 0;		
		$user = auth()->user();
		$Username =  $user->usr_id;
		$dt = date("Y-m-d");		
		//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;		
		//Maximum Bill Number
		$dbill=DB::table('trn_transaction')->orderBy('trn_billno', 'desc')->first();
		$billno=0;
		//Transaction record starts here..
		$transactionids=DB::table('trn_transaction')->insertGetId(['cst_id'=>$cstid,'fy_id'=>$fyrid,'grd_id'=>0,'grfn_id'=>0,'ftyp_id'=>0,'trn_type'=>'PREPAY','trn_billno'=>$billno,'trn_amt'=>0,'trn_date'=>$dt,'trn_note'=>'','trn_paytype'=>'From Advance','usr_name'=>$Username,'trn_status'=>'Active']);
		//Transaction record ends here..
		//#####################	BILLING TABLE STARTS HERE
		$trn_transaction = 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_billrec.bil_status='Active' ) AS totalamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE paid_trn_id=trn_transaction.trn_id AND bil_billrec.bil_status='Active' ) AS paidamt,(SELECT ifnull(sum(bil_amt),0) FROM `bil_billrec` WHERE paid_trn_id=trn_transaction.trn_id AND bil_type='PREPAY' AND bil_billrec.bil_status='Active' ) AS prepayamt 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.cst_id='$cstid' AND trn_transaction.trn_type in ('Daily','Weekly','Monthly','Quarterly','Semiannually','Yearly','One time') ORDER BY trn_transaction.trn_id"));
		$bal = 0;
		$paidamt=0;
		foreach($trn_transaction as $trn_val)
		{
			$bal=0;
			$paidamt=0;
			$bal = $trn_val->totalamt - ($trn_val->paidamt + $trn_val->prepayamt);
			if($bal > 0)
			{
				if($advamt > $bal)
				{
					$paidamt = $bal;
					$advamt = $advamt - $bal;
				}
				else
				{
					$paidamt = $advamt;
					$advamt =0;
				}
			}
			if($paidamt > 0)
			{
				$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>'PREPAY','trn_id'=>$transactionids,'cst_id'=>$cstid,'fy_id'=>$fyrid,'grd_id'=>0,'ftyp_id'=>0,'grfn_id'=>0,'bil_amt'=>$paidamt,'bil_date'=>$dt,'bil_note'=>'','paid_trn_id'=>$trn_val->trn_id,'usr_name'=>$Username,'bil_status'=>'Active']);
				$paidamt = -1 * $paidamt;
				$bil_billrec=DB::table('bil_billrec')->insertGetId(['bil_type'=>'Advance','trn_id'=>$transactionids,'cst_id'=>$cstid,'fy_id'=>$fyrid,'grd_id'=>0,'ftyp_id'=>0,'grfn_id'=>0,'bil_amt'=>$paidamt,'bil_date'=>$dt,'bil_note'=>'','paid_trn_id'=>$trn_val->trn_id,'usr_name'=>$Username,'bil_status'=>'Active']);
				$bilr_bilreceipt=DB::table('bilr_bilreceipt')->insertGetId(['trn_id'=>$transactionids,'bilr_slno'=>0,'bilr_ftype'=>$trn_val->ftype,'bilr_paycycle'=>$trn_val->paycycle,'bilr_totbalamt'=>0,'bilr_paidamt'=>$paidamt,'bilr_balamt'=>0,'bilr_status'=>'Active']);
			}		
		}
		//###################	BILLING TABLE ENDS HERE
		return 1;
	}
	//###MOVE Advance BALANCE to clear dues starts here
	//### DELETE PENDING RECORDS STARTS HERE ###
	public function DeletePendingMemberRecords()
	{
		return DB::table('cst_customer')->where('cst_status','=','Pending')->delete();
	}
	public function DeletePendingDocRecords()
	{
		return DB::table('doc_document')->where('doc_status','=','Pending')->delete();
	}
	//### DELETE PENDING RECORDS ENDS HERE ###
	public function insertFamilyMember($id)
	{
		$dt = date("Y-m-d");
		/***************************Residency*************************/
		$contact= array($id['cst_family_mobileno']);
		$contact1=serialize($contact);
		/***************************permenaennt*************************/
		if($id['family_cst_id']==0)
		{
			$insertcustomer=DB::table('cst_customer')->insertGetId(['grd_id'=>0,'cst_type'=>'Family','cst_code'=>0,'cst_name'=>$id['cst_family_name'],'cst_resaddress'=>$contact1,'cst_permaddress'=>'','cst_maritalstatus'=>$id['cst_family_maritalstatus'],'cst_qualification'=>$id['cst_family_qualification'],'cst_dob'=>$id['cst_family_dob'],'cst_gender'=>$id['cst_family_gender'],'cst_doj'=>$dt,'cst_bloodgroup'=>$id['cst_family_bloodgroup'],'cst_note'=>$id['cst_family_note'],'cst_status'=>$id['family_status'],'cst_relation_cst_id'=>$id['cst_id'],'cst_relationship'=>$id['cst_family_relationship']]);
			return $data=['status' => 'Success','entry_type' => 'Insert'];
		}
		else
		{
			$updatecustomer=DB::table('cst_customer')->where('cst_id','=',$id['family_cst_id'])->update(['grd_id'=>0,'cst_type'=>'Family','cst_code'=>0,'cst_name'=>$id['cst_family_name'],'cst_resaddress'=>$contact1,'cst_permaddress'=>'','cst_maritalstatus'=>$id['cst_family_maritalstatus'],'cst_occupation'=>'','cst_qualification'=>$id['cst_family_qualification'],'cst_dob'=>$id['cst_family_dob'],'cst_gender'=>$id['cst_family_gender'],'cst_doj'=>$dt,'cst_bloodgroup'=>$id['cst_family_bloodgroup'],'cst_note'=>$id['cst_family_note'],'cst_status'=>$id['family_status'],'cst_relation_cst_id'=>$id['cst_id'],'cst_relationship'=>$id['cst_family_relationship']]);
			return $data=['status' => 'Success','entry_type' => 'Update'];
		}			
	}
	//View Family Members starts here
	public function ViewFamilyMember($id)
	{
		return DB::table('cst_customer')->where('cst_relation_cst_id',$id['cst_id'])->where('cst_type','Family')->orderBy('cst_id')->get();
	}
	//View Family Members ends here
	public function insMemberDocsMod($id)
	{
		//document_cst_id: document_cst_id
		$dt = date("Y-m-d");
		if($id['document_cst_id']==0)
		{
			$insertcustomer=DB::table('doc_document')->insertGetId(['cst_id'=>$id['cst_id'],'doc_doctype'=>$id['doc_doctype'],'doc_docno'=>$id['doc_docno'],'doc_docfile'=>$id['docfilename'],'doc_detail'=>$id['doc_detail'],'doc_status'=>$id['doc_status']]);
			return $data=['status' => 'Success','entry_type' => 'Insert'];
		}
		else
		{
			/*
			$updatecustomer=DB::table('cst_customer')->where('cst_id','=',$id['family_cst_id'])->update(['grd_id'=>0,'cst_type'=>'Family','cst_code'=>0,'cst_name'=>$id['cst_family_name'],'cst_resaddress'=>$contact1,'cst_permaddress'=>'','cst_maritalstatus'=>$id['cst_family_maritalstatus'],'cst_occupation'=>'','cst_qualification'=>$id['cst_family_qualification'],'cst_dob'=>$id['cst_family_dob'],'cst_gender'=>$id['cst_family_gender'],'cst_doj'=>$dt,'cst_bloodgroup'=>$id['cst_family_bloodgroup'],'cst_note'=>$id['cst_family_note'],'cst_status'=>$id['family_status'],'cst_relation_cst_id'=>$id['cst_id'],'cst_relationship'=>$id['cst_family_relationship']]);
			return $data=['status' => 'Success','entry_type' => 'Update'];
			*/
		}			
	}
	
	//View Member Document starts here
	public function ViewMemberDocs($id)
	{
		return DB::table('doc_document')->where('cst_id',$id['cst_id'])->orderBy('doc_id')->get();
	}
	//View Member Document ends here
	
	public function TotPaidBalMod($cst_id)
	{
		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,trn_transaction.fy_id, trn_transaction.grd_id, trn_transaction.grfn_id, trn_transaction.ftyp_id,(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') AND trn_transaction.cst_id='$cst_id' ORDER BY trn_transaction.trn_id"));
	}
	
	public function TransactionReport($cst_id)
	{
		return DB::select( DB::raw("SELECT 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 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 WHERE trn_transaction.cst_id='$cst_id' AND trn_transaction.fy_id='1' AND trn_transaction.trn_status='Active' ORDER BY trn_transaction.trn_date, trn_transaction.trn_id asc"));
	}
	public function LoadCstRecMod($fund_cust_id)
	{
		return DB::table('cst_customer')->where('cst_code',$fund_cust_id)->where('cst_type','Member')->first();
	}
	public function MatriTransReport($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 cst_customer.cst_code, cst_customer.cst_name, trn_transaction.trn_id, trn_transaction.trn_billno, trn_transaction.trn_type, trn_transaction.trn_date, mrg_marriagecertificate.mrg_membercode, mrg_marriagecertificate.mrg_name,mrg_marriagecertificate.mrg_marriagedate, mrg_marriagecertificate.mrg_location, (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 LEFT JOIN mrg_marriagecertificate ON mrg_marriagecertificate.mrg_id=trn_transaction.link_id WHERE trn_transaction.fy_id='$fyrid' AND trn_transaction.trn_status='Active' ";
		if($id['cst_id'] != 0)
		{
			$sql = $sql . " AND trn_transaction.cst_id='" . $id['cst_id'] . "'";
		}
		if($id['mrg_no'] != 0)
		{
			$sql = $sql . " AND trn_transaction.trn_billno='" . $id['mrg_no'] . "'";
		}
		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 . " AND trn_transaction.trn_type='Matrimonial'";
		$sql = $sql . " ORDER BY trn_transaction.trn_date, trn_transaction.trn_id asc";
		return DB::select( DB::raw($sql));
	}
}