コード例 #1
0
        public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
        {
            ReportStudent2 rpt = new ReportStudent2();

            rpt.Site = this.Site;
            return(rpt);
        }
コード例 #2
0
/* --------------------------------------------------------------------------------------------- */

        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;
                }
            }
        }