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_DoseView]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DoseView]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника доз в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_DoseView]
     @Xml xml = null
    ,@IsNew bit = 1
    ,@ID int = 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'

IF @PATH IS NOT NULL
	BEGIN
IF (SELECT OBJECT_ID('tempdb..#T_DOSE_VIEW')) IS NOT NULL
	DROP TABLE #T_DOSE_VIEW
CREATE TABLE #T_DOSE_VIEW
	(
		 Name					VARCHAR(MAX)
		,Code					INT
		,ViewType				INT
		,OtherID				INT
		,FullName				VARCHAR
		,FedCode				VARCHAR
		,RegCode				VARCHAR
		,EhrRusS0JLHGTLBJ		INT
		,EhrRusS0JLHGTLBJVer	VARCHAR
	)
SET @SQL = 'INSERT INTO tempdb..#T_DOSE_VIEW
			SELECT 
				 [Name]
				,[Code]
				,[ViewType]
				,[OtherID]
				,[FullName]
				,[FedCode]
				,[RegCode]
				,EHR.[IdEHR] AS [EhrRusS0JLHGTLBJ]
				,EHR.[CurrVersion] AS [EhrRusS0JLHGTLBJVer]
			FROM ' + @PATH + '.[PHARM].T_DOSE_VIEW TDV
	OUTER APPLY 
		(
			SELECT TOP 1 er.IdEHR,  RRB.CurrVersion
				FROM ' + @PATH + '.EHR.T_EHR_RUS_S0JLHGTLBJ er
					LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				WHERE er.EhrRusID = TDV.EhrRusS0JLHGTLBJ
				ORDER BY RRB.CURRVERSION DESC
		) EHR'

EXECUTE (@SQL)

	--DELETE DV
	--	FROM [PHARM].T_DOSE_VIEW dv
	--JOIN #T_DOSE_VIEW dvl ON dvl.[Code] = dv.Code

