2602 lines
120 KiB
Python
2602 lines
120 KiB
Python
#!/usr/bin/env python3
|
|
from __future__ import annotations
|
|
|
|
import json
|
|
import re
|
|
import sqlite3
|
|
from collections import defaultdict
|
|
from datetime import datetime, timedelta
|
|
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
|
|
from pathlib import Path
|
|
from urllib.parse import parse_qs, urlparse
|
|
from xml.etree import ElementTree as ET
|
|
from zipfile import ZipFile
|
|
|
|
|
|
BASE_DIR = Path("/home/hyein/project")
|
|
DEFAULT_XLSX_PATH = BASE_DIR / "PTC(2023-2026.02).xlsx"
|
|
XLSX_SOURCE_CONFIG_PATH = BASE_DIR / "server" / "ptc_source_path.txt"
|
|
METHOD_XLSX_PATH = BASE_DIR / "PTC공법.xlsx"
|
|
DB_PATH = BASE_DIR / "db" / "ptc_local.sqlite3"
|
|
FRONTEND_INDEX_PATH = BASE_DIR / "PTC" / "index.html"
|
|
FRONTEND_DASHBOARD_PREVIEW_PATH = BASE_DIR / "PTC" / "dashboard_preview.html"
|
|
FRONTEND_CACHE: dict[str, str | int] = {"mtime_ns": -1, "html": ""}
|
|
FRONTEND_PREVIEW_CACHE: dict[str, str | int] = {"mtime_ns": -1, "html": ""}
|
|
NS = {"a": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
|
|
PROJECT_TYPE_OPTIONS = ["관리", "영업", "시공", "설계", "개발", "기술", "교휴", "기타"]
|
|
METHOD_FAMILY_OPTIONS = ["복합말뚝", "합성형라멘", "강관거더", "가시설"]
|
|
METHOD_FAMILY_MAP = {
|
|
"HCP": "복합말뚝",
|
|
"CFT": "복합말뚝",
|
|
"DDH": "복합말뚝",
|
|
"GC": "합성형라멘",
|
|
"PB": "합성형라멘",
|
|
"IT": "합성형라멘",
|
|
"DR": "합성형라멘",
|
|
"SGC": "합성형라멘",
|
|
"RSD": "강관거더",
|
|
"RSW": "가시설",
|
|
}
|
|
METHOD_OPTIONS = ["HCP", "CFT", "DDH", "GC", "PB", "IT", "DR", "SGC", "RSD", "RSW"]
|
|
ACCOUNT_MASTER = {
|
|
"711": {"project_type": "시공", "category": "자재비", "name": "강관"},
|
|
"712": {"project_type": "시공", "category": "자재비", "name": "PHC"},
|
|
"713": {"project_type": "시공", "category": "자재비", "name": "결합구"},
|
|
"714": {"project_type": "시공", "category": "자재비", "name": "부자재"},
|
|
"715": {"project_type": "시공", "category": "자재비", "name": "주자재"},
|
|
"721": {"project_type": "시공", "category": "외주비", "name": "항타장비"},
|
|
"722": {"project_type": "시공", "category": "외주비", "name": "두부보강"},
|
|
"723": {"project_type": "시공", "category": "외주비", "name": "시험용역"},
|
|
"725": {"project_type": "시공", "category": "외주비", "name": "외주비 등"},
|
|
"726": {"project_type": "시공", "category": "외주비", "name": "제작"},
|
|
"727": {"project_type": "시공", "category": "외주비", "name": "인장"},
|
|
"728": {"project_type": "시공", "category": "외주비", "name": "가설"},
|
|
"729": {"project_type": "시공", "category": "외주비", "name": "철근가공"},
|
|
"730": {"project_type": "시공", "category": "외주비", "name": "공장제작"},
|
|
"724": {"project_type": "시공", "category": "인건비", "name": "노무비"},
|
|
"513": {"project_type": "시공", "category": "인건비", "name": "시공 퇴직금"},
|
|
"731": {"project_type": "시공", "category": "장비비", "name": "장비비"},
|
|
"733": {"project_type": "시공", "category": "운반비", "name": "운반비"},
|
|
"732": {"project_type": "시공", "category": "운반비", "name": "유류비"},
|
|
"744": {"project_type": "시공", "category": "안전관리비", "name": "안전관리비(현장)"},
|
|
"734": {"project_type": "시공", "category": "경비", "name": "주재비"},
|
|
"735": {"project_type": "시공", "category": "경비", "name": "기타경비"},
|
|
"736": {"project_type": "시공", "category": "경비", "name": "복리후생비"},
|
|
"737": {"project_type": "시공", "category": "경비", "name": "여비교통비"},
|
|
"738": {"project_type": "시공", "category": "경비", "name": "지급임차료"},
|
|
"739": {"project_type": "시공", "category": "경비", "name": "보증수수료"},
|
|
"740": {"project_type": "시공", "category": "경비", "name": "소모자재비"},
|
|
"741": {"project_type": "시공", "category": "경비", "name": "잡자재대"},
|
|
"742": {"project_type": "시공", "category": "경비", "name": "가스수도료"},
|
|
"743": {"project_type": "시공", "category": "경비", "name": "수선비"},
|
|
"811": {"project_type": "관리", "category": "일반운영비", "name": "복리후생비"},
|
|
"812": {"project_type": "관리", "category": "일반운영비", "name": "여비교통비"},
|
|
"813": {"project_type": "관리", "category": "일반운영비", "name": "접대비"},
|
|
"814": {"project_type": "관리", "category": "일반운영비", "name": "통신비"},
|
|
"822": {"project_type": "관리", "category": "일반운영비", "name": "차량유지비"},
|
|
"823": {"project_type": "관리", "category": "일반운영비", "name": "연구개발비"},
|
|
"825": {"project_type": "관리", "category": "일반운영비", "name": "교육훈련비"},
|
|
"826": {"project_type": "관리", "category": "일반운영비", "name": "도서인쇄비"},
|
|
"827": {"project_type": "관리", "category": "일반운영비", "name": "광고선전비"},
|
|
"829": {"project_type": "관리", "category": "일반운영비", "name": "사무용품비"},
|
|
"830": {"project_type": "관리", "category": "일반운영비", "name": "소모품비"},
|
|
"843": {"project_type": "관리", "category": "일반운영비", "name": "부서비"},
|
|
"817": {"project_type": "관리", "category": "법정,의무", "name": "세금과공과금"},
|
|
"819": {"project_type": "관리", "category": "법정,의무", "name": "지급임차료"},
|
|
"821": {"project_type": "관리", "category": "법정,의무", "name": "보험료"},
|
|
"831": {"project_type": "관리", "category": "외부전문,전략", "name": "지급수수료"},
|
|
"849": {"project_type": "관리", "category": "외부전문,전략", "name": "지원서비스"},
|
|
"850": {"project_type": "관리", "category": "안전관리비", "name": "안전관리비(본사)"},
|
|
"501": {"project_type": "관리", "category": "인건비", "name": "관리 임금"},
|
|
"502": {"project_type": "관리", "category": "인건비", "name": "공무 임금"},
|
|
"503": {"project_type": "관리", "category": "인건비", "name": "시공 임금"},
|
|
"504": {"project_type": "관리", "category": "인건비", "name": "설계 임금"},
|
|
"505": {"project_type": "관리", "category": "인건비", "name": "지원 임금"},
|
|
"511": {"project_type": "관리", "category": "인건비", "name": "관리 퇴직금"},
|
|
"512": {"project_type": "관리", "category": "인건비", "name": "공무 퇴직금"},
|
|
"514": {"project_type": "관리", "category": "인건비", "name": "설계 퇴직금"},
|
|
"515": {"project_type": "관리", "category": "인건비", "name": "지원 퇴직금"},
|
|
"521": {"project_type": "관리", "category": "인건비", "name": "소득세"},
|
|
"522": {"project_type": "관리", "category": "인건비", "name": "주민세"},
|
|
"523": {"project_type": "관리", "category": "인건비", "name": "4대보험"},
|
|
"524": {"project_type": "관리", "category": "인건비", "name": "퇴직급여"},
|
|
}
|
|
ALLOWED_ACCOUNT_CODES_BY_PROJECT_TYPE = {
|
|
project_type: {code for code, item in ACCOUNT_MASTER.items() if item["project_type"] == project_type}
|
|
for project_type in ("시공", "관리")
|
|
}
|
|
SUGGESTED_ACCOUNT_REMAP = {
|
|
("시공", "811"): "736",
|
|
("시공", "812"): "737",
|
|
("시공", "819"): "738",
|
|
("시공", "850"): "744",
|
|
("관리", "736"): "811",
|
|
("관리", "737"): "812",
|
|
("관리", "738"): "819",
|
|
("관리", "744"): "850",
|
|
}
|
|
INCOME_ACCOUNT_CATEGORY_MAP = {
|
|
"401": "공사수입",
|
|
"402": "용역수입",
|
|
"403": "기타수입",
|
|
"110": "당좌자산",
|
|
}
|
|
INCOME_ACCOUNT_NAME_MAP = {
|
|
"401": "공사수입",
|
|
"402": "용역수입",
|
|
"403": "기타수입",
|
|
"110": "받을어음",
|
|
}
|
|
SPECIAL_ACCOUNT_MASTER = {
|
|
"901": {"section": "영업외 수지", "group": "영업외수익", "category": "이자수입", "name": "이자수입"},
|
|
"903": {"section": "영업외 수지", "group": "영업외수익", "category": "잡이익", "name": "잡이익"},
|
|
"904": {"section": "영업외 수지", "group": "영업외수익", "category": "배당수익", "name": "배당수익"},
|
|
"961": {"section": "영업외 수지", "group": "영업외비용", "category": "이자비용", "name": "이자비용"},
|
|
"962": {"section": "영업외 수지", "group": "영업외비용", "category": "잡손실", "name": "잡손실"},
|
|
"963": {"section": "영업외 수지", "group": "영업외비용", "category": "가지급금", "name": "가지급금"},
|
|
"999": {"section": "영업외 수지", "group": "영업외비용", "category": "법인세등", "name": "법인세등"},
|
|
"103": {"section": "자산", "group": "당좌자산", "category": "보통예금", "name": "보통예금"},
|
|
"124": {"section": "자산", "group": "당좌자산", "category": "매도가능증권", "name": "매도가능증권"},
|
|
"135": {"section": "자산", "group": "당좌자산", "category": "매입부가세", "name": "매입부가세"},
|
|
"178": {"section": "자산", "group": "투자자산", "category": "회원권", "name": "회원권"},
|
|
"191": {"section": "자산", "group": "투자자산", "category": "출자금", "name": "출자금"},
|
|
"192": {"section": "자산", "group": "투자자산", "category": "임차보증금", "name": "임차보증금"},
|
|
"194": {"section": "자산", "group": "기타비유동자산", "category": "전도금", "name": "전도금"},
|
|
"195": {"section": "자산", "group": "기타비유동자산", "category": "보증금", "name": "보증금"},
|
|
"196": {"section": "자산", "group": "기타비유동자산", "category": "대여금", "name": "대여금"},
|
|
"206": {"section": "자산", "group": "유형자산", "category": "기계장치", "name": "기계장치"},
|
|
"208": {"section": "자산", "group": "유형자산", "category": "차량운반구", "name": "차량운반구"},
|
|
"210": {"section": "자산", "group": "유형자산", "category": "공구기구", "name": "공구기구"},
|
|
"212": {"section": "자산", "group": "유형자산", "category": "비품", "name": "비품"},
|
|
"219": {"section": "자산", "group": "유형자산", "category": "시설장치", "name": "시설장치"},
|
|
"231": {"section": "자산", "group": "무형자산", "category": "영업권", "name": "영업권"},
|
|
"241": {"section": "자산", "group": "무형자산", "category": "사용수익기부자산", "name": "사용수익기부자산"},
|
|
"257": {"section": "부채", "group": "유동부채", "category": "가수금", "name": "가수금"},
|
|
"258": {"section": "부채", "group": "유동부채", "category": "매출부가세", "name": "매출부가세"},
|
|
"259": {"section": "부채", "group": "유동부채", "category": "선수금", "name": "선수금"},
|
|
"260": {"section": "부채", "group": "유동부채", "category": "단기차입금", "name": "단기차입금"},
|
|
"293": {"section": "부채", "group": "비유동부채", "category": "장기차입금", "name": "장기차입금"},
|
|
"294": {"section": "부채", "group": "비유동부채", "category": "임대보증금", "name": "임대보증금"},
|
|
}
|
|
ACCOUNT_STRUCTURE_TEMPLATE = [
|
|
{"section": "수입", "group": "수입", "categories": ["공사수입", "용역수입", "기타수입", "당좌자산"]},
|
|
{"section": "영업외 수지", "group": "영업외수익", "categories": ["이자수입", "잡이익", "배당수익"]},
|
|
{"section": "영업외 수지", "group": "영업외비용", "categories": ["이자비용", "잡손실", "가지급금", "법인세등"]},
|
|
{"section": "자산", "group": "당좌자산", "categories": ["보통예금", "매도가능증권", "매입부가세"]},
|
|
{"section": "자산", "group": "투자자산", "categories": ["회원권", "출자금", "임차보증금"]},
|
|
{"section": "자산", "group": "기타비유동자산", "categories": ["전도금", "보증금", "대여금"]},
|
|
{"section": "자산", "group": "유형자산", "categories": ["기계장치", "차량운반구", "공구기구", "비품", "시설장치"]},
|
|
{"section": "자산", "group": "무형자산", "categories": ["영업권", "사용수익기부자산"]},
|
|
{"section": "부채", "group": "유동부채", "categories": ["가수금", "매출부가세", "선수금", "단기차입금"]},
|
|
{"section": "부채", "group": "비유동부채", "categories": ["장기차입금", "임대보증금"]},
|
|
{"section": "지출", "group": "시공", "categories": ["자재비", "외주비", "인건비", "장비비", "운반비", "안전관리비", "경비"]},
|
|
{"section": "지출", "group": "관리", "categories": ["일반운영비", "법정,의무", "외부전문,전략", "안전관리비", "인건비"]},
|
|
]
|
|
|
|
|
|
def get_xlsx_path() -> Path:
|
|
if XLSX_SOURCE_CONFIG_PATH.exists():
|
|
configured = XLSX_SOURCE_CONFIG_PATH.read_text(encoding="utf-8").strip()
|
|
if configured:
|
|
path = Path(configured).expanduser()
|
|
if not path.is_absolute():
|
|
path = (BASE_DIR / path).resolve()
|
|
return path
|
|
return DEFAULT_XLSX_PATH
|
|
|
|
|
|
def get_frontend_html() -> str:
|
|
if not FRONTEND_INDEX_PATH.exists():
|
|
raise FileNotFoundError("PTC frontend not found")
|
|
|
|
mtime_ns = FRONTEND_INDEX_PATH.stat().st_mtime_ns
|
|
if FRONTEND_CACHE["mtime_ns"] != mtime_ns:
|
|
FRONTEND_CACHE["mtime_ns"] = mtime_ns
|
|
FRONTEND_CACHE["html"] = FRONTEND_INDEX_PATH.read_text(encoding="utf-8")
|
|
return str(FRONTEND_CACHE["html"])
|
|
|
|
|
|
def get_frontend_dashboard_preview_html() -> str:
|
|
if not FRONTEND_DASHBOARD_PREVIEW_PATH.exists():
|
|
raise FileNotFoundError("PTC dashboard preview frontend not found")
|
|
|
|
mtime_ns = FRONTEND_DASHBOARD_PREVIEW_PATH.stat().st_mtime_ns
|
|
if FRONTEND_PREVIEW_CACHE["mtime_ns"] != mtime_ns:
|
|
FRONTEND_PREVIEW_CACHE["mtime_ns"] = mtime_ns
|
|
FRONTEND_PREVIEW_CACHE["html"] = FRONTEND_DASHBOARD_PREVIEW_PATH.read_text(encoding="utf-8")
|
|
return str(FRONTEND_PREVIEW_CACHE["html"])
|
|
|
|
|
|
def normalize_dashboard_family(value: str) -> str:
|
|
text = (value or "").strip()
|
|
if not text or text.upper() == "NULL":
|
|
return "기타/미지정"
|
|
return text
|
|
|
|
|
|
def normalize_dashboard_method(value: str) -> str:
|
|
text = (value or "").strip()
|
|
if not text or text.upper() == "NULL":
|
|
return "공법미지정"
|
|
return text
|
|
|
|
|
|
def infer_project_type_from_code(project_code: str) -> str:
|
|
match = re.match(r"\d{2}-(.+?)-\d+", (project_code or "").strip())
|
|
return match.group(1) if match else ""
|
|
|
|
|
|
def resolve_project_type(project_code: str, raw_project_type: str, master_project_type: str = "") -> str:
|
|
inferred = infer_project_type_from_code(project_code)
|
|
raw = (raw_project_type or "").strip()
|
|
master = (master_project_type or "").strip()
|
|
|
|
# Project codes like "23-설계-13" are more stable than mixed transaction
|
|
# labels, so when the code clearly encodes the type, trust it first.
|
|
if inferred:
|
|
return inferred
|
|
|
|
if master:
|
|
return master
|
|
return raw
|
|
|
|
|
|
def resolve_construction_family(construction_method: str, stored_family: str = "") -> str:
|
|
method = (construction_method or "").strip().upper()
|
|
family = (stored_family or "").strip()
|
|
return METHOD_FAMILY_MAP.get(method, family)
|
|
|
|
|
|
def resolve_account_name(account_code: str, fallback_name: str = "") -> str:
|
|
code = (account_code or "").strip()
|
|
if code in INCOME_ACCOUNT_NAME_MAP:
|
|
return INCOME_ACCOUNT_NAME_MAP[code]
|
|
if code in SPECIAL_ACCOUNT_MASTER:
|
|
return SPECIAL_ACCOUNT_MASTER[code]["name"]
|
|
return ACCOUNT_MASTER.get(code, {}).get("name", fallback_name)
|
|
|
|
|
|
def suggest_account_code(project_type: str, account_code: str, account_name: str) -> str:
|
|
explicit = SUGGESTED_ACCOUNT_REMAP.get((project_type, account_code))
|
|
if explicit:
|
|
return explicit
|
|
normalized_name = (account_name or "").strip()
|
|
for code, item in ACCOUNT_MASTER.items():
|
|
if item["project_type"] == project_type and item["name"] == normalized_name:
|
|
return code
|
|
return ""
|
|
|
|
|
|
def get_category_account_items(section: str, group: str, category: str) -> list[dict]:
|
|
items = []
|
|
if section == "수입":
|
|
for code, target_category in INCOME_ACCOUNT_CATEGORY_MAP.items():
|
|
if target_category == category:
|
|
items.append({"account_code": code, "account_name": resolve_account_name(code, category)})
|
|
return items
|
|
|
|
if section in {"영업외 수지", "자산", "부채"}:
|
|
for code, meta in SPECIAL_ACCOUNT_MASTER.items():
|
|
if meta["section"] == section and meta["group"] == group and meta["category"] == category:
|
|
items.append({"account_code": code, "account_name": meta["name"]})
|
|
return items
|
|
|
|
for code, meta in ACCOUNT_MASTER.items():
|
|
if meta["project_type"] == group and meta["category"] == category:
|
|
items.append({"account_code": code, "account_name": meta["name"]})
|
|
return items
|
|
|
|
|
|
def col_to_num(col: str) -> int:
|
|
value = 0
|
|
for ch in col:
|
|
if ch.isalpha():
|
|
value = value * 26 + ord(ch.upper()) - 64
|
|
return value
|
|
|
|
|
|
def excel_serial_to_date(value: str) -> str:
|
|
if not value:
|
|
return ""
|
|
try:
|
|
number = float(value)
|
|
except ValueError:
|
|
return value
|
|
base = datetime(1899, 12, 30)
|
|
return (base + timedelta(days=number)).strftime("%Y-%m-%d")
|
|
|
|
|
|
def parse_amount(value: str) -> float:
|
|
text = (value or "").strip()
|
|
if not text or text == "-":
|
|
return 0.0
|
|
return float(text.replace(",", ""))
|
|
|
|
|
|
def normalize_transaction_type(in_out: str, account_name: str) -> str:
|
|
if "입" in in_out:
|
|
return "revenue"
|
|
if "출" in in_out:
|
|
if "수입" in account_name or "매출" in account_name:
|
|
return "revenue"
|
|
return "cost_expense"
|
|
return "unknown"
|
|
|
|
|
|
def read_xlsx_rows(path: Path) -> list[dict]:
|
|
with ZipFile(path) as book:
|
|
shared_strings = []
|
|
root = ET.fromstring(book.read("xl/sharedStrings.xml"))
|
|
for si in root.findall("a:si", NS):
|
|
text = "".join(node.text or "" for node in si.iterfind(".//a:t", NS))
|
|
shared_strings.append(text)
|
|
|
|
sheet = ET.fromstring(book.read("xl/worksheets/sheet1.xml"))
|
|
rows = []
|
|
for row in sheet.find("a:sheetData", NS).findall("a:row", NS):
|
|
values = defaultdict(str)
|
|
for cell in row.findall("a:c", NS):
|
|
ref = cell.attrib.get("r", "")
|
|
match = re.match(r"([A-Z]+)(\d+)", ref)
|
|
col = col_to_num(match.group(1)) if match else None
|
|
node = cell.find("a:v", NS)
|
|
if node is None:
|
|
value = ""
|
|
else:
|
|
value = node.text or ""
|
|
if cell.attrib.get("t") == "s":
|
|
value = shared_strings[int(value)]
|
|
values[col] = value
|
|
width = max(values) if values else 0
|
|
rows.append([values[i] for i in range(1, width + 1)])
|
|
|
|
if not rows:
|
|
return []
|
|
|
|
headers = rows[0]
|
|
data_rows = rows[1:]
|
|
width = len(headers)
|
|
items = []
|
|
for source_row_no, row in enumerate(data_rows, start=2):
|
|
current = row + [""] * (width - len(row)) if len(row) < width else row[:width]
|
|
payload = dict(zip(headers, current))
|
|
items.append(
|
|
{
|
|
"source_row_no": source_row_no,
|
|
"transaction_date_raw": payload.get("거래일", ""),
|
|
"transaction_date": excel_serial_to_date(payload.get("거래일", "")),
|
|
"in_out": payload.get("입/출금", ""),
|
|
"account_code": payload.get("계정코드", ""),
|
|
"account_name": payload.get("구분", ""),
|
|
"department_name": payload.get("부서", ""),
|
|
"vendor_name": payload.get("거래처", ""),
|
|
"project_code": payload.get("프로젝트코드", ""),
|
|
"project_type": payload.get("프로젝트 구분(안)", ""),
|
|
"project_name": payload.get("프로젝트명", ""),
|
|
"description": payload.get("적요", ""),
|
|
"supply_amount_raw": payload.get("공급가액", ""),
|
|
"vat_amount_raw": payload.get("부가세", ""),
|
|
"total_amount_raw": payload.get("합계금액", ""),
|
|
"remarks": payload.get("비고", ""),
|
|
"supply_amount": parse_amount(payload.get("공급가액", "")),
|
|
"vat_amount": parse_amount(payload.get("부가세", "")),
|
|
"total_amount": parse_amount(payload.get("합계금액", "")),
|
|
"normalized_type": normalize_transaction_type(
|
|
payload.get("입/출금", ""), payload.get("구분", "")
|
|
),
|
|
}
|
|
)
|
|
return items
|
|
|
|
|
|
def normalize_name(value: str) -> str:
|
|
return re.sub(r"\s+|,|\(|\)|~|-", "", (value or "").strip().lower())
|
|
|
|
|
|
def read_method_rows(path: Path) -> list[dict]:
|
|
if not path.exists():
|
|
return []
|
|
with ZipFile(path) as book:
|
|
shared_strings = []
|
|
root = ET.fromstring(book.read("xl/sharedStrings.xml"))
|
|
for si in root.findall("a:si", NS):
|
|
text = "".join(node.text or "" for node in si.iterfind(".//a:t", NS))
|
|
shared_strings.append(text)
|
|
|
|
sheet = ET.fromstring(book.read("xl/worksheets/sheet1.xml"))
|
|
rows = []
|
|
for row in sheet.find("a:sheetData", NS).findall("a:row", NS)[1:]:
|
|
values = defaultdict(str)
|
|
for cell in row.findall("a:c", NS):
|
|
ref = cell.attrib.get("r", "")
|
|
match = re.match(r"([A-Z]+)(\d+)", ref)
|
|
col = col_to_num(match.group(1)) if match else None
|
|
node = cell.find("a:v", NS)
|
|
if node is None:
|
|
value = ""
|
|
else:
|
|
value = node.text or ""
|
|
if cell.attrib.get("t") == "s":
|
|
value = shared_strings[int(value)]
|
|
values[col] = value
|
|
rows.append(
|
|
{
|
|
"project_code": values[1].strip(),
|
|
"project_name": values[2].strip(),
|
|
"construction_method": values[3].strip().upper(),
|
|
}
|
|
)
|
|
return rows
|
|
|
|
|
|
def get_conn() -> sqlite3.Connection:
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
return conn
|
|
|
|
|
|
def init_db() -> None:
|
|
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
|
|
conn = get_conn()
|
|
cur = conn.cursor()
|
|
cur.execute(
|
|
"""
|
|
create table if not exists ptc_transactions (
|
|
id integer primary key autoincrement,
|
|
source_row_no integer not null,
|
|
transaction_date_raw text,
|
|
transaction_date text,
|
|
in_out text,
|
|
account_code text,
|
|
account_name text,
|
|
account_code_final text,
|
|
account_name_final text,
|
|
department_name text,
|
|
vendor_name text,
|
|
project_code text,
|
|
project_type text,
|
|
project_name text,
|
|
description text,
|
|
supply_amount_raw text,
|
|
vat_amount_raw text,
|
|
total_amount_raw text,
|
|
remarks text,
|
|
supply_amount real not null default 0,
|
|
vat_amount real not null default 0,
|
|
total_amount real not null default 0,
|
|
normalized_type text,
|
|
imported_at text not null
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists meta (
|
|
key text primary key,
|
|
value text not null
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists project_master (
|
|
project_code text primary key,
|
|
project_name text,
|
|
project_type text,
|
|
construction_family text,
|
|
construction_method text,
|
|
start_date text,
|
|
end_date text,
|
|
note text,
|
|
updated_at text not null
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists project_budget_lines (
|
|
project_code text not null,
|
|
section text not null,
|
|
group_name text not null,
|
|
category text not null,
|
|
budget_amount real not null default 0,
|
|
updated_at text not null,
|
|
primary key (project_code, section, group_name, category)
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists project_progress (
|
|
project_code text primary key,
|
|
progress_rate real not null default 0,
|
|
contract_pile_count real not null default 0,
|
|
constructed_pile_count real not null default 0,
|
|
updated_at text not null
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists project_budget_account_lines (
|
|
project_code text not null,
|
|
section text not null,
|
|
group_name text not null,
|
|
category text not null,
|
|
account_code text not null,
|
|
account_name text,
|
|
budget_amount real not null default 0,
|
|
updated_at text not null,
|
|
primary key (project_code, section, group_name, category, account_code)
|
|
)
|
|
"""
|
|
)
|
|
cur.execute(
|
|
"""
|
|
create table if not exists project_pile_progress_entries (
|
|
id integer primary key autoincrement,
|
|
project_code text not null,
|
|
work_date text not null,
|
|
start_date text not null,
|
|
end_date text,
|
|
pile_count real not null default 0,
|
|
note text,
|
|
sort_order integer not null default 0,
|
|
updated_at text not null
|
|
)
|
|
"""
|
|
)
|
|
cur.execute("create index if not exists idx_ptc_transactions_project_code on ptc_transactions(project_code)")
|
|
cur.execute("create index if not exists idx_ptc_transactions_project_code_date on ptc_transactions(project_code, transaction_date desc, source_row_no desc)")
|
|
cur.execute("create index if not exists idx_ptc_transactions_project_code_account on ptc_transactions(project_code, account_code_final)")
|
|
cur.execute("create index if not exists idx_ptc_transactions_project_code_in_out on ptc_transactions(project_code, in_out)")
|
|
cur.execute("create index if not exists idx_ptc_transactions_vendor_name on ptc_transactions(vendor_name)")
|
|
cur.execute("create index if not exists idx_ptc_transactions_account_code_final on ptc_transactions(account_code_final)")
|
|
cur.execute("create index if not exists idx_project_pile_progress_entries_project_code on project_pile_progress_entries(project_code)")
|
|
cur.execute("create index if not exists idx_project_budget_lines_project_code on project_budget_lines(project_code)")
|
|
cur.execute("create index if not exists idx_project_budget_account_lines_project_code on project_budget_account_lines(project_code)")
|
|
existing_cols = [row["name"] for row in cur.execute("pragma table_info(project_master)").fetchall()]
|
|
if "construction_family" not in existing_cols:
|
|
cur.execute("alter table project_master add column construction_family text")
|
|
if "start_date" not in existing_cols:
|
|
cur.execute("alter table project_master add column start_date text")
|
|
if "end_date" not in existing_cols:
|
|
cur.execute("alter table project_master add column end_date text")
|
|
progress_cols = [row["name"] for row in cur.execute("pragma table_info(project_progress)").fetchall()]
|
|
if "contract_pile_count" not in progress_cols:
|
|
cur.execute("alter table project_progress add column contract_pile_count real not null default 0")
|
|
if "constructed_pile_count" not in progress_cols:
|
|
cur.execute("alter table project_progress add column constructed_pile_count real not null default 0")
|
|
pile_progress_cols = [row["name"] for row in cur.execute("pragma table_info(project_pile_progress_entries)").fetchall()]
|
|
if "work_date" not in pile_progress_cols:
|
|
cur.execute("alter table project_pile_progress_entries add column work_date text")
|
|
if "start_date" not in pile_progress_cols and "work_date" in pile_progress_cols:
|
|
cur.execute("alter table project_pile_progress_entries add column start_date text")
|
|
cur.execute("update project_pile_progress_entries set start_date = coalesce(nullif(start_date, ''), work_date)")
|
|
if "end_date" not in pile_progress_cols:
|
|
cur.execute("alter table project_pile_progress_entries add column end_date text")
|
|
cur.execute("update project_pile_progress_entries set work_date = coalesce(nullif(work_date, ''), start_date)")
|
|
cur.execute("update project_pile_progress_entries set end_date = coalesce(nullif(end_date, ''), start_date)")
|
|
txn_cols = [row["name"] for row in cur.execute("pragma table_info(ptc_transactions)").fetchall()]
|
|
if "account_code_final" not in txn_cols:
|
|
cur.execute("alter table ptc_transactions add column account_code_final text")
|
|
if "account_name_final" not in txn_cols:
|
|
cur.execute("alter table ptc_transactions add column account_name_final text")
|
|
cur.execute(
|
|
"""
|
|
update ptc_transactions
|
|
set
|
|
account_code_final = coalesce(nullif(account_code_final, ''), account_code),
|
|
account_name_final = coalesce(nullif(account_name_final, ''), account_name)
|
|
"""
|
|
)
|
|
conn.commit()
|
|
|
|
xlsx_path = get_xlsx_path()
|
|
if not xlsx_path.exists():
|
|
raise FileNotFoundError(f"PTC source xlsx not found: {xlsx_path}")
|
|
|
|
xlsx_source_signature = f"{xlsx_path.resolve()}|{int(xlsx_path.stat().st_mtime)}"
|
|
row = cur.execute("select value from meta where key = 'xlsx_source_signature'").fetchone()
|
|
needs_refresh = row is None or row["value"] != xlsx_source_signature
|
|
if needs_refresh:
|
|
rows = read_xlsx_rows(xlsx_path)
|
|
cur.execute("delete from ptc_transactions")
|
|
cur.executemany(
|
|
"""
|
|
insert into ptc_transactions (
|
|
source_row_no, transaction_date_raw, transaction_date, in_out, account_code,
|
|
account_name, account_code_final, account_name_final, department_name, vendor_name, project_code, project_type,
|
|
project_name, description, supply_amount_raw, vat_amount_raw, total_amount_raw,
|
|
remarks, supply_amount, vat_amount, total_amount, normalized_type, imported_at
|
|
) values (
|
|
:source_row_no, :transaction_date_raw, :transaction_date, :in_out, :account_code,
|
|
:account_name, :account_code, :account_name, :department_name, :vendor_name, :project_code, :project_type,
|
|
:project_name, :description, :supply_amount_raw, :vat_amount_raw, :total_amount_raw,
|
|
:remarks, :supply_amount, :vat_amount, :total_amount, :normalized_type, :imported_at
|
|
)
|
|
""",
|
|
[{**item, "imported_at": datetime.utcnow().isoformat()} for item in rows],
|
|
)
|
|
cur.execute(
|
|
"insert into meta(key, value) values('xlsx_source_signature', ?) "
|
|
"on conflict(key) do update set value = excluded.value",
|
|
(xlsx_source_signature,),
|
|
)
|
|
conn.commit()
|
|
|
|
method_mtime = str(int(METHOD_XLSX_PATH.stat().st_mtime)) if METHOD_XLSX_PATH.exists() else ""
|
|
row = cur.execute("select value from meta where key = 'method_xlsx_mtime'").fetchone()
|
|
needs_method_refresh = METHOD_XLSX_PATH.exists() and (row is None or row["value"] != method_mtime)
|
|
if needs_method_refresh:
|
|
method_rows = read_method_rows(METHOD_XLSX_PATH)
|
|
project_rows = cur.execute(
|
|
"select distinct project_code, project_name, project_type from ptc_transactions where coalesce(project_code,'') <> ''"
|
|
).fetchall()
|
|
by_code = {row["project_code"]: row for row in project_rows}
|
|
by_name = {normalize_name(row["project_name"]): row for row in project_rows if row["project_name"]}
|
|
for item in method_rows:
|
|
target = None
|
|
if item["project_code"] and item["project_code"] in by_code:
|
|
target = by_code[item["project_code"]]
|
|
elif item["project_name"] and normalize_name(item["project_name"]) in by_name:
|
|
target = by_name[normalize_name(item["project_name"])]
|
|
if not target:
|
|
continue
|
|
method = item["construction_method"]
|
|
family = METHOD_FAMILY_MAP.get(method, "")
|
|
updated_at = datetime.now().isoformat()
|
|
cur.execute(
|
|
"""
|
|
insert into project_master (
|
|
project_code, project_name, project_type, construction_family, construction_method, note, updated_at
|
|
) values (?, ?, ?, ?, ?, ?, ?)
|
|
on conflict(project_code) do update set
|
|
project_name = coalesce(project_master.project_name, excluded.project_name),
|
|
project_type = coalesce(project_master.project_type, excluded.project_type),
|
|
construction_family = excluded.construction_family,
|
|
construction_method = excluded.construction_method,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(
|
|
target["project_code"],
|
|
target["project_name"],
|
|
resolve_project_type(target["project_code"], target["project_type"]),
|
|
family,
|
|
method,
|
|
"",
|
|
updated_at,
|
|
),
|
|
)
|
|
cur.execute(
|
|
"insert into meta(key, value) values('method_xlsx_mtime', ?) "
|
|
"on conflict(key) do update set value = excluded.value",
|
|
(method_mtime,),
|
|
)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def fetch_project_master(conn: sqlite3.Connection, project_code: str) -> dict | None:
|
|
row = conn.execute(
|
|
"""
|
|
select project_code, project_name, project_type, construction_family, construction_method, start_date, end_date, note, updated_at
|
|
from project_master
|
|
where project_code = ?
|
|
""",
|
|
(project_code,),
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def fetch_project_defaults(conn: sqlite3.Connection, project_code: str) -> dict:
|
|
row = conn.execute(
|
|
"""
|
|
select project_code, max(project_name) as project_name, max(project_type) as project_type
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
group by project_code
|
|
""",
|
|
(project_code,),
|
|
).fetchone()
|
|
return dict(row) if row else {"project_code": project_code, "project_name": "", "project_type": ""}
|
|
|
|
|
|
def get_project_account_issues(conn: sqlite3.Connection, project_code: str, resolved_project_type: str) -> list[dict]:
|
|
allowed_codes = ALLOWED_ACCOUNT_CODES_BY_PROJECT_TYPE.get(resolved_project_type)
|
|
if not allowed_codes:
|
|
return []
|
|
|
|
rows = conn.execute(
|
|
"""
|
|
select
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
group by account_code_final, account_name_final
|
|
order by supply_sum desc, account_code_final
|
|
""",
|
|
(project_code,),
|
|
).fetchall()
|
|
|
|
items = []
|
|
for row in rows:
|
|
code = (row["account_code"] or "").strip()
|
|
if not code or code not in ACCOUNT_MASTER or code in allowed_codes:
|
|
continue
|
|
suggested_code = suggest_account_code(resolved_project_type, code, row["account_name"] or "")
|
|
items.append(
|
|
{
|
|
"account_code": code,
|
|
"account_name": row["account_name"] or "",
|
|
"txn_count": row["txn_count"],
|
|
"supply_sum": row["supply_sum"],
|
|
"suggested_code": suggested_code,
|
|
"suggested_name": resolve_account_name(suggested_code, ""),
|
|
"is_invalid": True,
|
|
}
|
|
)
|
|
return items
|
|
|
|
|
|
def build_account_structure_rows(account_rows: list[sqlite3.Row]) -> list[dict]:
|
|
aggregated: dict[tuple[str, str, str], dict] = {}
|
|
extra_rows: list[dict] = []
|
|
category_account_labels = defaultdict(list)
|
|
|
|
for code, category in INCOME_ACCOUNT_CATEGORY_MAP.items():
|
|
category_account_labels[("수입", category)].append(f"{code} {resolve_account_name(code, category)}")
|
|
for code, meta in SPECIAL_ACCOUNT_MASTER.items():
|
|
category_account_labels[(meta["group"], meta["category"])].append(f"{code} {meta['name']}")
|
|
for code, meta in ACCOUNT_MASTER.items():
|
|
category_account_labels[(meta["project_type"], meta["category"])].append(f"{code} {meta['name']}")
|
|
|
|
for row in account_rows:
|
|
code = (row["code"] or "").strip()
|
|
name = (row["name"] or "").strip()
|
|
count = row["count"] or 0
|
|
total = row["total"] or 0
|
|
|
|
if code in INCOME_ACCOUNT_CATEGORY_MAP:
|
|
key = ("수입", "수입", INCOME_ACCOUNT_CATEGORY_MAP[code])
|
|
elif code in SPECIAL_ACCOUNT_MASTER:
|
|
meta = SPECIAL_ACCOUNT_MASTER[code]
|
|
key = (meta["section"], meta["group"], meta["category"])
|
|
elif code in ACCOUNT_MASTER:
|
|
meta = ACCOUNT_MASTER[code]
|
|
key = ("지출", meta["project_type"], meta["category"])
|
|
else:
|
|
extra_rows.append(
|
|
{
|
|
"section": "기타",
|
|
"group": "미분류",
|
|
"category": f"{code} {name}".strip(),
|
|
"account_items": [{"account_code": code, "account_name": name, "actual_amount": total, "budget_amount": 0}],
|
|
"count": count,
|
|
"total": total,
|
|
}
|
|
)
|
|
continue
|
|
|
|
current = aggregated.setdefault(
|
|
key,
|
|
{
|
|
"section": key[0],
|
|
"group": key[1],
|
|
"category": key[2],
|
|
"account_labels": " / ".join(category_account_labels.get((key[1], key[2]), [])),
|
|
"account_items": [
|
|
{**item, "actual_amount": 0, "budget_amount": 0}
|
|
for item in get_category_account_items(key[0], key[1], key[2])
|
|
],
|
|
"count": 0,
|
|
"total": 0,
|
|
},
|
|
)
|
|
current["count"] += count
|
|
current["total"] += total
|
|
for account_item in current["account_items"]:
|
|
if account_item["account_code"] == code:
|
|
account_item["actual_amount"] = total
|
|
break
|
|
|
|
rows = []
|
|
for block in ACCOUNT_STRUCTURE_TEMPLATE:
|
|
for category in block["categories"]:
|
|
key = (block["section"], block["group"], category)
|
|
item = aggregated.get(
|
|
key,
|
|
{
|
|
"section": block["section"],
|
|
"group": block["group"],
|
|
"category": category,
|
|
"account_labels": " / ".join(category_account_labels.get((block["group"], category), [])),
|
|
"account_items": [
|
|
{**item, "actual_amount": 0, "budget_amount": 0}
|
|
for item in get_category_account_items(block["section"], block["group"], category)
|
|
],
|
|
"count": 0,
|
|
"total": 0,
|
|
},
|
|
)
|
|
if "account_labels" not in item:
|
|
item["account_labels"] = " / ".join(category_account_labels.get((block["group"], category), []))
|
|
rows.append(item)
|
|
|
|
rows.extend(extra_rows)
|
|
return rows
|
|
|
|
|
|
def build_budget_analysis(conn: sqlite3.Connection, project_code: str, account_structure_rows: list[dict]) -> dict:
|
|
pile_progress_rows = conn.execute(
|
|
"""
|
|
select
|
|
id,
|
|
coalesce(nullif(start_date, ''), work_date) as start_date,
|
|
coalesce(nullif(end_date, ''), nullif(start_date, ''), work_date) as end_date,
|
|
pile_count,
|
|
note,
|
|
sort_order
|
|
from project_pile_progress_entries
|
|
where project_code = ?
|
|
order by coalesce(nullif(start_date, ''), work_date) asc, sort_order asc, id asc
|
|
""",
|
|
(project_code,),
|
|
).fetchall()
|
|
item_budget_rows = conn.execute(
|
|
"""
|
|
select section, group_name, category, budget_amount
|
|
from project_budget_lines
|
|
where project_code = ?
|
|
""",
|
|
(project_code,),
|
|
).fetchall()
|
|
item_budget_map = {
|
|
(row["section"], row["group_name"], row["category"]): row["budget_amount"] or 0
|
|
for row in item_budget_rows
|
|
}
|
|
budget_rows = conn.execute(
|
|
"""
|
|
select section, group_name, category, account_code, budget_amount
|
|
from project_budget_account_lines
|
|
where project_code = ?
|
|
""",
|
|
(project_code,),
|
|
).fetchall()
|
|
budget_map = {
|
|
(row["section"], row["group_name"], row["category"], row["account_code"]): row["budget_amount"] or 0
|
|
for row in budget_rows
|
|
}
|
|
progress_row = conn.execute(
|
|
"select progress_rate, contract_pile_count, constructed_pile_count from project_progress where project_code = ?",
|
|
(project_code,),
|
|
).fetchone()
|
|
progress_rate = progress_row["progress_rate"] if progress_row else 0
|
|
contract_pile_count = float(progress_row["contract_pile_count"] or 0) if progress_row else 0
|
|
constructed_pile_count = float(progress_row["constructed_pile_count"] or 0) if progress_row else 0
|
|
entry_pile_total = sum(float(row["pile_count"] or 0) for row in pile_progress_rows)
|
|
if pile_progress_rows:
|
|
constructed_pile_count = entry_pile_total
|
|
if contract_pile_count > 0:
|
|
progress_rate = (constructed_pile_count / contract_pile_count) * 100
|
|
|
|
rows = []
|
|
expense_budget_total = 0.0
|
|
expense_actual_total = 0.0
|
|
revenue_budget_total = 0.0
|
|
revenue_actual_total = 0.0
|
|
|
|
for item in account_structure_rows:
|
|
account_items = []
|
|
for account_item in item.get("account_items", []):
|
|
budget_amount_item = float(
|
|
budget_map.get((item["section"], item["group"], item["category"], account_item["account_code"]), 0) or 0
|
|
)
|
|
account_items.append(
|
|
{
|
|
**account_item,
|
|
"budget_amount": budget_amount_item,
|
|
}
|
|
)
|
|
account_budget_total = sum(account_item["budget_amount"] for account_item in account_items)
|
|
budget_amount = float(
|
|
item_budget_map.get((item["section"], item["group"], item["category"]), account_budget_total) or 0
|
|
)
|
|
actual_amount = float(item["total"] or 0)
|
|
execution_rate = (actual_amount / budget_amount * 100) if budget_amount > 0 else 0
|
|
row = {
|
|
**item,
|
|
"account_items": account_items,
|
|
"budget_amount": budget_amount,
|
|
"account_budget_total": account_budget_total,
|
|
"actual_amount": actual_amount,
|
|
"execution_rate": execution_rate,
|
|
}
|
|
rows.append(row)
|
|
if item["section"] == "지출":
|
|
expense_budget_total += budget_amount
|
|
expense_actual_total += actual_amount
|
|
if item["section"] == "수입":
|
|
revenue_budget_total += budget_amount
|
|
revenue_actual_total += actual_amount
|
|
|
|
execution_rate_total = (expense_actual_total / expense_budget_total * 100) if expense_budget_total > 0 else 0
|
|
return {
|
|
"progress_rate": progress_rate,
|
|
"contract_pile_count": contract_pile_count,
|
|
"constructed_pile_count": constructed_pile_count,
|
|
"pile_progress_entries": [dict(row) for row in pile_progress_rows],
|
|
"execution_rate_total": execution_rate_total,
|
|
"expense_budget_total": expense_budget_total,
|
|
"expense_actual_total": expense_actual_total,
|
|
"revenue_budget_total": revenue_budget_total,
|
|
"revenue_actual_total": revenue_actual_total,
|
|
"rows": rows,
|
|
}
|
|
|
|
|
|
def build_where(params: dict[str, list[str]]) -> tuple[str, list]:
|
|
clauses = []
|
|
values = []
|
|
keyword = params.get("keyword", [""])[0].strip().lower()
|
|
project_type = params.get("project_type", ["전체"])[0]
|
|
in_out = params.get("in_out", ["전체"])[0]
|
|
|
|
if keyword:
|
|
like = f"%{keyword}%"
|
|
clauses.append(
|
|
"""
|
|
(
|
|
lower(coalesce(account_code_final, '')) like ?
|
|
or lower(coalesce(account_name_final, '')) like ?
|
|
or lower(coalesce(department_name, '')) like ?
|
|
or lower(coalesce(vendor_name, '')) like ?
|
|
or lower(coalesce(project_code, '')) like ?
|
|
or lower(coalesce(project_type, '')) like ?
|
|
or lower(coalesce(project_name, '')) like ?
|
|
or lower(coalesce(description, '')) like ?
|
|
)
|
|
"""
|
|
)
|
|
values.extend([like] * 8)
|
|
|
|
if project_type and project_type != "전체":
|
|
clauses.append("project_type = ?")
|
|
values.append(project_type)
|
|
|
|
if in_out and in_out != "전체":
|
|
clauses.append("in_out = ?")
|
|
values.append(in_out)
|
|
|
|
where = " where " + " and ".join(clauses) if clauses else ""
|
|
return where, values
|
|
|
|
|
|
def build_project_where(project_code: str, keyword: str = "", in_out: str = "전체") -> tuple[str, list]:
|
|
clauses = ["project_code = ?"]
|
|
values = [project_code]
|
|
|
|
if keyword.strip():
|
|
like = f"%{keyword.strip().lower()}%"
|
|
clauses.append(
|
|
"""
|
|
(
|
|
lower(coalesce(account_code_final, '')) like ?
|
|
or lower(coalesce(account_name_final, '')) like ?
|
|
or lower(coalesce(department_name, '')) like ?
|
|
or lower(coalesce(vendor_name, '')) like ?
|
|
or lower(coalesce(project_name, '')) like ?
|
|
or lower(coalesce(description, '')) like ?
|
|
)
|
|
"""
|
|
)
|
|
values.extend([like] * 6)
|
|
|
|
if in_out and in_out != "전체":
|
|
clauses.append("in_out = ?")
|
|
values.append(in_out)
|
|
|
|
return " where " + " and ".join(clauses), values
|
|
|
|
|
|
def query_summary(conn: sqlite3.Connection, params: dict[str, list[str]]) -> dict:
|
|
where, values = build_where(params)
|
|
row = conn.execute(
|
|
f"""
|
|
select
|
|
count(*) as count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
coalesce(sum(vat_amount), 0) as vat_sum,
|
|
coalesce(sum(total_amount), 0) as total_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{where}
|
|
""",
|
|
values,
|
|
).fetchone()
|
|
|
|
missing_row = conn.execute(
|
|
f"""
|
|
select count(*) as missing_critical
|
|
from ptc_transactions
|
|
{where}
|
|
{" and " if where else " where "}
|
|
(
|
|
coalesce(account_code_final, '') = ''
|
|
or coalesce(account_name_final, '') = ''
|
|
or coalesce(transaction_date, '') = ''
|
|
or coalesce(description, '') = ''
|
|
)
|
|
""",
|
|
values,
|
|
).fetchone()
|
|
|
|
return {
|
|
"count": row["count"],
|
|
"income_count": row["income_count"],
|
|
"expense_count": row["expense_count"],
|
|
"supply_sum": row["supply_sum"],
|
|
"vat_sum": row["vat_sum"],
|
|
"total_sum": row["total_sum"],
|
|
"min_date": row["min_date"] or "",
|
|
"max_date": row["max_date"] or "",
|
|
"missing_critical": missing_row["missing_critical"],
|
|
}
|
|
|
|
|
|
def rows_to_dicts(rows) -> list[dict]:
|
|
return [dict(row) for row in rows]
|
|
|
|
|
|
class Handler(BaseHTTPRequestHandler):
|
|
def _read_json(self) -> dict:
|
|
length = int(self.headers.get("Content-Length", "0"))
|
|
raw = self.rfile.read(length) if length > 0 else b"{}"
|
|
return json.loads(raw.decode("utf-8"))
|
|
|
|
def _send_html(self, status: int, html: str) -> None:
|
|
body = html.encode("utf-8")
|
|
self.send_response(status)
|
|
self.send_header("Content-Type", "text/html; charset=utf-8")
|
|
self.send_header("Content-Length", str(len(body)))
|
|
self.send_header("Access-Control-Allow-Origin", "*")
|
|
self.send_header("Cache-Control", "no-store, no-cache, must-revalidate, max-age=0")
|
|
self.send_header("Pragma", "no-cache")
|
|
self.send_header("Expires", "0")
|
|
self.end_headers()
|
|
self.wfile.write(body)
|
|
|
|
def _send_frontend(self) -> None:
|
|
if not FRONTEND_INDEX_PATH.exists():
|
|
self._send_html(404, "<h1>PTC frontend not found</h1>")
|
|
return
|
|
self._send_html(200, get_frontend_html())
|
|
|
|
def _send_frontend_dashboard_preview(self) -> None:
|
|
if not FRONTEND_DASHBOARD_PREVIEW_PATH.exists():
|
|
self._send_html(404, "<h1>PTC dashboard preview frontend not found</h1>")
|
|
return
|
|
self._send_html(200, get_frontend_dashboard_preview_html())
|
|
|
|
def _send(self, status: int, payload: dict) -> None:
|
|
body = json.dumps(payload, ensure_ascii=False).encode("utf-8")
|
|
self.send_response(status)
|
|
self.send_header("Content-Type", "application/json; charset=utf-8")
|
|
self.send_header("Content-Length", str(len(body)))
|
|
self.send_header("Access-Control-Allow-Origin", "*")
|
|
self.send_header("Access-Control-Allow-Methods", "GET, OPTIONS")
|
|
self.send_header("Access-Control-Allow-Headers", "Content-Type")
|
|
self.end_headers()
|
|
self.wfile.write(body)
|
|
|
|
def do_OPTIONS(self) -> None:
|
|
self._send(200, {"ok": True})
|
|
|
|
def do_POST(self) -> None:
|
|
parsed = urlparse(self.path)
|
|
conn = get_conn()
|
|
try:
|
|
if parsed.path == "/api/project-master/upsert":
|
|
payload = self._read_json()
|
|
project_code = str(payload.get("project_code", "")).strip()
|
|
if not project_code:
|
|
self._send(400, {"ok": False, "message": "project_code is required"})
|
|
return
|
|
|
|
project_name = str(payload.get("project_name", "")).strip()
|
|
project_type = str(payload.get("project_type", "")).strip()
|
|
if project_type and project_type not in PROJECT_TYPE_OPTIONS:
|
|
self._send(400, {"ok": False, "message": "invalid project_type"})
|
|
return
|
|
construction_family = str(payload.get("construction_family", "")).strip()
|
|
construction_method = str(payload.get("construction_method", "")).strip()
|
|
if construction_method and construction_method not in METHOD_OPTIONS:
|
|
self._send(400, {"ok": False, "message": "invalid construction_method"})
|
|
return
|
|
construction_family = resolve_construction_family(construction_method, construction_family)
|
|
start_date = str(payload.get("start_date", "")).strip()
|
|
end_date = str(payload.get("end_date", "")).strip()
|
|
note = str(payload.get("note", "")).strip()
|
|
updated_at = datetime.now().isoformat()
|
|
|
|
conn.execute(
|
|
"""
|
|
insert into project_master (
|
|
project_code, project_name, project_type, construction_family, construction_method, start_date, end_date, note, updated_at
|
|
) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
on conflict(project_code) do update set
|
|
project_name = excluded.project_name,
|
|
project_type = excluded.project_type,
|
|
construction_family = excluded.construction_family,
|
|
construction_method = excluded.construction_method,
|
|
start_date = excluded.start_date,
|
|
end_date = excluded.end_date,
|
|
note = excluded.note,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(project_code, project_name, project_type, construction_family, construction_method, start_date, end_date, note, updated_at),
|
|
)
|
|
conn.commit()
|
|
self._send(200, {"ok": True, "item": fetch_project_master(conn, project_code)})
|
|
return
|
|
|
|
if parsed.path == "/api/project-master/batch-update-method":
|
|
payload = self._read_json()
|
|
project_codes = payload.get("project_codes", [])
|
|
construction_method = str(payload.get("construction_method", "")).strip()
|
|
note = str(payload.get("note", "")).strip()
|
|
|
|
if not isinstance(project_codes, list) or not project_codes:
|
|
self._send(400, {"ok": False, "message": "project_codes is required"})
|
|
return
|
|
if construction_method and construction_method not in METHOD_OPTIONS:
|
|
self._send(400, {"ok": False, "message": "invalid construction_method"})
|
|
return
|
|
|
|
construction_family = resolve_construction_family(construction_method, "")
|
|
updated_at = datetime.now().isoformat()
|
|
updated_items = []
|
|
|
|
for raw_code in project_codes:
|
|
project_code = str(raw_code).strip()
|
|
if not project_code:
|
|
continue
|
|
default_item = fetch_project_defaults(conn, project_code)
|
|
existing = fetch_project_master(conn, project_code) or {}
|
|
project_name = existing.get("project_name") or default_item.get("project_name") or ""
|
|
project_type = resolve_project_type(
|
|
project_code,
|
|
default_item.get("project_type", ""),
|
|
existing.get("project_type", ""),
|
|
)
|
|
merged_note = note if note else (existing.get("note") or "")
|
|
|
|
start_date = existing.get("start_date") or ""
|
|
end_date = existing.get("end_date") or ""
|
|
|
|
conn.execute(
|
|
"""
|
|
insert into project_master (
|
|
project_code, project_name, project_type, construction_family, construction_method, start_date, end_date, note, updated_at
|
|
) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
on conflict(project_code) do update set
|
|
project_name = excluded.project_name,
|
|
project_type = excluded.project_type,
|
|
construction_family = excluded.construction_family,
|
|
construction_method = excluded.construction_method,
|
|
start_date = excluded.start_date,
|
|
end_date = excluded.end_date,
|
|
note = excluded.note,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(
|
|
project_code,
|
|
project_name,
|
|
project_type,
|
|
construction_family,
|
|
construction_method,
|
|
start_date,
|
|
end_date,
|
|
merged_note,
|
|
updated_at,
|
|
),
|
|
)
|
|
updated_items.append(project_code)
|
|
|
|
conn.commit()
|
|
self._send(
|
|
200,
|
|
{
|
|
"ok": True,
|
|
"updated_count": len(updated_items),
|
|
"project_codes": updated_items,
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-account-remap":
|
|
payload = self._read_json()
|
|
project_code = str(payload.get("project_code", "")).strip()
|
|
from_account_code = str(payload.get("from_account_code", "")).strip()
|
|
to_account_code = str(payload.get("to_account_code", "")).strip()
|
|
if not project_code or not from_account_code or not to_account_code:
|
|
self._send(400, {"ok": False, "message": "project_code, from_account_code, to_account_code are required"})
|
|
return
|
|
if to_account_code not in ACCOUNT_MASTER:
|
|
self._send(400, {"ok": False, "message": "invalid to_account_code"})
|
|
return
|
|
|
|
project_default = fetch_project_defaults(conn, project_code)
|
|
project_master = fetch_project_master(conn, project_code) or {}
|
|
resolved_project_type = resolve_project_type(
|
|
project_code,
|
|
project_default.get("project_type", ""),
|
|
project_master.get("project_type", ""),
|
|
)
|
|
allowed_codes = ALLOWED_ACCOUNT_CODES_BY_PROJECT_TYPE.get(resolved_project_type, set())
|
|
if allowed_codes and to_account_code not in allowed_codes:
|
|
self._send(400, {"ok": False, "message": "target account is not allowed for project type"})
|
|
return
|
|
|
|
to_account_name = resolve_account_name(to_account_code, "")
|
|
updated_at = datetime.now().isoformat()
|
|
cur = conn.cursor()
|
|
cur.execute(
|
|
"""
|
|
update ptc_transactions
|
|
set
|
|
account_code_final = ?,
|
|
account_name_final = ?,
|
|
imported_at = imported_at
|
|
where project_code = ?
|
|
and account_code_final = ?
|
|
""",
|
|
(to_account_code, to_account_name, project_code, from_account_code),
|
|
)
|
|
changed_rows = cur.rowcount
|
|
conn.commit()
|
|
self._send(
|
|
200,
|
|
{
|
|
"ok": True,
|
|
"updated_count": changed_rows,
|
|
"project_code": project_code,
|
|
"from_account_code": from_account_code,
|
|
"to_account_code": to_account_code,
|
|
"to_account_name": to_account_name,
|
|
"updated_at": updated_at,
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-account-remap-rows":
|
|
payload = self._read_json()
|
|
project_code = str(payload.get("project_code", "")).strip()
|
|
rows = payload.get("rows", [])
|
|
if not project_code or not isinstance(rows, list):
|
|
self._send(400, {"ok": False, "message": "project_code and rows are required"})
|
|
return
|
|
|
|
project_default = fetch_project_defaults(conn, project_code)
|
|
project_master = fetch_project_master(conn, project_code) or {}
|
|
resolved_project_type = resolve_project_type(
|
|
project_code,
|
|
project_default.get("project_type", ""),
|
|
project_master.get("project_type", ""),
|
|
)
|
|
allowed_codes = ALLOWED_ACCOUNT_CODES_BY_PROJECT_TYPE.get(resolved_project_type, set())
|
|
|
|
cur = conn.cursor()
|
|
updated_count = 0
|
|
for item in rows:
|
|
source_row_no = int(item.get("source_row_no", 0) or 0)
|
|
to_account_code = str(item.get("to_account_code", "")).strip()
|
|
if source_row_no <= 0 or not to_account_code:
|
|
continue
|
|
if to_account_code not in ACCOUNT_MASTER:
|
|
continue
|
|
if allowed_codes and to_account_code not in allowed_codes:
|
|
continue
|
|
to_account_name = resolve_account_name(to_account_code, "")
|
|
cur.execute(
|
|
"""
|
|
update ptc_transactions
|
|
set
|
|
account_code_final = ?,
|
|
account_name_final = ?,
|
|
imported_at = imported_at
|
|
where project_code = ?
|
|
and source_row_no = ?
|
|
""",
|
|
(to_account_code, to_account_name, project_code, source_row_no),
|
|
)
|
|
updated_count += cur.rowcount
|
|
|
|
conn.commit()
|
|
self._send(
|
|
200,
|
|
{
|
|
"ok": True,
|
|
"updated_count": updated_count,
|
|
"project_code": project_code,
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-budget/upsert":
|
|
payload = self._read_json()
|
|
project_code = str(payload.get("project_code", "")).strip()
|
|
item_rows = payload.get("item_rows", [])
|
|
account_rows = payload.get("account_rows", [])
|
|
progress_rate = float(payload.get("progress_rate", 0) or 0)
|
|
contract_pile_count = float(payload.get("contract_pile_count", 0) or 0)
|
|
constructed_pile_count = float(payload.get("constructed_pile_count", 0) or 0)
|
|
if not project_code:
|
|
self._send(400, {"ok": False, "message": "project_code is required"})
|
|
return
|
|
if not isinstance(item_rows, list):
|
|
self._send(400, {"ok": False, "message": "item_rows must be a list"})
|
|
return
|
|
if not isinstance(account_rows, list):
|
|
self._send(400, {"ok": False, "message": "account_rows must be a list"})
|
|
return
|
|
|
|
updated_at = datetime.now().isoformat()
|
|
conn.execute("delete from project_budget_lines where project_code = ?", (project_code,))
|
|
conn.execute("delete from project_budget_account_lines where project_code = ?", (project_code,))
|
|
for item in item_rows:
|
|
section = str(item.get("section", "")).strip()
|
|
group_name = str(item.get("group", "")).strip()
|
|
category = str(item.get("category", "")).strip()
|
|
budget_amount = float(item.get("budget_amount", 0) or 0)
|
|
if not section or not group_name or not category:
|
|
continue
|
|
conn.execute(
|
|
"""
|
|
insert into project_budget_lines (
|
|
project_code, section, group_name, category, budget_amount, updated_at
|
|
) values (?, ?, ?, ?, ?, ?)
|
|
""",
|
|
(project_code, section, group_name, category, budget_amount, updated_at),
|
|
)
|
|
for item in account_rows:
|
|
section = str(item.get("section", "")).strip()
|
|
group_name = str(item.get("group", "")).strip()
|
|
category = str(item.get("category", "")).strip()
|
|
account_code = str(item.get("account_code", "")).strip()
|
|
account_name = str(item.get("account_name", "")).strip()
|
|
budget_amount = float(item.get("budget_amount", 0) or 0)
|
|
if not section or not group_name or not category or not account_code:
|
|
continue
|
|
conn.execute(
|
|
"""
|
|
insert into project_budget_account_lines (
|
|
project_code, section, group_name, category, account_code, account_name, budget_amount, updated_at
|
|
) values (?, ?, ?, ?, ?, ?, ?, ?)
|
|
""",
|
|
(project_code, section, group_name, category, account_code, account_name, budget_amount, updated_at),
|
|
)
|
|
conn.execute(
|
|
"""
|
|
insert into project_progress (
|
|
project_code, progress_rate, contract_pile_count, constructed_pile_count, updated_at
|
|
)
|
|
values (?, ?, ?, ?, ?)
|
|
on conflict(project_code) do update set
|
|
progress_rate = excluded.progress_rate,
|
|
contract_pile_count = excluded.contract_pile_count,
|
|
constructed_pile_count = excluded.constructed_pile_count,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(project_code, progress_rate, contract_pile_count, constructed_pile_count, updated_at),
|
|
)
|
|
conn.commit()
|
|
self._send(200, {"ok": True, "project_code": project_code, "updated_at": updated_at})
|
|
return
|
|
|
|
if parsed.path == "/api/project-pile-progress/upsert":
|
|
payload = self._read_json()
|
|
project_code = str(payload.get("project_code", "")).strip()
|
|
contract_pile_count = float(payload.get("contract_pile_count", 0) or 0)
|
|
entries = payload.get("entries", [])
|
|
if not project_code:
|
|
self._send(400, {"ok": False, "message": "project_code is required"})
|
|
return
|
|
if not isinstance(entries, list):
|
|
self._send(400, {"ok": False, "message": "entries must be a list"})
|
|
return
|
|
|
|
updated_at = datetime.now().isoformat()
|
|
conn.execute("delete from project_pile_progress_entries where project_code = ?", (project_code,))
|
|
constructed_pile_count = 0.0
|
|
for idx, item in enumerate(entries):
|
|
start_date = str(item.get("start_date", "")).strip()
|
|
end_date = str(item.get("end_date", "")).strip()
|
|
pile_count = float(item.get("pile_count", 0) or 0)
|
|
note = str(item.get("note", "")).strip()
|
|
if not start_date:
|
|
continue
|
|
if not end_date:
|
|
end_date = start_date
|
|
constructed_pile_count += pile_count
|
|
conn.execute(
|
|
"""
|
|
insert into project_pile_progress_entries (
|
|
project_code, work_date, start_date, end_date, pile_count, note, sort_order, updated_at
|
|
) values (?, ?, ?, ?, ?, ?, ?, ?)
|
|
""",
|
|
(project_code, start_date, start_date, end_date, pile_count, note, idx, updated_at),
|
|
)
|
|
progress_rate = (constructed_pile_count / contract_pile_count * 100) if contract_pile_count > 0 else 0
|
|
conn.execute(
|
|
"""
|
|
insert into project_progress (
|
|
project_code, progress_rate, contract_pile_count, constructed_pile_count, updated_at
|
|
)
|
|
values (?, ?, ?, ?, ?)
|
|
on conflict(project_code) do update set
|
|
progress_rate = excluded.progress_rate,
|
|
contract_pile_count = excluded.contract_pile_count,
|
|
constructed_pile_count = excluded.constructed_pile_count,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(project_code, progress_rate, contract_pile_count, constructed_pile_count, updated_at),
|
|
)
|
|
conn.commit()
|
|
self._send(
|
|
200,
|
|
{
|
|
"ok": True,
|
|
"project_code": project_code,
|
|
"contract_pile_count": contract_pile_count,
|
|
"constructed_pile_count": constructed_pile_count,
|
|
"progress_rate": progress_rate,
|
|
"updated_at": updated_at,
|
|
},
|
|
)
|
|
return
|
|
|
|
self._send(404, {"ok": False, "message": "Not found"})
|
|
finally:
|
|
conn.close()
|
|
|
|
def do_GET(self) -> None:
|
|
parsed = urlparse(self.path)
|
|
params = parse_qs(parsed.query)
|
|
conn = get_conn()
|
|
try:
|
|
if parsed.path in {"/PTC-lab", "/PTC-lab/", "/PTC/dashboard_preview.html"}:
|
|
self._send_frontend_dashboard_preview()
|
|
return
|
|
|
|
if parsed.path in {"/PTC", "/PTC/", "/PTC/index.html"}:
|
|
self._send_frontend()
|
|
return
|
|
|
|
if parsed.path == "/":
|
|
count = conn.execute("select count(*) as count from ptc_transactions").fetchone()["count"]
|
|
xlsx_path = get_xlsx_path()
|
|
html = f"""<!DOCTYPE html>
|
|
<html lang="ko">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
<title>PTC API Server</title>
|
|
<style>
|
|
:root {{
|
|
--ink: #102037;
|
|
--muted: #63758d;
|
|
--line: #d7e1ea;
|
|
--blue: #124c7c;
|
|
--cyan: #1786a1;
|
|
--soft: #f5f9fc;
|
|
}}
|
|
* {{ box-sizing: border-box; }}
|
|
body {{
|
|
margin: 0;
|
|
font-family: Arial, sans-serif;
|
|
color: var(--ink);
|
|
background:
|
|
radial-gradient(circle at top right, rgba(23,134,161,0.10), transparent 24%),
|
|
linear-gradient(180deg, #f8fbff 0%, #eef3f7 100%);
|
|
}}
|
|
.page {{
|
|
width: min(1120px, calc(100vw - 32px));
|
|
margin: 0 auto;
|
|
padding: 28px 0 60px;
|
|
}}
|
|
.panel {{
|
|
background: rgba(255,255,255,0.92);
|
|
border: 1px solid var(--line);
|
|
border-radius: 24px;
|
|
box-shadow: 0 16px 40px rgba(16, 32, 55, 0.06);
|
|
padding: 24px;
|
|
}}
|
|
.hero {{
|
|
display: grid;
|
|
grid-template-columns: 1.2fr 0.8fr;
|
|
gap: 18px;
|
|
}}
|
|
.cards {{
|
|
display: grid;
|
|
grid-template-columns: repeat(3, 1fr);
|
|
gap: 14px;
|
|
margin-top: 18px;
|
|
}}
|
|
.card {{
|
|
border: 1px solid var(--line);
|
|
border-radius: 18px;
|
|
padding: 18px;
|
|
background: linear-gradient(180deg, white, var(--soft));
|
|
}}
|
|
.route {{
|
|
border: 1px solid var(--line);
|
|
border-radius: 16px;
|
|
padding: 16px;
|
|
background: white;
|
|
margin-top: 12px;
|
|
}}
|
|
.method {{
|
|
display: inline-block;
|
|
padding: 4px 8px;
|
|
border-radius: 999px;
|
|
background: #e8f4fb;
|
|
color: var(--blue);
|
|
font-size: 11px;
|
|
font-weight: 700;
|
|
margin-right: 8px;
|
|
}}
|
|
.path {{
|
|
font-family: monospace;
|
|
font-size: 14px;
|
|
font-weight: 700;
|
|
color: var(--ink);
|
|
}}
|
|
.subtle {{
|
|
color: var(--muted);
|
|
font-size: 13px;
|
|
line-height: 1.6;
|
|
}}
|
|
a {{ color: var(--cyan); text-decoration: none; }}
|
|
a:hover {{ text-decoration: underline; }}
|
|
@media (max-width: 900px) {{
|
|
.hero, .cards {{ grid-template-columns: 1fr; }}
|
|
}}
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<div class="page">
|
|
<section class="panel">
|
|
<div class="hero">
|
|
<div>
|
|
<div style="display:inline-flex;padding:7px 12px;border-radius:999px;background:#eaf3fb;color:#124c7c;font-size:11px;font-weight:700;">PTC Data API</div>
|
|
<h1 style="font-size:36px;line-height:1.2;margin:16px 0 12px;">PTC 원장 데이터 서버</h1>
|
|
<p class="subtle">
|
|
이 서버는 선택된 PTC 원본 엑셀 파일을 읽어 요약, 프로젝트 집계, 계정 집계, 거래 미리보기를 JSON API로 제공합니다.
|
|
메인 화면은 <a href="http://localhost:4000/PTC/">http://localhost:4000/PTC/</a> 에서 바로 확인할 수 있습니다.
|
|
</p>
|
|
</div>
|
|
<div class="card">
|
|
<div style="font-size:12px;color:var(--muted);font-weight:700;">현재 적재 건수</div>
|
|
<div style="font-size:34px;font-weight:700;margin-top:10px;">{count:,}</div>
|
|
<div class="subtle" style="margin-top:10px;">원본 파일 기준 전체 거래 행 수</div>
|
|
<div class="subtle" style="margin-top:10px;word-break:break-all;">원본 파일: {xlsx_path}</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="cards">
|
|
<div class="card">
|
|
<div style="font-size:12px;color:var(--muted);font-weight:700;">메인 화면</div>
|
|
<div style="font-size:20px;font-weight:700;margin-top:8px;">localhost:4000/PTC/</div>
|
|
<div class="subtle" style="margin-top:10px;">사용자가 보는 메인 대시보드와 API를 같은 서버에서 제공합니다.</div>
|
|
</div>
|
|
<div class="card">
|
|
<div style="font-size:12px;color:var(--muted);font-weight:700;">헬스체크</div>
|
|
<div style="font-size:20px;font-weight:700;margin-top:8px;">/api/health</div>
|
|
<div class="subtle" style="margin-top:10px;">서버 상태와 row count 확인</div>
|
|
</div>
|
|
<div class="card">
|
|
<div style="font-size:12px;color:var(--muted);font-weight:700;">요약</div>
|
|
<div style="font-size:20px;font-weight:700;margin-top:8px;">/api/summary</div>
|
|
<div class="subtle" style="margin-top:10px;">건수, 기간, 공급가액, 누락값</div>
|
|
</div>
|
|
</div>
|
|
</section>
|
|
|
|
<section class="panel" style="margin-top:18px;">
|
|
<h2 style="margin:0 0 10px;font-size:22px;">주요 API</h2>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/health</span>
|
|
<div class="subtle" style="margin-top:8px;">API 서버가 정상 동작하는지와 적재 건수를 반환합니다.</div>
|
|
</div>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/summary</span>
|
|
<div class="subtle" style="margin-top:8px;">건수, 입금/출금, 공급가액, 부가세, 기간, 누락값 요약을 반환합니다.</div>
|
|
</div>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/top-accounts</span>
|
|
<div class="subtle" style="margin-top:8px;">계정코드별 상위 집계를 반환합니다.</div>
|
|
</div>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/top-projects</span>
|
|
<div class="subtle" style="margin-top:8px;">프로젝트별 상위 집계를 반환합니다.</div>
|
|
</div>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/project-mismatches</span>
|
|
<div class="subtle" style="margin-top:8px;">프로젝트코드 대비 프로젝트명/구분 불일치를 반환합니다.</div>
|
|
</div>
|
|
<div class="route">
|
|
<span class="method">GET</span><span class="path">/api/transactions?limit=30</span>
|
|
<div class="subtle" style="margin-top:8px;">원본 거래 미리보기를 반환합니다.</div>
|
|
</div>
|
|
</section>
|
|
|
|
<section class="panel" style="margin-top:18px;">
|
|
<h2 style="margin:0 0 10px;font-size:22px;">필터 예시</h2>
|
|
<div class="route">
|
|
<span class="path">/api/summary?project_type=시공</span>
|
|
</div>
|
|
<div class="route">
|
|
<span class="path">/api/top-accounts?in_out=출금</span>
|
|
</div>
|
|
<div class="route">
|
|
<span class="path">/api/transactions?keyword=여비교통비&limit=20</span>
|
|
</div>
|
|
</section>
|
|
</div>
|
|
</body>
|
|
</html>"""
|
|
self._send_html(200, html)
|
|
return
|
|
|
|
if parsed.path == "/api/health":
|
|
count = conn.execute("select count(*) as count from ptc_transactions").fetchone()["count"]
|
|
self._send(200, {"ok": True, "row_count": count})
|
|
return
|
|
|
|
if parsed.path == "/api/project-master-options":
|
|
self._send(
|
|
200,
|
|
{
|
|
"project_type_options": PROJECT_TYPE_OPTIONS,
|
|
"method_family_options": METHOD_FAMILY_OPTIONS,
|
|
"method_options": METHOD_OPTIONS,
|
|
"method_family_map": METHOD_FAMILY_MAP,
|
|
"account_master": ACCOUNT_MASTER,
|
|
"allowed_account_codes_by_project_type": {
|
|
key: sorted(value) for key, value in ALLOWED_ACCOUNT_CODES_BY_PROJECT_TYPE.items()
|
|
},
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/summary":
|
|
summary = query_summary(conn, params)
|
|
self._send(200, summary)
|
|
return
|
|
|
|
if parsed.path == "/api/dashboard-prototype":
|
|
project_type = params.get("project_type", ["전체"])[0]
|
|
clauses = ["t.project_code is not null", "t.project_code <> ''"]
|
|
values: list[str] = []
|
|
if project_type and project_type != "전체":
|
|
clauses.append("coalesce(pm.project_type, t.project_type) = ?")
|
|
values.append(project_type)
|
|
where = " where " + " and ".join(clauses)
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
t.project_code,
|
|
coalesce(pm.project_name, max(t.project_name)) as project_name,
|
|
coalesce(pm.project_type, max(t.project_type)) as project_type,
|
|
coalesce(pm.construction_family, '') as construction_family,
|
|
coalesce(pm.construction_method, '') as construction_method,
|
|
coalesce(pp.progress_rate, 0) as progress_rate,
|
|
coalesce(pp.contract_pile_count, 0) as contract_pile_count,
|
|
coalesce(pp.constructed_pile_count, 0) as constructed_pile_count,
|
|
coalesce(sum(case when t.in_out = '입금' then t.supply_amount else 0 end), 0) as income_supply,
|
|
coalesce(sum(case when t.in_out = '출금' then t.supply_amount else 0 end), 0) as expense_supply,
|
|
count(*) as txn_count
|
|
from ptc_transactions t
|
|
left join project_master pm on pm.project_code = t.project_code
|
|
left join project_progress pp on pp.project_code = t.project_code
|
|
{where}
|
|
group by t.project_code, pm.project_name, pm.project_type, pm.construction_family, pm.construction_method, pp.progress_rate, pp.contract_pile_count, pp.constructed_pile_count
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
|
|
amount_buckets = [
|
|
{"key": "under_5", "label": "5억 미만", "min": 0, "max": 500_000_000},
|
|
{"key": "5_to_20", "label": "5억~20억", "min": 500_000_000, "max": 2_000_000_000},
|
|
{"key": "20_to_50", "label": "20억~50억", "min": 2_000_000_000, "max": 5_000_000_000},
|
|
{"key": "over_50", "label": "50억 이상", "min": 5_000_000_000, "max": None},
|
|
]
|
|
status_bands = [
|
|
{"key": "normal", "label": "정상"},
|
|
{"key": "upfront", "label": "선투입"},
|
|
{"key": "delay", "label": "회수지연"},
|
|
{"key": "risk", "label": "원가위험"},
|
|
]
|
|
|
|
def bucket_amount(value: float) -> str:
|
|
for bucket in amount_buckets:
|
|
if bucket["max"] is None and value >= bucket["min"]:
|
|
return bucket["key"]
|
|
if bucket["min"] <= value < bucket["max"]:
|
|
return bucket["key"]
|
|
return amount_buckets[0]["key"]
|
|
|
|
def classify_status(income_supply: float, expense_supply: float, progress_rate: float, contract_pile_count: float, constructed_pile_count: float) -> str:
|
|
profit_supply = income_supply - expense_supply
|
|
expense_ratio = (expense_supply / income_supply) if income_supply > 0 else None
|
|
has_progress_signal = progress_rate > 0 or contract_pile_count > 0 or constructed_pile_count > 0
|
|
|
|
if profit_supply >= 0:
|
|
return "normal"
|
|
|
|
if income_supply <= 0 and expense_supply > 0:
|
|
return "upfront"
|
|
|
|
if expense_ratio is not None and expense_ratio >= 1.15:
|
|
return "risk"
|
|
|
|
if expense_supply >= 300_000_000 and profit_supply < -100_000_000:
|
|
return "risk"
|
|
|
|
if income_supply > 0 and income_supply < expense_supply:
|
|
return "delay" if has_progress_signal else "risk"
|
|
|
|
return "normal"
|
|
|
|
by_method: dict[str, dict] = {}
|
|
overview = {
|
|
"project_count": 0,
|
|
"income_supply": 0.0,
|
|
"expense_supply": 0.0,
|
|
"profit_supply": 0.0,
|
|
"status_counts": {band["key"]: 0 for band in status_bands},
|
|
}
|
|
project_items = []
|
|
|
|
for row in rows:
|
|
method = normalize_dashboard_method(row["construction_method"] or "")
|
|
family = normalize_dashboard_family(row["construction_family"] or "")
|
|
income_supply = float(row["income_supply"] or 0)
|
|
expense_supply = float(row["expense_supply"] or 0)
|
|
profit_supply = income_supply - expense_supply
|
|
margin_rate = (profit_supply / income_supply * 100) if income_supply > 0 else 0.0
|
|
progress_rate = float(row["progress_rate"] or 0)
|
|
contract_pile_count = float(row["contract_pile_count"] or 0)
|
|
constructed_pile_count = float(row["constructed_pile_count"] or 0)
|
|
amount_bucket_key = bucket_amount(income_supply)
|
|
status_key = classify_status(income_supply, expense_supply, progress_rate, contract_pile_count, constructed_pile_count)
|
|
|
|
if method not in by_method:
|
|
by_method[method] = {
|
|
"method": method,
|
|
"family": family,
|
|
"project_count": 0,
|
|
"income_supply": 0.0,
|
|
"expense_supply": 0.0,
|
|
"profit_supply": 0.0,
|
|
"status_counts": {band["key"]: 0 for band in status_bands},
|
|
"cells": {
|
|
bucket["key"]: {
|
|
"bucket_key": bucket["key"],
|
|
"bucket_label": bucket["label"],
|
|
"project_count": 0,
|
|
"income_supply": 0.0,
|
|
"expense_supply": 0.0,
|
|
"profit_supply": 0.0,
|
|
"status_counts": {band["key"]: 0 for band in status_bands},
|
|
"projects": [],
|
|
}
|
|
for bucket in amount_buckets
|
|
},
|
|
}
|
|
|
|
cell = by_method[method]["cells"][amount_bucket_key]
|
|
cell["project_count"] += 1
|
|
cell["income_supply"] += income_supply
|
|
cell["expense_supply"] += expense_supply
|
|
cell["profit_supply"] += profit_supply
|
|
cell["status_counts"][status_key] += 1
|
|
if len(cell["projects"]) < 5:
|
|
cell["projects"].append({
|
|
"project_code": row["project_code"],
|
|
"project_name": row["project_name"],
|
|
"margin_rate": margin_rate,
|
|
"income_supply": income_supply,
|
|
"status_key": status_key,
|
|
})
|
|
|
|
by_method[method]["project_count"] += 1
|
|
by_method[method]["income_supply"] += income_supply
|
|
by_method[method]["expense_supply"] += expense_supply
|
|
by_method[method]["profit_supply"] += profit_supply
|
|
by_method[method]["status_counts"][status_key] += 1
|
|
|
|
overview["project_count"] += 1
|
|
overview["income_supply"] += income_supply
|
|
overview["expense_supply"] += expense_supply
|
|
overview["profit_supply"] += profit_supply
|
|
overview["status_counts"][status_key] += 1
|
|
|
|
project_items.append({
|
|
"project_code": row["project_code"],
|
|
"project_name": row["project_name"],
|
|
"project_type": row["project_type"],
|
|
"construction_method": method,
|
|
"construction_family": family,
|
|
"progress_rate": progress_rate,
|
|
"income_supply": income_supply,
|
|
"expense_supply": expense_supply,
|
|
"profit_supply": profit_supply,
|
|
"margin_rate": margin_rate,
|
|
"amount_bucket_key": amount_bucket_key,
|
|
"status_key": status_key,
|
|
})
|
|
|
|
overview["margin_rate"] = (overview["profit_supply"] / overview["income_supply"] * 100) if overview["income_supply"] > 0 else 0.0
|
|
method_items = []
|
|
for method, item in sorted(by_method.items(), key=lambda pair: (-pair[1]["income_supply"], pair[0])):
|
|
item["margin_rate"] = (item["profit_supply"] / item["income_supply"] * 100) if item["income_supply"] > 0 else 0.0
|
|
item["cells"] = [item["cells"][bucket["key"]] for bucket in amount_buckets]
|
|
method_items.append(item)
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"overview": overview,
|
|
"amount_buckets": amount_buckets,
|
|
"status_bands": status_bands,
|
|
"methods": method_items,
|
|
"projects": sorted(project_items, key=lambda item: (-item["income_supply"], item["project_code"])),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-types":
|
|
rows = conn.execute(
|
|
"select distinct project_type from ptc_transactions where coalesce(project_type,'') <> '' order by project_type"
|
|
).fetchall()
|
|
self._send(200, {"items": [row["project_type"] for row in rows]})
|
|
return
|
|
|
|
if parsed.path == "/api/projects":
|
|
keyword = params.get("keyword", [""])[0].strip().lower()
|
|
project_type = params.get("project_type", ["전체"])[0]
|
|
clauses = ["project_code is not null", "project_code <> ''"]
|
|
values = []
|
|
if keyword:
|
|
like = f"%{keyword}%"
|
|
clauses.append(
|
|
"""
|
|
(
|
|
lower(coalesce(project_code, '')) like ?
|
|
or lower(coalesce(project_name, '')) like ?
|
|
or lower(coalesce(project_type, '')) like ?
|
|
)
|
|
"""
|
|
)
|
|
values.extend([like, like, like])
|
|
if project_type and project_type != "전체":
|
|
clauses.append("project_type = ?")
|
|
values.append(project_type)
|
|
where = " where " + " and ".join(clauses)
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
project_code,
|
|
max(project_name) as project_name,
|
|
max(project_type) as project_type,
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{where}
|
|
group by project_code
|
|
order by supply_sum desc, project_code
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
items = rows_to_dicts(rows)
|
|
project_codes = [item["project_code"] for item in items if item.get("project_code")]
|
|
master_rows = {}
|
|
if project_codes:
|
|
placeholders = ",".join("?" for _ in project_codes)
|
|
master_rows = {
|
|
row["project_code"]: dict(row)
|
|
for row in conn.execute(
|
|
f"""
|
|
select project_code, project_name, project_type, construction_family, construction_method, note
|
|
from project_master
|
|
where project_code in ({placeholders})
|
|
""",
|
|
project_codes,
|
|
).fetchall()
|
|
}
|
|
for item in items:
|
|
master = master_rows.get(item["project_code"])
|
|
item["project_type"] = resolve_project_type(
|
|
item["project_code"],
|
|
item["project_type"],
|
|
master.get("project_type") if master else "",
|
|
)
|
|
if master:
|
|
item["project_name"] = master.get("project_name") or item["project_name"]
|
|
item["construction_family"] = resolve_construction_family(
|
|
master.get("construction_method"),
|
|
master.get("construction_family"),
|
|
)
|
|
item["construction_method"] = master.get("construction_method") or ""
|
|
item["note"] = master.get("note") or ""
|
|
else:
|
|
item["construction_family"] = ""
|
|
item["construction_method"] = ""
|
|
item["note"] = ""
|
|
self._send(200, {"items": items})
|
|
return
|
|
|
|
if parsed.path == "/api/vendors":
|
|
keyword = params.get("keyword", [""])[0].strip().lower()
|
|
clauses = ["coalesce(vendor_name, '') <> ''"]
|
|
values: list[str] = []
|
|
if keyword:
|
|
clauses.append(
|
|
"""
|
|
(
|
|
lower(coalesce(vendor_name, '')) like ?
|
|
or lower(coalesce(account_code_final, '')) like ?
|
|
or lower(coalesce(account_name_final, '')) like ?
|
|
)
|
|
"""
|
|
)
|
|
like = f"%{keyword}%"
|
|
values.extend([like, like, like])
|
|
where = f"where {' and '.join(clauses)}"
|
|
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
vendor_name,
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{where}
|
|
group by vendor_name
|
|
order by supply_sum desc, vendor_name
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
if parsed.path == "/api/accounts":
|
|
keyword = params.get("keyword", [""])[0].strip().lower()
|
|
clauses = ["coalesce(account_code_final, '') <> ''"]
|
|
values: list[str] = []
|
|
if keyword:
|
|
clauses.append(
|
|
"""
|
|
(
|
|
lower(coalesce(account_code_final, '')) like ?
|
|
or lower(coalesce(account_name_final, '')) like ?
|
|
or lower(coalesce(vendor_name, '')) like ?
|
|
)
|
|
"""
|
|
)
|
|
like = f"%{keyword}%"
|
|
values.extend([like, like, like])
|
|
where = f"where {' and '.join(clauses)}"
|
|
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{where}
|
|
group by account_code_final, account_name_final
|
|
order by cast(account_code_final as integer) asc, account_code_final asc
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
if parsed.path == "/api/account-detail":
|
|
account_code = params.get("account_code", [""])[0].strip()
|
|
project_code = params.get("project_code", [""])[0].strip()
|
|
if not account_code:
|
|
self._send(400, {"ok": False, "message": "account_code is required"})
|
|
return
|
|
|
|
detail_clauses = ["account_code_final = ?"]
|
|
detail_values: list[str] = [account_code]
|
|
if project_code:
|
|
detail_clauses.append("project_code = ?")
|
|
detail_values.append(project_code)
|
|
detail_where = " where " + " and ".join(detail_clauses)
|
|
|
|
summary = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{detail_where}
|
|
group by account_code_final, account_name_final
|
|
""",
|
|
detail_values,
|
|
).fetchone()
|
|
|
|
project_rows = conn.execute(
|
|
"""
|
|
select
|
|
project_code,
|
|
max(project_name) as project_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
where account_code_final = ?
|
|
group by project_code
|
|
order by supply_sum desc, project_code
|
|
limit 30
|
|
""",
|
|
(account_code,),
|
|
).fetchall()
|
|
|
|
vendor_rows = conn.execute(
|
|
f"""
|
|
select
|
|
vendor_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
{detail_where}
|
|
group by vendor_name
|
|
order by supply_sum desc, vendor_name
|
|
limit 30
|
|
""",
|
|
detail_values,
|
|
).fetchall()
|
|
|
|
transaction_rows = conn.execute(
|
|
f"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
project_code,
|
|
project_name,
|
|
vendor_name,
|
|
department_name,
|
|
description,
|
|
supply_amount
|
|
from ptc_transactions
|
|
{detail_where}
|
|
order by transaction_date desc, source_row_no desc
|
|
limit 100
|
|
""",
|
|
detail_values,
|
|
).fetchall()
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"summary": dict(summary) if summary else None,
|
|
"projects": rows_to_dicts(project_rows),
|
|
"vendors": rows_to_dicts(vendor_rows),
|
|
"transactions": rows_to_dicts(transaction_rows),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/vendor-detail":
|
|
vendor_name = params.get("vendor_name", [""])[0].strip()
|
|
project_code = params.get("project_code", [""])[0].strip()
|
|
account_code = params.get("account_code", [""])[0].strip()
|
|
if not vendor_name:
|
|
self._send(400, {"ok": False, "message": "vendor_name is required"})
|
|
return
|
|
|
|
detail_clauses = ["vendor_name = ?"]
|
|
detail_values: list[str] = [vendor_name]
|
|
if project_code:
|
|
detail_clauses.append("project_code = ?")
|
|
detail_values.append(project_code)
|
|
if account_code:
|
|
detail_clauses.append("account_code_final = ?")
|
|
detail_values.append(account_code)
|
|
detail_where = " where " + " and ".join(detail_clauses)
|
|
|
|
summary = conn.execute(
|
|
"""
|
|
select
|
|
vendor_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
where vendor_name = ?
|
|
group by vendor_name
|
|
""",
|
|
(vendor_name,),
|
|
).fetchone()
|
|
|
|
project_rows = conn.execute(
|
|
"""
|
|
select
|
|
project_code,
|
|
max(project_name) as project_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
where vendor_name = ?
|
|
group by project_code
|
|
order by supply_sum desc, project_code
|
|
limit 20
|
|
""",
|
|
(vendor_name,),
|
|
).fetchall()
|
|
|
|
account_rows = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_supply_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_supply_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
{detail_where}
|
|
group by account_code_final, account_name_final
|
|
order by supply_sum desc, account_code_final
|
|
limit 30
|
|
""",
|
|
detail_values,
|
|
).fetchall()
|
|
|
|
transaction_rows = conn.execute(
|
|
f"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
project_code,
|
|
project_name,
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
department_name,
|
|
description,
|
|
supply_amount
|
|
from ptc_transactions
|
|
{detail_where}
|
|
order by transaction_date desc, source_row_no desc
|
|
limit 100
|
|
""",
|
|
detail_values,
|
|
).fetchall()
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"summary": dict(summary) if summary else None,
|
|
"projects": rows_to_dicts(project_rows),
|
|
"accounts": rows_to_dicts(account_rows),
|
|
"transactions": rows_to_dicts(transaction_rows),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-detail":
|
|
project_code = params.get("project_code", [""])[0].strip()
|
|
if not project_code:
|
|
self._send(400, {"ok": False, "message": "project_code is required"})
|
|
return
|
|
|
|
keyword = params.get("keyword", [""])[0]
|
|
in_out = params.get("in_out", ["전체"])[0]
|
|
where, values = build_project_where(project_code, keyword, in_out)
|
|
|
|
summary = conn.execute(
|
|
f"""
|
|
select
|
|
project_code,
|
|
max(project_name) as project_name,
|
|
max(project_type) as project_type,
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
coalesce(sum(vat_amount), 0) as vat_sum,
|
|
coalesce(sum(total_amount), 0) as total_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
{where}
|
|
""",
|
|
values,
|
|
).fetchone()
|
|
|
|
account_rows = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as code,
|
|
account_name_final as name,
|
|
count(*) as count,
|
|
coalesce(sum(supply_amount), 0) as total
|
|
from ptc_transactions
|
|
{where}
|
|
group by account_code_final, account_name_final
|
|
order by total desc
|
|
limit 12
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
|
|
transaction_rows = conn.execute(
|
|
f"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
department_name,
|
|
vendor_name,
|
|
description,
|
|
supply_amount,
|
|
vat_amount,
|
|
total_amount
|
|
from ptc_transactions
|
|
{where}
|
|
order by transaction_date desc, source_row_no desc
|
|
limit 20
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
|
|
summary_dict = dict(summary) if summary else None
|
|
master = fetch_project_master(conn, project_code)
|
|
if summary_dict and master:
|
|
summary_dict["project_name"] = master.get("project_name") or summary_dict["project_name"]
|
|
summary_dict["project_type"] = resolve_project_type(
|
|
project_code,
|
|
summary_dict["project_type"],
|
|
master.get("project_type"),
|
|
)
|
|
summary_dict["construction_family"] = resolve_construction_family(
|
|
master.get("construction_method"),
|
|
master.get("construction_family"),
|
|
)
|
|
summary_dict["construction_method"] = master.get("construction_method") or ""
|
|
summary_dict["start_date"] = master.get("start_date") or ""
|
|
summary_dict["end_date"] = master.get("end_date") or ""
|
|
summary_dict["note"] = master.get("note") or ""
|
|
elif summary_dict:
|
|
summary_dict["project_type"] = resolve_project_type(project_code, summary_dict["project_type"])
|
|
summary_dict["construction_family"] = resolve_construction_family("")
|
|
summary_dict["construction_method"] = ""
|
|
summary_dict["start_date"] = ""
|
|
summary_dict["end_date"] = ""
|
|
summary_dict["note"] = ""
|
|
account_issues = get_project_account_issues(
|
|
conn,
|
|
project_code,
|
|
summary_dict["project_type"] if summary_dict else "",
|
|
)
|
|
budget_analysis = build_budget_analysis(conn, project_code, build_account_structure_rows(account_rows))
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"summary": summary_dict,
|
|
"project_master": master,
|
|
"account_structure": build_account_structure_rows(account_rows),
|
|
"budget_analysis": budget_analysis,
|
|
"accounts": rows_to_dicts(account_rows),
|
|
"account_issues": account_issues,
|
|
"transactions": rows_to_dicts(transaction_rows),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-account-issue-detail":
|
|
project_code = params.get("project_code", [""])[0].strip()
|
|
account_code = params.get("account_code", [""])[0].strip()
|
|
if not project_code or not account_code:
|
|
self._send(400, {"ok": False, "message": "project_code and account_code are required"})
|
|
return
|
|
|
|
rows = conn.execute(
|
|
"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
department_name,
|
|
vendor_name,
|
|
description,
|
|
supply_amount,
|
|
vat_amount,
|
|
total_amount
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
and account_code_final = ?
|
|
order by transaction_date desc, source_row_no desc
|
|
limit 100
|
|
""",
|
|
(project_code, account_code),
|
|
).fetchall()
|
|
|
|
summary = conn.execute(
|
|
"""
|
|
select
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum,
|
|
min(transaction_date) as min_date,
|
|
max(transaction_date) as max_date
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
and account_code_final = ?
|
|
""",
|
|
(project_code, account_code),
|
|
).fetchone()
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"project_code": project_code,
|
|
"account_code": account_code,
|
|
"account_name": resolve_account_name(account_code, rows[0]["account_name"] if rows else ""),
|
|
"summary": dict(summary) if summary else None,
|
|
"items": rows_to_dicts(rows),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/project-budget-actual-detail":
|
|
project_code = params.get("project_code", [""])[0].strip()
|
|
section = params.get("section", [""])[0].strip()
|
|
group_name = params.get("group_name", [""])[0].strip()
|
|
category = params.get("category", [""])[0].strip()
|
|
if not project_code or not section or not group_name or not category:
|
|
self._send(400, {"ok": False, "message": "project_code, section, group_name, category are required"})
|
|
return
|
|
|
|
category_accounts = get_category_account_items(section, group_name, category)
|
|
account_codes = [item["account_code"] for item in category_accounts if item.get("account_code")]
|
|
if not account_codes:
|
|
self._send(
|
|
200,
|
|
{
|
|
"project_code": project_code,
|
|
"section": section,
|
|
"group_name": group_name,
|
|
"category": category,
|
|
"summary": {"txn_count": 0, "income_count": 0, "expense_count": 0, "income_sum": 0, "expense_sum": 0, "supply_sum": 0},
|
|
"accounts": [],
|
|
"transactions": [],
|
|
},
|
|
)
|
|
return
|
|
|
|
placeholders = ",".join("?" for _ in account_codes)
|
|
values = [project_code, *account_codes]
|
|
|
|
summary = conn.execute(
|
|
f"""
|
|
select
|
|
count(*) as txn_count,
|
|
sum(case when in_out = '입금' then 1 else 0 end) as income_count,
|
|
sum(case when in_out = '출금' then 1 else 0 end) as expense_count,
|
|
coalesce(sum(case when in_out = '입금' then supply_amount else 0 end), 0) as income_sum,
|
|
coalesce(sum(case when in_out = '출금' then supply_amount else 0 end), 0) as expense_sum,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
and account_code_final in ({placeholders})
|
|
""",
|
|
values,
|
|
).fetchone()
|
|
|
|
account_rows = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as account_code,
|
|
max(account_name_final) as account_name,
|
|
count(*) as txn_count,
|
|
coalesce(sum(supply_amount), 0) as supply_sum
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
and account_code_final in ({placeholders})
|
|
group by account_code_final
|
|
order by account_code_final
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
|
|
transaction_rows = conn.execute(
|
|
f"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
department_name,
|
|
vendor_name,
|
|
description,
|
|
supply_amount,
|
|
vat_amount,
|
|
total_amount
|
|
from ptc_transactions
|
|
where project_code = ?
|
|
and account_code_final in ({placeholders})
|
|
order by transaction_date desc, source_row_no desc
|
|
limit 100
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
|
|
self._send(
|
|
200,
|
|
{
|
|
"project_code": project_code,
|
|
"section": section,
|
|
"group_name": group_name,
|
|
"category": category,
|
|
"summary": dict(summary) if summary else None,
|
|
"accounts": rows_to_dicts(account_rows),
|
|
"transactions": rows_to_dicts(transaction_rows),
|
|
},
|
|
)
|
|
return
|
|
|
|
if parsed.path == "/api/top-accounts":
|
|
where, values = build_where(params)
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
account_code_final as code,
|
|
account_name_final as name,
|
|
count(*) as count,
|
|
coalesce(sum(supply_amount), 0) as total
|
|
from ptc_transactions
|
|
{where}
|
|
group by account_code_final, account_name_final
|
|
order by total desc
|
|
limit 10
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
if parsed.path == "/api/top-projects":
|
|
where, values = build_where(params)
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
coalesce(project_code, '(없음)') as project_code,
|
|
coalesce(project_name, '(없음)') as project_name,
|
|
coalesce(project_type, '(없음)') as project_type,
|
|
count(*) as count,
|
|
coalesce(sum(supply_amount), 0) as total
|
|
from ptc_transactions
|
|
{where}
|
|
group by project_code, project_name, project_type
|
|
order by total desc
|
|
limit 10
|
|
""",
|
|
values,
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
if parsed.path == "/api/project-mismatches":
|
|
rows = conn.execute(
|
|
"""
|
|
select project_code, count(distinct project_name) as name_count, count(distinct project_type) as type_count
|
|
from ptc_transactions
|
|
where coalesce(project_code, '') <> ''
|
|
group by project_code
|
|
having count(distinct project_name) > 1 or count(distinct project_type) > 1
|
|
order by project_code
|
|
limit 20
|
|
"""
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
if parsed.path == "/api/transactions":
|
|
where, values = build_where(params)
|
|
limit = int(params.get("limit", ["30"])[0])
|
|
rows = conn.execute(
|
|
f"""
|
|
select
|
|
source_row_no,
|
|
transaction_date,
|
|
in_out,
|
|
account_code_final as account_code,
|
|
account_name_final as account_name,
|
|
department_name,
|
|
vendor_name,
|
|
project_code,
|
|
project_name,
|
|
project_type,
|
|
description,
|
|
supply_amount,
|
|
vat_amount,
|
|
total_amount
|
|
from ptc_transactions
|
|
{where}
|
|
order by source_row_no
|
|
limit ?
|
|
""",
|
|
values + [limit],
|
|
).fetchall()
|
|
self._send(200, {"items": rows_to_dicts(rows)})
|
|
return
|
|
|
|
self._send(404, {"ok": False, "message": "Not found"})
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def main() -> None:
|
|
init_db()
|
|
server = ThreadingHTTPServer(("0.0.0.0", 4000), Handler)
|
|
print("PTC API server listening on http://0.0.0.0:4000")
|
|
server.serve_forever()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|