public static IQueryable<vw_ActivityLog> PrepareQuery(IQueryable<vw_ActivityLog> actHistoryQuery, vw_ActivityLog alog)
{
#region Append WHERE clause if applicable
// Append filter for User Text or ID
if (!string.IsNullOrEmpty(alog.UserText))
actHistoryQuery = actHistoryQuery.Where(o => SqlMethods.Like(o.UserText.ToUpper(), alog.UserText.ToUpper()));
else if
(alog.UserID > 0) actHistoryQuery = actHistoryQuery.Where(o => o.UserID == alog.UserID);
// Append filter for Activity or ID
if (!string.IsNullOrEmpty(alog.Activity))
actHistoryQuery = actHistoryQuery.Where(o => SqlMethods.Like(o.Activity.ToUpper(), alog.Activity.ToUpper()));
else if
(alog.ActivityID > 0) actHistoryQuery = actHistoryQuery.Where(o => o.ActivityID == alog.ActivityID);
// Append filter for Claim
if (!string.IsNullOrEmpty(alog.ClaimText))
actHistoryQuery = actHistoryQuery.Where(o => Defaults.stringToStrList(alog.ClaimText).Contains(o.ClaimText));
// Append filter for File
if (!string.IsNullOrEmpty(alog.FileName))
actHistoryQuery = actHistoryQuery.Where(o => SqlMethods.Like(o.FileName.ToUpper(), alog.FileName.ToUpper()));
//Apply date filter (http://www.filamentgroup.com/lab/date_range_picker_using_jquery_ui_16_and_jquery_ui_css_framework/)
if (alog.ActDateFrom.HasValue)
actHistoryQuery = actHistoryQuery.Where(o => o.ActDateTime.Date >= alog.ActDateFrom_SQL.Value.Date);
if (alog.ActDateTo.HasValue)
actHistoryQuery = actHistoryQuery.Where(o => o.ActDateTime.Date <= alog.ActDateTo_SQL.Value.Date);
#endregion
return actHistoryQuery;
}
public ActionResult Log(vw_ActivityLog searchObj, string doReset, string qData)
{
searchOpts = (doReset == "on") ? new vw_ActivityLog() : searchObj; // Set or Reset Search-options
populateData(true);// Populate ddl Viewdata
//DO THE FOLLOWING WHEN SEARCH, SORT & PAGE - all come here
//if (doReset == "on") _Session.NewSort = _Session.OldSort = string.Empty;//Set sort variables
//AT PRESENT ONLY 'RESET' & 'SEARCH' come here so need to reset
//_Session.NewSort = _Session.OldSort = string.Empty;//Set sort variables
TempData["SearchData"] = searchObj;// To be used by partial view
return RedirectToAction("ActivityLog");//Though ajaxified but DON'T return - return View();
}
/* Group by sample
* var result = from i in
(from uh in db.UserHistories
where uh.User.UserID == UserID && uh.CRMEntityID == (int)entity
select new { uh.ActionID, uh.ActionType, uh.ObjectID, uh.Date })
group i by new { i.ActionID, i.ActionType, i.ObjectID } into g
select new { g.Key.ActionID, g.Key.ActionType, g.Key.ObjectID, g.Max(uh=>uh.Date) };
*/
public List<ActivityRpt.ActivityCount> GetActivityCount(vw_ActivityLog alog)
{
List<vw_ActivityLog> actList = SearchActivity(activitySortOn, alog);
List<ActivityRpt.ActivityCount> actCountLst = new List<ActivityRpt.ActivityCount>(actList.Count);
//Get grouped data
var grouped = from al in actList
group al by new {al.ActivityID, al.Activity} into g
orderby g.Key.Activity
select new
{ActivityID = g.Key.ActivityID, Activity= g.Key.Activity, Count = g.Count() };
//Populate ActivityCount list
foreach (var act in grouped)
actCountLst.Add(new ActivityRpt.ActivityCount()
{ ActivityID = act.ActivityID, Activity = act.Activity, Count = act.Count });
return actCountLst;
}
public PagedList<vw_ActivityLog> Search(string orderBy, int? pgIndex, int pageSize, vw_ActivityLog alog)
{
orderBy = string.IsNullOrEmpty(orderBy) ? sortOn : orderBy;
using (dbc)
{
IQueryable<vw_ActivityLog> actHistoryQuery = (from vw_u in dbc.vw_ActivityLogs orderby vw_u.ActivityID select vw_u);
//Get filters - if any
actHistoryQuery = PrepareQuery(actHistoryQuery, alog);
// Apply Sorting, Pagination and return PagedList
return actHistoryQuery.OrderBy(orderBy).ToPagedList(pgIndex ?? 1, pageSize);
}
}
public List<vw_ActivityLog> SearchActivity(string orderBy, vw_ActivityLog alog)
{
orderBy = string.IsNullOrEmpty(orderBy) ? activitySortOn : orderBy;
using (dbc)
{
IQueryable<vw_ActivityLog> actHistoryQuery = (from vw_u in dbc.vw_ActivityLogs orderby vw_u.ActivityID select vw_u);
//Get filters - if any
actHistoryQuery = ActivityLogService.PrepareQuery(actHistoryQuery, alog);
// Sort and return list
return actHistoryQuery.OrderBy(orderBy).ToList();
}
}
public List<ActivityRpt.UserActivity> GetUserwiseActivity(vw_ActivityLog alog)
{
List<vw_ActivityLog> actList = SearchActivity(activitySortOn, alog);
List<ActivityRpt.UserActivity> usrActLst = new List<ActivityRpt.UserActivity>(actList.Count);
//Get grouped data
var grouped = from al in actList
group al by new { al.UserID, al.UserText } into g
orderby g.Key.UserText
select new { UserID = g.Key.UserID, Email = g.Key.UserText, Count = g.Count() };
//Populate ActivityCount list
foreach (var act in grouped)
usrActLst.Add(new ActivityRpt.UserActivity()
{ UserID = act.UserID, Email = act.Email, Count = act.Count });
return usrActLst;
}
public List<ActivityRpt.MonthlyUserAct> GetMonthlyUserActivity(vw_ActivityLog alog)
{
//Ref: http://stackoverflow.com/questions/482912/sql-group-by-year-month-week-day-hour-sql-vs-procedural-performance
List<vw_ActivityLog> actList = SearchActivity(activitySortOn, alog);
List<ActivityRpt.MonthlyUserAct> actCountLst = new List<ActivityRpt.MonthlyUserAct>(actList.Count);
//Get grouped data
var grouped = from al in actList
// Ref: http://stackoverflow.com/questions/2105208/linq-group-by-month-question
group al by new { al.ActDateTime.Year, al.ActDateTime.Month } into g
orderby g.Key.Year, g.Key.Month
select new { YrMonth = new DateTime(g.Key.Year, g.Key.Month, 1), Count = g.Count() };
//Populate ActivityCount list
foreach (var act in grouped)
actCountLst.Add(new ActivityRpt.MonthlyUserAct() { YrMonth = act.YrMonth, Count = act.Count });
return actCountLst; // append + string.Format(countStr, g.Count()) before rendering view
}