如何使用PHP从考勤记录中创建工资报表

Question

我有一个名为:时间表的数据库表，它有以下列和数据:

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

记录了员工的每日出勤率，如上图所示。我想要的是使用PHP从这些考勤记录中创建一个工资单报告，该报告显示每个部门的每个员工的总工作时数，并在一个单独的html表中显示，如下所示:

财务部工资单html表

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

采购部工资表html表

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

请注意，我不知道所有员工的ID，所以代码应该只列出每个人按部门使用PHP/MYSQL

Answer 1

有几个视图可以完成这项工作:一个用于工作时间，一个用于员工，然后您可以将它们连接起来并对您的过滤器进行编程，如下所示:

-- 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';

或者，在单个查询中(没有视图):

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';

在php中，你应该使用一个循环和一个累积变量。首先，您应该使用如下查询进行预过滤:

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

如果结果是一个名为$rows的多维数组，php中就像这样:

$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 );