IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RR_PatientBenefits]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RR_PatientBenefits]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RR_PatientBenefits]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RR_PatientBenefits]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-13
-- Description: Загрузка регистра региональных льготников.
-- =============================================
CREATE PROCEDURE [APS].[sp_RR_PatientBenefits]
    @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('RR_PATIENT_BENEFIT') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #RR_PATIENT_BENEFITS
            SELECT t.c.value('./RrPatient[1]', 'int')                                    AS RrPatient,
                   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('./Dop[1]', 'varchar(1)')                                   AS Dop,
                   t.c.value('./Mkb[1]', 'varchar(8)')                                   AS Mkb,
                   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('./BenefitCode[1]', 'varchar(255)')                         AS BenefitCode,
                   t.c.value('./BenefitViews[1]', 'int')                                 AS BenefitViews,
                   t.c.value('./Lpu[1]', 'varchar(6)')                                   AS Lpu,
                   CONVERT(DATETIME, t.c.value('./LastEditDate[1]', 'varchar(10)'), 104) AS LastEditDate,
                   t.c.value('./Post[1]', 'varchar(10)')                                 AS Post,
                   t.c.value('./DivisionManager[1]', 'varchar(10)')                      AS DivisionManager
            INTO #RR_PATIENT_BENEFITS
            FROM @Xml.nodes('RR_PATIENT_BENEFIT/body/RR_PATIENT_BENEFITS/RR_PATIENT_BENEFIT') T(c)

            -- Первичная загрузка
            -- Удаляем отсутствующие
            delete pb
            from [APS].T_RR_PATIENT_BENEFIT pb

            -- Загружаем новые
            INSERT INTO [APS].[T_RR_PATIENT_BENEFIT]
                       ([RrPatientBenefitID] ,[DiagDate] ,[Document] ,[DocumentDate] ,[Dop] ,[Mkb] ,[Benefits]  ,[RrPatient] ,[BeginDate] ,[EndDate] ,[LastEditDate] ,[LastEditLpu], Post, DivisionManager)
            SELECT
                       NEWID()--<RrPatientBenefitID, uniqueidentifier,>
                       ,npb.DiagDate--<DiagDate, datetime,>
                       ,npb.Document--<Document, varchar(16),>
                       ,npb.DocumentDate--<DocumentDate, datetime,>
                       ,npb.Dop--<Dop, varchar(1),>
                       ,npb.Mkb--<Mkb, varchar(8),>
                       ,b.BenefitsID--<Benefits, uniqueidentifier,>
                       ,npb.RrPatient--<RrPatient, int,>
                       ,npb.BeginDate--<BeginDate, datetime,>
                       ,npb.EndDate--<EndDate, datetime,>
                       ,npb.LastEditDate--<LastEditDate, datetime,>
                       ,npb.Lpu--<LastEditLpu, varchar(6),>)
                       ,npb.Post
                       ,npb.DivisionManager
            from #RR_PATIENT_BENEFITS npb
                left join [VCLib].T_ENUMS eBv on eBv.Code = npb.BenefitViews and eBv.EnumTypeID = 190
                left join [APS].T_BENEFITS b on b.BenefitCode = npb.BenefitCode and b.BenefitViews = eBv.EnumID
        END

        -- Drop tables
        DROP TABLE #RR_PATIENT_BENEFITS

    END
END
