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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-02-07
-- Description: Выгрузка льготников по специальной льготе из RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetSpecialBenefitsList]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = null
	,@SNILS		varchar(14) = null
	,@Birthday	datetime    = null
	,@DocSeries	varchar(30) = null
	,@DocNumber	varchar(30) = null
	,@FirstName	varchar(60) = null
	,@LastName	varchar(60) = null
	,@FatherName varchar(60) = null
	,@Benefits varchar(max) = 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 null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'SPECIAL_BENEFITS_REGISTR'
              ,@OwnerID = @ID

        --выясняем тип операции
        IF (@ID is null)
        BEGIN
            select @OperationType = @FTLOAD
        END
        ELSE IF(@IsNew = 1)
        BEGIN
            select @OperationType = @INSERT
        END
        ELSE IF (@IsNew = 0 and exists (select * from RDS.T_SPECIAL_BENEFITS_REGISTR n where n.SpecialBenefitsRegistrID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from RDS.T_SPECIAL_BENEFITS_REGISTR n where n.SpecialBenefitsRegistrID = @ID))
        BEGIN
            select @OperationType = @DELETE
			select @SNILS = case
							   when RTRIM(LTRIM(REPLACE(REPLACE(@SNILS, '0', ''), '-', ''))) = '' then null
							   else @SNILS
							end
            select @Result = 
            (
                select @OperationType						as 'header/TYPE'
                      ,@ID									as 'header/ID'
					  ,@SNILS								as 'body/SNILS'
					  ,convert(varchar(10), @Birthday, 104)	as 'body/BIRTHDAY'
					  ,@DocSeries							as 'body/DOC_SERIES'
					  ,@DocNumber							as 'body/DOC_NUMBER'
					  ,@FirstName							as 'body/FIRST_NAME'
					  ,@LastName							as 'body/LAST_MANE'
					  ,@FatherName							as 'body/FATHER_NAME'
					  ,@Benefits							as 'body/BENIFITS'
                FOR XML PATH(''),
                ROOT('SPECIAL_BENEFITS_REGISTR')
            ) 
        END

            -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
			SELECT @Result = 
			( 
			    select @OperationType				as 'header/TYPE'
				,(
			      select sbr.[SpecialBenefitsRegistrID]						as 'SPECIAL_BENEFITS_REGISTR_ITEM/ID'
						,sbr.[Adress]										as 'SPECIAL_BENEFITS_REGISTR_ITEM/ADRESS'
						,sbr.[Comment]										as 'SPECIAL_BENEFITS_REGISTR_ITEM/COMMENT'
						,convert(varchar(10),sbr.[DateOfChange], 104)		as 'SPECIAL_BENEFITS_REGISTR_ITEM/CHANGE_DATE'
						,convert(varchar(10),sbr.[DateOfDeclusion], 104)	as 'SPECIAL_BENEFITS_REGISTR_ITEM/DECLUSION_DATE'
						,convert(varchar(10),sbr.[DateOfInclusion], 104)	as 'SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_DATE'
						,sbr.[OmsNumber]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/OMS_NUMBER'
						,sbr.[OmsSerial]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/OMS_SERIAL'
						,sbr.[UniqueNumber]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/UNIQUE_NUMBER'
						,sbr.[Mkb]											as 'SPECIAL_BENEFITS_REGISTR_ITEM/MKB'
						,sbr.[Diagnosis]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/DIAGNOSIS'
						,sbr.[DocSeries]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/DOC_SERIES'
						,sbr.[DocNumber]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/DOC_NUMBER'
						,convert(varchar(10), sbr.[Birthday], 104)			as 'SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY'
						,sbr.[FirstName]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/FIRST_NAME'
						,sbr.[LastName]										as 'SPECIAL_BENEFITS_REGISTR_ITEM/LAST_MANE'
						,sbr.[FatherName]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/FATHER_NAME'
						,convert(varchar(10), sbr.[DocIssueDate], 104)		as 'SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_DATE'
						,sbr.[DocIssueOrg]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/DOC_ISSUE_ORG'
						,eDoc.Code      									as 'SPECIAL_BENEFITS_REGISTR_ITEM/DOC_TYPE'
						,sbr.[InclusionLPU]									as 'SPECIAL_BENEFITS_REGISTR_ITEM/INCLUSION_LPU'
						,eSex.Code											as 'SPECIAL_BENEFITS_REGISTR_ITEM/SEX'
						,sbr.[BirthdayLastName]								as 'SPECIAL_BENEFITS_REGISTR_ITEM/BIRTHDAY_LAST_NAME'
						,sbr.[F002Smo]										as 'SPECIAL_BENEFITS_REGISTR_ITEM/SMO'
						,case
			                 when RTRIM(LTRIM(REPLACE(REPLACE(sbr.SNILS, '0', ''), '-', ''))) = '' then null
			                 else sbr.SNILS 
						 end												as 'SPECIAL_BENEFITS_REGISTR_ITEM/SNILS'
						,b.BenefitCode										as 'SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS'
						,b.BenefitViews										as 'SPECIAL_BENEFITS_REGISTR_ITEM/BENIFITS_VIEWS'
				from [RDS].T_SPECIAL_BENEFITS_REGISTR sbr
			    join [RDS].T_BENEFITS b on b.BenefitsID = sbr.Benefits
                join [VCLib].T_ENUMS eSex on eSex.EnumID = sbr.Sex
                join [VCLib].T_ENUMS eDoc on eDoc.EnumID = sbr.DocumentType
			for xml path(''),
                        type
                  ) as 'body/SPECIAL_BENEFITS_REGISTR_ITEMS'
            for xml path(''),
            root('SPECIAL_BENEFITS_REGISTR')
            )
			END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN 
				SELECT @Result = 
					( 
					    select @OperationType										as 'header/TYPE'
								,sbr.[SpecialBenefitsRegistrID]						as 'header/ID'
								,sbr.[Adress]										as 'body/ADRESS'
								,sbr.[Comment]										as 'body/COMMENT'
								,convert(varchar(10),sbr.[DateOfChange], 104)		as 'body/CHANGE_DATE'
								,convert(varchar(10),sbr.[DateOfDeclusion], 104)	as 'body/DECLUSION_DATE'
								,convert(varchar(10),sbr.[DateOfInclusion], 104)	as 'body/INCLUSION_DATE'
								,sbr.[OmsNumber]									as 'body/OMS_NUMBER'
								,sbr.[OmsSerial]									as 'body/OMS_SERIAL'
								,sbr.[UniqueNumber]									as 'body/UNIQUE_NUMBER'
								,sbr.[Mkb]											as 'body/MKB'
								,sbr.[Diagnosis]									as 'body/DIAGNOSIS'
								,sbr.[DocSeries]									as 'body/DOC_SERIES'
								,sbr.[DocNumber]									as 'body/DOC_NUMBER'
								,convert(varchar(10), sbr.[Birthday], 104)			as 'body/BIRTHDAY'
								,sbr.[FirstName]									as 'body/FIRST_NAME'
								,sbr.[LastName]										as 'body/LAST_MANE'
								,sbr.[FatherName]									as 'body/FATHER_NAME'
								,convert(varchar(10), sbr.[DocIssueDate], 104)		as 'body/DOC_ISSUE_DATE'
								,sbr.[DocIssueOrg]									as 'body/DOC_ISSUE_ORG'
								,eDoc.Code	       									as 'body/DOC_TYPE'
								,sbr.[InclusionLPU]									as 'body/INCLUSION_LPU'
								,eSex.Code											as 'body/SEX'
								,sbr.[BirthdayLastName]								as 'body/BIRTHDAY_LAST_NAME'
								,sbr.[F002Smo]										as 'body/SMO'
								,case
					                 when RTRIM(LTRIM(REPLACE(REPLACE(sbr.SNILS, '0', ''), '-', ''))) = '' then null
					                 else sbr.SNILS 
								 end												as 'body/SNILS'
								,b.BenefitCode										as 'body/BENIFITS'
								,b.BenefitViews										as 'body/BENIFITS_VIEWS'
						from [RDS].T_SPECIAL_BENEFITS_REGISTR sbr
							join [RDS].T_BENEFITS b on b.BenefitsID = sbr.Benefits
                            join [VCLib].T_ENUMS eSex on eSex.EnumID = sbr.Sex
                            join [VCLib].T_ENUMS eDoc on eDoc.EnumID = sbr.DocumentType
						where sbr.[SpecialBenefitsRegistrID] = @ID
					for xml path(''),
				    root('SPECIAL_BENEFITS_REGISTR')
				    )
		END

        -- Отправка
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where (@QueueName is not null and s.ServerName = @QueueName) or (@QueueName is null and s.ServerType in (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
    END
END
GO