/** * ============================================================================ * 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 * 7. Other Columns <- every column NOT in the LMG upload, with the reason * 8. HEADER MAP <- column mappings you confirm; remembered across runs * * 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. (Stacked product rows for the same person are * collapsed by the pre-pass, rules section 11; true duplicates are only flagged.) * ============================================================================ */ function main(workbook: ExcelScript.Workbook) { // QUOTE MODE: build-site.py publishes a second script, NCAM-Quote-Engine.ts, // with this set to true. It outputs one QUOTE line per employee (tiers only, // dependents purged) instead of the LMG/NCAM tabs. Never edit that copy. const QUOTE_MODE = true; const OUTPUT_SHEETS = ["QUOTE", "LMG", "NCAM", "SUMMARY", "VALIDATION ISSUES", "PROCESSING LOG", "RAW DATA", "Other Columns", "HEADER MAP"]; // ---- Audit collectors ------------------------------------------------- const log: string[] = []; const issues: string[][] = []; // SEVERITY, SOURCE ROW, LMG ROW, MEMBER, COLUMN, VALUE, ISSUE, ACTION // srcRowLabels[i] = the RAW DATA row number(s) behind enrollee row i (set after // the pre-pass; stacked rows collapsed into one person list every source row). let srcRowLabels: string[] = []; function addLog(msg: string) { log.push(msg); } // `row` is the LMG sheet row (header = row 1). The issue records both the // source row on RAW DATA (what the user looks up) and the LMG row (highlighting). function addIssue(sev: string, row: number, member: string, col: string, val: string, issue: string, action: string) { const src = row ? (srcRowLabels[row - 2] || String(row)) : ""; issues.push([sev, src, row ? String(row) : "", member, col, val, issue, action]); } // ---- Version stamp (compare this to the version shown on the hub page) ---- const ENGINE_VERSION = "3.5.0"; const ENGINE_BUILD = "2026-10-09"; 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[] = [ // MEMBER INFORMATION "Relationship", "Member Last Name", "First Name", "Middle Initial", "Gender", "Date of Birth", "Member Street Address", "City", "State", "Zip Code", "Phone Number", "Social Security Number", "Status Field", "Date of Hire", "Email Address", // ADDITIONAL DETAILS "Hours Worked Per Week", "Salary", "Occupation", "Disabled?", "Billing Division", // BENEFITS INFORMATION "Dental Cov Level", "Dental Plan Elected", "DHMO Provider Name", "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included", "Vision Cov Level", "Vision Plan Election", "Basic Life Cov Level", "Life/AD&D Class", "Primary Life Beneficiary", "Dependent Basic Life", "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount", "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "LTD Class", "Working Location" ]; // Alias variants -> LMG field. Keys are normalized (see normHeader()). // Field names used before v3.0.0 (2026-09-10 shared dictionary) and by the // old Python tool — accepted anywhere a user types an LMG field name. const LEGACY_FIELD_NAMES: { [old: string]: string } = { "Employee Status": "Status Field", "Status": "Status Field", "SSN": "Social Security Number", "Member First Name": "First Name", "Member City": "City", "Member State": "State", "Member Zip Code": "Zip Code", "Hours Worked per week": "Hours Worked Per Week", "Annual Salary": "Salary", "Dental Plan Selection": "Dental Plan Elected", "Dental Coverage Type": "Dental Cov Level", "Dental Coverage Tier": "Dental Cov Level", "Dental Prior Carrier Name": "Dental Prior Current Carrier Name", "Dental Prior/Current Carrier Name": "Dental Prior Current Carrier Name", "Dental Prior Carrier Termination Date": "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho": "Dental Prior Carrier Ortho included", "Orthodontia Included?": "Dental Prior Carrier Ortho included", "Vision Plan Selection": "Vision Plan Election", "Vision Plan Elected": "Vision Plan Election", "Vision Coverage Type": "Vision Cov Level", "Vision Coverage Tier": "Vision Cov Level", "Basic Life Election": "Basic Life Cov Level", "Basic Life Class": "Life/AD&D Class", "Primary Beneficiary": "Primary Life Beneficiary", "Employee Voluntary Life": "Employee Voluntary Life Flat Amount", "Spousal Voluntary Life": "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life": "Dependent Voluntary Life Flat Amount", "STD": "Short-Term Disability Cov Level", "STD Election": "Short-Term Disability Cov Level", "LTD": "Long-Term Disability Cov Level", "LTD Election": "Long-Term Disability Cov Level", "Location Number": "Working Location" }; 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", "STATUS FIELD"], "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", "SOCIAL SECURITY NUMBER NOT USED"], "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", "ZIP CODE 5 DIGITS"], "Phone Number": ["PHONE", "PHONE NUMBER", "HOME PHONE", "PERSONAL PHONE", "MOBILE PHONE", "CELL PHONE", "CELL", "MOBILE", "WORK PHONE", "OFFICE PHONE", "BUSINESS PHONE", "TELEPHONE", "CONTACT NUMBER", "PHONE NUMBER NUMERIC ONLY"], "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"], "Age": ["AGE", "CURRENT AGE", "EMPLOYEE AGE", "MEMBER AGE", "AGE AT EFFECTIVE DATE"], "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 Elected": ["DENTAL PLAN ELECTED", "DENTAL PLAN ELECTION", "DENTAL PLAN SELECTION", "DENTAL PLAN SELECT", "DENTAL PLAN NAME", "DENTAL PLAN", "DENTAL SELECT", "DENTAL ELECT", "DENTAL ELECTION", "DENTAL IF EMPLOYEE IS ELECTING DENTAL PLEASE SELECT PLAN"], "Dental Cov Level": ["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 Current Carrier Name": ["PRIOR CARRIER", "PRIOR CARRIER NAME", "CURRENT CARRIER", "PREVIOUS CARRIER", "DENTAL PRIOR CARRIER NAME", "PRIOR CURRENT CARRIER NAME", "IF A MEMBER HAD DENTAL AND OR ORTHO COVERAGE WITHIN THE PAST 12 MONTHS PLEASE PROVIDE 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 included": ["ORTHODONTIA INCLUDED", "WAS ORTHODONTIA INCLUDED", "ORTHODONTIA", "ORTHO", "PRIOR ORTHO", "ORTHO COVERAGE"], "Vision Plan Election": ["VISION PLAN ELECTED", "VISION PLAN ELECTION", "VISION PLAN SELECTION", "VISION PLAN SELECT", "VISION PLAN NAME", "VISION PLAN", "VISION SELECT", "VISION ELECT", "VISION ELECTION", "VISION IF EMPLOYEE IS ELECTING VISION PLEASE SELECT PLAN"], "Vision Cov Level": ["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 Cov Level": ["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 COVERAGGE TYPE", "LIFE BASIC LIFE ELECTION FLAT AMOUNT OR LIFE BY SALARY ELECT ENROLL OR WAIVE LIFE BY CLASS SELECT CLASS NUMBER OR WAIVE"], "Life/AD&D Class": ["LIFE/AD&D CLASS", "LIFE AD&D CLASS", "BASIC LIFE CLASS", "LIFE CLASS"], "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 Flat Amount": ["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", "EMPLOYEE VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN IF NOT ELECTING ENTER 0"], "Spouse Voluntary Life Flat Amount": ["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", "SPOUSAL VOLUNTARY LIFE AMOUNT FLAT AMOUNT BETWEEN MINIMUM AMOUNT AND MAXIMUM AMOUNT OF THE SOLD PLAN NOT TO EXCEED 50 OF THE EMPLOYEE ELECTION IF NOT ELECTING ENTER 0"], "Dependent Voluntary Life Flat Amount": ["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"], "Short-Term Disability Cov Level": ["STD ELECTION", "STD", "SHORT TERM DISABILITY", "STD ENROLL", "STD COVERAGE TYPE", "STD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"], "STD Class": ["STD CLASS", "SHORT TERM DISABILITY CLASS", "STD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"], "Long-Term Disability Cov Level": ["LTD ELECTION", "LTD", "LONG TERM DISABILITY", "LTD ENROLL", "LTD COVERAGE TYPE", "LTD IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"], "LTD Class": ["LTD CLASS", "LONG TERM DISABILITY CLASS", "LTD CLASS IF 100 EMPLOYER PAID REQUIRES 100 PARTICIPATION"], "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); } for (const f of LMG_FIELDS) if (!aliasToField.has(normHeader(f))) aliasToField.set(normHeader(f), f); // 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])); // Declared before the pre-pass: its tier detection (normalizeTier) reads it. const WAIVE_WORDS = new Set(["W", "WAIVE", "WAIVED", "WAIVES", "DECLINE", "DECLINED", "NONE", "N A", "NA", "WAIVE COVERAGE", "OPT OUT", "NO COVERAGE", "WAIVE WITH OTHER COVERAGE"]); // 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; srcRowLabels = shaped.srcRows; 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); for (const o of Object.keys(LEGACY_FIELD_NAMES)) fieldByNorm.set(normHeader(o), LEGACY_FIELD_NAMES[o]); // pre-3.0 names typed in Map To 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" }); } // Vertical-census columns (Plan Type, Coverage Tier, Benefit Amount...) cover many // coverages at once, so there is no one field to map them to. for (const pv of shaped.pivots) { colReview.push({ header: pv.header, status: "pivot", mapTo: "", suggested: pv.role, 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)."); // No Relationship column at all (common on quoting censuses): every row is an // employee. Without this, every row would be treated as a dependent and its // SSN / salary / tiers wiped. const noRelationshipCol = !fieldToCol.has("Relationship"); if (noRelationshipCol) { addIssue("INFO", 0, "", "Relationship", "", "No Relationship column in the census; every row treated as Employee", "If the file also lists spouses/children, add a Relationship column and re-run"); addLog("No Relationship column found: every row treated as Employee."); } // Does this group even carry each product? Controls blank-default behavior. const hasDental = fieldToCol.has("Dental Plan Elected") || fieldToCol.has("Dental Cov Level"); const hasVision = fieldToCol.has("Vision Plan Election") || fieldToCol.has("Vision Cov Level"); const hasSTD = fieldToCol.has("Short-Term Disability Cov Level") || fieldToCol.has("STD Class"); const hasLTD = fieldToCol.has("Long-Term Disability Cov Level") || 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"] ]; // Extra waiver words for the Enroll/Waive fields only (Basic Life, Dependent // Basic Life, STD, LTD). Not added to WAIVE_WORDS: a "0" there means an amount. const ENROLL_WAIVE_NO = ["N", "NO", "0", "FALSE"]; // Dental/Vision Cov Level waive text on LMG (owner decision 2026-09-27, reverting // 2026-09-10 for these two only). Basic Life / STD / LTD stay EE/Waive. const LMG_WAIVE_TEXT = "Waive with other coverage"; // ====================================================================== // 4. PER-ROW TRANSFORMATION -> LMG TABLE // ====================================================================== const lmgRows: string[][] = []; const ageByRow: string[] = []; // QUOTE tab only; 1:1 with lmgRows 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 = noRelationshipCol ? "Employee" : normalizeRelationship(relRaw); if (relRaw && relationship === "") { addIssue("WARNING", excelRow, memberName, "Relationship", relRaw, "Unrecognized relationship value", "Confirm: Employee / Spouse / Domestic Partner / Child"); } if (!relRaw && !noRelationshipCol) 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") && !(QUOTE_MODE && get("Age"))) addIssue("ERROR", excelRow, memberName, "Date of Birth", "", "Missing date of birth", "Enter as MM/DD/YYYY"); const age = ageFromDob(dob); if (dob && isFutureDate(dob)) addIssue("ERROR", excelRow, memberName, "Date of Birth", dob, "Date of birth is in the future", "Correct the date of birth (check the year)"); else if (age > 100) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (over 100)`, "Check the year of birth"); else if (age >= 65) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Member is ${age} (65 or older)`, "Review — age may affect eligibility/rating"); if (isEmployee && age >= 0 && age < 14) addIssue("WARNING", excelRow, memberName, "Date of Birth", dob, `Employee is ${age} years old (under 14)`, "Check the date of birth, or whether this row is a dependent"); 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"); if (doh) { const yearsSinceHire = ageFromDob(doh); if (isFutureDate(doh)) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is in the future", "Confirm the hire date (new hire starting later?)"); else if (yearsSinceHire > 100) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, `Hire date is ${yearsSinceHire} years ago`, "Check the year of hire"); if (dob && age >= 0 && yearsSinceHire >= 0 && age - yearsSinceHire < 14) addIssue("WARNING", excelRow, memberName, "Date of Hire", doh, "Hire date is before the employee turned 14", "Check the hire date and date of birth"); } 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) { // Cut at the last whole word that fits (hard cut only when one word is over 25). const cut = occ.lastIndexOf(" ", 25); const short = (cut > 0 ? occ.substring(0, cut) : occ.substring(0, 25)).trim(); addIssue("WARNING", excelRow, memberName, "Occupation", get("Occupation"), `Occupation exceeds 25 chars after abbreviation; truncated to '${short}'`, "Check the shortened title, or shorten it yourself"); occ = short; } } // --- Disabled --- let disabled = normalizeYesNo(get("Disabled?")); if (disabled === "") disabled = "No"; // --- Coverage tiers --- const dentalTierRaw = get("Dental Cov Level"); let dentalTier = normalizeTier(dentalTierRaw); if (dentalTierRaw && dentalTier === "") addIssue("WARNING", excelRow, memberName, "Dental Cov Level", dentalTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading"); const visionTierRaw = get("Vision Cov Level"); let visionTier = normalizeTier(visionTierRaw); if (visionTierRaw && visionTier === "") addIssue("WARNING", excelRow, memberName, "Vision Cov Level", visionTierRaw, "Unrecognized coverage tier; original value kept", "Change to EE / ES / EC / FAM / Waive before uploading"); // LMG output: W -> "Waive"; 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); // Waived Dental/Vision -> Plan column blank on LMG (source value stays on RAW DATA). const planOut = (field: string, tierOut: string): string => { const v = get(field); if (tierOut !== LMG_WAIVE_TEXT) return v; if (v.trim()) addIssue("INFO", excelRow, memberName, field, v, "Coverage waived; plan left blank on LMG", "Original value kept on RAW DATA"); return ""; }; // --- Voluntary life (employee rows). Employee/Spousal waived -> 0; // Dependent waived -> "Waive". --- const eeVol = volLife(get("Employee Voluntary Life Flat Amount"), isEmployee, "0", false); const spVol = volLife(get("Spouse Voluntary Life Flat Amount"), isEmployee, "0", false); const chVol = volLife(get("Dependent Voluntary Life Flat Amount"), 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 Flat Amount"); oddThousand(spVol, "Spouse Voluntary Life Flat Amount"); oddThousand(chVol, "Dependent Voluntary Life Flat Amount"); if (!isEmployee && (WAIVE_WORDS.has(normHeader(get("Spouse Voluntary Life Flat Amount"))) === false) && get("Spouse Voluntary Life Flat Amount")) { addIssue("INFO", excelRow, memberName, "Spouse Voluntary Life Flat Amount", get("Spouse Voluntary Life Flat Amount"), "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 Cov Level")); 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 is actually // enrolled in life: Basic Life elected (not waived) and/or real Employee Voluntary // Life. Spouse/child vol life alone does not insure the employee. const hasLife = present(basicLife) || realVol(eeVol); 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 Elected": planOut("Dental Plan Elected", dentalTierOut), "Dental Cov Level": dentalTierOut, "DHMO Provider Name": get("DHMO Provider Name"), "Dental Prior Current Carrier Name": get("Dental Prior Current Carrier Name"), "Dental Prior Carrier Effective Date": priorEff, "Dental Prior Carrier Term Date": priorTerm, "Dental Prior Carrier Ortho included": normalizeYesNo(get("Dental Prior Carrier Ortho included")), "Vision Plan Election": planOut("Vision Plan Election", visionTierOut), "Vision Cov Level": visionTierOut, "Basic Life Cov Level": basicLife, "Life/AD&D Class": get("Life/AD&D Class"), "Primary Life Beneficiary": beneficiary, "Dependent Basic Life": normalizeEnrollWaive(get("Dependent Basic Life")), "Employee Voluntary Life Flat Amount": eeVol, "Spouse Voluntary Life Flat Amount": spVol, "Dependent Voluntary Life Flat Amount": chVol, "Short-Term Disability Cov Level": normalizeEnrollWaive(get("Short-Term Disability Cov Level")), "STD Class": get("STD Class"), "Long-Term Disability Cov Level": normalizeEnrollWaive(get("Long-Term Disability Cov Level")), "LTD Class": get("LTD Class") }; lmgRows.push(LMG_FIELDS.map(f => rec[f] == null ? "" : rec[f])); ageByRow.push(get("Age").replace(/[^\d]/g, "")); // --- 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)"; const empRow = fam.emp + 2; // LMG sheet row of the 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 Flat Amount")], dep[ci("Spouse Voluntary Life Flat Amount")], dep[ci("Dependent Voluntary Life Flat Amount")]].filter(x => realAmt(x))[0] || ""; if (isSp && depVol && !realAmt(emp[ci("Spouse Voluntary Life Flat Amount")])) { emp[ci("Spouse Voluntary Life Flat Amount")] = depVol; rolled++; addIssue("INFO", empRow, empName, "Spouse Voluntary Life Flat Amount", 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 Flat Amount")])) { emp[ci("Dependent Voluntary Life Flat Amount")] = depVol; rolled++; addIssue("INFO", empRow, empName, "Dependent Voluntary Life Flat Amount", 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 Cov Level")]].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 Flat Amount")] = ""; dep[ci("Spouse Voluntary Life Flat Amount")] = ""; dep[ci("Dependent Voluntary Life Flat Amount")] = ""; } // 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 Cov Level", "Vision Cov Level"]) { const idx = ci(tf); const cur = emp[idx]; if (cur === "" || isWaiveTier(cur)) continue; // employee not enrolled in this product if (tierRank[cur] == null) continue; // unrecognized tier kept as-is (already flagged); never overwrite it 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", empRow, 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 Cov Level")], emp[ci("Vision Cov Level")]]; 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", empRow, 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", empRow, 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", empRow, 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", empRow, 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 Elected", "Dental Cov Level", "DHMO Provider Name", "Dental Prior Current Carrier Name", "Dental Prior Carrier Effective Date", "Dental Prior Carrier Term Date", "Dental Prior Carrier Ortho included", "Vision Plan Election", "Vision Cov Level", "Basic Life Cov Level", "Primary Life Beneficiary", "Dependent Basic Life", "Employee Voluntary Life Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount", "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "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, numeric fields (which keep decimals) and // the Dental/Vision Cov Level. Names and plan/class values keep ' and -. // ====================================================================== { 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 Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount", "Dental Cov Level", "Vision Cov Level" // normalized tiers have no specials; an unrecognized tier (EE+1) stays verbatim for review ].map(f => ci(f)).filter(i => i >= 0)); // Names and plan/class values keep apostrophes and hyphens (O'BRIEN, SMITH-JONES, // PPO-1500) - rules section 8b. const KEEP_APOS_HYPHEN = new Set([ "Member Last Name", "First Name", "Middle Initial", "Dental Plan Elected", "Vision Plan Election", "Life/AD&D Class", "STD Class", "LTD Class" ].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 = KEEP_APOS_HYPHEN.has(c) ? cleanSpecialKeep(v) : 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", "Short-Term Disability Cov Level", "STD Class", "Long-Term Disability Cov Level", "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" back to W; blank employee -> W const dt = lmgToNcamTier(row[colIdx("Dental Cov Level")], hasDental); const vt = lmgToNcamTier(row[colIdx("Vision Cov Level")], 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("Short-Term Disability Cov Level")], row[colIdx("STD Class")], row[colIdx("Long-Term Disability Cov Level")], row[colIdx("LTD Class")]] : []; ncamRows.push(base.concat(extra)); } addLog(`NCAM build complete: ${ncamRows.length} employee row(s)${includeStdLtd ? " (STD/LTD columns included)" : ""}.`); // ====================================================================== // 5b. QUOTE (quote mode: one line per employee, dependents purged) // Dependents only show up as the derived Dental/Vision tier. No plan names, // basic life or voluntary life. Columns blank for every employee are left off. // ====================================================================== const QUOTE_COLS: [string, string][] = [ ["Last Name", "Member Last Name"], ["First Name", "First Name"], ["Gender", "Gender"], ["Date of Birth", "Date of Birth"], ["Age", ""], ["Dental Coverage Type", "Dental Cov Level"], ["Vision Coverage Type", "Vision Cov Level"], ["Salary", "Salary"], ["Occupation", "Occupation"], ["Date of Hire", "Date of Hire"], ["Short-Term Disability Cov Level", "Short-Term Disability Cov Level"], ["STD Class", "STD Class"], ["Long-Term Disability Cov Level", "Long-Term Disability Cov Level"], ["LTD Class", "LTD Class"], ["City", "City"], ["State", "State"], ["Zip Code", "Zip Code"] ]; const quoteAll: string[][] = []; for (let i = 0; i < lmgRows.length; i++) { const row = lmgRows[i]; if (row[colIdx("Relationship")] !== "Employee") continue; quoteAll.push(QUOTE_COLS.map(q => { const out = q[0], f = q[1]; return ( out === "Age" ? ageByRow[i] : out === "Dental Coverage Type" ? (hasDental ? lmgToNcamTier(row[colIdx(f)], true) : "") : out === "Vision Coverage Type" ? (hasVision ? lmgToNcamTier(row[colIdx(f)], true) : "") : row[colIdx(f)]); })); } const QUOTE_KEEP = new Set(["Last Name", "Gender"]); const quoteKept: number[] = []; for (let c = 0; c < QUOTE_COLS.length; c++) { if (QUOTE_KEEP.has(QUOTE_COLS[c][0]) || quoteAll.some(r => r[c] !== "")) quoteKept.push(c); } // DOB or Age is required; keep DOB visible when neither column has data. if (quoteKept.every(c => QUOTE_COLS[c][0] !== "Date of Birth" && QUOTE_COLS[c][0] !== "Age")) { quoteKept.push(3); quoteKept.sort((a, b) => a - b); } const quoteHeaders = quoteKept.map(c => QUOTE_COLS[c][0]); const quoteRows = quoteAll.map(r => quoteKept.map(c => r[c])); if (QUOTE_MODE) addLog(`QUOTE build complete: ${quoteRows.length} employee line(s); ${lmgRows.length - quoteRows.length} dependent/other row(s) purged (their tiers are on the employee line).`); // ====================================================================== // 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(QUOTE_MODE ? ["QUOTE employee lines", String(quoteRows.length)] : ["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 Cov Level"); tierBlock("Vision", hasVision, (raw) => lmgToNcamTier(raw, true), "Vision Cov Level"); // (The issue tally is added to SUMMARY in section 7, after the last issue is // recorded, so the "Required column is entirely blank" errors are counted too.) // ====================================================================== // 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(QUOTE_MODE ? ["Member Last Name", "Gender"] : ["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 Flat Amount", "Spouse Voluntary Life Flat Amount", "Dependent Voluntary Life Flat Amount"]); // 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 Elected", "Vision Plan Election"]); 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(", ")}.`); // Quote mode: issues about LMG-only fields (SSN, plans, life, address...) don't // apply to a quote. They are counted in the log, not shown. if (QUOTE_MODE) { const quoteFields = new Set(QUOTE_COLS.map(q => q[1]).concat(["Relationship", "(row)", "", "Age"])); const before = issues.length; const keep = issues.filter(it => quoteFields.has(it[4])); issues.length = 0; for (const it of keep) { it[2] = ""; issues.push(it); } if (before > keep.length) addLog(`Quote mode: ${before - keep.length} issue(s) about LMG-only fields not listed (run the LMG/NCAM script to see them).`); } // Issue tally - every issue has been recorded by this point. 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.`); // 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); // Remove the other script's output tabs so a workbook never mixes LMG and QUOTE results. for (const n of (QUOTE_MODE ? ["LMG", "NCAM", "Other Columns"] : ["QUOTE"])) { const old = workbook.getWorksheet(n); if (old) old.delete(); } // Recreate output sheets. const firstSheet = QUOTE_MODE ? writeSheet(workbook, "QUOTE", [quoteHeaders].concat(quoteRows), "#1F4E79", TAB_PRIMARY) : writeSheet(workbook, "LMG", [lmgHeaderOut].concat(lmgRowsOut), "#1F4E79", TAB_PRIMARY); const lmgSheet = firstSheet; const ncamSheet = QUOTE_MODE ? null : writeSheet(workbook, "NCAM", [ncamHeaders].concat(ncamRows), "#2E75B6", TAB_PRIMARY); writeSheet(workbook, "SUMMARY", summary, "#548235", TAB_SECONDARY); const issuesHeader = ["SEVERITY", "SOURCE ROW (RAW DATA)", "LMG ROW", "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 pv of shaped.pivots) otherRows.push([pv.header, pv.role]); 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."]]; if (!QUOTE_MODE) 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 === "pivot") status = "Used by the vertical-census pivot - no mapping needed"; 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 // Every LMG field in column H, and 'Map To' becomes a dropdown of that list // (warning style, so the legacy pre-3.0 names can still be typed by hand). const hmWs = workbook.getWorksheet("HEADER MAP"); const optCol = hmHeaderRow.length + 1; // one blank column gap hmWs.getRangeByIndexes(0, optCol, LMG_FIELDS.length + 1, 1).setValues([["LMG FIELDS (Map To options)"]].concat(LMG_FIELDS.map(f => [f]))); const optHdr = hmWs.getRangeByIndexes(0, optCol, 1, 1).getFormat(); optHdr.getFill().setColor("#C55A11"); optHdr.getFont().setColor("#FFFFFF"); optHdr.getFont().setBold(true); hmWs.getRangeByIndexes(0, optCol, 1, 1).getEntireColumn().getFormat().autofitColumns(); const optLetter = String.fromCharCode(65 + optCol); const dv = hmWs.getRangeByIndexes(1, 1, Math.max(hmRows.length, 1) + 50, 1).getDataValidation(); // +50 rows for hand-added headers dv.setRule({ list: { inCellDropDown: true, source: `=$${optLetter}$2:$${optLetter}$${LMG_FIELDS.length + 1}` } }); dv.setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.warning, title: "Not an LMG field", message: "Pick a field from the list in column " + optLetter + " (legacy names still work — choose Yes to keep)." }); // ---- 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 = QUOTE_MODE ? [] : issues.slice().sort((a, b) => (sevRank[a[0]] || 0) - (sevRank[b[0]] || 0)); for (const it of ordered) { const rowNum = parseInt(it[2], 10); // LMG ROW 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[4] === "(row)") { lmgSheet.getRangeByIndexes(rIdx, 0, 1, keptCount).getFormat().getFill().setColor(color); } else { const full = LMG_FIELDS.indexOf(it[4]); 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. firstSheet.setPosition(0); if (ncamSheet) ncamSheet.setPosition(1); firstSheet.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(); } // Names / plan / class values: like cleanSpecial but apostrophes and hyphens stay // (curly apostrophes become straight ones). function cleanSpecialKeep(v: string): string { return v.replace(/[\u2018\u2019]/g, "'").replace(/[^A-Za-z0-9 '\-]+/g, " ").replace(/\s+/g, " ").trim(); } // True when an MM/DD/YYYY date is after today. function isFutureDate(mmddyyyy: string): boolean { const m = mmddyyyy.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/); if (!m) return false; const now = new Date(); const d = parseInt(m[3], 10) * 10000 + parseInt(m[1], 10) * 100 + parseInt(m[2], 10); const t = now.getFullYear() * 10000 + (now.getMonth() + 1) * 100 + now.getDate(); return d > t; } 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 Cov Level, Dependent Basic Life, STD, LTD all read "EE" / "Waive" (owner decision 2026-09-10; was Enroll/Waive). const n = normHeader(v); if (n === "") return ""; if (WAIVE_WORDS.has(n) || ENROLL_WAIVE_NO.indexOf(n) !== -1) return "Waive"; if (["ENROLL", "ENROLLED", "ENROLLEE", "ELECT", "ELECTED", "YES", "Y", "ACTIVE", "E", "ENROLLING", "ADD", "EE"].indexOf(n) !== -1) return "EE"; if (["EE", "ES", "EC", "FAM"].indexOf(normalizeTier(v)) !== -1) return "EE"; // a tier (Employee Only, Family...) on a vertical census = enrolled 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; if (raw.trim() !== "") return raw.trim(); // unrecognized (e.g. EE+1): keep the original; the caller raises a WARNING (rules section 5) // blank tier if (isEmployee && productExists) return waiveText; // NCAM would default blank to EE, so force waive return raw; // keep whatever was there (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); // Two-digit year: 20YY unless that is after the current year, else 19YY. if (yr < 100) yr += (2000 + yr) <= new Date().getFullYear() ? 2000 : 1900; if (isRealDate(yr, mo, da)) 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 (isRealDate(yr, mo, da)) return pad2(mo) + "/" + pad2(da) + "/" + yr; } return ""; // unparseable -> caller flags } function pad2(n: number): string { return n < 10 ? "0" + n : String(n); } // Day checked against the month (02/30 and 04/31 are rejected; leap years honored). function isRealDate(yr: number, mo: number, da: number): boolean { if (mo < 1 || mo > 12 || da < 1) return false; const d = new Date(Date.UTC(yr, mo - 1, da)); return d.getUTCMonth() === mo - 1 && d.getUTCDate() === da; } // ---- 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 }[], pivots: { header: string, role: string }[], srcRows: string[] } { const splits: { header: string, into: string }[] = []; const pivots: { header: string, role: string }[] = []; if (rows0.length === 0) return { headers: headers0, rows: rows0, splits: splits, pivots: pivots, srcRows: [] }; let headers = headers0.slice(); let rows = rows0.map(r => r.slice()); // RAW DATA row number(s) behind each row (header = row 1), kept in step with // rows through the collapse and explode stages. let srcRows: number[][] = rows0.map((_r, i) => [i + 2]); // ---- 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 cell = (row: string[], i: number): string => (i < 0 || row[i] == null) ? "" : String(row[i]).trim(); 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 ""; }; let productCol = findCol(["PRODUCT", "COVERAGE", "COVERAGE TYPE", "PLAN TYPE", "BENEFIT", "BENEFIT TYPE", "LINE OF COVERAGE", "PRODUCT LINE", "COVERAGE LINE", "PRODUCT TYPE", "COVERAGE NAME", "BENEFIT PLAN TYPE", "LINE OF BUSINESS", "LOB"]); // Header not recognized: find the column whose values name the products // (Dental, Vision, Basic Life, STD...) on 80%+ of rows, with 2+ different products. if (productCol < 0) { for (let i = 0; i < headers.length && productCol < 0; i++) { if (aliasToFieldMap.has(norm[i])) continue; let filled = 0, hits = 0; const kinds = new Set(); for (const row of rows) { const v = cell(row, i); if (v === "") continue; filled++; const k = productKey(v); if (k !== "") { hits++; kinds.add(k); } } if (filled > 0 && hits / filled >= 0.8 && kinds.size >= 2) { productCol = i; addLog(`Product column found by its values: '${headers[i]}' (${Array.from(kinds).join(", ")}).`); } } } 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 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) || /\bTIER\b/.test(h) || 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" }); else if (productCol >= 0 && /AMOUNT|\bAMT\b|VOLUME|BENEFIT/.test(h)) genericCols.push({ col: i, concept: "AMOUNT" }); else if (productCol >= 0 && /PREMIUM|\bRATE\b|COST|CONTRIBUTION|DEDUCTION|PER PAY/.test(h)) genericCols.push({ col: i, concept: "COST" }); } // Vertical census: a column whose values are tiers (Employee Only, Family, Waived...) // is the tier column whatever its header says (Election, Option, Enrollment Level...). if (productCol >= 0) { const looksLikeTiers = (i: number): boolean => { let filled = 0, hits = 0; for (const row of rows) { const v = cell(row, i); if (v === "") continue; filled++; if (normalizeTier(v) !== "") hits++; } return filled > 0 && hits / filled >= 0.8; }; for (let i = 0; i < norm.length; i++) { if (i === productCol || norm[i] === "" || /DENTAL|VISION|MEDICAL|LIFE|\bSTD\b|\bLTD\b|DISABILITY/.test(norm[i])) continue; const g = genericCols.filter(x => x.col === i)[0]; if (g && g.concept === "TIER") continue; if (g && g.concept !== "PLAN") continue; if (!g && aliasToFieldMap.has(norm[i])) continue; // a real field (Relationship, Gender...) if (!looksLikeTiers(i)) continue; if (g) g.concept = "TIER"; else genericCols.push({ col: i, concept: "TIER" }); addLog(`Tier column found by its values: '${headers[i]}'.`); } } const ROUTING: { [c: string]: { [p: string]: string } } = { // A tier on a Basic Life / STD / LTD row means enrolled (normalizeEnrollWaive reads it as EE / Waive). TIER: { DENTAL: "Dental Cov Level", VISION: "Vision Cov Level", BASIC_LIFE: "Basic Life Cov Level", STD: "Short-Term Disability Cov Level", LTD: "Long-Term Disability Cov Level" }, PLAN: { DENTAL: "Dental Plan Elected", VISION: "Vision Plan Election" }, CLASS: { BASIC_LIFE: "Life/AD&D Class", STD: "STD Class", LTD: "LTD Class" }, AMOUNT: { VOL_LIFE: "Employee Voluntary Life Flat Amount" } // on a spouse/child row the roll-up moves it to Spouse/Dependent }; 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; const unrouted = new Map(); // product value -> generic values with no output field for (const row of rows) { const p = productKey(cell(row, productCol)); for (const gc of genericCols) { const map = ROUTING[gc.concept]; const field = map && p ? map[p] : ""; const val = cell(row, gc.col); if (val === "" || gc.concept === "COST") continue; // per-product cost/premium: not used if (field) { const ti = ensureCol(field); if (cell(row, ti) === "") { row[ti] = val; routed++; } } else { const pv = cell(row, productCol) || "(blank product)"; unrouted.set(pv, (unrouted.get(pv) || 0) + 1); } } } // Voluntary life, medical and unknown products have no column here: say so, never drop quietly. unrouted.forEach((n, pv) => { addIssue("INFO", 0, "", "", pv, `${n} value(s) in the shared tier/amount column(s) for '${pv}' rows have no output column`, "Not used on the output; the values stay on RAW DATA"); }); // 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). for (const gc of genericCols) { pivots.push({ header: headers[gc.col], role: gc.concept === "COST" ? "Per-product cost/premium on a vertical census; not used" : `Shared ${gc.concept.toLowerCase()} column on a vertical census; values moved into each coverage's own column` }); headers[gc.col] = ""; } if (routed > 0) { addLog(`Product routing: moved ${routed} generic value(s) into product-specific columns via '${headers0[productCol]}'.`); } } if (productCol >= 0 && headers[productCol] !== "") { let filled = 0, hits = 0; for (const row of rows) { const v = cell(row, productCol); if (v !== "") { filled++; if (productKey(v) !== "") hits++; } } if (filled > 0 && hits * 2 >= filled) { pivots.push({ header: headers[productCol], role: "Coverage column on a vertical census; rows were combined into one line per person" }); headers[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 groupSrc = new Map(); const order: string[] = []; for (let ri = 0; ri < rows.length; ri++) { const r = rows[ri]; const k = personKey(r); if (!groups.has(k)) { groups.set(k, []); groupSrc.set(k, []); order.push(k); } (groups.get(k) as string[][]).push(r); (groupSrc.get(k) as number[]).push(...srcRows[ri]); } let collapsed = 0; const merged: string[][] = []; const mergedSrc: number[][] = []; // 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[][]; mergedSrc.push(groupSrc.get(k) as number[]); 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; srcRows = mergedSrc; 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[][] = []; const newSrc: number[][] = []; let depCount = 0; const depIdxs = Array.from(depGroups.keys()).sort((a, b) => a - b); for (let ri = 0; ri < rows.length; ri++) { const r = rows[ri]; 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); newSrc.push(srcRows[ri]); 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); newSrc.push(srcRows[ri]); // a dependent comes from its employee's source row } } headers = newHeaders; rows = newRows; srcRows = newSrc; if (depCount > 0) addLog(`Dependent-column explode: created ${depCount} dependent row(s) from ${depGroups.size} dependent slot(s).`); } return { headers, rows, splits, pivots, srcRows: srcRows.map(a => a.join(", ")) }; } }