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

--/****** Object:  StoredProcedure [dbo].[sp_StateNumber_Address]    Script Date: 02/04/2013 18:19:00 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--/*
--Парсит Criteria в AKUZDB.dbo.T_STATE на предмет вычленения адресов (улица - дом - корпус),
--закрепленных за участком.
--Результат: таблица State_Address. Устанавливает отношение между
--номером участка, названием улицы, номерами и корпусами домов.

--(с) г.Екатеринбург, МБУ "Центр Мединком"
--Сергей Александрович Матушкин
--*/
--GO

--CREATE procedure [dbo].[sp_StateNumber_Address]
--	 @lpuCode varchar(8)
--	 ,@branchCode varchar(8)
--as
--set nocount on

--declare @lDebug bit --режим отладки
--	,@Debug_StateId uniqueidentifier

--select @lDebug = 0, @Debug_StateId = '5c9622f8-035b-45eb-960f-4e892fe1352c'

--declare @Id int
--	,@Expression varchar(100)
--	,@Condition varchar(max)
--	,@nPos int
--	,@nPos2 int
--	,@nRecCount1 int
--	,@nRecCount2 int
--	,@StateID uniqueidentifier
--	,@Counter int
--	,@Total int
	
--	,@AddressId uniqueidentifier
--	,@StreetId uniqueidentifier
--	,@Street varchar(100)
--	,@House varchar(1000)
--	,@SingleHouse varchar(10)
--	,@Block varchar(1000)
--	,@SingleBlock varchar(10)

---- * * * --
----#Searched
--if object_id('tempdb..#Searched','U') is not null
--	drop table #Searched

--create table #Searched
--(
--	[Id] int not null
--	,[Expression] varchar(100) not null
--)

--insert into #Searched ([Id],[Expression]) 
--select 0,'<lx:criteria type="and"' union
--select 1,'<lx:criteria' union
--select 2,'</lx:criteria' union
--select 3,'<lx:criterion type="="' union
--select 4,'<lx:criterion' union
--select 5,'</lx:criterion' union
--select 6,'<lx:baseobjectoperand entityindex="515" primarykey="' union --улица
--select 7,'<lx:stepint index="5700004"' union --дом
--select 8,'<lx:constoperand type="string">' union
--select 9,'<lx:criterion type="in"' union
--select 10,'<lx:stepint index="5700005"' --корпус

--if object_id('#Address','U') is not null
--	drop table #Address

--create table #Address
--(
--	[Id] uniqueidentifier not null
--	,[StateId] uniqueidentifier not null
--	,[StreetId] uniqueidentifier not null
--	,[Street] varchar(100) not null
--	,[House] varchar(1000) null
--	,[Block] varchar(1000) null
--)

---- * * * --

----Понеслась
--select @Total = count(*)
--from [AKUZ].[t_state]
--where [Criteria] is not null

--set @Counter = 0

--if @lDebug = 0
--	declare [State] cursor for
--	select [StateID],lower(cast([Criteria] as varchar(max)))
--	from [AKUZ].[t_state] ts
--	join [AKUZ].[t_division] td on (td.[divisionid] = ts.[division])
--	join [AKUZ].[t_branch] tb on (tb.[LpuID] = td.[branch])
--	join [AKUZ].[T_LPU_MAIN] tl on (tl.LpuID = tb.[LpuMain])
--	where [Criteria] is not null and tl.Code = @lpuCode and tb.BranchCode = @branchCode
--else
--	declare [State] cursor for
--	select [StateID],lower(cast([Criteria] as varchar(max)))
--	from [AKUZ].[t_state]
--	where [Criteria] is not null
--		and [StateID] = @Debug_StateId

--open [State]
--fetch next from [State] into @StateID,@Condition
--while @@fetch_status = 0
--begin
--	set @Counter = @Counter + 1
--	print cast(@Counter as varchar)+ '/' +cast(@Total as varchar)
	
--	--Определение позиций ключевых элементов в XML
--	if object_id('tempdb..#Position','U') is not null
--		drop table #Position

