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