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

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра БСК.
-- =============================================
CREATE PROCEDURE [APS].[sp_GetBSKRegister]
    @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('BSK_REGISTER') T(c)

        -- Generate table #BSK_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 #BSK_REGISTER
        from @Xml.nodes('BSK_REGISTER/body/PATIENT') T(c)

		
        -- Generate table #BSK_BENEFITS
        select t.c.value('./RegisterID[1]', 'int')											as [RegisterID]
              ,t.c.value('./BenID[1]', 'uniqueidentifier')									as [BenID]
              ,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]
              ,t.c.value('./ServiceCode[1]', 'varchar(20)')									as [ServiceCode]
              ,t.c.value('./ServiceName[1]', 'varchar(255)')								as [ServiceName]
              ,convert(datetime, t.c.value('./DateBegin[1]', 'varchar(10)'), 104)			as [DateBegin]
              ,convert(datetime, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)				as [DateEnd]
              ,t.c.value('./ExclusionReason[1]', 'int')										as [ExclusionReason]
              ,convert(datetime, t.c.value('./EstimatedDDate[1]', 'varchar(10)'), 104)		as [EstimatedDDate]
              ,convert(datetime, t.c.value('./DRegistrationDate[1]', 'varchar(10)'), 104)   as [DRegistrationDate]
              ,t.c.value('./Place[1]', 'int')												as [Place]
              ,t.c.value('./LpuTo[1]', 'varchar(6)')										as [LpuTo]
              ,convert(datetime, t.c.value('./CloseDate[1]', 'varchar(10)'), 104)			as [CloseDate]
            INTO #BSK_BENEFITS
        from @Xml.nodes('BSK_REGISTER/body/PATIENT/BENEFITS') T(c)

		-- Generate table #BSK_MEDICAMENTS
        select t.c.value('./BenID[1]', 'uniqueidentifier')						  as [BenID]
              ,t.c.value('./Mnn[1]', 'int')										  as [Mnn]
              ,convert(datetime, t.c.value('./DateStart[1]', 'varchar(10)'), 104) as [DateStart]
              ,convert(datetime, t.c.value('./EndDate[1]', 'varchar(10)'), 104)   as [EndDate]
              ,t.c.value('./MedicationForms[1]', 'uniqueidentifier')			  as [MedicationForms]
              ,t.c.value('./Regiment[1]', 'varchar(255)')                         as [Regiment]
              ,t.c.value('./Dose[1]', 'decimal(31,10)')                           as [Dose]
              ,t.c.value('./Prepid[1]', 'int')									  as [Prepid]
              ,t.c.value('./DayliCoeff[1]', 'decimal(31,10)')                     as [DayliCoeff]
              ,t.c.value('./MeasureUnit[1]', 'varchar(255)')					  as [MeasureUnit]
              ,t.c.value('./MeasureUnitType[1]', 'int')							  as [MeasureUnitType]
              ,t.c.value('./SingleDose[1]', 'decimal(31,10)')                     as [SingleDose]
              ,t.c.value('./Multiplicity[1]', 'int')							  as [Multiplicity]
              ,t.c.value('./DoseView[1]', 'varchar(255)')					      as [DoseView]
              ,t.c.value('./DoseView[1]', 'int')							      as [DoseViewType]
            INTO #BSK_MEDICAMENTS
        from @Xml.nodes('BSK_REGISTER/body/PATIENT/BENEFITS/MEDICAMENTS') T(c)
        
		if (@OperationType = @FTLOAD or (select count(*) from #BSK_REGISTER) > 1)
        BEGIN	
			delete from APS.[T_BSK_REGISTER_MEDICAMENTS]
			delete from APS.[T_BSK_REGISTER_DATA]
			delete from APS.[T_BSK_REGISTER]
			
        END
		else
		begin 
			delete meds
			from [APS].[T_BSK_REGISTER_MEDICAMENTS] meds
				join [APS].[T_BSK_REGISTER_DATA] ben on ben.BskRegisterDataID = meds.BskRegisterData
				join [APS].[T_BSK_REGISTER] rr on  rr.BskRegisterID = ben.BskRegister
				join #BSK_REGISTER r on r.RegisterID = rr.PAC_ID

			delete ben 
			from [APS].[T_BSK_REGISTER_DATA] ben
				join [APS].[T_BSK_REGISTER] rr on  rr.BskRegisterID = ben.BskRegister
				join #BSK_REGISTER r on r.RegisterID = rr.PAC_ID

			delete r
			from APS.[T_BSK_REGISTER] rr
				join #BSK_REGISTER r on r.RegisterID = rr.PAC_ID
		end

		INSERT INTO [APS].[T_BSK_REGISTER]
					   ([BskRegisterID]
					   ,[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 #BSK_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_BSK_REGISTER_DATA]
					   ([BskRegisterDataID]
					   ,[BskRegister]
					   ,[ServiceCode]
					   ,[ServiceName]
					   ,[DiagDate]
					   ,[Document]
					   ,[DocumentDate]
					   ,[Mkb]
					   ,[Benefits]
					   ,[BeginDate]
					   ,[EndDate]
                       ,[ExclusionReason]
			           ,[EstimatedDDate]
			           ,[DRegistrationDate]
			           ,[Place]
			           ,[LpuMainTo]
					   ,[CloseDate]
			           ,[LpuMain]
					   ,[LastEditDate])
			select		b.BenID
					   ,r.BskRegisterID
					   ,[ServiceCode]
					   ,[ServiceName]
					   ,[DiagDate]
					   ,[Document]
					   ,[DocumentDate]
					   ,[Mkb]
					   ,bb.BenefitsID
					   ,[BeginDate]
					   ,[EndDate]
			           ,b.ExclusionReason
			           ,b.EstimatedDDate
			           ,b.DRegistrationDate
			           ,b.Place
			           ,b.LpuTo
					   ,b.CloseDate
					   ,b.Lpu
					   ,getdate()
			from #BSK_BENEFITS b
				join [APS].[T_BSK_REGISTER] r on r.[PAC_ID] = b.RegisterID
				left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = b.BenefitViews
				left join APS.T_BENEFITS bb on bb.BenefitCode = b.BenefitCode and bb.BenefitViews = e.EnumID

		INSERT INTO [APS].[T_BSK_REGISTER_MEDICAMENTS]
		           ([BskRegisterMedicamentsID]
		           ,[BskRegisterData]
		           ,[CMnn]
		           ,[DateStart]
		           ,[EndDate]
		           ,[MedicationForms]
		           ,[Regiment]
		           ,[Dose]
		           ,[MeasureUnit]
		           ,[Prepid]
		           ,[DayliCoeff]
				   ,[SingleDose]
				   ,[Multiplicity]
				   ,[DoseView])
		     select newid()
					,meds.BenID
					,(select top 1 CMnnID from PHARm.T_C_MNN where OtherID = meds.Mnn)
					,meds.DateStart
					,meds.EndDate
					,meds.MedicationForms
					,meds.Regiment
					,meds.Dose
					,(select top 1 dw.Code from PHARM.T_DOSE_VIEW dw where dw.Name = meds.MeasureUnit and dw.ViewType = meds.MeasureUnitType)
					,meds.Prepid
					,meds.DayliCoeff
					,meds.SingleDose
					,meds.Multiplicity
					,(select top 1 dw.Code from PHARM.T_DOSE_VIEW dw where dw.Name = meds.DoseView and dw.ViewType = meds.DoseViewType)
		     from #BSK_MEDICAMENTS meds

        -- Drop tables
        DROP TABLE #BSK_REGISTER
        DROP TABLE #BSK_BENEFITS
        DROP TABLE #BSK_MEDICAMENTS

    END
END
