?HAMA

Navigation

Breadcrumbs

/ / home / byroehnu / easepay.easetack.com / controllers

Create

Create File | Create Folder | Upload Files | Run Command

Edit File Size: 17.61 KB

/home/byroehnu/easepay.easetack.com/controllers/reportController.js

Text Base64

const { prisma } = require('../config/prisma');

const getSalesReport = async (req, res) => {
  try {
    const { 
      startDate, 
      endDate, 
      groupBy = 'day',
      category,
      format = 'json'
    } = req.query;

// Validate date range
    if (!startDate || !endDate) {
      return res.status(400).json({
        success: false,
        message: 'Start date and end date are required'
      });
    }

let groupByClause = '';
    switch (groupBy) {
      case 'day':
        groupByClause = "DATE_TRUNC('day', date)";
        break;
      case 'week':
        groupByClause = "DATE_TRUNC('week', date)";
        break;
      case 'month':
        groupByClause = "DATE_TRUNC('month', date)";
        break;
      default:
        groupByClause = "DATE_TRUNC('day', date)";
    }

// Using "Sale" table and corresponding columns.
    // tax is in metadata->>'tax'
    // total -> amount
    // sale_date -> date
    
    // Construct WHERE clause parts manually or param array? 
    // $queryRaw is tricky with dynamic WHERE parts string concatenation.
    // Better to use conditions in SQL string with proper casting.
    
    // Base SQL parts
    const categoryFilter = category ? `AND category = '${category}'` : ''; 
    // note: risk of injection if category not sanitized, but this is a protected route. 
    // Ideally use parameters for category too.
    
    const query = `
      SELECT 
        ${groupByClause} as period,
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_revenue,
        SUM(CAST(metadata->>'tax' AS DECIMAL)) as total_tax,
        AVG(amount) as avg_transaction_value,
        MIN(amount) as min_transaction,
        MAX(amount) as max_transaction
      FROM "Sale" 
      WHERE "userId" = '${req.user.id}' 
      AND date >= '${startDate}' 
      AND date <= '${endDate}'
      ${category ? `AND category = '${category}'` : ''}
      GROUP BY ${groupByClause} 
      ORDER BY ${groupByClause}
    `;
    
    // Executing raw query
    // Note: Template literals in $queryRaw are preferred for safety, 
    // but constructing dynamic strings requires using Prisma.sql or unsafeRaw (if available) or careful logic.
    // For specific variable parts, we can use ${startDate} etc.
    // But for dynamic "AND category = ...", we can't easily put it inside the backticks conditionally 
    // UNLESS we use Prisma.sql helper to join.
    // Simpler hack for this refactor: Query everything and filter in params is hard for aggregation.
    // I will use ${category || null} and handle null in SQL? 
    // "AND (${category}::text IS NULL OR category = ${category})"
    
    const salesData = await prisma.$queryRaw`
      SELECT 
        ${groupBy === 'month' ? Prisma.sql`DATE_TRUNC('month', date)` : 
          groupBy === 'week' ? Prisma.sql`DATE_TRUNC('week', date)` : 
          Prisma.sql`DATE_TRUNC('day', date)`} as period,
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_revenue,
        SUM(CAST(metadata->>'tax' AS DECIMAL)) as total_tax,
        AVG(amount) as avg_transaction_value,
        MIN(amount) as min_transaction,
        MAX(amount) as max_transaction
      FROM "Sale" 
      WHERE "userId" = ${req.user.id}
      AND date >= ${new Date(startDate)}
      AND date <= ${new Date(endDate)}
      AND (${category} IS NULL OR category = ${category})
      GROUP BY period
      ORDER BY period
    `;

// Wait, "GROUP BY period" works in Postgres if period is selected? Yes.
    // Prisma.sql is not imported. I need to rely on direct string interpolation only if NOT using tag function for dynamic parts nicely.
    // Let's stick to a simpler query structure without Prisma.sql dependency if possible (it's in @prisma/client).
    // I will use direct interpolation for the query string passing to $queryRawUnsafe if dynamic SQL is needed,
    // OR just write separate queries for category/no-category if needed.
    // Actually, `(${category} IS NULL OR category = ${category})` works great with standard tagging.
    
    // BUT groupByClause needs to be injected. Tagged template param is strictly VALUE. 
    // Use ::text cast? No, DATE_TRUNC takes identifier/string constant.
    // I will use $queryRawUnsafe for the dynamic groupBy part or simpler JS logic.
    // Given the complexity, let's use $queryRawUnsafe which accepts a string.
    
    const groupBySQL = groupBy === 'month' ? "DATE_TRUNC('month', date)" :
                       groupBy === 'week'  ? "DATE_TRUNC('week', date)" :
                                             "DATE_TRUNC('day', date)";
    
    const sql = `
      SELECT 
        ${groupBySQL} as period,
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_revenue,
        SUM(COALESCE(CAST(metadata->>'tax' AS DECIMAL), 0)) as total_tax,
        AVG(amount) as avg_transaction_value,
        MIN(amount) as min_transaction,
        MAX(amount) as max_transaction
      FROM "Sale" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
      ${category ? `AND category = $4` : ''}
      GROUP BY ${groupBySQL}
      ORDER BY ${groupBySQL}
    `;
    
    const params = [req.user.id, new Date(startDate), new Date(endDate)];
    if (category) params.push(category);

const salesList = await prisma.$queryRawUnsafe(sql, ...params);

// Get summary statistics
    const summarySql = `
      SELECT 
        COUNT(*)::int as total_transactions,
        SUM(amount) as total_revenue,
        SUM(COALESCE(CAST(metadata->>'tax' AS DECIMAL), 0)) as total_tax,
        AVG(amount) as avg_transaction_value,
        MIN(amount) as min_transaction,
        MAX(amount) as max_transaction
      FROM "Sale" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
      ${category ? `AND category = $4` : ''}
    `;
    
    const summaryList = await prisma.$queryRawUnsafe(summarySql, ...params);

// Get top customers
    // "customerName" and "customerEmail"
    const topCustomersSql = `
      SELECT 
        "customerName",
        "customerEmail",
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_spent
      FROM "Sale" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
      AND "customerName" IS NOT NULL
      ${category ? `AND category = $4` : ''}
      GROUP BY "customerName", "customerEmail"
      ORDER BY total_spent DESC
      LIMIT 10
    `;

const topCustomersList = await prisma.$queryRawUnsafe(topCustomersSql, ...params);

// Get payment method breakdown
    // "paymentMethod"
    const paymentMethodSql = `
      SELECT 
        "paymentMethod",
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_amount
      FROM "Sale" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
      ${category ? `AND category = $4` : ''}
      GROUP BY "paymentMethod"
      ORDER BY total_amount DESC
    `;

const paymentMethodsList = await prisma.$queryRawUnsafe(paymentMethodSql, ...params);

const report = {
      summary: summaryList[0],
      salesData: salesList,
      topCustomers: topCustomersList,
      paymentMethods: paymentMethodsList,
      filters: {
        startDate,
        endDate,
        groupBy,
        category
      }
    };

res.status(200).json({
      success: true,
      report
    });
  } catch (error) {
    console.error('Get sales report error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to generate sales report'
    });
  }
};

