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