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>());
}
