IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Nosology_Register_Direction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Nosology_Register_Direction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2023-06-14
-- Description: Отправка информации по направлениям в ВЗН регистр в RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_Nosology_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 = 'NOSOLOGY_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'   
				  ,pbd.BirthSurname					        as 'BirthSurname'
                  ,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(10), pbd.DocIssueDate, 104)  as 'DocIssueDate'
                  ,pbd.DocIssueOrg								as 'DocIssueOrg'
                  ,pbd.Enp									as 'Enp'
                  ,region.Code						        as 'Region'
                  ,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'
                  ,pbd.RealAOIDArea							as 'RealAOIDArea'
                  ,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'
                  ,pbd.LegalAOIDArea						as 'LegalAOIDArea'
                  ,convert(varchar(10), pbd.LegalLiveStartDate, 104)  as 'LegalLiveStartDate'
                  ,convert(varchar(10), pbd.LegalLiveEndDate, 104)  as 'LegalLiveEndDate'
                  ,cs.CODE									as 'CitizenShip'
                  ,pbd.PolicySerial							as 'PolicySerial'
                  ,pbd.PolicyNumber							as 'PolicyNumber'
                  ,pbd.SmoCod								as 'SmoCod'
                  ,pbd.PolicyForm							as 'PolicyForm'
                  ,rbd.WorkPlace							as 'WorkPlace'
                  ,rbd.WorkPost								as 'WorkPost'
                  ,rbd.RationaleForDirection				as 'RationaleForDirection'
                  ,rbd.Number								as 'Number'
                  ,rbd.FedReg								as 'FedReg'
                  ,rbd.FedRegCategoria						as 'FedRegCategoria'
                  ,rbd.LLOReg								as 'LLOReg'
                  ,rbd.Invalidity							as 'Invalidity'
                  ,wPost.FIO							    as 'PostFIO'
                  ,concat(wHeadPost.LastName, ' ', wHeadPost.FirstName, ' ', wHeadPost.FatherName)    as 'HeadPostFIO'
                  ,wMcPost.FIO							    as 'McPostFIO'
				  -- собираем льготы
				  ,( 
					     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 pbdd 
					         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('NOSOLOGY_DIRECTION_BENEFIT'),
					     TYPE
					 ) 
            from AKUZ.T_VZN_BENEFITS_DIRECTION rbd
                left join AKUZ.T_POST post on post.ResourceID = rbd.Post
                left join AKUZ.T_WORKER wPost on wPost.WorkerId = post.Worker
                left join AKUZ.T_POST headPost on headPost.ResourceID = rbd.HeadPost
                left join AKUZ.T_WORKER wHeadPost on wHeadPost.WorkerId = headPost.Worker
                left join AKUZ.T_POST mcPost on mcPost.ResourceID = rbd.MedicalCommissionPost
                left join AKUZ.T_WORKER wMcPost on wMcPost.WorkerId = mcPost.Worker
                left join [AKUZ].T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = rbd.PatientBenefitsDirectionID
                left join AKUZ.T_REGION region on pbd.Region = region.RegionID
                left join [VCLib].T_ENUMS e on e.EnumID = pbd.Sex
                left join [AKUZ].T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
				left 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)
                left 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('NOSOLOGY_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('NOSOLOGY_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.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.BenefitType in (24204)

    END
END
GO