Multi-Branch Sales Analytics & BI Dashboard

// Express REST Endpoint for Branch Financials & P&L Analysis
router.get("/api/analytics/financials", async (req, res) => {
  const { branchId, startDate, endDate } = req.query;
  const query = `
    SELECT 
      branch_site,
      COUNT(DISTINCT transaction_id) AS total_bills,
      SUM(quantity) AS total_units_sold,
      SUM(net_amount) AS total_revenue,
      SUM(cost_price * quantity) AS total_cogs,
      SUM(net_amount - (cost_price * quantity)) AS gross_profit,
      ROUND(SUM(net_amount - (cost_price * quantity)) / NULLIF(SUM(net_amount), 0) * 100, 2) AS profit_margin_pct,
      SUM(basic_amount) AS total_pre_tax_amount,
      SUM(tax_amount) AS total_tax_collected,
      ROUND(AVG(net_amount), 2) AS avg_bill_value,
      ROUND(AVG(net_amount - (cost_price * quantity)), 2) AS avg_profit_per_bill
    FROM sales_data
    WHERE bill_date BETWEEN $1 AND $2
      AND ($3::text IS NULL OR branch_site = $3)
    GROUP BY branch_site
    ORDER BY total_revenue DESC;
  `;
  const result = await db.query(query, [startDate, endDate, branchId || null]);
  res.json(result.rows);
});

import React from "react";
import { useQuery } from "@tanstack/react-query";

export const ExecutiveMetrics: React.FC<{ filters: FilterState }> = ({ filters }) => {
  const { data: metrics, isLoading } = useQuery({
    queryKey: ["sales-metrics", filters],
    queryFn: () => fetchSalesMetrics(filters),
  });

  if (isLoading || !metrics) return <div className="animate-pulse h-48 bg-secondary/50 rounded-xl" />;

  return (
    <div className="grid grid-cols-1 md:grid-cols-4 gap-4">
      <MetricCard title="Total Revenue" value={`₹${metrics.totalRevenue.toLocaleString()}`} change="+12.4%" />
      <MetricCard title="Gross Profit" value={`₹${metrics.grossProfit.toLocaleString()}`} subtitle={`Margin: ${metrics.profitMarginPct}%`} />
      <MetricCard title="Tax Collected" value={`₹${metrics.taxCollected.toLocaleString()}`} subtitle={`Tax Ratio: ${metrics.taxRatio}%`} />
      <MetricCard title="Total Transactions" value={metrics.totalBills.toLocaleString()} subtitle={`Avg Bill: ₹${metrics.avgBillValue}`} />
    </div>
  );
};

import multer from "multer";
import Papa from "papaparse";
import * as XLSX from "xlsx";

export async function processUploadedSalesReport(fileBuffer: Buffer, mimeType: string) {
  let rawRows: any[] = [];
  if (mimeType.includes("csv")) {
    const text = fileBuffer.toString("utf-8");
    rawRows = Papa.parse(text, { header: true, skipEmptyLines: true }).data;
  } else {
    const workbook = XLSX.read(fileBuffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    rawRows = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
  }

  const normalizedData = rawRows.map(row => ({
    transactionId: row["Bill No"] || row["Transaction ID"],
    itemCode: row["Item Code"],
    billDate: new Date(row["Bill Date"]),
    productName: row["Description"] || row["Item Name"],
    category: row["Category"],
    subCategory: row["Department"],
    salespersonName: row["Salesman"],
    quantity: Number(row["Qty"] || 1),
    costPrice: Number(row["Cost Price"] || 0),
    basicAmount: Number(row["Basic Amt"] || 0),
    taxAmount: Number(row["Tax Amt"] || 0),
    netAmount: Number(row["Net Amt"] || row["Total Amount"]),
    branchSite: row["Branch"] || row["Store Location"]
  }));

  await batchInsertSalesRecords(normalizedData);
}