private static void FillIntGender()
{
if (_intGender == null)
{
try { _intGender = new NumericLookup(3); } catch { }
}
// Import INTFDB SANDBOX
_intGender.Add(2, 12); // 2-Unknown -> 12-Unknown or Unspecified -> 3-Unknown or Unspecified
_intGender.Add(3, 1); // 3-Female -> 1 -Female -> 1-Female
_intGender.Add(4, 2); // 4-Male -> 2 -Male -> 2-Male
}
// ----------------------------------------------------------------------------------------
/// <!-- GetDictionaryFrom -->
/// <summary>
/// Converts two columns of a database table into a Dictionary<int,int> structure
/// </summary>
/// <param name="tableName"></param>
/// <param name="keyColumn"></param>
/// <param name="valueColumn"></param>
/// <param name="connection"></param>
/// <returns></returns>
public static NumericLookup GetDictionaryFrom(string tableName, string keyColumn
, string valueColumn, SqlConnection connection)
{
NumericLookup list = new NumericLookup(10);
string query
= " SELECT " + keyColumn + ", " + valueColumn
+ " FROM " + tableName
+ " WHERE " + valueColumn + " IS NOT NULL";
try
{
var command = new SqlCommand(query, connection);
DataTable table = new DataTable();
using (command)
{
SqlDataReader reader = command.ExecuteReader();
table.Load(reader);
}
for (int row = 0; row < table.Rows.Count; ++row)
{
int key = 0;
int value = 0;
if (int.TryParse(table.Rows[row][keyColumn].ToString(), out key) &&
int.TryParse(table.Rows[row][valueColumn].ToString(), out value))
{
list.Add(key, value);
}
}
}
catch (SqlException ex)
{
Pause();
if (ex.InnerException != null)
{
throw new Exception("Sql connection having trouble? - " + ex.Message + " - " + ex.InnerException.Message, ex);
}
else
{
throw new Exception("Sql connection having trouble? - " + ex.Message, ex);
}
}
catch (Exception ex) { Pause(); throw new Exception(ex.Message, ex); }
return(list);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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 void FillStates()
{
if (_state == null)
{
try { _state = new NumericLookup(60); } catch { }
}
_state.Add(1, 1); // 1 AL Alabama
_state.Add(2, 2); // 2 AK Alaska
_state.Add(3, 3); // 3 AS American Samoa
_state.Add(4, 4); // 4 AZ Arizona
_state.Add(5, 5); // 5 AR Arkansas
_state.Add(6, 6); // 6 CA California
_state.Add(7, 7); // 7 CO Colorado
_state.Add(8, 8); // 8 CT Connecticut
_state.Add(9, 9); // 9 DE Delaware
_state.Add(10, 10); // 10 DC District of Columbia
_state.Add(11, 11); // 11 FM Federated States of Micronesia
_state.Add(12, 12); // 12 FL Florida
_state.Add(13, 13); // 13 GA Georgia
_state.Add(14, 14); // 14 GU Guam
_state.Add(15, 15); // 15 HI Hawaii
_state.Add(16, 16); // 16 ID Idaho
_state.Add(17, 17); // 17 IL Illinois
_state.Add(18, 18); // 18 IN Indiana
_state.Add(19, 19); // 19 IA Iowa
_state.Add(20, 20); // 20 KS Kansas
_state.Add(21, 21); // 21 KY Kentucky
_state.Add(22, 22); // 22 LA Louisiana
_state.Add(23, 23); // 23 ME Maine
_state.Add(24, 24); // 24 MH Marshall Islands
_state.Add(25, 25); // 25 MD Maryland
_state.Add(26, 26); // 26 MA Massachusetts
_state.Add(27, 27); // 27 MI Michigan
_state.Add(28, 28); // 28 MN Minnesota
_state.Add(29, 29); // 29 MS Mississippi
_state.Add(30, 30); // 30 MO Missouri
_state.Add(31, 31); // 31 MT Montana
_state.Add(32, 32); // 32 NE Nebraska
_state.Add(33, 33); // 33 NV Nevada
_state.Add(34, 34); // 34 NH New Hampshire
_state.Add(35, 35); // 35 NJ New Jersey
_state.Add(36, 36); // 36 NM New Mexico
_state.Add(37, 37); // 37 NY New York
_state.Add(38, 38); // 38 NC North Carolina
_state.Add(39, 39); // 39 ND North Dakota
_state.Add(40, 40); // 40 MP Northern Mariana Islands
_state.Add(41, 41); // 41 OH Ohio
_state.Add(42, 42); // 42 OK Oklahoma
_state.Add(43, 43); // 43 OR Oregon
_state.Add(44, 44); // 44 PW Palau
_state.Add(45, 45); // 45 PA Pennsylvania
_state.Add(46, 46); // 46 PR Puerto Rico
_state.Add(47, 47); // 47 RI Rhode Island
_state.Add(48, 48); // 48 SC South Carolina
_state.Add(49, 49); // 49 SD South Dakota
_state.Add(50, 50); // 50 TN Tennessee
_state.Add(51, 51); // 51 TX Texas
_state.Add(52, 52); // 52 UT Utah
_state.Add(53, 53); // 53 VT Vermont
_state.Add(54, 54); // 54 VI Virgin Islands
_state.Add(55, 55); // 55 VA Virginia
_state.Add(56, 56); // 56 WA Washington
_state.Add(57, 57); // 57 WV West Virginia
_state.Add(58, 58); // 58 WI Wisconsin
_state.Add(59, 59); // 59 WY Wyoming
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- 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);
}
// ----------------------------------------------------------------------------------------
/// <!-- CheckForError -->
/// <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 CheckForError(int required, DataRow field, string fieldName, NumericLookup lookup
, string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing
, string errorCodeForValueIsNotAnInteger, string errorCodeForValueNotFoundInXref)
{
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 = errorCodeForRequiredDataMissing;
} // Check required data existence
}
else
{
errorCode = errorCodeForValueIsNotAnInteger;
}
}
else
{
if (!lookup.ContainsKey((int)num))
{
errorCode = errorCodeForValueNotFoundInXref;
}
} // Check data range validity
}
else
{
errorCode = errorCodeForColumnNameNotFound;
}
return(errorCode);
}