// ----------------------------------------------------------------------------------------
/// <!-- AddTwoValuesTo -->
/// <summary>
/// Adds two column values (and perhaps two columns if needed) to the table
/// </summary>
/// <param name="errorTable"></param>
/// <param name="col1"></param>
/// <param name="col2"></param>
/// <param name="data"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
/// <returns></returns>
public static bool AddTwoValuesTo(DataTable errorTable, DataRow data
, DataColumn col1, DataColumn col2, string value1, string value2, bool isRowInsertedIntoErrorTable, int level)
{
InfoAspect.Measuring("AddTwoValuesTo(7)");
col1.AllowDBNull = true;
col2.AllowDBNull = true;
if ((!errorTable.Columns.Contains(col1.ToString())) && (!errorTable.Columns.Contains(col2.ToString())))
{
errorTable.Columns.Add(col1);
errorTable.Columns.Add(col2);
}
if (isRowInsertedIntoErrorTable == false)
{
errorTable.ImportRow(data);
isRowInsertedIntoErrorTable = true;
}
else
{
// isrowinserted = true;
}
foreach (DataRow dtRow in errorTable.Rows)
{
if (dtRow["FileLineNumber"].ToString() == data["FileLineNumber"].ToString())
{
AddErrorCell(value1, level + 1);
dtRow[col1.ToString()] = value1;
dtRow[col2.ToString()] = value2;
}
}
InfoAspect.Measured("AddTwoValuesTo(7)");
return(isRowInsertedIntoErrorTable);
}
// ----------------------------------------------------------------------------------------
/// <!-- ImportErrorsInsert -->
/// <summary>
/// Inserts all module import errors into the database
/// </summary>
/// <param name="statusErrorLog"></param>
/// <param name="itemIdByLineNum"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static int ImportErrorsInsert(DataTable statusErrorLog, Dictionary <int, int> itemIdByLineNum
, string moduleName, InfoAspect aspect)
{
int errorCount = 0;
try
{
string query = ImportDalCommon.ImportErrorQuery(moduleName);
for (int row = 0; row < statusErrorLog.Rows.Count; ++row)
{
// ------------------------------------------------------------------
// Process the row
// ------------------------------------------------------------------
string statusErrorCode = statusErrorLog.Rows[row]["Status_Error_Code"].ToString();
if (!Regex.IsMatch(statusErrorCode, "0[01]$"))
{
ImportDalCommon.ImportErrorInsert(query, statusErrorLog, row, statusErrorCode
, itemIdByLineNum, moduleName, aspect.Enter(moduleName, "ImportErrorInsert")); aspect--;
errorCount++;
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(errorCount);
}
// ----------------------------------------------------------------------------------------
/// <!-- GetAllImportFilesMatching -->
/// <summary>
/// Returns all the files in each directory matching the pattern and not matching the avoid pattern
/// </summary>
/// <param name="filePattern"></param>
/// <param name="dirList"></param>
/// <param name="avoidPattern">the pattern starting with the beginning of the file name (leave out starting ^ and \\)</param>
/// <returns></returns>
public static List <string> GetAllImportFilesMatching(string filePattern, List <DirectoryInfo> dirList
, string avoidPattern, InfoAspect aspect)
{
List <string> fileName = new List <string>();
try
{
foreach (DirectoryInfo directory in dirList)
{
List <FileInfo> fi = new List <FileInfo>(directory.GetFiles());
List <FileInfo> orderedFileList = fi.OrderBy(x => x.LastWriteTime).ToList();
foreach (FileInfo file in orderedFileList)
{
if (Regex.IsMatch(file.Name, filePattern, RegexOptions.IgnoreCase))
{
if (!Regex.IsMatch(file.Name, "^" + avoidPattern, RegexOptions.IgnoreCase) &&
!Regex.IsMatch(file.Name, @"\\" + avoidPattern, RegexOptions.IgnoreCase))
{
fileName.Add(directory.Name + "\\" + file.Name);
}
}
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(fileName);
}
// ----------------------------------------------------------------------------------------
/// <!-- EndemeAccess_HappyPath_tests -->
/// <summary>
/// Methods used as intended, no null, empty or border tests
/// </summary>
/// <remarks></remarks>
private void EndemeAccess_HappyPath_tests()
{
string toEndemeTable = "Endeme";
Assert.ThingsAbout("EndemeAccess", "HappyPath");
InfoAspect aspect = new InfoAspect("Test", null, "", "", "", 0);
EndemeAccess e = new EndemeAccess();
EndemeSet enSet1 = EndemeTests.WetlandAnimals;
Guid setID1 = Guid.NewGuid();
int didOk1 = e.ToteEndemeSet(enSet1.WithId(setID1), connection, trx);
Guid setID2 = e.GetEndemeSetId(enSet1.Label, connection, trx);
EndemeSet enSet2 = e.OnEndemeSet(setID2, connection, trx);
Endeme en1 = new Endeme(enSet2, "ABC", true);
long endemeId = e.ToEndeme(en1, toEndemeTable, connection, trx);
Endeme en2 = e.GetEndeme(endemeId, toEndemeTable, connection, trx);
Endeme en3 = en2.Copy(); en3.Add("DEF");
int status = e.UpdateEndeme(en3, endemeId, toEndemeTable, aspect);
Assert.That(setID2, Is.equal_to, setID1);
Assert.That(enSet2.Count, Is.equal_to, 22);
Assert.That(enSet2.Label, Is.equal_to, enSet1.Label);
Assert.That(en2.ToString(), Is.equal_to, en1);
Assert.That(en3.ToString(), Is.equal_to, "ABCDEF");
Assert.That(enSet1.SetId, Is.not_equal_to, Guid.Empty);
_result += Assert.Conclusion;
}
// ----------------------------------------------------------------------------------------
/// <!-- ElementExists -->
/// <summary>
/// Looks for element exists on direct id transfer transformations (instructions)
/// </summary>
/// <param name="module"></param>
/// <param name="field"></param>
/// <param name="tableName"></param>
/// <param name="aspect"></param>
/// <returns></returns>
protected bool ElementExists(string tableName, DataRow field, InfoAspect aspect)
{
bool exists = false;
try
{
if (tableName == "Level_1") // "V"
{
TransformField transform = Transform["Level_1_ID"];
SqlInt32 extractPkValue = InData.GetSqlInt32(transform.XrefLookup);
string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--;
exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--;
}
else
{
// ------------------------------------------------------------------
// The normal direct 1 to 1 conversion case
// ------------------------------------------------------------------
string extractPkColumn = ColumnName_ExtractPkFromLoad(tableName, aspect.Enter()); aspect--;
SqlInt32 extractPkValue = InData.GetSqlInt32(field, extractPkColumn);
string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--;
exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--;
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(exists);
}
// ----------------------------------------------------------------------------------------
/// <!-- AddTwoValuesTo -->
/// <summary>
/// Adds two values and possibly two columns to an error table
/// </summary>
/// <param name="errorTable"></param>
/// <param name="data"></param>
/// <param name="column1"></param>
/// <param name="column2"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
/// <returns></returns>
public static bool AddTwoValuesTo(DataTable errorTable, DataRow data
, string column1, string column2, string value1, string value2, bool isRowInsertedIntoErrorTable, int level)
{
InfoAspect.Measuring("AddTwoValuesTo");
DataColumn col1 = new DataColumn(column1, typeof(string));
DataColumn col2 = new DataColumn(column2, typeof(string));
isRowInsertedIntoErrorTable = AddTwoValuesTo(errorTable, data, col1, col2, value1, value2, isRowInsertedIntoErrorTable, level + 1);
InfoAspect.Measured("AddTwoValuesTo");
return(isRowInsertedIntoErrorTable);
}
// ----------------------------------------------------------------------------------------
/// <!-- InitializeTable -->
/// <summary>
/// Initializes the error log table
/// </summary>
/// <param name="importData"></param>
/// <returns></returns>
public static DataTable InitializeStatusErrorStorage(DataTable importData, string tableName, int level)
{
InfoAspect.Measuring("InitializeStatusErrorStorage");
DataTable statusErrorLog = importData.Clone();
statusErrorLog.TableName = "ErrorLog";
statusErrorLog.Columns.Add("Status_Error_Code", typeof(string));
statusErrorLog.Columns.Add("Error_Message", typeof(string));
statusErrorLog.TableName = "StatusErrorLog";
importData.TableName = tableName;
importData.Columns.Add(new DataColumn("IsValid", typeof(bool)));
importData.Columns.Add(new DataColumn("IsUpdate", typeof(bool)));
InfoAspect.Measured("InitializeStatusErrorStorage");
return(statusErrorLog);
}
// ----------------------------------------------------------------------------------------
/// <!-- Import -->
/// <summary>
/// Imports a DataTable of location elements into one of the location tables
/// </summary>
/// <param name="importData"></param>
/// <param name="aspect"></param>
/// <returns></returns>
public DataTable Import(DataTable importData, string tableName, InfoAspect aspect)
{
// --------------------------------------------------------------------------
// Initialize variables
// --------------------------------------------------------------------------
DataTable importedData = ImportDalCommon.InitializeImportedDataTable(importData, tableName + "DbId", aspect.Enter()); aspect--; // the output table
string insertQuery = InsertQuery(tableName, aspect.Enter()); aspect--;
string updateQuery = UpdateQuery(tableName, aspect.Enter()); aspect--;
for (int row = 0; row < importData.Rows.Count; ++row)
{
DataRow field = importData.Rows[row];
try
{
// ------------------------------------------------------------------
// Insert or Update a valid location record in one of four location tables
// ------------------------------------------------------------------
bool isValid = (bool)field["IsValid"];
SqlInt32 id = SqlInt32.Null;
if (isValid)
{
bool isUpdate = (bool)field["IsUpdate"];
if (!isUpdate)
{
isUpdate = ElementExists(tableName, field, aspect.Enter()); aspect--;
}
if (isUpdate)
{
id = Update(tableName, field, importedData, aspect.Enter(tableName, "Update")); aspect--;
}
else
{
id = Insert(insertQuery, tableName, field, importedData, aspect.Enter(tableName, "Insert")); aspect--;
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
}
return(importedData);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Checks for an error in a numeric value field and returns an error code
/// if an error is found
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="lo"></param>
/// <param name="hi"></param>
/// <param name="columnErrorCode"></param>
/// <param name="nullErrorCode">null or blank but required</param>
/// <param name="typeErrorCode">not a number</param>
/// <param name="rangeErrorCode">out of range</param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field, string fieldName, long lo, long hi
, string columnErrorCode, string nullErrorCode, string typeErrorCode, string rangeErrorCode)
{
InfoAspect.Measuring("CheckForError(9)");
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName)) // Check column name existence
{
SqlInt64 num = InData.GetSqlInt64(field, fieldName);
if (num.IsNull) // Check numeric type validity
{
string strScore = field[fieldName].ToString();
if (string.IsNullOrEmpty(strScore))
{
if (required >= 2)
{
errorCode = nullErrorCode;
} // Check required data existence
}
else
{
errorCode = typeErrorCode;
}
}
else
{
if (num < lo || num > hi)
{
errorCode = rangeErrorCode;
}
} // Check data range validity
}
else
{
errorCode = columnErrorCode;
}
InfoAspect.Measured("CheckForError(9)");
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Checks for an error in a numeric code field and returns an error code
/// if there is an error detected, otherwise returns blank
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="options"></param>
/// <param name="columnErrorCode"></param>
/// <param name="nullErrorCode">null or blank but required</param>
/// <param name="typeErrorCode">not a number</param>
/// <param name="optionErrorCode">not in list</param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field, string fieldName, Dictionary <int, int> options
, string columnErrorCode, string nullErrorCode, string typeErrorCode, string optionErrorCode)
{
InfoAspect.Measuring("CheckForError(8a)");
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName)) // Check column name existence
{
SqlInt32 num = InData.GetSqlInt32(field, fieldName);
if (num.IsNull) // Check numeric type validity
{
string strScore = field[fieldName].ToString();
if (string.IsNullOrEmpty(strScore))
{
if (required >= 2)
{
errorCode = nullErrorCode;
} // Check required data existence
}
else
{
errorCode = typeErrorCode;
}
}
else
{
if (!options.ContainsKey((int)num))
{
errorCode = optionErrorCode;
}
} // Check data range validity
}
else
{
errorCode = columnErrorCode;
}
InfoAspect.Measured("CheckForError(8a)");
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Check in database
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="dataTable"></param>
/// <param name="column"></param>
/// <param name="columnErrorCode"></param>
/// <param name="nullErrorCode"></param>
/// <param name="formatErrorCode"></param>
/// <param name="optionErrorCode"></param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field, string fieldName, DataTable dataTable, string column
, string columnErrorCode, string nullErrorCode, string formatErrorCode, string optionErrorCode)
{
InfoAspect.Measuring("CheckForError(10)");
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName)) // Check column name existence
{
SqlString code = InData.GetSqlString(field, fieldName);
if (code.IsNull || string.IsNullOrEmpty(code.ToString().Trim()))
{
if (required >= 2)
{
errorCode = nullErrorCode;
} // Check required data existence
}
else
{
if (Regex.IsMatch(code.ToString(), "^'?[V0-9][0-9][0-9][.]?[0-9]*$", RegexOptions.IgnoreCase)) // Check data format validity
{
if (!InData.ContainsValue(dataTable, column, code))
{
errorCode = optionErrorCode;
}
}
else
{
errorCode = formatErrorCode;
}
}
}
else
{
errorCode = columnErrorCode;
}
InfoAspect.Measured("CheckForError(10)");
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Checks for error on a string field and returns an error code
/// if there is an error detected, otherwise returns blank
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="pattern"></param>
/// <param name="columnErrorCode"></param>
/// <param name="nullErrorCode">null or blank but required</param>
/// <param name="typeErrorCode">not matching pattern</param>
/// <param name="optionErrorCode">unused</param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field, string fieldName, string pattern
, string columnErrorCode, string nullErrorCode, string typeErrorCode, string optionErrorCode)
{
InfoAspect.Measuring("CheckForError(8)");
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName)) // Check column name existence
{
string str = field[fieldName].ToString();
// ----------------------------------------------------------------------
// Check for data existence and format validity
// ----------------------------------------------------------------------
if (!string.IsNullOrEmpty(str.Trim()))
{
if (!Regex.IsMatch(str, pattern))
{
errorCode = typeErrorCode;
}
}
else
{
if (required >= 2)
{
errorCode = nullErrorCode;
}
}
}
else
{
errorCode = columnErrorCode;
}
InfoAspect.Measured("CheckForError(8)");
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- RecordStatusError -->
/// <summary>
/// Adds an error row to the status-error log
/// </summary>
/// <param name="errorCode"></param>
/// <param name="importRow">The row to be imported</param>
/// <param name="statusErrorLog"></param>
public static void RecordStatusError(string errorCode, DataRow importRow, DataTable statusErrorLog)
{
InfoAspect.Measuring("RecordStatusError");
int row = statusErrorLog.Rows.Count;
if (Regex.IsMatch(errorCode, "03"))
{
Pause();
}
statusErrorLog.ImportRow(importRow);
statusErrorLog.Rows[row]["Status_Error_Code"] = errorCode;
if (StatusError.ErrorCodeList.ContainsKey(errorCode))
{
statusErrorLog.Rows[row]["Error_Message"] = StatusError.ErrorCodeList[errorCode];
}
else
{
Pause();
}
InfoAspect.Measured("RecordStatusError");
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckElementExists -->
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="exportIdColumName"></param>
/// <param name="id"></param>
/// <param name="aspect"></param>
/// <returns></returns>
protected bool CheckElementExists(string tableName, string exportIdColumName, object id, InfoAspect aspect)
{
bool found = false;
if (!InData.IsNull(id))
{
try
{
string query = " SELECT * FROM " + tableName + " WHERE " + exportIdColumName + " = " + id.ToString(); // TODO: parameterize this
DataTable table = InData.GetTable(tableName, query, aspect.SecondaryConnection);
found = (table.Rows.Count > 0);
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
}
else
{
throw new DataException("null key not findable");
}
return(found);
}
} // In SOA when you cache something in a static variable you may need to add this to the application refresh code
public static Guid MentalCharacteristics_insert(InfoAspect aspect)
{
_mind = null; return(new EndemeAccess().ToteEndemeSet(MentalCharacteristics.WithId(Guid.NewGuid()), aspect.SecondaryConn));
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// looks to see if the particular field value is found in the particular table column etc.
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="pattern"></param>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="errorCodeForColumnNameNotFound" ></param>
/// <param name="errorCodeForRequiredDataMissing" ></param>
/// <param name="errorCodeForValueIncorrectFormat"></param>
/// <param name="errorCodeForValueNotFoundInTable"></param>
/// <param name="errorCodeForExceedsDataLength" ></param>
/// <param name="aspect"></param>
/// <returns></returns>
internal static string CheckForError(int required, int maxLength
, DataRow field, string fieldName, string pattern, string tableName, string columnName
, string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing
, string errorCodeForValueIncorrectFormat, string errorCodeForValueNotFoundInTable
, string errorCodeForExceedsDataLength, InfoAspect aspect)
{
string errorCode = string.Empty;
try
{
string str = "";
if (field.Table.Columns.Contains(fieldName))
{
if (field[fieldName] != null)
{
str = field[fieldName].ToString();
}
if (!string.IsNullOrEmpty(str.Trim()))
{
if (Regex.IsMatch(str, pattern))
{
if (str.Length <= maxLength)
{
DataTable table = InData.GetTable(tableName
, " SELECT *"
+ " FROM " + tableName
+ " WHERE " + columnName + " = '" + str + "'"
, aspect.SecondaryConnection);
if (table.Rows.Count == 0)
{
errorCode = errorCodeForValueNotFoundInTable;
}
}
else
{
errorCode = errorCodeForExceedsDataLength;
}
}
else
{
errorCode = errorCodeForValueIncorrectFormat;
}
}
else
{
if (required > 0)
{
errorCode = errorCodeForRequiredDataMissing;
}
}
}
else
{
errorCode = errorCodeForColumnNameNotFound;
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// looks to see if the particular two part field value is found in the particular table column etc.
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName1"></param>
/// <param name="delim"></param>
/// <param name="fieldName2"></param>
/// <param name="lookup"></param>
/// <param name="pattern"></param>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="errorCodeForColumnNameNotFound"></param>
/// <param name="errorCodeForRequiredDataMissing"></param>
/// <param name="errorCodeForValueIncorrectFormat"></param>
/// <param name="errorCodeForValueNotFoundInTable"></param>
/// <param name="aspect"></param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field
, string fieldName1, string delim, string fieldName2, NumericLookup lookup, string pattern
, string tableName, string columnName
, string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing
, string errorCodeForValueIncorrectFormat, string errorCodeForValueNotFoundInTable, InfoAspect aspect)
{
string errorCode = string.Empty;
try
{
string str1 = "";
if (field.Table.Columns.Contains(fieldName1) && field.Table.Columns.Contains(fieldName2))
{
object obj1 = field[fieldName1];
if (obj1 != null)
{
str1 = obj1.ToString();
}
string str2 = ""; //field[fieldName2].ToString();
object obj2 = field[fieldName2];
if (obj2 != null)
{
str2 = obj2.ToString();
}
if (!string.IsNullOrEmpty(str1.Trim()))
{
if (Regex.IsMatch(str1, pattern))
{
SqlInt32 importId = InData.GetSqlInt32(field, fieldName2);
int realId = lookup[(int)importId];
string fullCode = str1 + delim + realId;
DataTable table = InData.GetTable(tableName
, " SELECT *"
+ " FROM " + tableName
+ " WHERE " + columnName + " = '" + fullCode + "'"
, aspect.SecondaryConnection);
if (table.Rows.Count == 0)
{
errorCode = errorCodeForValueNotFoundInTable;
}
}
else
{
errorCode = errorCodeForValueIncorrectFormat;
}
}
else
{
if (required > 0)
{
errorCode = errorCodeForRequiredDataMissing;
}
}
}
else
{
errorCode = errorCodeForColumnNameNotFound;
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Checks an imported string for errors
/// </summary>
/// <param name="nullValue"></param>
/// <param name="field"></param>
/// <param name="extract"></param>
/// <param name="aspect"></param>
/// <returns></returns>
public static string CheckForError(string nullValue, DataRow field, ExtractField extract, InfoAspect aspect)
{
string errorCode = string.Empty;
if (field.Table.Columns.Contains(extract.Column))
{
string str = field[extract.Column].ToString();
// ----------------------------------------------------------------------
// Check data validity
// ----------------------------------------------------------------------
if (!string.IsNullOrEmpty(str.Trim()) && str != nullValue && str != "NULL")
{
// ------------------------------------------------------------------
// Check data validity
// ------------------------------------------------------------------
string str2 = Regex.Replace(str, "^'", "");
if (!string.IsNullOrEmpty(extract.Pattern))
{
if (!Regex.IsMatch(str.Trim(), extract.Pattern) &&
!Regex.IsMatch(str2.Trim(), extract.Pattern))
{
errorCode = extract.ErrorFormat;
}
}
if (str2.Length > extract.Length)
{
errorCode = extract.ErrorLength;
}
}
else
{
if (extract.ImportField >= 2)
{
errorCode = extract.ErrorRequired;
}
}
}
else
{
errorCode = extract.ErrorFieldName;
}
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <summary>
/// Checks a date field in a DataTable for errors
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="datePattern"></param>
/// <param name="mustBeAfterDate"></param>
/// <param name="mustBeBeforeDate"></param>
/// <param name="errorCodeForColumnNameNotFound"></param>
/// <param name="errorCodeForRequiredDataMissing"></param>
/// <param name="errorCodeForValueIncorrectFormat"></param>
/// <param name="errorCodeForValueOutOfRange"></param>
/// <returns></returns>
public static string CheckForError(int required, DataRow field, string fieldName
, string datePattern, DateTime?mustBeAfterDate, DateTime?mustBeBeforeDate
, string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing
, string errorCodeForValueIncorrectFormat, string errorCodeForValueOutOfRange, InfoAspect aspect)
{
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName))
{
string str = field[fieldName].ToString();
// --------------------------------------------------------------------------
// Check data validity
// --------------------------------------------------------------------------
if (string.IsNullOrEmpty(str.Trim()))
{
if (required >= 2)
{
errorCode = errorCodeForRequiredDataMissing;
}
}
else
{
// ------------------------------------------------------------------
// Check data validity
// ------------------------------------------------------------------
string str2 = Regex.Replace(str, "^'", "");
if (!string.IsNullOrEmpty(datePattern))
{
if (!Regex.IsMatch(str, datePattern) &&
!Regex.IsMatch(str2, datePattern)
)
{
errorCode = errorCodeForValueIncorrectFormat;
}
}
if (string.IsNullOrEmpty(errorCode.Trim()))
{
DateTime date;
if (DateTime.TryParse(Regex.Replace(str2, "^'", ""), out date))
{
if (mustBeAfterDate != null && date <= mustBeAfterDate)
{
errorCode = errorCodeForValueOutOfRange;
}
if (mustBeBeforeDate != null && date >= mustBeBeforeDate)
{
errorCode = errorCodeForValueOutOfRange;
}
}
else
{
errorCode = errorCodeForValueIncorrectFormat;
}
}
}
}
else
{
errorCode = errorCodeForColumnNameNotFound;
}
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForErrors -->
/// <summary>
///
/// </summary>
/// <param name="required"></param>
/// <param name="field"></param>
/// <param name="fieldName"></param>
/// <param name="lookup"></param>
/// <param name="errorCodeForColumnNameNotFound"></param>
/// <param name="errorCodeForRequiredDataMissing"></param>
/// <param name="errorCodeForValueIsNotAnInteger"></param>
/// <param name="errorCodeForValueNotFoundInXref"></param>
/// <returns></returns>
public static string CheckForErrors(int required, DataRow field, string fieldName, NumericLookup lookup
, string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing
, string errorCodeForValueIsNotAnInteger, string errorCodeForValueNotFoundInXref, InfoAspect aspect)
{
string errorCode = string.Empty;
if (field.Table.Columns.Contains(fieldName))
{
string str = field[fieldName].ToString();
// ----------------------------------------------------------------------
// Check data validity
// ----------------------------------------------------------------------
if (!string.IsNullOrEmpty(str.Trim()) && str != "NULL")
{
// ------------------------------------------------------------------
// Check data validity
// ------------------------------------------------------------------
int id = 0;
if (int.TryParse(str, out id))
{
if (!lookup.ContainsKey(id))
{
errorCode = errorCodeForValueNotFoundInXref;
}
}
else
{
errorCode = errorCodeForValueIsNotAnInteger;
}
}
else
{
if (required >= 2)
{
errorCode = errorCodeForRequiredDataMissing;
}
}
}
else
{
errorCode = errorCodeForColumnNameNotFound;
}
return(errorCode);
}
// ----------------------------------------------------------------------------------------
/// <!-- CalculateImportStatus -->
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <param name="statusErrorLog"></param>
/// <returns></returns>
internal static string CalculateImportStatus(DataRow field, DataTable statusErrorLog, InfoAspect aspect)
{
string importStatus = "Not Imported";
try
{
string fileLineNumber = field["FileLineNumber"].ToString();
DataRow[] statusErrorRow = statusErrorLog.Select("FileLineNumber = '" + fileLineNumber + "'");
if (statusErrorRow.Length > 0)
{
string statusErrorCode = (string)statusErrorRow[0]["Status_Error_Code"];
SqlBoolean isUpdate = InData.GetSqlBoolean(field, "IsUpdate");
if (isUpdate)
{
if (Regex.IsMatch(statusErrorCode, "00$"))
{
throw new DataMisalignedException("Conflicting information about row update vs insert.");
}
}
else
{
if (Regex.IsMatch(statusErrorCode, "01$"))
{
throw new DataMisalignedException("Conflicting information about row update vs insert.");
}
}
if (Regex.IsMatch(statusErrorCode, "00$"))
{
importStatus = "Inserted";
}
if (Regex.IsMatch(statusErrorCode, "01$"))
{
importStatus = "Updated";
}
if (Regex.IsMatch(statusErrorCode, "35[01]$"))
{
importStatus = "Imported";
}
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(importStatus);
}
public static int MentalCharacteristics_update(InfoAspect aspect)
{
_mind = null; return(new EndemeAccess().ReUpEndemeSet(MentalCharacteristics.WithId(MentalCharacteristics_id(aspect)), aspect));
}
// ----------------------------------------------------------------------------------------
/// <!-- Update -->
/// <summary>
/// Updates a row in one of the location related tables
/// </summary>
/// <param name="updateQuery"></param>
/// <param name="field"></param>
/// <param name="importedData"></param>
/// <param name="aspect"></param>
private SqlInt32 Update(string tableName, DataRow field, DataTable importedData, InfoAspect aspect)
{
if (tableName == "Instructions")
{
string updateQuery = "";
object obj = null;
using (SqlCommand cmd = new SqlCommand(updateQuery, aspect.SecondaryConnection))
{
try
{
// --------------------------------------------------------------
// Fill the command
// --------------------------------------------------------------
//UpdateQueryFill(cmd, tableName, field, aspect.Enter(Name, "QueryFill")); aspect--;
foreach (string transformKey in Transform.Keys)
{
AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--;
}
// --------------------------------------------------------------
// Perform the insert
// --------------------------------------------------------------
obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--;
string report = InData.AsciiNewQuery(cmd);
if (obj != null)
{
SqlInt32 id = (SqlInt32)obj;
// ------------------------------------------------------------------
// Record the insert
// ------------------------------------------------------------------
if (id > 0)
{
int row = importedData.Rows.Count;
importedData.ImportRow(field);
importedData.Rows[row][tableName + "DbId"] = id;
field[RealIdColumn] = id;
}
}
else
{
string report2 = InData.AsciiNewQuery(cmd);
}
}
catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
}
}
return(0);
}
// ----------------------------------------------------------------------------------------
/// <!-- GetMostRecentImportFilesMatching -->
/// <summary>
/// Returns the most recent file in each directory matching the pattern and not matching the avoid pattern
/// </summary>
/// <param name="filePattern">files to include pattern</param>
/// <param name="dirList"></param>
/// <param name="avoidPattern">the pattern starting with the beginning of the file name (leave out ^ and \\)</param>
/// <returns></returns>
public static List <string> GetMostRecentImportFilesMatching(string filePattern
, List <DirectoryInfo> dirList, string avoidPattern, InfoAspect aspect)
{
List <string> fileName = new List <string>();
try
{
Dictionary <string, string> newestFile = new Dictionary <string, string>();
foreach (DirectoryInfo directory in dirList)
{
List <FileInfo> fi = new List <FileInfo>(directory.GetFiles());
List <FileInfo> orderedFileList = fi.OrderBy(x => x.LastWriteTime).ToList();
foreach (FileInfo file in orderedFileList)
{
if (Regex.IsMatch(file.Name, filePattern, RegexOptions.IgnoreCase))
{
if (!Regex.IsMatch(file.Name, "^" + avoidPattern, RegexOptions.IgnoreCase) &&
!Regex.IsMatch(file.Name, @"\\" + avoidPattern, RegexOptions.IgnoreCase))
{
// ----------------------------------------------------------
// Replace older files (earlier in the list) with newer files of the same directory
// ----------------------------------------------------------
string dirKey = directory.Name;
if (!newestFile.ContainsKey(dirKey))
{
newestFile.Add(dirKey, "");
}
newestFile[dirKey] = directory.Name + "\\" + file.Name;
}
}
}
}
foreach (string file in newestFile.Values)
{
fileName.Add(file);
}
}
catch (Exception ex) { Pause(); aspect.Rethrow(ex); }
return(fileName);
}
// ----------------------------------------------------------------------------------------
/// <!-- InitializeInsertedDataTable -->
/// <summary>
///
/// </summary>
/// <param name="importData"></param>
/// <param name="idColumnName"></param>
/// <returns></returns>
public static DataTable InitializeImportedDataTable(DataTable importData, string idColumnName, InfoAspect aspect)
{
DataTable importedData = new DataTable();
importedData = importData.Clone();
importedData.TableName = "ImportedData";
importedData.Columns.Add(new DataColumn(idColumnName, typeof(SqlInt32)));
return(importedData);
}
// ----------------------------------------------------------------------------------------
/// <!-- ImportErrorInsert -->
/// <summary>
/// Inserts a module import error row
/// </summary>
/// <param name="query"></param>
/// <param name="statusErrorLog"></param>
/// <param name="row"></param>
/// <param name="itemIdByLineNum"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static void ImportErrorInsert(string query, DataTable statusErrorLog, int row
, string statusErrorCode, Dictionary <int, int> itemIdByLineNum, string moduleName, InfoAspect aspect)
{
SqlCommand errorInsert = new SqlCommand(query, aspect.SecondaryConnection);
using (errorInsert)
{
// ------------------------------------------------------------------
// Insert the error into the error table
// ------------------------------------------------------------------
int lineNumber = int.Parse(statusErrorLog.Rows[row]["FileLineNumber"].ToString());
int itemId = itemIdByLineNum[lineNumber];
ImportDalCommon.ImportErrorFill(errorInsert, itemId, statusErrorCode, statusErrorLog.Rows[row], moduleName);
int errorId = (int)errorInsert.ExecuteScalar();
}
}
// ----------------------------------------------------------------------------------------
/// <!-- Insert -->
/// <summary>
/// If the location element does not exist then insert into one of the four location tables
/// </summary>
/// <param name="insertQuery"></param>
/// <param name="field"></param>
/// <param name="importedData">adds row to this table if the location element is actually saved</param>
/// <param name="aspect"></param>
/// <returns></returns>
private SqlInt32 Insert(string insertQuery, string tableName, DataRow field, DataTable importedData, InfoAspect aspect)
{
SqlInt32 id = SqlInt32.Null;
object obj = null;
using (SqlCommand cmd = new SqlCommand(insertQuery, aspect.SecondaryConnection))
{
try
{
// ------------------------------------------------------------------
// Fill the command
// ------------------------------------------------------------------
//InsertQueryFill(cmd, tableName, field, aspect.Enter(Name,"QueryFill")); aspect--;
foreach (string transformKey in Transform.Keys)
{
AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--;
}
// ------------------------------------------------------------------
// Sanity check
// ------------------------------------------------------------------
string err = ParameterSanityCheck(cmd);
if (!string.IsNullOrEmpty(err.Trim()))
{
throw new Exception(err);
}
// ------------------------------------------------------------------
// Perform the insert
// ------------------------------------------------------------------
obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--;
string report = InData.AsciiNewQuery(cmd);
if (obj != null)
{
id = InData.GetSqlInt32(obj);
// ------------------------------------------------------------------
// Record the insert
// ------------------------------------------------------------------
if (id > 0)
{
int row = importedData.Rows.Count;
importedData.ImportRow(field);
importedData.Rows[row][tableName + "DbId"] = id;
field[RealIdColumn] = id;
}
}
else
{
string report2 = InData.AsciiNewQuery(cmd);
}
}
catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); }
finally
{
// ------------------------------------------------------------------
// Make sure the database identity structure is set properly
// ------------------------------------------------------------------
string query = " SET IDENTITY_INSERT [" + tableName + "] OFF;";
SqlCommand reset = new SqlCommand(query, aspect.SecondaryConnection);
reset.ExecuteNonQuery();
}
}
return(id);
}
// ----------------------------------------------------------------------------------------
/// <!-- InsertRunError -->
/// <summary>
/// Inserts a row into the Import_Run_Error table
/// </summary>
/// <param name="err"></param>
/// <param name="importRunId"></param>
public static int InsertRunError(string err, Exception ex, string contentType, int importRunId, InfoAspect aspect)
{
int importErrorId = 0;
// --------------------------------------------------------------------------
// Build the parameterized import summary insert query
// --------------------------------------------------------------------------
string query
= " INSERT INTO dbo.Import_Run_Error"
+ " ( Level_2_ID"
+ " , Agency_ID"
+ " , Level_4_ID"
+ " , Content_Type"
+ " , Error_Type"
+ " , Error_Description"
+ " , Import_Run_ID"
+ " ) OUTPUT INSERTED.Import_Error_ID"
+ " VALUES "
+ " ( @Level_2_ID"
+ " , @Agency_ID"
+ " , @Level_4_ID"
+ " , @Content_Type"
+ " , @Error_Type"
+ " , @Error_Description"
+ " , @Import_Run_ID"
+ " )";
// --------------------------------------------------------------------------
// Run the insert
// --------------------------------------------------------------------------
using (SqlCommand command = new SqlCommand(query, aspect.SecondaryConnection))
{
// ------------------------------------------------------------------
// Fill the parameterized import error insert query
// ------------------------------------------------------------------
string errorType = "";
if (ex != null)
{
errorType = ex.GetType().ToString();
}
command.Parameters.AddWithValue("@Level_2_ID", 0);
command.Parameters.AddWithValue("@Agency_ID", 0);
command.Parameters.AddWithValue("@Level_4_ID", 0);
command.Parameters.AddWithValue("@Content_Type", contentType);
command.Parameters.AddWithValue("@Error_Type", errorType);
command.Parameters.AddWithValue("@Error_Description", err);
command.Parameters.AddWithValue("@Import_Run_ID", importRunId);
// ------------------------------------------------------------------
// Insert the import error row
// ------------------------------------------------------------------
importErrorId = (int)command.ExecuteScalar();
}
return(importErrorId);
}
// ----------------------------------------------------------------------------------------
/// <!-- Check -->
/// <summary>
///
/// </summary>
/// <param name="listFromXref"></param>
/// <param name="label"></param>
/// <param name="tableName"></param>
/// <param name="keyColumn"></param>
/// <param name="valueColumn"></param>
/// <param name="aspect"></param>
/// <returns></returns>
public static string Check(NumericLookup listFromXref, string label
, string tableName, string keyColumn, string valueColumn, InfoAspect aspect)
{
Dictionary <int, int> listFromTable = InData.GetDictionaryFrom(tableName, keyColumn, valueColumn, aspect.SecondaryConnection);
string err = "";
string delim = "";
foreach (int id in listFromXref.Values)
{
if (!listFromTable.ContainsKey(id))
{
err += delim + label + " Xref ID missing from " + tableName + " table: " + id;
delim = "\r\n";
}
}
return(err);
}
private static EndemeSet _mind = null; //ABCDEFGHIJKLMNOPQRSTUV|
// ------------------------------------------------------------------------------
// Methods
// ------------------------------------------------------------------------------
public static Guid MentalCharacteristics_id(InfoAspect aspect)
{
return(new EndemeAccess().InEndemeSetOfEndemeSetLabel(MentalCharacteristicsLabel, aspect).GuidValue(0, "EndemeSetId", Guid.NewGuid()));
}
