DROP FUNCTION IF EXISTS public."udf_pharmacypatientbills_finalreport"(text, integer, integer, character varying, character varying, integer, integer, integer, date, date, text, integer, text, boolean);

CREATE OR REPLACE FUNCTION public."udf_pharmacypatientbills_finalreport"(
	"billNumber" text DEFAULT NULL::text,
	"accountId" integer DEFAULT NULL::integer,
	"patientId" integer DEFAULT NULL::integer,
	"patientMobile" character varying DEFAULT NULL::text,
	"uMRNo" character varying DEFAULT NULL::text,
	"payTypeId" integer DEFAULT NULL::integer,
	"providerId" integer DEFAULT NULL::integer,
	"createdBy" 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,
	"retailName" text DEFAULT NULL::text,
	"retailPharmacyId" integer DEFAULT NULL::integer,
	"locationId" text DEFAULT NULL::text,
	"pharmacyBillType" boolean DEFAULT NULL::boolean)
    RETURNS TABLE("PharmacySaleHeaderId" integer, "BillNumber" character varying, "PaidVia" character varying, "PaymentNumber" character varying, "SaleDate" timestamp without time zone, "PatientName" character varying, "PatientMobile" character varying, "UMRNo" character varying, "ProviderName" character varying, "CreatedByName" text, "RoleName" character varying, "TotalAmount" numeric, "SaleReturnHeaderId" integer, "OverallTaxes" numeric, "RetailName" text) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

BEGIN

return query

