public static void Obfuscate(PerformContext performContext, DbAppContext dbContext, string sourceLocation, string destinationLocation, string systemId)
{
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, "Obfuscate_" + OldTableProgress, BcBidImport.SigId, NewTable);
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("*** Obfuscating " + XmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.DumpTruck[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, sourceLocation, rootAttr);
ImportModels.DumpTruck[] legacyItems = (ImportModels.DumpTruck[])ser.Deserialize(memoryStream);
// no fields to mask for dump truck - straight copy
performContext.WriteLine("Writing " + XmlFileName + " to " + destinationLocation);
// write out the array
FileStream fs = ImportUtility.GetObfuscationDestination(XmlFileName, destinationLocation);
ser.Serialize(fs, legacyItems);
fs.Close();
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
}
}
/// <summary>
/// Import Service Areas
/// </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 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;
performContext.WriteLine("Processing Service Areas");
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.ServiceArea[]), new XmlRootAttribute(rootAttr));
ser.UnknownAttribute += ImportUtility.UnknownAttribute;
ser.UnknownElement += ImportUtility.UnknownElement;
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
ImportModels.ServiceArea[] legacyItems = (ImportModels.ServiceArea[])ser.Deserialize(memoryStream);
Debug.WriteLine("Importing ServiceArea Data. Total Records: " + legacyItems.Length);
foreach (ImportModels.ServiceArea item in legacyItems.WithProgress(progress))
{
// see if we have this one already
HetImportMap importMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == item.Service_Area_Id.ToString());
// new entry
if (importMap == null && item.Service_Area_Cd != "000")
{
HetServiceArea serviceArea = null;
CopyToInstance(dbContext, item, ref serviceArea, systemId);
ImportUtility.AddImportMap(dbContext, OldTable, item.Service_Area_Id.ToString(), NewTable, serviceArea.ServiceAreaId);
}
}
performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***");
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="localArea"></param>
/// <param name="systemId"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.Area oldObject,
ref HetLocalArea localArea, string systemId)
{
try
{
if (oldObject.Area_Id <= 0)
{
return;
}
string tempLocalArea = ImportUtility.CleanString(oldObject.Area_Desc);
tempLocalArea = ImportUtility.GetCapitalCase(tempLocalArea);
localArea = new HetLocalArea
{
LocalAreaId = oldObject.Area_Id,
LocalAreaNumber = oldObject.Area_Id,
Name = tempLocalArea
};
// map to the correct service area
HetServiceArea serviceArea = dbContext.HetServiceArea.AsNoTracking()
.FirstOrDefault(x => x.MinistryServiceAreaId == oldObject.Service_Area_Id);
if (serviceArea == null)
{
// not mapped correctly
return;
}
localArea.ServiceAreaId = serviceArea.ServiceAreaId;
localArea.AppCreateUserid = systemId;
localArea.AppCreateTimestamp = DateTime.UtcNow;
localArea.AppLastUpdateUserid = systemId;
localArea.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetLocalArea.Add(localArea);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public static void Obfuscate(PerformContext performContext, DbAppContext dbContext, string sourceLocation, string destinationLocation, string systemId)
{
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.ServiceArea[]), new XmlRootAttribute(rootAttr));
ser.UnknownAttribute += ImportUtility.UnknownAttribute;
ser.UnknownElement += ImportUtility.UnknownElement;
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, sourceLocation, rootAttr);
ImportModels.ServiceArea[] legacyItems = (ImportModels.ServiceArea[])ser.Deserialize(memoryStream);
foreach (ImportModels.ServiceArea item in legacyItems.WithProgress(progress))
{
item.Created_By = systemId;
}
performContext.WriteLine("Writing " + XmlFileName + " to " + destinationLocation);
// write out the array
FileStream fs = ImportUtility.GetObfuscationDestination(XmlFileName, destinationLocation);
ser.Serialize(fs, legacyItems);
fs.Close();
}
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, NewTable);
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;
}
// manage the id value for new user records
int maxUserIndex = 0;
if (dbContext.HetUser.Any())
{
maxUserIndex = dbContext.HetUser.Max(x => x.UserId);
maxUserIndex = maxUserIndex + 1;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.UserHets[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
ImportModels.UserHets[] legacyItems = (ImportModels.UserHets[])ser.Deserialize(memoryStream);
int ii = startPoint;
// skip the portion already processed
if (startPoint > 0)
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
// create an array of names using the created by and modified by values in the data
performContext.WriteLine("Extracting first and last names");
progress.SetValue(0);
Dictionary <string, string> firstNames = new Dictionary <string, string>();
Dictionary <string, string> lastNames = new Dictionary <string, string>();
foreach (ImportModels.UserHets item in legacyItems.WithProgress(progress))
{
string name = item.Created_By;
GetNameParts(name, ref firstNames, ref lastNames);
name = item.Modified_By;
GetNameParts(name, ref firstNames, ref lastNames);
}
// import the data
performContext.WriteLine("Importing User Data");
progress.SetValue(0);
foreach (ImportModels.UserHets item in legacyItems.WithProgress(progress))
{
string tempId = item.Popt_Id + "-" + item.Service_Area_Id;
HetImportMap importMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == tempId);
if (importMap == null)
{
string username = NormalizeUserCode(item.User_Cd).ToUpper();
string firstName = GetNamePart(username, firstNames);
string lastName = GetNamePart(username, lastNames);
HetUser user = null;
username = username.ToLower();
CopyToInstance(dbContext, item, ref user, systemId, username, firstName, lastName, ref maxUserIndex);
if (user != null)
{
ImportUtility.AddImportMap(dbContext, OldTable, tempId, NewTable, user.UserId);
dbContext.SaveChangesForImport();
}
}
}
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 (UserIndex: {0}): {1}", maxUserIndex, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
public static void Obfuscate(PerformContext performContext, DbAppContext dbContext, string sourceLocation, string destinationLocation, string systemId)
{
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, "Obfuscate_" + OldTableProgress, BcBidImport.SigId, NewTable);
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("*** Obfuscating " + XmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.Equip[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, sourceLocation, rootAttr);
ImportModels.Equip[] legacyItems = (ImportModels.Equip[])ser.Deserialize(memoryStream);
performContext.WriteLine("Obfuscating Equip data");
progress.SetValue(0);
List <ImportMapRecord> importMapRecords = new List <ImportMapRecord>();
foreach (ImportModels.Equip item in legacyItems.WithProgress(progress))
{
item.Created_By = systemId;
if (item.Modified_By != null)
{
item.Modified_By = systemId;
}
Random random = new Random();
string newSerialNum = random.Next(10000).ToString();
ImportMapRecord importMapRecordOrganization = new ImportMapRecord
{
TableName = NewTable,
MappedColumn = "Serial_Num",
OriginalValue = item.Serial_Num,
NewValue = newSerialNum
};
importMapRecords.Add(importMapRecordOrganization);
item.Serial_Num = newSerialNum;
item.Addr1 = ImportUtility.ScrambleString(item.Addr1);
item.Addr2 = ImportUtility.ScrambleString(item.Addr2);
item.Addr3 = ImportUtility.ScrambleString(item.Addr3);
item.Addr4 = ImportUtility.ScrambleString(item.Addr4);
item.Postal = ImportUtility.ScrambleString(item.Postal);
item.Licence = ImportUtility.ScrambleString(ImportUtility.CleanString(item.Licence));
item.Operator = ImportUtility.ScrambleString(item.Operator);
}
performContext.WriteLine("Writing " + XmlFileName + " to " + destinationLocation);
// write out the array
FileStream fs = ImportUtility.GetObfuscationDestination(XmlFileName, destinationLocation);
ser.Serialize(fs, legacyItems);
fs.Close();
// write out the spreadsheet of import records
ImportUtility.WriteImportRecordsToExcel(destinationLocation, importMapRecords, OldTable);
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="rotationDoc"></param>
/// <param name="systemId"></param>
/// <param name="maxIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.RotationDoc oldObject,
ref BcbidRotationDoc rotationDoc, string systemId, ref int maxIndex)
{
try
{
if (rotationDoc != null)
{
return;
}
rotationDoc = new BcbidRotationDoc {
NoteId = oldObject.Note_Id
};
++maxIndex;
// ***********************************************
// we only need records from the current fiscal
// so ignore all others
// ***********************************************
DateTime fiscalStart;
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);
}
// ***********************************************
// set rotation data
// ***********************************************
string noteType = oldObject.Note_Type;
if (string.IsNullOrEmpty(noteType))
{
return;
}
rotationDoc.NoteType = noteType.Trim();
// reason
string reason = oldObject.Reason;
if (!string.IsNullOrEmpty(reason))
{
rotationDoc.Reason = reason;
}
// asked date
DateTime?createdDate = ImportUtility.CleanDate(oldObject.Created_Dt);
if (createdDate == null ||
createdDate < fiscalStart)
{
return; // move to next
}
rotationDoc.AskedDate = (DateTime)createdDate;
// was asked -- ForceHire
if (noteType.ToUpper() == "FORCEHIRE")
{
rotationDoc.WasAsked = false;
rotationDoc.IsForceHire = true;
}
else
{
rotationDoc.WasAsked = true;
rotationDoc.IsForceHire = false;
}
// setup the reason
string tempResponse = "";
if (noteType.ToUpper() == "FORCEHIRE")
{
tempResponse = "Force Hire";
}
else if (noteType.ToUpper() == "NOHIRE")
{
tempResponse = "No Hire";
}
else
{
switch (noteType.ToUpper())
{
case "0":
tempResponse = "Owner didn't call back/no answer";
break;
case "1":
tempResponse = "Equipment not suitable";
break;
case "2":
tempResponse = "Working elsewhere";
break;
case "3":
tempResponse = "No agreement on rates";
break;
case "4":
tempResponse = "Equipment under repairs";
break;
case "5":
tempResponse = "Work limit reached";
break;
case "6":
tempResponse = "No WCB/WCB in arrears";
break;
case "7":
tempResponse = "No insurance/inadequate insurance";
break;
case "8":
tempResponse = "Not interested/turned job down";
break;
case "9":
tempResponse = "Equipment not available";
break;
case "10":
tempResponse = "Other";
break;
}
}
if (string.IsNullOrEmpty(tempResponse))
{
tempResponse = noteType;
}
rotationDoc.OfferRefusalReason = tempResponse;
// ************************************************
// get the imported equipment record map
// ************************************************
string tempId = oldObject.Equip_Id.ToString();
HetImportMap mapEquip = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (mapEquip == null)
{
return; // ignore and move to the next record
}
// ***********************************************
// find the equipment record
// ***********************************************
HetEquipment equipment = dbContext.HetEquipment.AsNoTracking()
.Include(x => x.LocalArea)
.ThenInclude(y => y.ServiceArea)
.ThenInclude(z => z.District)
.FirstOrDefault(x => x.EquipmentId == mapEquip.NewKey);
if (equipment == null)
{
return; // ignore and move to the next record
}
int tempNewEquipmentId = equipment.EquipmentId;
rotationDoc.EquipmentId = tempNewEquipmentId;
// ************************************************
// get the imported project record map
// ************************************************
string tempProjectId = oldObject.Project_Id.ToString();
HetImportMap mapProject = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempProjectId &&
x.OldTable == ImportProject.OldTable &&
x.NewTable == ImportProject.NewTable);
// ***********************************************
// find the project record
// ***********************************************
HetProject project;
if (mapProject != null)
{
project = dbContext.HetProject.AsNoTracking()
.FirstOrDefault(x => x.ProjectId == mapProject.NewKey);
if (project == null)
{
throw new ArgumentException(string.Format("Cannot locate Project record (Rotation Doc Id: {0}", tempId));
}
int tempNewProjectId = project.ProjectId;
rotationDoc.ProjectId = tempNewProjectId;
}
else
{
int districtId = equipment.LocalArea.ServiceArea.District.DistrictId;
int?statusId = StatusHelper.GetStatusId(HetProject.StatusComplete, "projectStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (Time Sheet Equip Id: {0}", tempId));
}
// create new project
project = new HetProject
{
DistrictId = districtId,
Information = "Created to support Rotation Doc import from BCBid",
ProjectStatusTypeId = (int)statusId,
Name = "Legacy BCBid Project",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
// save now so we can access it for other time records
dbContext.HetProject.Add(project);
dbContext.SaveChangesForImport();
// add mapping record
ImportUtility.AddImportMapForProgress(dbContext, ImportProject.OldTable, tempProjectId, project.ProjectId, ImportProject.NewTable);
dbContext.SaveChangesForImport();
}
// ***********************************************
// create rotationDoc
// ***********************************************
rotationDoc.AppCreateUserid = systemId;
rotationDoc.AppCreateTimestamp = DateTime.UtcNow;
rotationDoc.AppLastUpdateUserid = systemId;
rotationDoc.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.BcbidRotationDoc.Add(rotationDoc);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Master Rotation Doc Index: " + maxIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Import Rotation Doc Records Records
/// </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 it has done today is complete for all the records in the xml file. // 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;
}
int maxIndex = startPoint;
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.RotationDoc[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
ImportModels.RotationDoc[] legacyItems = (ImportModels.RotationDoc[])ser.Deserialize(memoryStream);
int ii = startPoint;
// skip the portion already processed
if (startPoint > 0)
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
Debug.WriteLine("Importing Rotation Doc Data. Total Records: " + legacyItems.Length);
foreach (ImportModels.RotationDoc item in legacyItems.WithProgress(progress))
{
// see if we have this one already.
HetImportMap importMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == item.Note_Id.ToString());
// new entry
if (importMap == null)
{
BcbidRotationDoc rotationDoc = null;
CopyToInstance(dbContext, item, ref rotationDoc, systemId, ref maxIndex);
ImportUtility.AddImportMap(dbContext, OldTable, item.Note_Id.ToString(), NewTable, rotationDoc.NoteId);
}
// save change to database
if (++ii % 2000 == 0)
{
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, ii.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChangesForImport();
}
}
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 (Index: {0}): {1}", maxIndex, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Hangfire job to do the data import tasks
/// </summary>
/// <param name="context"></param>
/// <param name="connectionString"></param>
/// <param name="sourceFileLocation"></param>
/// <param name="destinationFileLocation"></param>
public static void ObfuscationJob(PerformContext context, string connectionString, string sourceFileLocation, string destinationFileLocation)
{
// open a connection to the database.
DbAppContext dbContext = new DbAppContext(connectionString)
{
SmUserId = SystemId
};
context.WriteLine("Starting Data Import Job");
// adding system Account if not there in the database
ImportUtility.InsertSystemUser(dbContext, SystemId);
dbContext = new DbAppContext(connectionString);
// Process service areas
ImportServiceArea.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process local areas
ImportLocalArea.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process users
ImportUser.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// process owners
ImportOwner.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// process equipment types
ImportDistrictEquipmentType.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// process dump trucks
ImportDumpTruck.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process equipment attachments
ImportEquipAttach.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// process equipment
ImportEquip.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process projects
ImportProject.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process rotation docs
ImportRotationDoc.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process blocks
ImportBlock.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
// Process equipment usage
ImportEquipUsage.Obfuscate(context, dbContext, sourceFileLocation, destinationFileLocation, SystemId);
}
/// <summary>
/// Hangfire job to do the data import tasks
/// </summary>
/// <param name="context"></param>
/// <param name="seniorityScoringRules"></param>
/// <param name="connectionString"></param>
/// <param name="fileLocation"></param>
public static void ImportJob(PerformContext context, string seniorityScoringRules, string connectionString, string fileLocation)
{
DbAppContext dbContext = new DbAppContext(connectionString)
{
SmUserId = SystemId
};
context.WriteLine("Starting Data Import Job");
// adding system Account if not there in the database
ImportUtility.InsertSystemUser(dbContext, SystemId);
// Create District status record
dbContext = new DbAppContext(connectionString);
ImportServiceArea.ResetDistrictStatus(context, dbContext, SystemId);
//*** Import Service Areas from ServiceArea.xml (HET_SERVICE_AREA)
dbContext = new DbAppContext(connectionString);
ImportServiceArea.Import(context, dbContext, fileLocation, SystemId);
//*** Import Local Areas from Area.xml (HET_LOCAL_AREA)
dbContext = new DbAppContext(connectionString);
ImportLocalArea.Import(context, dbContext, fileLocation, SystemId);
//*** Import Users from User_HETS.xml (HET_USER and HET_USER_ROLE)
dbContext = new DbAppContext(connectionString);
ImportUser.Import(context, dbContext, fileLocation, SystemId);
//*** Import Owners from Owner.xml (HETS_OWNER and HETS_Contact)
dbContext = new DbAppContext(connectionString);
ImportOwner.Import(context, dbContext, fileLocation, SystemId);
dbContext = new DbAppContext(connectionString);
ImportOwner.GenerateSecretKeys(context, dbContext);
dbContext = new DbAppContext(connectionString);
ImportOwner.FixPrimaryContacts(context, dbContext);
//*** Import District Equipment Type from EquipType.xml (HET_DISTRICT_EQUIPMENT_TYPE)
dbContext = new DbAppContext(connectionString);
ImportDistrictEquipmentType.Import(context, dbContext, fileLocation, SystemId);
//*** Import Equipment from Equip.xml (HET_EQUIPMENT)
dbContext = new DbAppContext(connectionString);
ImportEquip.Import(context, dbContext, fileLocation, SystemId);
//*** Import Dump Truck from Dump_Truck.xml (HET_EQUIPMENT_TYPE)
dbContext = new DbAppContext(connectionString);
ImportDumpTruck.Import(context, dbContext, fileLocation, SystemId);
//*** Import Equipment Attachments from Equip_Attach.xml (HET_EQUIPMENT_ATTACHMENT)
dbContext = new DbAppContext(connectionString);
ImportEquipAttach.Import(context, dbContext, fileLocation, SystemId);
//*** Process Equipment Block Assignments
dbContext = new DbAppContext(connectionString);
ImportEquip.ProcessBlocks(context, seniorityScoringRules, dbContext, SystemId);
//*** Import Projects from Project.xml (HET_PROJECT)
dbContext = new DbAppContext(connectionString);
ImportProject.Import(context, dbContext, fileLocation, SystemId);
// reset project sequence
dbContext = new DbAppContext(connectionString);
ImportProject.ResetSequence(context, dbContext);
//*** Import Rotation Docs from Rotation_Doc.xml (BCBID_ROTATION_DOC)
dbContext = new DbAppContext(connectionString);
ImportRotationDoc.Import(context, dbContext, fileLocation, SystemId);
//*** Import Blocks / Local Area Rotation List from Block.xml (HET_DISTRICT_ROTATION_LIST)
dbContext = new DbAppContext(connectionString);
ImportBlock.Import(context, dbContext, fileLocation, SystemId);
//*** Recreate "Last Called" records
dbContext = new DbAppContext(connectionString);
ImportBlock.ProcessLastCalled(context, dbContext, SystemId);
//*** Import Equipment Usage (Time) from Equip_Usage.xml (HET_RENTAL_AGREEMENT and HET_TIME_RECORD)
dbContext = new DbAppContext(connectionString);
ImportEquipUsage.Import(context, dbContext, fileLocation, SystemId);
// *** Final Step - fix the database sequences
dbContext = new DbAppContext(connectionString);
ImportServiceArea.ResetSequence(context, dbContext);
ImportLocalArea.ResetSequence(context, dbContext);
ImportUser.ResetSequence(context, dbContext);
ImportOwner.ResetSequence(context, dbContext);
ImportDistrictEquipmentType.ResetSequence(context, dbContext);
ImportEquip.ResetSequence(context, dbContext);
ImportEquipAttach.ResetSequence(context, dbContext);
ImportProject.ResetSequence(context, dbContext);
ImportBlock.ResetSequence(context, dbContext);
ImportEquipUsage.ResetSequence(context, dbContext);
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="equipAttach"></param>
/// <param name="systemId"></param>
/// <param name="maxEquipAttachIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.EquipAttach oldObject,
ref HetEquipmentAttachment equipAttach, string systemId, ref int maxEquipAttachIndex)
{
try
{
if (oldObject.Equip_Id <= 0)
{
return;
}
equipAttach = new HetEquipmentAttachment {
EquipmentAttachmentId = ++maxEquipAttachIndex
};
// ************************************************
// get the imported equipment record map
// ************************************************
string tempId = oldObject.Equip_Id.ToString();
HetImportMap map = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (map == null)
{
return; // ignore and move to the next record
}
// ************************************************
// get the equipment record
// ************************************************
HetEquipment equipment = dbContext.HetEquipment.AsNoTracking()
.FirstOrDefault(x => x.EquipmentId == map.NewKey);
if (equipment == null)
{
return; // ignore and move to the next record
}
// ************************************************
// set the equipment attachment attributes
// ************************************************
int tempEquipmentId = equipment.EquipmentId;
equipAttach.EquipmentId = tempEquipmentId;
string tempDescription = ImportUtility.CleanString(oldObject.Attach_Desc);
if (string.IsNullOrEmpty(tempDescription))
{
return; // don't add blank attachments
}
tempDescription = ImportUtility.GetCapitalCase(tempDescription);
// populate Name and Description with the same value
equipAttach.Description = tempDescription;
equipAttach.TypeName = tempDescription;
// ***********************************************
// create equipment attachment
// ***********************************************
equipAttach.AppCreateUserid = systemId;
equipAttach.AppCreateTimestamp = DateTime.UtcNow;
equipAttach.AppLastUpdateUserid = systemId;
equipAttach.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetEquipmentAttachment.Add(equipAttach);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Equipment Attachment: " + equipAttach.Description);
Debug.WriteLine("***Error*** - Master Equipment Attachment Index: " + maxEquipAttachIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="owner"></param>
/// <param name="systemId"></param>
/// <param name="maxOwnerIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.Owner oldObject,
ref HetOwner owner, string systemId, ref int maxOwnerIndex)
{
try
{
if (owner != null)
{
return;
}
owner = new HetOwner {
OwnerId = ++maxOwnerIndex
};
// ***********************************************
// set owner code
// ***********************************************
string tempOwnerCode = ImportUtility.CleanString(oldObject.Owner_Cd).ToUpper();
if (!string.IsNullOrEmpty(tempOwnerCode))
{
owner.OwnerCode = tempOwnerCode;
}
else
{
// must have an owner code: HETS-817
return;
}
// ***********************************************
// set company name
// ***********************************************
string tempCompanyName = ImportUtility.CleanString(oldObject.Company_Name);
if (!string.IsNullOrEmpty(tempCompanyName))
{
tempCompanyName = ImportUtility.GetCapitalCase(tempCompanyName);
// fix some capital case names
tempCompanyName = tempCompanyName.Replace("Bc", "BC");
tempCompanyName = tempCompanyName.Replace("Rv", "RV");
tempCompanyName = tempCompanyName.Replace(", & ", " & ");
owner.OrganizationName = tempCompanyName;
}
// ***********************************************
// DBA Name
// ***********************************************
string tempDbaName = ImportUtility.CleanString(oldObject.Operating_AS);
if (!string.IsNullOrEmpty(tempDbaName))
{
tempDbaName = ImportUtility.GetCapitalCase(tempDbaName);
owner.DoingBusinessAs = tempDbaName;
}
// ***********************************************
// maintenance contractor
// ***********************************************
if (oldObject.Maintenance_Contractor != null)
{
owner.IsMaintenanceContractor = (oldObject.Maintenance_Contractor.Trim() == "Y");
}
// ***********************************************
// residency
// ***********************************************
if (oldObject.Local_To_Area != null)
{
owner.MeetsResidency = (oldObject.Local_To_Area.Trim() == "Y");
}
// ***********************************************
// set local area
// ***********************************************
HetLocalArea localArea = dbContext.HetLocalArea.FirstOrDefault(x => x.LocalAreaId == oldObject.Area_Id);
if (localArea != null)
{
owner.LocalAreaId = localArea.LocalAreaId;
}
// ***********************************************
// set other attributes
// ***********************************************
owner.WorkSafeBcexpiryDate = ImportUtility.CleanDate(oldObject.CGL_End_Dt);
owner.CglendDate = ImportUtility.CleanDate(oldObject.CGL_End_Dt);
owner.WorkSafeBcpolicyNumber = ImportUtility.CleanString(oldObject.WCB_Num);
if (owner.WorkSafeBcpolicyNumber == "0")
{
owner.WorkSafeBcpolicyNumber = null;
}
string tempCglCompanyName = ImportUtility.CleanString(oldObject.CGL_Company);
tempCglCompanyName = ImportUtility.GetCapitalCase(tempCglCompanyName);
if (!string.IsNullOrEmpty(tempCglCompanyName))
{
owner.CglCompanyName = tempCglCompanyName;
}
owner.CglPolicyNumber = ImportUtility.CleanString(oldObject.CGL_Policy);
if (owner.CglPolicyNumber == "")
{
owner.CglPolicyNumber = null;
}
// ***********************************************
// manage archive and owner status
// ***********************************************
string tempArchive = oldObject.Archive_Cd;
string tempStatus = oldObject.Status_Cd.Trim();
if (tempArchive == "Y")
{
int?statusId = StatusHelper.GetStatusId("Archived", "ownerStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (OwnerIndex: {0}", maxOwnerIndex));
}
// archived!
owner.ArchiveCode = "Y";
owner.ArchiveDate = DateTime.UtcNow;
owner.ArchiveReason = "Imported from BC Bid";
owner.OwnerStatusTypeId = (int)statusId;
string tempArchiveReason = ImportUtility.CleanString(oldObject.Archive_Reason);
if (!string.IsNullOrEmpty(tempArchiveReason))
{
owner.ArchiveReason = ImportUtility.GetUppercaseFirst(tempArchiveReason);
}
}
else
{
int?statusId = StatusHelper.GetStatusId("Approved", "ownerStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (OwnerIndex: {0}", maxOwnerIndex));
}
int?statusIdUnapproved = StatusHelper.GetStatusId("Unapproved", "ownerStatus", dbContext);
if (statusIdUnapproved == null)
{
throw new DataException(string.Format("Status Id cannot be null (OwnerIndex: {0}", maxOwnerIndex));
}
owner.ArchiveCode = "N";
owner.ArchiveDate = null;
owner.ArchiveReason = null;
owner.OwnerStatusTypeId = tempStatus == "A" ? (int)statusId : (int)statusIdUnapproved;
owner.StatusComment = string.Format("Imported from BC Bid ({0})", tempStatus);
}
// ***********************************************
// manage owner information
// ***********************************************
string tempOwnerFirstName = ImportUtility.CleanString(oldObject.Owner_First_Name);
tempOwnerFirstName = ImportUtility.GetCapitalCase(tempOwnerFirstName);
string tempOwnerLastName = ImportUtility.CleanString(oldObject.Owner_Last_Name);
tempOwnerLastName = ImportUtility.GetCapitalCase(tempOwnerLastName);
// some fields have duplicates in the name
if (tempOwnerLastName == tempOwnerFirstName)
{
tempOwnerFirstName = "";
}
if (!string.IsNullOrEmpty(tempOwnerLastName))
{
owner.Surname = tempOwnerLastName;
}
if (!string.IsNullOrEmpty(tempOwnerFirstName))
{
owner.GivenName = tempOwnerFirstName;
}
// if there is no company name - create one
string tempName = "";
if (string.IsNullOrEmpty(tempCompanyName) &&
!string.IsNullOrEmpty(owner.OwnerCode) &&
string.IsNullOrEmpty(tempOwnerLastName))
{
owner.OrganizationName = owner.OwnerCode;
tempName = owner.OrganizationName;
}
else if (string.IsNullOrEmpty(tempCompanyName) && !string.IsNullOrEmpty(owner.OwnerCode))
{
owner.OrganizationName = string.Format("{0} - {1} {2}", owner.OwnerCode, tempOwnerFirstName, tempOwnerLastName);
tempName = owner.OrganizationName;
}
else if (string.IsNullOrEmpty(tempCompanyName))
{
owner.OrganizationName = string.Format("{0} {1}", tempOwnerFirstName, tempOwnerLastName);
tempName = owner.OrganizationName;
}
// check if the organization name is actually a "Ltd" or other company name
// (in BC Bid the names are sometimes used to store the org)
if (!string.IsNullOrEmpty(tempName) &&
tempName.IndexOf(" Ltd", StringComparison.Ordinal) > -1 ||
tempName.IndexOf(" Resort", StringComparison.Ordinal) > -1 ||
tempName.IndexOf(" Oilfield", StringComparison.Ordinal) > -1 ||
tempName.IndexOf(" Nation", StringComparison.Ordinal) > -1 ||
tempName.IndexOf(" Ventures", StringComparison.Ordinal) > -1 ||
tempName.IndexOf(" Indian Band", StringComparison.Ordinal) > -1)
{
owner.Surname = null;
owner.GivenName = null;
if (!string.IsNullOrEmpty(owner.OwnerCode))
{
owner.OrganizationName = string.Format("{0} {1}", tempOwnerFirstName, tempOwnerLastName);
}
}
// ***********************************************
// format company names
// ***********************************************
if (owner.OrganizationName.EndsWith(" Ltd") ||
owner.OrganizationName.EndsWith(" Ulc") ||
owner.OrganizationName.EndsWith(" Inc") ||
owner.OrganizationName.EndsWith(" Co"))
{
owner.OrganizationName = owner.OrganizationName + ".";
}
// ***********************************************
// manage contacts
// ***********************************************
bool contactExists = false;
if (owner.HetContact != null)
{
foreach (HetContact contactItem in owner.HetContact)
{
if (!string.Equals(contactItem.GivenName, tempOwnerFirstName, StringComparison.InvariantCultureIgnoreCase) &&
!string.Equals(contactItem.Surname, tempOwnerLastName, StringComparison.InvariantCultureIgnoreCase))
{
contactExists = true;
}
}
}
string tempPhone = ImportUtility.FormatPhone(oldObject.Ph_Country_Code, oldObject.Ph_Area_Code, oldObject.Ph_Number, oldObject.Ph_Extension);
string tempFax = ImportUtility.FormatPhone(oldObject.Fax_Country_Code, oldObject.Fax_Area_Code, oldObject.Fax_Number, oldObject.Fax_Extension);
string tempMobile = ImportUtility.FormatPhone(oldObject.Cell_Country_Code, oldObject.Cell_Area_Code, oldObject.Cell_Number, oldObject.Cell_Extension);
// only add if they don't already exist
if (!contactExists && !string.IsNullOrEmpty(tempOwnerLastName))
{
HetContact ownerContact = new HetContact
{
Role = "Owner",
Province = "BC",
Notes = "",
WorkPhoneNumber = tempPhone,
MobilePhoneNumber = tempMobile,
FaxPhoneNumber = tempFax,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
if (!string.IsNullOrEmpty(tempOwnerLastName))
{
ownerContact.Surname = tempOwnerLastName;
}
if (!string.IsNullOrEmpty(tempOwnerFirstName))
{
ownerContact.GivenName = tempOwnerFirstName;
}
if (owner.HetContact == null)
{
owner.HetContact = new List <HetContact>();
}
owner.HetContact.Add(ownerContact);
}
// is the BC Bid contact the same as the owner?
string tempContactPerson = ImportUtility.CleanString(oldObject.Contact_Person);
tempContactPerson = ImportUtility.GetCapitalCase(tempContactPerson);
if (!string.IsNullOrEmpty(tempContactPerson))
{
string tempContactPhone = ImportUtility.FormatPhone(oldObject.Contact_Country_Code, oldObject.Contact_Area_Code, oldObject.Contact_Number, oldObject.Contact_Extension);
// split the name
string tempContactFirstName;
string tempContactLastName;
if (tempContactPerson.IndexOf(" Or ", StringComparison.Ordinal) > -1)
{
tempContactFirstName = tempContactPerson;
tempContactLastName = "";
}
else
{
tempContactFirstName = ImportUtility.GetNamePart(tempContactPerson, 1);
tempContactLastName = ImportUtility.GetNamePart(tempContactPerson, 2);
}
// check if the name is unique
if ((!string.Equals(tempContactFirstName, tempOwnerFirstName, StringComparison.InvariantCultureIgnoreCase) &&
!string.Equals(tempContactLastName, tempOwnerLastName, StringComparison.InvariantCultureIgnoreCase)) ||
(!string.Equals(tempContactFirstName, tempOwnerFirstName, StringComparison.InvariantCultureIgnoreCase) &&
!string.Equals(tempContactFirstName, tempOwnerLastName, StringComparison.InvariantCultureIgnoreCase) &&
!string.IsNullOrEmpty(tempContactLastName)))
{
// check if the name(s) already exist
contactExists = false;
if (owner.HetContact != null)
{
foreach (HetContact contactItem in owner.HetContact)
{
if (string.Equals(contactItem.GivenName, tempContactFirstName, StringComparison.InvariantCultureIgnoreCase) &&
string.Equals(contactItem.Surname, tempContactLastName, StringComparison.InvariantCultureIgnoreCase))
{
contactExists = true;
}
}
}
if (!contactExists)
{
string tempPhoneNumber = "";
if (tempPhone != tempContactPhone && !string.IsNullOrEmpty(tempContactPhone))
{
tempPhoneNumber = tempContactPhone;
}
HetContact primaryContact = new HetContact
{
Role = "Primary Contact",
Province = "BC",
WorkPhoneNumber = tempPhone,
MobilePhoneNumber = tempMobile,
FaxPhoneNumber = tempFax,
Notes = tempPhoneNumber,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
if (!string.IsNullOrEmpty(tempContactLastName))
{
primaryContact.Surname = tempContactLastName;
}
if (!string.IsNullOrEmpty(tempContactFirstName))
{
primaryContact.GivenName = tempContactFirstName;
}
string tempComment = ImportUtility.CleanString(oldObject.Comment);
if (!string.IsNullOrEmpty(tempComment))
{
tempComment = ImportUtility.GetCapitalCase(tempComment);
primaryContact.Notes = tempComment;
}
owner.PrimaryContact = primaryContact; // since this was the default in the file - make it primary
}
}
}
// ensure the contact is valid
if (owner.HetContact != null)
{
for (int i = owner.HetContact.Count - 1; i >= 0; i--)
{
if (string.IsNullOrEmpty(owner.HetContact.ElementAt(i).GivenName) &&
string.IsNullOrEmpty(owner.HetContact.ElementAt(i).Surname))
{
owner.HetContact.Remove(owner.HetContact.ElementAt(i));
}
}
// update primary
if (owner.HetContact.Count > 0 &&
owner.PrimaryContact == null)
{
owner.PrimaryContact = owner.HetContact.ElementAt(0);
owner.HetContact.Remove(owner.HetContact.ElementAt(0));
}
}
// ***********************************************
// create owner
// ***********************************************
owner.AppCreateUserid = systemId;
owner.AppCreateTimestamp = DateTime.UtcNow;
owner.AppLastUpdateUserid = systemId;
owner.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetOwner.Add(owner);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Owner Code: " + owner.OwnerCode);
Debug.WriteLine("***Error*** - Master Owner Index: " + maxOwnerIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="systemId"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.DumpTruck oldObject, string systemId)
{
try
{
if (oldObject.Equip_Id <= 0)
{
return;
}
// dump truck records update the equipment record
// find the original equipment record
string tempId = oldObject.Equip_Id.ToString();
HetImportMap map = dbContext.HetImportMap
.FirstOrDefault(x => x.OldKey == tempId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (map == null)
{
return; // ignore and move to the next record
}
// ************************************************
// get the equipment record and update
// ************************************************
HetEquipment equipment = dbContext.HetEquipment.FirstOrDefault(x => x.EquipmentId == map.NewKey);
if (equipment == null)
{
return; // ignore and move to the next record
}
// set dump truck attributes
string tempLicensedGvw = ImportUtility.CleanString(oldObject.Licenced_GVW);
if (!string.IsNullOrEmpty(tempLicensedGvw))
{
equipment.LicencedGvw = tempLicensedGvw;
}
string tempLegalCapacity = ImportUtility.CleanString(oldObject.Legal_Capacity);
if (!string.IsNullOrEmpty(tempLegalCapacity))
{
equipment.LegalCapacity = tempLegalCapacity;
}
string tempPupLegalCapacity = ImportUtility.CleanString(oldObject.Legal_PUP_Tare_Weight);
if (!string.IsNullOrEmpty(tempPupLegalCapacity))
{
equipment.PupLegalCapacity = tempPupLegalCapacity;
}
equipment.AppLastUpdateUserid = systemId;
equipment.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetEquipment.Update(equipment);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - (Old) Equipment Id: " + oldObject.Equip_Id);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Copy Block item of LocalAreaRotationList item
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="rotationList"></param>
/// <param name="systemId"></param>
/// <param name="maxBlockIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.Block oldObject,
ref HetLocalAreaRotationList rotationList, string systemId, ref int maxBlockIndex)
{
try
{
bool isNew = false;
if (oldObject.Area_Id <= 0)
{
return; // ignore these records
}
if (oldObject.Equip_Type_Id <= 0)
{
return; // ignore these records
}
if (oldObject.Last_Hired_Equip_Id <= 0)
{
return; // ignore these records
}
string tempRecordDate = oldObject.Created_Dt;
if (string.IsNullOrEmpty(tempRecordDate))
{
return; // ignore if we don't have a created date
}
// ***********************************************
// get the area record
// ***********************************************
string tempOldAreaId = oldObject.Area_Id.ToString();
HetImportMap mapArea = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempOldAreaId &&
x.OldTable == ImportLocalArea.OldTable &&
x.NewTable == ImportLocalArea.NewTable);
if (mapArea == null)
{
throw new DataException(string.Format("Area Id cannot be null (BlockIndex: {0})", maxBlockIndex));
}
HetLocalArea area = dbContext.HetLocalArea.AsNoTracking()
.FirstOrDefault(x => x.LocalAreaId == mapArea.NewKey);
if (area == null)
{
throw new ArgumentException(string.Format("Cannot locate Local Area record (Local Area Id: {0})", tempOldAreaId));
}
// ***********************************************
// get the equipment type record
// ***********************************************
string tempOldEquipTypeId = oldObject.Equip_Type_Id.ToString();
HetImportMap mapEquipType = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempOldEquipTypeId &&
x.OldTable == ImportDistrictEquipmentType.OldTable &&
x.NewTable == ImportDistrictEquipmentType.NewTable);
if (mapEquipType == null)
{
return; // ignore and move to the next record
}
HetDistrictEquipmentType equipmentType = dbContext.HetDistrictEquipmentType.AsNoTracking()
.FirstOrDefault(x => x.DistrictEquipmentTypeId == mapEquipType.NewKey);
if (equipmentType == null)
{
throw new ArgumentException(string.Format("Cannot locate District Equipment Type record (Equipment Type Id: {0})", tempOldEquipTypeId));
}
// ***********************************************
// see if a record already exists
// ***********************************************
rotationList = dbContext.HetLocalAreaRotationList
.FirstOrDefault(x => x.LocalAreaId == area.LocalAreaId &&
x.DistrictEquipmentTypeId == equipmentType.DistrictEquipmentTypeId);
if (rotationList == null)
{
isNew = true;
// create new list
rotationList = new HetLocalAreaRotationList
{
LocalAreaRotationListId = ++maxBlockIndex,
LocalAreaId = area.LocalAreaId,
DistrictEquipmentTypeId = equipmentType.DistrictEquipmentTypeId,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow
};
}
// ***********************************************
// get the equipment record
// ***********************************************
string tempOldEquipId = oldObject.Last_Hired_Equip_Id.ToString();
HetImportMap mapEquip = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempOldEquipId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (mapEquip == null)
{
throw new DataException(string.Format("Equipment Id cannot be null (BlockIndex: {0})", maxBlockIndex));
}
HetEquipment equipment = dbContext.HetEquipment.AsNoTracking()
.FirstOrDefault(x => x.EquipmentId == mapEquip.NewKey);
if (equipment == null)
{
throw new ArgumentException(string.Format("Cannot locate Equipment record (Equipment Id: {0})", tempOldEquipId));
}
// ***********************************************
// update the "Ask Next" values
// ***********************************************
float?blockNum = ImportUtility.GetFloatValue(oldObject.Block_Num);
if (blockNum == null)
{
throw new DataException(string.Format("Block Number cannot be null (BlockIndex: {0}", maxBlockIndex));
}
// extract AskNextBlock*Id which is the secondary key of Equip.Id
int equipId = equipment.EquipmentId;
float?seniority = equipment.Seniority;
switch (blockNum)
{
case 1:
rotationList.AskNextBlock1Id = equipId;
rotationList.AskNextBlock1Seniority = seniority;
break;
case 2:
rotationList.AskNextBlock2Id = equipId;
rotationList.AskNextBlock2Seniority = seniority;
break;
case 3:
rotationList.AskNextBlockOpenId = equipId;
break;
}
// ***********************************************
// update or create rotation list
// ***********************************************
rotationList.AppLastUpdateUserid = systemId;
rotationList.AppLastUpdateTimestamp = DateTime.UtcNow;
if (isNew)
{
dbContext.HetLocalAreaRotationList.Add(rotationList);
}
else
{
dbContext.HetLocalAreaRotationList.Update(rotationList);
}
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Master Block Index: " + maxBlockIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Recalculate the block assignment for each piece of equipment
/// </summary>
/// <param name="performContext"></param>
/// <param name="seniorityScoringRules"></param>
/// <param name="dbContext"></param>
/// <param name="systemId"></param>
public static void ProcessBlocks(PerformContext performContext, string seniorityScoringRules, DbAppContext dbContext, string systemId)
{
try
{
performContext.WriteLine("*** Recalculating Equipment Block Assignment ***");
Debug.WriteLine("Recalculating Equipment Block Assignment");
int ii = 0;
string _oldTableProgress = "BlockAssignment_Progress";
string _newTable = "BlockAssignment";
// check if the block assignment has already been completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, _oldTableProgress, BcBidImport.SigId, _newTable);
if (startPoint == BcBidImport.SigId) // this means the assignment job is complete
{
performContext.WriteLine("*** Recalculating Equipment Block Assignment is complete from the former process ***");
return;
}
// ************************************************************
// cleanup old block assignment status records
// ************************************************************
List <HetImportMap> importMapList = dbContext.HetImportMap
.Where(x => x.OldTable == _oldTableProgress &&
x.NewTable == _newTable)
.ToList();
foreach (HetImportMap importMap in importMapList)
{
dbContext.HetImportMap.Remove(importMap);
}
dbContext.SaveChangesForImport();
// ************************************************************
// get processing rules
// ************************************************************
SeniorityScoringRules scoringRules = new SeniorityScoringRules(seniorityScoringRules);
// ************************************************************
// get all local areas
// (using active equipment to minimize the results)
// ************************************************************
List <HetLocalArea> localAreas = dbContext.HetEquipment.AsNoTracking()
.Include(x => x.EquipmentStatusType)
.Include(x => x.LocalArea)
.Where(x => x.EquipmentStatusType.EquipmentStatusTypeCode == HetEquipment.StatusApproved &&
x.ArchiveCode == "N")
.Select(x => x.LocalArea)
.Distinct()
.ToList();
// ************************************************************************
// iterate the data and update the assignment blocks
// (seniority is already calculated)
// ************************************************************************
Debug.WriteLine("Recalculating Equipment Block Assignment - Local Area Record Count: " + localAreas.Count);
foreach (HetLocalArea localArea in localAreas)
{
IQueryable <HetDistrictEquipmentType> equipmentTypes = dbContext.HetEquipment.AsNoTracking()
.Include(x => x.EquipmentStatusType)
.Include(x => x.DistrictEquipmentType)
.Where(x => x.EquipmentStatusType.EquipmentStatusTypeCode == HetEquipment.StatusApproved &&
x.ArchiveCode == "N" &&
x.LocalArea.LocalAreaId == localArea.LocalAreaId)
.Select(x => x.DistrictEquipmentType)
.Distinct();
foreach (HetDistrictEquipmentType districtEquipmentType in equipmentTypes)
{
// get the associated equipment type
HetEquipmentType equipmentTypeRecord = dbContext.HetEquipmentType.AsNoTracking()
.FirstOrDefault(x => x.EquipmentTypeId == districtEquipmentType.EquipmentTypeId);
if (equipmentTypeRecord == null)
{
throw new DataException(string.Format("Invalid District Equipment Type. No associated Equipment Type record (District Equipment Id: {0})", districtEquipmentType.DistrictEquipmentTypeId));
}
// get rules
int blockSize = equipmentTypeRecord.IsDumpTruck ? scoringRules.GetBlockSize("DumpTruck") : scoringRules.GetBlockSize();
int totalBlocks = equipmentTypeRecord.IsDumpTruck ? scoringRules.GetTotalBlocks("DumpTruck") : scoringRules.GetTotalBlocks();
// assign blocks
SeniorityListHelper.AssignBlocks(localArea.LocalAreaId, districtEquipmentType.DistrictEquipmentTypeId, blockSize, totalBlocks, dbContext, false);
// save change to database
if (ii++ % 100 == 0)
{
try
{
Debug.WriteLine("Recalculating Equipment Block Assignment - Index: " + ii);
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, ii.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
}
}
// ************************************************************
// save final set of updates
// ************************************************************
try
{
performContext.WriteLine("*** Recalculating Equipment Block Assignment is Done ***");
Debug.WriteLine("Recalculating Equipment Block Assignment is Done");
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
string temp = string.Format("Error saving data (Record: {0}): {1}", ii, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Copy xml item to instance (table entries)
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="equipType"></param>
/// <param name="systemId"></param>
/// <param name="maxEquipTypeIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.EquipType oldObject,
ref HetDistrictEquipmentType equipType, string systemId, ref int maxEquipTypeIndex)
{
try
{
if (equipType != null)
{
return;
}
// ***********************************************
// get the equipment type
// ***********************************************
float?tempBlueBookRate = ImportUtility.GetFloatValue(oldObject.Equip_Rental_Rate_No);
if (tempBlueBookRate == null)
{
return;
}
// ***********************************************
// get the parent equipment type
// ***********************************************
HetEquipmentType type = dbContext.HetEquipmentType.FirstOrDefault(x => x.BlueBookSection == tempBlueBookRate);
// if it's not found - try to round up to the "parent" blue book section
if (type == null)
{
int tempIntBlueBookRate = Convert.ToInt32(tempBlueBookRate);
type = dbContext.HetEquipmentType.FirstOrDefault(x => x.BlueBookSection == tempIntBlueBookRate);
}
// finally - if that's not found - map to 0 (MISCELLANEOUS)
if (type == null)
{
int tempIntBlueBookRate = 0;
type = dbContext.HetEquipmentType.FirstOrDefault(x => x.BlueBookSection == tempIntBlueBookRate);
}
if (type == null)
{
throw new ArgumentException(
string.Format("Cannot find Equipment Type (Blue Book Rate Number: {0} | Equipment Type Id: {1})",
tempBlueBookRate.ToString(), oldObject.Equip_Type_Id));
}
// ***********************************************
// get the description
// ***********************************************
string tempDistrictDescriptionOnly = ImportUtility.CleanString(oldObject.Equip_Type_Desc);
tempDistrictDescriptionOnly = ImportUtility.GetCapitalCase(tempDistrictDescriptionOnly);
// cleaning up a few data errors from BC Bid
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" ", " ");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" Lrg", "Lgr");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" LoadLgr", " Load Lgr");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("23000 - 37999", "23000-37999");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator- Class 1", "Excavator-Class 1");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" 3-4(19.05-23.13)Tonnes", " 3-4 (19.05-23.13)Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" 3 - 4 (19.05-23.13)Tonnes", " 3-4 (19.05-23.13)Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" 5(23.13-26.76)Tonnes", " 5 (23.13-26.76)Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" 6(26.76-30.84)Tonnes", " 6 (26.76-30.84)Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" 7(30.84-39.92)Tonnes", " 7 (30.84-39.92)Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator-Class 8 - 12", "Excavator-Class 8-12");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(")Tonnes", ") Tonnes");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("(Trailer/Skidmou", "(Trailer/Skidmou)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(" Lgr", "Large");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Compressors", "Compressor");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Bulldozers-Mini", "Bulldozer Mini");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Curbing Machines", "Curbing Machine");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Drilling, Specialized Equipment", "Specialized Drilling Equipment");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Geotch. Drilling Companie", "Geotch. Drilling Company");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavators", "Excavator");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavatr", "Excavator");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excvtr", "Excavator");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Attachements", "Attachments");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 2 To 41,000 Lbs", "Class 2 To 41,999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Heavy Excavator Class 5 To 58,999", "Heavy Excavator Class 5 To 58,999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Heavy Excavator Class 6 To 67,999", "Heavy Excavator Class 6 To 67,999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Heavy Excavator Class 7 To 87.999", "Heavy Excavator Class 7 To 87.999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Heavy Excavator Class 9 To 152,999 Lbs", "Heavy Excavator Class Class 9-12 To 152,999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 1 (Under 32000 Lbs)", "Excav Class 1 (Under 32000 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 2 (32000 - 41999 Lbs)", "Excav Class 2 (32000-41999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 3 (42000 - 44999 Lbs)", "Excav Class 3 (42000-44999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 4 (45000 - 50999 Lbs)", "Excav Class 4 (45000-50999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 5 (51000 - 58999 Lbs)", "Excav Class 5 (51000-58999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 6 (59000 - 67999 Lbs)", "Excav Class 6 (59000-67999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 8 (88000 - 95999 Lbs)", "Excav Class 8 (88000-95999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 9 (96000 - 102999 Lbs)", "Excav Class 9 (96000-102999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 10 (103000 - 118999 Lbs)", "Excav Class 10 (103000-118999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excav Class 11 (119000 - 151999 Lbs)", "Excav Class 11 (119000-151999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator-Class 8-12(39.92-68.95+)", "Excavator-Class 8-12 (39.92-68.95+)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace(") Lbs", " Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Lbs Lbs", "Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Lbs.", "Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Lb", "Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Lbss", "Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("999Lbs", "999 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("000Lbs+", "000 Lbs+");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("000 Lbs +", "000 Lbs+");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("000+ - 152,000Lbs", "000-152,000 Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tract.1", "Tract. 1");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tract.2", "Tract. 2");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class Class", "Class");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Graders 130 - 144 Fwhp", "Graders 130-144 Fwhp");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Graders Under 100 - 129 Fwhp", "Graders Under 100-129 Fwhp");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Craw.64-90,999Lbs", "Craw 64-90,999Lbs");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator Mini-", "Excavator Mini -");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator Mini -2", "Excavator Mini - 2");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator Mini 2", "Excavator Mini - 2");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Flat Decks", "Flat Deck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Misc. Equipment", "Misc Equipment");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Misc. Equip", "Miscellaneous");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Pipe Crews", "Pipe Crew");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Pipecrew", "Pipe Crew");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roll.TandemLarge.", "Roll. Tandem Large");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roll. TandemLarge", "Roll. Tandem Large");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roll.Tandem Med.", "Roll. Tandem Med");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Scraper - Two Engine -", "Scraper - Two Engines -");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("14.2 Scrapers", "14.2 Scraper");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Skidders", "Skidder");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Selfprop Scrapr", "Self Prop Scrapr");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Bellydump", "Belly Dump");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Dump Combo Pup", "Dump Combo (Pup)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tandem Dump Trk", "Tandem Dump Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tandem Dump Trks", "Tandem Dump Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tandem Dump Truc", "Tandem Dump Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tandem Dump Truckk", "Tandem Dump Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tractor/Trailers", "Tractor Trailers");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Truck - Tractor Trucks", "Truck - Tractor Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Loaders-Rubber Tired", "Loaders - Rubber Tired");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tractor-Crawler-Class ", "Tractor - Crawler - Class ");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Heavy Hydraulic Excav Class 11 (119000 - 151999 Lbs)", "Heavy Hydraulic Excav Class 11 (119000-151999 Lbs)");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 1-3 Under", "Class 1-3 - Under");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 4-5 2", "Class 4-5 - 2");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 6-7 3", "Class 6-7 - 3");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 8-9 4", "Class 8-9 - 4");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 10-11 5", "Class 10-11 - 5");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 12-16 6", "Class 12-16 - 6");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Tractors-Rubber Tired", "Tractors - Rubber Tired");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("(Rubber Tired)6.", "(Rubber Tired) 6");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Rubber Tired-Class ", "Rubber Tired - Class ");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("20 - 59.9 Fhwp", "20-59.9 Fhwp");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("20 - 150+ Fhwp", "20-150+ Fhwp");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 6-11 60-119.9 Fwhp", "Class 6-11 - 60-119.9 Fwhp");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Water Trucks", "Water Truck");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Welding Outfit/Truck Mounted", "Welding Outfit-Truck Mounted");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator-Wheeled-Class", "Excavator-Wheeled - Class");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 1-2(", "Class 1-2 - (");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Class 3(", "Class 3 - (");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Backhoe Sm.-40", "Backhoe Sm. -40");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Water Truck -Tandem", "Water Truck - Tandem");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer-Class ", "Roller/Packer - Class ");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer - Class 7 - 11", "Roller/Packer - Class 7-11");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer - Class 5 8", "Roller/Packer - Class 5 - 8");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer - Class 2-4", "Roller/Packer - Class 2 - 4");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer - Class 7-11", "Roller/Packer - Class 7 - 11");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Sp,Vib,Rubber", "Sp, Vib, Rubber");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Roller/Packer 10-15.9T, ", "Roller/Packer 10-15.9T ");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator-Class", "Excavator - Class");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator - Class 3 6", "Excavator - Class 3 - 6");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator - Class 2 3", "Excavator - Class 2 - 3");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Excavator - Class 5 13", "Excavator - Class 5 - 13");
tempDistrictDescriptionOnly = tempDistrictDescriptionOnly.Replace("Graders-Class", "Graders - Class");
// get the abbreviation portion
string tempAbbrev = ImportUtility.CleanString(oldObject.Equip_Type_Cd).ToUpper();
// cleaning up a few data errors from BC Bid
tempAbbrev = tempAbbrev.Replace("BUCTRK", "BUCKET");
tempAbbrev = tempAbbrev.Replace("DUMP 1", "DUMP1");
tempAbbrev = tempAbbrev.Replace("DUMP 2", "DUMP2");
tempAbbrev = tempAbbrev.Replace("MIS", "MISC");
tempAbbrev = tempAbbrev.Replace("MISCC", "MISC");
tempAbbrev = tempAbbrev.Replace("HEL", "HELICOP");
tempAbbrev = tempAbbrev.Replace("SCRNGEQ", "SCRPLA");
tempAbbrev = tempAbbrev.Replace("SECT. ", "SECT.");
tempAbbrev = tempAbbrev.Replace("SKI", "SKID");
tempAbbrev = tempAbbrev.Replace("SKIDD", "SKID");
tempAbbrev = tempAbbrev.Replace("SSM", "SSS");
tempAbbrev = tempAbbrev.Replace("SSP", "SSS");
tempAbbrev = tempAbbrev.Replace("TFC", "TFD");
tempAbbrev = tempAbbrev.Replace("TRUCKS/", "TRUCKS");
tempAbbrev = tempAbbrev.Replace("TDB", "TBD");
string tempDistrictDescriptionFull = string.Format("{0} - {1}", tempAbbrev, tempDistrictDescriptionOnly);
// add new district equipment type
int tempId = type.EquipmentTypeId;
equipType = new HetDistrictEquipmentType
{
DistrictEquipmentTypeId = ++maxEquipTypeIndex,
EquipmentTypeId = tempId,
DistrictEquipmentName = tempDistrictDescriptionFull
};
// ***********************************************
// set the district
// ***********************************************
HetServiceArea serviceArea = dbContext.HetServiceArea.AsNoTracking()
.Include(x => x.District)
.FirstOrDefault(x => x.MinistryServiceAreaId == oldObject.Service_Area_Id);
if (serviceArea == null)
{
throw new ArgumentException(
string.Format("Cannot find Service Area (Service Area Id: {0} | Equipment Type Id: {1})",
oldObject.Service_Area_Id, oldObject.Equip_Type_Id));
}
int districtId = serviceArea.District.DistrictId;
equipType.DistrictId = districtId;
// ***********************************************
// check that we don't have this equipment type
// already (from another service area - but same district)
// ***********************************************
HetDistrictEquipmentType existingEquipmentType = dbContext.HetDistrictEquipmentType.AsNoTracking()
.Include(x => x.District)
.FirstOrDefault(x => x.DistrictEquipmentName == tempDistrictDescriptionFull &&
x.District.DistrictId == districtId);
if (existingEquipmentType != null)
{
equipType.DistrictEquipmentTypeId = existingEquipmentType.DistrictEquipmentTypeId;
return; // not adding a duplicate
}
// ***********************************************
// save district equipment type record
// ***********************************************
equipType.AppCreateUserid = systemId;
equipType.AppCreateTimestamp = DateTime.UtcNow;
equipType.AppLastUpdateUserid = systemId;
equipType.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetDistrictEquipmentType.Add(equipType);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - (Old) Equipment Code: " + oldObject.Equip_Type_Cd);
Debug.WriteLine("***Error*** - Master District Equipment Index: " + maxEquipTypeIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
public static void Obfuscate(PerformContext performContext, DbAppContext dbContext, string sourceLocation, string destinationLocation, string systemId)
{
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, "Obfuscate_" + OldTableProgress, BcBidImport.SigId, NewTable);
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("*** Obfuscating " + XmlFileName + " is complete from the former process ***");
return;
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.Owner[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, sourceLocation, rootAttr);
ImportModels.Owner[] legacyItems = (ImportModels.Owner[])ser.Deserialize(memoryStream);
performContext.WriteLine("Obfuscating owner data");
progress.SetValue(0);
int currentOwner = 0;
List <ImportMapRecord> importMapRecords = new List <ImportMapRecord>();
foreach (ImportModels.Owner item in legacyItems.WithProgress(progress))
{
item.Created_By = systemId;
if (item.Modified_By != null)
{
item.Modified_By = systemId;
}
ImportMapRecord importMapRecordOrganization = new ImportMapRecord
{
TableName = NewTable,
MappedColumn = "OrganizationName",
OriginalValue = item.CGL_Company,
NewValue = "Company " + currentOwner
};
importMapRecords.Add(importMapRecordOrganization);
ImportMapRecord importMapRecordFirstName = new ImportMapRecord
{
TableName = NewTable,
MappedColumn = "Owner_First_Name",
OriginalValue = item.Owner_First_Name,
NewValue = "OwnerFirst" + currentOwner
};
importMapRecords.Add(importMapRecordFirstName);
ImportMapRecord importMapRecordLastName = new ImportMapRecord
{
TableName = NewTable,
MappedColumn = "Owner_Last_Name",
OriginalValue = item.Owner_Last_Name,
NewValue = "OwnerLast" + currentOwner
};
importMapRecords.Add(importMapRecordLastName);
ImportMapRecord importMapRecordOwnerCode = new ImportMapRecord
{
TableName = NewTable,
MappedColumn = "Owner_Cd",
OriginalValue = item.Owner_Cd,
NewValue = "OO" + currentOwner
};
importMapRecords.Add(importMapRecordOwnerCode);
item.Owner_Cd = "OO" + currentOwner;
item.Owner_First_Name = "OwnerFirst" + currentOwner;
item.Owner_Last_Name = "OwnerLast" + currentOwner;
item.Contact_Person = ImportUtility.ScrambleString(ImportUtility.CleanString(item.Contact_Person));
item.Comment = ImportUtility.ScrambleString(ImportUtility.CleanString(item.Comment));
item.WCB_Num = ImportUtility.ScrambleString(ImportUtility.CleanString(item.WCB_Num));
item.CGL_Company = ImportUtility.ScrambleString(ImportUtility.CleanString(item.CGL_Company));
item.CGL_Policy = ImportUtility.ScrambleString(ImportUtility.CleanString(item.CGL_Policy));
currentOwner++;
}
performContext.WriteLine("Writing " + XmlFileName + " to " + destinationLocation);
// write out the array
FileStream fs = ImportUtility.GetObfuscationDestination(XmlFileName, destinationLocation);
ser.Serialize(fs, legacyItems);
fs.Close();
// write out the spreadsheet of import records
ImportUtility.WriteImportRecordsToExcel(destinationLocation, importMapRecords, OldTable);
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="serviceArea"></param>
/// <param name="systemId"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.ServiceArea oldObject,
ref HetServiceArea serviceArea, string systemId)
{
try
{
if (serviceArea == null)
{
serviceArea = new HetServiceArea();
}
if (oldObject.Service_Area_Id <= 0)
{
return;
}
serviceArea.ServiceAreaId = oldObject.Service_Area_Id;
serviceArea.MinistryServiceAreaId = oldObject.Service_Area_Id;
serviceArea.Name = oldObject.Service_Area_Desc.Trim();
// remove " CA" from Service Area Names
if (serviceArea.Name.EndsWith(" CA"))
{
serviceArea.Name = serviceArea.Name.Replace(" CA", "");
}
// service area number
if (oldObject.Service_Area_Cd != null)
{
serviceArea.AreaNumber = int.Parse(oldObject.Service_Area_Cd);
}
// get the district for this service area
int tempServiceAreaId = GetServiceAreaId(serviceArea.Name);
if (tempServiceAreaId > 0)
{
HetDistrict district = dbContext.HetDistrict.AsNoTracking()
.FirstOrDefault(x => x.MinistryDistrictId == tempServiceAreaId);
if (district != null)
{
serviceArea.DistrictId = district.DistrictId;
}
}
if (oldObject.FiscalStart != null)
{
serviceArea.FiscalStartDate = DateTime.ParseExact(oldObject.FiscalStart.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
}
if (oldObject.FiscalEnd != null)
{
serviceArea.FiscalEndDate = DateTime.ParseExact(oldObject.FiscalEnd.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
}
// Address, Phone, etc.
serviceArea.Address = oldObject.Address.Trim();
if (serviceArea.Address.Length > 255)
{
serviceArea.Address = serviceArea.Address.Substring(1, 255);
}
serviceArea.Phone = oldObject.Phone.Trim();
if (serviceArea.Phone.Length > 50)
{
serviceArea.Phone = serviceArea.Phone.Substring(1, 50);
}
serviceArea.Phone = ImportUtility.FormatPhone(serviceArea.Phone);
serviceArea.Fax = oldObject.Fax.Trim();
if (serviceArea.Fax.Length > 50)
{
serviceArea.Fax = serviceArea.Fax.Substring(1, 50);
}
serviceArea.Fax = ImportUtility.FormatPhone(serviceArea.Fax);
serviceArea.SupportingDocuments = oldObject.Sup_Docs.Trim();
if (serviceArea.SupportingDocuments.Length > 500)
{
serviceArea.SupportingDocuments = serviceArea.SupportingDocuments.Substring(1, 500);
}
serviceArea.AppCreateUserid = systemId;
serviceArea.AppCreateTimestamp = DateTime.UtcNow;
serviceArea.AppLastUpdateUserid = systemId;
serviceArea.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetServiceArea.Add(serviceArea);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Service Area Id: " + oldObject.Service_Area_Id);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="equipment"></param>
/// <param name="systemId"></param>
/// <param name="maxEquipmentIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, ImportModels.Equip oldObject,
ref HetEquipment equipment, string systemId, ref int maxEquipmentIndex)
{
try
{
int?statusIdApproved = StatusHelper.GetStatusId("Approved", "equipmentStatus", dbContext);
int?statusIdUnapproved = StatusHelper.GetStatusId("Unapproved", "equipmentStatus", dbContext);
int?statusIdArchived = StatusHelper.GetStatusId("Archived", "equipmentStatus", dbContext);
if (oldObject.Equip_Id <= 0)
{
return;
}
equipment = new HetEquipment {
EquipmentId = ++maxEquipmentIndex
};
// there is a problem with 1 equipment record
// Per BC Bid - the correct Owner Id should be: 8786195
if (oldObject.Equip_Id == 19165)
{
oldObject.Owner_Popt_Id = 8786195;
}
// ***********************************************
// equipment code
// ***********************************************
string tempEquipmentCode = ImportUtility.CleanString(oldObject.Equip_Cd).ToUpper();
if (!string.IsNullOrEmpty(tempEquipmentCode))
{
equipment.EquipmentCode = tempEquipmentCode;
}
else
{
// must have an equipment code: HETS-817
return;
}
// ***********************************************
// set the equipment status
// ***********************************************
string tempArchive = oldObject.Archive_Cd;
string tempStatus = oldObject.Status_Cd.Trim();
if (tempArchive == "Y")
{
if (statusIdArchived == null)
{
throw new DataException(string.Format("Status Id cannot be null (EquipmentIndex: {0})", maxEquipmentIndex));
}
// archived!
equipment.ArchiveCode = "Y";
equipment.ArchiveDate = DateTime.UtcNow;
equipment.ArchiveReason = "Imported from BC Bid";
equipment.EquipmentStatusTypeId = (int)statusIdArchived;
string tempArchiveReason = ImportUtility.CleanString(oldObject.Archive_Reason);
if (!string.IsNullOrEmpty(tempArchiveReason))
{
equipment.ArchiveReason = ImportUtility.GetUppercaseFirst(tempArchiveReason);
}
}
else
{
if (statusIdApproved == null)
{
throw new DataException(string.Format("Status Id cannot be null (EquipmentIndex: {0})", maxEquipmentIndex));
}
if (statusIdUnapproved == null)
{
throw new DataException(string.Format("Status Id cannot be null (EquipmentIndex: {0})", maxEquipmentIndex));
}
equipment.ArchiveCode = "N";
equipment.ArchiveDate = null;
equipment.ArchiveReason = null;
equipment.EquipmentStatusTypeId = tempStatus == "A" ? (int)statusIdApproved : (int)statusIdUnapproved;
equipment.StatusComment = string.Format("Imported from BC Bid ({0})", tempStatus);
}
// ***********************************************
// set equipment attributes
// ***********************************************
string tempLicense = ImportUtility.CleanString(oldObject.Licence).ToUpper();
if (!string.IsNullOrEmpty(tempLicense))
{
equipment.LicencePlate = tempLicense;
}
equipment.ApprovedDate = ImportUtility.CleanDate(oldObject.Approved_Dt);
DateTime?tempReceivedDate = ImportUtility.CleanDate(oldObject.Received_Dt);
if (tempReceivedDate != null)
{
equipment.ReceivedDate = (DateTime)tempReceivedDate;
}
else
{
if (equipment.ArchiveCode == "N" &&
equipment.EquipmentStatusTypeId == statusIdApproved)
{
throw new DataException(string.Format("Received Date cannot be null (EquipmentIndex: {0}", maxEquipmentIndex));
}
}
// get the created date and use the timestamp to fix the
DateTime?tempCreatedDate = ImportUtility.CleanDateTime(oldObject.Created_Dt);
if (tempCreatedDate != null)
{
int hours = Convert.ToInt32(tempCreatedDate?.ToString("HH"));
int minutes = Convert.ToInt32(tempCreatedDate?.ToString("mm"));
int secs = Convert.ToInt32(tempCreatedDate?.ToString("ss"));
equipment.ReceivedDate = equipment.ReceivedDate.AddHours(hours);
equipment.ReceivedDate = equipment.ReceivedDate.AddMinutes(minutes);
equipment.ReceivedDate = equipment.ReceivedDate.AddSeconds(secs);
}
// pay rate
float?tempPayRate = ImportUtility.GetFloatValue(oldObject.Pay_Rate);
if (tempPayRate != null)
{
equipment.PayRate = tempPayRate;
}
// ***********************************************
// make, model, year, etc.
// ***********************************************
string tempType = ImportUtility.CleanString(oldObject.Type);
if (!string.IsNullOrEmpty(tempType))
{
tempType = ImportUtility.GetCapitalCase(tempType);
equipment.Type = tempType;
}
string tempMake = ImportUtility.CleanString(oldObject.Make);
if (!string.IsNullOrEmpty(tempMake))
{
tempMake = ImportUtility.GetCapitalCase(tempMake);
equipment.Make = tempMake;
}
// model
string tempModel = ImportUtility.CleanString(oldObject.Model).ToUpper();
if (!string.IsNullOrEmpty(tempModel))
{
equipment.Model = tempModel;
}
// year
string tempYear = ImportUtility.CleanString(oldObject.Year);
if (!string.IsNullOrEmpty(tempYear))
{
equipment.Year = tempYear;
}
// size
string tempSize = ImportUtility.CleanString(oldObject.Size);
if (!string.IsNullOrEmpty(tempSize))
{
tempSize = ImportUtility.GetCapitalCase(tempSize);
equipment.Size = tempSize;
}
// serial number
string tempSerialNumber = ImportUtility.CleanString(oldObject.Serial_Num).ToUpper();
if (!string.IsNullOrEmpty(tempSerialNumber))
{
equipment.SerialNumber = tempSerialNumber;
}
// operator
string tempOperator = ImportUtility.CleanString(oldObject.Operator);
if (!string.IsNullOrEmpty(tempOperator))
{
equipment.Operator = tempOperator ?? null;
}
// ***********************************************
// add comment into the notes field
// ***********************************************
string tempComment = ImportUtility.CleanString(oldObject.Comment);
if (!string.IsNullOrEmpty(tempComment))
{
tempComment = ImportUtility.GetUppercaseFirst(tempComment);
HetNote note = new HetNote
{
Text = tempComment,
IsNoLongerRelevant = false
};
if (equipment.HetNote == null)
{
equipment.HetNote = new List <HetNote>();
}
equipment.HetNote.Add(note);
}
// ***********************************************
// add equipment to the correct area
// ***********************************************
if (oldObject.Area_Id != null)
{
HetLocalArea area = dbContext.HetLocalArea.AsNoTracking()
.FirstOrDefault(x => x.LocalAreaNumber == oldObject.Area_Id);
if (area != null)
{
int tempAreaId = area.LocalAreaId;
equipment.LocalAreaId = tempAreaId;
}
}
if (equipment.LocalAreaId == null && equipment.ArchiveCode == "N" &&
equipment.EquipmentStatusTypeId == statusIdApproved)
{
throw new DataException(string.Format("Local Area cannot be null (EquipmentIndex: {0}", maxEquipmentIndex));
}
// ***********************************************
// set the equipment type
// ***********************************************
if (oldObject.Equip_Type_Id != null)
{
// get the new id for the "District" Equipment Type
string tempEquipmentTypeId = oldObject.Equip_Type_Id.ToString();
HetImportMap equipMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == ImportDistrictEquipmentType.OldTable &&
x.OldKey == tempEquipmentTypeId &&
x.NewTable == ImportDistrictEquipmentType.NewTable);
if (equipMap != null)
{
HetDistrictEquipmentType distEquipType = dbContext.HetDistrictEquipmentType
.FirstOrDefault(x => x.DistrictEquipmentTypeId == equipMap.NewKey);
if (distEquipType != null)
{
int tempEquipmentId = distEquipType.DistrictEquipmentTypeId;
equipment.DistrictEquipmentTypeId = tempEquipmentId;
}
}
}
if (equipment.DistrictEquipmentTypeId == null && equipment.ArchiveCode == "N" &&
equipment.EquipmentStatusTypeId == statusIdApproved)
{
throw new DataException(string.Format("Equipment Type cannot be null (EquipmentIndex: {0}", maxEquipmentIndex));
}
// ***********************************************
// set the equipment owner
// ***********************************************
HetImportMap ownerMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == ImportOwner.OldTable &&
x.OldKey == oldObject.Owner_Popt_Id.ToString());
if (ownerMap != null)
{
HetOwner owner = dbContext.HetOwner.FirstOrDefault(x => x.OwnerId == ownerMap.NewKey);
if (owner != null)
{
int tempOwnerId = owner.OwnerId;
equipment.OwnerId = tempOwnerId;
// set address fields on the owner record
if (string.IsNullOrEmpty(owner.Address1))
{
string tempAddress1 = ImportUtility.CleanString(oldObject.Addr1);
tempAddress1 = ImportUtility.GetCapitalCase(tempAddress1);
string tempAddress2 = ImportUtility.CleanString(oldObject.Addr2);
tempAddress2 = ImportUtility.GetCapitalCase(tempAddress2);
string tempCity = ImportUtility.CleanString(oldObject.City);
tempCity = ImportUtility.GetCapitalCase(tempCity);
owner.Address1 = tempAddress1;
owner.Address2 = tempAddress2;
owner.City = tempCity;
owner.PostalCode = ImportUtility.CleanString(oldObject.Postal).ToUpper();
owner.Province = "BC";
dbContext.HetOwner.Update(owner);
}
}
}
if (equipment.OwnerId == null && equipment.ArchiveCode != "Y")
{
throw new DataException(string.Format("Owner cannot be null (EquipmentIndex: {0}", maxEquipmentIndex));
}
// ***********************************************
// set seniority and hours
// ***********************************************
float?tempSeniority = ImportUtility.GetFloatValue(oldObject.Seniority);
equipment.Seniority = tempSeniority ?? 0.0F;
float?tempYearsOfService = ImportUtility.GetFloatValue(oldObject.Num_Years);
equipment.YearsOfService = tempYearsOfService ?? 0.0F;
int?tempBlockNumber = ImportUtility.GetIntValue(oldObject.Block_Num);
equipment.BlockNumber = tempBlockNumber ?? null;
float?tempServiceHoursLastYear = ImportUtility.GetFloatValue(oldObject.YTD1);
equipment.ServiceHoursLastYear = tempServiceHoursLastYear ?? 0.0F;
float?tempServiceHoursTwoYearsAgo = ImportUtility.GetFloatValue(oldObject.YTD2);
equipment.ServiceHoursTwoYearsAgo = tempServiceHoursTwoYearsAgo ?? 0.0F;
float?tempServiceHoursThreeYearsAgo = ImportUtility.GetFloatValue(oldObject.YTD3);
equipment.ServiceHoursThreeYearsAgo = tempServiceHoursThreeYearsAgo ?? 0.0F;
// ***********************************************
// using the "to date" field to store the
// equipment "Last_Dt" (hopefully the last time
// this equipment was hired)
// ***********************************************
DateTime?tempLastDate = ImportUtility.CleanDate(oldObject.Last_Dt);
if (tempLastDate != null)
{
equipment.ToDate = (DateTime)tempLastDate;
}
// ***********************************************
// set last verified date (default to March 31, 2018)
// ***********************************************
equipment.LastVerifiedDate = DateTime.Parse("2018-03-31 0:00:01");
// ***********************************************
// create equipment
// ***********************************************
equipment.AppCreateUserid = systemId;
equipment.AppCreateTimestamp = DateTime.UtcNow;
equipment.AppLastUpdateUserid = systemId;
equipment.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetEquipment.Add(equipment);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Equipment Code: " + equipment.EquipmentCode);
Debug.WriteLine("***Error*** - Master Equipment Index: " + maxEquipmentIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Import Equipment 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.HetTimeRecord.Any())
{
maxTimeSheetIndex = dbContext.HetTimeRecord.Max(x => x.TimeRecordId);
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.EquipUsage[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
ImportModels.EquipUsage[] legacyItems = (ImportModels.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 (ImportModels.EquipUsage item in legacyItems.WithProgress(progress))
{
// see if we have this one already
string oldProjectKey = item.Project_Id.ToString();
string oldEquipKey = item.Equip_Id.ToString();
string oldCreatedDate = item.Created_Dt;
string oldKey = string.Format("{0}-{1}-{2}", oldProjectKey, oldEquipKey, oldCreatedDate);
HetImportMap importMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == oldKey);
// new entry
if (importMap == null && item.Equip_Id > 0 && item.Project_Id > 0)
{
HetTimeRecord instance = null;
CopyToTimeRecorded(dbContext, item, ref instance, systemId, ref maxTimeSheetIndex);
if (instance != null)
{
ImportUtility.AddImportMap(dbContext, OldTable, oldKey, NewTable, instance.TimeRecordId);
}
// save change to database
if (++ii % 2000 == 0)
{
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, ii.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChanges();
}
}
}
try
{
performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***");
ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, NewTable);
dbContext.SaveChanges();
}
catch (Exception e)
{
string temp = string.Format("Error saving data (TimeRecordIndex: {0}): {1}", maxTimeSheetIndex, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Generate secret keys
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
public static void GenerateSecretKeys(PerformContext performContext, DbAppContext dbContext)
{
try
{
performContext.WriteLine("*** Generating New Secret Keys ***");
Debug.WriteLine("Generating New Secret Keys");
int ii = 0;
string _oldTableProgress = "SecretKeys_Progress";
string _newTable = "SecretKeys";
// check if the secret keys have already been completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, _oldTableProgress, BcBidImport.SigId, _newTable);
if (startPoint == BcBidImport.SigId) // this means the assignment job is complete
{
performContext.WriteLine("*** Generating New Secret Key is complete from the former process ***");
return;
}
// get records
List <HetOwner> owners = dbContext.HetOwner.AsNoTracking()
.Where(x => x.BusinessId == null)
.ToList();
int i = 0;
foreach (HetOwner owner in owners)
{
i++;
string key = SecretKeyHelper.RandomString(8, owner.OwnerId);
string temp = owner.OwnerCode;
if (string.IsNullOrEmpty(temp))
{
temp = SecretKeyHelper.RandomString(4, owner.OwnerId);
}
key = temp + "-" + DateTime.UtcNow.Year + "-" + key;
// get owner and update
HetOwner ownerRecord = dbContext.HetOwner.First(x => x.OwnerId == owner.OwnerId);
ownerRecord.SharedKey = key;
if (i % 500 == 0)
{
dbContext.SaveChangesForImport();
}
// save change to database
if (ii++ % 100 == 0)
{
try
{
Debug.WriteLine("Generating New Secret Keys - Index: " + ii);
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, ii.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
performContext.WriteLine("Error saving data " + e.Message);
}
}
}
// ************************************************************
// save final set of updates
// ************************************************************
try
{
performContext.WriteLine("*** Done generating New Secret Keys ***");
Debug.WriteLine("Generating New Secret Keys is Done");
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
string temp = string.Format("Error saving data (Record: {0}): {1}", ii, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// Create Last Called
/// </summary>
/// <param name="performContext"></param>
/// <param name="dbContext"></param>
/// <param name="systemId"></param>
public static void ProcessLastCalled(PerformContext performContext, DbAppContext dbContext, string systemId)
{
try
{
performContext.WriteLine("*** Recreating Last Called ***");
Debug.WriteLine("Recreating Last Called");
int ii = 0;
string _oldTableProgress = "LastCalled_Progress";
string _newTable = "LastCalled";
// check if the last called has already been completed
int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, _oldTableProgress, BcBidImport.SigId, _newTable);
if (startPoint == BcBidImport.SigId) // this means the assignment job is complete
{
performContext.WriteLine("*** Recreating Last Called is complete from the former process ***");
return;
}
// ************************************************************
// get all last called records
// ************************************************************
List <HetLocalAreaRotationList> rotationList = dbContext.HetLocalAreaRotationList.AsNoTracking()
.Distinct()
.ToList();
// ************************************************************************
// iterate the data and create rotation requests
// ************************************************************************
Debug.WriteLine("Recreating Last Called - Rotation List Record Count: " + rotationList.Count);
// get status
int?statusIdComplete = StatusHelper.GetStatusId(HetRentalRequest.StatusComplete, "rentalRequestStatus", dbContext);
if (statusIdComplete == null)
{
throw new DataException("Status Id cannot be null");
}
foreach (HetLocalAreaRotationList listItem in rotationList)
{
HetRentalRequest request = new HetRentalRequest
{
LocalAreaId = listItem.LocalAreaId,
DistrictEquipmentTypeId = listItem.DistrictEquipmentTypeId,
RentalRequestStatusTypeId = (int)statusIdComplete,
ExpectedStartDate = DateTime.Now,
ExpectedEndDate = DateTime.Now,
EquipmentCount = 1,
ExpectedHours = 0,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
dbContext.HetRentalRequest.Add(request);
// save change to database
if (ii++ % 100 == 0)
{
Debug.WriteLine("Recreating Last Called - Index: " + ii);
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, ii.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
}
// save remaining requests
dbContext.SaveChangesForImport();
// ************************************************************************
// iterate the data and create "last called" records
// ************************************************************************
foreach (HetLocalAreaRotationList listItem in rotationList)
{
// get request
HetRentalRequest request = dbContext.HetRentalRequest.AsNoTracking()
.FirstOrDefault(x => x.LocalAreaId == listItem.LocalAreaId &&
x.DistrictEquipmentTypeId == listItem.DistrictEquipmentTypeId);
if (request == null)
{
throw new DataException("Rental request cannot be null");
}
// block 1
if (listItem.AskNextBlock1Id != null)
{
// create last call record
HetRentalRequestRotationList rotation = new HetRentalRequestRotationList
{
RentalRequestId = request.RentalRequestId,
EquipmentId = listItem.AskNextBlock1Id,
BlockNumber = 1,
RotationListSortOrder = 1,
AskedDateTime = DateTime.Now,
WasAsked = true,
OfferResponse = "Yes",
OfferResponseDatetime = DateTime.Now,
IsForceHire = false,
Note = "CONVERSION",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
dbContext.HetRentalRequestRotationList.Add(rotation);
}
// block 2
if (listItem.AskNextBlock2Id != null)
{
// create last call record
HetRentalRequestRotationList rotation = new HetRentalRequestRotationList
{
RentalRequestId = request.RentalRequestId,
EquipmentId = listItem.AskNextBlock2Id,
BlockNumber = 2,
RotationListSortOrder = 2,
AskedDateTime = DateTime.Now,
WasAsked = true,
OfferResponse = "Yes",
OfferResponseDatetime = DateTime.Now,
IsForceHire = false,
Note = "CONVERSION",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
dbContext.HetRentalRequestRotationList.Add(rotation);
}
// open block
if (listItem.AskNextBlockOpenId != null)
{
// get equipment record
HetEquipment equipment = dbContext.HetEquipment.AsNoTracking()
.FirstOrDefault(x => x.EquipmentId == listItem.AskNextBlockOpenId);
if (equipment == null)
{
throw new DataException("Equipment cannot be null");
}
// create last call record
HetRentalRequestRotationList rotation = new HetRentalRequestRotationList
{
RentalRequestId = request.RentalRequestId,
EquipmentId = listItem.AskNextBlockOpenId,
BlockNumber = equipment.BlockNumber,
RotationListSortOrder = 3,
AskedDateTime = DateTime.Now,
WasAsked = true,
OfferResponse = "Yes",
OfferResponseDatetime = DateTime.Now,
IsForceHire = false,
Note = "CONVERSION",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
dbContext.HetRentalRequestRotationList.Add(rotation);
}
// save change to database
if (ii++ % 100 == 0)
{
Debug.WriteLine("Recreating Last Called - Index: " + ii);
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, ii.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
}
// save remaining requests
dbContext.SaveChangesForImport();
// ************************************************************
// save final set of updates
// ************************************************************
try
{
performContext.WriteLine("*** Recreating Last Called is Done ***");
Debug.WriteLine("Recreating Last Called is Done");
ImportUtility.AddImportMapForProgress(dbContext, _oldTableProgress, BcBidImport.SigId.ToString(), BcBidImport.SigId, _newTable);
dbContext.SaveChangesForImport();
}
catch (Exception e)
{
string temp = string.Format("Error saving data (Record: {0}): {1}", ii, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <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, ImportModels.EquipUsage oldObject,
ref HetTimeRecord 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;
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.CleanDate(tempRecordDate);
if (recordDate == null || recordDate < fiscalStart)
{
return; // ignore this record - it is outside of the fiscal years
}
}
// ************************************************
// get the imported equipment record map
// ************************************************
string tempId = oldObject.Equip_Id.ToString();
HetImportMap mapEquip = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempId &&
x.OldTable == ImportEquip.OldTable &&
x.NewTable == ImportEquip.NewTable);
if (mapEquip == null)
{
return; // ignore and move to the next record
}
// ***********************************************
// find the equipment record
// ***********************************************
HetEquipment equipment = dbContext.HetEquipment.AsNoTracking()
.Include(x => x.LocalArea)
.ThenInclude(y => y.ServiceArea)
.ThenInclude(z => z.District)
.FirstOrDefault(x => x.EquipmentId == mapEquip.NewKey);
if (equipment == null)
{
return; // ignore and move to the next record
}
// ************************************************
// get the imported project record map
// ************************************************
string tempProjectId = oldObject.Project_Id.ToString();
HetImportMap mapProject = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldKey == tempProjectId &&
x.OldTable == ImportProject.OldTable &&
x.NewTable == ImportProject.NewTable);
// ***********************************************
// find the project record
// (or create a project (inactive))
// ***********************************************
HetProject project;
if (mapProject != null)
{
project = dbContext.HetProject.AsNoTracking()
.FirstOrDefault(x => x.ProjectId == mapProject.NewKey);
if (project == null)
{
throw new ArgumentException(string.Format("Cannot locate Project record (Time Sheet Equip Id: {0}", tempId));
}
}
else
{
int districtId = equipment.LocalArea.ServiceArea.District.DistrictId;
int?statusId = StatusHelper.GetStatusId(HetProject.StatusComplete, "projectStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (Time Sheet Equip Id: {0}", tempId));
}
// create new project
project = new HetProject
{
DistrictId = districtId,
Information = "Created to support Time Record import from BCBid",
ProjectStatusTypeId = (int)statusId,
Name = "Legacy BCBid Project",
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
// save now so we can access it for other time records
dbContext.HetProject.Add(project);
dbContext.SaveChangesForImport();
// add mapping record
ImportUtility.AddImportMapForProgress(dbContext, ImportProject.OldTable, tempProjectId, project.ProjectId, ImportProject.NewTable);
dbContext.SaveChangesForImport();
}
// ***********************************************
// find or create the rental agreement
// ***********************************************
DateTime?enteredDate = ImportUtility.CleanDate(oldObject.Entered_Dt); // use for the agreement
HetRentalAgreement agreement = dbContext.HetRentalAgreement.AsNoTracking()
.FirstOrDefault(x => x.EquipmentId == equipment.EquipmentId &&
x.ProjectId == project.ProjectId &&
x.DistrictId == equipment.LocalArea.ServiceArea.District.DistrictId);
if (agreement == null)
{
int equipmentId = equipment.EquipmentId;
int projectId = project.ProjectId;
int districtId = equipment.LocalArea.ServiceArea.District.DistrictId;
int?statusId = StatusHelper.GetStatusId(HetRentalAgreement.StatusComplete, "rentalAgreementStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (Time Sheet Equip Id: {0}", tempId));
}
int?agrRateTypeId = StatusHelper.GetRatePeriodId(HetRatePeriodType.PeriodDaily, dbContext);
if (agrRateTypeId == null)
{
throw new DataException("Rate Period Id cannot be null");
}
int?year = (ImportUtility.CleanDate(oldObject.Worked_Dt))?.Year;
// create a new agreement record
agreement = new HetRentalAgreement
{
EquipmentId = equipmentId,
ProjectId = projectId,
DistrictId = districtId,
RentalAgreementStatusTypeId = (int)statusId,
RatePeriodTypeId = (int)agrRateTypeId,
Note = "Created to support Time Record import from BCBid",
Number = string.Format("BCBid{0}-{1}-{2}", projectId, equipmentId, year),
DatedOn = enteredDate,
AppCreateUserid = systemId,
AppCreateTimestamp = DateTime.UtcNow,
AppLastUpdateUserid = systemId,
AppLastUpdateTimestamp = DateTime.UtcNow
};
// save now so we can access it for other time records
dbContext.HetRentalAgreement.Add(agreement);
dbContext.SaveChangesForImport();
}
// ***********************************************
// create time record
// ***********************************************
timeRecord = new HetTimeRecord {
TimeRecordId = ++maxTimeSheetIndex
};
// ***********************************************
// set time period type
// ***********************************************
int?timePeriodTypeId = StatusHelper.GetTimePeriodId(HetTimePeriodType.PeriodDay, dbContext);
if (timePeriodTypeId == null)
{
throw new DataException("Time Period Id cannot be null");
}
timeRecord.TimePeriodTypeId = (int)timePeriodTypeId;
// ***********************************************
// set time record attributes
// ***********************************************
DateTime?workedDate = ImportUtility.CleanDate(oldObject.Worked_Dt);
if (workedDate != null)
{
timeRecord.WorkedDate = (DateTime)workedDate;
}
else
{
throw new DataException(string.Format("Worked Date cannot be null (TimeSheet Index: {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 (TimeSheet Index: {0}", maxTimeSheetIndex));
}
if (enteredDate != null)
{
timeRecord.EnteredDate = (DateTime)enteredDate;
}
else
{
throw new DataException(string.Format("Entered Date cannot be null (TimeSheet Index: {0}", maxTimeSheetIndex));
}
// ***********************************************
// create time record
// ***********************************************
int raId = agreement.RentalAgreementId;
timeRecord.RentalAgreementId = raId;
timeRecord.AppCreateUserid = systemId;
timeRecord.AppCreateTimestamp = DateTime.UtcNow;
timeRecord.AppLastUpdateUserid = systemId;
timeRecord.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetTimeRecord.Add(timeRecord);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Worked Date: " + oldObject.Worked_Dt);
Debug.WriteLine("***Error*** - Master Time Record Index: " + maxTimeSheetIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
/// <summary>
/// Import Equipment Attachments
/// </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 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;
}
int maxEquipAttachIndex = 0;
if (dbContext.HetEquipmentAttachment.Any())
{
maxEquipAttachIndex = dbContext.HetEquipmentAttachment.Max(x => x.EquipmentAttachmentId);
}
try
{
string rootAttr = "ArrayOf" + OldTable;
// create progress indicator
performContext.WriteLine("Processing " + OldTable);
IProgressBar progress = performContext.WriteProgressBar();
progress.SetValue(0);
// create serializer and serialize xml file
XmlSerializer ser = new XmlSerializer(typeof(ImportModels.EquipAttach[]), new XmlRootAttribute(rootAttr));
MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr);
ImportModels.EquipAttach[] legacyItems = (ImportModels.EquipAttach[])ser.Deserialize(memoryStream);
int ii = startPoint;
// skip the portion already processed
if (startPoint > 0)
{
legacyItems = legacyItems.Skip(ii).ToArray();
}
Debug.WriteLine("Importing Equipment Attachment Data. Total Records: " + legacyItems.Length);
foreach (ImportModels.EquipAttach item in legacyItems.WithProgress(progress))
{
// see if we have this one already. We used old combine because item.Equip_Id is not unique
string oldKeyCombined = (item.Equip_Id ?? 0 * 100 + item.Attach_Seq_Num ?? 0).ToString();
HetImportMap importMap = dbContext.HetImportMap.AsNoTracking()
.FirstOrDefault(x => x.OldTable == OldTable &&
x.OldKey == oldKeyCombined);
// new entry
if (importMap == null && item.Equip_Id > 0)
{
HetEquipmentAttachment instance = null;
CopyToInstance(dbContext, item, ref instance, systemId, ref maxEquipAttachIndex);
ImportUtility.AddImportMap(dbContext, OldTable, oldKeyCombined, NewTable, instance.EquipmentAttachmentId);
}
// 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 (EquipmentAttachmentIndex: {0}): {1}", maxEquipAttachIndex, e.Message);
performContext.WriteLine(temp);
throw new DataException(temp);
}
}
catch (Exception e)
{
performContext.WriteLine("*** ERROR ***");
performContext.WriteLine(e.ToString());
throw;
}
}
/// <summary>
/// Map data
/// </summary>
/// <param name="dbContext"></param>
/// <param name="oldObject"></param>
/// <param name="project"></param>
/// <param name="systemId"></param>
/// <param name="maxProjectIndex"></param>
private static void CopyToInstance(DbAppContext dbContext, Project oldObject,
ref HetProject project, string systemId, ref int maxProjectIndex)
{
try
{
if (project != null)
{
return;
}
project = new HetProject {
ProjectId = ++maxProjectIndex
};
// ***********************************************
// set project properties
// ***********************************************
string tempProjectNumber = ImportUtility.CleanString(oldObject.Project_Num).ToUpper();
if (!string.IsNullOrEmpty(tempProjectNumber))
{
project.ProvincialProjectNumber = tempProjectNumber;
}
// project name
string tempName = ImportUtility.CleanString(oldObject.Job_Desc1).ToUpper();
if (!string.IsNullOrEmpty(tempName))
{
project.Name = ImportUtility.GetCapitalCase(tempName);
}
// project information
string tempInformation = ImportUtility.CleanString(oldObject.Job_Desc2);
if (!string.IsNullOrEmpty(tempInformation))
{
tempInformation = ImportUtility.GetUppercaseFirst(tempInformation);
project.Information = tempInformation;
}
// ***********************************************
// set service area for the project
// ***********************************************
HetServiceArea serviceArea = dbContext.HetServiceArea.AsNoTracking()
.Include(x => x.District)
.FirstOrDefault(x => x.MinistryServiceAreaId == oldObject.Service_Area_Id);
if (serviceArea == null)
{
throw new DataException(string.Format("Service Area cannot be null (ProjectIndex: {0}", maxProjectIndex));
}
int tempDistrictId = serviceArea.District.DistrictId;
project.DistrictId = tempDistrictId;
// ***********************************************
// check that we don't have this equipment type
// already (from another service area - but same district)
// ***********************************************
HetProject existingProject = dbContext.HetProject.AsNoTracking()
.Include(x => x.ProjectStatusType)
.Include(x => x.District)
.FirstOrDefault(x => x.Name == tempName &&
x.District.DistrictId == tempDistrictId);
if (existingProject != null)
{
project.ProjectId = existingProject.ProjectId;
return; // not adding a duplicate
}
// ***********************************************
// default the project to Active
// ***********************************************
int?statusId = StatusHelper.GetStatusId(HetProject.StatusActive, "projectStatus", dbContext);
if (statusId == null)
{
throw new DataException(string.Format("Status Id cannot be null (ProjectIndex: {0}", maxProjectIndex));
}
project.ProjectStatusTypeId = (int)statusId;
// ***********************************************
// create project
// ***********************************************
project.AppCreateUserid = systemId;
project.AppCreateTimestamp = DateTime.UtcNow;
project.AppLastUpdateUserid = systemId;
project.AppLastUpdateTimestamp = DateTime.UtcNow;
dbContext.HetProject.Add(project);
}
catch (Exception ex)
{
Debug.WriteLine("***Error*** - Project Name: " + oldObject.Job_Desc1);
Debug.WriteLine("***Error*** - Master project Index: " + maxProjectIndex);
Debug.WriteLine(ex.Message);
throw;
}
}
