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

CodeGeeX

智谱AI发布的AI编程辅助工具插件，可以实现自动代码生成、代码翻译、自动编写注释以及智能问答等功能

170

查看详情

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`) -- 防止重复记录 );