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