--pan_PersonRegister_List_pg.rptdesign
with param as (select
  cast( to_timestamp($1,'dd.mm.yyyy') as timestamp) as p_date,
  cast($2 as int8) as p_personregistertype_id,
  cast($3 as int8) as p_lpu_id,
  cast($4 as int8) as p_privilegetype_id,
  cast($5 as int8) as p_invalid_yesno,
  cast($6 as int8) as p_duchet_yesno,
  cast($7 as int8) as p_diagcode,
  cast($8 as int8) as p_person_agegroup
  ),
  t_type as 
(
select personregistertype_sysnick, personregistertype_id
from v_personregistertype 
  where personregistertype_id = (select p_personregistertype_id from param)
union all
  select  'ipra' as PersonRegisterType_SysNick, 999 personregistertype_id
  from param
  where p_personregistertype_id = 999
),

-- Для обычных регистров
Register_Data as
( 
  select 
    PR.Person_id,
    PR.PersonRegister_setDate as Inclusion_Date
  from param
  inner join v_PersonRegister PR on true
  inner join t_type on true
	inner join v_MorbusType MorbusType on MorbusType.MorbusType_id = PR.MorbusType_id
	left join v_PersonRegisterType as PersonRegisterType on PersonRegisterType.PersonRegisterType_id = PR.PersonRegisterType_id
  left join v_PersonRegisterEndo PRE  on PR.PersonRegister_id = PRE.PersonRegister_id  
 -- left join v_Diag Diag with (nolock) on PR.Diag_id = Diag.Diag_id  
  left join v_MorbusCrazy MO  on PR.Morbus_id=MO.Morbus_id                            
  left join lateral (
	  select  CD.Diag_id 
     from v_MorbusCrazyDiag MCD 
	   left join v_CrazyDiag CD  on CD.CrazyDiag_id=MCD.CrazyDiag_id 
	   where MCD.MorbusCrazy_id=MO.MorbusCrazy_id
	   order by MCD.MorbusCrazyDiag_setDT desc limit 1
   )CDiag on true --111204#note-10 чтобы отчёт совпадал с формой регистра по психиатрии....
  left join v_Diag Diag  on Diag.Diag_id = CDiag.Diag_id
  left join v_Diag PRDiag  on PRDiag.Diag_id = PR.Diag_id
  left join lateral (
                        select IPRARegistry_id
                        from dbo.IPRARegistry
                        where Person_id = PR.Person_id
                        order by IPRARegistry_issueDate desc
                        limit 1
                    ) IR on true
  where 1 = 1
    and (PR.PersonRegister_disDate > p_Date + interval '1 day' - interval '1 minute' or PR.PersonRegister_disDate is null)
    and PR.PersonRegister_setDate <= p_Date
    and t_type.personregistertype_sysnick in ('pregnancy','onko','crazy','hepa','orphan','tub','vener','hiv','endoprosth','diabetes','nephro','prof','nolos','ibs','suicide','narc','palliat','geriatrics','ipra')
    and t_type.personregistertype_sysnick = 
       case when t_type.personregistertype_sysnick in ('pregnancy','orphan','prof','nolos','suicide','narc','palliat','geriatrics') then PersonRegisterType.personregistertype_sysnick 
                                      when t_type.personregistertype_sysnick in ('onko','crazy','hepa','tub','vener','hiv','diabetes','nephro','ibs','ipra') then MorbusType.MorbusType_sysnick
                                      when t_type.personregistertype_sysnick = 'endoprosth' then 'endoprosth'
								 end 
    and (coalesce(Diag.Diag_id, PRDiag.Diag_id) = p_DiagCode or p_DiagCode is null)
    and (PRE.PersonRegister_id is not null and t_type.PersonRegisterType_sysnick in ('endoprosth') or t_type.PersonRegisterType_sysnick not in ('endoprosth'))
	and (t_type.PersonRegisterType_sysnick = 'ipra' and exists (select IPRARegistryData_id from dbo.v_IPRARegistryData where IPRARegistry_id = IR.IPRARegistry_id limit 1) or t_type.PersonRegisterType_sysnick != 'ipra')

),

