private void button1_Click(object sender, EventArgs e)
{
if (collegeCombo.Text.Length == 0) return;
using (
var data = Database.Query(@"SELECT ld.Division, YEAR(e.regDate) as year, MONTHNAME(e.regDate) as month, COUNT(ld.learnerKey) as students
FROM enrollments e JOIN learner_details ld on ld.learnerKey = e.learnerKey
WHERE ld.Division = '" + clsFunctions.GetDivisionAbrev(collegeCombo.Text) +
@"' AND YEAR(e.regDate) > " + DateTime.Today.AddYears((int)numericUpDown1.Value*-1).ToString("yyyy") +
@"
AND (SELECT COUNT(*) FROM enrollments WHERE learnerKey = ld.learnerKey) < 2
GROUP BY YEAR(e.regDate), MONTH(e.regDate)
ORDER BY YEAR(e.regDate), MONTH(e.regDate)"))
{
if (data.Tables[0].Rows.Count == 0)
{
MessageBox.Show("No data found for specified college.");
return;
}
var report = new Reports.rptLearnerStatusCondensedGraph();
var chart = report.xrChart1;
report.titleLable.Text = "Learner Registrations by Year";
report.collegeLabel.Text = collegeCombo.Text;
chart.Legend.Visible = true;
List<Series> seriesList = new List<Series>();
string year = Convert.ToString(data.Tables[0].Rows[0]["year"]);
seriesList.Add(new Series(Convert.ToString(data.Tables[0].Rows[0]["year"]),ViewType.Bar));
foreach (DataRow myRow in data.Tables[0].Rows)
{
if (year != Convert.ToString(myRow["year"]))
{
year = Convert.ToString(myRow["year"]);
seriesList.Add(new Series(Convert.ToString(myRow["year"]), ViewType.Bar));
}
findSeries(Convert.ToString(myRow["year"]), seriesList).Points.Add(new SeriesPoint(Convert.ToString(myRow["month"]), Convert.ToInt16(myRow["students"])));
}
chart.Series.AddRange(seriesList.ToArray());
report.ShowPreviewDialog();
}
}
private void button3_Click(object sender, EventArgs e)
{
var report = new rptLearnerStatusCondensedGraph();
report.collegeLabel.Text = "";
string ecd = chkIncludeECD.Checked?"":"NOT";
var logs = Database.Query(@"SELECT ld.Division, ld.learnerStatus as Status, COUNT(ld.learnerKey) as learnerCount
FROM learner_Details ld JOIN
(
SELECT learnerKey, MIN(regDate)
FROM enrollments
GROUP BY learnerKey
) as e ON e.LearnerKey = ld.LearnerKey
WHERE ld.division "+ ecd + @" LIKE '%ECD%'
AND (SELECT COUNT(*) FROM enrollments WHERE learnerKey = ld.learnerKey) < 2"
+ (chkDate.Checked ? (" AND e.`MIN(regDate)` BETWEEN " + datStart.Value.ToString("yyyyMMdd")
+" AND "+datEnd.Value.ToString("yyyyMMdd") ):" ") +
" GROUP BY ld.division, learnerStatus ORDER BY ld.learnerStatus, division");
var chart = report.xrChart1;
List<Series> theSeries = new List<Series>();
string college = "";
Series series = new Series();
foreach (DataRow theRow in logs.Tables[0].Rows)
{
if (college.CompareTo((string)theRow["Status"]) != 0)
{
series = new Series((string)theRow["Status"], ViewType.StackedBar);
theSeries.Add(series);
series.Label.Visible = false;
college = (string)theRow["Status"];
}
series.Points.Add(new SeriesPoint((string)theRow["Division"], Convert.ToString(theRow["learnerCount"])));
}
chart.Series.AddRange(theSeries.ToArray());
chart.Legend.Visible = true;
((XYDiagram)chart.Diagram).Rotated = true;
report.ShowPreviewDialog();
}
private void graphReportButton_ItemClick(object sender, ItemClickEventArgs e)
{
var report = new rptLearnerStatusCondensedGraph();
report.collegeLabel.Text = GlobalProperties.LoggedOnUserDivision;
var logs = Database.Query(@"SELECT ld.Division, ld.learnerStatus as Status, COUNT(ld.learnerKey) as learnerCount
FROM learner_Details ld JOIN
(
SELECT learnerKey, MIN(regDate)
FROM enrollments
GROUP BY learnerKey
) as e ON e.LearnerKey = ld.LearnerKey
WHERE ld.division NOT LIKE '%ECD%'
GROUP BY ld.division, learnerStatus ORDER BY ld.learnerStatus, division");
var chart = report.xrChart1;
List<Series> theSeries = new List<Series>();
string college = "";
Series series = new Series();
foreach (DataRow theRow in logs.Tables[0].Rows)
{
if (college.CompareTo((string)theRow["Status"]) != 0)
{
series = new Series((string)theRow["Status"], ViewType.StackedBar);
theSeries.Add(series);
series.Label.Visible = false;
college = (string)theRow["Status"];
}
series.Points.Add(new SeriesPoint((string)theRow["Division"], Convert.ToString(theRow["learnerCount"])));
}
chart.Series.AddRange(theSeries.ToArray());
chart.Legend.Visible = true;
((XYDiagram)chart.Diagram).Rotated = true;
report.ShowPreviewDialog();
}