IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetNosologyList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetNosologyList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-17
-- Description: Выгрузка/загрузка регистра ВЗН льготников RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetNosologyList]
    @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 = 'NOSOLOGY'
              ,@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_NOSOLOGY_BENEFITS n where n.NosologyBenefitsID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from RDS.T_NOSOLOGY_BENEFITS n where n.NosologyBenefitsID = @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('NOSOLOGY')
            ) 
        END

         -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
        SELECT @Result = 
        ( 
            select @OperationType											as 'header/TYPE'
			,(
              select nos.[NosologyID]										as 'NOSOLOGY_ITEM/ID'
					,nos.[Adress]											as 'NOSOLOGY_ITEM/ADRESS'
					,convert(varchar(10), nos.[AutoDateOfChange], 104)		as 'NOSOLOGY_ITEM/AUTO_DATE_CHANGE'
					,nb.[CardStatus]										as 'NOSOLOGY_ITEM/CARD_STATUS'
					,nos.[Comment]											as 'NOSOLOGY_ITEM/COMMENT'
					,convert(varchar(10),nos.[DateOfChange], 104)			as 'NOSOLOGY_ITEM/CHANGE_DATE'
					,convert(varchar(10),nos.[DateOfDeclusion], 104)		as 'NOSOLOGY_ITEM/DECLUSION_DATE'
					,convert(varchar(10),nos.[DateOfInclusion], 104)		as 'NOSOLOGY_ITEM/INCLUSION_DATE'
					,nos.[ExecutiveAuthority]								as 'NOSOLOGY_ITEM/EXECUTIVE_AUTHORITY'
					,nb.[Head]												as 'NOSOLOGY_ITEM/HEAD'
					,nos.[OmsNumber]										as 'NOSOLOGY_ITEM/OMS_NUMBER'
					,nos.[OmsSerial]										as 'NOSOLOGY_ITEM/OMS_SERIAL'
					,nos.[OperationCode]									as 'NOSOLOGY_ITEM/OPERATION_CODE'
					,nos.PAC_ID												as 'NOSOLOGY_ITEM/UNIQUE_NUMBER'
					,nb.[Mkb]												as 'NOSOLOGY_ITEM/MKB'
					,nb.[Diagnosis]											as 'NOSOLOGY_ITEM/DIAGNOSIS'
					,nos.[DocSeries]										as 'NOSOLOGY_ITEM/DOC_SERIES'
					,nos.[DocNumber]										as 'NOSOLOGY_ITEM/DOC_NUMBER'
					,convert(varchar(10), nos.[Birthday], 104)				as 'NOSOLOGY_ITEM/BIRTHDAY'
					,nos.[FirstName]										as 'NOSOLOGY_ITEM/FIRST_NAME'
					,nos.[LastName]											as 'NOSOLOGY_ITEM/LAST_NAME'
					,nos.[FatherName]										as 'NOSOLOGY_ITEM/FATHER_NAME'
					,convert(varchar(10), nos.[DocIssueDate], 104)			as 'NOSOLOGY_ITEM/DOC_ISSUE_DATE'
					,nos.[DocIssueOrg]										as 'NOSOLOGY_ITEM/DOC_ISSUE_ORG'
					,nos.[DeclusionLPU]										as 'NOSOLOGY_ITEM/DECLUSION_LPU'
					,nos.[DocumentType]										as 'NOSOLOGY_ITEM/DOC_TYPE'
					,nos.[ExemptionBy178]									as 'NOSOLOGY_ITEM/EX178'
					,nos.[ExemptionBy890]									as 'NOSOLOGY_ITEM/EX890'
					,nos.[InclusionLPU]										as 'NOSOLOGY_ITEM/INCLUSION_LPU'
					,reg.Code												as 'NOSOLOGY_ITEM/REGION'
					,nos.[Sex]												as 'NOSOLOGY_ITEM/SEX'
					,nos.[SubjectOfRFAccounted]								as 'NOSOLOGY_ITEM/RF_ACCOUNT'
					,nos.[BirthdayLastName]									as 'NOSOLOGY_ITEM/BIRTHDAY_LAST_NAME'
					,nb.[Disability]										as 'NOSOLOGY_ITEM/DIASBILITY'
					,nb.[TherapyResistance]									as 'NOSOLOGY_ITEM/THERAPY_RESISTANCE'
					,convert(varchar(10), nos.[DeathDate], 104)				as 'NOSOLOGY_ITEM/DEATH_DATE'
					,nos.[OKATO]											as 'NOSOLOGY_ITEM/OKATO'
					,nos.[F002Smo]											as 'NOSOLOGY_ITEM/SMO'
					,case 
						when RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = '' then null
						else nos.SNILS 
					 end													as 'NOSOLOGY_ITEM/SNILS'
					,b.BenefitCode											as 'NOSOLOGY_ITEM/BENIFITS'
			from RDS.T_NOSOLOGY_BENEFITS nb
				join [RDS].T_NOSOLOGY nos on nos.NosologyID = nb.Nosology	
				left join [RDS].T_REGION reg on reg.RegionID = nos.Region
				join [RDS].T_BENEFITS b on b.BenefitsID = nb.Benefits
			for xml path(''),
                        type
                  ) as 'body/NOSOLOGY_ITEMS'
            for xml path(''),
            root('NOSOLOGY')
            ) 
			END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN
		SELECT @Result = 
        ( 
            select @OperationType											as 'header/TYPE'
					,nos.[NosologyID]										as 'header/ID'
					,nos.[Adress]											as 'body/ADRESS'
					,convert(varchar(10), nos.[AutoDateOfChange], 104)		as 'body/AUTO_DATE_CHANGE'
					,nb.[CardStatus]										as 'body/CARD_STATUS'
					,nos.[Comment]											as 'body/COMMENT'
					,convert(varchar(10),nos.[DateOfChange], 104)			as 'body/CHANGE_DATE'
					,convert(varchar(10),nos.[DateOfDeclusion], 104)		as 'body/DECLUSION_DATE'
					,convert(varchar(10),nos.[DateOfInclusion], 104)		as 'body/INCLUSION_DATE'
					,nos.[ExecutiveAuthority]								as 'body/EXECUTIVE_AUTHORITY'
					,nb.[Head]												as 'body/HEAD'
					,nos.[OmsNumber]										as 'body/OMS_NUMBER'
					,nos.[OmsSerial]										as 'body/OMS_SERIAL'
					,nos.[OperationCode]									as 'body/OPERATION_CODE'
					,nos.PAC_ID												as 'body/UNIQUE_NUMBER'
					,nb.[Mkb]												as 'body/MKB'
					,nb.[Diagnosis]											as 'body/DIAGNOSIS'
					,nos.[DocSeries]										as 'body/DOC_SERIES'
					,nos.[DocNumber]										as 'body/DOC_NUMBER'
					,convert(varchar(10), nos.[Birthday], 104)				as 'body/BIRTHDAY'
					,nos.[FirstName]										as 'body/FIRST_NAME'
					,nos.[LastName]											as 'body/LAST_NAME'
					,nos.[FatherName]										as 'body/FATHER_NAME'
					,convert(varchar(10), nos.[DocIssueDate], 104)			as 'body/DOC_ISSUE_DATE'
					,nos.[DocIssueOrg]										as 'body/DOC_ISSUE_ORG'
					,nos.[DeclusionLPU]										as 'body/DECLUSION_LPU'
					,nos.[DocumentType]										as 'body/DOC_TYPE'
					,nos.[ExemptionBy178]									as 'body/EX178'
					,nos.[ExemptionBy890]									as 'body/EX890'
					,nos.[InclusionLPU]										as 'body/INCLUSION_LPU'
					,reg.Code												as 'body/REGION'
					,nos.[Sex]												as 'body/SEX'
					,nos.[SubjectOfRFAccounted]								as 'body/RF_ACCOUNT'
					,nos.[BirthdayLastName]									as 'body/BIRTHDAY_LAST_NAME'
					,nb.[Disability]										as 'body/DIASBILITY'
					,nb.[TherapyResistance]									as 'body/THERAPY_RESISTANCE'
					,convert(varchar(10), nos.[DeathDate], 104)				as 'body/DEATH_DATE'
					,nos.[OKATO]											as 'body/OKATO'
					,nos.[F002Smo]											as 'body/SMO'
					,case
						when RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = '' then null
						else nos.SNILS 
					 end													as 'body/SNILS'
					,b.BenefitCode											as 'body/BENIFITS'
			from RDS.T_NOSOLOGY_BENEFITS nb
				join [RDS].T_NOSOLOGY nos on nos.NosologyID = nb.Nosology
				left join RDS.T_REGION reg on reg.RegionID = nos.Region
				join RDS.T_BENEFITS b on b.BenefitsID = nb.Benefits
			where nb.NosologyBenefitsID = @ID
			for xml path(''),
            root('NOSOLOGY')
            )
        END

        -- Отправка всем остальным подписанным серверам AKUZ-а и APS
        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

