Example #1
0
        private void fillReport()
        {
            if (cmbSearchBy.Text.Equals("All"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "order by t.dayDate asc";
                command = connection.GetCommand(query, CommandType.Text);
            }
            else if (cmbFilterBy.Text.Equals("- Select filter by -"))
            {
                MessageBox.Show(this, "Select 'filter by'", "Missing Filter", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                cmbFilterBy.Focus();

                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and p.national_id=@national_id order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                command.AddParameter("@national_id", "", SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("National ID Number") && cmbFilterBy.Text.Equals("Equals"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and p.national_id=@national_id order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                command.AddParameter("@national_id", txtSearch.Text, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("National ID Number") && cmbFilterBy.Text.Equals("Not Equals"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and p.national_id <> @national_id order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                command.AddParameter("@national_id", txtSearch.Text, SqlDbType.VarChar);
            }

            else if (cmbSearchBy.Text.Equals("Date") && cmbFilterBy.Text.Equals("Equals"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and t.dayDate = @dayDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt      = dtpStartDate.Value;
                string   strDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("Date") && cmbFilterBy.Text.Equals("Not Equals"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and t.dayDate <> @dayDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt      = dtpStartDate.Value;
                string   strDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("Date") && cmbFilterBy.Text.Equals("Between"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and t.dayDate between @startDate and @endDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt        = dtpStartDate.Value;
                string   startDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@startDate", startDate, SqlDbType.VarChar);
                dt = dtpEndDate.Value;
                string endDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@endDate", endDate, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("Date") && cmbFilterBy.Text.Equals("Less Than"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and t.dayDate < @dayDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt      = dtpStartDate.Value;
                string   strDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("Date") && cmbFilterBy.Text.Equals("Greater Than"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and t.dayDate > @dayDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt      = dtpStartDate.Value;
                string   strDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
            }
            else if (cmbSearchBy.Text.Equals("Date and National ID Number") && cmbFilterBy.Text.Equals("Equals"))
            {
                query = "select p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
                        "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
                        "from dbo.person p, dbo.time_attendance t where p.person_id=t.person_id " +
                        "and p.national_id=@national_id and t.dayDate = @dayDate order by t.dayDate asc";

                command = connection.GetCommand(query, CommandType.Text);
                DateTime dt      = dtpStartDate.Value;
                string   strDate = dt.Date.ToString("yyyy - MM - dd");
                command.AddParameter("@dayDate", strDate, SqlDbType.VarChar);
                command.AddParameter("@national_id", txtSearch.Text, SqlDbType.VarChar);
            }

            //query = "select pj.staff_no StaffId, p.national_id NationalID,p.surname Surname, p.first_name FirstName, " +
            //    "p.other_name Middlename, t.dayDate Date, t.time_in TimeIn, t.time_out TimeOut, t.minutes TimeStayed " +
            //    "from dbo.person p, dbo.time_attendance t, dbo.person_job pj where p.person_id=t.person_id and pj.person_id=p.person_id " +
            //    "order by t.dayDate asc";

            DataTable staffDt = DatabaseManipulate.GetDataTable(command);

            //command = connection.GetCommand(DatabaseManipulate.strOrganization, CommandType.Text);

            this.reportViewer1.LocalReport.DataSources.Clear();
            this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("date", DatabaseManipulate.getDate()));
            this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("attendance", staffDt));

            this.reportViewer1.RefreshReport();
        }
Example #2
0
        private void fillReport()
        {
            string query = "SELECT p.national_id ID, p.surname Surname, " +
                           "p.first_name FirstName,p.other_name OtherNames FROM dbo.person p order by p.surname asc";

            command = connection.GetCommand(query, CommandType.Text);
            DataTable staffDt = DatabaseManipulate.GetDataTable(command);

            command = connection.GetCommand(DatabaseManipulate.strOrganization, CommandType.Text);

            this.reportViewer1.LocalReport.DataSources.Clear();
            this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("date", DatabaseManipulate.getDate()));
            this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("person", staffDt));

            this.reportViewer1.RefreshReport();
        }