--	create table #Position
--	(
--		[Searched] int not null
--		,[Position] int not null
--		,[Position2] int not null
--	)

--	create index Position
--	on #Position (Position)

--	declare [Searched] cursor for
--	select [Id],[Expression]
--	from #Searched

--	open Searched
--	fetch next from [Searched] into @Id,@Expression
--	while @@fetch_status = 0
--	begin
--		set @nPos = 1
--		while @nPos <> 0
--		begin
--			set @nPos = charindex(@Expression,@Condition,@nPos)
--			if @nPos <> 0
--			begin
--				set @nPos2 = @nPos + len(@Expression)
--				insert into #Position values (@Id,@nPos,@nPos2)
--				set @nPos = @nPos2
--			end
--		end
		
--		fetch next from [Searched] into @Id,@Expression
--	end
--	close [Searched]
--	deallocate [Searched]

--	-- * * * --

--	--Criterion. Устанавливаем отношение между открывающими и закрывающими тегами
--	select @nRecCount1 = count(*)
--	from #Position
--	where [Searched] = 4

--	select @nRecCount2 = count(*)
--	from #Position
--	where [Searched] = 5

--	if @nRecCount1 <> @nRecCount2
--	begin
--		print '  Не совпадает количество открывающих и закрывающих тегов Criterion ('
--			+ cast(@nRecCount1 as varchar) + ' <> ' + cast(@nRecCount2 as varchar) + ')'
--	end

--	if object_id('tempdb..#Criterion','U') is not null
--		drop table #Criterion

--	create table #Criterion
--	(
--		[Searched] int not null
--		,[Start] int not null
--		,[Finish] int not null
--	)

--	create index [Start]
--	on #Criterion ([Start])

--	declare [Position] cursor for
--	select [Position]
--	from #Position
--	where [Searched] = 5
--	order by [Position]

--	open [Position]
--	fetch next from [Position] into @nPos
--	while @@fetch_status = 0
--	begin
--		insert into #Criterion
--		select a.[Searched],a.[Position],@nPos
--		from #Position a
--			join (
--				select max([Position]) as [Position]
--				from #Position
--				where [Searched] = 4
--					and [Position] < @nPos
--					and [Position] not in (
--						select [Start]
--						from #Criterion
--						)
--				) b
--				on a.[Position] = b.[Position]
		
--		fetch next from [Position] into @nPos
--	end
--	close [Position]
--	deallocate [Position]

--	-- * * * --

--	--House. Отбираем те ConstOperand, которые относятся к домам
--	if object_id('tempdb..#House','U') is not null
--		drop table #House

--	select a.[Position2] as Position
--	into #House
--	from #Position a
--		join #Criterion b on a.[Position] between b.[Start] and b.[Finish]
--		join #Position c on c.[Position] between b.[Start] and b.[Finish]
--	where a.[Searched] = 8
--		and b.[Searched] in (3,9)
--		and c.[Searched] = 7

--	-- * * * --

--	--Block. Отбираем те ConstOperand, которые относятся к корпусам
--	if object_id('tempdb..#Block','U') is not null
--		drop table #Block

--	select a.[Position2] as Position
--	into #Block
--	from #Position a
--		join #Criterion b on a.[Position] between b.[Start] and b.[Finish]
--		join #Position c on c.[Position] between b.[Start] and b.[Finish]
--	where a.[Searched] = 8
--		and b.[Searched] in (3,9)
--		and c.[Searched] = 10

--	drop table #Criterion

--	-- * * * --

--	--Criteria. Устанавливаем отношение между открывающими и закрывающими тегами.
--	select @nRecCount1 = count(*)
--	from #Position
--	where [Searched] = 1

--	select @nRecCount2 = count(*)
--	from #Position
--	where [Searched] = 2

--	if @nRecCount1 <> @nRecCount2
--	begin
--		print '  Не совпадает количество открывающих и закрывающих тегов Criteria ('
--			+ cast(@nRecCount1 as varchar) + ' <> ' + cast(@nRecCount2 as varchar) + ')'
--	end

--	if object_id('tempdb..#Criteria','U') is not null
--		drop table #Criteria

