public void LoadChart(UserModel currentUser)
{
try
{
this.DrillChartIds = (this.DrillOverride) ? this.Id.ToString() : this.DrillChartIds;
List<ChartParameter> chartParams = null;
ChartDataSet lastDs = null;
SandlerModels.DataIntegration.DataQueries queries = new SandlerModels.DataIntegration.DataQueries();
AppointmentSourceRepository appointmentSource;
ProductTypesRepository productTypesSource;
IEnumerable<Tbl_ProductType> products;
string searchForNewCompany,searchCompanies,franchiseeName, regionName, countryName;
int yearToProcess, monthToProcess;
switch ((ChartID)Enum.Parse(typeof(ChartID), this.Id.ToString(), true))
{
#region ProductsReports Logic
case ChartID.ProductMarginContributionByProductByMonth:
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = "0", Color = "32df00" });
foreach (ChartParameter parameter in chartParams)
{
try
{
IEnumerable<SandlerModels.DataIntegration.ProductTypeVM> productSalesCollection = queries.GetProductSalesByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
if (productSalesCollection != null)
{
var productSales = from record in productSalesCollection
select new { Category = record.ProductTypeName, Value = record.AvgPrice };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = ChartHelper.GeneratePageLink(lastDs.SeriesName, this.DrillChartIds) });
}
foreach (var record in productSales)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Value.ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.ProductMarginContributionByProductByMonth:" + ex.Message);
}
//}
}
break;
case ChartID.FirstSaleProductValueByProductByMonth:
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = "0", Color = "32df00" });
foreach (ChartParameter parameter in chartParams)
{
try
{
IEnumerable<SandlerModels.DataIntegration.ProductTypeVM> productSalesCollection = queries.GetProductFirstSalesByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
if (productSalesCollection != null)
{
var productSales = from record in productSalesCollection
select new { Category = record.ProductTypeName, Value = record.AvgPrice };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = ChartHelper.GeneratePageLink(lastDs.SeriesName, this.DrillChartIds) });
}
foreach (var record in productSales)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Value.ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.FirstSaleProductValueByProductByMonth:" + ex.Message);
}
//}
}
break;
case ChartID.ProductSoldToCompanyByProductByMonth:
string companyName;
try
{
companyName = new CompaniesRepository().GetById(long.Parse(SearchParameter)).COMPANYNAME;
this.Caption = this.Caption.Replace("Company Name", companyName);
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = "0", Color = "32df00" });
foreach (ChartParameter parameter in chartParams)
{
IEnumerable<SandlerModels.DataIntegration.ProductTypeVM> productSalesCollection = queries.GetProductSoldByCompanyByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month, int.Parse(this.SearchParameter));
if (productSalesCollection != null)
{
var productSales = from record in productSalesCollection
select new { Category = record.ProductTypeName, Qty = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = ChartHelper.GeneratePageLink(lastDs.SeriesName, this.DrillChartIds, "SoldByCompany.aspx?searchParameter=" + this.SearchParameter + "&") });
}
foreach (var record in productSales)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Qty.ToString();
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.ProductSoldToCompanyByProductByMonth:" + ex.Message);
}
break;
case ChartID.ProductSoldBySalesRepByProductByMonth:
this.Caption = this.Caption.Replace("Sales Rep", this.SearchParameter);
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = "0", Color = "32df00" });
foreach (ChartParameter parameter in chartParams)
{
try
{
IEnumerable<SandlerModels.DataIntegration.ProductTypeVM> productSalesCollection = queries.GetProductSoldBySalesRepByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month, this.SearchParameter);
if (productSalesCollection != null)
{
var productSales = from record in productSalesCollection
select new { Category = record.ProductTypeName, Qty = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = ChartHelper.GeneratePageLink(lastDs.SeriesName, this.DrillChartIds, "SoldByCompanySalesRep.aspx?searchParameter=" + this.SearchParameter + "&") });
}
foreach (var record in productSales)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Qty.ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.ProductSoldToCompanyByProductByMonth:" + ex.Message);
}
//}
}
break;
#endregion
#region ClientReports Logic
case ChartID.SalesTotalsByMonthQty:
string[] monthNames = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;
foreach (string monthName in monthNames) // writing out
{
if (!string.IsNullOrEmpty(monthName))
this.Categories.Add(new Category { Label = monthName.Substring(0, 3) });
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = (DateTime.Now.Year - 2).ToString(), Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = (DateTime.Now.Year - 1).ToString(), Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = DateTime.Now.Year.ToString(), Color = "32df00" });
IEnumerable<SandlerModels.DataIntegration.SalesTotalByMonthVM> salesTotalData;
foreach (ChartParameter parameter in chartParams)
{
try
{
salesTotalData = queries.GetSalesTotalByYear(currentUser, int.Parse(parameter.Value));
if (salesTotalData != null)
{
var salesDataForAYear = from opportunity in salesTotalData
group opportunity by new { opportunity.CloseDate.Month }
into grp
select new { Count = grp.Count(), MonthName = ChartHelper.GetMonthName(grp.Key.Month) };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = parameter.Value });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label });
}
foreach (var record in salesDataForAYear)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.MonthName)
set.Value = record.Count.ToString();
}
}
}
salesTotalData = null;
}
catch (Exception ex)
{
}
}
break;
case ChartID.CostOfSale:
IEnumerable<SandlerModels.DataIntegration.CostOfSaleVM> costofsaleData = queries.GetCostOfSale(currentUser);
this.DataSetCollection.Add(new ChartDataSet { Color = "0000FF", SeriesName = "Profit" });//0000FF blue
this.DataSetCollection.Add(new ChartDataSet { Color = "FF8C00", SeriesName = "Cost" });//FF8C00 darkorange
this.DataSetCollection.Add(new ChartDataSet { Color = "32CD32", SeriesName = "Revenue" });//32CD32 Lime green
foreach (SandlerModels.DataIntegration.CostOfSaleVM cosRecord in costofsaleData)
{
this.Categories.Add(new Category { Label = cosRecord.ProductName });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = cosRecord.Profit.ToString() });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = cosRecord.Cost.ToString() });
this.DataSetCollection[2].SetsCollection.Add(new SetValue { Value = cosRecord.Revenue.ToString() });
}
break;
#endregion
#region BenchmarkReports Logic
case ChartID.BenchmarkSalesRepFranchisee:
try
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2" });
chartParams.Add(new ChartParameter { Value = "-1" });
chartParams.Add(new ChartParameter { Value = "0" });
franchiseeName = new FranchiseeRepository().GetById(currentUser.FranchiseeID).Name;
Caption = Caption.Replace("Sales Rep", SearchParameter).Replace("Name", franchiseeName);
IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesRepToFranchiseeData;
this.DataSetCollection.Add(new ChartDataSet { Color = "4F94CD", SeriesName = "Franchisee" });//4F94CD steelblue3
this.DataSetCollection.Add(new ChartDataSet { Color = "FF8C00", SeriesName = "Rep" });//FF8C00 darkorange
Double totalValue = 0.0;
Double otherSalesRepsTotals = 0.0;
Double salesRepValue = 0.0;
foreach (ChartParameter parameter in chartParams)
{
salesRepToFranchiseeData = queries.GetBenchMarkSalesRepToFranchiseeByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
totalValue = salesRepToFranchiseeData.Sum(r => r.Value);
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
otherSalesRepsTotals = (from record in salesRepToFranchiseeData.Where(r => r.KeyGroupId != SearchParameter)
select record).Sum(r => r.Value);
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = ((otherSalesRepsTotals / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "SalesRepToFranchisee.aspx?searchParameter=" + this.SearchParameter + "&") });
SandlerModels.DataIntegration.BenchMarkVM repRecord = salesRepToFranchiseeData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault();
salesRepValue = (repRecord == null) ? 0.0 : repRecord.Value;
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = ((salesRepValue / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "SalesRepToFranchisee.aspx?searchParameter=" + this.SearchParameter + "&") });
}
}
catch (Exception ex)
{
throw new Exception("Exception in ChartID.BenchmarkSalesRepFranchisee:" + ex.Message);
}
break;
case ChartID.BenchmarkFranchiseeRegion:
try
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2" });
chartParams.Add(new ChartParameter { Value = "-1" });
chartParams.Add(new ChartParameter { Value = "0" });
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;
this.DataSetCollection.Add(new ChartDataSet { Color = "32df00", SeriesName = "Region" });
this.DataSetCollection.Add(new ChartDataSet { Color = "4F94CD", SeriesName = "Franchisee" });
Double totalValue = 0.0;
Double otherFranchiseesTotals = 0.0;
Double franchiseeValue = 0.0;
foreach (ChartParameter parameter in chartParams)
{
salesFranchiseeToRegionsData = queries.GetBenchMarkFranchiseeToRegionsByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
totalValue = salesFranchiseeToRegionsData.Sum(r => r.Value);
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
otherFranchiseesTotals = (from record in salesFranchiseeToRegionsData.Where(r => r.KeyGroupId != SearchParameter)
select record).Sum(r => r.Value);
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = ((otherFranchiseesTotals / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "FranchiseeToRegion.aspx?searchParameter=" + this.SearchParameter + "&") });
SandlerModels.DataIntegration.BenchMarkVM franchiseeRecord = salesFranchiseeToRegionsData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault();
franchiseeValue = (franchiseeRecord == null) ? 0.0 : franchiseeRecord.Value;
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = ((franchiseeValue / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "FranchiseeToRegion.aspx?searchParameter=" + this.SearchParameter + "&") });
}
}
catch (Exception ex)
{
throw new Exception("Exception in ChartID.BenchmarkFranchiseeRegion:" + ex.Message);
}
break;
case ChartID.BenchmarkRegionCountry:
try
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2" });
chartParams.Add(new ChartParameter { Value = "-1" });
chartParams.Add(new ChartParameter { Value = "0" });
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;
this.DataSetCollection.Add(new ChartDataSet { Color = "800080", SeriesName = "Country" });//800080 -- Purple
this.DataSetCollection.Add(new ChartDataSet { Color = "32df00", SeriesName = "Region" });
Double totalValue = 0.0;
Double otherRegionsTotal = 0.0;
Double regionValue = 0.0;
foreach (ChartParameter parameter in chartParams)
{
salesRegionToCountryData = queries.GetBenchMarkRegionToCountryByMonth(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
totalValue = salesRegionToCountryData.Sum(r => r.Value);
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
otherRegionsTotal = (from record in salesRegionToCountryData.Where(r => r.KeyGroupId != SearchParameter)
select record).Sum(r => r.Value);
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = ((otherRegionsTotal / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "RegionToCountry.aspx?searchParameter=" + this.SearchParameter + "&") });
SandlerModels.DataIntegration.BenchMarkVM regionRecord = salesRegionToCountryData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault();
regionValue = (regionRecord == null) ? 0.0 : regionRecord.Value;
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = ((regionValue / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "RegionToCountry.aspx?searchParameter=" + this.SearchParameter + "&") });
}
}
catch (Exception ex)
{
throw new Exception("Exception in ChartID.BenchmarkRegionCountry:" + ex.Message);
}
break;
case ChartID.BenchmarkCountryAll:
try
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2" });
chartParams.Add(new ChartParameter { Value = "-1" });
chartParams.Add(new ChartParameter { Value = "0" });
countryName = new CountryRepository().GetById(int.Parse(SearchParameter)).Name;
Caption = Caption.Replace("Country Name", countryName);
IEnumerable<SandlerModels.DataIntegration.BenchMarkVM> salesCountryAllData;
this.DataSetCollection.Add(new ChartDataSet { Color = "800080", SeriesName = "All" });//800080 -- Purple
this.DataSetCollection.Add(new ChartDataSet { Color = "00CED1", SeriesName = "Country" });//00CED1 -- darkturquoise
Double totalValue = 0.0;
Double otherCountriesTotal = 0.0;
Double countryValue = 0.0;
foreach (ChartParameter parameter in chartParams)
{
salesCountryAllData = queries.GetBenchMarkCountryAllByMonth(DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month);
totalValue = salesCountryAllData.Sum(r => r.Value);
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
otherCountriesTotal = (from record in salesCountryAllData.Where(r => r.KeyGroupId != SearchParameter)
select record).Sum(r => r.Value);
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = ((otherCountriesTotal / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "CountryToAll.aspx?searchParameter=" + this.SearchParameter + "&") });
SandlerModels.DataIntegration.BenchMarkVM franchiseeRecord = salesCountryAllData.Where(r => r.KeyGroupId == SearchParameter).SingleOrDefault();
countryValue = (franchiseeRecord == null) ? 0.0 : franchiseeRecord.Value;
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = ((countryValue / totalValue) * 100).ToString("f2"), Link = ChartHelper.GeneratePageLink(DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM"), this.DrillChartIds, "CountryToAll.aspx?searchParameter=" + this.SearchParameter + "&") });
}
}
catch (Exception ex)
{
throw new Exception("Exception in ChartID.BenchmarkCountryAll:" + ex.Message);
}
break;
#endregion
#region AdHocReports logic
case ChartID.ClosedSalesAnalysis:
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
if (this.SubType == ChartSubType.SalesValueOppSource || this.SubType == ChartSubType.SalesQuantityOppSource)
{
foreach (var record in new OppSourceeRepository().GetAll())
{
this.Categories.Add(new Category { Label = record.Name });
}
}
else if (this.SubType == ChartSubType.SalesValueOpportunityType || this.SubType == ChartSubType.SalesQuantityOpportunityType)
{
foreach (var record in new OpprtunityTypesRepository().GetAll())
{
this.Categories.Add(new Category { Label = record.Name });
}
}
else
{
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "8A4B08" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "0000FF" });
chartParams.Add(new ChartParameter { Value = "0", Color = "ff9966" });
searchForNewCompany = (HttpContext.Current.Session["searchForNewCompany"] == null) ? "" : HttpContext.Current.Session["searchForNewCompany"].ToString();
searchCompanies = (HttpContext.Current.Session["searchCompanies"] == null) ? "" : HttpContext.Current.Session["searchCompanies"].ToString();
foreach (ChartParameter parameter in chartParams)
{
try
{
if (this.SubType == ChartSubType.ProductsSoldBySalesValue)
this.Caption = "Sales Value By Product (By Month)";
if (this.SubType == ChartSubType.ProductsSoldBySalesQuantity)
this.Caption = "Sales Quantity By Product (By Month)";
if (this.SubType == ChartSubType.SalesValueOppSource)
this.Caption = "Sales Value By Opportunity Source (By Month)";
if (this.SubType == ChartSubType.SalesQuantityOppSource)
this.Caption = "Sales Quantity By Opportunity Source (By Month)";
if (this.SubType == ChartSubType.SalesValueOpportunityType)
this.Caption = "Sales Value By Opportunity Type (By Month)";
if (this.SubType == ChartSubType.SalesQuantityOpportunityType)
this.Caption = "Sales Quantity By Opportunity Type (By Month)";
IEnumerable<SandlerModels.DataIntegration.ClosedSalesVM> productTypeVMCollection = queries.GetClosedSalesAnalysis(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month, this.SubType.ToString(), searchForNewCompany, searchCompanies);
if (productTypeVMCollection != null)
{
var clientsWithProducts = from record in productTypeVMCollection
select new { Category = record.Name, SalesValue = record.AvgPrice, SalesQuantity = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
string link = "";
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
link = string.Format("{0}?{1}={2}&{3}={4}&SubType={5}", "ClosedSalesAnalysis.aspx", ConfigurationManager.AppSettings["QueryStringParamDrillChartIDs"], this.DrillChartIds, ConfigurationManager.AppSettings["QueryStringParamDrillBy"], lastDs.SeriesName, this.SubType.ToString());
//lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = ChartHelper.GeneratePageLink(lastDs.SeriesName, this.DrillChartIds, this.SubType.ToString()) });
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = link });
}
foreach (var record in clientsWithProducts)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
{
if (this.SubType.ToString().Contains("Value"))
{
set.Value = record.SalesValue.ToString();
}
if (this.SubType.ToString().Contains("Quantity"))
{
set.Value = record.SalesQuantity.ToString();
}
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.NewClientsByProductTypeMonth:" + ex.Message);
}
}
//}
break;
case ChartID.PipelineOpportunityAnalysis:
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
if (this.SubType == ChartSubType.SalesValueOppSource || this.SubType == ChartSubType.SalesQuantityOppSource)
{
foreach (var record in new OppSourceeRepository().GetAll())
{
this.Categories.Add(new Category { Label = record.Name });
}
}
else if (this.SubType == ChartSubType.SalesValueOpportunityType || this.SubType == ChartSubType.SalesQuantityOpportunityType)
{
foreach (var record in new OpprtunityTypesRepository().GetAll())
{
this.Categories.Add(new Category { Label = record.Name });
}
}
else
{
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
}
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "0", Color = "8A4B08" });
chartParams.Add(new ChartParameter { Value = "1", Color = "0000FF" });
chartParams.Add(new ChartParameter { Value = "2", Color = "ff9966" });
searchForNewCompany = (HttpContext.Current.Session["searchForNewCompany"] == null) ? "" : HttpContext.Current.Session["searchForNewCompany"].ToString();
searchCompanies = (HttpContext.Current.Session["searchCompanies"] == null) ? "" : HttpContext.Current.Session["searchCompanies"].ToString();
foreach (ChartParameter parameter in chartParams)
{
try
{
if (this.SubType == ChartSubType.ProductsSoldBySalesValue)
this.Caption = "Sales Value By Product (By Month)";
if (this.SubType == ChartSubType.ProductsSoldBySalesQuantity)
this.Caption = "Sales Quantity By Product (By Month)";
if (this.SubType == ChartSubType.SalesValueOppSource)
this.Caption = "Sales Value By Opportunity Source (By Month)";
if (this.SubType == ChartSubType.SalesQuantityOppSource)
this.Caption = "Sales Quantity By Opportunity Source (By Month)";
if (this.SubType == ChartSubType.SalesValueOpportunityType)
this.Caption = "Sales Value By Opportunity Type (By Month)";
if (this.SubType == ChartSubType.SalesQuantityOpportunityType)
this.Caption = "Sales Quantity By Opportunity Type (By Month)";
IEnumerable<SandlerModels.DataIntegration.PipelineOppAnalysisVM> productTypeVMCollection = queries.GetPipelineOpportunityAnalysis(currentUser, DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month, this.SubType.ToString(), searchForNewCompany, searchCompanies);
if (productTypeVMCollection != null)
{
var clientsWithProducts = from record in productTypeVMCollection
select new { Category = record.Name, SalesValue = record.AvgPrice, SalesQuantity = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = DateTime.Now.AddMonths(int.Parse(parameter.Value)).ToString("MMM") });
string link = "";
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
link = string.Format("{0}?{1}={2}&{3}={4}&SubType={5}", "PipelineOppAnalysis.aspx", ConfigurationManager.AppSettings["QueryStringParamDrillChartIDs"], this.DrillChartIds, ConfigurationManager.AppSettings["QueryStringParamDrillBy"], lastDs.SeriesName, this.SubType.ToString());
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = link });
}
foreach (var record in clientsWithProducts)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
{
if (this.SubType.ToString().Contains("Value"))
{
set.Value = record.SalesValue.ToString();
}
if (this.SubType.ToString().Contains("Quantity"))
{
set.Value = record.SalesQuantity.ToString();
}
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.PipelineOpportunityAnalysis:" + ex.Message);
}
}
//}
break;
#endregion
#region FranchiseeReports Logic
case ChartID.NewAppointmentsBySourceMonth:
appointmentSource = new AppointmentSourceRepository();
foreach (var record in appointmentSource.GetAll().Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ApptSourceName });
}
if (MonthYearCombinations == null)
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "3300ff" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "ff6600" });
chartParams.Add(new ChartParameter { Value = "0", Color = "32df00" });
}
else
chartParams = MonthYearCombinations;
foreach (ChartParameter parameter in chartParams)
{
try
{
yearToProcess = (string.IsNullOrEmpty(parameter.YearVal)) ? ((DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month > DateTime.Now.Month) ? DateTime.Now.AddYears(-1).Year : DateTime.Now.Year) : int.Parse(parameter.YearVal);
monthToProcess = (MonthYearCombinations == null) ? DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month : int.Parse(parameter.Value);
IEnumerable<SandlerModels.DataIntegration.AppointmentSourceVM> appointmentSourceVMcollection = queries.GetNewAppointmentSource(currentUser, monthToProcess,yearToProcess);
if (appointmentSourceVMcollection != null)
{
var newAppointments = from record in appointmentSourceVMcollection
select new { Category = record.SourceName, Count = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = ChartHelper.GetMonthName(monthToProcess) + " (" + yearToProcess.ToString() + ")"});
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink(ChartHelper.GetMonthName(monthToProcess), this.DrillChartIds) });
}
foreach (var record in newAppointments)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Count.ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.NewAppointmentsBySourceMonth:" + ex.Message);
}
//}
}
break;
case ChartID.NewClientsByProductTypeMonth:
productTypesSource = new ProductTypesRepository();
if (currentUser.Role == SandlerRoles.FranchiseeOwner || currentUser.Role == SandlerRoles.FranchiseeUser)
products = productTypesSource.GetWithFranchiseeId(currentUser.FranchiseeID);
else
products = productTypesSource.GetAll();
foreach (var record in products.Where(r => r.IsActive == true).AsEnumerable())
{
this.Categories.Add(new Category { Label = record.ProductTypeName });
}
if (MonthYearCombinations == null)
{
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = "-2", Color = "8A4B08" });
chartParams.Add(new ChartParameter { Value = "-1", Color = "0000FF" });
chartParams.Add(new ChartParameter { Value = "0", Color = "ff9966" });
}
else
chartParams = MonthYearCombinations;
foreach (ChartParameter parameter in chartParams)
{
try
{
yearToProcess = (string.IsNullOrEmpty(parameter.YearVal)) ? ((DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month > DateTime.Now.Month) ? DateTime.Now.AddYears(-1).Year : DateTime.Now.Year) : int.Parse(parameter.YearVal);
monthToProcess = (MonthYearCombinations == null) ? DateTime.Now.AddMonths(int.Parse(parameter.Value)).Month : int.Parse(parameter.Value);
IEnumerable<SandlerModels.DataIntegration.ProductTypeVM> productTypeVMCollection = queries.GetNewClientsByProductType(currentUser, monthToProcess,yearToProcess);
if (productTypeVMCollection != null)
{
var newClientsByProducts = from record in productTypeVMCollection
select new { Category = record.ProductTypeName, Count = record.Count };
this.DataSetCollection.Add(new ChartDataSet { Color = parameter.Color, SeriesName = ChartHelper.GetMonthName(monthToProcess) + " (" + yearToProcess.ToString() + ")" });
foreach (Category category in this.Categories)
{
lastDs = this.DataSetCollection.Last();
lastDs.SetsCollection.Add(new SetValue { Label = category.Label, Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink(ChartHelper.GetMonthName(monthToProcess), this.DrillChartIds) });
}
foreach (var record in newClientsByProducts)
{
foreach (SetValue set in lastDs.SetsCollection)
{
if (set.Label == record.Category)
set.Value = record.Count.ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error in ChartID.NewClientsByProductTypeMonth:" + ex.Message);
}
}
//}
break;
case ChartID.NewClientQuantityAverageContractPriceByMonth:
if (MonthYearCombinations == null)
{
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-2).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-1).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(0).ToString("MMM") });
}
else
{
foreach (ChartParameter param in MonthYearCombinations)
{
this.Categories.Add(new Category { Label = ChartHelper.GetMonthName(int.Parse(param.Value)) });
}
}
this.DataSetCollection.Add(new ChartDataSet { Color = "0000FF", SeriesName = "New Clients" });
this.DataSetCollection.Add(new ChartDataSet { Color = "ff6600", SeriesName = "Ave Contract Price" });
foreach (Category catagory in this.Categories)
{
try
{
yearToProcess = (DateTime.ParseExact(catagory.Label, "MMM", null).Month > DateTime.Now.Month) ? DateTime.Now.AddYears(-1).Year : DateTime.Now.Year;
monthToProcess = DateTime.ParseExact(catagory.Label, "MMM", null).Month;
int newClients = queries.GetNewClientCount(currentUser, monthToProcess, yearToProcess);
long aveContractPrice = queries.GetAveContractPrice(currentUser, monthToProcess, yearToProcess);
if (newClients > 0 && aveContractPrice > 0)
{
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = newClients.ToString(), Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink(catagory.Label, this.DrillChartIds) });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = (aveContractPrice / 5).ToString(), Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink(catagory.Label, this.DrillChartIds) });
}
else
{
this.DataSetCollection[0].SetsCollection.Add(new SetValue());
this.DataSetCollection[1].SetsCollection.Add(new SetValue());
}
if (MonthYearCombinations != null) //Check CustomChart reports request
{
//After done process category change its label with month and year combination
catagory.Label = catagory.Label + "(" + yearToProcess.ToString() + ")";
}
}
catch (System.InvalidOperationException)
{
}
}
break;
case ChartID.ClassHeadcountByCourseIndustryMonth:
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-2).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-1).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(0).ToString("MMM") });
this.DataSetCollection.Add(new ChartDataSet { Color = "ff6600", SeriesName = "Course" });
this.DataSetCollection.Add(new ChartDataSet { Color = "32df00", SeriesName = "Industry" });
foreach (Category catagory in this.Categories)
{
try
{
int classHeadCountsCourse = queries.GetClassHeadCountsCourse(currentUser, DateTime.ParseExact(catagory.Label, "MMM", null).Month);
int classHeadCountsIndustry = queries.GetClassHeadCountsIndustry(currentUser, DateTime.ParseExact(catagory.Label, "MMM", null).Month);
//if (classHeadCountsCourse > 0 && classHeadCountsIndustry > 0)
//{
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = (classHeadCountsCourse > 0) ? classHeadCountsCourse.ToString():"", Link = (currentUser.Role == SandlerRoles.Client) ? "" : (classHeadCountsCourse > 0) ? ChartHelper.GeneratePageLink(catagory.Label, this.DrillChartIds):"" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = (classHeadCountsIndustry > 0) ? classHeadCountsIndustry.ToString():"", Link = (currentUser.Role == SandlerRoles.Client) ? "" : (classHeadCountsIndustry > 0) ? ChartHelper.GeneratePageLink(catagory.Label, this.DrillChartIds):"" });
//}
}
catch (System.InvalidOperationException)
{
}
}
//}
break;
case ChartID.ActualDollarsBookedComparisonGoal:
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-4).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-3).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-2).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(-1).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(0).ToString("MMM") });
this.Categories.Add(new Category { Label = DateTime.Now.AddMonths(1).ToString("MMM") });
this.DataSetCollection.Add(new ChartDataSet { Color = "0066ff", SeriesName = "$$Booked" });
this.DataSetCollection.Add(new ChartDataSet { Color = "ffff99", SeriesName = "% of Goal" });
foreach (Category catagory in this.Categories)
{
try
{
long actualDollarsBooked = queries.GetActualDollarsBooked(currentUser, DateTime.ParseExact(catagory.Label, "MMM", null).Month);
long goalOfDollarsBooked = queries.GetGoalOfDollarsBooked(currentUser, DateTime.ParseExact(catagory.Label, "MMM", null).Month);
//if (actualDollarsBooked > 0 && goalOfDollarsBooked > 0)
//{
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Label = catagory.Label, Value = actualDollarsBooked.ToString(), Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink("", this.DrillChartIds) });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Label = catagory.Label, Value = ((Convert.ToDouble(actualDollarsBooked) / Convert.ToDouble(goalOfDollarsBooked)) * 100).ToString("#.##"), Link = (currentUser.Role == SandlerRoles.Client) ? "" : ChartHelper.GeneratePageLink("", this.DrillChartIds) });
//}
}
catch (System.InvalidOperationException)
{
}
}
break;
#endregion
case ChartID.GapAnalysis:
this.Categories.Add(new Category { Label = "Sales Cycle Time" });
this.Categories.Add(new Category { Label = "Sales Efficiency" });
this.Categories.Add(new Category { Label = "Sales Qualification" });
this.Categories.Add(new Category { Label = "Sales Rep Retention" });
this.Categories.Add(new Category { Label = "Quota Achievement" });
this.Categories.Add(new Category { Label = "Sandler Trng Benefits" });
GATracker gaRecord = null;
GapAnalysisRepository gaData = new GapAnalysisRepository();
this.DataSetCollection.Add(new ChartDataSet { Color = "0000FF", SeriesName = "As-Is" });
this.DataSetCollection.Add(new ChartDataSet { Color = "8A4B08", SeriesName = "To-Be" });
gaRecord = gaData.GetGATrackerById(int.Parse(SearchParameter));
if (gaRecord != null)
{
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsSalesCycleTimePercentVal.Value.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsSalesEfficiencyPercentVal.Value.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsSalesQualificationPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsSalesRepRetentionPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsQuotaAchievementPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[0].SetsCollection.Add(new SetValue { Value = gaRecord.AsIsTrngBenefitsPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeSalesCycleTimePercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeSalesEfficiencyPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeSalesQualificationPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeSalesRepRetentionPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeQuotaAchievementPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
this.DataSetCollection[1].SetsCollection.Add(new SetValue { Value = gaRecord.ToBeTrngBenefitsPercentVal.ToString(), Link = "GapAnalysisCreate.aspx" });
}
break;
default:
break;
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
SandlerModels.DataIntegration.DataQueries queries = new SandlerModels.DataIntegration.DataQueries();
chartParams = new List<ChartParameter>();
chartParams.Add(new ChartParameter { Value = (DateTime.Now.Year - 2).ToString() });
chartParams.Add(new ChartParameter { Value = (DateTime.Now.Year - 1).ToString() });
chartParams.Add(new ChartParameter { Value = DateTime.Now.Year.ToString() });
IEnumerable<SandlerModels.DataIntegration.SalesTotalByMonthVM> salesTotalData;
DataTable data = new DataTable();
DataRow dr;
int columnIndex = 0;
data.Columns.Add(new DataColumn(" "));
string[] monthNames = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;
foreach (string monthName in monthNames) // writing out
{
if (!string.IsNullOrEmpty(monthName))
{
dr = data.NewRow();
dr[columnIndex] = monthName.Substring(0, 3);
data.Rows.Add(dr);
}
}
foreach (ChartParameter parameter in chartParams)
{
data.Columns.Add(new DataColumn((parameter.Value == DateTime.Now.Year.ToString()) ? parameter.Value + "*" : parameter.Value));
columnIndex++;
try
{
salesTotalData = queries.GetSalesTotalByYear(CurrentUser, int.Parse(parameter.Value));
if (salesTotalData != null)
{
var salesDataForAYear = from opportunity in salesTotalData
group opportunity by new { opportunity.CloseDate.Month }
into grp
select new { TotalValue = grp.Sum(record => record.Value), MonthName = ChartHelper.GetMonthName(grp.Key.Month) };
foreach(DataRow dR in data.Rows)
{
foreach (var record in salesDataForAYear)
{
if (record.MonthName == dR[0].ToString())
dR[columnIndex] = record.TotalValue/1000;
}
}
}
}
catch (Exception ex)
{
}
}
gvSalesTotal.DataSource = data;
gvSalesTotal.DataBind();
}
}