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");
}
}
