public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
ReportTeacher2 rpt = new ReportTeacher2();
rpt.Site = this.Site;
return(rpt);
}
/* --------------------------------------------------------------------------------------------- */
private void myButtonReport_Click(object sender, EventArgs e)
{
if (treeViewReports.Nodes[0].Nodes[0].IsSelected)
{
string searchAem = textBoxAEM.Text.ToString();
if (searchAem == "")
{
searchAem = "0";
}
string query = "Select Student.AEM, Student.LastName, Student.FirstName, Student.AFM, SupervisorTeacher.Name, Student.StartingDate, Student.EndingDate, Supervisor.Name, Company.Name from Student, SupervisorTeacher, Supervisor, Company where (Student.AEM = " + searchAem + ") AND (Student.SupervisorTeacher_TID = SupervisorTeacher.TID) AND (Supervisor.ID = Student.Supervisor_ID) AND (Company.ComID= Supervisor.Company_ComID) ";
SqlDataAdapter daReport1 = new SqlDataAdapter(query, con);
DataSet DataSetReportStudent1 = new DataSet();
daReport1.Fill(DataSetReportStudent1);
DataTable dtReport1 = new DataTable();
DataSetReportStudent1.Tables.Add(dtReport1);
DataSetReportStudent1.WriteXmlSchema("ReportStudent1.xml");
ReportStudent1 cr = new ReportStudent1();
cr.SetDataSource(DataSetReportStudent1);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[0].Nodes[1].IsSelected)
{
string searchAem = textBoxAEM.Text.ToString();
if (searchAem == "")
{
searchAem = "0";
}
string query = "Select Student.AEM, Student.LastName, Student.FirstName, Student.AFM, SupervisorTeacher.Name, Student.StartingDate, Student.EndingDate, Supervisor.Name, Company.Name from Student, SupervisorTeacher, Supervisor, Company where (Student.AEM = " + searchAem + ") AND (Student.SupervisorTeacher_TID = SupervisorTeacher.TID) AND (Supervisor.ID = Student.Supervisor_ID) AND (Company.ComID= Supervisor.Company_ComID) ";
SqlDataAdapter daReport1 = new SqlDataAdapter(query, con);
DataSet DataSetReportStudent1 = new DataSet();
daReport1.Fill(DataSetReportStudent1);
DataTable dtReport1 = new DataTable();
DataSetReportStudent1.Tables.Add(dtReport1);
DataSetReportStudent1.WriteXmlSchema("ReportStudent1.xml");
string query2 = "Select Contacts.type, Contacts.details from Contacts, Student WHERE (Student.AEM = " + searchAem + ") AND (Student.StudID = Contacts.Student_ID)";
SqlDataAdapter daReport2 = new SqlDataAdapter(query2, con);
DataSet DataSetReportStudent2 = new DataSet();
daReport2.Fill(DataSetReportStudent2);
DataTable dtReport2 = new DataTable();
DataSetReportStudent2.Tables.Add(dtReport2);
DataSetReportStudent2.WriteXmlSchema("ReportStudent1c.xml");
string query3 = "Select address + ' ' + number, city, zipcode from address, Student WHERE (Student.AEM = " + searchAem + ") AND (Student.StudID = address.Student_StudID)";
SqlDataAdapter daReport3 = new SqlDataAdapter(query3, con);
DataSet DataSetReportStudent3 = new DataSet();
daReport3.Fill(DataSetReportStudent3);
DataTable dtReport3 = new DataTable();
DataSetReportStudent3.Tables.Add(dtReport3);
DataSetReportStudent3.WriteXmlSchema("ReportStudent1a.xml");
ReportStudent5 cr = new ReportStudent5();
cr.SetDataSource(DataSetReportStudent1);
cr.Subreports["contacts"].SetDataSource(DataSetReportStudent2);
cr.Subreports["address"].SetDataSource(DataSetReportStudent3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[0].Nodes[2].IsSelected)
{
string sqlquery = "Select Student.AEM, Student.LastName, Student.FirstName, Student.AFM, SupervisorTeacher.Name, Student.StartingDate, Student.EndingDate, Supervisor.Name, Company.Name from Student, SupervisorTeacher, Supervisor, Company where (Student.SupervisorTeacher_TID = SupervisorTeacher.TID) AND (Supervisor.ID = Student.Supervisor_ID) AND (Company.ComID= Supervisor.Company_ComID) AND (Student.EndingDate between '" + dateEndfrom + "' AND '" + dateEndUntil + "') AND (Student.StartingDate between '" + dateStartfrom + "' AND '" + dateStartUntil + "') ";
sqlquery += " AND ((TID = -1)";
string tid;
if (checkedListBoxTeacherStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxTeacherStRpt.CheckedItems.Count; i++)
{
tid = ((DataRowView)checkedListBoxTeacherStRpt.CheckedItems[i]).Row["TID"].ToString();
sqlquery += " or (TID ='" + tid + "')";
}
}
sqlquery += ") AND ((ComId = -1)";
string ComId;
if (checkedListBoxCompanyStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxCompanyStRpt.CheckedItems.Count; i++)
{
ComId = ((DataRowView)checkedListBoxCompanyStRpt.CheckedItems[i]).Row["ComId"].ToString();
sqlquery += " or (ComId ='" + ComId + "')";
}
}
sqlquery += ")";
SqlDataAdapter daReport2 = new SqlDataAdapter(sqlquery, con);
DataSet DataSetReportStudent2 = new DataSet();
daReport2.Fill(DataSetReportStudent2);
DataTable dtReport2 = new DataTable();
DataSetReportStudent2.Tables.Add(dtReport2);
DataSetReportStudent2.WriteXmlSchema("ReportStudent2.xml");
ReportStudent2 cr = new ReportStudent2();
cr.SetDataSource(DataSetReportStudent2);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[0].Nodes[3].IsSelected)
{
string sqlquery = "Select Student.AEM, Student.LastName, Student.FirstName, Student.AFM, SupervisorTeacher.Name as 'SupervisorTeacher name', Student.StartingDate, Student.EndingDate, Supervisor.Name as 'Supervisor name', Company.Name as 'company name' from Student, SupervisorTeacher, Supervisor, Company where (Student.SupervisorTeacher_TID = SupervisorTeacher.TID) AND (Supervisor.ID = Student.Supervisor_ID) AND (Company.ComID= Supervisor.Company_ComID) AND (Student.EndingDate between '" + dateEndfrom + "' AND '" + dateEndUntil + "') AND (Student.StartingDate between '" + dateStartfrom + "' AND '" + dateStartUntil + "') ";
sqlquery += " AND ((TID = -1)";
string tid;
if (checkedListBoxTeacherStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxTeacherStRpt.CheckedItems.Count; i++)
{
tid = ((DataRowView)checkedListBoxTeacherStRpt.CheckedItems[i]).Row["TID"].ToString();
sqlquery += " or (TID ='" + tid + "')";
}
}
sqlquery += ") AND ((ComId = -1)";
string ComId;
if (checkedListBoxCompanyStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxCompanyStRpt.CheckedItems.Count; i++)
{
ComId = ((DataRowView)checkedListBoxCompanyStRpt.CheckedItems[i]).Row["ComId"].ToString();
sqlquery += " or (ComId ='" + ComId + "')";
}
}
sqlquery += ")";
SqlDataAdapter daReport3 = new SqlDataAdapter(sqlquery, con);
DataSet DataSetReportStudent3 = new DataSet();
daReport3.Fill(DataSetReportStudent3);
DataTable dtReport3 = new DataTable();
DataSetReportStudent3.Tables.Add(dtReport3);
DataSetReportStudent3.WriteXmlSchema("ReportStudent2.xml");
ReportStudent3 cr = new ReportStudent3();
cr.SetDataSource(DataSetReportStudent3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[0].Nodes[4].IsSelected)
{
string sqlquery = "Select Student.AEM, Student.LastName, Student.FirstName, Student.AFM, SupervisorTeacher.Name as 'SupervisorTeacher name', Student.StartingDate, Student.EndingDate, Supervisor.Name as 'Supervisor name', Company.Name as 'company name' from Student, SupervisorTeacher, Supervisor, Company where (Student.SupervisorTeacher_TID = SupervisorTeacher.TID) AND (Supervisor.ID = Student.Supervisor_ID) AND (Company.ComID= Supervisor.Company_ComID) AND (Student.EndingDate between '" + dateEndfrom + "' AND '" + dateEndUntil + "') AND (Student.StartingDate between '" + dateStartfrom + "' AND '" + dateStartUntil + "') ";
sqlquery += " AND ((TID = -1)";
string tid;
if (checkedListBoxTeacherStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxTeacherStRpt.CheckedItems.Count; i++)
{
tid = ((DataRowView)checkedListBoxTeacherStRpt.CheckedItems[i]).Row["TID"].ToString();
sqlquery += " or (TID ='" + tid + "')";
}
}
sqlquery += ") AND ((ComId = -1)";
string ComId;
if (checkedListBoxCompanyStRpt.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxCompanyStRpt.CheckedItems.Count; i++)
{
ComId = ((DataRowView)checkedListBoxCompanyStRpt.CheckedItems[i]).Row["ComId"].ToString();
sqlquery += " or (ComId ='" + ComId + "')";
}
}
sqlquery += ")";
SqlDataAdapter daReport4 = new SqlDataAdapter(sqlquery, con);
DataSet DataSetReportStudent4 = new DataSet();
daReport4.Fill(DataSetReportStudent4);
DataTable dtReport4 = new DataTable();
DataSetReportStudent4.Tables.Add(dtReport4);
DataSetReportStudent4.WriteXmlSchema("ReportStudent3.xml");
ReportStudent4 cr = new ReportStudent4();
cr.SetDataSource(DataSetReportStudent4);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[1].Nodes[0].IsSelected)
{
if (selectedTeacher == "")
{
selectedTeacher = "0";
}
string query = "Select * FROM SupervisorTeacher WHERE TID = '" + selectedTeacher + "'";
SqlDataAdapter daReport1 = new SqlDataAdapter(query, con);
DataSet DataSetReportTeacher1 = new DataSet();
daReport1.Fill(DataSetReportTeacher1);
DataTable dtReport1 = new DataTable();
DataSetReportTeacher1.Tables.Add(dtReport1);
DataSetReportTeacher1.WriteXmlSchema("ReportTeacher1.xml");
ReportTeacher1 cr = new ReportTeacher1();
cr.SetDataSource(DataSetReportTeacher1);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[1].Nodes[1].IsSelected)
{
string query = "Select * FROM SupervisorTeacher WHERE (TID = '-1')";
string tid;
if (checkedListBoxTeachers.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxTeachers.CheckedItems.Count; i++)
{
tid = ((DataRowView)checkedListBoxTeachers.CheckedItems[i]).Row["TID"].ToString();
query = query + " or (TID ='" + tid + "')";
}
}
SqlDataAdapter daReport2 = new SqlDataAdapter(query, con);
DataSet DataSetReportTeacher2 = new DataSet();
daReport2.Fill(DataSetReportTeacher2);
DataTable dtReport2 = new DataTable();
DataSetReportTeacher2.Tables.Add(dtReport2);
DataSetReportTeacher2.WriteXmlSchema("ReportTeacher2.xml");
ReportTeacher2 cr = new ReportTeacher2();
cr.SetDataSource(DataSetReportTeacher2);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[0].IsSelected)
{
if (selectedCompany == "")
{
selectedCompany = "0";
}
string query = "Select * FROM Company, CompanyType WHERE (ComID = '" + selectedCompany + "') and (CompanyType.typeId = Company.CompanyType_TypeID)";
SqlDataAdapter daReport1 = new SqlDataAdapter(query, con);
DataSet DataSetReportCompany1 = new DataSet();
daReport1.Fill(DataSetReportCompany1);
DataTable dtReport1 = new DataTable();
DataSetReportCompany1.Tables.Add(dtReport1);
DataSetReportCompany1.WriteXmlSchema("ReportCompany1.xml");
string query2 = "Select Type, CompanyContacts.details from CompanyContacts, Company WHERE (ComID = '" + selectedCompany + "') AND (Company.ComID=CompanyContacts.Company_ComID) ";
SqlDataAdapter daReport2 = new SqlDataAdapter(query2, con);
DataSet DataSetReportCompany2 = new DataSet();
daReport2.Fill(DataSetReportCompany2);
DataTable dtReport2 = new DataTable();
DataSetReportCompany2.Tables.Add(dtReport2);
DataSetReportCompany2.WriteXmlSchema("ReportCompany1c.xml");
ReportCompany1 cr = new ReportCompany1();
cr.SetDataSource(DataSetReportCompany1);
cr.Subreports["contacts"].SetDataSource(DataSetReportCompany2);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[1].IsSelected)
{
if (selectedCompany == "")
{
selectedCompany = "0";
}
string query = "Select * FROM Company, CompanyType WHERE (ComID = '" + selectedCompany + "') and (CompanyType.typeId = Company.CompanyType_TypeID)";
SqlDataAdapter daReport1 = new SqlDataAdapter(query, con);
DataSet DataSetReportCompany1 = new DataSet();
daReport1.Fill(DataSetReportCompany1);
DataTable dtReport1 = new DataTable();
DataSetReportCompany1.Tables.Add(dtReport1);
DataSetReportCompany1.WriteXmlSchema("ReportCompany2.xml");
string query2 = "Select Type, CompanyContacts.details from CompanyContacts, Company WHERE (ComID = '" + selectedCompany + "') AND (Company.ComID = CompanyContacts.Company_ComID) ";
SqlDataAdapter daReport2 = new SqlDataAdapter(query2, con);
DataSet DataSetReportCompany2 = new DataSet();
daReport2.Fill(DataSetReportCompany2);
DataTable dtReport2 = new DataTable();
DataSetReportCompany2.Tables.Add(dtReport2);
DataSetReportCompany2.WriteXmlSchema("ReportCompany2c.xml");
string query3 = "Select Supervisor.Name, Supervisor.Position from Supervisor, Company WHERE (ComID = '" + selectedCompany + "') AND (Company.ComID = Supervisor.Company_ComID) ";
SqlDataAdapter daReport3 = new SqlDataAdapter(query3, con);
DataSet DataSetReportCompany3 = new DataSet();
daReport3.Fill(DataSetReportCompany3);
DataTable dtReport3 = new DataTable();
DataSetReportCompany3.Tables.Add(dtReport3);
DataSetReportCompany3.WriteXmlSchema("ReportCompany2s.xml");
ReportCompany2 cr = new ReportCompany2();
cr.SetDataSource(DataSetReportCompany1);
cr.Subreports["contacts"].SetDataSource(DataSetReportCompany2);
cr.Subreports["supervisors"].SetDataSource(DataSetReportCompany3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[2].IsSelected || treeViewReports.Nodes[2].Nodes[3].IsSelected || treeViewReports.Nodes[2].Nodes[4].IsSelected || treeViewReports.Nodes[2].Nodes[5].IsSelected)
{
string query = "Select Company.Name, Company.AFM, Company.LegalForm, Company.Address + ' ' + Company.Number + ' ' + Company.ZipCode as 'AddressFull', Company.City, CompanyType.Name FROM Company INNER JOIN companyType ON Company.CompanyType_TypeID = companyType.TypeId WHERE (ComID = '-1')";
string ComID;
if (checkedListBoxCompanies.CheckedItems.Count > 0)
{
for (int i = 0; i < checkedListBoxCompanies.CheckedItems.Count; i++)
{
ComID = ((DataRowView)checkedListBoxCompanies.CheckedItems[i]).Row["ComID"].ToString();
query = query + " or (ComID ='" + ComID + "')";
}
}
SqlDataAdapter daReport3 = new SqlDataAdapter(query, con);
DataSet DataSetReportCompany3 = new DataSet();
daReport3.Fill(DataSetReportCompany3);
DataTable dtReport3 = new DataTable();
DataSetReportCompany3.Tables.Add(dtReport3);
DataSetReportCompany3.WriteXmlSchema("ReportCompany3.xml");
if (treeViewReports.Nodes[2].Nodes[2].IsSelected)
{
ReportCompany3 cr = new ReportCompany3();
cr.SetDataSource(DataSetReportCompany3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[3].IsSelected)
{
ReportCompany4 cr = new ReportCompany4();
cr.SetDataSource(DataSetReportCompany3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[4].IsSelected)
{
ReportCompany5 cr = new ReportCompany5();
cr.SetDataSource(DataSetReportCompany3);
crystalReportViewer.ReportSource = cr;
}
if (treeViewReports.Nodes[2].Nodes[5].IsSelected)
{
ReportCompany6 cr = new ReportCompany6();
cr.SetDataSource(DataSetReportCompany3);
crystalReportViewer.ReportSource = cr;
}
}
}