import openpyxl, json, sys from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import FormulaRule from openpyxl.styles import PatternFill from datetime import datetime, date LIVE = '/tmp/Reactivations_live_now.xlsx' # re-downloaded fresh just before run AUTH = '/tmp/Reactivations_final2.xlsx' OUT = '/tmp/Reactivations_backfilled.xlsx' contacts = json.load(open('/tmp/s2_contacts.json')) s1_missing = json.load(open('/tmp/s1_missing_rows.json')) live_status = json.load(open('/tmp/live_status.json')) # captured earlier; will re-capture from fresh live below S1 = 'New No-Show-Cancel No-Followup' S2 = 'Active No-Followup' RM = 'README' wb = openpyxl.load_workbook(LIVE) ws1 = wb[S1]; ws2 = wb[S2]; rm = wb[RM] # ---- capture status values from the CURRENT live file (authoritative for preservation) ---- def capture_status(ws, idcol, statuscol): m = {} for r in range(2, ws.max_row+1): pid = ws.cell(r, idcol).value if pid is None or str(pid).strip()=='': continue sv = ws.cell(r, statuscol).value if sv and str(sv).strip(): m[str(pid).strip()] = str(sv).strip() return m s1_status = capture_status(ws1, 11, 12) s2_status = capture_status(ws2, 7, 8) # ================= SHEET 1: append 3 missing rows, keep most-recent-first ================= # collect existing rows as list of [12 vals] def read_rows(ws, ncol): out = [] for r in range(2, ws.max_row+1): pid_present = any(ws.cell(r,c).value not in (None,'') for c in range(1,ncol+1)) if not pid_present: continue out.append([ws.cell(r,c).value for c in range(1, ncol+1)]) return out s1_rows = read_rows(ws1, 12) existing_ids = {str(row[10]).strip() for row in s1_rows if row[10] is not None} for pid, row in s1_missing.items(): if pid not in existing_ids: s1_rows.append(row[:12]) # sort most-recent-first by Last Appt Date (col index 3 -> 0-based 3) def parse_date(v): if isinstance(v, (datetime, date)): return v if isinstance(v,datetime) else datetime(v.year,v.month,v.day) if v is None: return datetime.min s = str(v).strip() for fmt in ('%Y-%m-%d','%m/%d/%Y','%Y-%m-%dT%H:%M:%S','%m/%d/%Y %H:%M:%S'): try: return datetime.strptime(s[:19], fmt) except: pass return datetime.min s1_rows.sort(key=lambda r: parse_date(r[3]), reverse=True) # merge preserved status into col 12 (index 11) for row in s1_rows: pid = str(row[10]).strip() if row[10] is not None else '' if pid in s1_status: row[11] = s1_status[pid] # clear and rewrite S1 data area for r in range(2, ws1.max_row+1): for c in range(1, 13): ws1.cell(r,c).value = None for i, row in enumerate(s1_rows): for c in range(12): ws1.cell(2+i, c+1).value = row[c] n1 = len(s1_rows) # ================= SHEET 2: fill contacts, re-sort most-recent-first ================= s2_rows = read_rows(ws2, 8) # fill phone/email from contacts (col2 phone, col3 email; id col7) filled_p = filled_e = 0 for row in s2_rows: pid = str(row[6]).strip() if row[6] is not None else '' c = contacts.get(pid, {}) if c.get('phone') and not (row[1] and str(row[1]).strip()): row[1] = c['phone'] if c.get('email') and not (row[2] and str(row[2]).strip()): row[2] = c['email'] # sort most-recent-first by Last Kept Appt Date (col D -> index 3) s2_rows.sort(key=lambda r: parse_date(r[3]), reverse=True) # merge preserved status (col8 index7) for row in s2_rows: pid = str(row[6]).strip() if row[6] is not None else '' if pid in s2_status: row[7] = s2_status[pid] # clear + rewrite for r in range(2, ws2.max_row+1): for c in range(1, 9): ws2.cell(r,c).value = None for i, row in enumerate(s2_rows): for c in range(8): ws2.cell(2+i, c+1).value = row[c] n2 = len(s2_rows) ph = sum(1 for r in s2_rows if r[1] and str(r[1]).strip()) em = sum(1 for r in s2_rows if r[2] and str(r[2]).strip()) # ================= Re-apply Data Validation + Conditional Formatting ================= def reapply(ws, statuscol_letter, data_last_row, full_last_col_letter): # clear existing DV ws.data_validations.dataValidation = [] dv = DataValidation(type='list', formula1='"Contacted,Declined,Reactivated"', allow_blank=True) dv.sqref = f'{statuscol_letter}2:{statuscol_letter}{data_last_row}' ws.add_data_validation(dv) # clear existing CF then re-add ws.conditional_formatting._cf_rules.clear() rng = f'A2:{full_last_col_letter}{data_last_row}' specs = [('Contacted','FFFF00'), ('Declined','FF0000'), ('Reactivated','00B050')] for val, color in specs: fill = PatternFill(start_color=color, end_color=color, fill_type='solid') ws.conditional_formatting.add(rng, FormulaRule(formula=[f'${statuscol_letter}2="{val}"'], fill=fill)) # use generous ranges matching authoritative (S1 L2:L29 / A2:L29 ; S2 H2:H109 / A2:H109) reapply(ws1, 'L', max(n1+1, 29), 'L') reapply(ws2, 'H', max(n2+1, 109), 'H') # ================= README update ================= def set_readme(label, value): for r in range(1, rm.max_row+1): if rm.cell(r,1).value and str(rm.cell(r,1).value).strip()==label: rm.cell(r,2).value = value return True return False # update contact coverage + row count + sort note for r in range(1, rm.max_row+1): a = rm.cell(r,1).value b = rm.cell(r,2).value if a=='Contact coverage' and b and 'phone 21/108' in str(b): rm.cell(r,2).value = f'phone {ph}/108, email {em}/108 (backfilled via getdemographic)' if a=='Contact coverage' and b and '/27' in str(b): rm.cell(r,2).value = 'phone 28/28, email 28/28' if a=='Row count' and str(b).strip() in ('27','25'): rm.cell(r,2).value = '28' set_readme('Sheet 2 status', f'POPULATED {n2} rows; contacts backfilled (phone {ph}, email {em}) via AMD getdemographic; re-sorted most-recent-first by Last Kept Appt Date.') wb.save(OUT) print(f'BUILT {OUT}') print(f'S1 rows={n1} | S2 rows={n2} phone={ph} email={em}') # verify S2 row2 is most-recent print('S2 row2 date:', ws2.cell(2,4).value, '| S2 last date:', ws2.cell(1+n2,4).value) print('S1 status preserved:', len(s1_status), '| S2 status preserved:', len(s2_status))