IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_Pharmacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Pharmacy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_Pharmacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_Pharmacy]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-09-29
-- Description: Выгрузка/загрузка справочника Аптек в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_Pharmacy]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID varchar(255) = null,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID varchar(255)
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

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

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'PHARMACY'
              ,@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 [RDS].T_PHARMACY p where p.Code = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [RDS].T_PHARMACY p where p.Code = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result = 
            (
                select 
						@OperationType as 'header/TYPE'
                       ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('PHARMACY')
            ) 
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD                                            as 'header/TYPE'
                      ,(
                            select p.Code                                 as 'PHARMACY/ID'
                                  ,p.Name                                 as 'PHARMACY/Name'
                                  ,p.NameFull                             as 'PHARMACY/NameFull'
                                  ,p.OGRN                                 as 'PHARMACY/OGRN'
                                  ,p.Terr                                 as 'PHARMACY/Terr'
                                  ,p.OKATO                                as 'PHARMACY/OKATO'
                                  ,convert(varchar(10), p.DateBegin, 104) as 'PHARMACY/DateBegin'
                                  ,convert(varchar(10), p.DateEnd, 104)   as 'PHARMACY/DateEnd'
                                  ,convert(varchar(10), @dateTime, 104)   as 'PHARMACY/RegisterDate'
                                  ,p.RecipeServedTypes                    as 'PHARMACY/RecipeServedTypes'
								  ,p.[Address]						      as 'PHARMACY/Address'
								  ,r.[Code]								  as 'PHARMACY/Rajon'
								  ,p.[Number]							  as 'PHARMACY/Number'
								  ,p.Phone								  as 'PHARMACY/Phone'
                                  ,p.LicenseNumber						  as 'PHARMACY/LicenseNumber'
								  ,p.LicenseIssue						  as 'PHARMACY/LicenseIssue'
								  ,p.LicenseDate						  as 'PHARMACY/LicenseDate'
								  ,p.FiasAddress						  as 'PHARMACY/FiasAddress'
								  ,p.PostalCode						      as 'PHARMACY/PostalCode'
								  ,p.FiasHome							  as 'PHARMACY/FiasHome'
								  ,e.IdEHR								  as 'PHARMACY/EhrRusMdr308'
								  ,RRB.CurrVersion						  as 'PHARMACY/EhrRusMdr308Ver'
								  ,p.Dlo								  as 'PHARMACY/Dlo'
                            from [APS].T_PHARMACY p
								left join [APS].T_RAJON r on r.RajonID = p.Rajon
								left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
                			for xml path(''),
                            type
                      ) as 'body/PHARMACYS'
                for xml path(''),
                root('PHARMACY')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем PHARMACY
            SELECT @Result = 
            ( 
                select @OperationType                         as 'header/TYPE'
                      ,@ID                                    as 'header/ID'
                      ,p.Name                                 as 'body/Name'
                      ,p.NameFull                             as 'body/NameFull'
                      ,p.OGRN                                 as 'body/OGRN'
                      ,p.Terr                                 as 'body/Terr'
                      ,p.OKATO                                as 'body/OKATO'
                      ,convert(varchar(10), p.DateBegin, 104) as 'body/DateBegin'
                      ,convert(varchar(10), p.DateEnd, 104)   as 'body/DateEnd'
                      ,convert(varchar(10), @dateTime, 104)   as 'body/RegisterDate'
                      ,p.RecipeServedTypes                    as 'body/RecipeServedTypes'
					  ,p.[Address]						      as 'body/Address'
					  ,r.[Code]								  as 'body/Rajon'
					  ,p.[Number]							  as 'body/Number'
					  ,p.Phone								  as 'body/Phone'
                      ,p.LicenseNumber						  as 'body/LicenseNumber'
                      ,p.LicenseIssue						  as 'body/LicenseIssue'
                      ,p.LicenseDate						  as 'body/LicenseDate'
					  ,p.FiasAddress						  as 'body/FiasAddress'
                      ,p.PostalCode						      as 'body/PostalCode'
					  ,p.FiasHome							  as 'body/FiasHome'
					  ,e.IdEHR								  as 'body/EhrRusMdr308'
					  ,RRB.CurrVersion						  as 'body/EhrRusMdr308Ver'
					  ,p.Dlo								  as 'body/Dlo'
				from [APS].T_PHARMACY p
					left join [APS].T_RAJON r on r.RajonID = p.Rajon
					left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
				where p.Code = @ID
				for xml path(''),
				root('PHARMACY')
            ) 
        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,>)
              ,null--,<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('PHARMACY') T(c)

        -- Generate table #PHARMACYS
        select t.c.value('./ID[1]', 'varchar(255)')                                  as [ID]
              ,t.c.value('./Name[1]', 'varchar(255)')                                as [Name]
              ,t.c.value('./NameFull[1]', 'varchar(255)')                            as [NameFull]
              ,t.c.value('./OGRN[1]', 'varchar(255)')                                as [OGRN]
              ,t.c.value('./Terr[1]', 'varchar(255)')                                as [Terr]
              ,t.c.value('./OKATO[1]', 'varchar(255)')                               as [OKATO]
              ,convert(datetime, t.c.value('./DateBegin[1]', 'varchar(10)'), 104)    as [DateBegin]
              ,convert(datetime, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)      as [DateEnd]
              ,convert(datetime, t.c.value('./RegisterDate[1]', 'varchar(10)'), 104) as [RegisterDate]
              ,t.c.value('./RecipeServedTypes[1]', 'int')                            as [RecipeServedTypes]
			  ,t.c.value('./Address[1]', 'varchar(255)')                             as [Address]
			  ,t.c.value('./Rajon[1]', 'varchar(13)')								 as [Rajon]
			  ,t.c.value('./Number[1]', 'varchar(50)')								 as [Number]
			  ,t.c.value('./Phone[1]', 'varchar(40)')								 as Phone
			  ,t.c.value('./LicenseNumber[1]', 'varchar(255)')					     as LicenseNumber						  
			  ,t.c.value('./LicenseIssue[1]', 'varchar(255)')					     as LicenseIssue						  
			  ,convert(datetime, t.c.value('./LicenseDate[1]', 'varchar(10)'), 104)  as LicenseDate	
			  ,t.c.value('./Dlo[1]', 'int')											 as Dlo
			  ,t.c.value('./FiasAddress[1]', 'varchar(255)')						 as FiasAddress
			  ,t.c.value('./PostalCode[1]', 'varchar(255)')						     as PostalCode
			  ,t.c.value('./FiasHome[1]', 'varchar(255)')							 as FiasHome
			  ,t.c.value('./EhrRusMdr308[1]', 'int')								 as EhrRusMdr308
			  ,t.c.value('./EhrRusMdr308Ver[1]', 'varchar(8)')						 as EhrRusMdr308Ver
            INTO #PHARMACYS
        from @Xml.nodes('PHARMACY/body/PHARMACYS/PHARMACY') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #PHARMACY
        select T.c.value('./header[1]/ID[1]', 'varchar(255)')								 as [ID]
              ,t.c.value('./body[1]/Name[1]', 'varchar(255)')                                as [Name]
              ,t.c.value('./body[1]/NameFull[1]', 'varchar(255)')                            as [NameFull]
              ,t.c.value('./body[1]/OGRN[1]', 'varchar(255)')                                as [OGRN]
              ,t.c.value('./body[1]/Terr[1]', 'varchar(255)')                                as [Terr]
              ,t.c.value('./body[1]/OKATO[1]', 'varchar(255)')                               as [OKATO]
              ,convert(datetime, t.c.value('./body[1]/DateBegin[1]', 'varchar(10)'), 104)    as [DateBegin]
              ,convert(datetime, t.c.value('./body[1]/DateEnd[1]', 'varchar(10)'), 104)      as [DateEnd]
              ,convert(datetime, t.c.value('./body[1]/RegisterDate[1]', 'varchar(10)'), 104) as [RegisterDate]
              ,t.c.value('./body[1]/RecipeServedTypes[1]', 'int')                            as [RecipeServedTypes]
			  ,t.c.value('./body[1]/Address[1]', 'varchar(255)')                             as [Address]
			  ,t.c.value('./body[1]/Rajon[1]', 'varchar(13)')                                as [Rajon]
			  ,t.c.value('./body[1]/Number[1]', 'varchar(50)')								 as [Number]
			  ,t.c.value('./body[1]/Phone[1]', 'varchar(40)')								 as Phone
              ,t.c.value('./body[1]/LicenseNumber[1]', 'varchar(255)')					     as LicenseNumber						  
			  ,t.c.value('./body[1]/LicenseIssue[1]', 'varchar(255)')					     as LicenseIssue						  
			  ,convert(datetime, t.c.value('./body[1]/LicenseDate[1]', 'varchar(10)'), 104)  as LicenseDate	
			  ,t.c.value('./body[1]/Dlo[1]', 'int')											 as Dlo
			  ,t.c.value('./body[1]/FiasAddress[1]', 'varchar(255)')						 as FiasAddress
              ,t.c.value('./body[1]/PostalCode[1]', 'varchar(255)')						     as PostalCode
			  ,t.c.value('./body[1]/FiasHome[1]', 'varchar(255)')							 as FiasHome
			  ,t.c.value('./body[1]/EhrRusMdr308[1]', 'int')								 as EhrRusMdr308
			  ,t.c.value('./body[1]/EhrRusMdr308Ver[1]', 'varchar(8)')						 as EhrRusMdr308Ver
            INTO #PHARMACY
        from @Xml.nodes('PHARMACY') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- Аптеки не из справочника RDS переводим в неактивные
            update p
            set p.DateEnd = @dateTime
                ,p.RegisterDate = @dateTime
            from [APS].T_PHARMACY p
            where not exists
            (
                select *
                from #PHARMACYS np
                where np.ID = p.Code
            )

            -- Обновляем имеющиеся Аптеки
            update p
            set p.[Name] = np.[Name]
               ,p.[NameFull] = np.[NameFull]
               ,p.[OGRN] = np.[OGRN]
               ,p.[Terr] = np.[Terr]
               ,p.[OKATO] = np.[OKATO]
               ,p.[DateBegin] = np.[DateBegin]
               ,p.[DateEnd] = np.[DateEnd]
               ,p.[RegisterDate] = np.[RegisterDate]
               ,p.[RecipeServedTypes] = np.[RecipeServedTypes]
			   ,p.[Address] = np.[Address]
			   ,p.[Rajon] = (select top 1 r.RajonID from [APS].T_RAJON r where r.[Code] = np.[Rajon])
			   ,p.[Number] = np.[Number]
			   ,p.Phone = np.Phone
			   ,p.LicenseNumber = np.LicenseNumber
			   ,p.LicenseIssue = np.LicenseIssue
			   ,p.LicenseDate = np.LicenseDate
			   ,p.Dlo = ISNULL(np.Dlo, 0)
			   ,p.FiasAddress = np.FiasAddress
			   ,p.PostalCode = np.PostalCode
			   ,p.FiasHome = np.FiasHome
			   ,p.EhrRusMdr308 = e.EhrRusID
            from [APS].T_PHARMACY p
                join #PHARMACYS np on np.ID = p.Code
				outer apply (
					select e.EhrRusID 
					from EHR.T_EHR_RUS_MDR308 e
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
				) e

            -- Загружаем новые
            INSERT INTO [APS].[T_PHARMACY]
                       ([Code] ,[Name] ,[NameFull] ,[OGRN] ,[Terr] ,[OKATO] ,[DateBegin] ,[DateEnd] ,[RegisterDate] ,[RecipeServedTypes], [Address], [Rajon], [Number], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, PostalCode, FiasHome, EhrRusMdr308)
            SELECT
                       np.ID--<Code, int,>
                       ,np.Name--<Name, varchar(255),>
                       ,np.NameFull--<NameFull, varchar(255),>
                       ,np.OGRN--<OGRN, varchar(255),>
                       ,np.Terr--<Terr, varchar(255),>
                       ,np.OKATO--<OKATO, varchar(255),>
                       ,np.DateBegin--<DateBegin, datetime,>
                       ,np.DateEnd--<DateEnd, datetime,>
                       ,np.RegisterDate--<RegisterDate, datetime,>
                       ,np.RecipeServedTypes--<RecipeServedTypes, int,>)
					   ,np.[Address]--<Address, varchar(255),>
					   ,(select top 1 r.RajonID from [APS].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
					   ,np.[Number]--<Number, varchar(50),>
					   ,Phone
					   ,np.LicenseNumber
					   ,np.LicenseIssue
					   ,np.LicenseDate
					   ,ISNULL(Dlo, 0)
					   ,np.FiasAddress
					   ,np.PostalCode
					   ,np.FiasHome
					   ,e.EhrRusID
            from #PHARMACYS np
				outer apply (
					select e.EhrRusID 
					from EHR.T_EHR_RUS_MDR308 e
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
				) e
            where not exists
            (
                select *
                from [APS].T_PHARMACY p
                where p.Code = np.ID
            )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            update p
				set  p.DateEnd = @dateTime
				    ,p.RegisterDate = @dateTime
            from [APS].[T_PHARMACY] p
                join #PHARMACY np on np.[ID] = p.Code
        END
        else
        BEGIN
            -- update [APS].T_PHARMACY
            update p
            set p.[Name] = np.[Name]
               ,p.[NameFull] = np.[NameFull]
               ,p.[OGRN] = np.[OGRN]
               ,p.[Terr] = np.[Terr]
               ,p.[OKATO] = np.[OKATO]
               ,p.[DateBegin] = np.[DateBegin]
               ,p.[DateEnd] = np.[DateEnd]
               ,p.[RegisterDate] = np.[RegisterDate]
               ,p.[RecipeServedTypes] = np.[RecipeServedTypes]
			   ,p.[Address] = np.[Address]
			   ,p.[Rajon] = (select top 1 r.RajonID from [APS].T_RAJON r where r.[Code] = np.[Rajon])
			   ,p.[Number] = np.[Number]
			   ,p.Phone = np.Phone
			   ,p.LicenseNumber = np.LicenseNumber
			   ,p.LicenseIssue = np.LicenseIssue
			   ,p.LicenseDate = np.LicenseDate
			   ,p.Dlo = ISNULL(np.Dlo, 0)
			   ,p.FiasAddress = np.FiasAddress
			   ,p.PostalCode = np.PostalCode
			   ,p.FiasHome = np.FiasHome
			   ,p.EhrRusMdr308 = e.EhrRusID
            from [APS].T_PHARMACY p
                join #PHARMACY np on np.ID = p.Code
				outer apply (
					select e.EhrRusID 
					from EHR.T_EHR_RUS_MDR308 e
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
				) e
            where @OperationType = @UPDATE
            
            -- insert [APS].T_PHARMACY
            INSERT INTO [APS].[T_PHARMACY]
                       ([Code] ,[Name] ,[NameFull] ,[OGRN] ,[Terr] ,[OKATO] ,[DateBegin] ,[DateEnd] ,[RegisterDate] ,[RecipeServedTypes], [Address], [Rajon], [Number], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, PostalCode, FiasHome, EhrRusMdr308)
            SELECT
                       np.ID--<Code, int,>
                       ,np.Name--<Name, varchar(255),>
                       ,np.NameFull--<NameFull, varchar(255),>
                       ,np.OGRN--<OGRN, varchar(255),>
                       ,np.Terr--<Terr, varchar(255),>
                       ,np.OKATO--<OKATO, varchar(255),>
                       ,np.DateBegin--<DateBegin, datetime,>
                       ,np.DateEnd--<DateEnd, datetime,>
                       ,np.RegisterDate--<RegisterDate, datetime,>
                       ,np.RecipeServedTypes--<RecipeServedTypes, int,>)
					   ,np.[Address]--<Address, varchar(255),>
					   ,(select top 1 r.RajonID from [APS].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
					   ,np.[Number]--<Number, varchar(50),>
					   ,Phone
					   ,np.LicenseNumber
					   ,np.LicenseIssue
					   ,np.LicenseDate
					   ,ISNULL(Dlo, 0)
					   ,np.FiasAddress
                       ,np.PostalCode
					   ,np.FiasHome
					   ,e.EhrRusID
            from #PHARMACY np
				outer apply (
					select e.EhrRusID 
					from EHR.T_EHR_RUS_MDR308 e
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
				) e
            where 
				@OperationType = @INSERT
				and	not exists
                (
                    select *
                    from [APS].T_PHARMACY p
                    where p.Code = np.ID
                )
                    
        END

        -- генерируем след. номер
        exec [dbo].sp_GenerateIdForEntity 'PHARMACY'
        update ei
			set ei.id = (select MAX(Code) from [APS].T_PHARMACY) + 1
        from T_ENTITY_IDS ei
        where 
			ei.EntName = 'PHARMACY'
            and ei.FieldName = 'PrimaryKey'

        -- Drop tables
        DROP TABLE #PHARMACY
        DROP TABLE #PHARMACYS

    END
END
GO