public List <ListingComm> GetMapListings(SearchModelComm data)
{
string param = "";
List <ListingComm> listings = new List <ListingComm>();
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
string SQL = "SELECT l1.[MLSNumber] FROM [listings-commercial-3] l1 JOIN [listings-geo] l2 ON l1.[MLSNumber] = l2.[MLSNumber] WHERE l1.Status = 'A' ";
string[] TownList = GetRelatedTowns(data.City);
if (TownList != null && !string.IsNullOrEmpty(TownList[0]))
{
SQL += "AND (City = @City1 ";
for (int x = 2; x <= TownList.Length; x++)
{
SQL += " OR City = @City" + x.ToString();
}
SQL += ") ";
}
if (data.Acres > 0)
{
SQL += "AND [LotSizeArea] >= @Acres ";
}
if (data.Acres2 > 0)
{
SQL += "AND [LotSizeArea] <= @Acres2 ";
}
if (data.MinPrice > 0)
{
SQL += "AND [CurrentPrice] >= @MinPrice ";
}
if (data.MaxPrice > 0)
{
SQL += "AND [CurrentPrice] <= @MaxPrice ";
}
if (data.SqFt > 0)
{
SQL += "AND [SqFtTotal] >= @SqFt ";
}
if (data.SqFt2 > 0)
{
SQL += "AND [SqFtTotal] <= @SqFt2 ";
}
SQL += "ORDER BY [CurrentPrice] DESC, City";
using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
cmd.CommandType = CommandType.Text;
if (TownList != null && !string.IsNullOrEmpty(TownList[0]))
{
for (int x = 1; x <= TownList.Length; x++)
{
cmd.Parameters.Add("City" + x.ToString(), SqlDbType.VarChar, 255).Value = TownList[x - 1];
param += "&city=" + HttpUtility.UrlEncode(TownList[x - 1]);
}
}
if (data.Acres > 0)
{
cmd.Parameters.Add("Acres", SqlDbType.Float).Value = data.Acres; param += "&acres=" + data.Acres.ToString();
}
if (data.Acres2 > 0)
{
cmd.Parameters.Add("Acres2", SqlDbType.Float).Value = data.Acres2; param += "&acres2=" + data.Acres2.ToString();
}
if (data.MinPrice > 0)
{
cmd.Parameters.Add("MinPrice", SqlDbType.Float).Value = data.MinPrice * 1000; param += "&minprice=" + data.MinPrice.ToString();
}
if (data.MaxPrice > 0)
{
cmd.Parameters.Add("MaxPrice", SqlDbType.Float).Value = data.MaxPrice * 1000; param += "&maxprice=" + data.MaxPrice.ToString();
}
if (data.SqFt > 0)
{
cmd.Parameters.Add("SqFt", SqlDbType.Float).Value = data.SqFt; param += "&sqft=" + data.SqFt.ToString();
}
if (data.SqFt2 > 0)
{
cmd.Parameters.Add("SqFt2", SqlDbType.Float).Value = data.SqFt2; param += "&sqft2=" + data.SqFt2.ToString();
}
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
listings.Add(new ListingComm(dr[0].ToString()));
}
cmd.Connection.Close();
}
}
return(listings);
}
public SearchResultComm DoSearch(SearchModelComm data, int page = 1, int PostsPerPage = 12)
{
int start = ((page - 1) * PostsPerPage) + 1;
int end = (page * PostsPerPage);
string param = "";
string where = "";
SearchResultComm searchResult = new SearchResultComm();
List <ListingComm> listings = new List <ListingComm>();
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) {
string[] TownList = GetRelatedTowns(data.City);
if (data.City != null)
{
foreach (string city in data.City)
{
param += "&city=" + HttpUtility.UrlEncode(city);
}
}
if (TownList != null && !string.IsNullOrEmpty(TownList[0]))
{
where += "AND (City = @City1 ";
for (int x = 2; x <= TownList.Length; x++)
{
where += " OR City = @City" + x.ToString();
}
where += ") ";
}
if (data.Acres > 0)
{
where += "AND [LotSizeArea] >= @Acres ";
}
if (data.Acres2 > 0)
{
where += "AND [LotSizeArea] <= @Acres2 ";
}
if (data.MinPrice > 0)
{
where += "AND [CurrentPrice] >= @MinPrice ";
}
if (data.MaxPrice > 0)
{
where += "AND [CurrentPrice] <= @MaxPrice ";
}
if (data.SqFt > 0)
{
where += "AND [SqFtTotal] >= @SqFt ";
}
if (data.SqFt2 > 0)
{
where += "AND [SqFtTotal] <= @SqFt2 ";
}
string SQL = "SELECT COUNT(*) FROM [listings-commercial-3] WHERE Status = 'A' " + where;
using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
cmd.CommandType = CommandType.Text;
if (TownList != null && !string.IsNullOrEmpty(TownList[0]))
{
for (int x = 1; x <= TownList.Length; x++)
{
cmd.Parameters.Add("City" + x.ToString(), SqlDbType.VarChar, 255).Value = TownList[x - 1];
}
}
if (data.Acres > 0)
{
cmd.Parameters.Add("Acres", SqlDbType.Float).Value = data.Acres; param += "&acres=" + data.Acres.ToString();
}
if (data.Acres2 > 0)
{
cmd.Parameters.Add("Acres2", SqlDbType.Float).Value = data.Acres2; param += "&acres2=" + data.Acres2.ToString();
}
if (data.MinPrice > 0)
{
cmd.Parameters.Add("MinPrice", SqlDbType.Float).Value = data.MinPrice * 1000; param += "&minprice=" + data.MinPrice.ToString();
}
if (data.MaxPrice > 0)
{
cmd.Parameters.Add("MaxPrice", SqlDbType.Float).Value = data.MaxPrice * 1000; param += "&maxprice=" + data.MaxPrice.ToString();
}
if (data.SqFt > 0)
{
cmd.Parameters.Add("SqFt", SqlDbType.Float).Value = data.SqFt; param += "&sqft=" + data.SqFt.ToString();
}
if (data.SqFt2 > 0)
{
cmd.Parameters.Add("SqFt2", SqlDbType.Float).Value = data.SqFt2; param += "&sqft2=" + data.SqFt2.ToString();
}
cmd.Connection.Open();
searchResult.TotalResults = (int)cmd.ExecuteScalar();
cmd.Connection.Close();
int maxPages = (int)Math.Ceiling(Convert.ToDouble(searchResult.TotalResults) / PostsPerPage); // (searchResult.TotalResults / PostsPerPage) + 1;
if (page > maxPages)
{
start = ((maxPages - 1) * PostsPerPage) + 1;
end = (maxPages * PostsPerPage);
searchResult.NextPage = "";
}
else if (page == maxPages)
{
searchResult.NextPage = "";
}
else
{
searchResult.NextPage = "/searchcomm?" + param + "&page=" + (page + 1).ToString();
}
if (page == 1)
{
searchResult.PrevPage = "";
}
else
{
searchResult.PrevPage = "/searchcomm?" + param + "&page=" + (page - 1).ToString();
}
SQL = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [CurrentPrice] DESC, City) AS Rownumber, [MLSNumber] FROM [listings-commercial-3] WHERE Status = 'A' " + where;
SQL += ") a WHERE Rownumber BETWEEN " + start.ToString() + " and " + end.ToString();
cmd.CommandText = SQL;
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
listings.Add(new ListingComm(dr[1].ToString()));
}
cmd.Connection.Close();
}
}
searchResult.Listings = listings;
return(searchResult);
}
