with params as (
	select
		cast($1 as int8) p_lpu_id ,
		cast($2 as int8) p_LpuAttachType_id ,
		cast($3 as int8) p_LpuRegionType_id ,
		cast($4 as int8) p_LpuRegion_id ,
		cast($5 as int8) p_OrgSmo_id ,
		cast($6 as int8) p_CategoryStrah ,
		cast(to_timestamp($7,'dd.mm.yyyy') as timestamp) p_setdate 
),
LpuP as (
    --Выбираем все МО с их право приемственностью
    select     
        L.Lpu_id,
        L.Lpu_pid
    from params
    left join lateral (select*from rpt.GetLpu_succession (p_lpu_id, p_setdate, p_setdate)) as L   on true
    
),

PCSD1 as (
select
max(Personcard_id) as Personcard_id,Person_id
from
v_PersonCard_all pc
inner join LpuP lpu   on pc.Lpu_id = lpu.Lpu_id							
group by Person_id
),
pcard as (
select 
        lpu.Lpu_id,
        lpu.Lpu_pid,
        ps.Person_Surname,
        ps.Person_Firname,
        ps.Person_Secname,
        pc.LpuAttachType_id,
        pc.PersonCard_Code,
        pc.person_id as person_id,
        ps.Person_BirthDay,	
        dbo.AgeYmd(ps.Person_BirthDay,p_setdate,1) as Age,
        lr.LpuRegionType_id as LpuRegionType_id,
        ls.lpusection_name,
        Person_Fin,
		coalesce(lrt.LpuRegionType_Code,'')||' '||coalesce(lrt.LpuRegionType_Name,'') as LpuRegionType_Name,--
        lr.LpuRegion_id,
        case
            when (select count(lr_.LpuRegion_Name) from v_LpuRegion as lr_ where lr_.LpuRegion_Name = lr.LpuRegion_Name and lr_.Lpu_id = lpu.Lpu_pid) > 1
                and p_LpuAttachType_id = 1 and p_LpuRegionType_id is null and pc.LpuAttachType_id <> 1
            then lr.LpuRegion_Name || '(' || lrt.LpuRegionType_Code || ')'
            else lr.LpuRegion_Name
        end as LpuRegion_Name,
        ps.Sex_id as Sex_id,
        pol.Polis_id,
        osm.KLRgn_id as KLRgn_id,
        params.*
from params
	inner join PCSD1    on true
    inner join v_PersonState_all PS on PCSD1.person_id=PS.person_id
	inner join lateral (select * from  v_PersonCard PC where PC.Person_id = PS.Person_id limit 1) PC on true
	inner join LpuP lpu   on pc.Lpu_id = lpu.Lpu_id
	left join v_LpuRegion lr on lr.LpuRegion_id = pc.LpuRegion_id    
	left join v_LpuSection ls  on ls.lpusection_id=lr.lpusection_id
    left join v_Polis pol  on ps.Polis_id=pol.Polis_id
        and (cast(Pol.Polis_begDate as date) <= p_setdate or Pol.Polis_begDate is null)  
        and (cast(Pol.Polis_endDate as date) >= p_setdate or Pol.Polis_endDate is null)
    left join v_OrgSMO osm      on pol.OrgSMO_id = osm.OrgSMO_id
    left join v_LpuRegionType as lrt      on lrt.LpuRegionType_id = lr.LpuRegionType_id
    left join lateral ( --врач с региона
		select msr.MedPersonal_id, MedPersonal.Person_Fin 
		from v_MedStaffRegion msr 
		inner join v_MedStaffFact MedPersonal  on MedPersonal.MedPersonal_id = msr.MedPersonal_id
			and MedPersonal.Lpu_id = pc.Lpu_id 
			and MedPersonal.WorkData_begDate <= p_setdate
			and coalesce(MedPersonal.WorkData_endDate,p_setdate) >= p_setdate
		where msr.Lpu_id = pc.Lpu_id 
			and msr.LpuRegion_id = pc.LpuRegion_id
		order by msr.MedStaffRegion_isMain desc, MedPersonal.Person_Fin asc limit 1
	) msr on true
where  (pc.LpuAttachType_id = p_LpuAttachType_id or p_LpuAttachType_id is null)        
        and (cast(pc.PersonCard_begDate as date) <= p_setdate)-- and pc.PersonCard_begDate is not null)
        and (cast(pc.PersonCard_endDate as date) >= p_setdate or PC.PersonCard_endDate is null)	
		and coalesce(PS.Person_IsUnknown,1) != 2
		and ps.Person_Surname is not null 
    	and coalesce(ps.Person_IsDead, 1) <> 2
        and (pol.OrgSmo_id=p_OrgSmo_id or p_OrgSmo_id IS NULL)
        --and ps.Polis_id is not null
        and (pc.LpuAttachType_id = p_LpuAttachType_id or p_LpuAttachType_id is null)
        and (lr.LpuRegionType_id = p_LpuRegionType_id or p_LpuRegionType_id is null)	
        and (lr.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null ) 
		and (p_CategoryStrah is null or p_CategoryStrah = -1 
	        or (p_CategoryStrah = 1 and (pol.Polis_id is not null and osm.KLRgn_id is not null))                 -- Застрахованные на территории региона и других субъектов РФ
            or (p_CategoryStrah = 2 and (pol.Polis_id is not null and osm.KLRgn_id = (select dbo.GetRegion())))  -- Застрахованные на территории региона
            or (p_CategoryStrah = 3 and (pol.Polis_id is not null and osm.KLRgn_id <> (select dbo.GetRegion()))) -- Застрахованные на территории других субъектов РФ
	        or (p_CategoryStrah = 4 and pol.Polis_id is null)) 	
	
),

