--https://jira.is-mis.ru/browse/PROMEDWEB-78830
-- Регистр льготников (MS)
--declare  @date datetime = cast(cast(getdate() as date) as datetime);
with dt as
(
select --top 1
  ROW_NUMBER()OVER(partition by PP.PersonPrivilege_id order by PC.PersonCard_begDate desc, coalesce(PC.PersonCard_endDate, (current_date + 1)::date) desc) as rnum,
  PS.Person_id,
  upper(trim(trim(coalesce(PS.Person_SurName, '')) || ' ' || trim(coalesce(PS.Person_FirName, '')) || ' ' || trim(coalesce(PS.Person_SecName, '')))) as Person_FIO,
  PS.Person_BirthDay,
  ps.PersonCloseCause_id,
  ps.Person_closeDT,
  PP.Lpu_id as LpuPP_id,
  PC.Lpu_id as LpuPC_id,
  PS.PAddress_id,
  PS.UAddress_id,
  PT.PrivilegeType_Code,
  PT.PrivilegeType_Name,
  PP.PersonPrivilege_begDate,
  PP.PersonPrivilege_endDate,
  PP.Diag_id,
  PS.Server_pid,
  PS.Polis_id,
  PT.WhsDocumentCostItemType_id,
  PS.Person_IsRefuse,
  case when PS.Person_IsFedLgot = 1 then 1 else 0 end as Person_IsFedLgot,
  case when PS.Person_IsRegLgot = 1 then 1 else 0 end as Person_IsRegLgot
from dbo.PersonPrivilege PP
inner join dbo.v_PrivilegeType PT on PT.PrivilegeType_id = PP.PrivilegeType_id
  and PP.PersonPrivilege_begDate < (current_date + 1)::date
  and coalesce(PP.PersonPrivilege_endDate,(current_date + 1)::date) >= (current_date + 1)::date
  and coalesce(PP.PersonPrivilege_deleted, 1) = 1
inner join dbo.v_PersonState_all PS  on PP.Person_id = PS.Person_id
left join dbo.v_PersonCard_all PC  on PC.Person_id = PP.Person_id
  and PC.LpuAttachType_id = 1
  and PC.PersonCard_begDate < (current_date + 1)::date
)

select
  d.Person_FIO as "ФИО",
  d.Person_BirthDay as "ДР",
  d.PrivilegeType_Code as "Код льготы",
  d.PrivilegeType_Name as "Категория",
  d.PersonPrivilege_begDate as "Начало",
  d.PersonPrivilege_endDate as "Окончание",
  DPP.Diag_Code as "Код заболевания",

  CASE WHEN Pol.Polis_endDate is not null and Pol.Polis_endDate <= current_date 
  THEN 'orange'
  ELSE CASE WHEN d.PersonCloseCause_id = 2 and d.Person_closeDT is not null
       THEN 'red'
	   ELSE CASE WHEN d.Server_pid = 0
	          THEN 'true' ELSE 'false' END END END as "БДЗ",

  d.Person_IsFedLgot as "Фед. льг",
  CASE WHEN d.Person_IsRefuse = 1 and PT_WDCIT.WhsDocumentCostItemType_Nick = 'fl' THEN 1 ELSE 0 END as "Отказ на текущий год",
  d.Person_IsRegLgot as "Рег. льг"
from
(
  select *
  from dt 
  where rnum = 1
) d
left join dbo.v_Diag DPP  on DPP.Diag_id = d.Diag_id
left join dbo.v_Lpu LpuPC  on LpuPC.Lpu_id = d.LpuPC_id
left join dbo.Polis Pol  on Pol.Polis_id = d.Polis_id
left join dbo.v_WhsDocumentCostItemType PT_WDCIT  on PT_WDCIT.WhsDocumentCostItemType_id = d.WhsDocumentCostItemType_id
where
  LpuPC.Lpu_id is not null and coalesce(LpuPC.Lpu_IsTest, 1) = 1 or LpuPC.Lpu_id is null and Pol.Polis_id is not null

