with params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		cast($2 as bigint) as p_LpuBuilding_id,
		cast($3 as bigint) as p_MedService_id,
		cast($4 as bigint) as p_PayType_id,
		cast($5 as bigint) as p_UslugaComplex_id,
		cast($6 as bigint) as p_Analyzer_id,
		cast($7 as timestamp) as p_BegDate,
		cast($8 as timestamp) as p_EndDate
),

sub as(
	select
		dense_rank() over(order by coalesce(Analyzer_Name,'яяя')) as row_num,
		row_number() over(
			partition by an.Analyzer_Name
			order by 
				grouping(ucp.UslugaComplex_Code),
				ucp.UslugaComplex_Code,
				grouping(uc.UslugaComplex_Code) desc,
				uc.uslugacomplex_code) as ord,
		grouping(an.Analyzer_Name) as gr1,
		grouping(ucp.UslugaComplex_Code) as gr2,
		grouping(uc.UslugaComplex_Code) as gr3,
		an.Analyzer_Name as analyzer_name,
		case
			when grouping(uc.UslugaComplex_Code) = 0
				then uc.UslugaComplex_Code
			when grouping(uc.UslugaComplex_Code) = 1 and grouping(ucp.UslugaComplex_Code) = 0
				then ucp.UslugaComplex_Code
			when grouping(ucp.UslugaComplex_Code) = 1 and grouping(an.Analyzer_Name) = 0
				then 'Итого:'
			when grouping(an.Analyzer_Name) = 1
				then 'Итого по всем анализаторам:'
			end as uslugacomplex_code,
		case
			when grouping(uc.UslugaComplex_Code) = 0
				then uc.UslugaComplex_Name
			when grouping(uc.UslugaComplex_Code) = 1 and grouping(ucp.UslugaComplex_Code) = 0
				then ucp.UslugaComplex_Name
			end as uslugacomplex_name,
		ucp.UslugaComplex_id as p_UslugaComplex_id,
		uc.UslugaComplex_id,
		count(distinct ut.UslugaTest_id) as v1,
		count(distinct case when LabSampleStatus_id = 4 then ut.UslugaTest_id end) as v2
	from params
		inner join v_Evnlabsample as ELS on ELS.Lpu_id = p_Lpu_id or p_Lpu_id is null
		inner join v_UslugaTest as UT on UT.EvnLabSample_id = ELS.EvnLabSample_id
		inner join v_UslugaComplex as UC on UC.UslugaComplex_id = UT.UslugaComplex_id
		inner join v_EvnUslugaPAr as eup on eup.EvnUslugaPar_id = UT.UslugaTest_pid
		inner join v_UslugaComplex as ucp on ucp.UslugaComplex_id = eup.UslugaComplex_id
		inner join lis.v_Analyzer as an on ELS.Analyzer_id = an.Analyzer_id
		inner join v_MedService as ms on ms.MedService_id = an.MedService_id
		inner join v_LpuSection as ls on ls.lpuSection_id = ELS.LpuSection_did
	where (ls.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (ms.MedService_id = p_MedService_id or p_MedService_id is null)
		and (UT.PayType_id = p_PayType_id or p_PayType_id is null)
		and (ucp.UslugaComplex_id = p_UslugaComplex_id or p_UslugaComplex_id is null)
		and (an.Analyzer_id = p_Analyzer_id or p_Analyzer_id is null)
		and ELS.EvnLabSample_setDate between p_begdate and p_enddate
	group by grouping sets(
		(uc.UslugaComplex_Code,
		uc.UslugaComplex_Name,
		an.Analyzer_Name,
		ucp.UslugaComplex_Code,
		ucp.UslugaComplex_Name,
		ucp.UslugaComplex_id,
		uc.UslugaComplex_id),
		(an.Analyzer_Name,
		ucp.UslugaComplex_Code,
		ucp.UslugaComplex_Name,
		ucp.UslugaComplex_id),
		(an.Analyzer_Name),
		()
	)
)

select
	sub.row_num,
	sub.ord,
	sub.gr1,
	sub.gr2,
	sub.gr3,
	sub.analyzer_name,
	sub.uslugacomplex_code,
	sub.uslugacomplex_name,
	sub.v1,
	sub.v2
from sub
where sub.UslugaComplex_id != sub.p_UslugaComplex_id 
	or sub.UslugaComplex_id is null or sub.p_UslugaComplex_id is null
order by 
	sub.row_num,
	sub.ord
