public List <StatDeals> SelectDealTypes()
{
try
{
SqlConnection con = new SqlConnection(DBOpenClose.conStr);
List <StatDeals> statDealList = new List <StatDeals>();
DBOpenClose.OpenConnection(con);
SqlCommand getCategory = new SqlCommand(
"SELECT Deals.DealType, Count(Deals.DealType) AS NumbersOfDealType " +
"FROM Deals GROUP BY Deals.DealType; ", con);
SqlDataReader reader = getCategory.ExecuteReader();
while (reader.Read())
{
StatDeals statDeals = new StatDeals();
statDeals.NumberOfDealTypes = Convert.ToInt32(reader["NumbersOfDealType"]);
statDeals.DealType = Convert.ToString(reader["DealType"]);
statDealList.Add(statDeals);
}
DBOpenClose.CloseConnection(con);
return(statDealList);
}
catch (Exception ex)
{
SqlConnection con = new SqlConnection(DBOpenClose.conStr);
DBOpenClose.CloseConnection(con);
List <StatDeals> statDealList = new List <StatDeals>();
Log.WriteFail(ex);
return(statDealList);
}
}
private void StatisticCalculations()
{
StatDeals tempStatDeals = new StatDeals();
statDeals.TotalDeals = DB.SelectAllDeals().Count;
List <StatDeals> statDealBucket = DB.SelectCustomersWithDeals();
//Sorts o(StatDeals).NumberOfDeals => is foreach in LINQ
statDealBucket = statDealBucket.OrderBy(o => o.NumberOfDeals).ToList();
tempStatDeals = statDealBucket[statDealBucket.Count - 1];
Customer tempCustomer = DB.SelectCustomer(tempStatDeals.statCustomerID);
statDeals.CustomerMostDeals = tempCustomer.Name;
tempStatDeals = statDealBucket[0];
statDeals.DealsActive = DB.SelectActiveDeals().Count;
statDeals.DealsInactive = DB.SelectInactiveDeals().Count;
statDealBucket = DB.SelectDealTypes();
statDealBucket = statDealBucket.OrderBy(o => o.NumberOfDealTypes).ToList();
tempStatDeals = statDealBucket[statDealBucket.Count - 1];
statDeals.MostUsedDealType = tempStatDeals.MostUsedDealType;
}
public List <StatDeals> SelectCustomersWithDeals()
{
try
{
SqlConnection con = new SqlConnection(DBOpenClose.conStr);
List <StatDeals> statDealList = new List <StatDeals>();
DBOpenClose.OpenConnection(con);
SqlCommand getCategory = new SqlCommand(
"SELECT Customer.[CustomerID], Count(Deals.DealsID) AS NumberOfDeals " +
"FROM Deals, Customer WHERE Customer.CustomerID = Deals.CustomerID " +
"GROUP BY Customer.[CustomerID]; ", con);
SqlDataReader reader = getCategory.ExecuteReader();
while (reader.Read())
{
StatDeals statDeals = new StatDeals();
statDeals.NumberOfDeals = Convert.ToInt32(reader["NumberOfDeals"]);
statDeals.statCustomerID = Convert.ToInt32(reader["CustomerID"]);
statDealList.Add(statDeals);
}
DBOpenClose.CloseConnection(con);
return(statDealList);
}
catch (Exception ex)
{
SqlConnection con = new SqlConnection(DBOpenClose.conStr);
DBOpenClose.CloseConnection(con);
List <StatDeals> statDealList = new List <StatDeals>();
Log.WriteFail(ex);
return(statDealList);
}
}
