505 lines
No EOL
16 KiB
JavaScript
505 lines
No EOL
16 KiB
JavaScript
const express = require('express');
|
|
const router = express.Router();
|
|
const {
|
|
unitBalanceRepository,
|
|
invoiceRepository,
|
|
paymentRepository,
|
|
expenseRepository,
|
|
transactionRepository,
|
|
transactionLineRepository
|
|
} = require('../repositories');
|
|
const SiteRepository = require('../../../src/repositories/siteRepository');
|
|
const siteRepository = new SiteRepository();
|
|
|
|
/**
|
|
* Calculate unit balance based on real data:
|
|
* 1. Starting outstanding balance
|
|
* 2. Monthly fees (generated automatically)
|
|
* 3. Additional invoices (services, work orders, etc.)
|
|
* 4. Minus any payments made
|
|
*/
|
|
async function calculateUnitBalance(unitId, siteId) {
|
|
try {
|
|
// Get opening balance transactions for this unit
|
|
const openingBalanceTransactions = await transactionRepository.findByUnitIdAndType(
|
|
unitId,
|
|
'opening_balance'
|
|
);
|
|
const startingBalance = openingBalanceTransactions.reduce(
|
|
(sum, t) => sum + parseFloat(t.amount || 0),
|
|
0
|
|
);
|
|
|
|
// Get all adjustment transactions for this unit
|
|
const adjustmentTransactions = await transactionRepository.findByUnitIdAndType(
|
|
unitId,
|
|
'adjustment'
|
|
);
|
|
const adjustments = adjustmentTransactions.reduce(
|
|
(sum, t) => sum + parseFloat(t.amount || 0),
|
|
0
|
|
);
|
|
|
|
// Get all invoices for this unit
|
|
const invoices = await invoiceRepository.findByUnitId(unitId);
|
|
|
|
// Separate invoices by type
|
|
let monthlyFees = 0;
|
|
let serviceInvoices = 0;
|
|
let workOrderInvoices = 0;
|
|
|
|
for (const invoice of invoices) {
|
|
const amount = parseFloat(invoice.amount || 0);
|
|
if (invoice.invoice_type === 'monthly_fee') {
|
|
monthlyFees += amount;
|
|
} else if (invoice.invoice_type === 'service') {
|
|
serviceInvoices += amount;
|
|
} else if (invoice.invoice_type === 'work_order') {
|
|
workOrderInvoices += amount;
|
|
} else if (invoice.invoice_type === 'other') {
|
|
serviceInvoices += amount; // Treat other invoices as service invoices
|
|
}
|
|
}
|
|
|
|
// Get all payments for this unit
|
|
const payments = await paymentRepository.findByUnitId(unitId);
|
|
const totalPayments = payments.reduce((sum, payment) => {
|
|
return sum + parseFloat(payment.payment_amount || 0);
|
|
}, 0);
|
|
|
|
// Calculate totals: opening balance + adjustments + invoices - payments
|
|
const totalCharges = startingBalance + adjustments + monthlyFees + serviceInvoices + workOrderInvoices;
|
|
const currentBalance = totalCharges - totalPayments;
|
|
|
|
return {
|
|
unitId,
|
|
siteId,
|
|
// Balance components
|
|
startingBalance,
|
|
adjustments,
|
|
monthlyFees,
|
|
serviceInvoices,
|
|
workOrderInvoices,
|
|
totalCharges,
|
|
totalPayments,
|
|
currentBalance,
|
|
lastUpdated: new Date().toISOString(),
|
|
// Indicate if opening balance transaction exists
|
|
hasOpeningBalanceRecord: openingBalanceTransactions.length > 0,
|
|
breakdown: {
|
|
pendingInvoices: currentBalance > 0 ? Math.max(0, currentBalance) : 0,
|
|
overdueAmount: currentBalance > 2000 ? Math.max(0, currentBalance - 2000) : 0,
|
|
creditBalance: currentBalance < 0 ? Math.abs(currentBalance) : 0
|
|
},
|
|
// Summary for easy understanding
|
|
summary: {
|
|
totalOwed: Math.max(0, currentBalance),
|
|
totalCredit: Math.abs(Math.min(0, currentBalance)),
|
|
status: currentBalance > 0 ? 'outstanding' : currentBalance < 0 ? 'credit' : 'paid_up'
|
|
}
|
|
};
|
|
} catch (error) {
|
|
console.error('Error calculating unit balance:', error);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* GET /api/plugins/financials/balances/units/:unitId
|
|
* Get single unit balance
|
|
*/
|
|
router.get('/units/:unitId', async (req, res) => {
|
|
try {
|
|
const { unitId } = req.params;
|
|
const { site_id } = req.query;
|
|
|
|
if (!site_id) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'site_id query parameter is required'
|
|
});
|
|
}
|
|
|
|
const balance = await calculateUnitBalance(unitId, site_id);
|
|
|
|
res.json({
|
|
success: true,
|
|
data: balance,
|
|
message: 'Unit balance retrieved successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to get unit balance:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to retrieve unit balance',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
/**
|
|
* POST /api/plugins/financials/balances/units/batch
|
|
* Get multiple unit balances
|
|
*/
|
|
router.post('/units/batch', async (req, res) => {
|
|
try {
|
|
const { unit_ids, site_id } = req.body;
|
|
|
|
if (!unit_ids || !Array.isArray(unit_ids)) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'unit_ids array is required'
|
|
});
|
|
}
|
|
|
|
if (!site_id) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'site_id is required'
|
|
});
|
|
}
|
|
|
|
const balances = {};
|
|
|
|
// Calculate balance for each unit
|
|
for (const unitId of unit_ids) {
|
|
try {
|
|
const balance = await calculateUnitBalance(unitId, site_id);
|
|
balances[unitId] = balance.currentBalance;
|
|
} catch (error) {
|
|
console.error(`Error calculating balance for unit ${unitId}:`, error);
|
|
balances[unitId] = 0; // Default to 0 on error
|
|
}
|
|
}
|
|
|
|
res.json({
|
|
success: true,
|
|
data: balances,
|
|
message: 'Unit balances retrieved successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to get unit balances:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to retrieve unit balances',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
/**
|
|
* POST /api/plugins/financials/balances/units/:unitId/starting-balance
|
|
* Set starting balance for a unit (HA admin function)
|
|
* Creates an opening_balance transaction instead of storing in starting_balance field
|
|
*/
|
|
router.post('/units/:unitId/starting-balance', async (req, res) => {
|
|
try {
|
|
const { unitId } = req.params;
|
|
const { site_id, starting_balance, notes } = req.body;
|
|
|
|
if (!site_id) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'site_id is required'
|
|
});
|
|
}
|
|
|
|
if (typeof starting_balance !== 'number') {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'starting_balance must be a number'
|
|
});
|
|
}
|
|
|
|
// Check if opening balance transaction already exists for this unit
|
|
const existingOpeningBalance = await transactionRepository.findByUnitIdAndType(
|
|
unitId,
|
|
'opening_balance'
|
|
);
|
|
|
|
if (existingOpeningBalance.length > 0) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'Opening balance already set for this unit. Use adjustment transaction to modify balance.'
|
|
});
|
|
}
|
|
|
|
// Get site to retrieve currency from settings
|
|
const site = await siteRepository.findById(site_id);
|
|
const siteCurrency = site?.settings?.currency || 'USD';
|
|
|
|
// Create opening balance transaction
|
|
// IMPORTANT: Preserve the sign - negative balances mean HOA owes unit (credit), positive means unit owes HOA (debit)
|
|
const transaction = await transactionRepository.createTransaction({
|
|
site_id,
|
|
unit_id: unitId,
|
|
transaction_date: new Date().toISOString().split('T')[0],
|
|
description: `Opening balance${notes ? ': ' + notes : ''}`,
|
|
transaction_type: 'opening_balance',
|
|
amount: starting_balance, // Preserve sign - do NOT use Math.abs()
|
|
currency: siteCurrency, // Use site's currency from settings
|
|
status: 'posted',
|
|
created_by: req.user?.id || 'system',
|
|
notes: notes || 'Opening balance set'
|
|
});
|
|
|
|
// Create or update unit balance record (for backward compatibility, but balance is calculated from transactions)
|
|
const balance = await unitBalanceRepository.createOrUpdateUnitBalance({
|
|
unit_id: unitId,
|
|
site_id: site_id,
|
|
current_balance: starting_balance,
|
|
created_by: req.user?.id || 'system'
|
|
});
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
unit_id: unitId,
|
|
site_id,
|
|
transaction_id: transaction.id,
|
|
starting_balance,
|
|
message: 'Starting balance set successfully'
|
|
},
|
|
message: 'Starting balance updated successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to set starting balance:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to set starting balance',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/plugins/financials/balances/buildings/:buildingId
|
|
* Get building-level financial summary
|
|
*/
|
|
router.get('/buildings/:buildingId', async (req, res) => {
|
|
try {
|
|
const { buildingId } = req.params;
|
|
const { site_id } = req.query;
|
|
|
|
if (!site_id) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'site_id query parameter is required'
|
|
});
|
|
}
|
|
|
|
// Get units repository to find all units in this building
|
|
const UnitsRepository = require('../../../src/repositories/unitsRepository');
|
|
const unitsRepo = new UnitsRepository();
|
|
|
|
// Get all units for this building
|
|
const units = await unitsRepo.findAll({
|
|
building_id: buildingId,
|
|
is_deleted: false
|
|
});
|
|
|
|
if (!units || units.length === 0) {
|
|
return res.json({
|
|
success: true,
|
|
data: {
|
|
buildingId,
|
|
siteId: site_id,
|
|
totalUnits: 0,
|
|
totalOutstanding: 0,
|
|
totalCollected: 0,
|
|
totalMonthlyFees: 0,
|
|
averageBalance: 0
|
|
},
|
|
message: 'Building financial summary retrieved successfully'
|
|
});
|
|
}
|
|
|
|
// Calculate financial summary for all units
|
|
let totalOutstanding = 0; // Amount owed by units (opening balances are typically stored negative)
|
|
let totalCollected = 0;
|
|
let totalMonthlyFees = 0;
|
|
let totalCharges = 0;
|
|
let totalHOAOwes = 0; // HOA owes to units (negative balances/credits)
|
|
const unitIds = units.map(unit => unit.id);
|
|
|
|
for (const unit of units) {
|
|
try {
|
|
const balance = await calculateUnitBalance(unit.id, site_id);
|
|
const current = Number(balance.currentBalance || 0);
|
|
const payments = Number(balance.totalPayments || 0);
|
|
const creditToUnit = Math.max(0, current); // HOA owes unit
|
|
const owedByUnit = Math.max(0, -current); // Unit owes HOA
|
|
|
|
totalOutstanding += owedByUnit; // negatives only
|
|
totalHOAOwes += creditToUnit; // positives only
|
|
totalCollected += payments + creditToUnit; // cash + prepaid
|
|
totalMonthlyFees += balance.monthlyFees;
|
|
totalCharges += balance.totalCharges;
|
|
} catch (error) {
|
|
console.error(`Error calculating balance for unit ${unit.id}:`, error);
|
|
// Continue with next unit if one fails
|
|
}
|
|
}
|
|
|
|
// Calculate HOA owes to vendors (pending expenses)
|
|
let totalHOAOwesVendors = 0;
|
|
try {
|
|
const pendingExpenses = await expenseRepository.findByStatus(site_id, 'pending');
|
|
totalHOAOwesVendors = pendingExpenses.reduce((sum, expense) => {
|
|
return sum + parseFloat(expense.total_amount || 0);
|
|
}, 0);
|
|
} catch (error) {
|
|
console.error('Error calculating pending expenses:', error);
|
|
}
|
|
|
|
// Total HOA owes = credits to units + pending vendor expenses
|
|
const totalHOAOwesAll = totalHOAOwes + totalHOAOwesVendors;
|
|
|
|
// Average balance: total outstanding divided by number of units
|
|
const averageBalance = unitIds.length > 0 ? totalOutstanding / unitIds.length : 0;
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
buildingId,
|
|
siteId: site_id,
|
|
totalUnits: unitIds.length,
|
|
totalOutstanding, // Others owe to HOA
|
|
totalHOAOwesUnits: totalHOAOwes, // HOA owes to units (credits)
|
|
totalHOAOwesVendors, // HOA owes to vendors (pending expenses)
|
|
totalHOAOwes: totalHOAOwesAll, // Total HOA owes
|
|
totalCollected,
|
|
totalMonthlyFees,
|
|
totalCharges,
|
|
averageBalance,
|
|
unitIds
|
|
},
|
|
message: 'Building financial summary retrieved successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to get building financial summary:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to retrieve building financial summary',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/plugins/financials/balances/site/:siteId
|
|
* Get site-level financial summary (HOA owes vs owed)
|
|
*/
|
|
router.get('/site/:siteId', async (req, res) => {
|
|
try {
|
|
const { siteId } = req.params;
|
|
|
|
// Get all units for the site
|
|
const UnitsRepository = require('../../../src/repositories/unitsRepository');
|
|
const unitsRepo = new UnitsRepository();
|
|
|
|
const units = await unitsRepo.findAll({
|
|
site_id: siteId,
|
|
is_deleted: false
|
|
});
|
|
|
|
let totalOwedToHOA = 0; // Others owe to HOA (positive balances = receivables)
|
|
let totalHOAOwesUnits = 0; // HOA owes to units (negative balances/credits)
|
|
let totalCollected = 0;
|
|
let totalMonthlyFees = 0;
|
|
|
|
for (const unit of units || []) {
|
|
try {
|
|
const balance = await calculateUnitBalance(unit.id, siteId);
|
|
const current = Number(balance.currentBalance || 0);
|
|
const payments = Number(balance.totalPayments || 0);
|
|
const creditToUnit = Math.max(0, current); // HOA owes unit
|
|
const owedByUnit = Math.max(0, -current); // Unit owes HOA
|
|
|
|
totalOwedToHOA += owedByUnit; // negatives only
|
|
totalHOAOwesUnits += creditToUnit; // positives only
|
|
totalCollected += payments + creditToUnit; // cash + prepaid
|
|
totalMonthlyFees += balance.monthlyFees;
|
|
} catch (error) {
|
|
console.error(`Error calculating balance for unit ${unit.id}:`, error);
|
|
}
|
|
}
|
|
|
|
// Calculate HOA owes to vendors (pending expenses)
|
|
let totalHOAOwesVendors = 0;
|
|
try {
|
|
const pendingExpenses = await expenseRepository.findByStatus(siteId, 'pending');
|
|
totalHOAOwesVendors = pendingExpenses.reduce((sum, expense) => {
|
|
return sum + parseFloat(expense.total_amount || 0);
|
|
}, 0);
|
|
} catch (error) {
|
|
console.error('Error calculating pending expenses:', error);
|
|
}
|
|
|
|
// Total HOA owes = credits to units + pending vendor expenses
|
|
const totalHOAOwes = totalHOAOwesUnits + totalHOAOwesVendors;
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
siteId,
|
|
totalOwedToHOA, // Others owe to HOA
|
|
totalHOAOwesUnits, // HOA owes to units
|
|
totalHOAOwesVendors, // HOA owes to vendors
|
|
totalHOAOwes, // Total HOA owes
|
|
totalCollected,
|
|
totalMonthlyFees,
|
|
totalUnits: units?.length || 0
|
|
},
|
|
message: 'Site financial summary retrieved successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to get site financial summary:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to retrieve site financial summary',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
/**
|
|
* POST /api/plugins/financials/balances/generate-monthly-fees
|
|
* Generate monthly fee invoices for all units (scheduled job)
|
|
*/
|
|
router.post('/generate-monthly-fees', async (req, res) => {
|
|
try {
|
|
const { site_id, month, year } = req.body;
|
|
|
|
if (!site_id || !month || !year) {
|
|
return res.status(400).json({
|
|
success: false,
|
|
error: 'site_id, month, and year are required'
|
|
});
|
|
}
|
|
|
|
// TODO: Implement monthly fee generation from unit_monthly_fees table
|
|
// For now, return success with 0 invoices
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
site_id,
|
|
month,
|
|
year,
|
|
invoices_created: 0, // Will be implemented in future session
|
|
message: 'Monthly fee invoices generated successfully'
|
|
},
|
|
message: 'Monthly fee invoices generated successfully'
|
|
});
|
|
} catch (error) {
|
|
console.error('Failed to generate monthly fees:', error);
|
|
res.status(500).json({
|
|
success: false,
|
|
error: 'Failed to generate monthly fees',
|
|
message: error.message
|
|
});
|
|
}
|
|
});
|
|
|
|
module.exports = router; |