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_GetSetMedicationForms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetMedicationForms]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекарственных форм в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetMedicationForms]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = 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)

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

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD               as 'header/TYPE'
                      ,(
                            select 	mf.MedicationFormsID	as 'MEDICATION_FORM/ID'
								  ,mf.Code					as 'MEDICATION_FORM/Code'
                                  ,mf.Active				as 'MEDICATION_FORM/Active'
                                  ,mf.Name					as 'MEDICATION_FORM/Name'
								  ,mf.FormType				as 'MEDICATION_FORM/FormType'
								  ,mf.RegCode				as 'MEDICATION_FORM/RegCode'
								  ,mf.FedCode				as 'MEDICATION_FORM/FedCode'
								  ,mf.NameLat				as 'MEDICATION_FORM/NameLat'
								  ,ehr.IdEHR				as 'MEDICATION_FORM/EhrRusVzn431'
								  ,RRB.CurrVersion			as 'MEDICATION_FORM/EhrRusVzn431Ver'
								  ,mf.PackageType			as 'MEDICATION_FORM/PackageType'
                            from [PHARM].T_MEDICATION_FORMS mf
                                left join EHR.T_EHR_RUS_VZN431 ehr on ehr.EhrRusID = mf.EhrRusVzn431
								left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
                            for xml path(''),
                            type
                      ) as 'body/MEDICATION_FORMS'
                for xml path(''),
                root('MEDICATION_FORMS')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем MEDICATION_FORMS
            SELECT @Result = 
            ( 
                select @OperationType	    as 'header/TYPE'
                      ,@ID				    as 'header/ID'
                      ,mf.Active		    as 'body/Active'
                      ,mf.Name			    as 'body/Name'
					  ,mf.FormType		    as 'body/FormType'
					  ,mf.RegCode		    as 'body/RegCode'
					  ,mf.FedCode		    as 'body/FedCode'
					  ,mf.NameLat		    as 'body/NameLat'
					  ,mf.Code			    as 'body/Code'
					  ,ehr.IdEHR			as 'body/EhrRusVzn431'
					  ,RRB.CurrVersion		as 'body/EhrRusVzn431Ver'
					  ,mf.PackageType		as 'body/PackageType'
                from [PHARM].T_MEDICATION_FORMS mf
                    left join EHR.T_EHR_RUS_VZN431 ehr on ehr.EhrRusID = mf.EhrRusVzn431
				        left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
                where mf.MedicationFormsID = @ID
                for xml path(''),
                root('MEDICATION_FORMS')
            ) 
        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
    ELSE 
    IF (@Xml is not null)
    BEGIN
        
        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('MEDICATION_FORMS') T(c)

        -- Generate table #MEDICATION_FORMSS
        select t.c.value('./ID[1]', 'uniqueidentifier')             as [ID]
              ,t.c.value('./Active[1]', 'bit')					    as [Active]
              ,t.c.value('./Name[1]', 'varchar(255)')			    as [Name]
              ,t.c.value('./NameLat[1]', 'varchar(255)')		    as [NameLat]
			  ,t.c.value('./FormType[1]', 'int')				    as [FormType]
			  ,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
			  ,t.c.value('./EhrRusVzn431[1]', 'int')			    as EhrRusVzn431
			  ,t.c.value('./EhrRusVzn431Ver[1]', 'varchar(8)')	    as EhrRusVzn431Ver
			  ,t.c.value('./PackageType[1]', 'uniqueidentifier')	    as PackageType
            INTO #MEDICATION_FORMSS
        from @Xml.nodes('MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #MEDICATION_FORMS
        select T.c.value('./header[1]/ID[1]', 'uniqueidentifier')       as [ID]
              ,T.c.value('./body[1]/Active[1]', 'bit')				    as [Active]
              ,T.c.value('./body[1]/Name[1]', 'varchar(255)')		    as [Name]
              ,T.c.value('./body[1]/NameLat[1]', 'varchar(255)')	    as [NameLat]
			  ,t.c.value('./body[1]/FormType[1]', 'int')			    as [FormType]
			  ,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
			  ,t.c.value('./body[1]/EhrRusVzn431[1]', 'int')			as EhrRusVzn431
			  ,t.c.value('./body[1]/EhrRusVzn431Ver[1]', 'varchar(8)')	as EhrRusVzn431Ver
			  ,t.c.value('./body[1]/PackageType[1]', 'uniqueidentifier')	as PackageType
            INTO #MEDICATION_FORMS
        from @Xml.nodes('MEDICATION_FORMS') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- Лек. формы не из справочника RDS переводим в неактивные
            update mf
            set mf.Active = 0
            from [PHARM].T_MEDICATION_FORMS mf
            where not exists
                (
                    select *
                    from #MEDICATION_FORMSS nmf
                    where nmf.ID = mf.MedicationFormsID
                )

            -- Обновляем имеющиеся МНН
            update mf
            set mf.Name			    = nmf.Name
               ,mf.Active		    = nmf.Active
			   ,mf.FormType		    = ISNULL(nmf.FormType,0)
			   ,mf.RegCode		    = nmf.RegCode
			   ,mf.FedCode		    = nmf.FedCode
			   ,mf.NameLat		    = nmf.NameLat
			   ,mf.Code			    = nmf.Code
			   ,mf.EhrRusVzn431	    = eh1.EhrRusID
			   ,mf.PackageType	    = nmf.PackageType
            from [PHARM].T_MEDICATION_FORMS mf
                join #MEDICATION_FORMSS nmf on nmf.ID = mf.MedicationFormsID
                outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_VZN431 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
				) eh1
            -- Загружаем новые
            INSERT INTO [PHARM].[T_MEDICATION_FORMS]
                       (MedicationFormsID, [Name], [Active], [FormType], RegCode, FedCode, NameLat, [Code], EhrRusVzn431,PackageType)
            SELECT
				 nmf.ID--<MedicationFormsID, uniqueidentifier,>
				,nmf.Name--<Name, varchar(255),>
				,nmf.Active--<Active, bit,>
				,ISNULL(nmf.FormType,0)
				,nmf.RegCode
				,nmf.FedCode
				,nmf.NameLat
				,nmf.Code
			    ,eh1.EhrRusID
                ,nmf.PackageType
            from #MEDICATION_FORMSS nmf
            outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_VZN431 eh
					    join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
				) eh1
            where not exists
                (
                    select *
                    from [PHARM].T_MEDICATION_FORMS mf
                    where mf.MedicationFormsID = nmf.ID
                )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            update mf
            set mf.Active = 0
            from [PHARM].[T_MEDICATION_FORMS] mf
                join #MEDICATION_FORMS nmf on nmf.[ID] = mf.MedicationFormsID
        END
        ELSE
        BEGIN
            -- update [PHARM].T_MEDICATION_FORMS
            update mf
            set mf.Name         = nmf.Name
               ,mf.Active       = nmf.Active
			   ,mf.FormType     = ISNULL(nmf.FormType,0)
			   ,mf.RegCode      = nmf.RegCode
			   ,mf.FedCode      = nmf.FedCode
			   ,mf.NameLat      = nmf.NameLat
			   ,mf.Code         = nmf.Code
			   ,mf.EhrRusVzn431 = eh1.EhrRusID
			   ,mf.PackageType  = nmf.PackageType
            from [PHARM].T_MEDICATION_FORMS mf
                join #MEDICATION_FORMS nmf on nmf.ID = mf.MedicationFormsID
                outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_VZN431 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
				) eh1
            where @OperationType = @UPDATE

            -- insert [PHARM].T_MEDICATION_FORMS
            INSERT INTO [PHARM].[T_MEDICATION_FORMS]
                (MedicationFormsID ,[Name] ,[Active],[FormType],RegCode,FedCode, NameLat, [Code], EhrRusVzn431,PackageType)
            SELECT
				 nmf.ID--<MedicationFormsID, uniqueidentifier,>
				,nmf.Name--<Name, varchar(255),>
				,nmf.Active--<Active, bit,>
				,ISNULL(nmf.FormType,0)
				,nmf.RegCode
				,nmf.FedCode
				,nmf.NameLat
				,nmf.Code
				,eh1.EhrRusID
				,nmf.PackageType
            from #MEDICATION_FORMS nmf
            outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_VZN431 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
				) eh1
            where 
				@OperationType = @INSERT
				and not exists
                (
                    select *
                    from [PHARM].T_MEDICATION_FORMS mf
                    where mf.MedicationFormsID = nmf.ID
                )
   
        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 
			s.ServerName <> @QueueName
            and @OperationType <> @FTLOAD

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

        -- Drop tables
        DROP TABLE #MEDICATION_FORMS
        DROP TABLE #MEDICATION_FORMSS

    END
END
GO