IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.sp_VZN_Direction') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.sp_VZN_Direction
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 
-- Description: Загрузка информации по направлениям в регистр ВЗН
-- =============================================
CREATE PROCEDURE dbo.sp_VZN_Direction
    @Xml xml,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null,
	@Service bit = 0
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@OperationType varchar(6)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier
           ,@RegisterID int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@DirectionType int
		   ,@Snils varchar(14)
		   ,@oid varchar(max)

    -- Инициализация
    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'

    -- Получаем тип операции
    select @RegisterID = T.c.value('./RegisterID[1]', 'int')
			,@DirectionType = T.c.value('./ChangeType[1]', 'int')
			,@ContentEncoding = 'utf-8'
			,@Type = 'NOSOLOGY_DIRECTION'
			,@Snils = t.c.value('./SNILS[1]', 'varchar(14)')
			,@oid = mdr.oid
    from @Xml.nodes('NOSOLOGY_DIRECTION') T(c)
	left join RDS.T_LPU_MAIN lpu on lpu.MCOD = t.c.value('./LPU[1]', 'varchar(6)')	
	left join EHR.T_EHR_RUS_MDR308 mdr on mdr.EhrRusID = lpu.EhrRusMdr308Dop

    -- Generate table #NOSOLOGY
        select 
			   t.c.value('./ID[1]', 'uniqueidentifier')										as ID
              ,t.c.value('./LPU[1]', 'varchar(6)')											as Lpu
              ,convert(datetime, t.c.value('./EnterDate[1]', 'varchar(10)'), 104)			as EnterDate
              ,t.c.value('./LastName[1]', 'varchar(40)')									as LastName
              ,t.c.value('./FirstName[1]', 'varchar(40)')									as FirstName
              ,t.c.value('./FatherName[1]', 'varchar(40)')									as FatherName
			  ,T.c.value('./BirthSurname[1]', 'varchar(40)')								as BirthSurname
              ,convert(datetime, t.c.value('./Birthday[1]', 'varchar(10)'), 104)			as Birthday
              ,convert(datetime, t.c.value('./Date[1]', 'varchar(10)'), 104)				as Date
              ,t.c.value('./Sex[1]', 'int')													as Sex
              ,t.c.value('./Kod[1]', 'varchar(32)')											as Kod
              ,t.c.value('./DocSeries[1]', 'varchar(16)')									as DocSeries
              ,t.c.value('./DocNumber[1]', 'varchar(16)')									as DocNumber
              ,t.c.value('./DocType[1]', 'int')												as DocType
			  ,t.c.value('./RegisterID[1]', 'int')											as RegisterID
              ,t.c.value('./SNILS[1]', 'varchar(14)')										as Snils
              ,t.c.value('./ChangeType[1]', 'int')											as ChangeType
              ,t.c.value('./ExcludeReason[1]', 'int')										as ExcludeReason
			  ,convert(datetime, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 104)		as DocIssueDate
			  ,T.c.value('./DocIssueOrg[1]', 'varchar(256)')								as DocIssueOrg
			  ,T.c.value('./Enp[1]', 'varchar(16)')											as Enp
			  ,T.c.value('./Region[1]', 'varchar(13)')										as Region
			  ,T.c.value('./RealFiasAddress[1]', 'varchar(36)')								as RealFiasAddress
			  ,T.c.value('./RealFiasHome[1]', 'varchar(36)')								as RealFiasHome
			  ,T.c.value('./RealCity[1]', 'varchar(100)')									as RealCity
			  ,T.c.value('./RealCityPrefix[1]', 'varchar(10)')								as RealCityPrefix
			  ,T.c.value('./RealStreet[1]', 'varchar(100)')									as RealStreet
			  ,T.c.value('./RealStreetPrefix[1]', 'varchar(10)')							as RealStreetPrefix
			  ,T.c.value('./RealHouse[1]', 'varchar(100)')									as RealHouse
			  ,T.c.value('./RealBuild[1]', 'varchar(100)')									as RealBuild
			  ,T.c.value('./RealStruc[1]', 'varchar(100)')									as RealStruc
			  ,T.c.value('./RealRoom[1]', 'varchar(100)')									as RealRoom
			  ,T.c.value('./RealAOIDArea[1]', 'varchar(36)')								as RealAOIDArea
			  ,convert(datetime, T.c.value('./RealLiveStartDate[1]', 'varchar(10)'), 104)	as RealLiveStartDate
			  ,convert(datetime, T.c.value('./RealLiveEndDate[1]', 'varchar(10)'), 104)		as RealLiveEndDate
			  ,T.c.value('./LegalFiasAddress[1]', 'varchar(36)')							as LegalFiasAddress
			  ,T.c.value('./LegalFiasHome[1]', 'varchar(36)')								as LegalFiasHome
			  ,T.c.value('./LegalCity[1]', 'varchar(100)')									as LegalCity
			  ,T.c.value('./LegalCityPrefix[1]', 'varchar(10)')								as LegalCityPrefix
			  ,T.c.value('./LegalStreet[1]', 'varchar(100)')								as LegalStreet
			  ,T.c.value('./LegalStreetPrefix[1]', 'varchar(10)')							as LegalStreetPrefix
			  ,T.c.value('./LegalHouse[1]', 'varchar(100)')									as LegalHouse
			  ,T.c.value('./LegalBuild[1]', 'varchar(100)')									as LegalBuild
			  ,T.c.value('./LegalStruc[1]', 'varchar(100)')									as LegalStruc
			  ,T.c.value('./LegalRoom[1]', 'varchar(100)')									as LegalRoom
			  ,T.c.value('./LegalAOIDArea[1]', 'varchar(36)')								as LegalAOIDArea
			  ,convert(datetime, T.c.value('./LegalLiveStartDate[1]', 'varchar(10)'), 104)	as LegalLiveStartDate
			  ,convert(datetime, T.c.value('./LegalLiveEndDate[1]', 'varchar(10)'), 104)	as LegalLiveEndDate
			  ,T.c.value('./CitizenShip[1]', 'varchar(3)')									as CitizenShip
			  ,T.c.value('./PolicySerial[1]', 'varchar(12)')								as PolicySerial
			  ,T.c.value('./PolicyNumber[1]', 'varchar(32)')								as PolicyNumber
			  ,T.c.value('./SmoCod[1]', 'varchar(5)')										as SmoCod
			  ,T.c.value('./PolicyForm[1]', 'int')											as PolicyForm
			  ,T.c.value('./WorkPlace[1]', 'varchar(500)')									as WorkPlace
			  ,T.c.value('./WorkPost[1]', 'varchar(500)')									as WorkPost
			  ,T.c.value('./RationaleForDirection[1]', 'varchar(1000)')						as RationaleForDirection
			  ,T.c.value('./Number[1]', 'int')												as Number
			  ,T.c.value('./FedReg[1]', 'bit')												as FedReg
			  ,T.c.value('./FedRegCategoria[1]', 'int')										as FedRegCategoria
			  ,T.c.value('./LLOReg[1]', 'bit')												as LLOReg
			  ,T.c.value('./Invalidity[1]', 'int')											as Invalidity
			  ,T.c.value('./PostFIO[1]', 'varchar(255)')									as PostFIO
			  ,T.c.value('./HeadPostFIO[1]', 'varchar(255)')								as HeadPostFIO
			  ,T.c.value('./McPostFIO[1]', 'varchar(255)')									as McPostFIO
            INTO #NOSOLOGY_DIRECTION
        from @Xml.nodes('NOSOLOGY_DIRECTION') T(c)
		
        -- Generate table #NOSOLOGY_BENEFITS
        select t.c.value('./DirectionID[1]', 'uniqueidentifier')						as DirectionID
              ,convert(datetime, t.c.value('./DiagDate[1]', 'varchar(10)'), 104)		as DiagDate
              ,t.c.value('./Document[1]', 'varchar(16)')								as Document
              ,convert(datetime, t.c.value('./DocumentDate[1]', 'varchar(10)'), 104)	as DocumentDate
              ,t.c.value('./Mkb[1]', 'varchar(8)')										as Mkb
              ,t.c.value('./BenefitCode[1]', 'varchar(255)')							as BenefitCode
              ,t.c.value('./BenefitViews[1]', 'int')									as BenefitViews
              ,t.c.value('./PostCode[1]', 'varchar(255)')								as PostCode
              ,t.c.value('./ChangeType[1]', 'int')										as ChangeType
            INTO #NOSOLOGY_BENEFITS
        from @Xml.nodes('NOSOLOGY_DIRECTION/NOSOLOGY_DIRECTION_BENEFIT') T(c)
		
    -- Поиск по СНИЛС
    update rd
    set 
		rd.RegisterID = r.PAC_ID
       ,@RegisterID = r.PAC_ID
	   ,@OwnerID = r.NosologyID
        from #NOSOLOGY_DIRECTION rd
		join RDS.T_NOSOLOGY r on r.SNILS = rd.Snils
    where 
		rd.RegisterID is null
        and r.SNILS is not null
        and r.SNILS <> ''
        and r.SNILS <> '              '

    -- Генерируем новый идентификатор
    IF (@RegisterID is null)
    BEGIN
        exec @RegisterID = dbo.sp_GenerateIdForEntity 'NOSOLOGY_REGISTER', 'PrimaryKey', 1
        update rd
			set rd.RegisterID = @RegisterID
        from #NOSOLOGY_DIRECTION rd

		set @OwnerID = newid()

		IF(@RegisterID = 0)
		raiserror ('Не верно сгенерирован идентификатор льготника в регистре!', 16, 10)
    END

    update rp
    set 
     rp.DateOfChange = d.EnterDate
    ,rp.OmsNumber = d.PolicyNumber
    ,rp.OmsSerial = d.PolicySerial
    ,rp.DocSeries = d.DocSeries
    ,rp.DocNumber = d.DocNumber
    ,rp.Birthday = d.Birthday
    ,rp.FirstName = d.FirstName
    ,rp.LastName = d.LastName
    ,rp.FatherName = d.FatherName
    ,rp.DocIssueDate = d.DocIssueDate
    ,rp.DocIssueOrg = d.DocIssueOrg
    ,rp.DocumentType = eDoc.EnumID
    ,rp.ExemptionBy178 = d.FedReg --?? Льгота по 178-ФЗ
    ,rp.ExemptionBy890 = d.LLOReg  --?? Льгота по 890-ПП
    ,rp.InclusionLPU = d.Lpu
    ,rp.Sex = eSex.EnumID
    ,rp.SNILS = d.SNILS
    ,rp.BirthdayLastName = d.BirthSurname
    ,rp.F002Smo = d.SmoCod
	,rp.Region = region.RegionID
    ,rp.FiasAddress = d.RealAOIDArea
    ,rp.FiasStreet = d.RealFiasAddress
    ,rp.FiasHome = d.RealFiasHome
    ,rp.City = d.RealCity
    ,rp.CityPrefix = d.RealCityPrefix
    ,rp.Street = d.RealStreet
    ,rp.StreetPrefix = d.RealStreetPrefix
    ,rp.House = d.RealHouse
    ,rp.WorkPlace = d.WorkPlace
    ,rp.Post = d.PostFIO
    ,rp.MedicalCommissionPost = d.McPostFIO
    ,rp.WorkPost = d.WorkPost
    ,rp.RationaleForDirection = d.RationaleForDirection
    ,rp.FedRegCategoria = d.FedRegCategoria
    ,rp.PolicyForm = d.PolicyForm
    ,rp.CitizenShip = cs.EhrRusID
	,@OwnerID = rp.NosologyID
    from RDS.T_NOSOLOGY rp
        join #NOSOLOGY_DIRECTION d on d.RegisterID = rp.PAC_ID
		join RDS.T_REGION region on d.Region = region.Code
        join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = d.Sex
        join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = d.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = d.CitizenShip
    where d.ChangeType = 2

	update rp
    set rp.DateOfDeclusion = p.EnterDate
	   ,rp.ExcludeReason = p.ExcludeReason
	   ,rp.DeclusionLPU = p.Lpu
	   --,rp.DeathDate = (case when p.ExcludeReason = 1 then p.date else null end)
    from RDS.T_NOSOLOGY rp
        join #NOSOLOGY_DIRECTION p on p.RegisterID = rp.PAC_ID
    where p.ChangeType = 3
    
	update brd
    set brd.EndDate = @dateTime
    from RDS.T_NOSOLOGY_BENEFITS brd
		join RDS.T_NOSOLOGY rp on rp.NosologyID = brd.Nosology
        join #NOSOLOGY_DIRECTION p on p.RegisterID = rp.PAC_ID
    where p.ChangeType = 3 and (brd.EndDate is null or brd.EndDate > @dateTime)

	INSERT INTO RDS.T_NOSOLOGY
           (NosologyID
           ,Adress
           ,DateOfChange
           ,DateOfInclusion
           ,OmsNumber
           ,OmsSerial
           ,DocSeries
           ,DocNumber
           ,Birthday
           ,FirstName
           ,LastName
           ,FatherName
           ,DocIssueDate
           ,DocIssueOrg
           ,DocumentType
           ,ExemptionBy178
           ,ExemptionBy890
           ,InclusionLPU
           ,Region
           ,Sex
           ,SubjectOfRFAccounted
           ,SNILS
           ,BirthdayLastName
           ,F002Smo
           ,FiasAddress
           ,FiasStreet
           ,FiasHome
           ,City
           ,CityPrefix
           ,Street
           ,StreetPrefix
           ,House
           ,WorkPlace
           ,WorkPost
           ,Comment
           ,FedRegCategoria
           ,PolicyForm
           ,CitizenShip
           ,PAC_ID)
	select 
		 @OwnerID
		,''
		,@dateTime
		,d.EnterDate
		,d.PolicyNumber
		,d.PolicySerial
		,d.DocSeries
		,d.DocNumber
		,d.Birthday
		,d.FirstName
		,d.LastName
		,d.FatherName
		,d.DocIssueDate
		,d.DocIssueOrg
		,eDoc.EnumID
		,d.FedReg
		,d.LLOReg
		,d.Lpu
		,null
		,eSex.EnumID
		,0
		,d.SNILS
		,d.BirthSurname
		,d.SmoCod
		,d.RealAOIDArea
		,d.RealFiasAddress
		,d.RealFiasHome
		,d.RealCity
		,d.RealCityPrefix
		,d.RealStreet
		,d.RealStreetPrefix
		,d.RealHouse
		,d.WorkPlace
		,d.WorkPost
		,d.RationaleForDirection
		,d.FedRegCategoria
		,d.PolicyForm
		,cs.EhrRusID
		,@RegisterID
	from #NOSOLOGY_DIRECTION d
        join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = d.Sex
        join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = d.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = d.CitizenShip
	where d.ChangeType = 1

	INSERT INTO RDS.T_NOSOLOGY_BENEFITS
	           ([NosologyBenefitsID]
				 ,[Nosology]
				 ,[Benefits]
				 ,[Mkb]
				 ,[Post]
				 ,[Head]
				 ,[MedicalCommissionPost]
				 ,[DateBegin]
				 ,[EhrRusP360COA3OB]
				 ,[Disability]
				 ,[TherapyResistance]
				 ,[EndDate])
	 select		
			 newid()
			,r.NosologyID
			,b.BenefitsID
			,Mkb
			,d.PostFIO
			,d.HeadPostFIO
			,d.McPostFIO
			,@dateTime
			,(select er.EhrRusID 
					from EHR.T_EHR_RUS_P360COA3OB er
					WHERE er.IdEHR = d.Invalidity
						)
			,(case when d.Invalidity is not null then 1 else 0 end)
			,0
			,null
	from #NOSOLOGY_BENEFITS bd
		join #NOSOLOGY_DIRECTION d on d.ID = bd.DirectionID
		join RDS.T_NOSOLOGY r on r.PAC_ID = @RegisterID
		left join VCLib.T_ENUMS e on e.EnumTypeID = 190 and e.Code = bd.BenefitViews
		join RDS.T_BENEFITS b on b.BenefitCode = bd.BenefitCode  and b.BenefitViews = e.EnumID
	where bd.ChangeType = 1

    update rpb
    set rpb.EndDate = @dateTime
    from #NOSOLOGY_BENEFITS rdb
		join RDS.T_NOSOLOGY r on r.PAC_ID = @RegisterID
		left join VCLib.T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		left join RDS.T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
        join RDS.T_NOSOLOGY_BENEFITS rpb on rpb.Nosology = r.NosologyID and rpb.Benefits = b.BenefitsID
    where rdb.ChangeType = 3 and (rpb.EndDate is null or rpb.EndDate > @dateTime)
	
	-- собираем ответ в АКУЗ, NOSOLOGY_DIRECTION
    SELECT @Result = 
    ( 
        select 
			 rd.ID           as 'ID'
			,rd.RegisterID   as 'RegisterID'
		from #NOSOLOGY_DIRECTION rd
		for xml path(''),
		root('NOSOLOGY_DIRECTION')
    ) 

	-- собираем пакет в апс
	select @Xml = 
	(
                select @OperationType								as 'header/TYPE'
			,(
              select nos.NosologyID										as 'NOSOLOGY_ITEM/ID'
					,nos.Adress											as 'NOSOLOGY_ITEM/ADRESS'
					,nb.CardStatus										as 'NOSOLOGY_ITEM/CARD_STATUS'
					,nos.Comment										as 'NOSOLOGY_ITEM/COMMENT'
					,convert(varchar(10),nos.DateOfChange, 104)			as 'NOSOLOGY_ITEM/CHANGE_DATE'
					,convert(varchar(10),nos.DateOfDeclusion, 104)		as 'NOSOLOGY_ITEM/DECLUSION_DATE'
					,convert(varchar(10),nos.DateOfInclusion, 104)		as 'NOSOLOGY_ITEM/INCLUSION_DATE'
					,nos.ExecutiveAuthority								as 'NOSOLOGY_ITEM/EXECUTIVE_AUTHORITY'
					,nb.Head											as 'NOSOLOGY_ITEM/HEAD'
					,nos.OmsNumber										as 'NOSOLOGY_ITEM/OMS_NUMBER'
					,nos.OmsSerial										as 'NOSOLOGY_ITEM/OMS_SERIAL'
					,nos.OperationCode									as 'NOSOLOGY_ITEM/OPERATION_CODE'
					,nos.PAC_ID											as 'NOSOLOGY_ITEM/UNIQUE_NUMBER'
					,nb.Mkb												as 'NOSOLOGY_ITEM/MKB'
					,nb.Diagnosis										as 'NOSOLOGY_ITEM/DIAGNOSIS'
					,nos.DocSeries										as 'NOSOLOGY_ITEM/DOC_SERIES'
					,nos.DocNumber										as 'NOSOLOGY_ITEM/DOC_NUMBER'
					,convert(varchar(10), nos.Birthday, 104)			as 'NOSOLOGY_ITEM/BIRTHDAY'
					,nos.FirstName										as 'NOSOLOGY_ITEM/FIRST_NAME'
					,nos.LastName										as 'NOSOLOGY_ITEM/LAST_MANE'
					,nos.FatherName										as 'NOSOLOGY_ITEM/FATHER_NAME'
					,convert(varchar(10), nos.DocIssueDate, 104)		as 'NOSOLOGY_ITEM/DOC_ISSUE_DATE'
					,nos.DocIssueOrg									as 'NOSOLOGY_ITEM/DOC_ISSUE_ORG'
					,nos.DeclusionLPU									as 'NOSOLOGY_ITEM/DECLUSION_LPU'
					,nos.DocumentType									as 'NOSOLOGY_ITEM/DOC_TYPE'
					,nos.ExemptionBy178									as 'NOSOLOGY_ITEM/EX178'
					,nos.ExemptionBy890									as 'NOSOLOGY_ITEM/EX890'
					,nos.InclusionLPU									as 'NOSOLOGY_ITEM/INCLUSION_LPU'
					,reg.Code											as 'NOSOLOGY_ITEM/REGION'
					,nos.Sex											as 'NOSOLOGY_ITEM/SEX'
					,nos.SubjectOfRFAccounted							as 'NOSOLOGY_ITEM/RF_ACCOUNT'
					,nos.BirthdayLastName								as 'NOSOLOGY_ITEM/BIRTHDAY_LAST_NAME'
					,nb.Disability										as 'NOSOLOGY_ITEM/DIASBILITY'
					,nb.TherapyResistance								as 'NOSOLOGY_ITEM/THERAPY_RESISTANCE'
					,convert(varchar(10), nos.DeathDate, 104)			as 'NOSOLOGY_ITEM/DEAT_DATE'
					,nos.OKATO											as 'NOSOLOGY_ITEM/OKATO'
					,nos.F002Smo										as 'NOSOLOGY_ITEM/SMO'
					,case 
						when RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = '' then null
						else nos.SNILS 
					 end												as 'NOSOLOGY_ITEM/SNILS'
					,b.BenefitCode										as 'NOSOLOGY_ITEM/BENIFITS'
			from RDS.T_NOSOLOGY_BENEFITS nb
			join RDS.T_NOSOLOGY nos on nb.Nosology = nos.NosologyID
				left join RDS.T_REGION reg on reg.RegionID = nos.Region
				join RDS.T_BENEFITS b on b.BenefitsID = nb.Benefits
			where nos.PAC_ID = @RegisterID
			for xml path(''),
                        type
                  ) as 'body/NOSOLOGY_ITEMS'
            for xml path(''),
            root('NOSOLOGY')
            )

	INSERT INTO RDS.T_RABBIT_SEND_OBJECT
		    (RabbitSendObjectID ,QueueName ,Content ,MessageID ,Headers ,ContentEncoding ,Type ,UserID ,State ,CreateDateTime ,OwnerID)
		select 
		       NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		      ,s.ServerName--,<QueueName, varchar(255),>
		      ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
		      ,null--,<MessageID, varchar(255),>
		      ,null--,<Headers, varchar(255),>
		      ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		      ,@Type--,<Type, varchar(255),>
		      ,@User--,<UserID, varchar(255),>
		      ,0--,<State, int,>)
		      ,@dateTime--,<CreateDateTime, datetime,>)
		      ,@OwnerID--,<OwnerID, varchar(255),>
		from RDS.T_SYNC_SERVERS s
		where 
			s.ServerName = @QueueName
		    and s.ServerType = 0 -- AKUZ
		    and @Result is not null
		-- Рассылаем пакет всем подписанным серверам APS
		union all select 
		       NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		      ,s.ServerName--,<QueueName, varchar(255),>
		      ,dbo.ConvertTextToBinaryZip(cast(@Xml as varchar(max)), '')--,<Content, text,>
		      ,null--,<MessageID, varchar(255),>
		      ,null--,<Headers, varchar(255),>
		      ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		      ,'NOSOLOGY'--,<Type, varchar(255),>
		      ,@User--,<UserID, varchar(255),>
		      ,0--,<State, int,>)
		      ,@dateTime--,<CreateDateTime, datetime,>)
		      ,@OwnerID--,<OwnerID, varchar(255),>
		from RDS.T_SYNC_SERVERS s
		where s.ServerType = 1 -- APS

	update e
		set id = (select MAX(PAC_ID) + 1 from RDS.T_NOSOLOGY)
	from dbo.T_ENTITY_IDS e
	where e.EntName in ('NOSOLOGY_REGISTER')
    
	
	if (@Service = 1)
	begin

		if(@DirectionType = 1)
		begin 
			exec sp_CreateVZNServiceMessage 'patient.create', @OwnerID, @Snils, @oid
			--WAITFOR DELAY '00:00:00.500';
			--exec sp_CreateVZNServiceMessage 'patient_document.create', @OwnerID, @Snils, @oid 
		end
		else if(@DirectionType = 2)
		begin 
			exec sp_CreateVZNServiceMessage 'patient.update', @OwnerID, @Snils, @oid
			--WAITFOR DELAY '00:00:00.500';
			--exec sp_CreateVZNServiceMessage 'patient_document.update', @OwnerID, @Snils, @oid
		end
		else if(@DirectionType = 3) -- закрытие льготы
		begin 
			DECLARE @bemId1 uniqueidentifier

			DECLARE CURSOR_TT CURSOR FOR
				select rpb.NosologyBenefitsID 
				from RDS.T_NOSOLOGY_BENEFITS rpb
					left join RDS.T_NOSOLOGY r on r.NosologyID = rpb.Nosology
				where  r.PAC_ID = @RegisterID and r.SNILS = @Snils and rpb.EndDate = @dateTime
			OPEN CURSOR_TT
			FETCH NEXT FROM CURSOR_TT INTO @bemId1
			WHILE @@fetch_status = 0 
		
			BEGIN
		
				exec sp_CreateVZNServiceMessage 'vzn.update', @bemId1, @Snils, @oid
				WAITFOR DELAY '00:00:00.500';

			FETCH NEXT FROM CURSOR_TT INTO @bemId1  
		
			END
			CLOSE CURSOR_TT
			DEALLOCATE CURSOR_TT
		end

	if (@DirectionType <> 3 and exists(select * from #NOSOLOGY_BENEFITS d where d.DirectionID is not null)) -- обновление или создание льготы
	begin
		DECLARE @benType varchar(100), @bemId uniqueidentifier

		DECLARE CURSOR_TT CURSOR FOR
			select case when rdb.ChangeType = 1 then 'vzn.create' else 'vzn.update' end, rpb.NosologyBenefitsID 
			from #NOSOLOGY_BENEFITS rdb
				join RDS.T_NOSOLOGY r on r.PAC_ID = @RegisterID
			    join RDS.T_NOSOLOGY_BENEFITS rpb on rpb.Nosology = r.NosologyID
				left join VCLib.T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
				left join RDS.T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
			where rpb.Benefits = b.BenefitsID and  ((rdb.ChangeType = 3 and rpb.EndDate = @dateTime) or (rdb.ChangeType = 1 and (rpb.DateBegin = @dateTime)))
		
		OPEN CURSOR_TT
		FETCH NEXT FROM CURSOR_TT INTO @benType, @bemId
		WHILE @@fetch_status = 0 
		
		BEGIN
		
			exec sp_CreateVZNServiceMessage @benType, @bemId, @Snils, @oid
			WAITFOR DELAY '00:00:00.500';

		FETCH NEXT FROM CURSOR_TT INTO @benType, @bemId  
		
		END
		CLOSE CURSOR_TT
		DEALLOCATE CURSOR_TT
	end

	end

	-- Drop tables

    DROP TABLE #NOSOLOGY_DIRECTION
    DROP TABLE #NOSOLOGY_BENEFITS

END