grouper as (
    select
        case when oo.Okfs_Code in (11,12,13,14,61) then 0 else 1 end as "Okfs_id",
        L.Lpu_Nick,		
		LpuRegionType_Name,--
        pcard.LpuRegion_Name,
		lpusection_name,
		Person_Fin,--
        count(pcard.person_id) as v1, --при завершении задачи 173538 и выравниванию данных по прикреплениям, добавить distinct в поля
        count(case when pcard.Age between 0 and 0  and pcard.Sex_id = 2 then pcard.person_id end) as v2, -- #179863
        count(case when pcard.Age between 0 and 0  and pcard.Sex_id = 1 then pcard.person_id end) as v3,
        count(case when pcard.Age between 0 and 4  and pcard.Sex_id = 2 then pcard.person_id end) as v4,
        count(case when pcard.Age between 0 and 4  and pcard.Sex_id = 1 then pcard.person_id end) as v5,
        count(case when pcard.Age between 5 and 17  and pcard.Sex_id = 2 then pcard.person_id end) as v6,
        count(case when pcard.Age between 5 and 17  and pcard.Sex_id = 1 then pcard.person_id end) as v7,
        count(case when Sex_id=2 and rpt.workage(person_birthday,Sex_id,p_setdate)=2 then pcard.person_id end) v8,        
		count(case when Sex_id=1 and rpt.workage(person_birthday,Sex_id,p_setdate)=2 then pcard.person_id end) v9,        
		count(case when Sex_id=2 and rpt.workage(person_birthday,Sex_id,p_setdate)=1 then pcard.person_id end) v10,        
		count(case when Sex_id=1 and rpt.workage(person_birthday,Sex_id,p_setdate)=1 then pcard.person_id end) v11
    from pcard 
    join v_Lpu L  
        on L.Lpu_id = pcard.Lpu_pid
    join v_Org O  
        on L.Org_id = O.Org_id
    join v_Okfs oo  
        on O.Okfs_id = oo.Okfs_id
    where 1 = 1
        and p_lpu_id is not null
        and pcard.Lpu_id = pcard.Lpu_pid
    group by 
        case 
            when oo.Okfs_Code in (11,12,13,14,61) then 0 
            else 1 
        end,
        L.Lpu_Nick,		
		LpuRegionType_Name,--
        pcard.LpuRegion_Name,
		lpusection_name,
		Person_Fin
    union all
    select
        case when oo.Okfs_Code in (11,12,13,14,61) then 0 else 1 end as "Okfs_id",
        L.Lpu_Nick,
        --'' as LpuRegion_Name,
		'' LpuRegionType_Name,--
        '' LpuRegion_Name,
		'' lpusection_name,
		'' Person_Fin,--
        count(pcard.person_id) as v1, 
        count(case when pcard.Age between 0 and 0  and pcard.Sex_id = 2 then pcard.person_id end) as v2, 
        count(case when pcard.Age between 0 and 0  and pcard.Sex_id = 1 then pcard.person_id end) as v3,
        count(case when pcard.Age between 0 and 4  and pcard.Sex_id = 2 then pcard.person_id end) as v4,
        count(case when pcard.Age between 0 and 4  and pcard.Sex_id = 1 then pcard.person_id end) as v5,
        count(case when pcard.Age between 5 and 17  and pcard.Sex_id = 2 then pcard.person_id end) as v6,
        count(case when pcard.Age between 5 and 17  and pcard.Sex_id = 1 then pcard.person_id end) as v7,
        count(case when Sex_id=2 and rpt.workage(person_birthday,Sex_id,p_setdate)=2 then pcard.person_id end) v8,
        count(case when Sex_id=1 and rpt.workage(person_birthday,Sex_id,p_setdate)=2 then pcard.person_id end) v9,
        count(case when Sex_id=2 and rpt.workage(person_birthday,Sex_id,p_setdate)=1 then pcard.person_id end) v10,
        count(case when Sex_id=1 and rpt.workage(person_birthday,Sex_id,p_setdate)=1 then pcard.person_id end) v11
    from pcard 
    join v_Lpu L  
        on L.Lpu_id = pcard.Lpu_pid
    join v_Org O  
        on L.Org_id = O.Org_id
    join v_Okfs oo  
        on O.Okfs_id = oo.Okfs_id
    where 1 = 1
        and p_lpu_id is null
        and pcard.Lpu_id = pcard.Lpu_pid
    group by 
        case 
            when oo.Okfs_Code in (11,12,13,14,61) then 0 
            else 1 
        end,
        L.Lpu_Nick
)
select *
from grouper as g
order by 
    case 
        when g.LpuRegion_Name ~ '^[0-9\.]+$' then repeat('0', 100 - length(g.LpuRegion_Name)) || g.LpuRegion_Name
        else g.LpuRegion_Name
    end

