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