|
|
/**
|
|
|
* 库存数据模型
|
|
|
*
|
|
|
* 功能：封装库存表和库存日志表的数据库操作
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 库存模型类
|
|
|
*/
|
|
|
class Inventory {
|
|
|
/**
|
|
|
* 根据ID查找库存项
|
|
|
* @param {number} id - 库存ID
|
|
|
* @returns {Promise<object|null>} 库存对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM inventory WHERE id = ?',
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据原材料名称查找库存项
|
|
|
* @param {string} name - 原材料名称
|
|
|
* @returns {Promise<object|null>} 库存对象或null
|
|
|
*/
|
|
|
static async findByName(name) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM inventory WHERE name = ?',
|
|
|
[name]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取库存列表（分页、筛选）
|
|
|
* @param {object} options - 查询选项
|
|
|
* @returns {Promise<object>} 库存列表和总数
|
|
|
*/
|
|
|
static async findAll(options = {}) {
|
|
|
try {
|
|
|
const {
|
|
|
page = 1,
|
|
|
pageSize = 10,
|
|
|
category,
|
|
|
search,
|
|
|
low_stock_only = false
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (category) {
|
|
|
conditions.push('category = ?');
|
|
|
params.push(category);
|
|
|
}
|
|
|
|
|
|
if (search) {
|
|
|
conditions.push('(name LIKE ? OR supplier LIKE ?)');
|
|
|
params.push(`%${search}%`, `%${search}%`);
|
|
|
}
|
|
|
|
|
|
// 低库存筛选
|
|
|
if (low_stock_only) {
|
|
|
conditions.push('quantity <= alert_quantity');
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total FROM inventory ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT * FROM inventory ${whereClause}
|
|
|
ORDER BY created_at DESC
|
|
|
LIMIT ? OFFSET ?`,
|
|
|
[...params, pageSize, offset]
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
list: rows,
|
|
|
total,
|
|
|
page: parseInt(page),
|
|
|
pageSize: parseInt(pageSize)
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询库存列表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 创建新库存项
|
|
|
* @param {object} inventoryData - 库存数据
|
|
|
* @returns {Promise<object>} 创建的库存项
|
|
|
*/
|
|
|
static async create(inventoryData) {
|
|
|
try {
|
|
|
const {
|
|
|
name,
|
|
|
category,
|
|
|
quantity,
|
|
|
unit,
|
|
|
purchase_price,
|
|
|
supplier,
|
|
|
expiry_date,
|
|
|
alert_quantity
|
|
|
} = inventoryData;
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO inventory
|
|
|
(name, category, quantity, unit, purchase_price, supplier, expiry_date, alert_quantity)
|
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
|
[name, category, quantity, unit, purchase_price, supplier, expiry_date || null, alert_quantity]
|
|
|
);
|
|
|
|
|
|
// 记录库存日志
|
|
|
await this.addLog({
|
|
|
inventory_id: result.insertId,
|
|
|
change_type: 'in',
|
|
|
quantity_change: quantity,
|
|
|
quantity_after: quantity,
|
|
|
operator_id: inventoryData.operator_id,
|
|
|
remark: '初始入库'
|
|
|
});
|
|
|
|
|
|
return await this.findById(result.insertId);
|
|
|
} catch (error) {
|
|
|
throw new Error('创建库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新库存项
|
|
|
* @param {number} id - 库存ID
|
|
|
* @param {object} inventoryData - 要更新的数据
|
|
|
* @returns {Promise<object>} 更新后的库存项
|
|
|
*/
|
|
|
static async update(id, inventoryData) {
|
|
|
try {
|
|
|
const {
|
|
|
name,
|
|
|
category,
|
|
|
unit,
|
|
|
purchase_price,
|
|
|
supplier,
|
|
|
expiry_date,
|
|
|
alert_quantity
|
|
|
} = inventoryData;
|
|
|
|
|
|
const updates = [];
|
|
|
const values = [];
|
|
|
|
|
|
if (name !== undefined) {
|
|
|
updates.push('name = ?');
|
|
|
values.push(name);
|
|
|
}
|
|
|
if (category !== undefined) {
|
|
|
updates.push('category = ?');
|
|
|
values.push(category);
|
|
|
}
|
|
|
if (unit !== undefined) {
|
|
|
updates.push('unit = ?');
|
|
|
values.push(unit);
|
|
|
}
|
|
|
if (purchase_price !== undefined) {
|
|
|
updates.push('purchase_price = ?');
|
|
|
values.push(purchase_price);
|
|
|
}
|
|
|
if (supplier !== undefined) {
|
|
|
updates.push('supplier = ?');
|
|
|
values.push(supplier);
|
|
|
}
|
|
|
if (expiry_date !== undefined) {
|
|
|
updates.push('expiry_date = ?');
|
|
|
values.push(expiry_date);
|
|
|
}
|
|
|
if (alert_quantity !== undefined) {
|
|
|
updates.push('alert_quantity = ?');
|
|
|
values.push(alert_quantity);
|
|
|
}
|
|
|
|
|
|
if (updates.length === 0) {
|
|
|
throw new Error('没有要更新的数据');
|
|
|
}
|
|
|
|
|
|
values.push(id);
|
|
|
|
|
|
await pool.query(
|
|
|
`UPDATE inventory SET ${updates.join(', ')} WHERE id = ?`,
|
|
|
values
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 删除库存项
|
|
|
* @param {number} id - 库存ID
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async delete(id) {
|
|
|
try {
|
|
|
await pool.query('DELETE FROM inventory WHERE id = ?', [id]);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('删除库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 调整库存数量（入库/出库）
|
|
|
* @param {number} id - 库存ID
|
|
|
* @param {string} changeType - 变更类型 (in/out/adjust)
|
|
|
* @param {number} quantityChange - 数量变化（正数表示增加，负数表示减少）
|
|
|
* @param {number} operatorId - 操作人ID
|
|
|
* @param {string} remark - 备注
|
|
|
* @returns {Promise<object>} 更新后的库存项
|
|
|
*/
|
|
|
static async adjustQuantity(id, changeType, quantityChange, operatorId, remark = null) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
// 获取当前库存
|
|
|
const [rows] = await connection.query(
|
|
|
'SELECT * FROM inventory WHERE id = ?',
|
|
|
[id]
|
|
|
);
|
|
|
|
|
|
if (rows.length === 0) {
|
|
|
throw new Error('库存项不存在');
|
|
|
}
|
|
|
|
|
|
const currentItem = rows[0];
|
|
|
let newQuantity;
|
|
|
|
|
|
// 计算新数量
|
|
|
if (changeType === 'in') {
|
|
|
newQuantity = parseFloat(currentItem.quantity) + parseFloat(quantityChange);
|
|
|
} else if (changeType === 'out') {
|
|
|
newQuantity = parseFloat(currentItem.quantity) - parseFloat(quantityChange);
|
|
|
if (newQuantity < 0) {
|
|
|
throw new Error('库存不足，无法出库');
|
|
|
}
|
|
|
} else if (changeType === 'adjust') {
|
|
|
// 直接调整到目标数量
|
|
|
newQuantity = parseFloat(quantityChange);
|
|
|
} else {
|
|
|
throw new Error('无效的变更类型');
|
|
|
}
|
|
|
|
|
|
// 更新库存数量
|
|
|
await connection.query(
|
|
|
'UPDATE inventory SET quantity = ?, updated_at = NOW() WHERE id = ?',
|
|
|
[newQuantity, id]
|
|
|
);
|
|
|
|
|
|
// 记录库存日志
|
|
|
const actualChange = changeType === 'adjust'
|
|
|
? (newQuantity - parseFloat(currentItem.quantity))
|
|
|
: (changeType === 'in' ? quantityChange : -quantityChange);
|
|
|
|
|
|
await connection.query(
|
|
|
`INSERT INTO inventory_logs
|
|
|
(inventory_id, change_type, quantity_change, quantity_after, operator_id, remark)
|
|
|
VALUES (?, ?, ?, ?, ?, ?)`,
|
|
|
[id, changeType, actualChange, newQuantity, operatorId, remark]
|
|
|
);
|
|
|
|
|
|
await connection.commit();
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('调整库存失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取低库存预警列表
|
|
|
* @returns {Promise<array>} 低库存项列表
|
|
|
*/
|
|
|
static async getLowStockItems() {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT * FROM inventory
|
|
|
WHERE quantity <= alert_quantity
|
|
|
ORDER BY (quantity / alert_quantity) ASC`
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询低库存项失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取即将过期的库存列表
|
|
|
* @param {number} days - 预警天数（默认7天）
|
|
|
* @returns {Promise<array>} 即将过期的库存列表
|
|
|
*/
|
|
|
static async getExpiringItems(days = 7) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT * FROM inventory
|
|
|
WHERE expiry_date IS NOT NULL
|
|
|
AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
|
|
|
AND expiry_date >= CURDATE()
|
|
|
ORDER BY expiry_date ASC`,
|
|
|
[days]
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询即将过期库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取已过期的库存列表
|
|
|
* @returns {Promise<array>} 已过期的库存列表
|
|
|
*/
|
|
|
static async getExpiredItems() {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT * FROM inventory
|
|
|
WHERE expiry_date IS NOT NULL
|
|
|
AND expiry_date < CURDATE()
|
|
|
ORDER BY expiry_date ASC`
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询已过期库存失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取库存日志
|
|
|
* @param {number} inventoryId - 库存ID（可选）
|
|
|
* @param {object} options - 查询选项
|
|
|
* @returns {Promise<object>} 日志列表和总数
|
|
|
*/
|
|
|
static async getLogs(inventoryId = null, options = {}) {
|
|
|
try {
|
|
|
const {
|
|
|
page = 1,
|
|
|
pageSize = 20,
|
|
|
change_type
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (inventoryId) {
|
|
|
conditions.push('il.inventory_id = ?');
|
|
|
params.push(inventoryId);
|
|
|
}
|
|
|
|
|
|
if (change_type) {
|
|
|
conditions.push('il.change_type = ?');
|
|
|
params.push(change_type);
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total FROM inventory_logs il ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询日志列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
il.*,
|
|
|
i.name as inventory_name,
|
|
|
i.unit,
|
|
|
u.username as operator_name
|
|
|
FROM inventory_logs il
|
|
|
LEFT JOIN inventory i ON il.inventory_id = i.id
|
|
|
LEFT JOIN users u ON il.operator_id = u.id
|
|
|
${whereClause}
|
|
|
ORDER BY il.created_at DESC
|
|
|
LIMIT ? OFFSET ?`,
|
|
|
[...params, pageSize, offset]
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
list: rows,
|
|
|
total,
|
|
|
page: parseInt(page),
|
|
|
pageSize: parseInt(pageSize)
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询库存日志失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 添加库存日志
|
|
|
* @param {object} logData - 日志数据
|
|
|
* @returns {Promise<number>} 插入的日志ID
|
|
|
*/
|
|
|
static async addLog(logData) {
|
|
|
try {
|
|
|
const {
|
|
|
inventory_id,
|
|
|
change_type,
|
|
|
quantity_change,
|
|
|
quantity_after,
|
|
|
operator_id,
|
|
|
remark
|
|
|
} = logData;
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO inventory_logs
|
|
|
(inventory_id, change_type, quantity_change, quantity_after, operator_id, remark)
|
|
|
VALUES (?, ?, ?, ?, ?, ?)`,
|
|
|
[inventory_id, change_type, quantity_change, quantity_after, operator_id, remark || null]
|
|
|
);
|
|
|
|
|
|
return result.insertId;
|
|
|
} catch (error) {
|
|
|
throw new Error('添加库存日志失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 检查名称是否已存在
|
|
|
* @param {string} name - 原材料名称
|
|
|
* @param {number} excludeId - 排除的ID（用于更新时）
|
|
|
* @returns {Promise<boolean>} 是否存在
|
|
|
*/
|
|
|
static async isNameExists(name, excludeId = null) {
|
|
|
try {
|
|
|
const query = excludeId
|
|
|
? 'SELECT id FROM inventory WHERE name = ? AND id != ?'
|
|
|
: 'SELECT id FROM inventory WHERE name = ?';
|
|
|
|
|
|
const params = excludeId ? [name, excludeId] : [name];
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows.length > 0;
|
|
|
} catch (error) {
|
|
|
throw new Error('检查名称失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取库存分类列表
|
|
|
* @returns {Promise<array>} 分类列表
|
|
|
*/
|
|
|
static async getCategories() {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT DISTINCT category FROM inventory ORDER BY category'
|
|
|
);
|
|
|
return rows.map(row => row.category);
|
|
|
} catch (error) {
|
|
|
throw new Error('查询分类列表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取库存统计信息
|
|
|
* @returns {Promise<object>} 统计信息
|
|
|
*/
|
|
|
static async getStatistics() {
|
|
|
try {
|
|
|
const [totalResult] = await pool.query(
|
|
|
'SELECT COUNT(*) as total_items, SUM(quantity * purchase_price) as total_value FROM inventory'
|
|
|
);
|
|
|
|
|
|
const [lowStockResult] = await pool.query(
|
|
|
'SELECT COUNT(*) as low_stock_count FROM inventory WHERE quantity <= alert_quantity'
|
|
|
);
|
|
|
|
|
|
const [expiringResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as expiring_count FROM inventory
|
|
|
WHERE expiry_date IS NOT NULL
|
|
|
AND expiry_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
|
|
|
AND expiry_date >= CURDATE()`
|
|
|
);
|
|
|
|
|
|
const [expiredResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as expired_count FROM inventory
|
|
|
WHERE expiry_date IS NOT NULL AND expiry_date < CURDATE()`
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
total_items: totalResult[0].total_items || 0,
|
|
|
total_value: parseFloat(totalResult[0].total_value || 0),
|
|
|
low_stock_count: lowStockResult[0].low_stock_count || 0,
|
|
|
expiring_count: expiringResult[0].expiring_count || 0,
|
|
|
expired_count: expiredResult[0].expired_count || 0
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询库存统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = Inventory;
|
