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_lpuUnit_id,
		cast($4 as bigint) as p_lpuSection_id,
		cast($5 as bigint) as p_lpuregion_id,
		cast($6 as bigint) as p_onlypc,
		cast($7 as bigint) as p_yesno,
		cast($8 as timestamp) as p_begdate,	
		cast($9 as timestamp) as p_enddate,
		cast($10 as bigint) as p_begpersonage,
		cast($11 as bigint) as p_endpersonage
),
PersonCard2 as (
select ROW_NUMBER() OVER(PARTITION BY PersonCard.Person_id order by PersonCard.PersonCard_begDate) numCard,
PersonCard.Lpu_id,
PersonCard.LpuRegion_id,
PersonCard.LpuRegionType_id,
PersonCard.Person_id,
PersonCard.Person_BirthDay,
Polis.polis_id
from v_PersonCard_all PersonCard
left join params on 1=1
left join lateral (
	   select
		 pall.Person_id, pol.OrgSmo_id, pall.SocStatus_id
	   ,pol.polis_id
	   from v_Person_all pall  
		inner join v_Polis pol   on pall.Polis_id = pol.Polis_id
		inner join OrgSMO  on OrgSMO.OrgSMO_id=pol.OrgSmo_id
			and ((OrgSmo_endDate>=p_begdate or OrgSmo_endDate is null) or pol.OrgSMO_id in (5,7))
	   where
		pall.Person_id = PersonCard.Person_id
		and pall.Server_pid=0
			and  cast(Pol.Polis_begDate as date)<p_begdate  
			and (cast(Pol.Polis_endDate as date)>=p_begdate or Pol.Polis_endDate is null)
		order by Pol.Polis_begDate desc
		limit 1
	  ) as Polis on true
where 
PersonCard.LpuAttachType_id = 1
and PersonCard.LpuRegionType_id in (1,4)
and PersonCard.PersonCard_begDate <= p_enddate 
and coalesce(PersonCard.PersonCard_endDate,p_enddate) >= p_begdate 
and (PersonCard.Lpu_id in (select l.Lpu_id from rpt.getlpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l) or p_Lpu_id is null) 
and (PersonCard.LpuRegion_id=p_lpuregion_id or p_lpuregion_id is null) 
),
Zapros as (
--свидетельства о смерти
select DS.Person_id,
DS.DeathSvid_id as Evn_ID, 
dOsn.Diag_Code as Diag_CodeOsn,
dOsn.Diag_id as Diag_idOsn,
dVne.Diag_Code as  Diag_CodeVne,
dVne.Diag_id as Diag_idVne,
DS.lpu_id,
dbo.AgeYMD_pg(PS.Person_BirthDay,DS.DeathSvid_DeathDate,1) as vozr,
ps.Sex_id,
PS.Person_BirthDay,
DS.DeathSvid_DeathDate
from v_DeathSvid DS
left join params on 1=1
left join v_Diag D_mid on D_mid.Diag_id=Diag_mid
left join v_Diag D_tid on D_tid.Diag_id=Diag_tid
left join v_Diag D_iid on D_iid.Diag_id=Diag_iid
inner join v_Diag dOsn  on 
dOsn.Diag_id= case when D_mid.Diag_Code between 'S00' and 'T98.9' 
				   then D_mid.Diag_id
			       else case when D_tid.Diag_Code between 'S00' and 'T98.9' 
							 then D_tid.Diag_id
							 else case when D_iid.Diag_Code between 'S00' and 'T98.9'
								       then D_iid.Diag_id
								  end
						end
			   end
---то в 57 форму нужно разнести согласно приоритетности: Первоначальная причина смерти -- Патологическое состояние -- Непосредственная причина 
inner join lateral(
	select DiagFilter.Diag_id, DiagFilter.Diag_Code
	from v_Diag AS DiagFilter 
	where ds.Diag_eid = DiagFilter.Diag_id 
	and ((case
		when DiagFilter.Diag_Code like 'W___3%' then 2
		when DiagFilter.Diag_Code like 'X___3%' then 2
		when DiagFilter.Diag_Code in ('Y01.3','Y02.3','Y03.3','Y04.3','Y05.3','Y08.3','Y09.3') then 2
		when DiagFilter.Diag_Code like 'Y___3%' and DiagFilter.Diag_Code between 'Y10.3' and 'Y34.3' then 2
		else 1 end = p_YesNo and p_YesNo = 2) or coalesce(p_YesNo, 1) = 1) --Только спортивные 2- спортивные, 1- все	
	and	DiagFilter.Diag_Code between 'V01' and 'Y98.9'
	limit 1
) AS dVne on true --#158005
inner join (select MAX(DeathSvid_insDT) as DeathSvid_insDT,
person_id from v_DeathSvid DeathSvid
left join params on 1=1
where 
coalesce(DeathSvid.DeathSvid_IsBad,1) <> 2
and DeathSvid.DeathSvid_DeathDate between p_begdate and p_enddate
group by person_id
) maxdate on DS.Person_id=maxdate.Person_id and DS.DeathSvid_insDT=maxdate.DeathSvid_insDT
inner join v_PersonState PS  on DS.Person_id=PS.Person_id
	and (dbo.AgeYMD_pg(cast(PS.Person_BirthDay as date), coalesce(cast(DS.DeathSvid_DeathDate as date), cast(DS.DeathSvid_GiveDate as date)), 1) 
	      >= p_begpersonage or p_begpersonage is null) --#158005
	and (dbo.AgeYMD_pg(cast(PS.Person_BirthDay as date), coalesce(cast(DS.DeathSvid_DeathDate as date), cast(DS.DeathSvid_GiveDate as date)), 1) <= p_endpersonage or p_endpersonage is null)
LEFT join v_lpusection LS  on DS.LpuSection_id=LS.LpuSection_id
left join v_LpuUnit LU   on LS.LpuUnit_id=LU.LpuUnit_id
left join PersonCard2 PC  on DS.Person_id=PC.person_id and numCard=1
where 1=1
and coalesce(DS.DeathSvid_IsBad,1) <> 2
and DS.DeathSvid_DeathDate between p_begdate and p_enddate
and dOsn.Diag_Code between 'S00' and 'T98.9'
and dVne.Diag_Code between 'V01' and 'Y98.9'
and ((DS.Lpu_id in (select l.Lpu_id from rpt.getlpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l) and (/*p_onlyPC=1 or */ p_onlyPC is null) or p_lpu_id is null)
or (p_onlyPC=1 and PC.Lpu_id not in (select l.Lpu_id from rpt.getlpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l) and DS.Lpu_id in (select l.Lpu_id from rpt.getlpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l) )
or (p_onlyPC=2 and PC.Lpu_id in (select l.Lpu_id from rpt.getlpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l)))--#99114
and (LU.LpuBuilding_id=p_lpuBuilding_id or p_lpuBuilding_id is null)
and (LU.LpuUnit_id=p_lpuUnit_id or p_lpuUnit_id  is null)
and (LS.LpuSection_id=p_lpuSection_id or p_lpuSection_id is null)
)
select RepDiag.RepDiag_Row AS "RepDiag_Row",
RepDiag.RepDiag_Text1 AS "RepDiag_Text1",
RepDiag.RepDiag_Text2 AS "RepDiag_Text2",
RepDiag.RepDiag_Text3 AS "RepDiag_Text3",
COUNT(distinct case when Zapros.vozr<=17 then Zapros.Evn_ID end ) as v4,
COUNT(distinct case when Zapros.vozr>=18 then Zapros.Evn_ID end ) as v5,
COUNT(distinct case when rpt.workage(Zapros.Person_Birthday, zapros.sex_id, Zapros.DeathSvid_DeathDate)=1 then Zapros.Evn_ID end ) as v6
from RepDiag 
left join params on 1=1
left join Zapros on Zapros.Diag_CodeVne between RepDiag_FromDiag and RepDiag_ToDiag
where RepDiag_SysNick='F57' and RepDiag_Part=2
group by RepDiag.RepDiag_Row,
RepDiag.RepDiag_Text1,
RepDiag.RepDiag_Text2,
RepDiag.RepDiag_Text3
order by "RepDiag_Row"
