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