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

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier

    select @dateTime = ISNULL(@dateTime, GETDATE())

    IF (@Xml is null)
    BEGIN
        if(not exists(select * from akuz.T_PATIENT_BENEFITS_DIRECTION where PatientBenefitsDirectionID = @ID))
        begin
         return
        end
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'PALLIATIVE_DIRECTION'
              ,@OwnerID = @ID

        -- Обработка операции
        SELECT @Result = 
        ( 
            select @ID													as 'ID'
                  ,lm.LpuCode											as 'LPU'
                  ,convert(varchar(10), pbd.EnterDate, 104)				as 'EnterDate'
                  ,pbd.LastName											as 'LastName'
                  ,pbd.FirstName										as 'FirstName'
                  ,pbd.FatherName										as 'FatherName'
                  ,convert(varchar(10), pbd.Birthday, 104)				as 'Birthday'
                  ,convert(varchar(10), pbd.[Date], 104)				as 'Date'
                  ,e.Code												as 'Sex'
                  ,pbd.Kod												as 'Kod'
                  ,pbd.DocSeries										as 'DocSeries'
                  ,pbd.DocNumber										as 'DocNumber'
				  ,eDt.Code												as 'DocType'
                  ,pbd.PAC_ID											as 'RegisterID'
                  ,pbd.SNILS											as 'SNILS'
                  ,pbd.ChangeType										as 'ChangeType'
                  ,rbd.ExcludeReason									as 'ExcludeReason'
                  ,pbd.BirthSurname										as 'BirthSurname'
                  ,convert(varchar(10), pbd.DocIssueDate, 104)			as 'DocIssueDate'
                  ,pbd.DocIssueOrg										as 'DocIssueOrg'
                  ,pbd.Enp												as 'Enp'
                  ,pbd.RealFiasAddress									as 'RealFiasAddress'
                  ,pbd.RealFiasHome										as 'RealFiasHome'
                  ,pbd.RealCity											as 'RealCity'
                  ,pbd.RealCityPrefix									as 'RealCityPrefix'
                  ,pbd.RealStreet										as 'RealStreet'
                  ,pbd.RealStreetPrefix									as 'RealStreetPrefix'
                  ,pbd.RealHouse										as 'RealHouse'
                  ,pbd.RealBuild										as 'RealBuild'
                  ,pbd.RealStruc										as 'RealStruc'
                  ,pbd.RealRoom											as 'RealRoom'
                  ,convert(varchar(10), pbd.RealLiveStartDate, 104)		as 'RealLiveStartDate'
                  ,convert(varchar(10), pbd.RealLiveEndDate, 104)		as 'RealLiveEndDate'
                  ,pbd.LegalFiasAddress									as 'LegalFiasAddress'
                  ,pbd.LegalFiasHome									as 'LegalFiasHome'
                  ,pbd.LegalCity										as 'LegalCity'
                  ,pbd.LegalCityPrefix									as 'LegalCityPrefix'
                  ,pbd.LegalStreet										as 'LegalStreet'
                  ,pbd.LegalStreetPrefix								as 'LegalStreetPrefix'
                  ,pbd.LegalHouse										as 'LegalHouse'
                  ,pbd.LegalBuild										as 'LegalBuild'
                  ,pbd.LegalStruc										as 'LegalStruc'
                  ,pbd.LegalRoom										as 'LegalRoom'
                  ,convert(varchar(10), pbd.LegalLiveStartDate, 104)	as 'LegalLiveStartDate'
                  ,convert(varchar(10), pbd.LegalLiveEndDate, 104)		as 'LegalLiveEndDate'
                  ,cs.CODE												as 'CitizenShip'
				  -- собираем льготы
				  ,( 
					     select 
                                rbd.PatientBenefitsDirectionID					as 'DirectionID'
								,convert(varchar(10), pbdd.DiagDate, 104)		as 'DiagDate'
								,pbdd.Document									as 'Document'
								,convert(varchar(10), pbdd.DocumentDate, 104)	as 'DocumentDate'
								,pbdd.Mkb										as 'Mkb'
								,b.BenefitCode									as 'BenefitCode'
								,ebb.Code										as 'BenefitViews'
								,post.Code										as 'PostCode'
								,pbdd.ChangeType								as 'ChangeType'
					     from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA rbdd 
							 join [AKUZ].T_PATIENT_BENEFITS_DIRECTION_DATA pbdd on pbdd.PatientBenefitsDirectionDataID = rbdd.PatientBenefitsDirectionDataID
					         join [AKUZ].T_BENEFITS b on b.BenefitsID = pbdd.Benefits
                             join [VCLib].T_ENUMS ebb on ebb.EnumID = b.BenefitViews
							 left join AKUZ.T_POST post on post.ResourceID = pbdd.Doctor
					     where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
					     for xml path('PALLIATIVE_DIRECTION_BENEFIT'),
					     TYPE
					 ) 
            from akuz.T_PALLIATIVE_BENEFITS_DIRECTION rbd
                join [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = rbd.PatientBenefitsDirectionID
                join [VCLib].T_ENUMS e on e.EnumID = pbd.Sex
                join [AKUZ].T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
				join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                left join [AKUZ].T_POLICY p on p.PolicyID = [dbo].fn_GetOmsPolicy(pbd.Patient, @dateTime)
                join [VCLib].T_ENUMS eDt on eDt.EnumID = pbd.DocType
				left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = pbd.CitizenShip
            where pbd.PatientBenefitsDirectionID = @ID
            for xml path(''),
            root('PALLIATIVE_DIRECTION')
        ) 

        -- Отправка
        INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,@QueueName--,<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),>
    END
    ELSE
    BEGIN

        select T.c.value('./ID[1]', 'uniqueidentifier')                         as [ID]
              ,T.c.value('./RegisterID[1]', 'int')                           as [RegisterID]
            INTO #RR_DIRECTION
        from @Xml.nodes('PALLIATIVE_REGISTER') T(c)

        if not exists(select * 
						from [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd 
						join #RR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID)
            raiserror('Не найдены данные по направлению', 16, 0)

        -- Устанавливаем идентификатор пациента из RDS в направлении
        update pbd
        set pbd.PAC_ID = rd.RegisterID
        from [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd
            join #RR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID

        -- Добавление льготы
        INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
                   ([PatientBenefitsID] ,[Patient] ,[Benefits] ,[BenefitDocumentNumber] ,[BeginDate] ,[Mkb] ,[Active] ,[Deleted]
                   ,[CreateType] ,[LPU_INF] ,[RegDate], [PAC_ID],[EndDate])
        SELECT
                   NEWID()--<PatientBenefitsID, uniqueidentifier,>
                   ,pbd.Patient--<Patient, uniqueidentifier,>
                   ,pbdd.Benefits--<Benefits, uniqueidentifier,>
                   ,pbdd.Document--<BenefitDocumentNumber, varchar(255),>
                   ,pbd.[Date]--<BeginDate, datetime,>
                   ,(select top 1 m.MkbID from AKUZ.T_MKB m where m.MCBCode = pbdd.Mkb)--<Mkb, uniqueidentifier,>
                   ,1--<Active, bit,>
                   ,0--<Deleted, bit,>
                   ,2--<CreateType, int,>
                   ,lm.LpuCode--<LPU_INF, varchar(4),>
                   ,@dateTime--<RegDate, datetime,>
                   ,pbd.PAC_ID
				   ,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, pbd.Birthday)
									when b.CloseType = 0 and b.PeriodOfAct = 1 then DATEADD(MONTH,  b.Duration, pbd.Birthday)
									when b.CloseType = 0 and b.PeriodOfAct = 2 then DATEADD(YEAR,   b.Duration, pbd.Birthday)
									when b.CloseType = 1 and b.PeriodOfAct = 0 then DATEADD(DAY,    b.Duration, pbd.[Date])
									when b.CloseType = 1 and b.PeriodOfAct = 1 then DATEADD(MONTH,  b.Duration, pbd.[Date])
									when b.CloseType = 1 and b.PeriodOfAct = 2 then DATEADD(YEAR,   b.Duration, pbd.[Date])
									else null end)
						else null end--<EndDate, datetime,>
        from [AKUZ].T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
            join [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
            join #RR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
            join [AKUZ].T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
			join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
            join AKUZ.T_BENEFITS b on b.BenefitsID = pbdd.Benefits
        where pbdd.ChangeType = 1
                and
            not exists
            (
                select *
                from [AKUZ].T_PATIENT_BENEFITS pb
                where pb.Patient = pbd.Patient and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate > GetDate())
            )

        -- Закрытие льготы
        update pb
        set pb.EndDate = pbd.[Date]
            ,pb.Active = 0
            ,pb.Deleted = 1
        from [AKUZ].T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
            left join [AKUZ].T_MKB mkb on mkb.MCBCode = pbdd.Mkb
            join [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
            join #RR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
            join [AKUZ].T_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
                    and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate >= GetDate())
        where pbdd.ChangeType = 3

		update pb
        set pb.EndDate = pbd.[Date]
            ,pb.Active = 0
            ,pb.Deleted = 1
		from [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd
            join #RR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
            join [AKUZ].T_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
			join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
			join AKUZ.T_LPU_MAIN lp on lp.LpuID = pbd.LpuMain
        where pbd.ChangeType = 3 and (b.Private = 0 or (b.Private = 1 and (lp.LpuCode = pb.LPU_INF or lp.TFomsCode = pb.LPU_INF))) and (pb.EndDate is null or pb.EndDate >= GetDate())
        and b.BenefitViews in (19008)

    END
END
GO
