with params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		cast($2 as bigint) as p_Lpubuilging_id,
		cast($3 as bigint) as p_LpuUnit_id,
		cast($4 as bigint) as p_LpuSection_id,
		cast($5 as bigint) as p_LpuRegion_id,
		cast($6 as bigint) as p_MedPersonal_id,
		cast($7 as bigint) as p_IsFinished,
		cast($8 as bigint) as p_PayType_id,
		cast($9 as bigint) as p_TreatmentClass_id,
		cast($10 as timestamp) as p_begdate,
		cast($11 as timestamp) as p_enddate,
		cast($12 as bigint) as p_region
),

vizit as(
	select 
		/*EPS_AllDefects_pg.rptdesign*/
		epl.evnclass_id,
		epl.EvnPL_id,
		evpl.EvnVizitPL_id,
		evpl.vizitclass_id,
		UslugaComplex_Name,
		epl.EvnPL_NumCard as v2,
		ps.Person_Fio as v3,
		to_char(ps.Person_BirthDay,'dd.mm.yyyy') as v4,
		LR.LpuRegion_Name as v5,
		to_char(evpl.EvnVizitPL_setDate, 'dd.mm.yyyy') as v6,
		d.Diag_Code as v7,
		msf.Person_Fio as v8,
		vc.VizitClass_Name as v9,
		coalesce(uc.UslugaComplex_Name, '') as v10,
		evplstom.EvnVizitPlstom_IsPrimaryVizit,
		concat_ws(', ',
			case when evpl.Diag_id is null then 'диагноз' end,
			case when evpl.TreatmentClass_id is null then 'вид обращения' end,
			case when evpl.ServiceType_id is null then 'место' end,
			case when evpl.VizitType_id is null then 'цель посещения' end,
			case when evpl.LpuSectionProfile_id is null then 'профиль' end,
			case when evpl.PayType_id is null then 'вид оплаты' end,
			case when evpl.MedicalCareKind_id is null then 'вид мед. помощи' end,
			case
				when evpl.UslugaComplex_id is null
						and p_region in (2,3,35,5,79,66,40,45,55,59,60,68)
					then 'код посещения'
				end,
			case when evpl.LpuSectionProfile_id is null then 'профиль' end
			) as empty_fields,
		p_region
	from params
		inner join v_evnvizitpl as evpl on evpl.lpu_id = p_Lpu_id or p_Lpu_id is null
		inner join v_evnPL as epl on epl.EvnPL_id = evpl.EvnVizitPL_pid
		left join v_EvnVizitPlstom as evplstom on evplstom.EvnVizitPlstom_id = evpl.EvnVizitPL_id
		left join v_VizitClass as vc on vc.VizitClass_id = evpl.VizitClass_id
		left join v_MedStaffFact as msf on evpl.MedStaffFact_id = msf.MedStaffFact_id
		left join v_LpuSection as ls on evpl.LpuSection_id = ls.LpuSection_id
		left join v_UslugaComplex as uc on uc.UslugaComplex_id = evpl.UslugaComplex_id
		left join v_Diag as d on epl.Diag_id = d.Diag_id
		inner join v_Person_all as ps on ps.PersonEvn_id = evpl.PersonEvn_id
			and ps.Server_id = evpl.Server_id
		left join lateral(
			select
				lr.LpuRegion_Name,
				lr.LpuRegion_id
			from v_PersonCard as pc
				left join v_LpuRegion as lr on lr.LpuRegion_id = pc.LpuRegion_id
					and (lr.Lpu_id = p_Lpu_id or p_lpu_id is null)
			where pc.Person_id = epl.Person_id
				and (pc.Lpu_id = p_lpu_id or p_Lpu_id is null)
				and (pc.PersonCard_endDate > p_enddate or pc.PersonCard_endDate is null)
				and pc.PersonCard_begDate < p_enddate
				and (lr.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null)
			order by pc.PersonCard_begDate desc
			limit 1
		)LR on true
	where true
		and (epl.lpu_id = p_lpu_id or p_Lpu_id is null)
		and epl.EvnPL_disDate between p_begdate and p_enddate
		and (ls.LpuBuilding_id = p_Lpubuilging_id or p_Lpubuilging_id is null)
		and (ls.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (ls.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (epl.EvnPL_IsFinish = p_IsFinished or coalesce(p_IsFinished,3) = 3)	
		and (case
				when evpl.treatmentclass_id in (1,2,3,4) then 1 
				else 2 end = p_TreatmentClass_id or p_TreatmentClass_id is null)
		and (evpl.paytype_id = p_PayType_id or p_PayType_id is null)
		and (lr.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null)
		and evpl.EvnVizitPL_setDate <= p_enddate
		and (evpl.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (p_region <> 35 or p_region = 35 and epl.evnclass_id <> 6)
		--#158138 на Вологде не учитывать стомат
),

cnt as(
	select
		vizit.EvnPL_id,
		count(distinct
			case
				when vizit.VizitClass_id = 1
					then vizit.EvnVizitPL_id
				end) as first_cnt,
		count(distinct
			case
				when vizit.EvnVizitPlstom_IsPrimaryVizit = 2
					then vizit.EvnVizitPL_id
				end) as first_stom_cnt
	from vizit
	group by vizit.EvnPL_id
),

error as(
	select 
		v2,v3,v4,v5,v6,v7,v8,v9,v10,
		concat_ws('; '||chr(10),
			case 
				when EvnClass_id != 6 and cnt.first_cnt = 0
					then 'Нет первичного посещения в случае'
				when EvnClass_id = 6 and cnt.first_stom_cnt = 0
					then 'Нет первичного посещения в случае'
				end,
			case 
				when EvnClass_id != 6 and vizit.VizitClass_id is null
					then 'Вид посещения не заполнен'
				when EvnClass_id = 6 and vizit.EvnVizitPlstom_IsPrimaryVizit is null
					then 'Вид посещения не заполнен'
				end,
			case 
				when EvnClass_id != 6 and cnt.first_cnt > 1
					then 'Более одного первичного посещения'
				when EvnClass_id = 6 and cnt.first_stom_cnt > 1
					then 'Более одного первичного посещения'
				end,
			case 
				when p_region in (2,3,35,5,79,66,40,45,55,59,60,68) and EvnClass_id != 6
						and VizitClass_id = 1
						and (UslugaComplex_Name ilike '%повторн%' or UslugaComplex_Name is null)
					then 'Вид посещения не соответствует коду посещения' 
				when p_region in (2,3,35,5,79,66,40,45,55,59,60,68) and EvnClass_id != 6
						and VizitClass_id = 2
						and (UslugaComplex_Name ilike '%первичн%' or UslugaComplex_Name is null)
					then 'Вид посещения не соответствует коду посещения'
				end,
			'Не заполнены поля обязательные к заполнению ('||nullif(empty_fields, '')||')'
			) as v11
	from vizit
		inner join cnt on cnt.EvnPL_id = vizit.EvnPL_id
)

select 
	row_number() over (order by v3,v6,v2 asc) as v1,
	v2,v3,v4,v5,v6,v7,v8,v9,v10,v11
from error 
where rtrim(v11) <> ''
