private static void GetProductivityReport(OperatorReportItems ProductivityReport, chatEntities db)
{
var reports = db.OperatorProductivity(website: null, visitorID: null, startDate: null, endDate: null).ToList();
// var starters = db.Messages.GroupBy(x => x.ConversationID);
// SqlDataReader dr = sqlcomm.ExecuteReader();
// if (dr.Read())
foreach (var dr in reports)
{
OperatorReportViewModel opVM = new Models.OperatorReportViewModel();
opVM.ID = Convert.ToInt32(dr.OperatorID);
opVM.Name = Convert.ToString(dr.Name);
opVM.ProactiveAnswered = Convert.ToInt32(dr.ProactiveAnswered);
opVM.ProactiveSent = Convert.ToInt32(dr.ProactiveSent);
opVM.ProactiveResponseRate = Convert.ToInt32(dr.ProactiveResponseRate);
opVM.ReactiveAnswered = Convert.ToInt32(dr.ReactiveAnswered);
opVM.ReactiveReceived = Convert.ToInt32(dr.ReactiveReceived);
opVM.ReactiveResponseRate = Convert.ToInt32(dr.ReactiveResponseRate);
opVM.AverageChatLength = Convert.ToString(dr.AverageChatLength) + "mm";
// opVM.TotalChatLength = Convert.ToString(dr.TotalChatLength)+"mm";
TimeSpan t = TimeSpan.FromMinutes(Convert.ToDouble(dr.TotalChatLength));
string answer = string.Format("{0:D2}d:{1:D2}h:{2:D2}m",
t.Days,
t.Hours,
t.Minutes);
opVM.TotalChatLength = answer;
ProductivityReport.OperatorProductivity.Add(opVM);
}
}
public OperatorReportItems GetOperatorsReport()
{
OperatorReportItems ProductivityReport = new OperatorReportItems();
ProductivityReport.OperatorProductivity = new List <OperatorReportViewModel>();
ProductivityReport.Visitors = new List <Visitor>();
ProductivityReport.WebSite = new List <string>();
ProductivityReport.DateModels = new List <DateModel>();
try
{
//SqlConnection conn = new SqlConnection("Data Source=FAISALHABIB\\SQLEXPRESS;Initial Catalog=chat;User id=chat;Password=chat;");
//SqlCommand sqlcomm = new SqlCommand("exec dbo. ", conn);
using (var db = new chatEntities())
{
GetProductivityReport(ProductivityReport, db);
GetVisitors(ProductivityReport, db);
GetWebsites(ProductivityReport, db);
GetDates(ProductivityReport);
}
}
catch (Exception e)
{
//some message on window
}
return(ProductivityReport);
}
/// <summary>
/// Reports logic
/// </summary>
/// <returns></returns>
public ActionResult OperatorReport()
{
var productivityReport = new OperatorReportItems
{
OperatorProductivity = new List <OperatorReportViewModel>()
};
ViewBag.Message = "Operator Productivity Report";
productivityReport.OperatorProductivity = _repository.GetReports(new ReportFilter()).Select(r => new OperatorReportViewModel
{
ID = r.Id,
AverageChatLength = !string.IsNullOrEmpty(r.AverageChatLength) ? $"{r.AverageChatLength}m" : "-",
Name = r.Name,
ProactiveAnswered = r.ProactiveAnswered,
ProactiveResponseRate = r.ProactiveResponseRate,
ProactiveSent = r.ProactiveSent,
ReactiveAnswered = r.ReactiveAnswered,
ReactiveReceived = r.ReactiveReceived,
ReactiveResponseRate = r.ReactiveResponseRate,
TotalChatLength = _durationParser.Parse(r.TotalChatLength)
}).ToList();
return(View(productivityReport));
}
public OperatorReportItems GetOperatorsReport(string device, string web, string startDate, string endDate)
{
OperatorReportItems ProductivityReport = new OperatorReportItems();
ProductivityReport.OperatorProductivity = new List <OperatorReportViewModel>();
//ProductivityReport.Visitors = new List<Visitor>();
//ProductivityReport.WebSite = new List<string>();
//ProductivityReport.DateModels = new List<DateModel>();
try
{
using (var db = new chatEntities())
{
// Get filters data
DateTime?startDateTime = null;
if (startDate != "null")
{
var tt = startDate.Replace("0:0:0", "00:00:00");
// startDateTime = DateTime.ParseExact(startDate.Replace(' ','-'), "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture); // DateTime.Parse(startDate);
startDateTime = DateTime.ParseExact(tt, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
DateTime?endDateTime = null;
if (endDate != "null")
{
endDateTime = DateTime.ParseExact(endDate, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
if (web == "null")
{
web = null;
}
if (device == "null")
{
device = null;
}
var reports = db.OperatorProductivity(website: web, device: device, startDate: startDateTime, endDate: endDateTime).ToList();
GetProductivityReport(ProductivityReport, reports);
//GetVisitors(ProductivityReport, db);
//GetWebsites(ProductivityReport, db);
//GetDates(ProductivityReport);
}
}
catch (Exception e)
{
// will some message on window
}
return(ProductivityReport);
}
public ActionResult OperatorReport()
{
OperatorReportItems ProductivityReport = new OperatorReportItems();
ViewBag.Message = "Operator Productivity Report";
SqlCommand sqlcomm;
SqlDataReader dr;
List <string> websites = new List <string>();
List <string> devices = new List <string>();
try
{
sqlcomm = new SqlCommand("dbo.GetWebsites", _conn);
sqlcomm.CommandType = CommandType.StoredProcedure;
_conn.Open();
dr = sqlcomm.ExecuteReader();
while (dr.Read())
{
websites.Add(dr[0].ToString());
}
}
finally
{
_conn.Close();
}
try
{
sqlcomm = new SqlCommand("GetDevices", _conn);
sqlcomm.CommandType = CommandType.StoredProcedure;
_conn.Open();
dr = sqlcomm.ExecuteReader();
while (dr.Read())
{
devices.Add(dr[0].ToString());
}
_conn.Close();
}
finally
{
_conn.Close();
}
ViewBag.seletedListWebsites = new SelectList(websites);
ViewBag.seletedListDevices = new SelectList(devices);
return(View(ProductivityReport.OperatorProductivity));
}
public ActionResult GetOperationResult(DataFilters dataFilters)
{
OperatorReportItems ProductivityReport = new OperatorReportItems();
dataFilters = GetDateOutOfPreDefine(dataFilters);
if (dataFilters.ToDate >= dataFilters.FromDate)
{
try
{
SqlCommand sqlcomm = new SqlCommand("dbo.OperatorProductivityWithFilters ", _conn);
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.Add(new SqlParameter("@fromDate", (dataFilters.FromDate == DateTime.MinValue) ? (object)DBNull.Value : dataFilters.FromDate.ToString("yyyy-MM-dd")));
sqlcomm.Parameters.Add(new SqlParameter("@toDate", (dataFilters.ToDate == DateTime.MinValue) ? (object)DBNull.Value : dataFilters.ToDate.ToString("yyyy-MM-dd")));
sqlcomm.Parameters.Add(new SqlParameter("@website", dataFilters.Website ?? (object)DBNull.Value));
sqlcomm.Parameters.Add(new SqlParameter("@device", dataFilters.Device ?? (object)DBNull.Value));
_conn.Open();
SqlDataReader dr = sqlcomm.ExecuteReader();
while (dr.Read())
{
OperatorReportViewModel opVM = new OperatorReportViewModel();
opVM.ID = Convert.ToInt32(dr[0]);
opVM.Name = Convert.ToString(dr[1]);
opVM.ProactiveSent = Convert.ToInt32(dr[2]);
opVM.ProactiveAnswered = Convert.ToInt32(dr[3]);
opVM.ProactiveResponseRate = Convert.ToInt32(dr[4]);
opVM.ReactiveAnswered = Convert.ToInt32(dr[5]);
opVM.ReactiveReceived = Convert.ToInt32(dr[6]);
opVM.ReactiveResponseRate = Convert.ToInt32(dr[7]);
opVM.TotalChatLength = TimeSpan.FromMinutes(Convert.ToInt32(dr[8])).ToString(@"d\d' 'hh\h' 'mm\m");
opVM.AverageChatLength = TimeSpan.FromMinutes(Convert.ToInt32(dr[9])).ToString(@"mm\m");
ProductivityReport.OperatorProductivity.Add(opVM);
}
}
catch (SqlException ex)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Database Error, Connot complete the process"));
}
finally
{
_conn.Close();
}
return(Json(ProductivityReport.OperatorProductivity));
}
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Supplied Filter is incorrect"));
}
private void GetDates(OperatorReportItems productivityReport)
{
var tw = DateRangeRepository.ThisWeek(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "this week", startDate = tw.Start, endDate = tw.End
});
var lastweek = DateRangeRepository.LastWeek(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "last week", startDate = lastweek.Start, endDate = lastweek.End
});
var thisMonth = DateRangeRepository.ThisMonth(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "this month", startDate = thisMonth.Start, endDate = thisMonth.End
});
var lastMonth = DateRangeRepository.LastMonth(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "last month", startDate = lastMonth.Start, endDate = lastMonth.End
});
var thisYear = DateRangeRepository.ThisYear(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "this year", startDate = thisYear.Start, endDate = thisYear.End
});
var lastYear = DateRangeRepository.LastYear(DateTime.Now);
productivityReport.DateModels.Add(new DateModel()
{
name = "last year", startDate = lastYear.Start, endDate = lastYear.End
});
}
private static void GetProductivityReport(OperatorReportItems ProductivityReport, List <OperatorProductivity_Result> reports)
{
foreach (var dr in reports)
{
OperatorReportViewModel opVM = new Models.OperatorReportViewModel();
opVM.ID = Convert.ToInt32(dr.OperatorID);
opVM.Name = Convert.ToString(dr.Name);
opVM.ProactiveAnswered = Convert.ToInt32(dr.ProactiveAnswered);
opVM.ProactiveSent = Convert.ToInt32(dr.ProactiveSent);
opVM.ProactiveResponseRate = Convert.ToInt32(dr.ProactiveResponseRate);
opVM.ReactiveAnswered = Convert.ToInt32(dr.ReactiveAnswered);
opVM.ReactiveReceived = Convert.ToInt32(dr.ReactiveReceived);
opVM.ReactiveResponseRate = Convert.ToInt32(dr.ReactiveResponseRate);
opVM.AverageChatLength = Convert.ToString(dr.AverageChatLength) + "mm";
TimeSpan t = TimeSpan.FromMinutes(Convert.ToDouble(dr.TotalChatLength));
string answer = string.Format("{0:D2}d:{1:D2}h:{2:D2}m",
t.Days,
t.Hours,
t.Minutes);
opVM.TotalChatLength = answer;
ProductivityReport.OperatorProductivity.Add(opVM);
}
}
private void GetVisitors(OperatorReportItems productivityReport, chatEntities db)
{
var visitors = db.Visitor.ToList();
productivityReport.Visitors = visitors;
}
private void GetWebsites(OperatorReportItems productivityReport, chatEntities db)
{
productivityReport.WebSite = db.Conversation.Select(x => x.Website).Distinct().ToList();
}
