Ejemplo n.º 1
0
 public void CopyFrom(Address other)
 {
     this.Street = other.Street;
     this.City = other.City;
     this.State = other.State;
     this.Zip = other.Zip;
     this.Location = other.Location;
 }
Ejemplo n.º 2
0
        public ActionResult ImportRoster(Guid q, FormCollection fields)
        {
            // Provide a common way to abort this method while providing useful messages to the user
            Func<string, ActionResult> fail = x => { ViewData["error"] = x; return ImportRoster(q); };

            // Permission check
            if (!Permissions.HasPermission(PermissionType.AddOrganizationMembers, q)) return GetLoginRedirect();

            // Basic validation of file
            if (Request.Files.Count != 1)
            {
                throw new InvalidOperationException("Can only submit one roster");
            }

            Organization org;
            UnitStatusType defaultStatus;
            using (var ctx = this.GetRepository())
            {
                org = ctx.Organizations.IncludePaths("UnitStatusTypes").Single(f => f.Id == q);
                defaultStatus = org.UnitStatusTypes.First(f => f.IsActive && f.IsMissionQualified);
            }

            var postedFile = Request.Files[0];

            byte[] fileData = new byte[postedFile.ContentLength];

            // Read file as Excel spreadsheet
            ExcelFileType fileType;
            if (!Enum.TryParse<ExcelFileType>(System.IO.Path.GetExtension(postedFile.FileName).TrimStart('.'), true, out fileType))
            {
                return fail("Filename does not end in one of: ." + string.Join(", .", Enum.GetNames(typeof(ExcelFileType))));
            }

            ExcelFile file;
            try
            {
                file = ExcelService.Read(postedFile.InputStream, fileType);
            }
            catch
            {
                return fail(string.Format("Error reading {0} file \"{1}\"", fileType, postedFile.FileName));
            }

            ExcelSheet sheet = file.GetSheet(0);

            // Figure out the order of the columns, and which ones were included...
            Dictionary<RosterImportColumn, int> columnLookup = new Dictionary<RosterImportColumn, int>();

            // Figure out which columns the user included in the spreadsheet
            int col = -1;
            do
            {
                col++;
                string header = sheet.CellAt(0, col).StringValue;
                if (string.IsNullOrWhiteSpace(header))
                {
                    break;
                }

                RosterImportColumn column;
                if (Enum.TryParse<RosterImportColumn>(header, true, out column))
                {
                    if (columnLookup.ContainsKey(column))
                    {
                        return fail(string.Format("Found multiple '{0}' columns", column));
                    }
                    columnLookup.Add(column, col);
                }
            } while (col < 100);

            // Make sure the required columns are included
            if (!columnLookup.ContainsKey(RosterImportColumn.LastName))
            {
                return fail(string.Format("File does not contain required column '{0}'", RosterImportColumn.LastName));
            }

            int row = 0;
            string externalKeySource = string.Format("roster{0}", q);
            List<string> errors = new List<string>();

            // ### Start Processing Rows
            do
            {
                row++;

                // Get the lastname. Lastname is required - a column without indicates the end of the data
                string lastname = sheet.CellAt(row, columnLookup[RosterImportColumn.LastName]).StringValue;
                if (string.IsNullOrWhiteSpace(lastname))
                {
                    break;
                }

                using (var ctx = GetRepository())
                {
                    ctx.Organizations.Attach(org);

                    // If the spreadsheet supplies an external key, look the member up in the database.
                    string key = columnLookup.ContainsKey(RosterImportColumn.MemberId) ?
                        sheet.CellAt(row, columnLookup[RosterImportColumn.MemberId]).StringValue :
                        null;

                    SarMember member = null;
                    if (!string.IsNullOrWhiteSpace(key))
                    {
                        var query = from x in
                                        (from f in ctx.ExternalReferences where f.Source == externalKeySource && f.ExternalKey == key select f)
                                    join m in ctx.Members.IncludePaths("Addresses", "ContactInfo") on x.InternalKey equals m.Id into mbrs
                                    select mbrs;

                        member = query.SelectMany(f => f).FirstOrDefault();
                    }

                    // If the member was not found by external key, add it to this database.
                    if (member == null)
                    {
                        member = new SarMember();
                        ctx.Members.Add(member);

                        if (!string.IsNullOrWhiteSpace(key))
                        {
                            ctx.ExternalReferences.Add(new ExternalReference
                            {
                                Source = externalKeySource,
                                ExternalKey = key,
                                InternalKey = member.Id
                            });
                        }
                    }

                    // Some data is updated all the time.
                    // First and Last names are.
                    member.LastName = lastname;

                    member.FirstName = columnLookup.ContainsKey(RosterImportColumn.FirstName) ?
                        sheet.CellAt(row, columnLookup[RosterImportColumn.FirstName]).StringValue :
                        null;

                    // Birthdate is updated if the column exists.
                    // I think this is formatted differently than the FirstName because it was using more
                    // than one statement. Should be able to change it back.
                    if (columnLookup.ContainsKey(RosterImportColumn.BirthDate))
                    {
                        member.BirthDate = sheet.CellAt(row, columnLookup[RosterImportColumn.BirthDate]).DateValue;
                    }

                    // Gender if the column exists
                    if (columnLookup.ContainsKey(RosterImportColumn.Gender))
                    {
                        Gender gender;
                        string attempt = sheet.CellAt(row, columnLookup[RosterImportColumn.Gender]).StringValue;
                        if (!Enum.TryParse<Gender>(attempt, true, out gender))
                        {
                            errors.Add(string.Format("Row {0}: Can't understand gender '{1}'", row + 1, attempt));
                            continue;
                        }
                        member.Gender = gender;
                    }

                    DateTimeOffset joined = DataStoreService.DATE_UNKNOWN;
                    if (columnLookup.ContainsKey(RosterImportColumn.DateJoined))
                    {
                        string dateString = sheet.CellAt(row, columnLookup[RosterImportColumn.DateJoined]).StringValue;
                        DateTime rowDateTime;
                        if (DateTime.TryParse(dateString, out rowDateTime))
                        {
                            joined = TimeZoneInfo.ConvertTimeToUtc(rowDateTime, TimeZoneInfo.FindSystemTimeZoneById(org.TimeZone));
                        }
                    }

                    UnitStatusType status = defaultStatus;
                    if (columnLookup.ContainsKey(RosterImportColumn.Status))
                    {
                        status = org.UnitStatusTypes.Single(
                            f => f.Name.Equals(sheet.CellAt(row, columnLookup[RosterImportColumn.Status]).StringValue, StringComparison.OrdinalIgnoreCase));
                    }

                    string cardNumber = null;
                    if (columnLookup.ContainsKey(RosterImportColumn.Designator))
                    {
                        cardNumber = sheet.CellAt(row, columnLookup[RosterImportColumn.Designator]).StringValue;
                    }

                    UnitMembership um = new UnitMembership
                    {
                        Member = member,
                        OrganizationId = org.Id,
                        Start = joined.UtcDateTime,
                        WorkerNumber = cardNumber,
                        Status = status,
                        Organization = org
                    };

                    member.Memberships.Add(um);
                    member.OldMemberships.Add(um);

                    if (columnLookup.ContainsKey(RosterImportColumn.Street) && columnLookup.ContainsKey(RosterImportColumn.City)
                        && columnLookup.ContainsKey(RosterImportColumn.State) && columnLookup.ContainsKey(RosterImportColumn.ZIP))
                    {
                        Address address = new Address
                        {
                            Street = sheet.CellAt(row, columnLookup[RosterImportColumn.Street]).StringValue,
                            City = sheet.CellAt(row, columnLookup[RosterImportColumn.City]).StringValue,
                            State = sheet.CellAt(row, columnLookup[RosterImportColumn.State]).StringValue,
                            Zip = sheet.CellAt(row, columnLookup[RosterImportColumn.ZIP]).StringValue
                        };

                        if (!(string.IsNullOrWhiteSpace(address.Street) || string.IsNullOrWhiteSpace(address.City) ||
                            string.IsNullOrWhiteSpace(address.State) || string.IsNullOrWhiteSpace(address.Zip))
                            &&
                            !member.Addresses.Any(f => f.Address.Street == address.Street && f.Address.City == address.City &&
                            f.Address.State == address.State && f.Address.Zip == address.Zip))
                        {
                            member.Addresses.Add(new MemberAddress
                            {
                                Member = member,
                                Type = MemberAddressType.Mailing,
                                Address = address
                            });
                        }
                    }

                    foreach (var column in new[] { RosterImportColumn.HomePhone, RosterImportColumn.WorkPhone, RosterImportColumn.CellPhone, RosterImportColumn.Pager })
                    {
                        if (columnLookup.ContainsKey(column))
                        {
                            string phone = sheet.CellAt(row, columnLookup[column]).StringValue;
                            if (!string.IsNullOrWhiteSpace(phone) &&
                                !member.ContactInfo.Any(f => f.Value == phone))
                            {
                                member.ContactInfo.Add(new MemberContact
                                {
                                    Member = member,
                                    Type = ContactType.Phone,
                                    SubType = column.ToString().ToLowerInvariant().Replace("phone", ""),
                                    Value = phone
                                });
                            }
                        }
                    }

                    if (columnLookup.ContainsKey(RosterImportColumn.Email))
                    {
                        string email = sheet.CellAt(row, columnLookup[RosterImportColumn.Email]).StringValue;
                        if (!string.IsNullOrWhiteSpace(email))
                        {
                            member.ContactInfo.Add(new MemberContact
                            {
                                Member = member,
                                Type = ContactType.Email,
                                Value = email
                            });
                        }
                    }

                    ctx.SaveChanges();
                }
            } while (row < 1002); // Only allow 1000 rows per file

            if (errors.Count > 0)
            {
                throw new NotImplementedException("Should send email with errors: " + string.Join("::", errors.ToArray()));
            }

            file.Dispose();
            return RedirectToAction("ImportRosterResults", new { q = q, success = row - errors.Count, errors = errors.Count });
        }