with RegistryDataTemp as
(
select
      EvnPLDisp.evnpldisp_id as Evn_id,
      --p_Registry_id as Registry_id,
      --row_number() over(order by EvnPLDisp.evnpldisp_id) as RegistryData_RowNum,
      --PersonCard.personcard_code as NumCard,
      EvnPLDisp.evnpldisp_id as Evn_rid,
      PersonState.Person_id,
      /*SocStatus.SocStatus_Code,
      EvnVizitDisp.LpuSection_id,
      LpuSection.LpuSection_Code,
      COALESCE(lsp_ras.LpuSectionProfile_id,LpuSectionProfile.LpuSectionProfile_id) as LpuSectionProfile_id ,
      COALESCE(lsp_ras.LpuSectionProfile_code,LpuSectionProfile.LpuSectionProfile_code) as LpuSectionProfile_Code,
			MedStaffFact.MedStaffFact_id,
      MedPersonal.MedPersonal_id,
      MedPersonal.Person_Snils as MedPersonal_Snils,
      MedPersonal.Person_FIO as MedPersonal_FIO,
      1 as RegistryData_KdFact,
      1 as RegistryData_KdPlan,
      1 as RegistryData_KdPay,
      1 as RegistryData_UKL,
      EvnVizitDisp.Diag_id,
      coalesce(PersonState.UAddress_id, PersonState.PAddress_id,Patron.UAddress_id, Patron.PAddress_id) as Address_id,
      PersonState.Person_Surname,
      PersonState.Person_Firname,
      PersonState.Person_SecName,
      PersonState.Person_Birthday,
      coalesce(Polis.Polis_id,Patron.Polis_id) as Polis_id,
      case when Patron.person_pid is null then Polis.Polis_Ser else Patron.Polis_Ser end as Polis_Ser,
      case
          when Patron.person_pid is null then case when Polis.PolisType_id = 4 then coalesce(PersonState.Person_EdNum,Polis.Polis_Num) else Polis.Polis_Num end
          else Patron.Polis_Num
      end as Polis_Num,
      case when Patron.person_pid is null then Polis.Polis_begDate else Patron.Polis_begDate end as Polis_begDate,
      case when Patron.person_pid is null then Polis.Polis_endDate else Patron.Polis_endDate end as Polis_endDate,
      case when Patron.person_pid is null then Polis.OrgSmo_id else Patron.OrgSmo_id end as OrgSmo_id,
      MedSpecOms.medspecclass_id as MedSpec_id,
      OmsSprTerr.OmsSprTerr_id,
      OmsSprTerr.OmsSprTerr_Code,*/
      EvnPLDisp.evnpldisp_setdate as Evn_setDate,
      EvnPLDisp.evnpldisp_disdate as Evn_disDate--,
      /*EvnPLDisp.evnpldisp_upddt as Evn_updDate,
      PayMedType.paymedtype_id,
      --p_pmUser_id as pmUser_insID,
      --p_pmUser_id as pmUser_updID,
      current_timestamp as RegistryData_insDT,
      current_timestamp as RegistryData_updDT,
      EvnPLDisp.server_id,
      EvnPLDisp.evnclass_id,
      EvnPLDisp.personevn_id,
      EvnPLDisp.evnclass_id as ParentEvnClass_id,
			mesold.mes_id as MesItog_id,*/
      

  from v_EvnPLDisp EvnPLDisp
   --join v_EvnVizitDisp EvnVizitDisp on EvnVizitDisp.EvnVizitDisp_pid = EvnPLDisp.EvnPLDisp_id and EvnVizitDisp.EvnVizitDisp_Index = EvnVizitDisp.EvnVizitDisp_Count - 1

   join lateral ( --посещение терапевта или педиатра
   		select
        	EvnVizitDisp.evnvizitdisp_id,EvnVizitDisp.lpusection_id,EvnVizitDisp.medpersonal_id,EvnVizitDisp.medstafffact_id,EvnVizitDisp.diag_id,EvnUsluga.uslugacomplex_id
        from v_EvnVizitDisp EvnVizitDisp
         join v_EvnUsluga EvnUsluga on EvnVizitDisp.evnvizitdisp_id = EvnUsluga.evnusluga_pid
        where
        	EvnVizitDisp.EvnVizitDisp_pid = EvnPLDisp.EvnPLDisp_id
        order by
        	--EvnUsluga.uslugacomplex_id = any(_uslugacomplex_id_arr) desc,  --приоритетно прием терапевта или педиатра
            EvnVizitDisp.evnvizitdisp_disdate desc, EvnVizitDisp.evnvizitdisp_distime desc nulls last,EvnVizitDisp.evnvizitdisp_id desc
        limit 1
   ) EvnVizitDisp on true
	 left join r12.v_evnmesoldlink evnmes on evnmes.evn_id=EvnPLDisp.evnpldisp_id
	 left join v_mesold mesold on evnmes.mes_id=mesold.mes_id
	 left join r12.marimes mm on mm.marimes_code=mesold.mes_code and  EvnPLDisp.evnpldisp_disdate BETWEEN mm.marimes_begdate AND coalesce(mm.marimes_enddate,'infinity')
	 left join v_LpuSectionProfile lsp_ras on lsp_ras.LpuSectionProfile_code = mm.marimes_msp and lsp_ras.lpusectionprofile_enddt is null
	 
	 left join lateral (
			select mk.marikmu_mspf as vidpom
			from v_UslugaComplex UslugaComplex
			join r12.marikmu mk on mk.marikmu_code=UslugaComplex.uslugacomplex_code
			where UslugaComplex.uslugacomplex_id=EvnVizitDisp.uslugacomplex_id
			limit 1
			) Vidpom on true

   join dbo.EvnClass EvnClass on EvnPLDisp.evnclass_id = EvnClass.evnclass_id
   join v_Person_bdz PersonState on PersonState.PersonEvn_id = EvnPLDisp.PersonEvn_id and PersonState.Server_id = EvnPLDisp.Server_id

   left join SocStatus on SocStatus.SocStatus_id = PersonState.SocStatus_id
   left join LpuSection on LpuSection.LpuSection_id = EvnVizitDisp.LpuSection_id
   left join LpuSectionProfile on LpuSectionProfile.LpuSectionProfile_id = LpuSection.LpuSectionProfile_id

   left join v_MedPersonal MedPersonal on MedPersonal.MedPersonal_id = EvnVizitDisp.MedPersonal_id and MedPersonal.lpu_id = EvnPLDisp.lpu_id
   left join v_MedStaffFact MedStaffFact on EvnVizitDisp.MedStaffFact_id = MedStaffFact.MedStaffFact_id
   left join MedSpecOms on MedSpecOms.MedSpecOms_id = MedStaffFact.MedSpecOms_id
   left join fed.MedSpecClass ms  on  MedSpecOms.MedSpecClass_id = ms.MedSpecClass_id
  --ищем полис на дату закрытия
  left join lateral (
      select b.polis_id,b.person_ednum from dbo.v_person_bdz b
      where
          b.person_id = EvnPLDisp.person_id /*and b.personevn_insdt::date <= EvnPLDisp.evnpldisp_disdate::date*/ and b.polis_id is not null
								and exists (select 1 from v_Polis Polis where Polis.Polis_id = b.polis_id 
									and EvnPLDisp.evnpldisp_disdate::date <@ daterange(Polis.polis_begdate::date,Polis.polis_enddate::date,'[]')
									and Polis.polis_begdate::date<coalesce(Polis.polis_enddate::date,'infinity'))
      order by b.personevn_insdt desc, b.personevn_timestamp desc
      limit 1
  ) as Polis_on_close on true

  left join v_Polis Polis on Polis.Polis_id = coalesce(Polis_on_close.polis_id,PersonState.Polis_id)
														and EvnPLDisp.evnpldisp_disdate::date <@ daterange(Polis.polis_begdate::date,Polis.polis_enddate::date,'[]')
														and Polis.polis_begdate::date<coalesce(Polis.polis_enddate::date,'infinity')

   left join lateral (
       select
          PD.Person_pid,
          PolisPD.Polis_id,
          PolisPD.PolisType_id,
          PolisPD.Polis_Ser,
          case when PolisPD.PolisType_id = 4 then coalesce(Pers.Person_EdNum,PolisPD.Polis_Num) else PolisPD.Polis_Num end as Polis_Num,
					Pers.Person_EdNum,
          PolisPD.Polis_begDate,
          PolisPD.Polis_endDate,
          OrgSmo.OrgSMO_id,
          OmsSprTerr.OMSSprTerr_id,
          --OmsSprTerr.OMSSprTerr_Code,
          OrgSmo.Orgsmo_f002smocod,
          OmsSprTerr.KLRgn_id,
          --OrgSmo.OrgSmo_RegNomC,
          --OrgSmo.OrgSMO_RegNomN,
          Pers.Person_BirthDay,
          Pers.Sex_id,
          Pers.Document_id,
          Pers.PAddress_id,
          Pers.UAddress_id,
          Pers.person_surname,
          Pers.person_firname,
          Pers.person_secname,
          Pers.person_snils
      FROM
          PersonDeputy PD
            join lateral (
              select /*PersonEvn_id, Server_id,*/ Person_BirthDay,Polis_id,Document_id,UAddress_id,PAddress_id,/*SocStatus_id,Job_id,*/Sex_id,Person_SurName,Person_FirName, Person_SecName, Person_EdNum, Person_Snils
              from v_Person_bdz PersonAll
              where PersonAll.Person_id = PD.Person_pid and PersonAll.PersonEvn_insDT <= EvnPLDisp.EvnPLDisp_setDate
              order by PersonAll.PersonEvn_insDT desc
              limit 1
            ) Pers on true
          left join v_Polis PolisPD on PolisPD.Polis_id =Pers.Polis_id
          left join v_OrgSmo OrgSmo on OrgSmo.OrgSmo_id = PolisPD.OrgSmo_id
          left join v_OmsSprTerr OmsSprTerr on OmsSprTerr.OmsSprTerr_id = PolisPD.OmsSprTerr_id
          where
           PD.Person_id = EvnPLDisp.Person_id and Polis.Polis_id is null
           and age(EvnPLDisp.evnpldisp_setdate,PersonState.Person_BirthDay) < '2 months'::interval
           and EvnPLDisp.evnpldisp_setdate::date >= coalesce(PD.persondeputy_begdt::date,EvnPLDisp.evnpldisp_setdate::date)
          limit 1
    ) Patron on true
		left join lateral(
						select priv.privilegetype_vcode::text as code
						from v_personprivilege perspr
						left join v_privilegetype priv on priv.privilegetype_id=perspr.privilegetype_id
						where perspr.person_id=EvnPLDisp.Person_id 
									and EvnPLDisp.evnpldisp_disdate between coalesce(perspr.personprivilege_begdate,'-infinity') and coalesce(perspr.personprivilege_enddate,'infinity')
									and priv.privilegetype_code not in ('81','82','83')
									and case when mod(date_part ('year', age (to_date('31.12.'||to_char(EvnPLDisp.evnpldisp_disdate,'yyyy'),'dd.mm.yyyy'),(PersonState.Person_Birthday)::date))::integer,3)<>0 
											then priv.privilegetype_code in ('10','11','50','150')
											else true end
						order by priv.privilegetype_code limit 1) as kt on true
		left join lateral(
						select priv.privilegetype_vcode::text as code
						from v_personprivilege perspr
						left join v_privilegetype priv on priv.privilegetype_id=perspr.privilegetype_id
						where perspr.person_id=EvnPLDisp.Person_id 
									and priv.privilegetype_code in ('81','82','83')
						order by priv.privilegetype_code limit 1) as c_kat on true

   left join Diag on Diag.Diag_id = EvnVizitDisp.Diag_id
   --left join v_Address_all Addr on Addr.Address_id = coalesce(PersonState.UAddress_id, PersonState.PAddress_id,Patron.UAddress_id, Patron.PAddress_id)

   left join v_OrgSmo OrgSmo on OrgSmo.OrgSmo_id = coalesce(Polis.OrgSMO_id,Patron.OrgSmo_id)
   left join v_OmsSprTerr OmsSprTerr on OmsSprTerr.OmsSprTerr_id = coalesce(Polis.OmsSprTerr_id,Patron.OMSSprTerr_id)
   left join PolisType on PolisType.polistype_id = coalesce(Patron.PolisType_id,Polis.PolisType_id)
	 
	    left join lateral (
    select PersonCard.PersonCard_Code,PersonCard.lpu_id, PersonCard.lpuregion_id from v_PersonCard_all PersonCard 
    where 
      PersonCard.Person_id = EvnPLDisp.Person_id
      and EvnPLDisp.evnpldisp_disdate::date <@ daterange(PersonCard.personcard_begdate::date,PersonCard.personcard_enddate::date,'[]')
			and coalesce(PersonCard.personcard_begdate::date,'infinity')<coalesce(PersonCard.personcard_enddate::date,'infinity')
      and PersonCard.LpuAttachType_id = 1
      and Personcard.PersonCard_Code is not null
     order by PersonCard.personcard_begDate
     limit 1            
    ) Personcard on true
    
    left join Lpu LpuAttach on LpuAttach.lpu_id = PersonCard.lpu_id
		--left join lpuregion on lpuregion.lpuregion_id=PersonCard.lpuregion_id

    left join DispClass on EvnPLDisp.dispclass_id = DispClass.dispclass_id

    left join lateral (select PayMedType.paymedtype_id from v_PayMedType PayMedType where PayMedType.paymedtype_code = 29 order by PayMedType.paymedtype_id limit 1) as PayMedType on true
    
    left join lateral 
			 (select  uclink.uslugacomplex_id,
				uc.uslugacomplex_code
			  from r12.uslugasurveylink uclink
			  inner join UslugaComplex uc on uclink.uslugacomplex_id = uc.uslugacomplex_id
			  where
			  PersonState.sex_id = uclink.sex_id
			  and (coalesce(EvnPLDisp.person_age,dbo.AgeYMD(PersonState.Person_Birthday,EvnPLDisp.evnpldisp_disdate,1))::int4 between uclink.uslugasurveylink_from and uclink.uslugasurveylink_to
			    or uclink.uslugasurveylink_from is null and uclink.uslugasurveylink_to is null)
			  and EvnPLDisp.dispclass_id = uclink.dispclass_id
			  and (uclink.uslugasurveylink_enddate is null 
			  	or uclink.uslugasurveylink_enddate>=EvnPLDisp.evnpldisp_setdate)
			  and case when EvnPLDisp.evnpldisp_ismobile = 2 then uc.uslugacomplex_name like '%ММБ%' 
			  		   else true
			  	  end
			  limit 1
			 ) surv_usl on true
			 
  where
      EvnPLDisp.lpu_id = 2222030
      and EvnPLDisp.evnpldisp_disdate between '2022-03-01' and '2022-03-31'
			and EvnPLDisp.evnpldisp_disdate>='2021-10-01'
      and EvnPLDisp.evnpldisp_isfinish = 2

)
--select * from RegistryDataTemp
select
    rd.evn_id,
    --rd.evn_id,
    --rd.Registry_id,
    EvnUsluga.EvnUsluga_id,
		count(EvnUsluga.EvnUsluga_id)
    /*EvnUsluga.UslugaComplex_id,
    NULL::bigint AS UslugaComplexTariff_id,
    NULL::bigint as RegistryUsluga_Tariff,
    NULL::bigint as RegistryUsluga_Sum,
    coalesce(EvnUsluga.evnusluga_kolvo,1) as RegistryUsluga_Kol,
    EvnUsluga.MedStaffFact_id,
    coalesce(EvnUsluga.diag_id,rd.diag_id) as Diag_id,
    coalesce(EvnUsluga.LpuSectionProfile_id,rd.LpuSectionProfile_id) as LpuSectionProfile_id,
    coalesce(EvnUsluga.LpuSection_uid,rd.LpuSection_id) as LpuSection_id,
    EvnUsluga.EvnUsluga_setdate,
    --p_pmuser_id as pmUser_insID,
    --p_pmuser_id as pmUser_updID,
    now(),
    now(),
     coalesce(EvnUsluga.EvnUsluga_setdate,EvnUsluga.evnusluga_diddate,rd.evn_setdate) as EvnUsluga_setDate,
    UslugaComplex.uslugacomplex_code as RegistryUsluga_CodeUsl,*/
		--rd.Number,

