auto-generate-export-contracts/scripts/match_vehicle.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
车型匹配脚本：在价格配置表中按ModelCode匹配车型信息
"""

import os
import openpyxl

# 价格列映射
PRICE_COLUMN_MAP = {
    'FOB': 'N',
    'FCA': 'O',
    'EXW': 'P',
}

PRICE_COL_INDEX = {
    'FOB': 13,  # N列 (0-based index)
    'FCA': 14,  # O列
    'EXW': 15,  # P列
}


def get_price_config_path():
    """获取价格配置表路径"""
    script_dir = os.path.dirname(os.path.abspath(__file__))
    return os.path.join(script_dir, '..', 'assets', 'vehicle-price-config.xlsx')


def match_vehicle(model_code: str, trade_term: str = 'FCA'):
    """
    在价格配置表中匹配车型
    
    Args:
        model_code: 车型型号，如 LZW5030XXYLGHUG
        trade_term: 贸易条款，如 FCA/FOB/EXW
    
    Returns:
        dict or None: {
            'unit_price_usd': float,
            'description_cn': str,
            'description_en': str,
            'model_code': str,
            'engine_code': str,
            'version': str,
            'config_desc': str,
            'sheet_name': str,
        }
    """
    if not model_code:
        return None
    
    model_code = model_code.strip().upper()
    trade_term = trade_term.upper()
    
    # 默认使用FCA价格列
    price_col_idx = PRICE_COL_INDEX.get(trade_term, 14)
    
    config_path = get_price_config_path()
    if not os.path.exists(config_path):
        print(f"[WARN] Price config not found: {config_path}")
        return None
    
    wb = openpyxl.load_workbook(config_path, data_only=True)
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row, values_only=False):
            # G列是型号 (index 6)
            cell_g = row[6] if len(row) > 6 else None
            if cell_g and cell_g.value:
                # ModelCode可能包含换行符，如 "LZW5030XXYLGHUG\n国六B（RDE）"
                cell_val = str(cell_g.value).strip().upper()
                # 分割换行符取第一行作为ModelCode
                cell_model = cell_val.split('\n')[0].strip()
                if cell_model == model_code:
                    # 找到匹配
                    result = {
                        'model_code': model_code,
                        'sheet_name': sheet_name,
                    }
                    
                    # B列: 市场名称
                    result['description_cn'] = str(row[1].value).strip() if row[1].value else ''
                    # C列: 版本
                    result['version'] = str(row[2].value).strip() if row[2].value else ''
                    # F列: 系别 (中英文)
                    series_val = str(row[5].value).strip() if row[5].value else ''
                    result['description_en'] = series_val
                    # 分离F列中英文
                    series_lines = [l.strip() for l in series_val.split('\n') if l.strip()]
                    result['series_cn'] = series_lines[0] if series_lines else ''
                    result['series_en'] = series_lines[1] if len(series_lines) > 1 else ''
                    # I列: 发动机代码
                    result['engine_code'] = str(row[8].value).strip() if row[8].value else ''
                    # J列: 主要配置描述
                    result['config_desc'] = str(row[9].value).strip() if row[9].value else ''
                    
                    # 价格列
                    price_cell = row[price_col_idx] if len(row) > price_col_idx else None
                    if price_cell and price_cell.value:
                        try:
                            result['unit_price_usd'] = round(float(price_cell.value), 2)
                        except (ValueError, TypeError):
                            result['unit_price_usd'] = None
                    else:
                        result['unit_price_usd'] = None
                    
                    wb.close()
                    return result
    
    wb.close()
    return None


def build_vehicle_description(vehicle: dict, match_result: dict = None) -> str:
    """
    构建PI中的商品描述文本
    
    格式示例: Wuling LZW5030XXYLGHUG, AGMC,1.999L
    """
    import re
    
    model_code = vehicle.get('model_code', '')
    engine_code = vehicle.get('engine_code', '')
    
    # 尝试从匹配结果获取排量信息
    displacement = ''
    
    # 1. 从配置描述中提取排量，如 "(1.999L)"
    if match_result and match_result.get('config_desc'):
        m = re.search(r'\(([\d.]+L)\)', match_result['config_desc'])
        if m:
            displacement = m.group(1)
    
    # 2. 从发动机代码中提取排量，如 "VR5\n(1.999L)\n5MT"
    if not displacement and match_result and match_result.get('engine_code'):
        m = re.search(r'\(([\d.]+L)\)', match_result['engine_code'])
        if m:
            displacement = m.group(1)
    
    # 3. 从版本信息推断
    if not displacement and vehicle.get('version'):
        m = re.search(r'([\d.]+L)', vehicle['version'])
        if m:
            displacement = m.group(1)
    
    # 4. 从车型名称中推断（如 "2.0L 5MT"）
    if not displacement and vehicle.get('name_cn'):
        m = re.search(r'([\d.]+L)', vehicle['name_cn'])
        if m:
            displacement = m.group(1)
    
    parts = [p for p in [f'Wuling {model_code}', engine_code, displacement] if p]
    return ', '.join(parts)


if __name__ == '__main__':
    # 测试
    result = match_vehicle('LZW5030XXYLGHUG', 'FCA')
    print(result)
    
    result2 = match_vehicle('LZW1028SPY', 'FCA')
    print(result2)