/* ===== admin-archive.jsx — Arhiiv: reading RP's old profile workbooks ======== Christopher, 03.09: "just making sure we are not importing any of the old projects yet, just want to enable doing it in the future and have a place for it." So this READS and nothing else. It parses an archived "NN Geoloogia_profiil.xlsx", shows what an import would create, and writes nothing to the database. The write is the one remaining step, deliberately not built - an import button sitting here would be exactly the thing he hedged against. WHAT THE ARCHIVE ACTUALLY HOLDS, from surveying 1403 projects back to 2005: 2005-2009 no geology spreadsheet at the project's top level 2010-2013 the old BINARY .xls - a different reader entirely (BIFF, not a zip), so out of reach of xlsx-reader.js 2014-2022 this format, 465 projects 2023-2026 this format, but one folder down in 02_WORK And the profiles DO carry coordinates, which I had assumed they did not: row 28 holds "X=6534919 / Y=612940" per borehole, L-EST97 in metres, X north and Y east in the Estonian surveying convention. So a profile import can place a hole on the map without touching the DWG. THREE THINGS THE REAL FILES DO THAT THE APP'S OWN EXPORTS DO NOT: 1. THE SHEET NAME LIES. GL15001's second sheet is called "PA 11-13" and holds PA 7 to PA 12. Only the header row can be trusted. 2. THE IDS ARE UNEVENLY SPACED - "PA - 1", "PA - 11", "PA -12" - so the parser normalises whitespace before reading the number. 3. THERE IS A TRAILING "Kontroll" COLUMN of OK/- markers, which today's export drops. It has to be recognised so it is not read as a borehole. --------------------------------------------------------------------------- */ /* "PA - 1" / "PA - 11" / "PA -12" / "KP3" -> "PA1" / "PA11" / "PA12" / "KP3". Returns "" for anything that is not a borehole heading, which is how the Kontroll column and the empty corner cells are rejected. */ function archBhId(raw) { const t = String(raw == null ? "" : raw).replace(/\s+/g, " ").trim(); if (!t) return ""; const m = t.match(/^(PA|KP|LP|TP)\s*-?\s*(\d+)\s*(?:-\s*(\d+))?$/i); if (!m) return ""; return m[1].toUpperCase() + m[2] + (m[3] ? "-" + m[3] : ""); } /* "X=6534919\nY=612940" -> { north: 6534919, east: 612940 }. X is the NORTHING in Estonian surveying, which is the opposite of a CAD drawing's X - the same trap as the DXF export, so the names here say which is which rather than carrying X and Y through. */ function archCoords(raw) { const t = String(raw == null ? "" : raw); const x = t.match(/X\s*=\s*([\d\s.,]+)/i); const y = t.match(/Y\s*=\s*([\d\s.,]+)/i); const num = (s) => { const n = parseFloat(String(s).replace(/\s/g, "").replace(",", ".")); return isFinite(n) ? n : null; }; const north = x ? num(x[1]) : null; const east = y ? num(y[1]) : null; /* sanity, not decoration: L-EST97 northings are ~6.4M and eastings ~0.4-0.7M, so a swapped pair is obvious and worth reporting rather than importing */ const swapped = north != null && east != null && north < 1000000 && east > 1000000; return { north: swapped ? east : north, east: swapped ? north : east, swapped: swapped }; } const ARCH_E = [370000, 740000]; const ARCH_N = [6377000, 6635000]; function archInEstonia(c) { return c.east != null && c.north != null && c.east >= ARCH_E[0] && c.east <= ARCH_E[1] && c.north >= ARCH_N[0] && c.north <= ARCH_N[1]; } /* One workbook -> what an import would create. Pure: takes the grids xlsx-reader.js returns and touches nothing else. */ function parseArchiveProfile(wb, fileName) { const out = { file: fileName, code: (String(fileName).match(/GL[\s_]?\d+[-\d]*/i) || [""])[0].replace(/[\s_]/g, ""), sheets: [], boreholes: [], warnings: [], }; (wb.sheetNames || []).forEach((name) => { const rows = wb.sheet(name) || []; /* FIND THE HEADER by content, not by row number: it is row 27 in the files seen so far, but a row number is exactly the kind of assumption that breaks on the next workbook. The header is the row with the most borehole-shaped cells. */ let hdr = -1, best = 0; rows.forEach((r, i) => { const n = (r || []).filter((c) => archBhId(c)).length; if (n > best) { best = n; hdr = i; } }); if (hdr === -1 || best === 0) { out.warnings.push(name + ": ei leidnud puuraukude päist"); out.sheets.push({ name: name, boreholes: 0, rows: rows.length }); return; } const cols = []; (rows[hdr] || []).forEach((c, ci) => { const id = archBhId(c); if (id) cols.push({ ci: ci, id: id }); }); /* coordinates: the row under the header, when it carries X=/Y= */ const coordRow = rows[hdr + 1] || []; const hasCoords = cols.some((c) => /X\s*=/i.test(String(coordRow[c.ci] || ""))); /* the water row, by its label in the name column - it also carries the field date, which is the only date in the workbook */ let waterRow = -1, waterDate = ""; for (let i = hdr + 1; i < rows.length; i++) { const lbl = String((rows[i] || [])[1] || ""); if (/veetase/i.test(lbl)) { waterRow = i; const d = lbl.match(/(\d{1,2}\.\d{1,2}\.\d{4})/); waterDate = d ? d[1] : ""; break; } } /* material rows: between the coordinates and the water row, a row counts when it has a name in column B. The trailing Kontroll column is excluded already, because archBhId rejected its heading. */ const matFrom = hasCoords ? hdr + 2 : hdr + 1; const matTo = waterRow === -1 ? rows.length : waterRow; const mats = []; for (let i = matFrom; i < matTo; i++) { const nm = String((rows[i] || [])[1] || "").replace(/\s+/g, " ").trim(); if (nm) mats.push({ ri: i, name: nm }); } /* A SHEET WITH NO MATERIAL ROWS IS NOT A PROFILE. GL23001 carries a "PP_abi" helper tab whose one borehole-shaped cell made it report a borehole with zero layers - a phantom hole in the preview. Found by running the parser over real archived files rather than over this app's own exports. */ if (!mats.length) { out.warnings.push(name + ": abileht, kihte ei ole — vahele jäetud"); out.sheets.push({ name: name, boreholes: 0, materials: 0, skipped: true }); return; } cols.forEach((c) => { const layers = []; mats.forEach((m) => { const v = parseFloat(String((rows[m.ri] || [])[c.ci] || "").replace(",", ".")); if (isFinite(v) && v > 0) layers.push({ name: m.name, cm: v }); }); const coords = hasCoords ? archCoords(coordRow[c.ci]) : { north: null, east: null }; const w = waterRow === -1 ? null : parseFloat(String((rows[waterRow] || [])[c.ci] || "").replace(",", ".")); if (coords.swapped) out.warnings.push(c.id + ": X ja Y olid vahetuses, parandatud"); if ((coords.east != null || coords.north != null) && !archInEstonia(coords)) { out.warnings.push(c.id + ": koordinaadid väljaspool L-EST97 ala"); } out.boreholes.push({ sheet: name, id: c.id, layers: layers, depthCm: layers.reduce((n, l) => n + l.cm, 0), east: coords.east, north: coords.north, water: isFinite(w) ? w : null, waterDate: waterDate, }); }); out.sheets.push({ name: name, boreholes: cols.length, materials: mats.length, coords: hasCoords, waterRow: waterRow !== -1, waterDate: waterDate, }); }); /* the sheet names are not to be trusted - GL15001's "PA 11-13" holds PA 7-12 - so say so rather than letting someone read the tabs and believe them */ const dupes = {}; out.boreholes.forEach((b) => { dupes[b.id] = (dupes[b.id] || 0) + 1; }); Object.keys(dupes).filter((k) => dupes[k] > 1).forEach((k) => { out.warnings.push(k + ": esineb " + dupes[k] + " korda"); }); return out; } Object.assign(window, { archBhId, archCoords, parseArchiveProfile }); /* --------------------------------------------------------------------------- THE PAGE. Read-only on purpose: it answers "what could be imported" without importing anything, which is what was asked for. --------------------------------------------------------------------------- */ function ArchiveScreen({ onToast }) { const [files, setFiles] = React.useState([]); /* parsed results */ const [busy, setBusy] = React.useState(false); const [open, setOpen] = React.useState(""); /* which file is expanded */ const pick = () => { const inp = document.createElement("input"); inp.type = "file"; inp.accept = ".xlsx"; inp.multiple = true; inp.onchange = async () => { const list = [...(inp.files || [])]; if (!list.length) return; setBusy(true); const done = []; for (const f of list) { try { const wb = await XlsxRead.read(f); done.push(parseArchiveProfile(wb, f.name)); } catch (e) { done.push({ file: f.name, code: "", sheets: [], boreholes: [], warnings: ["ei õnnestunud lugeda: " + (e && e.message ? e.message : e)], failed: true, }); } } setFiles((p) => done.concat(p)); setBusy(false); const holes = done.reduce((n, d) => n + d.boreholes.length, 0); onToast && onToast("Loetud " + done.length + " faili · " + holes + " puurauku"); }; inp.click(); }; const totals = files.reduce((a, d) => ({ holes: a.holes + d.boreholes.length, coords: a.coords + d.boreholes.filter((b) => b.east != null).length, layers: a.layers + d.boreholes.reduce((n, b) => n + b.layers.length, 0), warn: a.warn + d.warnings.length, }), { holes: 0, coords: 0, layers: 0, warn: 0 }); return (

Arhiiv

Vanade geoloogia profiilide lugemine · midagi ei salvestata

{[ ["2005–2009", "geoloogia tabelit ei ole", "none"], ["2010–2013", "vana binaarne .xls — vajab teist lugejat", "old"], ["2014–2022", "see formaat · 465 projekti", "ok"], ["2023–2026", "see formaat, kaustas 02_WORK", "ok"], ].map(([era, what, cls]) => (
{era} {what}
))}

Profiilifail sisaldab puuraukude numbrid, kihid paksustega, veetaseme koos välitöö kuupäevaga ja koordinaadid (rida X=/Y=, L-EST97). Puurimise kuupäevi, proove ega laboriandmeid seal ei ole.
Import ise on veel tegemata — see leht loeb faili ja näitab, mis imporditaks. Andmebaasi ei kirjutata midagi.

{files.length > 0 && (
{files.map((d, i) => (
{open === d.file + i && (
{d.warnings.length > 0 && (
    {d.warnings.map((w, k) =>
  • {w}
    • )}
)} {d.boreholes.map((b, k) => ( ))}
NrLehtKihteSügavus N (põhi)E (ida)Veetase
{b.id} {b.sheet} {b.layers.length} {(b.depthCm / 100).toFixed(2)} m {b.north == null ? "—" : b.north.toFixed(0)} {b.east == null ? "—" : b.east.toFixed(0)} {b.water == null ? "—" : b.water.toFixed(2) + " m"} {b.waterDate ? ({b.waterDate}) : null}
)}
))}
)}
); } Object.assign(window, { ArchiveScreen });