private rptEnrollment createEnrollmentReport()
{
var report = new rptEnrollment();
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["collegeStreet"].Text = "\"" + division["Street"] + "\"";
report.DataDefinition.FormulaFields["collegeCity"].Text = "\"" + division["City"] + "\"";
report.DataDefinition.FormulaFields["collegeSubburb"].Text = "\"" + division["Subburb"] + "\"";
report.DataDefinition.FormulaFields["collegeFax"].Text = "\"" + division["Fax"] + "\"";
report.DataDefinition.FormulaFields["collegeTel"].Text = "\"" + division["Tel"] + "\"";
report.DataDefinition.FormulaFields["collegeCode"].Text = "\"" + division["Code"] + "\"";
report.DataDefinition.FormulaFields["Course"].Text = "\"" + course + "\"";
report.DataDefinition.FormulaFields["departmentOfEducation"].Text = "\"" + division["departmentOfEducation"] + "\"";
report.DataDefinition.FormulaFields["umalusiNumber"].Text = "\"" + division["umalusiNumber"] + "\"";
//theSet.Relations.Add(newRelation);
//theSet.WriteXmlSchema("C:\\temp.xml");
report.SetDataSource(theSet);
return report;
}
private void previewToolStripMenuItem_Click(object sender, EventArgs e)
{
string[] splitter = SplitByString(cboEnrollments.Text, "-");
if (cboEnrollments.Items.Count == 0 || Database.Count("enrollments", "idEnrollments = '" + splitter[1] + "'") == 0)
{
MessageBox.Show("No Enrollments for this learner.");
return;
}
MySqlCommand newCommand =
new MySqlCommand(
@"SELECT a.RegDate, a.DateOfFirstLesson, a.LearnerKey, e.Name, c.idComponents, e.ComponentAbrev, d.serialNumber, d.dateIssued
FROM enrollments a JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments
JOIN coursecomponents c on c.idCourses = b.idCourses LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments
and d.idCourseComponents = c.idComponents JOIN components e on e.idCourseComponents = c.idComponents
WHERE a.idEnrollments = '" +
splitter[1] + "'");
MySqlCommand secondCommand =
new MySqlCommand(@"SELECT * FROM learner_details WHERE Division = '" + GlobalProperties.loggedOnUserDivison +
"' AND learnerNumber = '" + GlobalProperties.strLearnerNumber + "'");
MySqlCommand thirdCommand = new MySqlCommand("SELECT * FROM paymentinfo WHERE idEnrollments = '" +
splitter[1] + "'");
var firstAdapter =
new MySqlDataAdapter(
@"SELECT *
FROM enrollments a JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments
JOIN coursecomponents c on c.idCourses = b.idCourses LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments
and d.idCourseComponents = c.idComponents JOIN components e on e.idCourseComponents = c.idComponents
WHERE a.idEnrollments = '" +
splitter[1] + @"' AND ((NOT(isElective = '1' AND d.idEnrollments is null)) OR isElective IS NULL) AND (d.status <> 'inactive' OR d.status IS NULL)
ORDER BY e.Name", Database.createConnection());
var secondAdapter = new MySqlDataAdapter(@"SELECT * FROM learner_details WHERE Division = '" + GlobalProperties.loggedOnUserDivison +
"' AND learnerNumber = '" + GlobalProperties.strLearnerNumber + "'", Database.createConnection());
var thirdAdapter = new MySqlDataAdapter("SELECT * FROM paymentinfo WHERE idEnrollments = '" +
splitter[1] + "'", Database.createConnection());
var division = Database.Row("division", "DivisionAbrev = '" + GlobalProperties.loggedOnUserDivison + "'");
var thedialog = new PrintDialog();
//thedialog.ShowDialog();
//var learner = Database.Table("learner_details", "Division = '" + clsGlobal.loggedOnUserDivison +
//"' AND learnerNumber = '" + clsGlobal.strLearnerNumber + "'");
//var table = Database.Table(newCommand);
DataSet theSet = new DataSet();
firstAdapter.Fill(theSet, "Results");
secondAdapter.Fill(theSet, "Learner");
thirdAdapter.Fill(theSet, "Payment");
//learner.WriteXmlSchema("C:\\tempSchemalearner.xml");
//table.WriteXmlSchema("C:\\tempSchemaenrollments.xml");
//theSet.WriteXml("C:\\LAD_DATA_DUMP\\enrollmentData.xml", XmlWriteMode.WriteSchema);
var report = new rptEnrollment();
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["collegeStreet"].Text = "\"" + division["Street"] + "\"";
report.DataDefinition.FormulaFields["collegeCity"].Text = "\"" + division["City"] + "\"";
report.DataDefinition.FormulaFields["collegeSubburb"].Text = "\"" + division["Subburb"] + "\"";
report.DataDefinition.FormulaFields["collegeFax"].Text = "\"" + division["Fax"] + "\"";
report.DataDefinition.FormulaFields["collegeTel"].Text = "\"" + division["Tel"] + "\"";
report.DataDefinition.FormulaFields["collegeCode"].Text = "\"" + division["Code"] + "\"";
report.DataDefinition.FormulaFields["Course"].Text = "\"" + lblDescription.Text + "\"";
report.DataDefinition.FormulaFields["departmentOfEducation"].Text = "\"" + division["departmentOfEducation"] + "\"";
report.DataDefinition.FormulaFields["umalusiNumber"].Text = "\"" + division["umalusiNumber"] + "\"";
DataRelation myRelation = new DataRelation("newRelation", theSet.Tables[1].Columns["LearnerKey"], theSet.Tables[0].Columns["LearnerKey"]);
//DataRelation newRelation = new DataRelation("secondRelation",
// theSet.Tables["Results"].Columns["idEnrollments"],
// theSet.Tables["Payment"].Columns["idEnrollments"]);
theSet.Relations.Add(myRelation);
//theSet.Relations.Add(newRelation);
//theSet.WriteXmlSchema("C:\\temp.xml");
report.SetDataSource(theSet);
//report.PrintOptions.PrinterName = thedialog.PrinterSettings.PrinterName;
//report.PrintToPrinter(2, false, 0, 0);
var reportViewer = new frmReport(report, false);
reportViewer.Show();
}