Beispiel #1
0
        public JsonResult Import(int rowId)
        {
            var file =
                Db.ImportFile.SingleOrDefault(
                    fi => fi.ElectionGuid == UserSession.CurrentElectionGuid && fi.C_RowId == rowId);

            if (file == null)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "File not found" }
                }.AsJsonResult());
            }

            var columnsToRead = file.ColumnsToRead;

            if (columnsToRead == null)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Mapping not defined" }
                }.AsJsonResult());
            }

            var start      = DateTime.Now;
            var textReader = new StringReader(file.Contents.AsString(file.CodePage));
            var csv        = new CsvReader(textReader, true)
            {
                SkipEmptyLines = true
            };

            //mapping:   csv->db,csv->db
            var currentMappings =
                columnsToRead.DefaultTo("").SplitWithString(",").Select(s => s.SplitWithString("->")).ToList();
            var dbFields      = DbFieldsList.ToList();
            var validMappings = currentMappings.Where(mapping => dbFields.Contains(mapping[1])).ToList();

            if (validMappings.Count == 0)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Mapping not defined" }
                }.AsJsonResult());
            }

            var mappedFields = dbFields.Where(f => validMappings.Select(m => m[1]).Contains(f)).ToList();

            if (!mappedFields.Contains("LastName"))
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Last Name must be mapped" }
                }.AsJsonResult());
            }

            var currentPeople = new PersonCacher(Db).AllForThisElection.ToList();
            var personModel   = new PeopleModel();
            var defaultReason = new ElectionModel().GetDefaultIneligibleReason();

            var rowsProcessed          = 0;
            var rowsSkipped            = 0;
            var peopleAdded            = 0;
            var peopleSkipped          = 0;
            var peopleSkipWarningGiven = false;

            var hub          = new ImportHub();
            var peopleToLoad = new List <Person>();
            var result       = new List <string>();

            var unexpectedReasons = new Dictionary <string, int>();
            var validReasons      = 0;

            csv.ReadNextRecord();
            while (!csv.EndOfStream)
            {
                rowsProcessed++;

                var valuesSet       = false;
                var namesFoundInRow = false;

                var query = currentPeople.AsQueryable();

                var person = new Person();
                var reason = defaultReason;

                foreach (var currentMapping in validMappings)
                {
                    var dbFieldName = currentMapping[1];
                    var value       = csv[currentMapping[0]];

                    switch (dbFieldName)
                    {
                    case "IneligibleReasonGuid":
                        // match value to the list of Enums
                        value = value.Trim();
                        if (value.HasContent())
                        {
                            var match = IneligibleReasonEnum.GetFor(value);
                            if (match != null)
                            {
                                reason        = match;
                                validReasons += 1;
                            }
                            else
                            {
                                // tried but didn't match a valid reason!
                                reason = defaultReason;
                                value  = HttpUtility.HtmlEncode(value);
                                result.Add("Invalid Eligibility Status reason on line {0}: {1}".FilledWith(rowsProcessed + 1, value));

                                if (unexpectedReasons.ContainsKey(value))
                                {
                                    unexpectedReasons[value] += 1;
                                }
                                else
                                {
                                    unexpectedReasons.Add(value, 1);
                                }
                            }
                        }
                        break;

                    default:
                        person.SetPropertyValue(dbFieldName, value);
                        break;
                    }
                    ;
                    valuesSet = true;

                    switch (dbFieldName)
                    {
                    case "LastName":
                        query           = query.Where(p => p.LastName == value);
                        namesFoundInRow = namesFoundInRow || value.HasContent();
                        break;

                    case "FirstName":
                        query           = query.Where(p => p.FirstName == value);
                        namesFoundInRow = namesFoundInRow || value.HasContent();
                        break;

                    case "OtherLastNames":
                        query = query.Where(p => p.OtherLastNames == value);
                        break;

                    case "OtherNames":
                        query = query.Where(p => p.OtherNames == value);
                        break;

                    case "OtherInfo":
                        query = query.Where(p => p.OtherInfo == value);
                        break;

                    case "Area":
                        query = query.Where(p => p.Area == value);
                        break;

                    case "BahaiId":
                        query = query.Where(p => p.BahaiId == value);
                        break;

                    case "IneligibleReasonGuid":
                        //if (reason != defaultReason)
                        //{
                        //  query = query.Where(p => p.IneligibleReasonGuid == reason.Value);
                        //}
                        break;

                    default:
                        throw new ApplicationException("Unexpected: " + dbFieldName);
                    }
                }

                if (!valuesSet || !namesFoundInRow)
                {
                    rowsSkipped++;
                    result.Add("Skipping line " + rowsProcessed);
                }
                else if (query.Any())
                {
                    peopleSkipped++;
                    if (peopleSkipped < 10)
                    {
                        result.Add("Duplicate on line " + (rowsProcessed + 1));
                    }
                    else
                    {
                        if (!peopleSkipWarningGiven)
                        {
                            result.Add("More duplicates... (Only the first 10 are noted.)");
                            peopleSkipWarningGiven = true;
                        }
                    }
                }
                else
                {
                    //get ready for DB
                    person.ElectionGuid = UserSession.CurrentElectionGuid;
                    person.PersonGuid   = Guid.NewGuid();

                    personModel.SetCombinedInfoAtStart(person);
                    personModel.SetInvolvementFlagsToDefault(person, reason);

                    //Db.Person.Add(person);
                    currentPeople.Add(person);
                    peopleToLoad.Add(person);

                    peopleAdded++;

                    if (peopleToLoad.Count >= 500)
                    {
                        //Db.SaveChanges();

                        Db.BulkInsert(peopleToLoad);
                        peopleToLoad.Clear();
                    }
                }

                if (rowsProcessed % 100 == 0)
                {
                    hub.ImportInfo(rowsProcessed, peopleAdded);
                }

                csv.ReadNextRecord();
            }

            if (peopleToLoad.Count != 0)
            {
                Db.BulkInsert(peopleToLoad);
            }

            file.ProcessingStatus = "Imported";

            Db.SaveChanges();

            new PersonCacher().DropThisCache();

            result.AddRange(new[]
            {
                "Processed {0} data line{1}".FilledWith(rowsProcessed, rowsProcessed.Plural()),
                "Added {0} {1}.".FilledWith(peopleAdded, peopleAdded.Plural("people", "person"))
            });
            if (peopleSkipped > 0)
            {
                result.Add("{0} duplicate{1} ignored.".FilledWith(peopleSkipped, peopleSkipped.Plural()));
            }
            if (rowsSkipped > 0)
            {
                result.Add("{0} line{1} skipped or blank.".FilledWith(rowsSkipped, rowsSkipped.Plural()));
            }
            if (validReasons > 0)
            {
                result.Add("{0} {1} with recognized Eligibility Status Reasons.".FilledWith(validReasons, validReasons.Plural("people", "person")));
            }
            if (unexpectedReasons.Count > 0)
            {
                result.Add("{0} Eligibility Status Reason{1} not recognized: ".FilledWith(unexpectedReasons.Count, unexpectedReasons.Count.Plural()));
                foreach (var r in unexpectedReasons)
                {
                    result.Add("&nbsp; &nbsp; \"{0}\"{1}".FilledWith(r.Key, r.Value == 1 ? "" : " x" + r.Value));
                }
            }

            result.Add("Import completed in " + (DateTime.Now - start).TotalSeconds.ToString("0.0") + " s.");

            new LogHelper().Add("Imported file #" + rowId + ": " + result.JoinedAsString(" "), true);

            return(new
            {
                result,
                count = NumberOfPeople
            }.AsJsonResult());
        }
