-- Реестр свидетельств о смерти
with p as ( select 1 as id, 1  as p_IsTest)
select
  distinct
    case p.sex_id when 1 then 'М' when 2 then 'Ж' else 'Нет' end as p001,
    "Дата создания свидетельства" as p002,
    getdate() as p3,
    'Да' as p4,
    upper(p."FIO") as p6,
  --  upper(coalesce(Person_surname,'')) ||' '|| upper(coalesce(Person_FirName,'')) ||' '|| upper(coalesce(Person_SecName,'')) as p6,
    to_char(dtr,'dd.MM.yyyy') as p7,
      coalesce(address_address,'Неизвестно') as p8,
      coalesce("AREA",'Неизвестно') as p9,
    case when KLAreaType_id=1 then 'Город' when KLAreaType_id=2 then 'Село' else 'Неизвестно' end as p10 ,
    case when d4.Diag_Code is null then case when d2.Diag_Code is null then d.Diag_Code ||' '|| d.Diag_Name else d2.Diag_Code ||' '|| d2.Diag_Name end else d4.Diag_Code ||' '|| d4.Diag_Name end as p11,
    d.Diag_Code ||' '|| d.Diag_Name as p12, --"идентификатор справочника диагнозов (непосредственная причина смерти)",
    d2.Diag_Code ||' '|| d2.Diag_Name as p13, -- "идентификатор справочника диагнозов (патологическое состояние)",
    d4.Diag_Code ||' '|| d4.Diag_Name as p14, --"идентификатор справочника диагнозов (основная причина смерти) Первоначальная причина смерти?",
    d5.Diag_Code ||' '|| d5.Diag_Name as p15, --"идентификатор справочника диагнозов (внешняя причина смерти)",
    d3.Diag_Code ||' '|| d3.Diag_Name as p16, --"идентификатор справочника диагнозов (прочие важные состояние)",
    "Дата смерти" as p17,
    "место смерти" as p18,
    (case when not LS.LpuSection_Name is null then p.Lpu_Nick || ', ' || LS.LpuSection_Name else p.Lpu_Nick end)::varchar as p19,
    case when DeathSetCause_id=1 then 'Осмотр трупа'
    when DeathSetCause_id=2 then 'Записи в медицинской документации'
    when DeathSetCause_id=3 then 'Предшествующие наблюдение за больным'
    when DeathSetCause_id=4 then 'Вскрытие'
    else 'Неизвестно' end as p20,
    "МСС" as p21,
    p22,
    p23,
    (PSMed.Person_SurName ||' '|| left(PSMed.Person_FirName,1) ||'. '|| case when length(PSMed.Person_SecName) = 0 then '' else left(PSMed.Person_SecName,1) ||'.' end)::varchar as p24,
    (PSMed.Person_SurName ||' '|| (PSMed.Person_FirName) ||' '|| case when length(PSMed.Person_SecName) = 0 then '' else (PSMed.Person_SecName) end)::varchar as p25,
    ogrn,
    case
    when DeathSvidType_Code=1 then 'Окончательное'
    when DeathSvidType_Code=2 then 'Предварительное'
    when DeathSvidType_Code=3 then 'Взамен предварительного'
    when DeathSvidType_Code=4 then 'Взамен окончательного' else null end "deathDocumentType",

    LB.LpuBuilding_Name,
    (rtrim(coalesce(PSOH.Person_SurName, '')) || rtrim(' ' || coalesce(PSOH.Person_FirName, '')) || rtrim(' ' || coalesce(PSOH.Person_SecName, '')))::varchar as OrgHead_FIO
