data_handler.py

import re
from datetime import datetime
from pathlib import Path

import pandas as pd

import config_handler

UKETSUKE_STATUSES = ["対応中", "検討中", "保留", "ボツ"]
ANKEN_STATUSES    = ["見積作成中", "決済待", "制作中", "検品中", "入金待", "完了", "ボツ"]
ANKEN_CO          = ["W", "R", "C"]


def is_sqlite() -> bool:
    return config_handler.is_sqlite()


def normalize_date(s: str) -> str:
    if not s:
        return s
    s = s.strip()
    if not s:
        return s
    if re.fullmatch(r'\d{4}[/\-]\d{1,2}[/\-]\d{1,2}', s):
        return s.replace('-', '/')
    m = re.fullmatch(r'(\d{2})/(\d{2})/(\d{2})', s)
    if m:
        return f"20{m.group(1)}/{m.group(2)}/{m.group(3)}"
    m = re.fullmatch(r'(\d{1,2})/(\d{1,2})', s)
    if m:
        y = datetime.now().year
        return f"{y}/{int(m.group(1)):02d}/{int(m.group(2)):02d}"
    return s


def load(sheet: str) -> pd.DataFrame:
    df = pd.read_excel(config_handler.get_data_path(), sheet_name=sheet, dtype=str)
    df = df.loc[:, ~df.columns.str.startswith("Unnamed")]
    df = df.where(df.notna(), "")
    if sheet == "受付管理":
        df = _ensure_uketsuke_no(df)
    if sheet == "案件管理":
        for col, default in (("確認日", ""), ("修正回数", "0")):
            if col not in df.columns:
                df[col] = default
    return df


def _ensure_uketsuke_no(df: pd.DataFrame) -> pd.DataFrame:
    yy = datetime.now().strftime("%y")
    prefix = f"A{yy}"
    existing = [
        int(v[len(prefix):]) for v in df["受付No"].tolist()
        if re.fullmatch(rf"A{yy}\d+", v)
    ]
    seq = max(existing) + 1 if existing else 1
    changed = False
    for i in df.index:
        if df.at[i, "受付No"].strip() == "":
            df.at[i, "受付No"] = f"{prefix}{seq:03d}"
            # 日付フォーマット統一（2026-05-12 → 2026/05/12）
            date_val = str(df.at[i, "受付日"])
            if "-" in date_val:
                df.at[i, "受付日"] = date_val[:10].replace("-", "/")
            if df.at[i, "Co"].strip() == "":
                df.at[i, "Co"] = "W"
            if df.at[i, "ステータス"].strip() == "":
                df.at[i, "ステータス"] = "対応中"
            seq += 1
            changed = True
    if changed:
        save("受付管理", df)
    return df


def save(sheet: str, df: pd.DataFrame):
    with pd.ExcelWriter(config_handler.get_data_path(), engine="openpyxl", mode="a", if_sheet_exists="replace") as w:
        df.to_excel(w, sheet_name=sheet, index=False)


def next_uketsuke_no() -> str:
    df = load("受付管理")
    yy = datetime.now().strftime("%y")
    prefix = f"A{yy}"
    existing = [
        int(v[len(prefix):]) for v in df["受付No"].tolist()
        if re.fullmatch(rf"A{yy}\d+", v)
    ]
    seq = max(existing) + 1 if existing else 1
    return f"{prefix}{seq:03d}"


def next_anken_no(kind: str = "M") -> str:
    df = load("案件管理")
    yy = datetime.now().strftime("%y")
    prefix = f"{kind}{yy}"
    existing = [
        int(v[len(prefix):]) for v in df["管理番号"].tolist()
        if re.fullmatch(rf"{kind}{yy}\d+", v)
    ]
    seq = max(existing) + 1 if existing else 1
    return f"{prefix}{seq:03d}"