--	create table #Criteria
--	(
--		[Searched] int not null
--		,[Start] int not null
--		,[Finish] int not null
--	)

--	create index [Start]
--	on #Criteria ([Start])

--	declare [Position] cursor for
--	select [Position]
--	from #Position
--	where [Searched] = 2
--	order by [Position]

--	open [Position]
--	fetch next from [Position] into @nPos
--	while @@fetch_status = 0
--	begin
--		insert into #Criteria
--		select a.[Searched],a.[Position],@nPos
--		from #Position a
--			join (
--				select max([Position]) as [Position]
--				from #Position
--				where [Searched] = 1
--					and [Position] < @nPos
--					and [Position] not in (
--						select [Start]
--						from #Criteria
--						)
--				) b
--				on a.[Position] = b.[Position]
		
--		fetch next from [Position] into @nPos
--	end
--	close [Position]
--	deallocate [Position]
	
--	-- * * * --
	
--	--Устанавливаем отношение между корпусом и его самым верхним AND, через
--	--который может быть установлена его связь с домом
--	if object_id('tempdb..#Block_and','U') is not null
--		drop table #Block_and
	
--	select a.[Position]
--		,min(b.[Start]) as [AndStart]
--	into #Block_and
--	from #Block a
--		join #Criteria b on a.[Position] between b.[Start] and b.[Finish]
--	where b.[Searched] = 0
--		and not exists (
--			select *
--			from (
--				select *
--				from #Criteria
--				where [Searched] = 1
--					and [Start] not in (
--						select [Start]
--						from #Criteria
--						where [Searched] = 0
--						)
--				) c
--			where a.[Position] between c.[Start] and c.[Finish]
--				and c.[Start] between b.[Start] and b.[Finish]
--			)
--	group by a.[Position]
	
--	drop table #Block

--	-- * * * --
	
--	--Устанавливаем отношение между улицей и её самым верхним AND.
--	--только через такой AND может быть установлена связь улицы с домами
--	if object_id('tempdb..#Street','U') is not null
--		drop table #Street
	
--	select a.[Position2] as Position
--		,min(b.[Start]) as [AndStart]
--	into #Street
--	from #Position a
--		left join #Criteria b on a.[Position] between b.[Start] and b.[Finish]
--	where a.[Searched] = 6
--		and b.[Searched] = 0
--		and not exists (
--			select *
--			from (
--				select *
--				from #Criteria
--				where [Searched] = 1
--					and [Start] not in (
--						select [Start]
--						from #Criteria
--						where [Searched] = 0
--						)
--				) c
--			where a.[Position] between c.[Start] and c.[Finish]
--				and c.[Start] between b.[Start] and b.[Finish]
--			)
--	group by a.[Position2]

--	-- * * * --

--	--Устанавливаем отношение между улицей,домом и корпусом через соответсвующие AND
	
--	insert into #Address
--	(
--		[Id]
--		,[StateID]
--		,[StreetId]
--		,[Street]
--		,[House]
--		,[Block]
--	)
--	select newid()
--		,@StateID
--		,d.[StreetID]
--		,d.[Name]
--		,substring(@Condition,b.[Position],
--		charindex('</',@Condition,b.[Position]) - b.Position) as [House]
--		,substring(@Condition,e.[Position],
--		charindex('</',@Condition,e.[Position]) - e.Position) as [Block]
--	from #Street a
--		join #Criteria c on a.[AndStart] = c.[Start]
--		left join #House b on b.[Position] between c.[Start] and c.[Finish]
--		left join (
--			select a.[Position],b.[Start],b.[Finish]
--			from #Block_and a
--				join #Criteria b on a.[AndStart] = b.[Start]
--			where b.[Searched]=0
--			) e
--			on b.[Position] between e.[Start] and e.[Finish]
--		join [AKUZ].[t_street] d
--			on substring(@Condition,a.[Position]
--			,charindex('"',@Condition,a.[Position]) - a.[Position]) = d.[StreetID]
--	where a.[AndStart] is not null
--		and c.[Searched] = 0

