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_MedicamentProducer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_MedicamentProducer]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-23
-- Description: Выгрузка/загрузка справочника Производитель торгового наименования в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_MedicamentProducer]
     @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(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
		   ,@SQL VARCHAR(MAX)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'
            -- Инициализация
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'MEDICAMENT_PRODUCER'
          ,@OwnerID = @ID

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_MEDICAMENT_PRODUCER
	(
		 Inn					varchar(255)
		,Name					varchar(1000)
		,Address				varchar(500)
		,Active					bit
		,Descr					varchar(255)
		,Contacts				varchar(255)
		,Country				varchar(13)
		,FedCode				varchar(50)
		,RegCode				varchar(50)
		,MedicamentProducerID	uniqueidentifier
		,Code					int
	)
SET @SQL = 'INSERT INTO tempdb..#T_MEDICAMENT_PRODUCER
			SELECT
				 Inn
				,mp.Name
				,Address
				,Active
				,Descr
				,Contacts
				,c.Code
				,FedCode
				,RegCode
				,MedicamentProducerID
				,mp.Code
			FROM ' + @PATH + '.[PHARM].T_MEDICAMENT_PRODUCER mp
				left join ' + @PATH + '.PHARM.T_COUNTRY c on c.CountryID = mp.Country'
EXECUTE (@SQL)

	-- Производителей не из справочника RDS переводим в неактивные
	UPDATE mp
	SET mp.Active = 0
	FROM [PHARM].T_MEDICAMENT_PRODUCER mp
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_MEDICAMENT_PRODUCER nmp
	        WHERE nmp.MedicamentProducerID = mp.MedicamentProducerID
	    )

	-- Обновляем имеющихся производителей
	UPDATE mp
	SET mp.Inn = nmp.Inn
	   ,mp.Name = nmp.Name
	   ,mp.[Address] = nmp.[Address]
	   ,mp.Country = (select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
	   ,mp.Active = nmp.Active
	   ,mp.RegCode = nmp.RegCode
	   ,mp.FedCode = nmp.FedCode
	   ,mp.Code = nmp.Code
	FROM [PHARM].T_MEDICAMENT_PRODUCER mp
	    join #T_MEDICAMENT_PRODUCER nmp on nmp.MedicamentProducerID = mp.MedicamentProducerID

	-- Загружаем новые
	INSERT INTO [PHARM].[T_MEDICAMENT_PRODUCER]
	           ([MedicamentProducerID] ,[Name] ,[Inn] ,[Address] ,Country ,[Active],RegCode,FedCode,Code)
	SELECT
	           nmp.[MedicamentProducerID]--<MedicamentProducerID, uniqueidentifier,>
	          ,nmp.Name--<Name, varchar(255),>
	          ,nmp.Inn--<Inn, varchar(255),>
	          ,nmp.[Address]--<Address, varchar(500),>
	          ,(select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
	          ,nmp.Active--<Active, bit,>)
			  ,RegCode
			  ,FedCode
	          ,Code
	FROM #T_MEDICAMENT_PRODUCER nmp
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_MEDICAMENT_PRODUCER mp
	        WHERE mp.MedicamentProducerID = nmp.[MedicamentProducerID]
	    )

	END
	ELSE
	BEGIN

    IF (@Xml is null)
    BEGIN

        --выясняем тип операции
        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_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('MEDICAMENT_PRODUCER')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD                             as 'header/TYPE'
                      ,(
                            select mp.MedicamentProducerID as 'MEDICAMENT_PRODUCER/ID'
                                  ,mp.Inn                  as 'MEDICAMENT_PRODUCER/Inn'
                                  ,mp.Name                 as 'MEDICAMENT_PRODUCER/Name'
                                  ,mp.[Address]            as 'MEDICAMENT_PRODUCER/Address'
                                  ,mp.Active               as 'MEDICAMENT_PRODUCER/Active'
                                  ,c.Code                  as 'MEDICAMENT_PRODUCER/Country'
								  ,RegCode				   as 'MEDICAMENT_PRODUCER/RegCode'
								  ,FedCode				   as 'MEDICAMENT_PRODUCER/FedCode'
								  ,mp.Code				   as 'MEDICAMENT_PRODUCER/Code'
                            from [PHARM].T_MEDICAMENT_PRODUCER mp
							left join PHARM.T_COUNTRY c on c.CountryID = mp.Country
                            for xml path(''),
                            type
                      ) as 'body/MEDICAMENT_PRODUCERS'
                for xml path(''),
                root('MEDICAMENT_PRODUCER')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем MEDICAMENT_PRODUCER
            SELECT @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                      ,mp.Inn         as 'body/Inn'
                      ,mp.Name        as 'body/Name'
                      ,mp.[Address]   as 'body/Address'
                      ,mp.Active      as 'body/Active'
                      ,c.Code	      as 'body/Country'
					  ,RegCode		  as 'body/RegCode'
					  ,FedCode		  as 'body/FedCode'
					  ,mp.Code		  as 'body/Code'
                from [PHARM].T_MEDICAMENT_PRODUCER mp
					left join PHARM.T_COUNTRY c on c.CountryID = mp.Country
                where mp.MedicamentProducerID = @ID
                for xml path(''),
                root('MEDICAMENT_PRODUCER')
            )
        END

        -- Отправка
        INSERT INTO [AKUZ].[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(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('MEDICAMENT_PRODUCER') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #MEDICAMENT_PRODUCERS
            SELECT t.c.value('./ID[1]', 'uniqueidentifier')  AS ID,
                   t.c.value('./Inn[1]', 'varchar(255)')     AS Inn,
                   t.c.value('./Name[1]', 'varchar(255)')    AS Name,
                   t.c.value('./Address[1]', 'varchar(500)') AS Address,
                   t.c.value('./Active[1]', 'bit')           AS Active,
                   t.c.value('./Country[1]', 'varchar(13)')  AS Country,
                   t.c.value('./RegCode[1]', 'varchar(50)')  AS RegCode,
                   t.c.value('./FedCode[1]', 'varchar(50)')  AS FedCode,
                   t.c.value('./Code[1]', 'int')             AS Code
            INTO #MEDICAMENT_PRODUCERS
            FROM @Xml.nodes('MEDICAMENT_PRODUCER/body/MEDICAMENT_PRODUCERS/MEDICAMENT_PRODUCER') T(c)

            -- Первичная загрузка
            -- Производителей не из справочника RDS переводим в неактивные
            update mp
            set mp.Active = 0
            from [PHARM].T_MEDICAMENT_PRODUCER mp
            where not exists
                (
                    select *
                    from #MEDICAMENT_PRODUCERS nmp
                    where nmp.ID = mp.MedicamentProducerID
                )

            -- Обновляем имеющихся производителей
            update mp
            set mp.Inn = nmp.Inn
               ,mp.Name = nmp.Name
               ,mp.[Address] = nmp.[Address]
               ,mp.Country = (select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
               ,mp.Active = nmp.Active
			   ,mp.RegCode = nmp.RegCode
			   ,mp.FedCode = nmp.FedCode
			   ,mp.Code = nmp.Code
            from [PHARM].T_MEDICAMENT_PRODUCER mp
                join #MEDICAMENT_PRODUCERS nmp on nmp.ID = mp.MedicamentProducerID

            -- Загружаем новые
            INSERT INTO [PHARM].[T_MEDICAMENT_PRODUCER]
                       ([MedicamentProducerID] ,[Name] ,[Inn] ,[Address] ,Country ,[Active],RegCode,FedCode,Code)
            SELECT
                       nmp.ID--<MedicamentProducerID, uniqueidentifier,>
                      ,nmp.Name--<Name, varchar(255),>
                      ,nmp.Inn--<Inn, varchar(255),>
                      ,nmp.[Address]--<Address, varchar(500),>
                      ,(select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
                      ,nmp.Active--<Active, bit,>)
					  ,RegCode
					  ,FedCode
                      ,Code
            from #MEDICAMENT_PRODUCERS nmp
            where not exists
                (
                    select *
                    from [PHARM].T_MEDICAMENT_PRODUCER mp
                    where mp.MedicamentProducerID = nmp.ID
                )
        END
        ELSE
        BEGIN
            -- Generate table #MEDICAMENT_PRODUCER
            SELECT T.c.value('./header[1]/ID[1]', 'uniqueidentifier') AS ID,
                   T.c.value('./body[1]/Inn[1]', 'varchar(255)')      AS Inn,
                   T.c.value('./body[1]/Name[1]', 'varchar(255)')     AS Name,
                   T.c.value('./body[1]/Address[1]', 'varchar(500)')  AS Address,
                   T.c.value('./body[1]/Active[1]', 'bit')            AS Active,
                   T.c.value('./body[1]/Country[1]', 'varchar(13)')   AS Country,
                   t.c.value('./body[1]/RegCode[1]', 'varchar(50)')   AS RegCode,
                   t.c.value('./body[1]/FedCode[1]', 'varchar(50)')   AS FedCode,
                   t.c.value('./body[1]/Code[1]', 'int')              AS Code
            INTO #MEDICAMENT_PRODUCER
            FROM @Xml.nodes('MEDICAMENT_PRODUCER') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                update mp
                set mp.Active = 0
                from [PHARM].[T_MEDICAMENT_PRODUCER] mp
                    join #MEDICAMENT_PRODUCER nmp on nmp.[ID] = mp.MedicamentProducerID
            END
            else
            BEGIN
                -- update [PHARM].T_MEDICAMENT_PRODUCER
                update mp
                set mp.Inn = nmp.Inn
                   ,mp.Name = nmp.Name
                   ,mp.[Address] = nmp.[Address]
                   ,mp.Country = (select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
                   ,mp.Active = nmp.Active
                   ,mp.RegCode = nmp.RegCode
                   ,mp.FedCode = nmp.FedCode
                   ,mp.Code = nmp.Code
                from [PHARM].T_MEDICAMENT_PRODUCER mp
                    join #MEDICAMENT_PRODUCER nmp on nmp.ID = mp.MedicamentProducerID
                where @OperationType = @UPDATE

                -- insert [PHARM].T_MEDICAMENT_PRODUCER
                INSERT INTO [PHARM].[T_MEDICAMENT_PRODUCER]
                           ([MedicamentProducerID] ,[Name] ,[Inn] ,[Address] ,Country ,[Active],RegCode,FedCode,Code)
                SELECT
                           nmp.ID--<MedicamentProducerID, uniqueidentifier,>
                          ,nmp.Name--<Name, varchar(255),>
                          ,nmp.Inn--<Inn, varchar(255),>
                          ,nmp.[Address]--<Address, varchar(500),>
                          ,(select top 1  c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country)
                          ,nmp.Active--<Active, bit,>)
                          ,RegCode
                          ,FedCode
                          ,Code
                from #MEDICAMENT_PRODUCER nmp
                where not exists
                    (
                        select *
                        from [PHARM].T_MEDICAMENT_PRODUCER mp
                        where mp.MedicamentProducerID = nmp.ID
                    )
                        and
                    @OperationType = @INSERT

            END
        END

		declare @EntId int
		select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'MEDICAMENT_PRODUCER'
        IF(exists (select top 1 * from [VCLib].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.MEDICAMENT_PRODUCER'))
        BEGIN
			-- генерируем след. номер
			--exec [dbo].sp_GenerateNumber @EntId, 'PHARM.MEDICAMENT_PRODUCER'
			update gn
			set gn.NextNumber = (select MAX(Code) from [PHARM].T_MEDICAMENT_PRODUCER)
			from [VCLib].T_GEN_NUMBERS gn
			where gn.EntityID = @EntId
					and
				gn.NumberName = 'PHARM.MEDICAMENT_PRODUCER'
		END
        ELSE
        BEGIN

			INSERT INTO [VCLib].[T_GEN_NUMBERS]
						([GenNumbersID]
						,[EntityID]
						,[NumberName]
						,[NextNumber])
			VALUES
						(newID()
						,@EntId
						,'PHARM.MEDICAMENT_PRODUCER'
						,ISNULL((select MAX(Code) from [PHARM].T_MEDICAMENT_PRODUCER), 1))
        END
    END
END
END
GO