private void massAssessmentReport_ItemClick(object sender, ItemClickEventArgs e) { bool allColleges = DevExpress.XtraEditors.XtraMessageBox.Show("This report can take a long time to compile. Please wait for the report to finish before printing or exporting. Do you want to continue?", "Warning!", MessageBoxButtons.YesNo) == DialogResult.Yes; if (!allColleges) return; var dateThing = new frmSubmitDate("Select Date for attendance.", "Attendance Date"); dateThing.ShowDialog(); if (!dateThing.status) return; var data = Database.Query(@"SELECT ld.learnerKey, e.idCourseComponents, CONCAT(e.idCourseComponents,'%'), e.Name, d.result, x.result, if(x.CapturedDate,x.CapturedDate,d.resultDate) as CapturedDate, d.dateModerated, d.moderated, d.outcome, x.CapturedDate as resultDate, v.Name, a.idEnrollments FROM learner_details ld JOIN enrollments a on a.learnerKey = ld.learnerKey JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments LEFT JOIN ( SELECT e.idEnrollments, ld.learnerKey, c.Name FROM enrollmentsCourses ec JOIN courses c on c.idCourses = ec.idCourses JOIN enrollments e USING(idEnrollments) JOIN learner_details ld USING (LearnerKey) WHERE c.Type <> 'CC' and e.completed IS NULL AND ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' ) as v ON v.idEnrollments = a.idEnrollments LEFT JOIN courses cor on cor.idCourses = b.idCourses JOIN coursecomponents c on c.idCourses = b.idCourses JOIN components e on e.idCourseComponents = c.idComponents LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments AND d.idCourseComponents = c.idComponents LEFT JOIN ( SELECT r.capturedDate, r.idEnrollments, r.idCourseComponents, (r.result/g.totalMark)*100 as result FROM results r JOIN components g on g.idCourseComponents = r.idCourseComponents JOIN enrollments e on e.idEnrollments = r.idEnrollments JOIN learner_details ld on ld.learnerKey = e.LearnerKey WHERE g.componentAbrev = 'EX' and (NOT g.Name LIKE '%Pre %') AND ld.learnerStatus IN ('Active','Handover_Attending') AND ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' ORDER BY capturedDate DESC ) as x ON x.idEnrollments = a.idEnrollments AND x.idCourseComponents LIKE CONCAT(e.idCourseComponents,'%') WHERE ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND ld.learnerStatus IN ('Active','Handover_Attending') AND ((NOT (e.isElective = '1' AND d.idEnrollments is null)) OR isElective IS NULL) AND (( NOT d.Status = 'Inactive') OR d.Status IS Null) AND a.completed IS NULL GROUP BY a.idEnrollments, c.idCourseComponents ORDER BY ld.learnerNumber, a.idEnrollments, e.idCourseComponents"); var myTable = Database.Table(@"SELECT la.*, lb.*, ld.learnerKey, DATE(la.dateTime) as InDate, DATE(lb.dateTime) as outDate, TIME(SUBTIME(TIME(lb.dateTime),TIME(la.dateTime))) as Duration, d.divisionName FROM learnerActivity la JOIN learner_details ld on ld.learnerNumber = la.learnerNumber AND ld.division = la.division LEFT JOIN learnerActivity lb on lb.idLearnerActivity = la.partnerID JOIN division d on d.divisionAbrev = la.division WHERE DATE(la.`dateTime`) > " + dateThing.Selection.ToString("yyyyMMdd") + " AND DATE(lb.`dateTime`) > " + dateThing.Selection.ToString("yyyyMMdd") + " AND la.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND la.`type` IN ('Attendance','Exam') AND ld.learnerStatus IN ('Active','Handover_Attending') AND la.details = 'IN' ORDER BY la.learnerNumber, la.dateTime"); data.Tables.Add(myTable); var learners = Database.Table(@"SELECT *, CONCAT(Name,' ',Surname) as learnerName , idEnrollments FROM learner_details ld JOIN enrollments e on e.learnerKey = ld.learnerKey WHERE division = '" + GlobalProperties.loggedOnUserDivison + "' AND learnerStatus IN ('Active','Handover_Attending') AND e.completed IS NULL"); learners.TableName = "learners"; data.Tables.Add(learners); var relation = new DataRelation("one", new DataColumn[] {data.Tables["learners"].Columns["learnerKey"]}, new DataColumn[] {data.Tables["GeneratedTable"].Columns["learnerKey"]},false); var relation2 = new DataRelation("two",data.Tables["learners"].Columns["idEnrollments"] ,data.Tables["MyTable"].Columns["idEnrollments"]); data.Relations.Add(relation); data.Relations.Add(relation2); var Report = new rptAssesmentReportNew(); Report.divisionLabel.Text = GlobalProperties.LoggedOnUserDivision; var college = Database.Row("SELECT * FROM division WHERE divisionAbrev = '" + GlobalProperties.loggedOnUserDivison+ "'"); Report.streetText.Text = Convert.ToString(college["Street"]); Report.suburbText.Text = Convert.ToString(college["Subburb"]); Report.cityText.Text = Convert.ToString(college["City"]); Report.telText.Text = "Tel. " + Convert.ToString(college["Tel"]); Report.commentText.Text = Report.commentText.Text.Replace("##","This Assesment report was generated on " + DateTime.Now.ToString("dd/MM/yyyy")); Report.DataSource = data; Report.ShowPreviewDialog(); //data.WriteXml(@"C:\LAD_DATA_DUMP\assRepMass.xml", XmlWriteMode.WriteSchema); }
private void assesmentReportToolStripMenuItem_Click(object sender, EventArgs e) { if (!cboEnrollments.Text.Contains("-")) { MessageBox.Show("Invalid Enrollment"); return; } var splitter = SplitByString(cboEnrollments.Text, "-"); var choice = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to view the learner's attendance as well?", "Attendance Logs", MessageBoxButtons.YesNo) == DialogResult.Yes; var data = Database.Query(@"SELECT ld.learnerKey, e.idCourseComponents, CONCAT(e.idCourseComponents,'%'), e.Name, d.result, x.result, if(x.CapturedDate,x.CapturedDate,d.resultDate) as CapturedDate, d.dateModerated, d.moderated, d.outcome, x.CapturedDate as resultDate, v.Name, a.idEnrollments FROM learner_details ld JOIN enrollments a on a.learnerKey = ld.learnerKey JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments LEFT JOIN ( SELECT e.idEnrollments, ld.learnerKey, c.Name FROM enrollmentsCourses ec JOIN courses c on c.idCourses = ec.idCourses JOIN enrollments e USING(idEnrollments) JOIN learner_details ld USING (LearnerKey) WHERE c.Type <> 'CC' AND e.idEnrollments = " + splitter[1] + @" ) as v ON v.idEnrollments = a.idEnrollments LEFT JOIN courses cor on cor.idCourses = b.idCourses JOIN coursecomponents c on c.idCourses = b.idCourses JOIN components e on e.idCourseComponents = c.idComponents LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments AND d.idCourseComponents = c.idComponents LEFT JOIN ( SELECT r.capturedDate, r.idEnrollments, r.idCourseComponents, (r.result/g.totalMark)*100 as result FROM results r JOIN components g on g.idCourseComponents = r.idCourseComponents JOIN enrollments e on e.idEnrollments = r.idEnrollments JOIN learner_details ld on ld.learnerKey = e.LearnerKey WHERE g.componentAbrev = 'EX' and (NOT g.Name LIKE '%Pre %') AND r.idEnrollments = " + splitter[1] + @" ORDER BY capturedDate DESC ) as x ON x.idEnrollments = a.idEnrollments AND x.idCourseComponents LIKE CONCAT(e.idCourseComponents,'%') WHERE a.idEnrollments = " + splitter[1] + @" AND ((NOT (e.isElective = '1' AND d.idEnrollments is null)) OR isElective IS NULL) AND (( NOT d.Status = 'Inactive') OR d.Status IS Null) GROUP BY a.idEnrollments, c.idCourseComponents ORDER BY ld.learnerNumber, a.idEnrollments, e.idCourseComponents"); var learners = Database.Table("SELECT *, CONCAT(Name,' ',Surname) as learnerName FROM learner_details WHERE ID = '" + GlobalProperties.varLearner_id + "'"); learners.TableName = "learners"; data.Tables.Add(learners); var relation2 = new DataRelation("two", data.Tables["learners"].Columns["learnerKey"], data.Tables["MyTable"].Columns["learnerKey"]); data.Relations.Add(relation2); var Report = new rptAssesmentReportNew(); if (choice) { var dateThing = new frmSubmitDate("Select Date for attendance.", "Attendance Date"); dateThing.ShowDialog(); if (!dateThing.status) return; var myTable = Database.Table(@"SELECT la.*, lb.*, ld.learnerKey, DATE(la.dateTime) as InDate, DATE(lb.dateTime) as outDate, TIME(SUBTIME(TIME(lb.dateTime),TIME(la.dateTime))) as Duration, d.divisionName FROM learnerActivity la JOIN learner_details ld on ld.learnerNumber = la.learnerNumber AND ld.division = la.division LEFT JOIN learnerActivity lb on lb.idLearnerActivity = la.partnerID JOIN division d on d.divisionAbrev = la.division WHERE DATE(la.`dateTime`) >= " + dateThing.Selection.ToString("yyyyMMdd") + " AND ld.ID = '" + GlobalProperties.varLearner_id + @"' AND la.`type` IN ('Attendance','Exam') AND la.details = 'IN' ORDER BY la.learnerNumber, la.dateTime"); data.Tables.Add(myTable); var relation = new DataRelation("one", new DataColumn[] { data.Tables["learners"].Columns["learnerKey"] }, new DataColumn[] { data.Tables["GeneratedTable"].Columns["learnerKey"] }, false); data.Relations.Add(relation); } else { Report.attendanceReport.Visible = false; } var college = Database.Row("SELECT * FROM division WHERE divisionAbrev = '" + GlobalProperties.loggedOnUserDivison + "'"); Report.streetText.Text = Convert.ToString(college["Street"]); Report.suburbText.Text = Convert.ToString(college["Subburb"]); Report.cityText.Text = Convert.ToString(college["City"]); Report.telText.Text = "Tel. " + Convert.ToString(college["Tel"]); Report.divisionLabel.Text = GlobalProperties.LoggedOnUserDivision; var theText = new frmSubmitText("Please enter comment", "Please enter comment for learner (Optional):", false); theText.ShowDialog(); if (theText.Selection == "***") return; Report.commentText.Rtf = Report.commentText.Rtf.Replace("##", theText.Selection); Report.DataSource = data; Report.ShowPreviewDialog(); }