public void comprehensiveReport()
{
var query = buildComprehensiveQuery();
var report = new rptLearnerStatusComprehensive();
prepareComprehensiveReport(report);
var data = Database.Query(query);
report.DataSource = data;
report.ShowPreviewDialog();
}
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 prepareComprehensiveReport(rptLearnerStatusComprehensive report)
{
report.Bands["Detail"].Visible = !this.HideDetails;
report.learnerHeaderTable.Visible = !this.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";
}
}
private string buildCondensedQuery()
{
SQLStringBuilder queryString = new SQLStringBuilder();
var report = new rptLearnerStatusComprehensive();
selectCondensedColumns(queryString);
includeRegDate(queryString, UseDate);
includeDivision(queryString);
limitToRegisteredEnrollments(queryString);
if (ShouldFilterByStatus)
{
filterByStatus(queryString);
}
if (!IncludeECD)
{
excludeECD(queryString);
}
if (ShouldFilterByCollege)
{
filterByCollege(queryString);
}
groupByDivision(queryString);
orderByLearnerNumber(queryString);
return queryString.ToString();
}