public Form1()
{
InitializeComponent();
radChart1.Series.Clear();
//adding the series items
ChartSeries cs = new ChartSeries();
cs.Items.Add(new ChartSeriesItem(3));
cs.Items.Add(new ChartSeriesItem(6));
cs.Items.Add(new ChartSeriesItem(4));
cs.Items.Add(new ChartSeriesItem(3));
cs.Items.Add(new ChartSeriesItem(8));
radChart1.Series.Add(cs);
//adding the colored zones
ChartMarkedZone zone1 = new ChartMarkedZone();
zone1.ValueStartY = 0;
zone1.ValueEndY = 3;
zone1.Appearance.FillStyle.MainColor = Color.LightGreen;
radChart1.PlotArea.MarkedZones.Add(zone1);
ChartMarkedZone zone2 = new ChartMarkedZone();
zone2.ValueStartY = 3;
zone2.ValueEndY = 6;
zone2.Appearance.FillStyle.MainColor = Color.LightYellow;
radChart1.PlotArea.MarkedZones.Add(zone2);
ChartMarkedZone zone3 = new ChartMarkedZone();
zone3.ValueStartY = 6;
zone3.ValueEndY = 8;
zone3.Appearance.FillStyle.MainColor = Color.PaleVioletRed;
radChart1.PlotArea.MarkedZones.Add(zone3);
radChart1.Update();
this.EnabledQSFButtons = QSFButtons.None;
}
protected void BindGraphs(DataTable dt)
{
////////////////////// Bar Graph ///////////////////////////
rc_bar.Clear();
rc_bar.PlotArea.XAxis.RemoveAllItems();
rc_bar.PlotArea.MarkedZones.Clear();
// Stylise
rc_bar.AutoLayout = true;
rc_bar.Legend.Appearance.Visible = false;
rc_bar.PlotArea.YAxis.AxisLabel.Visible = true;
rc_bar.PlotArea.XAxis.AxisLabel.Visible = true;
rc_bar.PlotArea.YAxis.AutoScale = false;
rc_bar.PlotArea.XAxis.AutoScale = false;
rc_bar.PlotArea.EmptySeriesMessage.TextBlock.Text = "There is no history series for the selected timescale.";
rc_bar.PlotArea.XAxis.AxisLabel.TextBlock.Text = "Report Week";
rc_bar.PlotArea.XAxis.Appearance.ValueFormat = Telerik.Charting.Styles.ChartValueFormat.ShortDate;
rc_bar.PlotArea.XAxis.Appearance.CustomFormat = "dd/MM/yy";
rc_bar.PlotArea.XAxis.Appearance.LabelAppearance.RotationAngle = 315;
rc_bar.PlotArea.YAxis.AxisLabel.TextBlock.Text = "Revenue";
rc_bar.ChartTitle.TextBlock.Text = "Revenue History";
// Define chart series
ChartSeries bar_series = new ChartSeries("Revenue", ChartSeriesType.Bar);
bar_series.Appearance.FillStyle.MainColor = Color.DarkSlateBlue;
int bar_height = 0;
int bar_total = 0;
for (int i = (gv.Rows.Count - 3); i > -1; i--)
{
bar_total += (Convert.ToInt32(Util.CurrencyToText(gv.Rows[i].Cells[gv.Rows[i].Cells.Count - 1].Text)));
if ((Convert.ToInt32(Util.CurrencyToText(gv.Rows[i].Cells[gv.Rows[i].Cells.Count - 1].Text))) > bar_height)
{
bar_height = (Convert.ToInt32(Util.CurrencyToText(gv.Rows[i].Cells[gv.Rows[i].Cells.Count - 1].Text)));
}
bar_series.AddItem(Convert.ToInt32(Util.CurrencyToText(gv.Rows[i].Cells[gv.Rows[i].Cells.Count - 1].Text)));
bar_series.Items[bar_series.Items.Count - 1].Label.TextBlock.Text = gv.Rows[i].Cells[gv.Rows[i].Cells.Count - 1].Text;
DateTime date = Convert.ToDateTime(dt.Rows[i]["WeekStart"].ToString().Substring(0, 10));
ChartAxisItem item = new ChartAxisItem();
item.Value = (decimal)date.ToOADate();
rc_bar.PlotArea.XAxis.AddItem(item);
bar_series.Appearance.TextAppearance.TextProperties.Color = Color.Black;
}
ChartMarkedZone bar_avg = new ChartMarkedZone();
bar_avg.Appearance.FillStyle.FillType = Telerik.Charting.Styles.FillType.Gradient;
bar_avg.Appearance.FillStyle.MainColor = Color.Orange;
bar_avg.Appearance.FillStyle.SecondColor = Color.OrangeRed;
if (bar_total != 0)
{
bar_avg.ValueStartY = (bar_total / (gv.Rows.Count - 2)) - (((bar_total / (gv.Rows.Count - 2)) / 100) * 2);
bar_avg.ValueEndY = (bar_total / (gv.Rows.Count - 2));
}
rc_bar.PlotArea.MarkedZones.Add(bar_avg);
if (rbl_bartype.SelectedIndex == 0)
{
bar_series.Type = ChartSeriesType.Bar;
}
else if (rbl_bartype.SelectedIndex == 1)
{
bar_series.Type = ChartSeriesType.Area;
}
else if (rbl_bartype.SelectedIndex == 2)
{
bar_series.Type = ChartSeriesType.SplineArea;
}
else if (rbl_bartype.SelectedIndex == 3)
{
bar_series.Type = ChartSeriesType.Bubble;
}
rc_bar.Series.Add(bar_series);
double max_value = bar_height + Convert.ToInt32((bar_height / 100) * 15);
double step = 10000;
rc_bar.PlotArea.YAxis.AddRange(0, max_value, step);
////////////////////// Line Graph //////////////////////////
rc_line.Clear();
rc_line.PlotArea.XAxis.RemoveAllItems();
rc_line.PlotArea.MarkedZones.Clear();
// Stylise
rc_line.Legend.Appearance.Visible = false;
rc_line.PlotArea.YAxis.AxisLabel.Visible = true;
rc_line.PlotArea.XAxis.AxisLabel.Visible = true;
rc_line.PlotArea.YAxis.AutoScale = false;
rc_line.PlotArea.XAxis.AutoScale = false;
rc_line.PlotArea.EmptySeriesMessage.TextBlock.Text = "There is no history series for the selected timescale.";
rc_line.PlotArea.XAxis.AxisLabel.TextBlock.Text = "Report Week";
rc_line.PlotArea.XAxis.Appearance.ValueFormat = Telerik.Charting.Styles.ChartValueFormat.ShortDate;
rc_line.PlotArea.XAxis.Appearance.CustomFormat = "dd/MM/yy";
rc_line.PlotArea.XAxis.Appearance.LabelAppearance.RotationAngle = 315;
rc_line.PlotArea.YAxis.AxisLabel.TextBlock.Text = "Approvals";
rc_line.ChartTitle.TextBlock.Text = "Approvals History";
// Define chart series
ChartSeries line_series = new ChartSeries("Approvals", ChartSeriesType.Line);
line_series.Appearance.FillStyle.MainColor = Color.DarkSlateBlue;
int line_total = 0;
int line_height = 0;
for (int i = gv.Rows.Count - 3; i > -1; i--)
{
line_series.AddItem(Convert.ToInt32(dt.Rows[i]["Approvals"]));
line_total += Convert.ToInt32(dt.Rows[i]["Approvals"]);
if (Convert.ToInt32(dt.Rows[i]["Approvals"]) > line_height)
{
line_height = Convert.ToInt32(dt.Rows[i]["Approvals"]);
}
DateTime date = Convert.ToDateTime(dt.Rows[i]["WeekStart"].ToString().Substring(0, 10));
ChartAxisItem item = new ChartAxisItem();
item.Value = (decimal)date.ToOADate();
rc_line.PlotArea.XAxis.AddItem(item);
line_series.Appearance.TextAppearance.TextProperties.Color = Color.Black;
}
ChartMarkedZone line_avg = new ChartMarkedZone();
line_avg.Appearance.FillStyle.FillType = Telerik.Charting.Styles.FillType.Gradient;
line_avg.Appearance.FillStyle.MainColor = Color.Orange;
line_avg.Appearance.FillStyle.SecondColor = Color.OrangeRed;
if (line_total != 0)
{
line_avg.ValueStartY = (line_total / (gv.Rows.Count - 2)) - 0.2;
line_avg.ValueEndY = (line_total / (gv.Rows.Count - 2));
}
rc_line.PlotArea.MarkedZones.Add(line_avg);
if (rbl_linetype.SelectedIndex == 0)
{
line_series.Type = ChartSeriesType.Line;
}
else if (rbl_linetype.SelectedIndex == 1)
{
line_series.Type = ChartSeriesType.Spline;
}
rc_line.Series.Add(line_series);
double pa_max_value = line_height + 5;
double pa_step = 5;
rc_line.PlotArea.YAxis.AddRange(0, pa_max_value, pa_step);
}
protected void grid_RowDataBound(object sender, GridViewRowEventArgs e)
{
String ter = ((GridView)sender).ToolTip;
if (e.Row.RowIndex == 3)
{
e.Row.BackColor = Color.LightSteelBlue;
e.Row.Cells[13].Text = "Annual";
e.Row.Cells[0].Font.Underline = true;
}
// Total rows
if (e.Row.RowIndex == 4 || e.Row.RowIndex == 5 || e.Row.RowIndex == 6 || e.Row.RowIndex == 7)
{
int total = 0;
for (int i = 1; i < e.Row.Cells.Count - 1; i++)
{
int this_val = 0;
Int32.TryParse(e.Row.Cells[i].Text, out this_val);
total += this_val;
if (!ter.Contains("_break"))
{
// If book value
if (e.Row.RowIndex == 4)
{
group_bookvalue[(i - 1)] = Convert.ToInt32(group_bookvalue[(i - 1)]) + this_val;
group_bookvalue[12] += this_val;
}
// If outstanding
else if (e.Row.RowIndex == 5)
{
group_outstanding[(i - 1)] = Convert.ToInt32(group_outstanding[(i - 1)]) + this_val;
group_outstanding[12] += this_val;
}
// If litigation
else if (e.Row.RowIndex == 6)
{
group_litigation[(i - 1)] = Convert.ToInt32(group_litigation[(i - 1)]) + this_val;
group_litigation[12] += this_val;
}
// If red line
else
{
group_red_line[(i - 1)] = Convert.ToInt32(group_red_line[(i - 1)]) + this_val;
group_red_line[12] += this_val;
}
}
e.Row.Cells[i].Text = Util.TextToCurrency(this_val.ToString(), "usd");
}
e.Row.Cells[13].Text = Util.TextToCurrency(total.ToString(), "usd");
}
else if (e.Row.RowIndex == 8 && ter != "Group") // ADP
{
ChartSeries cs = new ChartSeries(ter, ChartSeriesType.Line);
if (!ter.Contains("_break"))
{
cs.Appearance.LegendDisplayMode = Telerik.Charting.ChartSeriesLegendDisplayMode.SeriesName;
for (int i = 0; i < offices.Rows.Count; i++)
{
if (ter == offices.Rows[i]["Office"].ToString())
{
cs.Appearance.FillStyle.MainColor = Util.ColourTryParse(offices.Rows[i]["Colour"].ToString());
break;
}
}
rc_line_adp.Series.Add(cs);
}
for (int i = 1; i < e.Row.Cells.Count - 1; i++)
{
double adp = 0.0;
if (e.Row.Cells[i].Text == " " || e.Row.Cells[i].Text == "0")
{
e.Row.Cells[i].Text = "-";
}
else
{
String qry = "SELECT FORMAT(AVG(DATEDIFF(date_paid, ent_date)),1) as 'ADP' " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND date_paid >= ent_date " +
"AND date_paid <= NOW() " +
"AND date_paid IS NOT NULL " +
"AND deleted=0 AND IsDeleted=0 AND red_lined=0 " +
"AND sbh.SalesBookID=@sb_id";
DataTable dt_adp = SQL.SelectDataTable(qry, "@sb_id", e.Row.Cells[i].Text);
if (dt_adp.Rows.Count > 0 && dt_adp.Rows[0]["ADP"] != DBNull.Value)
{
e.Row.Cells[i].Text = dt_adp.Rows[0]["ADP"].ToString();
adp = Convert.ToDouble(dt_adp.Rows[0]["ADP"]);
group_adp[i - 1] += adp;
region_adp[i - 1] += adp;
}
else
{
e.Row.Cells[i].Text = "-";
}
}
if (!ter.Contains("_break"))
{
// cap adp to 100
if (adp > 100)
{
adp = 100;
}
ChartSeriesItem csi_item = new ChartSeriesItem(adp, " ");
cs.AddItem(csi_item);
}
}
if (cb_region.Checked)
{
if (!ter.Contains("_break"))
{
region_adp[12]++; // use 12th index as number of offices per region
}
else
{
for (int i = 1; i < e.Row.Cells.Count - 1; i++)
{
e.Row.Cells[i].Text = (region_adp[i - 1] / region_adp[12]).ToString("N1");
region_adp[i - 1] = 0;
}
region_adp[12] = 0;
}
}
}
else if (e.Row.RowIndex == 9) // PAID %
{
decimal sum = 0;
decimal val = 0;
int numnums = 0;
for (int i = 1; i < e.Row.Cells.Count - 1; i++)
{
// Fix _break header names when grouping by region
if (cb_region.Checked)
{
if (((GridView)sender).Rows[3].Cells[i].Text.Contains("_break"))
{
((GridView)sender).Rows[3].Cells[i].Text = Server.HtmlEncode(months[(i - 1)] + " " + dd_year.SelectedItem.Text);
}
}
Decimal.TryParse(e.Row.Cells[i].Text, out val);
if (!ter.Contains("_break"))
{
group_paid[(i - 1)] += val;
}
if (val != 0)
{
numnums++;
}
sum += val;
if (ter != "Group")
{
// Grab paid% history from previous day
String qry = "SELECT Paid FROM db_cashreporthistory WHERE Date=@date AND Office=@office AND Year=@year AND CalendarMonth=@cal_month";
DataTable history = SQL.SelectDataTable(qry,
new String[] { "@date", "@office", "@year", "@cal_month" },
new Object[] {
DateTime.Now.Date.Subtract(new TimeSpan(24, 0, 0)).ToString("yyyy/MM/dd").Substring(0, 10),
ter,
dd_year.SelectedItem.Text,
((GridView)sender).Columns[i].HeaderText
});
// Add coloured labels for today/yesterday
Label lbl_paid_cur = new Label();
lbl_paid_cur.Text = Server.HtmlEncode(e.Row.Cells[i].Text + "%");
ImageButton arrow = new ImageButton();
arrow.Enabled = false;
arrow.Height = arrow.Width = 10;
arrow.Attributes.Add("style", "position:relative; top:1px; left:3px;");
if (history.Rows.Count > 0)
{
if (Convert.ToDouble(e.Row.Cells[i].Text) > Convert.ToDouble(history.Rows[0]["Paid"]))
{
lbl_paid_cur.ForeColor = Color.Green;
arrow.ImageUrl = "~/Images/Icons/qr_Up.png";
}
else if (Convert.ToDouble(history.Rows[0]["Paid"]) > Convert.ToDouble(e.Row.Cells[i].Text))
{
lbl_paid_cur.ForeColor = Color.Red;
arrow.ImageUrl = "~/Images/Icons/qr_Down.png";
}
else if (Convert.ToDouble(history.Rows[0]["Paid"]) == Convert.ToDouble(e.Row.Cells[i].Text))
{
//lbl_paid_cur.ForeColor = Color.Orange;
arrow.Height = 8;
arrow.ImageUrl = "~/Images/Icons/qr_Equal.png";
}
arrow.ToolTip = "Yesterday's value: " + history.Rows[0]["Paid"].ToString() + "%";
}
else
{
arrow.Height = arrow.Width = 12;
arrow.ImageUrl = "~/Images/Icons/qr_Unknown.png";
arrow.ToolTip = "No history found";
arrow.Attributes.Add("style", "position:relative; top:2px; left:3px;");
}
e.Row.Cells[i].Controls.Add(lbl_paid_cur);
e.Row.Cells[i].Controls.Add(arrow);
}
}
decimal avg = 0;
if (sum != 0 && numnums != 0)
{
if (!ter.Contains("_break"))
{
group_paid[13] += 1;
}
avg = sum / numnums;
}
if (!ter.Contains("_break"))
{
group_paid[12] += avg;
}
e.Row.Cells[13].Text = avg.ToString("N2") + "% (avg.)";
// LAST ROW, DO GROUP
if (ter == "Group")
{
GridView gv_group = (GridView)sender;
GridViewRow bookval = gv_group.Rows[4];
GridViewRow outstanding = gv_group.Rows[5];
GridViewRow litigation = gv_group.Rows[6];
GridViewRow redline = gv_group.Rows[7];
GridViewRow adp = gv_group.Rows[8];
// Add series to group chart
rc_line_gadp.Clear();
ChartSeries cs = new ChartSeries("Group Avg", ChartSeriesType.Line);
cs.Appearance.LegendDisplayMode = Telerik.Charting.ChartSeriesLegendDisplayMode.SeriesName;
rc_line_gadp.Series.Add(cs);
double total_g_avg_value = 0;
int total_g_avg_moths = 0;
for (int j = 1; j < bookval.Cells.Count; j++)
{
// GROUP BOOKVAL
bookval.Cells[j].Text = Util.TextToCurrency(group_bookvalue[(j - 1)].ToString(), "USD");
// GROUP OUTSTANDING
outstanding.Cells[j].Text = Util.TextToCurrency(group_outstanding[(j - 1)].ToString(), "USD");
// GROUP LITIGATION
litigation.Cells[j].Text = Util.TextToCurrency(group_litigation[(j - 1)].ToString(), "USD");
// GROUP RED LINE
redline.Cells[j].Text = Util.TextToCurrency(group_red_line[(j - 1)].ToString(), "USD");
// GROUP ADP
if (group_adp[(j - 1)] == 0)
{
e.Row.Cells[j].Text = "0 (avg.)";
}
else
{
double avg_adp = (((double)group_adp[(j - 1)]) / offices.Rows.Count);
total_g_avg_value += avg_adp;
total_g_avg_moths++;
adp.Cells[j].Text = avg_adp.ToString("N2") + " (avg.)";
// Add to group chart
ChartSeriesItem csi_item = new ChartSeriesItem(avg_adp, " ");
cs.AddItem(csi_item);
}
// GROUP PAID
if (group_paid[13] == 0)
{
e.Row.Cells[j].Text = "0.0% (avg.)";
}
else
{
e.Row.Cells[j].Text = ((group_paid[(j - 1)]) / group_paid[13]).ToString("N2") + "% (avg.)";
}
}
// Add month names to line chart
rc_line_adp.PlotArea.XAxis.Items.Clear();
rc_line_gadp.PlotArea.XAxis.Items.Clear();
for (int i = 1; i < e.Row.Cells.Count - 1; i++)
{
String issue_name = ((GridView)sender).HeaderRow.Cells[i].Text;
rc_line_adp.PlotArea.XAxis.Items.Add(new ChartAxisItem(issue_name));
rc_line_gadp.PlotArea.XAxis.Items.Add(new ChartAxisItem(issue_name));
}
// Add group average line
rc_line_gadp.PlotArea.MarkedZones.Clear();
ChartMarkedZone g_avg = new ChartMarkedZone();
g_avg.Appearance.FillStyle.MainColor = Color.Orange;
double g_avg_value = total_g_avg_value / total_g_avg_moths;
g_avg.ValueStartY = g_avg_value;
g_avg.ValueEndY = g_avg_value + (g_avg_value / 100) * 2;
rc_line_gadp.PlotArea.MarkedZones.Add(g_avg);
rc_line_gadp.ChartTitle.TextBlock.Text = "Group Average Days to Pay (" + g_avg_value.ToString("N1") + " avg.)"; // set chart title
}
}
}
private void Fill_Charts(ITable pTable, Telerik.WinControls.UI.RadChart pChart, string ChartTitle)
{
pChart.Series.Clear(); //adding the series items
// pChart.ChartTitle.TextBlock.Text = ChartTitle;
ChartSeries cs = new ChartSeries();
if (ChartTitle == "مشهد")
{
cs.Items.Add(new ChartSeriesItem(90, "دکتری تخصصی"));
cs.Items.Add(new ChartSeriesItem(200, "کاردانی"));
cs.Items.Add(new ChartSeriesItem(107, "کاردانی پیوسته"));
pChart.Series.Add(cs);
cs = new ChartSeries();
cs.Items.Add(new ChartSeriesItem(1767, "کارشناسی"));
cs.Items.Add(new ChartSeriesItem(1155, "کارشناسی ارشد ناپیوسته"));
cs.Items.Add(new ChartSeriesItem(337, "کارشناسی ناپیوسته"));
pChart.Series.Add(cs);
}
else
{
cs.Items.Add(new ChartSeriesItem(150, "کاردانی"));
cs.Items.Add(new ChartSeriesItem(600, "کاردانی پیوسته"));
pChart.Series.Add(cs);
cs = new ChartSeries();
cs.Items.Add(new ChartSeriesItem(1259, "کارشناسی"));
cs.Items.Add(new ChartSeriesItem(62, "کارشناسی ارشد ناپیوسته"));
cs.Items.Add(new ChartSeriesItem(360, "کارشناسی ناپیوسته"));
pChart.Series.Add(cs);
}
pChart.ForeColor = Color.White;
ChartMarkedZone zone1 = new ChartMarkedZone();
zone1.ValueStartY = 0;
zone1.ValueEndY = 500;
zone1.Appearance.FillStyle.MainColor = Color.PaleVioletRed;
pChart.PlotArea.MarkedZones.Add(zone1);
ChartMarkedZone zone2 = new ChartMarkedZone();
zone2.ValueStartY = 500;
zone2.ValueEndY = 1000;
zone2.Appearance.FillStyle.MainColor = Color.LightYellow;
pChart.PlotArea.MarkedZones.Add(zone2);
ChartMarkedZone zone3 = new ChartMarkedZone();
zone3.ValueStartY = 1000;
zone3.ValueEndY = 1500;
zone3.Appearance.FillStyle.MainColor = Color.LightGreen;
pChart.PlotArea.MarkedZones.Add(zone3);
pChart.Series[0].Appearance.TextAppearance.TextProperties.Font = new Font("Tahoma", 8, FontStyle.Bold);
pChart.Series[0].Appearance.TextAppearance.TextProperties.Color = Color.Black;
pChart.Series[1].Appearance.TextAppearance.TextProperties.Font = new Font("Tahoma", 8, FontStyle.Bold);
pChart.Series[1].Appearance.TextAppearance.TextProperties.Color = Color.Black;
// pChart.PlotArea.DataTable.Visible = true;
// pChart.PlotArea.XAxis.Step = 2;
pChart.ChartTitle.TextBlock.Text = ChartTitle;
pChart.Skin = "Gradient";
pChart.Update();
pChart.Refresh();
pChart.Save(@"C:\Users\mgh\Desktop\" + ChartTitle + ".jpg");
}
