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 LostSale {
  f101: string; // Sale ID
  f102: string; // Date
  f103?: string; // Teller ID
  f104?: string; // Teller Name
  f105?: string; // Location ID
  f106?: string; // Store Name
  f107?: number; // Total Sale
  f108?: string; // Customer ID
  f200?: number; // Product ID
  f201?: string; // Product Name
  f202?: number; // Quantity
  f203?: number; // Line Price
  f206?: number; // Price Cause Code
  f208?: number; // Line Cost
  f209?: number; // Gross Profit
  f301?: string; // Payment Method
  PROD?: any[]; // Product details
  CUST?: any[]; // Customer details
}
 
export default function LostSalesPage() {
  const [locations, setLocations] = useState<Location[]>([]);
  const [departments, setDepartments] = useState<Department[]>([]);
  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 d = new Date();
    d.setDate(d.getDate() + 1); // Tomorrow
    return d.toISOString().split('T')[0];
  });
  const [selectedLocation, setSelectedLocation] = useState('0');
  const [selectedDepartment, setSelectedDepartment] = useState('0');
  const [maxRows, setMaxRows] = useState('200');
  const [sales, setSales] = useState<LostSale[]>([]);
  const [loading, setLoading] = useState(false);
  const [sortColumn, setSortColumn] = useState<keyof LostSale>('f102');
  const [sortDirection, setSortDirection] = useState<'asc' | 'desc'>('desc');
 
  useEffect(() => {
    loadLocations();
    loadDepartments();
    loadLostSales();
  }, []);
 
  const loadLocations = async () => {
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) return;
 
      const response = await fieldpineApi({
        endpoint: '/buck?3=retailmax.elink.locations&100=1',
        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 loadLostSales = async () => {
    setLoading(true);
    try {
      const apiKey = sessionStorage.getItem("fieldpine_apikey");
      if (!apiKey) {
        console.error("No API key found");
        setLoading(false);
        return;
      }
 
      let predicates = '';
 
      // Date range
      const fromFormatted = formatDateForAPI(fromDate);
      const toFormatted = formatDateForAPI(toDate);
      predicates += `&9=f102,4,${fromFormatted}`;
      predicates += `&9=f102,1,${toFormatted}`;
      
      // Filter for lost sales (f214 in specific status codes)
      predicates += '&9=f214,in,10000,10001,10002,128';
 
      // Add location filter
      if (selectedLocation !== '0' && selectedLocation !== '') {
        predicates += `&9=f105,0,${selectedLocation}`;
      }
 
      // Add department filter
      if (selectedDepartment !== '0' && selectedDepartment !== '') {
        predicates += `&9=f801,0,${selectedDepartment}`;
      }
 
      // Full product and customer packets
      predicates += '&103=1&104=1';
 
      // Add max rows limit
      predicates += `&8=${maxRows}`;
 
      const response = await fieldpineApi({
        endpoint: `/buck?3=retailmax.elink.saleflat.list${predicates}`,
        apiKey
      });
 
      if (response.APPT) {
        setSales(response.APPT);
      } else {
        setSales([]);
      }
    } catch (error) {
      console.error('Error loading lost sales:', error);
      setSales([]);
    } finally {
      setLoading(false);
    }
  };
 
  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 handleSearch = () => {
    loadLostSales();
  };
 
  const getPriceCause = (code?: number): string => {
    if (!code || code === 0) return '';
    switch (code) {
      case 1: return 'SpecOffer';
      case 2: return 'Reward';
      case 3: return 'Combo';
      case 4: return 'QtyDisc';
      case 5: return 'StoreDisc';
      case 6: return 'DiscManual';
      case 7: return 'PriceMap';
      case 8: return 'CustDisc';
      case 9: return 'TellerAction';
      case 11: return 'DiscVoucher';
      default: return String(code);
    }
  };
 
  const formatDate = (dateStr?: string): string => {
    if (!dateStr) return '';
    const date = new Date(dateStr);
    return date.toLocaleDateString('en-NZ', {
      year: 'numeric',
      month: 'short',
      day: 'numeric'
    });
  };
 
  const formatCurrency = (value?: number): string => {
    if (!value) return '$0.00';
    return new Intl.NumberFormat('en-NZ', {
      style: 'currency',
      currency: 'NZD'
    }).format(value);
  };
 
  const handleSort = (column: keyof LostSale) => {
    if (sortColumn === column) {
      setSortDirection(sortDirection === 'asc' ? 'desc' : 'asc');
    } else {
      setSortColumn(column);
      setSortDirection('asc');
    }
  };
 
  const sortedSales = [...sales].sort((a, b) => {
    const aVal = a[sortColumn];
    const bVal = b[sortColumn];
    
    if (aVal === undefined || bVal === undefined) return 0;
    
    if (typeof aVal === 'number' && typeof bVal === 'number') {
      return sortDirection === 'asc' ? aVal - bVal : bVal - aVal;
    }
    
    const aStr = String(aVal);
    const bStr = String(bVal);
    return sortDirection === 'asc' 
      ? aStr.localeCompare(bStr)
      : bStr.localeCompare(aStr);
  });
 
  const SortIcon = ({ column }: { column: keyof LostSale }) => {
    if (sortColumn !== column) return <span className="text-muted/50">⇅</span>;
    return <span>{sortDirection === 'asc' ? '↑' : '↓'}</span>;
  };
 
  const getCustomerName = (sale: LostSale): string => {
    if (sale.CUST && sale.CUST[0] && sale.CUST[0].f101) {
      return sale.CUST[0].f101;
    }
    return '';
  };
 
  return (
    <div className="p-6">
      {/* Header */}
      <div className="mb-6">
        <h1 className="text-3xl font-bold mb-2">Lost Sales 📉</h1>
        <p className="text-sm text-muted mb-2">
          Details of items that were ordered but unfulfilled when the order was shipped. Applies only to stores that use sales picking.
        </p>
        <div className="text-sm text-muted">
          <a href="/pages/reports/sales-reports" className="text-brand hover:underline">Sales</a>
          {' > '}
          <span>Lost Sales</span>
        </div>
      </div>
 
      {/* Filters */}
      <div className="bg-surface rounded-lg shadow p-6 mb-6">
        <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-4 mb-4">
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              From Date (including):
            </label>
            <input
              type="date"
              value={fromDate}
              onChange={(e) => setFromDate(e.target.value)}
              className="w-full border rounded px-3 py-2"
            />
          </div>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              To Date (excluding):
            </label>
            <input
              type="date"
              value={toDate}
              onChange={(e) => setToDate(e.target.value)}
              className="w-full border rounded px-3 py-2"
            />
          </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="w-full 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="w-full border rounded px-3 py-2"
            >
              <option value="0">--Select--</option>
              {departments.map(dept => (
                <option key={dept.f100} value={dept.f100}>
                  {dept.f101}
                </option>
              ))}
            </select>
          </div>
 
          <div>
            <label className="block text-sm font-medium text-text mb-2">
              Max Rows:
            </label>
            <input
              type="number"
              value={maxRows}
              onChange={(e) => setMaxRows(e.target.value)}
              className="w-full border rounded px-3 py-2"
            />
          </div>
 
          <div className="flex items-end">
            <button
              onClick={handleSearch}
              disabled={loading}
              className="w-full bg-brand text-white px-6 py-2 rounded hover:bg-brand2 disabled:bg-muted/50"
            >
              {loading ? 'Loading...' : 'Display'}
            </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 lost sales...</p>
          </div>
        ) : (
          <>
            <div className="overflow-x-auto max-h-[600px] overflow-y-auto">
              <table className="w-full text-sm">
                <thead className="bg-surface-2 sticky top-0">
                  <tr>
                    <th 
                      className="px-4 py-3 text-left font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f102')}
                    >
                      Date <SortIcon column="f102" />
                    </th>
                    <th 
                      className="px-4 py-3 text-left font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f106')}
                    >
                      Store <SortIcon column="f106" />
                    </th>
                    <th 
                      className="px-4 py-3 text-left font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f101')}
                    >
                      Sale# <SortIcon column="f101" />
                    </th>
                    <th 
                      className="px-4 py-3 text-right font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f107')}
                    >
                      Total Sale <SortIcon column="f107" />
                    </th>
                    <th 
                      className="px-4 py-3 text-left font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f104')}
                    >
                      Teller <SortIcon column="f104" />
                    </th>
                    <th className="px-4 py-3 text-left font-semibold">
                      Customer
                    </th>
                    <th 
                      className="px-4 py-3 text-left font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f201')}
                    >
                      Product <SortIcon column="f201" />
                    </th>
                    <th 
                      className="px-4 py-3 text-right font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f202')}
                    >
                      Qty <SortIcon column="f202" />
                    </th>
                    <th 
                      className="px-4 py-3 text-right font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f203')}
                    >
                      Line Price <SortIcon column="f203" />
                    </th>
                    <th 
                      className="px-4 py-3 text-right font-semibold cursor-pointer hover:bg-surface-2/80"
                      onClick={() => handleSort('f209')}
                    >
                      GP <SortIcon column="f209" />
                    </th>
                    <th className="px-4 py-3 text-center font-semibold">
                      C
                    </th>
                  </tr>
                </thead>
                <tbody className="divide-y divide-border">
                  {sortedSales.length === 0 ? (
                    <tr>
                      <td colSpan={11} className="px-4 py-8 text-center text-muted">
                        No lost sales found
                      </td>
                    </tr>
                  ) : (
                    sortedSales.map((sale, idx) => (
                      <tr key={idx} className="hover:bg-surface-2">
                        <td className="px-4 py-3 whitespace-nowrap">
                          {formatDate(sale.f102)}
                        </td>
                        <td className="px-4 py-3">
                          {sale.f106 || '-'}
                        </td>
                        <td className="px-4 py-3">
                          <a 
                            href={`/report/pos/sales/fieldpine/SingleSale.htm?sid=${sale.f101}`}
                            className="text-brand hover:underline"
                            target="_blank"
                            rel="noopener noreferrer"
                          >
                            {sale.f101}
                          </a>
                        </td>
                        <td className="px-4 py-3 text-right font-semibold">
                          {formatCurrency(sale.f107)}
                        </td>
                        <td className="px-4 py-3">
                          {sale.f104 || '-'}
                        </td>
                        <td className="px-4 py-3">
                          {getCustomerName(sale) || '-'}
                        </td>
                        <td className="px-4 py-3">
                          {sale.f200 ? (
                            <a 
                              href={`/report/pos/stock/fieldpine/singlepid.htm?pid=${sale.f200}`}
                              className="text-brand hover:underline"
                              target="_blank"
                              rel="noopener noreferrer"
                            >
                              {sale.f201 || sale.f200}
                            </a>
                          ) : (
                            sale.f201 || '-'
                          )}
                        </td>
                        <td className="px-4 py-3 text-right">
                          {sale.f202 || 0}
                        </td>
                        <td className="px-4 py-3 text-right">
                          {formatCurrency(sale.f203)}
                        </td>
                        <td className="px-4 py-3 text-right">
                          {formatCurrency(sale.f209)}
                        </td>
                        <td className="px-4 py-3 text-center text-xs">
                          {getPriceCause(sale.f206)}
                        </td>
                      </tr>
                    ))
                  )}
                </tbody>
              </table>
            </div>
            
            {sales.length > 0 && (
              <div className="px-4 py-3 bg-surface-2 border-t text-sm text-muted">
                Showing {sales.length} lost sale{sales.length !== 1 ? 's' : ''}
              </div>
            )}
          </>
        )}
      </div>
    </div>
  );
}