-- FUNCTION: public.udf_uiReport_fetch_Appointments_Location(integer, text, integer[], integer[], integer[], text, text, text, text, text, date, date, integer, integer)

-- DROP FUNCTION IF EXISTS public."udf_uiReport_fetch_Appointments_Location"(integer, text, integer[], integer[], integer[], text, text, text, text, text, date, date, integer, integer);

CREATE OR REPLACE FUNCTION public."udf_uiReport_fetch_Appointments_Location"(
	locationid integer DEFAULT NULL::integer,
	"appointmentNo" text DEFAULT NULL::text,
	"departmentId" integer[] DEFAULT NULL::integer[],
	"providerId" integer[] DEFAULT NULL::integer[],
	"patientId" integer[] DEFAULT NULL::integer[],
	"uMRNo" text DEFAULT NULL::text,
	"referredBy" text DEFAULT NULL::text,
	"referredByName" text DEFAULT NULL::text,
	mobile text DEFAULT NULL::text,
	"paymentType" text DEFAULT NULL::text,
	"fromDate" date DEFAULT NULL::date,
	"toDate" date DEFAULT NULL::date,
	"pageIndex" integer DEFAULT 0,
	"pageSize" integer DEFAULT 10)
    RETURNS TABLE("DepartmentId" text, "DepartmentName" character varying, "ProviderId" text, "ProviderName" character varying, "FollowUpForAppointmentId" integer, "AppointmentDate" date, "AppointmentNo" character varying, "PatientId" integer, "ReferredBy" character varying, "ReferredByName" character varying, "PatientName" character varying, "PatientAge" smallint, "UMRNo" character varying, "Mobile" character varying, "PatientGender" character, "AppointmentTime" time without time zone, "VisitType" character, "PaymentType" character varying, "TotalAppointments" bigint, "TotalAmount" bigint, "ReceiptCreatedByName" text, "ReceiptDate" timestamp without time zone, "ReceiptId" integer, "StreetAdress" character varying, "City" character varying, "State" character varying, "FatherOrHusband" text) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN

return query 

with cts as (select coalesce(A."DepartmentId"::text,'GrandTotal') "DepartmentId",
			 A."ProviderId"::text as "ProviderId", 
			 A."AppointmentDate" ,
			 A."FollowUpForAppointmentId",
			 A."AppointmentNo",A."PatientId" "PatientId",Pa."ReferredBy",Pa."ReferredByName",
			 Pa."FullName",Pa."UMRNo",Pa."Mobile",A."AppointmentTime",A."VisitType",
			 A."PaymentType",
			 A."AppointmentId",
	A."Status",COUNT(A."AppointmentNo") as "TotalAppointments" ,sum(A."Amount")::bigint as "TotalAmount" ,
			 Pa."FatherOrHusband"
			 from "Appointment" A
 left join "Patient" Pa on Pa."PatientId"::text=A."PatientId"::text
where A."Status"<>'C' and 
case when "departmentId" is null then 1=1 else  A."DepartmentId"  = any("departmentId") end and 
CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE A."LocationId"=locationId END and
case when "patientId" is null then 1=1 else  Pa."PatientId"  = any("patientId") end and 
case when "appointmentNo" is null then 1=1 when "appointmentNo" ='' then 1=1 else A."AppointmentNo" ilike'%'||"appointmentNo" ||'%' end  and
case when "providerId" is null then 1=1 else  A."ProviderId"  = any("providerId") end and 
case when "uMRNo" is null then 1=1 when "uMRNo" ='' then 1=1 else Pa."UMRNo" ilike'%'||"uMRNo" ||'%' end  and
case when "referredBy" is null then 1=1 when "referredBy" ='' then 1=1 else Pa."ReferredBy" ilike'%'||"referredBy" ||'%'  end and
case when "referredByName" is null then 1=1 when "referredByName" ='' then 1=1 else Pa."ReferredByName" ilike'%'||"referredByName" ||'%' end and 
case when "mobile" is null then 1=1 when "mobile" ='' then 1=1 else Pa."Mobile" ilike'%'||"mobile" ||'%' end  and
case when "paymentType" is null then 1=1 when "paymentType" ='' then 1=1 else A."PaymentType" ilike'%'||"paymentType"||'%' end  and
			 case when "fromDate" is null then 1=1 else "fromDate" <=A."AppointmentDate" and A."AppointmentDate" <="toDate" end 			 
		 
GROUP BY GROUPING SETS((A."DepartmentId",A."ProviderId",A."FollowUpForAppointmentId",A."PatientId",Pa."ReferredBy"
						,Pa."ReferredByName",Pa."FullName",Pa."UMRNo",Pa."Mobile",A."AppointmentDate",A."AppointmentTime",
						A."AppointmentNo",
						A."VisitType",
						A."PaymentType",
						A."AppointmentId",
						A."Status",Pa."FatherOrHusband"), (A."DepartmentId",A."ProviderId"), ())     
order by A."DepartmentId",A."ProviderId",A."AppointmentDate")

select A."DepartmentId",coalesce(D."DepartmentName",'GrandTotal') "DepartmentName",
case when A."AppointmentNo" is null then 'Total'  else A."ProviderId" end "ProviderId",
case when A."AppointmentNo" is null  then 'Total'  else  Pr."FullName"  end  as "ProviderName",
A."FollowUpForAppointmentId",A."AppointmentDate", 
A."AppointmentNo",A."PatientId",
case when A."AppointmentNo" is null  then null else Pa."ReferredBy"::varchar end as "ReferredBy",
case when A."AppointmentNo" is null  then null else Pa."ReferredByName"::varchar end as "ReferredByName",
case when A."AppointmentNo" is null  then null else Pa."FullName"::varchar end as "PatientName",
case when A."AppointmentNo" is null  then null else Pa."Age" end  "PatientAge",
case when A."AppointmentNo" is null  then null else Pa."UMRNo" end  "UMRNo",
case when A."AppointmentNo" is null  then null else Pa."Mobile" end  "Mobile",
case when A."AppointmentNo" is null  then null else Pa."Gender" end "PatientGender",
A."AppointmentTime",
A."VisitType",A."PaymentType",A."TotalAppointments",A."TotalAmount",
 CA."FullName"  "ReceiptCreatedByName", R."CreatedDate" "ReceiptDate",R."ReceiptId",
 case when A."AppointmentNo" is null  then null else Pa."StreetAddress" end "StreetAddress",
case when A."AppointmentNo" is null  then null else Pa."City" end "City",
case when A."AppointmentNo" is null  then null else Pa."State" end "State",
A."FatherOrHusband"
from cts A
left join "Department" D on A."DepartmentId"=D."DepartmentId"::text
left join "Provider" Pr on Pr."ProviderId"::text=A."ProviderId"
left join "Patient" Pa on Pa."PatientId"::text=A."PatientId"::text
left Join "Receipt" R on R."AppointmentId"=A."AppointmentId"
Left Join "Account" CA on CA."AccountId"=R."CreatedBy"

order by A."DepartmentId",A."ProviderId",A."AppointmentDate"
 ;

END
$BODY$;

ALTER FUNCTION public."udf_uiReport_fetch_Appointments_Location"(integer, text, integer[], integer[], integer[], text, text, text, text, text, date, date, integer, integer)
    OWNER TO postgres;
