public ActionResult ProductivityReportTable(ProductivityReportCriteria filter)
{
ActionResult result = null;
try
{
var model = LoadReport(filter);
result = PartialView(model);
}
catch (SqlException se)
{
Trace.WriteLine($"Failed to open the data store, due to exception:{Environment.NewLine}{se}");
result = new HttpStatusCodeResult(400, se.Message);
}
catch (Exception ex)
{
Trace.WriteLine($"Failed to load the report, due to exception:{Environment.NewLine}{ex}");
result = new HttpStatusCodeResult(500, "The report cannot be generated. Try again later.");
}
return(result);
}
private IEnumerable <OperatorReportItem> LoadReport(ProductivityReportCriteria filter)
{
var result = new List <OperatorReportItem>();
var cs = ConfigurationManager.ConnectionStrings["chat"].ConnectionString;
using (var conn = new SqlConnection(cs))
using (var sqlcomm = conn.CreateCommand())
{
sqlcomm.CommandText = "dbo.OperatorProductivityRef";
sqlcomm.CommandType = CommandType.StoredProcedure;
if (filter != null)
{
if (!string.IsNullOrWhiteSpace(filter.Device))
{
sqlcomm.Parameters
.AddWithValue("@Device", filter.Device)
.SqlDbType = SqlDbType.VarChar;
}
if (filter.EndDate.HasValue)
{
sqlcomm.Parameters
.AddWithValue("@EndDate", filter.EndDate.Value)
.SqlDbType = SqlDbType.DateTime2;
}
if (filter.StartDate.HasValue)
{
sqlcomm.Parameters
.AddWithValue("@StartDate", filter.StartDate)
.SqlDbType = SqlDbType.DateTime2;
}
if (!string.IsNullOrWhiteSpace(filter.Website))
{
sqlcomm.Parameters
.AddWithValue("@Website", filter.Website)
.SqlDbType = SqlDbType.VarChar;
}
}
conn.Open();
var dr = sqlcomm.ExecuteReader();
if (dr != null && dr.HasRows)
{
while (dr.Read())
{
var reportItem = new OperatorReportItem();
reportItem.ID = dr.GetInt32(0);
reportItem.Name = dr.GetString(1);
reportItem.ProactiveSent = dr.IsDBNull(2) ? 0 : (int)dr[2];
reportItem.ProactiveAnswered = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
reportItem.ReactiveReceived = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
reportItem.ReactiveAnswered = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
reportItem.TotalChatLengthSeconds = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
reportItem.AverageChatLengthSeconds = dr.IsDBNull(7) ? 0 : dr.GetInt32(7);
if (reportItem.ProactiveSent != 0)
{
reportItem.ProactiveResponseRate = Convert
.ToInt32((double)reportItem.ProactiveAnswered / reportItem.ProactiveSent * 100);
}
if (reportItem.ReactiveReceived != 0)
{
reportItem.ReactiveResponseRate = Convert
.ToInt32((double)reportItem.ReactiveAnswered / reportItem.ReactiveReceived * 100);
}
result.Add(reportItem);
}
}
}
return(result);
}
