public JsonResult Import(HttpPostedFileBase file)
{
try
{
_hub = new ImportHub();
VerifyFileReceived(file);
LoadIntoXmlDoc(file);
ValidateXml();
var success = LoadXmlToDatabase();
return(success
? new LoadResult(_electionGuid).AsJsonResult()
: new LoadResult("(Under construction!)").AsJsonResult());
}
catch (LoaderException ex)
{
_hub.StatusUpdate(ex.GetAllMsgs("\n"));
return(new LoadResult(ex.GetAllMsgs("\n")).AsJsonResult());
}
catch (Exception ex)
{
_hub.StatusUpdate(ex.GetAllMsgs("\n"));
// some unexpected exception...
return(new LoadResult(ex.GetType().Name + ": " + ex.GetAllMsgs("\n")).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());
}