ALTER
PROCEDURE [dbo].[ip_ListingSearch]
@State varchar(200),
@SalesRegionID int ,
@DistrictID int ,
@SuburbID varchar(200),
@bedrooms varchar(20),
@bathrooms varchar(20),
@carspaces varchar(20),
@EER decimal ,
@PropertyType varchar(200) ,
@DevelopmentName varchar(200),
@LandSize int ,
@HouseSize int ,
@ListingCategoryCodeID int ,
@ListingTypeCodeID int ,
@MinPrice int ,
@MaxPrice int ,
@ListingStartDate datetime ,
@ListingEndDate datetime ,
@OrderBy varchar(50)
,
@StartRow int
,
@EndRow int
,
@AdvanceQuery varchar(max)
AS
SET
NOCOUNT
ON
DECLARE @rowTotal INT
--bedrooms
DECLARE @bedroomsSign varchar(1)
DECLARE @bedroomCount int
SET @bedroomsSign =
'='
SET @bedroomCount = 0
IF @bedrooms <>
''
BEGIN
SET @bedroomsSign =
SUBSTRING(@bedrooms,0,2)
SET @bedroomCount =
CAST(REPLACE(@bedrooms,'+','')
AS
INT)
END
--bathrooms
DECLARE @bathroomsSign varchar(1)
DECLARE @bathroomCount int
SET @bathroomsSign =
'='
SET @bathroomCount = 0
IF @bathrooms <>
''
BEGIN
SET @bathroomsSign =
SUBSTRING(@bathrooms,0,2)
SET @bathroomCount =
CAST(REPLACE(@bathrooms,'+','')
AS
INT)
END
--carspaces
DECLARE @carspacesSign varchar(1)
DECLARE @carspaceCount int
SET @carspacesSign =
'='
SET @carspaceCount = 0
IF @carspaces <>''
BEGIN
SET @carspacesSign =
SUBSTRING(@carspaces,0,2)
SET @carspaceCount =
CAST(REPLACE(@carspaces,'+','')
AS
INT)
END
DECLARE @AddressType int
SELECT @AddressType = tblCode.CodeID FROM tblCodeHeader INNER
JOIN
dbo.tblCode ON dbo.tblCode.CodeHeaderID = dbo.tblCodeHeader.CodeHeaderID
WHERE
(dbo.tblCodeHeader.CodeTable =
'AddressTypeProperty')
AND
(dbo.tblCode.CodeValue =
'Location')
DECLARE @ListingParticipantTyepe int
SELECT @ListingParticipantTyepe = dbo.tblCode.CodeID
FROM dbo.tblCodeHeader INNER
JOIN
dbo.tblCode ON dbo.tblCode.CodeHeaderID = dbo.tblCodeHeader.CodeHeaderID
WHERE
(dbo.tblCodeHeader.CodeTable =
'ListingParticipantType')
AND
(dbo.tblCode.CodeValue =
'LISTAGENT')
SELECT
DISTINCT
dbo.tblSLListing.ListingID, dbo.tblProperty.PropertyID, dbo.tblProperty.PropertyState, dbo.tblSLDistrict.SalesRegionID, dbo.tblSLDistrict.DistrictID,
dbo.tblSuburb.SuburbID, dbo.tblSuburb.PostCode, dbo.tblSLListing.Price, dbo.tblSLListing.ListingDate, dbo.tblSLListing.roomCount, dbo.tblSLListing.bathRoomCount,
dbo.tblSLListing.carSpaceCount, dbo.tblProperty.PropertyTypeCodeID AS PropertyType, dbo.tblSLListing.ObjectStateID, dbo.tblProperty.BlockNumber,
dbo.tblProperty.SectionNumber, dbo.tblProperty.UnitNumber,
ISNULL(dbo.tblAddress.StreetName,
'Address not found')
AS
Address, dbo.tblAddress.Suburb,
dbo.tblProperty.BlockSize, dbo.tblSLListing.EER, dbo.tblSLListing.Size AS HouseSize, dbo.tblSLListingLegalentity.LegalEntityID AS PrimarySalesPersonLLE,
dbo.tblPerson.PersonID AS PrimarySalesPersonID,
ISNULL(dbo.tblSLListing.EstablishedFlag, 0)
AS EstablishedFlag, tblListingCategory.CodeValue AS listingCategory,
dbo.tblSLListing.ListingCategoryCodeID,
ISNULL(dbo.tblPerson.FirstName,
'')
+
' '
+
ISNULL(dbo.tblPerson.LastName,
'')
AS ListingAgent,
COALESCE
(SettlementState.Description, SaleState.Description, dbo.tblSLObjectState.Description)
AS CurrentState, dbo.tblSLObjectState.Description AS ListingState,
dbo.tblSLObjectState.ObjectStateID AS ListingObjectStateID, SaleState.Description AS SaleState, SaleState.ObjectStateID AS SaleObjectStateID,
SettlementState.Description AS SettlementState, SettlementState.ObjectStateID AS SettlementObjectStateID, dbo.tblProperty.PropertyState AS
State,
ISNULL(dbo.tblBodyCorporate.Name, dbo.tblDevelopment.DevelopmentName)
AS DevelopmentName, dbo.tblSLListing.PriceRangeFrom,
dbo.tblSLListing.PriceRangeTo, dbo.tblSLListing.AuctionLocation, dbo.tblSLListing.AuctionTime, dbo.tblSLListing.TenderClosingDate,
dbo.tblSLListing.TenderDeliveryLocation, dbo.tblSLListing.ListingTypeCodeID, dbo.tblSLSale.SaleID
,tblSLListingAttribute.AttributeID,tblPropertyLegalEntity.LegalEntityID as vendor_LEID
into #tempListing
FROM dbo.tblSLListing INNER
JOIN
dbo.tblProperty ON dbo.tblProperty.PropertyID = dbo.tblSLListing.PropertyID
LEFT
OUTER
JOIN tblPropertyLegalEntity ON tblPropertyLegalEntity.PropertyID = tblSLListing.PropertyID
INNER
JOIN dbo.tblSLObjectState ON dbo.tblSLObjectState.ObjectStateID = dbo.tblSLListing.ObjectStateID INNER
JOIN
dbo.tblCode AS tblListingCategory ON tblListingCategory.CodeID = dbo.tblSLListing.ListingCategoryCodeID LEFT
OUTER
JOIN
dbo.tblDevelopment ON dbo.tblProperty.DevelopmentID = dbo.tblDevelopment.DevelopmentID LEFT
OUTER
JOIN
dbo.tblBodyCorporate ON dbo.tblBodyCorporate.BodyCorporateID = dbo.tblProperty.BodyCorporateID LEFT
OUTER
JOIN
dbo.tblAddress ON dbo.tblAddress.RelatedTable =
'tblProperty'
AND dbo.tblAddress.RelatedID = dbo.tblProperty.PropertyID AND
dbo.tblAddress.AddressTypeCodeID = @AddressType LEFT
OUTER
JOIN
dbo.tblSuburb ON dbo.tblSuburb.SuburbID = dbo.tblAddress.SuburbID LEFT
OUTER
JOIN
dbo.tblSLDistrict ON dbo.tblSLDistrict.DistrictID = dbo.tblSuburb.DistrictID LEFT
OUTER
JOIN
dbo.tblSLListingLegalentity ON dbo.tblSLListingLegalentity.ListingID = dbo.tblSLListing.ListingID AND
dbo.tblSLListingLegalentity.ListingParticipantTypeCodeID = @ListingParticipantTyepe
LEFT
OUTER
JOIN
dbo.tblPerson ON dbo.tblPerson.LegalEntityID = dbo.tblSLListingLegalentity.LegalEntityID LEFT
OUTER
JOIN
dbo.tblSLSale ON dbo.tblSLSale.ListingID = dbo.tblSLListing.ListingID LEFT
OUTER
JOIN
dbo.tblSLObjectState AS SaleState ON SaleState.ObjectStateID = dbo.tblSLSale.objectStateID LEFT
OUTER
JOIN
dbo.tblSLSettlement ON dbo.tblSLSettlement.SaleID = dbo.tblSLSale.SaleID LEFT
OUTER
JOIN
dbo.tblSLObjectState AS SettlementState ON SettlementState.ObjectStateID = dbo.tblSLSettlement.objectStateID
LEFT
OUTER
JOIN tblSLListingAttribute ON tblSLListingAttribute.RelatedObjectID = tblsllisting.ListingID
WHERE
tblProperty.PropertyState = @State
AND
(@SalesRegionID = 0 or tblSLDistrict.SalesRegionID = @SalesRegionID)
AND
(@districtID = 0 or tblSLDistrict.DistrictID = @DistrictID)
AND
(@SuburbID =
''
or tblSuburb.SuburbID in
(select orderID from splitCommaIDs(@SuburbID)))
AND
( @bedrooms =
''
OR
(@bedroomsSign ='+'
AND tblSLListing.roomCount >= @bedroomCount)
OR
(@bedroomsSign='='
AND tblSLListing.roomCount = @bedroomCount))
AND
( @bathrooms =
''
OR
(@bathroomsSign ='+'
AND tblSLListing.bathRoomCount >= @bathroomCount)
OR
(@bathroomsSign ='='
AND tblSLListing.bathRoomCount = @bathroomCount)
)
AND
( @carspaces =
''
OR
(@carspacesSign ='+'
AND tblSLListing.carSpaceCount >= @carspaceCount)
OR
(@carspacesSign ='='
AND tblSLListing.carSpaceCount = @carspaceCount))
AND
( @EER = 0 or tblSLListing.EER = @EER )
AND
(@PropertyType = 0 or tblProperty.PropertyTypeCodeID in
(select orderID from splitCommaIDs(@PropertyType)))
AND
(@HouseSize = 0 or tblSLListing.Size >= @HouseSize)
AND
(@LandSize = 0 or tblProperty.BlockSize >= @LandSize)
AND
(@MinPrice = 0 or tblSLListing.Price >
= @MinPrice)
AND
(@MaxPrice = 0 or tblSLListing.Price <= @MaxPrice)
AND
(@ListingStartDate =
''
or tblSLListing.ListingDate >= @ListingStartDate)
AND
(@ListingEndDate =
''
or tblSLListing.ListingDate <= @ListingEndDate)
AND
(@ListingCategoryCodeID = 0 or tblSLListing.ListingCategoryCodeID = @ListingCategoryCodeID )
DECLARE @RowCount as
int
select @RowCount =
count(ListingID)
from #tempListing
DECLARE @sQuery as
varchar(1000)
DECLARE @RowBeforeFilter as
int
--hack to find the row count------------------------------------
SET @sQuery =
'select listingid from #tempListing '
+ @AdvanceQuery
Create
table #test
(
listingid int
)
insert #test
exec(@sQuery)
select @rowTotal=count(*)
from #test
--------------------------------------------------------
SET @sQuery =
'select *,Row_Number() over (order by '
+@orderby +') as rowNumber from #tempListing '
+ @AdvanceQuery
declare @pagingQuery as
varchar(2000);
set @pagingQuery =
'with tblResult as ('
+ @sQuery +
') select tblResult.*, '+
convert(varchar(10),@rowTotal)+' as TotalRows from tblResult where tblResult.rowNumber > '
+
convert(varchar(10), @StartRow)
+
' and tblResult.rowNumber <= '
+
convert(varchar(10), @EndRow)
+
';'
exec(@pagingQuery)
drop
table #tempListing
drop
table #test
