sj_nmtybt_server/scripts/cleanup-pctj-dirty.js

/**
 * 清理批次统计脏数据（含软删行），避免建唯一索引时仍撞到历史重复键。
 *
 * 策略：
 * 1) 拉取 states 含 -1 的全部行
 * 2) state=-1 或 c_batch_id 以 REMOVED| 开头：尝试物理删 / 销毁
 * 3) 同一 (c_batch_id, c_pay_bank) 多条：保留分最高的一条，其余删除
 * 4) 删除失败则把 c_batch_id 改成 REMOVED|{id}，不再占用业务键
 *
 * 用法：node scripts/cleanup-pctj-dirty.js
 */
const http = require("http");
const qs = require("querystring");

const HOST = "121.43.55.7";
const PORT = 2101;
const COL = { columnId: 1846, modelId: 1969 };

function request(method, urlPath, fields, token) {
  return new Promise((resolve, reject) => {
    const body = qs.stringify(fields || {});
    const headers = {
      "Content-Type": "application/x-www-form-urlencoded",
      "Content-Length": Buffer.byteLength(body),
    };
    if (token) {
      headers.Token = token;
      headers.token = token;
    }
    const req = http.request(
      { hostname: HOST, port: PORT, path: urlPath, method: method || "POST", headers },
      (res) => {
        let data = "";
        res.on("data", (c) => (data += c));
        res.on("end", () => {
          try {
            resolve(JSON.parse(data));
          } catch (e) {
            resolve({ raw: String(data).slice(0, 300), status: res.statusCode });
          }
        });
      }
    );
    req.on("error", reject);
    if (body) req.write(body);
    req.end();
  });
}

async function login() {
  const res = await request("POST", "/proxy_oauth/user/login", {
    userName: "user_liu",
    password: "WE176852439@lmx",
    clientId: "1",
  });
  if (res.code != 200) throw new Error("login fail: " + JSON.stringify(res));
  return res.message;
}

async function listAllStates(token) {
  const rows = [];
  for (let page = 0; page < 80; page++) {
    const res = await request(
      "POST",
      "/proxy_dms/content/selectContentList",
      {
        columnId: String(COL.columnId),
        modelId: String(COL.modelId),
        page: String(page),
        pageSize: "100",
        states: "0,1,2,3,-1,4",
      },
      token
    );
    if (res.code == 202) break;
    if (res.code != 200) throw new Error("list fail: " + JSON.stringify(res).slice(0, 300));
    const data = (res.content && res.content.data) || [];
    rows.push.apply(rows, data);
    if (data.length < 100) break;
  }
  return rows;
}

async function hardDelete(token, id) {
  return request(
    "DELETE",
    "/proxy_dms/content/delContentById",
    { contentId: id, columnId: String(COL.columnId) },
    token
  );
}

async function destroy(token, id) {
  return request(
    "POST",
    "/proxy_dms/content/updateAudit",
    {
      columnId: String(COL.columnId),
      id,
      state: "4",
      auditorName: "pctj-cleanup",
      auditorComment: "批次统计脏数据清理",
    },
    token
  );
}

async function neutralize(token, id) {
  return request(
    "POST",
    "/proxy_dms/content/updateContent",
    {
      columnId: String(COL.columnId),
      modelId: String(COL.modelId),
      content: JSON.stringify({
        id,
        state: -1,
        c_batch_id: "REMOVED|" + id,
        c_remarks: "PCTJ_CLEANED|" + Date.now(),
      }),
    },
    token
  );
}

async function removeOne(token, id) {
  const hard = await hardDelete(token, id);
  if (hard && hard.code == 200) return { ok: true, mode: "delete" };
  const soft = await destroy(token, id);
  if (soft && soft.code == 200) return { ok: true, mode: "destroy", hard };
  const neu = await neutralize(token, id);
  if (neu && neu.code == 200) return { ok: true, mode: "neutralize", hard, soft };
  return { ok: false, hard, soft, neu };
}

function score(row) {
  const should = Number(row.c_should_pay_count || 0) + Number(row.c_actual_pay_count || 0);
  const amt = Number(row.c_should_pay_amount || 0) + Number(row.c_actual_pay_amount || 0);
  const t = Date.parse(row.update_time || row.create_time || 0) || 0;
  const live = String(row.state) === "0" ? 100000 : 0;
  return live + should * 1000 + amt + t / 1e12;
}

(async () => {
  const token = await login();
  const rows = await listAllStates(token);
  console.log("全状态统计行=" + rows.length);

  const targets = new Map(); // id -> reason

  // A. 软删/销毁态但仍占用真实 batch_id
  for (const r of rows) {
    const st = String(r.state);
    const bid = String(r.c_batch_id || "");
    if ((st === "-1" || st === "4") && bid && !bid.startsWith("REMOVED|")) {
      targets.set(r.id, "soft_deleted_live_key state=" + st + " batch=" + bid + " bank=" + (r.c_pay_bank || ""));
    }
  }

  // B. 同一 (batch_id, pay_bank) 重复（含各 state）
  const groups = new Map();
  for (const r of rows) {
    const bid = String(r.c_batch_id || "").trim();
    if (!bid || bid.startsWith("REMOVED|")) continue;
    if (targets.has(r.id)) continue; // 已计划清理
    const bank = String(r.c_pay_bank || "").trim() || "(空)";
    const key = bid + "||" + bank;
    if (!groups.has(key)) groups.set(key, []);
    groups.get(key).push(r);
  }
  for (const [key, list] of groups) {
    if (list.length <= 1) continue;
    list.sort((a, b) => score(b) - score(a));
    console.log("重复组 " + key + " x" + list.length + " 保留 " + list[0].id);
    for (let i = 1; i < list.length; i++) {
      targets.set(list[i].id, "dup_of " + key + " keep=" + list[0].id);
    }
  }

  console.log("待清理条数=" + targets.size);
  let ok = 0;
  let fail = 0;
  for (const [id, reason] of targets) {
    const r = await removeOne(token, id);
    if (r.ok) {
      ok++;
      console.log("OK " + r.mode + " " + id + " :: " + reason);
    } else {
      fail++;
      console.log("FAIL " + id + " :: " + reason + " " + JSON.stringify(r).slice(0, 220));
    }
  }

  const after = await listAllStates(token);
  const liveKeys = after.filter(
    (r) => String(r.c_batch_id || "") && !String(r.c_batch_id).startsWith("REMOVED|")
  );
  const byBatch = new Map();
  for (const r of liveKeys) {
    const b = r.c_batch_id;
    byBatch.set(b, (byBatch.get(b) || 0) + 1);
  }
  const multi = [...byBatch.entries()].filter(([, n]) => n > 1);
  console.log("清理结果 ok=" + ok + " fail=" + fail);
  console.log("剩余占用业务键行=" + liveKeys.length + "；同 batch_id 多行组数=" + multi.length);
  for (const [b, n] of multi.slice(0, 15)) {
    const detail = liveKeys
      .filter((r) => r.c_batch_id === b)
      .map((r) => "bank=" + (r.c_pay_bank || "") + "/state=" + r.state)
      .join("; ");
    console.log("  " + b + " x" + n + " [" + detail + "]");
  }
  console.log(
    "说明：同 batch 多银行（合计/农商/农行）是正常数据；唯一索引请用 (c_batch_id, c_pay_bank)，不要单独 UNIQUE(c_batch_id)。"
  );
})().catch((e) => {
  console.error(e);
  process.exit(1);
});