1, '==================================================================== 'Function: gGetPropcateTypeListByPropMarketIds 'Purpose: Retrieves list of property category for specific markets '==================================================================== Public Shared Function gGetPropCateByPropMarketIds(ByVal iPropMarket As ICollection(Of Integer)) As DataSet 'Open connection Dim objConn As SqlConnection = openDBConnection() Dim spGetPropCate As String = "SELECT Distinct pc.CategoryName, pc.idPropCategory FROM Property p" & _ " JOIN PropCategoryList pcl ON p.idList = pcl.idList" & _ " JOIN PropCategory pc ON pcl.idPropCategory = pc.idPropCategory" & _ " WHERE p.idPropMarket IN (SELECT id FROM StringToIntTable(@PropMarketIds))" Dim objCmd As SqlCommand = New SqlCommand(spGetPropCate, objConn) 'Input Params objCmd.Parameters.Add("@PropMarketIds", SqlDbType.VarChar).Value = String.Join(",", New List(Of Integer)(iPropMarket).ConvertAll(Of String)(AddressOf Convert.ToString).ToArray()) 'Must use an adapter to populate DataSet Dim objAda As SqlDataAdapter = New SqlDataAdapter(objCmd) Dim objDSet As DataSet = New DataSet objAda.Fill(objDSet) 'Clean up objCmd.Dispose() objAda.Dispose() closeDBConnection(objConn) 'Return value Return objDSet End Function 2, '============================================================================== 'Function: gGetPropertiesForDocOutput 'Purpose: Retrieve dataset containing list of properties for Word doc 'TEMP.. no need for separate function like this? '============================================================================== Public Shared Function gGetPropertiesForDocOutput(ByVal strPropMarkets As System.Text.StringBuilder, _ ByVal strFolders As System.Text.StringBuilder, _ ByVal strPropStatus As System.Text.StringBuilder, _ ByVal strPTypes As System.Text.StringBuilder, _ ByVal strPCats As System.Text.StringBuilder, _ ByVal QueryCounty As Integer, _ ByVal QueryDistrict As Integer, _ ByVal QueryMinPrice As Double, _ ByVal QueryMaxPrice As Double, _ ByVal QueryRooms As Double, _ ByVal strCRules As System.Text.StringBuilder, _ ByVal QueryOrderBy As String, _ ByVal QueryAscDesc As String, _ ByVal QueryPropRef As String, _ ByVal iUser As Integer, _ ByVal QueryDateType As System.Text.StringBuilder, _ ByVal QueryDateFrom As System.Text.StringBuilder, _ ByVal QueryDateTo As System.Text.StringBuilder) As DataSet 'temp Dim gPublishedOnline As Integer = 1 Dim gArchivedOffline As Integer = 2 Dim gToBeDeleted As Integer = 3 Dim gExpired As Integer = 4 Dim gActiveOffline As Integer = 5 Dim gAwaitingPayment As Integer = 6 Dim gPendingReview As Integer = 7 Dim gNoPreference As Integer = 0 Dim gEmptyString As String = "" 'Open connection Dim objConn As SqlConnection = openDBConnection() Dim strSQL As System.Text.StringBuilder = New System.Text.StringBuilder strSQL.Append("SELECT DISTINCT p.idList, p.ListRef, p.AddressLine1, p.Postcode, p.Description, p.idPropMarket, p.idFolder, d.DistrictName, c.CountyCityName, ") strSQL.Append("bl.TotalRooms, p.PriceVal, f.FrequencyName, pt.PTypeName, pp.PicPath, pp.PicName, l.LTypeName, lcl.OccasionalWeekends, lcl.WeekendPrice, ") strSQL.Append("lc.idLetCategory, s.StatusName, p.DisplayAddress, ") strSQL.Append("(SELECT TOP 1 OfferPrice FROM PropOfferPrice WHERE idList = p.idList ORDER BY OfferPrice DESC) AS 'OfferPrice' ") strSQL.Append("FROM Property p ") strSQL.Append("JOIN PropertyType pt ON p.idPType = pt.idPType ") strSQL.Append("JOIN Frequency f ON p.idFrequency = f.idFrequency ") strSQL.Append("JOIN District d ON p.idDistrict = d.idDistrict ") strSQL.Append("JOIN CountyCity c ON p.idCountyCity = c.idCountyCity ") strSQL.Append("JOIN LivingType l ON p.idLType = l.idLType ") strSQL.Append("JOIN PropStatus s ON p.idPropStatus = s.idPropStatus ") strSQL.Append("LEFT OUTER JOIN BedroomList bl ON p.idList = bl.idList ") strSQL.Append("LEFT OUTER JOIN PropertyPicture pp ON p.idList = pp.idList AND pp.Thumbnail = 1 ") strSQL.Append("LEFT OUTER JOIN LetCategoryList lcl ON p.idList = lcl.idList ") strSQL.Append("LEFT OUTER JOIN LetCategory lc ON lcl.idLetCategory = lc.idLetCategory ") strSQL.Append("WHERE ") 'Custom arguments If (QueryDateType.ToString() <> "") Then strSQL.Append("(p." & QueryDateType.ToString & " BETWEEN '" & QueryDateFrom.ToString & "' AND DATEADD(mm,1,'" & QueryDateTo.ToString & "')) AND ") If strPropMarkets.Length <> 0 Then strSQL.Append("p.idPropMarket IN " & strPropMarkets.ToString & " AND ") If strFolders.Length <> 0 Then strSQL.Append("p.idFolder IN " & strFolders.ToString & " AND ") If strPropStatus.Length <> 0 Then strSQL.Append("p.idPropStatus IN " & strPropStatus.ToString & " AND ") If strPTypes.Length <> 0 Then strSQL.Append("p.idPType IN " & strPTypes.ToString & " AND ") If strPCats.Length <> 0 Then strSQL.Append("p.idList IN ( SELECT DISTINCT pcl.idList FROM PropCategoryList pcl WHERE pcl.idPropCategory IN " & strPCats.ToString & " ) AND ") If QueryCounty <> 0 Then strSQL.AppendFormat("p.idCountyCity = {0} AND ", QueryCounty) If QueryDistrict <> 0 Then strSQL.AppendFormat("p.idDistrict = {0} AND ", QueryDistrict) If QueryMinPrice <> 0 Then strSQL.AppendFormat("p.PriceVal >= {0} AND ", QueryMinPrice) If QueryMaxPrice <> 0 Then strSQL.AppendFormat("p.PriceVal <= {0} AND ", QueryMaxPrice) 'If numerical value passed in for PropRef, then compare idList as well as ListRef, otherwise just ListRef If QueryPropRef <> gEmptyString And ValidateExpression(QueryPropRef, "^[/d+]*/.?/d+$") Then strSQL.AppendFormat("(p.ListRef = '{0}' OR p.idList = '{0}') AND ", QueryPropRef) If QueryPropRef <> gEmptyString And (Not ValidateExpression(QueryPropRef, "^[/d+]*/.?/d+$")) Then strSQL.AppendFormat("p.ListRef = '{0}' AND ", QueryPropRef) If QueryRooms <> 0 And QueryRooms < 5 Then strSQL.AppendFormat("bl.TotalRooms = {0} AND ", QueryRooms) If QueryRooms <> 0 And QueryRooms > 5 Then strSQL.AppendFormat("bl.TotalRooms > 5 AND ") If strCRules.Length <> 0 Then strSQL.Append("p.idCRule IN " & strCRules.ToString & " AND ") Else strSQL.Append("(p.idCRule = " & gPublishedOnline & " OR p.idCRule = " & gArchivedOffline & " OR p.idCRule = " & gExpired & " OR p.idCRule = " & gPendingReview & ") AND ") End If strSQL.Append("p.idCreatedBy = @idUser ") If QueryOrderBy <> gEmptyString Then strSQL.Append("ORDER BY " & QueryOrderBy & " ") If QueryAscDesc <> gEmptyString Then strSQL.Append(QueryAscDesc) Dim objCmd As SqlCommand = New SqlCommand(strSQL.ToString, objConn) 'Input Params objCmd.Parameters.Add("@idUser", SqlDbType.Int).Value = iUser 'Must use an adapter and DataSet to use paging (since these retrieve data ICollections and store on cache) Dim objAda As SqlDataAdapter = New SqlDataAdapter(objCmd) Dim objDSet As DataSet = New DataSet objAda.Fill(objDSet) 'Clean up objCmd.Dispose() objAda.Dispose() closeDBConnection(objConn) 'Return value Return objDSet End Function