public int CountAllFilterActivityLog(ActivityLogQueryParms parms) { var total = 0; var parameters = new Dictionary <string, object> { { "@Email", parms.Email }, { "@ActivityText", parms.ActivityText }, { "@ActivityType", parms.ActivityType }, { "@FromDate", parms.FromDate }, { "@ToDate", parms.ToDate } }; using (var conn = new SqlConnection(_connectionString)) { var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text, @"SELECT count(*) as CountNum FROM aspnetactivitylog as a LEFT JOIN aspnetusers as b on a.UserId = b.Id WHERE 1 = 1 AND b.Email LIKE CONCAT('%', @Email , '%') AND a.ActivityText LIKE CONCAT('%', @ActivityText , '%') AND STR_TO_DATE(a.ActivityDate,'%Y-%m-%d') BETWEEN STR_TO_DATE(@FromDate,'%Y-%m-%d') AND STR_TO_DATE(@ToDate,'%Y-%m-%d') AND a.ActivityType LIKE CONCAT('%', @ActivityType , '%') ", parameters); if (reader.Read()) { total = Utils.ConvertToInt32(reader["CountNum"], 0); } } return(total); }
public ActionResult UserActivity(ActivityLogViewModel model) { int currentPage = 1; int pageSize = AdminSettings.PageSize; if (string.IsNullOrEmpty(model.SearchExec)) { model.SearchExec = "Y"; if (!ModelState.IsValid) { ModelState.Clear(); } } if (Request["Page"] != null) { currentPage = Convert.ToInt32(Request["Page"]); } var str_min = DateTime.MinValue.Year + "-" + DateTime.MinValue.Month + "-" + DateTime.MinValue.Day; var str_now = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; var parms = new ActivityLogQueryParms { Email = model.Email != null ? model.Email : string.Empty, ActivityText = model.ActivityText != null ? model.ActivityText : string.Empty, ActivityType = model.ActivityType != null ? model.ActivityType : string.Empty, FromDate = model.FromDate != null ? model.FromDate : str_min, ToDate = model.ToDate != null ? model.ToDate : str_now, PageSize = pageSize, CurrentPage = currentPage }; try { model.SearchResults = _activityStore.FilterActivityLog(parms); model.Total = _activityStore.CountAllFilterActivityLog(parms); model.CurrentPage = currentPage; model.PageNo = (model.Total + pageSize - 1) / pageSize; model.PageSize = pageSize; } catch (Exception ex) { this.AddNotification("Failed to get data because: " + ex.ToString(), NotificationType.ERROR); return(View(model)); } return(View(model)); }
public IQueryable <ActivityLog> FilterActivityLog(ActivityLogQueryParms parms) { List <ActivityLog> list = new List <ActivityLog>(); using (var conn = new SqlConnection(_connectionString)) { int offset = (parms.CurrentPage - 1) * parms.PageSize; var parameters = new Dictionary <string, object> { { "@Email", parms.Email }, { "@ActivityText", parms.ActivityText }, { "@ActivityType", parms.ActivityType }, { "@FromDate", parms.FromDate }, { "@ToDate", parms.ToDate }, { "@pageSize", parms.PageSize }, { "@offset", offset } }; var reader = MsSqlHelper.ExecuteReader(conn, CommandType.Text, @"SELECT a.*,b.Email FROM aspnetactivitylog as a LEFT JOIN aspnetusers as b on a.UserId = b.Id WHERE 1 = 1 AND b.Email LIKE CONCAT('%', @Email , '%') AND a.ActivityText LIKE CONCAT('%', @ActivityText , '%') AND STR_TO_DATE(a.ActivityDate,'%Y-%m-%d') BETWEEN STR_TO_DATE(@FromDate,'%Y-%m-%d') AND STR_TO_DATE(@ToDate,'%Y-%m-%d') AND a.ActivityType LIKE CONCAT('%', @ActivityType , '%') ORDER BY a.ActivityDate DESC, b.Email ASC LIMIT @offset,@pageSize", parameters); while (reader.Read()) { var item = (ActivityLog)Activator.CreateInstance(typeof(ActivityLog)); item.ActivityLogId = (int)reader[0]; item.UserId = reader[1].ToString(); item.ActivityText = reader[2].ToString(); item.TargetType = reader[3].ToString(); item.TargetId = reader[4].ToString(); item.IPAddress = reader[5].ToString(); item.ActivityDate = (DateTime)reader[6]; item.ActivityType = reader[7].ToString(); item.UserName = reader[8].ToString(); list.Add(item); } } return(list.AsQueryable <ActivityLog>()); }