public override DbImportResult Import(bool canCreateProperties = false) { DbImportResult = new DbImportResult(); if (MetaData.ImportType != CommonUtils.ImportType.CreateIssue && MetaData.ImportType != CommonUtils.ImportType.UpdateIssue) { DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE); return DbImportResult; } CanCreateProperties = false; mExistingStatuses = Cee.IssueStatuses.ToList(); mExistingCategories = Cee.IssueCategories.ToList(); mExistingIssueTypes = Cee.IssueTypes.ToList(); mExistingPriorities = Cee.IssuePriorities.ToList(); mExistingClassifications = Cee.IssueClassifications.ToList(); mExistingIssueSubTypes = Cee.IssueSubTypes.ToList(); mExistingIssueApprovals = Cee.IssueApprovals.ToList(); mExistingIssueCloseouts = Cee.IssueCloseouts.ToList(); mExistingIssueAssignedCategories = Cee.IssueAssignedCategories.ToList(); string connString = BuildConnectionString(MetaData.FullFileName); IList<IssueDataAdapter> importData = new List<IssueDataAdapter>(); using (OleDbConnection excelConn = new OleDbConnection(connString)) { try { using (OleDbCommand cmd = new OleDbCommand()) { cmd.CommandTimeout = 600; cmd.Connection = excelConn; if (MetaData.ImportType == CommonUtils.ImportType.CreateIssue) { cmd.CommandText = IssueCommandText; } else { cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, IssueColumn.ID.ToString()); } excelConn.Open(); if (!WorkSheetCheckColumnNamesAreValid<IssueColumn>(GetColumnHeadersFromDataSet(cmd, (int)IssueColumn.Response))) { DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage()); return DbImportResult; } int k = 1; using (OleDbDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { k++; if (k < 2)// rows 2 and 3 are not data..row 1 is our hidden headings. { continue; } IssueDataAdapter adapter = new IssueDataAdapter(dr, WorkSheetName, k); importData.Add(adapter); } excelConn.Close(); } } if (MetaData.ImportType == CommonUtils.ImportType.CreateIssue) { InsertData(importData); } else { UpdateData(importData); } DbImportResult.ImportedCount = mSavedResults.Count; return DbImportResult; } catch (OleDbException ex) { DbImportResult.ErrorMessages.Add(ex.ToString()); return DbImportResult; } finally { if (excelConn.State == ConnectionState.Open) { excelConn.Close(); } } } }
private bool BuildAndValidateIssue(IssueDataAdapter adapter, int i, Issue issue) { //NOT NULLs... issue.ModifiedDate = DateTime.Now; issue.ModifiedById = MetaData.UserId; issue.IsActive = true; //InitiationDate DateTime dt; if (!string.IsNullOrEmpty(adapter.InitiationDate) && DateTime.TryParse(adapter.InitiationDate, out dt)) { if (dt.Date > DateTime.Now.Date) { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildDateCannotBeInFutureInExcelMessage("InitiatedDate", adapter.InitiationDate, adapter.RowNumber))); return true; } issue.InitiatedDate = dt; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotInValidFormatInExcelMessage("InitiatedDate", adapter.InitiationDate, adapter.RowNumber))); return true; } //InitiatedBy if (!string.IsNullOrEmpty(adapter.InitiatedBy)) { User user = GetUserFromText(adapter.InitiatedBy); if (user != null) { issue.InitiatedById = user.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("InitiatedBy", adapter.InitiatedBy, adapter.RowNumber))); return true; } } //ProjectSupervisor if (!string.IsNullOrEmpty(adapter.ProjectSupervisor)) { User user = GetUserFromText(adapter.ProjectSupervisor); if (user != null) { issue.ProjectSupervisorId = user.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("ProjectSupervisor", adapter.ProjectSupervisor, adapter.RowNumber))); return true; } } //AssignedTo (OriginallyAssignedToId is set to the same) if (!string.IsNullOrEmpty(adapter.AssignedTo)) { User user = GetUserFromText(adapter.AssignedTo); if (user != null) { issue.CurrentlyAssignedToId = user.Id; issue.OriginallyAssignedToId = user.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("CurrentlyAssignedTo", adapter.AssignedTo, adapter.RowNumber))); return true; } } //IssueType IssueType issueType = (from x in mExistingIssueTypes where x.Name.Equals(adapter.IssueType, StringComparison.CurrentCultureIgnoreCase) select x).FirstOrDefault(); if (issueType != null) { issue.IssueTypeId = issueType.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("IssueType", adapter.IssueType, adapter.RowNumber))); return true; } //IssueSUBType IssueSubType issueSubType = (from x in mExistingIssueSubTypes where x.Name.Equals(adapter.IssueSubType, StringComparison.CurrentCultureIgnoreCase) select x).FirstOrDefault(); if (issueSubType != null) { issue.IssueSubTypeId = issueSubType.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("IssueSubType", adapter.IssueType, adapter.RowNumber))); return true; } //IssueClassification if (!string.IsNullOrEmpty(adapter.Classification)) { IssueClassification classification = (from x in mExistingClassifications where String.Compare(x.Name, adapter.Classification, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault(); if (classification != null) { issue.IssueClassificationId = classification.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Classification", adapter.Classification, adapter.RowNumber))); return true; } } //IssueCategory if (!string.IsNullOrEmpty(adapter.Categories)) { string[] categories = adapter.Categories.Split(new[] { ',' }); foreach (string adapterCategoryName in categories) { IssueCategory issueCategory = (from x in mExistingCategories where String.Compare(x.Name, adapterCategoryName, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault(); if (issueCategory != null) { issue.IssueAssignedCategories.Add(new IssueAssignedCategory { Issue = issue, IssueCategoryId = issueCategory.Id }); issue.IssueApprovals.Add(new IssueApproval { Issue = issue, IssueCategoryId = issueCategory.Id }); issue.IssueCloseouts.Add(new IssueCloseout { Issue = issue, IssueCategoryId = issueCategory.Id }); } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Category", adapterCategoryName, adapter.RowNumber))); return true; } } } //IssuePriority if (!string.IsNullOrEmpty(adapter.Priority)) { IssuePriority priority = (from x in mExistingPriorities where String.Compare(x.Name, adapter.Priority, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault(); if (priority != null) { issue.IssuePriorityId = priority.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Priority", adapter.Priority, adapter.RowNumber))); return true; } } //IssueStatus if (!string.IsNullOrEmpty(adapter.Status)) { IssueStatus status = (from x in mExistingStatuses where String.Compare(x.Name, adapter.Status, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault(); if (status != null) { issue.IssueStatusId = status.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Status", adapter.Status, adapter.RowNumber))); return true; } } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Status", adapter.Status, adapter.RowNumber))); return true; } //Areas if (!string.IsNullOrEmpty(adapter.AreaNumbers)) { string[] areas = adapter.AreaNumbers.Split(new[] { ',' }); foreach (string number in areas) { int areaNumber = -1; if (int.TryParse(number, out areaNumber)) { Area area = (from x in Cee.Areas where x.AreaNumber == areaNumber select x).FirstOrDefault(); if (area != null) { issue.IssueAreas.Add(new IssueArea {Issue = issue, AreaId = area.Id}); } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Areas", adapter.AreaNumbers, adapter.RowNumber))); return true; } } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("Areas", number, adapter.RowNumber))); } } } //Budgeted bool result; if (Boolean.TryParse(adapter.Budgeted, out result)) { issue.IssueTracking.Budgeted = result; } else { if (String.Compare(adapter.Budgeted, "Yes", true, CultureInfo.CurrentCulture) == 0) { issue.IssueTracking.Budgeted = true; } else { issue.IssueTracking.Budgeted = false; } } //EstimatedCost if (!string.IsNullOrEmpty(adapter.EstimatedCost)) { double dr; if (Double.TryParse(adapter.EstimatedCost, out dr)) { issue.IssueTracking.EstimatedCost = dr; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotInValidFormatInExcelMessage("EstimatedCost", adapter.EstimatedCost, adapter.RowNumber))); return true; } } //Effort if (!string.IsNullOrEmpty(adapter.Effort)) { int effort; if (Int32.TryParse(adapter.Effort, out effort)) { issue.IssueTracking.Effort = effort; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotInValidFormatInExcelMessage("Effort", adapter.Effort, adapter.RowNumber))); return true; } } //Reward if (!string.IsNullOrEmpty(adapter.Reward)) { int reward; if (Int32.TryParse(adapter.Reward, out reward)) { issue.IssueTracking.Return = reward; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotInValidFormatInExcelMessage("Reward", adapter.Reward, adapter.RowNumber))); return true; } } //ScopeUser if (!string.IsNullOrEmpty(adapter.ScopeUser)) { User user = GetUserFromText(adapter.ScopeUser); if (user != null) { issue.IssueTracking.ScopedUserId = user.Id; } else { RaiseMessage(CommonUtils.MessageType.Error, String.Format(BuildItemNotFoundInDatabaseMessage("ScopeUser", adapter.ScopeUser, adapter.RowNumber))); return true; } } //Response if (!string.IsNullOrEmpty(adapter.RevisionHistory)) { IssueResponse ir = new IssueResponse { AssignedToId = issue.CurrentlyAssignedToId, UserId = MetaData.UserId, Date = DateTime.Now, ResponseText = adapter.RevisionHistory, PriorityId = issue.IssuePriorityId, StatusId = issue.IssueStatusId, Issue = issue }; issue.IssueResponses.Add(ir); } return false; }