public ConversionTotalAndAvgData GetConversionStatsData([FromBody]Filter filters)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
var userName = HttpContext.Current.GetUserName();
Filter f = new Filter() { filters = filters.filters, range = filters.range };
#pragma warning disable CS0436 // Type conflicts with imported type
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getConversionStatistics]", userName);
#pragma warning restore CS0436 // Type conflicts with imported type
sqlComm.Connection = sqlCon;
ConversionTotalAndAvgData totals = new ConversionTotalAndAvgData();
sqlCon.Open();
try
{
SqlDataReader reader = sqlComm.ExecuteReader();
if (reader.Read())
{
totals = ConversionTotalAndAvgData.Create(reader);
}
}
catch (Exception ex)
{
throw ex;
}
return totals;
}
}
public ConversionRangeDataResponse GetConversionPerformanceData([FromBody]Filter filters)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
string userName = HttpContext.Current.GetUserName();
Filter f = new Filter() { filters = filters.filters, range = filters.range };
#pragma warning disable CS0436 // Type conflicts with imported type
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getConversionPerformanceData]", userName);
#pragma warning restore CS0436 // Type conflicts with imported type
sqlComm.Connection = sqlCon;
ConversionRangeDataResponse performanceData = new ConversionRangeDataResponse();
sqlCon.Open();
try
{
SqlDataReader reader = sqlComm.ExecuteReader();
performanceData = ConversionRangeDataResponse.Create(reader);
}
catch (Exception ex)
{
throw ex;
}
return performanceData;
}
}
public dynamic ExportWebSiteStatistic(Filter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(filters, "[GetWebSiteStatisticApi]", userName);
sqlComm.CommandTimeout = int.MaxValue;
sqlComm.Connection = sqlCon;
WebSiteAverage webSiteAverage = new WebSiteAverage();
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
webSiteAverage = new WebSiteAverage()
{
total = reader.IsDBNull(reader.GetOrdinal("total")) ? 0 : int.Parse(reader.GetValue(reader.GetOrdinal("total")).ToString()),
compliant = reader.IsDBNull(reader.GetOrdinal("compliant")) ? 0 : int.Parse(reader.GetValue(reader.GetOrdinal("compliant")).ToString()),
bad = reader.IsDBNull(reader.GetOrdinal("bad")) ? 0 : int.Parse(reader.GetValue(reader.GetOrdinal("bad")).ToString()),
nonCompliant = reader.IsDBNull(reader.GetOrdinal("nonCompliant")) ? 0 : int.Parse(reader.GetValue(reader.GetOrdinal("nonCompliant")).ToString()),
};
}
//return webSiteAverage;
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Total", propValue = "total", propPosition = 1
},
new PropertieName {
propName = "Compliant", propValue = "compliant", propPosition = 2
},
new PropertieName {
propName = "Non compliant", propValue = "nonCompliant", propPosition = 3
},
new PropertieName {
propName = "Bad", propValue = "bad", propPosition = 4
}
};
List <ExportWebSiteStatisticModel> exportWebSiteStatistic = new List <ExportWebSiteStatisticModel>();
exportWebSiteStatistic.Add(new ExportWebSiteStatisticModel()
{
bad = webSiteAverage.bad,
compliant = webSiteAverage.compliant,
nonCompliant = webSiteAverage.nonCompliant,
total = webSiteAverage.total
});
ExportHelper.Export(propNames, exportWebSiteStatistic, "WebStat" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Second.ToString() + ".xlsx", "WebSiteStatistic", userName);
}
catch (Exception ex)
{
throw ex;
}
}
return("success");
}
public ConversionChartData GetChartData([FromBody]ConversionChartFilter filters)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
string userName = HttpContext.Current.GetUserName();
Filter f = new Filter() { filters = filters.filters, range = filters.range };
#pragma warning disable CS0436 // Type conflicts with imported type
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getConversionChartData]", userName);
#pragma warning restore CS0436 // Type conflicts with imported type
if (filters.chartType != null && filters.chartType != "")
{
sqlComm.Parameters.AddWithValue("@chartType", filters.chartType);
}
sqlComm.Parameters.AddWithValue("@pagerows", filters.paging.pagerows);
sqlComm.Parameters.AddWithValue("@pagenum", filters.paging.pagenum);
if (filters.sorting?.sortBy != null && filters.sorting.sortOrder != null && filters.sorting.sortBy != "" && filters.sorting.sortOrder != "")
{
sqlComm.Parameters.AddWithValue("@OrderByColumn", filters.sorting.sortBy);
sqlComm.Parameters.AddWithValue("@sortOrder", (filters.sorting.sortOrder != "desc"));
}
sqlComm.Connection = sqlCon;
ConversionChartData chartData = new ConversionChartData();
sqlCon.Open();
try
{
SqlDataReader reader = sqlComm.ExecuteReader();
chartData = ConversionChartData.Create(reader);
}
catch (Exception ex)
{
throw ex;
}
return chartData;
}
}
public dynamic SectionsExport(Filter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
#pragma warning disable CS0436 // Type conflicts with imported type
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(filters, "[GetSectionScores]", userName);
#pragma warning restore CS0436 // Type conflicts with imported type
sqlComm.Connection = sqlCon;
List <CoachingQueue> coachingQueueLst = new List <CoachingQueue>();
List <CoachingQueueCallDetails> cqcd = new List <CoachingQueueCallDetails>();
CoachingQueueResponceData cq = new CoachingQueueResponceData();
var callShortInfo = new CallShortInfov2();
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
var sectioInfoRaw = new List <SectionInfoRaw>();
try
{
sectioInfoRaw = SectionInfoRaw.Create(reader);
}
catch (Exception ex)
{
throw ex;
}
//return scorecardInfo;
List <SectionsExportModel> sectionsExportModels = new List <SectionsExportModel>();
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Scorecard Name", propValue = "scorecardName", propPosition = 1
},
new PropertieName {
propName = "Section Name", propValue = "sectionName", propPosition = 2
},
new PropertieName {
propName = "Question Name", propValue = "questionName", propPosition = 3
},
new PropertieName {
propName = "isLinked", propValue = "isLinked", propPosition = 4
},
new PropertieName {
propName = "Total Right", propValue = "totalRight", propPosition = 5
},
new PropertieName {
propName = "Right Score, %", propValue = "rightScore", propPosition = 6
},
new PropertieName {
propName = "Total Wrong", propValue = "totalWrong", propPosition = 7
},
new PropertieName {
propName = "Wrong Score, %", propValue = "wrongScore", propPosition = 8
},
new PropertieName {
propName = "Total", propValue = "total", propPosition = 9
}
};
foreach (var item in sectioInfoRaw)
{
sectionsExportModels.Add(new SectionsExportModel
{
scorecardName = item.scorecardName,
sectionName = item.sectionName,
questionName = item.questionShortName,
isLinked = item.isLinked == true ? "Yes" : "No",
totalRight = item.totalRight,
totalWrong = item.totalWrong,
total = item.totalRight + item.totalWrong,
rightScore = (item.totalRight + item.totalWrong) == 0 ? 0 : Math.Round((double)item.totalRight / (item.totalRight + item.totalWrong) * 100, 2),
wrongScore = (item.totalRight + item.totalWrong) == 0 ? 0 : Math.Round((double)item.totalWrong / (item.totalRight + item.totalWrong) * 100, 2)
});
}
ExportHelper.Export(propNames, sectionsExportModels, "Sections" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Millisecond.ToString() + ".xlsx", "Sections", userName);
return("sucess");
}
catch (Exception ex)
{
throw ex;
}
}
}
public dynamic AgentRankingExport(AverageFilter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
string p = Path.Combine(HostingEnvironment.MapPath(@"~\export\"));
if (!Directory.Exists(p))
{
Directory.CreateDirectory(p);
}
Filter f = new Filter()
{
filters = filters.filters, range = filters.range
};
SqlCommand sqlComm = new SqlCommand();
if (filters.filters.missedItems != null && filters.filters.missedItems.Count != 0)
{
sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getAgentRankingJson_v3_ExportForMissedPoints]", userName, filters.comparison);
}
else
{
sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getAgentRankingJson_v2]", userName, filters.comparison);
}
sqlComm.Connection = sqlCon;
var aRankingResponseData = new AgentRankingResponseData();
List <AgentMissedPoint> agentRankingInfolst = new List <AgentMissedPoint>();
List <Agent> agentRankinglst = new List <Agent>();
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
try
{
while (reader.Read())
{
agentRankingInfolst.Add(new AgentMissedPoint
{
agentId = reader.GetValue(reader.GetOrdinal("agent")).ToString(),
questionShortName = reader.GetValue(reader.GetOrdinal("q_short_name")).ToString(),
missedCalls = int.Parse(reader.GetValue(reader.GetOrdinal("missed")).ToString()),
totalCalls = int.Parse(reader.GetValue(reader.GetOrdinal("total")).ToString()),
isComposite = int.Parse(reader.GetValue(reader.GetOrdinal("hasTemplate")).ToString()) == 1,
isLinked = int.Parse(reader.GetValue(reader.GetOrdinal("isLinked")).ToString()) == 1,
questionType = reader.GetValue(reader.GetOrdinal("questionType")).ToString(),
questionId = reader.IsDBNull(reader.GetOrdinal("question_id")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("question_id")).ToString())
});
}
}
catch (Exception ex) { throw ex; }
if (reader.NextResult())
{
while (reader.Read())
{
try
{
var temp_ranking = new Agent()
{
id = reader.GetValue(reader.GetOrdinal("agentID")).ToString(),
name = reader.GetValue(reader.GetOrdinal("AgentName")).ToString(),
groupNames = new List <string>(),
averageScore = reader.IsDBNull(reader.GetOrdinal("averageScore"))? 0:decimal.Parse(reader.GetValue(reader.GetOrdinal("averageScore")).ToString()),
previousAverageScore = reader.IsDBNull(reader.GetOrdinal("previousAverageScore")) ? 0 : decimal.Parse(reader.GetValue(reader.GetOrdinal("previousAverageScore")).ToString()),
totalCalls = reader.IsDBNull(reader.GetOrdinal("totalCalls")) ? 0 : int.Parse(reader.GetValue(reader.GetOrdinal("totalCalls")).ToString()),
totalBadCalls = reader.IsDBNull(reader.GetOrdinal("totalBadCalls")) ? (int?)null : (int?)reader.GetValue(reader.GetOrdinal("totalBadCalls")),
earliestCallDate = reader.IsDBNull(reader.GetOrdinal("earlier")) ? (DateTime?)null : (DateTime?)reader.GetDateTime(reader.GetOrdinal("earlier")),
// DateTime.Parse(reader.GetValue(reader.GetOrdinal("earlier")).ToString()),
};
temp_ranking.top3MissedPoints = (from val in agentRankingInfolst where val.agentId.Trim().Equals(temp_ranking.id.Trim()) select val).ToList();
agentRankinglst.Add(temp_ranking);
}
catch (Exception ex) { throw ex; }
}
}
List <UserGroupInfo> ugi = new List <UserGroupInfo>();
if (reader.NextResult())
{
while (reader.Read())
{
try
{
ugi.Add(new UserGroupInfo()
{
groupname = reader.GetValue(reader.GetOrdinal("user_group")).ToString(),
username = reader.GetValue(reader.GetOrdinal("Agent")).ToString()
});
}
catch (Exception ex)
{
throw ex;
}
}
}
foreach (var a in agentRankinglst)
{
a.groupNames = (from val in ugi where a.name.Equals(val.username) select val.groupname).ToList();
}
//
#region Export
//EXPORT Realization
aRankingResponseData.agents = agentRankinglst;
var propNames = new List <PropertieName>();
PropertieName name = new PropertieName();
List <ExportAgentRanking> exportAgentRanking = new List <ExportAgentRanking>();
List <ExportAgentRankingTopMissedPoints> exportAgentRankingTopMissedPoints = new List <ExportAgentRankingTopMissedPoints>();
if (f.filters.missedItems != null && f.filters.missedItems.Count == 1)
{
propNames.Add(new PropertieName {
propName = "Agent Name", propValue = "name", propPosition = 1
});
propNames.Add(new PropertieName {
propName = "Missed Item", propValue = "questionName", propPosition = 2
});
propNames.Add(new PropertieName {
propName = "Call Count", propValue = "callCount", propPosition = 3
});
propNames.Add(new PropertieName {
propName = "Missed Calls", propValue = "missedCalls", propPosition = 4
});
propNames.Add(new PropertieName {
propName = "Missed Percent", propValue = "missedPercent", propPosition = 5
});
//List<int> missed = new List<int>();
//int z = 0;
foreach (var item in aRankingResponseData.agents)
{
item.questionName = new List <string>();
foreach (var i in item.top3MissedPoints)
{
if (Convert.ToInt32(filters.filters.missedItems[0]) == i.questionId)
{
//missed.Add(i.missedCalls);
item.missedCalls = i.missedCalls;
item.totalCalls = i.totalCalls;
item.questionName.Add(i.questionShortName);
}
}
//z++;
}
foreach (var item in aRankingResponseData.agents)
{
exportAgentRankingTopMissedPoints.Add(new ExportAgentRankingTopMissedPoints
{
name = item.name,
questionName = item.questionName[0],
callCount = item.totalCalls,
missedCalls = item.missedCalls,
missedPercent = Math.Round((double)(item.missedCalls * 100) / item.totalCalls) + "%" //-100 - ((item.averageScore * 100) / item.previousAverageScore)
});
}
ExportHelper.Export(propNames, exportAgentRankingTopMissedPoints, "AgentRanking " + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Millisecond.ToString() + ".xlsx", "AgentRanking", userName);
}
else
{
propNames.Add(new PropertieName {
propName = "Agent Name", propValue = "name", propPosition = 1
});
propNames.Add(new PropertieName {
propName = "Start Date", propValue = "startDate", propPosition = 2
});
propNames.Add(new PropertieName {
propName = "Score", propValue = "score", propPosition = 3
});
propNames.Add(new PropertieName {
propName = "Group Name", propValue = "groupName", propPosition = 4
});
propNames.Add(new PropertieName {
propName = "Delta", propValue = "delta", propPosition = 5
});
propNames.Add(new PropertieName {
propName = "Total Calls", propValue = "totalCalls", propPosition = 6
});
propNames.Add(new PropertieName {
propName = "Top Missed Points", propValue = "top3Agents", propPosition = 7
});
foreach (var item in aRankingResponseData.agents)
{
List <string> topAgents = new List <string>();
foreach (var i in item.top3MissedPoints)
{
if (item.id == i.agentId)
{
topAgents.Add((new StringBuilder().Append(i.questionShortName + ", missed " + i.missedCalls + " of " + i.totalCalls + ";").ToString()));
}
}
exportAgentRanking.Add(new ExportAgentRanking
{
name = item.name,
startDate = item.earliestCallDate,
score = item.averageScore,
groupName = ExportCodeHelper.GetCSVFromList(item.groupNames),
delta = ((item.averageScore - item.previousAverageScore) % 100) + "%",
totalCalls = item.totalCalls,
top3Agents = ExportCodeHelper.GetCSVFromList(topAgents)//GetCSVFromList(topAgents) as string
});
}
ExportHelper.Export(propNames, exportAgentRanking, "AgentRanking " + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Millisecond.ToString() + ".xlsx", "AgentRanking", userName);
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
return("success");
}
}
public dynamic ExportTopQaMissedItems(AverageFilter filters, string userName)
{
Filter f = new Filter()
{
filters = filters.filters, range = filters.range
};
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
if (filters.filters.badCallsOnly == false)
{
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getTopqaMissedItemsJson_v2]", userName, filters.comparison);
sqlComm.Connection = sqlCon;
PageFiltersData pageFiltersData = new PageFiltersData();
sqlCon.Open();
TopMissedItemsResponseData topMissed = new TopMissedItemsResponseData()
{
missedItems = new List <MissedItem>()
};
try
{
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
try
{
topMissed.missedItems.Add(new MissedItem()
{
questionId = int.Parse(reader.GetValue(reader.GetOrdinal("questionId")).ToString()),
questionShortName = (reader.GetValue(reader.GetOrdinal("questionShortName")).ToString()),
scorecardName = (reader.GetValue(reader.GetOrdinal("scorecardName")).ToString()),
totalCalls = int.Parse(reader.GetValue(reader.GetOrdinal("totalCalls")).ToString()),
missedCalls = int.Parse(reader.GetValue(reader.GetOrdinal("missedCalls")).ToString()),
questionSectionName = reader.GetValue(reader.GetOrdinal("sectionName")).ToString(),
isComposite = bool.Parse(reader.GetValue(reader.GetOrdinal("hasTemplate")).ToString()),
isLinked = bool.Parse(reader.GetValue(reader.GetOrdinal("isLinked")).ToString()),
questionType = reader.GetValue(reader.GetOrdinal("questionType")).ToString(),
comparedMissedCalls = int.Parse(reader.GetValue(reader.GetOrdinal("comparedMissedCalls")).ToString()),
comparedTotalCalls = int.Parse(reader.GetValue(reader.GetOrdinal("comparedTotalCalls")).ToString()),
});
}
catch (Exception ex) { }
}
reader.NextResult();
List <MissedItemAgentInfo> lst = new List <MissedItemAgentInfo>();
while (reader.Read())
{
try
{
lst.Add(new MissedItemAgentInfo()
{
questionId = int.Parse(reader.GetValue(reader.GetOrdinal("questionId")).ToString()),
name = reader.GetValue(reader.GetOrdinal("reviewer")).ToString(),
totalCalls = int.Parse(reader.GetValue(reader.GetOrdinal("total_calls")).ToString()),
missedCalls = int.Parse(reader.GetValue(reader.GetOrdinal("number_missed")).ToString()),
});
}
catch (Exception ex) { }
}
foreach (var item in topMissed.missedItems)
{
item.top3Agents = new List <MissedItemAgentInfo>();
item.top3Agents.AddRange((from v in lst where v.questionId == item.questionId select v).ToList());
}
//return topMissed;
List <TopMissedItemsExportModel> topMissedItemsExportModel = new List <TopMissedItemsExportModel>();
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Missed Point", propValue = "questionShortName", propPosition = 1
},
new PropertieName {
propName = "Section", propValue = "questionSectionName", propPosition = 2
},
new PropertieName {
propName = "Scorecard", propValue = "scorecardName", propPosition = 3
},
new PropertieName {
propName = "Number missed", propValue = "missedCalls", propPosition = 4
},
new PropertieName {
propName = "Total calls", propValue = "totalCalls", propPosition = 5
},
new PropertieName {
propName = "Occurrence", propValue = "occurrence", propPosition = 6
},
new PropertieName {
propName = "Delta", propValue = "delta", propPosition = 7
},
new PropertieName {
propName = "Top 3 agents", propValue = "top3Agents", propPosition = 8
}
};
foreach (var item in topMissed.missedItems)
{
List <string> topAgents = new List <string>();
foreach (var i in item.top3Agents)
{
if (item.questionId == i.questionId)
{
topAgents.Add((new StringBuilder().Append(i.name + i.missedCalls + "/" + i.totalCalls + ";").ToString()));
}
}
if (item.comparedTotalCalls == 0)
{
topMissedItemsExportModel.Add(new TopMissedItemsExportModel
{
questionShortName = item.questionShortName,
questionSectionName = item.questionSectionName,
scorecardName = item.scorecardName,
missedCalls = item.missedCalls,
totalCalls = item.totalCalls,
occurrence = (float)Math.Round((float)((float)item.missedCalls / (float)item.totalCalls) * 100),
delta = (float)Math.Round((float)((float)item.missedCalls / (float)item.totalCalls) * 100), //(item.comparedMissedCalls / item.comparedTotalCalls),
top3Agents = ExportCodeHelper.GetCSVFromList(topAgents)
});
}
else
{
topMissedItemsExportModel.Add(new TopMissedItemsExportModel
{
questionShortName = item.questionShortName,
questionSectionName = item.questionSectionName,
scorecardName = item.scorecardName,
missedCalls = item.missedCalls,
totalCalls = item.totalCalls,
occurrence = (float)Math.Round(((float)item.missedCalls / (float)item.totalCalls) * 100),
delta = (float)Math.Round(((float)((float)item.missedCalls / (float)item.totalCalls) * 100) - ((float)((float)item.comparedMissedCalls / (float)item.comparedTotalCalls) * 100)),//(item.missedCalls / item.totalCalls) * 100,
top3Agents = ExportCodeHelper.GetCSVFromList(topAgents)
});
}
}
ExportHelper.Export(propNames, topMissedItemsExportModel, "TopQaMissed" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Second.ToString() + ".xlsx", "TopQaMissedPoints", userName);
}
catch (Exception ex)
{
throw ex;
}
}
return("success");
}
}
public dynamic ExportGroupPerformance(Filter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
Filter f = new Filter()
{
filters = filters.filters, range = filters.range
};
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "getGroupPerfAPI", userName);
sqlComm.Connection = sqlCon;
var gpl = new List <GroupPerformance>();
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
try
{
PeriodPerformance period = new PeriodPerformance()
{
callsCount = int.Parse(reader.GetValue(reader.GetOrdinal("num_calls")).ToString()),
score = float.Parse(reader.GetValue(reader.GetOrdinal("avg_score")).ToString())
};
PeriodPerformance prviousPeriod = new PeriodPerformance()
{
callsCount = int.Parse(reader.GetValue(reader.GetOrdinal("prev_num_calls")).ToString()),
score = float.Parse(reader.GetValue(reader.GetOrdinal("prev_avg_score")).ToString())
};
GroupInfo groupInfo = new GroupInfo()
{
id = reader.GetValue(reader.GetOrdinal("agent_group")).ToString(), name = reader.GetValue(reader.GetOrdinal("agent_group")).ToString()
};
gpl.Add(new GroupPerformance
{
groupInfo = groupInfo,
scorecardName = reader.GetValue(reader.GetOrdinal("scorecard_name")).ToString(),
currentPeriod = period,
previousPeriod = prviousPeriod
});
}
catch
{
}
}
;
//return gpl;
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Group name", propValue = "name", propPosition = 1
},
new PropertieName {
propName = "Scorecard name", propValue = "scorecardName", propPosition = 2
},
new PropertieName {
propName = "Score current period", propValue = "currentScore", propPosition = 3
},
new PropertieName {
propName = "Calls current period", propValue = "currentCalls", propPosition = 4
},
new PropertieName {
propName = "Score previous period", propValue = "previousScore", propPosition = 5
},
new PropertieName {
propName = "Calls previous period", propValue = "previousCalls", propPosition = 6
},
new PropertieName {
propName = "Delta score", propValue = "delta", propPosition = 7
}
};
List <ExportGroupPerformanceModel> exportGroupPerformanceModels = new List <ExportGroupPerformanceModel>();
foreach (var item in gpl)
{
exportGroupPerformanceModels.Add(new ExportGroupPerformanceModel
{
name = item.groupInfo.name,
scorecardName = item.scorecardName,
currentScore = item.currentPeriod.score,
currentCalls = item.currentPeriod.callsCount,
previousCalls = item.previousPeriod.callsCount,
previousScore = item.previousPeriod.score,
delta = (item.currentPeriod.score - item.previousPeriod.score) + "%"
});
}
ExportHelper.Export(propNames, exportGroupPerformanceModels, "GroupPerformance" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Second.ToString() + ".xlsx", "GroupPerformance", userName);
}
catch (Exception ex)
{
throw ex;
}
}
return("success");
}
public dynamic CalibrationSectionExport(Filter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(filters, "[GetCalibratorSectionScores]", userName);
sqlComm.Connection = sqlCon;
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
var sectioInfoRaw = new List <SectionInfoRaw>();
while (reader.Read())
{
try
{
sectioInfoRaw.Add(SectionInfoRaw.CreateRecord(reader));
}
catch
{
}
}
List <SectionsExportModel> sectionsExportModels = new List <SectionsExportModel>();
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Scorecard Name", propValue = "scorecardName", propPosition = 1
},
new PropertieName {
propName = "Section Name", propValue = "sectionName", propPosition = 2
},
new PropertieName {
propName = "Question Name", propValue = "questionName", propPosition = 3
},
new PropertieName {
propName = "isLinked", propValue = "isLinked", propPosition = 4
},
new PropertieName {
propName = "Total Right", propValue = "totalRight", propPosition = 5
},
new PropertieName {
propName = "Right Score, %", propValue = "rightScore", propPosition = 6
},
new PropertieName {
propName = "Total Wrong", propValue = "totalWrong", propPosition = 7
},
new PropertieName {
propName = "Wrong Score, %", propValue = "wrongScore", propPosition = 8
},
new PropertieName {
propName = "Total", propValue = "total", propPosition = 9
}
};
foreach (var item in sectioInfoRaw)
{
// var tot = item.totalRight = item.totalWrong;
sectionsExportModels.Add(new SectionsExportModel
{
scorecardName = item.scorecardName,
sectionName = item.sectionName,
questionName = item.questionShortName,
isLinked = item.isLinked == true ? "Yes" : "No",
totalRight = item.totalRight,
totalWrong = item.totalWrong,
total = item.totalRight + item.totalWrong,
rightScore = (item.totalRight + item.totalWrong) == 0 ? 0 :Math.Round((double)item.totalRight / (item.totalRight + item.totalWrong) * 100, 2),
wrongScore = (item.totalRight + item.totalWrong) == 0 ? 0 : Math.Round((double)item.totalWrong / (item.totalRight + item.totalWrong) * 100, 2)
});
}
ExportHelper.Export(propNames, sectionsExportModels, "CalibrationSection" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Second.ToString() + ".xlsx", "CalibrationSection", userName);
return("sucess");
}
catch (Exception ex)
{
throw ex;
}
}
}
public dynamic ExporCallsLeft(Filter filters, string userName)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
Filter f = new Filter()
{
filters = filters.filters, range = filters.range
};
SqlCommand sqlComm = DashboardHelpers.GetFiltersParameters(f, "GetCallsLeftList", userName);
sqlComm.Connection = sqlCon;
var callsLeftLst = new List <CallsLeft>();
var pendingCalls = new List <PendingCall>();
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
try
{
ScorecardInfo sc = new ScorecardInfo()
{
scorecardId = int.Parse(reader.GetValue(reader.GetOrdinal("scorecardId")).ToString()),
scorecardName = reader.GetValue(reader.GetOrdinal("scorecardName")).ToString()
};
CallsLeft callsLeft = new CallsLeft()
{
badCalls = int.Parse(reader.GetValue(reader.GetOrdinal("badCalls")).ToString()),
pending = int.Parse(reader.GetValue(reader.GetOrdinal("pending")).ToString()),
pendingNotReady = int.Parse(reader.GetValue(reader.GetOrdinal("pending_not_ready")).ToString()),
pendingReady = int.Parse(reader.GetValue(reader.GetOrdinal("pending_ready")).ToString()),
reviewed = int.Parse(reader.GetValue(reader.GetOrdinal("reviewed")).ToString()),
callDate = reader.IsDBNull(reader.GetOrdinal("callDate")) ? (DateTime?)null : (DateTime?)reader.GetDateTime(reader.GetOrdinal("callDate")),
//DateTime.Parse(reader.GetValue(reader.GetOrdinal("callDate")).ToString()),
scorecard = sc,
pendingCalls = new List <PendingCall>()
};
callsLeftLst.Add(callsLeft);
}
catch (Exception ex)
{
throw ex;
}
}
if (reader.NextResult())
{
while (reader.Read())
{
try
{
PendingCall sc = new PendingCall()
{
scorecardId = int.Parse(reader.GetValue(reader.GetOrdinal("scorecardId")).ToString()),
callDate = DateTime.Parse(reader.GetValue(reader.GetOrdinal("callDate")).ToString()),
receiveDate = DateTime.Parse(reader.GetValue(reader.GetOrdinal("receiveDate")).ToString()),
};
pendingCalls.Add(sc);
}
catch { }
}
}
foreach (var call in callsLeftLst)
{
call.pendingCalls = (from a in pendingCalls where a.scorecardId == call.scorecard.scorecardId && a.callDate == call.callDate select a).ToList();
}
//return callsLeftLst;
var propNames = new List <PropertieName>
{
new PropertieName {
propName = "Scorecard Name", propValue = "scorecardName", propPosition = 1
},
new PropertieName {
propName = "Scorecard Id", propValue = "scorecardId", propPosition = 2
},
new PropertieName {
propName = "Call date", propValue = "callDate", propPosition = 3
},
new PropertieName {
propName = "Bad calls", propValue = "badCalls", propPosition = 4
},
new PropertieName {
propName = "Reviewed calls", propValue = "reviewed", propPosition = 5
},
new PropertieName {
propName = "Pending calls", propValue = "pendingCalls", propPosition = 6
}
};
List <ExportCallsLeftModel> exportCallsLeftModel = new List <ExportCallsLeftModel>();
foreach (var i in callsLeftLst)
{
exportCallsLeftModel.Add(new ExportCallsLeftModel
{
scorecardName = i.scorecard.scorecardName,
scorecardId = i.scorecard.scorecardId,
callDate = i.callDate,
badCalls = i.badCalls,
reviewed = i.reviewed,
pendingCalls = i.pendingNotReady + "/" + i.pendingReady
});
}
ExportHelper.Export(propNames, exportCallsLeftModel, "CallsLeft" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Millisecond.ToString() + ".xlsx", "CallsLeft", userName);
}
catch (Exception ex)
{
throw ex;
}
return("succsess");
}
}
public dynamic CoachingQueueExport(Filter filters, string userName, List <AvailableColumns> columns)
{
using (SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CC_ProdConn"].ConnectionString))
{
string p = Path.Combine(HostingEnvironment.MapPath(@"~\export\"));
if (!Directory.Exists(p))
{
Directory.CreateDirectory(p);
}
Filter f = new Filter()
{
filters = filters.filters, range = filters.range
};
SqlCommand sqlComm = new SqlCommand();
sqlComm = DashboardHelpers.GetFiltersParameters(f, "[getCoachingQueueJsonv2]", userName);
List <ExportCoachingQueueColumns> exportCoachingQueueColumns = new List <ExportCoachingQueueColumns>();
sqlComm.Connection = sqlCon;
try
{
sqlCon.Open();
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
try
{
exportCoachingQueueColumns.Add(new ExportCoachingQueueColumns
{
agentName = reader.IsDBNull(reader.GetOrdinal("agentName")) ? null : reader.GetValue(reader.GetOrdinal("agentName")).ToString(),
callDate = reader.IsDBNull(reader.GetOrdinal("callDate")) ? (DateTime?)null : DateTime.Parse(reader.GetValue(reader.GetOrdinal("callDate")).ToString()),
agentScore = reader.IsDBNull(reader.GetOrdinal("total_score")) ? (float?)null : float.Parse(reader.GetValue(reader.GetOrdinal("total_score")).ToString()),
assignedToRole = reader.IsDBNull(reader.GetOrdinal("AssignedToRole")) ? (string)null : reader.GetValue(reader.GetOrdinal("AssignedToRole")).ToString(),
callType = reader.IsDBNull(reader.GetOrdinal("calltype")) ? (string)null : reader.GetValue(reader.GetOrdinal("calltype")).ToString(),
callId = reader.IsDBNull(reader.GetOrdinal("callId")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("callId")).ToString()),
callFailed = (reader.IsDBNull(reader.GetOrdinal("callFailed")) ? (bool?)null : bool.Parse(reader.GetValue(reader.GetOrdinal("callFailed")).ToString())) == true ? "Yes" : "No",
callAudioLength = reader.IsDBNull(reader.GetOrdinal("callAudioLength")) ? (float?)null : float.Parse(reader.GetValue(reader.GetOrdinal("callAudioLength")).ToString()),
callAudioUrl = reader.IsDBNull(reader.GetOrdinal("callAudioUrl")) ? (string)null : reader.GetValue(reader.GetOrdinal("callAudioUrl")).ToString(),
cali_id = reader.IsDBNull(reader.GetOrdinal("cali_id")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("cali_id")).ToString()),
calibratorName = reader.IsDBNull(reader.GetOrdinal("calibratorName")) ? (string)null : reader.GetValue(reader.GetOrdinal("calibratorName")).ToString(),
callReviewStatus = reader.IsDBNull(reader.GetOrdinal("callReviewStatus")) ? (string)null : reader.GetValue(reader.GetOrdinal("callReviewStatus")).ToString(),
missedItemsCount = reader.IsDBNull(reader.GetOrdinal("missedItemsCount")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("missedItemsCount")).ToString()),
notificationId = reader.IsDBNull(reader.GetOrdinal("NotificationID")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("NotificationID")).ToString()),
reviewDate = reader.IsDBNull(reader.GetOrdinal("reviewDate")) ? (DateTime?)null : DateTime.Parse(reader.GetValue(reader.GetOrdinal("reviewDate")).ToString()),
reviewerUserRole = reader.IsDBNull(reader.GetOrdinal("reviewerUserRole")) ? (string)null : reader.GetValue(reader.GetOrdinal("reviewerUserRole")).ToString(),
reviewerName = reader.IsDBNull(reader.GetOrdinal("reviewerName")) ? (string)null : reader.GetValue(reader.GetOrdinal("reviewerName")).ToString(),
reviewCommentsPresent = (reader.IsDBNull(reader.GetOrdinal("reviewCommentsPresent")) ? (bool?)null : bool.Parse(reader.GetValue(reader.GetOrdinal("reviewCommentsPresent")).ToString())) == true ? "Yes" : "No",
notificationCommentsPresent = (reader.IsDBNull(reader.GetOrdinal("notificationCommentsPresent")) ? (bool?)null : bool.Parse(reader.GetValue(reader.GetOrdinal("notificationCommentsPresent")).ToString())) == true ? "Yes" : "No",
scorecardFailScore = reader.IsDBNull(reader.GetOrdinal("scorecardFailScore")) ? (float?)null : float.Parse(reader.GetValue(reader.GetOrdinal("scorecardFailScore")).ToString()),
scorecardId = reader.IsDBNull(reader.GetOrdinal("scorecardId")) ? (int?)null : int.Parse(reader.GetValue(reader.GetOrdinal("scorecardId")).ToString()),
scorecardName = reader.IsDBNull(reader.GetOrdinal("scorecardName")) ? (string)null : reader.GetValue(reader.GetOrdinal("scorecardName")).ToString(),
websiteUrl = reader.IsDBNull(reader.GetOrdinal("websiteUrl")) ? (string)null : reader.GetValue(reader.GetOrdinal("websiteUrl")).ToString(),
agentGroup = reader.IsDBNull(reader.GetOrdinal("agentGroup")) ? (string)null : reader.GetValue(reader.GetOrdinal("agentGroup")).ToString(),
campaign = reader.IsDBNull(reader.GetOrdinal("campaign")) ? (string)null : reader.GetValue(reader.GetOrdinal("campaign")).ToString(),
sessionId = reader.IsDBNull(reader.GetOrdinal("sessionId")) ? (string)null : reader.GetValue(reader.GetOrdinal("sessionId")).ToString(),
profileId = reader.IsDBNull(reader.GetOrdinal("profileId")) ? (string)null : reader.GetValue(reader.GetOrdinal("profileId")).ToString(),
prospectFirstName = reader.IsDBNull(reader.GetOrdinal("prospectFirstName")) ? (string)null : reader.GetValue(reader.GetOrdinal("prospectFirstName")).ToString(),
prospectLastName = reader.IsDBNull(reader.GetOrdinal("prospectLastName")) ? (string)null : reader.GetValue(reader.GetOrdinal("prospectLastName")).ToString(),
prospectPhone = reader.IsDBNull(reader.GetOrdinal("prospectPhone")) ? (string)null : reader.GetValue(reader.GetOrdinal("prospectPhone")).ToString(),
prospectEmail = reader.IsDBNull(reader.GetOrdinal("prospectEmail")) ? (string)null : reader.GetValue(reader.GetOrdinal("prospectEmail")).ToString(),
wasEdited = (reader.IsDBNull(reader.GetOrdinal("wasEdit")) ? (bool?)null : bool.Parse(reader.GetValue(reader.GetOrdinal("wasEdit")).ToString())) == true ? "Yes" : "No",
notificationStatus = reader.IsDBNull(reader.GetOrdinal("notificationStatus")) ? (string)null : reader.GetValue(reader.GetOrdinal("notificationStatus")).ToString(),
notificationStep = reader.IsDBNull(reader.GetOrdinal("notificationStep")) ? (string)null : reader.GetValue(reader.GetOrdinal("notificationStep")).ToString(),
isOwnedNotification = (reader.IsDBNull(reader.GetOrdinal("isOwnedNotification")) ? (bool?)null : bool.Parse(reader.GetValue(reader.GetOrdinal("isOwnedNotification")).ToString())) == true ? "Yes" : "No",
OwnedNotification = (reader.IsDBNull(reader.GetOrdinal("OwnedNotification")) ? false : bool.Parse(reader.GetValue(reader.GetOrdinal("OwnedNotification")).ToString())) == true ? "Yes" : "No",
calibratorId = reader.IsDBNull(reader.GetOrdinal("calibratorId")) ? (string)null : reader.GetValue(reader.GetOrdinal("calibratorId")).ToString(),
missedItemsList = reader.IsDBNull(reader.GetOrdinal("missedItemsList")) ? (string)null : reader.GetValue(reader.GetOrdinal("missedItemsList")).ToString(),
missedItemsCommentList = reader.IsDBNull(reader.GetOrdinal("missedItemsCommentList")) ? (string)null : reader.GetValue(reader.GetOrdinal("missedItemsCommentList")).ToString()
});
}
catch (Exception ex)
{
throw ex;
}
}
var propNames = new List <PropertieName>();
var j = 1;
foreach (var item in columns)
{
switch (item.id)
{
case 1:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "agentName", propPosition = j
});
j++;
break;
case 2:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callDate", propPosition = j
});
j++;
break;
case 3:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callType", propPosition = j
});
j++;
break;
case 4:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callId", propPosition = j
});
j++;
break;
case 5:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callReviewStatus", propPosition = j
});
j++;
break;
case 6:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callAudioUrl", propPosition = j
});
j++;
break;
case 7:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callAudioLength", propPosition = j
});
j++;
break;
case 8:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "scorecardId", propPosition = j
});
j++;
break;
case 9:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "scorecardName", propPosition = j
});
j++;
break;
case 10:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "scorecardFailScore", propPosition = j
});
j++;
break;
case 11:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "reviewDate", propPosition = j
});
j++;
break;
case 12:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "reviewerUserRole", propPosition = j
});
j++;
break;
case 13:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "reviewerName", propPosition = j
});
j++;
break;
case 14:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "calibratorId", propPosition = j
});
j++;
break;
case 15:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "calibratorName", propPosition = j
});
j++;
break;
case 16:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "missedItemsCount", propPosition = j
});
j++;
break;
case 17:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "agentScore", propPosition = j
});
j++;
break;
case 18:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "callFailed", propPosition = j
});
j++;
break;
case 19:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "reviewCommentsPresent", propPosition = j
});
j++;
break;
case 20:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "notificationCommentsPresent", propPosition = j
});
j++;
break;
case 21:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "agentGroup", propPosition = j
});
j++;
break;
case 22:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "campaign", propPosition = j
});
j++;
break;
case 23:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "sessionId", propPosition = j
});
j++;
break;
case 24:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "profileId", propPosition = j
});
j++;
break;
case 25:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "prospectFirstName", propPosition = j
});
j++;
break;
case 26:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "prospectLastName", propPosition = j
});
j++;
break;
case 27:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "prospectPhone", propPosition = j
});
j++;
break;
case 28:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "prospectEmail", propPosition = j
});
j++;
break;
case 29:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "cali_id", propPosition = j
});
j++;
break;
case 30:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "wasEdit", propPosition = j
});
j++;
break;
case 31:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "notificationStatus", propPosition = j
});
j++;
break;
case 32:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "notificationStep", propPosition = j
});
j++;
break;
case 33:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "notificationId", propPosition = j
});
j++;
break;
case 34:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "isOwnedNotification", propPosition = j
});
j++;
break;
case 35:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "OwnedNotification", propPosition = j
});
j++;
break;
case 36:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "assignedToRole", propPosition = j
});
j++;
break;
case 37:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "missedItemsList", propPosition = j
});
j++;
break;
case 38:
propNames.Add(new PropertieName {
propName = item.Name, propValue = "missedItemsCommentList", propPosition = j
});
j++;
break;
}
}
ExportHelper.Export(propNames, exportCoachingQueueColumns, "CoachingQueue" + DateTime.Now.ToString("MM-dd-yyyy") + DateTime.Now.Second.ToString() + ".xlsx", "CoachingQueue", userName);
}
catch (Exception ex)
{
throw ex;
}
return("success");
}
}