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