public ActionResult SearchResult(AdvertisersViewModel advertisers)
{
ViewBag.Title = "Search Result";
return View("Advertisers", advertisers);
}
public AdvertisersViewModel GetAdvertiserListing(int pageNumber, int pageSize, int? categoryId, int? cityId,
int? townId, string displayName, string callType, string height, string build)
{
AdvertisersViewModel advertisers = new AdvertisersViewModel(_cityList, _categoryList)
{
Page = pageNumber,
PageSize = pageSize
};
const string advertiserSql = "SELECT * FROM [dbo].[AdvertiserActiveView] WHERE";
string townSql = "SELECT * FROM [dbo].[AdvertiserTownCount] Where (CityId = @CityId)";
StringBuilder sql = new StringBuilder();
sql.Append(advertiserSql);
if (townId.HasValue)
{
sql.AppendFormat(" (TownId = {0})", townId);
}
else if (cityId.HasValue)
{
sql.AppendFormat(" (CityId = {0})", cityId);
}
if (!string.IsNullOrWhiteSpace(displayName))
{
if ((!townId.HasValue) && (!cityId.HasValue))
{
sql.AppendFormat(" (DisplayName ='{0}')", displayName.Replace("'", "''"));
}
else
{
sql.AppendFormat(" AND (DisplayName ='{0}')", displayName.Replace("'", "''"));
}
}
if (categoryId.HasValue)
{
if ((!townId.HasValue) && (!cityId.HasValue && (string.IsNullOrWhiteSpace(displayName))))
{
sql.AppendFormat(" (CategoryId = {0})", categoryId);
}
else
{
sql.AppendFormat(" AND (CategoryId = {0})", categoryId);
}
}
if (!string.IsNullOrWhiteSpace(callType))
{
if ((!townId.HasValue) && (!cityId.HasValue) && (string.IsNullOrWhiteSpace(displayName)) &&
(!categoryId.HasValue))
{
sql.AppendFormat(" (CallType = '{0}')", callType);
}
else
{
sql.AppendFormat(" AND (CallType = '{0}')", callType);
}
}
IEnumerable<AdvertiserViewModel> advertisers1 = null;
IEnumerable<TownViewModel> townViews = null;
using (SqlConnection sqlConnection = new SqlConnection(GetConnection()))
{
advertisers1 = sqlConnection.Query<AdvertiserViewModel>(sql.ToString()).ToList();
townViews = sqlConnection.Query<TownViewModel>(townSql, new { @cityId = cityId });
}
////Set AdvertiserCount
var advertiserViewModels = advertisers1 as IList<AdvertiserViewModel> ?? advertisers1.ToList();
advertisers.AdvertiserCount = advertiserViewModels.Count();
advertisers.CategoryList.ForEach(x =>
{
x.AdvertiserCount = advertiserViewModels.Count(a => a.CategoryId == x.Id);
});
if (advertisers.CityList.Count > 0)
{
advertisers.CityList.ForEach(x =>
{
x.AdvertiserCount = advertiserViewModels.Count(a => a.CityId == x.Id);
x.TownList.Clear();
townViews.ToList().ForEach(y =>
x.TownList.Add(
new TownViewModel
{
AdvertiserCount = y.AdvertiserCount,
Name = y.Name,
Id = y.Id
})
);
});
}
var returns = advertiserViewModels.Skip(pageSize * (pageNumber - 1)).Take(pageSize);
returns.ToList().ForEach(a => { advertisers.AdvertiserList.Add(a); });
return advertisers;
}