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(" \"{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());
}
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(" \"{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());
}