IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RR_Patients]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RR_Patients]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-18
-- Description: Загрузка регистра региональных льготников.
-- =============================================
CREATE PROCEDURE [dbo].[sp_RR_Patients]
    @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') T(c)

        -- Generate table #RR_PATIENTS
        select t.c.value('./RrPatientID[1]', 'int')                                  as [RrPatientID]
              ,t.c.value('./Snils[1]', 'varchar(14)')                                as [Snils]
              ,t.c.value('./DocSeries[1]', 'varchar(16)')                            as [DocSeries]
              ,t.c.value('./DocNumber[1]', 'varchar(16)')                            as [DocNumber]
              ,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]
              ,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('./Sex[1]', 'int')                                          as [Sex]
              ,t.c.value('./Lpu[1]', 'varchar(6)')                                   as [Lpu]
              ,t.c.value('./ExcludeReason[1]', 'int')                                as [ExcludeReason]
              ,t.c.value('./DocType[1]', 'int')                                      as [TIPD]
              ,t.c.value('./Kod[1]', 'varchar(32)')                                  as [Kod]
              ,t.c.value('./KodAdr[1]', 'varchar(11)')                               as [KodAdr]
              ,convert(datetime, t.c.value('./DAT_PROV[1]', 'varchar(10)'), 104)     as [DAT_PROV]
              ,t.c.value('./BirthSurname[1]', 'varchar(40)')                         as [BirthSurname]
              ,t.c.value('./LpuFirst[1]', 'varchar(6)')                              as [LpuFirst]
              ,t.c.value('./SOST_R[1]', 'int')                                       as [SOST_R]
            INTO #RR_PATIENTS
        from @Xml.nodes('RR_PATIENT/body/RR_PATIENTS/RR_PATIENT') T(c)
        where @OperationType = @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- Версия регистра
            declare @versionId uniqueidentifier
            select @versionId = NEWID()
            INSERT INTO [AKUZ].[T_RR_LOAD]
                       ([Version]
                       ,[UpdateDate]
                       ,[RrLoadID]
                       ,[FileName]
                       ,[LoadType])
            SELECT
                       (select COUNT(*) from [AKUZ].T_RR_LOAD)--<Version, int,>
                       ,@dateTime--<UpdateDate, datetime,>
                       ,@versionId--<RrLoadID, uniqueidentifier,>
                       ,'RR_PATIENT'--<FileName, varchar(255),>
                       ,1--<LoadType, int,>) -- Полная загрузка

            --удаляем все
            delete from [AKUZ].T_RR_PATIENT

            -- Загружаем новые
            INSERT INTO AKUZ.T_RR_PATIENT
                       (PAC_ID ,FAM ,IM ,OT ,W ,DROD ,SS ,SDOK ,NDOK
                       ,DATE_RB ,DATE_RE ,DAT_REG ,LPU_INF ,RrLoad ,PRICHINA
                       ,RrPatientID,Kod, KodAdr, DAT_PROV, BirthSurname, LpuMainFirst, SOST_R, TIPD)
            SELECT
                       nrp.RrPatientID--<PAC_ID, int,>
                      ,nrp.LastName--<FAM, varchar(40),>
                      ,nrp.FirstName--<IM, varchar(40),>
                      ,nrp.FatherName--<OT, varchar(40),>
                      ,nrp.Sex--<W, varchar(1),>
                      ,nrp.Birthday--<DROD, datetime,>
                      ,nrp.Snils--<SS, varchar(14),>
                      ,nrp.DocSeries--<SDOK, varchar(8),>
                      ,nrp.DocNumber--<NDOK, varchar(8),>
                      ,nrp.DateBegin--<DATE_RB, datetime,>
                      ,nrp.DateEnd--<DATE_RE, datetime,>
                      ,nrp.LastEditDate--<DAT_REG, datetime,>
                      ,nrp.Lpu--<LPU_INF, varchar(4),>
                      ,@versionId--<RrLoad, uniqueidentifier,>
                      ,ISNULL(nrp.ExcludeReason, 0)--<PRICHINA, varchar(1),>
                      ,NEWID()--<RrPatientID, uniqueidentifier,>)
                      ,nrp.Kod
                      ,nrp.KodAdr
                      ,nrp.DAT_PROV
                      ,nrp.BirthSurname
                      ,nrp.LpuFirst
                      ,nrp.SOST_R
                      ,nrp.TIPD
            from #RR_PATIENTS nrp

            -- Связываем регистр с пациентами
            -- Поиск по СНИЛС + ДР
            update rp
            set rp.Patient = p.PatientID
            from [AKUZ].T_RR_PATIENT rp
                join [AKUZ].T_PATIENT p on p.SNILS = rp.SS and p.Birthday = rp.DROD
            where 
				rp.SS is not null 
				and rp.SS <> '' 
				and rp.SS <> '              '

            -- Поиск по серии и номеру документа
            update rp
            set rp.Patient = p.PatientID
            from [AKUZ].T_RR_PATIENT rp
                join [AKUZ].T_PATIENT p on LEFT(p.DocSeries, 8) = rp.SDOK and LEFT(p.DocNumber, 8) = rp.NDOK
            where 
				rp.Patient is null 
				and rp.SDOK is not null 
				and rp.SDOK <> '' 
				and rp.NDOK is not null 
				and rp.NDOK  <> ''

            -- Поиск по ФИО + ДР
            update rp
            set rp.Patient = p.PatientID
            from [AKUZ].T_RR_PATIENT rp
                join [AKUZ].T_PATIENT p on p.LastName = rp.FAM and p.FirstName = rp.IM and (p.FatherName = rp.OT or p.FatherName is null and rp.OT is null) and p.Birthday = rp.DROD
            where rp.Patient is null

        END

        -- Drop tables
        DROP TABLE #RR_PATIENTS

    END
END
