the7k the7k - 10 days ago 6
PHP Question

How to create a payroll report out of an attendance records using PHP

I have database table named: timesheets which has the following columns and data:

employee_id employee_name year month day timein timeout department
1 dave 2016 09 15 8 4 finance
1 dave 2016 09 16 8 4 finance
1 dave 2016 09 17 8 4 finance
2 frank 2016 09 15 8 4 purchase
2 frank 2016 09 16 8 4 purchase


which records the daily attendance of employees as you can see above. What I want is to create a payroll report from these attendance records using PHP that shows the total number of working hours for each employee with every department in a separate html table like so:

finance department payroll html table

employe_id | employee_name | total_working_hours
-----------+----------------+---------------------
1 | dave | 24 (8 hrs * 3 days)


purchase department payroll html table

employe_id | employee_name | total_working_hours
-----------+----------------+---------------------
1 | frank | 16


Please note that I don't know the ID's of all employees, so the code should just list everyone grouped by department using PHP / MYSQL

Answer

A couple of views will do the job: One for working hours, one for employees, then you can join them and and program your filters, like this:

-- Query the working hours
CREATE VIEW
 vw_working_hours
AS
SELECT
    employee_id,
    year,
    month, 
    SUM((timeout+12) - timein ) AS total_working_hours 
FROM
    timesheets
GROUP BY
    employee_id,
    year,
    month;

-- Query the employees
CREATE VIEW
    vw_employees
AS

SELECT 
    DISTINCT
    employee_id,
    employee_name,
    department
FROM
    timesheets;

-- This query is the actual report
-- just had to filter by department
-- in your script
SELECT
    wh.employee_id,
    emp.employee_name,
    wh.total_working_hours
FROM
    vw_working_hours AS wh
JOIN
    vw_employees AS emp
ON
    wh.employee_id = emp.employee_id
WHERE
    wh.year = 2016
    AND
        wh.month = '09'
    AND
        emp.department = 'finance';

Or, in a single query (no views):

SELECT
    wh.employee_id,
    emp.employee_name,
    wh.total_working_hours
FROM
    (
    SELECT
        employee_id,
        year,
        month, 
        SUM((timeout+12) - timein ) AS total_working_hours 
    FROM
        timesheets
    GROUP BY
        employee_id,
        year,
        month

    ) AS wh
JOIN
    (
    SELECT 
        DISTINCT
        employee_id,
        employee_name,
        department
    FROM
        timesheets

    ) AS emp
ON
    wh.employee_id = emp.employee_id
WHERE
    wh.year = 2016
    AND
        wh.month = '09'
    AND
        emp.department = 'finance';

In php you should use a loop and an accumulated variable. First you should pre-filter with a query like this:

SELECT
  *
FROM
  timesheets
WHERE
  department = 'finance'
  AND
    year = 2016
  AND
    month = '09'
  ORDER BY
    employee_id;

Then if the result is in a multidimensional array named $rows, something in php like this:

$employee_id = $rows[0]['employee_id'];
    $employee_name = $rows[0]['employee_name'];
    $accumulated = 0;

    foreach($rows as $row) {
      $total_working_hours = ($row['timeout'] + 12) - $row['timein'];
      if ( $row['employee_id'] == $employee_id ) {
        //  Same employee, acumulate
        $accumulated += $total_working_hours;
      } else {
        //  Another employee, pass the acumulation to result
        $rowTmp['employee_id'] = $employee_id;
        $rowTmp['employee_name'] = $employee_name;
        $rowTmp['total_working_hours'] = $accumulated;
        $result[] = $rowTmp;
        //  Updated the accumulation variables
        //  new employee
        $employee_id = $row['employee_id'];
        $employee_name = $row['employee_name'];
        //  reset accumulated
        $accumulated = $total_working_hours;
      }
    }

    // at the end, updates the las result:
    $rowTmp['employee_id'] = $employee_id;
    $rowTmp['employee_name'] = $employee_name;
    $rowTmp['total_working_hours'] = $accumulated;
    $result[] = $rowTmp;

    // Should pass result to HTML table
    print_r( $result ); 
Comments