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)

        -- 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(16)')									as [DocSeries]
              ,t.c.value('./DocNumber[1]', 'varchar(16)')									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]
			  ,T.c.value('./BirthSurname[1]', 'varchar(40)')								as [BirthSurname]
			  ,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 #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)
			
        if (@OperationType = @FTLOAD or (select count(*) from #PALLIATIVE_REGISTER) > 1)
        BEGIN	
			delete from APS.T_PALLIATIVE_REGISTER_DATA
			delete from APS.T_PALLIATIVE_REGISTER
			
        END
		else
		begin 
			delete ben 
			from APS.T_PALLIATIVE_REGISTER_DATA ben
				join APS.T_PALLIATIVE_REGISTER rr on  rr.PalliativeRegisterID = ben.PalliativeRegister
				join #PALLIATIVE_REGISTER r on r.RegisterID = rr.PAC_ID

			delete r
			from APS.T_PALLIATIVE_REGISTER rr
				join #PALLIATIVE_REGISTER r on r.RegisterID = rr.PAC_ID
		end

		INSERT INTO [APS].[T_PALLIATIVE_REGISTER]
		           (
				    [PalliativeRegisterID]
		           ,[ExcludeReason]
		           ,[LastEditDate]
		           ,[LastName]
		           ,[FirstName]
		           ,[FatherName]
		           ,[Birthday]
		           ,[Kod]
		           ,[DocSeries]
		           ,[DocNumber]
		           ,[PAC_ID]
		           ,[Sex]
		           ,[SNILS]
		           ,[DocType]
		           ,[LpuMain]
		           ,[DateBegin]
		           ,[DateEnd]
				   ,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 newid()
		           ,[ExcludeReason]
		           ,[LastEditDate]
		           ,[LastName]
		           ,[FirstName]
		           ,[FatherName]
		           ,[Birthday]
		           ,[Kod]
		           ,[DocSeries]
		           ,[DocNumber]
		           ,[RegisterID]
		           ,eSex.EnumID
		           ,[SNILS]
		           ,e.EnumID
		           ,[Lpu]
		           ,[DateBegin]
		           ,[DateEnd]
				   ,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
				   ,cs.EhrRusID
				   ,doc.EhrRusID
			from #PALLIATIVE_REGISTER r
				left join vclib.T_ENUMS e on e.EnumTypeID = 105 and e.Code = r.DocType
				left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = r.Sex
				left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = r.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 = r.EhrRus5o5e88vhc6 and RRB.CURRVERSION = r.EhrRus5o5e88vhc6Ver
				) doc


		INSERT INTO [APS].[T_PALLIATIVE_REGISTER_DATA]
		           ([PalliativeRegisterDataID]
		           ,[PalliativeRegister]
		           ,[DiagDate]
		           ,[Document]
		           ,[DocumentDate]
		           ,[Mkb]
		           ,[Benefits]
		           ,[BeginDate]
		           ,[EndDate]
		           ,[LastEditDate])
		select	newid()
		           ,rr.[PalliativeRegisterID]
		           ,[DiagDate]
		           ,[Document]
		           ,[DocumentDate]
		           ,[Mkb]
		           ,b.BenefitsID
		           ,[BeginDate]
		           ,[EndDate]
		           ,getdate()
		from #PALLIATIVE_BENEFITS r
			join APS.T_PALLIATIVE_REGISTER rr on rr.PAC_ID = r.RegisterID
			left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = r.BenefitViews
			left join APS.T_BENEFITS b on b.BenefitCode = r.BenefitCode and b.BenefitViews = e.EnumID


        -- Drop tables
        DROP TABLE #PALLIATIVE_REGISTER
        DROP TABLE #PALLIATIVE_BENEFITS

    END
END
