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

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка информации по направлениям в региональный регистр RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RR_Direction]
    @Xml xml,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@OperationType varchar(255)
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)

    -- Инициализация
    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'

    -- Получаем тип операции
    select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
    from @Xml.nodes('RR_DIRECTION') T(c)

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

    -- Generate table #RR_DIRECTION_BENEFIT
    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_DIRECTION_BENEFIT
    from @Xml.nodes('RR_DIRECTION/body/RR_DIRECTION/RR_DIRECTION_BENEFIT') T(c)
       

	delete pb from [APS].[T_RR_PATIENT_BENEFIT] pb
	join #RR_DIRECTION r on r.RrPatientID = pb.RrPatient

    delete p from [APS].[T_RR_PATIENT] p
	join #RR_DIRECTION r on r.RrPatientID = p.RrPatientID

    -- insert [APS].T_RR_PATIENT
    INSERT INTO [APS].[T_RR_PATIENT]
               (
				 [RrPatientID] 
				,[FatherName] 
				,[FirstName] 
				,[LastName] 
				,[Birthday] 
				,[Sex] 
				,[SNILS] 
				,[DateBegin] 
				,[DateEnd] 
				,[LastEditDate] 
				,[LastEditLpu] 
				,[ExcludeReason] 
				,[DocSeries] 
				,[DocNumber]
				,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
               pd.RrPatientID--<RrPatientID, int,>
              ,pd.FatherName--<FatherName, varchar(40),>
              ,pd.FirstName--<FirstName, varchar(40),>
              ,pd.LastName--<LastName, varchar(40),>
              ,pd.Birthday--<Birthday, datetime,>
              ,eSex.EnumID--<Sex, int,>
              ,pd.SNILS--<SNILS, char(14),>
              ,pd.[LastEditDate]--<DateBegin, datetime,>
              ,null--<DateEnd, datetime,>
              ,@dateTime--<LastEditDate, datetime,>
              ,pd.LPU--<LastEditLpu, varchar(6),>
              ,null--<ExcludeReason, int,>)
              ,pd.DocSeries--<DocSeries, varchar(8),>
              ,pd.DocNumber--<DocNumber, varchar(8),>)
              ,pd.Kod
              ,pd.KodAdr
              ,pd.DAT_PROV
              ,pd.BirthSurname
              ,pd.LpuFirst
              ,pd.SOST_R
              ,eDoc.EnumID
			  ,pd.DocIssueDate
			  ,pd.DocIssueOrg
			  ,pd.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_DIRECTION pd
			left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pd.Sex
			left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pd.TIPD
			left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = pd.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 = pd.EhrRus5o5e88vhc6 and RRB.CURRVERSION = pd.EhrRus5o5e88vhc6Ver
			) doc

		-- Загружаем новые
       INSERT INTO [APS].[T_RR_PATIENT_BENEFIT]
                  ([RrPatientBenefitID] ,[DiagDate] ,[Document] ,[DocumentDate] ,[Dop] ,[Mkb] ,[Benefits]  ,[RrPatient] ,[BeginDate] ,[EndDate] ,[LastEditDate] ,[LastEditLpu])
       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),>)
       from #RR_DIRECTION_BENEFIT npb
           join [VCLib].T_ENUMS eBv on eBv.Code = npb.BenefitViews and eBv.EnumTypeID = 190
           join [APS].T_BENEFITS b on b.BenefitCode = npb.BenefitCode and b.BenefitViews = eBv.EnumID
    -- Drop tables
    DROP TABLE #RR_DIRECTION

END
GO