391 lines
13 KiB
TypeScript
391 lines
13 KiB
TypeScript
import { strFromU8, unzipSync } from "fflate";
|
||
|
||
export const RESOURCE_IMPORT_MAX_ROWS = 10_000;
|
||
export const RESOURCE_IMPORT_MAX_BYTES = 20 * 1024 * 1024;
|
||
|
||
export type ResourceImportRow = {
|
||
rowNumber: number;
|
||
platform: string;
|
||
nickname: string;
|
||
publicAccountId: string;
|
||
profileUrl: string;
|
||
ipLocation: string;
|
||
followers: number;
|
||
followersResolved: boolean;
|
||
gender: "" | "男" | "女";
|
||
bio: string;
|
||
tags: string[];
|
||
cooperationSource: string;
|
||
errors: string[];
|
||
};
|
||
|
||
const HEADER_ALIASES = {
|
||
profileUrl: ["账号链接", "账号主页", "账号主页链接", "主页链接"],
|
||
nickname: ["账号昵称", "账号名称", "昵称"],
|
||
publicAccountId: ["账号ID", "账号号", "小红书号", "抖音号"],
|
||
ipLocation: ["IP属地", "IP地址", "IP地区", "IP所在地"],
|
||
followers: ["粉丝数", "粉丝", "粉丝量"],
|
||
gender: ["性别"],
|
||
bio: ["简介", "账号简介", "个人简介"],
|
||
tags: ["标签", "账号标签"],
|
||
cooperationSource: ["合作来源", "资源来源", "历史合作来源"],
|
||
} as const;
|
||
|
||
type CanonicalHeader = keyof typeof HEADER_ALIASES;
|
||
|
||
function decodeXml(value: string) {
|
||
return value
|
||
.replace(/<[^>]+>/g, "")
|
||
.replace(/</g, "<")
|
||
.replace(/>/g, ">")
|
||
.replace(/"/g, '"')
|
||
.replace(/'/g, "'")
|
||
.replace(/&/g, "&")
|
||
.replace(/&#(\d+);/g, (_, code) => String.fromCodePoint(Number(code)))
|
||
.replace(/&#x([0-9a-f]+);/gi, (_, code) =>
|
||
String.fromCodePoint(Number.parseInt(code, 16)),
|
||
);
|
||
}
|
||
|
||
function textNodes(xml: string) {
|
||
return [...xml.matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g)]
|
||
.map((match) => decodeXml(match[1]))
|
||
.join("");
|
||
}
|
||
|
||
function columnIndex(reference: string) {
|
||
const letters = reference.match(/^[A-Z]+/i)?.[0]?.toUpperCase() ?? "";
|
||
let result = 0;
|
||
for (const letter of letters) result = result * 26 + letter.charCodeAt(0) - 64;
|
||
return Math.max(0, result - 1);
|
||
}
|
||
|
||
function parseWorksheet(xml: string, sharedStrings: string[]) {
|
||
const rows: string[][] = [];
|
||
for (const rowMatch of xml.matchAll(/<row\b([^>]*)>([\s\S]*?)<\/row>/g)) {
|
||
const rowAttributes = rowMatch[1];
|
||
const rowNumber = Number(rowAttributes.match(/\br="(\d+)"/)?.[1] ?? rows.length + 1);
|
||
const values: string[] = [];
|
||
for (const cellMatch of rowMatch[2].matchAll(
|
||
/<c\b([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g,
|
||
)) {
|
||
const attributes = cellMatch[1];
|
||
const body = cellMatch[2] ?? "";
|
||
const reference = attributes.match(/\br="([A-Z]+\d+)"/i)?.[1] ?? "A1";
|
||
const type = attributes.match(/\bt="([^"]+)"/)?.[1] ?? "";
|
||
const rawValue = body.match(/<v>([\s\S]*?)<\/v>/)?.[1] ?? "";
|
||
let value = "";
|
||
if (type === "s") value = sharedStrings[Number(rawValue)] ?? "";
|
||
else if (type === "inlineStr") value = textNodes(body);
|
||
else if (type === "b") value = rawValue === "1" ? "TRUE" : "FALSE";
|
||
else value = decodeXml(rawValue);
|
||
values[columnIndex(reference)] = value.trim();
|
||
}
|
||
while (rows.length < rowNumber - 1) rows.push([]);
|
||
rows[rowNumber - 1] = values;
|
||
}
|
||
return rows;
|
||
}
|
||
|
||
function parseXlsx(bytes: Uint8Array) {
|
||
const entries = unzipSync(bytes);
|
||
const sharedXml = entries["xl/sharedStrings.xml"]
|
||
? strFromU8(entries["xl/sharedStrings.xml"])
|
||
: "";
|
||
const sharedStrings = [...sharedXml.matchAll(/<si\b[^>]*>([\s\S]*?)<\/si>/g)].map(
|
||
(match) => textNodes(match[1]),
|
||
);
|
||
const sheets = Object.keys(entries)
|
||
.filter((name) => /^xl\/worksheets\/sheet\d+\.xml$/.test(name))
|
||
.sort((left, right) => left.localeCompare(right, undefined, { numeric: true }));
|
||
if (sheets.length === 0) throw new Error("Excel 中没有可读取的工作表");
|
||
return sheets.map((name) => parseWorksheet(strFromU8(entries[name]), sharedStrings));
|
||
}
|
||
|
||
function parseCsv(text: string) {
|
||
const rows: string[][] = [];
|
||
let row: string[] = [];
|
||
let cell = "";
|
||
let quoted = false;
|
||
for (let index = 0; index < text.length; index += 1) {
|
||
const char = text[index];
|
||
if (quoted) {
|
||
if (char === '"' && text[index + 1] === '"') {
|
||
cell += '"';
|
||
index += 1;
|
||
} else if (char === '"') quoted = false;
|
||
else cell += char;
|
||
} else if (char === '"') quoted = true;
|
||
else if (char === ",") {
|
||
row.push(cell.trim());
|
||
cell = "";
|
||
} else if (char === "\n" || char === "\r") {
|
||
if (char === "\r" && text[index + 1] === "\n") index += 1;
|
||
row.push(cell.trim());
|
||
if (row.some(Boolean)) rows.push(row);
|
||
row = [];
|
||
cell = "";
|
||
} else cell += char;
|
||
}
|
||
row.push(cell.trim());
|
||
if (row.some(Boolean)) rows.push(row);
|
||
return rows;
|
||
}
|
||
|
||
function normalizeHeader(value: string) {
|
||
return value
|
||
.replace(/[\s_\-（）()]/g, "")
|
||
.replace(/必填|选填/g, "")
|
||
.toLocaleLowerCase("zh-CN");
|
||
}
|
||
|
||
function canonicalHeader(value: string): CanonicalHeader | null {
|
||
const normalized = normalizeHeader(value);
|
||
for (const [key, aliases] of Object.entries(HEADER_ALIASES)) {
|
||
if (aliases.some((alias) => normalizeHeader(alias) === normalized)) {
|
||
return key as CanonicalHeader;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
function findHeader(rows: string[][]) {
|
||
for (let index = 0; index < Math.min(rows.length, 12); index += 1) {
|
||
const mapping = new Map<CanonicalHeader, number>();
|
||
rows[index].forEach((cell, column) => {
|
||
const header = canonicalHeader(cell);
|
||
if (header && !mapping.has(header)) mapping.set(header, column);
|
||
});
|
||
if (mapping.has("profileUrl")) {
|
||
return { rowIndex: index, mapping };
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
export function normalizeProfileUrl(value: string) {
|
||
const extracted = value.match(/https?:\/\/[^\s，,；;]+/i)?.[0] ?? value.trim();
|
||
if (!extracted) return "";
|
||
try {
|
||
const url = new URL(extracted);
|
||
if (!(["http:", "https:"].includes(url.protocol))) return "";
|
||
url.protocol = "https:";
|
||
url.hostname = url.hostname.toLowerCase();
|
||
url.search = "";
|
||
url.hash = "";
|
||
url.pathname = url.pathname.replace(/\/+$/, "") || "/";
|
||
return url.toString().replace(/\/$/, "");
|
||
} catch {
|
||
return "";
|
||
}
|
||
}
|
||
|
||
export function platformFromProfileUrl(profileUrl: string) {
|
||
if (!profileUrl) return "";
|
||
try {
|
||
const url = new URL(profileUrl);
|
||
if (
|
||
(url.hostname === "xiaohongshu.com" ||
|
||
url.hostname.endsWith(".xiaohongshu.com")) &&
|
||
/^\/user\/profile\/[^/]+/i.test(url.pathname)
|
||
) {
|
||
return "小红书";
|
||
}
|
||
if (
|
||
((url.hostname === "douyin.com" || url.hostname.endsWith(".douyin.com")) &&
|
||
/^\/user\/[^/]+/i.test(url.pathname)) ||
|
||
((url.hostname === "iesdouyin.com" ||
|
||
url.hostname.endsWith(".iesdouyin.com")) &&
|
||
/^\/share\/user\/[^/]+/i.test(url.pathname))
|
||
) {
|
||
return "抖音";
|
||
}
|
||
} catch {
|
||
// URL validation is reported by normalizeRows.
|
||
}
|
||
return "";
|
||
}
|
||
|
||
function valueAt(row: string[], mapping: Map<CanonicalHeader, number>, key: CanonicalHeader) {
|
||
const index = mapping.get(key);
|
||
return index === undefined ? "" : String(row[index] ?? "").trim();
|
||
}
|
||
|
||
export function parseResourceFollowers(value: string) {
|
||
const normalized = value.trim().replace(/[,，\s]/g, "").replace(/\+$/, "");
|
||
if (!normalized) return { value: 0, resolved: false, valid: true };
|
||
const match = normalized.match(/^(\d+(?:\.\d+)?)(万|w|W|千|k|K)?$/);
|
||
if (!match) return { value: 0, resolved: false, valid: false };
|
||
const multiplier =
|
||
match[2] === "万" || match[2]?.toLowerCase() === "w"
|
||
? 10_000
|
||
: match[2] === "千" || match[2]?.toLowerCase() === "k"
|
||
? 1_000
|
||
: 1;
|
||
return {
|
||
value: Math.round(Number(match[1]) * multiplier),
|
||
resolved: true,
|
||
valid: true,
|
||
};
|
||
}
|
||
|
||
export function normalizeResourceGender(value: string) {
|
||
const normalized = value.trim().toLocaleLowerCase("zh-CN");
|
||
if (!normalized || ["未知", "未填写", "待识别", "unknown"].includes(normalized)) {
|
||
return { value: "" as const, valid: true };
|
||
}
|
||
if (["男", "男性", "male", "m"].includes(normalized)) {
|
||
return { value: "男" as const, valid: true };
|
||
}
|
||
if (["女", "女性", "female", "f"].includes(normalized)) {
|
||
return { value: "女" as const, valid: true };
|
||
}
|
||
return { value: "" as const, valid: false };
|
||
}
|
||
|
||
export function normalizeResourceTags(value: string | string[]) {
|
||
const source = Array.isArray(value) ? value.join(",") : value;
|
||
return [
|
||
...new Set(
|
||
source
|
||
.split(/[,，、;；|]/)
|
||
.map((item) => item.trim().replace(/^#+/, ""))
|
||
.filter(Boolean),
|
||
),
|
||
];
|
||
}
|
||
|
||
export function resourceImportMissingFields(
|
||
row: Pick<
|
||
ResourceImportRow,
|
||
| "nickname"
|
||
| "publicAccountId"
|
||
| "ipLocation"
|
||
| "followersResolved"
|
||
| "gender"
|
||
| "bio"
|
||
| "tags"
|
||
>,
|
||
) {
|
||
const missing: string[] = [];
|
||
if (!row.nickname.trim()) missing.push("nickname");
|
||
if (!row.publicAccountId.trim()) missing.push("publicAccountId");
|
||
if (!row.ipLocation.trim() || row.ipLocation.trim() === "待识别") {
|
||
missing.push("ipLocation");
|
||
}
|
||
if (!row.followersResolved) missing.push("followers");
|
||
if (!row.gender) missing.push("gender");
|
||
if (!row.bio.trim()) missing.push("bio");
|
||
return missing;
|
||
}
|
||
|
||
function normalizeRows(rows: string[][]) {
|
||
const header = findHeader(rows);
|
||
if (!header) {
|
||
throw new Error("没有找到模板表头，请使用系统提供的导入模板");
|
||
}
|
||
const result: ResourceImportRow[] = [];
|
||
for (let index = header.rowIndex + 1; index < rows.length; index += 1) {
|
||
const source = rows[index];
|
||
if (!source.some((cell) => String(cell ?? "").trim())) continue;
|
||
const rawProfileUrl = valueAt(source, header.mapping, "profileUrl");
|
||
const profileUrl = normalizeProfileUrl(rawProfileUrl);
|
||
const platform = platformFromProfileUrl(profileUrl);
|
||
const rawFollowers = valueAt(source, header.mapping, "followers");
|
||
const parsedFollowers = parseResourceFollowers(rawFollowers);
|
||
const parsedGender = normalizeResourceGender(
|
||
valueAt(source, header.mapping, "gender"),
|
||
);
|
||
const tags = normalizeResourceTags(valueAt(source, header.mapping, "tags"));
|
||
const ipLocation = valueAt(source, header.mapping, "ipLocation");
|
||
const errors: string[] = [];
|
||
if (!rawProfileUrl) errors.push("账号主页不能为空");
|
||
else if (!profileUrl) errors.push("账号主页链接格式不正确");
|
||
else if (!platform) errors.push("当前自动解析仅支持小红书或抖音账号主页");
|
||
if (!parsedFollowers.valid) {
|
||
errors.push("粉丝数格式不正确，请填写数字或如 1.3万、10+");
|
||
}
|
||
if (!parsedGender.valid) {
|
||
errors.push("性别格式不正确，请填写男、女或留空");
|
||
}
|
||
if (tags.length > 5) {
|
||
errors.push("单个账号最多填写 5 个标签，请用逗号分隔");
|
||
}
|
||
if (/^\d+$/.test(ipLocation)) {
|
||
errors.push("IP属地格式不正确，请填写省份、地区或国家名称");
|
||
}
|
||
result.push({
|
||
rowNumber: index + 1,
|
||
platform,
|
||
nickname: valueAt(source, header.mapping, "nickname"),
|
||
publicAccountId: valueAt(source, header.mapping, "publicAccountId"),
|
||
profileUrl,
|
||
ipLocation,
|
||
followers: parsedFollowers.value,
|
||
followersResolved: parsedFollowers.resolved,
|
||
gender: parsedGender.value,
|
||
bio: valueAt(source, header.mapping, "bio"),
|
||
tags: tags.slice(0, 5),
|
||
cooperationSource: valueAt(source, header.mapping, "cooperationSource"),
|
||
errors,
|
||
});
|
||
}
|
||
if (result.length === 0) throw new Error("表格中没有可导入的账号数据");
|
||
if (result.length > RESOURCE_IMPORT_MAX_ROWS) {
|
||
throw new Error(`单次最多导入 ${RESOURCE_IMPORT_MAX_ROWS} 个账号`);
|
||
}
|
||
return result;
|
||
}
|
||
|
||
export function parseResourceImportFile(fileName: string, bytes: Uint8Array) {
|
||
const extension = fileName.toLocaleLowerCase().split(".").pop();
|
||
const workbooks =
|
||
extension === "csv"
|
||
? [parseCsv(new TextDecoder("utf-8").decode(bytes).replace(/^\uFEFF/, ""))]
|
||
: extension === "xlsx"
|
||
? parseXlsx(bytes)
|
||
: null;
|
||
if (!workbooks) throw new Error("仅支持 .xlsx 或 .csv 文件");
|
||
for (const rows of workbooks) {
|
||
if (findHeader(rows)) return normalizeRows(rows);
|
||
}
|
||
throw new Error("没有找到模板表头，请使用系统提供的导入模板");
|
||
}
|
||
|
||
function shortHash(value: string) {
|
||
let hash = 2166136261;
|
||
for (const character of value) {
|
||
hash ^= character.charCodeAt(0);
|
||
hash = Math.imul(hash, 16777619);
|
||
}
|
||
return Math.abs(hash >>> 0).toString(36);
|
||
}
|
||
|
||
export function resourcePlatformUid(row: Pick<ResourceImportRow, "platform" | "profileUrl" | "publicAccountId">) {
|
||
if (row.profileUrl) {
|
||
try {
|
||
const url = new URL(row.profileUrl);
|
||
const candidate =
|
||
url.pathname.match(/\/user\/profile\/([^/]+)/i)?.[1] ??
|
||
url.pathname.match(/\/(?:user|profile)\/([^/]+)/i)?.[1] ??
|
||
url.pathname.split("/").filter(Boolean).at(-1);
|
||
if (candidate && candidate.length >= 3) return candidate;
|
||
} catch {
|
||
// Validation already reports malformed profile links.
|
||
}
|
||
}
|
||
const identity = row.publicAccountId || row.profileUrl;
|
||
return `manual-${shortHash(`${row.platform}:${identity}`)}`;
|
||
}
|
||
|
||
export function mergeCooperationSources(existing: string, incoming: string) {
|
||
return [
|
||
...new Set(
|
||
`${existing}、${incoming}`
|
||
.split(/[、,，;；|]/)
|
||
.map((item) => item.trim())
|
||
.filter(Boolean),
|
||
),
|
||
].join("、");
|
||
}
|