--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_statenumber_address'
--	)
--	then
--		drop function public.sp_statenumber_address(varchar(8), varchar(8));
--	end if;
--end;
--$$ language plpgsql;

--CREATE or REPLACE FUNCTION public.sp_StateNumber_Address
--(
--	"LpuCode" varchar(8)
--,	"BranchCode" varchar(16)
--)
--RETURNS table (StateNumber int, Street varchar(100), House varchar(1000), Block varchar(1000), StateID UUID, StreetId UUID)
--AS
--$$
--Declare	IdVar int;
--			ExpressionVar varchar(100);
--			Condition text;
--			nPos int;
--			nPos2 int;
--			nRecCount1 int;
--			nRecCount2 int;
--			StateIDVar UUID;
--			Counter int;
--			Total int;
--			AddressIdVar UUID;
--			StreetIdVar UUID;
--			StreetVar varchar(100);
--			HouseVar varchar(1000);
--			SingleHouse varchar(10);
--			BlockVar varchar(1000);
--			SingleBlock varchar(10);
--			curs1 CURSOR FOR 
--				select ts.StateID, lower(ts.Criteria) 
--				from akuz.T_State ts
--				inner join 
--					akuz.T_Division td 
--					on td.DivisionId = ts.Division
--				inner join 
--					akuz.T_Branch tb 
--					on tb.LpuID = td.branch
--				inner join 
--					akuz.T_LPU_MAIN tl 
--					on tl.LpuID = tb.LpuMain
--				where ts.Criteria is not null 
--				and tl.Code = "LpuCode"
--				and tb.BranchCode = "BranchCode";
--			Searched Cursor For
--				select Id, Expression
--				from TmpSearched;
--			CurPosition Cursor For
--				select p.Position 
--				from TmpPosition p
--				where p.Searched = 5
--				order by p.Position;
--			CurPosition2 Cursor For
--				select p.Position
--				 from TmpPosition p
--				 where p.Searched = 2
--				 order by p.Position;
--			Enumeration Cursor For
--				select ta.Id, ta.StateId, ta.StreetId, ta.Street, ta.House, ta.Block
--				from TmpAddress ta
--				where strpos(ta.House, ',') <> 0;
--			Enumeration2 Cursor For
--				select ta.Id, ta.StateId, ta.StreetId, ta.Street, ta.House, ta.Block
--				from TmpAddress ta
--				where strpos(ta.Block, ',') <> 0;
--BEGIN
---- * * * --
----#Searched
--	drop table if exists TmpSearched;
--	create temp table TmpSearched(Id int not null, Expression varchar(100) not null);
--	insert into TmpSearched (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"'; --корпус

--	drop table if exists TmpAddress;
--	create temp table TmpAddress(Id UUID not null, StateId UUID not null, StreetId UUID not null, Street varchar(100) not null, House varchar(1000) null, Block varchar(1000) null);
---- * * * --
--	--Понеслась
--	select count(*)
--	from akuz.T_State t
--	into Total
--	where t.Criteria is not null;

--	Counter := 0;

--	OPEN curs1;--открываем курсор
--	LOOP --начинаем цикл по курсору
--	FETCH curs1 INTO StateIDVar, Condition;
--		IF NOT FOUND THEN EXIT;
--		END IF;
--		Counter := Counter + 1;
--		raise notice '%/%',Counter, Total;
 
--		--Определение позиций ключевых элементов в XML
--		drop table if exists TmpPosition;
--		create temp table TmpPosition(Searched int not null, Position int not null, Position2 int not null);
--		create index IX_Position on TmpPosition USING BTree (Position);		
--		open Searched;
--		Loop
--		Fetch Searched into IdVar, ExpressionVar;
--			IF NOT FOUND THEN EXIT;
--			END IF;
--			nPos := 1;
--			while nPos <> 0 loop
--				nPos := dbo.CharIndex(ExpressionVar, Condition, nPos);
--				if nPos <> 0
--				then
--					nPos2 := nPos + char_length(ExpressionVar);
--					insert into TmpPosition values(IdVar, nPos, nPos2);
--					nPos := nPos2;
--				end if;
--			end loop;
--			Fetch Searched into IdVar, ExpressionVar;
--		end loop;
--		close Searched;
-- -- * * * --
-- --Criterion. Устанавливаем отношение между открывающими и закрывающими тегами
--		select count(*)
--		from TmpPosition p
--		into nRecCount1
--		where p.Searched = 4;

--		select count(*)
--		from TmpPosition p
--		into nRecCount2
--		where p.Searched = 5;

--		if nRecCount1 <> nRecCount2
--		then
--			raise notice '  Не совпадает количество открывающих и закрывающих тегов Criterion (% <> %)' , nRecCount1, nRecCount2;
--		end if;

--		drop table if exists TmpCriterion;
--		create temp table TmpCriterion(Searched int not null, Start int not null, Finish int not null);
--		create index IX_Start on TmpCriterion USING BTree (Start);

--		open CurPosition;
--		Loop
--		Fetch CurPosition into nPos;
--			IF NOT FOUND THEN EXIT;
--			END IF;
--			insert into TmpCriterion
--			select a.Searched, a.Position, nPos
--			from TmpPosition a
--			inner join 
--			(
--				select max(p.Position) as Position
--				from TmpPosition p
--				where p.Searched = 4 
--				and p.Position < nPos
--				and p.Position not in ( select Start from TmpCriterion )
--			) b
--				on a.Position = b.Position;
--			Fetch CurPosition into nPos;
--		end loop;
--		close CurPosition;			
-- -- * * * --
--		--House. Отбираем те ConstOperand, которые относятся к домам
--		drop table if exists TmpHouse;
--		create temp table TmpHouse as 
--			select a.Position2 as Position
--			from TmpPosition a
--			inner join 
--				TmpCriterion b 
--				on a.Position between b.Start and b.Finish
--			inner join 
--				TmpPosition 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, которые относятся к корпусам
--		drop table if exists TmpBlock;
--		create temp table TmpBlock as 
--			select a.Position2 as Position
--			from TmpPosition a
--			inner join 
--				TmpCriterion b 
--				on a.Position between b.Start and b.Finish
--			inner join 
--				TmpPosition 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 if exists TmpCriterion;
-- -- * * * --
--		--Criteria. Устанавливаем отношение между открывающими и закрывающими тегами.
--		select count(*)
--		from TmpPosition p
--		into nRecCount1
--		where p.Searched = 1;

--		select count(*)
--		from TmpPosition p
--		into nRecCount2
--		where p.Searched = 2;

--		if nRecCount1 <> nRecCount2
--		then
--			raise notice '  Не совпадает количество открывающих и закрывающих тегов Criteria (% <> %)', nRecCount1, nRecCount2;
--		end if;

--		drop table if exists TmpCriteria;
--		create temp table TmpCriteria( Searched int not null, Start int not null, Finish int not null);
--		create index IX_Start_Index on TmpCriteria USING BTree (Start);

--		open CurPosition2;
--		Loop
--		Fetch CurPosition2 into nPos;
--			IF NOT FOUND THEN EXIT;
--			END IF;
--			insert into TmpCriteria
--			select a.Searched, a.Position, nPos
--			from TmpPosition a
--			inner join 
--			(
--				select max(p.Position) as Position
--				from TmpPosition p
--				where p.Searched = 1
--				and p.Position < nPos
--				and p.Position not in ( select Start from TmpCriteria )
--			) b
--				on a.Position = b.Position;
--			Fetch CurPosition2 into nPos;
--		end loop;
--		close CurPosition2;
---- * * * --
--		--Устанавливаем отношение между корпусом и его самым верхним AND, через
--		--который может быть установлена его связь с домом
--		drop table if exists TmpBlock_and;
--		create temp table TmpBlock_and as 
--			select a.Position, min(b.Start) as AndStart
--			from TmpBlock a
--			inner join 
--				TmpCriteria b 
--				on a.Position between b.Start and b.Finish
--			where b.Searched = 0
--			and not exists 
--			(
--				select *
--				from 
--				(
--					select *
--					from TmpCriteria p
--					where p.Searched = 1
--					and p.Start not in ( select c.Start from TmpCriteria c where c.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 if exists TmpBlock;	
---- * * * --
--		--Устанавливаем отношение между улицей и её самым верхним AND.
--		--только через такой AND может быть установлена связь улицы с домами
--		drop table if exists TmpStreet;
--		create temp table TmpStreet as 
--			select	a.Position2 as Position
--			,	min(b.Start) as AndStart
--			from TmpPosition a
--			left join 
--				TmpCriteria 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 TmpCriteria c
--					where c.Searched = 1
--					and c.Start not in ( select tc.Start from TmpCriteria tc where tc.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 TmpAddress(Id, StateID, StreetId, Street, House, Block)
--			select	uuid_generate_v1(), StateIDVar, d.StreetID, d.Name
--			,	substring(Condition from b.Position for (dbo.CharIndex('</', Condition, b.Position) - b.Position)) as House
--			,	substring(Condition from e.Position for (dbo.CharIndex('</', Condition, e.Position) - e.Position)) as Block
--			from TmpStreet a
--			inner join 
--				TmpCriteria c 
--				on a.AndStart = c.Start
--			left join 
--				TmpHouse b 
--				on b.Position between c.Start and c.Finish
--			left join 
--			(
--				select a.Position, b.Start, b.Finish
--				from TmpBlock_and a
--				inner join 
--					TmpCriteria b 
--					on a.AndStart = b.Start
--				where b.Searched = 0
--			) e
--				on b.Position between e.Start and e.Finish
--			inner join 
--				akuz.T_Street d
--				on substring(Condition from a.Position for (dbo.CharIndex('"', Condition, a.Position) - a.Position)) = d.StreetID::text
--			where a.AndStart is not null
--			and c.Searched = 0;

--			drop table if exists TmpStreet;
--			drop table if exists TmpHouse;
--			drop table if exists TmpBlock_and;
--			drop table if exists TmpCriteria;
-- -- * * * --
-- 		FETCH curs1 INTO StateIDVar, Condition;
--	 END LOOP;
--	 CLOSE curs1;
--	--Номера домов
--	--Устранение тэгов <![CDATA]> из номеров домов
--	update TmpAddress ta
--	set House = substring(ta.House from 10 for (dbo.charindex(']', ta.House, 10) - 10))
--	where left(ta.House, 9) = '<![cdata[';

--	--Вычленение номеров, записанных как перечисление (через запятую) в операторе IN
--	open Enumeration;
--	Loop
--	Fetch Enumeration into AddressIdVar, StateIdVar, StreetIdVar, StreetVar, HouseVar, BlockVar;
--		IF NOT FOUND THEN EXIT;
--		END IF;
--		nPos := strpos(House, ',');
--		SingleHouse := left(House, nPos - 1);
--		HouseVar := substring(House from nPos + 1 for (char_length(HouseVar) - nPos));
	 
--		update TmpAddress
--		set House = SingleHouse
--		where Id = AddressIdVar;
	 
--		while nPos <> 0 loop
--			nPos := strpos(HouseVar, ',');
--			if nPos = 0
--			then
--				SingleHouse := HouseVar;
--			else 
--				SingleHouse := left(HouseVar, nPos - 1);
--				HouseVar := substring(HouseVar from nPos + 1 for (char_length(HouseVar) - nPos));
--			end if;
	  
--			insert into TmpAddress(Id, StateId, StreetId, Street, House, Block)
--			values (uuid_generate_v1(), StateIdVar, StreetIdVar, StreetVar, SingleHouse, BlockVar);
--		end loop;
--		Fetch Enumeration into AddressIdVar, StateIdVar, StreetIdVar, StreetVar, HouseVar, BlockVar;
--	end loop;
--	close Enumeration;
---- * * * --
--	--Корпуса
--	--Устранение тэгов <![CDATA]> из корпусов
--	update TmpAddress ta
--	set Block = substring( ta.Block from 10 for (dbo.CharIndex(']', ta.Block, 10) - 10))
--	where left(ta.Block, 9) = '<![cdata[';

--	--Вычленение номеров, записанных как перечисление в операторе IN
--	open Enumeration2;
--	Loop
--	Fetch Enumeration2 into AddressIdVar, StateIdVar, StreetIdVar, StreetVar, HouseVar, BlockVar;
--		IF NOT FOUND THEN EXIT;
--		END IF;
--		nPos := strpos(BlockVar, ',');
--		SingleBlock := left(BlockVar, nPos - 1);
--		BlockVar := substring(BlockVar from nPos + 1 for (char_length(BlockVar) - nPos));
	 
--		update TmpAddress
--		set Block = SingleBlock
--		where Id = AddressIdVar;
	 
--		while nPos <> 0 loop
--			nPos := strpos(BlockVar, ',');
--			if nPos = 0
--			then
--				SingleBlock := BlockVar;
--			else 
--				SingleBlock := left(BlockVar, nPos - 1);
--				BlockVar := substring(BlockVar from nPos + 1 for (char_length(BlockVar) - nPos));
--			end if;
	  
--			insert into TmpAddress(Id, StateId, StreetId, Street, House, Block)
--			values (uuid_generate_v1(), StateIdVar, StreetIdVar, StreetVar, HouseVar, SingleBlock);
--		end loop;
--		Fetch Enumeration2 into AddressIdVar, StateIdVar, StreetIdVar, StreetVar, HouseVar, BlockVar;
--	end loop;
--	close Enumeration2;
---- * * * --
--	return query
--	select	distinct
--		ts.StateNumber
--	,	a.Street
--	,	a.House
--	,	a.Block
--	,	ts.StateID
--	,	a.StreetId 
--	from TmpAddress a
--	inner join 
--		akuz.T_STATE ts 
--		on ts.StateID = a.StateID
--	order by ts.StateNumber;
--END;
--$$ LANGUAGE plpgsql;