204 lines
8.3 KiB
JavaScript
204 lines
8.3 KiB
JavaScript
const express = require('express');
|
||
const db = require('../db');
|
||
const { authenticate, requireAdmin } = require('../middleware/auth');
|
||
|
||
const router = express.Router();
|
||
|
||
// 获取项目收款记录
|
||
router.get('/', async (req, res) => {
|
||
try {
|
||
const { projectId } = req.query;
|
||
if (!projectId) {
|
||
return res.status(400).json({ success: false, message: '缺少项目ID' });
|
||
}
|
||
const result = await db.query(
|
||
`SELECT r.*, m.milestone_name, m.percentage as milestone_percentage,
|
||
u.name as creator_name
|
||
FROM project_receipts r
|
||
LEFT JOIN project_milestones m ON r.milestone_id = m.id
|
||
LEFT JOIN users u ON r.created_by = u.id
|
||
WHERE r.project_id = $1
|
||
ORDER BY r.receipt_date DESC, r.created_at DESC`,
|
||
[projectId]
|
||
);
|
||
res.json({ success: true, data: result.rows });
|
||
} catch (error) {
|
||
console.error('获取收款记录失败:', error);
|
||
res.status(500).json({ success: false, message: '获取收款记录失败' });
|
||
}
|
||
});
|
||
|
||
// 获取项目付款节点列表（用于收款时选择）
|
||
router.get('/milestones', async (req, res) => {
|
||
try {
|
||
const { projectId } = req.query;
|
||
if (!projectId) {
|
||
return res.status(400).json({ success: false, message: '缺少项目ID' });
|
||
}
|
||
const result = await db.query(
|
||
`SELECT id, milestone_name, percentage, amount, status, completion_progress
|
||
FROM project_milestones WHERE project_id = $1 ORDER BY id`,
|
||
[projectId]
|
||
);
|
||
res.json({ success: true, data: result.rows });
|
||
} catch (error) {
|
||
console.error('获取付款节点失败:', error);
|
||
res.status(500).json({ success: false, message: '获取付款节点失败' });
|
||
}
|
||
});
|
||
|
||
// 新增收款记录（同时写入 financial_records 统一记账）
|
||
router.post('/', authenticate, async (req, res) => {
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
const { project_id, receipt_type, milestone_id, amount, currency, exchange_rate, receipt_date, payer_name, description, voucher_url, counterparty_id } = req.body;
|
||
const userId = req.user?.id || req.user?.userId;
|
||
|
||
if (!project_id) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(400).json({ success: false, message: '缺少项目ID' });
|
||
}
|
||
|
||
const amt = parseFloat(amount) || 0;
|
||
const rate = parseFloat(exchange_rate) || 1;
|
||
const amountCny = parseFloat((amt * rate).toFixed(2));
|
||
|
||
// 1. 写入 financial_records（统一记账）
|
||
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}`;
|
||
|
||
// 确定分类
|
||
let categoryLevel2 = 'contract_payment';
|
||
if (receipt_type === 'advance') {
|
||
categoryLevel2 = 'customer_advance';
|
||
} else if (receipt_type === 'other') {
|
||
categoryLevel2 = 'other_income';
|
||
}
|
||
|
||
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, status)
|
||
VALUES ($1, 'income', $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, 'customer', $13, 'receipt', $14, $15, 'confirmed')
|
||
RETURNING id`,
|
||
[
|
||
recordCode, 'income', categoryLevel2, project_id,
|
||
userId, req.user?.name || req.user?.username || '',
|
||
amt, currency || 'CNY', rate, amountCny,
|
||
receipt_date, payer_name || '',
|
||
counterparty_id || null,
|
||
recordCode, description || ''
|
||
]
|
||
);
|
||
const financialRecordId = frResult.rows[0].id;
|
||
|
||
// 2. 写入 project_receipts
|
||
const receiptResult = 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, receipt_type || 'node', milestone_id || null, amt, currency || 'CNY', rate, amountCny,
|
||
receipt_date, payer_name || '', description || '', voucher_url || '', financialRecordId, userId]
|
||
);
|
||
|
||
// 3. 如果是节点收款，更新里程碑完成进度
|
||
if (receipt_type === 'node' && milestone_id) {
|
||
const msResult = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [milestone_id]);
|
||
if (msResult.rows.length > 0 && parseFloat(msResult.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'`,
|
||
[milestone_id]
|
||
);
|
||
const progress = Math.min(100, Math.round((parseFloat(totalReceived.rows[0].total) / parseFloat(msResult.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, receipt_date, milestone_id]
|
||
);
|
||
}
|
||
}
|
||
|
||
await client.query('COMMIT');
|
||
|
||
// 返回带里程碑名称的完整记录
|
||
const fullResult = await db.query(
|
||
`SELECT r.*, m.milestone_name FROM project_receipts r LEFT JOIN project_milestones m ON r.milestone_id = m.id WHERE r.id = $1`,
|
||
[receiptResult.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();
|
||
}
|
||
});
|
||
|
||
// 删除收款记录（同时删除 financial_records）
|
||
router.delete('/:receiptId', authenticate, async (req, res) => {
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
const { receiptId } = req.params;
|
||
|
||
const receiptResult = await client.query(
|
||
'SELECT * FROM project_receipts WHERE id = $1',
|
||
[receiptId]
|
||
);
|
||
if (receiptResult.rowCount === 0) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(404).json({ success: false, message: '收款记录不存在' });
|
||
}
|
||
|
||
const receipt = receiptResult.rows[0];
|
||
const financialRecordId = receipt.financial_record_id;
|
||
|
||
// 先删除 project_receipts（引用方），再删除 financial_records（被引用方）
|
||
await client.query('DELETE FROM project_receipts WHERE id = $1', [receiptId]);
|
||
|
||
if (financialRecordId) {
|
||
await client.query('DELETE FROM financial_records WHERE id = $1', [financialRecordId]);
|
||
}
|
||
|
||
// 如果是节点收款，重新计算里程碑进度
|
||
if (receipt.receipt_type === 'node' && receipt.milestone_id) {
|
||
const msResult = await client.query('SELECT amount FROM project_milestones WHERE id = $1', [receipt.milestone_id]);
|
||
if (msResult.rows.length > 0 && parseFloat(msResult.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(msResult.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('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;
|