IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BSK_Register_Direction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BSK_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_BSK_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 = 'BSK_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'
                  ,convert(varchar(19), getdate(), 120)					as 'CreateDateTime'
                  ,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'
                               ,bbdd.PatientBenefitsDirectionDataID					as 'DirectionBenID'
							   ,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'
							   ,ms.Name												as 'ServiceName'
							   ,ms.Code												as 'ServiceCode'
							   ,convert(varchar(10), bbdd.DateBegin, 104)			as 'DateBegin'
							   ,convert(varchar(10), bbdd.DateEnd, 104)				as 'DateEnd'
							   ,bbdd.ExclusionReason								as 'ExclusionReason'
							   ,convert(varchar(10), bbdd.EstimatedDDate, 104)      as 'EstimatedDDate'
							   ,convert(varchar(10), bbdd.DRegistrationDate, 104)   as 'DRegistrationDate'
							   ,bbdd.Place											as 'Place'
							   ,lmTo.LpuCode										as 'LpuTo'
							   ,convert(varchar(10), bbdd.CloseDate, 104)			as 'CloseDate'
							   -- собираем препараты
									  ,( 
										     select 
									                meds.BskBenefitsDirectionData               as 'DirectionBenID'
										           ,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 AKUZ.T_BSK_BENEFITS_DIRECTION_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.BskBenefitsDirectionData = bbdd.PatientBenefitsDirectionDataID
										     for xml path('BSK_BENEFITS_DIRECTION_MEDICAMENTS'),
										     TYPE
										 ) 
							 from AKUZ.T_BSK_BENEFITS_DIRECTION_DATA bbdd 
								join [AKUZ].T_PATIENT_BENEFITS_DIRECTION_DATA pbdd on pbdd.PatientBenefitsDirectionDataID = bbdd.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
								left join AKUZ.T_MEDICAL_SERVICE ms on ms.MedicalServiceID = bbdd.MedicalService
								left join [AKUZ].T_LPU_MAIN lmTo on lmTo.LpuID = bbdd.LpuMainTo
					     where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
					     for xml path('BSK_DIRECTION_BENEFIT'),
					     TYPE
					 ) 
            from akuz.T_BSK_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('BSK_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('BSK_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
                   ,IsNULL(isnull(bsk.CloseDate, bsk.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, 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 AKUZ.T_BSK_BENEFITS_DIRECTION_DATA bsk on bsk.PatientBenefitsDirectionDataID = pbdd.PatientBenefitsDirectionDataID
            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.BenefitType in (24207)

    END
END
GO
