244 lines
8.8 KiB
JavaScript
244 lines
8.8 KiB
JavaScript
// controllers/reportController.js
|
|
import supabase from '../config/supabaseClient.js';
|
|
|
|
/** Build start/end Date objects for a given range */
|
|
function getDateRange(range = 'month', options = {}) {
|
|
const now = options.now ? new Date(options.now) : new Date();
|
|
const year = options.year ? parseInt(options.year, 10) : now.getFullYear();
|
|
|
|
let start, end;
|
|
|
|
switch (range) {
|
|
case 'today': {
|
|
const d = options.date ? new Date(options.date) : now;
|
|
start = new Date(d.getFullYear(), d.getMonth(), d.getDate(), 0, 0, 0);
|
|
end = new Date(d.getFullYear(), d.getMonth(), d.getDate(), 23, 59, 59);
|
|
break;
|
|
}
|
|
|
|
case 'week': {
|
|
// Last 7 days including today
|
|
end = new Date(now.getFullYear(), now.getMonth(), now.getDate(), 23, 59, 59);
|
|
start = new Date(end);
|
|
start.setDate(end.getDate() - 6);
|
|
start.setHours(0, 0, 0);
|
|
break;
|
|
}
|
|
|
|
case 'month': {
|
|
const month = options.month ? parseInt(options.month, 10) - 1 : now.getMonth();
|
|
start = new Date(year, month, 1, 0, 0, 0);
|
|
end = new Date(year, month + 1, 0, 23, 59, 59);
|
|
break;
|
|
}
|
|
|
|
case 'quarter': {
|
|
const q = options.quarter ? parseInt(options.quarter, 10) : Math.floor(now.getMonth() / 3) + 1;
|
|
const startMonth = (q - 1) * 3;
|
|
start = new Date(year, startMonth, 1, 0, 0, 0);
|
|
end = new Date(year, startMonth + 3, 0, 23, 59, 59);
|
|
break;
|
|
}
|
|
|
|
case 'year':
|
|
default: {
|
|
start = new Date(year, 0, 1, 0, 0, 0);
|
|
end = new Date(year, 11, 31, 23, 59, 59);
|
|
break;
|
|
}
|
|
}
|
|
|
|
return { start, end }; // Return Date objects, not ISO strings
|
|
}
|
|
|
|
/** Fetch sales between two dates */
|
|
async function fetchSalesInRange(start, end) {
|
|
// Convert to ISO for Supabase filtering (UTC)
|
|
const { data, error } = await supabase
|
|
.from('sales')
|
|
.select(`
|
|
sale_id,
|
|
sale_date,
|
|
total_amount,
|
|
status,
|
|
sale_items(
|
|
sale_item_id,
|
|
product_id,
|
|
quantity,
|
|
price
|
|
)
|
|
`)
|
|
.gte('sale_date', start.toISOString())
|
|
.lte('sale_date', end.toISOString());
|
|
|
|
if (error) throw error;
|
|
return data || [];
|
|
}
|
|
|
|
/** GET /api/reports/summary */
|
|
async function getSummary(req, res) {
|
|
try {
|
|
const { range = 'month', year, month, quarter, date } = req.query;
|
|
const dr = getDateRange(range, { year, month, quarter, date });
|
|
|
|
const sales = await fetchSalesInRange(dr.start, dr.end);
|
|
|
|
const completed = sales.filter(s => s.status === 'completed');
|
|
const refunded = sales.filter(s => s.status === 'refunded');
|
|
const voided = sales.filter(s => s.status === 'voided');
|
|
|
|
const total_orders = completed.length;
|
|
const total_sales = completed.reduce((acc, s) => acc + (Number(s.total_amount) || 0), 0);
|
|
const total_refunds = refunded.reduce((acc, s) => acc + (Number(s.total_amount) || 0), 0);
|
|
|
|
res.json({
|
|
range,
|
|
start: dr.start.toISOString(),
|
|
end: dr.end.toISOString(),
|
|
total_orders,
|
|
total_sales: Number(total_sales.toFixed(2)),
|
|
total_refunds: Number(total_refunds.toFixed(2)),
|
|
breakdown: {
|
|
completed: completed.length,
|
|
refunded: refunded.length,
|
|
voided: voided.length
|
|
}
|
|
});
|
|
} catch (err) {
|
|
console.error('getSummary error', err);
|
|
res.status(500).json({ error: err.message || 'Internal server error' });
|
|
}
|
|
}
|
|
|
|
/** GET /api/reports/sales-trend */
|
|
async function getSalesTrend(req, res) {
|
|
try {
|
|
const { range = 'year', year, month } = req.query;
|
|
|
|
if (range === 'year') {
|
|
const useYear = year ? parseInt(year, 10) : new Date().getFullYear();
|
|
const dr = getDateRange('year', { year: useYear });
|
|
const sales = await fetchSalesInRange(dr.start, dr.end);
|
|
|
|
const months = Array.from({ length: 12 }, (_, i) => ({
|
|
monthIndex: i,
|
|
label: new Date(useYear, i, 1).toLocaleString('en-US', { month: 'short' }),
|
|
total: 0
|
|
}));
|
|
|
|
sales.forEach(s => {
|
|
if (s.status !== 'completed') return;
|
|
const m = new Date(s.sale_date).getMonth();
|
|
months[m].total += Number(s.total_amount) || 0;
|
|
});
|
|
|
|
res.json({ range: 'year', year: useYear, data: months.map(m => ({ label: m.label, total: Number(m.total.toFixed(2)) })) });
|
|
return;
|
|
}
|
|
|
|
if (range === 'month') {
|
|
const useYear = year ? parseInt(year, 10) : new Date().getFullYear();
|
|
const useMonth = month ? parseInt(month, 10) : (new Date().getMonth() + 1);
|
|
const dr = getDateRange('month', { year: useYear, month: useMonth });
|
|
const sales = await fetchSalesInRange(dr.start, dr.end);
|
|
|
|
const daysInMonth = new Date(useYear, useMonth, 0).getDate();
|
|
const days = Array.from({ length: daysInMonth }, (_, i) => ({ day: i + 1, total: 0 }));
|
|
|
|
sales.forEach(s => {
|
|
if (s.status !== 'completed') return;
|
|
const day = new Date(s.sale_date).getDate();
|
|
days[day - 1].total += Number(s.total_amount) || 0;
|
|
});
|
|
|
|
res.json({ range: 'month', year: useYear, month: useMonth, data: days.map(d => ({ day: d.day, total: Number(d.total.toFixed(2)) })) });
|
|
return;
|
|
}
|
|
} catch (err) {
|
|
console.error('getSalesTrend error', err);
|
|
res.status(500).json({ error: err.message || 'Internal server error' });
|
|
}
|
|
}
|
|
|
|
/** GET /api/reports/payment-methods */
|
|
async function getPaymentMethods(req, res) {
|
|
try {
|
|
const { range = 'month', year, month, quarter, date } = req.query;
|
|
const dr = getDateRange(range, { year, month, quarter, date });
|
|
const sales = await fetchSalesInRange(dr.start, dr.end);
|
|
|
|
const map = {};
|
|
sales.forEach(s => {
|
|
const key = s.payment_type || 'unknown';
|
|
if (!map[key]) map[key] = { count: 0, amount: 0 };
|
|
map[key].count += 1;
|
|
map[key].amount += Number(s.total_amount) || 0;
|
|
});
|
|
|
|
const result = Object.entries(map).map(([payment_type, v]) => ({
|
|
payment_type,
|
|
count: v.count,
|
|
amount: Number(v.amount.toFixed(2))
|
|
})).sort((a, b) => b.amount - a.amount);
|
|
|
|
res.json({ range, start: dr.start.toISOString(), end: dr.end.toISOString(), data: result });
|
|
} catch (err) {
|
|
console.error('getPaymentMethods error', err);
|
|
res.status(500).json({ error: err.message || 'Internal server error' });
|
|
}
|
|
}
|
|
|
|
/** GET /api/reports/top-products */
|
|
async function getTopProducts(req, res) {
|
|
try {
|
|
const { range = 'month', year, month, quarter, date, limit = 10 } = req.query;
|
|
const dr = getDateRange(range, { year, month, quarter, date });
|
|
const sales = await fetchSalesInRange(dr.start, dr.end);
|
|
|
|
const productMap = new Map();
|
|
for (const s of sales) {
|
|
if (!Array.isArray(s.sale_items)) continue;
|
|
for (const item of s.sale_items) {
|
|
const pid = item.product_id;
|
|
const qty = Number(item.quantity) || 0;
|
|
const price = Number(item.price) || 0;
|
|
const amount = qty * price;
|
|
if (!productMap.has(pid)) productMap.set(pid, { product_id: pid, name: item.name || null, qty: 0, amount: 0 });
|
|
const entry = productMap.get(pid);
|
|
entry.qty += qty;
|
|
entry.amount += amount;
|
|
}
|
|
}
|
|
|
|
const arr = Array.from(productMap.values())
|
|
.map(p => ({ ...p, amount: Number(p.amount.toFixed(2)) }))
|
|
.sort((a, b) => b.qty - a.qty)
|
|
.slice(0, parseInt(limit, 10));
|
|
|
|
// Fill missing names
|
|
const missingNames = arr.filter(p => !p.name).map(p => p.product_id);
|
|
if (missingNames.length > 0) {
|
|
const { data: products, error } = await supabase
|
|
.from('products')
|
|
.select('product_id, name')
|
|
.in('product_id', missingNames);
|
|
if (!error && products) {
|
|
const prodMap = new Map(products.map(p => [p.product_id, p.name]));
|
|
arr.forEach(p => { if (!p.name && prodMap.has(p.product_id)) p.name = prodMap.get(p.product_id); });
|
|
}
|
|
}
|
|
|
|
res.json({ range, start: dr.start.toISOString(), end: dr.end.toISOString(), data: arr });
|
|
} catch (err) {
|
|
console.error('getTopProducts error', err);
|
|
res.status(500).json({ error: err.message || 'Internal server error' });
|
|
}
|
|
}
|
|
|
|
export default {
|
|
getSummary,
|
|
getSalesTrend,
|
|
getPaymentMethods,
|
|
getTopProducts
|
|
};
|