page.tsx

Go to the documentation of this file.

"use client";
 
import { useState, useEffect } from 'react';
import { apiClient } from '@/lib/client/apiClient';
import { fieldpineApi } from "@/lib/client/fieldpineApi";
 
interface GroupData {
  groupValue: string;
  numsales: number;
  sales: number;
  lastcost: number;
  waccost: number;
  units: number;
  avesale: number;
  itemspersale: number;
  gp: number;
  percenttotal: number;
  v11000: number;
  v11001: number;
  v11002: number;
  v11003: number;
  v11004: number;
  v11005: number;
  v11006: number;
}
 
interface Location {
  f100: number;
  f101: string;
}
 
const groupOptions = [
  { value: '', label: '-- Select Group Method' },
  { value: 'customers.city', label: 'Customers City' },
  { value: 'customers.postcode', label: 'Customers Postcode' },
  { value: 'customers.priceband', label: 'Customers Priceband' },
  { value: 'customers.keywords', label: 'Customers Keywords' },
  { value: 'customers.referredby', label: 'Customers Referred By' },
  { value: 'accounts.city', label: 'Account Holders City' },
  { value: 'accounts.postcode', label: 'Account Holders Postcode' },
  { value: 'accounts.creditlimit', label: 'Account Holders Credit Limit' },
  { value: 'products.cartid', label: 'Printer Cartridge' },
  { value: 'salelines.cashitemname', label: 'Cash Item Name' }
];
 
