-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 19.11.2014
-- Description:	Проверка на содержание указанного дома(корпуса,квартиры)
-- =============================================
-- DROP FUNCTION fn_containshouse(uuid, character varying, character varying, character varying);
DROP FUNCTION IF EXISTS fn_containshouse;
DROP FUNCTION IF EXISTS dbo.fn_containshouse;
CREATE FUNCTION dbo.fn_containshouse
(
	"StateAddressHouseID" uuid
,	"House" varchar(128)
,	"Block" varchar(128)
,	"Flat" varchar(128)
)
RETURNS boolean 
AS
$BODY$
DECLARE	
	_hs varchar(128);
	_bs varchar(128);
	_fs varchar(128);
Begin
	select into 
		_hs, _bs, _fs 
		sah.House, sah.Block, sah.Flat
	from akuz.T_STATE_ADDRESS_HOUSE sah
	where sah.StateAddressHouseID = "StateAddressHouseID";

	if "House" IS NULL OR _hs IS NULL
	then
		if _bs IS NOT NULL
		then
			if "Block" IS NULL OR _bs IS NULL
			then
				if _fs IS NOT NULL
				then
					return dbo.fn_CheckAddress("Flat", _fs);
				end if;
				return '1';
			end if;
			if _fs IS NULL
			then
				return dbo.fn_CheckAddress("Block", _bs);
			else
				if dbo.fn_CheckAddress("Block", _bs) = '1'
				then
					return dbo.fn_CheckAddress("Flat", _fs);
				end if;
			end if;
			return '0';
		elsif _fs IS NOT NULL
		then
			return dbo.fn_CheckAddress("Flat", _fs);
		end if;
		return '1';
	end if;
	if _hs IS NOT NULL
	then
		if _bs IS NULL AND _fs IS NULL
		then
			return dbo.fn_CheckAddress("House", _hs);
		else
			if dbo.fn_CheckAddress("House", _hs) = '1'
			then
				if _bs IS NOT NULL
				then
					if "Block" IS NULL OR _bs IS NULL
					then
						if _fs IS NOT NULL
						then
							return dbo.fn_CheckAddress("Flat", _fs);
						end if;
						return '1';
					end if;
					if _fs IS NULL
					then
						return dbo.fn_CheckAddress("Block", _bs);
					else
						if dbo.fn_CheckAddress("Block", _bs) = '1'
						then
							return dbo.fn_CheckAddress("Flat", _fs);
						end if;
					end if;
					return '0';
				elsif _fs IS NOT NULL
				then
					return dbo.fn_CheckAddress("Flat", _fs);
				end if;
			end if;
		end if;
	end if;
	return '0';
END $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;
ALTER FUNCTION dbo.fn_containshouse(uuid, character varying, character varying, character varying)
  OWNER TO postgres;