498 lines
18 KiB
PHP
498 lines
18 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../app/lib/auth.php';
|
|
require_once __DIR__ . '/../app/lib/db.php';
|
|
require_once __DIR__ . '/../app/lib/helpers.php';
|
|
require_once __DIR__ . '/../app/lib/installment_service.php';
|
|
|
|
check_auth();
|
|
|
|
$pdo = db();
|
|
$uid = user_id();
|
|
|
|
$ym = $_GET['ym'] ?? date('Y-m');
|
|
$start = $ym . '-01';
|
|
$end = date('Y-m-t', strtotime($start));
|
|
$today = date('Y-m-d');
|
|
$nextYm = date('Y-m', strtotime($ym . '-01 +1 month'));
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END), 0) AS income_total,
|
|
COALESCE(SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END), 0) AS expense_total,
|
|
COALESCE(SUM(CASE WHEN transaction_type = 'card_payment' THEN amount ELSE 0 END), 0) AS card_payment_total
|
|
FROM transactions
|
|
WHERE user_id = ?
|
|
AND transaction_date BETWEEN ? AND ?
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$summary = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN account_type IN ('bank','cash','other') THEN current_balance ELSE 0 END), 0) AS liquid_assets
|
|
FROM accounts
|
|
WHERE user_id = ?
|
|
AND is_active = 1
|
|
");
|
|
$stmt->execute([$uid]);
|
|
$assetSummary = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT COALESCE(SUM(t.amount), 0)
|
|
FROM transactions t
|
|
JOIN accounts a ON a.id = t.account_id
|
|
WHERE t.user_id = ?
|
|
AND t.transaction_type = 'expense'
|
|
AND a.account_type = 'card'
|
|
AND a.card_kind = 'credit'
|
|
AND t.billing_year_month = ?
|
|
AND COALESCE(t.is_installment, 0) = 0
|
|
");
|
|
$stmt->execute([$uid, $ym]);
|
|
$cardDueThisMonth = (float)$stmt->fetchColumn();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT COALESCE(SUM(t.amount), 0)
|
|
FROM transactions t
|
|
JOIN accounts a ON a.id = t.account_id
|
|
WHERE t.user_id = ?
|
|
AND t.transaction_type = 'expense'
|
|
AND a.account_type = 'card'
|
|
AND a.card_kind = 'credit'
|
|
AND t.billing_year_month = ?
|
|
AND COALESCE(t.is_installment, 0) = 0
|
|
");
|
|
$stmt->execute([$uid, $nextYm]);
|
|
$cardDueNextMonth = (float)$stmt->fetchColumn();
|
|
|
|
$installmentDueNextMonth = get_installment_due_this_month($uid, $nextYm);
|
|
$cardTotalDueNextMonth = $cardDueNextMonth + $installmentDueNextMonth;
|
|
|
|
$installmentDueThisMonth = get_installment_due_this_month($uid, $ym);
|
|
$installmentRemainingPrincipal = get_installment_remaining_principal($uid);
|
|
$installmentRemainingInterest = get_installment_remaining_interest($uid);
|
|
$installmentRemainingTotal = get_installment_remaining_total($uid);
|
|
|
|
$cardTotalDueThisMonth = $cardDueThisMonth + $installmentDueThisMonth;
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT c.name, SUM(t.amount) AS total_amount
|
|
FROM transactions t
|
|
JOIN categories c ON t.category_id = c.id
|
|
WHERE t.user_id = ?
|
|
AND t.transaction_type = 'expense'
|
|
AND t.transaction_date BETWEEN ? AND ?
|
|
GROUP BY c.id, c.name
|
|
ORDER BY total_amount DESC
|
|
LIMIT 8
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$expenseByCategory = $stmt->fetchAll();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
DATE_FORMAT(transaction_date, '%d') AS day_num,
|
|
COALESCE(SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END), 0) AS daily_expense
|
|
FROM transactions
|
|
WHERE user_id = ?
|
|
AND transaction_date BETWEEN ? AND ?
|
|
GROUP BY DATE_FORMAT(transaction_date, '%Y-%m-%d'), DATE_FORMAT(transaction_date, '%d')
|
|
ORDER BY DATE_FORMAT(transaction_date, '%Y-%m-%d') ASC
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$dailyRows = $stmt->fetchAll();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM accounts
|
|
WHERE user_id = ?
|
|
AND is_active = 1
|
|
ORDER BY FIELD(account_type, 'bank', 'card', 'cash', 'other'), id ASC
|
|
");
|
|
$stmt->execute([$uid]);
|
|
$accounts = $stmt->fetchAll();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(scheduled_principal), 0) AS due_principal,
|
|
COALESCE(SUM(scheduled_interest), 0) AS due_interest,
|
|
COALESCE(SUM(scheduled_total), 0) AS due_total
|
|
FROM loan_schedules ls
|
|
JOIN loans l ON l.id = ls.loan_id
|
|
WHERE l.user_id = ?
|
|
AND ls.is_paid = 0
|
|
AND ls.due_date BETWEEN ? AND ?
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$loanDue = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(scheduled_principal), 0) AS overdue_principal,
|
|
COALESCE(SUM(scheduled_interest), 0) AS overdue_interest,
|
|
COALESCE(SUM(scheduled_total), 0) AS overdue_total
|
|
FROM loan_schedules ls
|
|
JOIN loans l ON l.id = ls.loan_id
|
|
WHERE l.user_id = ?
|
|
AND ls.is_paid = 0
|
|
AND ls.due_date < ?
|
|
");
|
|
$stmt->execute([$uid, $today]);
|
|
$loanOverdue = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT COALESCE(SUM(current_principal_balance), 0) AS remaining_principal
|
|
FROM loans
|
|
WHERE user_id = ?
|
|
AND status = 'active'
|
|
");
|
|
$stmt->execute([$uid]);
|
|
$loanPrincipalRow = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN ls.is_paid = 0 THEN ls.scheduled_interest ELSE 0 END), 0) AS remaining_interest,
|
|
COALESCE(SUM(CASE WHEN ls.is_paid = 0 THEN ls.scheduled_total ELSE 0 END), 0) AS remaining_total
|
|
FROM loan_schedules ls
|
|
JOIN loans l ON l.id = ls.loan_id
|
|
WHERE l.user_id = ?
|
|
AND l.status = 'active'
|
|
");
|
|
$stmt->execute([$uid]);
|
|
$loanRemaining = $stmt->fetch();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
DATE_FORMAT(payment_date, '%d') AS day_num,
|
|
COALESCE(SUM(total_amount), 0) AS daily_loan_payment
|
|
FROM loan_payments
|
|
WHERE user_id = ?
|
|
AND payment_date BETWEEN ? AND ?
|
|
GROUP BY DATE_FORMAT(payment_date, '%Y-%m-%d'), DATE_FORMAT(payment_date, '%d')
|
|
ORDER BY DATE_FORMAT(payment_date, '%Y-%m-%d') ASC
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$loanPaymentRows = $stmt->fetchAll();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
l.loan_name,
|
|
l.lender_name,
|
|
ls.due_date,
|
|
ls.scheduled_principal,
|
|
ls.scheduled_interest,
|
|
ls.scheduled_total
|
|
FROM loan_schedules ls
|
|
JOIN loans l ON l.id = ls.loan_id
|
|
WHERE l.user_id = ?
|
|
AND ls.is_paid = 0
|
|
AND ls.due_date BETWEEN ? AND ?
|
|
ORDER BY ls.due_date ASC, l.id ASC
|
|
LIMIT 10
|
|
");
|
|
$stmt->execute([$uid, $start, $end]);
|
|
$loanUpcoming = $stmt->fetchAll();
|
|
|
|
$liquidAssets = (float)($assetSummary['liquid_assets'] ?? 0);
|
|
|
|
$incomeTotal = (float)($summary['income_total'] ?? 0);
|
|
$expenseTotal = (float)($summary['expense_total'] ?? 0);
|
|
$cardPaymentTotal = (float)($summary['card_payment_total'] ?? 0);
|
|
|
|
$loanDuePrincipal = (float)($loanDue['due_principal'] ?? 0);
|
|
$loanDueInterest = (float)($loanDue['due_interest'] ?? 0);
|
|
$loanDueTotal = (float)($loanDue['due_total'] ?? 0);
|
|
|
|
$loanOverduePrincipal = (float)($loanOverdue['overdue_principal'] ?? 0);
|
|
$loanOverdueInterest = (float)($loanOverdue['overdue_interest'] ?? 0);
|
|
$loanOverdueTotal = (float)($loanOverdue['overdue_total'] ?? 0);
|
|
|
|
$loanRemainingPrincipal = (float)($loanPrincipalRow['remaining_principal'] ?? 0);
|
|
$loanRemainingInterest = (float)($loanRemaining['remaining_interest'] ?? 0);
|
|
$loanRemainingTotal = (float)($loanRemaining['remaining_total'] ?? 0);
|
|
|
|
$netCashFlow = $incomeTotal - $expenseTotal - $loanDueTotal;
|
|
|
|
$categoryLabels = array_map(fn($r) => $r['name'], $expenseByCategory);
|
|
$categoryValues = array_map(fn($r) => (float)$r['total_amount'], $expenseByCategory);
|
|
|
|
$dayLabels = array_map(fn($r) => $r['day_num'] . '일', $dailyRows);
|
|
$dayValues = array_map(fn($r) => (float)$r['daily_expense'], $dailyRows);
|
|
|
|
$loanDayLabels = array_map(fn($r) => $r['day_num'] . '일', $loanPaymentRows);
|
|
$loanDayValues = array_map(fn($r) => (float)$r['daily_loan_payment'], $loanPaymentRows);
|
|
|
|
require __DIR__ . '/../app/views/header.php';
|
|
?>
|
|
|
|
<div class="page-head">
|
|
<h2>대시보드</h2>
|
|
<form method="get" class="d-flex gap-2">
|
|
<input type="month" name="ym" class="form-control" value="<?= h($ym) ?>">
|
|
<button class="btn btn-outline-primary">조회</button>
|
|
</form>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-summary-card finance-summary-income h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">이번 달 수입</div>
|
|
<div class="hero-value"><?= won($incomeTotal) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-summary-card finance-summary-expense h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">이번 달 지출</div>
|
|
<div class="hero-value"><?= won($expenseTotal) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-summary-card finance-summary-loan h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">이번 달 카드 총청구액</div>
|
|
<div class="hero-value"><?= won($cardTotalDueThisMonth) ?></div>
|
|
<div class="small text-secondary mt-2">
|
|
일반 <?= won($cardDueThisMonth) ?> · 할부 <?= won($installmentDueThisMonth) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-summary-card finance-summary-net h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">이번 달 대출 납부 예정액</div>
|
|
<div class="hero-value"><?= won($loanDueTotal) ?></div>
|
|
<div class="small text-secondary mt-2">
|
|
원금 <?= won($loanDuePrincipal) ?> · 이자 <?= won($loanDueInterest) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">유동자산 합계</div>
|
|
<div class="stat-value text-primary"><?= won($liquidAssets) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">다음달 카드 예정액</div>
|
|
<div class="stat-value text-danger"><?= won($cardTotalDueNextMonth) ?></div>
|
|
<div class="small text-secondary mt-2">
|
|
일반 <?= won($cardDueNextMonth) ?> · 할부 <?= won($installmentDueNextMonth) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">남은 할부 총액</div>
|
|
<div class="stat-value"><?= won($installmentRemainingTotal) ?></div>
|
|
<div class="small text-secondary mt-2">
|
|
원금 <?= won($installmentRemainingPrincipal) ?> · 이자 <?= won($installmentRemainingInterest) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-6 col-xl-3">
|
|
<div class="card finance-card h-100 border-danger">
|
|
<div class="card-body">
|
|
<div class="stat-label">대출 미납액</div>
|
|
<div class="stat-value text-danger"><?= won($loanOverdueTotal) ?></div>
|
|
<div class="small text-secondary mt-2">
|
|
원금 <?= won($loanOverduePrincipal) ?> · 이자 <?= won($loanOverdueInterest) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<div class="col-12 col-md-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">남은 대출 원금</div>
|
|
<div class="stat-value text-primary"><?= won($loanRemainingPrincipal) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">남은 대출 이자</div>
|
|
<div class="stat-value text-danger"><?= won($loanRemainingInterest) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-md-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="stat-label">이번 달 순현금흐름</div>
|
|
<div class="stat-value <?= $netCashFlow >= 0 ? 'text-primary' : 'text-danger' ?>">
|
|
<?= won($netCashFlow) ?>
|
|
</div>
|
|
<div class="small text-secondary mt-2">수입 - 지출 - 대출예정</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<?php foreach ($accounts as $account): ?>
|
|
<div class="col-12 col-md-6 col-xl-4">
|
|
<a href="/transactions.php?ym=<?= h($ym) ?>&account_id=<?= (int)$account['id'] ?>" class="text-decoration-none">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-body">
|
|
<div class="eyebrow"><?= h($account['institution_name']) ?> / <?= h($account['account_type']) ?></div>
|
|
<div class="card-title-sm"><?= h($account['account_name']) ?></div>
|
|
<div class="stat-value <?= $account['account_type'] === 'card' ? 'text-danger' : 'text-primary' ?>">
|
|
<?= won($account['current_balance']) ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</a>
|
|
</div>
|
|
<?php endforeach; ?>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<div class="col-12 col-xl-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-header bg-transparent border-0 fw-bold pt-4 px-4">카테고리별 지출</div>
|
|
<div class="card-body pt-0">
|
|
<canvas id="categoryChart" style="min-height:280px;"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-xl-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-header bg-transparent border-0 fw-bold pt-4 px-4">일자별 지출</div>
|
|
<div class="card-body pt-0">
|
|
<canvas id="dailyChart" style="min-height:280px;"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-12 col-xl-4">
|
|
<div class="card finance-card h-100">
|
|
<div class="card-header bg-transparent border-0 fw-bold pt-4 px-4">일자별 대출 납부</div>
|
|
<div class="card-body pt-0">
|
|
<canvas id="loanPaymentChart" style="min-height:280px;"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-3">
|
|
<div class="col-12">
|
|
<div class="card finance-card">
|
|
<div class="card-header bg-transparent border-0 fw-bold pt-4 px-4">이번 달 대출 납부 예정</div>
|
|
<div class="card-body pt-0 mobile-scroll">
|
|
<table class="table table-hover align-middle mb-0">
|
|
<thead>
|
|
<tr>
|
|
<th>대출명</th>
|
|
<th>기관</th>
|
|
<th>납부일</th>
|
|
<th class="text-end">원금</th>
|
|
<th class="text-end">이자</th>
|
|
<th class="text-end">합계</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($loanUpcoming as $row): ?>
|
|
<tr>
|
|
<td><?= h($row['loan_name']) ?></td>
|
|
<td><?= h($row['lender_name'] ?: '-') ?></td>
|
|
<td><?= ymd($row['due_date']) ?></td>
|
|
<td class="text-end"><?= won($row['scheduled_principal']) ?></td>
|
|
<td class="text-end text-danger"><?= won($row['scheduled_interest']) ?></td>
|
|
<td class="text-end fw-bold"><?= won($row['scheduled_total']) ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
|
|
<?php if (!$loanUpcoming): ?>
|
|
<tr>
|
|
<td colspan="6" class="text-center text-secondary py-5">이번 달 대출 납부 예정이 없습니다.</td>
|
|
</tr>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
const categoryLabels = <?= json_encode($categoryLabels, JSON_UNESCAPED_UNICODE) ?>;
|
|
const categoryValues = <?= json_encode($categoryValues, JSON_UNESCAPED_UNICODE) ?>;
|
|
const dayLabels = <?= json_encode($dayLabels, JSON_UNESCAPED_UNICODE) ?>;
|
|
const dayValues = <?= json_encode($dayValues, JSON_UNESCAPED_UNICODE) ?>;
|
|
const loanDayLabels = <?= json_encode($loanDayLabels, JSON_UNESCAPED_UNICODE) ?>;
|
|
const loanDayValues = <?= json_encode($loanDayValues, JSON_UNESCAPED_UNICODE) ?>;
|
|
|
|
new Chart(document.getElementById('categoryChart'), {
|
|
type: 'doughnut',
|
|
data: {
|
|
labels: categoryLabels,
|
|
datasets: [{ data: categoryValues }]
|
|
},
|
|
options: {
|
|
plugins: { legend: { position: 'bottom' } }
|
|
}
|
|
});
|
|
|
|
new Chart(document.getElementById('dailyChart'), {
|
|
type: 'bar',
|
|
data: {
|
|
labels: dayLabels,
|
|
datasets: [{
|
|
label: '지출',
|
|
data: dayValues,
|
|
borderRadius: 8
|
|
}]
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: true
|
|
}
|
|
});
|
|
|
|
new Chart(document.getElementById('loanPaymentChart'), {
|
|
type: 'bar',
|
|
data: {
|
|
labels: loanDayLabels,
|
|
datasets: [{
|
|
label: '대출 납부',
|
|
data: loanDayValues,
|
|
borderRadius: 8
|
|
}]
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: true
|
|
}
|
|
});
|
|
</script>
|
|
|
|
<?php require __DIR__ . '/../app/views/footer.php'; ?>
|