Esempio n. 1
0
        /// <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();
                    }
                }
            }
        }
Esempio n. 2
0
        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));
        }