// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}