public ActionResult Index()
{
CodePointUploadModel model = new CodePointUploadModel();
GetLastUploadDetails(model);
GetViewData();
return(View(model));
}
private void GetLastUploadDetails(CodePointUploadModel model)
{
MetadataUpload dataUpload = db.MetadataUploads.Where(m => m.MetadataUploadTypeId == (Int32)Constants.MetadataUploadType.CodePoint).OrderByDescending(m => m.CreatedDateTimeUtc).FirstOrDefault();
if (dataUpload != null)
{
if (dataUpload.AspNetUser == null)
{
AspNetUser user = db.AspNetUsers.Find(dataUpload.CreatedByUserId);
if (user != null)
{
model.LastUploadedBy = user.Name;
}
}
else
{
model.LastUploadedBy = dataUpload.AspNetUser.Name;
}
model.LastUploadDateTimeUtc = dataUpload.CreatedDateTimeUtc;
DateTime.SpecifyKind(model.LastUploadDateTimeUtc.Value, DateTimeKind.Utc);
model.LastUploadFileName = dataUpload.FileName;
}
}
public ActionResult Index(CodePointUploadModel model)
{
if (ModelState.IsValid)
{
try
{
String[] validFileTypes = { ".zip" };
Boolean validFileType = false;
String CodePointFolder = Constants.ConfigSettings.CodePointUploadVirtualDirectoryName;
if (CodePointFolder.EndsWith(@"\"))
{
CodePointFolder = CodePointFolder.Substring(0, CodePointFolder.Length - 1);
}
// Check if config setting is valid
if (String.IsNullOrEmpty(CodePointFolder) || !Directory.Exists(CodePointFolder))
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "CodePointFolderNotConfigured", "Configuration setting VirtualDirectoryNameForStoringCodePointFiles is not set or is incorrect"));
}
foreach (String fileType in validFileTypes)
{
if (model.File.FileName.ToLower().EndsWith(fileType))
{
validFileType = true;
break;
}
}
if (!validFileType)
{
ModelState.AddModelError("File", AppGlobal.Language.GetText(this, "ZIPFilesOnly", "Please upload a ZIP file"));
}
else
{
String ZIPFile = Path.Combine(CodePointFolder, "CodePoint.zip");
// Save the zip file
model.File.SaveAs(ZIPFile);
// Unzip all the CSV files
using (ZipArchive za = ZipFile.OpenRead(ZIPFile))
{
foreach (ZipArchiveEntry entry in za.Entries.Where(entry => entry.Name.ToLower().EndsWith(".csv")).Where(entry => entry.Name.ToLower() != "code-point_open_column_headers.csv"))
{
entry.ExtractToFile(Path.Combine(CodePointFolder, entry.Name), true);
}
}
// Delete the zip file
System.IO.File.Delete(ZIPFile);
MetadataUpload metadataUpload = new MetadataUpload
{
MetadataUploadTypeId = (int)Constants.MetadataUploadType.CodePoint,
CreatedByUserId = Permission.GetCurrentUserGuid().ToString(),
CreatedDateTimeUtc = DateTime.UtcNow,
FileName = model.File.FileName,
FileSizeInBytes = model.File.ContentLength,
RowsBefore = db.GeoLocations.Count()
};
var sw = new Stopwatch();
sw.Start();
// Import the new data
String[] csvFiles = Directory.GetFiles(CodePointFolder, "*.csv");
if (csvFiles.GetLength(0) == 0)
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "UnableToFindCSVFile", "Unable to find any CSV files to import"));
DeleteProgressMessage();
}
else
{
AddOrReplaceProgressMessage(AppGlobal.Language.GetText(this, "StartingImport", "Starting Import..."));
Boolean cancellingImport = false;
String importingMessageText = AppGlobal.Language.GetText(this, "ImportingFileXOfY", "Importing file {0} of {1}...");
String mergingMessageText = AppGlobal.Language.GetText(this, "MergeData", "Merging Data...");
String removingTempDataMessageText = AppGlobal.Language.GetText(this, "RemovingTemporaryData", "Removing Temporary Data...");
String importSuccessfulMessageText = AppGlobal.Language.GetText(this, "ImportSuccessful", "Code Point Data Successfully Imported");
String importCancelledMessageText = AppGlobal.Language.GetText(this, "ImportCancelled", "Code Point Data Import Cancelled");
String importErrorMessageText = AppGlobal.Language.GetText(this, "ImportError", "Error Importing Code Point Data : {0}");
new Thread(() =>
{
try
{
ProviderPortalEntities _db = new ProviderPortalEntities();
// Open the database
using (SqlConnection conn = new SqlConnection(_db.Database.Connection.ConnectionString))
{
conn.Open();
using (SqlTransaction transaction = conn.BeginTransaction())
{
// Truncate the temporary import table just incase there's still data in there.
SqlCommand comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_GeoLocation];", conn, transaction);
comm.ExecuteNonQuery();
// Setup the DataTable
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn {
ColumnName = "Postcode", AllowDBNull = false, DataType = typeof(String), MaxLength = 8
});
dt.Columns.Add(new DataColumn {
ColumnName = "Lat", AllowDBNull = false, DataType = typeof(Decimal)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Lng", AllowDBNull = false, DataType = typeof(Decimal)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Northing", AllowDBNull = false, DataType = typeof(Decimal)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Easting", AllowDBNull = false, DataType = typeof(Decimal)
});
Int32 i = 1;
foreach (String csvFile in csvFiles)
{
// Check if we have stopped the import
if (IsCancellingImport(new ProviderPortalEntities()))
{
break;
}
// Remove all the rows
dt.Clear();
// Write the progress message
AddOrReplaceProgressMessage(_db, String.Format(importingMessageText, i++, csvFiles.GetLength(0)));
// Import the CSV file
using (CsvReader csv = new CsvReader(new StreamReader(csvFile)))
{
const Int32 POSTCODE = 0;
const Int32 EASTING = 2;
const Int32 NORTHING = 3;
csv.Configuration.HasHeaderRecord = false;
while (csv.Read())
{
String Postcode = CorrectPostcode(csv[POSTCODE]);
Double Northing = Convert.ToDouble(csv[NORTHING]);
Double Easting = Convert.ToDouble(csv[EASTING]);
LatLon latlon = LatLonConversions.ConvertOSToLatLon(Easting, Northing);
const Int32 decimalPlaces = 6;
if (Postcode.IndexOf(" ") == -1)
{
Postcode = Postcode.Substring(0, Postcode.Length - 3) + " " + Postcode.Substring(Postcode.Length - 3, 3);
}
DataRow dr = dt.NewRow();
dr["Postcode"] = Postcode;
dr["Lat"] = Math.Round(latlon.Latitude, decimalPlaces);
dr["Lng"] = Math.Round(latlon.Longitude, decimalPlaces);
dr["Northing"] = Northing;
dr["Easting"] = Easting;
dt.Rows.Add(dr);
// Every 100 rows, check whether we are cancelling the import
if (csv.Row % 100 == 0 && IsCancellingImport(new ProviderPortalEntities()))
{
cancellingImport = true;
break;
}
}
csv.Dispose();
// Delete the file to tidy up space as quickly as possible
try
{
System.IO.File.Delete(csvFile);
}
catch { }
}
if (!cancellingImport)
{
// Copy the data to the Import_GeoLocation Table
BulkImportData(conn, dt, transaction);
}
}
cancellingImport = IsCancellingImport(new ProviderPortalEntities());
if (!cancellingImport)
{
// Merge the data into the GeoLocation Table
AddOrReplaceProgressMessage(_db, mergingMessageText);
comm = new SqlCommand("MERGE [dbo].[GeoLocation] dest USING [dbo].[Import_GeoLocation] source ON dest.Postcode = source.Postcode WHEN MATCHED THEN UPDATE SET dest.Lat = source.Lat, dest.Lng = source.Lng, dest.Northing = source.Northing, dest.Easting = source.Easting WHEN NOT MATCHED THEN INSERT (Postcode, Lat, Lng, Northing, Easting) VALUES (source.Postcode, source.Lat, source.Lng, source.Northing, source.Easting);", conn, transaction)
{
CommandTimeout = 3600 /* 1 Hour */
};
comm.ExecuteNonQuery();
// Update any Address Rows that don't currently have any Latitude or Longitude
try
{
comm = new SqlCommand("UPDATE Address SET Address.Latitude = GeoLocation.Lat, Address.Longitude = GeoLocation.lng FROM Address INNER JOIN GeoLocation ON Address.Postcode = GeoLocation.Postcode WHERE Address.Latitude IS NULL AND GeoLocation.Lat IS NOT NULL;", conn, transaction)
{
CommandTimeout = 3600 /* 1 Hour */
};
comm.ExecuteNonQuery();
}
catch {}
}
// Truncate the temporary import table
if (!cancellingImport)
{
AddOrReplaceProgressMessage(_db, removingTempDataMessageText);
comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_GeoLocation];", conn, transaction);
comm.ExecuteNonQuery();
}
if (!IsCancellingImport(new ProviderPortalEntities()))
{
// Commit the transaction
transaction.Commit();
#region Update After Row Counts
// Add the current row count to MetadataUpload
// Save timings
sw.Stop();
metadataUpload.DurationInMilliseconds = (int)sw.ElapsedMilliseconds;
metadataUpload.RowsAfter = _db.GeoLocations.Count();
_db.MetadataUploads.Add(metadataUpload);
_db.SaveChanges();
#endregion
}
else
{
// Rollback the transaction
try
{
transaction.Rollback();
_db.Dispose();
}
catch { }
}
// Close the database
conn.Close();
}
}
// Delete all the uploaded and expanded files
try
{
foreach (FileInfo file in new DirectoryInfo(CodePointFolder).GetFiles())
{
file.Delete();
}
}
catch
{
}
// Write Success or Cancelled message
AddOrReplaceProgressMessage(_db, cancellingImport ? importCancelledMessageText : importSuccessfulMessageText, true);
}
catch (Exception ex)
{
AddOrReplaceProgressMessage(new ProviderPortalEntities(), String.Format(importErrorMessageText, ex.Message), true);
// Delete all the uploaded and expanded files
try
{
foreach (FileInfo file in new DirectoryInfo(CodePointFolder).GetFiles())
{
file.Delete();
}
}
catch
{
}
}
}).Start();
}
}
}
catch (Exception ex)
{
// Create a model error
ModelState.AddModelError("", ex.Message);
}
}
// No Errors so redirect to index which will show messages
if (ModelState.IsValid)
{
return(RedirectToAction("Index"));
}
GetLastUploadDetails(model);
return(View(model));
}
