page.tsx

Go to the documentation of this file.

"use client";
 
import React, { useState, useEffect } from 'react';
import { apiClient } from '@/lib/client/apiClient';
import { fieldpineApi } from "@/lib/client/fieldpineApi";
 
interface Location {
  f100: number;
  f101: string;
}
 
interface StoreKPI {
  storeId: number;
  storeName: string;
  sales: number;
  lastCost: number;
  wacCost: number;
  gpPercent: number;
  numSales: number;
  numUnits: number;
  avgRev: number;
}
 
export default function SalesKPIPage() {
  const [fromDate, setFromDate] = useState('');
  const [toDate, setToDate] = useState('');
  const [salesType, setSalesType] = useState('0');
  const [loading, setLoading] = useState(false);
  const [storeKPIs, setStoreKPIs] = useState<StoreKPI[]>([]);
  const [sortConfig, setSortConfig] = useState<{ key: keyof StoreKPI; direction: 'asc' | 'desc' } | null>(null);
 
  // Set default dates
  useEffect(() => {
    // Start of current month
    const now = new Date();
    const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);
    setFromDate(startOfMonth.toISOString().split('T')[0]);
    
    // Today
    setToDate(now.toISOString().split('T')[0]);
  }, []);
 
  const formatDateForAPI = (date: string) => {
    if (!date) return '';
    const d = new Date(date + 'T00:00:00');
    const months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    return `${d.getDate()}${months[d.getMonth()]}${d.getFullYear()}`;
  };
 
  const formatCurrency = (value: number) => {
    return new Intl.NumberFormat('en-NZ', {
      style: 'currency',
      currency: 'NZD',
      minimumFractionDigits: 2,
      maximumFractionDigits: 2
    }).format(value);
  };
 
  const formatNumber = (value: number) => {
    return new Intl.NumberFormat('en-NZ', {
      minimumFractionDigits: 0,
      maximumFractionDigits: 0
    }).format(value);
  };
 
  const loadReport = async () => {
    const apiKey = sessionStorage.getItem("fieldpine_apikey");
    if (!apiKey) {
      alert("Please log in first");
      return;
    }
 
    if (!fromDate || !toDate) {
      alert("Please select date range");
      return;
    }
 
    setLoading(true);
    setStoreKPIs([]);
 
    try {
      // First, load all locations
      const locationsResponse = await fieldpineApi({
        endpoint: '/BUCK?3=retailmax.elink.locations',
        apiKey: apiKey
      });
 
      if (!locationsResponse?.data?.DATS || !Array.isArray(locationsResponse.data.DATS)) {
        alert('Error: No locations/stores defined. Please create store definitions.');
        setLoading(false);
        return;
      }
 
      const locations: Location[] = locationsResponse.data.DATS;
      const kpis: StoreKPI[] = [];
 
      // Build date predicates
      const startDate = formatDateForAPI(fromDate);
      const endDate = formatDateForAPI(toDate);
      
      // Build sales type predicate
      let salesTypePred = '';
      if (Number(salesType) > 0) {
        salesTypePred = `&9=f1001,=,${salesType}`;
      }
 
      // Load sales data for each location
      for (const location of locations) {
        const locationId = Number(location.f100 || 0);
        const locationName = String(location.f101 || '');
 
        const salesResponse = await fieldpineApi({
          endpoint: `/BUCK?3=retailmax.elink.sale.totals&15=2,1,location&13=120&9=f110,4,${startDate}&9=f110,1,${endDate}&9=f100,0,${locationId}${salesTypePred}`,
          apiKey: apiKey
        });
 
        if (salesResponse?.data?.APPD && Array.isArray(salesResponse.data.APPD)) {
          for (const record of salesResponse.data.APPD) {
            const sales = Number(record.f120 || 0);
            
            // Only include stores with sales
            if (sales !== 0) {
              const lastCost = Number(record.f102 || 0);
              const units = Number(record.f107 || 0);
              
              // Calculate GP%
              let gpPercent = 0;
              if (sales !== 0) {
                gpPercent = ((sales - lastCost) * 100) / sales;
              }
              
              // Calculate average revenue
              let avgRev = 0;
              if (units !== 0) {
                avgRev = sales / units;
              }
 
              kpis.push({
                storeId: locationId,
                storeName: locationName,
                sales: sales,
                lastCost: lastCost,
                wacCost: Number(record.f103 || 0),
                gpPercent: gpPercent,
                numSales: Number(record.f105 || 0),
                numUnits: units,
                avgRev: avgRev
              });
            }
          }
        }
      }
 
      setStoreKPIs(kpis);
    } catch (error) {
      console.error('Error loading KPI report:', error);
      alert('Error loading report. Please try again.');
    } finally {
      setLoading(false);
    }
  };
 
  const handleSort = (key: keyof StoreKPI) => {
    let direction: 'asc' | 'desc' = 'asc';
    if (sortConfig && sortConfig.key === key && sortConfig.direction === 'asc') {
      direction = 'desc';
    }
    setSortConfig({ key, direction });
  };
 
  const getSortedKPIs = () => {
    if (!sortConfig) return storeKPIs;
 
    return [...storeKPIs].sort((a, b) => {
      const aVal = a[sortConfig.key];
      const bVal = b[sortConfig.key];
 
      if (aVal === undefined || aVal === null) return 1;
      if (bVal === undefined || bVal === null) return -1;
 
      if (typeof aVal === 'number' && typeof bVal === 'number') {
        return sortConfig.direction === 'asc' ? aVal - bVal : bVal - aVal;
      }
 
      const aStr = String(aVal).toLowerCase();
      const bStr = String(bVal).toLowerCase();
      
      if (aStr < bStr) return sortConfig.direction === 'asc' ? -1 : 1;
      if (aStr > bStr) return sortConfig.direction === 'asc' ? 1 : -1;
      return 0;
    });
  };
 
  const sortedKPIs = getSortedKPIs();
 
  return (
    <div className="p-6">
      <h1 className="text-3xl font-bold mb-6">Sales KPI Report</h1>
 
      {/* Filters */}
      <div className="bg-surface p-4 rounded-lg shadow mb-6">
        <form onSubmit={(e) => { e.preventDefault(); loadReport(); }} className="flex flex-wrap items-end gap-4">
          <div>
            <label className="block text-sm font-medium mb-1">From (including)</label>
            <input
              type="date"
              value={fromDate}
              onChange={(e) => setFromDate(e.target.value)}
              className="border rounded px-3 py-2"
            />
          </div>
 
          <div>
            <label className="block text-sm font-medium mb-1">To (excluding)</label>
            <input
              type="date"
              value={toDate}
              onChange={(e) => setToDate(e.target.value)}
              className="border rounded px-3 py-2"
            />
          </div>
 
          <div>
            <label className="block text-sm font-medium mb-1">Display</label>
            <select
              value={salesType}
              onChange={(e) => setSalesType(e.target.value)}
              className="border rounded px-3 py-2"
            >
              <option value="0">All Sales</option>
              <option value="2">Web Sales Only</option>
              <option value="258">Franchise Web Sales Only</option>
              <option value="4">Wholesale Sales Only</option>
            </select>
          </div>
 
          <div>
            <button
              type="submit"
              disabled={loading}
              className="bg-brand text-white px-6 py-2 rounded hover:bg-brand2 disabled:bg-muted/50"
            >
              {loading ? 'Loading...' : 'Submit'}
            </button>
          </div>
        </form>
      </div>
 
      {/* Loading Indicator */}
      {loading && (
        <div className="mb-4 p-3 bg-info/10 border border-info/30 rounded flex items-center gap-2">
          <div className="animate-spin rounded-full h-4 w-4 border-b-2 border-info"></div>
          <span>Working...</span>
        </div>
      )}
 
      {/* Results Table */}
      {sortedKPIs.length > 0 ? (
        <>
          <div className="bg-surface rounded-lg shadow overflow-hidden">
            <div className="overflow-x-auto">
              <table className="min-w-full divide-y divide-border">
                <thead className="bg-surface-2">
                  <tr>
                    <th 
                      onClick={() => handleSort('storeName')}
                      className="px-6 py-3 text-left text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Store {sortConfig?.key === 'storeName' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('sales')}
                      className="px-6 py-3 text-right text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Sales {sortConfig?.key === 'sales' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('lastCost')}
                      className="px-6 py-3 text-right text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Last Cost {sortConfig?.key === 'lastCost' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('gpPercent')}
                      className="px-6 py-3 text-center text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      GP% {sortConfig?.key === 'gpPercent' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('numSales')}
                      className="px-6 py-3 text-right text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Num Sales {sortConfig?.key === 'numSales' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('numUnits')}
                      className="px-6 py-3 text-right text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Num Units {sortConfig?.key === 'numUnits' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                    <th 
                      onClick={() => handleSort('avgRev')}
                      className="px-6 py-3 text-right text-xs font-medium text-muted uppercase tracking-wider cursor-pointer hover:bg-surface-2/80"
                    >
                      Avg Rev {sortConfig?.key === 'avgRev' && (sortConfig.direction === 'asc' ? '↑' : '↓')}
                    </th>
                  </tr>
                </thead>
                <tbody className="bg-surface divide-y divide-border">
                  {sortedKPIs.map((kpi, idx) => (
                    <tr key={idx} className="hover:bg-surface-2">
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-text">
                        {kpi.storeName}
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-right">
                        <a
                          href={`/report/pos/sales/fieldpine/salesflat_review.htm?sdt=${fromDate}&edt=${toDate}&loc=${kpi.storeId}`}
                          target="_blank"
                          className="text-brand hover:underline font-mono"
                        >
                          {formatCurrency(kpi.sales)}
                        </a>
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-text text-right font-mono">
                        {formatCurrency(kpi.lastCost)}
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm">
                        <div className="flex items-center justify-center">
                          {kpi.gpPercent > 0 && (
                            <div className="w-full max-w-[120px]">
                              <div className="flex items-center gap-2">
                                <div className="flex-1 bg-surface-2 rounded-full h-4 overflow-hidden">
                                  <div 
                                    className="bg-success h-full rounded-full transition-all"
                                    style={{ width: `${Math.min(Math.max(kpi.gpPercent, 0), 100)}%` }}
                                  ></div>
                                </div>
                                <span className="text-xs font-medium w-10 text-right">
                                  {kpi.gpPercent.toFixed(0)}%
                                </span>
                              </div>
                            </div>
                          )}
                        </div>
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-text text-right font-mono">
                        {formatNumber(kpi.numSales)}
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-text text-right font-mono">
                        {formatNumber(kpi.numUnits)}
                      </td>
                      <td className="px-6 py-4 whitespace-nowrap text-sm text-text text-right font-mono">
                        {formatCurrency(kpi.avgRev)}
                      </td>
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          </div>
          
          <p className="mt-4 text-sm text-muted text-center">
            Stores with no sales are not shown. Sales column is tax exclusive.
          </p>
        </>
      ) : (
        !loading && (
          <div className="bg-surface rounded-lg shadow p-8 text-center text-muted">
            No data available. Select date range and click Submit to load KPI report.
          </div>
        )
      )}
    </div>
  );
}