public int? UpdateProjects(List<iatiactivity> projects, string Iuser, bool notCleanOldData = true)
{
var aimsCurrencies = from c in dbContext.tblCurrencies
select new CurrencyLookupItem { Id = c.Id, IATICode = c.IATICode };
var aimsAidCategories = from c in dbContext.tblAidCategories
select new AidCategoryLookupItem { Id = c.Id, IATICode = c.IATICode };
var divisions = (from d in dbContext.tblDivisions
where d.GPSLatitude != null && d.GPSLongitude != null
select new GeoLocation
{
DivisionId = d.Id,
Name = d.DivisionName,
Latitude = (double)d.GPSLatitude,
Longitude = (double)d.GPSLongitude
}).ToList();
var districts = (from d in dbContext.tblDistricts
where d.GPSLatitude != null && d.GPSLongitude != null
select new GeoLocation
{
DistrictId = d.Id,
DivisionId = d.DivisionId,
Name = d.DistrictName,
Latitude = (double)d.GPSLatitude,
Longitude = (double)d.GPSLongitude
}).ToList();
var upazilas = (from d in dbContext.tblUpazilas
where d.GPSLatitude != null && d.GPSLongitude != null
select new GeoLocation
{
UpazilaId = d.Id,
DistrictId = d.DistrictId,
DivisionId = d.DivisionId,
Name = d.UpazilaName,
Latitude = (double)d.GPSLatitude,
Longitude = (double)d.GPSLongitude
}).ToList();
foreach (var mergedproject in projects)
{
try
{
bool isFinancialDataMismathed = false;
var defaultfinancetype = "100";
if (mergedproject.defaultfinancetype != null && !string.IsNullOrWhiteSpace(mergedproject.defaultfinancetype.code))
defaultfinancetype = mergedproject.defaultfinancetype.code.StartsWith("4") ? "400" : "100";
var p = dbContext.tblProjectInfoes?.FirstOrDefault(f => f.Id == mergedproject.ProjectId);
if (p == null)
{
p = new tblProjectInfo();
p.IDate = DateTime.Now;
p.IUser = Iuser;
p.FundSourceId = mergedproject.AimsFundSourceId;
p.IatiIdentifier = mergedproject.IatiIdentifier;
dbContext.tblProjectInfoes.Add(p);
}
else
{
// first check isFinancialDataMismathed
foreach (var MatchedProject in mergedproject.MatchedProjects)
{
isFinancialDataMismathed = CheckTransactionMismatch(p, MatchedProject, notCleanOldData);
if (isFinancialDataMismathed) break;
}
//if Financial Data are Mismathed then continue with next project
if (isFinancialDataMismathed) continue;
//if FinancialData are not Mismathed then delete existing transactions (this DP only)
foreach (var MatchedProject in mergedproject.MatchedProjects)
{
DeleteTransactions(p, MatchedProject);
}
//then we need another loop to update transactions !!! Do not combine these three identical loops.
foreach (var MatchedProject in mergedproject.MatchedProjects)
{
UpdateTransactions(Iuser, aimsCurrencies, aimsAidCategories, defaultfinancetype, p, MatchedProject);
}
}
isFinancialDataMismathed = CheckTransactionMismatch(p, mergedproject, notCleanOldData);
if (isFinancialDataMismathed) continue;
DeleteTransactions(p, mergedproject);
UpdateTransactions(Iuser, aimsCurrencies, aimsAidCategories, defaultfinancetype, p, mergedproject);
//if (checkMismatch)
//{
//we need to place this region at bottom due to checking isFinancialDataMismatched
#region Other Fields
p.Title = mergedproject.Title;
p.Objective = mergedproject.Description;
if (notCleanOldData == false)
{
foreach (var item in p.tblProjectAttachments.ToList())
{
dbContext.tblProjectAttachments.Remove(item);
}
foreach (var item in p.tblProjectSectoralAllocations.ToList())
{
dbContext.tblProjectSectoralAllocations.Remove(item);
}
foreach (var item in p.tblProjectGeographicAllocations.ToList())
{
dbContext.tblProjectGeographicAllocations.Remove(item);
}
foreach (var item in p.tblProjectExecutingAgencies.ToList())
{
dbContext.tblProjectExecutingAgencies.Remove(item);
}
}
#region Document
if (mergedproject.documentlink != null)
{
foreach (var document in mergedproject.documentlink)
{
var docTitle = document.title?.narrative.n(0).Value;
var attachment = p.tblProjectAttachments.FirstOrDefault(f => f.AttachmentTitle == docTitle);
if (attachment == null)
{
attachment = new tblProjectAttachment();
p.tblProjectAttachments.Add(attachment);
}
var docCatCode = document.category.n(0).code;
var docCategory = dbContext.tblDocumentCategories.FirstOrDefault(f => f.IATICode == docCatCode);
attachment.DocumentCategoryId = docCategory != null ? docCategory.Id : dbContext.tblDocumentCategories.OrderBy(o => o.Id).FirstOrDefault().Id;
attachment.AttachmentTitle = docTitle;
attachment.AttachmentFileURL = document.url;
attachment.IUser = Iuser;
attachment.IDate = DateTime.Now;
}
}
#endregion
#region Aid Type
var AssistanceType = dbContext.tblAssistanceTypes.FirstOrDefault(f => f.IATICode.Contains(mergedproject.AidTypeCode));
p.AssistanceTypeId = AssistanceType != null ? AssistanceType.Id : dbContext.tblAssistanceTypes.FirstOrDefault().Id;
var ProjectType = dbContext.tblProjectTypes.FirstOrDefault(f => f.IATICode.Contains(mergedproject.AidTypeCode));
p.ProjectTypeId = ProjectType != null ? ProjectType.Id : dbContext.tblProjectTypes.FirstOrDefault().Id;
#endregion
#region Dates
p.AgreementSignDate = mergedproject.ActualStartDate == default(DateTime) ? mergedproject.PlannedStartDate.ToSqlDateTime() : mergedproject.ActualStartDate;
p.PlannedProjectStartDate = mergedproject.PlannedStartDate.ToSqlDateTimeNull();
p.ActualProjectStartDate = mergedproject.ActualStartDate.ToSqlDateTimeNull();
p.PlannedProjectCompletionDate = mergedproject.PlannedEndDate.ToSqlDateTimeNull();
p.RevisedProjectCompletionDate = mergedproject.ActualEndDate.ToSqlDateTimeNull();
#endregion
#region Status
var statusCode = mergedproject.activitystatus?.code;
var ImplementationStatus = dbContext.tblImplementationStatus.FirstOrDefault(f => f.IATICode.Contains(statusCode));
p.ImplementationStatusId = ImplementationStatus == null ? default(int?) : ImplementationStatus.Id;
#endregion
#region Sector
if (mergedproject.sector != null)
{
var totalPercentage = mergedproject.sector.Sum(s => s.percentage); // to prevent percentage being greater than 100
foreach (var sector in mergedproject.sector)
{
if (sector.vocabulary == "1" || sector.vocabulary == "2")
{
var aimsSector = dbContext.tblSectors.FirstOrDefault(f => f.IATICode == sector.code);
var aimsSubsector = dbContext.tblSubSectors.FirstOrDefault(f => f.IATICode == sector.code);
var sectorId = aimsSector != null ?
aimsSector.Id : aimsSubsector != null ?
aimsSubsector.SectorId : 0;
if (sectorId > 0)
{
var SubsectorId = aimsSubsector != null ? aimsSubsector.Id : 0;
var psector = p.tblProjectSectoralAllocations.FirstOrDefault(f => f.SectorId == sectorId && f.SubSectorId == SubsectorId);
if (psector == null)
{
psector = new tblProjectSectoralAllocation();
p.tblProjectSectoralAllocations.Add(psector);
psector.IUser = Iuser;
psector.IDate = DateTime.Now;
}
psector.SectorId = sectorId;
psector.SubSectorId = SubsectorId;
psector.TotalCommitmentPercent = sector.percentage.ToPercent(totalPercentage);
}
}
}
}
#endregion
#region Location
if (mergedproject.location != null)
{
foreach (var location in mergedproject.location)
{
if (location.point == null) continue;
GeoLocation nearestGeoLocation = null;
var administrative = location.administrative?.FirstOrDefault();
if (administrative != null && administrative.vocabulary == "G1")
{
if (administrative.level == "1")
{
nearestGeoLocation = GetNearestGeoLocation(divisions, location);
}
else if (administrative.level == "2")
{
nearestGeoLocation = GetNearestGeoLocation(districts, location);
}
else if (administrative.level == "3")
{
nearestGeoLocation = GetNearestGeoLocation(upazilas, location);
}
}
else
{
nearestGeoLocation = GetNearestGeoLocation(districts, location);
}
var aimsProjectLocation = p.tblProjectGeographicAllocations.FirstOrDefault(f => f.DivisionId == nearestGeoLocation.DivisionId && f.DistrictId == nearestGeoLocation.DistrictId && f.UpazilaId == nearestGeoLocation.UpazilaId);
if (aimsProjectLocation == null)
{
aimsProjectLocation = new tblProjectGeographicAllocation();
aimsProjectLocation.DivisionId = nearestGeoLocation.DivisionId;
aimsProjectLocation.DistrictId = nearestGeoLocation.DistrictId;
aimsProjectLocation.UpazilaId = nearestGeoLocation.UpazilaId;
aimsProjectLocation.IUser = Iuser;
aimsProjectLocation.IDate = DateTime.Now;
p.tblProjectGeographicAllocations.Add(aimsProjectLocation);
}
aimsProjectLocation.TotalCommitmentPercentForDistrict = 100 / mergedproject.location.Count();
}
}
#endregion
#region Executing Agency
if (mergedproject.ImplementingOrgs.IsNotEmpty())
{
foreach (var ImplementingOrg in mergedproject.ImplementingOrgs.Where(w => w.AllID != mergedproject.AllID))
{
var executingAgency = p.tblProjectExecutingAgencies.FirstOrDefault(f => f.ExecutingAgencyOrganizationId == ImplementingOrg.ExecutingAgencyOrganizationId);
if (executingAgency == null)
{
executingAgency = new tblProjectExecutingAgency { ExecutingAgencyOrganizationId = ImplementingOrg.ExecutingAgencyOrganizationId, IUser = Iuser, IDate = DateTime.Now };
p.tblProjectExecutingAgencies.Add(executingAgency);
}
executingAgency.ExecutingAgencyTypeId = ImplementingOrg.ExecutingAgencyTypeId.Value;
executingAgency.ExecutingAgencyOrganizationTypeId = ImplementingOrg.ExecutingAgencyOrganizationTypeId;
}
}
#endregion
#endregion
//}
dbContext.SaveChanges();
mergedproject.ProjectId = p.Id;
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Trace.TraceInformation("Property: {0} Error: {1}",
validationError.PropertyName,
validationError.ErrorMessage);
}
}
}
catch (Exception ex)
{
Logger.WriteToDbAndFile(ex, LogType.Error, mergedproject.IATICode, mergedproject.IatiIdentifier);
}
}
try
{
aimsDBIatiDAL.MapActivities(projects);
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Trace.TraceInformation("Property: {0} Error: {1}",
validationError.PropertyName,
validationError.ErrorMessage);
}
}
}
catch (Exception ex)
{
Logger.WriteToDbAndFile(ex, LogType.Error);
}
return 1;
}
