/// <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 Review4x4Roster(bool?bot)
{
if (!Has4x4RosterPermissions())
{
return(CreateLoginRedirect());
}
// Value is pre-incremented in while loop.
int startRow = 13;
if (Request.Files.Count != 1)
{
throw new InvalidOperationException("Can only submit one roster");
}
var postedFile = Request.Files[0];
byte[] fileData = new byte[postedFile.ContentLength];
postedFile.InputStream.Read(fileData, 0, fileData.Length);
var stream = new System.IO.MemoryStream(fileData);
stream.Position = 0;
ExcelFile xl = null; // new ExcelFile();
if (postedFile.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
{
xl = ExcelService.Read(postedFile.InputStream, ExcelFileType.XLSX);
}
else
{
xl = ExcelService.Read(postedFile.InputStream, ExcelFileType.XLS);
}
ExcelSheet sheet = xl.GetSheet(0);
object start = sheet.CellAt(13, 4).StringValue.ToUpperInvariant().Replace("DATE", "").Replace("TIME", "").Replace("IN", "").Replace("OUT", "").Trim();
DateTime startDate = DateTime.Parse(start.ToString());
ExpandedRowsContext model = new ExpandedRowsContext();
model.EventId = Guid.NewGuid();
if (typeof(E) == typeof(Mission))
{
model.Type = RosterType.Mission;
model.SarEvent = new Mission {
Id = model.EventId
};
}
else
{
model.Type = RosterType.Training;
model.SarEvent = new Training {
Id = model.EventId
};
}
model.SarEvent.StateNumber = sheet.CellAt(9, 2).StringValue.Split(' ')[0];
model.SarEvent.Title = sheet.CellAt(10, 2).StringValue;
model.SarEvent.Location = sheet.CellAt(11, 2).StringValue;
model.SarEvent.County = sheet.CellAt(9, 10).StringValue.ToLowerInvariant();
model.BadRows = new List <IRosterEntry>();
model.Rows = new List <IRosterEntry>();
model.NumDays = 3;
model.RosterStart = startDate;
SarUnit unit = this.db.Units.Where(f => f.DisplayName == "4x4").Single();
//ctx.Detach(unit);
while (!string.IsNullOrWhiteSpace((sheet.CellAt(++startRow, 1).StringValue ?? "").ToString()))
{
R roster = new R();
string name = sheet.CellAt(startRow, 1).StringValue;
string dem = sheet.CellAt(startRow, 2).StringValue.PadLeft(4, '0');
var member = this.db.Members
.Where(f => f.LastName + ", " + f.FirstName == name && f.DEM == dem)
.SingleOrDefault();
if (member == null)
{
//throw new InvalidOperationException(string.Format("Member {0} not found", sheet.CellAt(startRow, 1]));
continue;
}
// ctx.Detach(member);
roster.Person = member;
for (int i = 0; i < 3; i++)
{
string v = (sheet.CellAt(startRow, 8 - i * 2).StringValue ?? "").ToString();
if (!string.IsNullOrWhiteSpace(v))
{
v = v.PadLeft(4, '0');
int hours = int.Parse(v.Substring(0, 2));
int minutes = int.Parse(v.Substring(2, 2));
roster.TimeIn = startDate.AddDays(2 - i).AddHours(hours).AddMinutes(minutes);
}
v = (sheet.CellAt(startRow, 5 + i * 2).StringValue ?? "").ToString();
if (!string.IsNullOrWhiteSpace(v))
{
v = v.PadLeft(4, '0');
int hours = int.Parse(v.Substring(0, 2));
int minutes = int.Parse(v.Substring(2, 2));
roster.TimeOut = startDate.AddDays(i).AddHours(hours).AddMinutes(minutes);
}
}
string miles = (sheet.CellAt(startRow, 11).StringValue ?? "").ToString();
if (miles != "")
{
double numericMiles;
if (double.TryParse(miles, out numericMiles))
{
roster.Miles = (int)Math.Ceiling(numericMiles);
}
}
MissionRoster missionRoster = roster as MissionRoster;
if (missionRoster != null)
{
missionRoster.Unit = unit;
if ("CO".Equals(sheet.CellAt(startRow, 3).StringValue))
{
missionRoster.InternalRole = "InTown";
}
else if ("OL".Equals(sheet.CellAt(startRow, 3).StringValue))
{
missionRoster.InternalRole = "OL";
}
else if (new[] { "AS", "ATL", "OUA", "TL", "TLT", "TM" }.Contains(sheet.CellAt(startRow, 3).StringValue))
{
missionRoster.InternalRole = "Field";
}
else if ("VAN".Equals(sheet.CellAt(startRow, 3).StringValue))
{
missionRoster.InternalRole = "Base";
}
else
{
missionRoster.InternalRole = "Responder";
}
}
model.Rows.Add(roster);
}
if (model.Rows.Count > 0)
{
model.SarEvent.StartTime = model.Rows.Min(f => f.TimeIn).Value;
model.SarEvent.StopTime = model.Rows.Max(f => f.TimeOut).Value;
}
DateTime early = model.SarEvent.StartTime.AddDays(-1);
DateTime late = model.SarEvent.StartTime.AddDays(1);
E[] alternates = this.GetEventSource().Where(f => f.StateNumber == model.SarEvent.StateNumber || (f.StartTime > early && f.StartTime < late)).OrderBy(f => f.StartTime).ToArray();
ViewData["alternateMissions"] = alternates;
ViewData["CanEditRoster"] = false;
ViewData["IsTemp"] = true;
SetSessionValue("reviewRosterBot", bot);
SetSessionValue("reviewRoster", model);
SetSessionValue("reviewRosterData", fileData);
SetSessionValue("reviewRosterName", postedFile.FileName);
return(View(model));
}
