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_MedicamentsOfRoszdravnadzor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_MedicamentsOfRoszdravnadzor]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_MedicamentsOfRoszdravnadzor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_MedicamentsOfRoszdravnadzor]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника Медикаменты Росздравнадзора в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_MedicamentsOfRoszdravnadzor]
     @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = null
    ,@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 = 'MEDICAMENT_OF_ROSZDRAVNADZOR'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_MEDICAMENTS_OF_ROSZDRAVNADZOR
	(
		 MedicamentsOfRoszdravnadzorID	UNIQUEIDENTIFIER
		,DrugID							INT
		,TradeNmR						VARCHAR(1000)
		,InnR							VARCHAR(1000)
		,PackNx							BIGINT
		,DosageR						VARCHAR(255)
		,PackQn							INT
		,Pack							VARCHAR(1000)
		,DrugFmNmRS						VARCHAR(1000)
		,Segment						VARCHAR(255)
		,Year							INT
		,Month							INT
		,Series							VARCHAR(255)
		,TotDrugQn						DECIMAL
		,MnfPrice						DECIMAL
		,PrcPrice						DECIMAL
		,RtlPrice						DECIMAL
		,Funds							DECIMAL
		,VendorID						VARCHAR(255)
		,Remark							VARCHAR(1000)
		,SrcOrg							VARCHAR(255)
		,EAN							VARCHAR(14)
		,MaxMnfPrice					DECIMAL
		,ExpiTermR						VARCHAR(500)
		,ClNm							VARCHAR(1000)
		,MnfNm							VARCHAR(1000)
		,PckNm							VARCHAR(1000)
		,RegNr							VARCHAR(100)
		,RegDate						DATETIME
		,Active							BIT
	)
