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

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_Series]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_Series]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника серии в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_Series]
	 @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
	,@PATH VARCHAR(MAX) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(6)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
		   ,@SQL VARCHAR(MAX)

    select @dateTime = ISNULL(@dateTime, GETDATE())
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'SERIES'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_SERIES
	(
		 SeriesID				UNIQUEIDENTIFIER
		,SerialNumber			VARCHAR(255)
		,RegistrationDate		DATETIME
		,ExpirationDate			DATETIME
		,DurationOfCertificate	DATETIME
		,CertificateNumber		VARCHAR(255)
		,IdentificationNumber	VARCHAR(255)
		,BillOfGoods			UNIQUEIDENTIFIER
		,Active					BIT
	)

SET @SQL = 'INSERT INTO tempdb..#T_SERIES
			SELECT
				 SeriesID
				,SerialNumber
				,RegistrationDate
				,ExpirationDate
				,DurationOfCertificate
				,CertificateNumber
				,IdentificationNumber
				,BillOfGoods
				,Active
			FROM ' + @PATH + '.[PHARM].T_SERIES'
EXECUTE (@SQL)

	--- Удаление
	DELETE S
		FROM [PHARM].T_SERIES S
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_SERIES nmf
	        WHERE nmf.SeriesID = s.SeriesID
	    )

	-- Обновляем имеющиеся
	update s
	set s.Active = nmf.Active
	   ,s.SerialNumber = nmf.SerialNumber
	   ,s.RegistrationDate = nmf.RegistrationDate
	   ,s.ExpirationDate = nmf.ExpirationDate
	   ,s.DurationOfCertificate = nmf.DurationOfCertificate
	   ,s.CertificateNumber = nmf.CertificateNumber
	   ,s.IdentificationNumber = nmf.IdentificationNumber
	   ,s.BillOfGoods = nmf.BillOfGoods
	from [PHARM].T_SERIES s
	    join #T_SERIES nmf on nmf.SeriesID = s.SeriesID

	-- Загружаем новые
	INSERT INTO [PHARM].[T_SERIES]
		(
			 [SeriesID]
			,[SerialNumber]
			,[RegistrationDate]
			,[ExpirationDate]
			,[DurationOfCertificate]
			,[CertificateNumber]
			,[IdentificationNumber]
			,[Active]
			,[BillOfGoods]
		)
	SELECT
			nmf.SeriesID,
			nmf.SerialNumber,
			nmf.RegistrationDate,
			nmf.ExpirationDate,
			nmf.DurationOfCertificate,
			nmf.CertificateNumber,
			nmf.IdentificationNumber,
			nmf.Active,
			nmf.BillOfGoods
	from #T_SERIES nmf
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_SERIES s
	        WHERE s.SeriesID = nmf.SeriesID
	    )

	END
    ELSE
	BEGIN

	IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @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 [PHARM].T_SERIES s where s.SeriesID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_SERIES s where s.SeriesID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('SERIES')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD						as 'header/TYPE'
                      ,(
                            select s.SeriesID		as 'SERIES/ID'
							,s.SerialNumber			as 'SERIES/SerialNumber'
							,convert(varchar(10), s.RegistrationDate, 104)			as 'SERIES/RegistrationDate'
							,convert(varchar(10), s.ExpirationDate, 104)			as 'SERIES/ExpirationDate'
							,convert(varchar(10), s.DurationOfCertificate, 104)	as 'SERIES/DurationOfCertificate'
							,s.CertificateNumber	as 'SERIES/CertificateNumber'
							,s.IdentificationNumber	as 'SERIES/IdentificationNumber'
                            ,s.Active				as 'SERIES/Active'
							,s.BillOfGoods			as 'SERIES/BillOfGoods'
                            from [PHARM].T_SERIES s
                            for xml path(''),
                            type
                      ) as 'body/SERIESS'
                for xml path(''),
                root('SERIES')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем SERIES
            SELECT @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'

                      ,s.SerialNumber			as 'body/SerialNumber'
					  ,convert(varchar(10), s.RegistrationDate, 104)			as 'body/RegistrationDate'
					  ,convert(varchar(10), s.ExpirationDate, 104)			as 'body/ExpirationDate'
					  ,convert(varchar(10), s.DurationOfCertificate, 104)	as 'body/DurationOfCertificate'
					  ,s.CertificateNumber		as 'body/CertificateNumber'
					  ,s.IdentificationNumber	as 'body/IdentificationNumber'
                      ,s.Active					as 'body/Active'
					  ,s.BillOfGoods			as 'body/BillOfGoods'
                from [PHARM].T_SERIES s
                where s.SeriesID = @ID
                for xml path(''),
                root('SERIES')
            )
        END

        -- Отправка
        INSERT INTO [aps].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,@QueueName--,<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),>
    END
    ELSE
    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
        from @Xml.nodes('SERIES') T(c)





        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #SERIESS
            select t.c.value('./ID[1]', 'uniqueidentifier')                                       as [ID]
                 , t.c.value('./Active[1]', 'bit')                                                as [Active]
                 , t.c.value('./SerialNumber[1]', 'varchar(255)')                                 as [SerialNumber]
                 , convert(datetime, t.c.value('./RegistrationDate[1]', 'varchar(10)'), 104)      as [RegistrationDate]
                 , convert(datetime, t.c.value('./ExpirationDate[1]', 'varchar(10)'), 104)        as [ExpirationDate]
                 , convert(datetime, t.c.value('./DurationOfCertificate[1]', 'varchar(10)'),
                                     104)                                                         as [DurationOfCertificate]
                 , t.c.value('./CertificateNumber[1]', 'int')                                     as [CertificateNumber]
                 , t.c.value('./IdentificationNumber[1]', 'int')                                  as [IdentificationNumber]
                 , t.c.value('./BillOfGoods[1]', 'uniqueidentifier')                              as [BillOfGoods]
            INTO #SERIESS
            from @Xml.nodes('SERIES/body/SERIESS/SERIES') T(c)

            delete s
            from [PHARM].T_SERIES s
            where not exists
                (
                    select *
                    from #SERIESS nmf
                    where nmf.ID = s.SeriesID
                )

            -- Обновляем имеющиеся
            update s
            set s.Active = nmf.Active
			   ,s.SerialNumber = nmf.SerialNumber
			   ,s.RegistrationDate = nmf.RegistrationDate
			   ,s.ExpirationDate = nmf.ExpirationDate
			   ,s.DurationOfCertificate = nmf.DurationOfCertificate
			   ,s.CertificateNumber = nmf.CertificateNumber
			   ,s.IdentificationNumber = nmf.IdentificationNumber
			   ,s.BillOfGoods = nmf.BillOfGoods
            from [PHARM].T_SERIES s
                join #SERIESS nmf on nmf.ID = s.SeriesID

            -- Загружаем новые
            INSERT INTO [PHARM].[T_SERIES]
                       (SeriesID,SerialNumber,RegistrationDate,ExpirationDate,DurationOfCertificate,CertificateNumber,IdentificationNumber,Active,BillOfGoods)
            SELECT
                       nmf.ID,
                       nmf.SerialNumber,
					   nmf.RegistrationDate,
					   nmf.ExpirationDate,
					   nmf.DurationOfCertificate,
					   nmf.CertificateNumber,
					   nmf.IdentificationNumber,
					   nmf.Active,
					   nmf.BillOfGoods
            from #SERIESS nmf
            where not exists
                (
                    select *
                    from [PHARM].T_SERIES s
                    where s.SeriesID = nmf.ID
                )
        END
        ELSE
            BEGIN
                -- Generate table #SERIES
                select T.c.value('./header[1]/ID[1]', 'int')                                                  as [ID]
                     , t.c.value('./body[1]/Active[1]', 'bit')                                                as [Active]
                     , t.c.value('./body[1]/SerialNumber[1]', 'varchar(255)')                                 as [SerialNumber]
                     , convert(datetime, t.c.value('./body[1]/RegistrationDate[1]', 'varchar(10)'),
                                         104)                                                                 as [RegistrationDate]
                     , convert(datetime, t.c.value('./body[1]/ExpirationDate[1]', 'varchar(10)'),
                                         104)                                                                 as [ExpirationDate]
                     , convert(datetime, t.c.value('./body[1]/DurationOfCertificate[1]', 'varchar(10)'),
                                         104)                                                                 as [DurationOfCertificate]
                     , t.c.value('./body[1]/CertificateNumber[1]', 'int')                                     as [CertificateNumber]
                     , t.c.value('./body[1]/IdentificationNumber[1]', 'int')                                  as [IdentificationNumber]
                     , t.c.value('./body[1]/BillOfGoods[1]', 'uniqueidentifier')                              as [BillOfGoods]
                INTO #SERIES
                from @Xml.nodes('SERIES') T(c)

                if (@OperationType = @DELETE)
                    BEGIN
                        -- Удаление
                        delete s
                        from [PHARM].[T_SERIES] s
                                 join #SERIES nmf on nmf.[ID] = s.SeriesID
                    END
                else
                    BEGIN
                        -- update [PHARM].T_SERIES
                        update s
                        set s.Active                = nmf.Active
                          , s.SerialNumber          = nmf.SerialNumber
                          , s.RegistrationDate      = nmf.RegistrationDate
                          , s.ExpirationDate        = nmf.ExpirationDate
                          , s.DurationOfCertificate = nmf.DurationOfCertificate
                          , s.CertificateNumber     = nmf.CertificateNumber
                          , s.IdentificationNumber  = nmf.IdentificationNumber
                          , s.BillOfGoods           = nmf.BillOfGoods
                        from [PHARM].T_SERIES s
                                 join #SERIES nmf on nmf.ID = s.SeriesID
                        where @OperationType = @UPDATE

                        -- insert [PHARM].T_SERIES
                        INSERT INTO [PHARM].[T_SERIES]
                        (SeriesID, SerialNumber, RegistrationDate, ExpirationDate, DurationOfCertificate,
                         CertificateNumber, IdentificationNumber, Active, BillOfGoods)
                        SELECT nmf.ID,
                               nmf.SerialNumber,
                               nmf.RegistrationDate,
                               nmf.ExpirationDate,
                               nmf.DurationOfCertificate,
                               nmf.CertificateNumber,
                               nmf.IdentificationNumber,
                               nmf.Active,
                               nmf.BillOfGoods
                        from #SERIES nmf
                        where @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_SERIES s
                             where s.SeriesID = nmf.ID)

                    END
            END


    END
END
END
GO