PHP と Vue を使用して従業員の勤怠に関する統計分析モジュールを構築する方法

CREATE TABLE attendance (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id INT(11) NOT NULL,
    attendance_date DATE NOT NULL,
    attendance_status ENUM('Present', 'Late', 'Absent') NOT NULL
);

<?php
// 数据库连接信息
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "attendance";

// 建立数据库连接
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// 查询某个月份的考勤统计
$month = $_GET['month'];
$sql = "SELECT employee_id, COUNT(*) AS total_attendance, 
        SUM(CASE WHEN attendance_status = 'Present' THEN 1 ELSE 0 END) AS total_present, 
        SUM(CASE WHEN attendance_status = 'Late' THEN 1 ELSE 0 END) AS total_late,
        SUM(CASE WHEN attendance_status = 'Absent' THEN 1 ELSE 0 END) AS total_absent
        FROM attendance WHERE DATE_FORMAT(attendance_date, '%Y-%m') = :month GROUP BY employee_id";

$stmt = $conn->prepare($sql);
$stmt->bindParam(':month', $month);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 将结果转换为JSON格式返回给前端
echo json_encode($results);
?>

<template>
  <div>
    <h2>{{ month }} 考勤统计</h2>
    <table>
      <thead>
        <tr>
          <th>员工ID</th>
          <th>总出勤天数</th>
          <th>正常出勤天数</th>
          <th>迟到天数</th>
          <th>旷工天数</th>
        </tr>
      </thead>
      <tbody>
        <tr v-for="record in attendanceRecords" :key="record.employee_id">
          <td>{{ record.employee_id }}</td>
          <td>{{ record.total_attendance }}</td>
          <td>{{ record.total_present }}</td>
          <td>{{ record.total_late }}</td>
          <td>{{ record.total_absent }}</td>
        </tr>
      </tbody>
    </table>
  </div>
</template>

<script>
export default {
  data() {
    return {
      month: '2021-01',
      attendanceRecords: []
    };
  },
  mounted() {
    this.fetchAttendanceData(this.month);
  },
  methods: {
    fetchAttendanceData(month) {
      fetch(`/api/attendance.php?month=${month}`)
        .then(response => response.json())
        .then(data => {
          this.attendanceRecords = data;
        })
        .catch(error => {
          console.error(error);
        });
    }
  }
}
</script>

<template>
  <div>
    <h1>员工考勤统计</h1>
    <attendance-statistics></attendance-statistics>
  </div>
</template>

<script>
import AttendanceStatistics from './components/AttendanceStatistics.vue';

export default {
  components: {
    AttendanceStatistics
  }
}
</script>