Đối soát giao dịch

<?php
$sepayToken = 'YOUR_API_TOKEN';
 
// Ket noi database
$conn = new mysqli('localhost', 'webhooks_receiver', 'EL2vKpfpDLsz', 'webhooks_receiver');
if ($conn->connect_error) {
    die('MySQL connection failed: ' . $conn->connect_error);
}
 
// Lay giao dich tu SePay trong 24h qua
$dateMin = date('Y-m-d H:i:s', strtotime('-24 hours'));
$dateMax = date('Y-m-d H:i:s');
 
$url = 'https://my.sepay.vn/userapi/transactions/list?'
    . http_build_query([
        'transaction_date_min' => $dateMin,
        'transaction_date_max' => $dateMax
    ]);
 
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    'Content-Type: application/json',
    'Authorization: Bearer ' . $sepayToken
]);
$response = curl_exec($ch);
curl_close($ch);
 
$data = json_decode($response, true);
$sepayTransactions = $data['transactions'] ?? [];
echo count($sepayTransactions) . " giao dich tu SePay\n";
 
// Lay danh sach reference_number da luu
$result = $conn->query(
    "SELECT reference_number FROM tb_transactions WHERE created_at >= '{$dateMin}'"
);
$existingRefs = [];
while ($row = $result->fetch_assoc()) {
    $existingRefs[$row['reference_number']] = true;
}
 
// Tim va bo sung giao dich thieu
$missingCount = 0;
foreach ($sepayTransactions as $tx) {
    if (!isset($existingRefs[$tx['reference_number']])) {
        echo "Thieu giao dich: ID={$tx['id']}, so tien={$tx['amount_in']}, ref={$tx['reference_number']}\n";
 
        $stmt = $conn->prepare(
            'INSERT INTO tb_transactions
                (gateway, transaction_date, account_number, sub_account,
                 amount_in, amount_out, accumulated, code,
                 transaction_content, reference_number, body)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $stmt->bind_param('ssssdddssss',
            $tx['bank_brand_name'],
            $tx['transaction_date'],
            $tx['bank_account_id'],
            $tx['bank_sub_acc_id'],
            $tx['amount_in'],
            $tx['amount_out'],
            $tx['accumulated'],
            $tx['code'],
            $tx['transaction_content'],
            $tx['reference_number'],
            $tx['description']
        );
        $stmt->execute();
        $missingCount++;
    }
}
 
echo "Doi soat xong. Bo sung {$missingCount} giao dich.\n";
$conn->close();
?>