import pandas as pd
from jira import JIRA

# 配置信息
EXCEL_PATH = 'bugs.xlsx'  # Excel文件路径
JIRA_SERVER = 'https://jira.com/'  # Jira服务器地址

# 字段映射配置 - 固定值部分
FIXED_FIELDS = {
    'issuetype': {'name': '故障'},
    '10801': {
        "self": "https:services/10504",
        "value": "合格性测试",
        "id": "10501"
    },
    '10802': {
        "self": "https://services/10504",
        "value": "功能错误",
        "id": "10504"
    },
    '10809': {
        "self": "https://services/10504",
        "value": "50%",
        "id": "10526"
    },
    '10805': '前提条件',
}

# 读取Excel数据
df = pd.read_excel(EXCEL_PATH, engine='openpyxl')
print(f"成功读取Excel文件，共{len(df)}条记录")
print("检测到的列:", list(df.columns))

# 定义动态字段映射
jira_fields_map = {
    "project": "项目",  # 需要根据实际修改excel
    "summary": "概要",
    "description": "描述",
    "10806": "操作步骤",
    "10807": "预期结果",
    "10808": "实际结果"
}

# 认证信息
username = '用户名'
password = '密码'

# 连接Jira
jira = JIRA(
    server=JIRA_SERVER,
    basic_auth=(username, password)
)

# 遍历每一行数据并创建issue
for index, row in df.iterrows():
    try:
        # 准备issue字段
        new_bug = {}

        # 添加固定字段
        new_bug.update(FIXED_FIELDS)

        # 添加动态字段
        for jira_field, excel_col in jira_fields_map.items():
            # 获取Excel中的值
            value = row[excel_col]

            # 处理NaN值（pandas的空值）
            if pd.isna(value):
                value = None
            # 特殊处理project字段
            if jira_field == 'project':
                # 项目字段需要以字典形式提供
                new_bug[jira_field] = {'key': str(value).strip()}
            # 特殊处理summary字段 - 添加固定前缀
            elif jira_field == 'summary':
                if value:
                    # 添加前缀【客户问题】
                    new_bug[jira_field] = "【问题】" + str(value)
                else:
                    # 如果标题为空，使用默认标题
                    new_bug[jira_field] = "【问题】无标题问题"
            else:
                # 其他字段直接赋值
                new_bug[jira_field] = value
            # # 添加到issue字段
            # new_bug[jira_field] = value
        # 创建issue
        issue = jira.create_issue(fields=new_bug)
        print(f'[{index + 1}/{len(df)}] 成功创建Bug: {issue.key}')

    except Exception as e:
        print(1)
        print(f'[{index + 1}/{len(df)}] 创建Bug失败: {str(e)}')
        # 打印当前行数据以便调试
        print(f"失败数据行: {index + 1}")
        for col in df.columns:
            print(f"{col}: {row[col]}")
        print("-" * 50)

print("所有记录处理完成！")