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)
           ,@nosologyid uniqueidentifier

    -- Инициализация
    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 @nosologyid = nosologyid
    FROM rds.T_NOSOLOGY n
    WHERE n.PAC_ID = @registerid

    SELECT @Xml =
           (
               SELECT @OperationType AS 'header/TYPE',
                      @nosologyid    AS 'header/ID',
                      (
                          SELECT nos.NosologyID                                 AS ID,
                                 CONVERT(VARCHAR(10), nos.DateOfChange, 104)    AS CHANGE_DATE,
                                 nos.PAC_ID                                     AS PAC_ID,
                                 nos.FirstName                                  AS FIRST_NAME,
                                 nos.LastName                                   AS LAST_NAME,
                                 nos.FatherName                                 AS FATHER_NAME,
                                 CONVERT(VARCHAR(10), nos.Birthday, 104)        AS BIRTHDAY,
                                 nos.BirthdayLastName                           AS BIRTHDAY_LAST_NAME,
                                 eSex.Code                                      AS SEX,
                                 CASE
                                     WHEN RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = ''
                                         THEN NULL
                                     ELSE nos.SNILS
                                     END                                        AS SNILS,
                                 eDoc.Code                                      AS DOC_TYPE,
                                 nos.DocSeries                                  AS DOC_SERIES,
                                 nos.DocNumber                                  AS DOC_NUMBER,
                                 CONVERT(VARCHAR(10), nos.DocIssueDate, 104)    AS DOC_ISSUE_DATE,
                                 nos.DocIssueOrg                                AS DOC_ISSUE_ORG,
                                 nos.OmsSerial                                  AS OMS_SERIAL,
                                 nos.OmsNumber                                  AS OMS_NUMBER,
                                 nos.F002Smo                                    AS SMO,
                                 nos.Adress                                     AS ADRESS,
                                 nos.OKATO                                      AS OKATO,
                                 CONVERT(VARCHAR(10), nos.DateOfInclusion, 104) AS INCLUSION_DATE,
                                 nos.InclusionLPU                               AS INCLUSION_LPU,
                                 nos.ExecutiveAuthority                         AS EXECUTIVE_AUTHORITY,
                                 CONVERT(VARCHAR(10), nos.DateOfDeclusion, 104) AS DECLUSION_DATE,
                                 nos.DeclusionLPU                               AS DECLUSION_LPU,
                                 nos.ExemptionBy178                             AS EX178,
                                 nos.ExemptionBy890                             AS EX890,
                                 nos.SubjectOfRFAccounted                       AS RF_ACCOUNT,
                                 CONVERT(VARCHAR(10), nos.DeathDate, 104)       AS DEATH_DATE,
                                 (
                                     SELECT nb.UniqueNumber                         AS UNIQUE_NUMBER,
                                            nos.NosologyID                          AS NOSOLOGYID,
                                            nb.Mkb                                  AS MKB,
                                            nb.CardStatus                           AS CARD_STATUS,
                                            nb.Head                                 AS HEAD,
                                            nb.Diagnosis                            AS DIAGNOSIS,
                                            nb.Disability                           AS DIASBILITY,
                                            nb.TherapyResistance                    AS THERAPY_RESISTANCE,
                                            b.BenefitCode                           AS BENEFIT_CODE,
                                            CONVERT(VARCHAR(10), nb.DateBegin, 104) AS DATE_BEGIN,
                                            CONVERT(VARCHAR(10), nb.EndDate, 104)   AS END_DATE
                                     FROM RDS.T_NOSOLOGY_BENEFITS nb
                                     JOIN RDS.T_BENEFITS b ON b.BenefitsID = nb.Benefits
                                     WHERE
                                         nb.Nosology = nos.NosologyID
                                     FOR XML PATH('NOSOLOGY_BENEFIT'), ROOT('NOSOLOGY_BENEFITS'), TYPE
                                 )
                          FROM RDS.T_NOSOLOGY nos
                          LEFT JOIN RDS.T_REGION reg ON reg.RegionID = nos.Region
                          LEFT JOIN VCLib.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.EnumID = NOS.Sex
                          LEFT JOIN vclib.T_ENUMS eDoc ON eDoc.EnumTypeID = 105
                              AND eDoc.EnumID = nos.DocumentType
                          WHERE
                              nos.NosologyID = @nosologyid
                          FOR XML PATH ('') , ROOT ('body') , TYPE
                      )
               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



