/// <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();
}
}
}
}
public ActionResult DownloadRoster(Guid? id, bool? includeHidden)
{
// The method almost supports id=null as downloading the KCSARA roster
// It doesn't do a distinct(person), so people in multiple units are recorded more than once.
ExcelFile xl;
using (FileStream fs = new FileStream(Server.MapPath(Url.Content("~/Content/roster-template.xls")), FileMode.Open, FileAccess.Read))
{
xl = ExcelService.Read(fs, ExcelFileType.XLS);
}
ExcelSheet ws = xl.GetSheet(0);
string filename = string.Format("roster-{0:yyMMdd}.xls", DateTime.Now);
IQueryable<UnitMembership> memberships = this.db.UnitMemberships.Include("Person").Include("Person.Addresses").Include("Person.ContactNumbers").Include("Status");
string unitShort = ConfigurationManager.AppSettings["dbNameShort"];
string unitLong = Strings.GroupName;
if (id.HasValue)
{
memberships = memberships.Where(um => um.Unit.Id == id.Value);
SarUnit sarUnit = (from u in this.db.Units where u.Id == id.Value select u).First();
unitShort = sarUnit.DisplayName;
unitLong = sarUnit.LongName;
}
memberships = memberships.Where(um => um.EndTime == null && um.Status.IsActive);
memberships = memberships.OrderBy(f => f.Person.LastName).ThenBy(f => f.Person.FirstName);
ws.Header = unitLong + " Active Roster";
ws.Footer = DateTime.Now.ToShortDateString();
filename = unitShort + "-" + filename;
using (SheetAutoFitWrapper wrap = new SheetAutoFitWrapper(xl, ws))
{
int idx = 1;
int c = 0;
foreach (UnitMembership membership in memberships)
{
Member member = membership.Person;
c = 0;
wrap.SetCellValue(string.Format("{0:0000}", member.DEM), idx, c++);
wrap.SetCellValue(member.LastName, idx, c++);
wrap.SetCellValue(member.FirstName, idx, c++);
wrap.SetCellValue(string.Join("\n", member.Addresses.Select(f => f.Street).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.Addresses.Select(f => f.City).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.Addresses.Select(f => f.State).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.Addresses.Select(f => f.Zip).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.ContactNumbers.Where(f => f.Type == "phone" && f.Subtype == "home").Select(f => f.Value).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.ContactNumbers.Where(f => f.Type == "phone" && f.Subtype == "cell").Select(f => f.Value).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.ContactNumbers.Where(f => f.Type == "email").Select(f => f.Value).ToArray()), idx, c++);
wrap.SetCellValue(string.Join("\n", member.ContactNumbers.Where(f => f.Type == "hamcall").Select(f => f.Value).ToArray()), idx, c++);
wrap.SetCellValue(member.WacLevel.ToString(), idx, c++);
wrap.SetCellValue(membership.Status.StatusName, idx, c++);
if ((includeHidden ?? false) && (Permissions.IsAdmin || (id.HasValue && Permissions.IsMembershipForUnit(id.Value))))
{
wrap.SetCellValue("DOB", 0, c);
wrap.SetCellValue(string.Format("{0:yyyy-MM-dd}", member.BirthDate), idx, c++);
wrap.SetCellValue("Gender", 0, c);
wrap.SetCellValue(member.Gender.ToString(), idx, c++);
}
idx++;
}
wrap.Sheet.AutoFitAll();
}
MemoryStream ms = new MemoryStream();
xl.Save(ms);
ms.Seek(0, SeekOrigin.Begin);
return this.File(ms, "application/vnd.ms-excel", filename);
}