<?php	
	namespace App\Http\Model;
	use Illuminate\Database\Eloquent\Model;
	use DB;
	use Auth;
	class WBReceiptModel extends Model
	{
		public function ReceiptPermission($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 searchquery($query)
        {
			return DB::table('cst_customer')
			->select(DB::raw('cst_customer.cst_id as id,CONCAT(`cst_name`,"(",`cst_code`,")-",`cst_contact1`) as name'))
			//->where('cst_type','=','WBCustomer')
			//->Where('cst_status','<>','Deleted')
			->whereRaw("cst_type='WBCustomer' AND cst_status<>'Deleted' AND (cst_name like '%".$query."%' or cst_contact1 like '%".$query."%' or cst_code like '%".$query."%')")
			->get();
		}
		public function modSearchcstrecord($cstid)
        {
			return $SalesView=DB::table('cst_customer')
			->select('cst_id','cst_name','cst_code','area_area.usr_id','cst_customer.tarf_id','tarf_tarifftype.tarf_tarifftype')
			->leftjoin('area_area','area_area.area_id','=','cst_customer.area_id')
			->leftjoin('tarf_tarifftype','tarf_tarifftype.tarf_id','=','cst_customer.tarf_id')
			->where('cst_id',$cstid)
			->first();
		}
		public function modSearchreceiptreceipt($cstid)
        {
			$lastID=DB::table('wtrb_waterbill')
			->where('cst_id',$cstid)
			->where('wtrb_rpttype','Receipt')
			->where('wtrb_status','Active')
			->max('wtrb_id');
			return $SalesView=DB::table('wtrb_waterbill')
			->select('wtrb_billdate','wtrb_currreading','wtrb_mtrstatus','wtrb_mtrreason')
			->where('wtrb_id',$lastID)
			->first();	
		}
		public function modnextreceiptno($usr_id,$rpttype)
        {
			$lastID=DB::table('wtrb_waterbill')
			//->where('wtrb_status','Active')
			->where('wtrb_rpttype',$rpttype)
			->where('usr_id',$usr_id)
			->max('wtrb_id');
						
			$nextreceiptno=0;
			
			$nextreceiptno=DB::table('wtrb_waterbill')
			->select('wtrb_billno')
			->where('wtrb_id',$lastID)
			->first();
			
			$nextreceiptno= $nextreceiptno ;
			return  $nextreceiptno;
		}
		public function modbalamt($cst_id)
        {			
			$totReceiptamt = DB::table('wtrb_waterbill')
			->where('wtrb_status','Active')
			->where('cst_id',$cst_id)
			->sum('wtrb_currpayable');			
			$totPaymentamt = DB::table('wtrb_waterbill')
			->where('wtrb_status','Active')
			->where('cst_id',$cst_id)
			->sum('wtrb_collamt');
			$totbalamt = $totReceiptamt - $totPaymentamt;
			return  $totbalamt;
		}
		public function modtariffchargelist($tarifftypeid)
        {
			return DB::table('tarc_tarffcharge')
			->select('tarc_id','tarc_unitfrom','tarc_unitto','tarc_tariffrange','tarc_tariffcharge')
			->where('tarf_id',$tarifftypeid)
			->orderBy('tarc_unitfrom','ASC')
			->get();
		}
		public function ViewAllPendingReceipt()
        {
			return $WBAllReport=DB::table('wtrb_waterbill')
			->select('wtrb_billno','cst_customer.cst_code','cst_customer.cst_name','wtrb_waterbill.wtrb_prevreading','wtrb_waterbill.wtrb_currreading','wtrb_waterbill.wtrb_prevbal','wtrb_waterbill.wtrb_currpayable','wtrb_waterbill.wtrb_billdate','wtrb_waterbill.wtrb_id')
			->leftjoin('cst_customer','cst_customer.cst_id','=','wtrb_waterbill.cst_id')
			->where('wtrb_status','Pending')
			->where('wtrb_rpttype','Receipt')
			->get();
		}
		
	}