366 lines
14 KiB
JavaScript
366 lines
14 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const db = require('../db');
|
|
const { authenticate } = require('../middleware/auth');
|
|
|
|
function generateCode(prefix) {
|
|
const now = new Date();
|
|
const dateStr = now.getFullYear().toString() +
|
|
(now.getMonth() + 1).toString().padStart(2, '0') +
|
|
now.getDate().toString().padStart(2, '0');
|
|
const rand = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
|
|
return `${prefix}-${dateStr}-${rand}`;
|
|
}
|
|
|
|
router.get('/', authenticate, async (req, res) => {
|
|
try {
|
|
const {
|
|
txn_type, category_level1, category_level2,
|
|
project_id, user_id, source,
|
|
date_from, date_to,
|
|
page = 1, pageSize = 50
|
|
} = req.query;
|
|
|
|
let sql = 'SELECT fr.*, p.name as project_name FROM financial_records fr LEFT JOIN projects p ON fr.project_id = p.id WHERE 1=1';
|
|
const params = [];
|
|
let paramIdx = 1;
|
|
|
|
if (txn_type) { params.push(txn_type); sql += ` AND fr.txn_type = $${paramIdx++}`; }
|
|
if (category_level1) { params.push(category_level1); sql += ` AND fr.category_level1 = $${paramIdx++}`; }
|
|
if (category_level2) { params.push(category_level2); sql += ` AND fr.category_level2 = $${paramIdx++}`; }
|
|
if (project_id) { params.push(project_id); sql += ` AND fr.project_id = $${paramIdx++}`; }
|
|
if (user_id) { params.push(user_id); sql += ` AND fr.user_id = $${paramIdx++}`; }
|
|
if (source) { params.push(source); sql += ` AND fr.source = $${paramIdx++}`; }
|
|
if (date_from) { params.push(date_from); sql += ` AND fr.record_date >= $${paramIdx++}`; }
|
|
if (date_to) { params.push(date_to); sql += ` AND fr.record_date <= $${paramIdx++}`; }
|
|
|
|
const countResult = await db.query(`SELECT COUNT(*) as total FROM (${sql}) sub`, params);
|
|
const total = parseInt(countResult.rows[0].total);
|
|
|
|
sql += ' ORDER BY fr.record_date DESC, fr.created_at DESC';
|
|
const offset = (parseInt(page) - 1) * parseInt(pageSize);
|
|
params.push(parseInt(pageSize));
|
|
sql += ` LIMIT $${paramIdx++}`;
|
|
params.push(offset);
|
|
sql += ` OFFSET $${paramIdx++}`;
|
|
|
|
const result = await db.query(sql, params);
|
|
res.json({
|
|
success: true,
|
|
data: result.rows,
|
|
pagination: {
|
|
page: parseInt(page),
|
|
pageSize: parseInt(pageSize),
|
|
total,
|
|
totalPages: Math.ceil(total / parseInt(pageSize))
|
|
}
|
|
});
|
|
} catch (error) {
|
|
console.error('查询财务记录失败:', error);
|
|
res.status(500).json({ success: false, message: '查询财务记录失败' });
|
|
}
|
|
});
|
|
|
|
router.get('/summary', authenticate, async (req, res) => {
|
|
try {
|
|
const { project_id, date_from, date_to } = req.query;
|
|
let where = "WHERE status != 'voided'";
|
|
const params = [];
|
|
let paramIdx = 1;
|
|
|
|
if (project_id) { params.push(project_id); where += ` AND project_id = $${paramIdx++}`; }
|
|
if (date_from) { params.push(date_from); where += ` AND record_date >= $${paramIdx++}`; }
|
|
if (date_to) { params.push(date_to); where += ` AND record_date <= $${paramIdx++}`; }
|
|
|
|
const totalResult = await db.query(
|
|
`SELECT
|
|
COALESCE(SUM(CASE WHEN txn_type = 'income' THEN amount_cny ELSE 0 END), 0) as total_income,
|
|
COALESCE(SUM(CASE WHEN txn_type = 'expense' THEN amount_cny ELSE 0 END), 0) as total_expense,
|
|
COALESCE(SUM(CASE WHEN txn_type = 'income' THEN amount_cny ELSE -amount_cny END), 0) as net_profit
|
|
FROM financial_records ${where}`,
|
|
params
|
|
);
|
|
|
|
const byCategory = await db.query(
|
|
`SELECT category_level1, category_level2,
|
|
COALESCE(SUM(amount_cny), 0) as total_amount,
|
|
COUNT(*) as count
|
|
FROM financial_records ${where}
|
|
GROUP BY category_level1, category_level2
|
|
ORDER BY category_level1, total_amount DESC`,
|
|
params
|
|
);
|
|
|
|
const byMonth = await db.query(
|
|
`SELECT TO_CHAR(record_date, 'YYYY-MM') as month,
|
|
COALESCE(SUM(CASE WHEN txn_type = 'income' THEN amount_cny ELSE 0 END), 0) as income,
|
|
COALESCE(SUM(CASE WHEN txn_type = 'expense' THEN amount_cny ELSE 0 END), 0) as expense
|
|
FROM financial_records ${where}
|
|
GROUP BY TO_CHAR(record_date, 'YYYY-MM')
|
|
ORDER BY month`,
|
|
params
|
|
);
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
totals: totalResult.rows[0],
|
|
byCategory: byCategory.rows,
|
|
byMonth: byMonth.rows
|
|
}
|
|
});
|
|
} catch (error) {
|
|
console.error('获取财务汇总失败:', error);
|
|
res.status(500).json({ success: false, message: '获取财务汇总失败' });
|
|
}
|
|
});
|
|
|
|
router.get('/project-profit/:projectId', authenticate, async (req, res) => {
|
|
try {
|
|
const { projectId } = req.params;
|
|
const projectResult = await db.query('SELECT * FROM projects WHERE id = $1', [projectId]);
|
|
if (projectResult.rows.length === 0) {
|
|
return res.status(404).json({ success: false, message: '项目不存在' });
|
|
}
|
|
const project = projectResult.rows[0];
|
|
|
|
const incomeResult = await db.query(
|
|
`SELECT COALESCE(SUM(amount_cny), 0) as total
|
|
FROM financial_records WHERE project_id = $1 AND txn_type = 'income' AND status != 'voided'`,
|
|
[projectId]
|
|
);
|
|
const expenseResult = await db.query(
|
|
`SELECT COALESCE(SUM(amount_cny), 0) as total
|
|
FROM financial_records WHERE project_id = $1 AND txn_type = 'expense' AND status != 'voided'`,
|
|
[projectId]
|
|
);
|
|
|
|
const expenseByCategory = await db.query(
|
|
`SELECT category_level2, COALESCE(SUM(amount_cny), 0) as total_amount, COUNT(*) as count
|
|
FROM financial_records
|
|
WHERE project_id = $1 AND txn_type = 'expense' AND status != 'voided'
|
|
GROUP BY category_level2 ORDER BY total_amount DESC`,
|
|
[projectId]
|
|
);
|
|
|
|
const expenseByUser = await db.query(
|
|
`SELECT user_name, COALESCE(SUM(amount_cny), 0) as total_amount, COUNT(*) as count
|
|
FROM financial_records
|
|
WHERE project_id = $1 AND txn_type = 'expense' AND status != 'voided' AND user_id IS NOT NULL
|
|
GROUP BY user_name ORDER BY total_amount DESC`,
|
|
[projectId]
|
|
);
|
|
|
|
const totalIncome = parseFloat(incomeResult.rows[0].total);
|
|
const totalExpense = parseFloat(expenseResult.rows[0].total);
|
|
const contractAmount = parseFloat(project.contract_amount || 0);
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
project_name: project.name,
|
|
contract_amount: contractAmount,
|
|
total_income: totalIncome,
|
|
total_expense: totalExpense,
|
|
gross_profit: totalIncome - totalExpense,
|
|
gross_margin: totalIncome > 0 ? ((totalIncome - totalExpense) / totalIncome * 100).toFixed(2) : 0,
|
|
expense_by_category: expenseByCategory.rows,
|
|
expense_by_user: expenseByUser.rows
|
|
}
|
|
});
|
|
} catch (error) {
|
|
console.error('获取项目利润失败:', error);
|
|
res.status(500).json({ success: false, message: '获取项目利润失败' });
|
|
}
|
|
});
|
|
|
|
router.post('/', authenticate, async (req, res) => {
|
|
try {
|
|
const {
|
|
txn_type, category_level1, category_level2,
|
|
project_id, user_id, user_name,
|
|
amount_original, currency, exchange_rate, amount_cny,
|
|
record_date,
|
|
counterparty_name, counterparty_type, counterparty_id,
|
|
source, source_id, source_code,
|
|
description, voucher_no, attachments
|
|
} = req.body;
|
|
|
|
if (!txn_type || !category_level1 || !category_level2 || !amount_original || !record_date) {
|
|
return res.status(400).json({ success: false, message: '缺少必填字段' });
|
|
}
|
|
|
|
const record_code = generateCode('FIN');
|
|
const calculated_amount_cny = amount_cny || (parseFloat(amount_original) * parseFloat(exchange_rate || 1));
|
|
|
|
const result = await db.query(
|
|
`INSERT INTO financial_records
|
|
(record_code, txn_type, category_level1, category_level2,
|
|
project_id, user_id, user_name,
|
|
amount_original, currency, exchange_rate, amount_cny,
|
|
record_date, counterparty_name, counterparty_type, counterparty_id,
|
|
source, source_id, source_code,
|
|
description, voucher_no, attachments)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)
|
|
RETURNING *`,
|
|
[record_code, txn_type, category_level1, category_level2,
|
|
project_id || null, user_id || null, user_name || null,
|
|
amount_original, currency || 'CNY', exchange_rate || 1, calculated_amount_cny,
|
|
record_date,
|
|
counterparty_name || null, counterparty_type || null, counterparty_id || null,
|
|
source || 'manual', source_id || null, source_code || null,
|
|
description || null, voucher_no || null, attachments || null]
|
|
);
|
|
|
|
res.json({ success: true, data: result.rows[0] });
|
|
} catch (error) {
|
|
console.error('创建财务记录失败:', error);
|
|
res.status(500).json({ success: false, message: '创建财务记录失败' });
|
|
}
|
|
});
|
|
|
|
router.post('/batch', authenticate, async (req, res) => {
|
|
try {
|
|
if (req.user.role !== 'admin') {
|
|
return res.status(403).json({ success: false, message: '无权限操作' });
|
|
}
|
|
|
|
const { records } = req.body;
|
|
if (!Array.isArray(records) || records.length === 0) {
|
|
return res.status(400).json({ success: false, message: '没有可导入的记录' });
|
|
}
|
|
|
|
const results = [];
|
|
const errors = [];
|
|
|
|
for (let i = 0; i < records.length; i++) {
|
|
const r = records[i];
|
|
try {
|
|
const missingFields = [];
|
|
if (!r.txn_type) missingFields.push('收支类型');
|
|
if (!r.category_level1) missingFields.push('一级分类');
|
|
if (!r.category_level2) missingFields.push('二级分类');
|
|
if (!r.amount_original) missingFields.push('金额');
|
|
if (!r.record_date) missingFields.push('日期');
|
|
if (missingFields.length > 0) {
|
|
errors.push({ row: i + 1, message: `缺少必填字段: ${missingFields.join(', ')}`, data: r });
|
|
continue;
|
|
}
|
|
|
|
const record_code = generateCode('FIN');
|
|
const calculated_amount_cny = r.amount_cny || (parseFloat(r.amount_original) * parseFloat(r.exchange_rate || 1));
|
|
|
|
let projectId = r.project_id || null;
|
|
if (!projectId && r.project_name) {
|
|
// 先精确匹配
|
|
const projResult = await db.query("SELECT id FROM projects WHERE name = $1", [r.project_name]);
|
|
if (projResult.rows.length > 0) {
|
|
projectId = projResult.rows[0].id;
|
|
} else {
|
|
// 模糊匹配：去除空格后比较
|
|
const fuzzyResult = await db.query(
|
|
"SELECT id, name FROM projects WHERE REPLACE(name, ' ', '') = REPLACE($1, ' ', '')",
|
|
[r.project_name]
|
|
);
|
|
if (fuzzyResult.rows.length > 0) {
|
|
projectId = fuzzyResult.rows[0].id;
|
|
} else {
|
|
// 包含匹配
|
|
const likeResult = await db.query(
|
|
"SELECT id, name FROM projects WHERE name LIKE '%' || $1 || '%' OR $1 LIKE '%' || name || '%'",
|
|
[r.project_name]
|
|
);
|
|
if (likeResult.rows.length > 0) {
|
|
projectId = likeResult.rows[0].id;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
let userId = r.user_id || null;
|
|
if (!userId && r.user_name) {
|
|
const userResult = await db.query("SELECT id FROM users WHERE name = $1 OR username = $1", [r.user_name]);
|
|
if (userResult.rows.length > 0) {
|
|
userId = userResult.rows[0].id;
|
|
}
|
|
}
|
|
|
|
const result = await db.query(
|
|
`INSERT INTO financial_records
|
|
(record_code, txn_type, category_level1, category_level2,
|
|
project_id, user_id, user_name,
|
|
amount_original, currency, exchange_rate, amount_cny,
|
|
record_date, counterparty_name, counterparty_type, counterparty_id,
|
|
source, source_id, source_code,
|
|
description, voucher_no)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
|
|
RETURNING *`,
|
|
[record_code, r.txn_type, r.category_level1, r.category_level2,
|
|
projectId, userId, r.user_name || null,
|
|
r.amount_original, r.currency || 'CNY', r.exchange_rate || 1, calculated_amount_cny,
|
|
r.record_date,
|
|
r.counterparty_name || null, r.counterparty_type || null, r.counterparty_id || null,
|
|
r.source || 'manual', r.source_id || null, r.source_code || null,
|
|
r.description || null, r.voucher_no || null]
|
|
);
|
|
results.push(result.rows[0]);
|
|
} catch (err) {
|
|
errors.push({ row: i + 1, message: err.message, data: { txn_type: r.txn_type, category_level1: r.category_level1, category_level2: r.category_level2, project_name: r.project_name, amount: r.amount_original } });
|
|
}
|
|
}
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
imported: results.length,
|
|
failed: errors.length,
|
|
errors
|
|
}
|
|
});
|
|
} catch (error) {
|
|
console.error('批量导入失败:', error);
|
|
res.status(500).json({ success: false, message: '批量导入失败' });
|
|
}
|
|
});
|
|
|
|
router.put('/:id', authenticate, async (req, res) => {
|
|
try {
|
|
if (req.user.role !== 'admin') {
|
|
return res.status(403).json({ success: false, message: '无权限操作' });
|
|
}
|
|
const { id } = req.params;
|
|
const fields = req.body;
|
|
const sets = [];
|
|
const params = [];
|
|
let idx = 1;
|
|
|
|
const allowedFields = ['category_level1', 'category_level2', 'description', 'counterparty_name', 'counterparty_type', 'voucher_no', 'status'];
|
|
for (const key of allowedFields) {
|
|
if (fields[key] !== undefined) {
|
|
params.push(fields[key]);
|
|
sets.push(`${key} = $${idx++}`);
|
|
}
|
|
}
|
|
|
|
if (sets.length === 0) {
|
|
return res.status(400).json({ success: false, message: '没有可更新的字段' });
|
|
}
|
|
|
|
params.push(id);
|
|
sets.push(`updated_at = CURRENT_TIMESTAMP`);
|
|
const result = await db.query(
|
|
`UPDATE financial_records SET ${sets.join(', ')} WHERE id = $${idx} RETURNING *`,
|
|
params
|
|
);
|
|
if (result.rows.length === 0) {
|
|
return res.status(404).json({ success: false, message: '记录不存在' });
|
|
}
|
|
res.json({ success: true, data: result.rows[0] });
|
|
} catch (error) {
|
|
console.error('更新财务记录失败:', error);
|
|
res.status(500).json({ success: false, message: '更新财务记录失败' });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|