public ActionResult DataSniffer()
{
DataSnifferObject dataSnifferObject = new DataSnifferObject();
ConnectionObject connectionObject = new ConnectionObject();
connectionObject.isConnected = false;
if (Session["ConnectionObject"] != null)
{
connectionObject = Session["ConnectionObject"] as ConnectionObject;
}
dataSnifferObject.connectionObject = connectionObject;
return(View(dataSnifferObject));
}
public ActionResult DataSniffer(DataSnifferObject dataSnifferObject)
{
List <string> parameterList = new List <string>();
parameterList.Add(dataSnifferObject.connectionObject.DatabaseSelectedName);
parameterList.Add(dataSnifferObject.parameter2.ToString());
if (dataSnifferObject.runProcedure == true)
{
dataSnifferObject.connectionObject = connection.executeStoredProcedure(dataSnifferObject.connectionObject
, "Library_model_ccc.dbo.DataSniffer_2"
, parameterList
, false);
}
string connectionString = connection.GetConnectionString(dataSnifferObject.connectionObject);
string sql =
" SELECT DISTINCT TableName, TableCriticality," +
" SUM(CASE WHEN DataElementCriticality = 'Mandatory' and DataPresentInFile = 0 AND COALESCE(Override,0) <> 1 THEN 1 ELSE 0 END) as MandatoryFieldMissingCount, " +
" SUM(CASE WHEN COALESCE(ResolvedInPostLoad,0) <> 1 THEN CAST(DataPresentInFile as int) ELSE 0 END ) as DataPresentInFileCount, " +
" SUM(CASE WHEN IssuePresent = 1 AND DataPresentInFile > 0 AND COALESCE(ResolvedInPostLoad,0) <> 1 THEN 1 ELSE 0 END) as IssuePresentInFileCount," +
" COUNT(*) as TotalFieldCount" +
" FROM (SELECT DISTINCT TableName, TableCriticality, DataElementCriticality, DataPresentInFile, DataElement, IssuePresent , Override " +
" FROM reference.DataSniffer WHERE IsActive = 1 AND COALESCE(ResolvedInPostLoad,0) <> 1 ) x GROUP BY TableName, TableCriticality ";
using (SqlConnection conn2 = new SqlConnection(connectionString))
{
SqlCommand cmd2 = new SqlCommand(sql, conn2);
conn2.Open();
SqlDataReader rdr2 = cmd2.ExecuteReader();
List <DataSnifferParent> model2 = new List <DataSnifferParent>();
while (rdr2.Read())
{
var record2 = new DataSnifferParent();
record2.TableName = rdr2["TableName"].ToString();
record2.TableCriticality = rdr2["TableCriticality"].ToString();
record2.MandatoryFieldMissingCount = Convert.ToInt32(rdr2["MandatoryFieldMissingCount"]);
record2.DataPresentInFileCount = Convert.ToInt32(rdr2["DataPresentInFileCount"]);
record2.IssuePresentInFileCount = Convert.ToInt32(rdr2["IssuePresentInFileCount"]);
record2.TotalFieldCount = Convert.ToInt32(rdr2["TotalFieldCount"]);
if (record2.DataPresentInFileCount > 0)
{
record2.CriticalPercent = Decimal.Round(100 * Decimal.Divide(record2.MandatoryFieldMissingCount, (record2.TotalFieldCount)));
record2.CautionaryPercent = Decimal.Round(100 * Decimal.Divide(record2.IssuePresentInFileCount, (record2.TotalFieldCount)));
record2.AcceptablePercent = Decimal.Round((100 - record2.CautionaryPercent - record2.CriticalPercent));
}
else
{
record2.CriticalPercent = 0;
record2.CautionaryPercent = 0;
record2.AcceptablePercent = 0;
}
model2.Add(record2);
}
conn2.Close();
dataSnifferObject.dataSnifferParents = model2;
}
return(View(dataSnifferObject));
}
