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

    SET NOCOUNT ON;

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

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

    -- Получаем тип операции
    select @RegisterID = T.c.value('./RegisterID[1]', 'int')
		  ,@ChangeType = T.c.value('./ChangeType[1]', 'int')
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'BSK_DIRECTION'
    from @Xml.nodes('BSK_DIRECTION') T(c)

		-- Generate table #BSK_REGISTER
        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]
              ,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('./CreateDateTime[1]', 'varchar(19)'), 120)		as [CreateDateTime]
			  ,T.c.value('./BirthSurname[1]', 'varchar(40)')								as [BirthSurname]
			  ,convert(datetime, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 104)		as [DocIssueDate]
			  ,T.c.value('./Enp[1]', 'varchar(16)')											as [Enp]
			  ,T.c.value('./DocIssueOrg[1]', 'varchar(256)')								as [DocIssueOrg]
			  ,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]
			  ,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]
			  ,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]
            INTO #BSK_DIRECTION
        from @Xml.nodes('BSK_DIRECTION') T(c)

        -- Generate table #BSK_BENEFITS
        select t.c.value('./DirectionID[1]', 'uniqueidentifier')							as [DirectionID]
			  ,t.c.value('./DirectionBenID[1]', 'uniqueidentifier')							as [DirectionBenID]
              ,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]
              ,t.c.value('./ServiceCode[1]', 'varchar(20)')									as [ServiceCode]
              ,t.c.value('./ServiceName[1]', 'varchar(255)')								as [ServiceName]
              ,convert(datetime, t.c.value('./DateBegin[1]', 'varchar(10)'), 104)			as [DateBegin]
              ,convert(datetime, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)				as [DateEnd]
              ,t.c.value('./ExclusionReason[1]', 'int')										as [ExclusionReason]
              ,convert(datetime, t.c.value('./EstimatedDDate[1]', 'varchar(10)'), 104)		as [EstimatedDDate]
              ,convert(datetime, t.c.value('./DRegistrationDate[1]', 'varchar(10)'), 104)   as [DRegistrationDate]
              ,t.c.value('./Place[1]', 'int')												as [Place]
              ,t.c.value('./LpuTo[1]', 'varchar(6)')										as [LpuTo]
              ,convert(datetime, t.c.value('./CloseDate[1]', 'varchar(10)'), 104)			as [CloseDate]
              ,t.c.value('./ConcomitantMkb[1]', 'varchar(255)')								as [ConcomitantMkb]
              ,t.c.value('./ContomitantDisease[1]', 'varchar(255)')							as [ContomitantDisease]
            INTO #BSK_BENEFITS
        from @Xml.nodes('BSK_DIRECTION/BSK_DIRECTION_BENEFITS/BSK_DIRECTION_BENEFIT') T(c)

		-- Generate table #BSK_MEDICAMENTS
        select t.c.value('./DirectionBenID[1]', 'uniqueidentifier')					as [DirectionBenID]
              ,t.c.value('./Mnn[1]', 'int')											as [Mnn]
              ,convert(datetime, t.c.value('./DateStart[1]', 'varchar(10)'), 104)	as [DateStart]
              ,convert(datetime, t.c.value('./EndDate[1]', 'varchar(10)'), 104)		as [EndDate]
              ,t.c.value('./MedicationForms[1]', 'uniqueidentifier')				as [MedicationForms]
              ,t.c.value('./Regiment[1]', 'varchar(255)')							as [Regiment]
              ,t.c.value('./Dose[1]', 'decimal(31,10)')								as [Dose]
              ,t.c.value('./Prepid[1]', 'int')										as [Prepid]
              ,t.c.value('./DayliCoeff[1]', 'decimal(31,10)')						as [DayliCoeff]
              ,t.c.value('./MeasureUnit[1]', 'varchar(255)')						as [MeasureUnit]
              ,t.c.value('./MeasureUnitType[1]', 'int')								as [MeasureUnitType]
              ,t.c.value('./SingleDose[1]', 'decimal(31,10)')						as [SingleDose]
              ,t.c.value('./Multiplicity[1]', 'int')								as [Multiplicity]
              ,t.c.value('./DoseViewUnit[1]', 'varchar(255)')						as [DoseView]
              ,t.c.value('./DoseViewUnitType[1]', 'int')							as [DoseViewType]
            INTO #BSK_MEDICAMENTS
        from @Xml.nodes('BSK_DIRECTION/BSK_DIRECTION_BENEFIT/BSK_BENEFITS_DIRECTION_MEDICAMENTS/BSK_BENEFITS_DIRECTION_MEDICAMENT') T(c)

		-- Generate table ##BSK_COMPLICATIONS
        select t.c.value('./DirectionBenID[1]', 'uniqueidentifier')									as [DirectionBenID]
              ,t.c.value('./ComplicationID[1]', 'uniqueidentifier')									as [ComplicationID]
              ,t.c.value('./ComplicationType[1]', 'int')											as [ComplicationType]
              ,convert(datetime, t.c.value('./RegistrationDate[1]', 'varchar(10)'), 104)			as [RegistrationDate]
              ,t.c.value('./MKBCode[1]', 'varchar(255)')											as [MKBCode]
              ,t.c.value('./Diagnosis[1]', 'varchar(255)')											as [Diagnosis]
              ,t.c.value('./EhrRusR6JR8PINUS[1]', 'int')											as [EhrRusR6JR8PINUS]
              ,t.c.value('./EhrRusR6JR8PINUSVer[1]', 'varchar(8)')									as [EhrRusR6JR8PINUSVer]
              ,t.c.value('./EhrRusIN6SHID9F6[1]', 'int')											as [EhrRusIN6SHID9F6]
              ,t.c.value('./EhrRusIN6SHID9F6Ver[1]', 'varchar(8)')									as [EhrRusIN6SHID9F6Ver]
              ,t.c.value('./EhrRusJEDS208EN8[1]', 'int')											as [EhrRusJEDS208EN8]
              ,t.c.value('./EhrRusJEDS208EN8Ver[1]', 'varchar(8)')											as [EhrRusJEDS208EN8Ver]
              ,t.c.value('./LeftVentricularEjectionFraction[1]', 'int')								as [LeftVentricularEjectionFraction]
              ,convert(datetime, t.c.value('./FractionRegistrationDate[1]', 'varchar(10)'), 104)	as [FractionRegistrationDate]
            INTO #BSK_COMPLICATIONS
        from @Xml.nodes('BSK_DIRECTION/BSK_DIRECTION_BENEFIT/BSK_REGISTER_COMPLICATION_LINKS/BSK_REGISTER_COMPLICATION_LINK') T(c)

	IF (@RegisterID is null and @ChangeType <> 1)
	begin		
		update bskD
		set bskD.RegisterID = br.PAC_ID
		   ,@RegisterID = br.PAC_ID
		from #BSK_DIRECTION bskD
			join [RDS].T_BSK_REGISTER br on bskD.SNILS = br.SNILS
		where 
			bskD.RegisterID is null
			and bskD.SNILS is not null
			and bskD.SNILS <> ''
			and	bskD.SNILS <> '              '
			and ((br.DateEnd is null or br.DateEnd > @dateTime) and br.ExcludeReason is null)

		-- Поиск по серии и номеру документа
		update bskD
		set 
			bskD.RegisterID = br.PAC_ID
		   ,@RegisterID = br.PAC_ID
		from #BSK_DIRECTION bskD
			join [RDS].T_BSK_REGISTER br on bskD.DocSeries = br.DocSeries and bskD.DocNumber = br.DocNumber
		where 
			bskD.RegisterID is null
			and bskD.DocSeries is not null
			and bskD.DocNumber is not null
			and bskD.DocSeries <> ''
			and bskD.DocNumber <> ''
			and ((br.DateEnd is null or br.DateEnd > @dateTime) and br.ExcludeReason is null)

		-- Поиск по ФИО + ДР
		update bskD
		set bskD.RegisterID = br.PAC_ID
		   ,@RegisterID		= br.PAC_ID
		from #BSK_DIRECTION bskD
			join [RDS].T_BSK_REGISTER br on bskD.LastName = br.LastName and bskD.FirstName = br.FirstName and (bskD.FatherName = br.FatherName or (bskD.FatherName is null and br.FatherName is null)) and bskD.Birthday = br.Birthday
		where 
			bskD.RegisterID is null
			and ((br.DateEnd is null or br.DateEnd > @dateTime) and br.ExcludeReason is null)
	end
    -- Генерируем новый идентификатор
	else IF (@RegisterID is null and @ChangeType = 1)
    BEGIN
        exec @RegisterID = [dbo].sp_GenerateIdForEntity 'BSK_REGISTER', 'PrimaryKey', 1
        update rd
			set rd.RegisterID = @RegisterID
        from #BSK_DIRECTION rd

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

    update rp
    set rp.LastName				= p.LastName
       ,rp.FirstName			= p.FirstName
       ,rp.FatherName			= p.FatherName
       ,rp.Birthday				= p.Birthday
	   ,rp.Kod					= p.Kod
       ,rp.DocSeries			= p.DocSeries
       ,rp.DocNumber			= p.DocNumber
       ,rp.Sex					= eSex.EnumID
       ,rp.SNILS				= p.Snils
	   ,rp.DocType				= eDoc.EnumID
       ,rp.LpuMain				= p.LPU
       ,rp.LastEditDate			= @dateTime
	   ,rp.DocIssueDate			= p.DocIssueDate
	   ,rp.DocIssueOrg			= p.DocIssueOrg
	   ,rp.Enp					= p.Enp
	   ,rp.RealFiasAddress		= p.RealFiasAddress
	   ,rp.RealFiasHome			= p.RealFiasHome
	   ,rp.RealCity				= p.RealCity
	   ,rp.RealCityPrefix		= p.RealCityPrefix
	   ,rp.RealStreet			= p.RealStreet
	   ,rp.RealStreetPrefix		= p.RealStreetPrefix
	   ,rp.RealHouse			= p.RealHouse
	   ,rp.RealBuild			= p.RealBuild
	   ,rp.RealStruc			= p.RealStruc
	   ,rp.RealRoom				= p.RealRoom
	   ,rp.RealLiveStartDate	= p.RealLiveStartDate
	   ,rp.RealLiveEndDate		= p.RealLiveEndDate
	   ,rp.LegalFiasAddress		= p.LegalFiasAddress
	   ,rp.LegalFiasHome		= p.LegalFiasHome
	   ,rp.LegalCity			= p.LegalCity
	   ,rp.LegalCityPrefix		= p.LegalCityPrefix
	   ,rp.LegalStreet			= p.LegalStreet
	   ,rp.LegalStreetPrefix	= p.LegalStreetPrefix
	   ,rp.LegalHouse			= p.LegalHouse
	   ,rp.LegalBuild			= p.LegalBuild
	   ,rp.LegalStruc			= p.LegalStruc
	   ,rp.LegalRoom			= p.LegalRoom
	   ,rp.LegalLiveStartDate	= p.LegalLiveStartDate
	   ,rp.LegalLiveEndDate		= p.LegalLiveEndDate
	   ,rp.CitizenShip			= cs.EhrRusID
	   ,rp.EhrRus5o5e88vhc6		= dul.EhrRus5o5e88vhc6
    from [RDS].T_BSK_REGISTER rp
        join #BSK_DIRECTION p on p.RegisterID = rp.PAC_ID
        left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = p.Sex
        left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = p.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.Code = p.CitizenShip
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
    where p.ChangeType = 2

	update rp
    set rp.LastEditDate = @dateTime
       ,rp.DateEnd = p.EnterDate
	   ,rp.ExcludeReason = p.ExcludeReason
    from [RDS].T_BSK_REGISTER rp
        join #BSK_DIRECTION p on p.RegisterID = rp.PAC_ID
    where p.ChangeType = 3
	
	update brd
    set brd.CloseDate = @dateTime,
	brd.LastEditDate = @dateTime,
	brd.ExclusionReason = case when p.ExcludeReason = 0 then 2
							   when p.ExcludeReason = 1 then 0
							   when p.ExcludeReason = 2 then 3 end
    from [RDS].[T_BSK_REGISTER_DATA] brd
	join [RDS].T_BSK_REGISTER rp on rp.BskRegisterId = brd.BskRegister
        join #BSK_DIRECTION p on p.RegisterID = rp.PAC_ID
    where p.ChangeType = 3 and brd.CloseDate is null

	INSERT INTO [RDS].[T_BSK_REGISTER]
	           (
				 [BskRegisterID]
				,[ExcludeReason]
				,[LastEditDate]
				,[LastName]
				,[FirstName]
				,[FatherName]
				,[Birthday]
				,[Kod]
				,[DocSeries]
				,[DocNumber]
				,[PAC_ID]
				,[Sex]
				,[SNILS]
				,[DocType]
				,[LpuMain]
				,[DateBegin]
				,[DateEnd]
				,DocIssueDate
				,DocIssueOrg
				,Enp
				,RealFiasAddress
				,RealFiasHome
				,RealCity
				,RealCityPrefix
				,RealStreet
				,RealStreetPrefix
				,RealHouse
				,RealBuild
				,RealStruc
				,RealRoom
				,RealLiveStartDate
				,RealLiveEndDate
				,LegalFiasAddress
				,LegalFiasHome
				,LegalCity
				,LegalCityPrefix
				,LegalStreet
				,LegalStreetPrefix
				,LegalHouse
				,LegalBuild
				,LegalStruc
				,LegalRoom
				,LegalLiveStartDate
				,LegalLiveEndDate
				,CitizenShip
				,EhrRus5o5e88vhc6)

	select		newid()
	           ,[ExcludeReason]
	           ,@dateTime
	           ,[LastName]
	           ,[FirstName]
	           ,[FatherName]
	           ,[Birthday]
	           ,[Kod]
	           ,[DocSeries]
	           ,[DocNumber]
	           ,RegisterID
	           ,eSex.EnumID
	           ,[Snils]
	           ,eDoc.EnumID
	           ,[Lpu]
	           ,@dateTime
	           ,null
			   ,d.DocIssueDate
			   ,d.DocIssueOrg
			   ,d.Enp
			   ,RealFiasAddress
			   ,RealFiasHome
			   ,RealCity
			   ,RealCityPrefix
			   ,RealStreet
			   ,RealStreetPrefix
			   ,RealHouse
			   ,RealBuild
			   ,RealStruc
			   ,RealRoom
			   ,RealLiveStartDate
			   ,RealLiveEndDate
			   ,LegalFiasAddress
			   ,LegalFiasHome
			   ,LegalCity
			   ,LegalCityPrefix
			   ,LegalStreet
			   ,LegalStreetPrefix
			   ,LegalHouse
			   ,LegalBuild
			   ,LegalStruc
			   ,LegalRoom
			   ,LegalLiveStartDate
			   ,LegalLiveEndDate
			   ,cs.EhrRusID
			   ,dul.EhrRus5o5e88vhc6
	from #BSK_DIRECTION d
        left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = d.Sex
        left 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
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
	where d.ChangeType = 1

	INSERT INTO [RDS].[T_BSK_REGISTER_DATA]
	           ([BskRegisterDataID]
	           ,[BskRegister]
	           ,[ServiceCode]
	           ,[ServiceName]
	           ,[DiagDate]
	           ,[Document]
	           ,[DocumentDate]
	           ,[Mkb]
	           ,[Benefits]
			   ,[BeginDate]
			   ,[EndDate]
			   ,[ExclusionReason]
			   ,[EstimatedDDate]
			   ,[DRegistrationDate]
			   ,[Place]
			   ,[LpuMainTo]
			   ,[CloseDate]
			   ,[LpuMain]
			   ,[CreateDateTime]
	           ,[LastEditDate]
			   ,[ConcomitantMkb]
			   ,[ContomitantDisease])

	 select		bd.DirectionBenID
	           ,r.[BskRegisterId]
	           ,[ServiceCode]
	           ,[ServiceName]
	           ,[DiagDate]
	           ,[Document]
	           ,[DocumentDate]
	           ,[Mkb]
	           ,b.[BenefitsID]
			   ,isnull(bd.DateBegin, @dateTime)
			   ,ISNULL(bd.DateEnd, case when (b.Duration is not null and b.PeriodOfAct is not null and b.CloseType is not null) 
										then (case when b.CloseType = 0 and b.PeriodOfAct = 0 then DATEADD(DAY, b.Duration, brr.Birthday)
											when b.CloseType = 0 and b.PeriodOfAct = 1 then DATEADD(MONTH,		b.Duration, brr.Birthday)
											when b.CloseType = 0 and b.PeriodOfAct = 2 then DATEADD(YEAR,		b.Duration, brr.Birthday)
											when b.CloseType = 1 and b.PeriodOfAct = 0 then DATEADD(DAY,		b.Duration, isnull(bd.DateBegin, @dateTime))
											when b.CloseType = 1 and b.PeriodOfAct = 1 then DATEADD(MONTH,		b.Duration, isnull(bd.DateBegin, @dateTime))
											when b.CloseType = 1 and b.PeriodOfAct = 2 then DATEADD(YEAR,		b.Duration, isnull(bd.DateBegin, @dateTime))
											else null end)
										else null end)--<EndDate, datetime,>
			   ,bd.ExclusionReason
			   ,bd.EstimatedDDate
			   ,bd.DRegistrationDate
			   ,bd.Place
			   ,bd.LpuTo
			   ,bd.CloseDate
			   ,brr.Lpu
			   ,ISNULL(brr.CreateDateTime, @dateTime)
			   ,@dateTime
			   ,bd.ConcomitantMkb
			   ,bd.ContomitantDisease
	from #BSK_BENEFITS bd
		join #BSK_DIRECTION brr on brr.id = bd.DirectionID
		join [RDS].[T_BSK_REGISTER] r on r.PAC_ID = @RegisterID
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = bd.BenefitViews
		left join [RDS].T_BENEFITS b on b.BenefitCode = bd.BenefitCode  and b.BenefitViews = e.EnumID
	where bd.ChangeType = 1

    update rpb
    set rpb.EndDate = rdb.DateEnd,
		rpb.ExclusionReason = rdb.ExclusionReason,
		rpb.CloseDate = rdb.CloseDate,
		rpb.LastEditDate = @dateTime
    from #BSK_BENEFITS rdb
		join [RDS].[T_BSK_REGISTER] 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_BSK_REGISTER_DATA rpb on rpb.BskRegister = r.BskRegisterID and rpb.Benefits = b.BenefitsID
    where 
		rdb.ChangeType = 3 and rpb.CloseDate is null and (rpb.EndDate is null or rpb.EndDate >= rdb.CloseDate)
	
    update rpb
    set rpb.DRegistrationDate = rdb.DRegistrationDate,
		rpb.LastEditDate = @dateTime
    from #BSK_BENEFITS rdb
		join [RDS].[T_BSK_REGISTER] 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_BSK_REGISTER_DATA rpb on rpb.BskRegister = r.BskRegisterID and rpb.Benefits = b.BenefitsID
    where 
		rdb.ChangeType = 2
		and (rpb.EndDate is null or rpb.EndDate > @dateTime) and rpb.CloseDate is null

	INSERT INTO [RDS].[T_BSK_REGISTER_MEDICAMENTS]
	           ([BskRegisterMedicamentsID]
	           ,[BskRegisterData]
	           ,[CMnn]
	           ,[DateStart]
	           ,[EndDate]
	           ,[MedicationForms]
	           ,[Regiment]
	           ,[Dose]
	           ,[MeasureUnit]
	           ,[Prepid]
	           ,[DayliCoeff]
			   ,[SingleDose]
			   ,[Multiplicity]
			   ,[DoseView])
	select newid()
			,ben.DirectionBenID
			,(select top 1 CMnnID from PHARm.T_C_MNN where OtherID = meds.Mnn)
			,meds.DateStart
			,meds.EndDate
			,meds.MedicationForms
			,meds.Regiment
			,meds.Dose
			,(select top 1 dw.Code from PHARM.T_DOSE_VIEW dw where dw.Name = meds.MeasureUnit and dw.ViewType = meds.MeasureUnitType)
			,meds.Prepid
			,meds.DayliCoeff
			,meds.SingleDose
			,meds.Multiplicity
			,(select top 1 dw.Code from PHARM.T_DOSE_VIEW dw where dw.Name = meds.DoseView and dw.ViewType = meds.DoseViewType)
	from #BSK_MEDICAMENTS meds
		join #BSK_BENEFITS ben on ben.DirectionBenID = meds.DirectionBenID
	where ben.ChangeType = 1	
	
	INSERT INTO [RDS].[T_BSK_REGISTER_COMPLICATION_LINK]
	(
		 [BskRegisterComplicationLinkID]
		,[BskRegisterData]
		,[ComplicationType]
		,[RegistrationDate]
		,[MKBCode]
		,[Diagnosis]
		,[type_]
	)
	select 
		 compl.ComplicationID
		,ben.DirectionBenID
		,compl.ComplicationType
		,compl.RegistrationDate
		,compl.MKBCode
		,compl.Diagnosis
		,(select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'BSK_REGISTER_CHRONIC_HEART_FAILURE')
	from #BSK_COMPLICATIONS compl
		join #BSK_BENEFITS ben on ben.DirectionBenID = compl.DirectionBenID
	where ben.ChangeType = 1	

	INSERT INTO RDS.T_BSK_REGISTER_CHRONIC_HEART_FAILURE
	(
		 [BskRegisterComplicationLinkID]
		,[ChronicHeartFailureClass]
		,[ChronicHeartFailureType]
		,[ChronicHeartFailureStage]
		,[LeftVentricularEjectionFraction]
		,[FractionRegistrationDate]
	)
	select 
		 compl.ComplicationID
		,ehr1.EhrRusID
		,ehr2.EhrRusID
		,ehr3.EhrRusID
		,compl.LeftVentricularEjectionFraction
		,compl.FractionRegistrationDate
	from #BSK_COMPLICATIONS compl
		join #BSK_BENEFITS ben on ben.DirectionBenID = compl.DirectionBenID
		outer apply 
		(
			select top 1 er.EhrRusID 
			from EHR.T_EHR_RUS_R6JR8PINUS er
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = compl.EhrRusR6JR8PINUS and RRB.CURRVERSION = compl.EhrRusR6JR8PINUSVer
		) ehr1
		outer apply 
		(
			select top 1 er.EhrRusID 
			from EHR.T_EHR_RUS_IN6SHID9F6 er
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = compl.EhrRusIN6SHID9F6 and RRB.CURRVERSION = compl.EhrRusIN6SHID9F6Ver
		) ehr2
		outer apply 
		(
			select top 1 er.EhrRusID 
			from EHR.T_EHR_RUS_JEDS208EN8 er
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = compl.EhrRusJEDS208EN8 and RRB.CURRVERSION = compl.EhrRusJEDS208EN8Ver
		) ehr3
	where ben.ChangeType = 1	
	
	-- собираем ответ в АКУЗ, BSK_DIRECTION
    SELECT @Result = 
    ( 
        select rd.ID			 as 'ID'
			  ,rd.RegisterID   as 'RegisterID'
		from #BSK_DIRECTION rd
		for xml path(''),
		root('BSK_REGISTER')
    ) 

	-- собираем пакет в апс
	select @Xml = 
	(
                select @OperationType                                           as 'header/TYPE'
                      ,(
                            select rp.PAC_ID										as 'RegisterID'
                                  ,rp.LastName										as 'LastName'
                                  ,rp.FirstName										as 'FirstName'
                                  ,rp.FatherName									as 'FatherName'
                                  ,convert(varchar(10), rp.Birthday, 104)			as 'Birthday'
                                  ,rp.Kod											as 'Kod'
                                  ,rp.DocSeries										as 'DocSeries'
                                  ,rp.DocNumber										as 'DocNumber'
                                  ,eSex.Code										as 'Sex'
                                  ,rp.Snils											as 'Snils'
                                  ,convert(varchar(10), rp.DateBegin, 104)			as 'DateBegin'
                                  ,convert(varchar(10), rp.DateEnd, 104)			as 'DateEnd'
                                  ,convert(varchar(10), rp.LastEditDate, 104)		as 'LastEditDate'
                                  ,rp.LpuMain										as 'Lpu'
                                  ,rp.ExcludeReason									as 'ExcludeReason'
                                  ,eDoc.Code										as 'DocType'
								  ,convert(varchar(10), rp.DocIssueDate, 104)		as 'DocIssueDate'
								  ,rp.DocIssueOrg									as 'DocIssueOrg'
								  ,rp.Enp											as 'Enp'
								  ,rp.RealFiasAddress								as 'RealFiasAddress'
								  ,rp.RealFiasHome									as 'RealFiasHome'
								  ,rp.RealCity										as 'RealCity'
								  ,rp.RealCityPrefix								as 'RealCityPrefix'
								  ,rp.RealStreet									as 'RealStreet'
								  ,rp.RealStreetPrefix								as 'RealStreetPrefix'
								  ,rp.RealHouse										as 'RealHouse'
								  ,rp.RealBuild										as 'RealBuild'
								  ,rp.RealStruc										as 'RealStruc'
								  ,rp.RealRoom										as 'RealRoom'
								  ,convert(varchar(10), rp.RealLiveStartDate, 104)  as 'RealLiveStartDate'
								  ,convert(varchar(10), rp.RealLiveEndDate, 104)	as 'RealLiveEndDate'
								  ,rp.LegalFiasAddress								as 'LegalFiasAddress'
								  ,rp.LegalFiasHome									as 'LegalFiasHome'
								  ,rp.LegalCity										as 'LegalCity'
								  ,rp.LegalCityPrefix								as 'LegalCityPrefix'
								  ,rp.LegalStreet									as 'LegalStreet'
								  ,rp.LegalStreetPrefix								as 'LegalStreetPrefix'
								  ,rp.LegalHouse									as 'LegalHouse'
								  ,rp.LegalBuild									as 'LegalBuild'
								  ,rp.LegalStruc									as 'LegalStruc'
								  ,rp.LegalRoom										as 'LegalRoom'
								  ,convert(varchar(10), rp.LegalLiveStartDate, 104)	as 'LegalLiveStartDate'
								  ,convert(varchar(10), rp.LegalLiveEndDate, 104)	as 'LegalLiveEndDate'
								  ,cs.CODE											as 'CitizenShip'
								  ,doc.CodeEHR										as 'EhrRus5o5e88vhc6'
								  ,RRB.CurrVersion									as 'EhrRus5o5e88vhc6Ver'
								  ,(
									      select rp.PAC_ID											as 'RegisterID'
									            ,rpb.BskRegisterDataID								as 'BenID'
									            ,convert(varchar(10), rpb.DiagDate, 104)			as 'DiagDate'
									            ,rpb.Document										as 'Document'
									            ,convert(varchar(10), rpb.DocumentDate, 104)		as 'DocumentDate'
									            ,rpb.Mkb											as 'Mkb'
									            ,b.BenefitCode										as 'BenefitCode'
									            ,e.Code												as 'BenefitViews'
									            ,convert(varchar(10), rpb.BeginDate, 104)			as 'BeginDate'
									            ,convert(varchar(10), rpb.EndDate, 104)				as 'EndDate'
									            ,rpb.ServiceCode									as 'ServiceCode'
									            ,rpb.ServiceName									as 'ServiceName'
												,rpb.ExclusionReason								as 'ExclusionReason'
												,convert(varchar(10), rpb.EstimatedDDate, 104)      as 'EstimatedDDate'
												,convert(varchar(10), rpb.DRegistrationDate, 104)   as 'DRegistrationDate'
												,rpb.Place											as 'Place'
												,rpb.LpuMainTo										as 'LpuTo'
												,rpb.LpuMain										as 'Lpu'
												,convert(varchar(10), rpb.CloseDate, 104)			as 'CloseDate'
												-- собираем препараты
														  ,( 
															     select 
														                meds.BskRegisterData                        as 'BenID'
															           ,mnn.OtherID                                 as 'Mnn'
																	   ,convert(varchar(10), meds.DateStart, 104)   as 'DateStart'
																	   ,convert(varchar(10), meds.EndDate, 104)     as 'EndDate'
															           ,meds.MedicationForms                        as 'MedicationForms'
															           ,meds.Regiment                               as 'Regiment'
															           ,meds.Dose                                   as 'Dose'
															           ,meds.Prepid                                 as 'Prepid'
															           ,meds.DayliCoeff                             as 'DayliCoeff'
															           ,dv.name										as 'MeasureUnit'
															           ,dv.ViewType	                                as 'MeasureUnitType'
										                               ,meds.SingleDose                             as 'SingleDose'
										                               ,meds.Multiplicity                           as 'Multiplicity'
										                               ,dvdv.Name	                                as 'DoseViewUnit'
										                               ,dvdv.ViewType	                            as 'DoseViewUnitType'
															     from RDS.T_BSK_REGISTER_MEDICAMENTS meds
																	join PHARM.T_C_MNN mnn on mnn.CMnnID = meds.CMnn
																	left join PHARM.T_DOSE_VIEW dv on dv.Code = meds.MeasureUnit
																	left join PHARM.T_DOSE_VIEW dvdv on dvdv.Code = meds.DoseView
																 where meds.BskRegisterData = rpb.BskRegisterDataID
															     for xml path('MEDICAMENTS'),
															     TYPE
															 ) 
													      from [RDS].[T_BSK_REGISTER_DATA] rpb
													          join [RDS].T_BENEFITS b on b.BenefitsID = rpb.Benefits
															  join [VCLib].T_ENUMS e on e.EnumTypeID = b.BenefitViews
														  where rpb.BskRegister = rp.BskRegisterID
														for xml path('BENEFITS'),
														TYPE
													)
                            from [RDS].[T_BSK_REGISTER] rp
                                join [VCLib].T_ENUMS eSex on eSex.EnumID = rp.Sex
                                join [VCLib].T_ENUMS eDoc on eDoc.EnumID = rp.DocType
								left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = rp.CitizenShip	
								left join EHR.T_EHR_RUS_5O5E88VHC6 doc on doc.EhrRusID = rp.EhrRus5o5e88vhc6
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = doc.EhrRusRefBooks
							where rp.PAC_ID = @RegisterID
                            for xml path('PATIENT'),
                            type
                      ) as 'body'
                for xml path(''),
                root('BSK_REGISTER')
            )


	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),>
		      ,@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.ServerType = 1 -- APS

	update e
	set id = (select MAX(b.PAC_ID) + 1 from RDS.T_BSK_REGISTER b)
	from dbo.T_ENTITY_IDS e
	where e.EntName in ('BSK_REGISTER', 'RDS.BSK_REGISTER')
    -- Drop tables
    DROP TABLE #BSK_DIRECTION
    DROP TABLE #BSK_BENEFITS
	DROP TABLE #BSK_MEDICAMENTS
	DROP TABLE #BSK_COMPLICATIONS
END



