-- FUNCTION: public.udf_PharmacyBills_Report(text, integer, character varying, character varying, integer, integer, integer, timestamp without time zone, timestamp without time zone, text, integer, text)

-- DROP FUNCTION IF EXISTS public."udf_PharmacyBills_Report"(text, integer, character varying, character varying, integer, integer, integer, timestamp without time zone, timestamp without time zone, text, integer, text);

CREATE OR REPLACE FUNCTION public."udf_PharmacyBills_Report"(
	"billNumber" text DEFAULT NULL::text,
	"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)
    RETURNS TABLE("PharmacySaleHeaderId" integer, "BillNumber" character varying, "SaleDate" timestamp without time zone, "PatientName" character varying, "PatientMobile" character varying, "UMRNo" character varying, "PaidVia" character varying, "PaymentNumber" character varying, "CreatedByName" text, "RoleName" character varying, "ProviderName" character varying, "TotalAmount" numeric, "RetailName" text, "OverallTaxes" numeric, "LocationName" character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN

return query
select PH."PharmacySaleHeaderId" ,PH."BillNumber",PH."CreatedDate" "SaleDate",PH."PatientName",PH."Mobile" 
,Pa."UMRNo",PT."PayTypeName" as "PaidVia",PH."PaymentNumber",A."FullName" 
"CreatedByName",R."RoleName" "Role" ,
PH."ProviderName", PH."OverallNetAmount" ,RP."RetailName",PH."OverallTaxes",L."Name" as "LocationName"
from  "PharmacySaleHeader" PH
left join "Patient" Pa on Pa."PatientId"::text=PH."PatientId"::text
--join "Provider" pr on pr."ProviderId"::text=PH."ProviderId"::text
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"
	join "Location" L on L."LocationId" = PH."LocationId"
	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 "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 PH."SaleDate" :: date >= "fromDate" :: date end and 
 case when "toDate" is null then 1=1 else PH."SaleDate" :: date<= "toDate" :: date 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",PH."SaleDate", PH."OverallNetAmount",PH."PatientName",PH."Mobile",Pa."UMRNo",PT."PayTypeName", PH."PaymentNumber", A."FullName" 
,R."RoleName" ,PH."ProviderName",RP."RetailName",PH."OverallTaxes",L."Name"
order by PH."SaleDate" desc;
END
$BODY$;

ALTER FUNCTION public."udf_PharmacyBills_Report"(text, integer, character varying, character varying, integer, integer, integer, timestamp without time zone, timestamp without time zone, text, integer, text)
    OWNER TO postgres;
--select * from udf_PharmacyBills_Report(text, integer,text, character varying, integer, integer, integer, '2023-02-28':: date, '2023-03-01':: date, text, integer, text)