from openpyxl import load_workbook from datetime import datetime, date, timezone from pathlib import Path import json, re, sys SOURCE = Path(sys.argv[1]) DEST = Path(sys.argv[2]) AS_OF = date(2026, 9, 15) ALIASES = { 'FUMUGATION':'Fumigation','FIRE SAFETY MARSHELS':'Fire Marshal & First Aider Training', 'FOOD HANDLERS':'Food Handlers / Medical Fitness','MEDICAL FITNESS CERTIFICATE':'Medical Fitness Certificate', 'HEALTH APPROVAL':'Health Approval / Operational Permit','WATER ANALYSIS':'Water Analysis','BREAD ANALYSIS':'Bread Analysis', 'FIRE EXTINGUISHER':'Fire Extinguisher Certification','FIRE SAFETY CERTIFICATE':'Fire Safety Certificate','FIRE SAFETY (':'Fire Safety Certificate', 'LOCAL GOVERONMENT':'Local Government Facility Permit','LOCAL GOVERNMENT':'Local Government Facility Permit', 'EIA':'Environmental Impact Assessment','EAR':'Environmental Audit Report','EMP':'Environmental Management Plan','NESREA EMP':'Environmental Management Plan', 'SON MANCAP':'SON MANCAP','SON MACAP':'SON MANCAP','NAFDAC - GHP':'NAFDAC Good Hygiene Practice','NAFDAC - GLOBAL':'NAFDAC Global Listing', 'NAFDAC - NUMBER':'NAFDAC Registration / Approval','FIRE SERVICE CHARGE':'Fire Service Charge','RIWAMA':'RIWAMA Compliance Letter', 'ECONOMY DEVELOPMENT LEVY':'Ecological / Economic Development Levy','OYO STATE MINISTRY':'Oyo State Health Operational Permit', 'EDC (':'Environmental Development Charge','ROUTINE TANKWASHING':'Tank Washing & Water Maintenance','STORES (SON)':'SON Store Reference' } NA = {'N/A','NA','NOT APPLICABLE'} MISSING = {'NIL','NILL','NONE','N',''} PROCESS = {'IN PROCESS','IN PROGRESS','PROCESSING','ONGOING'} MONTHS={'jan':1,'january':1,'feb':2,'february':2,'febuary':2,'mar':3,'march':3,'apr':4,'april':4,'may':5,'jun':6,'june':6,'jul':7,'july':7,'aug':8,'august':8,'sep':9,'sept':9,'september':9,'oct':10,'october':10,'nov':11,'november':11,'dec':12,'december':12} def norm(v): return re.sub(r'\s+',' ',str(v or '').replace('\xa0',' ')).strip() def sentinel(v): return norm(v).upper().strip(' .') def parse_date(v): if isinstance(v, datetime): return v.date() if isinstance(v, date): return v s=norm(v) if not s or sentinel(s) in NA|MISSING|PROCESS: return None s=re.sub(r'(?i)\b(monday|tuesday|wednesday|thursday|friday|saturday|sunday)\b[,]?','',s) s=re.sub(r'(?i)(\d)(st|nd|rd|th)',r'\1',s).replace(',', ' ') s=re.sub(r'\s+',' ',s).strip() for fmt in ('%d/%m/%Y','%d/%m/%y','%Y-%m-%d','%d %B %Y','%d %b %Y'): try: d=datetime.strptime(s,fmt).date() return d if 2000 <= d.year <= 2100 else None except ValueError: pass m=re.fullmatch(r'(\d{1,2})\s+([A-Za-z]+)\s*(\d{4})',s) if m and m.group(2).lower() in MONTHS: try:return date(int(m.group(3)),MONTHS[m.group(2).lower()],int(m.group(1))) except ValueError:return None return None def req_name(raw): u=norm(raw).upper() for k,v in ALIASES.items(): if k in u:return v return re.sub(r'\s*\([^)]*\)\s*',' ',norm(raw)).strip().title() def iso(d): return d.isoformat() if d else None def classify(issue_raw, expiry_raw): i_s,e_s=sentinel(issue_raw),sentinel(expiry_raw) if i_s in NA or e_s in NA:return 'not_applicable',None if i_s in PROCESS or e_s in PROCESS:return 'in_process',None expiry=parse_date(expiry_raw); issue=parse_date(issue_raw) if expiry: days=(expiry-AS_OF).days if days<0:return 'expired',days if days<=30:return 'expiring_30',days if days<=60:return 'expiring_60',days if days<=90:return 'expiring_90',days return 'compliant',days if e_s and e_s not in MISSING:return 'invalid_date',None if issue: return 'missing_expiry',None if i_s and i_s not in MISSING:return 'invalid_date',None return 'missing_evidence',None wb=load_workbook(SOURCE,data_only=True,read_only=True) records=[]; stores=[]; quality=[]; requirements=set() for ws in wb.worksheets: max_row=min(ws.max_row,200); max_col=min(ws.max_column,150) header_row=None for r in range(1,min(max_row,12)+1): vals=[sentinel(ws.cell(r,c).value) for c in range(1,min(max_col,8)+1)] if 'STORE LOCATION' in vals:header_row=r;break if not header_row: quality.append({'region':ws.title,'type':'structure','detail':'Store location header not found'});continue sub_row=header_row+1 pairs=[] for c in range(4,max_col+1): raw=norm(ws.cell(header_row,c).value) if not raw:continue # A requirement begins where the next row says issued date. Allow paired cells even where merges were lost. if 'ISSUED' not in sentinel(ws.cell(sub_row,c).value):continue expiry_col=c+1 if c+1<=max_col and 'EXPIRED' in sentinel(ws.cell(sub_row,c+1).value) else None if not expiry_col:continue pairs.append((c,expiry_col,req_name(raw),raw)); requirements.add(req_name(raw)) region=ws.title.replace(' REGION','').title().replace('South-South','South-South') inherited_subregion='' for r in range(sub_row+1,max_row+1): location=norm(ws.cell(r,2).value); sn=ws.cell(r,1).value if not location or sentinel(location) in {'STORE LOCATION','TOTAL'}:continue # Require a store-like row: a serial or at least one populated regulatory cell. populated=sum(1 for c,e,_,_ in pairs if norm(ws.cell(r,c).value) or norm(ws.cell(r,e).value)) if not populated:continue sub=norm(ws.cell(r,3).value) if sub:inherited_subregion=sub store_id=f'{ws.title}:{r}' stores.append({'id':store_id,'region':region,'sheet':ws.title,'subregion':inherited_subregion,'location':location,'sourceRow':r}) for c,e,req,raw_header in pairs: issue_raw=ws.cell(r,c).value; expiry_raw=ws.cell(r,e).value status,days=classify(issue_raw,expiry_raw) issue=parse_date(issue_raw); expiry=parse_date(expiry_raw) anomaly=[] if issue and expiry and expiry