/// <summary>
/// Map data
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="serviceArea"></param>
/// <param name="systemId"></param>
private static void CopyToInstance(PerformContext performContext, DbAppContext dbContext, ServiceArea oldObject, ref Models.ServiceArea serviceArea, string systemId)
{
bool isNew = false;
if (serviceArea == null)
{
isNew = true;
serviceArea = new Models.ServiceArea();
}
if (oldObject.Service_Area_Id <= 0)
{
return;
}
serviceArea.Id = oldObject.Service_Area_Id;
serviceArea.MinistryServiceAreaID = oldObject.Service_Area_Id;
serviceArea.DistrictId = oldObject.District_Area_Id;
serviceArea.Name = oldObject.Service_Area_Desc.Trim();
serviceArea.AreaNumber = oldObject.Service_Area_Cd;
District district = dbContext.Districts.FirstOrDefault(x => x.MinistryDistrictID == oldObject.District_Area_Id);
if (district == null)
{
// this means that the District is not in the database
// (this happens when the production data does not include district Other than "Lower Mainland" or all the districts)
return;
}
serviceArea.District = district;
try
{
serviceArea.StartDate = DateTime.ParseExact(oldObject.FiscalStart.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
}
catch
{
// do nothing
}
if (isNew)
{
serviceArea.CreateUserid = systemId;
serviceArea.CreateTimestamp = DateTime.UtcNow;
dbContext.ServiceAreas.Add(serviceArea);
}
else
{
serviceArea.LastUpdateUserid = systemId;
serviceArea.LastUpdateTimestamp = DateTime.UtcNow;
dbContext.ServiceAreas.Update(serviceArea);
}
try
{
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR With add or update Service Area ***");
performContext.WriteLine(e.ToString());
}
}
/// <summary>
/// Import Dis(trict) Equip(ment) Type
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="fileLocation"></param>
/// <param name="systemId"></param>
public static void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId)
{
// check the start point. If startPoint == sigId then it is already completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, OldTableProgress, BCBidImport.SigId);
if (startPoint == BCBidImport.SigId) // this means the import job it has done today is complete for all the records in the xml file.
{
performContext.WriteLine("*** Importing " + XmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create Processer progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(EquipType[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
EquipType[] legacyItems = (EquipType[])ser.Deserialize(memoryStream);
int ii = startPoint;
if (startPoint > 0) // skip the portion already processed
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
foreach (EquipType item in legacyItems.WithProgress(progress))
{
string serviceAreaName;
// see if we have this one already.
ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == item.Equip_Type_Id.ToString());
float equipRentalRateNo;
try
{
equipRentalRateNo = (float)Decimal.Parse(item.Equip_Rental_Rate_No, System.Globalization.NumberStyles.Any);
}
catch
{
equipRentalRateNo = (float)0.1;
}
string description;
try
{
description = item.Equip_Type_Desc.Length >= 225 ? item.Equip_Type_Desc.Substring(0, 225) : item.Equip_Type_Desc;
}
catch
{
description = "";
}
// new entry
if (importMap == null)
{
if (item.Equip_Type_Id > 0)
{
DistrictEquipmentType instance = null;
serviceAreaName = CopyToInstance(dbContext, item, ref instance, systemId, equipRentalRateNo);
if (serviceAreaName != "ERROR")
{
AddingDistrictEquipmentTypeInstance(dbContext, item, instance, equipRentalRateNo, description, serviceAreaName, true);
}
}
}
else // update
{
DistrictEquipmentType instance = dbContext.DistrictEquipmentTypes.FirstOrDefault(x => x.Id == importMap.NewKey);
if (instance == null) // record was deleted
{
serviceAreaName = CopyToInstance(dbContext, item, ref instance, systemId, equipRentalRateNo);
if (serviceAreaName != "ERROR")
{
AddingDistrictEquipmentTypeInstance(dbContext, item, instance, equipRentalRateNo, description, serviceAreaName, false);
// update the import map
importMap.NewKey = instance.Id;
dbContext.ImportMaps.Update(importMap);
}
}
else // ordinary update.
{
serviceAreaName = CopyToInstance(dbContext, item, ref instance, systemId, equipRentalRateNo);
if (serviceAreaName != "ERROR")
{
AddingDistrictEquipmentTypeInstance(dbContext, item, instance, equipRentalRateNo, description, serviceAreaName, false);
// touch the import map
importMap.LastUpdateTimestamp = DateTime.UtcNow;
dbContext.ImportMaps.Update(importMap);
}
}
}
// save change to database periodically to avoid frequent writing to the database
if (++ii % 250 == 0)
{
try
{
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, ii.ToString(), BCBidImport.SigId);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
}
try
{
performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***");
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BCBidImport.SigId.ToString(), BCBidImport.SigId);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
}
}
/// <summary>
/// Import Users
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="fileLocation"></param>
/// <param name="systemId"></param>
public static void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId)
{
// check the start point. If startPoint == sigId then it is already completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, OldTableProgress, BCBidImport.SigId);
if (startPoint == BCBidImport.SigId) // this means the import job it has done today is complete for all the records in the xml file.
{
performContext.WriteLine("*** Importing " + XmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create Processer progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(UserHets[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
UserHets[] legacyItems = (UserHets[])ser.Deserialize(memoryStream);
int ii = startPoint;
// skip the portion already processed
if (startPoint > 0)
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
foreach (UserHets item in legacyItems.WithProgress(progress))
{
// see if we have this one already
ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == item.Popt_Id.ToString());
User instance = dbContext.Users.FirstOrDefault(x => item.User_Cd.ToUpper().IndexOf(x.SmUserId.ToUpper(), StringComparison.Ordinal) >= 0);
if (instance == null)
{
CopyToInstance(dbContext, item, ref instance, systemId);
// new entry
if (importMap == null && instance != null)
{
ImportUtility.AddImportMap(dbContext, OldTable, item.Popt_Id.ToString(), NewTable, instance.Id);
}
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, (++ii).ToString(), BCBidImport.SigId);
dbContext.SaveChangesForImport();
}
}
try
{
performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***");
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BCBidImport.SigId.ToString(), BCBidImport.SigId);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="timeRecord"></param>
/// <param name="systemId"></param>
/// <param name="maxTimesheetIndex"></param>
private static void CopyToTimeRecorded(DbAppContext dbContext, EquipUsage oldObject, ref TimeRecord timeRecord,
string systemId, ref int maxTimesheetIndex)
{
try
{
if (oldObject.Equip_Id <= 0)
{
return;
}
if (oldObject.Project_Id <= 0)
{
return;
}
// ***********************************************
// we only need records from the current fiscal
// so ignore all others
// ***********************************************
DateTime fiscalStart;
DateTime fiscalEnd;
if (DateTime.UtcNow.Month == 1 || DateTime.UtcNow.Month == 2 || DateTime.UtcNow.Month == 3)
{
fiscalEnd = new DateTime(DateTime.UtcNow.Year, 3, 31);
}
else
{
fiscalEnd = new DateTime(DateTime.UtcNow.AddYears(1).Year, 3, 31);
}
if (DateTime.UtcNow.Month == 1 || DateTime.UtcNow.Month == 2 || DateTime.UtcNow.Month == 3)
{
fiscalStart = new DateTime(DateTime.UtcNow.AddYears(-1).Year, 4, 1);
}
else
{
fiscalStart = new DateTime(DateTime.UtcNow.Year, 4, 1);
}
string tempRecordDate = oldObject.Worked_Dt;
if (string.IsNullOrEmpty(tempRecordDate))
{
return; // ignore if we don't have a created date
}
if (!string.IsNullOrEmpty(tempRecordDate))
{
DateTime?recordDate = ImportUtility.CleanDateTime(tempRecordDate);
if (recordDate == null ||
recordDate < fiscalStart ||
recordDate > fiscalEnd)
{
return; // ignore this record - it is outside of the current fiscal year
}
}
// ************************************************
// get the imported equipment record map
// ************************************************
string tempId = oldObject.Equip_Id.ToString();
ImportMap mapEquip = dbContext.ImportMaps.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (mapEquip == null)
{
throw new DataException(string.Format("Cannot locate Equipment record (Timesheet Equip Id: {0}", tempId));
}
// ***********************************************
// find the equipment record
// ***********************************************
Equipment equipment = dbContext.Equipments.AsNoTracking().FirstOrDefault(x => x.Id == mapEquip.NewKey);
if (equipment == null)
{
throw new ArgumentException(string.Format("Cannot locate Equipment record (Timesheet Equip Id: {0}", tempId));
}
// ************************************************
// get the imported project record map
// ************************************************
string tempProjectId = oldObject.Project_Id.ToString();
ImportMap mapProject = dbContext.ImportMaps.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempProjectId &&
x.OldTable == ImportProject.OldTable &&
x.NewTable == ImportProject.NewTable);
// ***********************************************
// find the project record
// (or create a project (inactive))
// ***********************************************
Models.Project project;
if (mapProject != null)
{
project = dbContext.Projects.AsNoTracking().FirstOrDefault(x => x.Id == mapProject.NewKey);
if (project == null)
{
throw new ArgumentException(string.Format("Cannot locate Project record (Timesheet Equip Id: {0}", tempId));
}
}
else
{
// create new project
project = new Models.Project
{
Information = "Created to support Time Record import from BCBid",
Status = "Complete",
Name = "Legacy BCBid Project",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
dbContext.Projects.Add(project);
// save now so we can access it for other time records
dbContext.SaveChanges();
// add mapping record
ImportUtility.AddImportMapForProgress(dbContext, ImportProject.OldTable, tempProjectId, project.Id, ImportProject.NewTable);
dbContext.SaveChanges();
}
// ***********************************************
// find or create the rental agreement
// ***********************************************
DateTime?enteredDate = ImportUtility.CleanDateTime(oldObject.Entered_Dt); // use for the agreement
RentalAgreement agreement = dbContext.RentalAgreements.AsNoTracking()
.FirstOrDefault(x => x.EquipmentId == equipment.Id &&
x.ProjectId == project.Id);
if (agreement == null)
{
// create a new agreement record
agreement = new RentalAgreement
{
EquipmentId = equipment.Id,
ProjectId = project.Id,
Note = "Created to support Time Record import from BCBid",
Number = "Legacy BCBid Agreement",
DatedOn = enteredDate,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
if (project.RentalAgreements == null)
{
project.RentalAgreements = new List <RentalAgreement>();
}
project.RentalAgreements.Add(agreement);
// save now so we can access it for other time records
dbContext.SaveChangesForImport();
}
// ***********************************************
// create time record
// ***********************************************
timeRecord = new TimeRecord {
Id = ++maxTimesheetIndex
};
// ***********************************************
// set time record attributes
// ***********************************************
DateTime?workedDate = ImportUtility.CleanDateTime(oldObject.Worked_Dt);
if (workedDate != null)
{
timeRecord.WorkedDate = (DateTime)workedDate;
}
else
{
throw new DataException(string.Format("Worked Date cannot be null (TimesheetIndex: {0}", maxTimesheetIndex));
}
// get hours worked
float?tempHoursWorked = ImportUtility.GetFloatValue(oldObject.Hours);
if (tempHoursWorked != null)
{
timeRecord.Hours = tempHoursWorked;
}
else
{
throw new DataException(string.Format("Hours cannot be null (TimesheetIndex: {0}", maxTimesheetIndex));
}
if (enteredDate != null)
{
timeRecord.EnteredDate = (DateTime)enteredDate;
}
else
{
throw new DataException(string.Format("Entered Date cannot be null (TimesheetIndex: {0}", maxTimesheetIndex));
}
// ***********************************************
// create time record
// ***********************************************
timeRecord.AppCreateUserid = systemId;
timeRecord.AppCreateTimestamp = DateTime.UtcNow;
timeRecord.AppLastUpdateUserid = systemId;
timeRecord.AppLastUpdateTimestamp = DateTime.UtcNow;
if (agreement.TimeRecords == null)
{
agreement.TimeRecords = new List <TimeRecord>();
}
agreement.TimeRecords.Add(timeRecord);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Worked Date: " + oldObject.Worked_Dt);
Debug.WriteLine("***Error*** - Master TimeRecord Index: " + maxTimesheetIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Import Equip(ment) Usage
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="fileLocation"></param>
/// <param name="systemId"></param>
public static void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId)
{
// check the start point. If startPoint == sigId then it is already completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, OldTableProgress, BcBidImport.SigId, NewTable);
if (startPoint == BcBidImport.SigId) // this means the import job completed for all the records in this file
{
performContext.WriteLine("*** Importing " + XmlFileName + " is complete from the former process ***");
return;
}
int maxTimesheetIndex = 0;
if (dbContext.RentalAgreements.Any())
{
maxTimesheetIndex = dbContext.RentalAgreements.Max(x => x.Id);
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create Processer progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(EquipUsage[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
EquipUsage[] legacyItems = (EquipUsage[])ser.Deserialize(memoryStream);
int ii = startPoint;
// skip the portion already processed
if (startPoint > 0)
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
Debug.WriteLine("Importing TimeSheet Data. Total Records: " + legacyItems.Length);
foreach (EquipUsage item in legacyItems.WithProgress(progress))
{
// see if we have this one already
string oldProjectKey = item.Project_Id.ToString();
string oldEquipKey = item.Project_Id.ToString();
string oldCreatedDate = item.Created_Dt;
string oldKey = string.Format("{0}-{1}-{2}", oldProjectKey, oldEquipKey, oldCreatedDate);
ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == oldKey);
// new entry
if (importMap == null && item.Equip_Id > 0)
{
TimeRecord instance = null;
CopyToTimeRecorded(dbContext, item, ref instance, systemId, ref maxTimesheetIndex);
if (instance != null)
{
ImportUtility.AddImportMap(dbContext, OldTable, oldKey, NewTable, instance.Id);
}
}
// save change to database periodically to avoid frequent writing to the database
if (ii++ % 1000 == 0)
{
try
{
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, ii.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
}
try
{
performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***");
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
string temp = string.Format("Error saving data (RentalAgreementIndex: {0}): {1}", maxTimesheetIndex, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Import local areas
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="fileLocation"></param>
static public void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId)
{
// Check the start point. If startPoint == sigId then it is already completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, oldTable_Progress, BCBidImport.sigId);
if (startPoint == BCBidImport.sigId) // This means the import job it has done today is complete for all the records in the xml file.
{
performContext.WriteLine("*** Importing " + xmlFileName + " is complete from the former process ***");
return;
}
string rootAttr = "ArrayOf" + oldTable;
//Create Processer progress indicator
performContext.WriteLine("Processing " + oldTable);
var progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(HETSAPI.Import.Area[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.memoryStreamGenerator(xmlFileName, oldTable, fileLocation, rootAttr);
HETSAPI.Import.Area[] legacyItems = (HETSAPI.Import.Area[])ser.Deserialize(memoryStream);
int ii = startPoint;
if (startPoint > 0) // Skip the portion already processed
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
foreach (var item in legacyItems.WithProgress(progress))
{
LocalArea localArea = null;
// see if we have this one already.
ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == oldTable && x.OldKey == item.Area_Id.ToString());
if (dbContext.LocalAreas.Where(x => x.Name.ToUpper() == item.Area_Desc.Trim().ToUpper()).Count() > 0)
{
localArea = dbContext.LocalAreas.FirstOrDefault(x => x.Name.ToUpper() == item.Area_Desc.Trim().ToUpper());
}
if (importMap == null || dbContext.LocalAreas.Where(x => x.Name.ToUpper() == item.Area_Desc.Trim().ToUpper()).Count() == 0) // new entry
{
if (item.Area_Id > 0)
{
CopyToInstance(performContext, dbContext, item, ref localArea, systemId);
ImportUtility.AddImportMap(dbContext, oldTable, item.Area_Id.ToString(), newTable, localArea.Id);
}
}
else // update
{
localArea = dbContext.LocalAreas.FirstOrDefault(x => x.Id == importMap.NewKey);
if (localArea == null) // record was deleted
{
CopyToInstance(performContext, dbContext, item, ref localArea, systemId);
// update the import map.
importMap.NewKey = localArea.Id;
dbContext.ImportMaps.Update(importMap);
}
else // ordinary update.
{
CopyToInstance(performContext, dbContext, item, ref localArea, systemId);
// touch the import map.
importMap.LastUpdateTimestamp = DateTime.UtcNow;
dbContext.ImportMaps.Update(importMap);
}
}
if (++ii % 250 == 0) // Save change to database once a while to avoid frequent writing to the database.
{
try
{
ImportUtility.AddImportMap_For_Progress(dbContext, oldTable_Progress, ii.ToString(), BCBidImport.sigId);
int iResult = dbContext.SaveChangesForImport();
}
catch (Exception e)
{
string iStr = e.ToString();
}
}
}
try
{
performContext.WriteLine("*** Importing " + xmlFileName + " is Done ***");
ImportUtility.AddImportMap_For_Progress(dbContext, oldTable_Progress, BCBidImport.sigId.ToString(), BCBidImport.sigId);
int iResult = dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR With add or update Local Area ***");
performContext.WriteLine(e.ToString());
}
}