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

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-02-07
-- Description: Загрузка льготников по специальной льготе из RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_GetSetSpecialBenefitsList]
    @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('SPECIAL_BENEFITS_REGISTR') T(c)

		-- Generate table #SPECIAL_BENEFITS_REGISTR
        select t.c.value('./ID[1]', 'uniqueidentifier')								as [ID]
		,t.c.value('./ADRESS[1]', 'varchar(256)')									as [Adress]
		,t.c.value('./COMMENT[1]', 'varchar(1024)')									as [Comment]
		,convert(datetime, t.c.value('./CHANGE_DATE[1]', 'varchar(10)'), 104)		as [DateOfChange]
		,convert(datetime, t.c.value('./DECLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfDeclusion]
		,convert(datetime, t.c.value('./INCLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfInclusion]
		,t.c.value('./OMS_NUMBER[1]', 'varchar(30)')								as [OmsNumber]
		,t.c.value('./OMS_SERIAL[1]', 'varchar(30)')								as [OmsSerial]
		,t.c.value('./UNIQUE_NUMBER[1]', 'varchar(128)')							as [UniqueNumber]
		,t.c.value('./MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')								as [Diagnosis]
		,t.c.value('./DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,convert(datetime, t.c.value('./BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,t.c.value('./FIRST_NAME[1]', 'varchar(60)')								as [FirstName]
		,t.c.value('./LAST_MANE[1]', 'varchar(60)')									as [LastName]
		,t.c.value('./FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,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 [DocumentType]
		,t.c.value('./INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,t.c.value('./SEX[1]', 'int')												as [Sex]
		,t.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./BENIFITS[1]', 'varchar(max)')									as [Benefits]
		,t.c.value('./BENIFITS_VIEWS[1]', 'int')									as [BenefitsViews]
		,t.c.value('./BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #SPECIAL_BENEFITS_REGISTR
        from @Xml.nodes('SPECIAL_BENEFITS_REGISTR/body/SPECIAL_BENEFITS_REGISTR_ITEMS/SPECIAL_BENEFITS_REGISTR_ITEM') T(c)
        where @OperationType = @FTLOAD

		-- Generate table #SPECIAL_BENEFITS_REGISTR
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')							as [ID]
		,t.c.value('./body[1]/ADRESS[1]', 'varchar(256)')									as [Adress]
		,t.c.value('./body[1]/COMMENT[1]', 'varchar(1024)')									as [Comment]
		,convert(datetime, t.c.value('./body[1]/CHANGE_DATE[1]', 'varchar(10)'), 104)		as [DateOfChange]
		,convert(datetime, t.c.value('./body[1]/DECLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfDeclusion]
		,convert(datetime, t.c.value('./body[1]/INCLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfInclusion]
		,t.c.value('./body[1]/OMS_NUMBER[1]', 'varchar(30)')								as [OmsNumber]
		,t.c.value('./body[1]/OMS_SERIAL[1]', 'varchar(30)')								as [OmsSerial]
		,t.c.value('./body[1]/UNIQUE_NUMBER[1]', 'varchar(128)')							as [UniqueNumber]
		,t.c.value('./body[1]/MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./body[1]/DIAGNOSIS[1]', 'varchar(4096)')								as [Diagnosis]
		,t.c.value('./body[1]/DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./body[1]/DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,convert(datetime, t.c.value('./body[1]/BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,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]/FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,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 [DocumentType]
		,t.c.value('./body[1]/INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,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]/BENIFITS[1]', 'varchar(max)')									as [Benefits]
		,t.c.value('./body[1]/BENIFITS_VIEWS[1]', 'int')									as [BenefitsViews]
		,t.c.value('./body[1]/BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./body[1]/SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #SPECIAL_BENEFITS_REG
        from @Xml.nodes('SPECIAL_BENEFITS_REGISTR') T(c)
        where @OperationType <> @FTLOAD		
		
		if (@OperationType = @FTLOAD)
        BEGIN

			delete from [APS].T_SPECIAL_BENEFITS_REGISTR

			INSERT INTO [APS].[T_SPECIAL_BENEFITS_REGISTR]
				       ([SpecialBenefitsRegistrID]
				       ,[Adress]
				       ,[Comment]
				       ,[DateOfChange]
				       ,[DateOfDeclusion]
				       ,[DateOfInclusion]
				       ,[OmsNumber]
				       ,[OmsSerial]
				       ,[UniqueNumber]
				       ,[Mkb]
				       ,[Diagnosis]
				       ,[DocSeries]
				       ,[DocNumber]
				       ,[Birthday]
				       ,[FirstName]
				       ,[LastName]
				       ,[FatherName]
				       ,[DocIssueDate]
				       ,[DocIssueOrg]
				       ,[DocumentType]
				       ,[InclusionLPU]
				       ,[Sex]
				       ,[SNILS]
				       ,[Benefits]
					   ,[BirthdayLastName]
					   ,[F002Smo])
				SELECT
				        sbr.[ID]
				       ,sbr.[Adress]
				       ,sbr.[Comment]
				       ,sbr.[DateOfChange]
				       ,sbr.[DateOfDeclusion]
				       ,sbr.[DateOfInclusion]
				       ,sbr.[OmsNumber]
				       ,sbr.[OmsSerial]
				       ,sbr.[UniqueNumber]
				       ,sbr.[Mkb]
				       ,sbr.[Diagnosis]
				       ,sbr.[DocSeries]
				       ,sbr.[DocNumber]
				       ,sbr.[Birthday]
				       ,sbr.[FirstName]
				       ,sbr.[LastName]
				       ,sbr.[FatherName]
				       ,sbr.[DocIssueDate]
				       ,sbr.[DocIssueOrg]
				       ,(select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = sbr.DocumentType)
				       ,sbr.[InclusionLPU]
					   ,(select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = sbr.Sex)
				       ,sbr.[SNILS]
				       ,(select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = sbr.[Benefits] and b.BenefitViews = sbr.BenefitsViews)
					   ,sbr.[BirthdayLastName]
					   ,sbr.[F002Smo]
				from #SPECIAL_BENEFITS_REGISTR sbr

        END
		else if(@OperationType = @DELETE)
        BEGIN
			delete sbr 
			from [APS].T_SPECIAL_BENEFITS_REGISTR sbr
				join #SPECIAL_BENEFITS_REG sb on sb.ID = sbr.[SpecialBenefitsRegistrID]
		END
		ELSE
		BEGIN

			update sbr set 
			       sbr.[Adress]				= n.[Adress]
			      ,sbr.[Comment]			= n.[Comment]
			      ,sbr.[DateOfChange]		= n.[DateOfChange]
			      ,sbr.[DateOfDeclusion]	= n.[DateOfDeclusion]
			      ,sbr.[DateOfInclusion]	= n.[DateOfInclusion]
			      ,sbr.[OmsNumber]			= n.[OmsNumber]
			      ,sbr.[OmsSerial]			= n.[OmsSerial]
			      ,sbr.[UniqueNumber]		= n.[UniqueNumber]
			      ,sbr.[Mkb]				= n.[Mkb]
			      ,sbr.[Diagnosis]			= n.[Diagnosis]
			      ,sbr.[DocSeries]			= n.[DocSeries]
			      ,sbr.[DocNumber]			= n.[DocNumber]
			      ,sbr.[Birthday]			= n.[Birthday]
			      ,sbr.[FirstName]			= n.[FirstName]
			      ,sbr.[LastName]			= n.[LastName]
			      ,sbr.[FatherName]			= n.[FatherName]
			      ,sbr.[DocIssueDate]		= n.[DocIssueDate]
			      ,sbr.[DocIssueOrg]		= n.[DocIssueOrg]
			      ,sbr.[DocumentType]		= (select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = n.DocumentType)
			      ,sbr.[InclusionLPU]		= n.[InclusionLPU]
			      ,sbr.[Sex]				= (select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = n.Sex)
			      ,sbr.[SNILS]				= n.[SNILS]
			      ,sbr.[Benefits]			= (select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = n.[Benefits] and b.BenefitViews = n.BenefitsViews)
				  ,sbr.[BirthdayLastName]	= n.[BirthdayLastName]
				  ,sbr.[F002Smo]			= n.[F002Smo]
			  FROM [APS].[T_SPECIAL_BENEFITS_REGISTR] sbr
			  join #SPECIAL_BENEFITS_REG n on n.ID = sbr.SpecialBenefitsRegistrID

			INSERT INTO [APS].[T_SPECIAL_BENEFITS_REGISTR]
				       ([SpecialBenefitsRegistrID]
				       ,[Adress]
				       ,[Comment]
				       ,[DateOfChange]
				       ,[DateOfDeclusion]
				       ,[DateOfInclusion]
				       ,[OmsNumber]
				       ,[OmsSerial]
				       ,[UniqueNumber]
				       ,[Mkb]
				       ,[Diagnosis]
				       ,[DocSeries]
				       ,[DocNumber]
				       ,[Birthday]
				       ,[FirstName]
				       ,[LastName]
				       ,[FatherName]
				       ,[DocIssueDate]
				       ,[DocIssueOrg]
				       ,[DocumentType]
				       ,[InclusionLPU]
				       ,[Sex]
				       ,[SNILS]
				       ,[Benefits]
					   ,[BirthdayLastName]
					   ,[F002Smo])
				SELECT
				        sbr.[ID]
				       ,sbr.[Adress]
				       ,sbr.[Comment]
				       ,sbr.[DateOfChange]
				       ,sbr.[DateOfDeclusion]
				       ,sbr.[DateOfInclusion]
				       ,sbr.[OmsNumber]
				       ,sbr.[OmsSerial]
				       ,sbr.[UniqueNumber]
				       ,sbr.[Mkb]
				       ,sbr.[Diagnosis]
				       ,sbr.[DocSeries]
				       ,sbr.[DocNumber]
				       ,sbr.[Birthday]
				       ,sbr.[FirstName]
				       ,sbr.[LastName]
				       ,sbr.[FatherName]
				       ,sbr.[DocIssueDate]
				       ,sbr.[DocIssueOrg]
				       ,(select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 105 and e.Code = sbr.DocumentType)
				       ,sbr.[InclusionLPU]
				       ,(select TOP 1 e.EnumID from VCLIB.T_ENUMS e WHERE e.EnumTypeID = 103 and e.Code = sbr.Sex)
				       ,sbr.[SNILS]
				       ,(select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = sbr.[Benefits] and b.BenefitViews = sbr.BenefitsViews)
					   ,sbr.[BirthdayLastName]
					   ,sbr.[F002Smo]
				from #SPECIAL_BENEFITS_REG sbr
				where not exists
				    (
				        select *
				        from [APS].T_SPECIAL_BENEFITS_REGISTR s
				        where s.SpecialBenefitsRegistrID = sbr.ID
				    )

		END
    END
END
