/// <summary>
/// Deletes the <see cref="Apprenticeship"/> and also deletes it's associated <see cref="CourseInstance"/>s-->
/// </summary>
/// <param name="apprenticeship">The <see cref="Course"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this Apprenticeship apprenticeship, ProviderPortalEntities db)
{
foreach (ApprenticeshipLocation apprenticeshipLocation in apprenticeship.ApprenticeshipLocations.ToList())
{
foreach (DeliveryMode deliveryMode in apprenticeshipLocation.DeliveryModes.ToList())
{
apprenticeshipLocation.DeliveryModes.Remove(deliveryMode);
}
db.Entry(apprenticeshipLocation).State = EntityState.Deleted;
}
foreach (ApprenticeshipQACompliance qaCompliance in apprenticeship.ApprenticeshipQACompliances.ToList())
{
foreach (QAComplianceFailureReason complianceFailureReason in qaCompliance.QAComplianceFailureReasons.ToList())
{
qaCompliance.QAComplianceFailureReasons.Remove(complianceFailureReason);
}
db.Entry(qaCompliance).State = EntityState.Deleted;
}
foreach (ApprenticeshipQAStyle qaStyle in apprenticeship.ApprenticeshipQAStyles.ToList())
{
foreach (QAStyleFailureReason styleFailureReason in qaStyle.QAStyleFailureReasons.ToList())
{
qaStyle.QAStyleFailureReasons.Remove(styleFailureReason);
}
db.Entry(qaStyle).State = EntityState.Deleted;
}
db.Entry(apprenticeship).State = EntityState.Deleted;
}
/// <summary>
/// Deletes the <see cref="Course"/> and also deletes it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="course">The <see cref="Course"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this Course course, ProviderPortalEntities db)
{
foreach (CourseInstance courseInstance in course.CourseInstances.ToList())
{
course.CourseInstances.Remove(courseInstance);
courseInstance.Delete(db);
}
foreach (CourseLearnDirectClassification courseDirectClassification in course.CourseLearnDirectClassifications.ToList())
{
db.Entry(courseDirectClassification).State = EntityState.Deleted;
}
db.Entry(course).State = EntityState.Deleted;
}
/// <summary>
/// Unarchives the <see cref="SearchPhrase"/> and also manages status of it's associated <see cref="Course"/>
/// </summary>
/// <param name="SearchPhrase">The <see cref="SearchPhrase"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this SearchPhrase SearchPhrase, ProviderPortalEntities db)
{
SearchPhrase.RecordStatusId = (Int32)Constants.RecordStatus.Live;
SearchPhrase.ModifiedByUserId = Permission.GetCurrentUserId();
SearchPhrase.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(SearchPhrase).State = EntityState.Modified;
}
/// <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>
/// Unarchives the <see cref="Apprenticeship"/>
/// </summary>
/// <param name="apprenticeship">The <see cref="Apprenticeship"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this Apprenticeship apprenticeship, ProviderPortalEntities db)
{
apprenticeship.RecordStatusId = (Int32)Constants.RecordStatus.Pending;
apprenticeship.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeship.ModifiedDateTimeUtc = DateTime.UtcNow;
apprenticeship.ModifiedByUserId = Permission.GetCurrentUserId();
db.Entry(apprenticeship).State = EntityState.Modified;
}
/// <summary>
/// Unarchives the <see cref="Course"/>
/// </summary>
/// <param name="course">The <see cref="Course"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this Course course, ProviderPortalEntities db)
{
course.RecordStatusId = (Int32)Constants.RecordStatus.Pending;
course.AddedByApplicationId = (Int32)Constants.Application.Portal;
course.ModifiedDateTimeUtc = DateTime.UtcNow;
course.ModifiedByUserId = Permission.GetCurrentUserId();
db.Entry(course).State = EntityState.Modified;
}
/// <summary>
/// Deletes the <see cref="Location"/> and also archives it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="location">The <see cref="Location"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this Location location, ProviderPortalEntities db)
{
foreach (ApprenticeshipLocation apprenticeshipLocation in location.ApprenticeshipLocations.ToList())
{
apprenticeshipLocation.Delete(db);
}
db.Entry(location).State = EntityState.Deleted;
}
/// <summary>
/// Deletes the <see cref="Venue"/> and also archives it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="venue">The <see cref="Venue"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this Venue venue, ProviderPortalEntities db)
{
foreach (CourseInstance courseInstance in venue.CourseInstances.ToList())
{
courseInstance.Archive(db);
}
db.Entry(venue).State = EntityState.Deleted;
}
/// <summary>
/// Archives the <see cref="ApprenticeshipLocation"/> and also manages status of it's associated <see cref="Apprenticeship"/>
/// </summary>
/// <param name="apprenticeshipLocation">The <see cref="ApprenticeshipLocation"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Archive(this ApprenticeshipLocation apprenticeshipLocation, ProviderPortalEntities db)
{
// Check whether apprenticeship status should be changed to pending
ChangeApprenticeshipStatusToPending(apprenticeshipLocation, db);
apprenticeshipLocation.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
apprenticeshipLocation.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeshipLocation.ModifiedByUserId = Permission.GetCurrentUserId();
apprenticeshipLocation.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(apprenticeshipLocation).State = EntityState.Modified;
}
/// <summary>
/// Unarchives the <see cref="CourseInstance"/> and also manages status of it's associated <see cref="Course"/>
/// </summary>
/// <param name="courseInstance">The <see cref="CourseInstance"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this CourseInstance courseInstance, ProviderPortalEntities db)
{
// Set the course to LIVE if not currently LIVE
Course course = courseInstance.Course;
if (course.RecordStatusId != (Int32)Constants.RecordStatus.Live)
{
course.RecordStatusId = (Int32)Constants.RecordStatus.Live;
course.AddedByApplicationId = (Int32)Constants.Application.Portal;
course.ModifiedByUserId = Permission.GetCurrentUserId();
course.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(course).State = EntityState.Modified;
}
courseInstance.RecordStatusId = (Int32)Constants.RecordStatus.Live;
courseInstance.AddedByApplicationId = (Int32)Constants.Application.Portal;
courseInstance.ModifiedByUserId = Permission.GetCurrentUserId();
courseInstance.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(courseInstance).State = EntityState.Modified;
}
/// <summary>
/// Archives the <see cref="CourseInstance"/> and also manages status of it's associated <see cref="Course"/>
/// </summary>
/// <param name="courseInstance">The <see cref="CourseInstance"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Archive(this CourseInstance courseInstance, ProviderPortalEntities db)
{
// Check whether course status should be changed to pending
ChangeCourseStatusToPending(courseInstance, db);
courseInstance.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
courseInstance.AddedByApplicationId = (Int32)Constants.Application.Portal;
courseInstance.ModifiedByUserId = Permission.GetCurrentUserId();
courseInstance.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(courseInstance).State = EntityState.Modified;
}
/// <summary>
/// Unarchives the <see cref="ApprenticeshipLocation"/> and also manages status of it's associated <see cref="Apprenticeship"/>
/// </summary>
/// <param name="apprenticeshipLocation">The <see cref="ApprenticeshipLocation"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this ApprenticeshipLocation apprenticeshipLocation, ProviderPortalEntities db)
{
// Set the apprenticeship to LIVE if not currently LIVE
Apprenticeship apprenticeship = apprenticeshipLocation.Apprenticeship;
if (apprenticeship.RecordStatusId != (Int32)Constants.RecordStatus.Live)
{
apprenticeship.RecordStatusId = (Int32)Constants.RecordStatus.Live;
apprenticeship.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeship.ModifiedByUserId = Permission.GetCurrentUserId();
apprenticeship.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(apprenticeship).State = EntityState.Modified;
}
apprenticeshipLocation.RecordStatusId = (Int32)Constants.RecordStatus.Live;
apprenticeshipLocation.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeshipLocation.ModifiedByUserId = Permission.GetCurrentUserId();
apprenticeshipLocation.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(apprenticeshipLocation).State = EntityState.Modified;
}
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>
/// Deletes the <see cref="ApprenticeshipLocation"/> and also manages status of it's associated <see cref="Apprenticeship"/>
/// </summary>
/// <param name="apprenticeshipLocation">The <see cref="ApprenticeshipLocation"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this ApprenticeshipLocation apprenticeshipLocation, ProviderPortalEntities db)
{
// Check whether apprenticeship status should be changed to pending
ChangeApprenticeshipStatusToPending(apprenticeshipLocation, db);
foreach (DeliveryMode item in apprenticeshipLocation.DeliveryModes.ToList())
{
apprenticeshipLocation.DeliveryModes.Remove(item);
}
db.Entry(apprenticeshipLocation).State = EntityState.Deleted;
}
/// <summary>
/// Archives the <see cref="Apprenticeship"/> and also manages status of it's associated <see cref="ApprenticeshipLocation"/>s
/// </summary>
/// <param name="apprenticeship">The <see cref="Apprenticeship"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Archive(this Apprenticeship apprenticeship, ProviderPortalEntities db)
{
foreach (ApprenticeshipLocation apprenticeshipLocation in apprenticeship.ApprenticeshipLocations.ToList())
{
apprenticeshipLocation.Archive(db);
}
apprenticeship.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
apprenticeship.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeship.ModifiedDateTimeUtc = DateTime.UtcNow;
apprenticeship.ModifiedByUserId = Permission.GetCurrentUserId();
db.Entry(apprenticeship).State = EntityState.Modified;
}
/// <summary>
/// Archives the <see cref="Course"/> and also manages status of it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="course">The <see cref="Course"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Archive(this Course course, ProviderPortalEntities db)
{
foreach (CourseInstance courseInstance in course.CourseInstances.ToList())
{
courseInstance.Archive(db);
}
course.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
course.AddedByApplicationId = (Int32)Constants.Application.Portal;
course.ModifiedDateTimeUtc = DateTime.UtcNow;
course.ModifiedByUserId = Permission.GetCurrentUserId();
db.Entry(course).State = EntityState.Modified;
}
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>
/// Archives the <see cref="Location"/> and also manages status of it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="location">The <see cref="Location"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
/// <param name="archiveApprenticeships">If true <see cref="Apprenticeship"/>s assigned to this <see cref="Location"/> will also be archived</param>
public static void Archive(this Location location, ProviderPortalEntities db, Boolean archiveApprenticeships)
{
if (archiveApprenticeships)
{
foreach (ApprenticeshipLocation apprenticeshipLocation in location.ApprenticeshipLocations.ToList())
{
apprenticeshipLocation.Archive(db);
}
}
location.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
db.Entry(location).State = EntityState.Modified;
}
/// <summary>
/// Archives the <see cref="Venue"/> and also manages status of it's associated <see cref="CourseInstance"/>s
/// </summary>
/// <param name="venue">The <see cref="Venue"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
/// <param name="archiveCourseInstances">If true <see cref="CourseInstance"/>s assigned to this <see cref="Venue"/> will also be archived</param>
public static void Archive(this Venue venue, ProviderPortalEntities db, Boolean archiveCourseInstances)
{
if (archiveCourseInstances)
{
foreach (CourseInstance courseInstance in venue.CourseInstances.ToList())
{
courseInstance.Archive(db);
}
}
venue.RecordStatusId = (Int32)Constants.RecordStatus.Archived;
db.Entry(venue).State = EntityState.Modified;
}
/// <summary>
/// Deletes the <see cref="CourseInstance"/> and also manages status of it's associated <see cref="Course"/>
/// </summary>
/// <param name="courseInstance">The <see cref="CourseInstance"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this CourseInstance courseInstance, ProviderPortalEntities db)
{
// Check whether course status should be changed to pending
ChangeCourseStatusToPending(courseInstance, db);
foreach (A10FundingCode a10 in courseInstance.A10FundingCode.ToList())
{
courseInstance.A10FundingCode.Remove(a10);
}
foreach (CourseInstanceStartDate sd in courseInstance.CourseInstanceStartDates.ToList())
{
courseInstance.CourseInstanceStartDates.Remove(sd);
db.Entry(sd).State = EntityState.Deleted;
}
foreach (Venue venue in courseInstance.Venues.ToList())
{
courseInstance.Venues.Remove(venue);
}
db.Entry(courseInstance).State = EntityState.Deleted;
}
/// <summary>
/// Checks whether the <see cref="Course"/>'s status need to be set to Pending and sets it if required
/// </summary>
/// <param name="courseInstance">The <see cref="CourseInstance"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
private static void ChangeCourseStatusToPending(CourseInstance courseInstance, ProviderPortalEntities db)
{
// If there are no other LIVE opportunities for this course and the course is currently LIVE then set the course status to Pending
Course course = courseInstance.Course;
if (course.RecordStatusId == (Int32)Constants.RecordStatus.Live)
{
if (course.CourseInstances.Count(x => x.RecordStatusId == (Int32)Constants.RecordStatus.Live && x.CourseInstanceId != courseInstance.CourseInstanceId) == 0)
{
course.RecordStatusId = (Int32)Constants.RecordStatus.Pending;
course.AddedByApplicationId = (Int32)Constants.Application.Portal;
course.ModifiedByUserId = Permission.GetCurrentUserId();
course.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(course).State = EntityState.Modified;
}
}
}
/// <summary>
/// Checks whether the <see cref="Apprenticeship"/>'s status need to be set to Pending and sets it if required
/// </summary>
/// <param name="apprenticeshipLocation">The <see cref="ApprenticeshipLocation"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
private static void ChangeApprenticeshipStatusToPending(ApprenticeshipLocation apprenticeshipLocation,
ProviderPortalEntities db)
{
// If there are no other LIVE delivery locations for this apprenticeship and the apprenticeship is currently LIVE then set the apprenticeship status to Pending
Apprenticeship apprenticeship = apprenticeshipLocation.Apprenticeship;
if (apprenticeship.RecordStatusId == (Int32)Constants.RecordStatus.Live)
{
if (
apprenticeship.ApprenticeshipLocations.Count(
x =>
x.RecordStatusId == (Int32)Constants.RecordStatus.Live &&
x.ApprenticeshipLocationId != apprenticeshipLocation.ApprenticeshipLocationId) == 0)
{
apprenticeship.RecordStatusId = (Int32)Constants.RecordStatus.Pending;
apprenticeship.AddedByApplicationId = (Int32)Constants.Application.Portal;
apprenticeship.ModifiedByUserId = Permission.GetCurrentUserId();
apprenticeship.ModifiedDateTimeUtc = DateTime.UtcNow;
db.Entry(apprenticeship).State = EntityState.Modified;
}
}
}
/// <summary>
/// Deletes the <see cref="SearchPhrase"/> and also manages status of it's associated <see cref="Course"/>
/// </summary>
/// <param name="SearchPhrase">The <see cref="SearchPhrase"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Delete(this SearchPhrase SearchPhrase, ProviderPortalEntities db)
{
foreach (QualificationLevel qualLevel in SearchPhrase.QualificationLevels.ToList())
{
SearchPhrase.QualificationLevels.Remove(qualLevel);
}
foreach (StudyMode studyMode in SearchPhrase.StudyModes.ToList())
{
SearchPhrase.StudyModes.Remove(studyMode);
}
foreach (AttendanceType attendanceType in SearchPhrase.AttendanceTypes.ToList())
{
SearchPhrase.AttendanceTypes.Remove(attendanceType);
}
foreach (AttendancePattern attendancePattern in SearchPhrase.AttendancePatterns.ToList())
{
SearchPhrase.AttendancePatterns.Remove(attendancePattern);
}
db.Entry(SearchPhrase).State = EntityState.Deleted;
}
public static void Delete(this Address address, ProviderPortalEntities db)
{
db.Entry(address).State = System.Data.Entity.EntityState.Deleted;
}
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()));
}
/// <summary>
/// Deletes the <see cref="Venue"/> and also delete it's associated <see cref="Address"/> and <see cref="CourseInstance"/>
/// </summary>
/// <param name="venue"></param>
/// <param name="db"></param>
public static void DeleteCascade(this Venue venue, ProviderPortalEntities db)
{
venue.Address.Delete(db);
db.Entry(venue).State = EntityState.Deleted;
}
/// <summary>
/// Unarchives the <see cref="Venue"/>
/// </summary>
/// <param name="venue">The <see cref="Venue"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this Venue venue, ProviderPortalEntities db)
{
venue.RecordStatusId = (Int32)Constants.RecordStatus.Live;
db.Entry(venue).State = EntityState.Modified;
}
/// <summary>
/// Deletes the <see cref="Location"/> and also delete it's associated <see cref="Address"/> and <see cref="CourseInstance"/>
/// </summary>
/// <param name="location"></param>
/// <param name="db"></param>
public static void DeleteCascade(this Location location, ProviderPortalEntities db)
{
location.Address.Delete(db);
db.Entry(location).State = EntityState.Deleted;
}
/// <summary>
/// Unarchives the <see cref="Location"/>
/// </summary>
/// <param name="location">The <see cref="Location"/> object</param>
/// <param name="db">The <see cref="ProviderPortalEntities"/> object</param>
public static void Unarchive(this Location location, ProviderPortalEntities db)
{
location.RecordStatusId = (Int32)Constants.RecordStatus.Live;
db.Entry(location).State = EntityState.Modified;
}
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);
}
}
}