public void condensedReport()
        {
            var query = buildCondensedQuery();
            var report = new rptLearnerStatusCondensed();
            prepareCondensedReport(report);
            var data = Database.Query(query);

            report.DataSource = data;
            report.ShowPreviewDialog();
        }
Exemple #2
0
        private void createAndDisplayStatusReport()
        {
            string ecd = chkIncludeECD.Checked ? "" : "NOT";
            if (cboStatus.Text.Length == 0 || (cboCollege.Visible && cboCollege.Text.Length == 0))
            {
                if (cboCollege.Visible)
                {
                    MessageBox.Show("Please select a college and a status.");
                }
                else
                {
                    MessageBox.Show("Please select a status.");
                }
                return;
            }
            DataSet logs;
            DevExpress.XtraReports.UI.XtraReport report = null;
            #region condensed
            if (chkCondensed.Checked)
            {
                report = new rptLearnerStatusCondensed();

                ((rptLearnerStatusCondensed)report).Bands["Detail"].Visible = !chkHide.Checked;
                ((rptLearnerStatusCondensed)report).xrTable1.Visible = !chkHide.Checked;
                ((rptLearnerStatusCondensed)report).xrLine1.Visible = !chkHide.Checked;
                ((rptLearnerStatusCondensed)report).xrLine2.Visible = !chkHide.Checked;
                if (cboStatus.Text != "All")
                {

                    ((rptLearnerStatusCondensed)report).StatusLabel.Text = "With a status of " + cboStatus.Text;
                    if (cboCollege.Visible && cboCollege.Text == "ALL")
                    {
                        ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "All Colleges";
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey)
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate) IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey
            WHERE learnerStatus = '" + cboStatus.Text + @"' AND NOT e.theRegDate IS NULL
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division ORDER BY ld.learnerNumber");

                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey)
            FROM learner_Details ld
            WHERE learnerStatus = '" + cboStatus.Text + @"'
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division ORDER BY ld.learnerNumber");
                        }
                    }
                    else
                    {
                        if (cboCollege.Visible)
                        {
                            ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "College: " + cboCollege.Text;
                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "College: " + GlobalProperties.loggedOnUserDivison;
                        }
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey)
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate) IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey
            WHERE learnerStatus = '" + cboStatus.Text + "' AND ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) + @"' AND NOT e.theRegDate IS NULL
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division ORDER BY ld.learnerNumber");

                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey)
            FROM learner_Details ld
            WHERE learnerStatus = '" + cboStatus.Text + "' AND ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) + @"'
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division ORDER BY ld.learnerNumber");
                        }
                    }
                    //logs.WriteXml("C:\\MySchemaCondensed", XmlWriteMode.WriteSchema);

                }
                else
                {
                    //report = new rptLearnerStatusCondensed();

                    ((rptLearnerStatusCondensed)report).StatusLabel.Text = "Status: All";
                    if (cboCollege.Visible && cboCollege.Text == "ALL")
                    {
                        ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "All Colleges";
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey), ld.learnerStatus
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            WHERE ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate) IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey AND NOT e.theRegDate IS NULL
            GROUP BY division, ld.learnerStatus ORDER BY ld.learnerNumber");

                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey), ld.learnerStatus
            FROM learner_Details ld
            WHERE ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division, ld.learnerStatus ORDER BY ld.learnerNumber");
                        }
                    }
                    else
                    {
                        if (cboCollege.Visible)
                        {
                            ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "College: " + cboCollege.Text;
                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).CollegeLabel.Text = "College: " + GlobalProperties.loggedOnUserDivison;
                        }
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey), ld.learnerStatus
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate)IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey
            WHERE ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) + @"' AND NOT e.theRegDate IS NULL
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division, ld.learnerStatus ORDER BY ld.learnerNumber");

                        }
                        else
                        {
                            ((rptLearnerStatusCondensed)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.Division, COUNT(ld.learnerKey), ld.learnerStatus
            FROM learner_Details ld
            WHERE ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) + @"'
            AND ld.division " + ecd + @" LIKE  '%ECD%'
            GROUP BY division, ld.learnerStatus ORDER BY ld.learnerNumber");
                        }
                    }
                }
                //logs.WriteXml("C:\\MyData.xml", XmlWriteMode.WriteSchema);
            }
            #endregion
            #region comprehensive
            else
            {
                report = new rptLearnerStatusComprehensive();
                ((rptLearnerStatusComprehensive)report).Bands["Detail"].Visible = !chkHide.Checked;
                logs = null;
                if (cboStatus.Text != "All")
                {
                    ((rptLearnerStatusComprehensive)report).StatusLabel.Text = "With a status of " + cboStatus.Text;
                    if (cboCollege.Visible && cboCollege.Text == "ALL")
                    {
                        ((rptLearnerStatusComprehensive)report).CollegeLabel.Text = "All Colleges";
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate)IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE learnerStatus = '" + cboStatus.Text + @"' AND NOT e.theRegDate IS NULL ORDER BY ld.learnerNumber
            AND ld.division " + ecd + @" LIKE  '%ECD%'");
                            logs.WriteXml("C:\\UCO\\MyData.xml", XmlWriteMode.WriteSchema);
                        }
                        else
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld LEFT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE learnerStatus = '" + cboStatus.Text + @"' ORDER BY ld.learnerNumber
            AND ld.division " + ecd + @" LIKE  '%ECD%'");
                        }
                    }
                    else
                    {
                        if (cboCollege.Visible)
                        {
                            ((rptLearnerStatusComprehensive)report).CollegeLabel.Text = "College: " + cboCollege.Text;
                        }
                        else
                        {
                            ((rptLearnerStatusComprehensive)report).CollegeLabel.Text = "College: " + GlobalProperties.loggedOnUserDivison;
                        }
                        ((rptLearnerStatusComprehensive)report).xrLabel5.Visible = false;
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING (DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @") OR (MIN(regDate) IS NULL)
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE learnerStatus = '" + cboStatus.Text + @"'
            AND ld.division " + ecd + @" LIKE  '%ECD%' AND ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) + "' ORDER BY ld.learnerNumber");
                        }
                        else
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld LEFT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE learnerStatus = '" + cboStatus.Text +
                         @"' AND ld.Division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) +
                         @"' AND ld.division " + ecd + @" LIKE  '%ECD%'" +
                         @" ORDER BY ld.learnerNumber");
                        }
                    }
                    //logs.WriteXml("C:\\MySchemaComprehensive", XmlWriteMode.WriteSchema);
                }
                else
                {
                    ((rptLearnerStatusComprehensive)report).StatusLabel.Text = "Status: All";
                    if (cboCollege.Visible && cboCollege.Text == "ALL")
                    {
                        ((rptLearnerStatusComprehensive)report).CollegeLabel.Text = "All Colleges";
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @"
            ) as e ON e.LearnerKey = ld.LearnerKey
            WHERE ld.division " + ecd + @" LIKE  '%ECD%'
            JOIN division d on d.divisionAbrev = ld.division
            ORDER BY ld.learnerNumber");

                        }
                        else
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld LEFT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE ld.division " + ecd + @" LIKE  '%ECD%'
            ORDER BY ld.learnerNumber");
                        }
                    }
                    else
                    {
                        ((rptLearnerStatusComprehensive)report).CollegeLabel.Text = "College: " + GlobalProperties.LoggedOnUserDivision;
                        ((rptLearnerStatusComprehensive)report).xrLabel5.Visible = false;
                        if (chkDate.Checked)
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Text = "with registation dates between " + datStart.Value.ToString("dd/MM/yyyy") +
                                " and " + datEnd.Value.ToString("dd/MM/yyyy");
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld RIGHT JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey HAVING DATE(MIN(regDate)) BETWEEN " + datStart.Value.ToString("yyyyMMdd") + @"
            AND " + datEnd.Value.ToString("yyyyMMdd") + @"
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE ld.division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.LoggedOnUserDivision) +
                       @"' AND NOT e.theRegDate IS NULL ORDER BY ld.learnerNumber
            AND ld.division " + ecd + @" LIKE  '%ECD%'");
                        }
                        else
                        {
                            ((rptLearnerStatusComprehensive)report).DateLabel.Visible = false;
                            logs = Database.Query(@"SELECT ld.learnerNumber, ld.Name, ld.Surname, ld.id, DATE(e.theRegDate), ld.learnerStatus, d.divisionName as Division, ld.division as divisionAbrev
            FROM learner_Details ld LEFT OUTER JOIN
            (
            SELECT learnerKey, MIN(regDate) as theRegDate
            FROM enrollments
            GROUP BY learnerKey
            ) as e ON e.LearnerKey = ld.LearnerKey
            JOIN division d on d.divisionAbrev = ld.division
            WHERE ld.Division = '" + (cboCollege.Visible ? clsFunctions.GetDivisionAbrev(cboCollege.Text) : GlobalProperties.loggedOnUserDivison) +
            "' AND ld.division " + ecd + @" LIKE  '%ECD%'" +
                       "ORDER BY ld.learnerNumber");
                        }
                    }
                }
            }
            #endregion
            //logs.WriteXml("C:\\MyData.xml", XmlWriteMode.WriteSchema);
            //logs.WriteXml(@"C:\LAD_DATA_DUMP\learnerStatus.xml", XmlWriteMode.WriteSchema);
            report.DataSource = logs;
            report.ShowPreviewDialog();
        }
        private void prepareCondensedReport(rptLearnerStatusCondensed report)
        {
            report.Bands["Detail"].Visible = !HideDetails;
            report.xrTable1.Visible = !HideDetails;
            report.xrLine1.Visible = !HideDetails;
            report.xrLine2.Visible = !HideDetails;

            if (UseDate)
            {
                report.DateLabel.Text = String.Format("with registation dates between {0} and {1}", StartDate, EndDate);
            }
            else
            {
                report.DateLabel.Visible = false;
            }

            if (ShouldFilterByStatus)
            {
                report.StatusLabel.Text = "With a status of " + Status.GetStringValue();
            }
            else
            {
                report.StatusLabel.Text = "Status: All";
            }

            if (ShouldFilterByCollege)
            {
                report.CollegeLabel.Text = String.Format("College: {0}", College);
            }
            else
            {
                report.CollegeLabel.Text = "All Colleges";
            }
        }