-- =============================================
-- Author: Jitendra Singh-- Create date: 09/01/2013
-- Description: Advance Search the property details
-- =============================================
CREATE PROCEDURE [dbo].[lsp_PropAdvanceSearch]
@usrId NVARCHAR(50),
@TransId NVARCHAR(3) = '',
@PrpTypId NVARCHAR(10) ='',
@PrpSubTypId NVARCHAR(10)='',
@cityId NVARCHAR(200)='',
@Rooms NVARCHAR(3)='',
@MinAmnt NVARCHAR(50)='',
@MaxAmnt NVARCHAR(200)='',
@AmntCrncyId NVARCHAR(10)='',
@MinArea NVARCHAR(200)='',
@MaxArea NVARCHAR(200)='',
@AreaUnitId NVARCHAR(10)='',
@PGTyp NVARCHAR(5)='',
@PGRentTyp NVARCHAR(5)='',
@PCYear NVARCHAR(20)=''
AS
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
--DECLARE @CtyId bigint
--select @CtyId= Cityid from tblUsrCity where CityName=@city
SET @strQuery='SELECT DISTINCT(PROP.UsrShrPropId) AS UsrShrPropId,PROP.usrid,PROP.PropOBAId,PROP.TypOfSale,
PROP.PropTransTypId,PTRANS.PropTransTyp,PROP.propTypId,PT.PropTyp,PROP.propSubTypId,PST.PropSubTyp,(dbo.GetPropImgPath(PROP.UsrShrPropId) ) AS Pimage,PROP.ProjSocTitlName,
PROP.area,(dbo.getUnit(PROP.UntTypId)) AS areaUnit,
PROP.amnt,(dbo.getCrncy(PROP.CrncyTypId)) AS amntCrncy,
PROP.PCompletionYear,
PROP.RNTMonthlyRent,(dbo.getCrncy(PROP.RNTMonthlyRentCrncyTypeId)) As MnthlyRenttamntCrncy,
PROP.RNTSecurityAmt ,(dbo.getCrncy(PROP.RNtSecurityCrncyTypeId)) As SecurityRentamntCrncy,PROP.RNTSecurityTypeId,
PROP.NoOfRooms,PROP.NoOfBathRooms,
PROP.ReqAreaFrom,PROP.ReqAreaTo,(dbo.getUnit(PROP.ReqAreaUnit)) AS ReqAreaUnit,
PROP.ReqAmntFrom,PROP.ReqAmntTo,(dbo.getCrncy(PROP.ReqAmntCrncy)) AS ReqAmntCrncy,
--PROP.RNTElctrcChrgTypeId,PROP.RNTMntncChrgTypeId,PROP.RNTWaterBillTypeId,
--PROP.RNTMinRntAgrmntYear,PROP.RNTMinRntAgrmntMonth,PROP.RNTMaxRntAgrmntYear,PROP.RNTMaxRntAgrmntMonth,
PROP.PGTypId,PROP.PGChrgTypId,
(dbo.fn_LikeCnt(PROP.UsrShrPropId,10)) As pLikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=10 and tls.usrShrid=PROP.UsrShrPropId and tls.usrid='+@usrId+') as likedsts,
(dbo.fn_DisLikeCnt(PROP.UsrShrPropId,10)) As pDisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=10 and tds.usrShrid=PROP.UsrShrPropId and tds.usrid='+@usrId+') as dislikedsts,
(dbo.fn_CmntCnt(PROP.UsrShrPropId,10)) As pCmntCnt,
(dbo.GetUsrImgPath(PROP.usrid)) As usrimage,
PROP.PropOBAName AS usrname,PROP.Email,PROP.Phone,CTY.cityname,CNT.cntryname,PROP.addrs,
PROP.Description , PROP.cdate,PROP.lat,PROP.lng FROM tblUsrShrProp PROP
INNER JOIN tblUsrPropTrans PTRANS ON PTRANS.PropTransId=PROP.propTransTypId
LEFT OUTER JOIN tblUsrPropTyp PT ON PT.PropTypId=PROP.propTypId
LEFT OUTER JOIN tblUsrPropSubTyp PST ON PST.PropSubTypId=PROP.propSubTypId
INNER JOIN tblCountry CNT ON CNT.cntryCode=PROP.cntryId
LEFT OUTER JOIN tblCity CTY ON CTY.cityid=PROP.ctyId WHERE 1=1 AND PROP.usrid !=' + @usrId + ''
--IF(@usrId<>'' )
-- SET @strQuery = @strQuery + ' AND PROP.usrid =' + @usrId + ''
IF(@TransId=1)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=2)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=3)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@PGTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGTypId =' + @PGTyp + ''
IF(@PGRentTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGChrgTypId =' + @PGRentTyp + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=4)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntFrom >=' + @MinAmnt + ' AND PROP.ReqAmntFrom <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntCrncy =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaFrom >=' + @MinArea + ' AND PROP.ReqAreaFrom <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaUnit =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=5)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
IF(@PCYear <>'')
SET @strQuery = @strQuery + ' AND PROP.PCompletionYear =' + @PCYear + ''
END
--PRINT @strQuery
EXEC (@strQuery)
END
--EXEC lsp_PropAdvanceSearch 100,1,'','','','','','','','','','','','',''
--SELECT * FROM tblusrshrprop
No comments:
Post a Comment