Beispiel #1
0
        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);
        }
Beispiel #2
0
        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;
        }
Beispiel #3
0
        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;
        }