private static void TrainingReport(SpartReports me, ExcelPackage package, NameValueCollection queries)
{
var id = new Guid("574cb2fe-1acc-4e04-919c-030546b0e7bd");
var db = me.db.Value;
DateTime today = DateTime.Today;
var sheet = package.Workbook.Worksheets[1];
var members = db.GetActiveMembers(id, today, "Memberships").OrderBy(f => f.LastName).ThenBy(f => f.FirstName);
var spartCourses = new[] { "OEC", "Avalanche I", "Avalanche II", "MT&R", "MT&R 2" };
var courses = db.TrainingCourses.Where(f => spartCourses.Contains(f.DisplayName) || f.WacRequired > 0);
var spartCourseGuids = new Guid[spartCourses.Length];
for (int i = 0; i < spartCourses.Length; i++)
{
string courseName = spartCourses[i];
spartCourseGuids[i] = courses.Where(f => f.DisplayName == courseName).Select(f => f.Id).SingleOrDefault();
}
int row = 2;
foreach (var member in members)
{
var expires = CompositeTrainingStatus.Compute(member, courses, today);
int col = 1;
sheet.Cells[row, col++].Value = member.LastName;
sheet.Cells[row, col++].Value = member.FirstName;
foreach (var courseId in spartCourseGuids)
{
if (courseId != Guid.Empty)
{
var expire = expires.Expirations[(Guid)courseId];
if (expire.CourseName.StartsWith("Avalanche") && expire.Completed.HasValue)
{
expire.Expires = expire.Completed.Value.AddYears(3);
}
sheet.Cells[row, col].Value = expire.ToString();
}
col++;
}
var now = DateTime.Now;
var yearStart = new DateTime(now.Year, 1, 1);
var lastYear = new DateTime(now.Year - 1, 1, 1);
sheet.Cells[row, col++].Value = member.MissionRosters.Where(f => f.Unit.Id == id && f.TimeIn >= yearStart).Select(f => f.Mission.Id).Distinct().Count();
sheet.Cells[row, col++].Value = member.MissionRosters.Where(f => f.Unit.Id == id && f.TimeIn >= lastYear && f.TimeIn < yearStart).Select(f => f.Mission.Id).Distinct().Count();
row++;
}
sheet.Cells["A:I"].AutoFitColumns();
}
public Stream GetMembershipReport()
{
ExcelFile xl = ExcelService.Create(ExcelFileType.XLS);
var goodList = xl.CreateSheet("Mission Ready");
this.GenerateMissionReadySheets(null, xl, goodList);
var dataSheet = xl.CreateSheet("Member Data");
var interestingCourses = this.db.GetCoreCompetencyCourses();
IQueryable <Member> members = this.db.Members.Include("Addresses", "Memberships", "ComputedAwards.Course").Where(f => f.Memberships.Any(g => g.Status.IsActive && g.EndTime == null));
members = members.OrderBy(f => f.LastName).ThenBy(f => f.FirstName);
// Set column header titles. A static list, followed by a list of "interesting" training courses
var columns = new[] { "DEM", "Lastname", "Firstname", "WAC Card", "Street", "City", "State", "ZIP", "Phone", "Email", "HAM", "Units" }.Union(interestingCourses.Select(f => f.DisplayName)).ToArray();
for (int i = 0; i < columns.Length; i++)
{
dataSheet.CellAt(0, i).SetValue(columns[i]);
dataSheet.CellAt(0, i).SetBold(true);
}
int row = 1;
foreach (var m in members)
{
int col = 0;
dataSheet.CellAt(row, col++).SetValue(m.DEM);
dataSheet.CellAt(row, col++).SetValue(m.LastName);
dataSheet.CellAt(row, col++).SetValue(m.FirstName);
dataSheet.CellAt(row, col++).SetValue(m.WacLevel.ToString());
var address = m.Addresses.OrderBy(f => f.InternalType).FirstOrDefault();
if (address != null)
{
dataSheet.CellAt(row, col++).SetValue(address.Street);
dataSheet.CellAt(row, col++).SetValue(address.City);
dataSheet.CellAt(row, col++).SetValue(address.State);
dataSheet.CellAt(row, col++).SetValue(address.Zip);
}
else
{
col += 4;
}
Action <Member, string> doContact = (member, type) =>
{
var phone = m.ContactNumbers.Where(f => f.Type == type).OrderBy(f => f.Priority).FirstOrDefault();
if (phone != null)
{
dataSheet.CellAt(row, col).SetValue(phone.Value);
}
col++;
};
doContact(m, "phone");
doContact(m, "email");
doContact(m, "hamcall");
dataSheet.CellAt(row, col++).SetValue(string.Join(" ",
m.Memberships.Where(f => f.Status.IsActive && f.EndTime == null).Select(f => f.Unit.DisplayName).OrderBy(f => f)
));
var trainingStatus = CompositeTrainingStatus.Compute(m, interestingCourses, DateTime.Now);
for (int i = 0; i < interestingCourses.Count; i++)
{
dataSheet.CellAt(row, col++).SetValue(trainingStatus.Expirations[interestingCourses[i].Id].ToString());
}
row++;
}
//IQueryable<UnitMembership> memberships = this.db.UnitMemberships.Include("Person.Addresses", "Person.ContactNumbers").Include("Status");
//memberships = memberships.Where(um => um.EndTime == null && um.Status.IsActive);
//memberships = memberships.OrderBy(f => f.Person.LastName).ThenBy(f => f.Person.FirstName);
MemoryStream ms = new MemoryStream();
xl.Save(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
private static void TrainingReport(IstReports me, ExcelPackage package, NameValueCollection queries)
{
Guid id = new Guid("c118ce30-cd28-4635-ba3d-adf7c21358e2");
var db = me.db.Value;
DateTime today = DateTime.Today;
// Take current month and subtract 1 to move to Jan = 0 counting system
// Take away one more so that reports run during the first month of a new quarter report on last quarter.
// Then, convert -1 to 12 with +12,%12
// Divide by 3 months to get the quarter
int quarter = ((today.Month + 10) % 12) / 3;
DateTime quarterStart = new DateTime(today.AddMonths(-1).Year, 1, 1).AddMonths(quarter * 3);
DateTime quarterStop = quarterStart.AddMonths(3);
var sheet = package.Workbook.Worksheets[1];
var members = db.GetActiveMembers(id, today, "ContactNumbers", "Memberships").OrderBy(f => f.LastName).ThenBy(f => f.FirstName);
var istCourses = new[] { "ICS-300", "ICS-400", "ICS-200", "ICS-800" };
var courses = db.TrainingCourses.Where(f => istCourses.Contains(f.DisplayName) || f.WacRequired > 0).OrderBy(f => f.DisplayName);
sheet.Cells[1, 1].Value = today;
sheet.Cells[1, 1].Style.Font.Bold = true;
var headers = new[] { "Last Name", "First Name", "Ham Call", "Card Type", "Status", "Missing Training", "Mission Ready",
string.Format("Q{0} Missions", quarter + 1), string.Format("Q{0} Meetings", quarter + 1),
string.Format("Q{0} Missions", ((quarter + 3) % 4) + 1), string.Format("Q{0} Meetings", ((quarter + 3) % 4) + 1), // "quarter + 3 % 4" is same as "(quarter - 1) + 4 % 4"
string.Format("Q{0} Missions", ((quarter + 2) % 4) + 1), string.Format("Q{0} Meetings", ((quarter + 2) % 4) + 1),
string.Format("Q{0} Missions", ((quarter + 1) % 4) + 1), string.Format("Q{0} Meetings", ((quarter + 1) % 4) + 1) };
for (int i = 0; i < headers.Length; i++)
{
sheet.Cells[2, i + 1].Value = headers[i];
sheet.Cells[2, i + 1].Style.Font.Bold = true;
if (i >= headers.Length - 8)
{
sheet.Column(i + 1).Style.Numberformat.Format = "0";
}
}
int row = 3;
foreach (var member in members)
{
sheet.Cells[row, 1].Value = member.LastName;
sheet.Cells[row, 2].Value = member.FirstName;
sheet.Cells[row, 3].Value = member.ContactNumbers.Where(f => f.Type == "hamcall").Select(f => f.Value).FirstOrDefault();
sheet.Cells[row, 4].Value = member.WacLevel.ToString();
sheet.Cells[row, 5].Value = member.Memberships.Where(f => f.Unit.Id == id && f.EndTime == null).Select(f => f.Status.StatusName).FirstOrDefault();
var expires = CompositeTrainingStatus.Compute(member, courses, today);
List <string> missingCourses = new List <string>();
foreach (var course in courses)
{
if (!expires.Expirations[course.Id].Completed.HasValue)
{
missingCourses.Add(course.DisplayName);
}
}
sheet.Cells[row, 6].Value = string.Join(", ", missingCourses);
var qstart = quarterStart;
var qstop = quarterStop;
for (int i = 0; i < 4; i++)
{
sheet.Cells[row, 8 + i * 2].Value = member.MissionRosters.Where(f => f.Unit.Id == id && f.TimeIn >= qstart && f.TimeIn < qstop).Select(f => f.Mission.Id).Distinct().Count();
var trainingRosters = member.TrainingRosters.Where(f => f.TimeIn >= qstart && f.TimeIn < qstop).ToList();
sheet.Cells[row, 9 + i * 2].Value = trainingRosters.Where(f => Regex.IsMatch(f.Training.Title, "IST .*Meeting.*", RegexOptions.IgnoreCase)).Select(f => f.Training.Id).Distinct().Count();
qstop = qstart;
qstart = qstart.AddMonths(-3);
}
row++;
}
sheet.Cells["A:Z"].AutoFitColumns();
}
/// <summary>
///
/// </summary>
/// <param name="unit"></param>
/// <param name="xl"></param>
/// <param name="goodList"></param>
private void GenerateMissionReadySheets(SarUnit unit, ExcelFile xl, ExcelSheet goodList)
{
IMissionReadyPlugin extension = null;
string longName = this.settings.GroupFullName ?? this.settings.GroupName;
IQueryable <UnitMembership> memberships = this.db.UnitMemberships.Include("Person.ComputedAwards.Course").Include("Status");
if (unit != null)
{
memberships = memberships.Where(um => um.Unit.Id == unit.Id);
longName = unit.LongName;
extension = this.extensions.For <IMissionReadyPlugin>(unit);
}
memberships = memberships.Where(um => um.EndTime == null && um.Status.IsActive);
memberships = memberships.OrderBy(f => f.Person.LastName).ThenBy(f => f.Person.FirstName);
goodList.Header = longName + " Mission Active Roster";
goodList.Footer = DateTime.Now.ToShortDateString();
var courses = (from c in this.db.TrainingCourses where c.WacRequired > 0 select c).OrderBy(x => x.DisplayName).ToList();
int headerIdx = 0;
Action <string> appendHeader = head =>
{
var cell = goodList.CellAt(0, headerIdx++);
cell.SetValue(head);
cell.SetBold(true);
cell.SetTextWrap(true);
};
Action <MissionReadyColumns> insertExtensionHeaders = group =>
{
if (extension == null)
{
return;
}
foreach (var value in extension.GetHeadersAfter(group))
{
appendHeader(value);
}
};
insertExtensionHeaders(MissionReadyColumns.Start);
appendHeader("DEM");
insertExtensionHeaders(MissionReadyColumns.WorkerNumber);
appendHeader("Lastname");
appendHeader("Firstname");
insertExtensionHeaders(MissionReadyColumns.Name);
appendHeader("Card Type");
insertExtensionHeaders(MissionReadyColumns.WorkerType);
foreach (var course in courses)
{
var cell = goodList.CellAt(0, headerIdx++);
cell.SetValue(course.DisplayName);
cell.SetBold(true);
cell.SetTextWrap(true);
}
insertExtensionHeaders(MissionReadyColumns.Courses);
ExcelSheet badList = xl.CopySheet(goodList.Name, "Non-Mission Members");
ExcelSheet nonFieldList = xl.CopySheet(goodList.Name, "Admin Members");
using (SheetAutoFitWrapper good = new SheetAutoFitWrapper(xl, goodList))
{
using (SheetAutoFitWrapper bad = new SheetAutoFitWrapper(xl, badList))
{
using (SheetAutoFitWrapper admin = new SheetAutoFitWrapper(xl, nonFieldList))
{
int idx = 1;
int c = 0;
Guid lastId = Guid.Empty;
foreach (UnitMembership membership in memberships)
{
Member member = membership.Person;
if (member.Id == lastId)
{
continue;
}
lastId = member.Id;
CompositeTrainingStatus stats = CompositeTrainingStatus.Compute(member, courses, DateTime.Now);
SheetAutoFitWrapper wrap = bad;
// If the person isn't supposed to keep up a WAC card, then they're administrative...
if (membership.Status.WacLevel == WacLevel.None)
{
wrap = admin;
}
// If they're current on training and have a DEM card, they're good...
else if (stats.IsGood && member.WacLevel != WacLevel.None)
{
wrap = good;
}
idx = wrap.Sheet.NumRows + 1;
c = 0;
Action <MissionReadyColumns> insertExtensionColumns = group =>
{
if (extension == null)
{
return;
}
foreach (var value in extension.GetColumnsAfter(group, member))
{
wrap.SetCellValue(value, idx, c++);
}
};
insertExtensionColumns(MissionReadyColumns.Start);
wrap.SetCellValue(string.Format("{0:0000}", member.DEM), idx, c++);
insertExtensionColumns(MissionReadyColumns.WorkerNumber);
wrap.SetCellValue(member.LastName, idx, c++);
wrap.SetCellValue(member.FirstName, idx, c++);
insertExtensionColumns(MissionReadyColumns.Name);
ExcelCell cell = wrap.Sheet.CellAt(idx, c);
switch (member.WacLevel)
{
case WacLevel.Field:
cell.SetFillColor(Color.Green);
cell.SetFontColor(Color.White);
break;
case WacLevel.Novice:
cell.SetFillColor(Color.Red);
cell.SetFontColor(Color.White);
break;
case WacLevel.Support:
cell.SetFillColor(Color.Orange);
break;
}
wrap.SetCellValue(member.WacLevel.ToString(), idx, c++);
insertExtensionColumns(MissionReadyColumns.WorkerType);
foreach (var course in courses)
{
TrainingStatus stat = stats.Expirations[course.Id];
if ((stat.Status & ExpirationFlags.Okay) != ExpirationFlags.Okay)
{
wrap.Sheet.CellAt(idx, c).SetFillColor(Color.Pink);
wrap.Sheet.CellAt(idx, c).SetBorderColor(Color.Red);
}
wrap.SetCellValue(stat.ToString(), idx, c);
if (stat.Expires.HasValue)
{
wrap.Sheet.CellAt(idx, c).SetValue(stat.Expires.Value.Date.ToString("yyyy-MM-dd"));
}
c++;
}
insertExtensionColumns(MissionReadyColumns.Courses);
if (wrap == bad)
{
wrap.Sheet.CellAt(idx, c).SetValue(member.ContactNumbers.Where(f => f.Type == "email").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault());
}
insertExtensionColumns(MissionReadyColumns.End);
idx++;
}
admin.Sheet.AutoFitAll();
good.Sheet.AutoFitAll();
bad.Sheet.AutoFitAll();
}
}
}
}