select PH."PharmacySaleHeaderId" ,PH."BillNumber",PH."PaidVia",PH."PaymentNumber",PH."SaleDate" "SaleDate",PH."PatientName",
PH."Mobile" ,Ph."UMRNo",PH."ProviderName",ph."CreatedByName",
ph."Role", PH."OverallNetAmount",PH."SaleReturnHeaderId" ,PH."OverallTaxes",PH."RetailName"
from 
(
select PH."PharmacySaleHeaderId" ,PH."BillNumber",PT."PayTypeName" as "PaidVia",PH."PaymentNumber",PH."CreatedDate" "SaleDate",
	PH."PatientName",PH."Mobile" ,Pa."UMRNo",PH."ProviderName",A."FullName" 
"CreatedByName",R."RoleName" "Role" ,
	PH."OverallNetAmount",true "PharmacyBillType",null "SaleReturnHeaderId",PH."OverallTaxes",
	RP."RetailName"
from "PharmacySaleHeader" PH
	join "Account" A on A."AccountId"=PH."CreatedBy"
	join "Role" R on R."RoleId"=A."RoleId"
	join "PharmacySaleDetail" PSD on PSD."PharmacySaleHeaderId"=PH."PharmacySaleHeaderId"
	join "PharmacyRetailStock" PRS on PRS."PharmacyRetailStockId"=PSD."PharmacyRetailStockId"
	join "RetailWareHouseLink" RWL on RWL."RetailWareHouseLinkId" = PRS."RetailWareHouseLinkId"
	join "RetailPharmacy" RP on RP."RetailPharmacyId"=RWL."RetailPharmacyId"
left join "Patient" Pa on Pa."PatientId"::text=PH."PatientId"::text
	left join "PayType" PT on PT."PayTypeId"=PH."PayTypeId"
		
where	case when "billNumber" is null then 1=1 else  PH."BillNumber" ilike '%' || "billNumber" ||'%' end and 
	case when "accountId" is null then 1=1 else PH."CreatedBy"="accountId" end and
	case when "patientId" is null then 1=1 else PH."PatientId"="patientId" end and
	case when "patientMobile" is null then 1=1 else  PH."Mobile" ilike '%' || "patientMobile" ||'%' end  and
	case when "uMRNo" is null then 1=1 else  Pa."UMRNo" ilike '%' || "uMRNo" ||'%' end  and
		case when "payTypeId" is null then 1=1 else  PT."PayTypeId" = "payTypeId" end  and
	case when "providerId" is null then 1=1 else  PH."ProviderId" = "providerId" end  and
		case when "createdBy" is null then 1=1 else  A."AccountId"="createdBy" end and
    case when "fromDate" is null then 1=1 else "fromDate"<= PH."SaleDate"  end and
	case when "toDate" is null then 1=1 else PH."SaleDate"<=  "toDate" end and
	case when "retailName" is null then 1=1 else  RP."RetailName" ilike '%' || "retailName" ||'%' end and
	case when "retailPharmacyId" is null then 1=1 else  RP."RetailPharmacyId" = "retailPharmacyId" end and
	case when "locationId" is null then 1=1 else PH."LocationId" = "locationId"::int end
group by PH."PharmacySaleHeaderId" ,PH."BillNumber",PT."PayTypeName", PH."PaymentNumber",PH."SaleDate", PH."OverallNetAmount",
	PH."PatientName",PH."ProviderName",Pa."UMRNo",A."FullName" 
,R."RoleName",RP."RetailName"
union 
	
select PH."PharmacySaleHeaderId" ,PH."BillNumber",PT."PayTypeName" as "PaidVia",PH."PaymentNumber",srh."ReturnDate" "SaleDate",PH."PatientName",
	Ph."Mobile" ,Pa."UMRNo",PH."ProviderName",A."FullName" 
"CreatedByName",R."RoleName" "Role", -srh."OverallNetAmount" "TotalAmount",false "PharmacyBillType"
,srh."SaleReturnHeaderId",srh."OverallTaxes",
	RP."RetailName"
	from "SaleReturnHeader" srh
Join "PharmacySaleHeader" ph on ph."PharmacySaleHeaderId"= srh."PharmacySaleHeaderId"
	join "Account" A on A."AccountId"=srh."CreatedBy"
		join "Role" R on R."RoleId"=A."RoleId"
	join "PharmacySaleDetail" PSD on PSD."PharmacySaleHeaderId"=PH."PharmacySaleHeaderId"
	join "PharmacyRetailStock" PRS on PRS."PharmacyRetailStockId"=PSD."PharmacyRetailStockId"
	join "RetailWareHouseLink" RWL on RWL."RetailWareHouseLinkId" = PRS."RetailWareHouseLinkId"
	join "RetailPharmacy" RP on RP."RetailPharmacyId"=RWL."RetailPharmacyId"
left join "Patient" Pa on Pa."PatientId"::text=PH."PatientId"::text
	left join "PayType" PT on PT."PayTypeId"=PH."PayTypeId"
		
where case when "billNumber" is null then 1=1 else  PH."BillNumber" ilike '%' || "billNumber" ||'%' end  and
	case when "accountId" is null then 1=1 else srh."CreatedBy"="accountId" end and
	case when "patientId" is null then 1=1 else PH."PatientId"="patientId" end and
	case when "patientMobile" is null then 1=1 else  PH."Mobile" ilike '%' || "patientMobile" ||'%' end  and
	case when "uMRNo" is null then 1=1 else  Pa."UMRNo" ilike '%' || "uMRNo" ||'%' end  and
			case when "payTypeId" is null then 1=1 else  PT."PayTypeId" = "payTypeId" end  and
	case when "providerId" is null then 1=1 else  PH."ProviderId" = "providerId"  end  and
		case when "createdBy" is null then 1=1 else  A."AccountId"="createdBy" end and
 case when "fromDate" is null then 1=1 else srh."ReturnDate" >= "fromDate" end and 
	case when "toDate" is null then 1=1 else srh."ReturnDate" <= "toDate" end and
	case when "retailName" is null then 1=1 else  RP."RetailName" ilike '%' || "retailName" ||'%' end and
		case when "retailPharmacyId" is null then 1=1 else  RP."RetailPharmacyId" = "retailPharmacyId" end and
	case when "locationId" is null then 1=1 else ph."LocationId" = "locationId"::int end
group by PH."PharmacySaleHeaderId" ,PH."BillNumber",PT."PayTypeName", PH."PaymentNumber",srh."ReturnDate", srh."OverallNetAmount",
	PH."PatientName",Pa."Mobile",Pa."UMRNo",PH."ProviderName",A."FullName" 
,R."RoleName",srh."SaleReturnHeaderId", RP."RetailName"
) Ph
where case when "pharmacyBillType" is null then 1=1 
when "pharmacyBillType" = true then "PharmacyBillType" = true
when "pharmacyBillType" = false then "PharmacyBillType" = false
end 
order by PH."SaleDate" desc, PH."PharmacySaleHeaderId";
END

$BODY$;

ALTER FUNCTION public."udf_pharmacypatientbills_finalreport"(text, integer, integer, character varying, character varying, integer, integer, integer, timestamp without time zone, timestamp without time zone, text, integer, text, boolean)
    OWNER TO postgres;