/** * ============================================================================ * LMG / NCAM CENSUS TRANSLATION ENGINE * Version is set in ENGINE_VERSION below and stamped onto the SUMMARY and * PROCESSING LOG tabs when it runs — compare it to the version on the hub page * (lmg-ncam-engine.netlify.app) to confirm you're on the latest during testing. * Implements CENSUS-RULES.md (the canonical, language-agnostic spec). * If you change behavior here, update CENSUS-RULES.md first and bump its version. * ============================================================================ * Paste this whole file into Excel → Automate tab → New Script, then Run. * It rebuilds the open workbook into the LaunchMyGroup (LMG) upload format and * a derived NCAM quoting sheet, with full validation + audit logging. * * It reads your census from the sheet named "RAW DATA" if one exists, * otherwise from whatever sheet is active when you press Run. The original * data is always snapshotted to "RAW DATA" and never modified, so the script * is safe to re-run as many times as you like. * * OUTPUT SHEETS (created/overwritten on every run): * 1. LMG <- enrollment upload file (ALWAYS worksheet #1) * 2. NCAM <- employees-only quoting sheet * 3. SUMMARY <- counts by relationship and by plan/tier * 4. VALIDATION ISSUES <- every ERROR / WARNING / INFO found * 5. PROCESSING LOG <- audit trail of every transformation * 6. RAW DATA <- untouched copy of your original census * * DESIGN NOTE ON DUPLICATES: the spec called for silently merging duplicate * rows. This engine FLAGS duplicates in VALIDATION ISSUES instead of deleting * them, so an enrollee is never silently dropped from a file you upload. You * review the flags and decide. (Family-unit dedup/merge is a future version.) * ============================================================================ */ function main(workbook: ExcelScript.Workbook) { const OUTPUT_SHEETS = ["LMG", "NCAM", "SUMMARY", "VALIDATION ISSUES", "PROCESSING LOG", "RAW DATA", "Other Columns", "HEADER MAP"]; // ---- Audit collectors ------------------------------------------------- const log: string[] = []; const issues: string[][] = []; // SEVERITY, ROW, MEMBER, COLUMN, VALUE, ISSUE, ACTION function addLog(msg: string) { log.push(msg); } function addIssue(sev: string, row: number, member: string, col: string, val: string, issue: string, action: string) { issues.push([sev, row ? String(row) : "", member, col, val, issue, action]); } // ---- Version stamp (compare this to the version shown on the hub page) ---- const ENGINE_VERSION = "2.3.0"; const ENGINE_BUILD = "2026-07-06"; addLog(`LMG / NCAM Census Engine v${ENGINE_VERSION} (build ${ENGINE_BUILD}).`); // ====================================================================== // 1. LOCATE SOURCE DATA // ====================================================================== let sourceSheet: ExcelScript.Worksheet; const existingRaw = workbook.getWorksheet("RAW DATA"); if (existingRaw) sourceSheet = existingRaw; else sourceSheet = workbook.getActiveWorksheet(); if (OUTPUT_SHEETS.indexOf(sourceSheet.getName()) !== -1 && sourceSheet.getName() !== "RAW DATA") { throw new Error("Please select the worksheet that contains your census data, then run again. " + "(The active sheet is a generated output sheet and there is no RAW DATA sheet to read from.)"); } const usedRange = sourceSheet.getUsedRange(); if (!usedRange) throw new Error("The source worksheet is empty. Nothing to process."); const raw = usedRange.getValues(); if (raw.length < 2) throw new Error("The source worksheet has no data rows beneath the header row."); const rawHeaders = raw[0].map(h => String(h == null ? "" : h)); // Convert all cells to strings up front so the whole script is string[][] // (avoids union-type generics that the Office Scripts compiler rejects). // Excel date serials survive as numeric strings; formatDate() still parses them. const rawData: string[][] = raw.slice(1).map(r => r.map(v => v == null ? "" : String(v))); // (PRE-PASS runs below, after the alias map is built, so it knows which columns // we keep — rate/premium columns we don't keep must not drive the reshape logic.) // ====================================================================== // 2. LMG FIELD DEFINITIONS + HEADER ALIAS MAP // ====================================================================== // Canonical LMG output columns — EXACT names and order from the LMG platform. const LMG_FIELDS: string[] = [ "Relationship", "Social Security Number", "Status Field", "Member Last Name", "First Name", "Middle Initial", "Gender", "Date of Birth", "Date of Hire", "Member Street Address", "City", "State", "Zip Code", "Phone Number", "Email Address", "Working Location", "Billing Division", "Disabled?", "Salary", "Hours Worked per week", "Occupation", "Dental Plan Selection", "Dental Coverage Type", "DHMO Provider Name", "Dental Prior Carrier Name", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho", "Vision Plan Selection", "Vision Coverage Type", "Basic Life Election", "Primary Life Beneficiary", "Dependent Basic Life", "Employee Voluntary Life", "Spousal Voluntary Life", "Dependent Voluntary Life", "STD", "STD Class", "LTD", "LTD Class" ]; // Alias variants -> LMG field. Keys are normalized (see normHeader()). const ALIASES: { [field: string]: string[] } = { "Relationship": ["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"], "Status Field": ["STATUS", "EMPLOYEE STATUS", "EMPLOYMENT STATUS", "COBRA STATUS", "EMP STATUS"], "Social Security Number": ["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SOC SEC NO", "SS", "MEMBER SSN", "TAX ID", "EMPLOYEE SOCIAL SECURITY NUMBER", "EMPLOYEE SSN"], "Member Last Name": ["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"], "First Name": ["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"], "Middle Initial": ["MIDDLE INITIAL", "MI", "MIDDLE", "MIDDLE NAME", "MIDDLE INTIAL"], "Gender": ["GENDER", "SEX", "BIRTH SEX"], "Date of Birth": ["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"], "Date of Hire": ["DATE OF HIRE", "DOH", "HIRE DATE", "ORIGINAL HIRE DATE", "EMPLOYMENT DATE"], "Member Street Address": ["MEMBER STREET ADDRESS", "STREET ADDRESS", "ADDRESS", "ADDRESS 1", "ADDR1", "ADDR", "HOME ADDRESS", "MAILING ADDRESS"], "City": ["CITY", "TOWN", "MEMBER CITY"], "State": ["STATE", "ST", "PROVINCE", "MEMBER STATE", "STATE ABBREVIATION", "STATE ABBR", "ST ABBREVIATION", "ST ABBR", "STATE CODE", "USPS", "USPS STATE", "MEMBER STATE ABBREVIATION"], "Zip Code": ["ZIP", "ZIP CODE", "POSTAL CODE", "ZIP 4", "MEMBER ZIP", "MEMBER ZIP CODE"], "Phone Number": ["PHONE", "PHONE NUMBER", "HOME PHONE", "PERSONAL PHONE", "MOBILE PHONE", "CELL PHONE", "CELL", "MOBILE", "WORK PHONE", "OFFICE PHONE", "BUSINESS PHONE", "TELEPHONE", "CONTACT NUMBER"], "Email Address": ["EMAIL", "EMAIL ADDRESS", "E MAIL", "PERSONAL EMAIL", "HOME EMAIL", "WORK EMAIL", "OFFICE EMAIL", "BUSINESS EMAIL"], "Salary": ["ANNUAL SALARY", "SALARY", "SALARY AMOUNT", "ANNUAL INCOME", "INCOME", "COMPENSATION", "ANNUAL COMPENSATION", "BENEFIT SALARY", "COMP", "W2 WAGES", "BASE SALARY"], "Compensation Type": ["COMPENSATION TYPE", "SALARY TYPE", "PAY TYPE", "ANNUAL OR HOURLY", "ANNUAL HOURLY", "HOURLY OR ANNUAL", "PAY FREQUENCY"], "Hours Worked per week": ["HOURS WORKED PER WEEK", "HOURS WORKED", "HOURS", "SCHEDULED HOURS", "WEEKLY HOURS", "HRS"], "Occupation": ["OCCUPATION", "JOB TITLE", "POSITION", "JOB", "JOB CLASS"], "Disabled?": ["DISABLED", "DISABILITY", "IS DISABLED", "DISABLED DEPENDENT"], "Dental Plan Selection": ["DENTAL PLAN ELECTED", "DENTAL PLAN ELECTION", "DENTAL PLAN SELECTION", "DENTAL PLAN SELECT", "DENTAL PLAN NAME", "DENTAL PLAN", "DENTAL SELECT", "DENTAL ELECT", "DENTAL ELECTION"], "Dental Coverage Type": ["DENTAL COVERAGE TIER", "DENTAL COVERAGE TYPE", "DENTAL COVERAGE LEVEL", "DENTAL COVERAGE", "DENTAL COV", "DENTAL COV TIER", "DENTAL COV TYPE", "DENTAL COV LEVEL", "DENTAL TIER", "DENTAL LEVEL"], "DHMO Provider Name": ["DHMO PROVIDER NAME", "DHMO PROVIDER", "DENTAL PCP", "DENTAL OFFICE", "DENTAL FACILITY", "DHMO"], "Dental Prior Carrier Name": ["PRIOR CARRIER", "PRIOR CARRIER NAME", "CURRENT CARRIER", "PREVIOUS CARRIER", "DENTAL PRIOR CARRIER NAME", "PRIOR CURRENT CARRIER NAME"], "Dental Prior Carrier Effective Date": ["PRIOR CARRIER EFFECTIVE DATE", "PRIOR CARRIER EFF DATE", "PRIOR EFFECTIVE DATE", "PRIOR COVERAGE START"], "Dental Prior Carrier Term Date": ["PRIOR CARRIER TERM DATE", "PRIOR CARRIER TERMINATION DATE", "PRIOR TERMINATION DATE", "PRIOR COVERAGE END"], "Dental Prior Carrier Ortho": ["ORTHODONTIA INCLUDED", "WAS ORTHODONTIA INCLUDED", "ORTHODONTIA", "ORTHO", "PRIOR ORTHO", "ORTHO COVERAGE"], "Vision Plan Selection": ["VISION PLAN ELECTED", "VISION PLAN ELECTION", "VISION PLAN SELECTION", "VISION PLAN SELECT", "VISION PLAN NAME", "VISION PLAN", "VISION SELECT", "VISION ELECT", "VISION ELECTION"], "Vision Coverage Type": ["VISION COVERAGE TIER", "VISION COVERAGE TYPE", "VISION COVERAGE LEVEL", "VISION COVERAGE", "VISION COV", "VISION COV TIER", "VISION COV TYPE", "VISION COV LEVEL", "VISION TIER", "VISION LEVEL"], "Basic Life Election": ["BASIC LIFE ELECTION", "BASIC LIFE", "BASIC LIFE COVERAGE", "BASIC LIFE COVERAGE TYPE", "BASIC LIFE COV LEVEL", "LIFE ELECTION", "GROUP LIFE", "EMPLOYER PAID LIFE", "BASIC LIFE ENROLL", "BASIC LIFE CLASS", "BASIC LIFE COVERAGGE TYPE"], "Primary Life Beneficiary": ["PRIMARY BENEFICIARY", "BENEFICIARY", "PRIMARY LIFE BENEFICIARY", "BENE"], "Dependent Basic Life": ["DEPENDENT BASIC LIFE", "DEPENDENT BASIC LIFE COVERAGE LEVEL", "DEP BASIC LIFE COVERAGE LEVEL", "DEPENDENT LIFE", "DEP BASIC LIFE", "SPOUSE CHILD LIFE"], "Employee Voluntary Life": ["EMPLOYEE VOLUNTARY LIFE", "EMPLOYEE VOLUNTARY LIFE AMOUNT", "EMPLOYEE VOLUNTARY LIFE FLAT AMOUNT", "EE VOL LIFE", "EMPLOYEE VOL LIFE", "VOLUNTARY LIFE", "VOLUNTARY LIFE FLAT AMOUNT", "VOL LIFE", "SUPPLEMENTAL LIFE", "EMPLOYEE VOLUNTARY LIFE ADD", "VOLUNTARY LIFE ADD", "EE VOL LIFE AMT"], "Spousal Voluntary Life": ["SPOUSAL VOLUNTARY LIFE", "SPOUSAL VOLUNTARY LIFE AMOUNT", "SPOUSAL VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOLUNTARY LIFE", "SPOUSE VOLUNTARY LIFE AMOUNT", "SPOUSE VOLUNTARY LIFE FLAT AMOUNT", "SPOUSE VOL LIFE", "VOLUNTARY SPOUSE LIFE", "SP VOL LIFE", "SPOUSE VOLUNTARY LIFE ADD", "SPOUSAL VOLUNTARY LIFE ADD"], "Dependent Voluntary Life": ["DEPENDENT VOLUNTARY LIFE", "DEPENDENT VOLUNTARY LIFE AMOUNT", "DEPENDENT VOLUNTARY LIFE FLAT AMOUNT", "DEPENDENT VOL LIFE", "CHILD VOL LIFE", "VOLUNTARY CHILD LIFE", "DEP VOL LIFE", "DEPENDENT CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE", "CHILD VOLUNTARY LIFE ADD", "DEPENDENT VOLUNTARY LIFE ADD"], "STD": ["STD ELECTION", "STD", "SHORT TERM DISABILITY", "STD ENROLL", "STD COVERAGE TYPE"], "STD Class": ["STD CLASS", "SHORT TERM DISABILITY CLASS"], "LTD": ["LTD ELECTION", "LTD", "LONG TERM DISABILITY", "LTD ENROLL", "LTD COVERAGE TYPE"], "LTD Class": ["LTD CLASS", "LONG TERM DISABILITY CLASS"], "Working Location": ["WORKING LOCATION", "WORK LOCATION", "LOCATION", "LOCATION NUMBER", "LOCATION CODE", "BRANCH", "SOLD QUOTE LOCATION"], "Billing Division": ["BILLING DIVISION", "DIVISION", "BILL DIVISION", "BILLING DIV"] }; // Build normalized alias -> field lookup. const aliasToField = new Map(); for (const field of Object.keys(ALIASES)) { for (const a of ALIASES[field]) aliasToField.set(a, field); } // State lookups — declared BEFORE the pre-pass call because the §11c // combined-column split (Stage S in preprocess) validates state tokens. const STATES: { [k: string]: string } = { "ALABAMA": "AL", "ALASKA": "AK", "ARIZONA": "AZ", "ARKANSAS": "AR", "CALIFORNIA": "CA", "COLORADO": "CO", "CONNECTICUT": "CT", "DELAWARE": "DE", "DISTRICT OF COLUMBIA": "DC", "FLORIDA": "FL", "GEORGIA": "GA", "HAWAII": "HI", "IDAHO": "ID", "ILLINOIS": "IL", "INDIANA": "IN", "IOWA": "IA", "KANSAS": "KS", "KENTUCKY": "KY", "LOUISIANA": "LA", "MAINE": "ME", "MARYLAND": "MD", "MASSACHUSETTS": "MA", "MICHIGAN": "MI", "MINNESOTA": "MN", "MISSISSIPPI": "MS", "MISSOURI": "MO", "MONTANA": "MT", "NEBRASKA": "NE", "NEVADA": "NV", "NEW HAMPSHIRE": "NH", "NEW JERSEY": "NJ", "NEW MEXICO": "NM", "NEW YORK": "NY", "NORTH CAROLINA": "NC", "NORTH DAKOTA": "ND", "OHIO": "OH", "OKLAHOMA": "OK", "OREGON": "OR", "PENNSYLVANIA": "PA", "RHODE ISLAND": "RI", "SOUTH CAROLINA": "SC", "SOUTH DAKOTA": "SD", "TENNESSEE": "TN", "TEXAS": "TX", "UTAH": "UT", "VERMONT": "VT", "VIRGINIA": "VA", "WASHINGTON": "WA", "WEST VIRGINIA": "WV", "WISCONSIN": "WI", "WYOMING": "WY", "PUERTO RICO": "PR", "GUAM": "GU", "VIRGIN ISLANDS": "VI", "AMERICAN SAMOA": "AS" }; const VALID_USPS = new Set(Object.keys(STATES).map(k => STATES[k])); // PRE-PASS: reshape stacked product rows / dependent columns into one clean // enrollee row per person. aliasToField lets the collapse ignore columns we // don't keep (Total Rate, premiums, etc.) when checking for conflicts. const shaped = preprocess(rawHeaders, rawData, aliasToField); const sourceHeaders = shaped.headers; const dataRows = shaped.rows; addLog(`Read ${rawData.length} source row(s), ${rawHeaders.length} column(s) from "${sourceSheet.getName()}"; ${dataRows.length} enrollee row(s) after reshape.`); // ---- Fuzzy matching + HEADER MAP setup ---- const fieldByNorm = new Map(); // normalized field/alias name -> canonical field for (const f of LMG_FIELDS) fieldByNorm.set(normHeader(f), f); for (const f of Object.keys(ALIASES)) fieldByNorm.set(normHeader(f), f); const fuzzyCandidates: { field: string, text: string }[] = []; for (const f of Object.keys(ALIASES)) { fuzzyCandidates.push({ field: f, text: normHeader(f) }); for (const a of ALIASES[f]) fuzzyCandidates.push({ field: f, text: a }); } const AUTO_THRESHOLD = 0.85; // >= this similarity -> auto-apply (with a verify flag) const SUGGEST_THRESHOLD = 0.50; // >= this -> offer as a suggestion on HEADER MAP // Read user-confirmed mappings from a prior run's HEADER MAP sheet (they win). const userMap = new Map(); // normalized source header -> LMG field const userSrc = new Map(); // normalized source header -> original text const hmSheet = workbook.getWorksheet("HEADER MAP"); const hmRange = hmSheet ? hmSheet.getUsedRange() : undefined; if (hmSheet && hmRange) { const hv = hmRange.getValues(); if (hv.length >= 2) { const hh = hv[0].map(x => normHeader(String(x == null ? "" : x))); const srcIdx = hh.indexOf("SOURCE HEADER"); let mapIdx = hh.indexOf("MAP TO LMG FIELD"); if (mapIdx < 0) mapIdx = hh.indexOf("MAP TO"); if (srcIdx >= 0 && mapIdx >= 0) { for (let r = 1; r < hv.length; r++) { const srcH = String(hv[r][srcIdx] == null ? "" : hv[r][srcIdx]).trim(); const toH = String(hv[r][mapIdx] == null ? "" : hv[r][mapIdx]).trim(); if (!srcH || !toH) continue; const fld = fieldByNorm.get(normHeader(toH)); if (fld) { userMap.set(normHeader(srcH), fld); userSrc.set(normHeader(srcH), srcH); } } } } } if (userMap.size) addLog(`HEADER MAP: applied ${userMap.size} user-confirmed column mapping(s).`); // Map each LMG field to a source column index (first exact match wins). const fieldToCol = new Map(); const addressCols: number[] = []; const unmapped: string[] = []; const dupCols: string[][] = []; // [sourceHeader, lmgFieldItDuplicates] // Phone / Email can have several source columns. Instead of one winning, we keep // them all with a priority rank and pick the best non-blank value PER ROW. // Phone: personal/home/generic (1) > cell/mobile (2) > work/office (3). // Email: personal/home/generic (1) > work/office (2). const phoneCols: { col: number, rank: number }[] = []; const emailCols: { col: number, rank: number }[] = []; const phoneRank = (n: string): number => /CELL|MOBILE/.test(n) ? 2 : (/WORK|OFFICE|BUSINESS/.test(n) ? 3 : 1); const emailRank = (n: string): number => /WORK|OFFICE|BUSINESS/.test(n) ? 2 : 1; // colReview drives the HEADER MAP sheet (every column that wasn't a clean exact match). const colReview: { header: string, status: string, mapTo: string, suggested: string, conf: string }[] = []; // Known Humana products that are NOT part of the LMG ancillary upload. These are // recognized and set aside (not treated as "unrecognized"), so a full enrollment // census doesn't produce a wall of warnings for Medical/Medicare/CI/etc. const nonLmgCols: string[] = []; const NON_LMG_RE = /MEDICARE|HICN|HEALTH SAVINGS|\bHSA\b|\bHRA\b|\bFSA\b|\bFSD\b|\bMEDICAL\b|CRITICAL ILLNESS|\bACCIDENT\b|SUPPLEMENTAL HEALTH|TERM LIFE|WORKPLACE|WRITING AGENT|\bSAN\b|APPLICATION SIGNATURE|SEQUENCE NUMBER|WAIVE ALL|ELIMINATION PERIOD|BENEFIT DURATION/; for (let c = 0; c < sourceHeaders.length; c++) { const norm = normHeader(sourceHeaders[c]); if (norm === "") continue; // Address columns (Address 1/2/3, Addr2, Street Address 2, ...) merge together. if (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(norm) && !/EMAIL|IP/.test(norm)) { addressCols.push(c); continue; } // Priority: user-confirmed mapping > known non-LMG product > exact alias > fuzzy. let field = userMap.get(norm) || ""; let how = field ? "user" : ""; let suggested = "", conf = ""; if (!field && NON_LMG_RE.test(norm)) { nonLmgCols.push(sourceHeaders[c]); continue; } if (!field) { const exact = aliasToField.get(norm); if (exact) { field = exact; how = "exact"; } } if (!field) { const fz = bestFuzzy(norm, fuzzyCandidates); suggested = fz.field; conf = Math.round(fz.score * 100) + "%"; if (fz.score >= AUTO_THRESHOLD) { field = fz.field; how = "auto"; } else if (fz.score >= SUGGEST_THRESHOLD) { how = "suggest"; } else { how = "none"; } } // Route the resolved field. if (field === "Phone Number") phoneCols.push({ col: c, rank: phoneRank(norm) }); else if (field === "Email Address") emailCols.push({ col: c, rank: emailRank(norm) }); else if (field && !fieldToCol.has(field)) fieldToCol.set(field, c); else if (field) { dupCols.push([sourceHeaders[c], field]); how = "dup"; } else unmapped.push(sourceHeaders[c]); if (how === "auto") { addIssue("INFO", 0, "", sourceHeaders[c], "", `Auto-matched to '${field}' by similarity (${conf})`, "Verify on the HEADER MAP tab; clear its 'Map To' cell to undo"); } if (how !== "exact") { colReview.push({ header: sourceHeaders[c], status: how, mapTo: (how === "user" || how === "auto") ? field : "", suggested: how === "dup" ? field : suggested, conf: how === "user" ? "confirmed" : conf }); } } // Columns split by the pre-pass (§11c) appear on HEADER MAP with their targets. for (const sp of shaped.splits) { colReview.push({ header: sp.header, status: "split", mapTo: "", suggested: sp.into, conf: "auto" }); } // Order each group by priority, then by source column order for ties. phoneCols.sort((a, b) => a.rank - b.rank || a.col - b.col); emailCols.sort((a, b) => a.rank - b.rank || a.col - b.col); const coalesce = (cols: { col: number, rank: number }[], src: string[]): string => { for (const cc of cols) { const v = src[cc.col]; if (v != null && String(v).trim() !== "") return String(v).trim(); } return ""; }; if (phoneCols.length > 1) addLog(`Phone: coalescing ${phoneCols.length} columns per row by priority (personal > cell > work).`); if (emailCols.length > 1) addLog(`Email: coalescing ${emailCols.length} columns per row by priority (personal > work).`); if (addressCols.length) addLog(`Address merge: combining ${addressCols.length} source address column(s).`); if (unmapped.length) addLog(`Unmapped source columns kept only in RAW DATA: ${unmapped.join(", ")}.`); if (dupCols.length) addLog(`Duplicate/unused source columns (first match used): ${dupCols.map(d => d[0]).join(", ")}.`); if (nonLmgCols.length) addLog(`Non-LMG product columns set aside (${nonLmgCols.length}): ${nonLmgCols.join(", ")}.`); // Non-LMG product columns: recognized as a real Humana field but not part of the // LMG ancillary upload — INFO (not a warning), expected on full enrollment files. for (const x of nonLmgCols) { addIssue("INFO", 0, "", x, "", "Non-LMG product column (Medical / Medicare / Critical Illness / Accident / etc.)", "Not part of the LMG ancillary upload — kept in RAW DATA only. Expected on full enrollment censuses."); } // Flag unrecognized / duplicate source columns so nothing leaves the LMG upload // silently — the user sees them on VALIDATION ISSUES and Other Columns; the raw // values stay on RAW DATA. for (const u of unmapped) { addIssue("WARNING", 0, "", u, "", "Unrecognized column — not mapped to any LMG field", "Open the HEADER MAP tab, put the right LMG field in its 'Map To' cell, and re-run to include it. Data stays on RAW DATA meanwhile."); } for (const d of dupCols) { addIssue("WARNING", 0, "", d[0], "", `Duplicate source for '${d[1]}' — an earlier column already filled it`, "Only the first column was used. Data preserved on RAW DATA only; merge manually if needed."); } for (const f of LMG_FIELDS) { if (!fieldToCol.has(f) && !(f === "Member Street Address" && addressCols.length)) { // not present in source; will be blank in output (logged once, quietly) } } addLog("Header normalization complete (parenthetical text removed, aliases standardized)."); // Does this group even carry each product? Controls blank-default behavior. const hasDental = fieldToCol.has("Dental Plan Selection") || fieldToCol.has("Dental Coverage Type"); const hasVision = fieldToCol.has("Vision Plan Selection") || fieldToCol.has("Vision Coverage Type"); const hasSTD = fieldToCol.has("STD") || fieldToCol.has("STD Class"); const hasLTD = fieldToCol.has("LTD") || fieldToCol.has("LTD Class"); // ====================================================================== // 3. STATIC LOOKUP TABLES // ====================================================================== // (STATES / VALID_USPS are declared above the pre-pass call — the §11c // combined-column split needs them before this point.) const OCC_ABBR: [RegExp, string][] = [ [/\bSENIOR\b/g, "SR"], [/\bJUNIOR\b/g, "JR"], [/\bINFORMATION TECHNOLOGY\b/g, "IT"], [/\bSYSTEMS?\b/g, "SYS"], [/\bADMINISTRATORS?\b/g, "ADMIN"], [/\bADMINISTRATION\b/g, "ADMIN"], [/\bSUPPORT\b/g, "SUP"], [/\bTECHNICIANS?\b/g, "TECH"], [/\bREPRESENTATIVES?\b/g, "REP"], [/\bBUSINESS\b/g, "BIZ"], [/\bOPERATIONS?\b/g, "OPS"], [/\bMANAGERS?\b/g, "MGR"], [/\bMANAGEMENT\b/g, "MGMT"], [/\bASSISTANTS?\b/g, "ASST"], [/\bDIRECTORS?\b/g, "DIR"], [/\bENGINEERS?\b/g, "ENGR"], [/\bSUPERVISORS?\b/g, "SUPV"], [/\bDEVELOPERS?\b/g, "DEV"], [/\bCOORDINATORS?\b/g, "COORD"], [/\bSPECIALISTS?\b/g, "SPEC"], [/\bGENERAL\b/g, "GEN"] ]; const WAIVE_WORDS = new Set(["W", "WAIVE", "WAIVED", "WAIVES", "DECLINE", "DECLINED", "NONE", "N A", "NA", "WAIVE COVERAGE", "OPT OUT", "NO COVERAGE"]); const LMG_WAIVE_TEXT = "Waive with other coverage"; // ====================================================================== // 4. PER-ROW TRANSFORMATION -> LMG TABLE // ====================================================================== const lmgRows: string[][] = []; const dupKeyCount = new Map(); for (let r = 0; r < dataRows.length; r++) { const src = dataRows[r]; const excelRow = r + 2; // human-facing source row number (header is row 1) const get = (field: string): string => { const idx = fieldToCol.get(field); if (idx == null) return ""; const v = src[idx]; return v == null ? "" : String(v).trim(); }; // --- Names (needed for issue messages) --- const lastName = get("Member Last Name").toUpperCase(); const firstName = get("First Name").toUpperCase(); let middle = get("Middle Initial").toUpperCase(); if (middle.length > 1) middle = middle.charAt(0); const memberName = (firstName + " " + lastName).trim() || `(row ${excelRow})`; if (!lastName) addIssue("ERROR", excelRow, memberName, "Member Last Name", "", "Missing last name", "Add the member's last name"); if (!firstName) addIssue("ERROR", excelRow, memberName, "First Name", "", "Missing first name", "Add the member's first name"); // --- Relationship --- const relRaw = get("Relationship"); const relationship = normalizeRelationship(relRaw); if (relRaw && relationship === "") { addIssue("WARNING", excelRow, memberName, "Relationship", relRaw, "Unrecognized relationship value", "Confirm: Employee / Spouse / Domestic Partner / Child"); } if (!relRaw) addIssue("WARNING", excelRow, memberName, "Relationship", "", "Missing relationship", "Set Employee / Spouse / Domestic Partner / Child"); const relOut = relationship || relRaw; const isEmployee = relationship === "Employee"; // --- Gender --- const genderRaw = get("Gender"); const gender = normalizeGender(genderRaw); if (gender === "") { addIssue(genderRaw ? "WARNING" : "ERROR", excelRow, memberName, "Gender", genderRaw, genderRaw ? "Unrecognized gender" : "Missing gender", "Set M or F"); } // --- Dates --- const dob = formatDate(get("Date of Birth")); if (get("Date of Birth") && dob === "") addIssue("ERROR", excelRow, memberName, "Date of Birth", get("Date of Birth"), "Unparseable date of birth", "Enter as MM/DD/YYYY"); if (!get("Date of Birth")) addIssue("ERROR", excelRow, memberName, "Date of Birth", "", "Missing date of birth", "Enter as MM/DD/YYYY"); const age = ageFromDob(dob); if (age >= 65) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (65 or older)`, "Review — age may affect eligibility/rating"); const doh = isEmployee ? formatDate(get("Date of Hire")) : ""; // hire date blanked on dependents if (isEmployee && get("Date of Hire") && doh === "") addIssue("WARNING", excelRow, memberName, "Date of Hire", get("Date of Hire"), "Unparseable hire date", "Enter as MM/DD/YYYY"); const priorEff = formatDate(get("Dental Prior Carrier Effective Date")); const priorTerm = formatDate(get("Dental Prior Carrier Term Date")); // --- SSN --- // SSN is kept on the EMPLOYEE only — removed from spouse / domestic partner / child. let ssn = ""; if (isEmployee) { ssn = get("Social Security Number").replace(/\D/g, ""); if (ssn && ssn.length < 9) { // Often an 8-digit SSN simply lost a leading zero — pad to 9 and flag for a spot-check. const padded = ("000000000" + ssn).slice(-9); addIssue("INFO", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN had ${ssn.length} digits; padded leading zero(s) to ${padded}`, "Spot-check that the padded SSN is correct"); ssn = padded; } else if (ssn.length > 9) { addIssue("ERROR", excelRow, memberName, "Social Security Number", get("Social Security Number"), `SSN has ${ssn.length} digits (more than 9)`, "Correct to a 9-digit SSN"); } if (!ssn) addIssue("ERROR", excelRow, memberName, "Social Security Number", "", "Employee missing SSN", "Add 9-digit SSN"); } // --- Address (merge) --- let street = ""; if (addressCols.length) { const parts: string[] = []; for (const c of addressCols) { const v = src[c]; if (v != null && String(v).trim() !== "") parts.push(String(v).trim()); } street = parts.join(" ").replace(/\s+/g, " ").toUpperCase(); } else { street = get("Member Street Address").toUpperCase(); } // --- City / State / Zip --- const city = get("City").toUpperCase(); let state = get("State").toUpperCase().trim(); if (state.length > 2) { const abbr = STATES[state]; if (abbr) state = abbr; else addIssue("WARNING", excelRow, memberName, "State", get("State"), "Unknown state name", "Use 2-letter USPS code"); } else if (state.length === 2 && !VALID_USPS.has(state)) { addIssue("WARNING", excelRow, memberName, "State", get("State"), "Invalid 2-letter state", "Use a valid USPS code"); } else if (isEmployee && state === "") { addIssue("ERROR", excelRow, memberName, "State", "", "Employee missing state", "Add the 2-letter USPS state"); } let zip = get("Zip Code").replace(/\D/g, ""); if (zip.length > 5) zip = zip.substring(0, 5); if (zip && zip.length !== 5 && zip.length !== 0) { // pad short zips that lost a leading zero if (zip.length < 5) { zip = ("00000" + zip).slice(-5); } } // --- Phone (coalesce multiple columns per row by priority) --- const phoneRaw = coalesce(phoneCols, src); let phone = phoneRaw.replace(/\D/g, ""); if (phone.length === 11 && phone.charAt(0) === "1") phone = phone.substring(1); if (phone && phone.length !== 10) { addIssue("WARNING", excelRow, memberName, "Phone Number", phoneRaw, `Phone has ${phone.length} digits (need 10)`, "Correct to a 10-digit phone"); phone = ""; // do not export an invalid phone } // --- Email (coalesce multiple columns per row by priority) --- const email = coalesce(emailCols, src); // --- Hours / Salary --- // Use the member's actual Hours Worked Per Week. Default to 40 ONLY when // that field is blank/invalid (e.g. 20 hrs -> rate x 20 x 52, not x 40). const hoursRaw = get("Hours Worked per week").replace(/[^\d.]/g, ""); const hoursParsed = parseFloat(hoursRaw); const hoursProvided = !isNaN(hoursParsed) && hoursParsed > 0; const hoursNum = hoursProvided ? hoursParsed : 40; // 40 only as a fallback let hours = hoursProvided ? hoursRaw : (isEmployee ? "40" : ""); const salRaw = get("Salary"); let salary = ""; if (salRaw) { // Prefer an explicit Compensation Type (Hourly/Annual); else detect "/hr" text. const compType = normHeader(get("Compensation Type")); const compHourly = compType === "HOURLY" || compType === "HOUR" || compType === "H" || compType === "HRLY"; const compAnnual = compType === "ANNUAL" || compType === "SALARY" || compType === "SALARIED" || compType === "A" || compType === "YEARLY"; const isHourly = compHourly || (!compAnnual && compType === "" && /hr|hour/i.test(salRaw)); const num = parseFloat(salRaw.replace(/[^\d.]/g, "")); if (!isNaN(num)) { if (isHourly) { salary = String(Math.round(num * hoursNum * 52 * 100) / 100); addLog(`Hourly->annual: ${salRaw} x ${hoursNum}h x 52 = ${salary} (${memberName}).`); if (!hoursProvided) { addIssue("INFO", excelRow, memberName, "Hours Worked per week", "", "Hourly wage annualized using the default 40 hrs/week (no hours given)", "Enter the actual weekly hours if it is not 40"); } } else { salary = String(num); if (num > 0 && num < 1000) { addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary looks like an hourly rate", "Confirm annual salary, or label the cell '/hr'"); } } } else { addIssue("WARNING", excelRow, memberName, "Salary", salRaw, "Salary not numeric", "Enter a numeric salary"); } } // --- Occupation --- let occ = get("Occupation").toUpperCase().replace(/\s+/g, " ").trim(); if (occ) { for (const [re, rep] of OCC_ABBR) occ = occ.replace(re, rep); occ = occ.replace(/\s+/g, " ").trim(); if (occ.length > 25) occ = occ.replace(/(?!^)[AEIOU]/g, ""); // drop vowels except leading if (occ.length > 25) { addIssue("WARNING", excelRow, memberName, "Occupation", get("Occupation"), "Occupation exceeds 25 chars after abbreviation", "Shorten the job title"); occ = occ.substring(0, 25); } } // --- Disabled --- let disabled = normalizeYesNo(get("Disabled?")); if (disabled === "") disabled = "No"; // --- Coverage tiers --- const dentalTierRaw = get("Dental Coverage Type"); let dentalTier = normalizeTier(dentalTierRaw); if (dentalTierRaw && dentalTier === "") addIssue("WARNING", excelRow, memberName, "Dental Coverage Type", dentalTierRaw, "Unrecognized coverage tier", "Use EE / ES / EC / FAM / W"); const visionTierRaw = get("Vision Coverage Type"); let visionTier = normalizeTier(visionTierRaw); if (visionTierRaw && visionTier === "") addIssue("WARNING", excelRow, memberName, "Vision Coverage Type", visionTierRaw, "Unrecognized coverage tier", "Use EE / ES / EC / FAM / W"); // LMG output: W -> "Waive with other coverage"; blank employee -> waive (only if product exists) let dentalTierOut = tierToLmg(dentalTier, dentalTierRaw, isEmployee, hasDental, LMG_WAIVE_TEXT); let visionTierOut = tierToLmg(visionTier, visionTierRaw, isEmployee, hasVision, LMG_WAIVE_TEXT); // --- Voluntary life (employee rows). Employee/Spousal waived -> 0; // Dependent waived -> "Waive". --- const eeVol = volLife(get("Employee Voluntary Life"), isEmployee, "0", false); const spVol = volLife(get("Spousal Voluntary Life"), isEmployee, "0", false); const chVol = volLife(get("Dependent Voluntary Life"), isEmployee, "Waive", true); // 0 or blank -> Waive // Flag voluntary-life amounts that are not in even thousands (e.g. 62,500). const oddThousand = (v: string, label: string) => { const n = parseFloat(v); if (!isNaN(n) && n > 0 && n % 1000 !== 0) addIssue("WARNING", excelRow, memberName, label, v, "Voluntary life amount is not in even thousands", "Verify the volume amount"); }; oddThousand(eeVol, "Employee Voluntary Life"); oddThousand(spVol, "Spousal Voluntary Life"); oddThousand(chVol, "Dependent Voluntary Life"); if (!isEmployee && (WAIVE_WORDS.has(normHeader(get("Spousal Voluntary Life"))) === false) && get("Spousal Voluntary Life")) { addIssue("INFO", excelRow, memberName, "Spousal Voluntary Life", get("Spousal Voluntary Life"), "Spousal vol life on a dependent row", "Will roll up to employee in a future version; verify manually"); } // --- Basic life + beneficiary --- const basicLife = normalizeEnrollWaive(get("Basic Life Election")); let beneficiary = get("Primary Life Beneficiary"); const present = (x: string): boolean => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x)); const realVol = (x: string): boolean => x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x)); // Primary Beneficiary defaults to ON FILE when blank and the employee has Basic // and/or Voluntary Life. const hasLife = present(basicLife) || realVol(eeVol) || realVol(spVol) || realVol(chVol); if (isEmployee && hasLife && !beneficiary) beneficiary = "ON FILE"; // --- Assemble LMG record (must match LMG_FIELDS order) --- const rec: { [k: string]: string } = { "Relationship": relOut, "Status Field": get("Status Field"), "Social Security Number": ssn, "Member Last Name": lastName, "First Name": firstName, "Middle Initial": middle, "Gender": gender || genderRaw, "Date of Birth": dob, "Date of Hire": doh, "Member Street Address": street, "City": city, "State": state, "Zip Code": zip, "Phone Number": phone, "Email Address": email, "Working Location": get("Working Location"), "Billing Division": get("Billing Division"), "Salary": salary, "Hours Worked per week": hours, "Occupation": occ, "Disabled?": disabled, "Dental Plan Selection": get("Dental Plan Selection"), "Dental Coverage Type": dentalTierOut, "DHMO Provider Name": get("DHMO Provider Name"), "Dental Prior Carrier Name": get("Dental Prior Carrier Name"), "Dental Prior Carrier Effective Date": priorEff, "Dental Prior Carrier Term Date": priorTerm, "Dental Prior Carrier Ortho": normalizeYesNo(get("Dental Prior Carrier Ortho")), "Vision Plan Selection": get("Vision Plan Selection"), "Vision Coverage Type": visionTierOut, "Basic Life Election": basicLife, "Primary Life Beneficiary": beneficiary, "Dependent Basic Life": normalizeEnrollWaive(get("Dependent Basic Life")), "Employee Voluntary Life": eeVol, "Spousal Voluntary Life": spVol, "Dependent Voluntary Life": chVol, "STD": normalizeEnrollWaive(get("STD")), "STD Class": get("STD Class"), "LTD": normalizeEnrollWaive(get("LTD")), "LTD Class": get("LTD Class") }; lmgRows.push(LMG_FIELDS.map(f => rec[f] == null ? "" : rec[f])); // --- Duplicate detection (flag, never silently merge) --- const key = `${lastName}|${firstName}|${dob}|${gender}`; dupKeyCount.set(key, (dupKeyCount.get(key) || 0) + 1); if (dupKeyCount.get(key)! > 1) { addIssue("WARNING", excelRow, memberName, "(row)", key, "Possible duplicate (same name + DOB + gender)", "Review; remove the duplicate enrollee if confirmed"); } } addLog("State conversions complete."); addLog("Phone cleanup complete."); addLog("Date normalization complete."); addLog("Coverage normalization complete."); addLog(`LMG build complete: ${lmgRows.length} enrollee row(s).`); // ====================================================================== // 4b. FAMILY ROLL-UP (by adjacency) // Dependents belong to the nearest Employee row above them. Roll their // elections up to the employee row and derive the employee's coverage tier // from who is enrolled. Demographics stay on the dependent rows. // ====================================================================== { const ci = (f: string): number => LMG_FIELDS.indexOf(f); const relI = ci("Relationship"); const realAmt = (x: string): boolean => x != null && x !== "" && x !== "0" && !WAIVE_WORDS.has(normHeader(x)); const isWaiveTier = (x: string): boolean => x === LMG_WAIVE_TEXT || WAIVE_WORDS.has(normHeader(x)) || normHeader(x).indexOf("WAIVE") >= 0; const nameOf = (r: string[]): string => (r[ci("First Name")] + " " + r[ci("Member Last Name")]).trim(); const tierRank: { [k: string]: number } = { EE: 1, ES: 2, EC: 2, FAM: 3 }; // Build families by adjacency: an Employee starts one; following Spouse/DP/Child rows join it. const fams: { emp: number, deps: number[] }[] = []; for (let i = 0; i < lmgRows.length; i++) { const rel = lmgRows[i][relI]; if (rel === "Employee") fams.push({ emp: i, deps: [] }); else if (fams.length && (rel === "Spouse" || rel === "Domestic Partner" || rel === "Child")) fams[fams.length - 1].deps.push(i); } const censusListsDependents = fams.some(f => f.deps.length > 0); let rolled = 0; for (const fam of fams) { const emp = lmgRows[fam.emp]; const empName = nameOf(emp) || "(employee)"; for (const di of fam.deps) { const dep = lmgRows[di]; const rel = dep[relI]; const isSp = rel === "Spouse" || rel === "Domestic Partner"; const isCh = rel === "Child"; // The dependent's own voluntary-life amount (first real among the vol-life columns). const depVol = [dep[ci("Employee Voluntary Life")], dep[ci("Spousal Voluntary Life")], dep[ci("Dependent Voluntary Life")]].filter(x => realAmt(x))[0] || ""; if (isSp && depVol && !realAmt(emp[ci("Spousal Voluntary Life")])) { emp[ci("Spousal Voluntary Life")] = depVol; rolled++; addIssue("INFO", 0, empName, "Spousal Voluntary Life", depVol, `Rolled up spouse ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount"); } if (isCh && depVol && !realAmt(emp[ci("Dependent Voluntary Life")])) { emp[ci("Dependent Voluntary Life")] = depVol; rolled++; addIssue("INFO", 0, empName, "Dependent Voluntary Life", depVol, `Rolled up child ${nameOf(dep)}'s voluntary life to the employee row`, "Verify the amount"); } // Dependent Basic Life election. const depBasic = [dep[ci("Dependent Basic Life")], dep[ci("Basic Life Election")]].filter(x => x.trim() !== "" && !WAIVE_WORDS.has(normHeader(x)))[0] || ""; if (depBasic && (emp[ci("Dependent Basic Life")].trim() === "" || WAIVE_WORDS.has(normHeader(emp[ci("Dependent Basic Life")])))) { emp[ci("Dependent Basic Life")] = normalizeEnrollWaive(depBasic); rolled++; } // Vol-life elections live on the EE line in LMG, not the dependent rows — blank them. dep[ci("Employee Voluntary Life")] = ""; dep[ci("Spousal Voluntary Life")] = ""; dep[ci("Dependent Voluntary Life")] = ""; } // Derive each product's tier from who is actually ENROLLED in THAT product. // A dependent who WAIVED a product does not count toward its tier. Upgrade-only: // never lower a stated tier, and never raise it for a waived/absent dependent. for (const tf of ["Dental Coverage Type", "Vision Coverage Type"]) { const idx = ci(tf); const cur = emp[idx]; if (cur === "" || isWaiveTier(cur)) continue; // employee not enrolled in this product let spEnr = false, chEnr = false; for (const di of fam.deps) { const dv = lmgRows[di][idx]; if (dv.trim() === "" || isWaiveTier(dv)) continue; // dependent waived / not on this product const rel = lmgRows[di][relI]; if (rel === "Spouse" || rel === "Domestic Partner") spEnr = true; else if (rel === "Child") chEnr = true; } const famTier = spEnr && chEnr ? "FAM" : spEnr ? "ES" : chEnr ? "EC" : "EE"; if ((tierRank[famTier] || 0) > (tierRank[cur] || 0)) { emp[idx] = famTier; rolled++; addIssue("INFO", 0, empName, tf, `${cur} -> ${famTier}`, "Coverage tier raised — dependent(s) enrolled in this product", "Verify the tier"); } } // ---- Family consistency flags (do not auto-fix — flag for review) ---- let spouseRow = false, childRow = false; for (const di of fam.deps) { const r = lmgRows[di][relI]; if (r === "Spouse" || r === "Domestic Partner") spouseRow = true; else if (r === "Child") childRow = true; } const tiers = [emp[ci("Dental Coverage Type")], emp[ci("Vision Coverage Type")]]; const wantsSpouse = tiers.indexOf("ES") >= 0 || tiers.indexOf("FAM") >= 0; const wantsChild = tiers.indexOf("EC") >= 0 || tiers.indexOf("FAM") >= 0; // A dependent is listed but no coverage tier includes them. if (spouseRow && !wantsSpouse) addIssue("WARNING", 0, empName, "Coverage / Dependents", "", "Spouse is listed but not included in any coverage tier", "Review — confirm whether the spouse should be covered"); if (childRow && !wantsChild) addIssue("WARNING", 0, empName, "Coverage / Dependents", "", "Child(ren) listed but not included in any coverage tier", "Review — confirm whether the child(ren) should be covered"); // The tier implies a dependent that isn't listed (only checked when the census lists dependents as rows). if (censusListsDependents) { if (wantsSpouse && !spouseRow) addIssue("WARNING", 0, empName, "Coverage / Dependents", "", "Coverage tier includes a spouse, but no spouse is listed", "Review — add the spouse or correct the tier"); if (wantsChild && !childRow) addIssue("WARNING", 0, empName, "Coverage / Dependents", "", "Coverage tier includes children, but none are listed", "Review — add the child(ren) or correct the tier"); } } if (rolled > 0) addLog(`Family roll-up: rolled ${rolled} dependent election(s)/tier(s) up to employee rows (by adjacency).`); } // ====================================================================== // 4c. EMPLOYEE-ONLY FIELDS // Elections, employment/comp, AND address/contact belong on the EMPLOYEE line // only. Blank them on spouse / domestic partner / child rows (which keep just // Relationship, name, gender, DOB, Disabled?). Runs AFTER the family roll-up so // dependent values are read first. // ====================================================================== { const ci = (f: string): number => LMG_FIELDS.indexOf(f); const relI = ci("Relationship"); const EMPLOYEE_ONLY = [ "Member Street Address", "City", "State", "Zip Code", "Phone Number", "Email Address", "Status Field", "Date of Hire", "Working Location", "Billing Division", "Salary", "Hours Worked per week", "Occupation", "Dental Plan Selection", "Dental Coverage Type", "DHMO Provider Name", "Dental Prior Carrier Name", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho", "Vision Plan Selection", "Vision Coverage Type", "Basic Life Election", "Primary Life Beneficiary", "Dependent Basic Life", "Employee Voluntary Life", "Spousal Voluntary Life", "Dependent Voluntary Life", "STD", "STD Class", "LTD", "LTD Class" ]; const idxs = EMPLOYEE_ONLY.map(f => ci(f)).filter(i => i >= 0); let blanked = 0; for (const r of lmgRows) { if (r[relI] === "Employee") continue; for (const i of idxs) if (r[i] !== "") { r[i] = ""; blanked++; } } if (blanked > 0) addLog(`Cleared ${blanked} employee-only field value(s) from dependent rows.`); } // ====================================================================== // 4d. SPECIAL-CHARACTER CLEANING // Replace special characters with a space (# -> " UNIT ") across the census, // EXCEPT date fields, the email column, and numeric fields (which keep decimals). // ====================================================================== { const ci = (f: string): number => LMG_FIELDS.indexOf(f); const EXEMPT = new Set([ "Date of Birth", "Date of Hire", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date", "Email Address", "Social Security Number", "Phone Number", "Zip Code", "Salary", "Hours Worked per week", "Employee Voluntary Life", "Spousal Voluntary Life", "Dependent Voluntary Life" ].map(f => ci(f)).filter(i => i >= 0)); let cleaned = 0; for (const r of lmgRows) { for (let c = 0; c < r.length; c++) { if (EXEMPT.has(c)) continue; const v = r[c]; if (v === "") continue; const nv = cleanSpecial(v); if (nv !== v) { r[c] = nv; cleaned++; } } } if (cleaned > 0) addLog(`Special-character cleanup: normalized ${cleaned} cell(s).`); } // ====================================================================== // 5. NCAM (employees only, quoting layout) // ====================================================================== const ncamBaseHeaders = ["Last Name", "Gender", "Date of Birth", "Dental Coverage Type", "Vision Coverage Type"]; const ncamStdLtdHeaders = ["Salary", "Occupation", "STD", "STD Class", "LTD", "LTD Class"]; const includeStdLtd = hasSTD || hasLTD; const ncamHeaders = includeStdLtd ? ncamBaseHeaders.concat(ncamStdLtdHeaders) : ncamBaseHeaders; const colIdx = (f: string) => LMG_FIELDS.indexOf(f); const ncamRows: string[][] = []; for (let i = 0; i < lmgRows.length; i++) { if (lmgRows[i][colIdx("Relationship")] !== "Employee") continue; const row = lmgRows[i]; // Convert "Waive with other coverage" back to W; blank employee -> W const dt = lmgToNcamTier(row[colIdx("Dental Coverage Type")], hasDental); const vt = lmgToNcamTier(row[colIdx("Vision Coverage Type")], hasVision); const base = [row[colIdx("Member Last Name")], row[colIdx("Gender")], row[colIdx("Date of Birth")], dt, vt]; const extra = includeStdLtd ? [row[colIdx("Salary")], row[colIdx("Occupation")], row[colIdx("STD")], row[colIdx("STD Class")], row[colIdx("LTD")], row[colIdx("LTD Class")]] : []; ncamRows.push(base.concat(extra)); } addLog(`NCAM build complete: ${ncamRows.length} employee row(s)${includeStdLtd ? " (STD/LTD columns included)" : ""}.`); // ====================================================================== // 6. SUMMARY // ====================================================================== const relCount: { [k: string]: number } = { "Employee": 0, "Spouse": 0, "Domestic Partner": 0, "Child": 0, "Other/Unknown": 0 }; for (const row of lmgRows) { const rel = row[colIdx("Relationship")]; if (relCount[rel] != null) relCount[rel]++; else relCount["Other/Unknown"]++; } const summary: string[][] = []; summary.push(["LMG / NCAM CENSUS SUMMARY", ""]); summary.push(["Engine version", `${ENGINE_VERSION} (build ${ENGINE_BUILD})`]); summary.push(["Check for updates", "lmg-ncam-engine.netlify.app - if the site shows a newer version, re-copy the script"]); summary.push(["", ""]); summary.push(["Enrollment counts", ""]); summary.push(["Employees", String(relCount["Employee"])]); summary.push(["Spouses", String(relCount["Spouse"])]); summary.push(["Domestic Partners", String(relCount["Domestic Partner"])]); summary.push(["Children", String(relCount["Child"])]); if (relCount["Other/Unknown"]) summary.push(["Other / Unknown relationship", String(relCount["Other/Unknown"])]); summary.push(["Total LMG rows", String(lmgRows.length)]); summary.push(["NCAM employee rows", String(ncamRows.length)]); summary.push(["", ""]); // Tier breakdown per product (counts enrollees by EE/ES/EC/FAM/W on the employee tier value) function tierBlock(label: string, present: boolean, ncamTierResolver: (raw: string) => string, lmgFieldName: string) { if (!present) return; const counts: { [k: string]: number } = { EE: 0, ES: 0, EC: 0, FAM: 0, W: 0, OTHER: 0 }; for (const row of lmgRows) { if (row[colIdx("Relationship")] !== "Employee") continue; const t = ncamTierResolver(row[colIdx(lmgFieldName)]); if (counts[t] != null) counts[t]++; else counts["OTHER"]++; } const enrolled = counts.EE + counts.ES + counts.EC + counts.FAM; summary.push([label + " (by employee tier)", ""]); summary.push([" EE", String(counts.EE)]); summary.push([" ES", String(counts.ES)]); summary.push([" EC", String(counts.EC)]); summary.push([" FAM", String(counts.FAM)]); summary.push([" Enrolled (EE+ES+EC+FAM)", String(enrolled)]); summary.push([" Waived (W)", String(counts.W)]); summary.push([" Eligible (Enrolled + Waived)", String(enrolled + counts.W)]); if (counts.OTHER) summary.push([" Unclassified", String(counts.OTHER)]); summary.push(["", ""]); } tierBlock("Dental", hasDental, (raw) => lmgToNcamTier(raw, true), "Dental Coverage Type"); tierBlock("Vision", hasVision, (raw) => lmgToNcamTier(raw, true), "Vision Coverage Type"); // Issue tally let nErr = 0, nWarn = 0, nInfo = 0; for (const it of issues) { if (it[0] === "ERROR") nErr++; else if (it[0] === "WARNING") nWarn++; else nInfo++; } summary.push(["Validation results", ""]); summary.push(["Errors", String(nErr)]); summary.push(["Warnings", String(nWarn)]); summary.push(["Info", String(nInfo)]); addLog(`Validation complete: ${nErr} error(s), ${nWarn} warning(s), ${nInfo} info.`); // ====================================================================== // 7. WRITE ALL SHEETS // ====================================================================== // Tab colors: LMG & NCAM green; every other generated tab navy. const TAB_PRIMARY = "#5C9A1B"; // LMG, NCAM const TAB_SECONDARY = "#003057"; // SUMMARY, VALIDATION ISSUES, PROCESSING LOG, RAW DATA, Other Columns // Drop entirely-blank LMG columns — blank columns break the LMG upload. Their // names are listed on the "Other Columns" tab. NCAM and SUMMARY read from the // full unfiltered records (lmgRows), so they are unaffected. // REQUIRED columns are ALWAYS kept (never dropped) — if a required column is // entirely blank it is flagged as an ERROR so the data gets filled in. const REQUIRED = new Set(["Relationship", "Member Last Name", "First Name", "Gender", "Date of Birth", "State"]); // Voluntary Life columns only appear if there are real elections — a column // that is blank or "0" for everyone is dropped (an all-waived line of coverage). const ZERO_IS_BLANK = new Set(["Employee Voluntary Life", "Spousal Voluntary Life", "Dependent Voluntary Life"]); // A Plan column with only one distinct value is left off — the system auto-selects // the single available plan. const PLAN_DROP_IF_SINGLE = new Set(["Dental Plan Selection", "Vision Plan Selection"]); const keptCols: number[] = []; const blankCols: string[] = []; const singlePlanCols: string[] = []; for (let c = 0; c < LMG_FIELDS.length; c++) { const zeroBlank = ZERO_IS_BLANK.has(LMG_FIELDS[c]); let hasData = false; for (let r = 0; r < lmgRows.length; r++) { const v = lmgRows[r][c]; if (v !== "" && v != null && !(zeroBlank && (v === "0" || WAIVE_WORDS.has(normHeader(v))))) { hasData = true; break; } } // Drop a Plan column that only ever holds one distinct value. if (hasData && PLAN_DROP_IF_SINGLE.has(LMG_FIELDS[c])) { const distinct = new Set(); for (let r = 0; r < lmgRows.length; r++) { const v = lmgRows[r][c]; if (v !== "" && v != null) distinct.add(String(v).trim().toUpperCase()); } if (distinct.size <= 1) { singlePlanCols.push(LMG_FIELDS[c]); continue; } } if (hasData || REQUIRED.has(LMG_FIELDS[c])) { keptCols.push(c); if (!hasData) addIssue("ERROR", 0, "", LMG_FIELDS[c], "", "Required column is entirely blank", "Provide this required field for every enrollee"); } else { blankCols.push(LMG_FIELDS[c]); } } if (singlePlanCols.length) addLog(`Single-plan columns left off (system auto-selects): ${singlePlanCols.join(", ")}.`); const keptCount = keptCols.length; const lmgHeaderOut = keptCols.map(i => LMG_FIELDS[i]); const lmgRowsOut = lmgRows.map(row => keptCols.map(i => row[i])); if (blankCols.length) addLog(`Removed ${blankCols.length} blank column(s) from LMG (see Other Columns): ${blankCols.join(", ")}.`); // Snapshot RAW DATA first (only if it does not already exist). if (!workbook.getWorksheet("RAW DATA")) { const rawSheet = workbook.addWorksheet("RAW DATA"); if (raw.length && raw[0].length) rawSheet.getRangeByIndexes(0, 0, raw.length, raw[0].length).setValues(raw); addLog("RAW DATA created (untouched copy of original census)."); } const rawSheetRef = workbook.getWorksheet("RAW DATA"); if (rawSheetRef) rawSheetRef.setTabColor(TAB_SECONDARY); // Recreate output sheets. const lmgSheet = writeSheet(workbook, "LMG", [lmgHeaderOut].concat(lmgRowsOut), "#1F4E79", TAB_PRIMARY); const ncamSheet = writeSheet(workbook, "NCAM", [ncamHeaders].concat(ncamRows), "#2E75B6", TAB_PRIMARY); writeSheet(workbook, "SUMMARY", summary, "#548235", TAB_SECONDARY); const issuesHeader = ["SEVERITY", "ROW NUMBER", "MEMBER NAME", "COLUMN", "VALUE", "ISSUE", "RECOMMENDED ACTION"]; const issuesGrid = issues.length ? issues : [["INFO", "", "", "", "", "No issues found", ""]]; const issuesSheet = writeSheet(workbook, "VALIDATION ISSUES", [issuesHeader].concat(issuesGrid), "#C00000", TAB_SECONDARY); writeSheet(workbook, "PROCESSING LOG", [["STEP"]].concat(log.map(l => [l])), "#7F7F7F", TAB_SECONDARY); // "Other Columns" = everything NOT in the LMG upload, each with a reason. // (Unrecognized/duplicate source columns are flagged earlier, in section 2, // so they reach the VALIDATION ISSUES sheet and the SUMMARY tally. The data // itself is always preserved on RAW DATA.) const otherRows: string[][] = []; for (const u of unmapped) otherRows.push([u, "Unrecognized source header — data kept in RAW DATA only; NOT in the LMG upload"]); for (const d of dupCols) otherRows.push([d[0], `Duplicate of '${d[1]}' (already mapped) — data kept in RAW DATA only; NOT in the LMG upload`]); for (const b of blankCols) otherRows.push([b, "LMG field blank — excluded from upload"]); for (const s of singlePlanCols) otherRows.push([s, "Single plan option — system auto-selects; column left off the upload"]); for (const x of nonLmgCols) otherRows.push([x, "Recognized non-LMG product (Medical/Medicare/Critical Illness/etc.) — not part of the LMG ancillary upload"]); const otherGrid = otherRows.length ? [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"]].concat(otherRows) : [["COLUMN", "WHY IT IS NOT IN THE LMG UPLOAD"], ["None", "Every source column mapped and every LMG column had data."]]; writeSheet(workbook, "Other Columns", otherGrid, "#AF0061", "#AF0061"); // Plum tab // HEADER MAP — the user confirms unrecognized columns here; the tool applies // them on the next run and remembers them. Fill the 'Map To' cell with an LMG // field name and re-run to include that column. const hmHeaderRow = ["SOURCE HEADER", "MAP TO LMG FIELD", "SUGGESTED", "CONFIDENCE", "STATUS", "SHARE (copy → submit on hub)"]; const hmRows: string[][] = []; const hmSeen = new Set(); const shareLine = (src: string, to: string): string => to ? `${src} | ${to}` : ""; for (const cr of colReview) { hmSeen.add(normHeader(cr.header)); let status = "No close match — type the LMG field in Map To and re-run"; if (cr.status === "user") status = "Applied — your mapping"; else if (cr.status === "auto") status = "Applied automatically — verify (clear Map To to undo)"; else if (cr.status === "suggest") status = "Suggested — put a field in Map To and re-run to include"; else if (cr.status === "dup") status = "Duplicate of another column — not used"; else if (cr.status === "split") status = "Split automatically into the fields under SUGGESTED — original kept in RAW DATA"; const suggested = cr.suggested || (cr.status === "none" ? "(no close match)" : ""); hmRows.push([cr.header, cr.mapTo, suggested, cr.conf, status, shareLine(cr.header, cr.mapTo)]); } // Preserve any prior user mappings for columns not present in this file. userMap.forEach((fld, normSrc) => { if (!hmSeen.has(normSrc)) { const s = userSrc.get(normSrc) || normSrc; hmRows.push([s, fld, "", "confirmed", "Applied — your mapping (column not in this file)", shareLine(s, fld)]); } }); const hmGrid = hmRows.length ? [hmHeaderRow].concat(hmRows) : [hmHeaderRow, ["(none)", "", "", "", "Every column mapped cleanly — nothing to confirm"]]; writeSheet(workbook, "HEADER MAP", hmGrid, "#C55A11", "#C55A11"); // orange tab // ---- Highlight validation issues by severity ---- const HL: { [k: string]: string } = { INFO: "#BDD7EE", WARNING: "#FFEB9C", ERROR: "#FFC7CE" }; const sevRank: { [k: string]: number } = { INFO: 0, WARNING: 1, ERROR: 2 }; // LMG tab: color each offending cell. Apply INFO -> WARNING -> ERROR so the // most severe color wins when a cell has more than one issue. const ordered = issues.slice().sort((a, b) => (sevRank[a[0]] || 0) - (sevRank[b[0]] || 0)); for (const it of ordered) { const rowNum = parseInt(it[1], 10); if (isNaN(rowNum)) continue; const rIdx = rowNum - 1; // header at sheet row 0; data rows occupy 1..N if (rIdx < 1 || rIdx > lmgRowsOut.length) continue; const color = HL[it[0]] || HL.INFO; if (it[3] === "(row)") { lmgSheet.getRangeByIndexes(rIdx, 0, 1, keptCount).getFormat().getFill().setColor(color); } else { const full = LMG_FIELDS.indexOf(it[3]); const fc = full >= 0 ? keptCols.indexOf(full) : -1; if (fc >= 0) lmgSheet.getRangeByIndexes(rIdx, fc, 1, 1).getFormat().getFill().setColor(color); } } // VALIDATION ISSUES tab: color each SEVERITY cell to match. if (issues.length) { for (let i = 0; i < issues.length; i++) { const color = HL[issues[i][0]]; if (color) issuesSheet.getRangeByIndexes(i + 1, 0, 1, 1).getFormat().getFill().setColor(color); } } // Tab order: LMG is #1 (LaunchMyGroup uploads the first sheet only), NCAM #2. lmgSheet.setPosition(0); ncamSheet.setPosition(1); lmgSheet.activate(); // ---------------------------------------------------------------------- // Helper: replace-or-create a sheet, write grid, style header row. // ---------------------------------------------------------------------- function writeSheet(wb: ExcelScript.Workbook, name: string, grid: string[][], headerHex: string, tabColor: string) { const existing = wb.getWorksheet(name); if (existing) existing.delete(); const ws = wb.addWorksheet(name); ws.setTabColor(tabColor); writeGrid(ws, grid); if (grid.length && grid[0].length) { const hdr = ws.getRangeByIndexes(0, 0, 1, grid[0].length); hdr.getFormat().getFill().setColor(headerHex); hdr.getFormat().getFont().setColor("#FFFFFF"); hdr.getFormat().getFont().setBold(true); const used = ws.getUsedRange(); if (used) used.getFormat().autofitColumns(); ws.getFreezePanes().freezeRows(1); } return ws; } function writeGrid(ws: ExcelScript.Worksheet, grid: string[][]) { if (!grid.length) return; const rows = grid.length; const cols = grid.reduce((m, r) => Math.max(m, r.length), 0); // pad ragged rows const padded = grid.map(r => { const a = r.slice(); while (a.length < cols) a.push(""); return a; }); ws.getRangeByIndexes(0, 0, rows, cols).setValues(padded); } // ====================================================================== // PURE HELPER FUNCTIONS // ====================================================================== function cleanSpecial(v: string): string { // "#" -> " UNIT "; every other non-alphanumeric (except space) -> space; collapse. return v.replace(/#/g, " UNIT ").replace(/[^A-Za-z0-9 ]+/g, " ").replace(/\s+/g, " ").trim(); } function ageFromDob(mmddyyyy: string): number { const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/); if (!m) return -1; const mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10); const now = new Date(); let age = now.getFullYear() - yr; const moNow = now.getMonth() + 1, daNow = now.getDate(); if (moNow < mo || (moNow === mo && daNow < da)) age--; return age; } function normHeader(h: string): string { let s = String(h == null ? "" : h); // Drop parenthetical text, innermost-out, so nested parens like "Child(ren)" // inside an instruction "(... Employee +Child(ren), Family, Waive)" clear fully. let prev = ""; while (s !== prev) { prev = s; s = s.replace(/\([^()]*\)/g, " "); } s = s.replace(/\([\s\S]*$/, " "); // drop any unmatched "(" to end of string // A leftover ")" means the parens were unbalanced — e.g. real-world header // "Status (Active, COBRA, Disabled ) If COBRA, provide Loss of Event Date // and Event Type)". Everything from the first "(" was instruction text, so // fall back to the part of the original header before that "(". if (s.indexOf(")") !== -1) { const orig = String(h == null ? "" : h); const p = orig.indexOf("("); s = p >= 0 ? orig.slice(0, p) : s.replace(/\)/g, " "); } return s.toUpperCase() .replace(/[^A-Z0-9]+/g, " ") // punctuation -> space .replace(/\s+/g, " ") .trim(); } // ---- Combined-column split parsers (rules §11c) ---- // splitCombinedName is also used by the Stage 2 dependent-name split so // both share one behavior (suffix + middle-initial rules). function splitCombinedName(v: string): { first: string, mi: string, last: string, guessed: boolean } | null { let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim(); if (s === "") return null; let suffix = ""; const sm = s.match(/[,\s]+(JR|SR|II|III|IV|V)\.?$/i); if (sm) { suffix = sm[1].toUpperCase(); s = s.slice(0, s.length - sm[0].length).trim(); } const withSfx = (last: string): string => suffix !== "" ? (last + " " + suffix).trim() : last; const stripDot = (t: string): string => t.replace(/\.$/, ""); if (s.indexOf(",") >= 0) { // "Last, First [Middle]" — the comma makes the split reliable. const cp = s.indexOf(","); const last = s.slice(0, cp).trim(); const rest = s.slice(cp + 1).replace(/,/g, " ").replace(/\s+/g, " ").trim(); if (last === "" && rest === "") return null; const t = rest === "" ? [] : rest.split(" "); return { first: t.length > 0 ? t[0] : "", mi: t.length > 1 ? stripDot(t[1]) : "", last: withSfx(last), guessed: false }; } const t = s.split(" "); if (t.length === 1) return { first: "", mi: "", last: withSfx(t[0]), guessed: true }; if (t.length === 2) return { first: t[0], mi: "", last: withSfx(t[1]), guessed: true }; if (t.length === 3 && /^[A-Za-z]\.?$/.test(t[1])) return { first: t[0], mi: stripDot(t[1]), last: withSfx(t[2]), guessed: true }; return { first: t[0], mi: "", last: withSfx(t.slice(1).join(" ")), guessed: true }; // "Mary Van Buren" style } // "Austin, TX 78701" / "Round Rock TX 78664-1234" / "San Juan, Puerto Rico 00901". // Right-to-left: trailing zip, then a valid USPS code or full state name // (longest match first), remainder = city. zipOptional covers headers like // "CITY STATE" that legitimately carry no zip. Returns null when not confident. function parseCityStateZip(v: string, zipOptional: boolean): { city: string, state: string, zip: string } | null { let s = String(v == null ? "" : v).replace(/\s+/g, " ").trim(); if (s === "") return null; let zip = ""; const zm = s.match(/[,\s]+(\d{5}(?:-\d{4})?)$/); if (zm) { zip = zm[1]; s = s.slice(0, s.length - zm[0].length); } else if (!zipOptional) return null; s = s.replace(/[\s,]+$/, ""); let state = ""; const ab = s.match(/[,\s]+([A-Za-z]{2})\.?$/); if (ab && VALID_USPS.has(ab[1].toUpperCase())) { state = ab[1].toUpperCase(); s = s.slice(0, s.length - ab[0].length); } else { const words = s.split(/[\s,]+/).filter(w => w !== ""); for (let n = Math.min(3, words.length - 1); n >= 1; n--) { const cand = words.slice(words.length - n).join(" ").toUpperCase(); if (STATES[cand]) { state = STATES[cand]; for (let k = 0; k < n; k++) s = s.replace(/[\s,]+[^\s,]+$/, ""); break; } } } if (state === "") return null; const city = s.replace(/[\s,]+$/, "").trim(); if (city === "" || /^\d+$/.test(city)) return null; return { city: city, state: state, zip: zip }; } // Full "123 Main St, Apt 4, Austin, TX 78701". Peels zip + state via // parseCityStateZip, then the last comma segment is the city and the rest is // the street. No comma boundary -> null (the caller falls back to peeling // just State+ZIP so nothing is lost). function parseFullAddress(v: string): { street: string, city: string, state: string, zip: string } | null { const p = parseCityStateZip(v, false); if (p === null || p.zip === "") return null; const cp = p.city.lastIndexOf(","); if (cp < 0) return null; const street = p.city.slice(0, cp).replace(/[\s,]+$/, "").trim(); const city = p.city.slice(cp + 1).trim(); if (street === "" || city === "") return null; return { street: street, city: city, state: p.state, zip: p.zip }; } function normalizeRelationship(v: string): string { const n = normHeader(v); if (n === "") return ""; if (["EMPLOYEE", "EE", "EMP", "SELF", "PRIMARY", "SUBSCRIBER", "E", "EMPL"].indexOf(n) !== -1) return "Employee"; if (["SPOUSE", "SP", "SPS", "WIFE", "HUSBAND", "SPOUSE PARTNER"].indexOf(n) !== -1) return "Spouse"; if (["DOMESTIC PARTNER", "DP", "PARTNER", "CIVIL UNION"].indexOf(n) !== -1) return "Domestic Partner"; if (["CHILD", "CH", "DEPENDENT", "DEP", "SON", "DAUGHTER", "CHILDREN", "STEPCHILD", "STEPSON", "STEPDAUGHTER"].indexOf(n) !== -1) return "Child"; return ""; // unknown -> caller flags } function normalizeGender(v: string): string { const n = normHeader(v); if (n === "M" || n === "MALE") return "M"; if (n === "F" || n === "FEMALE") return "F"; return ""; } function normalizeEnrollWaive(v: string): string { // Basic Life Election, Dependent Basic Life, STD, LTD all read "Enroll" / "Waive". const n = normHeader(v); if (n === "") return ""; if (WAIVE_WORDS.has(n) || n === "WAIVE") return "Waive"; if (["ENROLL", "ENROLLED", "ENROLLEE", "ELECT", "ELECTED", "YES", "Y", "ACTIVE", "E", "ENROLLING", "ADD"].indexOf(n) !== -1) return "Enroll"; return v.trim(); } function normalizeYesNo(v: string): string { const n = normHeader(v); if (n === "") return ""; if (["Y", "YES", "TRUE", "1", "X"].indexOf(n) !== -1) return "Yes"; if (["N", "NO", "FALSE", "0"].indexOf(n) !== -1) return "No"; return v.trim(); } function normalizeTier(v: string): string { const n = normHeader(v); if (n === "") return ""; if (WAIVE_WORDS.has(n)) return "W"; if (["EE", "EO", "E", "EMPLOYEE", "EMPLOYEE ONLY", "EMP ONLY", "SELF", "SUBSCRIBER ONLY"].indexOf(n) !== -1) return "EE"; if (n === "ES" || n === "EC" || n === "FAM" || n === "W") return n; const hasSpouse = /\bSPOUSE\b|\bSP\b|DOMESTIC|PARTNER/.test(n); const hasChild = /\bCHILD\b|\bCHILDREN\b|\bCH\b|DEPENDENT|\bDEP\b|\bKIDS?\b/.test(n); const hasFamily = /FAMILY|\bFAM\b/.test(n); if (hasFamily || (hasSpouse && hasChild)) return "FAM"; if (hasSpouse) return "ES"; if (hasChild) return "EC"; if (/EMPLOYEE|\bEMP\b/.test(n)) return "EE"; return ""; } function tierToLmg(tier: string, raw: string, isEmployee: boolean, productExists: boolean, waiveText: string): string { if (tier === "W") return waiveText; if (tier !== "") return tier; // blank tier if (isEmployee && productExists) return waiveText; // NCAM would default blank to EE, so force waive return raw; // keep whatever was there (likely blank) for dependents } function lmgToNcamTier(v: string, productExists: boolean): string { if (v === LMG_WAIVE_TEXT) return "W"; if (v === "" && productExists) return "W"; // NCAM forces blank employee coverage to W return v; } function volLife(v: string, isEmployee: boolean, waivedValue: string, zeroWaives: boolean): string { if (!isEmployee) return v.trim(); const n = normHeader(v); if (n === "" || WAIVE_WORDS.has(n)) return waivedValue; const num = v.replace(/[^\d.]/g, ""); if (num !== "") { if (zeroWaives && parseFloat(num) === 0) return waivedValue; // Dependent: 0 -> Waive return num; } return v.trim(); } function formatDate(v: string | number): string { if (v === "" || v == null) return ""; // Excel serial date number if (typeof v === "number" || /^\d+(\.\d+)?$/.test(String(v))) { const serial = typeof v === "number" ? v : parseFloat(String(v)); if (serial > 59 && serial < 80000) { // plausible date serial (1900..~2119) const ms = Math.round((serial - 25569) * 86400 * 1000); // 25569 = 1970-01-01 in Excel serial const d = new Date(ms); return pad2(d.getUTCMonth() + 1) + "/" + pad2(d.getUTCDate()) + "/" + d.getUTCFullYear(); } } const s = String(v).trim(); // MM/DD/YYYY or M/D/YY or with - . let m = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{2,4})$/); if (m) { let mo = parseInt(m[1], 10), da = parseInt(m[2], 10), yr = parseInt(m[3], 10); if (yr < 100) yr += yr < 30 ? 2000 : 1900; if (mo >= 1 && mo <= 12 && da >= 1 && da <= 31) return pad2(mo) + "/" + pad2(da) + "/" + yr; } // YYYY-MM-DD m = s.match(/^(\d{4})[\/\-.](\d{1,2})[\/\-.](\d{1,2})/); if (m) { const yr = parseInt(m[1], 10), mo = parseInt(m[2], 10), da = parseInt(m[3], 10); if (mo >= 1 && mo <= 12 && da >= 1 && da <= 31) return pad2(mo) + "/" + pad2(da) + "/" + yr; } return ""; // unparseable -> caller flags } function pad2(n: number): string { return n < 10 ? "0" + n : String(n); } // ---- Fuzzy header similarity (word overlap + character bigram similarity) ---- function fuzzyBigrams(s: string): string[] { const g: string[] = []; for (let i = 0; i < s.length - 1; i++) g.push(s.substr(i, 2)); return g; } function diceCoef(a: string, b: string): number { if (a === b) return 1; const A = fuzzyBigrams(a), B = fuzzyBigrams(b); if (A.length === 0 || B.length === 0) return 0; const counts = new Map(); for (const g of A) counts.set(g, (counts.get(g) || 0) + 1); let match = 0; for (const g of B) { const n = counts.get(g) || 0; if (n > 0) { counts.set(g, n - 1); match++; } } return (2 * match) / (A.length + B.length); } function tokenJaccard(a: string, b: string): number { const A = a.split(" ").filter(x => x !== ""), B = b.split(" ").filter(x => x !== ""); if (A.length === 0 || B.length === 0) return 0; const setA = new Set(A), setB = new Set(B); let inter = 0; setA.forEach(x => { if (setB.has(x)) inter++; }); const uni = new Set(A.concat(B)).size; return inter / uni; } function simScore(a: string, b: string): number { return 0.6 * tokenJaccard(a, b) + 0.4 * diceCoef(a, b); } function bestFuzzy(norm: string, candidates: { field: string, text: string }[]): { field: string, score: number } { let bestField = "", bestScore = 0; for (const cand of candidates) { const s = simScore(norm, cand.text); if (s > bestScore) { bestScore = s; bestField = cand.field; } } return { field: bestField, score: bestScore }; } function normalizeDepRel(v: string): string { const r = normalizeRelationship(v); return (r === "" || r === "Employee") ? "" : r; // dependents are Spouse / Domestic Partner / Child } // ====================================================================== // PRE-PASS: reshape "nightmare" layouts into one clean row per person. // Stage S — split combined columns (Name / City-State-Zip / full address). // Stage 0 — product-coded routing (generic value + Product column). // Stage 1 — collapse stacked product rows (gated on a Product column). // Stage 2 — explode "Dependent N " columns into dependent rows. // Transforms only when confident; flags anything out of the norm. // ====================================================================== function preprocess(headers0: string[], rows0: string[][], aliasToFieldMap: Map): { headers: string[], rows: string[][], splits: { header: string, into: string }[] } { const splits: { header: string, into: string }[] = []; if (rows0.length === 0) return { headers: headers0, rows: rows0, splits: splits }; let headers = headers0.slice(); let rows = rows0.map(r => r.slice()); // ---- STAGE S: combined-column split ("data extrapolation", rules §11c) ---- // A single column holding a full name ("Smith, John" / "Jane Doe"), a // "City, State ZIP" value, or a whole "Address, City, State ZIP" is split // into the separate LMG fields. Runs FIRST so the collapse/explode stages // below can key on the split First/Last columns. Transforms only when // confident; below-threshold columns are flagged and passed through (§10). { const snorm = headers.map(h => normHeader(h)); const nCols = snorm.length; // freeze: columns appended below are never candidates const SPLIT_CONFIRM = 0.60; // named combined header: >=60% of non-blank values parse const SPLIT_STRONG = 0.90; // value-shape-only (header is just CITY): >=90% const SPLIT_ADDR = 0.70; // full address (always value-gated): >=70% const SPLIT_HINT = 0.30; // 30%..threshold -> WARNING, no transform const depSkipRe = /^DEP(?:ENDENT)?\s*\d/; // Stage 2's territory const val = (row: string[], i: number): string => (row[i] == null) ? "" : String(row[i]).trim(); const scan = (col: number, tryParse: (v: string) => boolean): { frac: number, nonBlank: number, hits: number } => { let nonBlank = 0, hits = 0; for (const row of rows) { const v = val(row, col); if (v === "") continue; nonBlank++; if (tryParse(v)) hits++; } return { frac: nonBlank ? hits / nonBlank : 0, nonBlank: nonBlank, hits: hits }; }; // Value-shape-only triggers on tiny columns (<5 non-blank) demand a 100% parse. const confident = (r: { frac: number, nonBlank: number }, threshold: number, valueOnly: boolean): boolean => r.nonBlank > 0 && (valueOnly && r.nonBlank < 5 ? r.frac === 1 : r.frac >= threshold); const hasFieldNow = (field: string): boolean => { for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return true; return false; }; const ensureField = (field: string): number => { for (let i = 0; i < headers.length; i++) if (headers[i] !== "" && aliasToFieldMap.get(normHeader(headers[i])) === field) return i; const idx = headers.length; headers.push(field); for (const row of rows) row.push(""); return idx; }; const setIfBlank = (row: string[], i: number, v: string) => { if (v !== "" && val(row, i) === "") row[i] = v; }; // Consume the combined column BEFORE ensureField so a column whose header // itself maps to a target field (e.g. "City", "Address") gets a fresh // target column instead of pointing back at itself. const consume = (col: number, into: string, hits: number, nonBlank: number) => { splits.push({ header: headers[col], into: into }); addLog(`Combined-column split: '${headers[col]}' -> ${into} (${hits}/${nonBlank} non-blank rows parsed).`); headers[col] = ""; // consumed; raw values stay on RAW DATA }; // -- Shape 1: combined full-name column -- const NAME_HEADERS = ["NAME", "FULL NAME", "EMPLOYEE NAME", "EMP NAME", "MEMBER NAME", "ENROLLEE NAME", "SUBSCRIBER NAME", "PARTICIPANT NAME", "EMPLOYEE FULL NAME", "MEMBER FULL NAME", "NAME LAST FIRST", "NAME FIRST LAST"]; const NAME_BLOCK_RE = /FIRST|LAST|MIDDLE|CARRIER|PLAN|PROVIDER|BENEFICIARY|OFFICE|FACILITY|PCP|COMPANY|GROUP|EMPLOYER|CONTACT|AGENT|BROKER|SPONSOR|LOCATION|DOCTOR|DENTIST|PHYSICIAN|DEPENDENT|\bDEP\b|SPOUSE|CHILD|BANK|POLICY/; const isNameHeader = (n: string): boolean => NAME_HEADERS.indexOf(n) !== -1 || (n !== "" && /( |^)NAME$/.test(n) && !NAME_BLOCK_RE.test(n)); const nameParses = (v: string): boolean => { const p = splitCombinedName(v); return p !== null && p.first !== "" && /[A-Za-z]/.test(p.first) && /[A-Za-z]/.test(p.last); }; for (let c = 0; c < nCols; c++) { if (headers[c] === "" || snorm[c] === "" || depSkipRe.test(snorm[c]) || !isNameHeader(snorm[c])) continue; if (hasFieldNow("Member Last Name") && hasFieldNow("First Name")) { addIssue("INFO", 0, "", headers[c], "", "Combined name column ignored — separate First/Last Name columns are present and were used", "Nothing to do unless First/Last are wrong; data kept in RAW DATA"); continue; } const r = scan(c, nameParses); if (confident(r, SPLIT_CONFIRM, false)) { const hdr = headers[c]; consume(c, "Member Last Name + First Name + Middle Initial", r.hits, r.nonBlank); const li = ensureField("Member Last Name"), fi = ensureField("First Name"), mi = ensureField("Middle Initial"); let guessed = 0; for (const row of rows) { const v = val(row, c); if (v === "") continue; const p = splitCombinedName(v); if (p === null) continue; if (p.first === "" && p.guessed) { // single word — cannot split setIfBlank(row, li, p.last); addIssue("WARNING", 0, v, hdr, v, "Only one name word — could not split into First + Last", "Add the missing first name on the LMG tab"); continue; } setIfBlank(row, li, p.last); setIfBlank(row, fi, p.first); setIfBlank(row, mi, p.mi); if (p.guessed) guessed++; } if (guessed > 0) addIssue("INFO", 0, "", hdr, "", `No comma in ${guessed} of ${r.nonBlank} name(s); split as 'First [MI] Last'`, "Verify first vs last name on the LMG tab"); addIssue("INFO", 0, "", hdr, "", "Combined name column split into Last / First / Middle Initial", "Verify on the LMG tab; original kept in RAW DATA"); } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) { addIssue("WARNING", 0, "", headers[c], "", "Column looks like a combined full-name column but too many values did not parse — not split automatically", "Split First/Last manually (or fix the values) and re-run"); } } // -- Shape 2: combined "City, State ZIP" column -- const CSZ_HEADERS = ["CITY STATE ZIP", "CITY ST ZIP", "CITY STATE ZIP CODE", "CITY ST ZIP CODE", "CITY STATE POSTAL CODE", "CSZ", "CITY STATE", "CITY AND STATE"]; const CITY_ONLY = ["CITY", "TOWN", "MEMBER CITY"]; for (let c = 0; c < nCols; c++) { if (headers[c] === "" || depSkipRe.test(snorm[c])) continue; const isCsz = CSZ_HEADERS.indexOf(snorm[c]) !== -1; const isCityShaped = !isCsz && CITY_ONLY.indexOf(snorm[c]) !== -1; if (!isCsz && !isCityShaped) continue; // Zip is optional only for named combined headers that don't promise one. const zipOpt = isCsz && snorm[c].indexOf("ZIP") === -1 && snorm[c] !== "CSZ"; const r = scan(c, (v: string) => parseCityStateZip(v, zipOpt) !== null); if (confident(r, isCsz ? SPLIT_CONFIRM : SPLIT_STRONG, !isCsz)) { const hdr = headers[c]; consume(c, "City + State + Zip Code", r.hits, r.nonBlank); const ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code"); for (const row of rows) { const v = val(row, c); if (v === "") continue; const p = parseCityStateZip(v, zipOpt); if (p) { setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); } else { setIfBlank(row, ci, v); // keep the whole value — no silent loss addIssue("WARNING", 0, "", hdr, v, "Could not parse 'City, State ZIP' — whole value placed in City", "Split this row manually on the LMG tab"); } } addIssue("INFO", 0, "", hdr, "", isCsz ? "Combined City/State ZIP column split into City, State, Zip Code" : `'${hdr}' contained full 'City, State ZIP' values — split into City, State, Zip Code`, "Verify on the LMG tab; original kept in RAW DATA"); } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) { addIssue("WARNING", 0, "", headers[c], "", "Some values look like combined 'City, State ZIP' but too many did not parse — not split automatically", "Fix the combined values (City, ST 12345) or split the column manually, then re-run"); } } // -- Shape 3: full "Address, City, State ZIP" column (always value-gated) -- const ADDR_EXTRA = ["FULL ADDRESS", "COMPLETE ADDRESS", "HOME ADDRESS", "MAILING ADDRESS", "FULL HOME ADDRESS"]; const isAddrHeader = (n: string): boolean => (/^(ADDRESS|ADDR|STREET ADDRESS|STREET)/.test(n) && !/EMAIL|IP/.test(n)) || ADDR_EXTRA.indexOf(n) !== -1; // Skipped entirely when separate City AND State AND Zip columns exist — // a street column can legitimately end in digits. if (!(hasFieldNow("City") && hasFieldNow("State") && hasFieldNow("Zip Code"))) { for (let c = 0; c < nCols; c++) { if (headers[c] === "" || depSkipRe.test(snorm[c]) || !isAddrHeader(snorm[c])) continue; const r = scan(c, (v: string) => parseFullAddress(v) !== null); if (confident(r, SPLIT_ADDR, true)) { const hdr = headers[c]; consume(c, "Member Street Address + City + State + Zip Code", r.hits, r.nonBlank); const sti = ensureField("Member Street Address"), ci = ensureField("City"), si = ensureField("State"), zi = ensureField("Zip Code"); for (const row of rows) { const v = val(row, c); if (v === "") continue; const p = parseFullAddress(v); if (p) { setIfBlank(row, sti, p.street); setIfBlank(row, ci, p.city); setIfBlank(row, si, p.state); setIfBlank(row, zi, p.zip); continue; } const csz = parseCityStateZip(v, false); // no comma boundary — peel State+ZIP, rest stays street if (csz && csz.zip !== "") { setIfBlank(row, sti, csz.city); setIfBlank(row, si, csz.state); setIfBlank(row, zi, csz.zip); addIssue("WARNING", 0, "", hdr, v, "Could not separate street from city (no comma) — both kept in Member Street Address", "Move the city out of the street address for this row"); } else { setIfBlank(row, sti, v); // keep the whole value — no silent loss addIssue("WARNING", 0, "", hdr, v, "Could not parse full address — whole value placed in Member Street Address", "Split City/State/Zip for this row manually"); } } addIssue("INFO", 0, "", hdr, "", "Combined full-address column split into Street / City / State / Zip", "Verify a few rows on the LMG tab; original kept in RAW DATA"); break; // only the first qualifying column splits; others merge into the street as usual } else if (r.nonBlank > 0 && r.frac >= SPLIT_HINT) { addIssue("WARNING", 0, "", headers[c], "", "Some values appear to contain City/State/ZIP inside the address — not split automatically", "Review — city/state/zip may be embedded in the street address"); } } } } // Column lookups are computed AFTER Stage S so split-out First/Last/City // columns are visible to the collapse/explode stages below. const norm = headers.map(h => normHeader(h)); const findCol = (names: string[]): number => { for (let i = 0; i < norm.length; i++) if (names.indexOf(norm[i]) !== -1) return i; return -1; }; const ssnCol = findCol(["SSN", "SSN NUMBER", "SOCIAL SECURITY NUMBER", "SOCIAL SECURITY", "SOCIAL SECURITY NO", "SOC SEC", "SS", "MEMBER SSN", "TAX ID"]); const lastCol = findCol(["LAST NAME", "LAST", "SURNAME", "MEMBER LAST NAME", "LNAME", "LASTNAME"]); const firstCol = findCol(["FIRST NAME", "FIRST", "GIVEN NAME", "MEMBER FIRST NAME", "FNAME", "FIRSTNAME"]); const dobCol = findCol(["DATE OF BIRTH", "DOB", "BIRTH DATE", "BIRTHDATE", "BORN"]); const relCol = findCol(["RELATIONSHIP", "RELATIONSHIP CODE", "RELATION", "REL", "DEPENDENT TYPE", "MEMBER TYPE", "ROLE"]); const genderCol = findCol(["GENDER", "SEX", "BIRTH SEX"]); const productCol = findCol(["PRODUCT", "COVERAGE", "COVERAGE TYPE", "PLAN TYPE", "BENEFIT", "BENEFIT TYPE", "LINE OF COVERAGE", "PRODUCT LINE", "COVERAGE LINE"]); const cell = (row: string[], i: number): string => (i < 0 || row[i] == null) ? "" : String(row[i]).trim(); const inheritRe = /ADDRESS|ADDR|STREET|CITY|TOWN|STATE|ZIP|POSTAL|LOCATION|DIVISION|BRANCH/; // ---- STAGE 0: product-coded routing ---- // When a row has a generic value column (Coverage Tier/Level, Plan, Election, // Class) plus a product column (Plan Type = Dental/Vision/Life/STD/LTD), move // that generic value into the product-specific LMG field for that row's product. const productKey = (v: string): string => { const n = normHeader(v); if (n === "") return ""; if (/DENTAL/.test(n)) return "DENTAL"; if (/VISION/.test(n)) return "VISION"; if (/MEDICAL|HEALTH/.test(n)) return "MEDICAL"; if (/VOLUNTARY|VOL LIFE|SUPPLEMENTAL/.test(n)) return "VOL_LIFE"; if (/BASIC LIFE|LIFE|AD D/.test(n)) return "BASIC_LIFE"; if (/\bSTD\b|SHORT TERM/.test(n)) return "STD"; if (/\bLTD\b|LONG TERM/.test(n)) return "LTD"; return ""; }; const genericCols: { col: number, concept: string }[] = []; for (let i = 0; i < norm.length; i++) { const h = norm[i]; if (i === productCol) continue; if (/DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(h)) continue; // already product-specific // COV / COVERAGE / TIER / LEVEL -> the election tier (EE/ES/EC/FAM/W). if (h === "COVERAGE DETAILS") continue; // free-text detail, not the tier if (/\bCOV/.test(h) || h === "TIER" || h === "LEVEL") genericCols.push({ col: i, concept: "TIER" }); // PLAN / SELECT / ELECT -> which plan to enroll on. else if (/\bPLAN\b/.test(h) || /SELECT/.test(h) || /ELECT/.test(h)) genericCols.push({ col: i, concept: "PLAN" }); else if (h === "CLASS" || h === "CLASS WAIVE") genericCols.push({ col: i, concept: "CLASS" }); } const ROUTING: { [c: string]: { [p: string]: string } } = { TIER: { DENTAL: "Dental Coverage Type", VISION: "Vision Coverage Type" }, PLAN: { DENTAL: "Dental Plan Selection", VISION: "Vision Plan Selection" }, CLASS: { STD: "STD Class", LTD: "LTD Class" } }; if (productCol >= 0 && genericCols.length > 0) { const targetCol = new Map(); const ensureCol = (field: string): number => { if (targetCol.has(field)) return targetCol.get(field) as number; for (let i = 0; i < headers.length; i++) { // reuse an existing column that maps to this field if (aliasToFieldMap.get(normHeader(headers[i])) === field) { targetCol.set(field, i); return i; } } const idx = headers.length; headers.push(field); for (const row of rows) row.push(""); targetCol.set(field, idx); return idx; }; let routed = 0; for (const row of rows) { const p = productKey(cell(row, productCol)); if (p === "") continue; // unknown product -> leave generic value alone (it will be flagged) for (const gc of genericCols) { const map = ROUTING[gc.concept]; const field = map ? map[p] : ""; const val = cell(row, gc.col); if (field && val !== "") { const ti = ensureCol(field); if (cell(row, ti) === "") { row[ti] = val; routed++; } } } } // Blank the consumed generic headers so they aren't re-flagged as unrecognized // (the values now live in product-specific columns; RAW DATA keeps the originals). if (routed > 0) { for (const gc of genericCols) headers[gc.col] = ""; addLog(`Product routing: moved ${routed} generic value(s) into product-specific columns via '${headers0[productCol]}'.`); } } // ---- STAGE 1: collapse stacked product rows ---- if (productCol >= 0 && (ssnCol >= 0 || (lastCol >= 0 && dobCol >= 0))) { const personKey = (row: string[]): string => [cell(row, ssnCol), cell(row, lastCol).toUpperCase(), cell(row, firstCol).toUpperCase(), cell(row, dobCol)].join("|"); const groups = new Map(); const order: string[] = []; for (const r of rows) { const k = personKey(r); if (!groups.has(k)) { groups.set(k, []); order.push(k); } (groups.get(k) as string[][]).push(r); } let collapsed = 0; const merged: string[][] = []; // Only columns we actually keep (map to an LMG field) drive conflict flags. // Columns we don't keep — Total Rate, premiums, etc. — legitimately differ // between a person's Dental and Vision lines, so they are ignored here. const keptForConflict = headers.map(h => aliasToFieldMap.has(normHeader(h))); for (const k of order) { const grp = groups.get(k) as string[][]; if (grp.length === 1) { merged.push(grp[0]); continue; } const memberName = (cell(grp[0], firstCol) + " " + cell(grp[0], lastCol)).trim() || "(stacked rows)"; const out: string[] = headers.map(() => ""); for (let c = 0; c < headers.length; c++) { let chosen = ""; for (const r of grp) { const v = cell(r, c); if (v === "") continue; if (chosen === "") chosen = v; else if (chosen !== v && c !== productCol && keptForConflict[c]) { addIssue("WARNING", 0, memberName, headers[c], `${chosen} / ${v}`, "Conflicting values while collapsing stacked rows for the same person", "Confirm which value is correct"); } } out[c] = chosen; } merged.push(out); collapsed += grp.length - 1; } if (collapsed > 0) { rows = merged; addLog(`Stacked-product collapse: merged ${collapsed} extra row(s) into ${merged.length} person row(s) via the '${headers0[productCol]}' column.`); } } // ---- STAGE 2: explode "Dependent N " columns into rows ---- const depRe = /^DEP(?:ENDENT)?\s*(\d+)\s+(.+)$/; const depGroups = new Map(); const depColSet = new Set(); for (let i = 0; i < norm.length; i++) { const m = norm[i].match(depRe); if (!m) continue; const idx = parseInt(m[1], 10); const sub = m[2]; if (!depGroups.has(idx)) depGroups.set(idx, { ssn: -1, dob: -1, gender: -1, rel: -1, last: -1, first: -1, name: -1 }); const g = depGroups.get(idx) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number }; let matched = true; if (sub === "SSN" || sub.indexOf("SOCIAL") === 0 || sub === "SS") g.ssn = i; else if (sub === "DOB" || sub.indexOf("BIRTH") >= 0 || sub === "DATE OF BIRTH" || sub === "BORN") g.dob = i; else if (sub === "GENDER" || sub === "SEX") g.gender = i; else if (sub.indexOf("RELATION") === 0 || sub === "REL" || sub === "TYPE") g.rel = i; else if (sub === "LAST NAME" || sub === "LAST") g.last = i; else if (sub === "FIRST NAME" || sub === "FIRST") g.first = i; else if (sub === "NAME" || sub === "FULL NAME") g.name = i; else matched = false; if (matched) depColSet.add(i); } if (depGroups.size > 0) { const keepIdx: number[] = []; for (let i = 0; i < headers.length; i++) if (!depColSet.has(i)) keepIdx.push(i); const reducedPos = (origCol: number): number => origCol < 0 ? -1 : keepIdx.indexOf(origCol); const inheritCols = keepIdx.filter(i => inheritRe.test(norm[i])); const newHeaders = keepIdx.map(i => headers[i]); const appendedRel = relCol < 0; if (appendedRel) newHeaders.push("Relationship"); const relPos = appendedRel ? newHeaders.length - 1 : reducedPos(relCol); const newRows: string[][] = []; let depCount = 0; const depIdxs = Array.from(depGroups.keys()).sort((a, b) => a - b); for (const r of rows) { const emp: string[] = keepIdx.map(i => r[i]); if (appendedRel) emp.push("Employee"); else if (relPos >= 0 && cell(r, relCol) === "") emp[relPos] = "Employee"; newRows.push(emp); const empName = (cell(r, firstCol) + " " + cell(r, lastCol)).trim(); for (const n of depIdxs) { const g = depGroups.get(n) as { ssn: number, dob: number, gender: number, rel: number, last: number, first: number, name: number }; const dssn = cell(r, g.ssn), ddob = cell(r, g.dob), dname = cell(r, g.name); const dlast = cell(r, g.last), dfirst = cell(r, g.first), dgender = cell(r, g.gender), drel = cell(r, g.rel); if (dssn === "" && ddob === "" && dname === "" && dlast === "" && dfirst === "") continue; // empty dependent slot depCount++; const drow: string[] = keepIdx.map(() => ""); for (const ic of inheritCols) drow[keepIdx.indexOf(ic)] = r[ic]; // inherit address/location to tie family const setR = (origCol: number, val: string) => { const p = reducedPos(origCol); if (p >= 0 && val !== "") drow[p] = val; }; if (dlast !== "" || dfirst !== "") { setR(lastCol, dlast); setR(firstCol, dfirst); } else if (dname !== "") { const np = splitCombinedName(dname); // shared §11c parser — comma "Last, First" is reliable if (np !== null) { if (np.guessed && np.first !== "") addIssue("INFO", 0, dname, "First Name", dname, "Dependent name had no comma; guessed 'First Last' split", "Verify first vs last name"); setR(lastCol, np.last); setR(firstCol, np.first); } } setR(ssnCol, dssn); setR(dobCol, ddob); setR(genderCol, dgender); let relVal = normalizeDepRel(drel); if (relVal === "") { relVal = "Child"; addIssue("INFO", 0, (dfirst + " " + dlast).trim() || empName, "Relationship", drel, "Dependent relationship not specified; defaulted to Child", "Set Spouse / Child / Domestic Partner if not a child"); } if (relPos >= 0) { if (appendedRel) drow.push(relVal); else drow[relPos] = relVal; } else if (appendedRel) drow.push(relVal); newRows.push(drow); } } headers = newHeaders; rows = newRows; if (depCount > 0) addLog(`Dependent-column explode: created ${depCount} dependent row(s) from ${depGroups.size} dependent slot(s).`); } return { headers, rows, splits }; } }