private static void UnitRoster(SmrReports me, ExcelPackage package, NameValueCollection queries)
{
var sheet = package.Workbook.Worksheets[1];
var memberships = me.db.Value.Units
.Where(f => f.DisplayName == "SMR")
.SelectMany(f => f.Memberships.Where(g => (g.EndTime == null || g.EndTime > DateTime.Now) && g.Status.IsActive));
var members = memberships
.Select(f => f.Person);
var status = memberships.ToDictionary(f => f.Person.Id, f => new { f.Status.StatusName, f.Activated });
var contacts = members.SelectMany(f => f.ContactNumbers).GroupBy(f => f.Person.Id).ToDictionary(f => f.Key, f => f.ToArray());
var addresses = members.SelectMany(f => f.Addresses).GroupBy(f => f.Person.Id).ToDictionary(f => f.Key, f => f.ToArray());
var roster = members.OrderBy(f => f.LastName).ThenBy(f => f.FirstName).ThenBy(f => f.Id).ToArray();
var oldDate = new DateTime(1930, 1, 1);
for (int i = 0; i < roster.Length; i++)
{
var memberId = roster[i].Id;
var col = 1;
var row = i + 4;
sheet.Cells[row, col++].Value = roster[i].LastName;
sheet.Cells[row, col++].Value = roster[i].FirstName;
sheet.Cells[row, col++].Value = roster[i].DEM;
sheet.Cells[row, col++].Value = roster[i].WacLevel == WacLevel.None ? null : roster[i].WacLevel.ToString();
sheet.Cells[row, col++].Value = status[memberId].StatusName;
sheet.Cells[row, col++].Value = contacts.ContainsKey(memberId) ? contacts[memberId].Where(f => f.Type == "phone" && f.Subtype == "cell").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault() : null;
sheet.Cells[row, col++].Value = contacts.ContainsKey(memberId) ? contacts[memberId].Where(f => f.Type == "phone" && f.Subtype == "home").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault() : null;
sheet.Cells[row, col++].Value = contacts.ContainsKey(memberId) ? contacts[memberId].Where(f => f.Type == "phone" && f.Subtype == "work").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault() : null;
sheet.Cells[row, col++].Value = contacts.ContainsKey(memberId) ? contacts[memberId].Where(f => f.Type == "email").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault() : null;
sheet.Cells[row, col++].Value = contacts.ContainsKey(memberId) ? contacts[memberId].Where(f => f.Type == "hamcall").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault() : null;
var address = addresses.ContainsKey(memberId) ? addresses[memberId].OrderBy(f => f.Type).FirstOrDefault() : null;
if (address != null)
{
sheet.Cells[row, col++].Value = address.Street;
sheet.Cells[row, col++].Value = address.City;
sheet.Cells[row, col++].Value = address.State;
sheet.Cells[row, col++].Value = address.Zip;
}
else
{
col += 4;
}
sheet.Cells[row, col++].Value = status[memberId].Activated > oldDate ? status[memberId].Activated : (DateTime?)null;
}
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
private static void TrainingList(SmrReports me, ExcelPackage package, NameValueCollection queries)
{
var sheet = package.Workbook.Worksheets[1];
// Find trainings where SMR either hosted, or it was not hosted and an SMR member attended.
var trainings = me.db.Value.TrainingRosters.Where(
f => (f.Training.HostUnits.Any(g => g.DisplayName == "SMR") || f.Training.HostUnits.Count == 0) &&
f.Person.Memberships.Any(h =>
h.Status.IsActive &&
h.Unit.DisplayName == "SMR" &&
h.Activated <= f.Training.StartTime &&
(h.EndTime == null || h.EndTime > f.Training.StartTime)))
.GroupBy(f => f.Training)
.Select(f => new {
f.Key.StartTime,
f.Key.Title,
f.Key.Location,
f.Key.StateNumber,
Persons = f.Select(g => g.Person.Id).Distinct().Count(),
Hours = f.Sum(g => (g.TimeOut.HasValue && g.TimeIn.HasValue) ? SqlFunctions.DateDiff("minute", g.TimeIn, g.TimeOut) / 60.0 : 0.0),
Miles = f.Sum(g => g.Miles)
})
.OrderByDescending(f => f.StartTime)
.ToArray();
for (int i = 0; i < trainings.Length; i++)
{
var col = 1;
var row = i + 4;
sheet.Cells[row, col++].Value = trainings[i].StartTime;
sheet.Cells[row, col++].Value = trainings[i].StateNumber;
sheet.Cells[row, col++].Value = string.Format("{0} - {1}", trainings[i].Title, trainings[i].Location);
sheet.Cells[row, col++].Value = string.Empty;
sheet.Cells[row, col++].Value = trainings[i].Persons;
sheet.Cells[row, col++].Value = trainings[i].Hours;
sheet.Cells[row, col++].Value = trainings[i].Miles;
}
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
private static IQueryable <MissionRoster> GetMissionRostersQuery(SmrReports me, DateTime start, DateTime stop)
{
return(me.db.Value.MissionRosters.Where(f => f.Unit.DisplayName == "SMR" && f.Mission.StartTime >= start && f.Mission.StartTime < stop));
}
private static void MissionList(SmrReports me, ExcelPackage package, NameValueCollection entries)
{
var sheet = package.Workbook.Worksheets[1];
int year;
if (!int.TryParse(entries["year"], out year))
{
year = DateTime.Now.AddMonths(-2).Year;
}
DateTime start = new DateTime(year, 1, 1);
DateTime stop = new DateTime(year + 1, 1, 1);
IQueryable <MissionRoster> rosters = GetMissionRostersQuery(me, start, stop);
MissionInfo[] missions = GetMissions(rosters);
var stats = rosters.GroupBy(f => f.Mission.Id)
.Select(f => new
{
Id = f.Key,
Persons = f.Select(g => g.Person.Id).Distinct().Count(),
Hours = f.Sum(g => SqlFunctions.DateDiff("minute", g.TimeIn, g.TimeOut) / 60.0),
Miles = f.Sum(g => g.Miles)
})
.ToDictionary(f => f.Id, f => f);
var total = rosters
.GroupBy(f => 1)
.Select(f => new
{
Persons = f.Select(g => g.Person.Id).Distinct().Count(),
Hours = f.Sum(g => SqlFunctions.DateDiff("minute", g.TimeIn, g.TimeOut) / 60.0),
Miles = f.Sum(g => g.Miles)
})
.Single();
sheet.Cells[1, 1].Value += string.Format(" ({0})", year);
for (int i = 0; i < missions.Length; i++)
{
var col = 1;
var row = i + 4;
sheet.Cells[row, col++].Value = missions[i].StartTime;
sheet.Cells[row, col++].Value = missions[i].StateNumber;
sheet.Cells[row, col++].Value = string.Format("{0}{1}", missions[i].Title, (missions[i].MissionType ?? string.Empty).Contains("turnaround") ? " (Turnaround)" : string.Empty);
sheet.Cells[row, col++].Value = stats[missions[i].Id].Persons;
sheet.Cells[row, col++].Value = stats[missions[i].Id].Hours;
sheet.Cells[row, col++].Value = stats[missions[i].Id].Miles;
}
sheet.Row(missions.Length + 4).Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Row(missions.Length + 4).Style.Border.Top.Color.SetColor(System.Drawing.Color.FromArgb(0, 76, 154));
sheet.Row(missions.Length + 4).Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 76, 154));
sheet.Row(missions.Length + 4).Style.Font.Bold = true;
sheet.Cells[missions.Length + 4, 3].Value = "Total Missions = " + missions.Length;
sheet.Cells[missions.Length + 4, 4].Value = total.Persons;
sheet.Cells[missions.Length + 4, 5].Value = total.Hours;
sheet.Cells[missions.Length + 4, 6].Value = total.Miles;
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
private static void MissionRosters(SmrReports me, ExcelPackage package, NameValueCollection entries)
{
var sheet = package.Workbook.Worksheets[1];
int year;
if (!int.TryParse(entries["year"], out year))
{
year = DateTime.Now.AddMonths(-2).Year;
}
DateTime start = new DateTime(year, 1, 1);
DateTime stop = new DateTime(year + 1, 1, 1);
IQueryable <MissionRoster> rosterQuery = GetMissionRostersQuery(me, start, stop);
MissionInfo[] missions = GetMissions(rosterQuery);
var rosters = rosterQuery
.GroupBy(f => f.Mission.Id)
.ToDictionary(
f => f.Key,
f => f.GroupBy(g => new { g.Person.DEM, g.Person.LastName, g.Person.FirstName, g.Person.Id, g.InternalRole })
.ToDictionary(
g => g.Key,
g => new
{
Hours = g.Sum(h => (h.TimeOut - h.TimeIn)?.TotalHours /*SqlFunctions.DateDiff("minute", h.TimeIn, h.TimeOut) / 60.0*/),
Miles = g.Sum(h => h.Miles)
}));
sheet.Cells[1, 1].Value += string.Format(" ({0})", year);
var row = 4;
for (int i = 0; i < missions.Length; i++)
{
sheet.Cells[row, 1].Value = string.Format("{0:yyyy-MM-dd} ({1}) {2}{3}", missions[i].StartTime, missions[i].StateNumber, missions[i].Title, (missions[i].MissionType ?? string.Empty).Contains("turnaround") ? " (Turnaround)" : string.Empty);
sheet.Row(row).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
sheet.Row(row).Merged = true;
sheet.Row(row).Style.Font.Bold = true;
sheet.Row(row).Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Row(row).Style.Border.Top.Color.SetColor(Color.FromArgb(0, 76, 154));
row++;
var roster = rosters[missions[i].Id];
foreach (var r in roster.OrderBy(f => f.Key.LastName).ThenBy(f => f.Key.FirstName).ThenBy(f => f.Key.Id))
{
sheet.Cells[row, 1].Value = r.Key.DEM;
sheet.Cells[row, 2].Value = string.Format("{0}, {1}", r.Key.LastName, r.Key.FirstName);
sheet.Cells[row, 3].Value = r.Key.InternalRole;
sheet.Cells[row, 4].Value = r.Value.Hours;
sheet.Cells[row, 5].Value = r.Value.Miles;
row++;
}
sheet.Cells[row, 2].Value = "Total Personnel = " + roster.Select(f => f.Key.Id).Distinct().Count();
sheet.Cells[row, 4].Value = roster.Sum(f => f.Value.Hours);
sheet.Cells[row, 5].Value = roster.Sum(f => f.Value.Miles);
sheet.Row(row).Style.Font.Color.SetColor(Color.FromArgb(0, 76, 154));
sheet.Row(row).Height *= 1.75;
sheet.Row(row).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
row++;
}
}
private static void FieldSummary(SmrReports me, ExcelPackage package, NameValueCollection queries)
{
var sheet = package.Workbook.Worksheets[1];
var memberships = me.db.Value.Units
.Where(f => f.DisplayName == "SMR")
.SelectMany(f => f.Memberships.Where(g => (g.EndTime == null || g.EndTime > DateTime.Now) && g.Status.IsActive && g.Status.WacLevel != WacLevel.None));
var members = memberships
.Select(f => f.Person);
var memberHistory = members.ToDictionary(f => f.Id, f => f.Memberships.Where(g => g.Status.IsActive && g.Unit.DisplayName == "SMR").OrderByDescending(g => g.Activated).ToList());
var currentTraining = members.SelectMany(f => f.ComputedAwards)
.GroupBy(f => f.Member.Id)
.ToDictionary(f => f.Key, f => f.Select(g => new { g.Course.DisplayName, g.NullableCompleted }).ToArray());
var today = DateTime.Today;
var oneYear = today.AddYears(-1);
var threeYears = today.AddYears(-3);
var recentMissions = members.SelectMany(f => f.MissionRosters)
.Where(f => f.Mission.StartTime > threeYears && f.Unit.DisplayName == "SMR")
.GroupBy(f => f.Person.Id, f => f)
.ToDictionary(
f => f.Key,
f => f.GroupBy(g => g.Mission.StartTime, g => g)
.ToDictionary(g => g.Key, g => new
{
Hours = g.Sum(h => h.TimeOut.HasValue ? (h.TimeOut.Value - h.TimeIn.Value).TotalHours : (double?)null),
Count = g.Select(h => h.Mission.Id).Distinct().Count()
}));
var recentTrainings = members.SelectMany(f => f.TrainingRosters)
.Where(f => f.Training.StartTime > threeYears)
.GroupBy(f => f.Person.Id, f => f)
.ToDictionary(
f => f.Key,
f => f.GroupBy(g => g.Training.StartTime, g => g)
.ToDictionary(g => g.Key, g => new
{
Hours = g.Sum(h => h.TimeOut.HasValue ? (h.TimeOut.Value - h.TimeIn.Value).TotalHours : (double?)null),
Count = g.Select(h => h.Training.Id).Distinct().Count()
}));
var recentRigging = members.SelectMany(f => f.TrainingRosters)
.Where(f => f.Training.StartTime > threeYears && f.TrainingAwards.Any(g => g.Course.DisplayName == "SMR Rigging Refresher"))
.GroupBy(f => f.Person.Id, f => f)
.ToDictionary(
f => f.Key,
f => f.GroupBy(g => g.Training.StartTime, g => g)
.ToDictionary(g => g.Key, g => new
{
Hours = g.Sum(h => h.TimeOut.HasValue ? (h.TimeOut.Value - h.TimeIn.Value).TotalHours : (double?)null),
Count = g.Select(h => h.Training.Id).Distinct().Count()
}));
var status = memberships.ToDictionary(f => f.Person.Id, f => new { f.Status.StatusName, f.Activated });
var roster = members.OrderBy(f => f.LastName).ThenBy(f => f.FirstName).ThenBy(f => f.Id).ToArray();
var oldDate = new DateTime(1930, 1, 1);
for (int i = 0; i < roster.Length; i++)
{
var memberId = roster[i].Id;
var col = 1;
var row = i + 4;
var joinDate = (DateTime?)null;
var joinHistory = memberHistory[memberId];
for (int j = 0; j < joinHistory.Count; j++)
{
try
{
if (j == 0 || joinDate.Value.Date == joinHistory[j].EndTime.Value.Date)
{
joinDate = joinHistory[j].Activated;
}
else
{
break;
}
}
catch (Exception e)
{
log4net.LogManager.GetLogger("debug").Error($"memberId:{memberId}, j={j}, joinDate={joinDate}, history={joinHistory[j]}");
}
}
sheet.Cells[row, col++].Value = roster[i].DEM;
sheet.Cells[row, col++].Value = roster[i].ReverseName;
sheet.Cells[row, col++].Value = joinDate > oldDate ? joinDate : (DateTime?)null;
sheet.Cells[row, col++].Value = roster[i].WacLevel == WacLevel.None ? null : roster[i].WacLevel.ToString();
sheet.Cells[row, col++].Value = status[memberId].StatusName;
col++; // MRA
if (currentTraining.ContainsKey(memberId) && currentTraining[memberId] != null)
{
sheet.Cells[row, col++].Value = currentTraining[memberId].Any(f => f.DisplayName == "Avalanche III") ? "Avy III"
: currentTraining[memberId].Any(f => f.DisplayName == "Avalanche II") ? "Avy II"
: currentTraining[memberId].Any(f => f.DisplayName == "Avalanche I") ? "Avy I"
: null;
sheet.Cells[row, col++].Value = currentTraining[memberId].Any(f => f.DisplayName == "SMR Snowmobile II") ? "Advanced"
: currentTraining[memberId].Any(f => f.DisplayName == "SMR Snowmobile") ? "Basic"
: null;
}
else
{
col += 2;
}
sheet.Cells[row, col++].Value = recentTrainings.ContainsKey(memberId) ? recentTrainings[memberId].Sum(f => f.Value.Count) : (int?)null;
sheet.Cells[row, col++].Value = recentMissions.ContainsKey(memberId) ? recentMissions[memberId].Where(f => f.Key > oneYear).Sum(f => f.Value.Count) : (int?)null;
sheet.Cells[row, col++].Value = recentMissions.ContainsKey(memberId) ? recentMissions[memberId].Sum(f => f.Value.Count) : (int?)null;
sheet.Cells[row, col++].Value = recentMissions.ContainsKey(memberId) ? (int?)recentMissions[memberId].Sum(f => f.Value.Hours) : null;
sheet.Cells[row, col++].Value = recentRigging.ContainsKey(memberId) ? (int?)recentRigging[memberId].Where(f => f.Key > oneYear).Sum(f => f.Value.Hours) : null;
sheet.Cells[row, col++].Value = recentRigging.ContainsKey(memberId) ? (int?)recentRigging[memberId].Sum(f => f.Value.Hours) : null;
sheet.Cells[row, col++].Value = recentTrainings.ContainsKey(memberId) ? (int?)recentTrainings[memberId].Sum(f => f.Value.Hours) : null;
}
sheet.Cells["A:C"].AutoFitColumns();
sheet.Column(3).Width += 2;
}
