private void SetUpCleanUp()
{
mMetaData.ResultMessage = string.Empty;
mMetaData.RevisionHistoryComment = string.Empty;
mFileTransferErrors = new List<string>();
resultTabBlock.Text = string.Format("{0} ({1})", RESULTS, 0);
errorTabBlock.Text = string.Format("{0} ({1})", ERRORS, 0);
warningTabBlock.Text = string.Format("{0} ({1})", WARNINGS, 0);
ResultTabText = RESULTS;
ErrorTabText = ERRORS;
WarningTabText = WARNINGS;
ResultTextBox.Text = string.Empty;
ErrorsTextBox.Text = string.Empty;
WarningsTextBox.Text = string.Empty;
mGlobalImportResult = new DbImportResult
{
ErrorMessages = new List<string>(),
WarningMessages = new List<string>(),
InformationMessages = new List<string>(),
DocumentVersions = new List<DocumentVersion>(),
ImportedCount = 0
};
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateControlsSystems && MetaData.ImportType != CommonUtils.ImportType.UpdateControlSystems)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
mExistingControlSystemTypes = Cee.ControlSystemTypes.ToList();
mExistingGraphics = Cee.Graphics.ToList();
mExistingEquipmentModules = Cee.EquipmentModules.ToList();
mExistingUnits = Cee.Units.ToList();
ExistingTinyDocuments = (from x in Cee.Documents select new TinyDocument { Id = x.Id, Name = x.Name, DocumentTypeId = x.DocumentTypeId }).ToList();
IList<ControlDataAdapter> importData = new List<ControlDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, ControlColumn.ControlSystemName); ;
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<ControlColumn>(GetColumnHeadersFromDataSet(cmd, (int)ControlColumn.Notes)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
try
{
var adapter = new ControlDataAdapter(dr);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("ControlDataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateControlsSystems)
{
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();
}
}
}
}
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateDocumentLocations)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
string connString = BuildConnectionString(MetaData.FullFileName);
IList<DocumentLocationDataAdapter> importData = new List<DocumentLocationDataAdapter>();
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, DocumentLocationColumn.DocumentName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<DocumentLocationColumn>(GetColumnHeadersFromDataSet(cmd, (int) DocumentLocationColumn.LocationName)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
List<DocumentLocation> documentLocations;
List<Document> documents;
using (var cee = new CmsEntities())
{
documentLocations = (from x in cee.DocumentLocations select x).ToList();
documents = (from x in cee.Documents select x).ToList();
}
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
//READ ROWS
while (dr.Read())
{
rowNumber++;
var adapter = new DocumentLocationDataAdapter(dr, rowNumber, documentLocations, documents);
if (adapter.IsValid())
{
importData.Add(adapter);
}
else
{
foreach (string message in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, message);
}
}
}
}
excelConn.Close();
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateDocumentLocations)
{
InsertData(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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateDocument && MetaData.ImportType != CommonUtils.ImportType.UpdateDocument)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
//Load Lookups into Memory.
mExistingDocumentTypes = Cee.DocumentTypes.ToList();
mExistingAuthors = Cee.DocumentAuthors.ToList();
mExistingDisciplines = Cee.DocumentDisciplines.ToList();
CanCreateProperties = canCreateProperties;
string connString = BuildConnectionString(MetaData.FullFileName);
IList<DocumentDataAdapter> importData = new List<DocumentDataAdapter>();
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 6000;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, DocumentColumn.Name);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<DocumentColumn>(GetColumnHeadersFromDataSet(cmd, (int)DocumentColumn.IsActive)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
rowNumber++;
var adapter = new DocumentDataAdapter(dr, rowNumber, CommonUtils.ImportType.UpdateDocument);
importData.Add(adapter);
}
}
excelConn.Close();
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateDocument)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateDocument)
{
UpdatetData(importData);
}
else
{
throw new InvalidDataException(string.Format("Invalid import type of '{0}'. Import aborted.", MetaData.ImportType));
}
DbImportResult.ImportedCount = mSavedResults.Count;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateElectrical && MetaData.ImportType != CommonUtils.ImportType.UpdateElectrical)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = canCreateProperties;
mExistingEquipmentTypes = (from x in Cee.ElectricalEquipmentTypes orderby x.Name select x).ToList();
ExistingTinyDocuments = (from x in Cee.Documents select new TinyDocument { Id = x.Id, Name = x.Name, DocumentTypeId = x.DocumentTypeId }).ToList();
IList<ElectricalDataAdapter> importData = new List<ElectricalDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
try
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = TagCommandText;
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<EquipmentColumn>(GetColumnHeadersFromDataSet(cmd, (int)EquipmentColumn.IsActive)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
ElectricalDataAdapter adapter = new ElectricalDataAdapter(dr, WorkSheetName, k, MetaData.ImportType);
importData.Add(adapter);
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateElectrical)
{
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateCalibrationComponent && MetaData.ImportType != CommonUtils.ImportType.UpdateCalibrationComponent)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
LoadCachedData();
CanCreateProperties = canCreateProperties;
//Include Headers so we can match up the component properties (HDR=YES).
string connString = BuildConnectionString(MetaData.FullFileName);
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
IList<CalibrationComponentDataAdapter> importData = new List<CalibrationComponentDataAdapter>();
try
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = CalibrationCommandText;
if (excelConn.State != ConnectionState.Open)
{
excelConn.Open();
}
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd, (int)CalibrationColumn.Notes + 1);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
if (!WorkSheetCheckColumnNamesAreValid<CalibrationColumn>(GetColumnHeadersFromDataSet(cmd, (int)CalibrationColumn.Notes)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
using (OleDbDataReader dr = cmd.ExecuteReader())
{
int rowNumber = 1;
while (dr.Read())
{
rowNumber++;
CalibrationComponentDataAdapter adapter = new CalibrationComponentDataAdapter(dr, mExistingProperties, spreadSheetPropertyNames,
rowNumber, CanCreateProperties, MetaData.ImportType);
if (!adapter.IsValid())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
excelConn.Close();
}
}
if (importData.Count == 0)
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
return DbImportResult;
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateCalibrationComponent)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateCalibrationComponent)
{
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();
}
}
}
}
public DbImportResult ImportDocumentVersions(ImportFileMetaData metaData, byte[] content, bool append)
{
try
{
//EXCEL DOC
string targetFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "temp_imports");
if (!Directory.Exists(targetFolder))
{
Directory.CreateDirectory(targetFolder);
}
WriteFileToDisk(metaData.FileName, content, targetFolder, false, targetFolder);
//we now have the file saved on the server's disk so we can process safely...
string fullPath = Path.Combine(targetFolder, targetFolder, metaData.FileName);
metaData.FullFileName = fullPath;
IImporter importer = ImporterFactory.CreateImporter(metaData);
DbImportResult dbImportResult = new DbImportResult();
importer.MessageRaised += (m, s) =>
{
switch (m)
{
case CommonUtils.MessageType.Error:
dbImportResult.ErrorMessages.Add(s);
break;
case CommonUtils.MessageType.Warning:
dbImportResult.WarningMessages.Add(s);
break;
case CommonUtils.MessageType.AlreadyExists:
Logger.Info(string.Format("added filename '{0}' to AlreadyExistsFileNames list.", s));
dbImportResult.AlreadyExistsFileNames.Add(s);
break;
case CommonUtils.MessageType.Added:
case CommonUtils.MessageType.Info:
dbImportResult.InformationMessages.Add(s);
break;
}
};
DbImportResult finalResult = new DbImportResult();
finalResult = importer.Import(false);
// set this property last as we may have had a new result object newed up inside an importer.
if (dbImportResult.ErrorMessages != null && dbImportResult.ErrorMessages.Any())
{
finalResult.ErrorMessages.AddRange(dbImportResult.ErrorMessages);
}
if (dbImportResult.WarningMessages != null && dbImportResult.WarningMessages.Any())
{
finalResult.WarningMessages.AddRange(dbImportResult.WarningMessages);
}
finalResult.WrittenByteArraySize = GetFileSizeWritten(fullPath);
return finalResult;
}
catch (Exception ex)
{
Logger.Error("", ex, "CMS.Boddington.ImporterPlugin.PluginImport");
DbImportResult errorResult = new DbImportResult();
errorResult.ErrorMessages.Add("Unhandled Exceptions have occured as follows:");
errorResult.ErrorMessages.Add("Message : " + ex.Message);
errorResult.ErrorMessages.Add("Stack : " + ex);
if (ex.InnerException != null)
{
errorResult.ErrorMessages.Add("Message : " + ex.InnerException.Message);
errorResult.ErrorMessages.Add("Stack : " + ex.InnerException);
}
return errorResult;
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateTuningProperties && MetaData.ImportType != CommonUtils.ImportType.UpdateTuningProperties)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
mUser = (from x in Cee.Users where x.Id == MetaData.UserId select x).FirstOrDefault();
if (mUser == null)
{
DbImportResult.ErrorMessages.Add(string.Format(BuildItemNotFoundInDatabaseMessage("UserId", MetaData.UserId.ToString())));
return DbImportResult;
}
IList<TuningPropertiesDataAdapter> importData = new List<TuningPropertiesDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, TuningPropertyColumn.ControlSystemName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<TuningPropertyColumn>(GetColumnHeadersFromDataSet(cmd, (int)TuningPropertyColumn.ComponentName)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
const int STARTCOLUMN = (int)TuningPropertyColumn.ComponentName;
List<string> dynamicProperyNames = GetDynamicProperties(cmd, STARTCOLUMN);
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
try
{
var adapter = new TuningPropertiesDataAdapter(dr, dynamicProperyNames, STARTCOLUMN + 1);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("EngineeringPropertyDataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateTuningProperties)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateTuningProperties)
{
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateInterlockRisks && MetaData.ImportType != CommonUtils.ImportType.UpdateInterlockRisks)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
mExistingInterlockRiskCategorys = Cee.InterlockRiskCategories.ToList();
mExistInterlockTypes = Cee.InterlockTypes.ToList();
mUser = (from x in Cee.Users where x.Id == MetaData.UserId select x).FirstOrDefault();
if (mUser == null)
{
DbImportResult.ErrorMessages.Add(string.Format(BuildItemNotFoundInDatabaseMessage("UserId", MetaData.UserId.ToString(CultureInfo.CurrentCulture))));
return DbImportResult;
}
IList<InterlockRiskDataAdapter> importData = new List<InterlockRiskDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, InterlockRiskColumn.ControlSystemName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<InterlockRiskColumn>(GetColumnHeadersFromDataSet(cmd, (int) InterlockRiskColumn.Description)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
try
{
var adapter = new InterlockRiskDataAdapter(dr);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("InterlockDataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateInterlockRisks)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateInterlockRisks)
{
UpdateData(importData);
}
DbImportResult.ImportedCount = mOutPutResults.Count;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
public DbImportResult PluginImport(ImportFileMetaData metaData, byte[] content, bool canCreateProperties)
{
string targetFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "temp_imports");
if (!Directory.Exists(targetFolder))
{
Directory.CreateDirectory(targetFolder);
}
string path = Guid.NewGuid().ToString();
try
{
WriteFileToDisk(metaData.FileName, content, path, false, targetFolder);
//we now have the file saved on the server's disk so we can process safely...
string fullPath = Path.Combine(targetFolder, path, metaData.FileName);
metaData.FullFileName = fullPath;
IImporter importer = ImporterFactory.CreateImporter(metaData);
DbImportResult raisedMessages = new DbImportResult();
importer.MessageRaised += (m, s) =>
{
switch (m)
{
case CommonUtils.MessageType.Error:
raisedMessages.ErrorMessages.Add(s);
break;
case CommonUtils.MessageType.Warning:
raisedMessages.WarningMessages.Add(s);
break;
case CommonUtils.MessageType.AlreadyExists:
Logger.Info(string.Format("added filename '{0}' to AlreadyExistsFileNames list.", s));
raisedMessages.AlreadyExistsFileNames.Add(s);
break;
case CommonUtils.MessageType.Added:
case CommonUtils.MessageType.Info:
raisedMessages.InformationMessages.Add(s);
break;
}
};
//IMPORT STARTS HERE----------------------------
DbImportResult dbImportResult = importer.Import(canCreateProperties);
//----------------------------------------------
if (raisedMessages.WarningMessages.Count > 0)
{
dbImportResult.WarningMessages.InsertRange(0, raisedMessages.WarningMessages);
}
if (raisedMessages.InformationMessages.Count > 0)
{
dbImportResult.InformationMessages.InsertRange(0, raisedMessages.InformationMessages);
}
if (raisedMessages.ErrorMessages.Count > 0)
{
dbImportResult.ErrorMessages.InsertRange(0, raisedMessages.ErrorMessages);
}
if (raisedMessages.AlreadyExistsFileNames.Count > 0)
{
Logger.Info(string.Format("Adding {0} AlreadyExistsFileNames", raisedMessages.AlreadyExistsFileNames.Count));
dbImportResult.AlreadyExistsFileNames.InsertRange(0, raisedMessages.AlreadyExistsFileNames);
}
dbImportResult.WrittenByteArraySize = GetFileSizeWritten(fullPath);
return dbImportResult;
}
catch (Exception ex)
{
Logger.Error("", ex, "CMS.Boddington.ImporterPlugin.PluginImport");
DbImportResult errorResult = new DbImportResult();
errorResult.ErrorMessages.Add("Unhandled Exceptions have occured as follows:");
errorResult.ErrorMessages.Add("Message : " + ex.Message);
errorResult.ErrorMessages.Add("Stack : " + ex);
if (ex.InnerException != null)
{
errorResult.ErrorMessages.Add("Message : " + ex.InnerException.Message);
errorResult.ErrorMessages.Add("Stack : " + ex.InnerException);
}
return errorResult;
}
finally
{
//we can clean-up
if (Directory.Exists(Path.Combine(targetFolder, path)))
{
Directory.Delete(Path.Combine(targetFolder, path), true);
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.UpdateAlarms)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
mExistingResponses = (from x in Cee.ControlSystemAlarmResponses select x).ToList();
mExistingPurposes = (from x in Cee.ControlSystemAlarmPurposes select x).ToList();
mExistingConsequences = (from x in Cee.ControlSystemAlarmConsequences select x).ToList();
mExistingPriorities = (from x in Cee.ControlSystemAlarmPriorities select x).ToList();
IList<AlarmDataAdapter> importData = new List<AlarmDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, AlarmColumn.ControlSystemName); ;
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<AlarmColumn>(GetColumnHeadersFromDataSet(cmd, (int)AlarmColumn.TestedUser)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
var adapter = new AlarmDataAdapter(dr, WorkSheetName, k, MetaData.ImportType);
importData.Add(adapter);
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.UpdateAlarms)
{
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreatePipe && MetaData.ImportType != CommonUtils.ImportType.UpdatePipe)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
string connString = BuildConnectionString(MetaData.FullFileName);
IList<PipeDataAdapter> importData = new List<PipeDataAdapter>();
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = PipeCommandText;
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<PipeColumn>(GetColumnHeadersFromDataSet(cmd, (int) PipeColumn.IsActive)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
var pipeDataAdapter = new PipeDataAdapter(dr, WorkSheetName, k);
importData.Add(pipeDataAdapter);
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreatePipe)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdatePipe)
{
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.Cji3)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
IList<Cji3DataAdapter> importData = new List<Cji3DataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
excelConn.Open();
DataTable dbSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
cmd.CommandText = string.Format(@"SELECT * FROM [{0}] where [Period] IS NOT NULL", firstSheetName);
if (!WorkSheetCheckColumnNamesAreValid<Cij3Column>(GetColumnHeadersFromDataSet(cmd,
(int)Enum.GetValues(typeof(Cij3Column)).Cast<Cij3Column>().Max())))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr != null && dr.Read())
{
rowNumber++;
try
{
var adapter = new Cji3DataAdapter(dr, rowNumber);
if (adapter.IsValid())
{
if (ContainsDuplicates(importData, adapter))
{
//string message = String.Format("Duplicate Po Number '{0}', Project Definition '{1} an Reference Item '{2}''. Will SKIPP this row.",
// adapter.PoNumber, adapter.ProjectDefinition, adapter.ReferenceItem);
DbImportResult.ErrorMessages.Add(string.Format("FinancialDataAdapter row {0} - {1}", rowNumber, ""));
}
else
{
importData.Add(adapter);
}
}
else
{
foreach (string message in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, message);
}
}
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("FinancialDataAdapter row {0} - {1}", rowNumber, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.Cji3)
{
//First insert data into FinancialActual table
InsertFinancialActuals(importData);
//Then Insert/Update IssueFinancials
//InsertUpdateData(importData);
//Removed as per https://jira.issgroup.com.au/browse/BODCMS-1625
}
else
{
}
DbImportResult.ImportedCount = mInsertedResults.Count + mUpdatedResults.Count + mInsertedFinancialActualsCount;
return DbImportResult;
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
private void TransferGeneralExcelFiles(ImportFileMetaData metaData, FileInfo fileInfo)
{
try
{
Stream stream = fileInfo.OpenRead();
long sentFileSize = fileInfo.Length;
long writtenFileSize = 0;
int transferChunkSize = Utils.GetTransferChunkSize(stream);
if (stream.Position > -1 && stream.Position < stream.Length)
{
int chunkSize = Utils.GetCurrentChunkSize(stream, transferChunkSize);
byte[] fileBytes = new byte[chunkSize];
stream.Read(fileBytes, 0, chunkSize);
CmsWebServiceClient cmsWebServiceClient = new CmsWebServiceClient(Utils.WcfBinding, Utils.WcfEndPoint);
cmsWebServiceClient.UploadGenericImportExcelFileCompleted += (s1, e1) =>
{
if (e1.Result.ErrorMessages.Any())
{
ExcelTransferFailed(e1.Result);
stream.Close();
return;
}
if (stream.Position > -1 && stream.Position < stream.Length)
{
//STILL GOING
chunkSize = Utils.GetCurrentChunkSize(stream, transferChunkSize);
fileBytes = new byte[chunkSize];
stream.Read(fileBytes, 0, chunkSize);
writtenFileSize += e1.Result.WrittenByteArraySize;
cmsWebServiceClient.UploadGenericImportExcelFileAsync(metaData, fileBytes, GetCanCreateProperties());
}
else
{
writtenFileSize += e1.Result.WrittenByteArraySize;
//Transfer complete
stream.Close();
if (writtenFileSize < sentFileSize)
{
DbImportResult r = new DbImportResult();
r.ErrorMessages = new List<string>();
r.ErrorMessages.Add("There was a loss of information while attempting to transfer the Excel file. Please try again. ");
ExcelTransferFailed(r);
}
else
{
ExcelTransferSucceeded(e1.Result);
busyIndicator.IsBusy = false;
}
}
};
cmsWebServiceClient.UploadGenericImportExcelFileAsync(mMetaData, fileBytes, GetCanCreateProperties());
}
}
catch (IOException)
{
ResultsRadTabControl.SelectedIndex = (int)ResultTab.Errors;
ErrorsTextBox.Text = "Could not open the file - perhaps it is already open in another window? If so close it and try again. ";
errorTabBlock.Text = string.Format("{0} ({1})", ERRORS, 1);
ImportButton.IsEnabled = true;
busyIndicator.IsBusy = false;
}
catch (Exception ex)
{
ResultsRadTabControl.SelectedIndex = (int)ResultTab.Errors;
ErrorsTextBox.Text = ex.ToString();
ImportButton.IsEnabled = true;
busyIndicator.IsBusy = false;
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.LinkRelatedEquipmentDocument)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
mExistingDocuments = Cee.Documents.Select(x => new LightDocument {Id = x.Id, Name = x.Name}).ToList();
mExistingDocumentEquipments = Cee.DocumentEquipments.ToList();
mExistingEquipmentTypes = Cee.EquipmentTypes.ToList();
CanCreateProperties = false;
string connString = BuildConnectionString(MetaData.FullFileName);
IList<DocumentEquipmentDataAdapter> importData = new List<DocumentEquipmentDataAdapter>();
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, DocumentEquipmentColumn.DocumentName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<DocumentEquipmentColumn>(GetColumnHeadersFromDataSet(cmd, (int) DocumentEquipmentColumn.Tag)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
rowNumber++;
var adapter = new DocumentEquipmentDataAdapter(dr, rowNumber, MetaData.ImportType);
importData.Add(adapter);
}
}
excelConn.Close();
}
SaveData(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 void TransferSingleAttachmentFile(FileInfo fileInfo, BaseAttachmentDto attachmentDto)
{
long sentFileSize = fileInfo.Length;
long writtenFileSize = 0;
CmsWebServiceClient cmsWebServiceClient = new CmsWebServiceClient(Utils.WcfBinding, Utils.WcfEndPoint);
try
{
Stream stream = fileInfo.OpenRead();
if (stream.Length > int.MaxValue)
{
DbImportResult r = new DbImportResult();
r.ErrorMessages = new List<string>();
r.ErrorMessages.Add(string.Format("File [{0}] can not be bigger than 2GB. ", fileInfo.Name));
FileTransferFailed(r);
return;
}
int transferChunkSize = Utils.GetTransferChunkSize(stream);
CommonUploadFile file = new CommonUploadFile { Name = fileInfo.Name, Size = stream.Length, Path = attachmentDto.Path };
if (stream.Position > -1 && stream.Position < stream.Length)
{
int chunkSize = Utils.GetCurrentChunkSize(stream, transferChunkSize);
byte[] fileBytes = new byte[chunkSize];
stream.Read(fileBytes, 0, chunkSize);
cmsWebServiceClient.UploadFileCompleted +=
(s1, e1) =>
{
if (stream.Position > -1 && stream.Position < stream.Length)
{
chunkSize = Utils.GetCurrentChunkSize(stream, transferChunkSize);
fileBytes = new byte[chunkSize];
stream.Read(fileBytes, 0, chunkSize);
writtenFileSize += e1.Result.EntityResult.WrittenByteArraySize;
cmsWebServiceClient.UploadFileAsync(file.Name, fileBytes, CMS.AppSetting.AttachmentsRootUrl, file.Path, true);
}
else
{
//Close the stream when all files are uploaded
stream.Close();
writtenFileSize += e1.Result.EntityResult.WrittenByteArraySize;
if (writtenFileSize < sentFileSize)
{
DbImportResult r = new DbImportResult();
r.ErrorMessages = new List<string>();
r.ErrorMessages.Add("There was a loss of information while attempting to transfer the file. Please try again. ");
FileTransferFailed(r);
return;
}
BeginAttachmentInsert(attachmentDto);
}
};
cmsWebServiceClient.UploadFileAsync(file.Name, fileBytes, CMS.AppSetting.AttachmentsRootUrl, file.Path, false);
}
}
catch (Exception ex)
{
mGlobalImportResult.ErrorMessages.Add(ex.Message);
}
}
private void ImportDocuments(BackgroundWorker worker)
{
try
{
if (mExcelFileInfo == null)
{
return;
}
mMetaData.RevisionHistoryComment = txtComment.Text;
mMetaData.ImportType = CommonUtils.ImportType.CreateDocumentVersionStep1;
mMetaData.FullFileName = txtExcelFileName.Text;
DirectoryInfo dirInfo = new DirectoryInfo(folderBrowserDialog.SelectedPath);
FileInfo[] files = dirInfo.GetFiles();
int fileCount = files.Length;
if (fileCount == 0)
{
worker.ReportProgress(0, string.Format("No Files found in path: {0}", folderBrowserDialog.SelectedPath));
worker.CancelAsync();
return;
}
DocumentVersionImporter importer = new DocumentVersionImporter(mMetaData);
DbImportResult raisedMessages = new DbImportResult();
importer.MessageRaised += (m, s) =>
{
switch (m)
{
case CommonUtils.MessageType.Error:
raisedMessages.ErrorMessages.Add(s);
break;
case CommonUtils.MessageType.Warning:
raisedMessages.WarningMessages.Add(s);
break;
case CommonUtils.MessageType.Added:
case CommonUtils.MessageType.Info:
raisedMessages.InformationMessages.Add(s);
break;
case CommonUtils.MessageType.AlreadyExists:
raisedMessages.AlreadyExistsFileNames.Add(s);
break;
}
};
DbImportResult result = importer.Import(false);
worker.ReportProgress(0 / fileCount, string.Format("About to build insert candidates for Document Version records (Step 1) using {0} files.", fileCount));
//CHECK FOR CANCELLED
if (mWorker.CancellationPending)
{
AddMessageToGrid("Cancelled!", Common.ImporterMessageType.Info, true);
return;
}
if (result.HasErrors)
{
foreach (string errorMessage in result.ErrorMessages)
{
worker.ReportProgress(0 / result.DocumentVersions.Count, new FileState { Message = errorMessage, Type = Common.ImporterMessageType.Error });
}
}
if (result.WarningMessages != null && result.WarningMessages.Any())
{
foreach (string warningMessage in result.WarningMessages)
{
worker.ReportProgress(0 / result.DocumentVersions.Count, new FileState { Message = warningMessage, Type = Common.ImporterMessageType.Warning });
}
}
if (result.InformationMessages != null && result.InformationMessages.Any())
{
foreach (string message in result.InformationMessages)
{
worker.ReportProgress(0 / result.DocumentVersions.Count, new FileState { Message = message, Type = Common.ImporterMessageType.Info });
}
}
List<DocumentVersion> canidateVersions = result.DocumentVersions;
foreach (var raisedMessage in raisedMessages.ErrorMessages)
{
worker.ReportProgress(0, new FileState { Message = raisedMessage, Type = Common.ImporterMessageType.Error });
}
foreach (var raisedMessage in raisedMessages.WarningMessages)
{
worker.ReportProgress(0, new FileState { Message = raisedMessage, Type = Common.ImporterMessageType.Warning });
}
foreach (var raisedMessage in raisedMessages.InformationMessages)
{
worker.ReportProgress(0, new FileState { Message = raisedMessage, Type = Common.ImporterMessageType.Info });
}
foreach (var fileName in raisedMessages.AlreadyExistsFileNames)
{
mAlreadyExistsFileNames.Add(fileName);
}
worker.ReportProgress(0, string.Format("Completed building {0}/{1} Document Version insert candidates (Step 1).", canidateVersions.Count, fileCount));
string settingValue = CommonUtils.GetAppSettingValue(CommonUtils.AppSettingKey.UploadedDocumentsPathRoot);
worker.ReportProgress(0, string.Format("The actual documents will be uploaded to the directory path: '{0}'.", settingValue));
DoInsert(worker, canidateVersions, settingValue, files);
}
catch (Exception ex)
{
worker.ReportProgress(0, string.Format("Errors = {0}", ex));
if (ex.InnerException != null)
{
worker.ReportProgress(0, string.Format("Iner Exception: Errors = {0}", ex));
}
worker.CancelAsync();
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
base.WorkSheetName = ImportWorkSheetName.Instruments.ToString();
if (MetaData.ImportType != CommonUtils.ImportType.CreateInstrument && MetaData.ImportType != CommonUtils.ImportType.UpdateInstrument)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = canCreateProperties;
mExistingInstrumentTypes = Cee.InstrumentTypes.ToList();
mExistingVerificationFrequencies = Cee.VerificationFrequencies.ToList();
Stopwatch sw = new Stopwatch();
sw.Start();
ExistingTinyDocuments = (from x in Cee.Documents select new TinyDocument { Id = x.Id, Name = x.Name, DocumentTypeId = x.DocumentTypeId }).ToList();
sw.Stop();
Debug.WriteLine("It took {0} seconds to get all documents", sw.Elapsed.TotalSeconds);
string connString = BuildConnectionString(MetaData.FullFileName);
IList<InstrumentDataAdapter> importData = new List<InstrumentDataAdapter>();
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
try
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = TagCommandText;
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<InstrumentColumn>(GetColumnHeadersFromDataSet(cmd, (int)InstrumentColumn.IsActive)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
InstrumentDataAdapter adapter = new InstrumentDataAdapter(dr, WorkSheetName, k, MetaData.ImportType);
importData.Add(adapter);
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateInstrument)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateInstrument)
{
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();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
try
{
if (MetaData.ImportType == CommonUtils.ImportType.CreateDocumentVersionStep1 || MetaData.ImportType == CommonUtils.ImportType.UpdateDocumentVersion)
{
string connString = BuildConnectionString(MetaData.FullFileName);
log.Debug("excel connString: " + connString);
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
try
{
IList<DocumentVersionDataAdapter> importData = new List<DocumentVersionDataAdapter>();
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, DocumentVersionColumn.DocumentName.ToString());
excelConn.Open();
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
log.Debug(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
log.Debug("spread sheet opened ok.");
if (!WorkSheetCheckColumnNamesAreValid<DocumentVersionColumn>(GetColumnHeadersFromDataSet(cmd, (int)DocumentVersionColumn.Native)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
log.Debug("ColumnNamesAreValid");
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
log.Debug("about to open datareader");
while (dr.Read())
{
rowNumber++;
try
{
DocumentVersionDataAdapter adapter = new DocumentVersionDataAdapter(dr, rowNumber, MetaData);
if (adapter.ErrorMessages.Any())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
DbImportResult.ErrorMessages.Add(errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
catch (FormatException ex)
{
log.Debug(string.Format("Error converting from data reader on row number {0}.", rowNumber));
DbImportResult.ErrorMessages.Add(ex.Message);
continue;
}
}
excelConn.Close();
}
}
log.Debug(string.Format("After Reading Excel we have {0} canidate importData records.", importData.Count.ToString()));
DbImportResult.DocumentVersions = BuildStep1Result(importData);
if (MetaData.ImportType == CommonUtils.ImportType.UpdateDocumentVersion)
{
Update(DbImportResult.DocumentVersions);
DbImportResult.ImportedCount = mImportCount;
return DbImportResult;
}
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.Message);
log.Error("", ex, "Import");
if (ex.InnerException != null)
{
DbImportResult.ErrorMessages.Add(ex.InnerException.Message);
log.Error("", ex.InnerException, "Import");
}
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
else
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(ex.Message);
log.Error("", ex, "Import");
if (ex.InnerException != null)
{
DbImportResult.ErrorMessages.Add(ex.InnerException.Message);
log.Error("", ex.InnerException, "Import");
}
}
DbImportResult.ImportedCount = mImportCount;
return DbImportResult;
}
public override DbImportResult Import(bool canCreateProperties = false)
{
if (MetaData.ImportType != CommonUtils.ImportType.CreatePipeComponent && MetaData.ImportType != CommonUtils.ImportType.UpdatePipeComponent)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = canCreateProperties;
DbImportResult = new DbImportResult();
mExistingComponents = (from x in Cee.PipeComponents
.Include("PipeComponentType")
select x).ToList();
mExistingComponentTypes = Cee.PipeComponentTypes.ToList();
mExistingComponentProperties = Cee.PipeProperties.ToList();
mExistingEquipmentComponentTypeProperty = (from x in Cee.PipeComponentTypeProperties.Include("PipeComponentType") select x).ToList();
mExistingPropertyValues = Cee.PipePropertyValues.ToList();
mExistingManufacturers = (from x in Cee.Manufacturers.Include("Models") where x.EquipmentTypeId == (int) CommonUtils.EquipmentTypeCode.PIPE select x).ToList();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
IList<PipeComponentDataAdapter> importData = new List<PipeComponentDataAdapter>();
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = PipeComponentCommandText;
if (excelConn.State != ConnectionState.Open)
{
excelConn.Open();
}
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd, (int) PipeComponentColumn.Model + 1);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
if (!WorkSheetCheckColumnNamesAreValid<PipeComponentColumn>(GetColumnHeadersFromDataSet(cmd, (int) PipeComponentColumn.Model)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
using (OleDbDataReader dr = cmd.ExecuteReader())
{
mEmptyPipeLineNbrCount = 0;
int rowNumber = 1;
while (dr.Read())
{
rowNumber++;
var adapter = new PipeComponentDataAdapter(dr, mExistingComponentProperties, spreadSheetPropertyNames, rowNumber, CanCreateProperties);
if (adapter.HasEmptyPipeComponentError)
{
mEmptyPipeLineNbrCount++;
if (mEmptyPipeLineNbrCount == 3)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format("Worksheet {0}, row {1}. The third blank 'Pipe Name' in a row. Skipping work sheet.", WorkSheetName, rowNumber));
}
}
if (!adapter.IsValid())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
excelConn.Close();
}
}
if (!importData.Any())
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
return DbImportResult;
}
if (MetaData.ImportType == CommonUtils.ImportType.CreatePipeComponent)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdatePipeComponent)
{
UpdateData(importData);
}
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
DbImportResult.ImportedCount = mSavedResults.Count;
return DbImportResult;
}
private void AccumulateResults(DbImportResult importResult)
{
//errors
mGlobalImportResult.ErrorMessages.AddRange(importResult.ErrorMessages);
//warnings
mGlobalImportResult.WarningMessages.AddRange(importResult.WarningMessages);
//info
mGlobalImportResult.InformationMessages.AddRange(importResult.InformationMessages);
//import count
mGlobalImportResult.ImportedCount = importResult.ImportedCount;
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateRelatedDocuments)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
IList<RelatedDocumentDataAdapter> importData = new List<RelatedDocumentDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, CommonColumn.EquipmentName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<CommonColumn>(GetColumnHeadersFromDataSet(cmd, (int)CommonColumn.DocumentName)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
try
{
var adapter = new RelatedDocumentDataAdapter(dr);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("DataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateRelatedDocuments)
{
InsertData(importData);
}
DbImportResult.ImportedCount = mResultMessages.Count;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
private void ProcessAttachmentExcelFileResults(DbImportResult dbImportResult)
{
if (dbImportResult.Attachments != null && dbImportResult.Attachments.Any())
{
foreach (BaseAttachmentDto attachmentDto in dbImportResult.Attachments)
{
mAttachmentStack.Push(attachmentDto);
}
ProcessSingleAttachmentFile(); //gets the chain reaction started..
//busyIndicator.IsBusy = true;
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateMobilePlantComponent && MetaData.ImportType != CommonUtils.ImportType.UpdateMobilePlantComponent)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
mExistingEquipments = Cee.MobilePlants.ToList();
mExistingComponents = (from x in Cee.MobilePlantComponents
.Include("MobilePlantComponentType")
select x).ToList();
mExistingEquipmentProperties = Cee.MobilePlantProperties.ToList();
mExistingEquipmentComponentTypes = Cee.MobilePlantComponentTypes.ToList();
mExistingEquipmentComponentTypeProperty = (from x in Cee.MobilePlantComponentTypeProperties.Include("MobilePlantComponentType") select x).ToList();
mExistingPropertyValues = Cee.MobilePlantPropertyValues.ToList();
mExistingManufacturers = (from x in Cee.Manufacturers.Include("Models") where x.EquipmentTypeId == (int)CommonUtils.EquipmentTypeCode.MOB select x).ToList();
CanCreateProperties = canCreateProperties;
//Include Headers so we can match up the component properties (HDR=YES).
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
IList<MobilePlantComponentDataAdapter> importData = new List<MobilePlantComponentDataAdapter>();
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = ComponentCommandText;
excelConn.Open();
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
if (!WorkSheetCheckColumnNamesAreValid<BaseComponentColumn>(GetColumnHeadersFromDataSet(cmd, (int)BaseComponentColumn.Model)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
using (OleDbDataReader dr = cmd.ExecuteReader())
{
int rowNumber = 1;
while (dr.Read())
{
rowNumber++;
var adapter = new MobilePlantComponentDataAdapter(dr, mExistingEquipmentProperties,
spreadSheetPropertyNames, rowNumber, canCreateProperties, MetaData.ImportType);
if (!adapter.IsValid())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
excelConn.Close();
}
}
if (importData.Count == 0)
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
}
string operation = "Addded";
if (MetaData.ImportType == CommonUtils.ImportType.CreateMobilePlantComponent)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateMobilePlantComponent)
{
UpdateData(importData);
operation = "Updated";
}
foreach (MobilePlantComponent result in mSavedResults)
{
RaiseMessage(CommonUtils.MessageType.Added, string.Format("{0} Component '{1}' from worksheet {2}.",
operation, result.Name, WorkSheetName));
}
ImportedCount = mSavedResults.Count;
DbImportResult.ImportedCount = ImportedCount;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
private void ProcessDocumentVersionExcelFileResults(DbImportResult dbImportResult)
{
if (dbImportResult.DocumentVersions != null && dbImportResult.DocumentVersions.Any())
{
foreach (DocumentVersion documentVersion in dbImportResult.DocumentVersions)
{
mDocVersionStack.Push(documentVersion);
}
ProcessSingleDocumentVersionFile(); //gets the chain reaction started..
busyIndicator.IsBusy = (mDocVersionStack.Count > 0);
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.Cji5)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
IList<Cji5DataAdapter> importData = new List<Cji5DataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
excelConn.Open();
DataTable dbSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
cmd.CommandText = string.Format(@"SELECT * FROM [{0}] where [Ref Document Number] IS NOT NULL", firstSheetName);
if (!WorkSheetCheckColumnNamesAreValid<PurchaseColumn>(GetColumnHeadersFromDataSet(cmd, (int)PurchaseColumn.ValueInObjCrcy)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int rowNumber = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr != null && dr.Read())
{
rowNumber++;
try
{
var adapter = new Cji5DataAdapter(dr,rowNumber);
if (adapter.IsValid())
{
if (ContainsDuplicates(importData, adapter))
{
string message = String.Format(
"Duplicate Po Number '{0}', Project Definition '{1}', Reference Item '{2}', Reference Doc type '{3}' and WBS Element '{4}' . Will SKIPP this row.",
adapter.PoNumber, adapter.ProjectDefinition, adapter.ReferenceItem, adapter.ReferenceDocType, adapter.WbsElement);
DbImportResult.ErrorMessages.Add(string.Format("FinancialDataAdapter row {0} - {1}", rowNumber, message));
}
else
{
importData.Add(adapter);
}
}
else
{
foreach (string message in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, message);
}
}
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("FinancialDataAdapter row {0} - {1}", rowNumber, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.Cji5)
{
InsertData(importData);
}
else
{
}
DbImportResult.ImportedCount = mSavedResults.Count;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateIssueReferences && MetaData.ImportType != CommonUtils.ImportType.UpdateIssueReferences)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = false;
mExistingSystemReferenceTypes = Cee.SystemReferenceTypes.ToList();
mExistingSystemReferences = Cee.SystemReferences.ToList();
IList<IssueSystemReferencesDataAdapter> importData = new List<IssueSystemReferencesDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, IssueSystemReferencesColumn.IssueId);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<IssueSystemReferencesColumn>(GetColumnHeadersFromDataSet(cmd, (int)IssueSystemReferencesColumn.Funding)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr != null && dr.Read())
{
k++;
try
{
var adapter = new IssueSystemReferencesDataAdapter(dr,k);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("MilestoneDataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateIssueReferences)
{
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();
}
}
}
}