PHP WebShell
Текущая директория: /var/www/bitcardoApp/cron
Просмотр файла: reconcile_bitgo_transfers.php
<?php
/**
* Reconcile missed BitGo inflow transfers (BTC-only for now).
* - Scans BitGo wallet transfer history and upserts into `transactions`
* - Credits `user_wallets.balance` once per tx when confirmed
* - Prints JSON log lines to STDOUT/HTTP and also logs to file
*
* Usage:
* php reconcile_bitgo_transfers.php # default: last 14 days, up to 200 pages
* php reconcile_bitgo_transfers.php 72 # look back 72 hours
* php reconcile_bitgo_transfers.php 336 300 # 14 days, 300 pages
*/
@ini_set('display_errors', '0');
// If run via web server, return text/plain so JSON lines are readable.
if (php_sapi_name() !== 'cli') {
header('Content-Type: text/plain; charset=UTF-8');
}
$LOG_FILE = __DIR__ . '/bitgo_reconcile.log';
/** Log + Echo one JSON line */
function out(string $lvl, string $msg, array $ctx = []): void {
global $LOG_FILE;
$line = ['ts'=>gmdate('c'),'lvl'=>$lvl,'msg'=>$msg,'ctx'=>$ctx];
$json = json_encode($line, JSON_UNESCAPED_SLASHES);
echo $json . PHP_EOL;
@file_put_contents($LOG_FILE, $json . PHP_EOL, FILE_APPEND | LOCK_EX);
}
// ---- Load configs
require_once __DIR__ . '/../config/db_config.php'; // provides $conn (mysqli)
require_once __DIR__ . '/../config/bitgo_config.php'; // BITGO_API_BASE_URL, BITGO_ACCESS_TOKEN, BITGO_BTC_WALLET_ID
define('BITGO_BTC_WALLET_ID', '68cc0c43259341e085a5471bc1031f22');
if (!isset($conn) || !($conn instanceof mysqli)) { out('error','db-conn-missing'); exit(1); }
if (!defined('BITGO_API_BASE_URL') || !defined('BITGO_ACCESS_TOKEN')) { out('error','bitgo-config-missing'); exit(1); }
if (!defined('BITGO_BTC_WALLET_ID') || !BITGO_BTC_WALLET_ID) { out('error','BITGO_BTC_WALLET_ID missing'); exit(1); }
// ---- Settings
$coinTicker = 'btc';
$walletId = BITGO_BTC_WALLET_ID;
$lookbackHrs = isset($argv[1]) ? (int)$argv[1] : 14*24; // default 14 days
$maxPages = isset($argv[2]) ? (int)$argv[2] : 200; // pagination cap
$limitPerPage = 500; // pull max per page
// ---- Helpers (mirror webhook behavior)
function coin_decimals(string $network, string $asset, ?int $tokenDecimals): int {
$network = strtolower($network);
if ($tokenDecimals !== null) return $tokenDecimals;
return match ($network) { 'btc'=>8,'eth'=>18,'sol'=>9,'trx'=>6, default=>8 };
}
function confirm_threshold(string $network, string $asset): int {
$network = strtolower($network); $asset = strtoupper($asset);
$map = ['BTC'=>2,'ETH'=>12,'SOL'=>32,'TRX'=>19];
if ($asset === 'USDT') return $map[strtoupper($network)] ?? 6;
return $map[$asset] ?? ($map[strtoupper($network)] ?? 1);
}
function to_human(string $valueBase, int $decimals, int $scale = 12): string {
if (!function_exists('bcscale')) {
$div = 1; for ($i=0;$i<$decimals;$i++) $div *= 10;
return rtrim(rtrim(number_format(((float)$valueBase)/$div, min($scale,$decimals), '.', ''), '0'), '.') ?: '0';
}
bcscale($scale);
$div = bcpow('10', (string)$decimals);
$res = bcdiv($valueBase, $div, $scale);
return rtrim(rtrim($res, '0'), '.') ?: '0';
}
function wallet_scale_for_asset(string $asset): int {
return match (strtoupper($asset)) { 'BTC'=>8,'TRX'=>6,'SOL'=>9,'ETH'=>10, default=>10 };
}
function decimal_clause_for_scale(int $scale): string {
$s = max(0, min(18, $scale)); return "DECIMAL(30,$s)";
}
function is_positive_value(string $v): bool {
if (function_exists('bccomp')) return bccomp($v, '0', 0) === 1;
return (float)$v > 0;
}
/** Address → user wallet (returns user_id, wallet_id, wallet_add) */
function find_user_by_address(mysqli $conn, string $addrLower, string $asset): ?array {
$sql = "SELECT user_id, wallet_id, wallet_add
FROM user_wallets
WHERE TRIM(LOWER(wallet_add)) = TRIM(LOWER(?))
AND UPPER(coin) = UPPER(?)
LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $addrLower, $asset);
$stmt->execute();
$res = $stmt->get_result();
$row = ($res && $res->num_rows) ? $res->fetch_assoc() : null;
$stmt->close();
return $row;
}
/** Find existing tx row for idempotency */
function find_tx(mysqli $conn, string $txid, string $receiverLower, string $asset): ?array {
$sql = "SELECT trans_id, applied
FROM transactions
WHERE txid = ?
AND receiver_address = ?
AND coin = ?
LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('sss', $txid, $receiverLower, $asset);
$stmt->execute();
$res = $stmt->get_result();
$row = ($res && $res->num_rows) ? $res->fetch_assoc() : null;
$stmt->close();
return $row;
}
/** Credit user wallet with dynamic scale */
function credit_user_wallet(mysqli $conn, int $userId, string $asset, string $addr, string $amountHuman): void {
$scale = wallet_scale_for_asset($asset);
$dec = decimal_clause_for_scale($scale);
$sql = "
UPDATE user_wallets
SET balance = ROUND((CAST(balance AS $dec) + CAST(? AS $dec)), $scale),
updated_at = NOW()
WHERE user_id = ? AND coin = ? AND wallet_add = ?
LIMIT 1
";
$stmt = $conn->prepare($sql);
$stmt->bind_param('siss', $amountHuman, $userId, $asset, $addr);
$stmt->execute();
$stmt->close();
}
function mark_applied(mysqli $conn, int $transId): void {
$stmt = $conn->prepare("UPDATE transactions SET applied=1, updated_at=NOW() WHERE trans_id=?");
$stmt->bind_param('i', $transId);
$stmt->execute();
$stmt->close();
}
// BitGo GET helper
function bitgo_get(string $url): ?array {
$ch = curl_init($url);
curl_setopt_array($ch, [
CURLOPT_RETURNTRANSFER => true,
CURLOPT_TIMEOUT => 30,
CURLOPT_HTTPHEADER => ['Authorization: Bearer '.BITGO_ACCESS_TOKEN],
]);
$res = curl_exec($ch);
$err = curl_error($ch);
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($res === false || $code >= 400) {
out('error','bitgo-get-failed',['code'=>$code,'err'=>$err,'url'=>$url,'body'=>substr((string)$res,0,300)]);
return null;
}
$j = json_decode((string)$res, true);
if (!is_array($j)) {
out('error','bitgo-bad-json',['url'=>$url,'body'=>substr((string)$res,0,300)]);
return null;
}
return $j;
}
// Lookback
$sinceTs = $lookbackHrs > 0 ? time() - ($lookbackHrs * 3600) : 0;
$sinceIso = $sinceTs ? gmdate('c', $sinceTs) : null;
out('info','start-reconcile',[
'coin'=>$coinTicker,'walletId'=>$walletId,'lookback_hours'=>$lookbackHrs,'since'=>$sinceIso
]);
$base = rtrim(BITGO_API_BASE_URL,'/');
$prevId = null;
$page = 0;
$processed=0; $inserted=0; $credited=0; $fetched=0; $oldestTs=null; $newestTs=null;
$hitOlder = false;
do {
$page++;
if ($page > $maxPages) { out('warn','max-pages-reached',['page'=>$page-1]); break; }
// Build query (no 'state' filter; BitGo v2 rejects 'pending' here)
$q = [
'limit' => $limitPerPage,
// Only include prevId when we have it, to avoid sending empty param
];
if ($prevId) $q['prevId'] = $prevId;
$url = "{$base}/{$coinTicker}/wallet/{$walletId}/transfer?" . http_build_query($q);
$resp = bitgo_get($url);
if (!$resp) break;
$transfers = $resp['transfers'] ?? [];
if (!is_array($transfers) || empty($transfers)) {
out('info','no-more-transfers',['page'=>$page]); break;
}
$pageMinTs = PHP_INT_MAX; $pageMaxTs = 0;
foreach ($transfers as $tr) {
$tWhen = $tr['date'] ?? $tr['createDate'] ?? null;
$tTs = $tWhen ? strtotime($tWhen) : 0;
if ($tTs) {
$pageMinTs = min($pageMinTs, $tTs);
$pageMaxTs = max($pageMaxTs, $tTs);
if ($oldestTs===null || $tTs < $oldestTs) $oldestTs = $tTs;
if ($newestTs===null || $tTs > $newestTs) $newestTs = $tTs;
}
if ($sinceTs && $tTs && $tTs < $sinceTs) {
$hitOlder = true; // will stop after this page
continue; // skip older than our window
}
$type = strtolower($tr['type'] ?? '');
if ($type !== 'receive') continue;
$state = strtolower($tr['state'] ?? '');
$confirms = (int)($tr['confirmations'] ?? 0);
$txid = $tr['txid'] ?? null;
$entries = $tr['entries'] ?? [];
if (!$txid || !is_array($entries)) continue;
// Find positive entry (credit to our wallet)
$toAddrLower=null; $amountBase='0';
foreach ($entries as $e) {
$v = (string)($e['value'] ?? '0');
if (is_positive_value($v)) {
$toAddrLower = strtolower($e['address'] ?? '');
$amountBase = $v;
break;
}
}
if (!$toAddrLower) continue;
$assetCoin = strtoupper($tr['token'] ?? ($tr['tokenInfo']['symbol'] ?? 'BTC'));
$decimals = coin_decimals($coinTicker,$assetCoin, isset($tr['tokenInfo']['decimals'])?(int)$tr['tokenInfo']['decimals']:null);
$confirmNeed = confirm_threshold($coinTicker,$assetCoin);
$amountHuman = to_human($amountBase, $decimals, 12);
$status = ($confirms >= $confirmNeed || $state==='confirmed') ? 'confirmed' : 'pending';
// Resolve user wallet (by address + coin)
$userRow = find_user_by_address($conn, $toAddrLower, $assetCoin);
if (!$userRow) { out('ignored','address-not-found',['address'=>$toAddrLower,'coin'=>$assetCoin,'txid'=>$txid]); continue; }
$userId = (int)$userRow['user_id'];
$uwId = (int)$userRow['wallet_id']; // FK into transactions.wallet_id
$uwAddr = $userRow['wallet_add'];
// Upsert
$conn->begin_transaction();
try {
$existing = find_tx($conn, $txid, $toAddrLower, $assetCoin);
if ($existing) {
$cid=(int)$existing['trans_id'];
$stmt=$conn->prepare("UPDATE transactions SET confirmation=?, status=?, updated_at=NOW() WHERE trans_id=?");
$stmt->bind_param('isi',$confirms,$status,$cid);
$stmt->execute(); $stmt->close();
$alreadyApplied = (int)$existing['applied'] === 1;
if (!$alreadyApplied && $status==='confirmed') {
credit_user_wallet($conn,$userId,$assetCoin,$uwAddr,$amountHuman);
mark_applied($conn,$cid);
$credited++; out('success','credited-on-confirm-existing',['txid'=>$txid,'user_id'=>$userId,'coin'=>$assetCoin,'amount'=>$amountHuman]);
} else {
out('info','updated-confirmations',['txid'=>$txid,'confirms'=>$confirms,'status'=>$status,'already_applied'=>$alreadyApplied]);
}
} else {
// INSERT — do NOT list created_at/updated_at: let defaults set them
$stmt = $conn->prepare("
INSERT INTO transactions
(coin, user_id, wallet_id, transfer_id, sender_address, receiver_address, amount,
type, txid, reference, provider, confirmation, status, applied)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
");
$applied = 0;
$tType = 'deposit';
$provider = 'bitgo';
$transferId = null; // unknown here
$reference = null; // optional
$sender = strtolower($tr['fromAddress'] ?? $tr['from'] ?? '');
// 14 placeholders → 14 types → 14 args
// s i i s s s s s s s s i s i
$stmt->bind_param(
"siisssssssssii",
$assetCoin, // s coin
$userId, // i user_id
$uwId, // i wallet_id (INT FK)
$transferId, // s transfer_id (nullable)
$sender, // s sender_address
$toAddrLower, // s receiver_address
$amountHuman, // s amount (string decimal)
$tType, // s type
$txid, // s txid
$reference, // s reference
$provider, // s provider
$confirms, // i confirmation
$status, // s status
$applied // i applied
);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
if ($status==='confirmed') {
credit_user_wallet($conn,$userId,$assetCoin,$uwAddr,$amountHuman);
mark_applied($conn,$newId);
$credited++; out('success','credited-on-insert-confirmed',['txid'=>$txid,'user_id'=>$userId,'coin'=>$assetCoin,'amount'=>$amountHuman]);
} else {
out('info','inserted-pending',['txid'=>$txid,'user_id'=>$userId,'coin'=>$assetCoin,'amount'=>$amountHuman,'confirms'=>$confirms]);
}
$inserted++;
}
$conn->commit(); $processed++; $fetched++;
} catch (Throwable $e) {
$conn->rollback(); out('error','db-failure',['err'=>$e->getMessage(),'txid'=>$txid]);
}
}
out('info','page-range',[
'page'=>$page,
'count'=>count($transfers),
'page_first'=>($pageMaxTs?gmdate('c',$pageMaxTs):null),
'page_last' =>($pageMinTs?gmdate('c',$pageMinTs):null),
'cursor_present'=> isset($resp['nextBatchPrevId']) || isset($resp['next'])
]);
// Pagination cursor
$prevId = $resp['nextBatchPrevId'] ?? $resp['next'] ?? null;
if ($sinceTs && $hitOlder) {
out('info','hit-lookback-boundary',['since'=>$sinceIso,'page'=>$page]);
break;
}
} while ($prevId);
// Summary
out('info','done',[
'processed'=>$processed, 'inserted'=>$inserted, 'credited'=>$credited, 'fetched'=>$fetched,
'overall_newest'=>($newestTs?gmdate('c',$newestTs):null),
'overall_oldest'=>($oldestTs?gmdate('c',$oldestTs):null),
'lookback_hours'=>$lookbackHrs,
'max_pages'=>$maxPages
]);
Выполнить команду
Для локальной разработки. Не используйте в интернете!