ZKTeco考勤机与Google Sheets/PHP服务器集成指南

function getAttendanceData() {
  // 替换为您的中间服务器API的URL
  var apiUrl = "http://your-intermediate-server.com/api/attendance"; 

  try {
    // 使用UrlFetchApp.fetch发送HTTP GET请求
    var response = UrlFetchApp.fetch(apiUrl);

    // 获取响应内容
    var content = response.getContentText();

    // 将JSON字符串解析为JavaScript对象
    var jsonData = JSON.parse(content);

    // 获取当前活动的电子表格和工作表
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    // 假设第一行是标题，从第二行开始写入数据
    // 可以在这里添加标题行，如果尚未存在
    // sheet.appendRow(["User ID", "Timestamp", "Device ID", "Status"]); 

    // 清除现有数据（可选，根据需求决定是否每次刷新都清空）
    // var lastRow = sheet.getLastRow();
    // if (lastRow > 1) { // 假设第一行是标题
    //   sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
    // }

    // 遍历JSON数据并写入Google Sheet
    if (jsonData && jsonData.length > 0) {
      var dataToWrite = [];
      jsonData.forEach(function(record) {
        dataToWrite.push([
          record.user_id,
          record.timestamp,
          record.device_id,
          record.status
        ]);
      });
      // 将数据写入到工作表，从下一行开始
      sheet.getRange(sheet.getLastRow() + 1, 1, dataToWrite.length, dataToWrite[0].length).setValues(dataToWrite);
      Logger.log("Attendance data successfully updated.");
    } else {
      Logger.log("No attendance data received or data is empty.");
    }

  } catch (e) {
    Logger.log("Error fetching or processing attendance data: " + e.toString());
    SpreadsheetApp.getUi().alert("错误", "获取考勤数据时发生错误: " + e.message, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

<?php

// 替换为您的中间服务器API的URL
$apiUrl = "http://your-intermediate-server.com/api/attendance"; 

try {
    // 使用cURL获取数据
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $apiUrl);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // 返回响应内容而不是直接输出
    // 如果您的API需要认证，可以在这里添加头部信息
    // curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Bearer YOUR_API_KEY')); 

    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($httpCode === 200 && $response) {
        $jsonData = json_decode($response, true); // 将JSON字符串解析为PHP数组

        if (json_last_error() === JSON_ERROR_NONE) {
            if (!empty($jsonData)) {
                // 连接数据库（请替换为您的数据库连接信息）
                $servername = "localhost";
                $username = "username";
                $password = "password";
                $dbname = "attendance_db";

                $conn = new mysqli($servername, $username, $password, $dbname);

                if ($conn->connect_error) {
                    die("数据库连接失败: " . $conn->connect_error);
                }

                // 准备SQL插入语句
                $stmt = $conn->prepare("INSERT INTO attendance_logs (user_id, timestamp, device_id, status) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE timestamp=VALUES(timestamp), device_id=VALUES(device_id), status=VALUES(status)");
                $stmt->bind_param("ssss", $userId, $timestamp, $deviceId, $status);

                foreach ($jsonData as $record) {
                    $userId = $record['user_id'];
                    $timestamp = $record['timestamp'];
                    $deviceId = $record['device_id'];
                    $status = $record['status'];
                    $stmt->execute();
                }

                $stmt->close();
                $conn->close();
                echo "考勤数据成功同步到数据库。\n";
            } else {
                echo "未从中间服务器获取到考勤数据。\n";
            }
        } else {
            echo "JSON解析错误: " . json_last_error_msg() . "\n";
        }
    } else {
        echo "从中间服务器获取数据失败。HTTP状态码: " . $httpCode . ", 响应: " . $response . "\n";
    }

} catch (Exception $e) {
    echo "获取或处理考勤数据时发生错误: " . $e->getMessage() . "\n";
}

?>

CREATE TABLE `attendance_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(50) NOT NULL,
  `timestamp` DATETIME NOT NULL,
  `device_id` VARCHAR(50) NOT NULL,
  `status` VARCHAR(10) NOT NULL,
  UNIQUE KEY `idx_user_time_device` (`user_id`, `timestamp`, `device_id`) -- 防止重复记录
);