/* ============================================================ CurrencyDesk OS — Ledger Import Owner / manager tool: drop a CSV or Excel export, map the columns to the house format, review, and post into the ledger. ============================================================ */ (function () { const { useState, useMemo, useRef } = React; const { CD, Ic, fmt, num, TYPES, CCY, newTx, mkRef, TODAY, crossRate } = window.CDOS; const ICFG_KEY = 'cdos_import_cfg_v1'; const DEF_CFG = { dateFormat: 'auto', defaultInCcy: 'CAD', defaultType: 'Currency Exchange', autoCreateClients: true, skipDuplicateRefs: true }; const loadCfg = () => { try { return Object.assign({}, DEF_CFG, JSON.parse(localStorage.getItem(ICFG_KEY) || '{}') || {}); } catch (e) { return Object.assign({}, DEF_CFG); } }; const saveCfgPatch = (patch) => { const next = Object.assign({}, loadCfg(), patch); try { localStorage.setItem(ICFG_KEY, JSON.stringify(next)); } catch (e) {} return next; }; // target fields the ledger understands, in display order const TARGETS = [ { key: 'date', label: 'Date', req: true }, { key: 'time', label: 'Time' }, { key: 'customer', label: 'Customer', req: true }, { key: 'type', label: 'Transaction type' }, { key: 'beneficiary', label: 'Beneficiary' }, { key: 'inAmt', label: 'Pay-in amount', req: true }, { key: 'inCcy', label: 'Pay-in currency' }, { key: 'outAmt', label: 'Pay-out amount' }, { key: 'outCcy', label: 'Pay-out currency' }, { key: 'rate', label: 'Rate' }, { key: 'fee', label: 'Fee' }, { key: 'ref', label: 'Reference' }, { key: 'notes', label: 'Notes / memo' }, { key: 'teller', label: 'Teller' }, ]; const SYN = { date: ['date', 'txndate', 'transactiondate', 'day', 'dealdate', 'valuedate'], time: ['time', 'txntime', 'timestamp'], customer: ['customer', 'client', 'name', 'sender', 'payer', 'conductor', 'clientname', 'customername'], type: ['type', 'transactiontype', 'product', 'service', 'txntype', 'category'], beneficiary: ['beneficiary', 'recipient', 'payee', 'receiver', 'beneficiaryname'], inAmt: ['payin', 'payinamount', 'amountin', 'amount', 'inamount', 'paidin', 'debit', 'sellamount', 'fromamount', 'amountpaid'], inCcy: ['payincurrency', 'incurrency', 'fromcurrency', 'currencyin', 'sellcurrency', 'currency', 'ccyin', 'fromccy'], outAmt: ['payout', 'payoutamount', 'amountout', 'paidout', 'credit', 'buyamount', 'toamount', 'received', 'amountreceived'], outCcy: ['payoutcurrency', 'outcurrency', 'tocurrency', 'currencyout', 'buycurrency', 'ccyout', 'toccy', 'receivedcurrency'], rate: ['rate', 'fxrate', 'exchangerate', 'rateapplied'], fee: ['fee', 'commission', 'charge', 'fees', 'servicefee'], ref: ['ref', 'reference', 'id', 'txnid', 'transactionid', 'number', 'refno', 'referencenumber'], notes: ['notes', 'memo', 'description', 'purpose', 'remarks', 'note', 'comment'], teller: ['teller', 'agent', 'staff', 'cashier', 'operator', 'processedby', 'handledby'], }; /* ---------- parsing ---------- */ const norm = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]/g, ''); const pad = (n) => String(n).padStart(2, '0'); // delimited text → array of string[] rows. Handles quotes, escaped quotes, embedded newlines. function parseDelimited(text, delim) { const rows = []; let row = [], field = '', i = 0, q = false; const n = text.length; while (i < n) { const c = text[i]; if (q) { if (c === '"') { if (text[i + 1] === '"') { field += '"'; i += 2; continue; } q = false; i++; continue; } field += c; i++; continue; } if (c === '"') { q = true; i++; continue; } if (c === delim) { row.push(field); field = ''; i++; continue; } if (c === '\n' || c === '\r') { if (c === '\r' && text[i + 1] === '\n') i++; row.push(field); rows.push(row); row = []; field = ''; i++; continue; } field += c; i++; } if (field.length || row.length) { row.push(field); rows.push(row); } return rows.filter(r => r.some(c => String(c).trim() !== '')); } function sniffDelim(text) { const head = text.split(/\r?\n/).slice(0, 5).join('\n'); const counts = { ',': (head.match(/,/g) || []).length, '\t': (head.match(/\t/g) || []).length, ';': (head.match(/;/g) || []).length }; return Object.keys(counts).sort((a, b) => counts[b] - counts[a])[0] || ','; } // ---- minimal XLSX reader (central-directory zip + inflate-raw) ---- const u16 = (d, o) => d[o] | (d[o + 1] << 8); const u32 = (d, o) => (d[o] | (d[o + 1] << 8) | (d[o + 2] << 16) | (d[o + 3] << 24)) >>> 0; async function inflateRaw(bytes) { if (typeof DecompressionStream === 'undefined') throw new Error('no-inflate'); const ds = new DecompressionStream('deflate-raw'); const stream = new Response(bytes).body.pipeThrough(ds); return new Uint8Array(await new Response(stream).arrayBuffer()); } async function unzip(ab) { const d = new Uint8Array(ab); let eocd = -1; for (let i = d.length - 22; i >= 0; i--) { if (u32(d, i) === 0x06054b50) { eocd = i; break; } } if (eocd < 0) throw new Error('not-xlsx'); const cd = u32(d, eocd + 16), total = u16(d, eocd + 10); const files = {}; let p = cd; for (let k = 0; k < total; k++) { if (u32(d, p) !== 0x02014b50) break; const method = u16(d, p + 10), compSize = u32(d, p + 20); const nameLen = u16(d, p + 28), extraLen = u16(d, p + 30), commentLen = u16(d, p + 32); const lho = u32(d, p + 42); const name = new TextDecoder().decode(d.subarray(p + 46, p + 46 + nameLen)); const lNameLen = u16(d, lho + 26), lExtraLen = u16(d, lho + 28); const start = lho + 30 + lNameLen + lExtraLen; const comp = d.subarray(start, start + compSize); let content = null; if (method === 0) content = comp; else if (method === 8) content = await inflateRaw(comp); if (content) files[name] = content; p = p + 46 + nameLen + extraLen + commentLen; } return files; } const dec = (b) => new TextDecoder().decode(b); const xmlUnesc = (s) => String(s).replace(/</g, '<').replace(/>/g, '>').replace(/"/g, '"').replace(/'/g, "'").replace(/&#(\d+);/g, (m, n) => String.fromCharCode(+n)).replace(/&/g, '&'); function colToIdx(ref) { const m = String(ref).match(/^([A-Z]+)/); if (!m) return 0; let n = 0; for (const ch of m[1]) n = n * 26 + (ch.charCodeAt(0) - 64); return n - 1; } async function parseXlsx(ab) { const files = await unzip(ab); // shared strings const shared = []; if (files['xl/sharedStrings.xml']) { const xml = dec(files['xl/sharedStrings.xml']); (xml.match(/[\s\S]*?<\/si>/g) || []).forEach(si => { const txt = (si.match(/]*>([\s\S]*?)<\/t>/g) || []).map(t => xmlUnesc(t.replace(/<[^>]+>/g, ''))).join(''); shared.push(txt); }); } // first worksheet by workbook order; fall back to sheet1 let sheetPath = 'xl/worksheets/sheet1.xml'; const sheetKeys = Object.keys(files).filter(k => /^xl\/worksheets\/sheet\d+\.xml$/.test(k)).sort(); if (sheetKeys.length) sheetPath = sheetKeys[0]; if (!files[sheetPath]) throw new Error('no-sheet'); const sx = dec(files[sheetPath]); const rowsXml = sx.match(/]*>[\s\S]*?<\/row>/g) || []; const grid = []; rowsXml.forEach(rx => { const cells = rx.match(/]*\/>|]*>[\s\S]*?<\/c>/g) || []; const arr = []; cells.forEach(cx => { const rAttr = (cx.match(/r="([A-Z]+\d+)"/) || [])[1]; const idx = rAttr ? colToIdx(rAttr) : arr.length; const tAttr = (cx.match(/\st="([^"]+)"/) || [])[1]; let val = ''; if (tAttr === 'inlineStr') { val = xmlUnesc(((cx.match(/]*>([\s\S]*?)<\/t>/) || [])[1]) || ''); } else { const v = (cx.match(/]*>([\s\S]*?)<\/v>/) || [])[1]; if (v != null) { val = (tAttr === 's') ? (shared[+v] || '') : xmlUnesc(v); } } arr[idx] = val; }); for (let i = 0; i < arr.length; i++) if (arr[i] == null) arr[i] = ''; grid.push(arr); }); return grid.filter(r => r.some(c => String(c).trim() !== '')); } /* ---------- value coercion ---------- */ function toISO(raw, fmt) { if (raw == null) return null; let s = String(raw).trim(); if (!s) return null; if (/^\d+(\.\d+)?$/.test(s)) { const v = parseFloat(s); if (v >= 30000 && v <= 60000) { const ms = Date.UTC(1899, 11, 30) + Math.round(v) * 86400000; return new Date(ms).toISOString().slice(0, 10); } } let m = s.match(/^(\d{4})[-/.](\d{1,2})[-/.](\d{1,2})/); if (m) { const mo = +m[2], da = +m[3]; if (mo >= 1 && mo <= 12 && da >= 1 && da <= 31) return `${m[1]}-${pad(mo)}-${pad(da)}`; } m = s.match(/^(\d{1,2})[-/.](\d{1,2})[-/.](\d{2,4})/); if (m) { let a = +m[1], b = +m[2], y = +m[3]; if (y < 100) y += 2000; let mo, da; if (fmt === 'MDY') { mo = a; da = b; } else if (fmt === 'DMY') { da = a; mo = b; } else { if (a > 12) { da = a; mo = b; } else if (b > 12) { mo = a; da = b; } else { da = a; mo = b; } } if (mo < 1 || mo > 12 || da < 1 || da > 31) return null; return `${y}-${pad(mo)}-${pad(da)}`; } const d = new Date(s); if (!isNaN(d)) return d.toISOString().slice(0, 10); return null; } const toNum = (raw) => { if (raw == null) return null; const s = String(raw).replace(/[$,\s]/g, '').replace(/[^\d.\-]/g, ''); if (s === '' || s === '-' || s === '.') return null; const n = parseFloat(s); return isNaN(n) ? null : n; }; const toTime = (raw) => { const s = String(raw || '').trim(); const m = s.match(/(\d{1,2}):(\d{2})/); return m ? `${pad(m[1])}:${m[2]}` : ''; }; function matchType(raw) { const v = norm(raw); if (!v) return null; const exact = TYPES.find(t => norm(t) === v); if (exact) return exact; if (/(remit|transfer|wire|send)/.test(v)) return /(receive|inbound|incoming)/.test(v) ? 'Remittance — Receive' : 'Remittance — Send'; if (/(cheque|check)/.test(v)) return 'Cheque Cashing'; if (/(moneyorder|mo\b|draft)/.test(v)) return 'Money Order'; if (/(bill|utility|payment)/.test(v)) return 'Bill Payment'; if (/(exchange|fx|buy|sell|convert|currency)/.test(v)) return 'Currency Exchange'; return null; } /* ---------- auto-map headers ---------- */ function autoMap(headers) { const map = {}; const used = new Set(); const nheaders = headers.map(norm); TARGETS.forEach(t => { const syns = SYN[t.key] || []; for (let i = 0; i < nheaders.length; i++) { if (used.has(i)) continue; if (nheaders[i] && (syns.includes(nheaders[i]) || syns.some(sy => nheaders[i] === sy))) { map[t.key] = i; used.add(i); return; } } // looser contains pass for (let i = 0; i < nheaders.length; i++) { if (used.has(i)) continue; if (nheaders[i] && syns.some(sy => nheaders[i].includes(sy) || sy.includes(nheaders[i]))) { map[t.key] = i; used.add(i); return; } } }); return map; } /* ---------- build a preview row ---------- */ function buildRow(cells, map, cfg, existingRefs) { const g = (k) => { const i = map[k]; return (i == null || i < 0) ? '' : (cells[i] == null ? '' : String(cells[i]).trim()); }; const errors = [], warns = []; const date = toISO(g('date'), cfg.dateFormat); if (!date) errors.push('date'); const customer = g('customer'); if (!customer) errors.push('customer'); const inAmt = toNum(g('inAmt')); if (inAmt == null || inAmt <= 0) errors.push('amount'); let inCcy = (g('inCcy') || cfg.defaultInCcy || 'CAD').toUpperCase().slice(0, 3); if (!/^[A-Z]{3}$/.test(inCcy)) { inCcy = (cfg.defaultInCcy || 'CAD'); warns.push('pay-in ccy'); } else if (!CCY.includes(inCcy)) warns.push('ccy ' + inCcy); let type = matchType(g('type')); if (!type) { type = cfg.defaultType || 'Currency Exchange'; if (g('type')) warns.push('type→' + type.split(' ')[0]); } const flat = (type === 'Cheque Cashing' || type === 'Money Order' || type === 'Bill Payment'); let outCcy = (g('outCcy') || '').toUpperCase().slice(0, 3); if (!/^[A-Z]{3}$/.test(outCcy)) outCcy = flat ? inCcy : (inCcy === 'CAD' ? 'USD' : 'CAD'); let rate = toNum(g('rate')); let outAmt = toNum(g('outAmt')); if (inAmt != null) { if (outAmt == null && rate != null) outAmt = +(inAmt * rate).toFixed(2); else if (outAmt == null) { const cr = inCcy === outCcy ? 1 : crossRate(inCcy, outCcy); outAmt = +(inAmt * (cr || 1)).toFixed(2); } if (rate == null) rate = inAmt ? +(outAmt / inAmt).toFixed(6) : (inCcy === outCcy ? 1 : crossRate(inCcy, outCcy)); } const fee = toNum(g('fee')); const ref = g('ref'); if (ref && existingRefs.has(ref)) warns.push('dup ref'); return { ok: errors.length === 0, errors, warns, tx: { date: date || '', time: toTime(g('time')), customer, type, beneficiary: g('beneficiary'), inCcy, inAmt: inAmt == null ? '' : inAmt, outCcy, outAmt: outAmt == null ? '' : outAmt, rate: rate == null ? '' : rate, fee: fee == null ? '' : fee, notes: g('notes'), teller: g('teller'), ref }, }; } /* ---------- small UI atoms ---------- */ function Seg({ value, onChange, options }) { return (
{options.map(o => )}
); } function Toggle({ on, onClick }) { return (); } /* ---------- main ---------- */ function LedgerImport({ rows, setRows, clients, setClients, settings, me, log, onOpenLedger }) { const isOwner = me && me.role === 'Owner'; const [cfg] = useState(loadCfg); const [step, setStep] = useState('upload'); // upload | map | review | done const [fileName, setFileName] = useState(''); const [grid, setGrid] = useState(null); // string[][] const [hasHeader, setHasHeader] = useState(true); const [map, setMap] = useState({}); const [parsing, setParsing] = useState(false); const [parseErr, setParseErr] = useState(''); const [drag, setDrag] = useState(false); const [result, setResult] = useState(null); const fileRef = useRef(null); const existingRefs = useMemo(() => new Set(rows.map(r => r.ref).filter(Boolean)), [rows]); const headers = useMemo(() => { if (!grid || !grid.length) return []; return hasHeader ? grid[0].map(h => String(h || '').trim()) : grid[0].map((_, i) => 'Column ' + (i + 1)); }, [grid, hasHeader]); const dataRows = useMemo(() => { if (!grid) return []; return hasHeader ? grid.slice(1) : grid; }, [grid, hasHeader]); const preview = useMemo(() => dataRows.map(c => buildRow(c, map, cfg, existingRefs)), [dataRows, map, cfg, existingRefs]); const validRows = preview.filter(p => p.ok); const dupCount = preview.filter(p => p.ok && p.warns.includes('dup ref')).length; async function handleFile(file) { if (!file) return; setParsing(true); setParseErr(''); setFileName(file.name); setResult(null); try { let g; if (/\.xlsx$/i.test(file.name)) { g = await parseXlsx(await file.arrayBuffer()); } else { const text = await file.text(); g = parseDelimited(text, sniffDelim(text)); } if (!g || !g.length) throw new Error('empty'); setGrid(g); const hdr = g[0].map(h => String(h || '').trim()); // header heuristic: first row mostly non-numeric labels const looksHeader = hdr.filter(h => h && isNaN(parseFloat(h))).length >= Math.ceil(hdr.length / 2); setHasHeader(looksHeader); setMap(autoMap(looksHeader ? hdr : hdr.map((_, i) => ''))); setStep('map'); } catch (e) { setParseErr(/\.xlsx$/i.test(file.name) ? "Couldn't read this Excel file. Try re-saving it as CSV (File ▸ Save As ▸ CSV) and uploading that." : "Couldn't parse this file. Make sure it's a CSV, TSV, or Excel export."); setStep('upload'); } finally { setParsing(false); } } const onPick = (e) => { const f = e.target.files && e.target.files[0]; handleFile(f); e.target.value = ''; }; const onDrop = (e) => { e.preventDefault(); setDrag(false); const f = e.dataTransfer.files && e.dataTransfer.files[0]; handleFile(f); }; const reset = () => { setStep('upload'); setGrid(null); setMap({}); setFileName(''); setParseErr(''); setResult(null); }; function doImport() { const seqByDate = {}; rows.forEach(r => { if (r.date) seqByDate[r.date] = Math.max(seqByDate[r.date] || 0, parseInt(String(r.ref).slice(-3), 10) || 0); }); const skipDup = cfg.skipDuplicateRefs; const newRows = []; const newClients = {}; let skipped = 0; validRows.forEach(p => { const t = p.tx; if (skipDup && t.ref && existingRefs.has(t.ref)) { skipped++; return; } let ref = t.ref; if (!ref) { const seq = (seqByDate[t.date] || 0) + 1; seqByDate[t.date] = seq; ref = mkRef(t.date, seq); } const stamp = `${t.date} ${t.time || '00:00'}`; newRows.push(newTx({ ref, date: t.date, time: t.time || '00:00', customer: t.customer, beneficiary: t.beneficiary || '', type: t.type, inCcy: t.inCcy, inAmt: t.inAmt, rate: t.rate === '' ? 1 : t.rate, outCcy: t.outCcy, outAmt: t.outAmt, fee: t.fee === '' ? '' : t.fee, teller: t.teller || me.name, notes: t.notes || '', thread: t.notes ? [{ ts: stamp, user: me.name, text: t.notes }] : [], createdBy: me.name, createdAt: `imported ${new Date().toLocaleString('en-CA', { hour12: false }).replace(',', '')}`, importedFrom: fileName, })); if (cfg.autoCreateClients && t.customer && !clients[t.customer] && !newClients[t.customer]) { newClients[t.customer] = { name: t.customer, kind: 'individual', createdVia: 'import', note: `Added on import from ${fileName}` }; } }); if (Object.keys(newClients).length) setClients(c => Object.assign({}, c, newClients)); if (newRows.length) setRows(rs => [...newRows, ...rs]); log && log('Ledger import', `${newRows.length} record${newRows.length === 1 ? '' : 's'} from ${fileName}${skipped ? ` · ${skipped} duplicate${skipped === 1 ? '' : 's'} skipped` : ''}`); setResult({ imported: newRows.length, clients: Object.keys(newClients).length, skipped }); setStep('done'); } function downloadTemplate() { const head = 'Date,Time,Customer,Type,Beneficiary,Pay-in amount,Pay-in currency,Pay-out amount,Pay-out currency,Rate,Fee,Reference,Notes'; const ex = [ '2026-06-18,09:41,Jakob Miller,Currency Exchange,,2400,CAD,1752,USD,0.73,18,,Walk-in buy USD', '2026-06-18,10:12,Rachel Carter,Remittance — Send,M. Carter · Cebu,600,CAD,24300,PHP,40.5,9.99,,Cebu pickup', '2026-06-17,11:05,Jakob Miller,Cheque Cashing,,1850,CAD,1813,CAD,1,37,,Payroll cheque', ]; const blob = new Blob([head + '\n' + ex.join('\n')], { type: 'text/csv' }); const a = document.createElement('a'); a.href = URL.createObjectURL(blob); a.download = 'ledger-import-template.csv'; a.click(); setTimeout(() => URL.revokeObjectURL(a.href), 1000); } if (!isOwner && !(me && me.role === 'Manager')) { return (
Ledger import is limited to owner and manager accounts.
); } const Head = (
Ledger Import
Bring a CSV or Excel export into the house ledger
{step !== 'upload' && }
); return (
{Head}
{step === 'upload' && (
{ e.preventDefault(); setDrag(true); }} onDragLeave={() => setDrag(false)} onDrop={onDrop} className="grid place-items-center text-center px-6 py-14" style={{ border: `2px dashed ${drag ? CD.ink : CD.line}`, borderRadius: 16, background: drag ? CD.lineSoft : CD.panel, transition: 'background .15s, border-color .15s' }}>
{parsing ? 'Reading file…' : 'Drop a CSV or Excel file here'}
We'll read the columns and map them to Date, Customer, Type, amounts and more.
{parseErr &&
{parseErr}
}
Recognised columns: Date, Time, Customer, Type, Beneficiary, Pay-in amount & currency, Pay-out amount & currency, Rate, Fee, Reference, Notes, Teller. Missing pieces (rate, pay-out) are computed at the live mid where possible.
)} {step === 'map' && grid && (
Match the columns
{fileName} · {dataRows.length} row{dataRows.length === 1 ? '' : 's'} · {headers.length} columns
{TARGETS.map(t => { const sel = map[t.key]; const missingReq = t.req && (sel == null || sel < 0); return (
{t.label}{t.req && *}
); })}
{validRows.length} of {dataRows.length} rows ready{preview.length - validRows.length ? ` · ${preview.length - validRows.length} need attention` : ''}
)} {step === 'review' && (
{[['Ready to import', validRows.length, CD.green], ['Need attention', preview.length - validRows.length, preview.length - validRows.length ? CD.flag : CD.mute], ['Duplicates', dupCount, dupCount ? CD.amber : CD.mute], ['Total rows', preview.length, CD.ink]].map(([l, v, c], i) => (
{l}
{v}
))}
{['', 'Date', 'Customer', 'Type', 'Pay-in', 'Pay-out', 'Fee', 'Notes'].map((h, i) => )} {preview.slice(0, 200).map((p, i) => { const t = p.tx; return (); })}
{h}
{p.ok ? : } {t.date || '— no date —'}{t.time ? ' ' + t.time : ''} {t.customer || '— no name —'} {t.type}{p.warns.some(w => w.startsWith('type')) && ·} {t.inAmt === '' ? '—' : `${num(t.inAmt)} ${t.inCcy}`} {t.outAmt === '' ? '—' : `${num(t.outAmt)} ${t.outCcy}`} {t.fee === '' ? '—' : fmt(t.fee, 'CAD')} {p.warns.includes('dup ref') ? duplicate ref : (t.notes || '')}
{preview.length > 200 &&
Showing first 200 of {preview.length} rows — all valid rows import.
}
Rows with a red flag are skipped. {cfg.autoCreateClients ? 'Unknown customers are added as new client records.' : 'Unknown customers are left unmatched.'} Imported deals run through the same compliance checks as anything booked at the counter.
)} {step === 'done' && result && (
Imported {result.imported} record{result.imported === 1 ? '' : 's'}
from {fileName}{result.clients ? ` · ${result.clients} new client${result.clients === 1 ? '' : 's'} created` : ''}{result.skipped ? ` · ${result.skipped} duplicate${result.skipped === 1 ? '' : 's'} skipped` : ''}.
)}
); } window.CDOS = Object.assign(window.CDOS || {}, { LedgerImport, importCfg: { load: loadCfg, save: saveCfgPatch, DEF: DEF_CFG } }); })();