<?php
// 통계 데이터의 '기준값'을 연산하고 변수로 정의
// 초기 데이터 연산
$cur_av = 0; $cur_cv = 0; $cur_iv = 0; $cur_pv = 0;
try {
$stmt = $pdo->query("SELECT total_asset_value, cash_balance, total_profit_amount FROM daemon_upbit_Ticker_user ORDER BY collected_at DESC LIMIT 1");
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) { $cur_av = (float)$r['total_asset_value']; $cur_cv = (float)$r['cash_balance']; $cur_iv = $cur_av - $cur_cv; $cur_pv = (float)$r['total_profit_amount']; }
} catch (Throwable $e) {}
$base_date = "2026-02-26"; $write_table = "g5_write_moving_assets"; $base_asset = 0;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $base_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC); if ($r) $base_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$base_in = 0; $base_out = 0;
$row_base = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$base_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
if ($row_base) { $base_in = (float)$row_base['x2_total_bank_in']; $base_out = (float)$row_base['x2_total_bank_out']; }
$io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} ORDER BY wr_datetime DESC LIMIT 1");
$cur_in = $io ? (float)$io['x2_total_bank_in'] : $base_in; $cur_out = $io ? (float)$io['x2_total_bank_out'] : $base_out;
$should_have_latest = $base_asset + ($cur_in - $base_in) - ($cur_out - $base_out);
$cost_basis = $cur_iv - $cur_pv; $total_asset = $cost_basis + $cur_cv;
$pure_profit = $cur_av - $should_have_latest;
$pure_roi = $should_have_latest ? ($pure_profit / $should_have_latest * 100) : 0;
$buy_ratio = $total_asset ? ($cost_basis / $total_asset * 100) : 0;
$total_roi = $total_asset ? ($cur_pv / $total_asset * 100) : 0;
$days = (new DateTime())->diff(new DateTime($base_date))->days;
// $cur_pv = 실시간 현재 손익금
// 실시간 현재 손익률
$current_roi = $cost_basis ? ($cur_iv * 100 / $cost_basis - 100) : 0;
// 일 평균 손익금 계산
$avg_pnl = $days ? ($pure_profit / $days) : 0;
// 일 평균 손익률 계산
$avg_roi = $days ? ($pure_roi / $days) : 0;
// 시간 평균 손익금 계산
$avg_pnl_hour = $avg_pnl / 24;
// 시간 평균 손익률 계산
$avg_roi_hour = $avg_roi / 24;
// 분 평균 손익금 계산
$avg_pnl_minute = $avg_pnl_hour / 60;
// 분 평균 손익률 계산
$avg_roi_minute = $avg_roi_hour / 60;
// 초 평균 손익금 계산
$avg_pnl_second = $avg_pnl_minute / 60;
// 초 평균 손익률 계산
$avg_roi_second = $avg_roi_minute / 60;
// 년 평균 손익률 계산 ← roi = 수익률
$avg_roi_y = $avg_roi * 365;
// 년 평균 손익금 계산 ← pnl = 손익금
$avg_pnl_y = $avg_pnl * 365;
//월 평균 손익금 계산
$avg_roi_m = $avg_roi_y/12;
//월 평균 손익률 계산
$avg_pnl_m = $avg_pnl_y/12;
$buy_av = $cost_basis + $cur_cv;
// 기간별 손익 계산용 fallback: DB 최초(가장 오래된) 데이터 ---------------------------------------
$oldest_asset = 0;
$oldest_io_in = 0; $oldest_io_out = 0;
try {
$stmt = $pdo->query("SELECT total_asset_value FROM daemon_upbit_user_1m ORDER BY collected_at ASC LIMIT 1");
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $oldest_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$_oldest_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} ORDER BY wr_datetime ASC LIMIT 1");
if ($_oldest_io) { $oldest_io_in = (float)$_oldest_io['x2_total_bank_in']; $oldest_io_out = (float)$_oldest_io['x2_total_bank_out']; }
// 최근 365일 손익 계산 ----------------------------------------------------------------------------
$d365_date = date('Y-m-d', strtotime('-365 days'));
$d365_asset = $oldest_asset;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $d365_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $d365_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$d365_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$d365_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
$d365_in = $d365_io ? (float)$d365_io['x2_total_bank_in'] : $oldest_io_in;
$d365_out = $d365_io ? (float)$d365_io['x2_total_bank_out'] : $oldest_io_out;
$d365_should_have = $d365_asset + ($cur_in - $d365_in) - ($cur_out - $d365_out);
$d365_profit = $cur_av - $d365_should_have;
$d365_roi = $d365_should_have ? ($d365_profit / $d365_should_have * 100) : 0;
// 최근 180일(6개월) 손익 계산 -----------------------------------------------------------------------
$d180_date = date('Y-m-d', strtotime('-180 days'));
$d180_asset = $oldest_asset;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $d180_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $d180_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$d180_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$d180_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
$d180_in = $d180_io ? (float)$d180_io['x2_total_bank_in'] : $oldest_io_in;
$d180_out = $d180_io ? (float)$d180_io['x2_total_bank_out'] : $oldest_io_out;
$d180_should_have = $d180_asset + ($cur_in - $d180_in) - ($cur_out - $d180_out);
$d180_profit = $cur_av - $d180_should_have;
$d180_roi = $d180_should_have ? ($d180_profit / $d180_should_have * 100) : 0;
// 최근 90일(3개월) 손익 계산 -----------------------------------------------------------------------
$d90_date = date('Y-m-d', strtotime('-90 days'));
$d90_asset = $oldest_asset;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $d90_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $d90_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$d90_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$d90_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
$d90_in = $d90_io ? (float)$d90_io['x2_total_bank_in'] : $oldest_io_in;
$d90_out = $d90_io ? (float)$d90_io['x2_total_bank_out'] : $oldest_io_out;
$d90_should_have = $d90_asset + ($cur_in - $d90_in) - ($cur_out - $d90_out);
$d90_profit = $cur_av - $d90_should_have;
$d90_roi = $d90_should_have ? ($d90_profit / $d90_should_have * 100) : 0;
// 최근 30일 손익 계산 -----------------------------------------------------------------------------
$d30_date = date('Y-m-d', strtotime('-30 days'));
$d30_asset = $oldest_asset;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $d30_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $d30_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$d30_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$d30_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
$d30_in = $d30_io ? (float)$d30_io['x2_total_bank_in'] : $oldest_io_in;
$d30_out = $d30_io ? (float)$d30_io['x2_total_bank_out'] : $oldest_io_out;
$d30_should_have = $d30_asset + ($cur_in - $d30_in) - ($cur_out - $d30_out);
$d30_profit = $cur_av - $d30_should_have;
$d30_roi = $d30_should_have ? ($d30_profit / $d30_should_have * 100) : 0;
// 최근 15일/10일/7일/3일 손익 계산 ---------------------------------------------------------------
foreach ([15, 10, 7, 3] as $_d) {
$_date = date('Y-m-d', strtotime("-{$_d} days"));
$_asset = $oldest_asset;
try {
$stmt = $pdo->prepare("SELECT total_asset_value FROM daemon_upbit_user_1m WHERE collected_at >= :d ORDER BY collected_at ASC LIMIT 1");
$stmt->execute([':d' => $_date . ' 00:00:00']);
$r = $stmt->fetch(PDO::FETCH_ASSOC);
if ($r) $_asset = (float)$r['total_asset_value'];
} catch (Throwable $e) {}
$_io = sql_fetch("SELECT x2_total_bank_in, x2_total_bank_out FROM {$write_table} WHERE wr_datetime <= '{$_date} 23:59:59' ORDER BY wr_datetime DESC LIMIT 1");
$_in = $_io ? (float)$_io['x2_total_bank_in'] : $oldest_io_in;
$_out = $_io ? (float)$_io['x2_total_bank_out'] : $oldest_io_out;
$_should_have = $_asset + ($cur_in - $_in) - ($cur_out - $_out);
${'d'.$_d.'_profit'} = $cur_av - $_should_have;
${'d'.$_d.'_roi'} = $_should_have ? (${'d'.$_d.'_profit'} / $_should_have * 100) : 0;
}
?>