-- FUNCTION: public.udf_Employee_Revenue_Location(integer, integer, integer, timestamp without time zone, timestamp without time zone)

-- DROP FUNCTION IF EXISTS public."udf_Employee_Revenue_Location"(integer, integer, integer, timestamp without time zone, timestamp without time zone);

CREATE OR REPLACE FUNCTION public."udf_Employee_Revenue_Location"(
	accountid integer DEFAULT NULL::integer,
	locationid integer DEFAULT NULL::integer,
	roleid integer DEFAULT NULL::integer,
	"fromDate" timestamp without time zone DEFAULT (now())::timestamp without time zone,
	"toDate" timestamp without time zone DEFAULT (now())::timestamp without time zone)
    RETURNS TABLE("AccountId" integer, "EmployeeName" text, "RoleName" character varying, "AppointmentCashTotal" numeric, "AppointmentCardTotal" numeric, "AppointmentAmount" numeric, "AdmissionCashTotal" numeric, "AdmissionCardTotal" numeric, "AdmissionAmount" numeric, "LabCash" numeric, "LabCard" numeric, "LabAmount" numeric, "PharmacySaleCash" numeric, "PharmacySaleCard" numeric, "PharmacyAmount" numeric, "TotalCash" numeric, "TotalCard" numeric, "Total" numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
begin
 return query 

with accountdata as (
select a."AccountId",a."FullName" "EmployeeName",rol."RoleName"
from "Account" a
JOIN "LocationAccountMap" LAM on LAM."AccountId"=a."AccountId"  
join "Role" rol on rol."RoleId" = a."RoleId" and rol."RoleName"<>'Patient'
where case when accountid is null then 1=1 else a."AccountId"=accountid end   
and	case when roleid is null then 1=1 else a."RoleId"=roleid end 	
AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE LAM."LocationId"=locationId END
)

,actappointmentamount as (
	
	select a."AccountId",sum(A."CashTotal") "AppointmentCashTotal",sum(A."CardTotal") "AppointmentCardTotal",sum(A."CashTotal")+ sum(A."CardTotal") appointmentamount from (
   select  a."CreatedBy"  "AccountId",   
   case when A."PayTypeId"=1 then coalesce(A."Cost",0) else 0 end  "CashTotal" ,
   case when A."PayTypeId"=2 then coalesce(A."Cost",0) else 0 end "CardTotal"
		
		from "Receipt" A
    join "Appointment" ap on  ap."AppointmentId" =A."AppointmentId"  
	join "Provider" pr on pr."ProviderId"= ap."ProviderId"
    and ap."Active" =true	
   where case when accountid is null then 1=1 else a."CreatedBy"=accountid end  and A."ReceiptTypeId"=1 	
	and A."Active"<>false and A."AppointmentId" is not null
	AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ap."LocationId"=locationId END 
	and coalesce(A."IsRefunded",false) <>true and ap."Status" <> 'C' and	
	case when "fromDate" is null then 1=1 
	else (A."CreatedDate" >= "fromDate" and A."CreatedDate" <= "toDate")  end	)a 
	group by  a."AccountId"	 
)

,refundappointmentamount as (
	select a."AccountId" ,sum(A."CashTotal") "AppointmentRefCashTotal",sum(A."CardTotal") "AppointmentRefCardTotal",sum(A."CashTotal")+ sum(A."CardTotal") refappointmentamount  from (
    select  a."CreatedBy"  "AccountId",
		   case when A."PayTypeId"=1 then coalesce(A."Cost",0) else 0 end  "CashTotal" ,
          case when A."PayTypeId"=2 then coalesce(A."Cost",0) else 0 end "CardTotal" 
		 from "Receipt" A
    join "Appointment" ap on ap."AppointmentId" =A."AppointmentId"  
	join "Provider" pr on pr."ProviderId"= ap."ProviderId"
   and ap."Status" <> 'C'   and ap."Active" =true
	
   where case when accountid is null then 1=1 else a."CreatedBy"=accountid end   
	and A."Active"<>false and A."AppointmentId" is not null 
	AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ap."LocationId"=locationId END 
	and coalesce(A."IsRefunded",false) = true and 
	case when "fromDate" is null then 1=1 
	else (A."CreatedDate" >= "fromDate" and A."CreatedDate" <= "toDate")  end	) a
	group by  a."AccountId"	
	
)

,appointmentamount as (
select  A."AccountId",
	coalesce(A."AppointmentCashTotal",0) - coalesce(B."AppointmentRefCashTotal",0) as "AppointmentCashTotal" ,
	coalesce(A."AppointmentCardTotal",0) - coalesce(B."AppointmentRefCardTotal",0) as "AppointmentCardTotal" ,	
	coalesce(A.appointmentamount,0) - coalesce(B.refappointmentamount,0) as "AppointmentAmount" from actappointmentamount A
	left join refundappointmentamount B on A."AccountId"=B."AccountId"
	
	
)
,actadmissionamount as (
	
	select a."AccountId" , sum(A."CashTotal") "AdmissionCashTotal",sum(A."CardTotal") "AdmissionCardTotal",sum(A."CashTotal")+ sum(A."CardTotal") "AdmissionAmount" from (
select adr."CreatedBy" "AccountId" ,
   case when adr."PayTypeId"=1 then coalesce(adr."Cost",0) else 0 end  "CashTotal" ,
   case when adr."PayTypeId"=2 then coalesce(adr."Cost",0) else 0 end "CardTotal"									  
												  
from  "Receipt" adr 	 
	 join "Admission" ad on  adr."AdmissionId" = ad."AdmissionId" and ad."Active" <> false
	join "Provider" pr on pr."ProviderId"= ad."ProviderId"
	where case when accountid is null then 1=1 else adr."CreatedBy"=accountid end 	
	and adr."ReceiptTypeId"=1  and adr."Active"<>false  and adr."AdmissionId" is not null
		AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ad."LocationId"=locationId END 
	and case when "fromDate" is null then 1=1 
	else (adr."CreatedDate" >= "fromDate" and adr."CreatedDate" <= "toDate")  end	)a
	group by  a."AccountId"	
)

,refadmissionamount as (
	select a."AccountId" , sum(A."CashTotal") "AdmissionRefCashTotal",sum(A."CardTotal") "AdmissionRefCardTotal",sum(A."CashTotal")+ sum(A."CardTotal") "AdmissionRefAmount" from (
select adr."CreatedBy" "AccountId"  ,  
		case when adr."PayTypeId"=1 then coalesce(adr."Cost",0) else 0 end  "CashTotal" ,
        case when adr."PayTypeId"=2 then coalesce(adr."Cost",0) else 0 end "CardTotal" 
from  "Receipt" adr 	 
	 join "Admission" ad on  adr."AdmissionId" = ad."AdmissionId" and ad."Active" <> false
	join "Provider" pr on pr."ProviderId"= ad."ProviderId"
	where case when accountid is null then 1=1 else adr."CreatedBy"=accountid end 	
	 and adr."IsRefunded" = true   and adr."Active"<>false  and adr."AdmissionId" is not null
		AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ad."LocationId"=locationId END 
	and case when "fromDate" is null then 1=1 
	else (adr."CreatedDate" >= "fromDate" and adr."CreatedDate" <= "toDate")  end	)a
	group by  a."AccountId"	
)
,admissionamount as (
select A."AccountId" ,
	coalesce(A."AdmissionCashTotal",0) - coalesce(B."AdmissionRefCashTotal",0) "AdmissionCashTotal",
	coalesce(A."AdmissionCardTotal",0) - coalesce(B."AdmissionRefCardTotal",0) "AdmissionCardTotal",
	coalesce(A."AdmissionAmount",0) - coalesce(B."AdmissionRefAmount",0) "AdmissionAmount"
   from actadmissionamount A
	left join refadmissionamount B on A."AccountId"=B."AccountId"	 

)

,LabAmount as ( 
select a."AccountId",sum(a."Cash") "LabCash",sum(a."Card") "LabCard" ,sum(a."Cash")+sum(a."Card")"LabAmount"  from(
select 	a."AccountId",							  
	case when "PaidVia"='Cash' then  coalesce(ar."NetAmount",0) else 0 end "Cash"
	,case when "PaidVia"<>'Cash' then  coalesce(ar."NetAmount",0) else 0 end "Card"
from "Account" a
join "LabBookingHeader" ar on ar."CreatedBy" = a."AccountId" and ar."Active" <> false
where case when accountid is null then 1=1 else a."AccountId"=accountid end 
	AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ar."LocationId"=locationId END 
	and case when "fromDate" is null then 1=1 
	else (ar."CreatedDate" >= "fromDate" and ar."CreatedDate" <= "toDate")  end)a
group by a."AccountId" )

,PharmaSaleAmount as (
	
select a."AccountId", sum(a."Cash") "PharmaSaleCash",sum(a."Card") "PharmaSaleCard" , sum(a."Cash")+ sum(a."Card")
	"PharmaSaleAmount" from (
select a."AccountId", 
	 case when "PaidVia"='Cash' then  coalesce(ar."OverallNetAmount",0) else 0 end "Cash"
	,case when "PaidVia"<>'Cash' then  coalesce(ar."OverallNetAmount",0) else 0 end "Card"
		
from "Account" a
join "PharmacySaleHeader" ar on ar."CreatedBy" = a."AccountId"
where case when accountid is null then 1=1 else a."AccountId"=accountid end
		AND CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE ar."LocationId"=locationId END 
		and case when "fromDate" is null then 1=1 
else (ar."SaleDate" >= "fromDate" and ar."SaleDate" <= "toDate")  end	)a
group by a."AccountId"
)
,PharmaReturnAmount as (
select a."AccountId", sum(a."Cash") "PharmaReturnCash",sum(a."Card") "PharmaReturnCard" ,sum(a."Cash")+sum(a."Card")   "PharmaReturnAmount" from(
select a."AccountId", case when srh."PaidVia"='Cash' then  coalesce(ar."OverallNetAmount",0) else 0 end "Cash"
	,case when srh."PaidVia"='Card' then  coalesce(ar."OverallNetAmount",0) else 0 end "Card"
								   
							   
from "Account" a
JOIN "LocationAccountMap" LAM on LAM."AccountId"=a."AccountId"
left join "SaleReturnHeader" ar on ar."CreatedBy" = a."AccountId"
left join "PharmacySaleHeader" srh on srh."PharmacySaleHeaderId" = ar."PharmacySaleHeaderId"
where case when accountid is null then 1=1 else ar."CreatedBy"=accountid end 
	AND   CASE WHEN (locationId IS NULL OR locationId=0) THEN 1=1 ELSE LAM."LocationId"=locationId END
	and case when "fromDate" is null then 1=1 
else (ar."ReturnDate" >= "fromDate" and ar."ReturnDate" <= "toDate")  end	)a
group by a."AccountId"
)

,PharmaAmount as (
select a."AccountId", 
	
	 coalesce(a."PharmaSaleCash",0) - coalesce(b. "PharmaReturnCash",0) as  "PharmaSaleCash" ,
	 coalesce(a."PharmaSaleCard",0) - coalesce(b. "PharmaReturnCard",0) as  "PharmaSaleCard" ,
	coalesce(a."PharmaSaleAmount",0) - coalesce(b. "PharmaReturnAmount",0) as  "PharmaAmount" 
from PharmaSaleAmount a
	left join PharmaReturnAmount b on a."AccountId"=b."AccountId"

)
 select distinct  a."AccountId",a."EmployeeName",a."RoleName",
						  ap."AppointmentCashTotal",ap."AppointmentCardTotal",ap."AppointmentAmount",
						  ad."AdmissionCashTotal",ad."AdmissionCardTotal",ad."AdmissionAmount",
						  lb."LabCash", lb."LabCard",lb."LabAmount",
sum(pa."PharmaSaleCash") over(partition by a."AccountId")  "PharmacySaleCash",
sum(pa."PharmaSaleCard") over(partition by a."AccountId") 	"PharmacySaleCard",
sum(pa."PharmaAmount") over(partition by a."AccountId") "PharmacyAmount",
coalesce(ap."AppointmentCashTotal",0)+	coalesce(ad."AdmissionCashTotal",0)+coalesce(lb."LabCash",0)+coalesce(sum(pa."PharmaSaleCash") over(partition by a."AccountId") ,0) "TotalCash",
coalesce(ap."AppointmentCardTotal",0)+	coalesce(ad."AdmissionCardTotal",0)+coalesce(lb."LabCard",0)+coalesce(sum(pa."PharmaSaleCard") over(partition by a."AccountId"),0)  "TotalCard",	
coalesce(ap."AppointmentAmount",0)+coalesce(ad."AdmissionAmount",0)+coalesce(lb."LabAmount",0)
+coalesce(sum(pa."PharmaAmount") over(partition by a."AccountId"),0) "Total"

from accountdata a

left join appointmentamount ap on ap."AccountId"=a."AccountId"
left join admissionamount ad on ad."AccountId"=a."AccountId"
left join LabAmount lb on lb."AccountId"=a."AccountId"
left join PharmaAmount pa on pa."AccountId"=a."AccountId"
;
	end
$BODY$;

ALTER FUNCTION public."udf_Employee_Revenue_Location"(integer, integer, integer, timestamp without time zone, timestamp without time zone)
    OWNER TO postgres;
