mescloud/_apk_work/regen_mes_icons_current_sql.py

# -*- coding: utf-8 -*-
"""Regenerate restore SQL: only current (status=0) image per mes_icons id."""
from __future__ import print_function

from collections import Counter, defaultdict
import os
import sys

sys.path.insert(0, r"D:\IdeaProjects\mes-ahdy\mescloud\_apk_work")
from extract_mes_icons_files import (  # noqa: E402
    DUMP,
    OUT_DIR,
    find_offsets,
    iter_value_rows,
    parse_create_columns,
    read_until_stmt_end,
    row_to_dict,
    sql_quote,
    unquote,
)


def pick_latest(lst):
    return sorted(
        lst,
        key=lambda x: (
            x.get("update_date") or "",
            x.get("create_date") or "",
            x.get("id") or "",
        ),
        reverse=True,
    )[0]


def main():
    hits = find_offsets(DUMP, ["mes_icons", "js_sys_file_upload", "js_sys_file_entity"])
    create_cols = {}
    inserts = {}
    for off, name in hits:
        if name.startswith("CREATE:"):
            t = name.split(":", 1)[1]
            stmt = read_until_stmt_end(DUMP, off, max_bytes=200 * 1024)
            create_cols[t] = parse_create_columns(stmt)
        else:
            inserts[name] = off

    icons_cols = create_cols["mes_icons"]
    upload_cols = create_cols["js_sys_file_upload"]
    entity_cols = create_cols["js_sys_file_entity"]

    icons = [row_to_dict(f, icons_cols) for f in iter_value_rows(DUMP, inserts["mes_icons"])]
    icon_by_id = {i["id"]: i for i in icons}

    biz_type_idx = upload_cols.index("biz_type")
    icon_uploads = []
    for fields in iter_value_rows(DUMP, inserts["js_sys_file_upload"]):
        if unquote(fields[biz_type_idx]) == "mesIcons_image":
            icon_uploads.append(row_to_dict(fields, upload_cols))

    print("upload status:", Counter(u.get("status") for u in icon_uploads))

    by_biz = defaultdict(list)
    for u in icon_uploads:
        if (u.get("status") or "0") == "0":
            by_biz[u.get("biz_key")].append(u)

    current = {}
    for k, lst in by_biz.items():
        if k:
            current[k] = pick_latest(lst)

    print(
        "modules with active image=%d, multi-active=%d"
        % (len(current), sum(1 for v in by_biz.values() if len(v) > 1))
    )

    needed = set(u["file_id"] for u in current.values() if u.get("file_id"))
    file_id_idx = entity_cols.index("file_id")
    entities = {}
    for fields in iter_value_rows(DUMP, inserts["js_sys_file_entity"]):
        fid = unquote(fields[file_id_idx])
        if fid in needed:
            entities[fid] = row_to_dict(fields, entity_cols)
            if len(entities) == len(needed):
                break
    print("entities=%d" % len(entities))

    def disk_rel(e):
        if not e:
            return ""
        path = (e.get("file_path") or "").replace("\\", "/")
        md5 = e.get("file_md5") or ""
        ext = e.get("file_extension") or ""
        return ("%s%s.%s" % (path, md5, ext)) if ext else ("%s%s" % (path, md5))

    map2 = os.path.join(OUT_DIR, "mes_icons_image_map_current_only.txt")
    with open(map2, "w", encoding="utf-8") as wf:
        wf.write(
            "icon_id\ttitle\tmode\ticon_status\tupload_id\tfile_id\tfile_name\t"
            "file_path\tfile_md5\text\tdisk_rel\tupdate_date\n"
        )
        for biz_key in sorted(current.keys()):
            u = current[biz_key]
            e = entities.get(u.get("file_id") or {}, {})
            ic = icon_by_id.get(biz_key, {})
            wf.write(
                "\t".join(
                    [
                        biz_key,
                        ic.get("title") or "",
                        ic.get("mode") or "",
                        ic.get("status") or "",
                        u.get("id") or "",
                        u.get("file_id") or "",
                        u.get("file_name") or "",
                        (e.get("file_path") or "").replace("\\", "/"),
                        e.get("file_md5") or "",
                        e.get("file_extension") or "",
                        disk_rel(e),
                        u.get("update_date") or "",
                    ]
                )
                + "\n"
            )
    print("Wrote", map2)

    sql_path = os.path.join(OUT_DIR, "restore_mes_icons_image_mapping.sql")
    with open(sql_path, "w", encoding="utf-8") as wf:
        wf.write("-- ============================================================\n")
        wf.write("-- Restore PDA功能模块图片映射（仅当前有效图 status=0，每模块取最新一条）\n")
        wf.write("-- 来源: mes_cloud_20260919.sql\n")
        wf.write("-- 关联: biz_key=mes_icons.id, biz_type=mesIcons_image\n")
        wf.write("-- 务必同步拷贝磁盘文件到新服务器 {baseDir}/userfiles/\n")
        wf.write("-- ============================================================\n")
        wf.write("SET NAMES utf8mb4;\nSTART TRANSACTION;\n\n")
        wf.write("-- 1) 文件实体\n")
        for fid in sorted(needed):
            e = entities[fid]
            size = e.get("file_size")
            size_sql = size if size is not None else "NULL"
            wf.write(
                "INSERT INTO `js_sys_file_entity` "
                "(`file_id`,`file_md5`,`file_path`,`file_content_type`,`file_extension`,"
                "`file_size`,`file_meta`,`file_preview`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s) "
                "ON DUPLICATE KEY UPDATE "
                "`file_md5`=VALUES(`file_md5`),`file_path`=VALUES(`file_path`),"
                "`file_content_type`=VALUES(`file_content_type`),"
                "`file_extension`=VALUES(`file_extension`),`file_size`=VALUES(`file_size`),"
                "`file_meta`=VALUES(`file_meta`),`file_preview`=VALUES(`file_preview`);\n"
                % (
                    sql_quote(e.get("file_id")),
                    sql_quote(e.get("file_md5")),
                    sql_quote(e.get("file_path")),
                    sql_quote(e.get("file_content_type")),
                    sql_quote(e.get("file_extension")),
                    size_sql,
                    sql_quote(e.get("file_meta")),
                    sql_quote(e.get("file_preview")),
                )
            )

        wf.write("\n-- 2) 按模块 id 替换绑定\n")
        for biz_key in sorted(current.keys()):
            u = current[biz_key]
            ic = icon_by_id.get(biz_key, {})
            e = entities.get(u.get("file_id") or {}, {})
            wf.write(
                "\n-- id=%s title=%s mode=%s disk=%s\n"
                % (biz_key, ic.get("title") or "", ic.get("mode") or "", disk_rel(e))
            )
            wf.write(
                "DELETE FROM `js_sys_file_upload` "
                "WHERE `biz_type`='mesIcons_image' AND `biz_key`=%s;\n"
                % sql_quote(biz_key)
            )
            sortv = u.get("file_sort")
            sort_sql = sortv if sortv is not None else "NULL"
            wf.write(
                "INSERT INTO `js_sys_file_upload` "
                "(`id`,`file_id`,`file_name`,`file_type`,`file_sort`,`biz_key`,`biz_type`,"
                "`status`,`create_by`,`create_date`,`update_by`,`update_date`,`remarks`) "
                "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);\n"
                % (
                    sql_quote(u.get("id")),
                    sql_quote(u.get("file_id")),
                    sql_quote(u.get("file_name")),
                    sql_quote(u.get("file_type")),
                    sort_sql,
                    sql_quote(u.get("biz_key")),
                    sql_quote(u.get("biz_type")),
                    sql_quote(u.get("status") or "0"),
                    sql_quote(u.get("create_by") or "system"),
                    sql_quote(u.get("create_date")),
                    sql_quote(u.get("update_by") or "system"),
                    sql_quote(u.get("update_date")),
                    sql_quote(u.get("remarks")),
                )
            )

        missing = [
            i
            for i in icons
            if i.get("id") not in current and (i.get("status") or "0") == "0"
        ]
        if missing:
            wf.write("\n-- 老库正常模块但无有效图片:\n")
            for i in missing:
                wf.write(
                    "--   id=%s title=%s mode=%s\n"
                    % (i.get("id"), i.get("title"), i.get("mode"))
                )
        wf.write("\nCOMMIT;\n")
        wf.write(
            "\n-- 校验:\n"
            "-- SELECT u.biz_key, i.title, i.mode, u.file_name, e.file_path, e.file_md5, e.file_extension\n"
            "-- FROM js_sys_file_upload u\n"
            "-- LEFT JOIN js_sys_file_entity e ON e.file_id=u.file_id\n"
            "-- LEFT JOIN mes_icons i ON i.id=u.biz_key\n"
            "-- WHERE u.biz_type='mesIcons_image' AND u.status='0'\n"
            "-- ORDER BY i.title;\n"
        )
    print("Wrote", sql_path)

    files_list = os.path.join(OUT_DIR, "mes_icons_image_files_to_copy.txt")
    seen = set()
    with open(files_list, "w", encoding="utf-8") as wf:
        wf.write("# 拷到新服务器 {file.baseDir}/userfiles/ 下，保持相对路径\n")
        for u in current.values():
            rel = disk_rel(entities.get(u.get("file_id") or {}))
            if not rel or rel in seen:
                continue
            seen.add(rel)
            wf.write(rel + "\n")
    print("files=%d Wrote %s" % (len(seen), files_list))

    print("--- preview (first 15) ---")
    with open(map2, encoding="utf-8") as rf:
        for i, line in enumerate(rf):
            if i <= 15:
                print(line.rstrip())
            else:
                break
    print("DONE")


if __name__ == "__main__":
    main()