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