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'
							   ,bbdd.ConcomitantMkb								    as 'ConcomitantMkb'
							   ,bbdd.ConcomitantDisease								as 'ConcomitantDisease'
							   -- собираем препараты
							   ,( 
							        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_MEDICAMENT'), ROOT ('BSK_BENEFITS_DIRECTION_MEDICAMENTS'),
							   	    TYPE
								)
                               ,(
                                    select 
                                        compl.BskRegisterComplicationLinkID                     as 'ComplicationID'
									    ,compl.BskBenefitsDirectionData                         as 'DirectionBenID'
                                        ,compl.ComplicationType                                 as 'ComplicationType'
                                        ,convert(varchar(10), compl.RegistrationDate, 104)      as 'RegistrationDate'
                                        ,compl.MKBCode                                          as 'MKBCode'
                                        ,compl.Diagnosis                                        as 'Diagnosis'
                                        ,class.IdEHR                                            as 'EhrRusR6JR8PINUS'
                                        ,rrb.CurrVersion                                        as 'EhrRusR6JR8PINUSVer'
                                        ,[type].IdEHR                                           as 'EhrRusIN6SHID9F6'
                                        ,rrb1.CurrVersion                                       as 'EhrRusIN6SHID9F6Ver'
                                        ,stage.IdEHR                                            as 'EhrRusJEDS208EN8'
                                        ,rrb2.CurrVersion                                       as 'EhrRusJEDS208EN8Ver'
                                        ,chf.LeftVentricularEjectionFraction                    as 'LeftVentricularEjectionFraction'
                                        ,convert(varchar(10),chf.FractionRegistrationDate, 104) as 'FractionRegistrationDate'
                                    from AKUZ.T_BSK_REGISTER_COMPLICATION_LINK compl
								     join AKUZ.T_BSK_REGISTER_CHRONIC_HEART_FAILURE chf on chf.BskRegisterComplicationLinkID = compl.BskRegisterComplicationLinkID
                                        left join EHR.T_EHR_RUS_R6JR8PINUS class on chf.ChronicHeartFailureClass = class.EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb ON rrb.EhrRusRefBooksID = class.EhrRusRefBooks
                                        left join EHR.T_EHR_RUS_IN6SHID9F6 [type] on chf.ChronicHeartFailureType = [type].EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb1 ON rrb1.EhrRusRefBooksID = [type].EhrRusRefBooks
                                        left join EHR.T_EHR_RUS_JEDS208EN8 stage on chf.ChronicHeartFailureStage = stage.EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb2 ON rrb2.EhrRusRefBooksID = stage.EhrRusRefBooks
								    where compl.BskBenefitsDirectionData = bbdd.PatientBenefitsDirectionDataID
                                    for xml path('BSK_REGISTER_COMPLICATION_LINK'), ROOT ('BSK_REGISTER_COMPLICATION_LINKS'),
                                    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') ,ROOT('BSK_DIRECTION_BENEFITS'),
					     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
 