from (
  select L.Lpu_Name , L.Lpu_Nick,
    --(case when not T00.LpuSection_Name is null then L.Lpu_Nick || ', ' || T00.LpuSection_Name else L.Lpu_Nick end) as Lpu_Nick,
    L.Lpu_id,
    L.Lpu_OGRN AS ogrn,
    adr.KLAreaType_id,
    coalesce(adrf.address_address,adr.address_address ) as address_address,
    coalesce(kar.klarea_name,'')||' '||coalesce(socr.klsocr_name,'') as "AREA",
      coalesce(ps.Person_surname,'')||' '||coalesce(ps.Person_FirName,'')||' '||coalesce(ps.Person_SecName,'') "FIO",
      ps.Person_BirthDay as dtr,
      ps.Sex_id,
      Diag_eid ,
    Diag_iid ,
    Diag_mid,
    Diag_oid,
    Diag_tid ,
      dp.DeathPlace_Name "место смерти",
       DeathSvid_DeathDate  "Дата смерти",
       ds.DeathSetCause_id,
      DS.DeathSvid_insDT as "Дата создания свидетельства",
    trim(coalesce(ds.DeathSvid_Ser,'')||' '||coalesce(ds.DeathSvid_Num,'')) as "МСС",
    DS.DeathSvid_id as p22,
    0 as p23,
--     T01.Person_SurName ||' '|| left(t01.Person_FirName,1) ||'. '|| case when length(t01.Person_SecName) = 0 then '' else left(t01.Person_SecName,1) ||'.' end as p24,
--     T01.Person_SurName ||' '|| (t01.Person_FirName) ||' '|| case when length(t01.Person_SecName) = 0 then '' else (t01.Person_SecName) end as p25,
    coalesce(DS.MedStaffFact_id, DS.MedStaffFact_did) as WorkPlace_id,
    dst.DeathSvidType_Code,
    DS.LpuSection_id,
    DS.person_hid,
    DS.orgheadpost_id
  from dbo.v_Lpu L --
  inner join p on id = id and (coalesce(L.Lpu_IsTest, 1) = p_IsTest or p_IsTest is null)
  inner join dbo.v_DeathSvid DS on DS.Lpu_id = L.Lpu_id --and cast(DS.DeathSvid_DeathDate as date) between p_BegDate and p_EndDate
  left join dbo.v_DeathSvidType DST  on DST.DeathSvidType_id = DS.DeathSvidType_id
  left join dbo.v_PersonState PS  on PS.Person_id = DS.Person_id
  left join address as adr  on ps.uaddress_id = adr.address_id --адрес регистрации
  left join address as adrf on ps.PAddress_id = adrf.address_id --адрес фактич
--inner join address as adr on m.uaddress_id = adr.address_id --адрес регистрации
--inner join address as adrf on m.PAddress_id = adrf.address_id --адрес фактич
  left join klarea as kar  on adr.klrgn_id = kar.klarea_id-- and kar.klarealevel_id='1' --область
  left join klsocr as socr  on kar.klsocr_id = socr.klsocr_id
  left join dbo.v_Sex S  on S.Sex_id = PS.Sex_id
  left join dbo.v_SocStatus SS  on SS.SocStatus_id = PS.SocStatus_id
  left join v_DeathPlace dp  on dp.DeathPlace_id=ds.DeathPlace_id
  --left join dbo.v_MedStaffFact T01 on DS.Lpu_id =T01.Lpu_id and DS.MedStaffFact_id = T01.MedStaffFact_id --and T01.Lpu_id =13100902
  where DS.DeathSvid_IsActual = 2
--
union all
--
  select L.Lpu_Name , L.Lpu_Nick,
    --(case when not T00.LpuSection_Name is null then L.Lpu_Nick ||', '|| T00.LpuSection_Name else L.Lpu_Nick end) as Lpu_Nick,
    L.Lpu_id ,
      L.Lpu_OGRN ,
      adr.KLAreaType_id,
    coalesce(adrf.address_address,adr.address_address ) as address_address,
      coalesce(kar.klarea_name,'')||' '||coalesce(socr.klsocr_name,'') as "AREA",
    upper(PntDS.PntDeathSvid_ChildFio) as "FIO",
    coalesce(cast(pntds.PntDeathSvid_ChildBirthDT as date) ,cast(PntDeathSvid_DeathDate as date)) as dtr,
    --ps.Person_surname,
  --    ps.Person_FirName,
   --   ps.Person_SecName,
   --   coalesce(ps.Person_BirthDay,cast(PntDeathSvid_DeathDate as date)) as dtr,
      ps.Sex_id,
      Diag_eid ,
    Diag_iid ,
    Diag_mid,
    Diag_oid,
    Diag_tid ,
       dp.DeathPlace_Name "место смерти",
      PntDeathSvid_DeathDate "Дата смерти",
      PntDS.PntDeathSetCause_id as DeathSetCause_id,
     PntDS.PntDeathSvid_insDT as "Дата создания свидетельства",
    trim(coalesce(PntDS.PntDeathSvid_Ser,'')||' '||coalesce(PntDS.PntDeathSvid_Num,'')) as "МСС",
    PntDS.PntDeathSvid_id,
    1 as p23,
--     T01.Person_SurName ||' '|| left(t01.Person_FirName,1) ||'. '|| case when length(t01.Person_SecName) = 0 then '' else left(t01.Person_SecName,1) ||'.' end  as p24,
--     T01.Person_SurName ||' '|| (t01.Person_FirName) ||' '|| case when length(t01.Person_SecName) = 0 then '' else (t01.Person_SecName) end  as p24,
    coalesce(PntDS.MedStaffFact_id, PntDS.MedStaffFact_did) as WorkPlace_id,
    dst.DeathSvidType_Code,
    PntDS.LpuSection_id,
    PntDS.person_hid,
    PntDS.orgheadpost_id
  from dbo.v_Lpu L
  inner join p on id = id and (coalesce(L.Lpu_IsTest, 1) = p_IsTest or p_IsTest is null)
  inner join dbo.v_PntDeathSvid PntDS  on PntDS.Lpu_id = L.Lpu_id --and cast(PntDS.PntDeathSvid_DeathDate as date) between p_BegDate and p_EndDate
  left join dbo.v_PersonState PS  on PS.Person_id = PntDS.Person_id
  left join address as adr  on ps.uaddress_id = adr.address_id --адрес регистрации
  left join address as adrf on ps.PAddress_id = adrf.address_id --адрес фактич
--inner join address as adr on m.uaddress_id = adr.address_id --адрес регистрации
--inner join address as adrf on m.PAddress_id = adrf.address_id --адрес фактич
  left join klarea as kar  on adr.klrgn_id = kar.klarea_id-- and kar.klarealevel_id='1' --область
  left join klsocr as socr  on kar.klsocr_id = socr.klsocr_id
  left join dbo.v_DeathSvidType DST  on DST.DeathSvidType_id = PntDS.DeathSvidType_id
  left join dbo.v_Sex S  on S.Sex_id = PntDS.Sex_id
  left join v_DeathPlace dp  on dp.DeathPlace_id=PntDS.PntDeathPlace_id
  --left join dbo.v_MedStaffFact T01 on pntDS.Lpu_id =T01.Lpu_id and PntDS.MedStaffFact_id = T01.MedStaffFact_id --and T01.Lpu_id =13100902
  where PntDS.PntDeathSvid_IsActual = 2
) p
left join persis.WorkPlace WP on WP.id = p.WorkPlace_id
left join persis.Staff Staff on Staff.id = WP.Staff_id
left join persis.MedWorker MW on MW.id = WP.MedWorker_id
left join dbo.v_PersonState PSMed on PSMed.Person_id = MW.Person_id
left join dbo.v_LpuSection_all LS on LS.LpuSection_id = coalesce(Staff.LpuSection_id, p.LpuSection_id)-- and T00.Lpu_id =13100902
left join dbo.v_LpuUnit LU on LU.LpuUnit_id = coalesce(LS.LpuUnit_id, Staff.LpuUnit_id)
left join dbo.v_LpuBuilding LB on LB.LpuBuilding_id = coalesce(LU.LpuBuilding_id, Staff.LpuBuilding_id)
left join dbo.v_Diag D on D.Diag_id = coalesce(p.Diag_iid,0)
left join dbo.v_Diag D2 on D2.Diag_id = coalesce(p.Diag_tid,0)
left join dbo.v_Diag D3 on D3.Diag_id = coalesce(p.Diag_oid,0)
left join dbo.v_Diag D4 on D4.Diag_id = coalesce(p.Diag_mid,0)
left join dbo.v_Diag D5 on D5.Diag_id = coalesce(p.Diag_eid,0)

left join lateral
(
  select OH.Person_id
  from dbo.v_OrgHead OH
  where oh.Person_id = p.Person_hid and OH.OrgHeadPost_id = p.OrgHeadPost_id
  limit 1
) OH on true

left join dbo.v_PersonState PSOH on PSOH.Person_id = OH.Person_id

