IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RR_Direction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RR_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_RR_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 = 'RR_DIRECTION'
              ,@OwnerID = @ID

        -- Обработка операции
        SELECT @Result = 
        ( 
            select @ID                                      as 'ID'
                  ,case when ls.RegionCode =  '1600000000000' then '16' + lm.Code
						else lm.LpuCode end					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'
                  ,e.Code                                   as 'Sex'
                  ,pbd.Kod                                  as 'Kod'
                  ,case when pbd.DocSeries = '0' then null
						else pbd.DocSeries 
				   end										as 'DocSeries'
                  ,case when pbd.DocNumber = '0' then null
						else pbd.DocNumber 
				   end										as 'DocNumber'
                  ,pbd.PAC_ID                               as 'RrPatient'
                  ,case when RTRIM(LTRIM(REPLACE(REPLACE(pbd.SNILS, '0', ''), '-', ''))) = '' then null
                        else pbd.SNILS
                   end                                      as 'SNILS'
				  ,eDt.Code									as 'DocType'
                  ,pbd.ChangeType					        as 'ChangeType'
                  ,rbd.ExcludeReason                        as 'ExcludeReason'
                  ,rbd.KodAdr					            as 'KodAdr'
                  ,pbd.BirthSurname					        as 'BirthSurname'
                  ,lmF.LpuCode								as 'LpuFirst'
                  ,rbd.SOST_R								as 'SOST_R'
                  ,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'
					           ,rbdd.Dop                                     as 'Dop'
					           ,pDop.Code                                    as 'DivisionManager'
					     from AKUZ.T_REGIONAL_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
							 left join AKUZ.T_POST pDop on pDop.ResourceID = rbdd.DivisionManager
					     where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
					     for xml path('RR_DIRECTION_BENEFIT'),
					     TYPE
					 ) 
            from akuz.T_REGIONAL_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 [AKUZ].T_LPU_MAIN lmF on lmF.LpuID = rbd.LpuMainFirst
				left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = pbd.CitizenShip
            where pbd.PatientBenefitsDirectionID = @ID
            for xml path(''),
            root('RR_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

        -- Generate table #RR_DIRECTION
        select T.c.value('./ID[1]', 'uniqueidentifier')                         as [ID]

              ,T.c.value('./RrPatient[1]', 'int')                                 as [RrPatient]
            INTO #RR_DIRECTION
        from @Xml.nodes('RR_DIRECTION') 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.RrPatient
        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,>
                   ,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 ls.RegionCode = '1600000000000' and (b.BenefitCode = '057' or b.BenefitCode = '57') and b.BenefitViews = 19002  
							then (case when pbd.Snils is not null and pbd.Snils <> '' then DATEADD(day,-1,DATEADD(year, 3, pbd.Birthday)) else DATEADD(day,-1,DATEADD(MONTH, 2, pbd.Birthday)) end)
						 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
        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
					join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
                where 
					pb.Patient = pbd.Patient 
					and pb.Benefits = pbdd.Benefits
					and (pb.EndDate is null or pb.EndDate > GetDate())
					and (b.Private = 0 or (b.Private = 1 and (lm.LpuCode = pb.LPU_INF or lm.TFomsCode = pb.LPU_INF)))
            )

        -- Закрытие льготы
        update pb
        set pb.EndDate = pbd.[Date]
            ,pb.Active = 0
            ,pb.Deleted = 1
        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_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
                    and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate >= GetDate())
			join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
			join AKUZ.T_LPU_MAIN lp on lp.LpuID = pbd.LpuMain
        where pbdd.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())

		 
		update pb
        set pb.EndDate = pbd.[EnterDate]
            ,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 (24202, 24203)

    END
END
GO
