DROP FUNCTION IF EXISTS public."udf_uiReport_fetch_Appointments_Location"(integer, text, integer[], integer[], integer[], text, integer[], text, text, integer, date, date, integer[], 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,
	"patientReferredById" integer[] DEFAULT NULL::integer[],
	"referredByName" text DEFAULT NULL::text,
	mobile text DEFAULT NULL::text,
	"payTypeId" integer DEFAULT NULL::integer,
	"fromDate" date DEFAULT NULL::date,
	"toDate" date DEFAULT NULL::date,
	"appointmentTypeId" integer[] DEFAULT NULL::integer[],
	"pageIndex" integer DEFAULT 0,
	"pageSize" integer DEFAULT 10)
    RETURNS TABLE("DepartmentId" text, "DepartmentName" character varying, "ProviderId" text, "ProviderName" character varying, "AppointmentTypeName" character varying, "AppointmentDate" date, "AppointmentNo" character varying, "PatientId" integer, "Name" 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, "PaymentNumber" character varying, "TotalAppointments" bigint, "TotalAmount" bigint, "TotalAmountStr" text, "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."AppointmentTypeId",
			ATN."Name" "AppointmentTypeName",
			 A."AppointmentNo",A."PatientId" "PatientId",PRB."Name",Pa."ReferredByName",
			 Pa."FullName",Pa."UMRNo",Pa."Mobile",A."AppointmentTime",A."VisitType",
			 PT."PayTypeName" as "PaymentType",A."PaymentNumber",
			 A."AppointmentId",
	A."Status",COUNT(A."AppointmentNo") as "TotalAppointments" ,sum(A."Total")::text as "TotalAmountStr" ,
			 sum(A."Total")::bigint as "TotalAmount" ,
			 Pa."FatherOrHusband"
			 from "Appointment" A
 			left join "Patient" Pa on Pa."PatientId"::text=A."PatientId"::text
			 left join "PayType" PT on PT."PayTypeId"=A."PayTypeId"
			 left join "PatientReferredBy" PRB on PRB."PatientReferredById"::text=Pa."PatientReferredById"::text			
		left join "AppointmentType" ATN on A."AppointmentTypeId"=ATN."AppointmentTypeId"
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 "patientReferredById" is null then 1=1 else  Pa."PatientReferredById"  = any("patientReferredById") end and 
case when "appointmentTypeId" is null then 1=1 else  ATN."AppointmentTypeId"  = any("appointmentTypeId") 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 "payTypeId" is null then 1=1  else A."PayTypeId" ="payTypeId" 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."PatientId",PRB."Name",
						Pa."ReferredByName",Pa."FullName",Pa."UMRNo",Pa."Mobile",A."AppointmentDate",A."AppointmentTime",
						A."AppointmentNo",A."AppointmentTypeId",
						ATN."Name" ,
						A."VisitType",
						PT."PayTypeName",A."PaymentNumber",
						A."AppointmentId",
						A."Status",Pa."FatherOrHusband"), (A."DepartmentId",A."ProviderId"), ())     
order by A."AppointmentId")

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",
 ATN."Name" "AppointmentTypeName",
A."AppointmentDate",
A."AppointmentNo",A."PatientId",
case when A."AppointmentNo" is null  then null else PRB."Name"::varchar end as "Name",
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."PaymentNumber",A."TotalAppointments",A."TotalAmount",A."TotalAmountStr",
 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"
left join "PatientReferredBy" PRB on PRB."PatientReferredById"::text=Pa."PatientReferredById"::text
 left join  "AppointmentType" ATN on ATN."AppointmentTypeId"=A."AppointmentTypeId"
order by A."AppointmentId"
 ;

END
$BODY$;

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