public new void LoadChart(UserModel currentUser) { try { this.DrillChartIds = (this.DrillOverride) ? this.Id.ToString() : this.DrillChartIds; int colorIndex = 0; //UserEntities userEntities; SandlerModels.DataIntegration.DataQueries queries = new SandlerModels.DataIntegration.DataQueries(); switch (this.Id) { case ChartID.ReturnOnTrainingInvestment: int ROIAdjustmentFactor = int.Parse(ConfigurationManager.AppSettings["ROIAdjustmentFactor"]); GapAnalysisRepository reposiroty = new GapAnalysisRepository(); GATracker gaTracker = reposiroty.GetGATrackerById(int.Parse(SearchParameter)); double year1ExpVal, year2ExpVal, year3ExpVal, year1AF, year2AF, year3AF; year1ExpVal = Convert.ToDouble(gaTracker.Year1.Value); year2ExpVal = Convert.ToDouble(gaTracker.Year2.Value); year3ExpVal = Convert.ToDouble(gaTracker.Year3.Value); year1AF = GetFactorValue(year1ExpVal); year2AF = GetFactorValue(year2ExpVal); year3AF = GetFactorValue(year3ExpVal); double scVal, seVal, sqVal, tcsVal, qaVal, ebgVal; double year1Savings, year1ROIBalance, year2Savings, year2ROIBalance, year3Savings, year3ROIBalance; string year1ROIPercent, year2ROIPercent, year3ROIPercent; //Year1 Calculations scVal = (year1ExpVal * (gaTracker.ToBeSalesCycleTimePercentVal.Value - gaTracker.AsIsSalesCycleTimePercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; seVal = (year1ExpVal * (gaTracker.ToBeSalesEfficiencyPercentVal.Value - gaTracker.AsIsSalesEfficiencyPercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; sqVal = (year1ExpVal * (gaTracker.ToBeSalesQualificationPercentVal.Value - gaTracker.AsIsSalesQualificationPercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; tcsVal = (year1ExpVal * (gaTracker.ToBeSalesRepRetentionPercentVal.Value - gaTracker.AsIsSalesRepRetentionPercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; qaVal = (year1ExpVal * (gaTracker.ToBeQuotaAchievementPercentVal.Value - gaTracker.AsIsQuotaAchievementPercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; ebgVal = (year1ExpVal * (gaTracker.ToBeTrngBenefitsPercentVal.Value - gaTracker.AsIsTrngBenefitsPercentVal.Value) * ROIAdjustmentFactor * year1AF) / 10000; year1Savings = scVal + seVal + sqVal + tcsVal + qaVal + ebgVal; year1ROIBalance = year1Savings - year1ExpVal; year1ROIPercent = ((year1Savings / year1ExpVal * 100)).ToString(); //Year2 Calculations scVal = ((year2ExpVal * scVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesCycleTime) / 100)) / year1ExpVal) * year2AF; seVal = ((year2ExpVal * seVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesEfficiency) / 100)) / year1ExpVal) * year2AF; sqVal = ((year2ExpVal * sqVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesQualfn) / 100)) / year1ExpVal) * year2AF; tcsVal = ((year2ExpVal * tcsVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesRepRetention) / 100)) / year1ExpVal) * year2AF; qaVal = ((year2ExpVal * qaVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptQuotaAcht) / 100)) / year1ExpVal) * year2AF; ebgVal = ((year2ExpVal * ebgVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptTrngBenefits) / 100)) / year1ExpVal) * year2AF; year2Savings = scVal + seVal + sqVal + tcsVal + qaVal + ebgVal; year2ROIBalance = year2Savings - year2ExpVal; year2ROIPercent = ((year2Savings / year2ExpVal * 100)).ToString(); //Year3 Calculations scVal = ((year3ExpVal * scVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesCycleTime) / 100)) / year2ExpVal) * year3AF; seVal = ((year3ExpVal * seVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesEfficiency) / 100)) / year2ExpVal) * year3AF; sqVal = ((year3ExpVal * sqVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesQualfn) / 100)) / year2ExpVal) * year3AF; tcsVal = ((year3ExpVal * tcsVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptSalesRepRetention) / 100)) / year2ExpVal) * year3AF; qaVal = ((year3ExpVal * qaVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptQuotaAcht) / 100)) / year2ExpVal) * year3AF; ebgVal = ((year3ExpVal * ebgVal * (1 + Convert.ToDouble(gaTracker.DesiredAnnualImptTrngBenefits) / 100)) / year2ExpVal) * year3AF; year3Savings = scVal + seVal + sqVal + tcsVal + qaVal + ebgVal; year3ROIBalance = year3Savings - year3ExpVal; year3ROIPercent = ((year3Savings / year3ExpVal * 100)).ToString(); this.SetsCollection.Add(new SetValue { Color = "#008000", Label = "Year1", Value = year1ROIPercent }); this.SetsCollection.Add(new SetValue { Color = "#008000", Label = "Year2", Value = year2ROIPercent }); this.SetsCollection.Add(new SetValue { Color = "#008000", Label = "Year3", Value = year3ROIPercent }); break; #region FranchiseeReports Logic case ChartID.AverageLengthTimeActiveClientsByIndustry: try { var AverageLengthTimeActiveClientsByIndustry = queries.AverageLengthTimeActiveClientsByIndustry(currentUser); IndustryTypeRepository industrySource = new IndustryTypeRepository(); foreach (var record in industrySource.GetAll().Where(r => r.IsActive == true).AsEnumerable()) { try { foreach (var d in AverageLengthTimeActiveClientsByIndustry) { if (record.IndustryTypeName == d.IndustryTypeName) { this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.IndustryTypeName, Value = d.Count.ToString("#"), Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink("", this.DrillChartIds) }); break; } } } catch (System.InvalidOperationException) { } colorIndex++; } } catch (Exception ex) { throw new Exception("Error in ChartID.AverageLengthTimeActiveClientsByIndustry:" + ex.Message); } break; #endregion default: break; } } catch (Exception ex) { throw ex; } }
public new void LoadChart(UserModel currentUser) { try { this.DrillChartIds = (this.DrillOverride) ? this.Id.ToString() : this.DrillChartIds; int colorIndex = 0; SandlerModels.DataIntegration.DataQueries queries = new SandlerModels.DataIntegration.DataQueries(); AppointmentSourceRepository appointmentSource = null; ProductTypesRepository productTypesSource = null; string[] colors = null; string searchForNewCompany, searchCompanies; var totalPrice = 0.0; var totalCounts = 0.0; IEnumerable<Tbl_ProductType> products; string companyName = ""; string franchiseeName = ""; string regionName = ""; string countryName = ""; int monthToProcess, yearToProcess; double avgValue; switch (this.Id) { case ChartID.SalesCycleTimeMain: IEnumerable<SandlerModels.DataIntegration.SalesCycleTimePortfolioVM> salesCyclePortfolio = queries.GetSalesCycleTimePortfolio(currentUser); int totalCount = salesCyclePortfolio.Count(); if (salesCyclePortfolio != null) { var salesCycleData = from opportunity in salesCyclePortfolio group opportunity by new { opportunity.MultipleOfSixVal } into grp select new { Legend = ChartHelper.GetSCTimeLegend(grp.Key.MultipleOfSixVal), Count = grp.Count() * 100 / totalCount }; colors = new string[] { "CC6600", "9900CC", "FF3300", "0099FF", "00CC66", "FFFF00", "CC6600", "9900CC" }; foreach (var record in salesCycleData) { this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Legend, Value = record.Count.ToString() }); colorIndex++; } } break; #region AdHocReports logic case ChartID.PipelineOpportunityAnalysisBySource: if (this.SubType == ChartSubType.ProductsSoldBySalesValue) this.Caption = "Sales Value Percentage By Product"; if (this.SubType == ChartSubType.ProductsSoldBySalesQuantity) this.Caption = "Sales Quantity Percentage By Product"; if (this.SubType == ChartSubType.SalesValueOppSource) this.Caption = "Sales Value Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesQuantityOppSource) this.Caption = "Sales Quantity Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesValueOpportunityType) this.Caption = "Sales Value Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesQuantityOpportunityType) this.Caption = "Sales Quantity Percentage By Opportunity Source"; searchForNewCompany = (HttpContext.Current.Session["searchForNewCompany"] == null) ? "" : HttpContext.Current.Session["searchForNewCompany"].ToString(); searchCompanies = (HttpContext.Current.Session["searchCompanies"] == null) ? "" : HttpContext.Current.Session["searchCompanies"].ToString(); IEnumerable<SandlerModels.DataIntegration.PipelineOppAnalysisVM> pipelineSalesVMCollection = queries.GetPipelineOpportunityAnalysis(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, this.SubType.ToString(), searchForNewCompany, searchCompanies); colors = new string[] { "CC6600", "9900CC", "FF3300", "0099FF", "00CC66", "FFFF00", "CC6600", "9900CC" }; totalPrice = pipelineSalesVMCollection.Sum(r => r.AvgPrice); totalCounts = pipelineSalesVMCollection.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); if (this.SubType == ChartSubType.SalesValueOppSource || this.SubType == ChartSubType.SalesQuantityOppSource) { foreach (var record in new OpprtunitySourceRepository().GetAll()) { try { if (pipelineSalesVMCollection.Single(r => r.Name == record.Name) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = (((pipelineSalesVMCollection.Single(r => r.Name == record.Name).AvgPrice) / totalPrice) * 100).ToString() }); else { this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = ((pipelineSalesVMCollection.Single(r => r.Name == record.Name).Count * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } colorIndex++; } } else if (this.SubType == ChartSubType.SalesValueOpportunityType || this.SubType == ChartSubType.SalesQuantityOpportunityType) { foreach (var record in new OpprtunityTypesRepository().GetAll()) { try { if (pipelineSalesVMCollection.Single(r => r.Name == record.Name) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = (((pipelineSalesVMCollection.Single(r => r.Name == record.Name).AvgPrice) / totalPrice) * 100).ToString() }); else { this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = ((pipelineSalesVMCollection.Single(r => r.Name == record.Name).Count * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } colorIndex++; } } else { int tmpCount = 0; foreach (var record in products.AsEnumerable()) { try { if (pipelineSalesVMCollection.Single(r => r.Name == record.ProductTypeName) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((pipelineSalesVMCollection.Single(r => r.Name == record.ProductTypeName).AvgPrice) / totalPrice) * 100).ToString() }); else { tmpCount = pipelineSalesVMCollection.Single(r => r.Name == record.ProductTypeName).Count; this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = ((tmpCount * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } } } //} break; case ChartID.ClosedSalesAnalysisBySource: if (this.SubType == ChartSubType.ProductsSoldBySalesValue) this.Caption = "Sales Value Percentage By Product"; if (this.SubType == ChartSubType.ProductsSoldBySalesQuantity) this.Caption = "Sales Quantity Percentage By Product"; if (this.SubType == ChartSubType.SalesValueOppSource) this.Caption = "Sales Value Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesQuantityOppSource) this.Caption = "Sales Quantity Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesValueOpportunityType) this.Caption = "Sales Value Percentage By Opportunity Source"; if (this.SubType == ChartSubType.SalesQuantityOpportunityType) this.Caption = "Sales Quantity Percentage By Opportunity Source"; searchForNewCompany = (HttpContext.Current.Session["searchForNewCompany"] == null) ? "" : HttpContext.Current.Session["searchForNewCompany"].ToString(); searchCompanies = (HttpContext.Current.Session["searchCompanies"] == null) ? "" : HttpContext.Current.Session["searchCompanies"].ToString(); IEnumerable<SandlerModels.DataIntegration.ClosedSalesVM> productTypeVMCollection = queries.GetClosedSalesAnalysis(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, this.SubType.ToString(), searchForNewCompany, searchCompanies); colors = new string[] { "CC6600", "9900CC", "FF3300", "0099FF", "00CC66", "FFFF00", "CC6600", "9900CC" }; totalPrice = productTypeVMCollection.Sum(r => r.AvgPrice); totalCounts = productTypeVMCollection.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); if (this.SubType == ChartSubType.SalesValueOppSource || this.SubType == ChartSubType.SalesQuantityOppSource) { foreach (var record in new OpprtunitySourceRepository().GetAll()) { try { if (productTypeVMCollection.Single(r => r.Name == record.Name) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = (((productTypeVMCollection.Single(r => r.Name == record.Name).AvgPrice) / totalPrice) * 100).ToString() }); else { this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = ((productTypeVMCollection.Single(r => r.Name == record.Name).Count * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } colorIndex++; } } else if (this.SubType == ChartSubType.SalesValueOpportunityType || this.SubType == ChartSubType.SalesQuantityOpportunityType) { foreach (var record in new OpprtunityTypesRepository().GetAll()) { try { if (productTypeVMCollection.Single(r => r.Name == record.Name) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = (((productTypeVMCollection.Single(r => r.Name == record.Name).AvgPrice) / totalPrice) * 100).ToString() }); else { this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.Name, Value = ((productTypeVMCollection.Single(r => r.Name == record.Name).Count * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } colorIndex++; } } else { int tmpCount = 0; foreach (var record in products.AsEnumerable()) { try { if (productTypeVMCollection.Single(r => r.Name == record.ProductTypeName) != null) { if (this.SubType.ToString().Contains("Value")) this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productTypeVMCollection.Single(r => r.Name == record.ProductTypeName).AvgPrice) / totalPrice) * 100).ToString() }); else { tmpCount = productTypeVMCollection.Single(r => r.Name == record.ProductTypeName).Count; this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = ((tmpCount * 100) / totalCounts).ToString() }); } } } catch (System.InvalidOperationException) { } } } //} break; #endregion #region FranchiseeReports logic case ChartID.NewAppointmentsBySource: monthToProcess = DateTime.ParseExact(this.DrillBy, "MMM", null).Month; yearToProcess = (monthToProcess > DateTime.Now.Month) ? DateTime.Now.AddYears(-1).Year : DateTime.Now.Year; //if (queries.GetNewAppointmentSource(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) != null) //{ var NewAppointmentSource = from record in queries.GetNewAppointmentSource(currentUser, monthToProcess, yearToProcess) select new { Category = record.SourceName, Count = record.Count }; totalCount = NewAppointmentSource.Sum(r => r.Count); appointmentSource = new AppointmentSourceRepository(); foreach (var record in appointmentSource.GetAll().Where(r => r.IsActive == true).AsEnumerable()) { try { if (NewAppointmentSource.Single(r => r.Category == record.ApptSourceName) != null) { avgValue = NewAppointmentSource.Single(r => r.Category == record.ApptSourceName).Count * 100; avgValue = Math.Round(avgValue/totalCount,2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ApptSourceName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } colorIndex++; } //} break; case ChartID.NewClientByProductType: //if (queries.GetNewClientsByProductType(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) != null) //{ monthToProcess = DateTime.ParseExact(this.DrillBy, "MMM", null).Month; yearToProcess = (monthToProcess > DateTime.Now.Month) ? DateTime.Now.AddYears(-1).Year : DateTime.Now.Year; var NewClientsByProductType = from record in queries.GetNewClientsByProductType(currentUser,monthToProcess, yearToProcess ) select new { Category = record.ProductTypeName, Count = record.Count }; totalCount = NewClientsByProductType.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (NewClientsByProductType.Single(r => r.Category == record.ProductTypeName) != null) { avgValue = NewClientsByProductType.Single(r => r.Category == record.ProductTypeName).Count * 100; avgValue = Math.Round(avgValue / totalCount, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##")}); } } catch (System.InvalidOperationException) { } } //} break; case ChartID.NewClientQuantity: //if (queries.NewClientsWithProductTypes(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) != null) //{ var NewClientsWithProductTypes = from record in queries.NewClientsWithProductTypes(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Category = record.ProductTypeName, Count = record.Count }; productTypesSource = new ProductTypesRepository(); totalCount = NewClientsWithProductTypes.Sum(r => r.Count); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (NewClientsWithProductTypes.Single(r => r.Category == record.ProductTypeName) != null) { avgValue = NewClientsWithProductTypes.Single(r => r.Category == record.ProductTypeName).Count * 100; avgValue = Math.Round(avgValue / totalCount, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } } //} break; case ChartID.ContractPrice: //if (queries.ContractPriceWithProductTypes(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) != null) //{ var ContractPriceWithProductTypes = from record in queries.ContractPriceWithProductTypes(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Category = record.ProductTypeName, AvgPrice = record.AvgPrice }; productTypesSource = new ProductTypesRepository(); totalPrice = ContractPriceWithProductTypes.Sum(r => r.AvgPrice); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (ContractPriceWithProductTypes.Single(r => r.Category == record.ProductTypeName) != null) { avgValue = ContractPriceWithProductTypes.Single(r => r.Category == record.ProductTypeName).AvgPrice * 100; avgValue = Math.Round(avgValue / totalPrice, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } } //} break; case ChartID.HeadcountByCourse: //if (queries.GetHeadcountByCourse(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) != null) //{ var headCountsByCourse = from record in queries.GetHeadcountByCourse(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Course = record.CourseName, Count = record.Count }; totalCount = headCountsByCourse.Sum(r => r.Count); colors = new string[] { "CC6600", "9900CC", "FF3300", "0099FF", "00CC66", "FFFF00" }; CourseRepository courseSource = new CourseRepository(); foreach (var record in courseSource.GetAll().Where(r => r.IsActive == true).AsEnumerable()) { try { if (headCountsByCourse.Single(r => r.Course == record.CourseName) != null) { avgValue = headCountsByCourse.Single(r => r.Course == record.CourseName).Count * 100; avgValue = Math.Round(avgValue/totalCount,2); this.SetsCollection.Add(new SetValue { Color = colors.GetValue(colorIndex).ToString(), Label = record.CourseName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } colorIndex++; } //} break; case ChartID.HeadcountByIndustry: var data = from record in queries.GetHeadcountByIndustry(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Industry = record.IndustryTypeName, Count = record.Count }; totalCount = data.Sum(r => r.Count); colors = new string[] { "9900CC", "FF3300", "0099FF", "00CC66", "FFFF00" }; IndustryTypeRepository industrySource = new IndustryTypeRepository(); foreach (var record in industrySource.GetAll().Where(r => r.IsActive == true).AsEnumerable()) { try { if (data.Single(r => r.Industry == record.IndustryTypeName) != null) { avgValue = data.Single(r => r.Industry == record.IndustryTypeName).Count * 100; avgValue = Math.Round(avgValue / totalCount, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.IndustryTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } colorIndex++; } //} break; #endregion #region ProductsReports Logic case ChartID.ProductMarginValue: var productSalesValue = from record in queries.GetProductSalesByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Name = record.ProductTypeName, Value = record.AvgPrice }; totalPrice = productSalesValue.Sum(r => r.Value); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productSalesValue.Single(r => r.Name == record.ProductTypeName) != null) { avgValue = productSalesValue.Single(r => r.Name == record.ProductTypeName).Value * 100; avgValue = Math.Round(avgValue / totalPrice, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } } break; case ChartID.ProductSalesQty: var productSalesQty = from record in queries.GetProductSalesByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Name = record.ProductTypeName, Count = record.Count }; totalPrice = productSalesQty.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productSalesQty.Single(r => r.Name == record.ProductTypeName) != null) { avgValue = productSalesQty.Single(r => r.Name == record.ProductTypeName).Count * 100; avgValue = Math.Round(avgValue / totalPrice, 2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } } break; case ChartID.ProductFirstSalesValue: var productFirstSalesValue = from record in queries.GetProductFirstSalesByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Name = record.ProductTypeName, Value = record.AvgPrice }; totalPrice = productFirstSalesValue.Sum(r => r.Value); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productFirstSalesValue.Single(r => r.Name == record.ProductTypeName) != null) { avgValue = productFirstSalesValue.Single(r => r.Name == record.ProductTypeName).Value*100; avgValue = Math.Round(avgValue/totalPrice,2); this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = avgValue.ToString("#.##") }); } } catch (System.InvalidOperationException) { } } break; case ChartID.ProductFirstSalesQty: var productFirstSalesQty = from record in queries.GetProductFirstSalesByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month) select new { Name = record.ProductTypeName, Count = record.Count }; totalPrice = productFirstSalesQty.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productFirstSalesQty.Single(r => r.Name == record.ProductTypeName) != null) this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productFirstSalesQty.Single(r => r.Name == record.ProductTypeName).Count) / totalPrice) * 100).ToString() }); } catch (System.InvalidOperationException) { } } break; case ChartID.ProductSalesByCompanyQuantity: try { companyName = new CompaniesRepository().GetById(long.Parse(SearchParameter)).COMPANYNAME; this.Caption = this.Caption.Replace("Company Name", companyName); var productSalestoCompanyQty = from record in queries.GetProductSoldByCompanyByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, int.Parse(this.SearchParameter)) select new { Name = record.ProductTypeName, Count = record.Count }; totalCounts = productSalestoCompanyQty.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); //double percentCount = 0.0; foreach (var record in products.AsEnumerable()) { try { if (productSalestoCompanyQty.Single(r => r.Name == record.ProductTypeName) != null) { this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productSalestoCompanyQty.Single(r => r.Name == record.ProductTypeName).Count) / totalCounts) * 100).ToString() }); } } catch (System.InvalidOperationException) { } } } catch (Exception ex) { throw new Exception("Exception in ChartID.ProductSalesByCompanyQuantity:" + ex.Message); } break; case ChartID.ProductSalesByCompanyValue: try { companyName = new CompaniesRepository().GetById(long.Parse(SearchParameter)).COMPANYNAME; this.Caption = this.Caption.Replace("Company Name", companyName); var productSalestoCompanyValue = from record in queries.GetProductSoldByCompanyByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, int.Parse(SearchParameter)) select new { Name = record.ProductTypeName, Value = record.AvgPrice }; totalPrice = productSalestoCompanyValue.Sum(r => r.Value); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productSalestoCompanyValue.Single(r => r.Name == record.ProductTypeName) != null) this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productSalestoCompanyValue.Single(r => r.Name == record.ProductTypeName).Value) / totalPrice) * 100).ToString() }); } catch (System.InvalidOperationException) { } } } catch (Exception ex) { throw new Exception("Exception in ChartID.ProductSalesByCompanyValue:" + ex.Message); } break; case ChartID.ProductSalesBySalesRepQuantity: try { this.Caption = this.Caption.Replace("Sales Rep", SearchParameter); var productSalesBySalesRepQty = from record in queries.GetProductSoldBySalesRepByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, this.SearchParameter) select new { Name = record.ProductTypeName, Count = record.Count }; totalCounts = productSalesBySalesRepQty.Sum(r => r.Count); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); //double percentCount = 0.0; foreach (var record in products.AsEnumerable()) { try { if (productSalesBySalesRepQty.Single(r => r.Name == record.ProductTypeName) != null) { this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productSalesBySalesRepQty.Single(r => r.Name == record.ProductTypeName).Count) / totalCounts) * 100).ToString() }); } } catch (System.InvalidOperationException) { } } } catch (Exception ex) { throw new Exception("Exception in ChartID.ProductSalesBySalesRepQuantity:" + ex.Message); } break; case ChartID.ProductSalesBySalesRepValue: try { this.Caption = this.Caption.Replace("Sales Rep", SearchParameter); var productSalesBtSalesRepValue = from record in queries.GetProductSoldBySalesRepByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month, SearchParameter) select new { Name = record.ProductTypeName, Value = record.AvgPrice }; totalPrice = productSalesBtSalesRepValue.Sum(r => r.Value); productTypesSource = new ProductTypesRepository(); if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser) products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID); else products = productTypesSource.GetAll().Where(r => r.IsActive == true); foreach (var record in products.AsEnumerable()) { try { if (productSalesBtSalesRepValue.Single(r => r.Name == record.ProductTypeName) != null) this.SetsCollection.Add(new SetValue { Color = record.ColorCode, Label = record.ProductTypeName, Value = (((productSalesBtSalesRepValue.Single(r => r.Name == record.ProductTypeName).Value) / totalPrice) * 100).ToString() }); } catch (System.InvalidOperationException) { } } } catch (Exception ex) { throw new Exception("Exception in ChartID.ProductSalesBySalesRepValue:" + ex.Message); } break; #endregion #region BenchMarkReports Logic case ChartID.BenchmarkSalesRepFranchiseeQty: double salesRepQty; try { franchiseeName = new FranchiseeRepository().GetById(currentUser.FranchiseeID).Name; Caption = Caption.Replace("Sales Rep", SearchParameter).Replace("Name", franchiseeName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesRepToFranchiseeData = queries.GetBenchMarkSalesRepToFranchiseeByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalCounts = salesRepToFranchiseeData.Sum(r => r.Count); this.SetsCollection.Add(new SetValue { Color = "4F94CD", Label = "Franchisee", Value = ((((from record in salesRepToFranchiseeData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Count)) / totalCounts) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM repRecord = salesRepToFranchiseeData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); salesRepQty = (repRecord == null) ? 0.0 : repRecord.Count; this.SetsCollection.Add(new SetValue { Color = "FF8C00", Label = "Rep", Value = (( salesRepQty/ totalCounts) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkSalesRepFranchiseeQty:" + ex.Message); } break; case ChartID.BenchmarkSalesRepFranchiseeValue: double salesRepValue; try { franchiseeName = new FranchiseeRepository().GetById(currentUser.FranchiseeID).Name; Caption = Caption.Replace("Sales Rep", SearchParameter).Replace("Name", franchiseeName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesRepToFranchiseeData = queries.GetBenchMarkSalesRepToFranchiseeByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalPrice = salesRepToFranchiseeData.Sum(r => r.Value); this.SetsCollection.Add(new SetValue { Color = "4F94CD", Label = "Franchisee", Value = ((((from record in salesRepToFranchiseeData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Value)) / totalPrice) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM repRecord = salesRepToFranchiseeData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); salesRepValue = (repRecord == null) ? 0.0 : repRecord.Value; this.SetsCollection.Add(new SetValue { Color = "FF8C00", Label = "Rep", Value = ((salesRepValue / totalPrice) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkSalesRepFranchiseeValue:" + ex.Message); } break; case ChartID.BenchmarkFranchiseeRegionQty: double franchiseeQty; try { franchiseeName = new FranchiseeRepository().GetById(int.Parse(SearchParameter)).Name; regionName = new RegionRepository().GetById(currentUser.RegionID).Name; Caption = Caption.Replace("Region Name", regionName).Replace("Franchisee Name", franchiseeName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesFranchiseeToRegionsData = queries.GetBenchMarkFranchiseeToRegionsByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalCounts = salesFranchiseeToRegionsData.Sum(r => r.Count); this.SetsCollection.Add(new SetValue { Color = "32df00", Label = "Region", Value = ((((from record in salesFranchiseeToRegionsData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Count)) / totalCounts) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM franchiseeRecord = salesFranchiseeToRegionsData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); franchiseeQty = (franchiseeRecord == null) ? 0.0 : franchiseeRecord.Count; this.SetsCollection.Add(new SetValue { Color = "4F94CD", Label = "Franchisee", Value = ((franchiseeQty / totalCounts) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkFranchiseeRegionQty:" + ex.Message); } break; case ChartID.BenchmarkFranchiseeRegionValue: double franchiseeValue; try { franchiseeName = new FranchiseeRepository().GetById(int.Parse(SearchParameter)).Name; regionName = new RegionRepository().GetById(currentUser.RegionID).Name; Caption = Caption.Replace("Region Name", regionName).Replace("Franchisee Name", franchiseeName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesFranchiseeToRegionsData = queries.GetBenchMarkFranchiseeToRegionsByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalPrice = salesFranchiseeToRegionsData.Sum(r => r.Value); this.SetsCollection.Add(new SetValue { Color = "32df00", Label = "Region", Value = ((((from record in salesFranchiseeToRegionsData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Value)) / totalPrice) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM franchiseeRecord = salesFranchiseeToRegionsData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); franchiseeValue = (franchiseeRecord == null) ? 0.0 : franchiseeRecord.Value; this.SetsCollection.Add(new SetValue { Color = "4F94CD", Label = "Franchisee", Value = ((franchiseeValue / totalPrice) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkFranchiseeRegionValue:" + ex.Message); } break; case ChartID.BenchmarkRegionCountryQty: double regionQty; try { regionName = new RegionRepository().GetById(int.Parse(SearchParameter)).Name; countryName = new CountryRepository().GetById(currentUser.CountryID).Name; Caption = Caption.Replace("Region Name", regionName).Replace("Country Name", countryName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesRegionToCountryData = queries.GetBenchMarkRegionToCountryByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalCounts = salesRegionToCountryData.Sum(r => r.Count); this.SetsCollection.Add(new SetValue { Color = "800080", Label = "Country", Value = ((((from record in salesRegionToCountryData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Count)) / totalCounts) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM regionRecord = salesRegionToCountryData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); regionQty = (regionRecord == null) ? 0.0 : regionRecord.Count; this.SetsCollection.Add(new SetValue { Color = "32df00", Label = "Region", Value = ((regionQty / totalCounts) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkRegionCountryQty:" + ex.Message); } break; case ChartID.BenchmarkRegionCountryValue: double regionValue; try { regionName = new RegionRepository().GetById(int.Parse(SearchParameter)).Name; countryName = new CountryRepository().GetById(currentUser.CountryID).Name; Caption = Caption.Replace("Region Name", regionName).Replace("Country Name", countryName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesRegionToCountryData = queries.GetBenchMarkRegionToCountryByMonth(currentUser, DateTime.ParseExact(this.DrillBy, "MMM", null).Month); totalPrice = salesRegionToCountryData.Sum(r => r.Value); this.SetsCollection.Add(new SetValue { Color = "800080", Label = "Country", Value = ((((from record in salesRegionToCountryData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Value)) / totalPrice) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM regionRecord = salesRegionToCountryData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); regionValue = (regionRecord == null) ? 0.0 : regionRecord.Value; this.SetsCollection.Add(new SetValue { Color = "32df00", Label = "Region", Value = ((regionValue / totalPrice) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkFranchiseeRegionValue:" + ex.Message); } break; case ChartID.BenchmarkCountryAllQty: double countryQty; try { countryName = new CountryRepository().GetById(int.Parse(SearchParameter)).Name; Caption = Caption.Replace("Country Name", countryName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesCountryAllData = queries.GetBenchMarkCountryAllByMonth( DateTime.ParseExact(this.DrillBy, "MMM", null).Month); ; totalCounts = salesCountryAllData.Sum(r => r.Count); this.SetsCollection.Add(new SetValue { Color = "800080", Label = "All", Value = ((((from record in salesCountryAllData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Count)) / totalCounts) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM countryRecord = salesCountryAllData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); countryQty = (countryRecord == null) ? 0.0 : countryRecord.Count; this.SetsCollection.Add(new SetValue { Color = "00CED1", Label = "Country", Value = ((countryQty / totalCounts) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkRegionCountryQty:" + ex.Message); } break; case ChartID.BenchmarkCountryAllValue: double countryValue; try { countryName = new CountryRepository().GetById(int.Parse(SearchParameter)).Name; Caption = Caption.Replace("Country Name", countryName); IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesCountryAllData = queries.GetBenchMarkCountryAllByMonth( DateTime.ParseExact(this.DrillBy, "MMM", null).Month); totalPrice = salesCountryAllData.Sum(r => r.Value); this.SetsCollection.Add(new SetValue { Color = "800080", Label = "All", Value = ((((from record in salesCountryAllData.Where(r => r.KeyGroupId != SearchParameter) select record).Sum(r => r.Value)) / totalPrice) * 100).ToString() }); SandlerModels.DataIntegration.BenchMarkVM countryRecord = salesCountryAllData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault(); countryValue = (countryRecord == null) ? 0.0 : countryRecord.Value; this.SetsCollection.Add(new SetValue { Color = "00CED1", Label = "Country", Value = ((countryValue / totalPrice) * 100).ToString() }); } catch (Exception ex) { throw new Exception("Exception in ChartID.BenchmarkCountryAllValue:" + ex.Message); } break; #endregion default: break; } } catch (Exception ex) { throw ex; } }