recycle-docker/tools/init_price_option_factor.py

# import pymysql

# DB = dict(
#     host="127.0.0.1",
#     user="root",
#     password="root",
#     database="recycle",
#     charset="utf8mb4"
# )

# GROUP_MAP = {
#     "账号情况": "account",
#     "机身外观": "body",
#     "屏幕外观": "screen_appearance",
#     "屏幕显示": "screen_display",
#     "触摸": "touch",
#     "拍摄": "camera",
#     "WiFi/蓝牙": "wifi",
#     "通话": "call",
#     "面容/指纹": "biometrics",
#     "其他功能问题（可多选或不选）": "other_function",
#     "整机维修（可多选或不选）": "repair",
#     "转轴情况": "hinge",
# }

# SPECIAL_OPTION_IDS = {
#     100560,   # 已开启丢失模式
# }

# # `repair_level` tinyint DEFAULT '0' COMMENT '维修分级：0非维修 1次要 2重要 3核心',
# repair_level_map = {
#     "core": 3,
#     "important": 2,
#     "minor": 1
# }
# REPAIR_LEVEL_MAP = {
#     # core
#     100384: repair_level_map["core"],

#     # important
#     100346: repair_level_map["important"],
#     100530: repair_level_map["important"],
#     100382: repair_level_map["important"],
#     100383: repair_level_map["important"],

#     # minor
#     100381: repair_level_map["minor"],
#     100410: repair_level_map["minor"],
#     100529: repair_level_map["minor"],
# }

# def calc_severity(factor: float) -> int:
#     if factor >= 0.40:
#         return 3
#     if factor >= 0.20:
#         return 2
#     if factor > 0:
#         return 1
#     return 0


# def main():

#     conn = pymysql.connect(**DB)
#     cur = conn.cursor(pymysql.cursors.DictCursor)

#     # 取出所有 option + factor + 维度名称
#     cur.execute("""
#         SELECT
#             f.id,
#             f.option_id,
#             f.factor,
#             o.option_key_name
#         FROM price_option_factor f
#         LEFT JOIN release_option o
#             ON f.option_id = o.option_id
#     """)

#     rows = cur.fetchall()

#     print("rows:", len(rows))

#     for r in rows:

#         option_id = int(r["option_id"])
#         factor = float(r["factor"] or 0)
#         option_key_name = r["option_key_name"]

#         group_code = GROUP_MAP.get(option_key_name, "other")
#         severity_level = calc_severity(factor)

#         # 次瑕疵折扣系数（统一先给 0.4）
#         sub_weight = 0.4

#         is_special = 1 if option_id in SPECIAL_OPTION_IDS else 0

#         repair_level = REPAIR_LEVEL_MAP.get(option_id)

#         cur.execute("""
#             UPDATE price_option_factor
#             SET
#                 group_code = %s,
#                 severity_level = %s,
#                 sub_weight = %s,
#                 is_special = %s,
#                 repair_level = %s
#             WHERE id = %s
#         """, (
#             group_code,
#             severity_level,
#             sub_weight,
#             is_special,
#             repair_level,
#             r["id"]
#         ))

#     conn.commit()
#     cur.close()
#     conn.close()

#     print("done.")



import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    user="root",
    password="root",
    database="recycle",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor
)

GROUP_MAP = {
    "boot": ["开机情况"],

    "body": ["机身外观"],

    # 屏幕综合组 = 外观 + 显示 + 触摸
    "screen": ["屏幕外观", "屏幕显示", "触摸"],

    "account": ["账号情况"],

    "function": ["WiFi/蓝牙", "面容/指纹", "拍摄", "通话"],

    "hinge": ["转轴情况"],

    "repair": ["整机维修（可多选或不选）"],

    "other_special": ["其他功能问题（可多选或不选）"]
}

repair_level_map = {
    "core": 3,
    "important": 2,
    "minor": 1
}


def get_group_code(row):
    for g, keys in GROUP_MAP.items():
        if row["option_key_name"] in keys:
            return g
    return None


def calc_severity(factor):
    if factor is None:
        return 0

    f = float(factor)

    if f <= 0:
        return 0
    elif f < 0.06:
        return 1
    elif f < 0.12:
        return 2
    elif f < 0.25:
        return 3
    elif f < 0.4:
        return 4
    else:
        return 5


def calc_repair_level(option_name):
    if "无维修" in option_name:
        return 0

    if "主板" in option_name:
        return "core"

    if "屏幕维修" in option_name:
        return "important"

    if "更换原厂屏" in option_name:
        return "important"

    if "摄像头" in option_name:
        return "important"

    if "电池" in option_name:
        return "minor"

    if "后壳" in option_name:
        return "minor"

    if "配件" in option_name:
        return "minor"

    return None


def is_special(row):
    return row["option_key_name"] == "其他功能问题（可多选或不选）"

def main():
    with conn.cursor() as cur:

        cur.execute("""
            SELECT
                r.option_id,
                r.option_name,
                r.option_key_name,
                p.factor
            FROM release_option r
            JOIN price_option_factor p
              ON r.option_id = p.option_id
        """)

        rows = cur.fetchall()

        for r in rows:

            group_code = get_group_code(r)
            severity = calc_severity(r["factor"])
            sub_weight = 0.5 if group_code in ("screen", "body", "function", "hinge") else None
            special = 1 if is_special(r) else 0

            repair_level = None
            if group_code == "repair":
                repair_level = calc_repair_level(r["option_name"])
                if repair_level:
                    repair_level = repair_level_map[repair_level]
            cur.execute("""
                UPDATE price_option_factor
                SET
                    group_code = %s,
                    severity_level = %s,
                    sub_weight = %s,
                    is_special = %s,
                    repair_level = %s
                WHERE option_id = %s
            """, (
                group_code,
                severity,
                sub_weight,
                special,
                repair_level,
                r["option_id"]
            ))

        conn.commit()

    print("done.")


if __name__ == "__main__":
    main()