218 lines
7.3 KiB
JavaScript
218 lines
7.3 KiB
JavaScript
const fs = require('fs');
|
||
const XLSX = require('xlsx');
|
||
|
||
const files = [
|
||
{ file: '大荤.xlsx', cat: '大荤' },
|
||
{ file: '小荤.xlsx', cat: '小荤' },
|
||
{ file: '素菜.xlsx', cat: '素菜' },
|
||
];
|
||
|
||
const TRAIL_RE = /^(.+?)\s*(\d+(?:\.\d+)?)\s*(斤|只|包|瓶|盒|袋|根)$/;
|
||
const LEAD_NUM_RE = /^(\d+(?:\.\d+)?)\s*(斤|只|条|包|瓶|盒)?(.+)$/;
|
||
const LEAD_CN_RE = /^([一二三四五六七八九十两]+)\s*(条|斤|只|包)?(.+)$/;
|
||
const CN_NUM = { 一: 1, 二: 2, 两: 2, 三: 3, 四: 4, 五: 5, 六: 6, 七: 7, 八: 8, 九: 9, 十: 10 };
|
||
|
||
function escapeSql(s) {
|
||
return String(s).replace(/\\/g, '\\\\').replace(/'/g, "''");
|
||
}
|
||
|
||
function cleanName(raw) {
|
||
return String(raw || '')
|
||
.replace(/（/g, '(')
|
||
.replace(/）/g, ')')
|
||
.replace(/\s+/g, ' ')
|
||
.trim()
|
||
.replace(/\(斤\)/g, '')
|
||
.replace(/\(盒\)/g, '')
|
||
.replace(/\(块\)/g, '')
|
||
.trim();
|
||
}
|
||
|
||
function parseMaterialCell(nameRaw, qtyRaw) {
|
||
let name = String(nameRaw || '').trim();
|
||
if (!name) return null;
|
||
|
||
let qty = qtyRaw === '' || qtyRaw === null || qtyRaw === undefined ? null : qtyRaw;
|
||
let unitHint = null;
|
||
|
||
if (typeof qty === 'string') {
|
||
const qm = String(qty).trim().match(/^(\d+(?:\.\d+)?)\s*(斤|只|包|瓶|盒|袋|个)?$/);
|
||
if (qm) {
|
||
qty = Number(qm[1]);
|
||
if (qm[2]) unitHint = qm[2];
|
||
} else if (String(qty).includes('包')) {
|
||
const m = String(qty).match(/(\d+)/);
|
||
qty = m ? Number(m[1]) : null;
|
||
unitHint = '包';
|
||
} else {
|
||
qty = null;
|
||
}
|
||
}
|
||
|
||
let m = name.match(TRAIL_RE);
|
||
if (m) {
|
||
return {
|
||
name: cleanName(m[1]),
|
||
quantity: qty != null ? Number(qty) : Number(m[2]),
|
||
unit: m[3],
|
||
};
|
||
}
|
||
|
||
if (/^\d/.test(name)) {
|
||
m = name.match(LEAD_NUM_RE);
|
||
if (m && m[3] && /[\u4e00-\u9fa5]/.test(m[3])) {
|
||
// 1.5条肉 -> 条肉
|
||
const matName = m[2] === '条' ? cleanName(m[2] + m[3]) : cleanName(m[3]);
|
||
return {
|
||
name: matName,
|
||
quantity: qty != null ? Number(qty) : Number(m[1]),
|
||
unit: m[2] === '条' ? '条' : m[2] || '斤',
|
||
};
|
||
}
|
||
}
|
||
|
||
m = name.match(LEAD_CN_RE);
|
||
if (m && m[3] && CN_NUM[m[1]]) {
|
||
// 两条肉 -> 条肉
|
||
const matName = m[2] === '条' ? cleanName(m[2] + m[3]) : cleanName(m[3]);
|
||
return {
|
||
name: matName,
|
||
quantity: qty != null ? Number(qty) : CN_NUM[m[1]],
|
||
unit: m[2] || '条',
|
||
};
|
||
}
|
||
|
||
// 黑木耳0
|
||
name = name.replace(/([\u4e00-\u9fa5])0+$/u, '$1').trim();
|
||
const n = cleanName(name);
|
||
if (!n) return null;
|
||
|
||
let unit = unitHint || '斤';
|
||
if ((n === '鸡蛋' || n === '鹌鹑蛋') && qty != null && Number(qty) >= 1) unit = '个';
|
||
|
||
return {
|
||
name: n,
|
||
quantity: qty != null && !Number.isNaN(Number(qty)) ? Number(qty) : null,
|
||
unit,
|
||
};
|
||
}
|
||
|
||
const materialMap = new Map();
|
||
const recipes = [];
|
||
let matSeq = 904000001;
|
||
let recipeSeq = 905000001;
|
||
let linkSeq = 906000001;
|
||
|
||
function getOrCreateMaterial(parsed) {
|
||
const key = parsed.name;
|
||
if (!materialMap.has(key)) {
|
||
materialMap.set(key, { id: matSeq++, name: key, unit: parsed.unit || '斤' });
|
||
} else {
|
||
const cur = materialMap.get(key);
|
||
if (cur.unit === '斤' && parsed.unit && parsed.unit !== '斤') cur.unit = parsed.unit;
|
||
}
|
||
return materialMap.get(key);
|
||
}
|
||
|
||
for (const f of files) {
|
||
const wb = XLSX.readFile('C:/Users/Administrator/Desktop/' + f.file);
|
||
const rows = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], { header: 1, defval: '' });
|
||
for (let i = 1; i < rows.length; i++) {
|
||
const r = rows[i];
|
||
const dishName = String(r[1] || '').trim();
|
||
if (!dishName) continue;
|
||
|
||
const mats = [];
|
||
for (let c = 2; c <= 10; c += 2) {
|
||
const parsed = parseMaterialCell(r[c], r[c + 1]);
|
||
if (!parsed || !parsed.name) continue;
|
||
const mat = getOrCreateMaterial(parsed);
|
||
mats.push({
|
||
materialId: mat.id,
|
||
name: mat.name,
|
||
quantity: parsed.quantity,
|
||
sort: mats.length,
|
||
});
|
||
}
|
||
|
||
recipes.push({
|
||
id: recipeSeq++,
|
||
name: dishName,
|
||
category: f.cat,
|
||
materials: mats,
|
||
});
|
||
}
|
||
}
|
||
|
||
const out = [];
|
||
out.push('-- =============================================');
|
||
out.push('-- 客户食材+菜谱导入（大荤/小荤/素菜 Excel）');
|
||
out.push('-- 生成时间: ' + new Date().toISOString());
|
||
out.push('-- ID 段: 食材 904xxxxxx / 菜谱 905xxxxxx / 明细 906xxxxxx');
|
||
out.push('-- 请确认与库中已有 ID 不冲突；tenant_id=1；material_type=2（线上食材）');
|
||
out.push('-- 用量：按客户原表“克重”数值写入（业务习惯多为斤），单位写入食材 unit');
|
||
out.push('-- 菜谱 remark = 分类（大荤/小荤/素菜）；无食材的菜谱仍导入菜名');
|
||
out.push('-- =============================================');
|
||
out.push('');
|
||
out.push('-- ---------- 1. 食材（' + materialMap.size + ' 种） ----------');
|
||
out.push('INSERT INTO `canteen_erp_material`');
|
||
out.push('(`id`, `tenant_id`, `dept_id`, `name`, `unit`, `material_type`, `nutrition_tags`, `del_flag`, `create_by`, `create_time`)');
|
||
out.push('VALUES');
|
||
|
||
const mats = [...materialMap.values()].sort((a, b) => a.id - b.id);
|
||
mats.forEach((m, idx) => {
|
||
const comma = idx === mats.length - 1 ? ';' : ',';
|
||
out.push(`(${m.id}, 1, NULL, '${escapeSql(m.name)}', '${escapeSql(m.unit)}', 2, NULL, '0', 'admin', NOW())${comma}`);
|
||
});
|
||
|
||
out.push('');
|
||
out.push('-- ---------- 2. 排菜菜谱（' + recipes.length + ' 道） ----------');
|
||
out.push('INSERT INTO `canteen_menu_recipe`');
|
||
out.push('(`id`, `tenant_id`, `dept_id`, `merchant_id`, `name`, `remark`, `status`, `del_flag`, `create_by`, `create_time`)');
|
||
out.push('VALUES');
|
||
recipes.forEach((r, idx) => {
|
||
const comma = idx === recipes.length - 1 ? ';' : ',';
|
||
out.push(`(${r.id}, 1, NULL, NULL, '${escapeSql(r.name)}', '${escapeSql(r.category)}', 0, '0', 'admin', NOW())${comma}`);
|
||
});
|
||
|
||
out.push('');
|
||
out.push('-- ---------- 3. 菜谱食材明细 ----------');
|
||
const links = [];
|
||
for (const r of recipes) {
|
||
for (const m of r.materials) {
|
||
links.push({
|
||
id: linkSeq++,
|
||
recipeId: r.id,
|
||
materialId: m.materialId,
|
||
quantity: m.quantity,
|
||
sort: m.sort,
|
||
comment: `${r.category}-${r.name}-${m.name}`,
|
||
});
|
||
}
|
||
}
|
||
out.push('INSERT INTO `canteen_menu_recipe_material` (`id`, `recipe_id`, `material_id`, `quantity`, `sort_order`, `create_time`) VALUES');
|
||
links.forEach((l, idx) => {
|
||
let qtySql = 'NULL';
|
||
if (l.quantity != null && !Number.isNaN(l.quantity)) {
|
||
qtySql = Number.isInteger(l.quantity)
|
||
? l.quantity.toFixed(2)
|
||
: String(Math.round(l.quantity * 1000) / 1000);
|
||
}
|
||
const comma = idx === links.length - 1 ? ';' : ',';
|
||
out.push(`(${l.id}, ${l.recipeId}, ${l.materialId}, ${qtySql}, ${l.sort}, NOW())${comma} -- ${escapeSql(l.comment)}`);
|
||
});
|
||
|
||
out.push('');
|
||
out.push('-- 统计: 食材 ' + mats.length + ' / 菜谱 ' + recipes.length + ' / 明细 ' + links.length);
|
||
out.push('-- 按分类: ' + files.map((f) => f.cat + '=' + recipes.filter((r) => r.category === f.cat).length).join(', '));
|
||
|
||
const path = 'C:/workcode/java/biz-api-cloud-zax/db/1030pjcloudx_canteen_customer_recipe_import.sql';
|
||
fs.writeFileSync(path, out.join('\n'), 'utf8');
|
||
console.log('written', path);
|
||
console.log('materials', mats.length, 'recipes', recipes.length, 'links', links.length);
|
||
console.log(
|
||
'empty-material recipes:',
|
||
recipes.filter((r) => r.materials.length === 0).map((r) => r.category + ':' + r.name).join(', '),
|
||
);
|
||
console.log('sample materials:', mats.slice(0, 20).map((m) => m.name + '(' + m.unit + ')').join(', '));
|