MEK-Charge-Reconciliation/process_steps.md

# MEK Charge reconciliation process

Sites:
- Waystar   (ZirMed) -> https://login.zirmed.com/ui
- Aquilaehr          -> https://platform.aquilaehr.com/

Run: `python mek_recon.py` -> both sites at the same time, each in its own Chrome window
(own profile: .chrome_profile_waystar / .chrome_profile_aquilaehr).
One site only: `python mek_recon.py Waystar` or `python mek_recon.py Aquilaehr`.
Close processing/ and output/ files in Excel/WPS before running (the script stops and lists them otherwise).
Full run verified 10/27/2026: both sites 10/10 locations, reports in output/ (~1 min).

Logins come from .env (values never stored in code):
- Waystar: WAYSTAR_USERNAME, WAYSTAR_PASSWORD, WAYSTAR_ANSWER_CAR, WAYSTAR_ANSWER_JOB
  -> fills #loginName / #password -> "Log in" -> security question page: answer by keyword
  ("first car" / "first job") -> click "Verify"
- Aquilaehr: AQUILAEHR_USERNAME, AQUILAEHR_PASSWORD

## Web app / hosting
- `python app.py` -> http://127.0.0.1:8000 (sign in with APP_PASSWORD, Run, per-location / All Locations / ZIP downloads)
- Repo: https://giteanew.ecareindia.com/automation-tools/MEK-Charge-Reconciliation (main)
- Dokploy: project "MEK Charge Reconciliation", app mek-charge-reconciliation-jhyjop (Dockerfile, port 8000),
  env vars hold all logins + APP_PASSWORD + SECRET_KEY + COOKIE_SECURE=1
- Domain: https://mek-charge-reconciliation.api.ecareindia.com
  DNS -> front proxy 10.200.0.55 -> Dokploy Traefik 10.200.0.15. The front proxy needs this host added
  (status 10/27/2026: Dokploy route OK, front proxy returned 404 for this host and 502 for the other apps)

## Locations (run the whole process for each, on BOTH sites)
- ~~Adelman Health~~ (skipped)
- Alpha Well-Being LLC
- ARC Wound Care
- Cynthia DM Concierge NP Services LLC
- Healing Bridge
- Joy Solutions LLC
- Lunas Home NP LLC
- Stem Regen NJ
- Sunwave Woundcare
- WoundDocs LLC
- Wound Pros of FL

## Waystar steps
1. Open https://login.zirmed.com/ui and log in
2. Select location: top "Account" box -> type location name
   -> pick from dropdown (e.g. "Healing Bridge (313858)")
   -> lands on "Success - You have switched to the following account" page
3. Professional Claims > click "Claims" tab
   -> https://claims.zirmed.com/Claims/Listing/Index?appid=1
4. Left panel filters:
   - Status (select#SearchCriteria_Status) -> "All"   (default was "All Rejected")
   - Transaction Date (select#SearchCriteria_TransDate) -> "All"   (default "Last 90 Days"; "All" = last 2 years)
5. Click "Search"
6. Click "Download CSV" (#downloadCsvBtn, "Excel-Friendly CSV" ticked)
   -> save as processing/Waystar/<Location>.csv
   CSV layout: 7 preamble lines (Generated by / Account: / Filters...), then header row:
   Trans Date, Patient Name, Claim Number, Claim ID, Instance ID, Payer Name, Payer ID, From Date, To Date,
   Rendering Provider, Charges, Status, Sequence, Source, Hidden, Remit #s, Total Remit Amount, Last Note, Last Event Message
7. Repeat 2-6 for every location.

## Aquilaehr steps
1. Open https://platform.aquilaehr.com/  (login page: #normal_login_email, #normal_login_password, "Login")
2. Sidebar "Reports" -> https://platform.aquilaehr.com/#/reports/clinical
3. Click "Encounter & Claim Monitor" tab
4. Top practice dropdown (antd select, virtual list - scroll to find) -> pick location
   -> URL gets ?practiceId=<guid>; page reloads data. Date range left empty = all dates.
5. Wait until the data call for the NEW practice finishes
   (GET aquila-api-prod-xv7xr.ondigitalocean.app/api/v1/reports/clinical_notes?practice_id=<new id>),
   and the dropdown shows the chosen practice. (Exporting earlier gives the PREVIOUS practice's data.)
   Then click "Export CSV" (it's <a download="e&c-monitor.csv" href=blob:...>, contains ALL rows, not just page 1)
   -> save as processing/Aquilaehr/<Waystar location name>.csv
   Columns: Account #, Patient, DOB, Visit Date, Provider, Location, Visit Reason, Room, Template,
   Plan Name, Claim ID, Note Status, Claim Status
6. Repeat 4-5 for every location.

Waystar name -> Aquilaehr dropdown name: see LOCATIONS in mek_recon.py
- Adelman Health: SKIPPED on both sites (no Aquilaehr practice)
- Cynthia DM Concierge NP Services LLC -> "Moona Concierge Nurse Practitioner Services LLC"
- WoundDocs LLC -> "WOUNDOCS LLC" + "WOUNDOCS LLC - 2", both exported and merged into one file

## Consolidation
Match key (verified 10/27/2026, 3938 of 4027 Waystar claims found in Aquilaehr = 97.8%):
  Waystar "Claim Number" = ="<Aquilaehr Claim ID> <Aquilaehr Account #>"  e.g. ="42756 35055"
  -> first token of Waystar Claim Number == Aquilaehr "Claim ID"
Parsing notes: Waystar CSV = skip 7 preamble lines, rows end with a trailing comma (read with index_col=False),
values wrapped like ="..." (strip them). Aquilaehr CSV: one row per note/template, so a Claim ID can repeat.

Report (consolidate.py, runs automatically at the end of mek_recon.py, or alone: `python consolidate.py`):
- Match on Patient Name + Date of Service, per location:
  Aquilaehr Patient + Visit Date  vs  Waystar Patient Name + From Date
  = same DOS, same first name, at least one last-name word in common
  (handles "Mary Lopez Garcia" vs "LOPEZ, MARY", extra spaces, Jr/Sr)
- Aquilaehr rows grouped to one row per visit (Account # + Patient + Visit Date);
  Waystar rows grouped per Patient Name + From Date (Charges summed)
- Output: output/<Location>.xlsx with 2 sheets
  - Summary: Aquilaehr visits, Waystar visits, Matched, Missing in Waystar, Missing in Aquilaehr, Duplicate in Waystar
  - Reconciliation: one row per visit, Result column:
    Missing in Waystar   = visit in Aquilaehr, no Waystar claim for that patient + DOS
    Missing in Aquilaehr = Waystar claim, no Aquilaehr visit for that patient + DOS
    Duplicate in Waystar = 2nd Waystar claim for an already-matched patient + DOS (e.g. two name spellings)
    Matched
  - Note column: when the same Claim ID exists on the other side under a different date