public async Task <ReportResults> RunReport(AltaskDbContext context, string[] parameters) { var range = GetDateRange(parameters[0]); var toDate = range.Item2 > DateTime.Now ? DateTime.Now : range.Item2; var options = new ProjectToOptions() { FromDate = range.Item1, ToDate = toDate }; var tasks = await Helper.TaskInstance.Create(context, options); var records = tasks .Where(t => (!t.Completed.HasValue || !t.Completed.Value) && t.Date <toDate && t.Date> range.Item1) .Select(task => new { Task = task.Name, AssetId = task.Asset.Id, Asset = task.Asset.Name, CustomId = task.Asset.CustomId, Serial = task.Asset.Serial, Department = task.Asset.Department.Name, DueDate = task.Date.ToString("MM/dd/yyyy"), DueTime = task.Date.ToString("hh:mm tt") }) .Cast <object>() .ToList(); return(new ReportResults() { Columns = new List <string> { "Task", "AssetId", "Asset", "CustomId", "Serial", "Department", "DueDate", "DueTime" }, Records = records }); }
protected async Task <ReportResults> RunStoredProcedure(AltaskDbContext context, string storedProcedure) { using (var command = new SqlCommand(storedProcedure, context.Database.Connection as SqlConnection)) { bool closeConnection = false; if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); closeConnection = true; } command.CommandTimeout = 300; command.CommandType = System.Data.CommandType.StoredProcedure; var reader = await command.ExecuteReaderAsync(); var columns = new List <string>(); var records = new List <object>(); for (int index = 0; index < reader.FieldCount; index++) { columns.Add(reader.GetName(index)); } while (await reader.ReadAsync()) { var record = new ExpandoObject(); var dictionary = record as IDictionary <string, object>; foreach (var column in columns) { dictionary.Add(column, reader[column].ToString()); } records.Add(record); } if (closeConnection) { command.Connection.Close(); } return(new ReportResults { Columns = columns, Records = records }); } }
public override Task <ReportResults> RunReport(AltaskDbContext context, string[] parameters) { return(RunStoredProcedure(context, "[dbo].[ReportNotes]")); }
public abstract Task <ReportResults> RunReport(AltaskDbContext context, string[] parameters);
public static async Task <List <Models.TaskInstance> > Create(AltaskDbContext context, ProjectToOptions filter) { if (filter == null) { filter = new ProjectToOptions(); } var fromDate = filter.FromDate; var toDate = filter.ToDate; var instances = new List <Models.TaskInstance>(); if (fromDate <= DateTime.Now) { filter.ToDate = DateTime.Now; var pastOccurrences = await context.Occurrences.AsNoTracking().Where(filter.GetOccurrencePredicate()) .Include(e => e.Task) .Include(e => e.Schedule) .ToListAsync(); foreach (var occurrence in pastOccurrences) { var instance = Altask.www.Models.TaskInstance.FromSchedule(occurrence.Task, occurrence.Date, occurrence.Schedule); instance.MergeOccurrence(occurrence); instances.Add(instance); } filter.FromDate = DateTime.Now; } filter.ToDate = toDate; var tasks = await context.Tasks.AsNoTracking().Where(filter.GetTaskPredicate()) .Include(e => e.Form) .Include(e => e.Schedules) .ToListAsync(); var occurrences = await context.Occurrences.AsNoTracking().Where(filter.GetOccurrencePredicate()).ToListAsync(); var userIds = filter.GetUserIds(); var assetIds = filter.GetAssetIds(); foreach (var task in tasks) { foreach (var schedule in task.Schedules) { var dtoSchedule = schedule.ToDto(); var dates = dtoSchedule.GetRunDates(filter.FromDate, filter.ToDate); foreach (var date in dates) { if (dtoSchedule.Assets.Count > 0) { foreach (var asset in dtoSchedule.Assets) { if (assetIds.Count > 0 && !assetIds.Contains(asset.AssetId)) { continue; } if (dtoSchedule.Users.Count > 0) { foreach (var user in dtoSchedule.Users) { if (userIds.Count > 0 && !userIds.Contains(user.UserId)) { continue; } var instance = Models.TaskInstance.FromSchedule(task, date, schedule); var occurrence = occurrences.SingleOrDefault(o => o.ScheduleId == schedule.Id && o.AssetId == asset.AssetId && o.UserId == user.Id && o.Date == date); if (occurrence != null) { instance.MergeOccurrence(occurrence); } else { instance.AssetId = asset.AssetId; instance.Asset = asset.Asset; instance.UserId = user.UserId; instance.User = user.User; } instances.Add(instance); } } else { var instance = Models.TaskInstance.FromSchedule(task, date, schedule); var occurrence = occurrences.SingleOrDefault(o => o.ScheduleId == schedule.Id && o.AssetId == asset.AssetId && o.Date == date); if (occurrence != null) { instance.MergeOccurrence(occurrence); } else { instance.AssetId = asset.AssetId; instance.Asset = asset.Asset; } instances.Add(instance); } } } else { foreach (var user in dtoSchedule.Users) { if (userIds.Count > 0 && !userIds.Contains(user.UserId)) { continue; } var instance = Models.TaskInstance.FromSchedule(task, date, schedule); var occurrence = occurrences.SingleOrDefault(o => o.ScheduleId == schedule.Id && o.UserId == user.UserId && o.Date == date); if (occurrence != null) { instance.MergeOccurrence(occurrence); } else { instance.UserId = user.UserId; instance.User = user.User; } instances.Add(instance); } } } } } return(instances); }