-- Для Общетерапевтического регистра
DUchet_Data as
(
  select 
    PD.Person_id,
    PD.PersonDisp_begDate as Inclusion_Date  
     
  from param 
  inner join v_PersonDisp PD on true
  inner join t_type on true
  inner join v_Lpu Lpu  on PD.Lpu_id = Lpu.Lpu_id
    and (Lpu.Lpu_id = p_Lpu_id or p_Lpu_id is null) 
    
  inner join lateral
  (
    select 
      PC.PersonCard_id,
      PC.Lpu_id
    from v_PersonCard_all PC 
    where
      PC.LpuAttachType_id = 1
      and PC.Person_id = PD.Person_id 
      and cast(PC.PersonCard_begDate as date) <= p_Date
	  and (cast(PC.PersonCard_endDate as date) > p_Date or PC.PersonCard_endDate  is null)--#111204
    order by PC.PersonCard_begDate desc, PC.PersonCard_id desc limit 1   
  ) Person_PC  on true     
    
  inner join v_Lpu LpuPC  on Person_PC.Lpu_id = LpuPC.Lpu_id
    and PD.Lpu_id = LpuPC.Lpu_id  

  left join v_Diag Diag  on Diag.Diag_id = PD.Diag_id 
  
  
  where 1 = 1
    and cast(PD.PersonDisp_begDate as date) < p_Date
    and (cast(PD.PersonDisp_endDate as date) > p_Date or PD.PersonDisp_endDate is null)
    and t_type.PersonRegisterType_sysnick = 'common' 
    and coalesce(p_DUchet_YesNo, 2) <> 1 
    and (Diag.Diag_id = p_DiagCode or p_DiagCode is null)       
),

-- Для регистров по Федеральным и Региональным льготам
Lgoti_Data as
(
  select 
    PersPriv.Person_id,
    PersPriv.PersonPrivilege_begDate as Inclusion_Date
  from param inner join v_PersonPrivilege PersPriv on true
    inner join t_type on true
    inner join v_PrivilegeType PrivT  on PersPriv.PrivilegeType_id = PrivT.PrivilegeType_id
  where 1=1
    and cast(PersPriv.PersonPrivilege_begDate as date) < p_Date
    and (cast(PersPriv.PersonPrivilege_endDate as date) > p_Date or PersPriv.PersonPrivilege_endDate is null) 
    and t_type.PersonRegisterType_sysnick in ('common_fl','common_rl','diabetes')
    and (PrivT.PrivilegeType_Code between 1 and 150 and t_type.PersonRegisterType_sysnick  = 'common_fl'
      or PrivT.PrivilegeType_Code > 150 and t_type.PersonRegisterType_sysnick  = 'common_rl'
      or PrivT.PrivilegeType_Code = 319 and t_type.PersonRegisterType_sysnick  = 'diabetes') 
    and p_DiagCode is null  

)

