private string BuildWhereClause(Audit auditToParse, int testIndex)
{
string result;
AuditTest currTest = auditToParse.Test;
string criteria = currTest.Criteria;
string columnName = currTest.ColumnName;
string Operator = currTest.Operator;
string whereClause = auditToParse.Test.WhereClause;
// Add additional custom criteria inside this select statement
switch (criteria.ToUpper())
{
case "TODAY":
result = Today(columnName) + Operator + "0";
auditToParse.Test.WhereClause = result;
break;
case "COUNTROWS":
result = whereClause.Replace("COUNTROWS", auditToParse.Test.RowCount.ToString());
auditToParse.Test.WhereClause = result;
break;
default:
result = auditToParse.Test.WhereClause;
break;
}
return(result);
}
/// <summary>
/// Initializes a new instance of the <see cref="Audit"/> class.
/// </summary>
public Audit()
{
Test = new AuditTest();
ErrorMessages = new List <string>();
ShowQueryMessage = true;
ShowThresholdMessage = true;
ShowCommentMessage = true;
}
/// <summary>
/// Initializes a new instance of the <see cref="Audit"/> class.
/// </summary>
public Audit()
{
//EmailSubscribers = new ArrayList();
//EmailCarbonCopySubscribers = new ArrayList();
//EmailBlindCarbonCopySubscribers = new ArrayList();
Test = new AuditTest();
ErrorMessages = new List <string>();
ShowQueryMessage = true;
ShowThresholdMessage = true;
ShowCommentMessage = true;
}
/// <summary>
/// Removes the specified item.
/// </summary>
/// <param name="item">The item.</param>
public void Remove(AuditTest item)
{
List.Remove(item);
}
/// <summary>
/// Inserts the specified index.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="item">The item.</param>
public void Insert(int index, AuditTest item)
{
List.Insert(index, item);
}
/// <summary>
/// Adds the specified item.
/// </summary>
/// <param name="item">The item.</param>
/// <returns>System.Int32.</returns>
public int Add(AuditTest item)
{
return(List.Add(item));
}
/// <summary>
/// Determines whether [contains] [the specified item].
/// </summary>
/// <param name="item">The item.</param>
/// <returns><c>true</c> if [contains] [the specified item]; otherwise, <c>false</c>.</returns>
public bool Contains(AuditTest item)
{
return(List.Contains(item));
}
private void RunTests(ref Audit currentAudit)
{
int testCount;
int tempFor1 = 1;
for (testCount = 0; testCount < tempFor1; testCount++)
{
DataSet dsTest = GetTestDataSet(ref currentAudit, testCount);
if (dsTest.Tables.Count == 0)
{
AuditTest currTest = currentAudit.Test;
if (!currTest.FailIfConditionIsTrue)
{
currentAudit.Result = true;
}
else
{
// TODO: This is a hack that needs to be fixed.
// I want the test to succeed, but not send
// any emails. When this app was first built,
// it always assumed that the audit would fail or
// succeed with no further processing. This is to handle
// the weird case where there are actually two thresholds;
// the first one is the usual one, and the second one
// is for the data itself.
// TODO: Think of a better way of doing this!
if (currTest.SendReport)
{
currentAudit.Result = true;
}
else
{
currentAudit.Result = false;
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit,
dsTest);
}
}
}
else
{
var currTest = currentAudit.Test;
int rowCount = dsTest.Tables[0].Rows.Count;
if (currTest.TestReturnedRows)
{
if (currTest.Criteria.ToUpper() == "COUNTROWS")
{
string threshold;
switch (currTest.Operator)
{
case ">":
if (rowCount > currTest.RowCount)
{
if (!currTest.FailIfConditionIsTrue)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was greater than " + threshold + " rows. This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
else
{
if (rowCount <= currTest.RowCount)
{
// Threshold was not broken, so the test passes.
currentAudit.Result = true;
}
else
{
threshold =
currentAudit.Test.RowCount.ToString(
CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage =
"The failure threshold was greater than " + threshold +
" rows. This audit returned " +
rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
break;
case ">=":
case "=>":
if (rowCount >= currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was greater than or equal to " + threshold + " rows. This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
break;
case "<":
if (rowCount < currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was less than " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "<=":
case "=<":
if (rowCount <= currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was less than or equal to " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "=":
if (rowCount == currTest.RowCount)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was equal to " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "<>":
case "!=":
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
break;
}
}
else
{
if (rowCount > 0)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
currentAudit.Test.FailedMessage = "This audit was set to have more than zero rows returned. " + "This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
else
{
currentAudit.Result = true;
}
}
}
}
else
{
if (rowCount == 0)
{
currentAudit.Result = true;
}
else
{
currentAudit.Test.FailedMessage = "This audit was set to not return any rows. " + "This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
if (currentAudit.Result == false)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit, dsTest);
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
if (currentAudit.Test.SendReport)
{
// It's not really a failure. Just want to send a report-like email.
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit, dsTest);
}
}
}
dsTest.Dispose();
}
}
currentAudit.HasRun = true;
}