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 Location {
  f100: number;
  f101: string;
}
 
interface Department {
  f100: number;
  f101: string;
}
 
interface Supplier {
  f100: number;
  f101: string;
}
 
interface MonthData {
  locid: string;
  storename: string;
  months: { [key: number]: number };
  total: number;
  average: number;
  smallest: number;
  largest: number;
  monthCount: number;
}
 
export default function SalesByMonthPage() {
  const [locations, setLocations] = useState<Location[]>([]);
  const [departments, setDepartments] = useState<Department[]>([]);
  const [suppliers, setSuppliers] = useState<Supplier[]>([]);
  
  const [periodEnd, setPeriodEnd] = useState(() => {
    const d = new Date();
    return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`;
  });
  
  const [selectedLocation, setSelectedLocation] = useState('0');
  const [selectedDepartment, setSelectedDepartment] = useState('0');
  const [selectedSupplier, setSelectedSupplier] = useState('0');
  const [statistic, setStatistic] = useState('tax_totex');
  const [includeAccountPayments, setIncludeAccountPayments] = useState(false);
  
  const [storeData, setStoreData] = useState<MonthData[]>([]);
  const [loading, setLoading] = useState(false);
  const [sortColumn, setSortColumn] = useState<string>('storename');
  const [sortDirection, setSortDirection] = useState<'asc' | 'desc'>('asc');
 
  const monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
 
  useEffect(() => {
    loadLocations();
    loadDepartments();
    loadSuppliers();
  }, []);
 
  const loadLocations = async () => {
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) return;
 
      const response = await fieldpineApi({
        endpoint: '/buck?3=retailmax.elink.locations',
        apiKey
      });
 
      if (response.DATS) {
        setLocations(response.DATS);
      }
    } catch (error) {
      console.error('Error loading locations:', error);
    }
  };
 
  const loadDepartments = async () => {
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) return;
 
      const response = await fieldpineApi({
        endpoint: '/buck?3=retailmax.elink.departments',
        apiKey
      });
 
      if (response.DATS) {
        setDepartments(response.DATS);
      }
    } catch (error) {
      console.error('Error loading departments:', error);
    }
  };
 
  const loadSuppliers = async () => {
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) return;
 
      const response = await fieldpineApi({
        endpoint: '/buck?3=retailmax.elink.suppliers',
        apiKey
      });
 
      if (response.DATS) {
        setSuppliers(response.DATS);
      }
    } catch (error) {
      console.error('Error loading suppliers:', error);
    }
  };
 
  const loadReport = async () => {
    setLoading(true);
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) {
        console.error("No API key found");
        setLoading(false);
        return;
      }
 
      // Parse period end (format: YYYY-MM)
      const [year, month] = periodEnd.split('-').map(Number);
      
      // Calculate period start (12 months back)
      const fromDate = `01-${String(month).padStart(2, '0')}-${year - 1}`;
      const toDate = `01-${String(month).padStart(2, '0')}-${year}`;
 
      let exPreds = '';
 
      // Add location filter
      if (selectedLocation !== '0' && selectedLocation !== '') {
        exPreds += `&9=f100,0,${selectedLocation}`;
      }
 
      // Add supplier filter
      if (selectedSupplier !== '0' && selectedSupplier !== '') {
        exPreds += `&9=f1003,0,${selectedSupplier}`;
      }
 
      // Add department filter
      if (selectedDepartment !== '0' && selectedDepartment !== '') {
        exPreds += `&9=f1002,0,${selectedDepartment}`;
      }
 
      // Account payments filter
      if (!includeAccountPayments) {
        exPreds += '&9=f1008,5,0';
      }
 
      const endpoint = `/buck?3=retailmax.elink.sale.cube&100=6&101=${statistic}&9=f110,4,${fromDate}&9=f110,1,${toDate}${exPreds}`;
 
      const response = await fieldpineApi({
        endpoint,
        apiKey
      });
 
      if (response.APPD && response.APPD.length > 0) {
        // Process the data
        const dataMap: { [key: string]: MonthData } = {};
 
        response.APPD.forEach((item: any) => {
          const locid = String(item.f100 || '0');
          const monthStr = item.f101 || '';
          const value = Number(item.f201 || 0);
          const storeName = item.f1003 || 'Unknown Store';
 
          if (!dataMap[locid]) {
            dataMap[locid] = {
              locid,
              storename: storeName,
              months: {},
              total: 0,
              average: 0,
              smallest: Infinity,
              largest: 0,
              monthCount: 0
            };
          }
 
          const data = dataMap[locid];
 
          // Parse month from format "YYYY|M|D"
          const monthParts = monthStr.split('|');
          if (monthParts.length >= 2) {
            const monthNum = Number(monthParts[1]);
            data.months[monthNum] = value;
 
            if (value !== 0) {
              data.monthCount++;
              data.smallest = Math.min(data.smallest, value);
              data.largest = Math.max(data.largest, value);
            }
            data.total += value;
          }
        });
 
        // Calculate averages
        Object.values(dataMap).forEach(data => {
          if (data.monthCount > 0) {
            data.average = data.total / data.monthCount;
          }
          if (data.smallest === Infinity) data.smallest = 0;
        });
 
        setStoreData(Object.values(dataMap));
      } else {
        setStoreData([]);
      }
    } catch (error) {
      console.error('Error loading report:', error);
      setStoreData([]);
    } finally {
      setLoading(false);
    }
  };
 
  const formatValue = (value: number | undefined): string => {
    if (!value || value === 0) return '';
    
    if (statistic === 'qty') {
      return value.toFixed(0);
    } else {
      return new Intl.NumberFormat('en-NZ', {
        style: 'currency',
        currency: 'NZD',
        minimumFractionDigits: 2
      }).format(value);
    }
  };
 
  const handleSort = (column: string) => {
    if (sortColumn === column) {
      setSortDirection(sortDirection === 'asc' ? 'desc' : 'asc');
    } else {
      setSortColumn(column);
      setSortDirection('asc');
    }
  };
 
  const sortedData = [...storeData].sort((a, b) => {
    let aVal: any;
    let bVal: any;
 
    if (sortColumn === 'storename') {
      aVal = a.storename;
      bVal = b.storename;
    } else if (sortColumn.startsWith('month_')) {
      const monthNum = Number(sortColumn.split('_')[1]);
      aVal = a.months[monthNum] || 0;
      bVal = b.months[monthNum] || 0;
    } else {
      aVal = (a as any)[sortColumn] || 0;
      bVal = (b as any)[sortColumn] || 0;
    }
 
    if (typeof aVal === 'number' && typeof bVal === 'number') {
      return sortDirection === 'asc' ? aVal - bVal : bVal - aVal;
    }
 
    return sortDirection === 'asc' 
      ? String(aVal).localeCompare(String(bVal))
      : String(bVal).localeCompare(String(aVal));
  });
 
  const SortIcon = ({ column }: { column: string }) => {
    if (sortColumn !== column) return <span className="text-muted/50">⇅</span>;
    return <span>{sortDirection === 'asc' ? '↑' : '↓'}</span>;
  };
 
  // Get the 12 months to display based on period end
  const getMonthHeaders = () => {
    const [year, month] = periodEnd.split('-').map(Number);
    const headers = [];
    
    for (let i = 0; i < 12; i++) {
      let m = month + i;
      let y = year - 1;
      if (m > 12) {
        m -= 12;
        y++;
      }
      headers.push({ month: m, year: y, label: `${monthNames[m - 1]}-${y}` });
    }
    
    return headers;
  };
 
  const monthHeaders = getMonthHeaders();
 
  // Find the highest value for each store
  const getHighlightedMonth = (store: MonthData): number => {
    let max = 0;
    let maxMonth = 0;
    
    Object.entries(store.months).forEach(([monthNum, value]) => {
      if (value > max) {
        max = value;
        maxMonth = Number(monthNum);
      }
    });
    
    return maxMonth;
  };
 
  return (
    <div className="p-6">
      {/* Header */}
      <div className="mb-6">
        <h1 className="text-3xl font-bold mb-2">Sales by Month 📅</h1>
        <p className="text-sm text-muted mb-2">
          Monthly sales summary across the year
        </p>
        <div className="text-sm text-muted">
          <a href="/pages/reports/sales-reports" className="text-brand hover:underline">Sales</a>
          {' > '}
          <span>Sales by Month</span>
        </div>
      </div>
 
      {/* Filters */}
      <div className="bg-surface rounded-lg shadow p-6 mb-6">
        <div className="flex flex-wrap items-end gap-4 mb-4">
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Period End:
            </label>
            <input
              type="month"
              value={periodEnd}
              onChange={(e) => setPeriodEnd(e.target.value)}
              className="border rounded px-3 py-2"
            />
          </div>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Statistic:
            </label>
            <select
              value={statistic}
              onChange={(e) => setStatistic(e.target.value)}
              className="border rounded px-3 py-2"
            >
              <option value="qty">Quantity</option>
              <option value="tax_totex">Revenue ex Tax</option>
              <option value="tax_totinc">Revenue incl Tax</option>
            </select>
          </div>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Store:
            </label>
            <select
              value={selectedLocation}
              onChange={(e) => setSelectedLocation(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>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Department:
            </label>
            <select
              value={selectedDepartment}
              onChange={(e) => setSelectedDepartment(e.target.value)}
              className="border rounded px-3 py-2"
            >
              <option value="0">All Departments</option>
              {departments.map(dept => (
                <option key={dept.f100} value={dept.f100}>
                  {dept.f101}
                </option>
              ))}
              <option value="without">Without Department</option>
            </select>
          </div>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Supplier:
            </label>
            <select
              value={selectedSupplier}
              onChange={(e) => setSelectedSupplier(e.target.value)}
              className="border rounded px-3 py-2"
            >
              <option value="0">All Suppliers</option>
              {suppliers.map(sup => (
                <option key={sup.f100} value={sup.f100}>
                  {sup.f101}
                </option>
              ))}
            </select>
          </div>
 
          <div className="flex items-center">
            <label className="flex items-center gap-2">
              <input
                type="checkbox"
                checked={includeAccountPayments}
                onChange={(e) => setIncludeAccountPayments(e.target.checked)}
                className="rounded"
              />
              <span className="text-sm">Include Account Payments</span>
            </label>
          </div>
 
          <div>
            <button
              onClick={loadReport}
              disabled={loading}
              className="bg-brand text-white px-6 py-2 rounded hover:bg-brand2 disabled:bg-muted/50"
            >
              {loading ? 'Loading...' : 'Load Period'}
            </button>
          </div>
        </div>
      </div>
 
      {/* Results Table */}
      <div className="bg-surface rounded-lg shadow overflow-hidden">
        {loading ? (
          <div className="p-8 text-center">
            <div className="inline-block animate-spin rounded-full h-8 w-8 border-b-2 border-info"></div>
            <p className="mt-2 text-muted">Loading sales data...</p>
          </div>
        ) : (
          <>
            <div className="overflow-x-auto">
              <table className="w-full text-sm">
                <thead className="bg-[var(--brand)] text-surface sticky top-0">
                  <tr>
                    <th 
                      className="px-3 py-2 text-left font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('locid')}
                    >
                      Id# <SortIcon column="locid" />
                    </th>
                    <th 
                      className="px-3 py-2 text-left font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('storename')}
                    >
                      Store Name <SortIcon column="storename" />
                    </th>
                    {monthHeaders.map((header, idx) => (
                      <th 
                        key={idx}
                        className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                        onClick={() => handleSort(`month_${header.month}`)}
                      >
                        {header.label} <SortIcon column={`month_${header.month}`} />
                      </th>
                    ))}
                    <th 
                      className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('total')}
                    >
                      Total <SortIcon column="total" />
                    </th>
                    <th 
                      className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('average')}
                      title="Average of non zero months"
                    >
                      Average <SortIcon column="average" />
                    </th>
                    <th 
                      className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('smallest')}
                      title="Smallest value seen over year"
                    >
                      Smallest <SortIcon column="smallest" />
                    </th>
                    <th 
                      className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('largest')}
                      title="Largest value seen over year"
                    >
                      Largest <SortIcon column="largest" />
                    </th>
                    <th 
                      className="px-3 py-2 text-right font-semibold cursor-pointer hover:bg-surface-2/80 whitespace-nowrap"
                      onClick={() => handleSort('monthCount')}
                      title="How many months have values other than zero"
                    >
                      Month Count <SortIcon column="monthCount" />
                    </th>
                  </tr>
                </thead>
                <tbody className="divide-y divide-border">
                  {sortedData.length === 0 ? (
                    <tr>
                      <td colSpan={17} className="px-4 py-8 text-center text-muted">
                        No sales data found for the selected period
                      </td>
                    </tr>
                  ) : (
                    sortedData.map((store, idx) => {
                      const highlightedMonth = getHighlightedMonth(store);
                      return (
                        <tr key={idx} className={idx % 2 === 0 ? 'bg-surface' : 'bg-surface-2'}>
                          <td className="px-3 py-2">
                            {store.locid}
                          </td>
                          <td className="px-3 py-2 font-medium whitespace-nowrap">
                            {store.storename}
                          </td>
                          {monthHeaders.map((header, mIdx) => {
                            const value = store.months[header.month];
                            const isHighlighted = header.month === highlightedMonth && value > 0;
                            return (
                              <td 
                                key={mIdx} 
                                className={`px-3 py-2 text-right whitespace-nowrap ${isHighlighted ? 'bg-green-100' : ''}`}
                              >
                                {formatValue(value)}
                              </td>
                            );
                          })}
                          <td className="px-3 py-2 text-right font-semibold whitespace-nowrap">
                            {formatValue(store.total)}
                          </td>
                          <td className="px-3 py-2 text-right whitespace-nowrap">
                            {formatValue(store.average)}
                          </td>
                          <td className="px-3 py-2 text-right whitespace-nowrap">
                            {formatValue(store.smallest)}
                          </td>
                          <td className="px-3 py-2 text-right whitespace-nowrap">
                            {formatValue(store.largest)}
                          </td>
                          <td className="px-3 py-2 text-right">
                            {store.monthCount}
                          </td>
                        </tr>
                      );
                    })
                  )}
                </tbody>
              </table>
            </div>
            
            {storeData.length > 0 && (
              <div className="px-4 py-3 bg-surface-2 border-t text-sm text-muted">
                Displaying {storeData.length} store{storeData.length !== 1 ? 's' : ''}. 
                Highest value per store highlighted in green.
              </div>
            )}
          </>
        )}
      </div>
    </div>
  );
}