Coming up with a business logic helper class

[eluser]afro[/eluser]
I have this requirement:
to limit the number of staff who can be on leave at
any particular to 3 staffs from the same department
I need help to come up with this functions in a helper class
where the helper class should be able to tell the user/staff
that there three staffs from your department who have applied
for leave, the system should then tell the staff when the staff
who went for the leave earlier will be reporting, then he/she
will be eligible for a leave.
i have a function to check the number of employees

Code:

function check_staff_on_leave()
               {
                $this->db->select('Department, COUNT(*) AS on_leave', FALSE);
                $this->db->from('checkoff');
                $this->db->where('Department', $this->input->post('department'));
                $this->db->group_by('Department');
                $this->db->having('COUNT(*) >', 2);  
                $result = $this->db->get();
                //echo $this->db->last_query();
                //
                if($result->num_rows() > 0)
                {
                 $rows = $result->result_array();
                
                  return FALSE;
                
                    }
                 else
                  {
                  return TRUE;
                }  
              }

I now need 1 that will be checking the staff who went on leave earlier and when he/she will
be reporting back.
please help me

[eluser]toopay[/eluser]
More details, for example what your database structure look like.

[eluser]afro[/eluser]
Am using mysql database, i have a table called staff, where i save the date of application for leave
, the date to start the leave and also the date the staff is expected to come back, reporting date
so the function should be able to check those who have have applied for a leave, if they are are more than
3 the system should then check the table of those on leave grouping by the department and select the staff
who first went for the leave and tell the applicant that he/she will be able to apply for leave after the first person form the
department comes back.the table that hold the record for those on leave is called staff_on_leave.

hope that will be satisfactory.

[eluser]toopay[/eluser]
Maybe, it will more clear if you write down your table like:

Code:

+----------------+
| what_table_name
+----------------+
| some_field
| another_field
| .......
+----------------+

then explain what output you want to get from that...

[eluser]afro[/eluser]
This is the first table the system will check, if there are 3 staffs from the same department who have applied for a leave,
the system should then tell the applicant that there 3 staffs have already applied for leave which are still pending, the system changes when the Status field to either rejected or approved when a leave is rejected or approved.

Code:

+--|---------|-------|----|-------------|----------|----------------|-------------|----------------+
|ID|StaffName|StaffId|Date|DaysRequested|Department|CommencementDate|ReportingBack|AppliedOn|Status|
+--|---------|-------|----|-------------|----------|----------------|-------------|----------------+

The sytem then checks the appliedleaves table(below) and group the results by department where status is ON(meaning still on leave) , if there are more than three, the system should get the record with the lowest timestamp, and picks the ReportBack field and then
tell the applicant when the first staff is expected back from leave. This is when the staff will be eligible to apply for a leave

Code:

+--|---------|-------|----|------------|-------------|----------|---------|------------|-------------|------|---------|----------+
|Id|StaffName|StaffId|Date|LeaveBalance|DaysRequested|Department|LeaveType|CommenceDate|ReportingBack|Status|AppliedOn|ApprovedOn|
+--|---------|-------|----|------------|-------------|----------|---------|------------|-------------|------|---------|----------+

[eluser]toopay[/eluser]
How currently results you get, and what the results you still need?

[eluser]afro[/eluser]
Currently am able to determine the number of employee from the same department whose
leave applications are still pending, that is they have no been approved or not rejected,

what i now need to get now is to get the employee whose leave was first approved from that
department (the same department as the current applicant) and the date he/she is expected to return
from the leave.

[eluser]toopay[/eluser]
I'm still not clear, try to explain with 'data' languange rather than 'business logic' languange, because i see there is two table.

Are you try to fetch data JUST from one table, OR fetch data from one table then find match row in another table based by result of last query on table one which had the lowest timestamp ?

[eluser]afro[/eluser]
using the checkoff table (the 1st one), the system should loop thru' and get the department
that has 3 employees who have just applied for leaves and the status should be pending.

the system then checks the appliedleaves table (2nd 1) for the employee of that particular
departmnet where they are three a gets the lowest timestamp among the the results, picks
the ReportBack field, it then tells the applicant the day the employee with lowest timestamp
is come back.

ope this will help

[eluser]toopay[/eluser]
i'm still not sure, maybe something like this..

Code:

function check_staff_on_leave()
{
      $this->db->select('Department, COUNT(*) AS on_leave', FALSE);
      $this->db->from('checkoff');
      $this->db->where('Department', $this->input->post('department'));
      $this->db->group_by('Department');
      $this->db->having('COUNT(*) >', 2);  
      $result = $this->db->get();
      if($result->num_rows() > 0)
      {
              $rows = $result->result_array();
              // Generating query to table 2
              $this->db->select('MIN(Date),StaffName,ReportBack');
              $this->db->from('appliedleaves');
              $this->db->where('Department', $rows[0]['Department']);
              $res = $this->db->get();
              // This variable should give you matched result from table 2
              $row2 = $res->result_array();
              return FALSE;
       }
       else
       {
              return TRUE;
       }  
}