from RegistryDataTemp rd 
 inner join lateral
			( select eu.evnusluga_id as EvnUsluga_id,
			  eu.evnusluga_kolvo,
			  eu.MedStaffFact_id,
			  eu.UslugaComplex_id,
			  eu.diag_id,
				eu.evnusluga_count as evn_count, 
				1 as evn_deleted,
			  eu.LpuSectionProfile_id,
			  eu.LpuSection_uid,
			   coalesce (evnvizitdisp.evnvizitdisp_setdate  ,eu.evnusluga_setdt) as EvnUsluga_setdate,
			  eu.evnusluga_diddt as evnusluga_diddate,
			  ROW_NUMBER() over (partition by eu.evnusluga_rid,eu.uslugacomplex_id) as usl_order
			from v_EvnUsluga eu
			left join v_evnvizitdisp evnvizitdisp on eu.evnusluga_pid = evnvizitdisp.evnvizitdisp_id
			where 
			rd.Evn_id = eu.evnusluga_rid
			) EvnUsluga on true
 join UslugaComplex on EvnUsluga.UslugaComplex_id = UslugaComplex.uslugacomplex_id
 join r12.marikmu mk on mk.marikmu_code=UslugaComplex.uslugacomplex_code and rd.evn_disdate between coalesce(mk.marikmu_begdate,'-infinity') AND coalesce(mk.marikmu_enddate,'infinity')
 --join dbo.lpusection ls on ls.lpusection_id=coalesce(EvnUsluga.LpuSection_uid,rd.LpuSection_id)  --временное условие, падает счет 959 у Сернурской
where 
	/*rd.Registry_id = p_registry_id
	and*/ EvnUsluga.usl_order = 1
	group by 1,2
	ORDER BY 3
