static void Main(string[] args)
{
var service = new ExcelService();
service.Create();
}
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);
}