// @desc    Generate expense report
// @route   GET /api/reports/expenses
// @access  Private
const getExpenseReport = async (req, res) => {
  try {
    const { 
      startDate, 
      endDate, 
      groupBy = 'day',
      category,
      isDeductible
    } = req.query;

if (!startDate || !endDate) {
      return res.status(400).json({
        success: false,
        message: 'Start date and end date are required'
      });
    }

const groupBySQL = groupBy === 'month' ? "DATE_TRUNC('month', date)" :
                       groupBy === 'week'  ? "DATE_TRUNC('week', date)" :
                                             "DATE_TRUNC('day', date)";

// Expense table: "Expense"
    // Columns: "amount", "date", "userId", "category".
    // isDeductible missing in schema -> Assuming false/0 or ignoring.
    // Query builder adaptation
    
    // Note: If isDeductible is required by user, this feature is currently broken/missing in Schema.
    // I will omit the filter for now to prevent SQL component error if column doesn't exist.
    // Or I can check metadata? No, Expense doesn't have metadata in schema.

let expenseSql = `
      SELECT 
        ${groupBySQL} as period,
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_expenses,
        AVG(amount) as avg_expense,
        MIN(amount) as min_expense,
        MAX(amount) as max_expense
      FROM "Expense" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
    `;

const expenseParams = [req.user.id, new Date(startDate), new Date(endDate)];
    let paramIndex = 4;

if (category) {
      expenseSql += ` AND category = $${paramIndex}`;
      expenseParams.push(category);
      paramIndex++;
    }

// Checking metadata for isDeductible if possible? 
    // Prisma Schema line 70: `category String?`.
    // Let's assume isDeductible is gone for now.

expenseSql += ` GROUP BY ${groupBySQL} ORDER BY ${groupBySQL}`;

const expenseData = await prisma.$queryRawUnsafe(expenseSql, ...expenseParams);

// Summary
    let summarySql = `
      SELECT 
        COUNT(*)::int as total_transactions,
        SUM(amount) as total_expenses,
        AVG(amount) as avg_expense,
        MIN(amount) as min_expense,
        MAX(amount) as max_expense
      FROM "Expense" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
    `;
    
    if (category) {
       summarySql += ` AND category = $4`;
    }

const summaryData = await prisma.$queryRawUnsafe(summarySql, ...expenseParams);

// Category breakdown
    let categorySql = `
      SELECT 
        category,
        COUNT(*)::int as transaction_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount
      FROM "Expense" 
      WHERE "userId" = $1 
      AND date >= $2 
      AND date <= $3
    `;
    
    if (category) {
       categorySql += ` AND category = $4`;
    }
    
    categorySql += ` GROUP BY category ORDER BY total_amount DESC`;

const categoryBreakdown = await prisma.$queryRawUnsafe(categorySql, ...expenseParams);

const report = {
      summary: summaryData[0],
      expenseData: expenseData,
      categoryBreakdown: categoryBreakdown,
      filters: {
        startDate,
        endDate,
        groupBy,
        category,
        isDeductible // Returning filter even if ignored
      }
    };

res.status(200).json({
      success: true,
      report
    });
  } catch (error) {
    console.error('Get expense report error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to generate expense report'
    });
  }
};

