Database

ALTER   PROCEDURE [sde].[PROPERTY_LIST]

	@ACTIVE_USER_ID 		INTEGER	=	0,
	@PARCEL_NO 			VARCHAR(50)	=	'', 
	@BOUNDARY_AREA 		VARCHAR(40)	=	'',
	@NUM_KEY 			VARCHAR(10)	=	'',
	@LIS_KEY 			VARCHAR(50)	=	'',
	@OWNER_NAME		VARCHAR(500)	=	'',
	@PARCEL_PORTION_NO 	VARCHAR(10)	=	'',
	@CELL_NO 			VARCHAR(50)	=	'', 
	@ATTRIB_CODE 		VARCHAR(12)	=	'',
	@EXTENSION 			INTEGER	=	0, 
	@FUNC_KEY 			VARCHAR(30)	=	'',
	@PROVINCE_ID 		INTEGER	=	0, 
	@STREET_NO 			VARCHAR(500)	=	'', 
	@STREET_NAME 		VARCHAR(500)	=	'',
	@ID 				INTEGER 	=	0
AS
/*-------------MODIFIED BY SIVU 2007/05/30-----------
	ADDED [DESCRIPTION] COLUMN IN THE QUERY
*/
	SET @PARCEL_NO 		= ISNULL(@PARCEL_NO,'')
	SET @BOUNDARY_AREA 	= ISNULL(@BOUNDARY_AREA,'')
	SET @LIS_KEY 		= ISNULL(@LIS_KEY,'')
	SET @OWNER_NAME 		= ISNULL(@OWNER_NAME,'')
	SET @PARCEL_PORTION_NO 	= ISNULL(@PARCEL_PORTION_NO,'')
	SET @CELL_NO 		= ISNULL(@CELL_NO,'')
	SET @ATTRIB_CODE 		= ISNULL(@ATTRIB_CODE,'')
	SET @FUNC_KEY 		= ISNULL(@FUNC_KEY,'')
	SET @STREET_NO 		= ISNULL(@STREET_NO,'')
	SET @STREET_NAME 		= ISNULL(@STREET_NAME,'')
	SET @EXTENSION		= ISNULL(@EXTENSION, 0)

	IF @ACTIVE_USER_ID <> 0
	BEGIN
		IF @ID = 0
		BEGIN
			DECLARE @strSQL NVARCHAR(4000)
			
			SET @strSQL = 
				'SELECT DISTINCT TOP 200 PARCEL_PORTION_NO,
				PROPERTY_ID, PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, EXTENSION, FUNC_KEY, ISNULL(P.NOTIFICATION_EXIST, ''FALSE'') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, ''FALSE'') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM, 
				COMPL_BOUNDARY_AREA,
				CASE (CASE CHARINDEX(''/'', PARCEL_PORTION_NO)  
					WHEN 0 THEN LEN(LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO,''''))))
					ELSE LEN(LTRIM(RTRIM(ISNULL( SUBSTRING(PARCEL_PORTION_NO,1,CHARINDEX(''/'', PARCEL_PORTION_NO)-1), ''''))))
					END) 
					WHEN 1 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + ''  '' +
						    CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
					WHEN 2 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + '' '' +
						    CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
					ELSE CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + 
					     CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
				END AS COMPL_PARCEL_PORTION, PROP_DESCRIPTION AS [DESCRIPTION], 
				CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) +
				CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) AS COMPL_PARCEL_PORTION_OLD,
				NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
				TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
				EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
				PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME, 
				SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE, 
				(
					SELECT TOP 1 C.PROP_CATEGORY_DESCR
					FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
					INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
						  ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
					WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
				) AS PROP_CATEGORY_DESCR
				FROM sde.PROPERTY_SUMMARY P
				WHERE 1=1 --P.ARCHIVE_DATE IS NULL
				'
