"""데이터 파싱 및 변환""" from decimal import Decimal, InvalidOperation from typing import Any, Optional from datetime import date from .data_models import ( FinancialReport, PLItem, MonthlyValue, KPICard, ChartData, ) def parse_number(value: Any) -> Decimal: """문자열을 Decimal로 변환""" if value is None or value == "": return Decimal(0) if isinstance(value, (int, float)): return Decimal(str(value)) try: # 쉼표, 엔화 기호 제거 cleaned = str(value).replace(",", "").replace("¥", "").replace("円", "").strip() # 괄호로 표시된 음수 처리 if cleaned.startswith("(") and cleaned.endswith(")"): cleaned = "-" + cleaned[1:-1] # 퍼센트 제거 cleaned = cleaned.replace("%", "") if cleaned == "" or cleaned == "-": return Decimal(0) return Decimal(cleaned) except InvalidOperation: return Decimal(0) def parse_pl_items_from_gemegg_format(data: list[list[Any]]) -> tuple[list[PLItem], list[str]]: """GemEgg 스프레드시트 형식에서 P/L 항목 파싱 형식: - Row 3: 월 헤더 (예: ['', '', '', '', '2025-04', '', '', '', '2025-05', ...]) - Row 4: 컬럼 헤더 (예: ['', '項目', '', '', '予算', '実績', '差異', '達成率', ...]) - Row 5+: 데이터 행 Returns: (PLItem 목록, 월 라벨 목록) """ if len(data) < 5: return [], [] # 월 헤더 행 찾기 (Row 3, index 3) month_header_row = data[3] if len(data) > 3 else [] column_header_row = data[4] if len(data) > 4 else [] # 월별 컬럼 위치 찾기 month_columns = {} # {month_label: {'budget': col_idx, 'actual': col_idx}} current_month = None for i, cell in enumerate(month_header_row): cell_str = str(cell).strip() if cell_str and cell_str != '': current_month = cell_str month_columns[current_month] = {'start': i} # 예산/실적 컬럼 매핑 for month, info in month_columns.items(): start_col = info['start'] # 예산, 실績 컬럼 찾기 for j in range(start_col, min(start_col + 10, len(column_header_row))): if j < len(column_header_row): col_name = str(column_header_row[j]).strip() if col_name == '予算': month_columns[month]['budget'] = j elif col_name == '実績': month_columns[month]['actual'] = j month_labels = list(month_columns.keys()) # P/L 항목 파싱 pl_items = [] for row_idx in range(5, len(data)): row = data[row_idx] if not row or len(row) < 2: continue # 항목명 찾기 (column 1 또는 2) item_name = "" level = 0 if len(row) > 1 and str(row[1]).strip(): item_name = str(row[1]).strip() level = 0 elif len(row) > 2 and str(row[2]).strip(): item_name = str(row[2]).strip() level = 1 if not item_name: continue # 합계 항목 여부 is_total = any(kw in item_name for kw in ["計", "金額", "総"]) # 월별 값 파싱 monthly_values = [] for month_label, cols in month_columns.items(): budget_col = cols.get('budget', -1) actual_col = cols.get('actual', -1) budget = Decimal(0) actual = Decimal(0) if budget_col >= 0 and budget_col < len(row): budget = parse_number(row[budget_col]) if actual_col >= 0 and actual_col < len(row): actual = parse_number(row[actual_col]) monthly_values.append(MonthlyValue( month=month_label, budget=budget, actual=actual, )) pl_items.append(PLItem( name=item_name, level=level, is_total=is_total, monthly_values=monthly_values, )) return pl_items, month_labels def calculate_kpis(pl_items: list[PLItem]) -> list[KPICard]: """P/L 항목에서 KPI 계산""" kpis = [] # 매출 KPI sales_item = next((item for item in pl_items if item.name == "売上高"), None) if sales_item: kpis.append(KPICard( title="売上高", value=sales_item.total_actual, unit="円", target_value=sales_item.total_budget, color="#2563eb", )) # 매출총이익 gross_profit = next((item for item in pl_items if "売上総" in item.name), None) if gross_profit: kpis.append(KPICard( title="売上総利益", value=gross_profit.total_actual, unit="円", target_value=gross_profit.total_budget, color="#16a34a" if gross_profit.total_actual >= 0 else "#dc2626", )) # 영업이익 KPI op_profit = next((item for item in pl_items if "営業損益" in item.name), None) if op_profit: kpis.append(KPICard( title="営業利益", value=op_profit.total_actual, unit="円", target_value=op_profit.total_budget, color="#16a34a" if op_profit.total_actual >= 0 else "#dc2626", )) # 영업이익률 if sales_item and op_profit and sales_item.total_actual != 0: op_margin = (op_profit.total_actual / sales_item.total_actual) * 100 kpis.append(KPICard( title="営業利益率", value=op_margin, unit="%", color="#8b5cf6", )) # 경상이익 KPI ord_profit = next((item for item in pl_items if "経常損益" in item.name), None) if ord_profit: kpis.append(KPICard( title="経常利益", value=ord_profit.total_actual, unit="円", target_value=ord_profit.total_budget, color="#0891b2" if ord_profit.total_actual >= 0 else "#dc2626", )) return kpis def build_chart_data(pl_items: list[PLItem], month_labels: list[str]) -> dict[str, ChartData]: """차트 데이터 생성""" charts = {} # 매출/이익 콤보 차트 sales_item = next((item for item in pl_items if item.name == "売上高"), None) profit_item = next((item for item in pl_items if "営業損益" in item.name), None) if sales_item and profit_item: charts["sales_profit"] = ChartData( title="売上高・営業利益推移", labels=month_labels, datasets=[ { "label": "売上高(予算)", "data": [float(v.budget) for v in sales_item.monthly_values], "type": "bar", "color": "#93c5fd", }, { "label": "売上高(実績)", "data": [float(v.actual) for v in sales_item.monthly_values], "type": "bar", "color": "#2563eb", }, { "label": "営業利益(実績)", "data": [float(v.actual) for v in profit_item.monthly_values], "type": "line", "color": "#dc2626", }, ], chart_type="combo", ) # 달성률 추이 차트 if sales_item: achievement_rates = [] for v in sales_item.monthly_values: if v.budget != 0: rate = float((v.actual / v.budget) * 100) else: rate = 0 achievement_rates.append(rate) charts["achievement"] = ChartData( title="売上達成率推移", labels=month_labels, datasets=[ { "label": "達成率", "data": achievement_rates, "type": "line", "color": "#16a34a", }, { "label": "目標(100%)", "data": [100] * len(month_labels), "type": "line", "color": "#9ca3af", "dashed": True, }, ], chart_type="line", ) return charts def build_financial_report( raw_data: list[list[Any]], company_name: str, fiscal_year: str, period: str, target_month: str, ) -> FinancialReport: """원시 데이터에서 FinancialReport 객체 생성""" # P/L 항목 파싱 (GemEgg 형식) pl_items, month_labels = parse_pl_items_from_gemegg_format(raw_data) # KPI 계산 annual_kpis = calculate_kpis(pl_items) # 차트 데이터 생성 charts = build_chart_data(pl_items, month_labels) return FinancialReport( company_name=company_name, fiscal_year=fiscal_year, period=period, target_month=target_month, generated_at=date.today(), annual_kpis=annual_kpis, pl_items=pl_items, month_labels=month_labels, charts=charts, )