IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetNosology]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetNosology]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-17
-- Description: Загрузка льготников по 7 ноз из RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetNosology]
    @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('NOSOLOGY') T(c)

		-- Generate table #NOSOLOGY_ITEMS
        select t.c.value('./ID[1]', 'uniqueidentifier')								as [ID]
		,t.c.value('./ADRESS[1]', 'varchar(256)')									as [Adress]
		,convert(datetime, t.c.value('./AUTO_DATE_CHANGE[1]', 'varchar(10)'), 104)	as [AutoDateOfChange]
		,t.c.value('./CARD_STATUS[1]', 'varchar(256)')								as [CardStatus]
		,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('./EXECUTIVE_AUTHORITY[1]', 'varchar(256)')						as [ExecutiveAuthority]
		,t.c.value('./HEAD[1]', 'varchar(256)')										as [Head]
		,t.c.value('./OMS_NUMBER[1]', 'varchar(30)')								as [OmsNumber]
		,t.c.value('./OMS_SERIAL[1]', 'varchar(30)')								as [OmsSerial]
		,t.c.value('./OPERATION_CODE[1]', 'varchar(128)')							as [OperationCode]
		,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_NAME[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('./DECLUSION_LPU[1]', 'varchar(6)')								as [DeclusionLPU]
		,t.c.value('./DOC_TYPE[1]', 'int')											as [DocumentType]
		,t.c.value('./EX178[1]', 'int')												as [ExemptionBy178]
		,t.c.value('./EX890[1]', 'int')												as [ExemptionBy890]
		,t.c.value('./INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,t.c.value('./REGION[1]', 'varchar(13)')									as [Region]
		,t.c.value('./SEX[1]', 'int')												as [Sex]
		,t.c.value('./RF_ACCOUNT[1]', 'int')										as [SubjectOfRFAccounted]
		,t.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./BENIFITS[1]', 'varchar(max)')									as [Benefits]
		,t.c.value('./BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./THERAPY_RESISTANCE[1]', 'int')								as [TherapyResistance]
		,convert(datetime, t.c.value('./DEATH_DATE[1]', 'varchar(10)'), 104)		as [DeathDate]
		,t.c.value('./OKATO[1]', 'varchar(max)')									as [OKATO]
		,t.c.value('./SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #NOSOLOGYS
        from @Xml.nodes('NOSOLOGY/body/NOSOLOGY_ITEMS/NOSOLOGY_ITEM') T(c)
        where @OperationType = @FTLOAD

		-- Generate table #NOSOLOGY_ITEMS
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')							as [ID]
		,t.c.value('./body[1]/ADRESS[1]', 'varchar(256)')									as [Adress]
		,convert(datetime, t.c.value('./body[1]/AUTO_DATE_CHANGE[1]', 'varchar(10)'), 104)	as [AutoDateOfChange]
		,t.c.value('./body[1]/CARD_STATUS[1]', 'varchar(256)')								as [CardStatus]
		,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]/EXECUTIVE_AUTHORITY[1]', 'varchar(256)')						as [ExecutiveAuthority]
		,t.c.value('./body[1]/HEAD[1]', 'varchar(256)')										as [Head]
		,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]/OPERATION_CODE[1]', 'varchar(128)')							as [OperationCode]
		,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_NAME[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]/DECLUSION_LPU[1]', 'varchar(6)')								as [DeclusionLPU]
		,t.c.value('./body[1]/DOC_TYPE[1]', 'int')											as [DocumentType]
		,t.c.value('./body[1]/EX178[1]', 'int')												as [ExemptionBy178]
		,t.c.value('./body[1]/EX890[1]', 'int')												as [ExemptionBy890]
		,t.c.value('./body[1]/INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,t.c.value('./body[1]/REGION[1]', 'varchar(13)')									as [Region]
		,t.c.value('./body[1]/SEX[1]', 'int')												as [Sex]
		,t.c.value('./body[1]/RF_ACCOUNT[1]', 'int')										as [SubjectOfRFAccounted]
		,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]/BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./body[1]/DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./body[1]/THERAPY_RESISTANCE[1]', 'int')								as [TherapyResistance]
		,convert(datetime, t.c.value('./body[1]/DEATH_DATE[1]', 'varchar(10)'), 104)			as [DeathDate]
		,t.c.value('./body[1]/OKATO[1]', 'varchar(max)')									as [OKATO]
		,t.c.value('./body[1]/SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #NOSOLOGY
        from @Xml.nodes('NOSOLOGY') T(c)
        where @OperationType <> @FTLOAD

		if (@OperationType = @FTLOAD)
        BEGIN

			delete from [APS].T_NOSOLOGY

			INSERT INTO [APS].[T_NOSOLOGY]
				       ([NosologyID]
				       ,[Adress]
				       ,[AutoDateOfChange]
				       ,[CardStatus]
				       ,[Comment]
				       ,[DateOfChange]
				       ,[DateOfDeclusion]
				       ,[DateOfInclusion]
				       ,[ExecutiveAuthority]
				       ,[Head]
				       ,[OmsNumber]
				       ,[OmsSerial]
				       ,[OperationCode]
				       ,[UniqueNumber]
				       ,[Mkb]
				       ,[Diagnosis]
				       ,[DocSeries]
				       ,[DocNumber]
				       ,[Birthday]
				       ,[FirstName]
				       ,[LastName]
				       ,[FatherName]
				       ,[DocIssueDate]
				       ,[DocIssueOrg]
				       ,[DeclusionLPU]
				       ,[DocumentType]
				       ,[ExemptionBy178]
				       ,[ExemptionBy890]
				       ,[InclusionLPU]
				       ,[Region]
				       ,[Sex]
				       ,[SubjectOfRFAccounted]
				       ,[SNILS]
				       ,[Benefits]
					   ,[BirthdayLastName]
					   ,[Disability]
					   ,[TherapyResistance]
					   ,[DeathDate]
					   ,[OKATO]
					   ,[F002Smo])
				SELECT
				        nos.[ID]
				       ,nos.[Adress]
				       ,nos.[AutoDateOfChange]
				       ,nos.[CardStatus]
				       ,nos.[Comment]
				       ,nos.[DateOfChange]
				       ,nos.[DateOfDeclusion]
				       ,nos.[DateOfInclusion]
				       ,nos.[ExecutiveAuthority]
				       ,nos.[Head]
				       ,nos.[OmsNumber]
				       ,nos.[OmsSerial]
				       ,nos.[OperationCode]
				       ,nos.[UniqueNumber]
				       ,nos.[Mkb]
				       ,nos.[Diagnosis]
				       ,nos.[DocSeries]
				       ,nos.[DocNumber]
				       ,nos.[Birthday]
				       ,nos.[FirstName]
				       ,nos.[LastName]
				       ,nos.[FatherName]
				       ,nos.[DocIssueDate]
				       ,nos.[DocIssueOrg]
				       ,nos.[DeclusionLPU]
				       ,nos.[DocumentType]
				       ,nos.[ExemptionBy178]
				       ,nos.[ExemptionBy890]
				       ,nos.[InclusionLPU]
				       ,(select TOP 1 r.RegionID from [APS].T_REGION r where r.Code = nos.[Region])
				       ,nos.[Sex]
				       ,nos.[SubjectOfRFAccounted]
				       ,nos.[SNILS]
				       ,(select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19006)
					   ,nos.[BirthdayLastName]
					   ,ISNULL(nos.[Disability], 0)
					   ,nos.[TherapyResistance]
					   ,nos.[DeathDate]
					   ,nos.[OKATO]
					   ,nos.[F002Smo]
				from #NOSOLOGYS nos

        END
		else if(@OperationType = @DELETE)
        BEGIN

			delete n 
			from [APS].T_NOSOLOGY n
				join #NOSOLOGY nos on nos.ID = n.NosologyID

		END
		ELSE
		BEGIN

			update nos set 
			       nos.[Adress]					= n.[Adress]
			      ,nos.[AutoDateOfChange]		= n.[AutoDateOfChange]
			      ,nos.[CardStatus]				= n.[CardStatus]
			      ,nos.[Comment]				= n.[Comment]
			      ,nos.[DateOfChange]			= n.[DateOfChange]
			      ,nos.[DateOfDeclusion]		= n.[DateOfDeclusion]
			      ,nos.[DateOfInclusion]		= n.[DateOfInclusion]
			      ,nos.[ExecutiveAuthority]		= n.[ExecutiveAuthority]
			      ,nos.[Head]					= n.[Head]
			      ,nos.[OmsNumber]				= n.[OmsNumber]
			      ,nos.[OmsSerial]				= n.[OmsSerial]
			      ,nos.[OperationCode]			= n.[OperationCode]
			      ,nos.[UniqueNumber]			= n.[UniqueNumber]
			      ,nos.[Mkb]					= n.[Mkb]
			      ,nos.[Diagnosis]				= n.[Diagnosis]
			      ,nos.[DocSeries]				= n.[DocSeries]
			      ,nos.[DocNumber]				= n.[DocNumber]
			      ,nos.[Birthday]				= n.[Birthday]
			      ,nos.[FirstName]				= n.[FirstName]
			      ,nos.[LastName]				= n.[LastName]
			      ,nos.[FatherName]				= n.[FatherName]
			      ,nos.[DocIssueDate]			= n.[DocIssueDate]
			      ,nos.[DocIssueOrg]			= n.[DocIssueOrg]
			      ,nos.[DeclusionLPU]			= n.[DeclusionLPU]
			      ,nos.[DocumentType]			= n.[DocumentType]
			      ,nos.[ExemptionBy178]			= n.[ExemptionBy178]
			      ,nos.[ExemptionBy890]			= n.[ExemptionBy890]
			      ,nos.[InclusionLPU]			= n.[InclusionLPU]
			      ,nos.[Region]					= (select TOP 1 r.RegionID from [APS].T_REGION r where r.Code = n.[Region])
			      ,nos.[Sex]					= n.[Sex]
			      ,nos.[SubjectOfRFAccounted]	= n.[SubjectOfRFAccounted]
			      ,nos.[SNILS]					= n.[SNILS]
			      ,nos.[Benefits]				= (select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = n.[Benefits] and b.BenefitViews = 19006)
				  ,nos.[BirthdayLastName]		= n.[BirthdayLastName]
				  ,nos.[Disability]				= ISNULL(n.[Disability], 0)
				  ,nos.[TherapyResistance]		= n.[TherapyResistance]
				  ,nos.[DeathDate]				= n.[DeathDate]
				  ,nos.[OKATO]					= n.[OKATO]
				  ,nos.[F002Smo]				= n.[F002Smo]
			  FROM [APS].[T_NOSOLOGY] nos
				join #NOSOLOGY n on n.ID = nos.NosologyID

			INSERT INTO [APS].[T_NOSOLOGY]
				       ([NosologyID]
				       ,[Adress]
				       ,[AutoDateOfChange]
				       ,[CardStatus]
				       ,[Comment]
				       ,[DateOfChange]
				       ,[DateOfDeclusion]
				       ,[DateOfInclusion]
				       ,[ExecutiveAuthority]
				       ,[Head]
				       ,[OmsNumber]
				       ,[OmsSerial]
				       ,[OperationCode]
				       ,[UniqueNumber]
				       ,[Mkb]
				       ,[Diagnosis]
				       ,[DocSeries]
				       ,[DocNumber]
				       ,[Birthday]
				       ,[FirstName]
				       ,[LastName]
				       ,[FatherName]
				       ,[DocIssueDate]
				       ,[DocIssueOrg]
				       ,[DeclusionLPU]
				       ,[DocumentType]
				       ,[ExemptionBy178]
				       ,[ExemptionBy890]
				       ,[InclusionLPU]
				       ,[Region]
				       ,[Sex]
				       ,[SubjectOfRFAccounted]
				       ,[SNILS]
				       ,[Benefits]
					   ,[BirthdayLastName]
					   ,[Disability]
					   ,[TherapyResistance]
					   ,[DeathDate]
					   ,[OKATO]
					   ,[F002Smo])
				SELECT
				        nos.[ID]
				       ,nos.[Adress]
				       ,nos.[AutoDateOfChange]
				       ,nos.[CardStatus]
				       ,nos.[Comment]
				       ,nos.[DateOfChange]
				       ,nos.[DateOfDeclusion]
				       ,nos.[DateOfInclusion]
				       ,nos.[ExecutiveAuthority]
				       ,nos.[Head]
				       ,nos.[OmsNumber]
				       ,nos.[OmsSerial]
				       ,nos.[OperationCode]
				       ,nos.[UniqueNumber]
				       ,nos.[Mkb]
				       ,nos.[Diagnosis]
				       ,nos.[DocSeries]
				       ,nos.[DocNumber]
				       ,nos.[Birthday]
				       ,nos.[FirstName]
				       ,nos.[LastName]
				       ,nos.[FatherName]
				       ,nos.[DocIssueDate]
				       ,nos.[DocIssueOrg]
				       ,nos.[DeclusionLPU]
				       ,nos.[DocumentType]
				       ,nos.[ExemptionBy178]
				       ,nos.[ExemptionBy890]
				       ,nos.[InclusionLPU]
				       ,(select TOP 1 r.RegionID from [APS].T_REGION r where r.Code = nos.[Region])
				       ,nos.[Sex]
				       ,nos.[SubjectOfRFAccounted]
				       ,nos.[SNILS]
				       ,(select TOP 1 b.BenefitsID from [APS].T_BENEFITS b where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19006)
					   ,nos.[BirthdayLastName]
					   ,ISNULL(nos.[Disability], 0)
					   ,nos.[TherapyResistance]
					   ,nos.[DeathDate]
					   ,nos.[OKATO]
					   ,nos.[F002Smo]
				from #NOSOLOGY nos
				where not exists
				    (
				        select *
				        from [APS].T_NOSOLOGY n
				        where n.NosologyID = nos.ID
				    )
				
		END
		DROP TABLE #NOSOLOGY
		DROP TABLE #NOSOLOGYS
    END
END
GO