Select 
   ROW_NUMBER()Over(order by concat_ws(' ',PS.Person_SurName,PS.Person_FirName,PS.Person_SecName),PS.Person_BirthDay, reg.Inclusion_Date)::bigint as "RowNum",

    concat_ws(' ',PS.Person_SurName,PS.Person_FirName,PS.Person_SecName) as "Person_FIO",
    PS.Person_BirthDay as "Person_BirthDay",
    Addr.Address_Address::varchar as "Person_Address", 
    LpuPC.Lpu_Nick::varchar as "LpuPC_Name",
    reg.Inclusion_Date as "Inclusion_Date",
  
    case when Invalid.PersonPrivilege_id is null 
      then 'нет' else 'да' end::varchar as "Invalid",
   	concat_ws(' ',Privilege_1.PrivilegeType_Code,Privilege_1.PrivilegeType_Name) as "Privilege_1",
    concat_ws(' ',Privilege_2.PrivilegeType_Code,Privilege_2.PrivilegeType_Name) as "Privilege_2",
    concat_ws(' ',Privilege_3.PrivilegeType_Code,Privilege_3.PrivilegeType_Name) as "Privilege_3", 
    concat_ws(' ',Privilege_4.PrivilegeType_Code,Privilege_4.PrivilegeType_Name) as "Privilege_4",
    concat_ws(' ',Privilege_5.PrivilegeType_Code,Privilege_5.PrivilegeType_Name) as "Privilege_5",
    case when D_Uchet.PersonDisp_id is null 
      then 'нет' else 'да' end::varchar as "D_Uchet"

 from param
  inner join (
    select person_id,Inclusion_Date from Register_Data
    union all
    select person_id,Inclusion_Date from DUchet_Data
    union all
    select person_id,Inclusion_Date from Lgoti_Data
  ) reg on TRUE
  inner join v_PersonState PS  on reg.Person_id = PS.Person_id
  left join Address Addr  on coalesce(PS.PAddress_id, PS.UAddress_id) = Addr.Address_id
  left join lateral
  (
    select 
      PC.PersonCard_id,
      PC.Lpu_id
    from v_PersonCard_all PC 
    where
      PC.LpuAttachType_id = 1
      and PC.Person_id = PS.Person_id 
      and cast(PC.PersonCard_begDate as date) <= p_Date
	  and (cast(PC.PersonCard_endDate as date) > p_Date or PC.PersonCard_endDate  is null)--#111204
    order by PC.PersonCard_begDate desc, PC.PersonCard_id desc limit 1   
  ) Person_PC on true
  
  left join v_Lpu_all LpuPC  on Person_PC.Lpu_id = LpuPC.Lpu_id

  left join lateral
  (
    select  
      PP.PersonPrivilege_id,
      PP.PersonPrivilege_begDate  
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
      and PT.PrivilegeType_Code in (81,82,83,84)  
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1
  ) Invalid on true

  left join lateral
  (
    select  
      PP.PersonPrivilege_id,
      PT.PrivilegeType_id,
      PT.PrivilegeType_Code,
      PT.PrivilegeType_Name
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1 
  ) Privilege_1 on true

  left join lateral
  (
    select  
      PP.PersonPrivilege_id,
      PT.PrivilegeType_id,
      PT.PrivilegeType_Code,
      PT.PrivilegeType_Name
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
      and PP.PersonPrivilege_id not in (Privilege_1.PersonPrivilege_id)
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1  
  ) Privilege_2 on true

  left join lateral
  (
    select  
      PP.PersonPrivilege_id,
      PT.PrivilegeType_id,
      PT.PrivilegeType_Code,
      PT.PrivilegeType_Name
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
      and PP.PersonPrivilege_id not in (Privilege_1.PersonPrivilege_id, Privilege_2.PersonPrivilege_id)
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1
  ) Privilege_3 on true

  left join lateral
  (
    select  
      PP.PersonPrivilege_id,
      PT.PrivilegeType_id,
      PT.PrivilegeType_Code,
      PT.PrivilegeType_Name
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
      and PP.PersonPrivilege_id not in (Privilege_1.PersonPrivilege_id, Privilege_2.PersonPrivilege_id,
        Privilege_3.PersonPrivilege_id)
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1   
  ) Privilege_4 on true

  left join lateral
  ( 
    select 
      PP.PersonPrivilege_id,
      PT.PrivilegeType_id,
      PT.PrivilegeType_Code,
      PT.PrivilegeType_Name
    from v_PersonPrivilege PP 
    inner join v_PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PP.Person_id = PS.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= p_Date
      and (cast(PP.PersonPrivilege_endDate as date) >= p_Date or PP.PersonPrivilege_endDate is null)
      and PP.PersonPrivilege_id not in (Privilege_1.PersonPrivilege_id, Privilege_2.PersonPrivilege_id,
        Privilege_3.PersonPrivilege_id, Privilege_4.PersonPrivilege_id)
    order by PP.PersonPrivilege_begDate desc, PP.PersonPrivilege_id desc limit 1    
  ) Privilege_5 on true

  left join lateral
  (
    select 
      PersonDisp.PersonDisp_id
    from v_PersonDisp PersonDisp
    where
      PersonDisp.Person_id = PS.Person_id
      and PersonDisp.Lpu_id = Person_PC.Lpu_id 
      and cast(PersonDisp.PersonDisp_begDate as date) <= p_Date
      and (cast(PersonDisp.PersonDisp_endDate as date) >= p_Date or PersonDisp.PersonDisp_endDate is null)  
    order by PersonDisp.PersonDisp_begDate desc, PersonDisp.PersonDisp_id desc  limit 1 	
  ) D_Uchet on true
where
    (Person_PC.Lpu_id = p_Lpu_id or p_Lpu_id is null)
    and ((Privilege_1.PrivilegeType_id = p_PrivilegeType_id 
        or Privilege_2.PrivilegeType_id = p_PrivilegeType_id
        or Privilege_3.PrivilegeType_id = p_PrivilegeType_id
        or Privilege_4.PrivilegeType_id = p_PrivilegeType_id
        or Privilege_5.PrivilegeType_id = p_PrivilegeType_id) 
      or p_PrivilegeType_id is null)
    and (case when Invalid.PersonPrivilege_id is null then 1 else 2 end = p_Invalid_YesNo 
      or p_Invalid_YesNo is null)
    and (case when D_Uchet.PersonDisp_id is null then 1 else 2 end = p_DUchet_YesNo 
      or p_DUchet_YesNo is null)
    and (p_Person_AgeGroup = case when dbo.age2(PS.Person_BirthDay,p_Date) >= 18 then 1 else 2 end  
	  or p_Person_AgeGroup is null)




