/// <summary>
/// reusable method
/// </summary>
/// <param name="fileAsZipInUTF8"></param>
/// <param name="OverwriteMethod"></param>
/// <param name="SubTypeID">
/// usually empty value.
/// we use the parameter for sub types
/// eg, sales order that derive from transaction
/// eg, invoice that derive from transaction
/// eg, visit that derive from activity
/// in that case we take the SubTypeID from the metadata endpoint (see GetSubTypesMetadata method)
/// The custom fields are TSA fields
/// </param>
/// <returns></returns>
/// <remarks>
/// 1. the post body is in UTF8
/// </remarks>
private BulkUploadResponse BulkUploadOfZip(byte[] fileAsZipInUTF8, eOverwriteMethod OverwriteMethod, string SubTypeID = null)
{
string RequestUri =
(SubTypeID == null || SubTypeID.Length == 0) ?
string.Format("bulk/{0}/csv_zip", ResourceName) :
string.Format("bulk/{0}/{1}/csv_zip", ResourceName, SubTypeID); //eg, for transaction or activity
Dictionary <string, string> dicQueryStringParameters = new Dictionary <string, string>();
dicQueryStringParameters.Add("overwrite", OverwriteMethod.ToString());
byte[] postBody = fileAsZipInUTF8;
string contentType = "application/zip";
string accept = "application/json";
PepperiHttpClient PepperiHttpClient = new PepperiHttpClient(this.Authentication, this.Logger);
PepperiHttpClientResponse PepperiHttpClientResponse = PepperiHttpClient.PostByteArraycontent(
ApiBaseUri,
RequestUri,
dicQueryStringParameters,
postBody,
contentType,
accept
);
PepperiHttpClient.HandleError(PepperiHttpClientResponse);
BulkUploadResponse result = PepperiJsonSerializer.DeserializeOne <BulkUploadResponse>(PepperiHttpClientResponse.Body);
return(result);
}
private BulkUploadResponse BulkUpload_OfJson(IEnumerable <TModel> data, eOverwriteMethod OverwriteMethod, IEnumerable <string> fieldsToUpload)
{
FlatModel FlatModel = PepperiFlatSerializer.MapDataToFlatModel(data, fieldsToUpload, "''");
string RequestUri = string.Format("bulk/{0}/json", ResourceName);
Dictionary <string, string> dicQueryStringParameters = new Dictionary <string, string>();
dicQueryStringParameters.Add("overwrite", OverwriteMethod.ToString());
string postBody = PepperiJsonSerializer.Serialize(FlatModel); //null values are not serialzied.
string contentType = "application/json";
string accept = "application/json";
PepperiHttpClient PepperiHttpClient = new PepperiHttpClient(this.Authentication, this.Logger);
PepperiHttpClientResponse PepperiHttpClientResponse = PepperiHttpClient.PostStringContent(
ApiBaseUri,
RequestUri,
dicQueryStringParameters,
postBody,
contentType,
accept
);
PepperiHttpClient.HandleError(PepperiHttpClientResponse);
BulkUploadResponse result = PepperiJsonSerializer.DeserializeOne <BulkUploadResponse>(PepperiHttpClientResponse.Body);
return(result);
}
private BulkUploadResponse BulkUploadOfZip(IEnumerable <TModel> data, eOverwriteMethod OverwriteMethod, IEnumerable <string> fieldsToUpload, string FilePathToStoreZipFile, string SubTypeID = null)
{
FlatModel FlatModel = PepperiFlatSerializer.MapDataToFlatModel(data, fieldsToUpload, "''");
string CsvFileAsString = PepperiFlatSerializer.FlatModelToCsv(FlatModel);
byte[] CsvFileAsZipInUTF8 = PepperiFlatSerializer.UTF8StringToZip(CsvFileAsString, FilePathToStoreZipFile);
BulkUploadResponse result = BulkUploadOfZip(CsvFileAsZipInUTF8, OverwriteMethod, SubTypeID);
return(result);
}
public BulkUploadResponse BulkUpload(string csvFilePath, eOverwriteMethod OverwriteMethod, Encoding fileEncoding, string SubTypeID = "", string FilePathToStoreZipFile = null)
{
byte[] fileAsBinary = File.ReadAllBytes(csvFilePath);
bool isToAddBOM = true;
// UTF8 byte order mark is: 0xEF,0xBB,0xBF
if (fileAsBinary[0] == 0xEF && fileAsBinary[1] == 0xBB && fileAsBinary[2] == 0xBF)
{
isToAddBOM = false;
}
byte[] fileAsUtf8 = Encoding.Convert(fileEncoding, Encoding.UTF8, fileAsBinary);
string fileAsUtf8String = System.Text.Encoding.UTF8.GetString(fileAsUtf8);
byte[] fileAsZipInUTF8 = PepperiFlatSerializer.UTF8StringToZip(fileAsUtf8String, FilePathToStoreZipFile, isToAddBOM);
BulkUploadResponse result = BulkUploadOfZip(fileAsZipInUTF8, OverwriteMethod, SubTypeID);
return(result);
}
/// <summary>
/// Upsert of collection As json or csv zip
/// </summary>
/// <param name="data"></param>
/// <param name="OverrideMethod"></param>
/// <param name="BulkUploadMethod"></param>
/// <param name="fieldsToUpload"></param>
/// <param name="FilePathToStoreZipFile">Optional. We can store the generated zip file for debugging purpose.</param>
/// <returns></returns>
public BulkUploadResponse BulkUpload(IEnumerable <TModel> data, eOverwriteMethod OverrideMethod, eBulkUploadMethod BulkUploadMethod, IEnumerable <string> fieldsToUpload, bool SaveZipFileInLocalDirectory = false, string SubTypeID = "")
{
//validate input
if (fieldsToUpload == null || fieldsToUpload.Count() == 0)
{
throw new PepperiException("No header fields are specified.");
}
BulkUploadResponse BulkUploadResponse = null;
switch (BulkUploadMethod)
{
case eBulkUploadMethod.Json:
{
BulkUploadResponse = BulkUpload_OfJson(data, OverrideMethod, fieldsToUpload);
break;
}
case eBulkUploadMethod.Zip:
{
string FilePathToStoreZipFile = null;
if (SaveZipFileInLocalDirectory)
{
string AssemblyLocation = Assembly.GetExecutingAssembly().Location;
string AssemblyPath = Path.GetDirectoryName(AssemblyLocation);
string zipDirectory = AssemblyPath;
string zipFileName = "BulkUpload_" + this.ResourceName + ".zip";
FilePathToStoreZipFile = Path.Combine(zipDirectory, zipFileName);
}
BulkUploadResponse = BulkUploadOfZip(data, OverrideMethod, fieldsToUpload, FilePathToStoreZipFile, SubTypeID);
break;
}
default:
{
throw new PepperiException("Invalid argument: the upload method is not supported.");
}
}
return(BulkUploadResponse);
}
public async Task <BulkUploadResponse> CompareAndProcessRegistrations(IList <TqRegistrationProfile> registrations)
{
var result = new BulkUploadResponse();
var ulns = new HashSet <int>();
//var seedValue = 0;
//var entitiesToLoad = 10000;
//var ulns = new HashSet<int>();
//var registrations = new List<TqRegistrationProfile>();
//var dateTimeNow = DateTime.Now;
//Random random = new Random();
//for (int i = 1; i <= entitiesToLoad; i++)
//{
// if (i <= 10000)
// ulns.Add(seedValue + i);
// var reg = new TqRegistrationProfile
// {
// //Id = i,
// UniqueLearnerNumber = seedValue + i,
// Firstname = "Firstname " + (seedValue + "XY" + i),
// Lastname = "Lastname " + (seedValue + i),
// DateofBirth = DateTime.Parse("17/01/1983")
// };
// reg.TqRegistrationPathways = new List<TqRegistrationPathway>
// {
// new TqRegistrationPathway
// {
// TqProviderId = 1,
// StartDate = DateTime.Parse("01/06/2020"),
// Status = 1,
// TqProvider = new TqProvider { TqAwardingOrganisationId = 1, TlProviderId = 1, TqAwardingOrganisation = new TqAwardingOrganisation { TlAwardingOrganisatonId = 1, TlPathwayId = 1 } },
// //TqProvider = new TqProvider { TqAwardingOrganisationId = 1, TlProviderId = 2, TqAwardingOrganisation = new TqAwardingOrganisation { TlAwardingOrganisatonId = 3, TlPathwayId = 5 } },
// TqRegistrationSpecialisms = new List<TqRegistrationSpecialism>
// {
// new TqRegistrationSpecialism
// {
// TlSpecialismId = 17,
// StartDate = DateTime.Parse("21/07/2020"),
// Status = 1
// }
// }
// }
// };
// registrations.Add(reg);
//}
//var watch = System.Diagnostics.Stopwatch.StartNew();
//watch.Start();
registrations.ToList().ForEach(r => ulns.Add(r.UniqueLearnerNumber));
var existingRegistrationsFromDb = await ctx.TqRegistrationProfile.Where(x => ulns.Contains(x.UniqueLearnerNumber))
.Include(x => x.TqRegistrationPathways)
.ThenInclude(x => x.TqRegistrationSpecialisms)
.Include(x => x.TqRegistrationPathways)
.ThenInclude(x => x.TqProvider)
.ThenInclude(x => x.TqAwardingOrganisation)
//.ThenInclude(x => x.TlAwardingOrganisaton)
.ToListAsync();
//watch.Stop();
//var sec1 = watch.ElapsedMilliseconds;
//watch.Restart();
var modifiedRegistrations = new List <TqRegistrationProfile>();
var modifiedRegistrationsToIgnore = new List <TqRegistrationProfile>();
var modifiedPathwayRecords = new List <TqRegistrationPathway>();
var modifiedSpecialismRecords = new List <TqRegistrationSpecialism>();
var ulnComparer = new TqRegistrationUlnEqualityComparer();
var comparer = new TqRegistrationRecordEqualityComparer();
var newRegistrations = registrations.Except(existingRegistrationsFromDb, ulnComparer).ToList();
var matchedRegistrations = registrations.Intersect(existingRegistrationsFromDb, ulnComparer).ToList();
var sameOrDuplicateRegistrations = matchedRegistrations.Intersect(existingRegistrationsFromDb, comparer).ToList();
if (matchedRegistrations.Count != sameOrDuplicateRegistrations.Count)
{
modifiedRegistrations = matchedRegistrations.Except(sameOrDuplicateRegistrations, comparer).ToList();
var tqRegistrationProfileComparer = new TqRegistrationProfileEqualityComparer();
var tqRegistrationPathwayComparer = new TqRegistrationPathwayEqualityComparer();
var tqRegistrationSpecialismComparer = new TqRegistrationSpecialismEqualityComparer();
modifiedRegistrations.ForEach(modifiedRegistration =>
{
var existingRegistration = existingRegistrationsFromDb.FirstOrDefault(existingRegistration => existingRegistration.UniqueLearnerNumber == modifiedRegistration.UniqueLearnerNumber);
if (existingRegistration != null)
{
var hasBothPathwayAndSpecialismsRecordsChanged = false;
var hasOnlySpecialismsRecordChanged = false;
var hasTqRegistrationProfileRecordChanged = !tqRegistrationProfileComparer.Equals(modifiedRegistration, existingRegistration);
modifiedRegistration.Id = existingRegistration.Id;
modifiedRegistration.TqRegistrationPathways.ToList().ForEach(p => p.TqRegistrationProfileId = existingRegistration.Id);
var pathwayRegistrationsInDb = existingRegistration.TqRegistrationPathways.Where(s => s.Status == 1).ToList();
var pathwaysToAdd = modifiedRegistration.TqRegistrationPathways.Where(s => !pathwayRegistrationsInDb.Any(r => r.TqProviderId == s.TqProviderId)).ToList();
var pathwaysToUpdate = pathwaysToAdd.Count > 0 ? pathwayRegistrationsInDb : pathwayRegistrationsInDb.Where(s => modifiedRegistration.TqRegistrationPathways.Any(r => r.TqProviderId == s.TqProviderId)).ToList();
if (pathwaysToUpdate.Count > 0)
{
var hasProviderChanged = !pathwaysToUpdate.Any(x => modifiedRegistration.TqRegistrationPathways.Any(r => r.TqProvider.TlProviderId == x.TqProvider.TlProviderId));
var hasPathwayChanged = !pathwaysToUpdate.Any(x => modifiedRegistration.TqRegistrationPathways.Any(r => r.TqProvider.TqAwardingOrganisation.TlPathwayId == x.TqProvider.TqAwardingOrganisation.TlPathwayId));
if (hasPathwayChanged)
{
//TODO: Need to check if there is an active registration for another AO, if so show error message and reject the file
var hasAoChanged = !pathwaysToUpdate.Any(x => modifiedRegistration.TqRegistrationPathways.Any(r => r.TqProvider.TqAwardingOrganisation.TlAwardingOrganisatonId == x.TqProvider.TqAwardingOrganisation.TlAwardingOrganisatonId));
if (hasAoChanged)
{
result.BulkUploadErrors.Add(new BulkUploadError
{
FieldName = "Uln",
FieldValue = modifiedRegistration.UniqueLearnerNumber.ToString(),
ErrorMessage = "There is active registration with another Awarding Organisation"
});
}
}
if (!result.HasAnyErrors)
{
// change existing TqRegistrationPathway record status and related TqRegistrationSpecialism records status to "Changed"
if (pathwaysToAdd.Count > 0)
{
pathwaysToUpdate.ForEach(pathwayToUpdate =>
{
pathwayToUpdate.Status = 2; // update status to changed
pathwayToUpdate.EndDate = DateTime.UtcNow;
pathwayToUpdate.ModifiedBy = "LoggedIn User";
pathwayToUpdate.ModifiedOn = DateTime.UtcNow;
pathwayToUpdate.TqRegistrationSpecialisms.Where(s => s.Status == 1).ToList().ForEach(specialismToUpdate =>
{
specialismToUpdate.Status = 2; // update status to changed
specialismToUpdate.EndDate = DateTime.UtcNow;
specialismToUpdate.ModifiedBy = "LoggedIn User";
specialismToUpdate.ModifiedOn = DateTime.UtcNow;
});
});
hasBothPathwayAndSpecialismsRecordsChanged = true;
}
else
{
modifiedRegistration.TqRegistrationPathways.ToList().ForEach(importPathwayRecord =>
{
var existingPathwayRecord = pathwaysToUpdate.FirstOrDefault(p => p.TqProviderId == importPathwayRecord.TqProviderId);
if (existingPathwayRecord != null && existingPathwayRecord.TqRegistrationSpecialisms.Any())
{
var existingSpecialisms = existingPathwayRecord.TqRegistrationSpecialisms.Where(s => s.Status == 1).ToList();
//1,1 - 2,1
//3,1 2,1 - 1,1 2,1
//3,1 4,1 - 1,1 2,1
//3,1 4,1 - 1,1 2,1 4,1
// below commented line using EqualityComprarer
//var specialismsToAdd = mr.TqSpecialismRegistrations.Except(specialismsInDb, specialismComparer).ToList();
//var specialismsToUpdate = specialismsInDb.Except(mr.TqSpecialismRegistrations, specialismComparer).ToList();
var specialismsToAdd = importPathwayRecord.TqRegistrationSpecialisms.Where(s => !existingSpecialisms.Any(r => r.TlSpecialismId == s.TlSpecialismId)).ToList();
var specialismsToUpdate = existingSpecialisms.Where(s => !importPathwayRecord.TqRegistrationSpecialisms.Any(r => r.TlSpecialismId == s.TlSpecialismId)).ToList();
specialismsToUpdate.ForEach(s =>
{
s.Status = 2; // change the status to inactive or withdrawn
s.EndDate = DateTime.UtcNow;
s.ModifiedBy = "LoggedIn User";
s.ModifiedOn = DateTime.UtcNow;
});
specialismsToAdd.ForEach(s =>
{
s.TqRegistrationPathwayId = existingPathwayRecord.Id;
s.Status = 1;
s.StartDate = DateTime.UtcNow;
s.CreatedBy = "LoggedIn User";
});
if (specialismsToAdd.Count > 0 || specialismsToUpdate.Count > 0)
{
hasOnlySpecialismsRecordChanged = true;
existingPathwayRecord.TqRegistrationSpecialisms.Clear();
existingPathwayRecord.TqRegistrationSpecialisms = specialismsToAdd.Concat(specialismsToUpdate).ToList();
}
}
else if (existingPathwayRecord != null && importPathwayRecord.TqRegistrationSpecialisms.Any())
{
importPathwayRecord.TqRegistrationSpecialisms.ToList().ForEach(s =>
{
existingPathwayRecord.TqRegistrationSpecialisms.Add(new TqRegistrationSpecialism
{
TqRegistrationPathwayId = existingPathwayRecord.Id,
TlSpecialismId = s.TlSpecialismId,
StartDate = s.StartDate,
Status = s.Status,
CreatedBy = s.CreatedBy
});
});
hasOnlySpecialismsRecordChanged = true;
}
});
}
}
}
if (!result.HasAnyErrors)
{
if (hasTqRegistrationProfileRecordChanged && hasBothPathwayAndSpecialismsRecordsChanged)
{
modifiedRegistration.TqRegistrationPathways = pathwaysToAdd.Concat(pathwaysToUpdate).ToList();
}
else if (hasTqRegistrationProfileRecordChanged && !hasBothPathwayAndSpecialismsRecordsChanged && hasOnlySpecialismsRecordChanged)
{
pathwaysToUpdate.ForEach(p => { modifiedSpecialismRecords.AddRange(p.TqRegistrationSpecialisms); });
modifiedRegistration.TqRegistrationPathways.Clear();
}
else if (hasTqRegistrationProfileRecordChanged && !hasBothPathwayAndSpecialismsRecordsChanged && !hasOnlySpecialismsRecordChanged)
{
modifiedRegistration.TqRegistrationPathways.Clear();
}
else if (hasBothPathwayAndSpecialismsRecordsChanged && !hasOnlySpecialismsRecordChanged)
{
modifiedPathwayRecords.AddRange(pathwaysToAdd.Concat(pathwaysToUpdate));
modifiedRegistrationsToIgnore.Add(modifiedRegistration);
}
else if (!hasBothPathwayAndSpecialismsRecordsChanged && hasOnlySpecialismsRecordChanged)
{
pathwaysToUpdate.ForEach(p => { modifiedSpecialismRecords.AddRange(p.TqRegistrationSpecialisms); });
modifiedRegistrationsToIgnore.Add(modifiedRegistration);
}
}
}
});
}
if (!result.HasAnyErrors && (newRegistrations.Count > 0 || modifiedRegistrations.Count > 0))
{
var registrationsToSendToDB = newRegistrations.Concat(modifiedRegistrations.Except(modifiedRegistrationsToIgnore, ulnComparer)).ToList();
result.IsSuccess = await _registrationRepository.BulkInsertOrUpdateTqRegistrations(registrationsToSendToDB, modifiedPathwayRecords, modifiedSpecialismRecords);
result.BulkUploadStats = new BulkUploadStats
{
NewRecordsCount = newRegistrations.Count,
UpdatedRecordsCount = modifiedRegistrations.Count,
DuplicateRecordsCount = sameOrDuplicateRegistrations.Count
};
}
//watch.Stop();
//var sec = watch.ElapsedMilliseconds;
return(result);
}
