DROP FUNCTION IF EXISTS public."udf_fetch_Beds_ListView"(text, integer, integer, boolean, text, date, date, date, text, boolean, integer, integer, integer, integer, integer, integer);


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- FUNCTION: public.udf_fetch_Beds_ListView(text, integer, integer, boolean, text, date, date, date, text, boolean, integer, integer, integer, integer, integer, integer)

-- DROP FUNCTION IF EXISTS public."udf_fetch_Beds_ListView"(text, integer, integer, boolean, text, date, date, date, text, boolean, integer, integer, integer, integer, integer, integer);

CREATE OR REPLACE FUNCTION public."udf_fetch_Beds_ListView"(
	"admissionNo" text DEFAULT NULL::text,
	"providerId" integer DEFAULT NULL::integer,
	"patientId" integer DEFAULT NULL::integer,
	"isDischarged" boolean DEFAULT NULL::boolean,
	"patientMobileNo" text DEFAULT NULL::text,
	"fromDate" date DEFAULT NULL::date,
	"toDate" date DEFAULT NULL::date,
	"dischargeDate" date DEFAULT NULL::date,
	"uMRNo" text DEFAULT NULL::text,
	active boolean DEFAULT NULL::boolean,
	locationid integer DEFAULT NULL::integer,
	floorid integer DEFAULT NULL::integer,
	wardid integer DEFAULT NULL::integer,
	bedstatusid integer DEFAULT NULL::integer,
	"pageIndex" integer DEFAULT 0,
	"pageSize" integer DEFAULT 10)
    RETURNS TABLE("AdmissionId" integer, "AdmissionNo" text, "PatientPriorityId" integer, "IsConvertedFromOPtoIp" boolean, "AdmissionDate" timestamp without time zone, "AdmissionTime" time without time zone, "PatientName" text, "UMRNo" character varying, "PatientAge" smallint, "PatientGender" character, "patientMobile" character varying, "ProviderAge" smallint, "ProviderGender" character, "ProviderName" text, "DepartmentName" text, "WardName" character varying, "RoomName" character varying, "BedNumber" character varying, "AttendantName" character varying, "AttendantRelationWithPatient" character varying, "AttendantContactNo" character varying, "IsDischarged" boolean, "DischargeDate" date, "DischargeTime" time without time zone, "DischargeStatus" character varying, "IsMaternity" boolean, "EncounterId" integer, "SurgeryName" character varying, "ProviderThumbnailUrl" text, "PatientThumbnailUrl" text, "BedId" integer, "RoomId" integer, "WardId" integer, "DepartmentId" integer, "PatientId" integer, "ProviderId" integer, "SurgeryTypeId" integer, "PatientType" character, "AdmissionNotes" text, "PaidAmount" numeric, "FinalAmount" numeric, "PaymentStatus" text, "IsFinalBill" boolean, "TotalItems" bigint, "Active" boolean, "ExpectedDischargeDate" timestamp without time zone, "DischargedBy" integer, "DischargedByRole" text, "DischargedByName" text, "BedStatusName" character varying, "BedStatusID" integer, "FloorId" integer, "FloorName" character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN

return query 

With TotalItems as (

select  count(distinct B."BedId")::bigint "TotalItems"
	from "Bed" B
	join "Room" R on B."RoomId"=R."RoomId"  
	join "Ward" W on w."WardId"=R."WardId"      
	left join "BedStatus" BS ON BS."BedStatusId" = B."BedStatusId"
	LEFT JOIN "Admission" A on B."BedId"=A."BedId" and A."Active"=true and A."IsDischarged"=false
	LEFT join "Patient" Pa on A."PatientId"=Pa."PatientId"
	LEFT join "Provider" Pr on A."ProviderId"=Pr."ProviderId"
	LEFT join "Department" D on D."DepartmentId"=A."DepartmentId"	
	left join "SurgeryType" st on st."SurgeryTypeId"=A."SurgeryTypeId"
	left join "Discharge" ds on ds."AdmissionId"=A."AdmissionId" and ds."Active"=true
	left join "DischargeStatus" dss on ds."DischargeStatusId"=dss."DischargeStatusId"	
	left join "Receipt" Re on A."AdmissionId" = Re."AdmissionId" and Re."ReceiptTypeId"=1 and Re."Active"=true
---left join "Receipt" Rf on Rf."ReceiptTypeId"=2 and A."AdmissionId"= Rf ."AdmissionId" and Rf."Active"=true  and  Rf."IsRefunded" =true
left join "FinalBill" FB on  A."AdmissionId"= FB."AdmissionId" and FB."Active"=true	  
    
)

select  A."AdmissionId",A."AdmissionNo",A."PatientPriorityId", A."IsConvertedFromOPtoIp",A."AdmissionDate",A."AdmissionTime",
	Pa."FullName" "PatientName",Pa."UMRNo",Pa."Age" "PatientAge",Pa."Gender" as "PatientGender",pa."Mobile" "patientMobile",Pr."Age" "ProviderAge",Pr."Gender" as "ProviderGender",
	Pr."FullName"::text "ProviderName",D."DepartmentName"::text "DepartmentName" , w."WardName", R."RoomName", B."BedNumber"
	,A."AttendantName" "AttendantName",A."AttendantRelationWithPatient" "AttendantRelationWithPatient",A."AttendantContactNo" "AttendantContactNumber",
	case when ds."DischargeId" is not null then true else false end as "IsDischarged",ds."DischargeDate",ds."DischargeTime",
	dss."DischargeStatus"
	
	,A."IsMaternity",A."EncounterId",st."SurgeryName",
	(CASE WHEN pr."ThumbnailUrl" IS NOT NULL THEN CONCAT(pr."Guid", '/', pr."ThumbnailUrl") ELSE NULL END) AS "ProviderThumbnailUrl",
(CASE WHEN pa."ThumbnailUrl" IS NOT NULL THEN CONCAT(pa."Guid", '/', pa."ThumbnailUrl") ELSE NULL END) AS "PatientThumbnailUrl"
	,B."BedId",B."RoomId",w."WardId",D."DepartmentId",A."PatientId", A."ProviderId", A."SurgeryTypeId", A."PatientType",A."AdmissionNotes"
,
sum(coalesce(Re."Cost",0))-coalesce((select sum(Rfd."Cost") from "Receipt" Rfd where Rfd."AdmissionId"=A."AdmissionId" and "ReceiptTypeId"=2 and  Rfd."Active"=true and  Rfd."IsRefunded" =true ),0) "PaidAmount"
,
FB."FinalAmount" ,
case when FB."FinalAmount"-(sum(coalesce(Re."Cost",0))-coalesce((select sum(Rfd."Cost") from "Receipt" Rfd where Rfd."AdmissionId"=A."AdmissionId" and "ReceiptTypeId"=2 and  Rfd."Active"=true and  Rfd."IsRefunded" =true ),0))=0 then 'Payment Cleared'
	 when FB."FinalAmount"-(sum(coalesce(Re."Cost",0))-coalesce((select sum(Rfd."Cost") from "Receipt" Rfd where Rfd."AdmissionId"=A."AdmissionId" and "ReceiptTypeId"=2 and  Rfd."Active"=true and  Rfd."IsRefunded" =true ),0))>0 then 'Payment Due'	
	 when FB."FinalAmount"-(sum(coalesce(Re."Cost",0))-coalesce((select sum(Rfd."Cost") from "Receipt" Rfd where Rfd."AdmissionId"=A."AdmissionId" and "ReceiptTypeId"=2 and  Rfd."Active"=true and  Rfd."IsRefunded" =true ),0))<0 then 'Pending Refund' end "PaymentStatus",
case when FB."FinalBillId" is null then false else true end "IsFinalBill",(select T."TotalItems" from TotalItems T) "TotalItems",
A."Active",
A."ExpectedDischargeDate",
A."DischargedBy" ,
case when RL."RoleId" =  '3' then DP."DepartmentName"::TEXT else RL."RoleName"::TEXT end as "DischargedByRole",
case when RL."RoleId" =  '3' then PRO."FullName" else AC."FullName" end as "DischargedByName",
BS."BedStatusName",BS."BedStatusId",F."FloorId",F."FloorName"
from "Bed" B
	join "Room" R on B."RoomId"=R."RoomId"  
	join "Ward" W on w."WardId"=R."WardId"
    JOIN "Floor" F on F."FloorId"=W."FloorId"
	left join "BedStatus" BS ON BS."BedStatusId" = B."BedStatusId"
	LEFT JOIN "Admission" A on B."BedId"=A."BedId" and A."Active"=true and A."IsDischarged"=false
	LEFT join "Patient" Pa on A."PatientId"=Pa."PatientId"
	LEFT join "Provider" Pr on A."ProviderId"=Pr."ProviderId"
	LEFT join "Department" D on D."DepartmentId"=A."DepartmentId"	
	left join "SurgeryType" st on st."SurgeryTypeId"=A."SurgeryTypeId"
	left join "Discharge" ds on ds."AdmissionId"=A."AdmissionId" and ds."Active"=true
	left join "DischargeStatus" dss on ds."DischargeStatusId"=dss."DischargeStatusId"	
	left join "Receipt" Re on A."AdmissionId" = Re."AdmissionId" and Re."ReceiptTypeId"=1 and Re."Active"=true
---left join "Receipt" Rf on Rf."ReceiptTypeId"=2 and A."AdmissionId"= Rf ."AdmissionId" and Rf."Active"=true  and  Rf."IsRefunded" =true
left join "FinalBill" FB on  A."AdmissionId"= FB."AdmissionId" and FB."Active"=true		
	left join "Account" AC on AC."AccountId"=A."DischargedBy" 
left join "Role" RL on RL."RoleId"= AC."RoleId"
left join "Provider" PRO on PRO."ProviderId"=AC."ReferenceId"
left join "Department" DP on DP."DepartmentId"=PRO."DepartmentId"				
	where f."LocationId" = locationid
	and case when coalesce(floorid,0) <> 0 then F."FloorId" = floorid else 1 = 1 end
	and case when coalesce(wardid,0) <> 0 then w."WardId" = wardid else 1 = 1 end
	and case when coalesce(bedstatusid,0) <> 0 then B."BedStatusId" = bedstatusid else 1 = 1 end
 group by  A."AdmissionId",A."AdmissionNo",A."PatientPriorityId",A."IsConvertedFromOPtoIp",A."AdmissionDate",A."AdmissionTime",
	Pa."FullName",Pa."UMRNo",Pa."Age" ,Pa."Gender" ,Pr."Age" ,Pr."Gender" ,
	Pr."FullName",D."DepartmentName" , w."WardName", R."RoomName", B."BedNumber"
	,A."AttendantName",A."AttendantRelationWithPatient",A."AttendantContactNo" , ds."DischargeId",F."FloorId",F."FloorName"
																														 
	,ds."DischargeDate",ds."DischargeTime",
	dss."DischargeStatus",pa."Mobile"
	
	,A."IsMaternity",A."EncounterId",st."SurgeryName",pr."ThumbnailUrl", pr."Guid",pr."ThumbnailUrl" ,pa."Guid", pa."ThumbnailUrl",
	B."BedId",B."RoomId",w."WardId",D."DepartmentId",A."PatientId", A."ProviderId", A."SurgeryTypeId", A."PatientType",A."AdmissionNotes"
		,FB."FinalAmount"	,FB."FinalBillId",
	RL."RoleId",DP."DepartmentName",PRO."FullName",AC."FullName",BS."BedStatusName",BS."BedStatusId"
	order by B."BedNumber" desc; 
 --limit "pageSize" offset ("pageSize"*"pageIndex");

END
$BODY$;

ALTER FUNCTION public."udf_fetch_Beds_ListView"(text, integer, integer, boolean, text, date, date, date, text, boolean, integer, integer, integer, integer, integer, integer)
    OWNER TO postgres;
