protected override string[] LowThresholdColumns(DTO.QueryComposer.QueryComposerResponseDTO response)
{
List <string> ltCols = new List <string>();
foreach (IEnumerable <Dictionary <string, object> > table in response.Results)
{
Dictionary <string, object> row = table.FirstOrDefault();
if (row != null && row.Count > 0)
{
try
{
foreach (string column in row.Keys)
{
if (SummaryQueryUtil.IsCheckedColumn(column))
{
ltCols.Add(column);
var depCols = SummaryQueryUtil.GetDependentComputedColumns(column, row);
if (depCols != null && depCols.Count > 0)
{
ltCols.AddRange(depCols);
}
}
}
}
catch { }
}
}
return(ltCols.ToArray());
}
protected IEnumerable <Dictionary <string, object> > ExecuteQuery(string query, List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors, bool viewSQL)
{
List <Dictionary <string, object> > results = new List <Dictionary <string, object> >();
List <string> ltCols = new List <string>();
System.Data.IDbConnection conn = null;
try
{
if (!viewSQL)
{
conn = Utilities.OpenConnection(settings, logger);
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandTimeout = Convert.ToInt32(settings.GetSetting("CommandTimeout", 120));
logger.Debug("Executing query:" + Environment.NewLine + query);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader.GetName(i), reader.GetValue(i).ConvertDBNullToNull());
}
results.Add(row);
}
}
var testRow = results.FirstOrDefault();
if (testRow != null && testRow.Count > 0)
{
try
{
foreach (string column in testRow.Keys)
{
if (SummaryQueryUtil.IsCheckedColumn(column))
{
ltCols.Add(column);
var depCols = SummaryQueryUtil.GetDependentComputedColumns(column, testRow);
if (depCols != null && depCols.Count > 0)
{
ltCols.AddRange(depCols);
}
}
}
}
catch { }
}
var columnNames = ltCols.ToArray();
if (columnNames != null && columnNames.Length != 0 && _lowThresholdValue.HasValue)
{
foreach (Dictionary <string, object> row in results)
{
try
{
foreach (string column in columnNames)
{
object currentValue;
if (row.TryGetValue(column, out currentValue))
{
double value = Convert.ToDouble(currentValue);
if (value > 0 && value < _lowThresholdValue)
{
row[LowThresholdColumnName] = true;
}
}
}
}
catch { }
}
}
}
}
else
{
Dictionary <string, object> row = new Dictionary <string, object>();
row.Add("SQL", query);
results.Add(row);
}
}
catch (Exception ex)
{
errors.Add(new DTO.QueryComposer.QueryComposerResponseErrorDTO
{
Code = "999",
Description = ex.UnwindException(true)
});
}
finally
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
return(results);
}
