private void LoadChart(RadChart chart, List<ReportData> dt, string seriesname)
{
if (dt != null)
{
chart.DataManager.DataSource = dt;
//set the column in the datasource to be used for the labels
chart.DataManager.LabelsColumn = "Value";
chart.DataManager.ValuesYColumns = new string[] { "Count" };
//sets the labels so that there are no overlaps
chart.IntelligentLabelsEnabled = true;
chart.DataBind();
chart.Series[0].Name = seriesname;
//the legend will display the labels of the items, instead of the name of the series
chart.Series[0].Appearance.LegendDisplayMode = Telerik.Charting.ChartSeriesLegendDisplayMode.Nothing;
chart.Update();
}
else { chart.Clear(); chart.Update(); chart.Refresh(); }
}
protected RadChart GenerateVersusChart(String office)
{
RadChart rc = new RadChart();
rc.Clear();
rc.PlotArea.XAxis.Items.Clear();
rc.Width = 450;
rc.Height = 300;
rc.Skin = "Black";
rc.ChartTitle.TextBlock.Text = office + ": Weekly SPA Overview (Target to Actual)";
rc.ChartTitle.TextBlock.Appearance.TextProperties.Font = new Font("Verdana", 10, FontStyle.Regular);
rc.PlotArea.EmptySeriesMessage.TextBlock.Text = "Error, database connection could not be establised.";
//rc.PlotArea.SeriesOrientation = ChartSeriesOrientation.Horizontal;
rc.Legend.Appearance.Visible = false;
rc.PlotArea.YAxis.Appearance.TextAppearance.Visible = false;
rc.PlotArea.YAxis.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;
rc.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;
rc.PlotArea.XAxis.AutoScale = false;
rc.PlotArea.YAxis.AutoScale = false;
rc.AutoLayout = true;
// Define chart target series
ChartSeries SPATargetSeries = new ChartSeries("revTarget", ChartSeriesType.Bar);
SPATargetSeries.Appearance.LegendDisplayMode = ChartSeriesLegendDisplayMode.ItemLabels;
SPATargetSeries.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;;
SPATargetSeries.Appearance.TextAppearance.TextProperties.Font = new Font("Verdana", 7, FontStyle.Regular);
rc.Series.Add(SPATargetSeries);
// Define chart values series
ChartSeries SPASeries = new ChartSeries("rev", ChartSeriesType.Bar);
SPASeries.Appearance.LegendDisplayMode = ChartSeriesLegendDisplayMode.ItemLabels;
SPASeries.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;;
SPASeries.Appearance.TextAppearance.TextProperties.Font = new Font("Verdana", 7, FontStyle.Regular);
rc.Series.Add(SPASeries);
int s = 0;
int p = 0;
int a = 0;
int tr = 0;
int pr = 0;
int no_ccas = 0;
// Grab SPA
String qry = "SELECT prh.ProgressReportID," +
"SUM((mS+tS+wS+thS+fS+xS)) as Suspects, " +
"SUM((mP+tP+wP+thP+fP+xP)) as Prospects, " +
"SUM((mA+tA+wA+thA+fA+xA)) as Approvals, " +
"SUM((mTotalRev+tTotalRev+wTotalRev+thTotalRev+fTotalRev+xTotalRev)) as TR, " +
"SUM(PersonalRevenue) as PR, " +
"COUNT(*) as CCAs, " +
"0 as RD, " +
"0 as PD " +
"FROM db_progressreport pr, db_progressreporthead prh " +
"WHERE prh.ProgressReportID = pr.ProgressReportID " +
"AND Office=@office " +
"GROUP BY prh.ProgressReportID " +
"ORDER BY StartDate DESC LIMIT 1";
DataTable dt_report_info = SQL.SelectDataTable(qry, "@office", office);
if (dt_report_info.Rows.Count > 0)
{
// Grab Target SPA
int no_631 = 0;
int no_321 = 0;
qry = "SELECT CCAType, COUNT(*) as no FROM db_progressreport WHERE ProgressReportID=@ProgressReportID GROUP BY CCAType";
DataTable ccas_types = SQL.SelectDataTable(qry, "@ProgressReportID", dt_report_info.Rows[0]["ProgressReportID"]);
if (ccas_types.Rows.Count > 0)
{
for (int j = 0; j < ccas_types.Rows.Count; j++)
{
switch (ccas_types.Rows[j]["CCAType"].ToString())
{
case "-1":
no_631 += Convert.ToInt32(ccas_types.Rows[j]["no"]);
break;
case "1":
no_631 += Convert.ToInt32(ccas_types.Rows[j]["no"]);
break;
case "2":
no_321 = Convert.ToInt32(ccas_types.Rows[j]["no"]);
break;
}
}
}
int target_s = (no_631 * 3) + (no_321 * 6);
int target_p = (no_631 * 2) + (no_321 * 3);
int target_a = no_631 + no_321;
s = Convert.ToInt32(dt_report_info.Rows[0]["Suspects"]);
p = Convert.ToInt32(dt_report_info.Rows[0]["Prospects"]);
a = Convert.ToInt32(dt_report_info.Rows[0]["Approvals"]);
tr = Convert.ToInt32(dt_report_info.Rows[0]["TR"]);
pr = Convert.ToInt32(dt_report_info.Rows[0]["PR"]);
no_ccas = Convert.ToInt32(dt_report_info.Rows[0]["CCAs"]);
int greatest = s;
greatest = Math.Max(p, greatest);
greatest = Math.Max(a, greatest);
greatest = Math.Max(a, greatest);
greatest = Math.Max(target_s, greatest);
greatest = Math.Max(target_p, greatest);
greatest = Math.Max(target_a, greatest);
greatest = Convert.ToInt32((((float)greatest / 100) * 108));
double max_value = Convert.ToDouble(greatest) + 0.5;
double step = 1;
rc.PlotArea.YAxis.AddRange(0, max_value, step);
// Actual SPA
ChartSeriesItem csi_a = new ChartSeriesItem(a, a.ToString(), Color.DodgerBlue, false);
ChartSeriesItem csi_p = new ChartSeriesItem(p, p.ToString(), Color.Magenta, false);
ChartSeriesItem csi_s = new ChartSeriesItem(s, s.ToString(), Color.Lime, false);
// Target SPA
ChartSeriesItem csi_ta = new ChartSeriesItem(target_a, target_a.ToString(), Color.DarkOrange, false);
ChartSeriesItem csi_tp = new ChartSeriesItem(target_p, target_p.ToString(), Color.DarkOrange, false);
ChartSeriesItem csi_ts = new ChartSeriesItem(target_s, target_s.ToString(), Color.DarkOrange, false);
SPASeries.AddItem(csi_a);
SPASeries.AddItem(csi_p);
SPASeries.AddItem(csi_s);
SPATargetSeries.AddItem(csi_ta);
SPATargetSeries.AddItem(csi_tp);
SPATargetSeries.AddItem(csi_ts);
rc.PlotArea.XAxis.Items.Add(new ChartAxisItem("Ap"));
rc.PlotArea.XAxis.Items.Add(new ChartAxisItem("Pr"));
rc.PlotArea.XAxis.Items.Add(new ChartAxisItem("Su"));
//rc.PlotArea.XAxis.Items.Add(new ChartAxisItem("T" + Environment.NewLine + Environment.NewLine + "A"));
SPASeries.Appearance.Border.PenStyle = System.Drawing.Drawing2D.DashStyle.Dot;
SPASeries.Appearance.Border.Visible = true;
}
else
{
rc = null;
}
return(rc);
}
// Graphs
protected void BindChart(RadChart chart, ChartSeriesType chartType, String chartTimeScale)
{
// Clear chart and reset total_usd
double total_usd = 0;
chart.Clear();
chart.PlotArea.XAxis.Items.Clear();
// Define parent chart series and format
ChartSeries parent_series = new ChartSeries("parent_series", chartType);
parent_series.Appearance.LegendDisplayMode = Telerik.Charting.ChartSeriesLegendDisplayMode.ItemLabels;
parent_series.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;
chart.Series.Add(parent_series);
// Iterate offices
double highest_price = 0;
double lowest_price = 999999;
for (int i = 0; i < offices.Rows.Count; i++)
{
String territory = (String)offices.Rows[i]["Office"];
String shortname = (String)offices.Rows[i]["ShortName"];
Color colour = Util.ColourTryParse((String)offices.Rows[i]["Colour"]);
// Toggle latest/annual data
String timeScaleExpr = String.Empty;
if (chartTimeScale == "latest")
{
timeScaleExpr = "=(SELECT SalesBookID FROM db_salesbookhead WHERE Office=@office ORDER BY StartDate DESC LIMIT 1) ";
}
else if (chartTimeScale == "annual")
{
timeScaleExpr = "IN (SELECT SalesBookID FROM db_salesbookhead WHERE Office=@office AND YEAR(StartDate)=@year) ";
}
// For each office..
String qry = "SELECT ROUND(IFNULL(CONVERT(SUM(Price*Conversion), SIGNED),0)- " +
"IFNULL((SELECT CONVERT(SUM(rl_price*Conversion), SIGNED) " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.rl_sb_id = sbh.SalesBookID " +
"AND sbh.SalesBookID " + timeScaleExpr +
"AND red_lined=1 AND IsDeleted=0 AND Office=@office),0)) as total_price " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND sbh.SalesBookID " + timeScaleExpr +
"AND deleted=0 AND IsDeleted=0";
String[] pn = { "@office", "@year" };
Object[] pv = { territory, dd_annual_year.Text };
DataTable totalprice = SQL.SelectDataTable(qry, pn, pv);
if (totalprice.Rows.Count > 0 && totalprice.Rows[0]["total_price"] != DBNull.Value)
{
double price = Convert.ToDouble(totalprice.Rows[0]["total_price"]);
String currency_terrtory = territory;
total_usd += price;
// Get max and min for chart padding
highest_price = Math.Max(highest_price, price);
lowest_price = Math.Min(lowest_price, price);
ChartSeriesItem csi_item = new ChartSeriesItem(
price,
Util.TextToCurrency(price.ToString(), "usd"),
colour,
false);
csi_item.Name = territory;
csi_item.Parent = parent_series;
parent_series.AddItem(csi_item);
if (chart.ID.Contains("bar"))
{
chart.PlotArea.XAxis.Items.Add(new ChartAxisItem(shortname));
}
else
{
csi_item.Appearance.FillStyle.MainColor = colour;
csi_item.Appearance.FillStyle.SecondColor = colour;
}
}
}
// Individual chart formatting
if (chart.ID.Contains("rc_pie"))
{
parent_series.Appearance.TextAppearance.TextProperties.Color = Color.DarkOrange;
lbl_drill_down.Text = "Click a pie section to drill down";
chart.ChartTitle.Visible = false;
chart.Height = 300;
}
else if (chart.ID.Contains("rc_bar"))
{
if (chart.ID.Contains("latest"))
{
parent_series.ActiveRegionToolTip = "Click to view the latest book for this territory.";
}
parent_series.Appearance.TextAppearance.TextProperties.Font = new Font("Verdana", 8, FontStyle.Regular);
if (lowest_price != 0)
{
lowest_price = (lowest_price + (lowest_price / 100) * 40);
}
double step = highest_price / 10;
if (step == 0)
{
step = 1;
}
rc_bar_latest.PlotArea.YAxis.AddRange(0, (highest_price + (highest_price / 100) * 20), step);
}
// Set total USD label
lbl_total_usd.Text = "Total USD: " + Util.TextToCurrency(total_usd.ToString(), "us");
}
