-- FUNCTION: public.udf_FetchDoctor_With_Availability_Specialization_Op(character varying, integer, integer, character varying)

-- DROP FUNCTION IF EXISTS public."udf_FetchDoctor_With_Availability_Specialization_Op"(character varying, integer, integer, character varying);

CREATE OR REPLACE FUNCTION public."udf_FetchDoctor_With_Availability_Specialization_Op"(
	filter character varying DEFAULT NULL::text,
	"locationId" integer DEFAULT NULL::integer,
	"consultationTypeId" integer DEFAULT NULL::integer,
	"appointmentDate" character varying DEFAULT NULL::text)
    RETURNS TABLE("ProviderAvailabilityId" integer, "FullName" character varying, "DepartmentName" character varying, "DepartmentId" integer, "ProviderId" integer, "SpecializationId" integer, "SpecializationName" character varying, "LocationId" integer, "ConsultationTypeId" integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN

return query

SELECT DISTINCT  on (pr2."ProviderId", s."SpecializationId") prl."ProviderAvailabilityId" ,pr2."FullName" 
, d."DepartmentName" , d."DepartmentId" 
,pr2."ProviderId"
, s. "SpecializationId", s."SpecializationName"
, CMT."LocationId", prl."ConsultationTypeId"
                           FROM "ProviderAvailability" prl
						   Join "DoctorSpecializationMap" LSM on LSM."ProviderId" = prl."ProviderId"  and LSM."SpecializationId" = 																				prl."SpecializationId" and LSM."ConsultationTypeId" = prl."ConsultationTypeId"
						   join "DoctorSpecializationChargeModuleDetails" DSCD on DSCD."ReferenceId" = LSM."DoctorSpecializationMapId"
						   join "DoctorSpecializationChargeModuleCategory" DSCC on DSCC."DoctorSpecializationChargeModuleCategoryId" = 																			DSCD."DoctorSpecializationChargeModuleCategoryId"
						   join "ChargeModuleTemplate" CMT on CMT."ChargeModuleTemplateId" = DSCC."ChargeModuleTemplateId"
                           JOIN "Provider" pr2 on pr2."ProviderId" = LSM."ProviderId" and pr2."Active" is true
						   join "Account" pa on pa."ReferenceId" = pr2."ProviderId" and pa."Active" is true and pa."RoleId" = 3
						   JOin "LocationAccountMap" LAM on LAM."AccountId" = pa."AccountId" 
						   join "Specialization" s on s."SpecializationId" = ANY (pr2."Specializations")
                           JOIN "Location" pral on pral."LocationId" = CMT."LocationId" AND pral."Active" IS TRUE
                           JOIN "Practice" pra on pra."PracticeId" = pral."PracticeId" AND pra."Active" IS TRUE
						   JOIN "Department" d on d."DepartmentId" = pr2."DepartmentId" 
						   where pr2."Active" is true and CMT."LocationId" = "locationId"  and s."Active" = true 
						   --and CMT."StartDate"::date <= "appointmentDate"::date and CMT."EndDate"::date >= "appointmentDate"::date 
						   and "IsInUse" is true
 
and case when "filter" is null then 1=1 else  TRIM(UPPER(pr2."FullName")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(s."SpecializationName")) ilike'%'|| "filter"||'%' end 
and case when "consultationTypeId" is null then 1=1 else  prl."ConsultationTypeId" = "consultationTypeId" end
and case when "appointmentDate" is null then CMT."StartDate"::date <= current_date::date and CMT."EndDate"::date >= current_date::date else CMT."StartDate"::date <= "appointmentDate"::date and CMT."EndDate"::date >= "appointmentDate"::date end
	
	--and	case when "locationId" is null then 1=1 else  LAM."LocationId"  = "LocationId" end
	--order by "FullName" asc
;
END
$BODY$;

ALTER FUNCTION public."udf_FetchDoctor_With_Availability_Specialization_Op"(character varying, integer, integer, character varying)
    OWNER TO postgres;
