/// <summary> /// Import Dump Truck /// </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; // create Processer progress indicator performContext.WriteLine("Processing " + OldTable); IProgressBar progress = performContext.WriteProgressBar(); progress.SetValue(0); // create serializer and serialize xml file XmlSerializer ser = new XmlSerializer(typeof(DumpTruck[]), new XmlRootAttribute(rootAttr)); MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr); DumpTruck[] legacyItems = (DumpTruck[])ser.Deserialize(memoryStream); int ii = startPoint; // skip the portion already processed if (startPoint > 0) { legacyItems = legacyItems.Skip(ii).ToArray(); } Debug.WriteLine("Importing Dump Truck Data. Total Records: " + legacyItems.Length); int lastEquipmentIndex = -1; foreach (DumpTruck item in legacyItems.WithProgress(progress)) { lastEquipmentIndex = item.Equip_Id; // see if we have this one already ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == item.Equip_Id.ToString()); // new entry if (importMap == null && item.Equip_Id > 0) { CopyToInstance(dbContext, item, systemId); ImportUtility.AddImportMap(dbContext, OldTable, item.Equip_Id.ToString(), NewTable, item.Equip_Id); } // save change to database periodically to avoid frequent writing to the database if (++ii % 500 == 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 (Dumptruck EquipmentIndex: {0}): {1}", lastEquipmentIndex, e.Message); performContext.WriteLine(temp); throw new DataException(temp); } } catch (Exception e) { performContext.WriteLine("*** ERROR ***"); performContext.WriteLine(e.ToString()); throw; } }
/// <summary> /// Adding Rental Agreement Rate and Time Records to rental agreement /// </summary> /// <param name="dbContext"></param> /// <param name="oldObject"></param> /// <param name="rentalAgreement"></param> /// <param name="workedDate"></param> /// <param name="systemId"></param> private static void AddingRateTimeForRentaAgreement(DbAppContext dbContext, EquipUsage oldObject, ref RentalAgreement rentalAgreement, string workedDate, string systemId) { // adding rental agreement rates and time records: // the two are added together because time record reference rental agreement rate // adding general properties for Rental Agreement Rate DateTime lastUpdateTimestamp = DateTime.UtcNow; if (oldObject.Entered_Dt != null) { lastUpdateTimestamp = DateTime.ParseExact(oldObject.Entered_Dt.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } string lastUpdateUserid = oldObject.Created_By == null ? systemId : ImportUtility.AddUserFromString(dbContext, oldObject.Created_By, systemId).SmUserId; // adding general properties for Time Record DateTime workedDateTime; if (oldObject.Worked_Dt != null) { workedDateTime = DateTime.ParseExact(workedDate, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } else { workedDateTime = DateTime.UtcNow; } DateTime createdDate; if (oldObject.Created_Dt != null) { createdDate = DateTime.ParseExact(oldObject.Created_Dt.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } else { createdDate = DateTime.UtcNow; } // adding three rates and hours using a Dictionary Dictionary <int, Pair> f = new Dictionary <int, Pair>(); float rate = (float)Decimal.Parse(oldObject.Rate ?? "0", System.Globalization.NumberStyles.Any); float rate2 = (float)Decimal.Parse(oldObject.Rate2 ?? "0", System.Globalization.NumberStyles.Any); float rate3 = (float)Decimal.Parse(oldObject.Rate3 ?? "0", System.Globalization.NumberStyles.Any); float hours = (float)Decimal.Parse(oldObject.Hours ?? "0", System.Globalization.NumberStyles.Any); float hours2 = (float)Decimal.Parse(oldObject.Hours2 ?? "0", System.Globalization.NumberStyles.Any); float hours3 = (float)Decimal.Parse(oldObject.Hours3 ?? "0", System.Globalization.NumberStyles.Any); // add items to dictionary if (hours != 0.0 || rate != 0.0) { f.Add(1, new Pair(hours, rate)); } if (hours2 != 0.0 || rate2 != 0.0) { f.Add(2, new Pair(hours2, rate2)); } if (hours3 != 0.0 || rate3 != 0.0) { f.Add(3, new Pair(hours3, rate3)); } // use var in foreach loop. int ii = 0; RentalAgreementRate [] rateA = new RentalAgreementRate[3]; TimeRecord [] tRecA = new TimeRecord[3]; foreach (var pair in f) { RentalAgreementRate exitingRate = rentalAgreement.RentalAgreementRates.FirstOrDefault(x => x.Rate == pair.Value.Rate); // rate does not exist if (exitingRate == null) { // adding the new rate rateA[ii] = new RentalAgreementRate { Comment = "Import from BCBid", IsAttachment = false, LastUpdateTimestamp = lastUpdateTimestamp, LastUpdateUserid = lastUpdateUserid, CreateTimestamp = createdDate, CreateUserid = lastUpdateUserid, Rate = pair.Value.Rate }; rentalAgreement.RentalAgreementRates.Add(rateA[ii]); // add time Record tRecA[ii] = new TimeRecord { EnteredDate = lastUpdateTimestamp, LastUpdateUserid = lastUpdateUserid, WorkedDate = workedDateTime, Hours = pair.Value.Hours, CreateTimestamp = createdDate, CreateUserid = lastUpdateUserid, RentalAgreementRate = rateA[ii] }; rentalAgreement.TimeRecords.Add(tRecA[ii]); } else { //the rate already existed which is exitingRate, no need to add rate, just add Time Record TimeRecord existingTimeRec = rentalAgreement.TimeRecords.FirstOrDefault(x => x.WorkedDate == workedDateTime); if (existingTimeRec == null) { // the new Time Record is added if it does not exist, otherwise, it already existed tRecA[ii] = new TimeRecord { EnteredDate = lastUpdateTimestamp, LastUpdateUserid = lastUpdateUserid, WorkedDate = workedDateTime, Hours = pair.Value.Hours, CreateTimestamp = createdDate, CreateUserid = lastUpdateUserid, RentalAgreementRate = exitingRate }; rentalAgreement.TimeRecords.Add(tRecA[ii]); } } ii++; } }
/// <summary> /// Create a service and set the database context /// </summary> public TimeRecordService(DbAppContext context) { _context = context; }
/// <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> /// Create a service and set the database context /// </summary> public RegionService(DbAppContext context) { _context = context; }
internal InteractionService(DbAppContext appContext) { _context = appContext; }
public PDFController(IHttpContextAccessor httpContextAccessor, IConfigurationRoot configuration, DbAppContext context, ILoggerFactory loggerFactory) { Configuration = configuration; _httpContextAccessor = httpContextAccessor; _context = context; userId = getFromHeaders("SM_UNIVERSALID"); guid = getFromHeaders("SMGOV_USERGUID"); directory = getFromHeaders("SM_AUTHDIRNAME"); _logger = loggerFactory.CreateLogger(typeof(PDFController)); }
/// <summary> /// Region Service Constructor /// </summary> public RegionService(DbAppContext context, IConfiguration configuration) { _context = context; _configuration = configuration; }
public CompanyCrud(DbAppContext db) : base(db) { }
/// <summary> /// Fix the sequence for the tables populated by the import process /// </summary> /// <param name="performContext"></param> /// <param name="dbContext"></param> public static void ResetSequence(PerformContext performContext, DbAppContext dbContext) { try { // ************************************** // Time Records // ************************************** performContext.WriteLine("*** Resetting HET_TIME_RECORD database sequence after import ***"); Debug.WriteLine("Resetting HET_TIME_RECORD database sequence after import"); if (dbContext.HetTimeRecord.Any()) { // get max key int maxKey = dbContext.HetTimeRecord.Max(x => x.TimeRecordId); maxKey = maxKey + 1; using (DbCommand command = dbContext.Database.GetDbConnection().CreateCommand()) { // check if this code already exists command.CommandText = string.Format(@"SELECT SETVAL('public.""HET_TIME_RECORD_ID_seq""', {0});", maxKey); dbContext.Database.OpenConnection(); command.ExecuteNonQuery(); dbContext.Database.CloseConnection(); } } performContext.WriteLine("*** Done resetting HET_TIME_RECORD database sequence after import ***"); Debug.WriteLine("Resetting HET_TIME_RECORD database sequence after import - Done!"); // ************************************** // Rental Agreements // ************************************** performContext.WriteLine("*** Resetting HET_RENTAL_AGREEMENT database sequence after import ***"); Debug.WriteLine("Resetting HET_RENTAL_AGREEMENT database sequence after import"); if (dbContext.HetRentalAgreement.Any()) { // get max key int maxKey = dbContext.HetRentalAgreement.Max(x => x.RentalAgreementId); maxKey = maxKey + 1; using (DbCommand command = dbContext.Database.GetDbConnection().CreateCommand()) { // check if this code already exists command.CommandText = string.Format(@"SELECT SETVAL('public.""HET_RENTAL_AGREEMENT_ID_seq""', {0});", maxKey); dbContext.Database.OpenConnection(); command.ExecuteNonQuery(); dbContext.Database.CloseConnection(); } } performContext.WriteLine("*** Done resetting HET_RENTAL_AGREEMENT database sequence after import ***"); Debug.WriteLine("Resetting HET_RENTAL_AGREEMENT database sequence after import - Done!"); } catch (Exception e) { performContext.WriteLine("*** ERROR ***"); performContext.WriteLine(e.ToString()); throw; } }
static private void CopyToInstance(PerformContext performContext, DbAppContext dbContext, HETSAPI.Import.Project oldObject, ref Models.Project instance, string systemId) { bool isNew = false; if (oldObject.Project_Id <= 0) { return; } //Add the user specified in oldObject.Modified_By and oldObject.Created_By if not there in the database Models.User modifiedBy = ImportUtility.AddUserFromString(dbContext, "", systemId); Models.User createdBy = ImportUtility.AddUserFromString(dbContext, oldObject.Created_By, systemId); if (instance == null) { isNew = true; instance = new Models.Project(); instance.Id = oldObject.Project_Id; try { // instance.ProjectId = oldObject.Reg_Dump_Trk; try { //4 properties instance.ProvincialProjectNumber = oldObject.Project_Num; ServiceArea serviceArea = dbContext.ServiceAreas.FirstOrDefault(x => x.Id == oldObject.Service_Area_Id); District dis = dbContext.Districts.FirstOrDefault(x => x.Id == serviceArea.DistrictId); if (dis != null) { instance.District = dis; instance.DistrictId = dis.Id; } else // This means that the District Id is not in the database. { //This happens when the production data does not include district Other than "Lower Mainland" or all the districts return; } } catch (Exception e) { string iii = e.ToString(); } try { instance.Name = oldObject.Job_Desc1; } catch (Exception e) { string i = e.ToString(); } try { instance.Information = oldObject.Job_Desc2; } catch (Exception e) { string i = e.ToString(); } try { instance.Notes = new List <Note>(); Models.Note note = new Note(); note.Text = new string(oldObject.Job_Desc2.Take(2048).ToArray()); note.IsNoLongerRelevant = true; instance.Notes.Add(note); } catch (Exception e) { string i = e.ToString(); } // instance.RentalAgreements // instance.RentalRequests = oldObject. try { //9 properties instance.CreateTimestamp = DateTime.ParseExact(oldObject.Created_Dt.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } catch (Exception e) { instance.CreateTimestamp = DateTime.UtcNow; } instance.CreateUserid = createdBy.SmUserId; } catch (Exception e) { string i = e.ToString(); } dbContext.Projects.Add(instance); } else { instance = dbContext.Projects .First(x => x.Id == oldObject.Project_Id); instance.LastUpdateUserid = modifiedBy.SmUserId; instance.LastUpdateTimestamp = DateTime.UtcNow; dbContext.Projects.Update(instance); } }
/// <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 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> /// Create a service and set the database context /// </summary> public AdminService(IHttpContextAccessor httpContextAccessor, IConfiguration configuration, DbAppContext context) : base(httpContextAccessor, context) { _context = context; Configuration = configuration; }
/// <summary> /// Create a service and set the database context /// </summary> public SchoolBusOwnerService(DbAppContext context) { _context = context; }
public CompanyController(DbAppContext db) { this.db = db; }
/// <summary> /// Create a service and set the database context /// </summary> public DumpTruckService(DbAppContext context) { _context = context; }
/// <summary> /// Create a service and set the database context /// </summary> public OwnerService(IHttpContextAccessor httpContextAccessor, DbAppContext context) : base(httpContextAccessor, context) { _context = context; }
/// <summary> /// Create a service and set the database context /// </summary> public AttachmentService(DbAppContext context) { _context = context; }
/// <summary> /// Create a service and set the database context /// </summary> public EquipmentTypeService(DbAppContext context) { _context = context; }
/// <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> /// Create a service and set the database context /// </summary> public SchoolBusNoteApiService(DbAppContext context) { _context = context; }
/// <summary> /// Create a service and set the database context /// </summary> public CityService(DbAppContext context) { _context = context; }
/// <summary> /// IMport City Constructor /// </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) { ImportCities(performContext, dbContext, fileLocation, systemId); }
/// <summary> /// Map data /// </summary> /// <param name="dbContext"></param> /// <param name="oldObject"></param> /// <param name="rentalAgreement"></param> /// <param name="note"></param> /// <param name="workedDate"></param> /// <param name="equips"></param> /// <param name="systemId"></param> private static void CopyToTimeRecorded(DbAppContext dbContext, EquipUsage oldObject, ref RentalAgreement rentalAgreement, string note, string workedDate, List <Equipment> equips, string systemId) { // add the user specified in oldObject.Modified_By and oldObject.Created_By if not there in the database User modifiedBy = ImportUtility.AddUserFromString(dbContext, "", systemId); User createdBy = ImportUtility.AddUserFromString(dbContext, oldObject.Created_By, systemId); if (rentalAgreement == null) { rentalAgreement = new RentalAgreement { RentalAgreementRates = new List <RentalAgreementRate>(), TimeRecords = new List <TimeRecord>() }; Equipment equip = equips.FirstOrDefault(x => x.Id == oldObject.Equip_Id); if (equip != null) { rentalAgreement.Equipment = equip; rentalAgreement.EquipmentId = equip.Id; } Models.Project proj = dbContext.Projects.FirstOrDefault(x => x.Id == oldObject.Project_Id); if (proj != null) { rentalAgreement.Project = proj; rentalAgreement.ProjectId = proj.Id; } // adding rental agreement rates and Time_Records: The two are added together becase Time Record reference rental agreement rate. AddingRateTimeForRentaAgreement(dbContext, oldObject, ref rentalAgreement, workedDate, systemId); rentalAgreement.Status = "Imported from BCBid"; rentalAgreement.Note = note; rentalAgreement.EquipmentRate = (float)Decimal.Parse(oldObject.Rate ?? "0", System.Globalization.NumberStyles.Any); if (oldObject.Entered_Dt != null) { rentalAgreement.DatedOn = DateTime.ParseExact(oldObject.Entered_Dt.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } if (oldObject.Created_Dt != null) { try { rentalAgreement.CreateTimestamp = DateTime.ParseExact(oldObject.Created_Dt.Trim().Substring(0, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture); } catch { rentalAgreement.CreateTimestamp = DateTime.UtcNow; } } rentalAgreement.CreateUserid = createdBy.SmUserId; dbContext.RentalAgreements.Add(rentalAgreement); } else { rentalAgreement = dbContext.RentalAgreements.First(x => x.Note == note); if (rentalAgreement.RentalAgreementRates == null) { rentalAgreement.RentalAgreementRates = new List <RentalAgreementRate>(); } if (rentalAgreement.TimeRecords == null) { rentalAgreement.TimeRecords = new List <TimeRecord>(); } AddingRateTimeForRentaAgreement(dbContext, oldObject, ref rentalAgreement, workedDate, systemId); rentalAgreement.LastUpdateUserid = modifiedBy.SmUserId; rentalAgreement.LastUpdateTimestamp = DateTime.UtcNow; dbContext.RentalAgreements.Update(rentalAgreement); } }
/// <summary> /// Import existing Cities /// </summary> /// <param name="performContext"></param> /// <param name="dbContext"></param> /// <param name="fileLocation"></param> /// <param name="systemId"></param> private static void ImportCities(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId) { string completed = DateTime.Now.ToString("d") + "-" + "Completed"; ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == completed && x.NewKey == SigId); if (importMap != null) { performContext.WriteLine("*** Importing " + XmlFileName + " is complete from the former process ***"); return; } try { string rootAttr = "ArrayOf" + OldTable; performContext.WriteLine("Processing " + OldTable); IProgressBar progress = performContext.WriteProgressBar(); progress.SetValue(0); // create serializer and serialize xml file XmlSerializer ser = new XmlSerializer(typeof(HetsCity[]), new XmlRootAttribute(rootAttr)); MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr); HetsCity[] legacyItems = (HetsCity[])ser.Deserialize(memoryStream); foreach (var item in legacyItems.WithProgress(progress)) { // see if we have this one already importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == item.City_Id.ToString()); // new entry if (importMap == null) { City city = null; CopyToInstance(performContext, dbContext, item, ref city, systemId); ImportUtility.AddImportMap(dbContext, OldTable, item.City_Id.ToString(), NewTable, city.Id); } else // update { City city = dbContext.Cities.FirstOrDefault(x => x.Id == importMap.NewKey); // record was deleted if (city == null) { CopyToInstance(performContext, dbContext, item, ref city, systemId); // update the import map importMap.NewKey = city.Id; dbContext.ImportMaps.Update(importMap); dbContext.SaveChangesForImport(); } else // ordinary update { CopyToInstance(performContext, dbContext, item, ref city, systemId); // touch the import map importMap.LastUpdateTimestamp = DateTime.UtcNow; dbContext.ImportMaps.Update(importMap); dbContext.SaveChangesForImport(); } } } performContext.WriteLine("*** Done ***"); ImportUtility.AddImportMap(dbContext, OldTable, completed, NewTable, SigId); } catch (Exception e) { performContext.WriteLine("*** ERROR ***"); performContext.WriteLine(e.ToString()); } }
/// <summary> /// Import Equip(ment) Usage /// </summary> /// <param name="performContext"></param> /// <param name="dbContext"></param> /// <param name="fileLocation"></param> /// <param name="systemId"></param> public static void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId) { // check the start point. If startPoint == sigId then it is already completed int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, OldTableProgress, BCBidImport.SigId); if (startPoint == BCBidImport.SigId) // this means the import job it has done today is complete for all the records in the xml file. { performContext.WriteLine("*** Importing " + XmlFileName + " is complete from the former process ***"); return; } try { string rootAttr = "ArrayOf" + OldTable; // create Processer progress indicator performContext.WriteLine("Processing " + OldTable); IProgressBar progress = performContext.WriteProgressBar(); progress.SetValue(0); // create serializer and serialize xml file XmlSerializer ser = new XmlSerializer(typeof(EquipUsage[]), new XmlRootAttribute(rootAttr)); MemoryStream memoryStream = ImportUtility.MemoryStreamGenerator(XmlFileName, OldTable, fileLocation, rootAttr); EquipUsage[] legacyItems = (EquipUsage[])ser.Deserialize(memoryStream); //Use this list to save a trip to query database in each iteration List <Equipment> equips = dbContext.Equipments .Include(x => x.DumpTruck) .Include(x => x.DistrictEquipmentType) .ToList(); int ii = startPoint; // skip the portion already processed if (startPoint > 0) { legacyItems = legacyItems.Skip(ii).ToArray(); } foreach (var item in legacyItems.WithProgress(progress)) { // see if we have this one already string oldKey = (item.Equip_Id ?? 0) + (item.Project_Id ?? 0).ToString() + (item.Service_Area_Id ?? 0); string workedDate = item.Worked_Dt.Trim().Substring(0, 10); string note = oldKey + "-" + workedDate.Substring(0, 4); string oldKeyAll = oldKey + "-" + workedDate.Substring(0, 10); ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == OldTable && x.OldKey == oldKeyAll); // new entry if (importMap == null) { if (item.Equip_Id > 0) { RentalAgreement rentalAgreement = dbContext.RentalAgreements.FirstOrDefault(x => x.Note == note); CopyToTimeRecorded(dbContext, item, ref rentalAgreement, note, workedDate, equips, systemId); ImportUtility.AddImportMap(dbContext, OldTable, oldKeyAll, NewTable, rentalAgreement.Id); } } else // update { RentalAgreement rentalAgreement = dbContext.RentalAgreements.FirstOrDefault(x => x.Id == importMap.NewKey); // record was deleted if (rentalAgreement == null) { CopyToTimeRecorded(dbContext, item, ref rentalAgreement, note, workedDate, equips, systemId); // update the import map importMap.NewKey = rentalAgreement.Id; dbContext.ImportMaps.Update(importMap); } else // ordinary update { CopyToTimeRecorded(dbContext, item, ref rentalAgreement, note, workedDate, equips, systemId); // touch the import map importMap.LastUpdateTimestamp = DateTime.UtcNow; dbContext.ImportMaps.Update(importMap); } } // save change to database periodically to avoid frequent writing to the database if (++ii % 1000 == 0) { try { ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, ii.ToString(), BCBidImport.SigId); dbContext.SaveChangesForImport(); } catch (Exception e) { performContext.WriteLine("Error saving data " + e.Message); } } } try { performContext.WriteLine("*** Importing " + XmlFileName + " is Done ***"); ImportUtility.AddImportMapForProgress(dbContext, OldTableProgress, BCBidImport.SigId.ToString(), BCBidImport.SigId); dbContext.SaveChangesForImport(); } catch (Exception e) { performContext.WriteLine("Error saving data " + e.Message); } } catch (Exception e) { performContext.WriteLine("*** ERROR ***"); performContext.WriteLine(e.ToString()); } }
static public void Import(PerformContext performContext, DbAppContext dbContext, string fileLocation, string systemId) { // Check the start point. If startPoint == sigId then it is already completed int startPoint = ImportUtility.CheckInterMapForStartPoint(dbContext, oldTable_Progress, BCBidImport.sigId); if (startPoint == BCBidImport.sigId) // This means the import job it has done today is complete for all the records in the xml file. { performContext.WriteLine("*** Importing " + xmlFileName + " is complete from the former process ***"); return; } try { string rootAttr = "ArrayOf" + oldTable; //Create Processer progress indicator performContext.WriteLine("Processing " + oldTable); var progress = performContext.WriteProgressBar(); progress.SetValue(0); // create serializer and serialize xml file XmlSerializer ser = new XmlSerializer(typeof(Rotation_Doc[]), new XmlRootAttribute(rootAttr)); MemoryStream memoryStream = ImportUtility.memoryStreamGenerator(xmlFileName, oldTable, fileLocation, rootAttr); HETSAPI.Import.Rotation_Doc[] legacyItems = (HETSAPI.Import.Rotation_Doc[])ser.Deserialize(memoryStream); //Use this list to save a trip to query database in each iteration List <Models.Equipment> equips = dbContext.Equipments .Include(x => x.DumpTruck) .Include(x => x.DistrictEquipmentType) .ToList(); List <Models.Project> projs = dbContext.Projects .ToList(); int ii = startPoint; if (startPoint > 0) // Skip the portion already processed { legacyItems = legacyItems.Skip(ii).ToArray(); } foreach (var item in legacyItems.WithProgress(progress)) { // see if we have this one already. string oldKey = item.Equip_Id + item.Note_Dt + item.Created_Dt; ImportMap importMap = dbContext.ImportMaps.FirstOrDefault(x => x.OldTable == oldTable && x.OldKey == oldKey); if (importMap == null) // new entry { Models.Note instance = null; CopyToInstance(performContext, dbContext, item, ref instance, equips, projs, systemId); ImportUtility.AddImportMap(dbContext, oldTable, oldKey, newTable, instance.Id); } else // update { Models.Note instance = dbContext.Notes.FirstOrDefault(x => x.Id == importMap.NewKey); if (instance == null) // record was deleted { CopyToInstance(performContext, dbContext, item, ref instance, equips, projs, systemId); // update the import map. importMap.NewKey = instance.Id; dbContext.ImportMaps.Update(importMap); } else // ordinary update. { CopyToInstance(performContext, dbContext, item, ref instance, equips, projs, systemId); // touch the import map. importMap.LastUpdateTimestamp = DateTime.UtcNow; dbContext.ImportMaps.Update(importMap); } } if (++ii % 1000 == 0) { try { ImportUtility.AddImportMap_For_Progress(dbContext, oldTable_Progress, ii.ToString(), BCBidImport.sigId); int iResult = dbContext.SaveChangesForImport(); } catch (Exception e) { string iStr = e.ToString(); } } } try { performContext.WriteLine("*** Importing " + xmlFileName + " is Done ***"); ImportUtility.AddImportMap_For_Progress(dbContext, oldTable_Progress, BCBidImport.sigId.ToString(), BCBidImport.sigId); int iResult = dbContext.SaveChangesForImport(); } catch (Exception e) { string iStr = e.ToString(); } } catch (Exception e) { performContext.WriteLine("*** ERROR ***"); performContext.WriteLine(e.ToString()); } }
/// <summary> /// Create a service and set the database context /// </summary> public PermissionService(DbAppContext context) { _context = context; }
/// <summary> /// Map data /// </summary> /// <param name="dbContext"></param> /// <param name="oldObject"></param> /// <param name="systemId"></param> private static void CopyToInstance(DbAppContext dbContext, DumpTruck oldObject, string systemId) { try { if (oldObject.Equip_Id <= 0) { return; } // dump truck records update the equipment record // find the original equiopment record string tempId = oldObject.Equip_Id.ToString(); ImportMap map = dbContext.ImportMaps.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 // ************************************************ Equipment equipment = dbContext.Equipments.FirstOrDefault(x => x.Id == map.NewKey); if (equipment == null) { throw new ArgumentException(string.Format("Cannot locate Equipment record (DumpTruck Equip Id: {0}", tempId)); } // 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.Equipments.Update(equipment); // ************************************************ // get the equipment type record and update // ************************************************ int?tempEquipTypeId = equipment.DistrictEquipmentTypeId; if (tempEquipTypeId == null) { return; } EquipmentType equipmentType = dbContext.EquipmentTypes.FirstOrDefault(x => x.Id == tempEquipTypeId); if (equipmentType != null && !equipmentType.IsDumpTruck) { equipmentType.IsDumpTruck = true; equipmentType.AppLastUpdateUserid = systemId; equipmentType.AppLastUpdateTimestamp = DateTime.UtcNow; dbContext.EquipmentTypes.Update(equipmentType); } } catch (Exception ex) { Debug.WriteLine("***Error*** - (Old) Equipment Id: " + oldObject.Equip_Id); Debug.WriteLine(ex.Message); throw; } }