172 lines
4.2 KiB
PHP
172 lines
4.2 KiB
PHP
<?php
|
|
|
|
require_once __DIR__ . '/db.php';
|
|
|
|
function normalize_merchant_text(string $text): string
|
|
{
|
|
$text = trim($text);
|
|
$text = mb_strtolower($text, 'UTF-8');
|
|
|
|
$removeWords = [
|
|
'주식회사',
|
|
'(주)',
|
|
'㈜',
|
|
'유한회사',
|
|
'영농조합법인',
|
|
'농업회사법인',
|
|
'사단법인',
|
|
'재단법인',
|
|
'법인',
|
|
'카드',
|
|
'체크',
|
|
'승인',
|
|
'취소',
|
|
'일시불',
|
|
'할부',
|
|
'누적',
|
|
'잔액',
|
|
];
|
|
|
|
foreach ($removeWords as $word) {
|
|
$text = str_replace($word, '', $text);
|
|
}
|
|
|
|
$replace = [
|
|
' ' => '',
|
|
'-' => '',
|
|
'_' => '',
|
|
'(' => '',
|
|
')' => '',
|
|
'[' => '',
|
|
']' => '',
|
|
'{' => '',
|
|
'}' => '',
|
|
'.' => '',
|
|
',' => '',
|
|
':' => '',
|
|
';' => '',
|
|
'/' => '',
|
|
'\\' => '',
|
|
'|' => '',
|
|
'\'' => '',
|
|
'"' => '',
|
|
"\t" => '',
|
|
"\n" => '',
|
|
"\r" => '',
|
|
];
|
|
|
|
$text = strtr($text, $replace);
|
|
|
|
// 금액/시간/승인번호처럼 추천에 방해되는 숫자 덩어리 완화
|
|
$text = preg_replace('/\d{4,}/u', '', $text);
|
|
|
|
return trim((string)$text);
|
|
}
|
|
|
|
function map_transaction_type_to_category_type(string $transactionType): string
|
|
{
|
|
if ($transactionType === 'income') {
|
|
return 'income';
|
|
}
|
|
|
|
if ($transactionType === 'expense') {
|
|
return 'expense';
|
|
}
|
|
|
|
return 'transfer';
|
|
}
|
|
|
|
function merchant_starts_with(string $haystack, string $needle): bool
|
|
{
|
|
if ($needle === '') {
|
|
return false;
|
|
}
|
|
|
|
return mb_substr($haystack, 0, mb_strlen($needle, 'UTF-8'), 'UTF-8') === $needle;
|
|
}
|
|
|
|
function suggest_category_from_merchant(int $userId, string $merchantText, string $transactionType): ?array
|
|
{
|
|
$pdo = db();
|
|
|
|
$merchantText = trim($merchantText);
|
|
if ($merchantText === '') {
|
|
return null;
|
|
}
|
|
|
|
$normalized = normalize_merchant_text($merchantText);
|
|
if ($normalized === '') {
|
|
return null;
|
|
}
|
|
|
|
$categoryType = map_transaction_type_to_category_type($transactionType);
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
r.id,
|
|
r.pattern_text,
|
|
r.normalized_pattern,
|
|
r.match_type,
|
|
r.priority,
|
|
r.confidence,
|
|
c.id AS category_id,
|
|
c.name AS category_name,
|
|
c.category_type
|
|
FROM merchant_pattern_rules r
|
|
JOIN categories c
|
|
ON c.id = r.category_id
|
|
AND c.user_id = r.user_id
|
|
WHERE r.user_id = ?
|
|
AND r.is_active = 1
|
|
AND c.is_active = 1
|
|
AND c.category_type = ?
|
|
AND r.normalized_pattern IS NOT NULL
|
|
AND r.normalized_pattern <> ''
|
|
ORDER BY
|
|
CASE r.match_type
|
|
WHEN 'exact' THEN 1
|
|
WHEN 'prefix' THEN 2
|
|
ELSE 3
|
|
END ASC,
|
|
r.priority ASC,
|
|
CHAR_LENGTH(r.normalized_pattern) DESC,
|
|
r.id ASC
|
|
");
|
|
$stmt->execute([$userId, $categoryType]);
|
|
$rows = $stmt->fetchAll();
|
|
|
|
foreach ($rows as $row) {
|
|
$pattern = (string)$row['normalized_pattern'];
|
|
|
|
if ($pattern === '') {
|
|
continue;
|
|
}
|
|
|
|
$matched = false;
|
|
|
|
if ($row['match_type'] === 'exact') {
|
|
$matched = ($normalized === $pattern);
|
|
} elseif ($row['match_type'] === 'prefix') {
|
|
$matched = merchant_starts_with($normalized, $pattern);
|
|
} else {
|
|
$matched = mb_strpos($normalized, $pattern, 0, 'UTF-8') !== false;
|
|
}
|
|
|
|
if ($matched) {
|
|
return [
|
|
'rule_id' => (int)$row['id'],
|
|
'pattern_text' => (string)$row['pattern_text'],
|
|
'keyword' => (string)$row['pattern_text'],
|
|
'match_type' => (string)$row['match_type'],
|
|
'priority' => (int)$row['priority'],
|
|
'confidence' => (float)$row['confidence'],
|
|
'category_id' => (int)$row['category_id'],
|
|
'category_name' => (string)$row['category_name'],
|
|
'category_type' => (string)$row['category_type'],
|
|
'normalized_input' => $normalized,
|
|
];
|
|
}
|
|
}
|
|
|
|
return null;
|
|
} |