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 AccountData {
  accid: number;
  name: string;
  numsales: number;
  sales: number;
  salesIncTax: number;
  lastcost: number;
  waccost: number;
  units: number;
  avesale: number;
  itemspersale: number;
  gp: number;
  percenttotal: number;
  lastSaleDate: string;
  v11000: number;
  v11001: number;
  v11002: number;
  v11003: number;
  v11004: number;
  v11005: number;
  v11006: number;
}
 
interface Location {
  f100: number;
  f101: string;
}
 
export default function SalesByAccountPage() {
  const [accountData, setAccountData] = useState<AccountData[]>([]);
  const [locations, setLocations] = useState<Location[]>([]);
  const [isLoading, setIsLoading] = useState(false);
  const [sortColumn, setSortColumn] = useState<keyof AccountData>('sales');
  const [sortDirection, setSortDirection] = useState<'asc' | 'desc'>('desc');
  const [totalPeriodRev, setTotalPeriodRev] = useState(0);
 
  // Filter states
  const [fromDate, setFromDate] = useState(() => {
    const d = new Date();
    d.setDate(1); // First of month
    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) {
      loadReport();
    }
  }, [fromDate, toDate, selectedLocation, 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) 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}` : '';
 
      // 10=11000,131 means we want quartile stats and last sale date
      const endpoint = `/buck?3=retailmax.elink.sale.totals.group&10=11000,131&15=2,1,accid&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 accounts: AccountData[] = 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 {
            accid: Number(rec.f1006 || 0),
            name: rec.f2006 || '',
            numsales: numsales,
            sales: sales,
            salesIncTax: Number(rec.f121 || 0),
            lastcost: lastcost,
            waccost: Number(rec.f103 || 0),
            units: units,
            avesale: avesale,
            itemspersale: itemspersale,
            gp: gp,
            percenttotal: 0,
            lastSaleDate: rec.f131 || '',
            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 account
        accounts.forEach(a => {
          if (totRev > 0) {
            a.percenttotal = (a.sales / totRev) * 100;
          }
        });
 
        setAccountData(accounts);
      } else {
        setAccountData([]);
      }
    } catch (error) {
      console.error('Error loading report:', error);
      setAccountData([]);
    }
    setIsLoading(false);
  };
 
  const handleSort = (column: keyof AccountData) => {
    if (sortColumn === column) {
      setSortDirection(sortDirection === 'asc' ? 'desc' : 'asc');
    } else {
      setSortColumn(column);
      setSortDirection('desc');
    }
  };
 
  const sortedData = [...accountData].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 AccountData }) => {
    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 = ({ account }: { account: AccountData }) => {
    if (account.numsales < 3) return null;
 
    const { v11000, v11001, v11002, v11003, v11004, v11005, v11006 } = account;
    
    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(v11005);
    const q1Pos = getPosition(v11001);
    const medPos = getPosition(v11002);
    const q3Pos = getPosition(v11003);
    const maxPos = getPosition(v11006);
 
    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>
    );
  };
 
  const buildSalesLink = (account: AccountData) => {
    const fromFormatted = formatDateForAPI(fromDate);
    const toFormatted = formatDateForAPI(toDate);
    let url = `https://my.fieldpine.com/report/pos/sales/fieldpine/salesflat_review.htm?sdt=${fromFormatted}&edt=${toFormatted}&accid=${account.accid}`;
    if (selectedLocation > 0) {
      url += `&loc=${selectedLocation}`;
    }
    return url;
  };
 
  const formatDate = (dateStr: string): string => {
    if (!dateStr) return '';
    const date = new Date(dateStr);
    return date.toLocaleDateString('en-AU', {
      year: 'numeric',
      month: 'short',
      day: 'numeric'
    });
  };
 
  return (
    <div className="p-6">
      <div className="mb-6">
        <h1 className="text-3xl font-bold mb-2">Sales by Account</h1>
        <p className="text-muted">Account sales performance summary</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">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">Store</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}
            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('accid')}
                >
                  Accid# <SortIndicator column="accid" />
                </th>
                <th 
                  className="px-4 py-3 text-left text-sm font-medium cursor-pointer hover:bg-surface-2"
                  onClick={() => handleSort('name')}
                >
                  Name <SortIndicator column="name" />
                </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')}
                  title="What percentage of total revenue this account made up. Based on the total of accounts currently showing on this report"
                >
                  % 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')}
                  title="Total number of items purchased"
                >
                  Items Purchased <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('lastSaleDate')}
                >
                  Last Sale <SortIndicator column="lastSaleDate" />
                </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">
              {sortedData.length === 0 && !isLoading ? (
                <tr>
                  <td colSpan={10} className="px-4 py-8 text-center text-muted">
                    No account data found for the selected period
                  </td>
                </tr>
              ) : (
                sortedData.map((account, idx) => (
                  <tr key={idx} className="hover:bg-surface-2">
                    <td className="px-4 py-3 text-sm">{account.accid}</td>
                    <td className="px-4 py-3 text-sm">{account.name}</td>
                    <td className="px-4 py-3 text-sm text-right">{account.numsales.toLocaleString()}</td>
                    <td className="px-4 py-3 text-sm">
                      <SalesPctBar value={account.percenttotal} />
                    </td>
                    <td className="px-4 py-3 text-sm text-right">{account.units.toLocaleString()}</td>
                    <td className="px-4 py-3 text-sm text-right">
                      <a 
                        href={buildSalesLink(account)}
                        target="_blank"
                        rel="noopener noreferrer"
                        className="text-brand hover:underline"
                      >
                        {new Intl.NumberFormat('en-AU', {
                          style: 'currency',
                          currency: 'AUD'
                        }).format(account.sales)}
                      </a>
                    </td>
                    <td className="px-4 py-3 text-sm text-right">
                      {formatDate(account.lastSaleDate)}
                    </td>
                    <td className="px-4 py-3 text-sm text-right">
                      {new Intl.NumberFormat('en-AU', {
                        style: 'currency',
                        currency: 'AUD'
                      }).format(account.lastcost)}
                    </td>
                    <td className="px-4 py-3 text-sm">
                      <GPBar value={account.gp} />
                    </td>
                    <td className="px-4 py-3 text-sm">
                      <QuartileBox account={account} />
                    </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>
  );
}