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; } }