public ActionResult History(DataTableParamModel request) { var query = (from source in context.DataSources join fileDataSource in context.Set<FileDataSource>() on source.Id equals fileDataSource.Id into filejoin from subFileSource in filejoin.DefaultIfEmpty() join feedDataSource in context.Set<FeedDataSource>() on source.Id equals feedDataSource.Id into fjn from subFeedDataSource in fjn.DefaultIfEmpty(null) join manualDataSource in context.Set<ManualDataSource>() on source.Id equals manualDataSource.Id into manualjoin from subManualSource in manualjoin.DefaultIfEmpty() select new InputHistoryDataModel { Name = (subFileSource != null) ? subFileSource.OriginalFileName : (subFeedDataSource == null) ? "Manual Entry" : subFeedDataSource.SourceUrl, EditDate = source.DateEdit, UserName = source.UserName, Type = (subManualSource != null) ? subManualSource.DisplayType : (subFileSource != null) ? subFileSource.HandlerName : subFeedDataSource.HandlerName, Status = source.InputStatus, Id = source.Id }); var builder = new DataTableResultModelBuilder<InputHistoryDataModel>(); builder.AddQueryable(query); builder.AddDataExpression(arg => new object[] { HttpUtility.HtmlEncode(arg.Name), arg.EditDate.ToString(Constants.Constants.DateFormat), HttpUtility.HtmlEncode(arg.UserName), arg.Type, Enum.GetName(typeof (SourceStatus), arg.Status), arg.Id.ToString(), Url.RouteUrl("editsource", new {SourceId = arg.Id}), Url.Action("SelectSource", new {SourceId = arg.Id}) }); SourceStatus status; if (Enum.TryParse(request.sSearch, true, out status)) { builder.AddSearchFilter(model => model.Status == status); } else { builder.AddSearchFilter(arg => arg.Name.Contains(request.sSearch) || arg.UserName.Contains(request.sSearch) || arg.Type.Contains(request.sSearch)); } builder.AddSortExpression(data => data.Name); builder.AddSortExpression(data => data.EditDate); builder.AddSortExpression(data => data.UserName); builder.AddSortExpression(data => data.Type); builder.AddSortExpression(data => data.Status); var result = builder.BuildResult(request); return Json(result, JsonRequestBehavior.AllowGet); }
public ActionResult AuditHistory( DateTime startDate, DateTime endDate, string costCode, Guid?activityGroupId, DataTableParamModel request) { var builder = new DataTableResultModelBuilder <AuditHistory>(); var activityNode = (activityGroupId == null) ? new HierarchyId("/") : context.ActivityGroups.Find(activityGroupId).Node; var costCentreNode = context.CostCentres.Find(costCode).Node; var query = from e in context.CarbonEmissionEntries where (e.EntryDate >= startDate) && (e.EntryDate <= endDate) && (e.ActivityGroupNode.IsDescendantOf(activityNode)) && (e.CostCentreNode.IsDescendantOf(costCentreNode)) group new { e.Units, e.Money, e.CarbonEmissions } by e.SourceEntry.SourceId into g from source in context.DataSources join fileDataSource in context.Set <FileDataSource>() on source.Id equals fileDataSource.Id into filejoin from subFileSource in filejoin.DefaultIfEmpty() join manualDataSource in context.Set <ManualDataSource>() on source.Id equals manualDataSource.Id into manualjoin from subManualSource in manualjoin.DefaultIfEmpty() join feedDataSource in context.Set <FeedDataSource>() on source.Id equals feedDataSource.Id into fjn from subFeedDataSource in fjn.DefaultIfEmpty(null) where source.Id == g.Key select new AuditHistory { CurrentFileName = (subFileSource != null) ? subFileSource.CurrentFileName : (subFeedDataSource == null) ? null : subFeedDataSource.SourceUrl, Name = (subFileSource == null) ? "Manual Entry" : subFileSource.OriginalFileName, DateEdit = source.DateEdit, UserName = source.UserName, HandlerName = (subManualSource != null) ? subManualSource.DisplayType : (subFileSource == null) ? subFeedDataSource.HandlerName : subFileSource.HandlerName, Emissions = g.Sum(arg => arg.CarbonEmissions) / 1000, Cost = g.Sum(arg => arg.Money), Units = g.Sum(arg => arg.Units), SourceId = g.Key }; builder.AddQueryable(query); builder.AddDataExpression(arg => new object[] { HttpUtility.HtmlEncode(arg.Name), arg.DateEdit.ToString(Constants.Constants.DateFormat), HttpUtility.HtmlEncode(arg.UserName), arg.Units.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.Emissions.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.Cost.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.HandlerName, Url.RouteUrl("editsource", new { arg.SourceId }), Url.RouteUrl("selectsource", new { arg.SourceId }) }); builder.AddSearchFilter(arg => arg.Name.Contains(request.sSearch) || arg.UserName.Contains(request.sSearch) || arg.HandlerName.Contains(request.sSearch)); builder.AddSortExpression(data => data.Name); builder.AddSortExpression(data => data.DateEdit); builder.AddSortExpression(data => data.UserName); builder.AddSortExpression(data => data.Units); builder.AddSortExpression(data => data.Emissions); builder.AddSortExpression(data => data.Cost); builder.AddSortExpression(data => data.HandlerName); var result = builder.BuildResult(request); return(Json(result, JsonRequestBehavior.AllowGet)); }
public ActionResult History(DataTableParamModel request) { var query = (from source in context.DataSources join fileDataSource in context.Set <FileDataSource>() on source.Id equals fileDataSource.Id into filejoin from subFileSource in filejoin.DefaultIfEmpty() join feedDataSource in context.Set <FeedDataSource>() on source.Id equals feedDataSource.Id into fjn from subFeedDataSource in fjn.DefaultIfEmpty(null) join manualDataSource in context.Set <ManualDataSource>() on source.Id equals manualDataSource.Id into manualjoin from subManualSource in manualjoin.DefaultIfEmpty() select new InputHistoryDataModel { Name = (subFileSource != null) ? subFileSource.OriginalFileName : (subFeedDataSource == null) ? "Manual Entry" : subFeedDataSource.SourceUrl, EditDate = source.DateEdit, UserName = source.UserName, Type = (subManualSource != null) ? subManualSource.DisplayType : (subFileSource != null) ? subFileSource.HandlerName : subFeedDataSource.HandlerName, Status = source.InputStatus, Id = source.Id }); var builder = new DataTableResultModelBuilder <InputHistoryDataModel>(); builder.AddQueryable(query); builder.AddDataExpression(arg => new object[] { HttpUtility.HtmlEncode(arg.Name), arg.EditDate.ToString(Constants.Constants.DateFormat), HttpUtility.HtmlEncode(arg.UserName), arg.Type, Enum.GetName(typeof(SourceStatus), arg.Status), arg.Id.ToString(), Url.RouteUrl("editsource", new { SourceId = arg.Id }), Url.Action("SelectSource", new { SourceId = arg.Id }) }); SourceStatus status; if (Enum.TryParse(request.sSearch, true, out status)) { builder.AddSearchFilter(model => model.Status == status); } else { builder.AddSearchFilter(arg => arg.Name.Contains(request.sSearch) || arg.UserName.Contains(request.sSearch) || arg.Type.Contains(request.sSearch)); } builder.AddSortExpression(data => data.Name); builder.AddSortExpression(data => data.EditDate); builder.AddSortExpression(data => data.UserName); builder.AddSortExpression(data => data.Type); builder.AddSortExpression(data => data.Status); var result = builder.BuildResult(request); return(Json(result, JsonRequestBehavior.AllowGet)); }
public ActionResult AuditHistory( DateTime startDate, DateTime endDate, string costCode, Guid? activityGroupId, DataTableParamModel request) { var builder = new DataTableResultModelBuilder<AuditHistory>(); var activityNode = (activityGroupId == null) ? new HierarchyId("/") : context.ActivityGroups.Find(activityGroupId).Node; var costCentreNode = context.CostCentres.Find(costCode).Node; var query = from e in context.CarbonEmissionEntries where (e.EntryDate >= startDate) && (e.EntryDate <= endDate) && (e.ActivityGroupNode.IsDescendantOf(activityNode)) && (e.CostCentreNode.IsDescendantOf(costCentreNode)) group new { e.Units, e.Money, e.CarbonEmissions } by e.SourceEntry.SourceId into g from source in context.DataSources join fileDataSource in context.Set<FileDataSource>() on source.Id equals fileDataSource.Id into filejoin from subFileSource in filejoin.DefaultIfEmpty() join manualDataSource in context.Set<ManualDataSource>() on source.Id equals manualDataSource.Id into manualjoin from subManualSource in manualjoin.DefaultIfEmpty() join feedDataSource in context.Set<FeedDataSource>() on source.Id equals feedDataSource.Id into fjn from subFeedDataSource in fjn.DefaultIfEmpty(null) where source.Id == g.Key select new AuditHistory { CurrentFileName = (subFileSource != null) ? subFileSource.CurrentFileName : (subFeedDataSource == null) ? null : subFeedDataSource.SourceUrl, Name = (subFileSource == null) ? "Manual Entry" : subFileSource.OriginalFileName, DateEdit = source.DateEdit, UserName = source.UserName, HandlerName = (subManualSource != null) ? subManualSource.DisplayType : (subFileSource == null) ? subFeedDataSource.HandlerName : subFileSource.HandlerName, Emissions = g.Sum(arg => arg.CarbonEmissions)/1000, Cost = g.Sum(arg => arg.Money), Units = g.Sum(arg => arg.Units), SourceId = g.Key }; builder.AddQueryable(query); builder.AddDataExpression(arg => new object[] { HttpUtility.HtmlEncode(arg.Name), arg.DateEdit.ToString(Constants.Constants.DateFormat), HttpUtility.HtmlEncode(arg.UserName), arg.Units.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.Emissions.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.Cost.ToString(Constants.Constants.NumberFormat, CultureInfo.CurrentCulture), arg.HandlerName, Url.RouteUrl("editsource", new {arg.SourceId}), Url.RouteUrl("selectsource", new {arg.SourceId}) }); builder.AddSearchFilter(arg => arg.Name.Contains(request.sSearch) || arg.UserName.Contains(request.sSearch) || arg.HandlerName.Contains(request.sSearch)); builder.AddSortExpression(data => data.Name); builder.AddSortExpression(data => data.DateEdit); builder.AddSortExpression(data => data.UserName); builder.AddSortExpression(data => data.Units); builder.AddSortExpression(data => data.Emissions); builder.AddSortExpression(data => data.Cost); builder.AddSortExpression(data => data.HandlerName); var result = builder.BuildResult(request); return Json(result, JsonRequestBehavior.AllowGet); }
public ActionResult RefrigerantData(DataTableParamModel request, Guid sourceId) { var builder = new DataTableResultModelBuilder<RefrigerantData>(); builder.AddQueryable(context.Set<RefrigerantData>() .Where(data => data.SourceId == sourceId)); var columnIndex = new List<string> { "", "", "", "StartDate", "EndDate", "CostCode", "Money", "Units", "RefrigerantType", }; builder.AddDataExpression(data => new object[] { data.Id.ToString(), data.Errors.Select(error => new { error.Column, error.Message, error.ErrorType, index = columnIndex.IndexOf(error.Column) }).ToArray(), ConvertToString(data.RowNo), ConvertToString(data.StartDate), ConvertToString(data.EndDate), ConvertToString(data.CostCode), ConvertToString(data.Money), ConvertToString(data.Units), ConvertToString(data.RefrigerantType), }); var searchSet = false; int numeric; DataErrorType errorType; if (!(int.TryParse(request.sSearch, out numeric)) && Enum.TryParse(request.sSearch, true, out errorType)) { searchSet = true; builder.AddSearchFilter(data => (data.Errors.Any(error => error.ErrorType == errorType))); } if(string.Equals("AllErrors",request.sSearch,StringComparison.InvariantCultureIgnoreCase)){ searchSet = true; builder.AddSearchFilter(data => data.Errors.Any()); } var refrigeranttype = TryParser.Nullable<RefrigerantType>(request.sSearch); if((!searchSet) && (refrigeranttype !=null)) { builder.AddSearchFilter(data => data.RefrigerantType == refrigeranttype); } DateTime searchDate; if ((!searchSet) && (DateTime.TryParse(request.sSearch,CultureInfo.CurrentCulture,DateTimeStyles.None, out searchDate))) { builder.AddSearchFilter(data => (data.StartDate == searchDate) || (data.EndDate == searchDate)); } int searchRow; if ((!searchSet) && (int.TryParse(request.sSearch, out searchRow))) { builder.AddSearchFilter(data =>(data.RowNo == searchRow)); } decimal amount; if ((!searchSet) && (decimal.TryParse(request.sSearch, out amount))) { builder.AddSearchFilter(data => (data.Money == amount) || (data.Units == amount)); } if (!searchSet) { builder.AddSearchFilter(data => data.CostCode.Contains(request.sSearch)); } builder.AddSortExpression(data => data.Id); builder.AddSortExpression(data => data.Id); builder.AddSortExpression(data => data.RowNo); builder.AddSortExpression(data => data.StartDate); builder.AddSortExpression(data => data.EndDate); builder.AddSortExpression(data => data.CostCode); builder.AddSortExpression(data => data.Money); builder.AddSortExpression(data => data.Units); builder.AddSortExpression(data => data.RefrigerantType); var result = builder.BuildResult(request); return Json(result, JsonRequestBehavior.AllowGet); }