--	drop table #Street
--	drop table #House
--	drop table #Block_and
--	drop table #Criteria
	
--	-- * * * --
	
--	fetch next from [State] into @StateID,@Condition
--end
--close [State]
--deallocate [State]

---- * * * --

----Номера домов
----Устранение тэгов <![CDATA]> из номеров домов
--update #Address
--set [House] = substring([House],10,charindex(']',[House],10) - 10)
--where left([House],9) = '<![cdata['

----Вычленение номеров, записанных как перечисление (через запятую) в операторе IN
--declare [Enumeration] cursor for
--select [Id],[StateId],[StreetId],[Street],[House],[Block]
--from #Address
--where charindex(',',[House]) <> 0

--open [Enumeration]
--fetch next from [Enumeration] into @AddressId,@StateId,@StreetId,@Street,@House,@Block
--while @@fetch_status = 0
--begin
--	set @nPos = charindex(',',@House)
--	set @SingleHouse = left(@House,@nPos - 1)
--	set @House = substring(@House,@nPos + 1,len(@House) - @nPos)
	
--	update #Address
--	set [House] = @SingleHouse
--	where [Id] = @AddressId
	
--	while @nPos <> 0
--	begin
--		set @nPos = charindex(',',@House)
--		if @nPos = 0
--			set @SingleHouse = @House
--		else begin
--			set @SingleHouse = left(@House,@nPos - 1)
--			set @House = substring(@House,@nPos + 1,len(@House) - @nPos)
--		end
		
--		insert into #Address
--		(
--			[Id]
--			,[StateId]
--			,[StreetId]
--			,[Street]
--			,[House]
--			,[Block]
--		)
--		values (
--			newid()
--			,@StateId
--			,@StreetId
--			,@Street
--			,@SingleHouse
--			,@Block
--			)
--	end
	
--	fetch next from [Enumeration] into @AddressId,@StateId,@StreetId,@Street,@House,@Block
--end
--close [Enumeration]
--deallocate [Enumeration]

---- * * * --

----Корпуса
----Устранение тэгов <![CDATA]> из корпусов
--update #Address
--set [Block] = substring([Block],10,charindex(']',[Block],10) - 10)
--where left([Block],9) = '<![cdata['

----Вычленение номеров, записанных как перечисление в операторе IN
--declare [Enumeration] cursor for
--select [Id],[StateId],[StreetId],[Street],[House],[Block]
--from #Address
--where charindex(',',[Block]) <> 0

--open [Enumeration]
--fetch next from [Enumeration] into @AddressId,@StateId,@StreetId,@Street,@House,@Block
--while @@fetch_status = 0
--begin
--	set @nPos = charindex(',',@Block)
--	set @SingleBlock = left(@Block,@nPos - 1)
--	set @Block = substring(@Block,@nPos + 1,len(@Block) - @nPos)
	
--	update #Address
--	set [Block] = @SingleBlock
--	where [Id] = @AddressId
	
--	while @nPos <> 0
--	begin
--		set @nPos = charindex(',',@Block)
--		if @nPos = 0
--			set @SingleBlock = @Block
--		else begin
--			set @SingleBlock = left(@Block,@nPos - 1)
--			set @Block = substring(@Block,@nPos + 1,len(@Block) - @nPos)
--		end
		
--		insert into #Address
--		(
--			[Id]
--			,[StateId]
--			,[StreetId]
--			,[Street]
--			,[House]
--			,[Block]
--		)
--		values (
--			newid()
--			,@StateId
--			,@StreetId
--			,@Street
--			,@House
--			,@SingleBlock
--			)
--	end
	
--	fetch next from [Enumeration] into @AddressId,@StateId,@StreetId,@Street,@House,@Block
--end
--close [Enumeration]
--deallocate [Enumeration]

---- * * * --
--select distinct
--	ts.[StateNumber]
--	,[Street]
--	,[House]
--	,[Block]
--	,ts.[StateID]
--	,[StreetId] 
--from #Address a
--	join [AKUZ].[T_STATE] ts on (ts.StateID = a.StateID)
--order by ts.StateNumber
--GO