281 lines
11 KiB
JavaScript
281 lines
11 KiB
JavaScript
|
const express = require('express');
|
||
|
|
const db = require('../db');
|
||
|
|
const { authenticate, requireAdmin } = require('../middleware/auth');
|
||
|
|
|
||
|
|
const router = express.Router();
|
||
|
|
|
||
|
|
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('/summary', authenticate, async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { date_from, date_to } = req.query;
|
||
|
|
let where = "WHERE status != 'voided'";
|
||
|
|
const params = [];
|
||
|
|
let idx = 1;
|
||
|
|
|
||
|
|
if (date_from) { params.push(date_from); where += ` AND record_date >= $${idx++}`; }
|
||
|
|
if (date_to) { params.push(date_to); where += ` AND record_date <= $${idx++}`; }
|
||
|
|
|
||
|
|
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_amount
|
||
|
|
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 recentResult = await db.query(
|
||
|
|
`SELECT fr.*, p.name as project_name
|
||
|
|
FROM financial_records fr
|
||
|
|
LEFT JOIN projects p ON fr.project_id = p.id
|
||
|
|
${where}
|
||
|
|
ORDER BY fr.record_date DESC, fr.created_at DESC
|
||
|
|
LIMIT 20`,
|
||
|
|
params
|
||
|
|
);
|
||
|
|
|
||
|
|
res.json({
|
||
|
|
success: true,
|
||
|
|
data: {
|
||
|
|
totals: totalResult.rows[0],
|
||
|
|
byCategory: byCategory.rows,
|
||
|
|
recent: recentResult.rows
|
||
|
|
}
|
||
|
|
});
|
||
|
|
} catch (error) {
|
||
|
|
console.error('获取资金概览失败:', error);
|
||
|
|
res.status(500).json({ success: false, message: '获取资金概览失败' });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
router.get('/records', authenticate, async (req, res) => {
|
||
|
|
try {
|
||
|
|
const {
|
||
|
|
txn_type, category_level1, category_level2,
|
||
|
|
project_id, date_from, date_to,
|
||
|
|
page = 1, pageSize = 20
|
||
|
|
} = 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 idx = 1;
|
||
|
|
|
||
|
|
if (txn_type) { params.push(txn_type); sql += ` AND fr.txn_type = $${idx++}`; }
|
||
|
|
if (category_level1) { params.push(category_level1); sql += ` AND fr.category_level1 = $${idx++}`; }
|
||
|
|
if (category_level2) { params.push(category_level2); sql += ` AND fr.category_level2 = $${idx++}`; }
|
||
|
|
if (project_id) { params.push(project_id); sql += ` AND fr.project_id = $${idx++}`; }
|
||
|
|
if (date_from) { params.push(date_from); sql += ` AND fr.record_date >= $${idx++}`; }
|
||
|
|
if (date_to) { params.push(date_to); sql += ` AND fr.record_date <= $${idx++}`; }
|
||
|
|
|
||
|
|
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 $${idx++}`;
|
||
|
|
params.push(offset);
|
||
|
|
sql += ` OFFSET $${idx++}`;
|
||
|
|
|
||
|
|
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.post('/', authenticate, async (req, res) => {
|
||
|
|
const client = await db.pool.connect();
|
||
|
|
try {
|
||
|
|
await client.query('BEGIN');
|
||
|
|
const {
|
||
|
|
txn_type, category_level1, category_level2,
|
||
|
|
project_id, amount, currency, exchange_rate,
|
||
|
|
record_date, counterparty_name, counterparty_type, counterparty_id,
|
||
|
|
description, voucher_url
|
||
|
|
} = req.body;
|
||
|
|
|
||
|
|
const userId = req.user?.id || req.user?.userId;
|
||
|
|
|
||
|
|
if (!txn_type || !category_level1 || !category_level2 || !amount || !record_date) {
|
||
|
|
await client.query('ROLLBACK');
|
||
|
|
return res.status(400).json({ success: false, message: '缺少必填字段' });
|
||
|
|
}
|
||
|
|
|
||
|
|
const amt = parseFloat(amount) || 0;
|
||
|
|
const rate = parseFloat(exchange_rate) || 1;
|
||
|
|
const amountCny = parseFloat((amt * rate).toFixed(2));
|
||
|
|
|
||
|
|
const today = new Date();
|
||
|
|
const dateStr = today.toISOString().slice(0, 10).replace(/-/g, '');
|
||
|
|
const codeResult = await client.query(
|
||
|
|
"SELECT COUNT(*) as cnt FROM financial_records WHERE record_code LIKE $1",
|
||
|
|
[`FIN-${dateStr}%`]
|
||
|
|
);
|
||
|
|
const seq = String(parseInt(codeResult.rows[0].cnt) + 1).padStart(4, '0');
|
||
|
|
const recordCode = `FIN-${dateStr}-${seq}`;
|
||
|
|
|
||
|
|
const frResult = await client.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_code, description, attachments, status)
|
||
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, 'cash_management', $16, $17, $18, 'confirmed')
|
||
|
|
RETURNING *`,
|
||
|
|
[
|
||
|
|
recordCode, txn_type, category_level1, category_level2,
|
||
|
|
project_id || null, userId, req.user?.name || req.user?.username || '',
|
||
|
|
amt, currency || 'CNY', rate, amountCny,
|
||
|
|
record_date,
|
||
|
|
counterparty_name || null, counterparty_type || null, counterparty_id || null,
|
||
|
|
recordCode, description || null, voucher_url || null
|
||
|
|
]
|
||
|
|
);
|
||
|
|
|
||
|
|
if (txn_type === 'income' && project_id && ['contract_payment', 'customer_advance'].includes(category_level2)) {
|
||
|
|
const msResult = await client.query(
|
||
|
|
'SELECT id FROM project_milestones WHERE project_id = $1 ORDER BY id LIMIT 1',
|
||
|
|
[project_id]
|
||
|
|
);
|
||
|
|
if (msResult.rows.length > 0) {
|
||
|
|
const milestoneId = msResult.rows[0].id;
|
||
|
|
const msData = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [milestoneId]);
|
||
|
|
if (msData.rows.length > 0 && parseFloat(msData.rows[0].amount) > 0) {
|
||
|
|
const prResult = await client.query(
|
||
|
|
`INSERT INTO project_receipts
|
||
|
|
(project_id, receipt_type, milestone_id, amount, currency, exchange_rate, amount_cny,
|
||
|
|
receipt_date, payer_name, description, voucher_url, financial_record_id, created_by)
|
||
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
|
||
|
|
RETURNING *`,
|
||
|
|
[
|
||
|
|
project_id,
|
||
|
|
category_level2 === 'customer_advance' ? 'advance' : 'node',
|
||
|
|
milestoneId, amt, currency || 'CNY', rate, amountCny,
|
||
|
|
record_date, counterparty_name || '', description || '', voucher_url || '',
|
||
|
|
frResult.rows[0].id, userId
|
||
|
|
]
|
||
|
|
);
|
||
|
|
|
||
|
|
const totalReceived = await client.query(
|
||
|
|
`SELECT COALESCE(SUM(amount), 0) as total FROM project_receipts
|
||
|
|
WHERE milestone_id = $1 AND receipt_type = 'node'`,
|
||
|
|
[milestoneId]
|
||
|
|
);
|
||
|
|
const progress = Math.min(100, Math.round((parseFloat(totalReceived.rows[0].total) / parseFloat(msData.rows[0].amount)) * 100));
|
||
|
|
await client.query(
|
||
|
|
`UPDATE project_milestones SET completion_progress = $1::numeric,
|
||
|
|
status = CASE WHEN $1::numeric >= 100 THEN 'completed' ELSE 'in_progress' END,
|
||
|
|
actual_date = CASE WHEN $1::numeric >= 100 THEN $2 ELSE actual_date END
|
||
|
|
WHERE id = $3`,
|
||
|
|
[progress, record_date, milestoneId]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
await client.query('COMMIT');
|
||
|
|
|
||
|
|
const fullResult = await db.query(
|
||
|
|
`SELECT fr.*, p.name as project_name FROM financial_records fr LEFT JOIN projects p ON fr.project_id = p.id WHERE fr.id = $1`,
|
||
|
|
[frResult.rows[0].id]
|
||
|
|
);
|
||
|
|
|
||
|
|
res.json({ success: true, data: fullResult.rows[0] });
|
||
|
|
} catch (error) {
|
||
|
|
await client.query('ROLLBACK');
|
||
|
|
console.error('新增收支记录失败:', error);
|
||
|
|
res.status(500).json({ success: false, message: '新增收支记录失败' });
|
||
|
|
} finally {
|
||
|
|
client.release();
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
router.delete('/:id', authenticate, async (req, res) => {
|
||
|
|
const client = await db.pool.connect();
|
||
|
|
try {
|
||
|
|
await client.query('BEGIN');
|
||
|
|
const { id } = req.params;
|
||
|
|
|
||
|
|
const recordResult = await client.query(
|
||
|
|
`SELECT * FROM financial_records WHERE id = $1 AND source = 'cash_management'`,
|
||
|
|
[id]
|
||
|
|
);
|
||
|
|
if (recordResult.rowCount === 0) {
|
||
|
|
await client.query('ROLLBACK');
|
||
|
|
return res.status(404).json({ success: false, message: '记录不存在或无权删除' });
|
||
|
|
}
|
||
|
|
|
||
|
|
const record = recordResult.rows[0];
|
||
|
|
|
||
|
|
if (record.txn_type === 'income' && record.project_id) {
|
||
|
|
const prResult = await client.query(
|
||
|
|
'SELECT id FROM project_receipts WHERE financial_record_id = $1',
|
||
|
|
[id]
|
||
|
|
);
|
||
|
|
if (prResult.rows.length > 0) {
|
||
|
|
const receipt = prResult.rows[0];
|
||
|
|
await client.query('DELETE FROM project_receipts WHERE id = $1', [receipt.id]);
|
||
|
|
|
||
|
|
if (receipt.milestone_id) {
|
||
|
|
const msData = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [receipt.milestone_id]);
|
||
|
|
if (msData.rows.length > 0 && parseFloat(msData.rows[0].amount) > 0) {
|
||
|
|
const totalReceived = await client.query(
|
||
|
|
`SELECT COALESCE(SUM(amount), 0) as total FROM project_receipts
|
||
|
|
WHERE milestone_id = $1 AND receipt_type = 'node'`,
|
||
|
|
[receipt.milestone_id]
|
||
|
|
);
|
||
|
|
const progress = Math.min(100, Math.round((parseFloat(totalReceived.rows[0].total) / parseFloat(msData.rows[0].amount)) * 100));
|
||
|
|
await client.query(
|
||
|
|
`UPDATE project_milestones SET completion_progress = $1::numeric,
|
||
|
|
status = CASE WHEN $1::numeric >= 100 THEN 'completed' WHEN $1::numeric > 0 THEN 'in_progress' ELSE 'pending' END
|
||
|
|
WHERE id = $2`,
|
||
|
|
[progress, receipt.milestone_id]
|
||
|
|
);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
await client.query('DELETE FROM financial_records WHERE id = $1', [id]);
|
||
|
|
|
||
|
|
await client.query('COMMIT');
|
||
|
|
res.json({ success: true, message: '删除成功' });
|
||
|
|
} catch (error) {
|
||
|
|
await client.query('ROLLBACK');
|
||
|
|
console.error('删除收支记录失败:', error);
|
||
|
|
res.status(500).json({ success: false, message: '删除收支记录失败' });
|
||
|
|
} finally {
|
||
|
|
client.release();
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
module.exports = router;
|