-- Обновляем имеющиеся

	update dv
	set dv.Name = dvl.Name
		,dv.FullName = dvl.FullName
		,dv.OtherID = dvl.OtherID
		,dv.ViewType = dvl.ViewType
	    ,dv.RegCode = dvl.RegCode
	    ,dv.FedCode = dvl.FedCode
	    ,dv.EhrRusS0JLHGTLBJ = ehr.EhrRusID
	from [PHARM].T_DOSE_VIEW dv
	    join #T_DOSE_VIEW dvl on dvl.[Code] = dv.Code
		outer apply 
		(
			select top 1 er.EhrRusID 
				from EHR.T_EHR_RUS_S0JLHGTLBJ er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer
		) ehr
	
	-- Загружаем новые
	INSERT INTO [PHARM].T_DOSE_VIEW
			(
			     [Code]
			    ,[Name] 
				,[FullName] 
				,[OtherID]
				,[ViewType]
				,[RegCode]
				,[FedCode]
				,[EhrRusS0JLHGTLBJ]
			)
	SELECT
	            dvl.[Code]
			   ,dvl.Name
			   ,dvl.FullName
			   ,dvl.OtherID
			   ,dvl.ViewType
			   ,RegCode
			   ,FedCode
			   ,ehr.EhrRusID
	from #T_DOSE_VIEW dvl
		outer apply (
			select top 1 er.EhrRusID 
				from EHR.T_EHR_RUS_S0JLHGTLBJ er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer
		) ehr
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_DOSE_VIEW dv
	        WHERE dv.Code = dvl.Code
	    )

	END
	ELSE
	BEGIN

  IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'DOSE_VIEW'
              ,@OwnerID = newiD()

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD                           as 'header/TYPE'
                      ,(
                            select dv.Code				as 'DOSE_VIEW/ID'
                                  ,dv.Name				as 'DOSE_VIEW/Name'
                                  ,dv.FullName			as 'DOSE_VIEW/FullName'
                                  ,dv.OtherID			as 'DOSE_VIEW/OtherID'
                                  ,dv.ViewType			as 'DOSE_VIEW/ViewType'
								  ,RegCode				as 'DOSE_VIEW/RegCode'
								  ,FedCode				as 'DOSE_VIEW/FedCode'
								  ,er.IdEHR				as 'DOSE_VIEW/EhrRusS0JLHGTLBJ'
								  ,RRB.CurrVersion		as 'DOSE_VIEW/EhrRusS0JLHGTLBJVer'
                            from [PHARM].T_DOSE_VIEW dv
								left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks 
                            for xml path(''),
                            type
                      ) as 'body/DOSE_VIEW_LIST'
                for xml path(''),
                root('DOSE_VIEW')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем DOSE_VIEW
            SELECT @Result = 
            ( 
                select @OperationType        as 'header/TYPE'
						,@ID                 as 'header/ID'
						,dv.Name             as 'body/Name'
						,dv.FullName		 as 'body/FullName'
						,dv.OtherID			 as 'body/OtherID'
						,dv.ViewType		 as 'body/ViewType'
						,RegCode			 as 'body/RegCode'
						,FedCode			 as 'body/FedCode'
						,er.IdEHR			 as 'DOSE_VIEW/EhrRusS0JLHGTLBJ'
						,RRB.CurrVersion	 as 'DOSE_VIEW/EhrRusS0JLHGTLBJVer'
                from [PHARM].T_DOSE_VIEW dv
					left join EHR.T_EHR_RUS_S0JLHGTLBJ er on er.EhrRusID = dv.EhrRusS0JLHGTLBJ
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                where dv.Code = @ID
                for xml path(''),
                root('DOSE_VIEW')
            ) 
        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(36)')
        from @Xml.nodes('DOSE_VIEW') T(c)

        -- Generate table #DOSE_VIEW_LIST
        select t.c.value('./ID[1]', 'int')								as [ID]
              ,t.c.value('./Name[1]', 'varchar(256)')					as [Name]
              ,t.c.value('./FullName[1]', 'varchar(4000)')				as [FullName]
              ,t.c.value('./OtherID[1]', 'int')							as [OtherID]
              ,t.c.value('./ViewType[1]', 'int')						as [ViewType]
			  ,t.c.value('./RegCode[1]', 'varchar(50)')					as RegCode
			  ,t.c.value('./FedCode[1]', 'varchar(50)')					as FedCode
			  ,t.c.value('./EhrRusS0JLHGTLBJ[1]', 'int')				as EhrRusS0JLHGTLBJ
			  ,t.c.value('./EhrRusS0JLHGTLBJVer[1]', 'varchar(50)')		as EhrRusS0JLHGTLBJVer
            INTO #DOSE_VIEW_LIST
        from @Xml.nodes('DOSE_VIEW/body/DOSE_VIEW_LIST/DOSE_VIEW') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #DOSE_VIEW
        select T.c.value('./header[1]/ID[1]', 'int')							as [ID]
              ,T.c.value('./body[1]/Name[1]', 'varchar(256)')					as [Name]
              ,t.c.value('./body[1]/FullName[1]', 'varchar(4000)')				as [FullName]
              ,t.c.value('./body[1]/OtherID[1]', 'int')							as [OtherID]
              ,t.c.value('./body[1]/ViewType[1]', 'int')						as [ViewType]
			  ,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]/EhrRusS0JLHGTLBJ[1]', 'int')				as EhrRusS0JLHGTLBJ
			  ,t.c.value('./body[1]/EhrRusS0JLHGTLBJVer[1]', 'varchar(50)')		as EhrRusS0JLHGTLBJVer
            INTO #DOSE_VIEW
        from @Xml.nodes('DOSE_VIEW') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Обновляем имеющиеся
            update dv
            set dv.Name = dvl.Name
				,dv.FullName = dvl.FullName
				,dv.OtherID = dvl.OtherID
				,dv.ViewType = dvl.ViewType
			    ,dv.RegCode = dvl.RegCode
			    ,dv.FedCode = dvl.FedCode
			    ,dv.EhrRusS0JLHGTLBJ = ehr.EhrRusID
            from [PHARM].T_DOSE_VIEW dv
                join #DOSE_VIEW_LIST dvl on dvl.ID = dv.Code
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_S0JLHGTLBJ er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer
				) ehr

            -- Загружаем новые
            INSERT INTO [PHARM].T_DOSE_VIEW
                       ([Code], [Name], [FullName], [OtherID], [ViewType],RegCode,FedCode,EhrRusS0JLHGTLBJ)
            SELECT
                       dvl.ID
					   ,dvl.Name
					   ,dvl.FullName
					   ,dvl.OtherID
					   ,dvl.ViewType
					   ,RegCode
					   ,FedCode
					   ,ehr.EhrRusID
            from #DOSE_VIEW_LIST dvl
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_S0JLHGTLBJ er 
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dvl.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dvl.EhrRusS0JLHGTLBJVer
				) ehr
            where not exists
                (
                    select *
                    from [PHARM].T_DOSE_VIEW dv
                    where dv.Code = dvl.ID
                )
        END
        else
        BEGIN
            update d
            set d.Name = dv.Name
				,d.FullName = dv.FullName
				,d.OtherID = dv.OtherID
				,d.ViewType = dv.ViewType
			    ,d.RegCode = dv.RegCode
			    ,d.FedCode = dv.FedCode
			    ,d.EhrRusS0JLHGTLBJ = ehr.EhrRusID
            from [PHARM].T_DOSE_VIEW d
                join #DOSE_VIEW dv on dv.ID = d.Code
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_S0JLHGTLBJ er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dv.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dv.EhrRusS0JLHGTLBJVer
				) ehr
            where @OperationType = @UPDATE

            -- Загружаем новый TRN
            INSERT INTO [PHARM].T_DOSE_VIEW
                       ([Code], [Name], [FullName], [OtherID], [ViewType],RegCode,FedCode,EhrRusS0JLHGTLBJ)
            SELECT
                       dv.ID
					   ,dv.Name
					   ,dv.FullName
					   ,dv.OtherID
					   ,dv.ViewType
					   ,RegCode
					   ,FedCode
					   ,ehr.EhrRusID
            from #DOSE_VIEW dv
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_S0JLHGTLBJ er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = dv.EhrRusS0JLHGTLBJ and RRB.CURRVERSION = dv.EhrRusS0JLHGTLBJVer
				) ehr
            where not exists
                (
                    select *
                    from [PHARM].T_DOSE_VIEW dvv
                    where dvv.Code = dv.ID
                )
                    and
                @OperationType = @INSERT

        END
                
        declare @EntId int
        select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'DOSE_VIEW' and [Schema] = 'PHARM' 

        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/0'))
        BEGIN
			update gn
			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 0), 1)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.DOSE_VIEW/0'
        END
        ELSE
        BEGIN
        
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			VALUES
			      (newID()
			      ,@EntId
			      ,'PHARM.DOSE_VIEW/0'
			      ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 0), 1))
        END

        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/1'))
        BEGIN
        			update gn
        			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 1), 1)
        			from [APS].T_GEN_NUMBERS gn
        			where 
						gn.EntityID = @EntId
        				and gn.NumberName = 'PHARM.DOSE_VIEW/1'
        		END
        ELSE
        BEGIN
        
        			INSERT INTO [APS].[T_GEN_NUMBERS]
        			           ([GenNumbersID]
        			           ,[EntityID]
        			           ,[NumberName]
        			           ,[NextNumber])
        			     VALUES
        			           (newID()
        			           ,@EntId
        			           ,'PHARM.DOSE_VIEW/1'
        			           ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 1), 1))
        END
        
        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/2'))
        BEGIN
			update gn
			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 2), 1)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.DOSE_VIEW/2'
        END
        ELSE
        BEGIN
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			VALUES
        			           (newID()
        			           ,@EntId
        			           ,'PHARM.DOSE_VIEW/2'
        			           ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 2), 1))
        END
        
        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/3'))
        BEGIN
			update gn
			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 3), 1)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.DOSE_VIEW/3'
        END
        ELSE
        BEGIN
        
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			VALUES
			      (newID()
			      ,@EntId
			      ,'PHARM.DOSE_VIEW/3'
			      ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 3), 1))
        END
        
        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/4'))
        BEGIN
			update gn
			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 4), 1)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.DOSE_VIEW/4'
        END
        ELSE
        BEGIN
        
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			VALUES
			      (newID()
			      ,@EntId
			      ,'PHARM.DOSE_VIEW/4'
			      ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 4), 1))
        END
        
        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.DOSE_VIEW/5'))
        BEGIN
			update gn
			set gn.NextNumber = ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 5), 1)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.DOSE_VIEW/5'
        END
        ELSE
        BEGIN
        
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			VALUES
			      (newID()
			      ,@EntId
			      ,'PHARM.DOSE_VIEW/5'
			      ,ISNULL((select MAX(Code) from [PHARM].T_DOSE_VIEW where ViewType = 5), 1))
        END

        DROP TABLE #DOSE_VIEW
        DROP TABLE #DOSE_VIEW_LIST

    END
END
END
GO