/// <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;
            }
        }
Exemple #2
0
        /// <summary>
        /// Utility function to add a new ImportMap to the database
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="oldTable"></param>
        /// <param name="oldKey"></param>
        /// <param name="newTable"></param>
        /// <param name="newKey"></param>
        public static void AddImportMap(DbAppContext dbContext, string oldTable, string oldKey, string newTable, int newKey)
        {
            HetImportMap importMap = new HetImportMap
            {
                OldTable               = oldTable,
                OldKey                 = oldKey,
                NewTable               = newTable,
                NewKey                 = newKey,
                AppCreateTimestamp     = DateTime.Now,
                AppLastUpdateTimestamp = DateTime.Now
            };

            dbContext.HetImportMap.Add(importMap);
        }
Exemple #3
0
        /// <summary>
        /// Return startPoint:  0 - if ImportMap entry with oldTable == "%%%%%_Progress" does not exist
        ///                     sigId - if ImportMap entry with oldTable == "%%%%%_Progress" exist, and oldKey = signId.toString()
        ///                     Other int - if ImportMap entry with oldTable == "%%%%%_Progress" exist, and oldKey != signId.toString()
        /// The Import_Table entry for this kind of record has NewTable as  BCBidImport.todayDate.
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="oldTableProgress"></param>
        /// <param name="sigId"></param>
        /// <param name="newTable"></param>
        /// <returns></returns>
        public static int CheckInterMapForStartPoint(DbAppContext dbContext, string oldTableProgress, int sigId, string newTable)
        {
            HetImportMap importMap = (
                from u in dbContext.HetImportMap
                where u.OldTable == oldTableProgress &&
                u.NewKey == sigId &&
                u.NewTable == newTable
                orderby int.Parse(u.OldKey) descending
                select u).AsNoTracking()
                                     .FirstOrDefault();

            // OldKey is recorded where the import progress stopped last time
            // when it stores the value of sigId, it signals the completion of the import of the corresponding xml file
            int startPoint = importMap != null?int.Parse(importMap.OldKey) : 0;

            return(startPoint);
        }
        /// <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="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;
            }
        }
Exemple #6
0
        /// <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;
            }
        }
Exemple #7
0
        /// <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;
            }
        }
        /// <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>
        /// 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;
            }
        }
Exemple #10
0
        /// <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;
            }
        }
Exemple #11
0
        /// <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>
        /// 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;
            }
        }
Exemple #13
0
        /// <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;
            }
        }