private void barButtonItem45_ItemClick(object sender, ItemClickEventArgs e)
{
MonthInputDialogResult theResults = MonthInputDialog.Show();
//int month;
if (theResults.Result == DialogResult.OK)
{
GlobalProperties.monthInt = clsFunctions.getMonthInt(theResults.Submission);
}
else
{
return;
}
bool allColleges = MessageBox.Show("Do you want to generate the data for all colleges?",
"College Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
bool activeOnly = MessageBox.Show("Do you want to generate the data for active learners only?",
"Learner Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes;
bool includeECD = MessageBox.Show("Do you want to Include ECD students?",
"ECD", MessageBoxButtons.YesNo) == DialogResult.Yes;
var criteria = new List<String>();
if (!allColleges)
criteria.Add("division = '" + GlobalProperties.loggedOnUserDivison + "' ");
if (activeOnly)
criteria.Add("learnerStatus = 'Active' OR learnerStatus = 'Handover_Attending'");
if (!includeECD)
criteria.Add("division NOT LIKE '%ECD%'");
GlobalProperties.tempQuery = String.Join(" AND ", criteria);
BackgroundWorker bw = GlobalProperties.bw = new BackgroundWorker();
bw.DoWork += new DoWorkEventHandler(registrationsWorker);
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);
if (!myProgressForm.Cancelled)
{
rptRegistrations myReport = new rptRegistrations();
myReport.xrLabel2.Text = "Registration Expiries";
myReport.DataSource = learners;
myReport.ShowPreviewDialog();
}
myProgressForm = null;
learners = null;
}
private void barButtonItem44_ItemClick(object sender, ItemClickEventArgs e)
{
DataSet theData;
MonthInputDialogResult theResults = MonthInputDialog.Show();
if (theResults.Result == DialogResult.OK)
{
int month;
switch (theResults.Submission)
{
case "January":
month = 1;
break;
case "February":
month = 2;
break;
case "March":
month = 3;
break;
case "April":
month = 4;
break;
case "May":
month = 5;
break;
case "June":
month = 6;
break;
case "July":
month = 7;
break;
case "August":
month = 8;
break;
case "September":
month = 9;
break;
case "October":
month = 10;
break;
case "November":
month = 11;
break;
case "December":
month = 12;
break;
default:
month = 0;
break;
}
if (clsFunctions.CheckRights("12") && MessageBox.Show("Do you want to view the report for all colleges?", "Report Criteria", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (MessageBox.Show("Do you want to view active students only?", "Criteria", MessageBoxButtons.YesNo) == DialogResult.No)
theData = Database.Query(@"SELECT l.learnerNumber, l.LearnerStatus, (SELECT DivisionName FROM division d WHERE d.DivisionAbrev = l.division) as Division, e.idEnrollments, e.regdate, e.numRegistrations, a.Attendance, e.regDate + interval 24*(e.numRegistrations) month as expiry
FROM learner_details l
JOIN enrollments e on e.learnerKey = l.learnerKey
JOIN
(
SELECT learnerNumber, division, max(`dateTime`)as Attendance
FROM learneractivity
GROUP BY learnerNumber, division
ORDER BY division, learnerNumber
) as a ON l.division = a.division AND l.learnerNumber = a.learnerNumber
WHERE MONTH(e.regDate) = '" + month.ToString() + "' AND DATE( e.regDate + interval 24*(e.numRegistrations) month) <= " + DateTime.Now.AddMonths(6).ToString("yyyyMMdd") +
" ORDER BY e.regDate, l.LEarnerStatus, l.LEarnerNumber");
else
theData = Database.Query(@"SELECT l.learnerNumber, l.LearnerStatus, (SELECT DivisionName FROM division d WHERE d.DivisionAbrev = l.division) as Division, e.idEnrollments, e.regdate, e.numRegistrations, a.Attendance, e.regDate + interval 24*(e.numRegistrations) month as expiry
FROM learner_details l
JOIN enrollments e on e.learnerKey = l.learnerKey
JOIN
(
SELECT learnerNumber, division, max(`dateTime`)as Attendance
FROM learneractivity
GROUP BY learnerNumber, division
ORDER BY division, learnerNumber
) as a ON l.division = a.division AND l.learnerNumber = a.learnerNumber AND (l.LearnerStatus = 'Active' OR l.learnerStatus = 'Handover_Attending')
WHERE MONTH(e.regDate) = '" + month.ToString() + "' AND DATE( e.regDate + interval 24*(e.numRegistrations) month) <= " + DateTime.Now.AddMonths(6).ToString("yyyyMMdd") +
" ORDER BY e.regDate, l.LEarnerStatus, l.LEarnerNumber");
}
else
{
if (MessageBox.Show("Do you want to view active students only?", "Criteria", MessageBoxButtons.YesNo) == DialogResult.No)
theData = Database.Query(@"SELECT l.learnerNumber, l.LearnerStatus, (SELECT DivisionName FROM division d WHERE d.DivisionAbrev = l.division) as Division, e.idEnrollments, e.regdate, e.numRegistrations, a.Attendance, e.regDate + interval 24*(e.numRegistrations) month as expiry
FROM learner_details l
JOIN enrollments e on e.learnerKey = l.learnerKey
JOIN
(
SELECT learnerNumber, division, max(`dateTime`)as Attendance
FROM learneractivity
GROUP BY learnerNumber, division
ORDER BY division, learnerNumber
) as a ON l.division = a.division AND l.learnerNumber = a.learnerNumber
WHERE MONTH(e.regDate) = '" + month.ToString() + "' AND l.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND DATE( e.regDate + interval 24*(e.numRegistrations) month) <= " + DateTime.Now.AddMonths(6).ToString("yyyyMMdd") +
" ORDER BY e.regDate, l.LEarnerStatus, l.LEarnerNumber");
else
theData = Database.Query(@"SELECT l.learnerNumber, l.LearnerStatus, (SELECT DivisionName FROM division d WHERE d.DivisionAbrev = l.division) as Division, e.idEnrollments, e.regdate, e.numRegistrations, a.Attendance, e.regDate + interval 24*(e.numRegistrations) month as expiry
FROM learner_details l
JOIN enrollments e on e.learnerKey = l.learnerKey
JOIN
(
SELECT learnerNumber, division, max(`dateTime`)as Attendance
FROM learneractivity
GROUP BY learnerNumber, division
ORDER BY division, learnerNumber
) as a ON l.division = a.division AND l.learnerNumber = a.learnerNumber AND (l.LearnerStatus = 'Active' OR l.learnerStatus = 'Handover_Attending')
WHERE MONTH(e.regDate) = '" + month.ToString() + "' AND l.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND DATE( e.regDate + interval 24*(e.numRegistrations) month) <= " + DateTime.Now.AddMonths(6).ToString("yyyyMMdd") +
" ORDER BY e.regDate, l.LEarnerStatus, l.LEarnerNumber");
}
//theData.WriteXml("C:\\MyData.xml",XmlWriteMode.WriteSchema);
rptRegistrations myReport = new rptRegistrations();
myReport.xrLabel2.Text = "Registration Expiries for " + theResults.Submission;
myReport.DataSource = theData;
myReport.ShowPreviewDialog();
}
}