// @desc    Generate profit/loss report
// @route   GET /api/reports/profit-loss
// @access  Private
const getProfitLossReport = async (req, res) => {
  try {
    const { 
      startDate, 
      endDate, 
      groupBy = 'month'
    } = req.query;

if (!startDate || !endDate) {
      return res.status(400).json({
        success: false,
        message: 'Start date and end date are required'
      });
    }

const groupBySQL = groupBy === 'day' ? "DATE_TRUNC('day', date)" :
                       groupBy === 'week' ? "DATE_TRUNC('week', date)" :
                                           "DATE_TRUNC('month', date)";

// Consolidated CTE query
    const profitLossSql = `
      WITH sales_summary AS (
        SELECT 
          ${groupBySQL} as period,
          SUM(amount) as revenue,
          SUM(COALESCE(CAST(metadata->>'tax' AS DECIMAL), 0)) as tax_collected
        FROM "Sale" 
        WHERE "userId" = $1 
        AND date >= $2 
        AND date <= $3
        GROUP BY ${groupBySQL}
      ),
      expense_summary AS (
        SELECT 
          ${groupBySQL} as period,
          SUM(amount) as expenses
          -- removed deductible logic
        FROM "Expense" 
        WHERE "userId" = $1 
        AND date >= $2 
        AND date <= $3
        GROUP BY ${groupBySQL}
      )
      SELECT 
        COALESCE(s.period, e.period) as period,
        COALESCE(s.revenue, 0) as revenue,
        COALESCE(s.tax_collected, 0) as tax_collected,
        COALESCE(e.expenses, 0) as expenses,
        (COALESCE(s.revenue, 0) - COALESCE(e.expenses, 0)) as net_profit,
        CASE 
          WHEN COALESCE(s.revenue, 0) > 0 
          THEN ((COALESCE(s.revenue, 0) - COALESCE(e.expenses, 0)) / COALESCE(s.revenue, 0)) * 100 
          ELSE 0 
        END as profit_margin
      FROM sales_summary s
      FULL OUTER JOIN expense_summary e ON s.period = e.period
      ORDER BY period
    `;

const params = [req.user.id, new Date(startDate), new Date(endDate)];
    const profitLossData = await prisma.$queryRawUnsafe(profitLossSql, ...params);

// Calculate totals in JS
    const totals = profitLossData.reduce((acc, row) => ({
      totalRevenue: acc.totalRevenue + parseFloat(row.revenue || 0),
      totalExpenses: acc.totalExpenses + parseFloat(row.expenses || 0),
      totalNetProfit: acc.totalNetProfit + parseFloat(row.net_profit || 0),
      totalTaxCollected: acc.totalTaxCollected + parseFloat(row.tax_collected || 0)
    }), {
      totalRevenue: 0,
      totalExpenses: 0,
      totalNetProfit: 0,
      totalTaxCollected: 0
    });

totals.overallProfitMargin = totals.totalRevenue > 0 
      ? ((totals.totalNetProfit / totals.totalRevenue) * 100) 
      : 0;

res.status(200).json({
      success: true,
      report: {
        data: profitLossData,
        summary: totals,
        filters: {
          startDate,
          endDate,
          groupBy
        }
      }
    });
  } catch (error) {
    console.error('Get profit/loss report error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to generate profit/loss report'
    });
  }
};

