--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_ContainsHouse]') and xtype in (N'FN', N'IF', N'TF'))
--drop function [dbo].[fn_ContainsHouse]
--GO
---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 19.11.2014
---- Description:	Проверка на содержание указанного дома(корпуса,квартиры)
---- =============================================
--create function [dbo].[fn_ContainsHouse](@StateAddressHouseID uniqueidentifier, @House varchar(128), @Block varchar(128), @Flat varchar(128))
--returns bit
--as
--begin
--	declare @hs varchar(128), @bs varchar(128), @fs varchar(128)
--	select @hs = replace(ltrim(rtrim(sah.House)), CHAR(9), ''), @bs = replace(ltrim(rtrim(sah.Block)), CHAR(9), ''), @fs = replace(ltrim(rtrim(sah.Flat)), CHAR(9), '')
--	from [AKUZ].T_STATE_ADDRESS_HOUSE sah
--	where sah.StateAddressHouseID = @StateAddressHouseID

--    if @House IS NULL OR @hs IS NULL
--    begin
--        if @bs IS NOT NULL
--        begin
--            if @Block IS NULL OR @bs IS NULL
--            begin
--                if @fs IS NOT NULL
--                begin
--					return [dbo].[fn_CheckAddress] (@Flat, @fs);
--                end
--                return 1;
--            end

--			if @fs IS NULL
--			begin
--				return [dbo].[fn_CheckAddress] (@Block, @bs);
--			end
--			else
--			begin
--                    if [dbo].[fn_CheckAddress] (@Block, @bs) = 1
--                    begin
--						return [dbo].[fn_CheckAddress] (@Flat, @fs);
--                    end
--			end
--            return 0;
--        end
--        else if @fs IS NOT NULL
--        begin
--			return [dbo].[fn_CheckAddress] (@Flat, @fs);
--        end
--        return 1;
--    end

--	if @hs IS NOT NULL
--    begin
--        if @bs IS NULL AND @fs IS NULL
--        begin
--			return [dbo].[fn_CheckAddress] (@House, @hs);
--        end
--        else
--        begin
--            if [dbo].[fn_CheckAddress] (@House, @hs) = 1
--            begin
--                if @bs IS NOT NULL
--                begin
--					if @Block IS NULL OR @bs IS NULL
--					begin
--						if @fs IS NOT NULL
--						begin
--							return [dbo].[fn_CheckAddress] (@Flat, @fs);
--						end
--						return 1;
--					end

--					if @fs IS NULL
--					begin
--						return [dbo].[fn_CheckAddress] (@Block, @bs);
--					end
--					else
--					begin
--							if [dbo].[fn_CheckAddress] (@Block, @bs) = 1
--							begin
--								return [dbo].[fn_CheckAddress] (@Flat, @fs);
--							end
--					end
--					return 0;
--                end
--                else if @fs IS NOT NULL
--                begin
--					return [dbo].[fn_CheckAddress] (@Flat, @fs);
--                end
--            end
--        end
--    end

--	return 0;
--end	
--GO

--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_CheckAddress]') and xtype in (N'FN', N'IF', N'TF'))
--drop function [dbo].[fn_CheckAddress]
--GO

---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 19.11.2014
---- Description:	Проверка вхождения номера дома в указанные адреса (через запятую или диапазон)
----				Вызов из [dbo].fn_ContainsHouse()
---- =============================================
--create function [dbo].fn_CheckAddress(@h varchar(128), @hs varchar(128))
--returns bit
--as
--begin

-- if @h is NULL SET @h = ''
-- if @hs is NULL SET @hs = ''

-- Declare @idx Int, @prev Int, @split varchar(128), @res bit
-- Select @idx = charindex(',', @hs), @prev = 0
 
-- While 1=1
-- Begin

--	if @idx > 0
--	 set @split = substring(@hs, @prev, @idx - @prev)
--	else
--	 set @split = substring(@hs, @prev, len(@hs) + 1 - @prev) 

--  -- поиск включения и обработка ч/н
--  if substring(@split, 1, 1) = 'н'
--	  begin
--	   if substring(@split, 2, 1) = '(' AND substring(@split, len(@split), 1) = ')'
--			AND ISNUMERIC(@h) = 1 -- явл-ся числом (исключаем 5а,7б и т.д.)
--			AND [dbo].fn_CheckHouse(@h, substring(@split, 3, len(@split) - 3)) = 1 -- вход в диапазон
--			AND @h%2 = 1 -- нечетность
--		return 1;
--	  end
--  else if substring(@split, 1, 1) = 'ч'
--	  begin
--	   if substring(@split, 2, 1) = '(' AND substring(@split, len(@split), 1) = ')'
--			AND ISNUMERIC(@h) = 1 -- явл-ся числом (исключаем 5а,7б и т.д.)
--			AND [dbo].fn_CheckHouse(@h, substring(@split, 3, len(@split) - 3)) = 1 -- вход в диапазон
--			AND @h%2 = 0 -- четность
--		return 1;
--	  end
--  else
--	  if [dbo].fn_CheckHouse(@h, @split) = 1 
--		return 1;

--  if @idx = 0
--   break;

--  -- переход к след.строке
--  Set @prev = @idx + 1
--  Set @idx = charindex(',', @hs, @prev)
-- End

-- return 0;
--end
--GO

--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_CheckHouse]') and xtype in (N'FN', N'IF', N'TF'))
--drop function [dbo].[fn_CheckHouse]
--GO

---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 19.11.2014
---- Description:	Проверка совпадения номера дома или вхождения в диапазон.
----				Вызов из [dbo].fn_CheckAddress()
---- =============================================
--create function [dbo].fn_CheckHouse(@h varchar(128), @hs varchar(128))
--returns bit
--as
--begin
-- Declare @idx Int, @prev Int, @from varchar(128), @to varchar(128)
-- Select @idx = charindex('-', @hs), @prev = 0
-- if @idx > 0
-- begin
--	 set @from = substring(@hs, @prev, @idx - @prev)
--	 Set @prev = @idx + 1
--	 set @to = substring(@hs, @idx + 1, len(@hs) + 1 - @prev)
--	 if ISNUMERIC(@h) = 1
--	  return (case when cast(@from as int) <= cast(@h as int) and cast(@h as int) <= cast(@to as int) then 1 else 0 end);
--	 else
--	  return (case when @from <= @h and @h <= @to then 1 else 0 end);
-- end
-- set @to = substring(@hs, @prev, len(@hs) + 1 - @prev)
-- return (case when @to = @h then 1 else 0 end);
--end
