IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetRDSOrphRegister]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetRDSOrphRegister]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetRDSOrphRegister]    Script Date: 18.11.2015 22:00:23 ******/

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

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

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-09-01
-- Description: Загрузка льготников по орфанным заболеваниям
-- =============================================
create PROCEDURE [APS].[sp_GetSetRDSOrphRegister]
    @Xml xml = null,
    @ID uniqueidentifier = null,
    @IsNew bit = 1,
    @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(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('ORPHAN_REGISTER') T(c)

		-- Generate table #ORPHAN_REGISTERS
        select t.c.value('./ID[1]', 'uniqueidentifier')								as [ID]
		,convert(datetime, t.c.value('./DATE_BEGIN[1]', 'varchar(10)'), 104)		as [DateBegin]
		,convert(datetime, t.c.value('./DATE_END[1]', 'varchar(10)'), 104)			as [DateEnd]
		,t.c.value('./FIRST_NAME[1]', 'varchar(60)')								as [FirstName]
		,t.c.value('./LAST_MANE[1]', 'varchar(60)')									as [LastName]
		,t.c.value('./BIRTH_LAST_MANE[1]', 'varchar(60)')							as [BirthLastName]
		,t.c.value('./FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,convert(datetime, t.c.value('./BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,t.c.value('./SEX[1]', 'int')												as [Sex]
		,t.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./ADRESS[1]', 'varchar(1000)')									as [Adress]
		,t.c.value('./OKATO[1]', 'varchar(256)')									as [OKATO]
		,t.c.value('./FEDERAL[1]', 'bit')											as [Federal]
		,convert(datetime, t.c.value('./DOC_ISSUE_DATE[1]', 'varchar(10)'), 104)	as [DocIssueDate]
		,t.c.value('./DOC_ISSUE_ORG[1]', 'varchar(256)')							as [DocIssueOrg]
		,t.c.value('./DOC_TYPE[1]', 'int')											as [DocType]
		,t.c.value('./DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,t.c.value('./SMO[1]', 'varchar(5)')										as [F002Smo]
		,t.c.value('./OMS_SERIAL[1]', 'varchar(12)')								as [OmsSerial]
		,t.c.value('./OMS_NUMBER[1]', 'varchar(32)')								as [OmsNumber]
		,t.c.value('./MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')								as [Diagnosis]
		,t.c.value('./MKB_DOP[1]', 'varchar(10)')									as [MkbDop]
		,t.c.value('./DIAGNOSIS_DOP[1]', 'varchar(4096)')							as [DiagnosisDop]
		,t.c.value('./LPU_MAIN_FIRST[1]', 'varchar(6)')								as [LpuMainFirst]
		,t.c.value('./LPU_MAIN[1]', 'varchar(6)')									as [LpuMain]
		,t.c.value('./POST_FIO[1]', 'varchar(255)')									as [PostFio]
		,t.c.value('./REGISTER_ID[1]', 'int')										as [RegisterId]
        INTO #ORPHAN_REGISTERS
        from @Xml.nodes('ORPHAN_REGISTER/body/ORPHAN_REGISTERS/ORPHAN_REGISTER_ITEM') T(c)
        where @OperationType = @FTLOAD

		-- Generate table #ORPHAN_REGISTER
        select t.c.value('./body[1]/ID[1]', 'uniqueidentifier')								as [ID]
		,convert(datetime, t.c.value('./body[1]/DATE_BEGIN[1]', 'varchar(10)'), 104)		as [DateBegin]
		,convert(datetime, t.c.value('./body[1]/DATE_END[1]', 'varchar(10)'), 104)			as [DateEnd]
		,t.c.value('./body[1]/FIRST_NAME[1]', 'varchar(60)')								as [FirstName]
		,t.c.value('./body[1]/LAST_MANE[1]', 'varchar(60)')									as [LastName]
		,t.c.value('./body[1]/BIRTH_LAST_MANE[1]', 'varchar(60)')							as [BirthLastName]
		,t.c.value('./body[1]/FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,convert(datetime, t.c.value('./body[1]/BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,t.c.value('./body[1]/SEX[1]', 'int')												as [Sex]
		,t.c.value('./body[1]/SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./body[1]/DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./body[1]/ADRESS[1]', 'varchar(1000)')									as [Adress]
		,t.c.value('./body[1]/OKATO[1]', 'varchar(256)')									as [OKATO]
		,t.c.value('./body[1]/FEDERAL[1]', 'bit')											as [Federal]
		,convert(datetime, t.c.value('./body[1]/DOC_ISSUE_DATE[1]', 'varchar(10)'), 104)	as [DocIssueDate]
		,t.c.value('./body[1]/DOC_ISSUE_ORG[1]', 'varchar(256)')							as [DocIssueOrg]
		,t.c.value('./body[1]/DOC_TYPE[1]', 'int')											as [DocType]
		,t.c.value('./body[1]/DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./body[1]/DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,t.c.value('./body[1]/SMO[1]', 'varchar(5)')										as [F002Smo]
		,t.c.value('./body[1]/OMS_SERIAL[1]', 'varchar(12)')								as [OmsSerial]
		,t.c.value('./body[1]/OMS_NUMBER[1]', 'varchar(32)')								as [OmsNumber]
		,t.c.value('./body[1]/MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./body[1]/DIAGNOSIS[1]', 'varchar(4096)')								as [Diagorphis]
		,t.c.value('./body[1]/MKB_DOP[1]', 'varchar(10)')									as [MkbDop]
		,t.c.value('./body[1]/DIAGNOSIS_DOP[1]', 'varchar(4096)')							as [DiagorphisDop]
		,t.c.value('./body[1]/LPU_MAIN_FIRST[1]', 'varchar(6)')								as [LpuMainFirst]
		,t.c.value('./body[1]/LPU_MAIN[1]', 'varchar(6)')									as [LpuMain]
		,t.c.value('./body[1]/POST_FIO[1]', 'varchar(255)')									as [PostFio]
		,t.c.value('./body[1]/REGISTER_ID[1]', 'int')										as [RegisterId]
        INTO #ORPHAN_REGISTER
        from @Xml.nodes('ORPHAN_REGISTER') T(c)
        where @OperationType <> @FTLOAD		

		if (@OperationType = @FTLOAD)
        BEGIN

		delete from [APS].[T_ORPHAN_REGISTER]

		INSERT INTO [APS].[T_ORPHAN_REGISTER]
			     ([OrphanRegisterID]
			     ,[Snils]
			     ,[Birthday]
			     ,[FirstName]
			     ,[LastName]
			     ,[FatherName]
			     ,[DocIssueDate]
			     ,[DocIssueOrg]
			     ,[Mkb]
			     ,[Diagnosis]
			     ,[DocSeries]
			     ,[DocNumber]
			     ,[DateBegin]
			     ,[DateEnd]
			     ,[Adress]
			     ,[Sex]
			     ,[DocType]
			     ,[MkbDop]
			     ,[DiagnosisDop]
			     ,[KodAdr]
			     ,[BirthLastName]
			     ,[Disability]
			     ,[OMSSerial]
			     ,[OMSNumber]
			     ,[F002Smo]
			     ,[LpuMainFirst]
			     ,[LpuMain]
			     ,[Federal]
			     ,[PostFio]
			     ,[RegisterId])
		select  [ID]
			   ,[Snils]
			   ,[Birthday]
			   ,[FirstName]
			   ,[LastName]
			   ,[FatherName]
			   ,[DocIssueDate]
			   ,[DocIssueOrg]
			   ,[Mkb]
			   ,[Diagnosis]
			   ,[DocSeries]
			   ,[DocNumber]
			   ,[DateBegin]
			   ,[DateEnd]
			   ,[Adress]
			   ,[Sex]
			   ,[DocType]
			   ,[MkbDop]
			   ,[DiagnosisDop]
			   ,[OKATO]
			   ,[BirthLastName]
			   ,[Disability]
			   ,[OMSSerial]
			   ,[OMSNumber]
			   ,[F002Smo]
			   ,[LpuMainFirst]
			   ,[LpuMain]
			   ,[Federal]
			   ,[PostFio]
			   ,[RegisterId] 
		from #ORPHAN_REGISTERS

        END
		else if(@OperationType = @DELETE)
        BEGIN

			delete orp 
			from [APS].[T_ORPHAN_REGISTER] orp
				join #ORPHAN_REGISTER orph on orph.[ID] = orp.OrphanRegisterID

		END
		ELSE
		BEGIN
		
			delete orp 
			from [APS].[T_ORPHAN_REGISTER] orp
				join #ORPHAN_REGISTER orph on orph.[ID] = orp.OrphanRegisterID

			
			INSERT INTO [APS].[T_ORPHAN_REGISTER]
			     ([OrphanRegisterID]
			     ,[Snils]
			     ,[Birthday]
			     ,[FirstName]
			     ,[LastName]
			     ,[FatherName]
			     ,[DocIssueDate]
			     ,[DocIssueOrg]
			     ,[Mkb]
			     ,[Diagnosis]
			     ,[DocSeries]
			     ,[DocNumber]
			     ,[DateBegin]
			     ,[DateEnd]
			     ,[Adress]
			     ,[Sex]
			     ,[DocType]
			     ,[MkbDop]
			     ,[DiagnosisDop]
			     ,[KodAdr]
			     ,[BirthLastName]
			     ,[Disability]
			     ,[OMSSerial]
			     ,[OMSNumber]
			     ,[F002Smo]
			     ,[LpuMainFirst]
			     ,[LpuMain]
			     ,[Federal]
			     ,[PostFio]
			     ,[RegisterId])
		select  [ID]
			   ,[Snils]
			   ,[Birthday]
			   ,[FirstName]
			   ,[LastName]
			   ,[FatherName]
			   ,[DocIssueDate]
			   ,[DocIssueOrg]
			   ,[Mkb]
			   ,[Diagnosis]
			   ,[DocSeries]
			   ,[DocNumber]
			   ,[DateBegin]
			   ,[DateEnd]
			   ,[Adress]
			   ,[Sex]
			   ,[DocType]
			   ,[MkbDop]
			   ,[DiagnosisDop]
			   ,[OKATO]
			   ,[BirthLastName]
			   ,[Disability]
			   ,[OMSSerial]
			   ,[OMSNumber]
			   ,[F002Smo]
			   ,[LpuMainFirst]
			   ,[LpuMain]
			   ,[Federal]
			   ,[PostFio]
			   ,[RegisterId] 
		from #ORPHAN_REGISTER

		END

		DROP TABLE #ORPHAN_REGISTER
		DROP TABLE #ORPHAN_REGISTERS
    END
END