IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetPalliativeRegister]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetPalliativeRegister]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра паллиативных больных.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetPalliativeRegister]
    @Xml xml = null
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
AS
BEGIN
    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
        from @Xml.nodes('PALLIATIVE_REGISTER') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #PALLIATIVE_REGISTER
            SELECT t.c.value('./RegisterID[1]', 'int')                                   AS RegisterID,
                   t.c.value('./LastName[1]', 'varchar(40)')                             AS LastName,
                   t.c.value('./FirstName[1]', 'varchar(40)')                            AS FirstName,
                   t.c.value('./FatherName[1]', 'varchar(40)')                           AS FatherName,
                   CONVERT(DATETIME, t.c.value('./Birthday[1]', 'varchar(10)'), 104)     AS Birthday,
                   t.c.value('./Kod[1]', 'varchar(32)')                                  AS Kod,
                   t.c.value('./DocSeries[1]', 'varchar(8)')                             AS DocSeries,
                   t.c.value('./DocNumber[1]', 'varchar(8)')                             AS DocNumber,
                   t.c.value('./Sex[1]', 'int')                                          AS Sex,
                   t.c.value('./Snils[1]', 'varchar(14)')                                AS Snils,
                   CONVERT(DATETIME, t.c.value('./DateBegin[1]', 'varchar(10)'), 104)    AS DateBegin,
                   CONVERT(DATETIME, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)      AS DateEnd,
                   CONVERT(DATETIME, t.c.value('./LastEditDate[1]', 'varchar(10)'), 104) AS LastEditDate,
                   t.c.value('./Lpu[1]', 'varchar(6)')                                   AS Lpu,
                   t.c.value('./ExcludeReason[1]', 'int')                                AS ExcludeReason,
                   t.c.value('./DocType[1]', 'int')                                      AS DocType,
                   t.c.value('./PatientId[1]', 'uniqueidentifier')                       AS PatientId
            INTO #PALLIATIVE_REGISTER
            FROM @Xml.nodes('PALLIATIVE_REGISTER/body/PATIENT') T(c)

            -- Generate table #PALLIATIVE_BENEFITS
            SELECT t.c.value('./RegisterID[1]', 'int')                                   AS RegisterID,
                   CONVERT(DATETIME, t.c.value('./DiagDate[1]', 'varchar(10)'), 104)     AS DiagDate,
                   t.c.value('./Document[1]', 'varchar(16)')                             AS Document,
                   CONVERT(DATETIME, t.c.value('./DocumentDate[1]', 'varchar(10)'), 104) AS DocumentDate,
                   t.c.value('./Mkb[1]', 'varchar(8)')                                   AS Mkb,
                   t.c.value('./BenefitCode[1]', 'varchar(255)')                         AS BenefitCode,
                   t.c.value('./BenefitViews[1]', 'int')                                 AS BenefitViews,
                   CONVERT(DATETIME, t.c.value('./BeginDate[1]', 'varchar(10)'), 104)    AS BeginDate,
                   CONVERT(DATETIME, t.c.value('./EndDate[1]', 'varchar(10)'), 104)      AS EndDate,
                   t.c.value('./Lpu[1]', 'varchar(6)')                                   AS Lpu
            INTO #PALLIATIVE_BENEFITS
            FROM @Xml.nodes('PALLIATIVE_REGISTER/body/PATIENT/BENEFITS') T(c)

            -- Связываем регистр с пациентами
            -- Поиск по СНИЛС + ДР
            update pr
            set pr.PatientId = p.PatientID
            from #PALLIATIVE_REGISTER pr
                join [AKUZ].T_PATIENT p on p.SNILS = pr.Snils and p.Birthday = pr.Birthday
            where
				pr.Snils is not null
				and pr.Birthday is not null

            -- Поиск по серии и номеру документа
            update pr
            set pr.PatientId = p.PatientID
            from #PALLIATIVE_REGISTER pr
                join [AKUZ].T_PATIENT p on LEFT(p.DocSeries, 8) = pr.DocSeries and LEFT(p.DocNumber, 8) = pr.DocNumber
            where
				pr.PatientId is null
				and pr.DocSeries is not null
				and pr.DocNumber is not null

            -- Поиск по ФИО + ДР
            update pr
            set pr.PatientId = p.PatientID
            from #PALLIATIVE_REGISTER pr
                join [AKUZ].T_PATIENT p on p.LastName = pr.LastName and p.FirstName = pr.FirstName and (p.FatherName = pr.FatherName or p.FatherName is null and pr.FatherName is null) and p.Birthday = pr.Birthday
            where pr.PatientId is null

			delete pb
            from akuz.T_PATIENT_BENEFITS pb
			join [AKUZ].T_BENEFITS b on b.BenefitsID = pb.Benefits
			where b.BenefitViews in (19008)

            -- Загружаем новые
            INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
                       ([PatientBenefitsID] ,[Patient] ,[Benefits] ,[BenefitDocumentNumber] ,[BeginDate] ,[EndDate] ,[Mkb] ,[Active]
                       ,[Deleted] ,[CreateType] ,[LPU_INF] ,[RegDate], [PAC_ID])
            SELECT
                       NEWID()--<PatientBenefitsID, uniqueidentifier,>
                       ,p.PatientID--<Patient, uniqueidentifier,>
                       ,b.BenefitsID--<Benefits, uniqueidentifier,>
                       ,pb.Document--<BenefitDocumentNumber, varchar(255),>
                       ,pb.BeginDate--<BeginDate, datetime,>
                       ,pb.EndDate--<EndDate, datetime,>
                       ,pb.Mkb--<Mkb, uniqueidentifier,>
                       ,1--<Active, bit,>
                       ,0--<Deleted, bit,>
                       ,2--<CreateType, int,>
                       ,pb.Lpu--<LPU_INF, varchar(6),>
                       ,@dateTime--<RegDate, datetime,>)
					   ,pb.RegisterID
            from #PALLIATIVE_BENEFITS pb
                join #PALLIATIVE_REGISTER pr on pr.RegisterID = pb.RegisterID
                join [AKUZ].T_PATIENT p on p.PatientID = pr.PatientId
                join [VCLib].T_ENUMS eBv on eBv.Code = pb.BenefitViews and eBv.EnumTypeID = 190
                join [AKUZ].T_BENEFITS b on b.BenefitCode = pb.BenefitCode and b.BenefitViews = eBv.EnumID
        END
    END
END
