Download web/src/sheet/PivotTab.tsx from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 19.7 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/web/src/sheet/PivotTab.tsx
- Command line
-
hf download hf://spaces/fsanyoto/loopable/web/src/sheet/PivotTab.tsx
-
curl -L -o PivotTab.tsx https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/web/src/sheet/PivotTab.tsx
19.7 kB
| // S70-T17 / C6. This worksheet stores configuration only. The server owns all aggregation. | |
| import { useEffect, useId, useRef, useState } from "react"; | |
| import type { ReactNode } from "react"; | |
| import { API_V1, CREDENTIALS } from "../apiContract"; | |
| import { FieldTypeIcon } from "../customer-grid/icons"; | |
| import { limitSummary } from "../customer-grid/counts"; | |
| import { AnchoredOverlay, BodyPortal, OverlayProvider, useOverlayLayer } from "../customer-grid/OverlaySurface"; | |
| import { choiceVocabulary, isFilterGroup } from "../customer-grid/types"; | |
| import type { Field, FilterNode, FilterTree, GridLimit } from "../customer-grid/types"; | |
| import { FilterBuilderPanel, FilterFieldStatusContext } from "../filter-kit/FilterBuilderPanel"; | |
| import type { FilterValuesState } from "../filter-kit/FilterBuilderPanel"; | |
| import { isNumericType } from "../filter-kit/ops"; | |
| import { fetchDistinctValues } from "./sheetApi"; | |
| import { pivotAxisLabel, readPivotAnswer } from "./types"; | |
| import type { PivotAnswer, PivotSpec, SheetTab } from "./types"; | |
| const EMPTY_PIVOT: PivotSpec = { database: "", rows: [], columns: [], values: [], filters: [] }; | |
| const AGGREGATES = [ | |
| ["sum", "Sum"], ["count", "Count"], ["avg", "Average"], ["min", "Min"], ["max", "Max"], | |
| ] as const; | |
| const numberFormat = new Intl.NumberFormat(undefined, { maximumFractionDigits: 6 }); | |
| /** Keep unrecognised limits readable without showing their wire representation. */ | |
| function pivotLimits(limits: unknown): GridLimit[] { | |
| const entries = Array.isArray(limits) ? limits | |
| : limits && (typeof limits !== "object" || Object.keys(limits).length) ? [limits] : []; | |
| return entries.map(raw => { | |
| const entry = raw && typeof raw === "object" ? raw as Partial<GridLimit> : {}; | |
| return { | |
| subject: typeof entry.subject === "string" ? entry.subject : "", | |
| effect: typeof entry.effect === "string" ? entry.effect : "unknown", | |
| cause: typeof entry.cause === "string" ? entry.cause : "A limit applies to this pivot.", | |
| recommendation: typeof entry.recommendation === "string" ? entry.recommendation : "", | |
| }; | |
| }); | |
| } | |
| function filtersAvailable(nodes: FilterNode[], keys: Set<string>): boolean { | |
| return nodes.every(node => isFilterGroup(node) ? filtersAvailable(node.children, keys) | |
| : keys.has(node.colId) && (!node.rhs || !("colId" in node.rhs) || keys.has(node.rhs.colId))); | |
| } | |
| /** C6 stores a node list; an OR at the builder root must keep its conjunction in a group. */ | |
| export const pivotFilterNodes = (tree: FilterTree): FilterNode[] => tree.conj === "or" | |
| && tree.nodes.length ? [{ conj: "or", children: tree.nodes }] : tree.nodes; | |
| function PivotFilterDialog({ children, onClose }: { children: ReactNode; onClose: () => void }) { | |
| const panelRef = useRef<HTMLDivElement>(null); | |
| const titleId = useId(); | |
| useOverlayLayer({ panelRef, onDismiss: onClose, dismissOnOutside: false, | |
| initialFocus: "first", restoreFocus: true, trapFocus: true }); | |
| return <BodyPortal><div className="lp-dialog-scrim"> | |
| <div ref={panelRef} className="lp-dialog" role="dialog" aria-modal="true" aria-labelledby={titleId} | |
| > | |
| <div className="lp-dialog-head"> | |
| <h2 id={titleId} className="lp-dialog-title">Filters</h2> | |
| <button type="button" className="lp-dialog-x" aria-label="Close filters" onClick={onClose}>×</button> | |
| </div> | |
| <div className="lp-dialog-body">{children}</div> | |
| <div className="lp-dialog-actions"> | |
| <button type="button" className="lp-dialog-btn lp-dialog-btn-primary" onClick={onClose}>Done</button> | |
| </div> | |
| </div> | |
| </div></BodyPortal>; | |
| } | |
| function PivotFilters({ database, fields, userOptions, nodes, onChange }: { | |
| database: string; fields: Field[]; userOptions: string[]; nodes: FilterNode[]; | |
| onChange: (nodes: FilterNode[]) => void; | |
| }) { | |
| const [valuesState, setValuesState] = useState<Record<string, FilterValuesState>>({}); | |
| const values = useRef<Record<string, FilterValuesState>>({}); | |
| const live = useRef(true); | |
| useEffect(() => { live.current = true; return () => { live.current = false; }; }, []); | |
| const loadValues = async (key: string, search = "", more = false) => { | |
| if (!fields.some(field => field.key === key)) return; | |
| const held = values.current[key]; | |
| if ((held?.pending && held.search === search) || (more && !held?.hasMore)) return; | |
| const offset = more && held ? held.offset : 0; | |
| const pending: FilterValuesState = { values: more && held ? held.values : [], search, offset, | |
| pending: true, loaded: false, hasMore: more && held ? held.hasMore : false }; | |
| values.current = { ...values.current, [key]: pending }; | |
| setValuesState(values.current); | |
| const got = await fetchDistinctValues(database, key, { offset, search, limit: 200 }); | |
| if (!live.current || values.current[key] !== pending) return; | |
| const next: FilterValuesState = got.ok ? { | |
| ...pending, values: [...new Set([...pending.values, ...got.value.values])], pending: false, | |
| loaded: true, hasMore: got.value.hasMore, offset: got.value.offset + got.value.limit, | |
| } : { ...pending, pending: false, error: "Unavailable" }; | |
| values.current = { ...values.current, [key]: next }; | |
| setValuesState(values.current); | |
| }; | |
| const statusValues = Object.fromEntries(fields.map(field => { | |
| const declared = choiceVocabulary(field, []); | |
| return [field.key, field.type === "user" ? [...new Set([...userOptions, ...(valuesState[field.key]?.values ?? [])])] | |
| : declared.length ? declared : valuesState[field.key]?.values ?? []]; | |
| })); | |
| // Unwrap only the single root group produced above. The builder can keep editing OR faithfully. | |
| const filters: FilterTree = nodes.length === 1 && isFilterGroup(nodes[0]) | |
| ? { conj: nodes[0].conj, nodes: nodes[0].children } : { conj: "and", nodes }; | |
| return <FilterFieldStatusContext.Provider value={{ relatedPending: false, unavailable: [], | |
| valuesState, wantValues: (key, search) => { void loadValues(key, search); }, | |
| moreValues: key => { void loadValues(key, values.current[key]?.search, true); } }}> | |
| <FilterBuilderPanel database={database} fields={fields} filters={filters} statusValues={statusValues} | |
| userOptions={userOptions} onChange={tree => onChange(pivotFilterNodes(tree))} /> | |
| </FilterFieldStatusContext.Provider>; | |
| } | |
| export default function PivotTab({ tab, databases, readOnly, onChange }: { | |
| tab: SheetTab; databases: { key: string; label: string }[]; readOnly: boolean; | |
| onChange: (pivot: PivotSpec) => void; | |
| }) { | |
| const spec = tab.pivot ?? EMPTY_PIVOT; | |
| const [refresh, setRefresh] = useState(0); | |
| const [search, setSearch] = useState(""); | |
| const [filtersOpen, setFiltersOpen] = useState(false); | |
| const [limitOpenKey, setLimitOpenKey] = useState<string | null>(null); | |
| const limitTrigger = useRef<HTMLButtonElement>(null); | |
| const limitTipId = useId(); | |
| const [schema, setSchema] = useState<{ | |
| key: string; fields: Field[]; userOptions: string[]; error?: string; | |
| } | null>(null); | |
| const [aggregate, setAggregate] = useState<{ | |
| key: string; answer?: PivotAnswer; error?: string; | |
| } | null>(null); | |
| const allowed = databases.some(database => database.key === spec.database); | |
| // Refresh rechecks permission as well as data. No memo may outrun a changed field wall. | |
| const schemaKey = JSON.stringify([tab.id, spec.database, databases, refresh]); | |
| const resultKey = JSON.stringify([schemaKey, spec]); | |
| const schemaReady = allowed && schema?.key === schemaKey && !schema.error; | |
| const fields = schemaReady ? schema.fields : []; | |
| const keys = new Set(fields.map(field => field.key)); | |
| const scoped = schemaReady && spec.rows.every(key => keys.has(key)) | |
| && spec.columns.every(key => keys.has(key)) && spec.values.every(value => keys.has(value.field)) | |
| && filtersAvailable(spec.filters, keys); | |
| const ready = scoped && spec.values.length > 0; | |
| const current = ready && aggregate?.key === resultKey ? aggregate : null; | |
| useEffect(() => { | |
| const controller = new AbortController(); | |
| setSchema(null); | |
| setAggregate(null); | |
| if (!allowed) return () => controller.abort(); | |
| // Same schema-only door as BindingEditor. It declares fields/options without reading records. | |
| const scope = spec.database === "customer_data" ? "customer" | |
| : spec.database === "product_data" ? "product" : spec.database; | |
| void (async () => { | |
| try { | |
| const response = await fetch(`${API_V1}/workspace?scope=${encodeURIComponent(scope)}&fields_only=1`, | |
| { credentials: CREDENTIALS, signal: controller.signal }); | |
| const body = await response.json(); | |
| if (controller.signal.aborted) return; | |
| if (!response.ok || body?.workspace?.fieldsOnly !== true || !Array.isArray(body.workspace.fields)) { | |
| setSchema({ key: schemaKey, fields: [], userOptions: [], | |
| error: response.status === 403 ? "No access" : "Unavailable" }); | |
| return; | |
| } | |
| setSchema({ key: schemaKey, fields: body.workspace.fields, | |
| userOptions: Array.isArray(body.workspace.userOptions) ? body.workspace.userOptions : [] }); | |
| } catch { | |
| if (!controller.signal.aborted) | |
| setSchema({ key: schemaKey, fields: [], userOptions: [], error: "Unavailable" }); | |
| } | |
| })(); | |
| return () => controller.abort(); | |
| }, [schemaKey, allowed]); // All inputs to the read are in schemaKey. | |
| useEffect(() => { | |
| const controller = new AbortController(); | |
| setAggregate(null); | |
| if (!ready) return () => controller.abort(); | |
| void (async () => { | |
| try { | |
| const response = await fetch(`${API_V1}/sheets/pivot`, { | |
| method: "POST", credentials: CREDENTIALS, signal: controller.signal, | |
| headers: { "Content-Type": "application/json" }, body: JSON.stringify(spec), | |
| }); | |
| const raw = await response.json(); | |
| if (controller.signal.aborted) return; | |
| if (response.status === 403) { | |
| // A fresh permission refusal invalidates the earlier field offer as well as the matrix. | |
| setSchema({ key: schemaKey, fields: [], userOptions: [], error: "No access" }); | |
| } | |
| const answer = response.ok ? readPivotAnswer(raw) : null; | |
| // A named field limit keeps the offered fields; only a 403 means the access changed. | |
| const limit = (raw?.error?.code === "pivot_field_unsupported" || raw?.error?.code === "pivot_too_large") | |
| && typeof raw.error.message === "string" ? raw.error.message : null; | |
| setAggregate({ key: resultKey, ...(answer ? { answer } : { | |
| error: response.status === 403 ? "No access" : response.ok && raw?.ok === true | |
| ? "Invalid response" : limit ?? "Refused", | |
| }) }); | |
| } catch { | |
| if (!controller.signal.aborted) setAggregate({ key: resultKey, error: "Unavailable" }); | |
| } | |
| })(); | |
| return () => controller.abort(); | |
| }, [resultKey, ready]); | |
| const change = (next: PivotSpec) => { if (!readOnly) { setAggregate(null); onChange(next); } }; | |
| const label = (key: string) => fields.find(field => field.key === key)?.label ?? "Unavailable"; | |
| const addField = (field: Field, area: "rows" | "columns" | "values") => { | |
| if (area === "values") change({ ...spec, values: [...spec.values, | |
| { field: field.key, agg: isNumericType(field.type) ? "sum" : "count" }] }); | |
| else if (!spec[area].includes(field.key)) change({ ...spec, [area]: [...spec[area], field.key] }); | |
| }; | |
| const answer = current?.answer; | |
| const limits = answer?.limits; | |
| const limitNote = limitSummary(pivotLimits(limits), undefined, | |
| key => keys.has(key) ? label(key) : fields.find(field => field.label === key)?.label); | |
| const limitText = limitNote ? `${limitNote.short}\n\n${limitNote.full}` : ""; | |
| const status = !spec.database ? "Choose a database" : !allowed ? "No access" | |
| : schema?.key === schemaKey && schema.error ? schema.error : !schemaReady ? "Loading" | |
| : !scoped ? "Unavailable fields" : !spec.values.length ? "Choose a value" | |
| : current?.error ?? (!answer ? "Loading" : !answer.rows.length ? "No results" : ""); | |
| return <section className="sheet-grid-wrap" aria-label="PivotTable" data-sheet-kind="pivot"> | |
| <div className="sheet-grid-head"> | |
| <label className="sheet-field" style={{ flex: "1 1 auto" }}> | |
| <span>Database</span> | |
| <select aria-label="Pivot database" value={allowed ? spec.database : ""} disabled={readOnly} | |
| onChange={event => { setSearch(""); setFiltersOpen(false); change({ database: event.target.value, | |
| rows: [], columns: [], values: [], filters: [] }); }}> | |
| <option value="">Choose a database</option> | |
| {databases.map(database => <option key={database.key} value={database.key}>{database.label}</option>)} | |
| </select> | |
| </label> | |
| <button type="button" className="lp-btn lp-btn-quiet" disabled={!spec.database || !allowed} | |
| onClick={() => { setFiltersOpen(false); setSchema(null); setAggregate(null); setRefresh(value => value + 1); }}> | |
| Refresh | |
| </button> | |
| </div> | |
| <div style={{ display: "flex", flex: "1 1 auto", minHeight: 0, overflow: "auto", flexWrap: "wrap" }}> | |
| <aside aria-label="Pivot fields" className="cg-detail-section" style={{ flex: "0 1 var(--lp-rail-w)", minWidth: 0, | |
| fontSize: "var(--lp-fs-xs)", | |
| borderRight: "1px solid var(--lp-line)", overflow: "auto" }}> | |
| <h2 className="sheet-binding-title">Fields</h2> | |
| <label className="sheet-field"> | |
| <span>Find a field</span> | |
| <input aria-label="Find a pivot field" value={search} onChange={event => setSearch(event.target.value)} /> | |
| </label> | |
| <div style={{ maxHeight: "12rem", overflow: "auto" }}> | |
| {fields.filter(field => field.label.toLowerCase().includes(search.toLowerCase())).map(field => | |
| <div key={field.key} className="pg-list-item" style={{ alignItems: "center", flexWrap: "wrap" }}> | |
| <FieldTypeIcon type={field.type} /> | |
| <span style={{ flex: "1 1 auto", fontSize: "var(--lp-fs-xs)", color: "var(--lp-ink)" }}>{field.label}</span> | |
| {!readOnly && <span style={{ display: "flex", flexWrap: "wrap" }}> | |
| {(["rows", "columns", "values"] as const).map(area => <button key={area} type="button" | |
| className="lp-btn lp-btn-quiet" aria-label={`Add ${field.label} to ${area}`} | |
| disabled={area !== "values" && spec[area].includes(field.key)} onClick={() => addField(field, area)}> | |
| {area === "rows" ? "Rows" : area === "columns" ? "Columns" : "Values"} | |
| </button>)} | |
| </span>} | |
| </div>)} | |
| </div> | |
| {(["rows", "columns"] as const).map(area => <fieldset key={area} className="sheet-scope" disabled={readOnly || !schemaReady}> | |
| <legend>{area === "rows" ? "Rows" : "Columns"}</legend> | |
| {spec[area].map((key, index) => <div key={`${key}:${index}`} className="sheet-field-row" style={{ alignItems: "center" }}> | |
| <span style={{ flex: "1 1 auto" }}>{label(key)}</span> | |
| {!readOnly && <button type="button" className="lp-btn lp-btn-quiet" aria-label={`Remove ${area} field ${index + 1}`} | |
| onClick={() => change({ ...spec, [area]: spec[area].filter((_, i) => i !== index) })}>Remove</button>} | |
| </div>)} | |
| </fieldset>)} | |
| <fieldset className="sheet-scope" disabled={readOnly || !schemaReady}> | |
| <legend>Values</legend> | |
| {spec.values.map((value, index) => <div key={`${value.field}:${index}`}> | |
| <label className="sheet-field"> | |
| <span>{label(value.field)}</span> | |
| <select aria-label={`Aggregate for value ${index + 1}`} value={value.agg} | |
| onChange={event => change({ ...spec, values: spec.values.map((held, i) => i === index | |
| ? { ...held, agg: event.target.value as PivotSpec["values"][number]["agg"] } : held) })}> | |
| {AGGREGATES.map(([key, name]) => <option key={key} value={key}>{name}</option>)} | |
| </select> | |
| </label> | |
| {!readOnly && <button type="button" className="lp-btn lp-btn-quiet" aria-label={`Remove value ${index + 1}`} | |
| onClick={() => change({ ...spec, values: spec.values.filter((_, i) => i !== index) })}>Remove</button>} | |
| </div>)} | |
| </fieldset> | |
| <fieldset className="sheet-scope" disabled={readOnly || !schemaReady}> | |
| <legend>Filters</legend> | |
| {scoped ? <button type="button" className="lp-btn lp-btn-quiet" aria-label="Edit pivot filters" | |
| onClick={() => setFiltersOpen(true)}>Filters</button> : schemaReady && <> | |
| <span role="status">Unavailable</span> | |
| {!readOnly && <button type="button" className="lp-btn lp-btn-quiet" | |
| onClick={() => change({ ...spec, filters: [] })}>Clear filters</button>} | |
| </>} | |
| </fieldset> | |
| </aside> | |
| <div aria-label="Pivot results" aria-busy={status === "Loading"} | |
| style={{ flex: "1 1 var(--lp-rail-w)", minWidth: 0, overflow: "auto", color: "var(--lp-ink)", fontSize: "var(--lp-fs-xs)" }}> | |
| {status && <p className="sheet-placeholder-note" role={current?.error ? "alert" : "status"}>{status}</p>} | |
| {answer && <> | |
| <div className="sheet-grid-head" role="status" style={{ fontVariantNumeric: "tabular-nums" }}> | |
| <span>{`${numberFormat.format(answer.total_rows)} records`}</span> | |
| {limitNote && <> | |
| <button ref={limitTrigger} type="button" className="lp-btn lp-btn-quiet" | |
| title={limitText} aria-describedby={limitOpenKey === resultKey ? limitTipId : undefined} | |
| onMouseEnter={() => setLimitOpenKey(resultKey)} | |
| onMouseLeave={event => { | |
| if (document.activeElement !== event.currentTarget) setLimitOpenKey(null); | |
| }} | |
| onFocus={() => setLimitOpenKey(resultKey)} onBlur={() => setLimitOpenKey(null)}>Limited</button> | |
| {limitOpenKey === resultKey && <AnchoredOverlay anchor={limitTrigger.current} | |
| id={limitTipId} className="cg-pop sheet-pivot-limit-tip" role="tooltip" | |
| initialFocus="none" restoreFocus={false} onDismiss={() => setLimitOpenKey(null)}> | |
| {limitText} | |
| </AnchoredOverlay>} | |
| </>} | |
| </div> | |
| <table aria-label="PivotTable results" className="pg-table" style={{ fontVariantNumeric: "tabular-nums" }}> | |
| <thead><tr> | |
| <th scope="col"> | |
| {spec.rows.map(label).join(" / ") || "Total"} | |
| </th> | |
| {answer.columns.map((column, index) => <th key={index} scope="col" | |
| className="is-right">{pivotAxisLabel(column)}</th>)} | |
| </tr></thead> | |
| <tbody>{answer.rows.map((row, rowIndex) => <tr key={rowIndex}> | |
| <th scope="row" style={{ fontWeight: 500 }}>{pivotAxisLabel(row)}</th> | |
| {answer.cells[rowIndex].map((cell, colIndex) => <td key={colIndex} | |
| className="is-right" title={cell === null ? undefined : String(cell)}> | |
| {cell === null ? "" : numberFormat.format(cell)} | |
| </td>)} | |
| </tr>)}</tbody> | |
| </table> | |
| </>} | |
| </div> | |
| </div> | |
| {filtersOpen && scoped && !readOnly && <OverlayProvider> | |
| <PivotFilterDialog onClose={() => setFiltersOpen(false)}> | |
| <PivotFilters key={schemaKey} database={spec.database} fields={fields} | |
| userOptions={schema?.userOptions ?? []} nodes={spec.filters} | |
| onChange={filters => change({ ...spec, filters })} /> | |
| </PivotFilterDialog> | |
| </OverlayProvider>} | |
| </section>; | |
| } | |