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