/// <summary>
/// Saves this instance.
/// </summary>
private void Save()
{
CacheManagement.CacheHandler.Add(CacheKey, Cache);
var db = new ProviderPortalEntities();
foreach (UserProvisionHistory item in db.UserProvisionHistories.Where(x => x.UserId == UserId).ToList())
{
db.Entry(item).State = EntityState.Deleted;
}
db.SaveChanges();
int displayOrder = 0;
foreach (var item in Cache.Organisations)
{
db.UserProvisionHistories.Add(new UserProvisionHistory
{
UserId = UserId,
OrganisationId = Int32.Parse(item.Value.Substring(1)),
DisplayOrder = ++displayOrder
});
}
displayOrder = 0;
foreach (var item in Cache.Providers)
{
db.UserProvisionHistories.Add(new UserProvisionHistory
{
UserId = UserId,
ProviderId = Int32.Parse(item.Value.Substring(1)),
DisplayOrder = ++displayOrder
});
}
db.SaveChanges();
}
/// <summary>
/// Finds all permissions from the constants in the Permissions Class, and adds the missing ones to the DB.
/// </summary>
public static void AddMissingPermissions()
{
// Get Database Permissions
using (ProviderPortalEntities databaseContext = new ProviderPortalEntities())
{
var permissions = from perm in databaseContext.Permissions
select new { PermissionId = perm.PermissionId };
System.Array enumValues = System.Enum.GetValues(typeof(Permission.PermissionName));
for (int index = 1; index < enumValues.Length; index++)
{
int permissionId = index;
if (!permissions.Any(p => p.PermissionId == permissionId))
{
// Add permission
string permissionName = ((Permission.PermissionName)(int) enumValues.GetValue(index)).ToString();
Entities.Permission newPermission = new Entities.Permission
{
PermissionName = permissionName,
PermissionId = permissionId,
PermissionDescription =
"Automatically added by the site code, description required"
};
databaseContext.Permissions.Add(newPermission);
}
}
databaseContext.SaveChanges();
}
}
public static void ImportLARSFile(String userId, Int32 languageId)
{
ProviderPortalEntities db = new ProviderPortalEntities();
String LARSFolder = Constants.ConfigSettings.LARSUploadVirtualDirectoryName;
if (LARSFolder.EndsWith(@"\"))
{
LARSFolder = LARSFolder.Substring(0, LARSFolder.Length - 1);
}
// Check if config setting is valid
if (String.IsNullOrEmpty(LARSFolder) || !Directory.Exists(LARSFolder))
{
throw new Exception(AppGlobal.Language.GetText("LARS_ImportLARSFile_LARSFolderNotConfigured", "Configuration setting VirtualDirectoryNameForStoringLARSFiles is not set or is incorrect", false, languageId));
}
// Unzip the file(s) - Should only be 1 really
foreach (String zipFile in Directory.GetFiles(LARSFolder, "*.zip"))
{
System.IO.Compression.ZipFile.ExtractToDirectory(zipFile, LARSFolder);
System.IO.File.Delete(zipFile);
}
// Get the MDB Filename
String[] mdbFiles = Directory.GetFiles(LARSFolder, "*.mdb");
if (mdbFiles.GetLength(0) == 0)
{
throw new Exception(AppGlobal.Language.GetText("LARS_ImportLARSFile_UnableToFindMDBFile", "Unable to find MDB file in ZIP file", false, languageId));
}
mdbFilename = mdbFiles[0];
mdbFileSize = new FileInfo(mdbFilename).Length;
// Open the database
using (SqlConnection conn = new SqlConnection(db.Database.Connection.ConnectionString))
{
// Open the database connection
conn.Open();
// Import the data
ImportClassifications(conn, userId, db);
ImportAwardingOrganisation(conn, userId, db);
ImportLearningAims(conn, userId, db);
ImportValidity(conn, userId, db);
ImportFrameworksAndStandards(conn, userId, db);
// Close the database
conn.Close();
}
// Save metadata upload records
db.SaveChanges();
}
public static void CompleteAutomatedTask(AutomatedTaskName taskName)
{
String name = taskName.ToString();
ProviderPortalEntities db = new ProviderPortalEntities();
AutomatedTask at = db.AutomatedTasks.Where(x => x.TaskName == name).FirstOrDefault();
if (at != null)
{
at.InProgress = false;
db.Entry(at).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
/// <summary>
/// Create a new language.
/// </summary>
/// <param name="newLanguageName">The language name (English, French)</param>
/// <param name="newLanguageIetf">The two-letter IETF code for the language (en, fr)</param>
/// <returns>True when language creation succeeds.</returns>
public bool CreateNewLanguage(string newLanguageName, string newLanguageIetf)
{
var languages = _db.up_LanguageList(null, null);
if (
languages.Any(
x =>
x.IETF.Equals(newLanguageIetf, StringComparison.CurrentCultureIgnoreCase) ||
x.DefaultText.Equals(newLanguageName, StringComparison.CurrentCultureIgnoreCase)))
{
return(false);
}
CultureInfo ci = null;
try
{
ci = CultureInfo.GetCultureInfo(newLanguageIetf);
}
catch (CultureNotFoundException) { }
var language = new Language
{
DefaultText = newLanguageName,
IETF = newLanguageIetf,
SqlLanguageId = ci == null ? (int?)null : ci.TextInfo.LCID,
IsDefaultLanguage = false,
};
_db.Languages.Add(language);
_db.SaveChanges();
language.LanguageFieldName = String.Format("Table_Language_{0}", language.LanguageID);
_db.SaveChanges();
return(true);
}
private static void AddOrReplaceProgressMessage(ProviderPortalEntities _db, String message, Boolean isComplete = false)
{
ProgressMessage pm = _db.ProgressMessages.Find(MessageArea);
if (pm != null)
{
_db.Entry(pm).State = EntityState.Modified;
}
else
{
pm = new ProgressMessage
{
MessageArea = MessageArea
};
_db.Entry(pm).State = EntityState.Added;
}
pm.MessageText = message;
pm.IsComplete = isComplete;
_db.SaveChanges();
}
/// <summary>
/// Update a role name in the configuration settings.
/// </summary>
/// <param name="oldName">Old role name.</param>
/// <param name="newName">New role name.</param>
public void RenameConfiguredRoles(string oldName, string newName)
{
var roleConfigSettings = new List <string>
{
"AdminContextCanAddRoles",
"AdminUserCanAddRoles",
"OrganisationContextCanAddRoles",
"OrganisationUserCanAddRoles",
"ProviderContextCanAddRoles",
"ProviderUserCanAddRoles",
"SAUserRolePrimary",
"SAUserRoleSecondary"
};
oldName = oldName.Trim();
newName = newName.Trim();
using (var db = new ProviderPortalEntities())
{
foreach (var settingName in roleConfigSettings)
{
var configSetting = db.ConfigurationSettings.Find(settingName);
var roles = configSetting.Value.Trim().Split(';');
for (var i = 0; i < roles.Length; i++)
{
if (roles[i].Equals(oldName, StringComparison.CurrentCultureIgnoreCase))
{
roles[i] = newName;
}
}
configSetting.Value = string.Join(";", roles);
}
db.SaveChanges();
}
Constants.ConfigSettings.Refresh();
}
/// <summary>
/// Backend routine for saving, archiving, publishing and updating content availability new and existing content.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="model">The model.</param>
/// <returns></returns>
public static List <KeyValuePair <string, string> > HandleContentEdit(ProviderPortalEntities db, AddEditContentViewModel model)
{
var errors = new List <KeyValuePair <string, string> >();
var invalidOperation = AppGlobal.Language.GetText("SiteContent_Edit_InvalidOperation",
"That operation is invalid for the current content.");
var notFound = AppGlobal.Language.GetText("SiteContent_Edit_NotFound",
"This content is no longer available.");
if (String.IsNullOrWhiteSpace(model.SubmitAction))
{
errors.Add(new KeyValuePair <string, string>("", invalidOperation));
return(errors);
}
// Save
if (model.SubmitAction.Equals("Save", StringComparison.CurrentCultureIgnoreCase))
{
var content = model.ToEntity(db);
if (content.ContentId == 0)
{
db.Contents.Add(content);
}
return(errors);
}
var item = db.Contents.FirstOrDefault(x => x.ContentId == model.ContentId);
if (item == null)
{
errors.Add(new KeyValuePair <string, string>("", notFound));
return(errors);
}
if (model.SubmitAction.Equals("UpdateAvailability", StringComparison.CurrentCultureIgnoreCase) &&
item.RecordStatusId == (int)Constants.RecordStatus.Live)
{
EnsureContextAvailability(db, model.ContentId, model.Path, model.UserContext);
item.UserContext = (int)model.UserContext;
item.ModifiedByUserId = Permission.GetCurrentUserId();
item.ModifiedDateTimeUtc = DateTime.UtcNow;
db.SaveChanges();
return(errors);
}
if (model.SubmitAction.Equals("Publish", StringComparison.CurrentCultureIgnoreCase) &&
item.RecordStatusId != (int)Constants.RecordStatus.Live)
{
EnsureContextAvailability(db, model.ContentId, model.Path, model.UserContext);
item.RecordStatusId = (int)Constants.RecordStatus.Live;
item.ModifiedByUserId = Permission.GetCurrentUserId();
item.ModifiedDateTimeUtc = DateTime.UtcNow;
db.SaveChanges();
return(errors);
}
if (model.SubmitAction.Equals("Archive", StringComparison.CurrentCultureIgnoreCase) &&
item.RecordStatusId != (int)Constants.RecordStatus.Archived)
{
item.RecordStatusId = (int)Constants.RecordStatus.Archived;
item.ModifiedByUserId = Permission.GetCurrentUserId();
item.ModifiedDateTimeUtc = DateTime.UtcNow;
db.SaveChanges();
return(errors);
}
errors.Add(new KeyValuePair <string, string>("", invalidOperation));
return(errors);
}
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));
}
public ActionResult StartImport()
{
// Check if import is already in progress.
ProgressMessage pm = db.ProgressMessages.Find(MessageArea);
if (pm != null)
{
if (!pm.IsComplete)
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "ImportInProgress", "Import Already In Progress"));
}
else
{
DeleteProgressMessage();
}
}
String AddressBaseFolder = Constants.ConfigSettings.AddressBaseUploadVirtualDirectoryName;
if (AddressBaseFolder.EndsWith(@"\"))
{
AddressBaseFolder = AddressBaseFolder.Substring(0, AddressBaseFolder.Length - 1);
}
// Check if config setting is valid
if (String.IsNullOrEmpty(AddressBaseFolder) || !Directory.Exists(AddressBaseFolder))
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "AddressBaseFolderNotConfigured", "Configuration setting VirtualDirectoryNameForStoringAddressBaseFiles is not set or is incorrect"));
DeleteProgressMessage();
}
if (ModelState.IsValid)
{
// Get the CSV Filenames
String[] zipFiles = Directory.GetFiles(AddressBaseFolder, "*.zip");
if (zipFiles.GetLength(0) == 0)
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "UnableToFindZIPFile", "Unable to find ZIP file(s) 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 unzippingMessageText = AppGlobal.Language.GetText(this, "UnzippingFileXOfY", "Unzipping 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", "Address Base Data Successfully Imported");
String importCancelledMessageText = AppGlobal.Language.GetText(this, "ImportCancelled", "Address Base Data Import Cancelled");
String importErrorMessageText = AppGlobal.Language.GetText(this, "ImportError", "Error Importing Address Base : {0}");
String userId = Permission.GetCurrentUserId();
new Thread(() =>
{
try
{
ProviderPortalEntities _db = new ProviderPortalEntities();
const Int32 UPRN = 0;
const Int32 ORGANISATION_NAME = 3;
const Int32 DEPARTMENT_NAME = 4;
const Int32 PO_BOX_NUMBER = 5;
const Int32 SUB_BUILDING_NAME = 6;
const Int32 BUILDING_NAME = 7;
const Int32 BUILDING_NUMBER = 8;
const Int32 DEPENDENT_THOROUGHFARE_NAME = 9;
const Int32 THOROUGHFARE_NAME = 10;
const Int32 POST_TOWN = 11;
const Int32 DOUBLE_DEPENDENT_LOCALITY = 12;
const Int32 DEPENDENT_LOCALITY = 13;
const Int32 POSTCODE = 14;
const Int32 CHANGE_TYPE = 22;
const Int32 LATITUDE = 18;
const Int32 LONGITUDE = 19;
var totalSize = 0;
var fileNames = String.Empty;
foreach (var item in zipFiles)
{
totalSize += (int)new FileInfo(item).Length;
fileNames += Path.GetFileName(item) + ";";
}
var metadataUpload = new MetadataUpload
{
MetadataUploadTypeId = (int)Constants.MetadataUploadType.AddressBase,
CreatedByUserId = userId,
CreatedDateTimeUtc = DateTime.UtcNow,
FileName = fileNames.TrimEnd(';'),
FileSizeInBytes = totalSize,
RowsBefore = _db.AddressBases.Count()
};
var sw = new Stopwatch();
sw.Start();
// Open the database
SqlConnection conn = new SqlConnection(_db.Database.Connection.ConnectionString);
conn.Open();
// Truncate the temporary import table just incase there's still data in there.
SqlCommand comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_AddressBase];", conn);
comm.ExecuteNonQuery();
// Setup the DataTable
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn {
ColumnName = "UPRN", AllowDBNull = false, DataType = typeof(Int64)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Postcode", AllowDBNull = false, DataType = typeof(String), MaxLength = 8
});
dt.Columns.Add(new DataColumn {
ColumnName = "OrganisationName", AllowDBNull = true, DataType = typeof(String), MaxLength = 60
});
dt.Columns.Add(new DataColumn {
ColumnName = "DepartmentName", AllowDBNull = true, DataType = typeof(String), MaxLength = 60
});
dt.Columns.Add(new DataColumn {
ColumnName = "POBoxNumber", AllowDBNull = true, DataType = typeof(String), MaxLength = 6
});
dt.Columns.Add(new DataColumn {
ColumnName = "BuildingName", AllowDBNull = true, DataType = typeof(String), MaxLength = 50
});
dt.Columns.Add(new DataColumn {
ColumnName = "SubBuildingName", AllowDBNull = true, DataType = typeof(String), MaxLength = 30
});
dt.Columns.Add(new DataColumn {
ColumnName = "BuildingNumber", AllowDBNull = true, DataType = typeof(Int32)
});
dt.Columns.Add(new DataColumn {
ColumnName = "DependentThoroughfareName", AllowDBNull = true, DataType = typeof(String), MaxLength = 80
});
dt.Columns.Add(new DataColumn {
ColumnName = "ThoroughfareName", AllowDBNull = true, DataType = typeof(String), MaxLength = 80
});
dt.Columns.Add(new DataColumn {
ColumnName = "Town", AllowDBNull = true, DataType = typeof(String), MaxLength = 30
});
dt.Columns.Add(new DataColumn {
ColumnName = "DoubleDependentLocality", AllowDBNull = true, DataType = typeof(String), MaxLength = 35
});
dt.Columns.Add(new DataColumn {
ColumnName = "DependentLocality", AllowDBNull = true, DataType = typeof(String), MaxLength = 35
});
dt.Columns.Add(new DataColumn {
ColumnName = "Latitude", AllowDBNull = true, DataType = typeof(Decimal)
});
dt.Columns.Add(new DataColumn {
ColumnName = "Longitude", AllowDBNull = true, DataType = typeof(Decimal)
});
Int32 i = 1;
foreach (String zipFile in zipFiles)
{
// Write the progress message
AddOrReplaceProgressMessage(_db, String.Format(unzippingMessageText, i, zipFiles.GetLength(0)));
// Unzip the file
System.IO.Compression.ZipFile.ExtractToDirectory(zipFile, AddressBaseFolder);
// Delete the zip file
TryToDeleteFile(zipFile);
String[] csvFiles = Directory.GetFiles(AddressBaseFolder, "*.csv");
if (csvFiles.GetLength(0) == 0)
{
ModelState.AddModelError("", AppGlobal.Language.GetText(this, "UnableToFindCSVFile", "Unable to find CSV file to import"));
DeleteProgressMessage();
}
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++, zipFiles.GetLength(0)));
// Import the CSV file
using (CsvReader csv = new CsvReader(new StreamReader(csvFile)))
{
csv.Configuration.HasHeaderRecord = false;
while (csv.Read())
{
if (csv[CHANGE_TYPE] == "D")
{
AddressBase addressBase = _db.AddressBases.Find(Convert.ToInt32(csv[UPRN]));
if (addressBase != null)
{
_db.Entry(addressBase).State = EntityState.Deleted;
_db.SaveChanges();
}
}
else
{
DataRow dr = dt.NewRow();
dr["UPRN"] = Convert.ToInt64(csv[UPRN]);
dr["Postcode"] = csv[POSTCODE];
dr["OrganisationName"] = csv[ORGANISATION_NAME];
dr["DepartmentName"] = csv[DEPARTMENT_NAME];
dr["POBoxNumber"] = csv[PO_BOX_NUMBER];
dr["BuildingName"] = csv[BUILDING_NAME];
dr["SubBuildingName"] = csv[SUB_BUILDING_NAME];
dr["BuildingNumber"] = csv[BUILDING_NUMBER] == "" ? DBNull.Value : (Object)Convert.ToInt32(csv[BUILDING_NUMBER]);
dr["DependentThoroughfareName"] = csv[DEPENDENT_THOROUGHFARE_NAME];
dr["ThoroughfareName"] = csv[THOROUGHFARE_NAME];
dr["Town"] = csv[POST_TOWN];
dr["DoubleDependentLocality"] = csv[DOUBLE_DEPENDENT_LOCALITY];
dr["DependentLocality"] = csv[DEPENDENT_LOCALITY];
dr["Latitude"] = csv[LATITUDE] == "" ? (Object)DBNull.Value : Convert.ToDecimal(csv[LATITUDE]);
dr["Longitude"] = csv[LONGITUDE] == "" ? (Object)DBNull.Value : Convert.ToDecimal(csv[LONGITUDE]);
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
TryToDeleteFile(csvFile);
}
if (!cancellingImport)
{
// Copy the data to the Import_BaseAddress Table
BulkImportData(conn, dt);
}
}
}
cancellingImport = IsCancellingImport(new ProviderPortalEntities());
if (!cancellingImport)
{
// Merge the data into the AddressBase Table
AddOrReplaceProgressMessage(_db, mergingMessageText);
comm = new SqlCommand("MERGE [dbo].[AddressBase] dest USING [dbo].[Import_AddressBase] source ON dest.UPRN = source.UPRN WHEN MATCHED THEN UPDATE SET dest.Postcode = source.Postcode, dest.OrganisationName = source.OrganisationName, dest.DepartmentName = source.DepartmentName, dest.POBoxNumber = source.POBoxNumber, dest.BuildingName = source.BuildingName, dest.SubBuildingname = source.SubBuildingName, dest.BuildingNumber = source.BuildingNumber, dest.DependentThoroughfareName = source.DependentThoroughfareName, dest.ThoroughfareName = source.ThoroughfareName, dest.Town = source.Town, dest.DoubleDependentLocality = source.DoubleDependentLocality, dest.DependentLocality = source.DependentLocality, dest.Latitude = source.Latitude, dest.Longitude = source.Longitude WHEN NOT MATCHED THEN INSERT (UPRN, Postcode, OrganisationName, DepartmentName, POBoxNumber, BuildingName, SubBuildingName, BuildingNumber, DependentThoroughfareName, ThoroughfareName, Town, DoubleDependentLocality, DependentLocality, Latitude, Longitude) VALUES (source.UPRN, source.Postcode, source.OrganisationName, source.DepartmentName, source.POBoxNumber, source.BuildingName, source.SubBuildingName, source.BuildingNumber, source.DependentThoroughfareName, source.ThoroughfareName, source.Town, source.DoubleDependentLocality, source.DependentLocality, source.Latitude, source.Longitude);", conn)
{
CommandTimeout = 7200 /* 2 Hours */
};
comm.ExecuteNonQuery();
}
// Truncate the temporary import table
if (!cancellingImport)
{
AddOrReplaceProgressMessage(_db, removingTempDataMessageText);
}
comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_AddressBase];", conn)
{
CommandTimeout = 3600 /* 1 Hours */
};
comm.ExecuteNonQuery();
// Close the database
conn.Close();
// Save timings
if (!cancellingImport)
{
sw.Stop();
_db.Database.CommandTimeout = 3600; /* 1 Hour */
metadataUpload.DurationInMilliseconds = (int)sw.ElapsedMilliseconds;
metadataUpload.RowsAfter = _db.AddressBases.Count();
_db.MetadataUploads.Add(metadataUpload);
_db.SaveChanges();
}
// Delete all the uploaded and expanded files (if any still exist)
try
{
foreach (FileInfo file in new DirectoryInfo(AddressBaseFolder).GetFiles())
{
file.Delete();
}
}
catch {}
// Write Success or Cancelled message
AddOrReplaceProgressMessage(_db, cancellingImport ? importCancelledMessageText : importSuccessfulMessageText, true);
}
catch (Exception ex)
{
// Log the error
AppGlobal.Log.WriteError(String.Format("Error importing AddressBase: Message:{0}, Inner Message:{1}", ex.Message, ex.InnerException != null ? ex.InnerException.Message : ""));
// Write Error to Display to User
AddOrReplaceProgressMessage(new ProviderPortalEntities(), String.Format(importErrorMessageText, ex.InnerException != null ? ex.InnerException.Message : ex.Message), true);
// Delete all the uploaded and expanded files (if any still exist)
try
{
foreach (FileInfo file in new DirectoryInfo(AddressBaseFolder).GetFiles())
{
file.Delete();
}
}
catch {}
}
}).Start();
}
}
if (ModelState.IsValid)
{
return(RedirectToAction("Index"));
}
GetViewData();
return(View("Index", new UploadAddressBaseModel()));
}
public static void ImportRoATPData(Object stateInfo)
{
Boolean automatedTaskStarted = false;
try
{
if (String.IsNullOrEmpty(Constants.ConfigSettings.RoATPAPIImportTime))
{
// Log Warning about RoATPAPIImportTime not being set
AppGlobal.Log.WriteError(AppGlobal.Language.GetText("Automation_RoATPAPIImport_ImportTimeNotConfigured", "Error Importing RoATP Data. RoATPAPIImportTime Not Configured", false, languageId));
return;
}
if (DateTime.Now.ToString("HH:mm") == Constants.ConfigSettings.RoATPAPIImportTime)
{
// Ensure that another server hasn't picked this up
if (!CanRunAutomatedTask(AutomatedTaskName.RoATPAPI))
{
AppGlobal.Log.WriteLog(AppGlobal.Language.GetText("Automation_RoATPImport_Running", "Automated RoATP API data import running on a different server", false, languageId));
return;
}
automatedTaskStarted = true;
// Do the import
new Thread(() =>
{
try
{
using (ProviderPortalEntities db = new ProviderPortalEntities())
{
List <Int32> ukprns = new List <Int32>();
using (RoatpApiClient client = new RoatpApiClient())
{
foreach (SFA.Roatp.Api.Types.Provider apiProvider in client.FindAll())
{
if (apiProvider.ProviderType == SFA.Roatp.Api.Types.ProviderType.MainProvider)
{
if (apiProvider.Ukprn <= Int32.MaxValue) // Should always be but just incase
{
ukprns.Add(Convert.ToInt32(apiProvider.Ukprn));
}
}
List <Provider> providers = db.Providers.Where(x => x.Ukprn == apiProvider.Ukprn && x.RecordStatusId == (Int32)Constants.RecordStatus.Live).ToList();
if (providers.Count > 0)
{
foreach (Provider provider in providers)
{
RoATPProviderType providerType = db.RoATPProviderTypes.Find((Int32)apiProvider.ProviderType);
if (providerType != null)
{
if (provider.RoATPProviderType != providerType || provider.RoATPStartDate != apiProvider.StartDate || provider.ApprenticeshipContract != (apiProvider.ProviderType == SFA.Roatp.Api.Types.ProviderType.MainProvider))
{
// Provider found and updated - log it.
AppGlobal.Log.WriteLog(String.Format(AppGlobal.Language.GetText("Automation_RoATPImport_ProviderUpdated", "Automated RoATP API data import updated provider {0} for ukprn {1}", false, languageId), provider.ProviderId, provider.Ukprn));
provider.RoATPProviderType = providerType;
provider.RoATPStartDate = apiProvider.StartDate;
provider.ApprenticeshipContract = apiProvider.ProviderType == SFA.Roatp.Api.Types.ProviderType.MainProvider;
db.Entry(provider).State = EntityState.Modified;
}
}
}
}
else
{
// Provider not found - log it
AppGlobal.Log.WriteLog(String.Format(AppGlobal.Language.GetText("Automation_RoATPImport_ProviderNotFound", "Automated RoATP API data import no provider found for ukprn {0}", false, languageId), apiProvider.Ukprn));
}
}
}
// Get current apprenticeship provers who are not in the API
foreach (Provider provider in db.Providers.Where(x => x.ApprenticeshipContract == true && !ukprns.Contains(x.Ukprn)))
{
// Log it
AppGlobal.Log.WriteLog(String.Format(AppGlobal.Language.GetText("Automation_RoATPImport_RemovedProviderFromRoATP", "Automated RoATP API data import removed provider {0} from RoATP, ukprn {1}", false, languageId), provider.ProviderId, provider.Ukprn));
provider.ApprenticeshipContract = false;
db.Entry(provider).State = EntityState.Modified;
}
// Save the changes
db.SaveChanges();
}
// Complete the task
CompleteAutomatedTask(AutomatedTaskName.RoATPAPI);
}
catch (Exception ex)
{
// Send Email
foreach (String address in Constants.ConfigSettings.RoATPImportErrorEmailAddress.Split(';'))
{
//AppGlobal.EmailQueue.AddToSendQueue(
// TemplatedEmail.EmailMessage(
// new MailAddress(address),
// null,
// new MailAddress(Constants.ConfigSettings.AutomatedFromEmailAddress, Constants.ConfigSettings.AutomatedFromEmailName),
// Constants.EmailTemplates.RoATPImportError,
// new List<EmailParameter>
// {
// new EmailParameter("%EXCEPTION%", ex.Message),
// new EmailParameter("%STACKTRACE%", ex.StackTrace)
// },
// AppGlobal.Language.GetText("TemplatedEmail_EmailOverride_FormatString", "<p>This email was originally sent to {0}:<p>{1}", false, languageId)));
var emailMessage = TemplatedEmail.EmailMessage(
new MailAddress(address),
null,
new MailAddress(Constants.ConfigSettings.AutomatedFromEmailAddress, Constants.ConfigSettings.AutomatedFromEmailName),
Constants.EmailTemplates.RoATPImportError,
new List <EmailParameter>
{
new EmailParameter("%EXCEPTION%", ex.Message),
new EmailParameter("%STACKTRACE%", ex.StackTrace)
},
AppGlobal.Language.GetText("TemplatedEmail_EmailOverride_FormatString", "<p>This email was originally sent to {0}:<p>{1}", false, languageId));
var response = SfaSendGridClient.SendGridEmailMessage(emailMessage, null);
}
AppGlobal.Log.WriteError(String.Format(AppGlobal.Language.GetText("Automation_RoATPImport_GenericError", "Automated RoATP API Data Importer Failed With Error: {0}", false, languageId), ex.Message));
if (automatedTaskStarted)
{
CompleteAutomatedTask(AutomatedTaskName.RoATPAPI);
}
}
}).Start();
}
}
catch (Exception ex)
{
AppGlobal.Log.WriteError(String.Format(AppGlobal.Language.GetText("Automation_RoATPImport_GenericError", "Automated RoATP API Data Importer Failed With Error: {0}", false, languageId), ex.Message));
if (automatedTaskStarted)
{
CompleteAutomatedTask(AutomatedTaskName.RoATPAPI);
}
}
}