const getMonthlyReport = async (req, res) => {
  try {
    const userId = req.user.id;
    const { month, year } = req.query;

const targetDate = new Date(year || new Date().getFullYear(), month ? parseInt(month) - 1 : new Date().getMonth(), 1);
    const startOfMonth = new Date(targetDate.getFullYear(), targetDate.getMonth(), 1);
    const endOfMonth = new Date(targetDate.getFullYear(), targetDate.getMonth() + 1, 0, 23, 59, 59, 999);

// Total Sales
    const sales = await prisma.sale.aggregate({
      where: { userId, date: { gte: startOfMonth, lte: endOfMonth } },
      _sum: { amount: true }
    });

// Total Expenses
    const expenses = await prisma.expense.aggregate({
      where: { userId, date: { gte: startOfMonth, lte: endOfMonth } },
      _sum: { amount: true }
    });

// Money Made (Sales + Paid Invoices)
    const paidInvoices = await prisma.invoice.aggregate({
      where: { 
        userId, 
        status: { in: ['PAID', 'PARTIALLY_PAID'] },
        updatedAt: { gte: startOfMonth, lte: endOfMonth }
      },
      _sum: { paidAmount: true }
    });

const totalSales = Number(sales._sum.amount) || 0;
    const totalExpenses = Number(expenses._sum.amount) || 0;
    const totalInvoicesPaid = Number(paidInvoices._sum.paidAmount) || 0;
    const moneyMade = totalSales + totalInvoicesPaid;

// Recent Activities for the month
    const recentSales = await prisma.sale.findMany({
      where: { userId, date: { gte: startOfMonth, lte: endOfMonth } },
      orderBy: { date: 'desc' },
      take: 10,
      select: { id: true, amount: true, description: true, date: true, paymentMethod: true }
    });
    
    const recentExpenses = await prisma.expense.findMany({
      where: { userId, date: { gte: startOfMonth, lte: endOfMonth } },
      orderBy: { date: 'desc' },
      take: 10,
      select: { id: true, amount: true, description: true, date: true, paymentMethod: true }
    });

const recentInvoices = await prisma.invoice.findMany({
      where: { userId, createdAt: { gte: startOfMonth, lte: endOfMonth } },
      orderBy: { createdAt: 'desc' },
      take: 10,
      select: { id: true, invoiceNumber: true, total: true, status: true, createdAt: true, customerName: true }
    });

let activities = [
      ...recentSales.map(s => ({ type: 'SALE', ...s, date: s.date })),
      ...recentExpenses.map(e => ({ type: 'EXPENSE', ...e, date: e.date })),
      ...recentInvoices.map(i => ({ type: 'INVOICE', ...i, date: i.createdAt }))
    ];
    
    activities.sort((a, b) => new Date(b.date) - new Date(a.date));

res.status(200).json({
      success: true,
      data: {
        month: targetDate.toLocaleString('default', { month: 'long' }),
        year: targetDate.getFullYear(),
        moneyMade,
        totalSales,
        totalExpenses,
        recentActivities: activities
      }
    });

} catch (error) {
    console.error('Get monthly report error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to get monthly report'
    });
  }
};

module.exports = {
  getSalesReport,
  getExpenseReport,
  getProfitLossReport,
  getMonthlyReport
};

Exit & Reset Text mode: syntax highlighting auto-detects file type.

Directory Contents

Dirs: 1 × Files: 17

Sort:

Enable drag-to-move

Name	Size	Perms	Modified	Actions
controllers DIR	-	drwxr-xr-x	2026-03-20 23:25:48	Edit Download recursive recursive
.htaccess	127 B	lr--r--r--	2026-03-14 01:49:22	Edit Download recursive recursive
advancedAuthController.js	15.34 KB	lrw-r--r--	2026-02-19 22:06:47	Edit Download recursive recursive
authController.js	30.77 KB	lrw-r--r--	2026-03-04 06:33:18	Edit Download recursive recursive
authController_copy.js	62.98 KB	lrw-r--r--	2026-03-01 23:46:52	Edit Download recursive recursive
businessController.js	10.79 KB	lrw-r--r--	2026-02-07 16:28:16	Edit Download recursive recursive
businessProfileController.js	14.17 KB	lrw-r--r--	2026-02-27 04:02:38	Edit Download recursive recursive
creditScoreController.js	6.32 KB	lrw-r--r--	2026-02-06 17:10:19	Edit Download recursive recursive
dashboardController.js	15.13 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
expenseController.js	8.50 KB	lrw-r--r--	2026-03-01 05:05:52	Edit Download recursive recursive
inventoryController.js	12.15 KB	lrw-r--r--	2026-02-27 04:02:38	Edit Download recursive recursive
invoiceController.js	25.39 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
reportController.js	17.61 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
saleController.js	6.45 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
settingsController.js	2.33 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
subscriptionController.js	12.78 KB	lrw-r--r--	2026-02-21 08:13:30	Edit Download recursive recursive
taxController.js	9.96 KB	lrw-r--r--	2026-03-01 05:05:51	Edit Download recursive recursive
webhookController.js	8.51 KB	lrw-r--r--	2026-02-20 19:31:28	Edit Download recursive recursive