private EventSearchModel Search(EventSearchModel model)
{
var query = EventRepo.GetAll().Where(x => x.UserId == currentUser.Id);
if (!string.IsNullOrEmpty(model.SearchTitle))
{
query = query.Where(x => x.Title.Contains(model.SearchTitle));
}
if (model.SearchProvinceId.HasValue)
{
query = query.Where(x => x.City.provinceId == model.SearchProvinceId);
}
if (model.SearchCityId.HasValue)
{
query = query.Where(x => x.CityId == model.SearchCityId);
}
model.TotalItems = query.Count();
var result = query.OrderBy(x => x.Createdate)
.Skip((model.CurrentPage - 1) * model.PageSize)
.Take(model.PageSize)
//.ToModel()
.ToList();
model.Events = result;
return(model);
}
private EventSearchModel Search(EventSearchModel model)
{
var query = EventRepo.GetAll()
.Include(x => x.City)
.ThenInclude(x => x.Province)
.AsQueryable();
if (!string.IsNullOrEmpty(model.SearchTitle))
{
query = query.Where(x => x.Title.Contains(model.SearchTitle));
}
if (model.SearchProvinceId.HasValue)
{
query = query.Where(x => x.City.provinceId == model.SearchProvinceId);
}
if (model.SearchCityId.HasValue)
{
query = query.Where(x => x.CityId == model.SearchCityId);
}
model.TotalItems = query.Count();
var result = query
.Skip((model.CurrentPage - 1) * model.PageSize)
.Take(model.PageSize)
.ToList();
model.Events = result;
return(model);
}
public MainViewModel()
{
User = new AppUser();
EventSearchModel = new EventSearchModel();
Purchases = new List <Purchase>();
Comments = new List <Comments>();
}
public IActionResult List(EventSearchModel model)
{
System.Threading.Thread.Sleep(5000);
var result = Search(model);
return(PartialView(result));
}
/// <summary>
/// Search the events
/// </summary>
/// <param name="si"></param>
/// <param name="model"></param>
/// <returns></returns>
public JqGridSearchOut SearchEvents(JqSearchIn si, EventSearchModel model)
{
var data = SearchEvents(model);
var events = Maps(data);
return(si.Search(events));
}
public async Task <IActionResult> Index(string key)
{
var model = new EventSearchModel {
SearchTitle = key
};
model = Search(model);
return(View(model));
}
/// <summary>
/// Export events
/// </summary>
/// <param name="si"></param>
/// <param name="gridExportMode"></param>
/// <param name="model"></param>
/// <returns></returns>
public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, EventSearchModel model)
{
var data = gridExportMode == GridExportMode.All ? GetAll() : SearchEvents(model);
var events = Maps(data);
var exportData = si.Export(events, gridExportMode);
return(ExcelUtilities.CreateWorkBook(exportData));
}
/// <summary>
/// Export events
/// </summary>
/// <param name="si"></param>
/// <param name="gridExportMode"></param>
/// <param name="model"></param>
/// <returns></returns>
public ActionResult Exports(JqSearchIn si, GridExportMode gridExportMode, EventSearchModel model)
{
var workbook = _eventService.Exports(si, gridExportMode, model);
var output = new MemoryStream();
workbook.Write(output);
return(File(output.ToArray(), "application/vnd.ms-excel", "Events.xls"));
}
/// <summary>
/// Search the events
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private IQueryable <Event> SearchEvents(EventSearchModel model)
{
return(Fetch(@event => (string.IsNullOrEmpty(model.Keyword) ||
(!string.IsNullOrEmpty(@event.Title) && @event.Title.Contains(model.Keyword))
||
(!string.IsNullOrEmpty(@event.EventDescription) &&
@event.EventDescription.Contains(model.Keyword))
||
(!string.IsNullOrEmpty(@event.EventSummary) &&
@event.EventSummary.Contains(model.Keyword))) &&
(!model.EventCategoryId.HasValue
||
@event.EventEventCategories.Any(
eventEventCategory =>
eventEventCategory.EventCategoryId == model.EventCategoryId))));
}
public IEnumerable <HistoryEventInfo> Search(EventSearchModel searchModel)
{
//初始化查询sql语句
var sqlBuilder = new StringBuilder("select * from HistoryEvent join PersonEventRelation on HistoryEvent.HistoryEventId=PersonEventRelation.HistoryEventId where ");
//初始化查询参数
var searchParams = new List <object>();
//处理参数
DealStringQueryParam("Title", searchModel.Title, sqlBuilder, searchParams);
DealStringQueryParam("Province", searchModel.Province, sqlBuilder, searchParams);
DealStringQueryParam("Place", searchModel.Place, sqlBuilder, searchParams);
if (searchModel.FamousPersonId != null)
{
sqlBuilder.Append("PersonEventRelation.FamousPersonId ={0} and ");
searchParams.Add(searchModel.FamousPersonId);
}
if (searchModel.MinOccurDate != null)
{
sqlBuilder.Append("OccurDate >={0} and ");
searchParams.Add(searchModel.MinOccurDate);
}
if (searchModel.MaxOccurDate != null)
{
sqlBuilder.Append("OccurDate <={0} and ");
searchParams.Add(searchModel.MaxOccurDate);
}
var eventInfos = new List <HistoryEventInfo>();
//无任何查询参数 返回无元素的数组
if (searchParams.Count == 0)
{
return(eventInfos);
}
//移除最后多余的"and "
sqlBuilder.Remove(sqlBuilder.Length - 5, 4);
//sql查询
var events = historyEventRepository.FindBySQL(sqlBuilder.ToString(), searchParams.ToArray());
foreach (var @event in events)
{
var personIds = personEventRelationRepository.FindByWhereAndSelect(p => p.HistoryEventId == @event.HistoryEventId, p => p.FamousPersonId).ToList();
eventInfos.Add(HistoryEventConverter.ConvertToDto(@event, (IEnumerable <int>)personIds));
}
return(eventInfos);
}
public async Task <ObservableCollection <HistoryEvent> > SearchAsync(EventSearchModel searchModel)
{
using (var httpClient = new HttpClient())
{
var response = await httpClient.PostAsJsonAsync(SearchUrl, searchModel);
if (response.IsSuccessStatusCode)
{
return(await response.Content.ReadAsAsync <ObservableCollection <HistoryEvent> >());
}
else
{
var apiErrorModel = await response.Content.ReadAsAsync <ApiErrorModel>();
throw new ApiErrorException(apiErrorModel);
}
}
}
public List <EventSearchModel> FillEventSearchGrid(EventSearchModel model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <EventSearchModel> lstEvent = new List <EventSearchModel>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetEventPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbParameter paramSortBy = cmd.CreateParameter();
paramSortBy.ParameterName = "SortBy";
paramSortBy.Value = model.SortBy;
cmd.Parameters.Add(paramSortBy);
DbParameter paramOrderBy = cmd.CreateParameter();
paramOrderBy.ParameterName = "OrderBy";
paramOrderBy.Value = model.OrderBy;
cmd.Parameters.Add(paramOrderBy);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
EventSearchModel searchmodel = new EventSearchModel();
searchmodel.EventID = Convert.ToInt64(dr["EventID"].ToString());
searchmodel.EventName = dr["EventName"].ToString();
searchmodel.PropertyName = dr["PropertyName"].ToString();
searchmodel.Photo = dr["Photo"].ToString();
searchmodel.EventDate = dr["EventDate"].ToString();
searchmodel.Description = dr["Description"].ToString();
searchmodel.Type = Convert.ToInt32(dr["Type"].ToString());
lstEvent.Add(searchmodel);
}
db.Dispose();
return(lstEvent.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public int BuildPaganationEventList(EventSearchModel model)
{
int NOP = 0;
ShomaRMEntities db = new ShomaRMEntities();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetEventPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbParameter paramSortBy = cmd.CreateParameter();
paramSortBy.ParameterName = "SortBy";
paramSortBy.Value = model.SortBy;
cmd.Parameters.Add(paramSortBy);
DbParameter paramOrderBy = cmd.CreateParameter();
paramOrderBy.ParameterName = "OrderBy";
paramOrderBy.Value = model.OrderBy;
cmd.Parameters.Add(paramOrderBy);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
if (dtTable.Rows.Count > 0)
{
NOP = int.Parse(dtTable.Rows[0]["NumberOfPages"].ToString());
}
db.Dispose();
return(NOP);
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public string _AjaxBinding(JqSearchIn si, EventSearchModel model)
{
return(JsonConvert.SerializeObject(_eventService.SearchEvents(si, model)));
}
public ActionResult Index()
{
var model = new EventSearchModel();
return(View(model));
}
public IEnumerable <HistoryEventInfo> Search([FromBody] EventSearchModel searchModel)
{
return(HistoryEventService.Search(searchModel));
}
public async Task <ObservableCollection <HistoryEvent> > SearchAsync(EventSearchModel searchModel)
{
return(await HttpClientUtils.PostJsonAsync <EventSearchModel, ObservableCollection <HistoryEvent> >(SEARCH_URL, searchModel));
}
public async Task <PaginatedResultModel <EventModel> > GetEvents(EventSearchModel filters)
{
return(await _dataAccess.GetEvents(filters));
}