-- PROP_CATEGORY_DESCR added above by Jacob Jackson 200708 to include category as defined by new legislation

			IF @EXTENSION <> 0
			BEGIN				SET @strSQL = @strSQL  + ' AND P.EXTENSION = ' +  CONVERT(VARCHAR, @EXTENSION)
			END

			IF @BOUNDARY_AREA  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.BOUNDARY_AREA LIKE ''' + @BOUNDARY_AREA + ''''
			END

			IF @OWNER_NAME  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.OWN_NAME LIKE ''' + @OWNER_NAME + ''''
			END

			IF @PARCEL_NO <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PARCEL_NO LIKE ''%' + @PARCEL_NO + '%'''
			END

			IF @LIS_KEY  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.LIS_KEY LIKE ''' + @LIS_KEY + '%'''
			END

			IF @PARCEL_PORTION_NO  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PARCEL_PORTION_NO LIKE ''' + @PARCEL_PORTION_NO + ''''
			END

			IF @CELL_NO <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.CELL_NO = ' +  @CELL_NO
			END

			IF @ATTRIB_CODE = '' OR @ATTRIB_CODE = '__________'
			BEGIN
				SET @strSQL = @strSQL + ' AND (P.ATTRIB_CODE IS NULL OR P.ATTRIB_CODE LIKE ''%'') '
			END
			ELSE
			BEGIN
				SET @strSQL = @strSQL +  ' AND ISNULL(P.ATTRIB_CODE, '''') LIKE '''+  @ATTRIB_CODE + '%'' '
			END

			IF @FUNC_KEY <> '' 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.FUNC_KEY LIKE ''' + CONVERT(VARCHAR, @FUNC_KEY) + ''''
			END

/*			IF @STREET_NO  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%'''
			END

			IF @STREET_NAME  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
			END
*/
			IF @STREET_NO  <> ''
			BEGIN
				SET @strSQL = @strSQL  + '   AND (P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%'' 
								OR P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NO) + '%'' ) '
			END

			IF @STREET_NAME  <> ''
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
			END

			IF @PROVINCE_ID <> 0 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.PROVINCE_ID = ' + CONVERT(VARCHAR, @PROVINCE_ID)
			END
	
	
			/* CURRENT QUICK FIX FOR GUEST & FINANCE USERS TO PREVENT THEM FROM SEEING PROPERTIES WITHOUT CURRENT_VALUES. TO BE REMOVED AT A LATER DATE*/
			DECLARE @IS_GUEST_FINANCE INTEGER
			SET @IS_GUEST_FINANCE =	(
							SELECT	CASE WHEN LOGIN_NAME IN ('GUEST', 'FINANCE')
									THEN 1
									ELSE 0
									END 
							FROM		SDE.USER_INFO
							WHERE	[USER_ID] = @ACTIVE_USER_ID
							)

			IF ISNULL(@IS_GUEST_FINANCE, 0) <> 0 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.CURRENT_VALUES IS NOT NULL '
			END
	

			SET @strSQL = @strSQL + ' AND P.ARCHIVE_DATE IS NULL ' + ' ORDER BY	P.COMPL_BOUNDARY_AREA, P.COMPL_PARCEL_PORTION, P.PROPERTY_KEY'

			EXECUTE SP_EXECUTESQL @strSQL

		END
		ELSE -- SPECIFIC RECORD
		BEGIN	
					SELECT 	PP.*, 
					ISNULL(P.NOTIFICATION_EXIST, 'FALSE') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, 'FALSE') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM, 
					PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, FUNC_KEY, PROP_DESCRIPTION AS [DESCRIPTION], 
					EXTENSION, NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
					TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, IMPROVEMENTS_VALUE,  ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
					EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
					PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME, 
					SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE, 
					PURCHASE_DATE, PURCHASE_PRICE, LAST_SUPPL_NO, OLD_SUPPL_NO, VALUES_EFFECTIVE, ACTIVE_VAL_PROCESSED_DATE, 

					(
						SELECT TOP 1 C.PROP_CATEGORY_DESCR
						FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
					
						INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
							  ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
						WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
					) AS PROP_CATEGORY_DESCR


			FROM    	sde.PROPERTY_SUMMARY P
			INNER JOIN 	SDE.VW_PROPERTY_PROCESSING_PENDING PP ON P.PROPERTY_ID = PP.PROPERTY_ID
			WHERE 	P.PROPERTY_ID = @ID

		END
	END
	ELSE
	BEGIN
		PRINT 'PLEASE SPECIFY AN ACTIVE_USER_ID'
	END