Beispiel #2
0
        public JsonResult Import(int rowId)
        {
            var file =
                Db.ImportFile.SingleOrDefault(
                    fi => fi.ElectionGuid == UserSession.CurrentElectionGuid && fi.C_RowId == rowId);

            if (file == null)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "File not found" }
                }.AsJsonResult());
            }

            var columnsToRead = file.ColumnsToRead;

            if (columnsToRead == null)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Mapping not defined" }
                }.AsJsonResult());
            }

            var start      = DateTime.Now;
            var fileString = file.Contents.AsString(file.CodePage);

            var firstDataRow       = file.FirstDataRow.AsInt();
            var numFirstRowsToSkip = firstDataRow - 2;

            if (numFirstRowsToSkip > 0)
            {
                // 1 based... headers on line 1, data on line 2. If 2 or less, ignore it.
                fileString = fileString.GetLinesAfterSkipping(numFirstRowsToSkip);
            }
            else
            {
                numFirstRowsToSkip = 0;
            }

            var textReader = new StringReader(fileString);
            var csv        = new CsvReader(textReader, true, ',', '"', '"', '#', ValueTrimmingOptions.All, 4096, null)
            {
                // had to provide all parameters in order to set ValueTrimmingOption.All
                SkipEmptyLines     = false,
                MissingFieldAction = MissingFieldAction.ReplaceByEmpty,
                SupportsMultiline  = false,
            };

            //mapping:   csv->db,csv->db
            var currentMappings =
                columnsToRead.DefaultTo("").SplitWithString(",").Select(s => s.SplitWithString(MappingSymbol)).ToList();
            var dbFields      = DbFieldsList.ToList();
            var validMappings = currentMappings.Where(mapping => dbFields.Contains(mapping[1])).ToList();

            if (validMappings.Count == 0)
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Mapping not defined" }
                }.AsJsonResult());
            }

            var mappedFields = dbFields.Where(f => validMappings.Select(m => m[1]).Contains(f)).ToList();

            if (!mappedFields.Contains("LastName"))
            {
                return(new
                {
                    failed = true,
                    result = new[] { "Last Name must be mapped" }
                }.AsJsonResult());
            }
            if (!mappedFields.Contains("FirstName"))
            {
                return(new
                {
                    failed = true,
                    result = new[] { "First Name must be mapped" }
                }.AsJsonResult());
            }

            var phoneNumberChecker = new Regex(@"\+[0-9]{4,15}");
            var phoneNumberCleaner = new Regex(@"[^\+0-9]");
            var emailChecker       = new Regex(@".*@.*\..*");

            var currentPeople = new PersonCacher(Db).AllForThisElection.ToList();

            currentPeople.ForEach(p => p.TempImportLineNum = -1);

            var knownEmails = currentPeople.Where(p => p.Email != null).Select(p => p.Email.ToLower()).ToList();
            var knownPhones = currentPeople.Where(p => p.Phone != null).Select(p => p.Phone).ToList();

            var personModel = new PeopleModel();
            // var defaultReason = new ElectionModel().GetDefaultIneligibleReason();

            var currentLineNum = numFirstRowsToSkip > 0 ? numFirstRowsToSkip : 1; // including header row
            var rowsWithErrors = 0;
            var peopleAdded    = 0;
            var peopleSkipped  = 0;
            // var peopleSkipWarningGiven = false;

            var hub          = new ImportHub();
            var peopleToLoad = new List <Person>();
            var result       = new List <string>();

            var unexpectedReasons = new Dictionary <string, int>();
            // var validReasons = 0;
            var continueReading = true;

            hub.StatusUpdate("Processing", true);

            while (csv.ReadNextRecord() && continueReading)
            {
                if (csv.GetCurrentRawData() == null)
                {
                    continue;
                }

                // currentLineNum++;
                currentLineNum = numFirstRowsToSkip + (int)csv.CurrentRecordIndex + 1;

                var valuesSet       = false;
                var namesFoundInRow = 0;
                var errorInRow      = false;

                var duplicateInFileSearch = currentPeople.AsQueryable();
                var doDupQuery            = false;

                var person = new Person
                {
                    TempImportLineNum = currentLineNum
                };

                foreach (var currentMapping in validMappings)
                {
                    var csvColumnName = currentMapping[0];
                    var dbFieldName   = currentMapping[1];

                    string value;
                    try
                    {
                        value = csv[csvColumnName] ?? "";
                    }
                    catch (Exception e)
                    {
                        result.Add($"~E Line {currentLineNum} - {e.Message.Split('\r')[0]}. Are there \"\" marks missing?");
                        errorInRow      = true;
                        continueReading = false;
                        break;
                    }
                    var rawValue      = HttpUtility.HtmlEncode(value);
                    var originalValue = value;


                    switch (dbFieldName)
                    {
                    case "IneligibleReasonGuid":
                        // match value to the list of Enums
                        value = value.Trim();
                        if (value.HasContent())
                        {
                            if (value == "Eligible")
                            {
                                // leave as null
                            }
                            else
                            {
                                var match = IneligibleReasonEnum.GetFor(value);
                                if (match != null)
                                {
                                    person.IneligibleReasonGuid = match.Value;
                                }
                                else
                                {
                                    // tried but didn't match a valid reason!
                                    errorInRow = true;

                                    result.Add($"~E Line {currentLineNum} - Invalid Eligibility Status reason: {rawValue}");

                                    if (unexpectedReasons.ContainsKey(value))
                                    {
                                        unexpectedReasons[value] += 1;
                                    }
                                    else
                                    {
                                        unexpectedReasons.Add(value, 1);
                                    }
                                }
                            }
                        }
                        break;

                    case "FirstName":
                    case "LastName":
                        if (value.Trim() == "")
                        {
                            result.Add($"~E Line {currentLineNum} - {dbFieldName} must not be blank");
                        }
                        else
                        {
                            person.SetPropertyValue(dbFieldName, value);
                        }
                        break;

                    default:
                        person.SetPropertyValue(dbFieldName, value);
                        break;
                    }
                    ;

                    valuesSet = valuesSet || value.HasContent();

                    if (value.HasContent())
                    {
                        doDupQuery = true;
                        switch (dbFieldName)
                        {
                        case "LastName":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.LastName == value);
                            namesFoundInRow++;
                            break;

                        case "FirstName":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.FirstName == value);
                            namesFoundInRow++;
                            break;

                        case "OtherLastNames":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.OtherLastNames == value);
                            break;

                        case "OtherNames":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.OtherNames == value);
                            break;

                        case "OtherInfo":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.OtherInfo == value);
                            break;

                        case "Area":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.Area == value);
                            break;

                        case "BahaiId":
                            duplicateInFileSearch = duplicateInFileSearch.Where(p => p.BahaiId == value);
                            break;

                        case "Email":
                            if (value.HasContent())
                            {
                                value = value.ToLower();
                                if (!emailChecker.IsMatch(value))
                                {
                                    result.Add($"~E Line {currentLineNum} - Invalid email: {rawValue}");
                                    errorInRow = true;
                                }
                                else if (knownEmails.Contains(value))
                                {
                                    result.Add($"~E Line {currentLineNum} - Duplicate email: {rawValue}");
                                    errorInRow = true;
                                }

                                if (!errorInRow)
                                {
                                    knownEmails.Add(value);
                                }
                            }
                            break;

                        case "Phone":
                            if (value.HasContent())
                            {
                                value = phoneNumberCleaner.Replace(value, "");

                                if (!value.StartsWith("+") && value.Length == 10)
                                {
                                    // assume north american
                                    value = "+1" + value;
                                }

                                if (!phoneNumberChecker.IsMatch(value))
                                {
                                    result.Add($"~E Line {currentLineNum} - Invalid phone number: {rawValue}");
                                    errorInRow = true;
                                }
                                else if (originalValue != value)
                                {
                                    result.Add($"~W Line {currentLineNum} - Phone number adjusted from {rawValue} to {value} ");
                                }

                                if (value.HasContent())
                                {
                                    if (knownPhones.Contains(value))
                                    {
                                        result.Add($"~E Line {currentLineNum} - Duplicate phone number: {value}");
                                        errorInRow = true;
                                    }

                                    knownPhones.Add(value);
                                }

                                // update with the cleaned phone number
                                person.SetPropertyValue(dbFieldName, value.HasContent() ? value : null);
                            }

                            break;

                        case "IneligibleReasonGuid":
                            break;

                        default:
                            throw new ApplicationException("Unexpected: " + dbFieldName);
                        }
                    }
                }

                var addRow = true;

                if (!valuesSet)
                {
                    // don't count it as an error
                    result.Add($"~I Line {currentLineNum} - Ignoring blank line");
                    addRow = false;
                }
                else if (namesFoundInRow != 2 || errorInRow)
                {
                    addRow = false;
                    rowsWithErrors++;

                    if (namesFoundInRow != 2)
                    {
                        result.Add($"~E Line {currentLineNum} - First or last name missing");
                    }
                }

                if (doDupQuery)
                {
                    var duplicates = duplicateInFileSearch.Select(p => p.TempImportLineNum).Distinct().ToList();
                    if (duplicates.Any())
                    {
                        addRow = false;

                        if (duplicates.All(n => n == -1))
                        {
                            result.Add($"~I Line {currentLineNum} - {person.FirstName} {person.LastName} - skipped - Matching person found in existing records");
                        }
                        else
                        {
                            peopleSkipped++;
                            foreach (var n in duplicates.Where(n => n > 0))
                            {
                                result.Add($"~E Line {currentLineNum} - {person.FirstName} {person.LastName} - Duplicate person found on line {n}");
                            }
                        }
                    }
                }



                if (addRow)
                { //get ready for DB
                    person.ElectionGuid = UserSession.CurrentElectionGuid;
                    person.PersonGuid   = Guid.NewGuid();

                    personModel.SetCombinedInfoAtStart(person);
                    personModel.ApplyVoteReasonFlags(person);

                    peopleToLoad.Add(person);

                    // result.Add($"~I Line {currentLineNum} - {person.FirstName} {person.LastName}");  -- not good for large lists!

                    peopleAdded++;
                }

                currentPeople.Add(person);

                if (currentLineNum % 100 == 0)
                {
                    hub.ImportInfo(currentLineNum, peopleAdded);
                }

                if (result.Count(s => s.StartsWith("~E")) == 10)
                {
                    result.Add("~E Import aborted after 10 errors");
                    break;
                }
            }

            var abort = rowsWithErrors > 0 || peopleSkipped > 0;

            if (!abort && peopleToLoad.Count != 0)
            {
                hub.StatusUpdate("Saving");

                var error = BulkInsert_CheckErrors(peopleToLoad);
                if (error != null)
                {
                    abort = true;
                    result.Add(error);
                }
                else
                {
                    result.Add("Saved to database");
                }
            }

            file.ProcessingStatus = abort ? "Import aborted" : "Imported";

            Db.SaveChanges();

            new PersonCacher().DropThisCache();

            result.AddRange(new[]
            {
                "---------",
                $"Processed {currentLineNum:N0} data line{currentLineNum.Plural()}",
            });
            // if (peopleSkipped > 0)
            // {
            //   result.Add($"{peopleSkipped:N0} duplicate{peopleSkipped.Plural()} ignored.");
            // }
            if (rowsWithErrors > 0)
            {
                result.Add($"{rowsWithErrors:N0} line{rowsWithErrors.Plural("s had errors or were", " had errors or was")} blank.");
            }
            // if (validReasons > 0)
            // {
            //   result.Add($"{validReasons:N0} {validReasons.Plural("people", "person")} with recognized Eligibility Status Reason{validReasons.Plural()}.");
            // }
            if (unexpectedReasons.Count > 0)
            {
                result.Add($"{unexpectedReasons.Count:N0} Eligibility Status Reason{unexpectedReasons.Count.Plural()} not recognized: ");
                foreach (var r in unexpectedReasons)
                {
                    result.Add("&nbsp; &nbsp; \"{0}\"{1}".FilledWith(r.Key, r.Value == 1 ? "" : " x" + r.Value));
                }
            }

            result.Add("---------");

            if (abort)
            {
                result.Add($"Import aborted due to errors in file. Please correct and try again.");
            }
            else
            {
                result.Add($"Added {peopleAdded:N0} {peopleAdded.Plural("people", "person")}.");
                result.Add($"Import completed in {(DateTime.Now - start).TotalSeconds:N1} s.");
            }

            var resultsForLog = result.Where(s => !s.StartsWith("~"));

            new LogHelper().Add("Imported file #" + rowId + ":\r" + resultsForLog.JoinedAsString("\r"), true);

            return(new
            {
                result,
                count = NumberOfPeople
            }.AsJsonResult());
        }