Files
jurong_circle_shopping_black/routes/specifications.js

710 lines
24 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

const express = require('express');
const router = express.Router();
const {getDB} = require('../database');
const {SelectBuilder} = require('../config/dbv2')
const {db} = require("../server");
const sql = require("../config/config");
router.get('/names', async (req, res) => {
try {
const {status = 'active'} = req.query;
let query = `
SELECT * FROM spec_names
`;
const params = [];
if (status) {
query += ' WHERE status = ?';
params.push(status);
}
query += ' ORDER BY sort_order';
const [specNames] = await getDB().execute(query, params);
res.json({
success: true,
data: specNames
});
} catch (error) {
console.error('获取规格名称失败:', error);
res.status(500).json({success: false, message: '获取规格名称失败'});
}
});
router.post('/names', async (req, res) => {
const db = getDB();
try {
const {name, display_name, sort_order = 0} = req.body;
if (!name || !display_name) {
return res.status(400).json({success: false, message: '规格名称和显示名称不能为空'});
}
// let spec_name = new SelectBuilder().from()
const [result] = await getDB().execute(
'INSERT INTO spec_names (name, display_name, sort_order) VALUES (?, ?, ?)',
[name, display_name, sort_order]
);
res.status(201).json({
success: true,
message: '规格名称创建成功',
data: {id: result.insertId}
});
} catch (error) {
if (error.code === 'ER_DUP_ENTRY') {
return res.status(400).json({success: false, message: '规格名称已存在'});
}
console.error('创建规格名称失败:', error);
res.status(500).json({success: false, message: '创建规格名称失败'});
}
});
router.delete('/names/:id', async (req, res) => {
try {
const {id} = req.params;
const db = getDB();
// const {id:user_id,role} = req.user
let specCountQuery = new SelectBuilder()
.from('spec_names')
.select('COUNT(*) as total')
.where('id=?',id)
// if(role !== 'admin'){
// specCountQuery.where('created_id=?',user_id)
// }
let [spec] = await specCountQuery.execute(db)
if (spec.total === 0) {
return res.status(404).json({success: false, message: '规格名称不存在'});
}
// 检查该规格名称下是否还有规格值
const [specValues] = await getDB().execute(
'SELECT COUNT(*) as count FROM spec_values WHERE spec_name_id = ?',
[id]
);
if (specValues[0].count > 0) {
return res.status(400).json({success: false, message: '该规格名称下还有规格值,请先删除所有规格值'});
}
// 删除规格名称
await getDB().execute(
'DELETE FROM spec_names WHERE id = ?',
[id]
);
res.json({
success: true,
message: '规格名称删除成功'
});
} catch (error) {
console.error('删除规格名称失败:', error);
res.status(500).json({success: false, message: '删除规格名称失败'});
}
});
router.get('/values', async (req, res) => {
try {
const {spec_name_id, status = 'active'} = req.query;
let query = `
SELECT sv.*, sn.name as spec_name, sn.display_name as spec_display_name
FROM spec_values sv
LEFT JOIN spec_names sn ON sv.spec_name_id = sn.id
WHERE 1 = 1
`;
const params = [];
if (spec_name_id) {
query += ' AND sv.spec_name_id = ?';
params.push(spec_name_id);
}
if (status) {
query += ' AND sv.status = ?';
params.push(status);
}
query += ' ORDER BY sv.spec_name_id, sv.sort_order, sv.id';
const [specValues] = await getDB().execute(query, params);
res.json({
success: true,
data: specValues
});
} catch (error) {
console.error('获取规格值失败:', error);
res.status(500).json({success: false, message: '获取规格值失败'});
}
});
router.post('/values', async (req, res) => {
try {
const {spec_name_id, value, display_value, color_code, image_url, sort_order = 0} = req.body;
if (!spec_name_id || !value || !display_value) {
return res.status(400).json({success: false, message: '规格名称ID、规格值和显示值不能为空'});
}
const [result] = await getDB().execute(
`INSERT INTO spec_values (spec_name_id, value, display_value, color_code, image_url, sort_order)
VALUES (?, ?, ?, ?, ?, ?)`,
[spec_name_id, value, display_value, color_code || null, image_url || null, sort_order]
);
res.status(201).json({
success: true,
message: '规格值创建成功',
data: {id: result.insertId}
});
} catch (error) {
if (error.code === 'ER_DUP_ENTRY') {
return res.status(400).json({success: false, message: '该规格名称下的规格值已存在'});
}
console.error('创建规格值失败:', error);
res.status(500).json({success: false, message: '创建规格值失败'});
}
});
router.delete('/values/:id', async (req, res) => {
try {
const {id} = req.params;
console.log(12345,id)
// 检查规格值是否存在
const [specValue] = await getDB().execute(
'SELECT COUNT(*) as count FROM spec_values WHERE id = ?',
[id]
);
if (specValue[0].count === 0) {
return res.status(404).json({success: false, message: '规格值不存在'});
} else {
// 删除规格值
await getDB().execute(
'DELETE FROM spec_values WHERE id = ?',
[id]
);
}
res.json({
success: true,
message: '规格值删除成功'
});
} catch (error) {
console.error('删除规格值失败:', error);
res.status(500).json({success: false, message: '删除规格值失败'});
}
})
router.get('/combinations/:productId', async (req, res) => {
try {
const {productId} = req.params;
const {status} = req.query;
// 获取商品的规格组合
let query = `
SELECT psc.*,
p.name as product_name,
p.price as base_price,
p.points_price as base_points_price,
p.rongdou_price as base_rongdou_price
FROM product_spec_combinations psc
LEFT JOIN products p ON psc.product_id = p.id
WHERE psc.product_id = ?
`;
const params = [productId];
if (status) {
query += ' AND psc.status = ?';
params.push(status);
}
query += ' ORDER BY psc.combination_key';
const [combinations] = await getDB().execute(query, params);
// 为每个组合获取详细的规格值信息
for (let combination of combinations) {
let specValueIds;
try {
// 处理不同的数据格式
if (!combination.spec_values) {
specValueIds = [];
} else if (typeof combination.spec_values === 'string') {
// 如果是字符串尝试JSON解析失败则按逗号分隔处理
try {
specValueIds = JSON.parse(combination.spec_values);
} catch {
// 按逗号分隔的字符串处理
specValueIds = combination.spec_values.split(',').map(id => parseInt(id.trim())).filter(id => !isNaN(id));
}
} else if (Buffer.isBuffer(combination.spec_values)) {
// 如果是Buffer转换为字符串后处理
const strValue = combination.spec_values.toString();
try {
specValueIds = JSON.parse(strValue);
} catch {
specValueIds = strValue.split(',').map(id => parseInt(id.trim())).filter(id => !isNaN(id));
}
} else {
// 其他情况,尝试直接使用
specValueIds = Array.isArray(combination.spec_values) ? combination.spec_values : [];
}
} catch (error) {
console.error('解析规格值失败:', combination.spec_values, error);
specValueIds = [];
}
if (specValueIds && specValueIds.length > 0) {
const placeholders = specValueIds.map(() => '?').join(',');
const [specDetails] = await getDB().execute(
`SELECT sv.*, sn.name as spec_name, sn.display_name as spec_display_name
FROM spec_values sv
LEFT JOIN spec_names sn ON sv.spec_name_id = sn.id
WHERE sv.id IN (${placeholders})
ORDER BY sn.sort_order, sv.sort_order`,
specValueIds
);
combination.spec_details = specDetails;
} else {
combination.spec_details = [];
}
// 计算实际价格
combination.actual_price = combination.base_price + (combination.price_adjustment || 0);
combination.actual_points_price = combination.base_points_price + (combination.points_adjustment || 0);
combination.actual_rongdou_price = combination.base_rongdou_price + (combination.rongdou_adjustment || 0);
combination.is_available = combination.stock > 0;
}
res.json({
success: true,
data: combinations
});
} catch (error) {
console.error('获取规格组合失败:', error);
res.status(500).json({success: false, message: '获取规格组合失败'});
}
});
router.get('/combinations/:id', async (req, res) => {
try {
const {id} = req.params;
// 获取规格组合详情
const [combinations] = await getDB().execute(
`SELECT psc.*,
p.name as product_name,
p.price as base_price,
p.points_price as base_points_price,
p.rongdou_price as base_rongdou_price
FROM product_spec_combinations psc
LEFT JOIN products p ON psc.product_id = p.id
WHERE psc.id = ?`,
[id]
);
if (combinations.length === 0) {
return res.status(404).json({success: false, message: '规格组合不存在'});
}
const combination = combinations[0];
// 解析规格值并获取详细信息
let specValueIds;
try {
if (!combination.spec_values) {
specValueIds = [];
} else if (typeof combination.spec_values === 'string') {
try {
specValueIds = JSON.parse(combination.spec_values);
} catch {
specValueIds = combination.spec_values.split(',').map(id => parseInt(id.trim())).filter(id => !isNaN(id));
}
} else if (Buffer.isBuffer(combination.spec_values)) {
const strValue = combination.spec_values.toString();
try {
specValueIds = JSON.parse(strValue);
} catch {
specValueIds = strValue.split(',').map(id => parseInt(id.trim())).filter(id => !isNaN(id));
}
} else {
specValueIds = Array.isArray(combination.spec_values) ? combination.spec_values : [];
}
} catch (error) {
console.error('解析规格值失败:', combination.spec_values, error);
specValueIds = [];
}
if (specValueIds && specValueIds.length > 0) {
const placeholders = specValueIds.map(() => '?').join(',');
const [specDetails] = await getDB().execute(
`SELECT sv.*, sn.name as spec_name, sn.display_name as spec_display_name
FROM spec_values sv
LEFT JOIN spec_names sn ON sv.spec_name_id = sn.id
WHERE sv.id IN (${placeholders})
ORDER BY sn.sort_order, sv.sort_order`,
specValueIds
);
combination.spec_details = specDetails;
} else {
combination.spec_details = [];
}
// 计算实际价格
combination.actual_price = combination.base_price + (combination.price_adjustment || 0);
combination.actual_points_price = combination.base_points_price + (combination.points_adjustment || 0);
combination.actual_rongdou_price = combination.base_rongdou_price + (combination.rongdou_adjustment || 0);
combination.is_available = combination.stock > 0;
res.json({
success: true,
data: combination
});
} catch (error) {
console.error('获取规格组合详情失败:', error);
res.status(500).json({success: false, message: '获取规格组合详情失败'});
}
});
router.delete('/combinations/:id', async (req, res) => {
try {
const {id} = req.params;
// 检查规格组合是否存在
const [existingCombination] = await getDB().execute(
'SELECT id FROM product_spec_combinations WHERE id = ?',
[id]
);
if (existingCombination.length === 0) {
return res.status(404).json({success: false, message: '规格组合不存在'});
}
// 删除规格组合
await getDB().execute(
'DELETE FROM product_spec_combinations WHERE id = ?',
[id]
);
res.json({
success: true,
message: '规格组合删除成功'
});
} catch (error) {
console.error('删除规格组合失败:', error);
res.status(500).json({success: false, message: '删除规格组合失败'});
}
});
router.put('/combinations/:id', async (req, res) => {
try {
const {id} = req.params;
const {
price_adjustment,
points_adjustment,
rongdou_adjustment,
stock,
sku_code,
barcode,
weight,
volume,
status
} = req.body;
// 检查规格组合是否存在
const [existing] = await getDB().execute(
'SELECT id FROM product_spec_combinations WHERE id = ?',
[id]
);
if (existing.length === 0) {
return res.status(404).json({success: false, message: '规格组合不存在'});
}
// 构建更新字段
const updateFields = [];
const updateValues = [];
if (price_adjustment !== undefined) {
updateFields.push('price_adjustment = ?');
updateValues.push(price_adjustment);
}
if (points_adjustment !== undefined) {
updateFields.push('points_adjustment = ?');
updateValues.push(points_adjustment);
}
if (rongdou_adjustment !== undefined) {
updateFields.push('rongdou_adjustment = ?');
updateValues.push(rongdou_adjustment);
}
if (stock !== undefined) {
updateFields.push('stock = ?');
updateValues.push(stock);
}
if (sku_code !== undefined) {
updateFields.push('sku_code = ?');
updateValues.push(sku_code);
}
if (barcode !== undefined) {
updateFields.push('barcode = ?');
updateValues.push(barcode);
}
if (weight !== undefined) {
updateFields.push('weight = ?');
updateValues.push(weight);
}
if (volume !== undefined) {
updateFields.push('volume = ?');
updateValues.push(volume);
}
if (status !== undefined) {
updateFields.push('status = ?');
updateValues.push(status);
}
if (updateFields.length === 0) {
return res.status(400).json({success: false, message: '没有提供要更新的字段'});
}
updateFields.push('updated_at = NOW()');
updateValues.push(id);
const updateQuery = `UPDATE product_spec_combinations
SET ${updateFields.join(', ')}
WHERE id = ?`;
console.log(12345,updateQuery, updateValues);
await getDB().execute(updateQuery, updateValues);
res.json({
success: true,
message: '规格组合更新成功'
});
} catch (error) {
console.error('更新规格组合失败:', error);
res.status(500).json({success: false, message: '更新规格组合失败'});
}
});
router.post('/combinations', async (req, res) => {
try {
const {
product_id,
spec_values,
price_adjustment = 0,
points_adjustment = 0,
rongdou_adjustment = 0,
stock = 0,
sku_code,
barcode,
weight,
volume
} = req.body;
if (!product_id || !spec_values || !Array.isArray(spec_values) || spec_values.length === 0) {
return res.status(400).json({success: false, message: '商品ID和规格值数组不能为空'});
}
// 生成组合键
const sortedSpecValues = [...spec_values].sort((a, b) => a - b);
const combinationKey = sortedSpecValues.join('-');
const [result] = await getDB().execute(
`INSERT INTO product_spec_combinations
(product_id, combination_key, spec_values, price_adjustment, points_adjustment,
rongdou_adjustment, stock, sku_code, barcode, weight, volume)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
product_id,
combinationKey,
JSON.stringify(sortedSpecValues),
price_adjustment,
points_adjustment,
rongdou_adjustment,
stock,
sku_code,
barcode,
weight,
volume
]
);
res.status(201).json({
success: true,
message: '规格组合创建成功',
data: {id: result.insertId, combination_key: combinationKey}
});
} catch (error) {
if (error.code === 'ER_DUP_ENTRY') {
return res.status(400).json({success: false, message: '该规格组合已存在'});
}
console.error('创建规格组合失败:', error);
res.status(500).json({success: false, message: '创建规格组合失败'});
}
});
router.post('/generate-combinations', async (req, res) => {
try {
const {product_id, spec_name_ids, default_stock = 0} = req.body;
if (!product_id || !spec_name_ids || !Array.isArray(spec_name_ids) || spec_name_ids.length === 0) {
return res.status(400).json({success: false, message: '商品ID和规格名称ID数组不能为空'});
}
// 获取每个规格名称下的所有活跃规格值
const specValueGroups = [];
for (const specNameId of spec_name_ids) {
const [specValues] = await getDB().execute(
'SELECT id FROM spec_values WHERE spec_name_id = ? AND status = "active" ORDER BY sort_order, id',
[specNameId]
);
if (specValues.length === 0) {
return res.status(400).json({
success: false,
message: `规格名称ID ${specNameId} 下没有活跃的规格值`
});
}
specValueGroups.push(specValues.map(sv => sv.id));
}
// 生成笛卡尔积
function cartesianProduct(arrays) {
return arrays.reduce((acc, curr) => {
const result = [];
acc.forEach(a => {
curr.forEach(c => {
result.push([...a, c]);
});
});
return result;
}, [[]]);
}
const combinations = cartesianProduct(specValueGroups);
// 生成所有组合键
const combinationData = combinations.map(combination => {
const sortedCombination = [...combination].sort((a, b) => a - b);
const combinationKey = sortedCombination.join('-');
return {
combination: sortedCombination,
key: combinationKey
};
});
// 批量检查已存在的组合
const existingKeys = new Set();
if (combinationData.length > 0) {
const keys = combinationData.map(item => item.key);
const placeholders = keys.map(() => '?').join(',');
const [existingCombinations] = await getDB().execute(
`SELECT combination_key
FROM product_spec_combinations
WHERE product_id = ?
AND combination_key IN (${placeholders})`,
[product_id, ...keys]
);
existingCombinations.forEach(row => {
existingKeys.add(row.combination_key);
});
}
// 过滤出需要插入的新组合
const newCombinations = combinationData.filter(item => !existingKeys.has(item.key));
// 批量插入新的规格组合
let createdCount = 0;
const skippedCount = combinationData.length - newCombinations.length;
if (newCombinations.length > 0) {
// 使用批量插入提高性能
const values = [];
const placeholders = [];
const createProduct = `
INSERT INTO products (name, price)
VALUES ('temp', 0)
`
const deleteProduct = `
DELETE FROM products WHERE name = 'temp'
`
var newProductId = null;
if (product_id === 'new') {
await getDB().execute(deleteProduct);
await getDB().execute(createProduct);
// const deleteProduct = `
// DELETE FROM products WHERE name = 'temp'
// `
// await getDB().execute(deleteProduct);
const getProductId = `
SELECT id FROM products WHERE name = 'temp'
`
const [productResult] = await getDB().execute(getProductId);
newProductId = productResult[0].id;
}
newCombinations.forEach(item => {
values.push(
newProductId === null ? product_id : newProductId,
item.key,
JSON.stringify(item.combination),
default_stock
);
placeholders.push('(?, ?, ?, ?)');
});
const sql = `INSERT INTO product_spec_combinations
(product_id, combination_key, spec_values, stock)
VALUES ${placeholders.join(', ')}`;
const [result] = await getDB().execute(sql, values);
createdCount = result.affectedRows;
}
res.status(201).json({
success: true,
message: '规格组合生成完成',
data: {
total_combinations: combinations.length,
created: createdCount,
skipped: skippedCount,
product_id: newProductId === null ? product_id : newProductId
}
});
} catch (error) {
console.error('生成规格组合失败:', error);
res.status(500).json({success: false, message: '生成规格组合失败'});
}
});
module.exports = router;