524 lines
17 KiB
TypeScript
524 lines
17 KiB
TypeScript
import Papa from "papaparse";
|
|
import type {
|
|
AgentWorkflowBrandStoreEntry,
|
|
AgentWorkflowCategoryLabels,
|
|
AgentWorkflowFourthCategoryEntry,
|
|
AgentWorkflowStoreEntry,
|
|
AgentWorkflowThirdCategoryEntry,
|
|
} from "@/types/agent";
|
|
import {
|
|
normalizeAgentWorkflowCategoryLabels,
|
|
} from "@/lib/agents/workflow-ui-config";
|
|
|
|
type SpreadsheetRecord = Record<string, unknown>;
|
|
|
|
export const AGENT_WORKFLOW_BRAND_IMPORT_ACCEPT = ".xlsx,.csv";
|
|
|
|
const BRAND_SHEET_NAME = "品牌树模板";
|
|
const INSTRUCTION_SHEET_NAME = "填写说明";
|
|
const TEMPLATE_FILE_NAME = "风格化品牌树导入模板";
|
|
|
|
const STATIC_HEADER_ALIASES = {
|
|
brandName: ["品牌", "品牌名称", "brand", "brandname"],
|
|
storeName: ["店铺", "店铺名称", "shop", "shopname", "store", "storename"],
|
|
thirdCategory: ["三级分类", "三级类目", "thirdcategory", "third"],
|
|
fourthCategory: ["四级分类", "四级类目", "fourthcategory", "fourth"],
|
|
fifthCategory: ["五级分类", "五级类目", "fifthcategory", "fifth"],
|
|
} as const;
|
|
|
|
const TEMPLATE_SAMPLE_ROWS = [
|
|
{
|
|
brandName: "朴美",
|
|
storeName: "天猫朴美家居旗舰店",
|
|
thirdCategory: "桌类",
|
|
fourthCategory: "餐桌",
|
|
fifthCategory: "岩板餐桌",
|
|
},
|
|
{
|
|
brandName: "朴美",
|
|
storeName: "天猫朴美家居旗舰店",
|
|
thirdCategory: "桌类",
|
|
fourthCategory: "茶几",
|
|
fifthCategory: "圆形茶几",
|
|
},
|
|
{
|
|
brandName: "朴美",
|
|
storeName: "天猫朴美家居旗舰店",
|
|
thirdCategory: "椅类",
|
|
fourthCategory: "餐椅",
|
|
fifthCategory: "软包餐椅",
|
|
},
|
|
{
|
|
brandName: "朴美",
|
|
storeName: "京东朴美家居旗舰店",
|
|
thirdCategory: "柜类",
|
|
fourthCategory: "电视柜",
|
|
fifthCategory: "落地电视柜",
|
|
},
|
|
];
|
|
|
|
function getTemplateHeaders(labels?: Partial<AgentWorkflowCategoryLabels>) {
|
|
const resolvedLabels = normalizeAgentWorkflowCategoryLabels(labels);
|
|
return {
|
|
brandName: resolvedLabels.firstCategory,
|
|
storeName: resolvedLabels.secondCategory,
|
|
thirdCategory: resolvedLabels.thirdCategory,
|
|
fourthCategory: resolvedLabels.fourthCategory,
|
|
fifthCategory: resolvedLabels.fifthCategory,
|
|
} as const;
|
|
}
|
|
|
|
function getHeaderAliases(labels?: Partial<AgentWorkflowCategoryLabels>) {
|
|
const headers = getTemplateHeaders(labels);
|
|
return {
|
|
brandName: Array.from(
|
|
new Set([headers.brandName, ...STATIC_HEADER_ALIASES.brandName].filter(Boolean))
|
|
),
|
|
storeName: Array.from(
|
|
new Set([headers.storeName, ...STATIC_HEADER_ALIASES.storeName].filter(Boolean))
|
|
),
|
|
thirdCategory: Array.from(
|
|
new Set([headers.thirdCategory, ...STATIC_HEADER_ALIASES.thirdCategory].filter(Boolean))
|
|
),
|
|
fourthCategory: Array.from(
|
|
new Set([headers.fourthCategory, ...STATIC_HEADER_ALIASES.fourthCategory].filter(Boolean))
|
|
),
|
|
fifthCategory: Array.from(
|
|
new Set([headers.fifthCategory, ...STATIC_HEADER_ALIASES.fifthCategory].filter(Boolean))
|
|
),
|
|
} as const;
|
|
}
|
|
|
|
function normalizeCell(value: unknown, limit = 64) {
|
|
const normalized = String(value ?? "")
|
|
.replace(/\s+/g, " ")
|
|
.trim();
|
|
if (!normalized) {
|
|
return "";
|
|
}
|
|
return normalized.slice(0, limit);
|
|
}
|
|
|
|
function normalizeHeader(value: unknown) {
|
|
return String(value ?? "")
|
|
.replace(/\s+/g, "")
|
|
.replace(/[_-]+/g, "")
|
|
.trim()
|
|
.toLowerCase();
|
|
}
|
|
|
|
function normalizeSpreadsheetCellValue(value: unknown) {
|
|
if (value == null) {
|
|
return "";
|
|
}
|
|
if (value instanceof Date) {
|
|
return value.toISOString();
|
|
}
|
|
if (typeof value !== "object") {
|
|
return value;
|
|
}
|
|
|
|
const cellObject = value as {
|
|
hyperlink?: unknown;
|
|
result?: unknown;
|
|
richText?: Array<{ text?: unknown }>;
|
|
text?: unknown;
|
|
};
|
|
|
|
if (Array.isArray(cellObject.richText)) {
|
|
return cellObject.richText.map((part) => String(part.text ?? "")).join("");
|
|
}
|
|
if (cellObject.result != null) {
|
|
return cellObject.result;
|
|
}
|
|
if (cellObject.text != null) {
|
|
return cellObject.text;
|
|
}
|
|
if (cellObject.hyperlink != null) {
|
|
return cellObject.hyperlink;
|
|
}
|
|
|
|
return String(value);
|
|
}
|
|
|
|
function triggerBlobDownload(blob: Blob, fileName: string) {
|
|
const url = URL.createObjectURL(blob);
|
|
const anchor = document.createElement("a");
|
|
anchor.href = url;
|
|
anchor.download = fileName;
|
|
document.body.appendChild(anchor);
|
|
anchor.click();
|
|
document.body.removeChild(anchor);
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
function matchColumn(record: SpreadsheetRecord, aliases: readonly string[]) {
|
|
const aliasSet = new Set(aliases.map((alias) => normalizeHeader(alias)));
|
|
return Object.keys(record).find((key) => aliasSet.has(normalizeHeader(key))) || null;
|
|
}
|
|
|
|
async function readSpreadsheetRecords(file: File) {
|
|
const extension = file.name.toLowerCase().split(".").pop() || "";
|
|
if (extension === "csv") {
|
|
const text = await file.text();
|
|
const parsed = Papa.parse<SpreadsheetRecord>(text, {
|
|
header: true,
|
|
skipEmptyLines: true,
|
|
transformHeader: (header) => normalizeCell(header, 128),
|
|
});
|
|
|
|
if (parsed.errors.length > 0) {
|
|
throw new Error(`CSV 解析失败：${parsed.errors[0]?.message || "未知错误"}`);
|
|
}
|
|
|
|
return parsed.data;
|
|
}
|
|
|
|
const { Workbook } = await import("exceljs");
|
|
const workbook = new Workbook();
|
|
const buffer = await file.arrayBuffer();
|
|
await workbook.xlsx.load(buffer);
|
|
|
|
const worksheet = workbook.worksheets[0];
|
|
if (!worksheet) {
|
|
throw new Error("表格中没有可读取的工作表");
|
|
}
|
|
|
|
const rawHeaderValues = worksheet.getRow(1).values;
|
|
const headerValues: unknown[] = Array.isArray(rawHeaderValues) ? rawHeaderValues.slice(1) : [];
|
|
const headers = headerValues.map((value) =>
|
|
normalizeCell(normalizeSpreadsheetCellValue(value), 128)
|
|
);
|
|
const records: SpreadsheetRecord[] = [];
|
|
|
|
worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
|
|
if (rowNumber === 1) {
|
|
return;
|
|
}
|
|
|
|
const record: SpreadsheetRecord = {};
|
|
let hasValue = false;
|
|
|
|
headers.forEach((header, index) => {
|
|
if (!header) {
|
|
return;
|
|
}
|
|
const value = normalizeSpreadsheetCellValue(row.getCell(index + 1).value);
|
|
record[header] = value;
|
|
if (normalizeCell(value, 2048)) {
|
|
hasValue = true;
|
|
}
|
|
});
|
|
|
|
if (hasValue) {
|
|
records.push(record);
|
|
}
|
|
});
|
|
|
|
return records;
|
|
}
|
|
|
|
function createBrandEntry(brandName: string, sortOrder: number): AgentWorkflowBrandStoreEntry {
|
|
return {
|
|
brandName,
|
|
stores: [],
|
|
storeEntries: [],
|
|
sortOrder,
|
|
};
|
|
}
|
|
|
|
function createStoreEntry(storeName: string, sortOrder: number): AgentWorkflowStoreEntry {
|
|
return {
|
|
storeName,
|
|
thirdCategories: [],
|
|
thirdCategoryEntries: [],
|
|
sortOrder,
|
|
};
|
|
}
|
|
|
|
function createThirdCategoryEntry(
|
|
categoryName: string,
|
|
sortOrder: number
|
|
): AgentWorkflowThirdCategoryEntry {
|
|
return {
|
|
categoryName,
|
|
fourthCategories: [],
|
|
fourthCategoryEntries: [],
|
|
sortOrder,
|
|
};
|
|
}
|
|
|
|
function createFourthCategoryEntry(
|
|
categoryName: string,
|
|
sortOrder: number
|
|
): AgentWorkflowFourthCategoryEntry {
|
|
return {
|
|
categoryName,
|
|
fifthCategories: [],
|
|
sortOrder,
|
|
};
|
|
}
|
|
|
|
export interface ParsedAgentWorkflowBrandImportResult {
|
|
entries: AgentWorkflowBrandStoreEntry[];
|
|
rowCount: number;
|
|
}
|
|
|
|
export async function downloadAgentWorkflowBrandImportTemplate(
|
|
labels?: Partial<AgentWorkflowCategoryLabels>
|
|
) {
|
|
const { Workbook } = await import("exceljs");
|
|
const templateHeaders = getTemplateHeaders(labels);
|
|
const resolvedLabels = normalizeAgentWorkflowCategoryLabels(labels);
|
|
|
|
const workbook = new Workbook();
|
|
const sheet = workbook.addWorksheet(BRAND_SHEET_NAME);
|
|
sheet.columns = [
|
|
{ header: templateHeaders.brandName, key: "brandName", width: 20 },
|
|
{ header: templateHeaders.storeName, key: "storeName", width: 28 },
|
|
{ header: templateHeaders.thirdCategory, key: "thirdCategory", width: 18 },
|
|
{ header: templateHeaders.fourthCategory, key: "fourthCategory", width: 18 },
|
|
{ header: templateHeaders.fifthCategory, key: "fifthCategory", width: 18 },
|
|
];
|
|
sheet.getRow(1).font = { bold: true };
|
|
sheet.views = [{ state: "frozen", ySplit: 1 }];
|
|
|
|
TEMPLATE_SAMPLE_ROWS.forEach((row) => {
|
|
sheet.addRow(row);
|
|
});
|
|
|
|
const instructionSheet = workbook.addWorksheet(INSTRUCTION_SHEET_NAME);
|
|
instructionSheet.columns = [
|
|
{ header: "字段", key: "field", width: 18 },
|
|
{ header: "是否必填", key: "required", width: 12 },
|
|
{ header: "说明", key: "description", width: 54 },
|
|
{ header: "示例", key: "example", width: 28 },
|
|
];
|
|
instructionSheet.getRow(1).font = { bold: true };
|
|
instructionSheet.views = [{ state: "frozen", ySplit: 1 }];
|
|
instructionSheet.addRows([
|
|
{
|
|
field: resolvedLabels.firstCategory,
|
|
required: "是",
|
|
description: `每一行都必须填写${resolvedLabels.firstCategory}名称；相同${resolvedLabels.firstCategory}会自动合并。`,
|
|
example: "朴美",
|
|
},
|
|
{
|
|
field: resolvedLabels.secondCategory,
|
|
required: "否",
|
|
description: `不填时只导入${resolvedLabels.firstCategory}；填写后会挂到对应${resolvedLabels.firstCategory}下。`,
|
|
example: "天猫朴美家居旗舰店",
|
|
},
|
|
{
|
|
field: resolvedLabels.thirdCategory,
|
|
required: "否",
|
|
description: `填写后会挂到对应${resolvedLabels.secondCategory}下；如果不填，${resolvedLabels.fourthCategory}/${resolvedLabels.fifthCategory}也必须留空。`,
|
|
example: "桌类",
|
|
},
|
|
{
|
|
field: resolvedLabels.fourthCategory,
|
|
required: "否",
|
|
description: `填写后会挂到对应${resolvedLabels.thirdCategory}下；如果不填，${resolvedLabels.fifthCategory}也必须留空。`,
|
|
example: "餐桌",
|
|
},
|
|
{
|
|
field: resolvedLabels.fifthCategory,
|
|
required: "否",
|
|
description: `填写后会挂到对应${resolvedLabels.fourthCategory}下。`,
|
|
example: "岩板餐桌",
|
|
},
|
|
]);
|
|
|
|
const buffer = await workbook.xlsx.writeBuffer();
|
|
const blob = new Blob([buffer], {
|
|
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|
});
|
|
triggerBlobDownload(blob, `${TEMPLATE_FILE_NAME}.xlsx`);
|
|
}
|
|
|
|
export async function parseAgentWorkflowBrandImportFile(
|
|
file: File,
|
|
labels?: Partial<AgentWorkflowCategoryLabels>
|
|
): Promise<ParsedAgentWorkflowBrandImportResult> {
|
|
const templateHeaders = getTemplateHeaders(labels);
|
|
const headerAliases = getHeaderAliases(labels);
|
|
const resolvedLabels = normalizeAgentWorkflowCategoryLabels(labels);
|
|
const extension = file.name.toLowerCase().split(".").pop() || "";
|
|
if (!["xlsx", "csv"].includes(extension)) {
|
|
throw new Error("仅支持 xlsx、csv 文件");
|
|
}
|
|
|
|
const records = await readSpreadsheetRecords(file);
|
|
if (records.length === 0) {
|
|
throw new Error("表格中没有有效数据");
|
|
}
|
|
|
|
const firstRecord = records[0];
|
|
const brandColumn =
|
|
matchColumn(firstRecord, headerAliases.brandName) ||
|
|
Object.keys(firstRecord).find((key) => normalizeHeader(key) === normalizeHeader(templateHeaders.brandName));
|
|
if (!brandColumn) {
|
|
throw new Error(`未找到${templateHeaders.brandName}列，请使用模板文件填写`);
|
|
}
|
|
|
|
const storeColumn = matchColumn(firstRecord, headerAliases.storeName);
|
|
const thirdColumn = matchColumn(firstRecord, headerAliases.thirdCategory);
|
|
const fourthColumn = matchColumn(firstRecord, headerAliases.fourthCategory);
|
|
const fifthColumn = matchColumn(firstRecord, headerAliases.fifthCategory);
|
|
|
|
const brandEntries: AgentWorkflowBrandStoreEntry[] = [];
|
|
const brandLookup = new Map<string, AgentWorkflowBrandStoreEntry>();
|
|
let effectiveRowCount = 0;
|
|
|
|
const getOrCreateBrand = (brandName: string) => {
|
|
const lookupKey = brandName.toLowerCase();
|
|
const existing = brandLookup.get(lookupKey);
|
|
if (existing) {
|
|
return existing;
|
|
}
|
|
const nextEntry = createBrandEntry(brandName, brandEntries.length);
|
|
brandEntries.push(nextEntry);
|
|
brandLookup.set(lookupKey, nextEntry);
|
|
return nextEntry;
|
|
};
|
|
|
|
const getOrCreateStore = (brandEntry: AgentWorkflowBrandStoreEntry, storeName: string) => {
|
|
const storeEntries = brandEntry.storeEntries || [];
|
|
const existing = storeEntries.find(
|
|
(entry) => entry.storeName.toLowerCase() === storeName.toLowerCase()
|
|
);
|
|
if (existing) {
|
|
return existing;
|
|
}
|
|
const nextEntry = createStoreEntry(storeName, storeEntries.length);
|
|
brandEntry.storeEntries = [...storeEntries, nextEntry];
|
|
brandEntry.stores = brandEntry.storeEntries.map((entry) => entry.storeName);
|
|
return nextEntry;
|
|
};
|
|
|
|
const getOrCreateThirdCategory = (storeEntry: AgentWorkflowStoreEntry, categoryName: string) => {
|
|
const thirdEntries = storeEntry.thirdCategoryEntries || [];
|
|
const existing = thirdEntries.find(
|
|
(entry) => entry.categoryName.toLowerCase() === categoryName.toLowerCase()
|
|
);
|
|
if (existing) {
|
|
return existing;
|
|
}
|
|
const nextEntry = createThirdCategoryEntry(categoryName, thirdEntries.length);
|
|
storeEntry.thirdCategoryEntries = [...thirdEntries, nextEntry];
|
|
storeEntry.thirdCategories = storeEntry.thirdCategoryEntries.map((entry) => entry.categoryName);
|
|
return nextEntry;
|
|
};
|
|
|
|
const getOrCreateFourthCategory = (
|
|
thirdEntry: AgentWorkflowThirdCategoryEntry,
|
|
categoryName: string
|
|
) => {
|
|
const fourthEntries = thirdEntry.fourthCategoryEntries || [];
|
|
const existing = fourthEntries.find(
|
|
(entry) => entry.categoryName.toLowerCase() === categoryName.toLowerCase()
|
|
);
|
|
if (existing) {
|
|
return existing;
|
|
}
|
|
const nextEntry = createFourthCategoryEntry(categoryName, fourthEntries.length);
|
|
thirdEntry.fourthCategoryEntries = [...fourthEntries, nextEntry];
|
|
thirdEntry.fourthCategories = thirdEntry.fourthCategoryEntries.map((entry) => entry.categoryName);
|
|
return nextEntry;
|
|
};
|
|
|
|
records.forEach((record, index) => {
|
|
const lineNumber = index + 2;
|
|
const brandName = normalizeCell(record[brandColumn]);
|
|
const storeName = storeColumn ? normalizeCell(record[storeColumn]) : "";
|
|
const thirdCategory = thirdColumn ? normalizeCell(record[thirdColumn]) : "";
|
|
const fourthCategory = fourthColumn ? normalizeCell(record[fourthColumn]) : "";
|
|
const fifthCategory = fifthColumn ? normalizeCell(record[fifthColumn]) : "";
|
|
|
|
const hasAnyValue = Boolean(brandName || storeName || thirdCategory || fourthCategory || fifthCategory);
|
|
if (!hasAnyValue) {
|
|
return;
|
|
}
|
|
|
|
if (!brandName) {
|
|
throw new Error(`第 ${lineNumber} 行缺少${resolvedLabels.firstCategory}名称`);
|
|
}
|
|
if (!storeName && (thirdCategory || fourthCategory || fifthCategory)) {
|
|
throw new Error(
|
|
`第 ${lineNumber} 行缺少${resolvedLabels.secondCategory}名称，不能直接填写分类`
|
|
);
|
|
}
|
|
if (!thirdCategory && (fourthCategory || fifthCategory)) {
|
|
throw new Error(
|
|
`第 ${lineNumber} 行缺少${resolvedLabels.thirdCategory}，不能直接填写${resolvedLabels.fourthCategory}或${resolvedLabels.fifthCategory}`
|
|
);
|
|
}
|
|
if (!fourthCategory && fifthCategory) {
|
|
throw new Error(
|
|
`第 ${lineNumber} 行缺少${resolvedLabels.fourthCategory}，不能直接填写${resolvedLabels.fifthCategory}`
|
|
);
|
|
}
|
|
|
|
effectiveRowCount += 1;
|
|
const brandEntry = getOrCreateBrand(brandName);
|
|
if (!storeName) {
|
|
return;
|
|
}
|
|
|
|
const storeEntry = getOrCreateStore(brandEntry, storeName);
|
|
if (!thirdCategory) {
|
|
return;
|
|
}
|
|
|
|
const thirdEntry = getOrCreateThirdCategory(storeEntry, thirdCategory);
|
|
if (!fourthCategory) {
|
|
return;
|
|
}
|
|
|
|
const fourthEntry = getOrCreateFourthCategory(thirdEntry, fourthCategory);
|
|
if (!fifthCategory) {
|
|
return;
|
|
}
|
|
|
|
if (
|
|
!fourthEntry.fifthCategories.some(
|
|
(item) => item.toLowerCase() === fifthCategory.toLowerCase()
|
|
)
|
|
) {
|
|
fourthEntry.fifthCategories = [...fourthEntry.fifthCategories, fifthCategory];
|
|
}
|
|
});
|
|
|
|
if (effectiveRowCount === 0) {
|
|
throw new Error("表格中没有可导入的有效行");
|
|
}
|
|
|
|
return {
|
|
entries: brandEntries.map((brandEntry, brandIndex) => ({
|
|
...brandEntry,
|
|
stores: (brandEntry.storeEntries || []).map((storeEntry) => storeEntry.storeName),
|
|
storeEntries: (brandEntry.storeEntries || []).map((storeEntry, storeIndex) => ({
|
|
...storeEntry,
|
|
thirdCategories: (storeEntry.thirdCategoryEntries || []).map((thirdEntry) => thirdEntry.categoryName),
|
|
thirdCategoryEntries: (storeEntry.thirdCategoryEntries || []).map((thirdEntry, thirdIndex) => ({
|
|
...thirdEntry,
|
|
fourthCategories: (thirdEntry.fourthCategoryEntries || []).map(
|
|
(fourthEntry) => fourthEntry.categoryName
|
|
),
|
|
fourthCategoryEntries: (thirdEntry.fourthCategoryEntries || []).map(
|
|
(fourthEntry, fourthIndex) => ({
|
|
...fourthEntry,
|
|
fifthCategories: Array.from(new Set((fourthEntry.fifthCategories || []).filter(Boolean))),
|
|
sortOrder: fourthIndex,
|
|
})
|
|
),
|
|
sortOrder: thirdIndex,
|
|
})),
|
|
sortOrder: storeIndex,
|
|
})),
|
|
sortOrder: brandIndex,
|
|
})),
|
|
rowCount: effectiveRowCount,
|
|
};
|
|
}
|