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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_MedicationForms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_MedicationForms]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекартсвенных форм в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_MedicationForms]
     @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())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'


IF @PATH IS NOT NULL
	BEGIN
IF (SELECT OBJECT_ID('tempdb..#T_MEDICATION_FORMS')) IS NOT NULL
	DROP TABLE #T_MEDICATION_FORMS
CREATE TABLE #T_MEDICATION_FORMS
	(
		 Name				VARCHAR(255)
		,Code				INT
		,Active				BIT
		,FormType			INT
		,NameLat			VARCHAR(4000)
		,FedCode			VARCHAR(50)
		,RegCode			VARCHAR(50)
		,MedicationFormsID	UNIQUEIDENTIFIER
        ,EhrRusVzn431       INT
        ,EhrRusVzn431Ver    VARCHAR(8)
		,PackageType	    UNIQUEIDENTIFIER
	)
SET @SQL = 'INSERT INTO tempdb..#T_MEDICATION_FORMS
			SELECT
				 mf.Name
				,mf.Code
				,Active
				,FormType
				,NameLat
				,FedCode
				,RegCode
				,MedicationFormsID
                ,er.IdEHR
				,RRB.CurrVersion
                ,PackageType
			FROM ' + @PATH + '.[PHARM].T_MEDICATION_FORMS mf
                left join ' + @PATH + '.EHR.T_EHR_RUS_VZN431 er on er.EhrRusID = mf.EhrRusVzn431
			    left join ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks'
EXECUTE (@SQL)

	-- первичная загрузка

	--DELETE mf
	--	FROM [PHARM].T_MEDICATION_FORMS mf
	--WHERE NOT EXISTS
	--    (
	--        SELECT *
	--			FROM #T_MEDICATION_FORMS nmf
	--        WHERE nmf.MedicationFormsID = mf.MedicationFormsID
	--    )

    -- Лек. формы не из справочника RDS переводим в неактивные
    UPDATE mf
    SET mf.Active = 0
		FROM [PHARM].T_MEDICATION_FORMS mf
    WHERE NOT EXISTS
        (
            SELECT *
		FROM #T_MEDICATION_FORMS nmf
            WHERE nmf.MedicationFormsID = 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 = ehr.EhrRusID
		,MF.PackageType		= nmf.PackageType
    from [PHARM].T_MEDICATION_FORMS mf
        join #T_MEDICATION_FORMS nmf on nmf.MedicationFormsID = mf.MedicationFormsID
        outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_VZN431 er
                join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nmf.EhrRusVzn431 AND RRB.CurrVersion = nmf.EhrRusVzn431Ver
		) ehr

    -- Загружаем новые
    INSERT INTO [PHARM].[T_MEDICATION_FORMS]
               ([MedicationFormsID] ,[Name] ,[Active],[FormType],RegCode,FedCode, NameLat,Code, EhrRusVzn431,PackageType)
    SELECT
         nmf.MedicationFormsID--<MedicationFormsID, uniqueidentifier,>
        ,nmf.Name--<Name, varchar(255),>
        ,nmf.Active--<Active, bit,>
	    ,ISNULL(nmf.FormType,0)
	    ,nmf.RegCode
	    ,nmf.FedCode
	    ,nmf.NameLat
        ,nmf.Code
        ,ehr.EhrRusID
        ,nmf.PackageType
    FROM #T_MEDICATION_FORMS nmf
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_VZN431 er
                join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
		) ehr
    WHERE NOT EXISTS
        (
            SELECT *
		FROM [PHARM].T_MEDICATION_FORMS mf
            WHERE mf.MedicationFormsID = nmf.MedicationFormsID
        )
	END
	ELSE
	BEGIN

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'MEDICATION_FORMS'
              ,@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.Active				as 'MEDICATION_FORM/Active'
                                  ,mf.Name					as 'MEDICATION_FORM/Name'
								  ,mf.FormType				as 'MEDICATION_FORM/FormType'
								  ,RegCode					as 'MEDICATION_FORM/RegCode'
								  ,FedCode					as 'MEDICATION_FORM/FedCode'
								  ,NameLat					as 'MEDICATION_FORM/NameLat'
								  ,mf.Code					as 'MEDICATION_FORM/Code'
                                  ,er.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 er on er.EhrRusID = mf.EhrRusVzn431
							    LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.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'
					  ,RegCode		        as 'body/RegCode'
					  ,FedCode		        as 'body/FedCode'
					  ,NameLat		        as 'body/NameLat'
					  ,mf.Code		        as 'body/Code'
                      ,er.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 er on er.EhrRusID = mf.EhrRusVzn431
				    LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                where mf.MedicationFormsID = @ID
                for xml path(''),
                root('MEDICATION_FORMS')
            )
        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('MEDICATION_FORMS') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- 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)

            -- Первичная загрузка
            -- Лек. формы не из справочника 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 = ehr.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 er.EhrRusID
					from EHR.T_EHR_RUS_VZN431 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
				) ehr

            -- Загружаем новые
            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
                       ,ehr.EhrRusID
                       ,nmf.PackageType
            from #MEDICATION_FORMSS nmf
            outer apply (
				select top 1 er.EhrRusID
				from EHR.T_EHR_RUS_VZN431 er
                    join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver
			) ehr

            where not exists
                (
                    select *
                    from [PHARM].T_MEDICATION_FORMS mf
                    where mf.MedicationFormsID = nmf.ID
                )
        END
        ELSE
            BEGIN
                -- 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)

                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 = ehr.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 er.EhrRusID
                                              from EHR.T_EHR_RUS_VZN431 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nmf.EhrRusVzn431
                                                and RRB.CurrVersion = nmf.EhrRusVzn431Ver) ehr
                        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
                             , ehr.EhrRusID
                             , nmf.PackageType
                        from #MEDICATION_FORMS nmf
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_VZN431 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nmf.EhrRusVzn431
                                                and RRB.CurrVersion = nmf.EhrRusVzn431Ver) ehr
                        where @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_MEDICATION_FORMS mf
                             where mf.MedicationFormsID = nmf.ID)

                    END
            END


		declare @EntId int
		select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'MEDICATION_FORMS'
        IF(exists (select * 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

    END
END
END
GO