SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

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


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

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- 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,
                   CONVERT(DATETIME, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 104)       AS DocIssueDate,
                   T.c.value('./Enp[1]', 'varchar(16)')                                        AS Enp,
                   T.c.value('./DocIssueOrg[1]', 'varchar(256)')                               AS DocIssueOrg,
                   T.c.value('./RealFiasAddress[1]', 'varchar(36)')                            AS RealFiasAddress,
                   T.c.value('./RealFiasHome[1]', 'varchar(36)')                               AS RealFiasHome,
                   T.c.value('./RealCity[1]', 'varchar(100)')                                  AS RealCity,
                   T.c.value('./RealCityPrefix[1]', 'varchar(10)')                             AS RealCityPrefix,
                   T.c.value('./RealStreet[1]', 'varchar(100)')                                AS RealStreet,
                   T.c.value('./RealStreetPrefix[1]', 'varchar(10)')                           AS RealStreetPrefix,
                   T.c.value('./RealHouse[1]', 'varchar(100)')                                 AS RealHouse,
                   T.c.value('./RealBuild[1]', 'varchar(100)')                                 AS RealBuild,
                   T.c.value('./RealStruc[1]', 'varchar(100)')                                 AS RealStruc,
                   T.c.value('./RealRoom[1]', 'varchar(100)')                                  AS RealRoom,
                   CONVERT(DATETIME, T.c.value('./RealLiveStartDate[1]', 'varchar(10)'), 104)  AS RealLiveStartDate,
                   CONVERT(DATETIME, T.c.value('./RealLiveEndDate[1]', 'varchar(10)'), 104)    AS RealLiveEndDate,
                   T.c.value('./LegalFiasAddress[1]', 'varchar(36)')                           AS LegalFiasAddress,
                   T.c.value('./LegalFiasHome[1]', 'varchar(36)')                              AS LegalFiasHome,
                   T.c.value('./LegalCity[1]', 'varchar(100)')                                 AS LegalCity,
                   T.c.value('./LegalCityPrefix[1]', 'varchar(10)')                            AS LegalCityPrefix,
                   T.c.value('./LegalStreet[1]', 'varchar(100)')                               AS LegalStreet,
                   T.c.value('./LegalStreetPrefix[1]', 'varchar(10)')                          AS LegalStreetPrefix,
                   T.c.value('./LegalHouse[1]', 'varchar(100)')                                AS LegalHouse,
                   T.c.value('./LegalBuild[1]', 'varchar(100)')                                AS LegalBuild,
                   T.c.value('./LegalStruc[1]', 'varchar(100)')                                AS LegalStruc,
                   T.c.value('./LegalRoom[1]', 'varchar(100)')                                 AS LegalRoom,
                   CONVERT(DATETIME, T.c.value('./LegalLiveStartDate[1]', 'varchar(10)'), 104) AS LegalLiveStartDate,
                   CONVERT(DATETIME, T.c.value('./LegalLiveEndDate[1]', 'varchar(10)'), 104)   AS LegalLiveEndDate,
                   T.c.value('./CitizenShip[1]', 'int')                                        AS CitizenShip,
                   T.c.value('./EhrRus5o5e88vhc6[1]', 'varchar(52)')                           AS EhrRus5o5e88vhc6,
                   T.c.value('./EhrRus5o5e88vhc6Ver[1]', 'varchar(52)')                        AS EhrRus5o5e88vhc6Ver
            INTO #RR_PATIENTS
            FROM @Xml.nodes('RR_PATIENT/body/RR_PATIENTS/RR_PATIENT') T(c)

            -- Первичная загрузка
            delete from [APS].[T_RR_PATIENT_BENEFIT]
			delete from [APS].[T_RR_PATIENT]

            -- Загружаем новые
            INSERT INTO [APS].[T_RR_PATIENT]
                       (
						 [RrPatientID]
						,[SNILS]
						,[DocSeries]
						,[DocNumber]
						,[FatherName]
						,[FirstName]
						,[LastName]
						,[Birthday]
						,[Sex]
						,[DateBegin]
						,[DateEnd]
						,[LastEditDate]
						,[LastEditLpu]
						,[ExcludeReason]
						,Kod
						,KodAdr
						,DAT_PROV
						,BirthSurname
						,LpuMainFirst
						,SOST_R
						,DocType
						,DocIssueDate
						,DocIssueOrg
						,Enp
						,RealFiasAddress
						,RealFiasHome
						,RealCity
						,RealCityPrefix
						,RealStreet
						,RealStreetPrefix
						,RealHouse
						,RealBuild
						,RealStruc
						,RealRoom
						,RealLiveStartDate
						,RealLiveEndDate
						,LegalFiasAddress
						,LegalFiasHome
						,LegalCity
						,LegalCityPrefix
						,LegalStreet
						,LegalStreetPrefix
						,LegalHouse
						,LegalBuild
						,LegalStruc
						,LegalRoom
						,LegalLiveStartDate
						,LegalLiveEndDate
						,CitizenShip
						,EhrRus5o5e88vhc6)
            SELECT
                       nrp.RrPatientID--<RrPatientID, int,>
                      ,nrp.Snils--<SNILS, varchar(14),>
                      ,nrp.DocSeries--<DocSeries, varchar(8),>
                      ,nrp.DocNumber--<DocNumber, varchar(8),>
                      ,nrp.FatherName--<FatherName, varchar(40),>
                      ,nrp.FirstName--<FirstName, varchar(40),>
                      ,nrp.LastName--<LastName, varchar(40),>
                      ,nrp.Birthday--<Birthday, datetime,>
                      ,eSex.EnumID--<Sex, int,>
                      ,nrp.DateBegin--<DateBegin, datetime,>
                      ,nrp.DateEnd--<DateEnd, datetime,>
                      ,nrp.LastEditDate--<LastEditDate, datetime,>
                      ,nrp.Lpu--<LastEditLpu, varchar(6),>)
                      ,nrp.ExcludeReason--<ExcludeReason, int,>)
                      ,nrp.Kod
                      ,nrp.KodAdr
                      ,nrp.DAT_PROV
                      ,nrp.BirthSurname
                      ,nrp.LpuFirst
                      ,nrp.SOST_R
                      ,e.EnumID
			          ,nrp.DocIssueDate
			          ,nrp.DocIssueOrg
			          ,nrp.Enp
			          ,RealFiasAddress,RealFiasHome,RealCity,RealCityPrefix,RealStreet,RealStreetPrefix,RealHouse,RealBuild,RealStruc,RealRoom,RealLiveStartDate,RealLiveEndDate
			          ,LegalFiasAddress,LegalFiasHome,LegalCity,LegalCityPrefix,LegalStreet,LegalStreetPrefix,LegalHouse,LegalBuild,LegalStruc,LegalRoom,LegalLiveStartDate,LegalLiveEndDate
			          ,cs.EhrRusID
			          ,doc.EhrRusID
            from #RR_PATIENTS nrp
                left join [VCLib].T_ENUMS eSex on eSex.Code = nrp.Sex and eSex.EnumTypeID = 103
				left join vclib.T_ENUMS e on e.EnumTypeID = 105 and e.Code = nrp.TIPD
		        left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = nrp.CitizenShip
		        outer apply (
		        	select top 1 doc.EhrRusID
		        	from EHR.T_EHR_RUS_5O5E88VHC6 doc
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = doc.EhrRusRefBooks
		        	where doc.CodeEHR = nrp.EhrRus5o5e88vhc6 and RRB.CURRVERSION = nrp.EhrRus5o5e88vhc6Ver
		        ) doc

        END

        -- Drop tables
        DROP TABLE #RR_PATIENTS

    END
END