SET @SQL = 'INSERT INTO tempdb..#T_MEDICAMENTS_OF_ROSZDRAVNADZOR
			SELECT
				 MedicamentsOfRoszdravnadzorID
				,DrugID
				,TradeNmR
				,InnR
				,PackNx
				,DosageR
				,PackQn
				,Pack
				,DrugFmNmRS
				,Segment
				,Year
				,Month
				,Series
				,TotDrugQn
				,MnfPrice
				,PrcPrice
				,RtlPrice
				,Funds
				,VendorID
				,Remark
				,SrcOrg
				,EAN
				,MaxMnfPrice
				,ExpiTermR
				,ClNm
				,MnfNm
				,PckNm
				,RegNr
				,RegDate
				,Active
			FROM ' + @PATH + '.[PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR'
EXECUTE (@SQL)

	--- Delete

	--DELETE S
	--	FROM [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR s
	--WHERE NOT EXISTS
	--    (
	--        select *
	--        from #T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
	--        where nmf.MedicamentsOfRoszdravnadzorID = s.MedicamentsOfRoszdravnadzorID
	--    )

	----

	UPDATE S
	SET	s.Active = 0
		FROM [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR s
	WHERE NOT EXISTS
	    (
	        select *
	        from #T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
	        where nmf.MedicamentsOfRoszdravnadzorID = s.MedicamentsOfRoszdravnadzorID
	    )

            -- Обновляем имеющиеся
	 update m
	 set
		 m.[DrugID]			= nmf.[DrugID]
	     ,m.[TradeNmR]		= nmf.[TradeNmR]
	     ,m.[InnR]			= nmf.[InnR]
	     ,m.[PackNx]		= nmf.[PackNx]
	     ,m.[DosageR]		= nmf.[DosageR]
	     ,m.[PackQn]		= nmf.[PackQn]
	     ,m.[Pack]			= nmf.[Pack]
	     ,m.[DrugFmNmRS]	= nmf.[DrugFmNmRS]
	     ,m.[Segment]		= nmf.[Segment]
	     ,m.[Year]			= nmf.[Year]
	     ,m.[Month]			= nmf.[Month]
	     ,m.[Series]		= nmf.[Series]
	     ,m.[TotDrugQn]		= nmf.[TotDrugQn]
	     ,m.[MnfPrice]		= nmf.[MnfPrice]
	     ,m.[PrcPrice]		= nmf.[PrcPrice]
	     ,m.[RtlPrice]		= nmf.[RtlPrice]
	     ,m.[Funds]			= nmf.[Funds]
	     ,m.[VendorID]		= nmf.[VendorID]
	     ,m.[Remark]		= nmf.[Remark]
	     ,m.[SrcOrg]		= nmf.[SrcOrg]
	     ,m.[EAN]			= nmf.[EAN]
	     ,m.[MaxMnfPrice]	= nmf.[MaxMnfPrice]
	     ,m.[ExpiTermR]		= nmf.[ExpiTermR]
	     ,m.[ClNm]			= nmf.[ClNm]
	     ,m.[MnfNm]			= nmf.[MnfNm]
	     ,m.[PckNm]			= nmf.[PckNm]
	     ,m.[RegNr]			= nmf.[RegNr]
	     ,m.[RegDate]		= nmf.[RegDate]
	     ,m.[Active]		= ISNULL(nmf.[Active],1)
	 from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
	     join #T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf on nmf.MedicamentsOfRoszdravnadzorID = m.MedicamentsOfRoszdravnadzorID
	 and m.Active = 1

	 -- Загружаем новые
	 INSERT INTO [PHARM].[T_MEDICAMENTS_OF_ROSZDRAVNADZOR]
	([MedicamentsOfRoszdravnadzorID]
		,[DrugID]
		,[TradeNmR]
		,[InnR]
		,[PackNx]
		,[DosageR]
		,[PackQn]
		,[Pack]
		,[DrugFmNmRS]
		,[Segment]
		,[Year]
		,[Month]
		,[Series]
		,[TotDrugQn]
		,[MnfPrice]
		,[PrcPrice]
		,[RtlPrice]
		,[Funds]
		,[VendorID]
		,[Remark]
		,[SrcOrg]
		,[EAN]
		,[MaxMnfPrice]
		,[ExpiTermR]
		,[ClNm]
		,[MnfNm]
		,[PckNm]
		,[RegNr]
		,[RegDate]
		,[Active])
	select
		 nmf.MedicamentsOfRoszdravnadzorID
		,nmf.[DrugID]
		,nmf.[TradeNmR]
		,nmf.[InnR]
		,nmf.[PackNx]
		,nmf.[DosageR]
		,nmf.[PackQn]
		,nmf.[Pack]
		,nmf.[DrugFmNmRS]
		,nmf.[Segment]
		,nmf.[Year]
		,nmf.[Month]
		,nmf.[Series]
		,nmf.[TotDrugQn]
		,nmf.[MnfPrice]
		,nmf.[PrcPrice]
		,nmf.[RtlPrice]
		,nmf.[Funds]
		,nmf.[VendorID]
		,nmf.[Remark]
		,nmf.[SrcOrg]
		,nmf.[EAN]
		,nmf.[MaxMnfPrice]
		,nmf.[ExpiTermR]
		,nmf.[ClNm]
		,nmf.[MnfNm]
		,nmf.[PckNm]
		,nmf.[RegNr]
		,nmf.[RegDate]
		,ISNULL(nmf.[Active],1)
FROM #T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
WHERE NOT EXISTS
    (
        SELECT *
			FROM [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
        WHERE m.MedicamentsOfRoszdravnadzorID = nmf.MedicamentsOfRoszdravnadzorID
    )

	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_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD						as 'header/TYPE'
                      ,(
                            select m.MedicamentsOfRoszdravnadzorID		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ID'
							,m.DrugID			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DrugID'
							,m.TradeNmR			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/TradeNmR'
							,m.InnR				as 'MEDICAMENT_OF_ROSZDRAVNADZOR/InnR'
							,m.PackNx			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PackNx'
							,m.DosageR			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DosageR'
							,m.PackQn			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PackQn'
							,m.Pack				as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Pack'
							,m.DrugFmNmRS		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DrugFmNmRS'
							,m.Segment			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Segment'
							,m.[Year]			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Year'
							,m.[Month]			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Month'
							,m.Series			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Series'
							,m.TotDrugQn		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/TotDrugQn'
							,m.MnfPrice			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MnfPrice'
							,m.PrcPrice			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PrcPrice'
							,m.RtlPrice			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RtlPrice'
							,m.Funds			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Funds'
							,m.VendorID			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/VendorID'
							,m.Remark			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Remark'
							,m.SrcOrg			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/SrcOrg'
							,m.EAN				as 'MEDICAMENT_OF_ROSZDRAVNADZOR/EAN'
							,m.ExpiTermR		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ExpiTermR'
							,m.ClNm				as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm'
							,m.MnfNm			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm'
							,m.PckNm			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm'
							,m.RegNr			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr'
					        ,m.MaxMnfPrice		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MaxMnfPrice'
							,convert(varchar(10), m.RegDate, 104)	as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate'
							,m.Active			as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Active'
                            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                            where m.Active = 1
                            for xml path(''),
                            type
                      ) as 'body/MEDICAMENTS_OF_ROSZDRAVNADZOR'
                for xml path(''),
                root('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем SERIES
            SELECT @Result =
            (
                select
					@OperationType		as 'header/TYPE'
                    ,@ID				as 'header/ID'
					,m.DrugID			as 'body/DrugID'
					,m.TradeNmR			as 'body/TradeNmR'
					,m.InnR				as 'body/InnR'
					,m.PackNx			as 'body/PackNx'
					,m.DosageR			as 'body/DosageR'
					,m.PackQn			as 'body/PackQn'
					,m.Pack				as 'body/Pack'
					,m.DrugFmNmRS		as 'body/DrugFmNmRS'
					,m.Segment			as 'body/Segment'
					,m.[Year]			as 'body/Year'
					,m.[Month]			as 'body/Month'
					,m.Series			as 'body/Series'
					,m.TotDrugQn		as 'body/TotDrugQn'
					,m.MnfPrice			as 'body/MnfPrice'
					,m.PrcPrice			as 'body/PrcPrice'
					,m.RtlPrice			as 'body/RtlPrice'
					,m.Funds			as 'body/Funds'
					,m.VendorID			as 'body/VendorID'
					,m.Remark			as 'body/Remark'
					,m.SrcOrg			as 'body/SrcOrg'
					,m.EAN				as 'body/EAN'
					,m.ExpiTermR		as 'body/ExpiTermR'
					,m.ClNm				as 'body/ClNm'
					,m.MnfNm			as 'body/MnfNm'
					,m.PckNm			as 'body/PckNm'
					,m.RegNr			as 'body/RegNr'
					,m.MaxMnfPrice		as 'body/MaxMnfPrice'
					,convert(varchar(10), m.RegDate, 104)	as 'body/RegDate'
					,m.Active			as 'body/Active'
                from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                where m.MedicamentsOfRoszdravnadzorID = @ID
                for xml path(''),
                root('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        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('MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #MEDICAMENTS_OF_ROSZDRAVNADZOR
            select t.c.value('./ID[1]', 'uniqueidentifier')                         as [ID]
                 , t.c.value('./DrugID[1]', 'int')                                  as [DrugID]
                 , t.c.value('./TradeNmR[1]', 'varchar(1000)')                      as [TradeNmR]
                 , t.c.value('./InnR[1]', 'varchar(1000)')                          as [InnR]
                 , t.c.value('./PackNx[1]', 'bigint')                               as [PackNx]
                 , t.c.value('./DosageR[1]', 'varchar(255)')                        as [DosageR]
                 , t.c.value('./PackQn[1]', 'int')                                  as [PackQn]
                 , t.c.value('./Pack[1]', 'varchar(1000)')                          as [Pack]
                 , t.c.value('./DrugFmNmRS[1]', 'varchar(1000)')                    as [DrugFmNmRS]
                 , t.c.value('./Segment[1]', 'varchar(255)')                        as [Segment]
                 , t.c.value('./Year[1]', 'int')                                    as [Year]
                 , t.c.value('./Month[1]', 'int')                                   as [Month]
                 , t.c.value('./Series[1]', 'varchar(255)')                         as [Series]
                 , t.c.value('./TotDrugQn[1]', 'decimal(12,2)')                     as [TotDrugQn]
                 , t.c.value('./MnfPrice[1]', 'decimal(12,2)')                      as [MnfPrice]
                 , t.c.value('./PrcPrice[1]', 'decimal(12,2)')                      as [PrcPrice]
                 , t.c.value('./RtlPrice[1]', 'decimal(12,2)')                      as [RtlPrice]
                 , t.c.value('./Funds[1]', 'decimal(12,2)')                         as [Funds]
                 , t.c.value('./VendorID[1]', 'varchar(255)')                       as [VendorID]
                 , t.c.value('./Remark[1]', 'varchar(1000)')                        as [Remark]
                 , t.c.value('./SrcOrg[1]', 'varchar(255)')                         as [SrcOrg]
                 , t.c.value('./EAN[1]', 'varchar(14)')                             as [EAN]
                 , t.c.value('./MaxMnfPrice[1]', 'decimal(12,2)')                   as [MaxMnfPrice]
                 , t.c.value('./ExpiTermR[1]', 'varchar(500)')                      as [ExpiTermR]
                 , t.c.value('./ClNm[1]', 'varchar(255)')                           as [ClNm]
                 , t.c.value('./MnfNm[1]', 'varchar(1000)')                         as [MnfNm]
                 , t.c.value('./PckNm[1]', 'varchar(1000)')                         as [PckNm]
                 , t.c.value('./RegNr[1]', 'varchar(100)')                          as [RegNr]
                 , convert(datetime, t.c.value('./RegDate[1]', 'varchar(10)'), 104) as [RegDate]
                 , t.c.value('./Active[1]', 'bit')                                  as [Active]
            INTO #MEDICAMENTS_OF_ROSZDRAVNADZOR
            from @Xml.nodes('MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)

            update s
            set s.Active = 0
            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR s
            where not exists
                (
                    select *
                    from #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                    where nmf.ID = s.MedicamentsOfRoszdravnadzorID
                )

            -- Обновляем имеющиеся
            update m
            set  m.[DrugID]			= nmf.[DrugID]
                ,m.[TradeNmR]		= nmf.[TradeNmR]
                ,m.[InnR]			= nmf.[InnR]
                ,m.[PackNx]			= nmf.[PackNx]
                ,m.[DosageR]		= nmf.[DosageR]
                ,m.[PackQn]			= nmf.[PackQn]
                ,m.[Pack]			= nmf.[Pack]
                ,m.[DrugFmNmRS]		= nmf.[DrugFmNmRS]
                ,m.[Segment]		= nmf.[Segment]
                ,m.[Year]			= nmf.[Year]
                ,m.[Month]			= nmf.[Month]
                ,m.[Series]			= nmf.[Series]
                ,m.[TotDrugQn]		= nmf.[TotDrugQn]
                ,m.[MnfPrice]		= nmf.[MnfPrice]
                ,m.[PrcPrice]		= nmf.[PrcPrice]
                ,m.[RtlPrice]		= nmf.[RtlPrice]
                ,m.[Funds]			= nmf.[Funds]
                ,m.[VendorID]		= nmf.[VendorID]
                ,m.[Remark]			= nmf.[Remark]
                ,m.[SrcOrg]			= nmf.[SrcOrg]
                ,m.[EAN]			= nmf.[EAN]
                ,m.[MaxMnfPrice]	= nmf.[MaxMnfPrice]
                ,m.[ExpiTermR]		= nmf.[ExpiTermR]
                ,m.[ClNm]			= nmf.[ClNm]
                ,m.[MnfNm]			= nmf.[MnfNm]
                ,m.[PckNm]			= nmf.[PckNm]
                ,m.[RegNr]			= nmf.[RegNr]
                ,m.[RegDate]		= nmf.[RegDate]
                ,m.[Active]			= ISNULL(nmf.[Active],1)
            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                join #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf on nmf.ID = m.MedicamentsOfRoszdravnadzorID
            and m.Active = 1

            -- Загружаем новые
            INSERT INTO [PHARM].[T_MEDICAMENTS_OF_ROSZDRAVNADZOR]
           ([MedicamentsOfRoszdravnadzorID]
           ,[DrugID]
           ,[TradeNmR]
           ,[InnR]
           ,[PackNx]
           ,[DosageR]
           ,[PackQn]
           ,[Pack]
           ,[DrugFmNmRS]
           ,[Segment]
           ,[Year]
           ,[Month]
           ,[Series]
           ,[TotDrugQn]
           ,[MnfPrice]
           ,[PrcPrice]
           ,[RtlPrice]
           ,[Funds]
           ,[VendorID]
           ,[Remark]
           ,[SrcOrg]
           ,[EAN]
           ,[MaxMnfPrice]
           ,[ExpiTermR]
           ,[ClNm]
           ,[MnfNm]
           ,[PckNm]
           ,[RegNr]
           ,[RegDate]
		   ,[Active])
		select
			nmf.[ID]
           ,nmf.[DrugID]
           ,nmf.[TradeNmR]
           ,nmf.[InnR]
           ,nmf.[PackNx]
           ,nmf.[DosageR]
           ,nmf.[PackQn]
           ,nmf.[Pack]
           ,nmf.[DrugFmNmRS]
           ,nmf.[Segment]
           ,nmf.[Year]
           ,nmf.[Month]
           ,nmf.[Series]
           ,nmf.[TotDrugQn]
           ,nmf.[MnfPrice]
           ,nmf.[PrcPrice]
           ,nmf.[RtlPrice]
           ,nmf.[Funds]
           ,nmf.[VendorID]
           ,nmf.[Remark]
           ,nmf.[SrcOrg]
           ,nmf.[EAN]
           ,nmf.[MaxMnfPrice]
           ,nmf.[ExpiTermR]
           ,nmf.[ClNm]
           ,nmf.[MnfNm]
           ,nmf.[PckNm]
           ,nmf.[RegNr]
           ,nmf.[RegDate]
		   ,ISNULL(nmf.[Active],1)
            from #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
            where not exists
                (
                    select *
                    from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    where m.MedicamentsOfRoszdravnadzorID = nmf.ID
                )
        END
        ELSE
            BEGIN
                -- Generate table #MEDICAMENT_OF_ROSZDRAVNADZOR
                select T.c.value('./header[1]/ID[1]', 'uniqueidentifier')               as [ID]
                     , t.c.value('./body[1]/DrugID[1]', 'int')                          as [DrugID]
                     , t.c.value('./body[1]/TradeNmR[1]', 'varchar(1000)')              as [TradeNmR]
                     , t.c.value('./body[1]/InnR[1]', 'varchar(1000)')                  as [InnR]
                     , t.c.value('./body[1]/PackNx[1]', 'bigint')                       as [PackNx]
                     , t.c.value('./body[1]/DosageR[1]', 'varchar(255)')                as [DosageR]
                     , t.c.value('./body[1]/PackQn[1]', 'int')                          as [PackQn]
                     , t.c.value('./body[1]/Pack[1]', 'varchar(1000)')                  as [Pack]
                     , t.c.value('./body[1]/DrugFmNmRS[1]', 'varchar(1000)')            as [DrugFmNmRS]
                     , t.c.value('./body[1]/Segment[1]', 'varchar(255)')                as [Segment]
                     , t.c.value('./body[1]/Year[1]', 'int')                            as [Year]
                     , t.c.value('./body[1]/Month[1]', 'int')                           as [Month]
                     , t.c.value('./body[1]/Series[1]', 'varchar(255)')                 as [Series]
                     , t.c.value('./body[1]/TotDrugQn[1]', 'decimal(12,2)')             as [TotDrugQn]
                     , t.c.value('./body[1]/MnfPrice[1]', 'decimal(12,2)')              as [MnfPrice]
                     , t.c.value('./body[1]/PrcPrice[1]', 'decimal(12,2)')              as [PrcPrice]
                     , t.c.value('./body[1]/RtlPrice[1]', 'decimal(12,2)')              as [RtlPrice]
                     , t.c.value('./body[1]/Funds[1]', 'decimal(12,2)')                 as [Funds]
                     , t.c.value('./body[1]/VendorID[1]', 'varchar(255)')               as [VendorID]
                     , t.c.value('./body[1]/Remark[1]', 'varchar(1000)')                as [Remark]
                     , t.c.value('./body[1]/SrcOrg[1]', 'varchar(255)')                 as [SrcOrg]
                     , t.c.value('./body[1]/EAN[1]', 'varchar(14)')                     as [EAN]
                     , t.c.value('./body[1]/MaxMnfPrice[1]', 'decimal(12,2)')           as [MaxMnfPrice]
                     , t.c.value('./body[1]/ExpiTermR[1]', 'varchar(500)')              as [ExpiTermR]
                     , t.c.value('./body[1]/ClNm[1]', 'varchar(255)')                   as [ClNm]
                     , t.c.value('./body[1]/MnfNm[1]', 'varchar(1000)')                 as [MnfNm]
                     , t.c.value('./body[1]/PckNm[1]', 'varchar(1000)')                 as [PckNm]
                     , t.c.value('./body[1]/RegNr[1]', 'varchar(100)')                  as [RegNr]
                     , t.c.value('./body[1]/Active[1]', 'bit')                          as [Active]
                     , convert(datetime, t.c.value('./RegDate[1]', 'varchar(10)'), 104) as [RegDate]
                INTO #MEDICAMENT_OF_ROSZDRAVNADZOR
                from @Xml.nodes('MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)

                if (@OperationType = @DELETE)
                    BEGIN
                        update m
                        set m.Active = 0
                        from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                                 join #MEDICAMENT_OF_ROSZDRAVNADZOR nmf on m.MedicamentsOfRoszdravnadzorID = nmf.ID
                    END
                else
                    BEGIN
                        -- update
                        update m
                        set m.[DrugID]      = nmf.[DrugID]
                          , m.[TradeNmR]    = nmf.[TradeNmR]
                          , m.[InnR]        = nmf.[InnR]
                          , m.[PackNx]      = nmf.[PackNx]
                          , m.[DosageR]     = nmf.[DosageR]
                          , m.[PackQn]      = nmf.[PackQn]
                          , m.[Pack]        = nmf.[Pack]
                          , m.[DrugFmNmRS]  = nmf.[DrugFmNmRS]
                          , m.[Segment]     = nmf.[Segment]
                          , m.[Year]        = nmf.[Year]
                          , m.[Month]       = nmf.[Month]
                          , m.[Series]      = nmf.[Series]
                          , m.[TotDrugQn]   = nmf.[TotDrugQn]
                          , m.[MnfPrice]    = nmf.[MnfPrice]
                          , m.[PrcPrice]    = nmf.[PrcPrice]
                          , m.[RtlPrice]    = nmf.[RtlPrice]
                          , m.[Funds]       = nmf.[Funds]
                          , m.[VendorID]    = nmf.[VendorID]
                          , m.[Remark]      = nmf.[Remark]
                          , m.[SrcOrg]      = nmf.[SrcOrg]
                          , m.[EAN]         = nmf.[EAN]
                          , m.[MaxMnfPrice] = nmf.[MaxMnfPrice]
                          , m.[ExpiTermR]   = nmf.[ExpiTermR]
                          , m.[ClNm]        = nmf.[ClNm]
                          , m.[MnfNm]       = nmf.[MnfNm]
                          , m.[PckNm]       = nmf.[PckNm]
                          , m.[RegNr]       = nmf.[RegNr]
                          , m.[RegDate]     = nmf.[RegDate]
                          , m.[Active]      = ISNULL(nmf.[Active], 1)
                        from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                                 join #MEDICAMENT_OF_ROSZDRAVNADZOR nmf on nmf.ID = m.MedicamentsOfRoszdravnadzorID
                        where @OperationType = @UPDATE
                          and m.Active = 1

                        -- insert
                        INSERT INTO [PHARM].[T_MEDICAMENTS_OF_ROSZDRAVNADZOR]
                        ( [MedicamentsOfRoszdravnadzorID]
                        , [DrugID]
                        , [TradeNmR]
                        , [InnR]
                        , [PackNx]
                        , [DosageR]
                        , [PackQn]
                        , [Pack]
                        , [DrugFmNmRS]
                        , [Segment]
                        , [Year]
                        , [Month]
                        , [Series]
                        , [TotDrugQn]
                        , [MnfPrice]
                        , [PrcPrice]
                        , [RtlPrice]
                        , [Funds]
                        , [VendorID]
                        , [Remark]
                        , [SrcOrg]
                        , [EAN]
                        , [MaxMnfPrice]
                        , [ExpiTermR]
                        , [ClNm]
                        , [MnfNm]
                        , [PckNm]
                        , [RegNr]
                        , [RegDate]
                        , [Active])
                        select nmf.[ID]
                             , nmf.[DrugID]
                             , nmf.[TradeNmR]
                             , nmf.[InnR]
                             , nmf.[PackNx]
                             , nmf.[DosageR]
                             , nmf.[PackQn]
                             , nmf.[Pack]
                             , nmf.[DrugFmNmRS]
                             , nmf.[Segment]
                             , nmf.[Year]
                             , nmf.[Month]
                             , nmf.[Series]
                             , nmf.[TotDrugQn]
                             , nmf.[MnfPrice]
                             , nmf.[PrcPrice]
                             , nmf.[RtlPrice]
                             , nmf.[Funds]
                             , nmf.[VendorID]
                             , nmf.[Remark]
                             , nmf.[SrcOrg]
                             , nmf.[EAN]
                             , nmf.[MaxMnfPrice]
                             , nmf.[ExpiTermR]
                             , nmf.[ClNm]
                             , nmf.[MnfNm]
                             , nmf.[PckNm]
                             , nmf.[RegNr]
                             , nmf.[RegDate]
                             , ISNULL(nmf.[Active], 1)
                        from #MEDICAMENT_OF_ROSZDRAVNADZOR nmf
                        where not exists
                            (select *
                             from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                             where m.MedicamentsOfRoszdravnadzorID = nmf.ID)
                          and @OperationType = @INSERT
                    END
            END


        update bg
        set bg.MedicamentsOfRoszdravnadzor = null
        from PHARM.T_BILL_OF_GOODS bg
			join PHARM.[T_MEDICAMENTS_OF_ROSZDRAVNADZOR] mor on mor.MedicamentsOfRoszdravnadzorID = bg.MedicamentsOfRoszdravnadzor
        where mor.Active = 0

    END
END
END
GO