select
  EvnPL.EvnPL_id ,
 -- EvnPL.Lpu_id,
  EvnPL.EvnPL_setDate,
  EvnPL.EvnPL_disDate,
  EvnPL.EvnPL_NumCard,
  EvnPL.EvnPL_UKL,
  EvnPL.evnclass_id,
  EvnVizit.PersonEvn_id ,
  EvnVizit.Server_id ,
  EvnVizit.EvnVizitPL_id ,
  --ResultClass.ResultClass_id,
  LpuSection.LpuSection_id,
  coalesce(EvnVizit.LpuSectionProfile_id, LpuSection.LpuSectionProfile_id) as LpuSectionProfile_id,
  DiagViz.Diag_id,
  DiagViz.Diag_code,
  DiagStom.diag_id,
  DiagStom.diag_code

from
  tmp.tTempEvnPL00 as EvnPL
     
  inner join v_EvnVizitPL as EvnVizit on EvnVizit.EvnVizitPL_pid = EvnPL.EvnPL_id
    and (EvnVizit.EvnVizitPL_Index = EvnVizit.EvnVizitPL_Count - 1
        or EvnVizit.EvnClass_id = 13)  -- по стомат берем все посещения

  inner join ResultClass on ResultClass.ResultClass_id = EvnPL.ResultClass_id

--  inner join Lpu on Lpu.Lpu_id = EvnPL.Lpu_id
  inner join LpuSection on LpuSection.LpuSection_id = EvnVizit.LpuSection_id
        and case
            when '{}'::bigint[] = '{}'::bigint[] then true
              else LpuSection.lpusection_id = any('{}'::bigint[])
          end
  inner join v_LpuUnit LpuUnit on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
  --inner join LpuSectionProfile on LpuSectionProfile.LpuSectionProfile_id = coalesce(EvnVizit.LpuSectionProfile_id, LpuSection.LpuSectionProfile_id)

  left join Paytype on Paytype.PayType_id = EvnVizit.PayType_id
  left join Diag DiagViz on DiagViz.Diag_id = coalesce(EvnPL.Diag_id, EvnVizit.Diag_id)

  left join lateral (
    select
        S.Diag_id,
        D.Diag_Code
      from 
        v_EvnDiagPLStom S
        inner join Diag D on D.Diag_id = S.Diag_id
      where 
        S.EvnDiagPLStom_rid = EvnPL.EvnPL_id
      order by S.EvnDiagPLStom_id desc
      limit 1
  ) DiagStom on true

  left join lateral (
    select
        ds.evnDiagPLStom_iszno as zno
      from 
        v_EvnDiagPLStom ds  
      where 
        ds.EvnDiagPLStom_rid  = EvnPL.EvnPL_id
      limit 1
  ) DiagStom_zno on true  

  --TAS000001155364
  left join lateral (
    select 
     count(case when rt.registryerrortype_code not in ('62','212','213','89100') then 1 end) as cnt,
     count(case when rt.registryerrortype_code in ('62','212','213') then 1 end) as cnt62
    from
     r89.registryerrortfoms re
     inner join r89.registryerrortype rt on rt.registryerrortype_id = re.registryerrortype_id
    where
       re.evn_id = EvnVizit.evnvizitpl_id
    ) as sank on true 




where 1 = 1

  
  and 1 = 
    case when EvnPL.EvnPL_disDate between '2022-03-01 00:00:00'::date and '2022-03-31 00:00:00'::date    and coalesce(1,1) = 1
            then 1
         when EvnPL.EvnPL_disDate between '2022-02-20 00:00:00'::date and '2022-03-01 00:00:00'::date  -- INC000014329059
                 and coalesce(1,1) = 1
                 and ResultClass.ResultClass_code = '302' --302 Лечение прервано по инициативе пациента
                 and EvnVizit.evnvizitpl_ispaid is null
            then 1
         when EvnPL.EvnPL_disDate between '2022-02-01 00:00:00'::date and '2022-02-28 00:00:00'::date     and coalesce(1,1) = 2 
            then 1
         when EvnPL.EvnPL_disDate between '2022-01-23 00:00:00'::date and '2022-02-01 00:00:00'::date   
                 and coalesce(1,1) = 2
                 and ResultClass.ResultClass_code = '302' --302 Лечение прервано по инициативе пациента
                 and EvnVizit.evnvizitpl_ispaid = 1
            then 1
         else 0
      end   

  and coalesce(EvnVizit.EvnVizitPL_IsInReg,1) = 1   -- #########################

  and 1 = 
    case when coalesce(2,1) = 1 
             and (EvnVizit.evnvizitpl_iszno = 1 or EvnVizit.evnvizitpl_iszno is null)
             and coalesce(DiagStom_zno.zno,1) = 1
             and left(coalesce(DiagStom.Diag_Code, DiagViz.Diag_Code),3) not between 'C00' and 'D09' 
         then 1
         when coalesce(2,1) = 2 
               and 2 = 
                 case when EvnVizit.evnvizitpl_iszno = 2 then 2
                      when DiagStom_zno.zno = 2 then 2
                      when left(coalesce(DiagStom.Diag_Code, DiagViz.Diag_Code),3) between 'C00' and 'D09' then 2
                      else 0
                 end     
            then 1
         else 0
      end   
      
      
  and Paytype.PayType_Code = 1

  and 1 = 
    case when coalesce(1,1) = 1
              and LpuUnit.LpuUnittype_id != 12
           then 1   
         when coalesce(1,1) = 2
              and LpuUnit.LpuUnittype_id = 12
           then 1   
         else 0  
    end


  and 1 = 
    case when coalesce(1,1) = 1
              and EvnVizit.evnvizitpl_ispaid is null
           then 1   
         when coalesce(1,1) = 2 
              and EvnVizit.evnvizitpl_ispaid = 1
              and coalesce(null,1) = 1
              -- and sank.s_osn = '62' 
              and sank.cnt62 > 0 -- TAS000001155364
           then 1   
         when coalesce(1,1) = 2 
              and EvnVizit.evnvizitpl_ispaid = 1
              and coalesce(null,1) = 2
              --and sank.s_osn != '62' 
              and sank.cnt > 0 -- TAS000001155364
            then 1   
         else 0  
    end      

  and 1 = 
    case when coalesce(null,1) = 1
           then 1   
         when EvnVizit.EvnClass_id = 13
           then 1   
         when null = 2 
               and EvnVizit.treatmentclass_id = 2
            then 1   
         else 0  
    end
