public ILatency AverageLatency(bool prevMonth)
{
GenericInitialization gen = new GenericInitialization();
IndicatorsModel avgLatency = new IndicatorsModel();
try
{
using (gen.sqlConnection = new SqlConnection(gen.connectionString))
{
gen.sqlConnection.Open();
if (prevMonth == true)
{
gen.queryString = "SELECT AVG(DateDiff(s, Requested_Date, Confirmed_Date)/86400) as [Average_Days],AVG(DateDiff(s, Requested_Date,Confirmed_Date)%86400/3600) as [Average_Hours], AVG(DateDiff(s,Requested_Date,Confirmed_Date)%3600/60)as [Average_Minutes],AVG((DateDiff(s, Requested_Date, Confirmed_Date)%60)) as [Average_Seconds] FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Confirmed_Date IS NOT NULL AND DATEPART(m, Confirmed_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Confirmed_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate()))";
}
else
{
gen.queryString = "SELECT AVG(DateDiff(s, Requested_Date, Confirmed_Date)/86400) as [Average_Days],AVG(DateDiff(s, Requested_Date,Confirmed_Date)%86400/3600) as [Average_Hours], AVG(DateDiff(s,Requested_Date,Confirmed_Date)%3600/60)as [Average_Minutes],AVG((DateDiff(s, Requested_Date, Confirmed_Date)%60)) as [Average_Seconds] FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Confirmed_Date IS NOT NULL";
}
using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection))
{
using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader())
{
if (gen.sqlDataReader.HasRows)
{
while (gen.sqlDataReader.Read())
{
avgLatency.days = UInt16.Parse(gen.sqlDataReader["Average_Days"].ToString());
avgLatency.hours = UInt16.Parse(gen.sqlDataReader["Average_Hours"].ToString());
avgLatency.minutes = UInt16.Parse(gen.sqlDataReader["Average_Minutes"].ToString());
avgLatency.seconds = UInt16.Parse(gen.sqlDataReader["Average_Seconds"].ToString());
}
}
}
}
}
}
catch (SqlException ex)
{
return(avgLatency); //Error
}
catch (Exception ex)
{
return(avgLatency); //Error
}
return(avgLatency); // No Value
}
public IRatings Ratings(bool prevMonth)
{
GenericInitialization gen = new GenericInitialization();
IndicatorsModel ratings = new IndicatorsModel();
try
{
using (gen.sqlConnection = new SqlConnection(gen.connectionString))
{
gen.sqlConnection.Open();
if (prevMonth == false)
{
gen.queryString = "SELECT AVG(Rating) AS Rating FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Status]='Accepted'";
}
else
{
gen.queryString = "SELECT AVG(Rating) AS Rating FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Status]='Accepted' AND DATEPART(m, Confirmed_Date) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, Confirmed_Date) = DATEPART(yyyy, DATEADD(m, -1, getdate()))";
}
using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection))
{
using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader())
{
if (gen.sqlDataReader.HasRows)
{
while (gen.sqlDataReader.Read())
{
ratings.ratingCount = float.Parse(gen.sqlDataReader["Rating"].ToString());
}
}
}
}
}
}
catch (SqlException ex)
{
return(ratings); //Error
}
catch (Exception ex)
{
return(ratings); //Error
}
return(ratings); // No Value
}