export default function SalesByXPage() {
  const [groupData, setGroupData] = useState<GroupData[]>([]);
  const [locations, setLocations] = useState<Location[]>([]);
  const [isLoading, setIsLoading] = useState(false);
  const [sortColumn, setSortColumn] = useState<keyof GroupData>('sales');
  const [sortDirection, setSortDirection] = useState<'asc' | 'desc'>('desc');
  const [totalPeriodRev, setTotalPeriodRev] = useState(0);
 
  // Filter states
  const [groupBy, setGroupBy] = useState('');
  const [fromDate, setFromDate] = useState(() => {
    const d = new Date();
    d.setMonth(0, 1); // January 1st
    return d.toISOString().split('T')[0];
  });
  const [toDate, setToDate] = useState(() => {
    const today = new Date();
    return today.toISOString().split('T')[0];
  });
  const [selectedLocation, setSelectedLocation] = useState<number>(0);
  const [maxRows, setMaxRows] = useState(200);
 
  // Load locations on mount
  useEffect(() => {
    loadLocations();
  }, []);
 
  // Load report when filters change
  useEffect(() => {
    if (locations.length > 0 && groupBy) {
      loadReport();
    }
  }, [fromDate, toDate, selectedLocation, groupBy, locations]);
 
  const loadLocations = async () => {
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) return;
 
      const response = await fieldpineApi({
        endpoint: '/buck?3=retailmax.elink.locations',
        apiKey: apiKey
      });
 
      if (response?.DATS) {
        setLocations(response.DATS);
      }
    } catch (error) {
      console.error('Error loading locations:', error);
    }
  };
 
  const formatDateForAPI = (dateStr: string): string => {
    const date = new Date(dateStr);
    const day = date.getDate();
    const month = date.getMonth() + 1;
    const year = date.getFullYear();
    return `${day}-${month}-${year}`;
  };
 
  const loadReport = async () => {
    if (!fromDate || !toDate || !groupBy) return;
 
    setIsLoading(true);
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) {
        setIsLoading(false);
        return;
      }
 
      const fromFormatted = formatDateForAPI(fromDate);
      const toFormatted = formatDateForAPI(toDate);
 
      let locationPred = '';
      if (selectedLocation > 0) {
        locationPred = `&9=f100,0,${selectedLocation}`;
      }
 
      const maxRowPred = maxRows > 0 ? `&8=${maxRows}` : '';
 
      const endpoint = `/buck?3=retailmax.elink.sale.totals.group&10=11000&15=2,1,${groupBy}&13=120&9=f110,4,${fromFormatted}&9=f110,1,${toFormatted}${maxRowPred}${locationPred}`;
 
      const response = await fieldpineApi({
        endpoint: endpoint,
        apiKey: apiKey
      });
 
      if (response?.APPD) {
        let totRev = 0;
        const groups: GroupData[] = response.APPD.map((rec: any) => {
          const sales = Number(rec.f120 || 0);
          const lastcost = Number(rec.f102 || 0);
          const units = Number(rec.f107 || 0);
          const numsales = Number(rec.f109 || 0);
 
          totRev += sales;
 
          let gp = 0;
          if (sales !== 0) {
            gp = ((sales - lastcost) * 100) / sales;
          }
 
          let avesale = 0;
          if (numsales > 0) {
            avesale = sales / numsales;
          }
 
          let itemspersale = 0;
          if (numsales > 0) {
            itemspersale = units / numsales;
          }
 
          return {
            groupValue: rec.f149 || '',
            numsales: numsales,
            sales: sales,
            lastcost: lastcost,
            waccost: Number(rec.f103 || 0),
            units: units,
            avesale: avesale,
            itemspersale: itemspersale,
            gp: gp,
            percenttotal: 0,
            v11000: Number(rec.f11000 || 0),
            v11001: Number(rec.f11001 || 0),
            v11002: Number(rec.f11002 || 0),
            v11003: Number(rec.f11003 || 0),
            v11004: Number(rec.f11004 || 0),
            v11005: Number(rec.f11005 || 0),
            v11006: Number(rec.f11006 || 0)
          };
        });
 
        setTotalPeriodRev(totRev);
 
        // Calculate percent of total for each group
        groups.forEach(g => {
          if (totRev > 0) {
            g.percenttotal = (g.sales / totRev) * 100;
          }
        });
 
        setGroupData(groups);
      } else {
        setGroupData([]);
      }
    } catch (error) {
      console.error('Error loading report:', error);
      setGroupData([]);
    }
    setIsLoading(false);
  };
 
  const handleSort = (column: keyof GroupData) => {
    if (sortColumn === column) {
      setSortDirection(sortDirection === 'asc' ? 'desc' : 'asc');
    } else {
      setSortColumn(column);
      setSortDirection('desc');
    }
  };
 
  const sortedData = [...groupData].sort((a, b) => {
    let aVal = a[sortColumn];
    let bVal = b[sortColumn];
 
    if (typeof aVal === 'string') aVal = aVal.toLowerCase();
    if (typeof bVal === 'string') bVal = bVal.toLowerCase();
 
    if (aVal < bVal) return sortDirection === 'asc' ? -1 : 1;
    if (aVal > bVal) return sortDirection === 'asc' ? 1 : -1;
    return 0;
  });
 
  const SortIndicator = ({ column }: { column: keyof GroupData }) => {
    if (sortColumn !== column) return null;
    return <span className="ml-1">{sortDirection === 'asc' ? '▲' : '▼'}</span>;
  };
 
  const GPBar = ({ value }: { value: number }) => {
    const percentage = Math.max(0, Math.min(100, Math.round(value)));
    let colorClass = 'bg-brand';
    if (percentage < 40) colorClass = 'bg-danger';
    if (percentage > 60) colorClass = 'bg-success';
 
    return (
      <div className="flex items-center gap-2">
        <div className="w-16 bg-surface-2 rounded-full h-4">
          <div 
            className={`${colorClass} h-full rounded-full transition-all`}
            style={{ width: `${percentage}%` }}
          ></div>
        </div>
        <span className="text-sm">{value.toFixed(2)}%</span>
      </div>
    );
  };
 
  const SalesPctBar = ({ value }: { value: number }) => {
    const percentage = Math.max(0, Math.min(100, Math.round(value)));
    return (
      <div className="flex items-center gap-2">
        <div className="w-16 bg-surface-2 rounded-full h-4">
          <div 
            className="bg-success h-full rounded-full transition-all"
            style={{ width: `${percentage}%` }}
          ></div>
        </div>
        <span className="text-sm">{value.toFixed(2)}%</span>
      </div>
    );
  };
 
  const QuartileBox = ({ group }: { group: GroupData }) => {
    if (group.numsales < 3) return null;
 
    const { v11000, v11001, v11002, v11003, v11004, v11005, v11006 } = group;
    
    const tooltip = `Sales Values: Smallest ${v11000.toFixed(1)} / Lower Qtr ${v11001.toFixed(1)} / Average ${v11002.toFixed(1)} / Upper Qtr ${v11003.toFixed(1)} / Maximum ${v11004.toFixed(1)} / Plotting Min ${v11005.toFixed(1)} / Plotting Max ${v11006.toFixed(1)}`;
 
    const range = v11006 - v11005;
    const getPosition = (val: number) => {
      if (range === 0) return 50;
      return ((val - v11005) / range) * 100;
    };
 
    const minPos = getPosition(v11000);
    const q1Pos = getPosition(v11001);
    const medPos = getPosition(v11002);
    const q3Pos = getPosition(v11003);
    const maxPos = getPosition(v11004);
 
    return (
      <div className="relative w-48 h-4" title={tooltip}>
        {/* Whisker line */}
        <div 
          className="absolute top-1/2 h-0.5 bg-muted/50"
          style={{ 
            left: `${minPos}%`, 
            width: `${maxPos - minPos}%`,
            transform: 'translateY(-50%)'
          }}
        ></div>
        
        {/* Box (Q1 to Q3) */}
        <div 
          className="absolute top-0 h-full bg-brand/30 border border-brand"
          style={{ 
            left: `${q1Pos}%`, 
            width: `${q3Pos - q1Pos}%`
          }}
        ></div>
        
        {/* Median line */}
        <div 
          className="absolute top-0 h-full w-0.5 bg-red-600"
          style={{ left: `${medPos}%` }}
        ></div>
        
        {/* Min whisker cap */}
        <div 
          className="absolute top-0 h-full w-0.5 bg-muted"
          style={{ left: `${minPos}%` }}
        ></div>
        
        {/* Max whisker cap */}
        <div 
          className="absolute top-0 h-full w-0.5 bg-muted"
          style={{ left: `${maxPos}%` }}
        ></div>
      </div>
    );
  };
 
  return (
    <div className="p-6">
      <div className="mb-6">
        <h1 className="text-3xl font-bold mb-2">Sales by X</h1>
        <p className="text-muted text-sm">
          This report allows you to report sales by a range of obscure methods. 
          Start by selecting a "group by" method to lump all sales with that value in common together.
        </p>
      </div>
 
      {/* Filter Form */}
      <div className="bg-surface p-4 rounded-lg shadow mb-6">
        <div className="flex flex-wrap gap-4 items-end">
          <div>
            <label className="block text-sm font-medium mb-1">Group By</label>
            <select
              value={groupBy}
              onChange={(e) => setGroupBy(e.target.value)}
              className="border rounded px-3 py-2 min-w-[200px]"
            >
              {groupOptions.map(opt => (
                <option key={opt.value} value={opt.value}>{opt.label}</option>
              ))}
            </select>
          </div>
 
          <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">Max Rows</label>
            <input
              type="number"
              value={maxRows}
              onChange={(e) => setMaxRows(Number(e.target.value))}
              className="border rounded px-3 py-2 w-24"
            />
          </div>
 
          <div>
            <label className="block text-sm font-medium mb-1">Filter</label>
            <select
              value={selectedLocation}
              onChange={(e) => setSelectedLocation(Number(e.target.value))}
              className="border rounded px-3 py-2"
            >
              <option value="0">All Stores</option>
              {locations.map(loc => (
                <option key={loc.f100} value={loc.f100}>{loc.f101}</option>
              ))}
            </select>
          </div>
 
          <button
            onClick={loadReport}
            disabled={isLoading || !groupBy}
            className="bg-brand text-white px-6 py-2 rounded hover:bg-brand2 disabled:bg-muted/50"
          >
            {isLoading ? 'Loading...' : 'Run Report'}
          </button>
        </div>
      </div>
 
      {/* Loading Indicator */}
      {isLoading && (
        <div className="flex items-center gap-2 mb-4">
          <div className="animate-spin rounded-full h-5 w-5 border-b-2 border-info"></div>
          <span>Working...</span>
        </div>
      )}
 
      {/* Results Table */}
      <div className="bg-surface rounded-lg shadow overflow-hidden">
        <div className="overflow-x-auto">
          <table className="w-full">
            <thead className="bg-surface-2 border-b">
              <tr>
                <th 
                  className="px-4 py-3 text-left text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('groupValue')}
                >
                  Group Value <SortIndicator column="groupValue" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('numsales')}
                >
                  #Sales <SortIndicator column="numsales" />
                </th>
                <th 
                  className="px-4 py-3 text-left text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('percenttotal')}
                >
                  % of Sales Rev <SortIndicator column="percenttotal" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('units')}
                >
                  Items Sold <SortIndicator column="units" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('sales')}
                >
                  Sales <SortIndicator column="sales" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('avesale')}
                >
                  Avg. Sale$ <SortIndicator column="avesale" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('itemspersale')}
                >
                  Avg #Items/Sale <SortIndicator column="itemspersale" />
                </th>
                <th 
                  className="px-4 py-3 text-right text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('lastcost')}
                >
                  Last Cost <SortIndicator column="lastcost" />
                </th>
                <th 
                  className="px-4 py-3 text-left text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('gp')}
                >
                  GP% <SortIndicator column="gp" />
                </th>
                <th 
                  className="px-4 py-3 text-left text-sm font-medium"
                  title="Spread of total value of sales"
                >
                  Quartiles
                </th>
              </tr>
            </thead>
            <tbody className="divide-y">
              {!groupBy ? (
                <tr>
                  <td colSpan={10} className="px-4 py-8 text-center text-muted">
                    Please select a "Group By" method to view results
                  </td>
                </tr>
              ) : sortedData.length === 0 && !isLoading ? (
                <tr>
                  <td colSpan={10} className="px-4 py-8 text-center text-muted">
                    No data found for the selected criteria
                  </td>
                </tr>
              ) : (
                sortedData.map((group, idx) => (
                  <tr key={idx} className="hover:bg-surface-2">
                    <td className="px-4 py-3 text-sm">{group.groupValue || '(empty)'}</td>
                    <td className="px-4 py-3 text-sm text-right">{group.numsales.toLocaleString()}</td>
                    <td className="px-4 py-3 text-sm">
                      <SalesPctBar value={group.percenttotal} />
                    </td>
                    <td className="px-4 py-3 text-sm text-right">{group.units.toLocaleString()}</td>
                    <td className="px-4 py-3 text-sm text-right">
                      {new Intl.NumberFormat('en-AU', {
                        style: 'currency',
                        currency: 'AUD'
                      }).format(group.sales)}
                    </td>
                    <td className="px-4 py-3 text-sm text-right">
                      {new Intl.NumberFormat('en-AU', {
                        style: 'currency',
                        currency: 'AUD'
                      }).format(group.avesale)}
                    </td>
                    <td className="px-4 py-3 text-sm text-right">
                      {group.itemspersale.toFixed(1)}
                    </td>
                    <td className="px-4 py-3 text-sm text-right">
                      {new Intl.NumberFormat('en-AU', {
                        style: 'currency',
                        currency: 'AUD'
                      }).format(group.lastcost)}
                    </td>
                    <td className="px-4 py-3 text-sm">
                      <GPBar value={group.gp} />
                    </td>
                    <td className="px-4 py-3 text-sm">
                      <QuartileBox group={group} />
                    </td>
                  </tr>
                ))
              )}
            </tbody>
          </table>
        </div>
      </div>
 
      {/* Footer Summary */}
      {totalPeriodRev > 0 && (
        <div className="mt-4 text-right text-sm text-muted">
          Total Period Revenue: {new Intl.NumberFormat('en-AU', {
            style: 'currency',
            currency: 'AUD'
          }).format(totalPeriodRev)}
        </div>
      )}
    </div>
  );
}