ALTER PROCEDURE [dbo].[advance_search]
@zip_code varchar(10) = null,
@bedrooms float = null,
@bathrooms float = null,
@price_min money = null,
@price_max money = null,
@built int = null,
@sq_ft int = null,
@aptType_id int = null,
@minLong real = null,
@maxLong real = null,
@minLat real = null,
@maxLat real = null,
@amenities varchar(100) = null,
@amenityCnt int = null,
@longitudes varchar(8000) = null,
@latitudes varchar(8000) = null,
@filter varchar(1000) = null,
@pageNum int = 1,
@pageSize smallint = 50,
@wd bit = 1
, @High_Altitude varchar(50)= 'false
CommissionAmount, @Search_Para
, @Commission_Amount varchar(50)= null
, @Search_Para varchar(50) = null
AS
begin
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin Transaction
DECLARE @firstRecord int, @lastRecord int
SET @firstRecord = (@pageNum - 1) * @pageSize + 1
SET @lastRecord = (@pageNum * @pageSize)
declare @exec varchar(8000)
set @exec = 'SELECT DISTINCT complexes_active.UID,CommissionAmount,complexes_active.Property_Name,
complexes_active.Street_Address_1,complexes_active.City,complexes_active.metro_area,Longitude,Latitude,
Survey_Date,(SELECT COUNT(*) FROM apartment_graphics with (updlock)
WHERE apartment_id=complexes_active.uid AND type = ''.JPG'') AS gr, promoted,property_status,
State, Zip_Code, Telephone_Number'
If @amenities is not null
set @exec = @exec + ',(select count(*) from ACAmenities with (updlock) where ac_uid=complexes_active.UID and Amenity_id in (' + @amenities + ')) as foundAmens'
set @exec = @exec + ' into #tPaging FROM complexes_active with (updlock) INNER JOIN apartment_units ON apartment_units.AC_UID=complexes_active.UID'
If (@Search_Para is not null)
set @exec = @exec + ' Inner Join submarkets On submarkets.smrkt_Id = complexes_active.submarketid '
set @exec = @exec + ' WHERE 1=1 '
If @ManagementID is not null
set @exec = @exec + ' AND Management_UID=''' + cast(@ManagementID as varchar(6)) + ''''
else
If @city is not null
set @exec = @exec + ' AND City=''' + @city + ''''
If @Metro_Area is not null
set @exec = @exec + ' AND Metro_Area=''' + @Metro_Area + ''''
If @city_region is not null
set @exec = @exec + ' AND City_Region in (' + @city_region + ')'
If @zip_code is not null
set @exec = @exec + ' AND zip_code LIKE ''' + @zip_code + '%'''
If @Property_Name is not null
Begin
set @exec = @exec + ' AND ' + dbo.f_PropertyNames(@Property_Name)
--set @exec= @exec +' or '+ dbo.f_FKANames(@Property_Name)
End
If @Street_Address is not null
set @exec = @exec + ' AND Street_Address_1 like ''%' + @Street_Address + '%'''
If @built is not null
set @exec = @exec + ' AND Year_Built>=''' + CAST(@built as varchar(4)) + ''''
If @minLong is not null and @maxLong is not null and @minLat is not null and @maxLat is not null
set @exec = @exec + ' AND Longitude between ' + CAST(@minLong as varchar(12)) + ' and ' + CAST(@maxLong as varchar(12)) + ' and Latitude between ' + CAST(@minLat as varchar(12)) + ' and ' + CAST(@maxLat as varchar(12))
If @price_max is not null or @price_min is not null or @bedrooms is not null or @bathrooms is not null or @sq_ft is not null or @aptType_id is not null or @wd = 1
set @exec = @exec + ' AND PFP_DELETED = 0'
If @aptType_id is not null
set @exec = @exec + ' AND PFP_CC_ID_UNITTYPE=' + CAST(@aptType_id as varchar(10))
If @price_min is not null
set @exec = @exec + ' AND PFP_MarketRentMin>' + CAST(@price_min as varchar(10))
If @price_max is not null
set @exec = @exec + ' AND PFP_MarketRentMin<' + CAST(@price_max as varchar(10))
If @bedrooms is not null
set @exec = @exec + ' AND PFP_NumBed=' + CAST(@bedrooms as varchar(3))
If @bathrooms is not null
set @exec = @exec + ' AND PFP_NumBath=' + CAST(@bathrooms as varchar(3))
If @sq_ft is not null
set @exec = @exec + ' and PFP_SqFt>=' + cast(@sq_ft as varchar(5))
if (@Commission_Amount is not null)
set @exec = @exec + ' and CommissionAmount >= '+ @Commission_Amount -- added by Tarveen to make filter for Commission_Amount
If (lower(@High_Altitude) = 'true') -- added by Tarveen to make filter for High_Rise
set @exec = @exec + ' and High_Altitude = 1'
If (@Search_Para is not null) -- added by Tarveen to make filter for Galleria and Midtown
set @exec = @exec + ' and submarkets.smrkt_name = '''+ @Search_Para + ''''
If @amenities is not null
set @exec = @exec + ' AND 1<=(select count(*) from ACAmenities with(updlock)
where ac_uid=complexes_active.UID and Amenity_id in (' + @amenities + '))'
If @filter is not null
set @exec = @exec + @filter
If @amenities is not null
set @exec = @exec + ' order by foundAmens desc, gr desc, CommissionAmount desc, Property_Name'
else
set @exec = @exec + ' order by gr desc, CommissionAmount desc, Property_Name'
If @amenityCnt is not null and @amenityCnt>0
begin
SET @exec = @exec + '; SELECT COUNT(*) FROM #tPaging with(updlock) where foundAmens=' + CAST(@amenityCnt as varchar(3))
SET @exec = @exec + '; SELECT * FROM #tPaging with(updlock) where foundAmens=' + CAST(@amenityCnt as varchar(3))
end
else
begin
SET @exec = @exec + '; SELECT COUNT(*) FROM #tPaging with(updlock)'
SET @exec = @exec + '; SELECT * FROM #tPaging with(updlock)'
end
print @exec
exec (@exec)
commit transaction
if(@@error > 0)
rollback
end
No comments:
Post a Comment