private void barButtonItem50_ItemClick_1(object sender, ItemClickEventArgs e)
{
bool allColleges = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for all colleges?", "College Criteria",MessageBoxButtons.YesNo) == DialogResult.Yes;
bool activeOnly = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for active students only?", "Learner Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
string query = null;
if (allColleges && !activeOnly)
query = @"SELECT ld.division, ld.learnerNumber, CONCAT(ld.Name,' ',ld.surname) as name, ec.idCourseComponents, ec.idEnrollments, c.Name, ld.learnerStatus
FROM enrollmentComponents ec
JOIN enrollments e on ec.idEnrollments = e.idEnrollments
JOIN learner_details ld on ld.learnerKey = e.learnerKey
JOIN components c on c.idCourseComponents = ec.idCourseComponents
WHERE ec.outcome = 'N.Y.C.'";
else if (allColleges && activeOnly)
query = @"SELECT ld.division, ld.learnerNumber, CONCAT(ld.Name,' ',ld.surname) as name, ec.idCourseComponents, ec.idEnrollments, c.Name, ld.learnerStatus
FROM enrollmentComponents ec
JOIN enrollments e on ec.idEnrollments = e.idEnrollments
JOIN learner_details ld on ld.learnerKey = e.learnerKey
JOIN components c on c.idCourseComponents = ec.idCourseComponents
WHERE ec.outcome = 'N.Y.C.' AND (ld.learnerStatus = 'Active' OR ld.learnerStatus = 'Handover_Attending')";
else if (!allColleges && activeOnly)
query = @"SELECT ld.division, ld.learnerNumber, CONCAT(ld.Name,' ',ld.surname) as name, ec.idCourseComponents, ec.idEnrollments, c.Name, ld.learnerStatus
FROM enrollmentComponents ec
JOIN enrollments e on ec.idEnrollments = e.idEnrollments
JOIN learner_details ld on ld.learnerKey = e.learnerKey
JOIN components c on c.idCourseComponents = ec.idCourseComponents
WHERE ec.outcome = 'N.Y.C.' AND (ld.learnerStatus = 'Active' OR ld.learnerStatus = 'Handover_Attending') AND
ld.division = '" + GlobalProperties.loggedOnUserDivison + "'";
else if (!allColleges && !activeOnly)
query = @"SELECT ld.division, ld.learnerNumber, CONCAT(ld.Name,' ',ld.surname) as name, ec.idCourseComponents, ec.idEnrollments, c.Name, ld.learnerStatus
FROM enrollmentComponents ec
JOIN enrollments e on ec.idEnrollments = e.idEnrollments
JOIN learner_details ld on ld.learnerKey = e.learnerKey
JOIN components c on c.idCourseComponents = ec.idCourseComponents
WHERE ec.outcome = 'N.Y.C.' AND
ld.division = '" + GlobalProperties.loggedOnUserDivison + "'";
GlobalProperties.tempQuery = query;
BackgroundWorker bw = GlobalProperties.bw = new BackgroundWorker();
bw.DoWork += new DoWorkEventHandler(resubsOnlyWorker);
bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler
(bw_RunWorkerCompleted);
bw.WorkerSupportsCancellation = true;
// Create a progress form on the UI thread
myProgressForm = new frmProgress();
// Kick off the Async thread
bw.RunWorkerAsync();
// Lock up the UI with this modal progress form.
myProgressForm.ShowDialog(this);
myProgressForm = null;
var report = new rptResubs();
report.DataSource = learners;
report.ShowPreviewDialog();
learners = null;
//learners.WriteXml("C:\\LAD_DATA_DUMP\\resubs.xml", XmlWriteMode.WriteSchema);
}
private void resubStillValidReportClick(object sender, ItemClickEventArgs e)
{
bool allColleges = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for all colleges?", "College Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
bool activeOnlyBool = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for active learners only?", "Learner Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
string activeOnly = activeOnlyBool ? "ld.LearnerStatus IN ('Active','Handover_Attending') AND " : "";
string query = null;
if (allColleges)
query = @"SELECT ld.learnerKey, ld.learnerNumber, ld.division, ld,learnerStatus,
CONCAT(ld.Name,' ',ld.Surname) as Name, e.idEnrollments, d.Name as courseName, c.Name as examName, r.result, c.componentAbrev,
c.Name, rl.learnerKey, r.capturedDate, ADDDATE(r.capturedDate, INTERVAL 6 MONTH) as expiryDate
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN enrollmentComponents ec ON ec.idEnrollments = e.idEnrollments
JOIN components d ON d.idCourseComponents = ec.idCourseComponents
LEFT JOIN results r on r.idEnrollments = e.idEnrollments AND r.idCourseComponents LIKE concat(ec.idCourseComponents,'%')
LEFT JOIN components c on c.idCourseComponents = r.idCourseComponents
LEFT JOIN (
SELECT ld.learnerKEy, d.myParent
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN results r on r.idEnrollments = e.idEnrollments
JOIN components c on c.idCourseComponents = r.idCourseComponents
JOIN components d on d.idCourseComponents = c.myParent
WHERE c.Name LIKE '%Resub%' AND ((r.Result/c.TotalMark)*100) >= 60
) as rl on rl.learnerKey = ld.learnerKey AND rl.myParent = ec.idCourseComponents
WHERE " + activeOnly + @"
ec.outcome IN ('N.Y.C.','Not yet Competent') AND c.ComponentAbrev = 'EX' AND rl.learnerKey IS NULL
AND c.Name NOT LIKE '%Pre%' AND ADDDATE(r.capturedDate, INTERVAL 6 MONTH) >= '" + DateTime.Today.ToString("yyyyMMdd") +
"' ORDER BY learnerNumber";
else
query = @"SELECT ld.learnerKey, ld.learnerNumber, ld.division, ld.learnerStatus,
CONCAT(ld.Name,' ',ld.Surname) as Name, e.idEnrollments, d.Name as courseName, c.Name as examName, r.result, c.componentAbrev,
c.Name, rl.learnerKey, r.capturedDate, ADDDATE(r.capturedDate, INTERVAL 6 MONTH) as expiryDate
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN enrollmentComponents ec ON ec.idEnrollments = e.idEnrollments
JOIN components d ON d.idCourseComponents = ec.idCourseComponents
LEFT JOIN results r on r.idEnrollments = e.idEnrollments AND r.idCourseComponents LIKE concat(ec.idCourseComponents,'%')
LEFT JOIN components c on c.idCourseComponents = r.idCourseComponents
LEFT JOIN (
SELECT ld.learnerKEy, d.myParent
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN results r on r.idEnrollments = e.idEnrollments
JOIN components c on c.idCourseComponents = r.idCourseComponents
JOIN components d on d.idCourseComponents = c.myParent
WHERE c.Name LIKE '%Resub%' AND ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND ((r.Result/c.TotalMark)*100) >= 60
) as rl on rl.learnerKey = ld.learnerKey AND rl.myParent = ec.idCourseComponents
WHERE ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND " + activeOnly + @"
ec.outcome IN ('N.Y.C.','Not yet Competent') AND c.ComponentAbrev = 'EX' AND rl.learnerKey IS NULL
AND c.Name NOT LIKE '%Pre%' AND ADDDATE(r.capturedDate, INTERVAL 6 MONTH) >= '" + DateTime.Today.ToString("yyyyMMdd") +
"' ORDER BY learnerNumber";
learners = Database.Query(query);
var report = new rptResubs();
if (!activeOnlyBool)
{
report.statusLabel.Visible = true;
report.statusText.Visible = true;
}
//learners.WriteXml(@"C:\LAD_DATA_DUMP\resubs.xml", XmlWriteMode.WriteSchema);
report.DataSource = learners;
report.ShowPreviewDialog();
learners = null;
}
private void barButtonItem44_ItemClick_1(object sender, ItemClickEventArgs e)
{
bool allColleges = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for all colleges?", "College Criteria",MessageBoxButtons.YesNo) == DialogResult.Yes;
bool activeOnlyBool = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to generate data for active learners only?", "Learner Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
string activeOnly = activeOnlyBool ? "ld.LearnerStatus IN ('Active','Handover_Attending') AND " : "";
string query = null;
if (allColleges)
query = @"SELECT ld.learnerKey, ld.learnerNumber, ld.division,
CONCAT(ld.Name,' ',ld.Surname) as Name, e.idEnrollments, ec.idCourseComponents as courseName, r.idCourseComponents, c.componentAbrev,
c.Name as examName, rl.learnerKey, r.capturedDate as examDate, ADDDATE(r.capturedDate, INTERVAL 6 MONTH) as expiryDate
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN enrollmentComponents ec ON ec.idEnrollments = e.idEnrollments
LEFT JOIN results r on r.idEnrollments = e.idEnrollments AND r.idCourseComponents LIKE concat(ec.idCourseComponents,'%')
LEFT JOIN components c on c.idCourseComponents = r.idCourseComponents
LEFT JOIN (
SELECT ld.learnerKEy, d.myParent
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN results r on r.idEnrollments = e.idEnrollments
JOIN components c on c.idCourseComponents = r.idCourseComponents
JOIN components d on d.idCourseComponents = c.myParent
WHERE c.Name LIKE '%Final%'
) as rl on rl.learnerKey = ld.learnerKey AND rl.myParent = ec.idCourseComponents
WHERE " + activeOnly + @"
ec.outcome IN ('N.Y.C.','Not yet Competent') AND c.ComponentAbrev = 'EX' AND rl.learnerKey IS NULL
AND c.Name LIKE '%Pre%'
GROUP BY ld.learnerKey, ec.idCourseComponents
ORDER BY learnerNumber";
else
query = @"SELECT ld.learnerKey, ld.learnerNumber, ld.division,
CONCAT(ld.Name,' ',ld.Surname) as Name, e.idEnrollments, ec.idCourseComponents as courseName, r.idCourseComponents, c.componentAbrev,
c.Name as examName, rl.learnerKey, r.capturedDate as examDate, ADDDATE(r.capturedDate, INTERVAL 6 MONTH) as expiryDate
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN enrollmentComponents ec ON ec.idEnrollments = e.idEnrollments
LEFT JOIN results r on r.idEnrollments = e.idEnrollments AND r.idCourseComponents LIKE concat(ec.idCourseComponents,'%')
LEFT JOIN components c on c.idCourseComponents = r.idCourseComponents
LEFT JOIN (
SELECT ld.learnerKEy, d.myParent
FROM learner_details ld JOIN enrollments e on e.learnerKEy = ld.learnerKey
JOIN results r on r.idEnrollments = e.idEnrollments
JOIN components c on c.idCourseComponents = r.idCourseComponents
JOIN components d on d.idCourseComponents = c.myParent
WHERE c.Name LIKE '%Final%' and ld.division = '" + GlobalProperties.loggedOnUserDivison + @"'
) as rl on rl.learnerKey = ld.learnerKey AND rl.myParent = ec.idCourseComponents
WHERE ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND " + activeOnly + @"
ec.outcome IN ('N.Y.C.','Not yet Competent') AND c.ComponentAbrev = 'EX' AND rl.learnerKey IS NULL
AND c.Name LIKE '%Pre%'
GROUP BY ld.learnerKey, ec.idCourseComponents
ORDER BY learnerNumber";
learners = Database.Query(query);
var report = new rptResubs();
report.DataSource = learners;
report.ShowPreviewDialog();
learners = null;
}
