1154 lines
35 KiB
PHP
1154 lines
35 KiB
PHP
<?php
|
|
|
|
require_once __DIR__ . '/db.php';
|
|
require_once __DIR__ . '/account_service.php';
|
|
|
|
function add_months_keep_day(string $date, int $months, int $paymentDay): string
|
|
{
|
|
$base = new DateTime($date);
|
|
$base->modify('first day of this month');
|
|
|
|
if ($months > 0) {
|
|
$base->modify('+' . $months . ' month');
|
|
}
|
|
|
|
$lastDay = (int)$base->format('t');
|
|
$day = min($paymentDay, $lastDay);
|
|
$base->setDate((int)$base->format('Y'), (int)$base->format('m'), $day);
|
|
|
|
return $base->format('Y-m-d');
|
|
}
|
|
|
|
function monthly_rate(float $annualRate): float
|
|
{
|
|
return ($annualRate / 100.0) / 12.0;
|
|
}
|
|
|
|
function calc_equal_payment_monthly_amount(float $principal, float $annualRate, int $months): float
|
|
{
|
|
if ($months <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
$r = monthly_rate($annualRate);
|
|
|
|
if ($r <= 0) {
|
|
return round($principal / $months, 2);
|
|
}
|
|
|
|
$payment = $principal * ($r * pow(1 + $r, $months)) / (pow(1 + $r, $months) - 1);
|
|
return round($payment, 2);
|
|
}
|
|
|
|
function build_loan_schedule_rows(
|
|
float $principal,
|
|
float $annualRate,
|
|
string $startDate,
|
|
int $graceMonths,
|
|
int $repaymentMonths,
|
|
string $repaymentMethod,
|
|
int $paymentDay
|
|
): array {
|
|
$rows = [];
|
|
$balance = round($principal, 2);
|
|
$cycleNo = 1;
|
|
$firstDueOffset = 1;
|
|
|
|
for ($i = 0; $i < $graceMonths; $i++) {
|
|
$dueDate = add_months_keep_day($startDate, $i + $firstDueOffset, $paymentDay);
|
|
$interest = round($balance * monthly_rate($annualRate), 2);
|
|
|
|
$rows[] = [
|
|
'cycle_no' => $cycleNo++,
|
|
'due_date' => $dueDate,
|
|
'opening_principal' => $balance,
|
|
'scheduled_principal' => 0.00,
|
|
'scheduled_interest' => $interest,
|
|
'scheduled_total' => round($interest, 2),
|
|
'closing_principal' => $balance,
|
|
'payment_phase' => 'grace',
|
|
];
|
|
}
|
|
|
|
$repaymentStartOffset = $graceMonths + $firstDueOffset;
|
|
|
|
if ($repaymentMethod === 'interest_only_then_equal_payment' || $repaymentMethod === 'equal_payment') {
|
|
$monthlyPayment = calc_equal_payment_monthly_amount($balance, $annualRate, $repaymentMonths);
|
|
|
|
for ($i = 0; $i < $repaymentMonths; $i++) {
|
|
$dueDate = add_months_keep_day($startDate, $repaymentStartOffset + $i, $paymentDay);
|
|
$opening = $balance;
|
|
$interest = round($opening * monthly_rate($annualRate), 2);
|
|
$principalPart = round($monthlyPayment - $interest, 2);
|
|
|
|
if ($i === $repaymentMonths - 1 || $principalPart > $balance) {
|
|
$principalPart = $balance;
|
|
}
|
|
|
|
$total = round($principalPart + $interest, 2);
|
|
$balance = round($balance - $principalPart, 2);
|
|
|
|
$rows[] = [
|
|
'cycle_no' => $cycleNo++,
|
|
'due_date' => $dueDate,
|
|
'opening_principal' => $opening,
|
|
'scheduled_principal' => $principalPart,
|
|
'scheduled_interest' => $interest,
|
|
'scheduled_total' => $total,
|
|
'closing_principal' => $balance,
|
|
'payment_phase' => 'repayment',
|
|
];
|
|
}
|
|
} elseif ($repaymentMethod === 'interest_only_then_equal_principal' || $repaymentMethod === 'equal_principal') {
|
|
$principalBase = round($balance / $repaymentMonths, 2);
|
|
|
|
for ($i = 0; $i < $repaymentMonths; $i++) {
|
|
$dueDate = add_months_keep_day($startDate, $repaymentStartOffset + $i, $paymentDay);
|
|
$opening = $balance;
|
|
$interest = round($opening * monthly_rate($annualRate), 2);
|
|
|
|
$principalPart = $principalBase;
|
|
if ($i === $repaymentMonths - 1 || $principalPart > $balance) {
|
|
$principalPart = $balance;
|
|
}
|
|
|
|
$total = round($principalPart + $interest, 2);
|
|
$balance = round($balance - $principalPart, 2);
|
|
|
|
$rows[] = [
|
|
'cycle_no' => $cycleNo++,
|
|
'due_date' => $dueDate,
|
|
'opening_principal' => $opening,
|
|
'scheduled_principal' => $principalPart,
|
|
'scheduled_interest' => $interest,
|
|
'scheduled_total' => $total,
|
|
'closing_principal' => $balance,
|
|
'payment_phase' => 'repayment',
|
|
];
|
|
}
|
|
} elseif ($repaymentMethod === 'interest_only_then_bullet' || $repaymentMethod === 'bullet') {
|
|
for ($i = 0; $i < $repaymentMonths; $i++) {
|
|
$dueDate = add_months_keep_day($startDate, $repaymentStartOffset + $i, $paymentDay);
|
|
$opening = $balance;
|
|
$interest = round($opening * monthly_rate($annualRate), 2);
|
|
|
|
$principalPart = 0.00;
|
|
if ($i === $repaymentMonths - 1) {
|
|
$principalPart = $balance;
|
|
}
|
|
|
|
$total = round($principalPart + $interest, 2);
|
|
$balance = round($balance - $principalPart, 2);
|
|
|
|
$rows[] = [
|
|
'cycle_no' => $cycleNo++,
|
|
'due_date' => $dueDate,
|
|
'opening_principal' => $opening,
|
|
'scheduled_principal' => $principalPart,
|
|
'scheduled_interest' => $interest,
|
|
'scheduled_total' => $total,
|
|
'closing_principal' => $balance,
|
|
'payment_phase' => 'repayment',
|
|
];
|
|
}
|
|
} else {
|
|
throw new RuntimeException('지원하지 않는 상환 방식입니다.');
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
function get_category_id_by_name(int $userId, string $categoryType, string $name): ?int
|
|
{
|
|
$pdo = db();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT id
|
|
FROM categories
|
|
WHERE user_id = ?
|
|
AND category_type = ?
|
|
AND name = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$userId, $categoryType, $name]);
|
|
$row = $stmt->fetch();
|
|
|
|
return $row ? (int)$row['id'] : null;
|
|
}
|
|
|
|
function build_tx_fingerprint(
|
|
int $userId,
|
|
int $accountId,
|
|
?int $relatedAccountId,
|
|
int $categoryId,
|
|
string $transactionType,
|
|
float $amount,
|
|
string $transactionDate,
|
|
?string $merchantName,
|
|
?string $description,
|
|
?int $sourceLoanId = null
|
|
): string {
|
|
$raw = implode('|', [
|
|
$userId,
|
|
$accountId,
|
|
$relatedAccountId ?? 0,
|
|
$categoryId,
|
|
$transactionType,
|
|
number_format($amount, 2, '.', ''),
|
|
$transactionDate,
|
|
trim((string)$merchantName),
|
|
trim((string)$description),
|
|
$sourceLoanId ?? 0,
|
|
]);
|
|
|
|
return hash('sha256', $raw);
|
|
}
|
|
|
|
function insert_transaction_row(array $data): int
|
|
{
|
|
$pdo = db();
|
|
|
|
$sourceLoanId = !empty($data['source_loan_id']) ? (int)$data['source_loan_id'] : null;
|
|
|
|
$fingerprint = build_tx_fingerprint(
|
|
(int)$data['user_id'],
|
|
(int)$data['account_id'],
|
|
!empty($data['related_account_id']) ? (int)$data['related_account_id'] : null,
|
|
(int)$data['category_id'],
|
|
(string)$data['transaction_type'],
|
|
(float)$data['amount'],
|
|
(string)$data['transaction_date'],
|
|
$data['merchant_name'] ?? null,
|
|
$data['description'] ?? null,
|
|
$sourceLoanId
|
|
);
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT id
|
|
FROM transactions
|
|
WHERE user_id = ?
|
|
AND fingerprint = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([
|
|
$data['user_id'],
|
|
$fingerprint
|
|
]);
|
|
$exists = $stmt->fetch();
|
|
|
|
if ($exists) {
|
|
return (int)$exists['id'];
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO transactions
|
|
(
|
|
user_id,
|
|
account_id,
|
|
category_id,
|
|
transaction_type,
|
|
amount,
|
|
is_installment,
|
|
installment_months,
|
|
installment_interest_rate,
|
|
installment_interest_total,
|
|
installment_total_billed,
|
|
installment_prepay_amount,
|
|
transaction_date,
|
|
merchant_name,
|
|
description,
|
|
related_account_id,
|
|
source_loan_id,
|
|
fingerprint
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, 0, NULL, 0, 0, NULL, 0, ?, ?, ?, ?, ?, ?)
|
|
");
|
|
|
|
$stmt->execute([
|
|
$data['user_id'],
|
|
$data['account_id'],
|
|
$data['category_id'],
|
|
$data['transaction_type'],
|
|
$data['amount'],
|
|
$data['transaction_date'],
|
|
$data['merchant_name'],
|
|
$data['description'],
|
|
$data['related_account_id'],
|
|
$sourceLoanId,
|
|
$fingerprint,
|
|
]);
|
|
|
|
return (int)$pdo->lastInsertId();
|
|
}
|
|
|
|
function create_loan_schedule_only(
|
|
int $loanId,
|
|
float $principalAmount,
|
|
float $annualInterestRate,
|
|
string $startDate,
|
|
int $graceMonths,
|
|
int $repaymentMonths,
|
|
string $repaymentMethod,
|
|
int $paymentDay
|
|
): void {
|
|
$pdo = db();
|
|
|
|
$rows = build_loan_schedule_rows(
|
|
$principalAmount,
|
|
$annualInterestRate,
|
|
$startDate,
|
|
$graceMonths,
|
|
$repaymentMonths,
|
|
$repaymentMethod,
|
|
$paymentDay
|
|
);
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO loan_schedules
|
|
(
|
|
loan_id, cycle_no, due_date,
|
|
opening_principal, scheduled_principal, scheduled_interest, scheduled_total,
|
|
closing_principal, payment_phase, is_paid, paid_at
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NULL)
|
|
");
|
|
|
|
foreach ($rows as $row) {
|
|
$stmt->execute([
|
|
$loanId,
|
|
$row['cycle_no'],
|
|
$row['due_date'],
|
|
$row['opening_principal'],
|
|
$row['scheduled_principal'],
|
|
$row['scheduled_interest'],
|
|
$row['scheduled_total'],
|
|
$row['closing_principal'],
|
|
$row['payment_phase'],
|
|
]);
|
|
}
|
|
}
|
|
|
|
function create_loan_with_schedule(array $data): int
|
|
{
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO loans
|
|
(
|
|
user_id, account_id, loan_name, lender_name,
|
|
principal_amount, annual_interest_rate,
|
|
start_date, maturity_date,
|
|
grace_period_months, repayment_months, repayment_method,
|
|
payment_day, current_principal_balance, status, description
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?)
|
|
");
|
|
|
|
$stmt->execute([
|
|
$data['user_id'],
|
|
$data['account_id'] ?: null,
|
|
$data['loan_name'],
|
|
$data['lender_name'] ?: null,
|
|
$data['principal_amount'],
|
|
$data['annual_interest_rate'],
|
|
$data['start_date'],
|
|
$data['maturity_date'] ?: null,
|
|
$data['grace_period_months'],
|
|
$data['repayment_months'],
|
|
$data['repayment_method'],
|
|
$data['payment_day'],
|
|
$data['principal_amount'],
|
|
$data['description'] ?: null,
|
|
]);
|
|
|
|
$loanId = (int)$pdo->lastInsertId();
|
|
|
|
create_loan_schedule_only(
|
|
$loanId,
|
|
(float)$data['principal_amount'],
|
|
(float)$data['annual_interest_rate'],
|
|
(string)$data['start_date'],
|
|
(int)$data['grace_period_months'],
|
|
(int)$data['repayment_months'],
|
|
(string)$data['repayment_method'],
|
|
(int)$data['payment_day']
|
|
);
|
|
|
|
$pdo->commit();
|
|
return $loanId;
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function backfill_loan_transactions_and_mark_paid(
|
|
int $userId,
|
|
int $loanId,
|
|
?int $accountId,
|
|
string $todayDate
|
|
): void {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if (!$loan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
if (empty($accountId)) {
|
|
throw new RuntimeException('과거 거래 일괄 생성에는 입출금 계좌가 필요합니다.');
|
|
}
|
|
|
|
$incomeCategoryId = get_category_id_by_name($userId, 'income', '대출금');
|
|
if (!$incomeCategoryId) {
|
|
throw new RuntimeException('income 카테고리 "대출금"이 없습니다.');
|
|
}
|
|
|
|
$expenseCategoryId = get_category_id_by_name($userId, 'expense', '대출상환');
|
|
if (!$expenseCategoryId) {
|
|
throw new RuntimeException('expense 카테고리 "대출상환"이 없습니다.');
|
|
}
|
|
|
|
insert_transaction_row([
|
|
'user_id' => $userId,
|
|
'account_id' => $accountId,
|
|
'category_id' => $incomeCategoryId,
|
|
'transaction_type' => 'income',
|
|
'amount' => (float)$loan['principal_amount'],
|
|
'transaction_date' => $loan['start_date'],
|
|
'merchant_name' => $loan['loan_name'],
|
|
'description' => '[대출실행] ' . ($loan['lender_name'] ?: $loan['loan_name']),
|
|
'related_account_id' => null,
|
|
'source_loan_id' => $loanId,
|
|
]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
AND due_date <= ?
|
|
ORDER BY due_date ASC, cycle_no ASC
|
|
");
|
|
$stmt->execute([$loanId, $todayDate]);
|
|
$pastSchedules = $stmt->fetchAll();
|
|
|
|
foreach ($pastSchedules as $schedule) {
|
|
if ((int)$schedule['is_paid'] === 1) {
|
|
continue;
|
|
}
|
|
|
|
$principal = (float)$schedule['scheduled_principal'];
|
|
$interest = (float)$schedule['scheduled_interest'];
|
|
$total = (float)$schedule['scheduled_total'];
|
|
|
|
$stmt2 = $pdo->prepare("
|
|
INSERT INTO loan_payments
|
|
(
|
|
user_id, loan_id, loan_schedule_id, account_id,
|
|
payment_date, principal_amount, interest_amount, fee_amount,
|
|
total_amount, payment_type, is_auto_generated, description
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, 'scheduled', 1, ?)
|
|
");
|
|
$stmt2->execute([
|
|
$userId,
|
|
$loanId,
|
|
$schedule['id'],
|
|
$accountId,
|
|
$schedule['due_date'],
|
|
$principal,
|
|
$interest,
|
|
$total,
|
|
'[자동반영] 대출 상환 회차 ' . $schedule['cycle_no'],
|
|
]);
|
|
|
|
insert_transaction_row([
|
|
'user_id' => $userId,
|
|
'account_id' => $accountId,
|
|
'category_id' => $expenseCategoryId,
|
|
'transaction_type' => 'expense',
|
|
'amount' => $total,
|
|
'transaction_date' => $schedule['due_date'],
|
|
'merchant_name' => $loan['loan_name'],
|
|
'description' => '[대출상환] 원금 ' . number_format($principal, 0) . ' / 이자 ' . number_format($interest, 0),
|
|
'related_account_id' => null,
|
|
'source_loan_id' => $loanId,
|
|
]);
|
|
|
|
$stmt2 = $pdo->prepare("
|
|
UPDATE loan_schedules
|
|
SET is_paid = 1, paid_at = NOW()
|
|
WHERE id = ?
|
|
");
|
|
$stmt2->execute([$schedule['id']]);
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN is_paid = 0 THEN scheduled_principal ELSE 0 END), 0) AS remaining_principal
|
|
FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
$remaining = $stmt->fetch();
|
|
|
|
$remainingPrincipal = (float)($remaining['remaining_principal'] ?? 0);
|
|
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loans
|
|
SET
|
|
current_principal_balance = ?,
|
|
status = CASE WHEN ? <= 0.009 THEN 'closed' ELSE 'active' END
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$remainingPrincipal, $remainingPrincipal, $loanId]);
|
|
|
|
$pdo->commit();
|
|
recalculate_account_balance($accountId);
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function create_loan_with_full_backfill(array $data): int
|
|
{
|
|
$loanId = create_loan_with_schedule($data);
|
|
|
|
if (!empty($data['create_full_history'])) {
|
|
backfill_loan_transactions_and_mark_paid(
|
|
(int)$data['user_id'],
|
|
$loanId,
|
|
!empty($data['account_id']) ? (int)$data['account_id'] : null,
|
|
$data['today_date'] ?? date('Y-m-d')
|
|
);
|
|
}
|
|
|
|
return $loanId;
|
|
}
|
|
|
|
function reset_loan_auto_history_and_reapply(
|
|
int $userId,
|
|
int $loanId,
|
|
string $todayDate
|
|
): void {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if (!$loan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
if (empty($loan['account_id'])) {
|
|
throw new RuntimeException('연결된 입출금 계좌가 없어 자동반영 리셋을 할 수 없습니다.');
|
|
}
|
|
|
|
// 자동 생성 거래 삭제
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM transactions
|
|
WHERE user_id = ?
|
|
AND source_loan_id = ?
|
|
");
|
|
$stmt->execute([$userId, $loanId]);
|
|
|
|
// 자동반영 payment 삭제
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loan_payments
|
|
WHERE user_id = ?
|
|
AND loan_id = ?
|
|
AND is_auto_generated = 1
|
|
");
|
|
$stmt->execute([$userId, $loanId]);
|
|
|
|
// 스케줄 초기화
|
|
$stmt = $pdo->prepare("DELETE FROM loan_schedules WHERE loan_id = ?");
|
|
$stmt->execute([$loanId]);
|
|
|
|
// 원금/상태 초기화
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loans
|
|
SET
|
|
current_principal_balance = principal_amount,
|
|
status = 'active'
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
|
|
// 초기 스케줄 재생성
|
|
create_loan_schedule_only(
|
|
$loanId,
|
|
(float)$loan['principal_amount'],
|
|
(float)$loan['annual_interest_rate'],
|
|
(string)$loan['start_date'],
|
|
(int)$loan['grace_period_months'],
|
|
(int)$loan['repayment_months'],
|
|
(string)$loan['repayment_method'],
|
|
(int)$loan['payment_day']
|
|
);
|
|
|
|
$pdo->commit();
|
|
|
|
// 다시 자동반영 적용
|
|
backfill_loan_transactions_and_mark_paid(
|
|
$userId,
|
|
$loanId,
|
|
(int)$loan['account_id'],
|
|
$todayDate
|
|
);
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function get_loan_remaining_summary(int $loanId): array
|
|
{
|
|
$pdo = db();
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE WHEN is_paid = 0 THEN scheduled_principal ELSE 0 END), 0) AS remaining_principal,
|
|
COALESCE(SUM(CASE WHEN is_paid = 0 THEN scheduled_interest ELSE 0 END), 0) AS remaining_interest,
|
|
COALESCE(SUM(CASE WHEN is_paid = 0 THEN scheduled_total ELSE 0 END), 0) AS remaining_total
|
|
FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
$row = $stmt->fetch();
|
|
|
|
return [
|
|
'remaining_principal' => (float)($row['remaining_principal'] ?? 0),
|
|
'remaining_interest' => (float)($row['remaining_interest'] ?? 0),
|
|
'remaining_total' => (float)($row['remaining_total'] ?? 0),
|
|
];
|
|
}
|
|
|
|
function pay_loan_schedule(
|
|
int $userId,
|
|
int $loanScheduleId,
|
|
?int $accountId,
|
|
string $paymentDate,
|
|
?string $description = null
|
|
): void {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
ls.*,
|
|
l.user_id,
|
|
l.id AS loan_id,
|
|
l.loan_name
|
|
FROM loan_schedules ls
|
|
JOIN loans l ON l.id = ls.loan_id
|
|
WHERE ls.id = ?
|
|
AND l.user_id = ?
|
|
");
|
|
$stmt->execute([$loanScheduleId, $userId]);
|
|
$schedule = $stmt->fetch();
|
|
|
|
if (!$schedule) {
|
|
throw new RuntimeException('상환 회차를 찾을 수 없습니다.');
|
|
}
|
|
|
|
if ((int)$schedule['is_paid'] === 1) {
|
|
throw new RuntimeException('이미 납부된 회차입니다.');
|
|
}
|
|
|
|
$principal = (float)$schedule['scheduled_principal'];
|
|
$interest = (float)$schedule['scheduled_interest'];
|
|
$total = (float)$schedule['scheduled_total'];
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO loan_payments
|
|
(
|
|
user_id, loan_id, loan_schedule_id, account_id,
|
|
payment_date, principal_amount, interest_amount, fee_amount,
|
|
total_amount, payment_type, is_auto_generated, description
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, 'scheduled', 0, ?)
|
|
");
|
|
$stmt->execute([
|
|
$userId,
|
|
$schedule['loan_id'],
|
|
$loanScheduleId,
|
|
$accountId ?: null,
|
|
$paymentDate,
|
|
$principal,
|
|
$interest,
|
|
$total,
|
|
$description,
|
|
]);
|
|
|
|
if (!empty($accountId) && $total > 0) {
|
|
$expenseCategoryId = get_category_id_by_name($userId, 'expense', '대출상환');
|
|
|
|
if ($expenseCategoryId) {
|
|
insert_transaction_row([
|
|
'user_id' => $userId,
|
|
'account_id' => $accountId,
|
|
'category_id' => $expenseCategoryId,
|
|
'transaction_type' => 'expense',
|
|
'amount' => $total,
|
|
'transaction_date' => $paymentDate,
|
|
'merchant_name' => $schedule['loan_name'],
|
|
'description' => '[대출상환] 원금 ' .
|
|
number_format($principal, 0) .
|
|
' / 이자 ' .
|
|
number_format($interest, 0),
|
|
'related_account_id' => null,
|
|
'source_loan_id' => $schedule['loan_id'],
|
|
]);
|
|
}
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loan_schedules
|
|
SET is_paid = 1, paid_at = NOW()
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$loanScheduleId]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loans
|
|
SET current_principal_balance = GREATEST(current_principal_balance - ?, 0)
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$principal, $schedule['loan_id']]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT current_principal_balance
|
|
FROM loans
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$schedule['loan_id']]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if ($loan && (float)$loan['current_principal_balance'] <= 0.009) {
|
|
$stmt = $pdo->prepare("UPDATE loans SET status = 'closed' WHERE id = ?");
|
|
$stmt->execute([$schedule['loan_id']]);
|
|
}
|
|
|
|
$pdo->commit();
|
|
|
|
if (!empty($accountId)) {
|
|
recalculate_account_balance((int)$accountId);
|
|
}
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function rebuild_remaining_loan_schedules(int $loanId): void
|
|
{
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if (!$loan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
AND is_paid = 0
|
|
ORDER BY cycle_no ASC
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
$remaining = $stmt->fetchAll();
|
|
|
|
if (!$remaining) {
|
|
$stmt = $pdo->prepare("UPDATE loans SET status = 'closed', current_principal_balance = 0 WHERE id = ?");
|
|
$stmt->execute([$loanId]);
|
|
$pdo->commit();
|
|
return;
|
|
}
|
|
|
|
$remainingMonths = count($remaining);
|
|
$firstUnpaid = $remaining[0];
|
|
$newStartDate = $firstUnpaid['due_date'];
|
|
|
|
if ($firstUnpaid['payment_phase'] === 'grace') {
|
|
$graceCount = 0;
|
|
foreach ($remaining as $r) {
|
|
if ($r['payment_phase'] === 'grace') {
|
|
$graceCount++;
|
|
} else {
|
|
break;
|
|
}
|
|
}
|
|
$graceMonths = $graceCount;
|
|
$repaymentMonths = $remainingMonths - $graceMonths;
|
|
} else {
|
|
$graceMonths = 0;
|
|
$repaymentMonths = $remainingMonths;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
AND is_paid = 0
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
|
|
$rows = build_loan_schedule_rows(
|
|
(float)$loan['current_principal_balance'],
|
|
(float)$loan['annual_interest_rate'],
|
|
$newStartDate,
|
|
$graceMonths,
|
|
$repaymentMonths,
|
|
(string)$loan['repayment_method'],
|
|
(int)$loan['payment_day']
|
|
);
|
|
|
|
$maxPaidCycleStmt = $pdo->prepare("
|
|
SELECT COALESCE(MAX(cycle_no), 0) AS max_cycle
|
|
FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
AND is_paid = 1
|
|
");
|
|
$maxPaidCycleStmt->execute([$loanId]);
|
|
$maxPaidCycle = (int)($maxPaidCycleStmt->fetch()['max_cycle'] ?? 0);
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO loan_schedules
|
|
(
|
|
loan_id, cycle_no, due_date,
|
|
opening_principal, scheduled_principal, scheduled_interest, scheduled_total,
|
|
closing_principal, payment_phase, is_paid, paid_at
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NULL)
|
|
");
|
|
|
|
foreach ($rows as $row) {
|
|
$stmt->execute([
|
|
$loanId,
|
|
$maxPaidCycle + (int)$row['cycle_no'],
|
|
$row['due_date'],
|
|
$row['opening_principal'],
|
|
$row['scheduled_principal'],
|
|
$row['scheduled_interest'],
|
|
$row['scheduled_total'],
|
|
$row['closing_principal'],
|
|
$row['payment_phase'],
|
|
]);
|
|
}
|
|
|
|
$pdo->commit();
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function prepay_loan(
|
|
int $userId,
|
|
int $loanId,
|
|
?int $accountId,
|
|
string $paymentDate,
|
|
float $principalAmount,
|
|
float $interestAmount = 0.0,
|
|
float $feeAmount = 0.0,
|
|
?string $description = null
|
|
): void {
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if (!$loan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
$principalAmount = round($principalAmount, 2);
|
|
$interestAmount = round($interestAmount, 2);
|
|
$feeAmount = round($feeAmount, 2);
|
|
|
|
if ($principalAmount <= 0 && $interestAmount <= 0 && $feeAmount <= 0) {
|
|
throw new RuntimeException('중도상환 금액이 0보다 커야 합니다.');
|
|
}
|
|
|
|
if ($principalAmount > (float)$loan['current_principal_balance']) {
|
|
throw new RuntimeException('중도상환 원금이 남은 원금을 초과합니다.');
|
|
}
|
|
|
|
$totalAmount = round($principalAmount + $interestAmount + $feeAmount, 2);
|
|
|
|
$stmt = $pdo->prepare("
|
|
INSERT INTO loan_payments
|
|
(
|
|
user_id, loan_id, loan_schedule_id, account_id,
|
|
payment_date, principal_amount, interest_amount, fee_amount,
|
|
total_amount, payment_type, is_auto_generated, description
|
|
)
|
|
VALUES (?, ?, NULL, ?, ?, ?, ?, ?, ?, 'prepayment', 0, ?)
|
|
");
|
|
$stmt->execute([
|
|
$userId,
|
|
$loanId,
|
|
$accountId ?: null,
|
|
$paymentDate,
|
|
$principalAmount,
|
|
$interestAmount,
|
|
$feeAmount,
|
|
$totalAmount,
|
|
$description,
|
|
]);
|
|
|
|
if ($principalAmount > 0) {
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loans
|
|
SET current_principal_balance = GREATEST(current_principal_balance - ?, 0)
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$principalAmount, $loanId]);
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT current_principal_balance
|
|
FROM loans
|
|
WHERE id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
$updatedLoan = $stmt->fetch();
|
|
|
|
if ((float)$updatedLoan['current_principal_balance'] <= 0.009) {
|
|
$stmt = $pdo->prepare("UPDATE loans SET current_principal_balance = 0, status = 'closed' WHERE id = ?");
|
|
$stmt->execute([$loanId]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
AND is_paid = 0
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
} else {
|
|
$pdo->commit();
|
|
rebuild_remaining_loan_schedules($loanId);
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
}
|
|
|
|
$pdo->commit();
|
|
|
|
if (!empty($accountId)) {
|
|
recalculate_account_balance((int)$accountId);
|
|
}
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function delete_loan_with_history(int $userId, int $loanId): void
|
|
{
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
$loan = $stmt->fetch();
|
|
|
|
if (!$loan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM transactions
|
|
WHERE user_id = ?
|
|
AND source_loan_id = ?
|
|
");
|
|
$stmt->execute([$userId, $loanId]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
|
|
$pdo->commit();
|
|
|
|
if (!empty($loan['account_id'])) {
|
|
recalculate_account_balance((int)$loan['account_id']);
|
|
}
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
function update_loan_and_rebuild_full_history(int $userId, int $loanId, array $data): void
|
|
{
|
|
$pdo = db();
|
|
$pdo->beginTransaction();
|
|
|
|
try {
|
|
$stmt = $pdo->prepare("
|
|
SELECT *
|
|
FROM loans
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([$loanId, $userId]);
|
|
$oldLoan = $stmt->fetch();
|
|
|
|
if (!$oldLoan) {
|
|
throw new RuntimeException('대출 정보를 찾을 수 없습니다.');
|
|
}
|
|
|
|
if (!empty($data['create_full_history']) && empty($data['account_id'])) {
|
|
throw new RuntimeException('완전 반영 방식을 사용할 때는 입출금 계좌를 선택하세요.');
|
|
}
|
|
|
|
$oldAccountId = !empty($oldLoan['account_id']) ? (int)$oldLoan['account_id'] : 0;
|
|
$newAccountId = !empty($data['account_id']) ? (int)$data['account_id'] : 0;
|
|
|
|
// 기존 자동 생성 거래/납부/스케줄 제거
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM transactions
|
|
WHERE user_id = ?
|
|
AND source_loan_id = ?
|
|
");
|
|
$stmt->execute([$userId, $loanId]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loan_payments
|
|
WHERE loan_id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
|
|
$stmt = $pdo->prepare("
|
|
DELETE FROM loan_schedules
|
|
WHERE loan_id = ?
|
|
");
|
|
$stmt->execute([$loanId]);
|
|
|
|
// 대출 기본정보 수정
|
|
$stmt = $pdo->prepare("
|
|
UPDATE loans
|
|
SET
|
|
account_id = ?,
|
|
loan_name = ?,
|
|
lender_name = ?,
|
|
principal_amount = ?,
|
|
annual_interest_rate = ?,
|
|
start_date = ?,
|
|
maturity_date = NULL,
|
|
grace_period_months = ?,
|
|
repayment_months = ?,
|
|
repayment_method = ?,
|
|
payment_day = ?,
|
|
current_principal_balance = ?,
|
|
status = 'active',
|
|
description = ?,
|
|
updated_at = NOW()
|
|
WHERE id = ?
|
|
AND user_id = ?
|
|
");
|
|
$stmt->execute([
|
|
$newAccountId ?: null,
|
|
$data['loan_name'],
|
|
$data['lender_name'] ?: null,
|
|
$data['principal_amount'],
|
|
$data['annual_interest_rate'],
|
|
$data['start_date'],
|
|
$data['grace_period_months'],
|
|
$data['repayment_months'],
|
|
$data['repayment_method'],
|
|
$data['payment_day'],
|
|
$data['principal_amount'],
|
|
$data['description'] ?: null,
|
|
$loanId,
|
|
$userId
|
|
]);
|
|
|
|
// 새 스케줄 생성
|
|
create_loan_schedule_only(
|
|
$loanId,
|
|
(float)$data['principal_amount'],
|
|
(float)$data['annual_interest_rate'],
|
|
(string)$data['start_date'],
|
|
(int)$data['grace_period_months'],
|
|
(int)$data['repayment_months'],
|
|
(string)$data['repayment_method'],
|
|
(int)$data['payment_day']
|
|
);
|
|
|
|
$pdo->commit();
|
|
|
|
if (!empty($data['create_full_history'])) {
|
|
backfill_loan_transactions_and_mark_paid(
|
|
$userId,
|
|
$loanId,
|
|
$newAccountId ?: null,
|
|
$data['today_date'] ?? date('Y-m-d')
|
|
);
|
|
}
|
|
|
|
if ($oldAccountId > 0) {
|
|
recalculate_account_balance($oldAccountId);
|
|
}
|
|
if ($newAccountId > 0 && $newAccountId !== $oldAccountId) {
|
|
recalculate_account_balance($newAccountId);
|
|
}
|
|
} catch (Throwable $e) {
|
|
if ($pdo->inTransaction()) {
|
|
$pdo->rollBack();
|
|
}
|
|
throw $e;
|
|
}
|
|
} |