如何新增記帳系統的自動化功能 - 使用PHP開發自動化記帳任務的方法

<?php
// 连接数据库
$host = 'localhost';
$dbname = 'accounting_system';
$username = 'root';
$password = '123456';
$db = new PDO("mysql:host={$host};dbname={$dbname}", $username, $password);

// 获取需要自动记账的交易记录
$sql = "SELECT * FROM transactions WHERE status = 'unprocessed'";
$stmt = $db->prepare($sql);
$stmt->execute();
$transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 遍历交易记录，并根据分类自动记账
foreach ($transactions as $transaction) {
  $categoryId = $transaction['category_id'];

  // 根据分类查询对应的账户
  $sql = "SELECT * FROM accounts WHERE category_id = :categoryId";
  $stmt = $db->prepare($sql);
  $stmt->bindParam(':categoryId', $categoryId);
  $stmt->execute();
  $account = $stmt->fetch(PDO::FETCH_ASSOC);

  // 更新账户余额和交易状态
  $amount = $transaction['amount'];
  $accountId = $account['id'];
  $newBalance = $account['balance'] + $amount;
  $sql = "UPDATE accounts SET balance = :newBalance WHERE id = :accountId";
  $stmt = $db->prepare($sql);
  $stmt->bindParam(':newBalance', $newBalance);
  $stmt->bindParam(':accountId', $accountId);
  $stmt->execute();

  $transactionId = $transaction['id'];
  $sql = "UPDATE transactions SET status = 'processed' WHERE id = :transactionId";
  $stmt = $db->prepare($sql);
  $stmt->bindParam(':transactionId', $transactionId);
  $stmt->execute();
}

echo "自动记账任务执行完毕。";

?>

0 0 * * * php /path/to/your/task_script.php