sahil-12kumar/IL_CMS_Tools
1
1r"""2IL CMS Tagger — Excel template generator3----------------------------------------4Standalone tool. Opens the IL CMS Create/Edit Question form with Playwright5(reusing a saved CMS session) and writes a ready-to-fill tagging template6(tagging_template.xlsx) with one column for EVERY field on the form, across all7three tabs:8 9 Learning Outcomes : Subject, Chapter, Topic, Subtopic, Minitopic, Micro topic10 Exam Details : TOC, Grade11 Other Details : Objective, Difficulty Level, Bloom Taxonomy, Relevance,12 Concept Level, Source, Syllabus, Author, Tags,13 Exam, Year, Month, Date14 15For each flat-list dropdown the live options are scraped and turned into an16in-Excel dropdown (data validation). Cascade dropdowns (Chapter / Topic /17Subtopic / Minitopic / Micro topic — options depend on the prior pick), plus18multi-select / date / free-text fields (Author, Tags, Date), are left as plain19columns so you can type freely.20 21Usage22 python make_template.py # auto-pick newest saved session23 python make_template.py --qid ILQ-2344120 # open this question's Edit form24 python make_template.py --state path\to\state.json --out my_template.xlsx25 python make_template.py --no-scrape # skip CMS, use built-in defaults26 python make_template.py --headed # watch the browser (default: headed)27 28Why a question id helps: the surest way to reveal the dropdowns is to open an29existing question's "Edit Details" form. Pass any real question id you can see30in the bank with --qid. Without it the script tries a "Create Question" button,31and if that isn't found it still writes the template from the built-in lists.32 33Scraped values always win; the built-in lists only fill fields that couldn't be34read (e.g. a tab that failed to open). Re-run whenever the CMS taxonomy changes.35"""36 37import argparse38import glob39import os40import sys41import threading42 43from openpyxl import Workbook44from openpyxl.utils import get_column_letter45from openpyxl.worksheet.datavalidation import DataValidation46 47import il_endpoints48 49BASE_DIR = os.path.dirname(os.path.abspath(__file__))50DATA_DIR = os.path.join(os.path.dirname(BASE_DIR), "database")51STATE_FILE = os.path.join(BASE_DIR, "state.json")52STATE_DIR = os.path.join(DATA_DIR, "tagger_sessions")53URL = il_endpoints.QUESTION_BANK_URL54 55# Subject → Chapter → Topic → Sub-Topic taxonomy used to build the cascading56# (dependent) Excel dropdowns. Two offline sources are merged so all five subjects57# have a full cascade:58# * JEE TOC CSV → Physics / Mathematics / Chemistry59# * Biology xlsx → Botany / Zoology (cols SUBJECTNAME/CHAPTERNAME/TOPICNAME/SUBTOPICNAME)60TAXONOMY_CSV = r"C:\Users\admin\Downloads\JEE MAIN TOC (1).csv"61TAX_ENCODING = "cp1252"62BIO_TOC_XLSX = r"C:\Users\admin\Downloads\Biology TOC.xlsx"63SUBJECT_LIST = ["Physics", "Chemistry", "Mathematics", "Botany", "Zoology"]64 65# ── Template layout ───────────────────────────────────────────────────────────66# Every field on the CMS Create-Question form, in tab order, becomes a column.67# mode "scrape" : a flat-list ng-select — we read its options and add an68# in-Excel dropdown (data validation).69# mode "free" : cascade / multi-select / date / free-text — plain column, no70# dropdown (its options depend on prior picks or aren't a list).71# tab : which CMS tab the field lives on (None = no tab switch)72# placeholders : ng-select placeholder texts to try when opening it73# label : the on-screen field label (used as a locator fallback)74# for_attr : label[for=] fallback (works when a value is already selected)75# default : built-in list used only when scraping can't read the field76TRACKING_COLS = ["Status", "Run Details", "Attempts", "Last Worker", "Last Updated"]77 78FIELDS = [79 # ── Learning Outcomes ──────────────────────────────────────────────────────80 {"col": "subject", "label": "Subject", "tab": "Learning Outcomes", "mode": "scrape",81 "placeholders": ["Select Subject"], "for_attr": "Subject",82 "default": ["Physics", "Chemistry", "Biology", "Mathematics"]},83 {"col": "chapter", "label": "Chapter", "tab": "Learning Outcomes", "mode": "free"},84 {"col": "topic", "label": "Topic", "tab": "Learning Outcomes", "mode": "free"},85 {"col": "subtopic", "label": "Subtopic", "tab": "Learning Outcomes", "mode": "free"},86 {"col": "minitopic", "label": "Minitopic", "tab": "Learning Outcomes", "mode": "free"},87 {"col": "micro_topic", "label": "Micro topic", "tab": "Learning Outcomes", "mode": "free"},88 # ── Exam Details ───────────────────────────────────────────────────────────89 {"col": "toc", "label": "TOC", "tab": "Exam Details", "mode": "scrape",90 "placeholders": ["Select TOC"]},91 {"col": "grade", "label": "Grade", "tab": "Exam Details", "mode": "scrape",92 "placeholders": ["Select Grade"], "default": ["Grade 11", "Grade 12"]},93 # ── Other Details ──────────────────────────────────────────────────────────94 {"col": "objective", "label": "Objective", "tab": "Other Details", "mode": "scrape",95 "placeholders": ["Select Objective"], "for_attr": "objective",96 "default": ["Formula Based", "Theory", "Multi-Concept", "Real-Life Example", "Extrapolation"]},97 {"col": "difficulty", "label": "Difficulty Level", "tab": "Other Details", "mode": "scrape",98 "placeholders": ["Select Difficulty Level", "Select Difficulty"], "for_attr": "difficultyLevel",99 "default": ["easy", "moderate", "difficult", "very difficult"]},100 {"col": "bloom_taxonomy", "label": "Bloom Taxonomy", "tab": "Other Details", "mode": "scrape",101 "placeholders": ["Select Bloom Taxonomy", "Select Bloom"], "for_attr": "bloomTaxonomy",102 "default": ["Knowledge", "Understanding (Comprehension)", "Application",103 "Analysis (compare, contrast, categorize, identify)"]},104 {"col": "relevance", "label": "Relevence", "tab": "Other Details", "mode": "scrape",105 "placeholders": ["Select Relevence", "Select Relevance"], "for_attr": "relevence", # CMS typo106 "default": ["Relevant to NEET"]},107 {"col": "concept_level", "label": "Concept Level", "tab": "Other Details", "mode": "scrape",108 "placeholders": ["Select Concept Level"]},109 {"col": "source", "label": "Source", "tab": "Other Details", "mode": "scrape",110 "placeholders": ["Select Source"]},111 {"col": "syllabus", "label": "Syllabus", "tab": "Other Details", "mode": "scrape",112 "placeholders": ["Select Syllabus"]},113 {"col": "author", "label": "Author", "tab": "Other Details", "mode": "free"},114 {"col": "tags", "label": "Tags", "tab": "Other Details", "mode": "free"},115 {"col": "exam", "label": "Exam", "tab": "Other Details", "mode": "scrape",116 "placeholders": ["Select Exam"]},117 {"col": "year", "label": "Year", "tab": "Other Details", "mode": "scrape",118 "placeholders": ["Select Year"]},119 {"col": "month", "label": "Month", "tab": "Other Details", "mode": "scrape",120 "placeholders": ["Select Month"],121 "default": ["January", "February", "March", "April", "May", "June", "July",122 "August", "September", "October", "November", "December"]},123 {"col": "date", "label": "Date", "tab": "Other Details", "mode": "free"},124]125 126SCRAPE_FIELDS = [f for f in FIELDS if f["mode"] == "scrape"]127 128# Lists longer than this aren't turned into a dropdown (e.g. huge Author/Tag129# lists) — they'd bloat the file and overwhelm the picker; the column stays free.130MAX_DROPDOWN_OPTIONS = 500131 132COLUMN_ORDER = ["question_id"] + [f["col"] for f in FIELDS] + TRACKING_COLS133 134# Illustrative rows for the sample workbook. The Physics paths are real taxonomy135# entries so the cascade dropdowns show them as valid picks; the Botany row shows136# what a row looks like for a subject that has no offline cascade data yet.137EXAMPLE_ROWS = [138 {"question_id": "ILQ-2344120", "subject": "Physics", "chapter": "Motion in a Straight Line",139 "topic": "Kinematic equations for uniformly accelerated motion", "subtopic": "Motion in Horizontal Plane",140 "grade": "Grade 11", "objective": "Formula Based", "difficulty": "moderate",141 "bloom_taxonomy": "Application", "relevance": "Relevant to NEET", "Status": "unprocessed"},142 {"question_id": "ILQ-2344125", "subject": "Physics", "chapter": "Motion in a Straight Line",143 "topic": "Kinematic equations for uniformly accelerated motion", "subtopic": "Motion under gravity",144 "grade": "Grade 11", "objective": "Multi-Concept", "difficulty": "difficult",145 "bloom_taxonomy": "Application", "relevance": "Relevant to NEET", "Status": "unprocessed"},146 {"question_id": "ILQ-2344127", "subject": "Physics", "chapter": "Laws of Motion",147 "topic": "Newton's second law of motion", "subtopic": "Impulse & its Applications",148 "grade": "Grade 11", "objective": "Formula Based", "difficulty": "moderate",149 "bloom_taxonomy": "Application", "relevance": "Relevant to NEET", "Status": "unprocessed"},150 {"question_id": "ILQ-2344153", "subject": "Physics", "chapter": "Kinetic Theory",151 "topic": "Kinetic theory of an ideal gas", "subtopic": "RMS Speed",152 "grade": "Grade 11", "objective": "Theory", "difficulty": "moderate",153 "bloom_taxonomy": "Understanding (Comprehension)", "relevance": "Relevant to NEET", "Status": "unprocessed"},154 {"question_id": "ILQ-2400001", "subject": "Botany", "chapter": "Cell: The Unit of Life",155 "topic": "Cell theory and cell organelles", "subtopic": "Mitochondria",156 "grade": "Grade 11", "objective": "Theory", "difficulty": "easy",157 "bloom_taxonomy": "Analysis (compare, contrast, categorize, identify)",158 "relevance": "Relevant to NEET", "Status": "unprocessed"},159]160 161 162# ── Session discovery ─────────────────────────────────────────────────────────163 164def pick_state_file(explicit):165 """Resolve which Playwright storage_state to use."""166 if explicit:167 if not os.path.exists(explicit):168 sys.exit(f"State file not found: {explicit}")169 return explicit170 candidates = sorted(glob.glob(os.path.join(STATE_DIR, "state_*.json")),171 key=os.path.getmtime, reverse=True)172 if candidates:173 return candidates[0]174 if os.path.exists(STATE_FILE):175 return STATE_FILE176 return None177 178 179# ── Scraping ──────────────────────────────────────────────────────────────────180 181def scrape_options(state_file, qid, headless):182 """Open the CMS form and return {col: [options]} for every field we could read."""183 from playwright.sync_api import sync_playwright184 from playwright.sync_api import TimeoutError as PWTimeout185 186 QID_SELECTOR = "input[formcontrolname='searchById'], input[placeholder*='Question ID']"187 found = {}188 189 def wait(page, ms=800):190 page.wait_for_timeout(ms)191 192 def first_visible(locator):193 for i in range(locator.count()):194 item = locator.nth(i)195 try:196 if item.is_visible():197 return item198 except Exception:199 continue200 return None201 202 def maybe_click(page, texts, exact=False):203 for text in texts:204 for loc in [205 page.get_by_role("button", name=text, exact=exact),206 page.get_by_text(text, exact=exact),207 page.locator(f"text={text}"),208 page.locator(f"button:has-text('{text}')"),209 ]:210 target = first_visible(loc)211 if target:212 try:213 target.scroll_into_view_if_needed()214 target.click()215 return True216 except Exception:217 continue218 return False219 220 def open_dropdown(page, field):221 """Click a field's arrow so its ng-dropdown-panel opens. Returns True/False."""222 cands = []223 for ph in field.get("placeholders", []):224 cands += [225 page.locator(f"div[role='dialog'] ng-select:has(.ng-placeholder:text-is('{ph}')) .ng-arrow-wrapper"),226 page.locator(f"ng-select:has(.ng-placeholder:text-is('{ph}')) .ng-arrow-wrapper"),227 page.locator(f"ng-select[placeholder='{ph}'] .ng-arrow-wrapper"),228 ]229 if field.get("for_attr"):230 cands.append(page.locator(231 f".form-group:has(label[for='{field['for_attr']}']) .ng-arrow-wrapper"))232 if field.get("label"):233 # Last resort: the ng-select that follows a label carrying this text.234 cands.append(page.locator(235 f"label:has-text('{field['label']}')"236 ).locator("xpath=following::ng-select[1]//*[contains(@class,'ng-arrow-wrapper')]"))237 for cand in cands:238 target = first_visible(cand)239 if target is None:240 continue241 try:242 target.scroll_into_view_if_needed()243 target.click()244 wait(page, 700)245 if page.locator(".ng-dropdown-panel .ng-option").count() > 0:246 return True247 # opened but empty — close and try next candidate248 page.keyboard.press("Escape")249 except Exception:250 try:251 page.keyboard.press("Escape")252 except Exception:253 pass254 return False255 256 def read_open_options(page):257 opts = page.locator(".ng-dropdown-panel .ng-option")258 out, seen = [], set()259 for i in range(opts.count()):260 try:261 txt = opts.nth(i).inner_text().strip()262 except Exception:263 continue264 if not txt or txt.lower() == "no items found":265 continue266 if txt not in seen:267 seen.add(txt)268 out.append(txt)269 try:270 page.keyboard.press("Escape")271 except Exception:272 pass273 wait(page, 300)274 return out275 276 def open_form(page):277 """Reveal the Create/Edit Question form. Returns the page that has the form."""278 # Path A — open an existing question's Edit Details (most reliable).279 if qid:280 try:281 page.wait_for_load_state("networkidle", timeout=15000)282 except Exception:283 pass284 for _ in range(5):285 box = page.locator(QID_SELECTOR)286 if box.count() and box.first.is_visible():287 break288 maybe_click(page, ["Filter"])289 wait(page, 2000)290 box = page.locator(QID_SELECTOR)291 if box.count() and box.first.is_visible():292 try:293 box.first.fill(qid)294 except Exception:295 pass296 maybe_click(page, ["Save", "Apply"])297 wait(page, 3000)298 links = page.locator("a")299 for i in range(links.count()):300 link = links.nth(i)301 try:302 if qid not in link.inner_text():303 continue304 with page.context.expect_page(timeout=3000) as new_info:305 link.click()306 np = new_info.value307 np.wait_for_load_state()308 page = np309 break310 except PWTimeout:311 try:312 link.click()313 page.wait_for_load_state("networkidle", timeout=8000)314 except Exception:315 continue316 except Exception:317 continue318 wait(page, 3000)319 unapprove = first_visible(page.locator("button:has-text('Unapprove')"))320 if unapprove:321 try:322 unapprove.click()323 wait(page, 1000)324 ok = first_visible(page.locator(325 ".modal-content button.btn-success, .modal-content button:has-text('Submit')"))326 if ok:327 ok.click()328 wait(page, 3000)329 except Exception:330 pass331 for _ in range(2):332 if maybe_click(page, ["Edit Details"]):333 break334 wait(page, 2000)335 wait(page, 2500)336 return page337 # Path B — try a Create Question button.338 if maybe_click(page, ["Create Question", "Add Question", "Create", "Add"]):339 wait(page, 2500)340 return page341 342 with sync_playwright() as pw:343 browser = pw.chromium.launch(headless=headless, slow_mo=120)344 context = (browser.new_context(storage_state=state_file)345 if state_file and os.path.exists(state_file)346 else browser.new_context())347 page = context.new_page()348 page.goto(URL)349 wait(page, 3000)350 if "/login" in page.url:351 browser.close()352 raise RuntimeError("CMS session is not logged in (redirected to /login). "353 "Set up / refresh the CMS login first.")354 355 page = open_form(page)356 357 current_tab = None358 for field in SCRAPE_FIELDS:359 tab = field["tab"]360 if tab and tab != current_tab:361 maybe_click(page, [tab])362 wait(page, 900)363 current_tab = tab364 try:365 if open_dropdown(page, field):366 opts = read_open_options(page)367 if opts:368 found[field["col"]] = opts369 print(f" scraped {field['col']:<14} {len(opts)} option(s)")370 else:371 print(f" WARN {field['col']:<14} dropdown opened but no options")372 else:373 print(f" WARN {field['col']:<14} could not open dropdown")374 except Exception as e:375 print(f" WARN {field['col']:<14} {e}")376 377 browser.close()378 return found379 380 381# ── Other-Details options from the CMS /lookups API (no browser) ──────────────382 383# Which Other-Details column is filled from which /lookups code. Values are the384# exact option NAMES the tagger (push_tags) resolves back to UUIDs, so a pick from385# these dropdowns is guaranteed to tag cleanly.386LOOKUP_CODE_FOR_COL = {387 "objective": "OBJECTIVE", "bloom_taxonomy": "TAXONOMY", "relevance": "RELEVANCE",388 "concept_level": "TEST_LEVEL", "source": "SOURCE", "syllabus": "SYLLABUS",389}390 391 392# The lookup options are tenant/token-bound UUIDs, and /tag/prep + /tag/apply call393# lookups_options() back-to-back — fetch once per QB token per process.394_lookups_cache = {}395_lookups_lock = threading.Lock()396 397 398def lookups_options(state_file):399 """Return {col: [option names]} for the lookup-backed Other-Details fields,400 fetched from the QB /lookups API using a saved CMS session (no Playwright).401 Cached per QB token."""402 sys.path.insert(0, os.path.join(BASE_DIR, "ai_tagger"))403 import push_tags as pt404 h = pt.qb_headers_from_state(state_file)405 token = h["authorization"]406 with _lookups_lock:407 if token in _lookups_cache:408 return _lookups_cache[token]409 codes = sorted(set(LOOKUP_CODE_FOR_COL.values()))410 r = pt.SESSION.post(f"{pt.QB}/lookups", json=codes, headers=h, timeout=30)411 r.raise_for_status()412 by_code = {x["lookupcode"]: [v["name"] for v in x.get("lookupvalues", [])]413 for x in r.json().get("data", [])}414 out = {}415 for col, code in LOOKUP_CODE_FOR_COL.items():416 if by_code.get(code):417 out[col] = by_code[code]418 with _lookups_lock:419 _lookups_cache[token] = out420 return out421 422 423# ── live Subject→Chapter→Topic→Subtopic taxonomy from the CMS community API ────424 425# Grade-tree subject ROOT node ids (the "Master TOC" roots the tagger resolves426# against). Same map as push_tags.SUBJECT_ID.427CMS_SUBJECT_ROOT = {"physics": 1, "chemistry": 2, "mathematics": 3,428 "botany": 4, "zoology": 5}429CMS_GRADES = (11, 12)430 431# Exam → subject list mapping (used to filter cascading dropdowns by selected TOC).432# Key matches the pill data-toc attribute sent from the tagger page.433EXAM_SUBJECTS = {434 "JEE Main": ["Physics", "Chemistry", "Mathematics"],435 "JEE Advanced": ["Physics", "Chemistry", "Mathematics"],436 "NEET": ["Physics", "Chemistry", "Botany", "Zoology"],437 "EAPCET": ["Physics", "Chemistry", "Mathematics"],438 "KCET": ["Physics", "Chemistry", "Mathematics"],439}440 441 442def cms_taxonomy(subjects=None, grades=CMS_GRADES, log=print):443 """Build {subject: {chapter: {topic: [subtopics]}}} live from the community444 node API (extract_toc), instead of the offline CSV/xlsx. One bulk call per445 level (children carry parentNodeId, so a whole level resolves at once);446 grades are merged by name. Needs a community-valid token."""447 from collections import OrderedDict448 sys.path.insert(0, os.path.join(BASE_DIR, "ai_tagger"))449 import extract_toc as toc450 try:451 toc._ensure_session()452 # verify the saved token actually passes the community API; if not, log in453 if not toc._token_works_for_community(toc._SESSION.get("token")):454 toc.fetch_token_via_login(log_cb=lambda m: log(f" [community] {m}"))455 except Exception:456 toc.fetch_token_via_login(log_cb=lambda m: log(f" [community] {m}"))457 458 def bulk_children(parent_ids, grade, chunk=200):459 """[(child_id, child_name, parent_id)] for every parent, one call/chunk."""460 ids, out = [int(x) for x in parent_ids], []461 for i in range(0, len(ids), chunk):462 body = {"nodeIds": ids[i:i + chunk], "status": "active",463 "gradeIds": [int(grade)]}464 data = toc._community_get("/node/get_nodes_under_nodes_grades",465 json_body=body, method="POST")466 for n in data.get("data", {}).get("nodes_grades_list", []):467 out.append((n["nodeId"], n["nodeName"], n.get("parentNodeId")))468 return out469 470 subjects = subjects or SUBJECT_LIST471 tree = OrderedDict()472 for s in subjects:473 root = CMS_SUBJECT_ROOT.get(s.lower())474 if not root:475 log(f" {s:<12} no subject root id — skipped")476 continue477 stree = tree.setdefault(s, OrderedDict())478 for g in grades:479 chaps = bulk_children([root], g)480 chap_name = {cid: nm for cid, nm, _ in chaps}481 if not chap_name:482 continue483 tops = bulk_children(list(chap_name), g)484 top_name = {tid: nm for tid, nm, _ in tops}485 top_parent = {tid: p for tid, _, p in tops}486 subs = bulk_children(list(top_name), g) if top_name else []487 for _, cnm, _ in chaps:488 stree.setdefault(cnm, OrderedDict())489 for tid, tnm, cpar in tops:490 cnm = chap_name.get(cpar)491 if cnm:492 stree[cnm].setdefault(tnm, [])493 for _, snm, tpar in subs:494 cnm = chap_name.get(top_parent.get(tpar))495 tnm = top_name.get(tpar)496 if cnm and tnm:497 lst = stree[cnm].setdefault(tnm, [])498 if snm not in lst:499 lst.append(snm)500 log(f" {s:<12} G{g}: {len(chap_name)} chapters, "501 f"{len(top_name)} topics, {len(subs)} subtopics")502 return tree503 504 505# ── Excel writing ─────────────────────────────────────────────────────────────506 507def load_taxonomy(csv_path=TAXONOMY_CSV, encoding=TAX_ENCODING, subjects=SUBJECT_LIST):508 """Read the Subject/Chapter/Topic/Sub-Topic CSV into an ordered nested dict509 {subject: {chapter: {topic: [subtopics]}}}. Only the requested subjects are510 kept; a requested subject with no rows simply stays absent (empty cascade).511 Returns {} if the file is missing."""512 import csv513 from collections import OrderedDict514 if not csv_path or not os.path.exists(csv_path):515 return {}516 517 def cn(s):518 return " ".join(str(s or "").split()).strip()519 520 want = {s.lower(): s for s in subjects}521 tree = OrderedDict()522 with open(csv_path, encoding=encoding) as f:523 for row in csv.DictReader(f):524 subj = want.get(cn(row.get("Subject")).lower())525 if not subj:526 continue527 ch, tp, st = (cn(row.get("Chapter Name")),528 cn(row.get("Topic")), cn(row.get("Sub-Topic")))529 if not (ch and tp and st):530 continue531 chs = tree.setdefault(subj, OrderedDict())532 tps = chs.setdefault(ch, OrderedDict())533 sts = tps.setdefault(tp, [])534 if st not in sts:535 sts.append(st)536 537 _merge_bio_xlsx(tree, BIO_TOC_XLSX, want)538 return tree539 540 541def _merge_bio_xlsx(tree, xlsx_path, want):542 """Merge Botany/Zoology (and any other requested subject) from a Biology TOC543 .xlsx with columns SUBJECTNAME / CHAPTERNAME / TOPICNAME / SUBTOPICNAME into544 the in-place `tree`. `want` is {subject_lower: CanonicalName}. No-op if the545 file is missing."""546 from collections import OrderedDict547 if not xlsx_path or not os.path.exists(xlsx_path):548 return549 import openpyxl550 551 def cn(s):552 return " ".join(str(s or "").split()).strip()553 554 wb = openpyxl.load_workbook(xlsx_path, read_only=True)555 ws = wb.active556 rows = ws.iter_rows(values_only=True)557 header = [cn(h).lower() for h in next(rows)]558 idx = {name: header.index(name) for name in559 ("subjectname", "chaptername", "topicname", "subtopicname")560 if name in header}561 if len(idx) < 4:562 wb.close()563 return564 for r in rows:565 subj = want.get(cn(r[idx["subjectname"]]).lower())566 if not subj:567 continue568 ch, tp, st = (cn(r[idx["chaptername"]]), cn(r[idx["topicname"]]),569 cn(r[idx["subtopicname"]]))570 if not (ch and tp and st):571 continue572 chs = tree.setdefault(subj, OrderedDict())573 tps = chs.setdefault(ch, OrderedDict())574 sts = tps.setdefault(tp, [])575 if st not in sts:576 sts.append(st)577 wb.close()578 579 580def write_cascade(wb, ws, col_index, tree, subjects, rows):581 """Add dependent Subject→Chapter→Topic→Subtopic dropdowns.582 583 A hidden 'taxonomy' sheet holds, for each level, a *key* column and a *value*584 column, laid out so every parent's children sit in a contiguous block:585 586 C: subject D: chapter (one row per chapter)587 F: "subject|chapter" G: topic (one row per topic)588 I: "subject|chapter|topic" J: subtopic (one row per subtopic)589 590 Three workbook-scoped names then return *only* the current row's children via591 OFFSET(MATCH.., COUNTIF..). The relative row reference is written as row 1 so592 that, evaluated in data row r, it reads the parent cell on the same row r.593 No per-item names and no text sanitisation, so any characters are fine.594 """595 from openpyxl.workbook.defined_name import DefinedName596 597 tax = wb.create_sheet("taxonomy")598 tax.sheet_state = "hidden"599 tax["A1"] = "subject"600 tax["C1"] = "chap_key"; tax["D1"] = "chapter"601 tax["F1"] = "topic_key"; tax["G1"] = "topic"602 tax["I1"] = "sub_key"; tax["J1"] = "subtopic"603 604 for i, s in enumerate(subjects, start=2):605 tax.cell(row=i, column=1, value=s)606 607 cr = fr = ir = 2 # next write-row for chapter / topic / subtopic blocks608 counts = {"chapters": 0, "topics": 0, "subtopics": 0}609 for subj in subjects:610 for ch, topics in (tree.get(subj) or {}).items():611 tax.cell(row=cr, column=3, value=subj)612 tax.cell(row=cr, column=4, value=ch); cr += 1; counts["chapters"] += 1613 for tp, subtopics in topics.items():614 tax.cell(row=fr, column=6, value=f"{subj}|{ch}")615 tax.cell(row=fr, column=7, value=tp); fr += 1; counts["topics"] += 1616 for st in subtopics:617 tax.cell(row=ir, column=9, value=f"{subj}|{ch}|{tp}")618 tax.cell(row=ir, column=10, value=st); ir += 1619 counts["subtopics"] += 1620 621 L = {lvl: get_column_letter(col_index[lvl])622 for lvl in ("subject", "chapter", "topic", "subtopic")}623 subj_ref = f"Tagging!${L['subject']}1"624 chap_ref = f"Tagging!${L['chapter']}1"625 top_ref = f"Tagging!${L['topic']}1"626 t_key = f'{subj_ref}&"|"&{chap_ref}'627 s_key = f'{subj_ref}&"|"&{chap_ref}&"|"&{top_ref}'628 629 defs = {630 "ChapterList": (f"OFFSET(taxonomy!$D$2,MATCH({subj_ref},taxonomy!$C:$C,0)-2,0,"631 f"COUNTIF(taxonomy!$C:$C,{subj_ref}),1)"),632 "TopicList": (f"OFFSET(taxonomy!$G$2,MATCH({t_key},taxonomy!$F:$F,0)-2,0,"633 f"COUNTIF(taxonomy!$F:$F,{t_key}),1)"),634 "SubtopicList": (f"OFFSET(taxonomy!$J$2,MATCH({s_key},taxonomy!$I:$I,0)-2,0,"635 f"COUNTIF(taxonomy!$I:$I,{s_key}),1)"),636 }637 for name, formula in defs.items():638 wb.defined_names[name] = DefinedName(name, attr_text=formula)639 640 # Subject: fixed list of the requested subjects (short → safe inline).641 sub_dv = DataValidation(type="list", allow_blank=True, showDropDown=False,642 formula1='"' + ",".join(subjects) + '"')643 ws.add_data_validation(sub_dv)644 c = get_column_letter(col_index["subject"])645 sub_dv.add(f"{c}2:{c}{rows + 1}")646 647 # Chapter / Topic / Subtopic: dependent lists. Errors off, so an empty parent648 # just yields an empty dropdown instead of nagging the user.649 for col, name in (("chapter", "ChapterList"), ("topic", "TopicList"),650 ("subtopic", "SubtopicList")):651 dv = DataValidation(type="list", formula1=name, allow_blank=True,652 showDropDown=False, showErrorMessage=False)653 dv.prompt = f"Pick a {col} (depends on the level above)"654 dv.promptTitle = col655 ws.add_data_validation(dv)656 cl = get_column_letter(col_index[col])657 dv.add(f"{cl}2:{cl}{rows + 1}")658 659 return counts660 661 662def build_workbook(options, out_path, rows=1000, taxonomy=None, subjects=None,663 sample_rows=None):664 """Write the template with header columns + data-validation dropdowns.665 666 Flat-list option values live on a hidden '_lists' sheet (one column each) and667 the validations reference those ranges — this avoids Excel's 255-char inline668 limit and survives commas inside option text.669 670 When taxonomy data is available, Subject/Chapter/Topic/Subtopic become671 dependent (cascading) dropdowns instead (see write_cascade).672 """673 if subjects is None:674 subjects = SUBJECT_LIST675 if taxonomy is None:676 taxonomy = load_taxonomy(subjects=subjects)677 cascade_active = bool(taxonomy)678 679 wb = Workbook()680 ws = wb.active681 ws.title = "Tagging"682 ws.append(COLUMN_ORDER)683 684 for row in (sample_rows or []):685 ws.append([row.get(col, "") for col in COLUMN_ORDER])686 687 lists = wb.create_sheet("_lists")688 lists.sheet_state = "hidden"689 690 col_index = {name: i + 1 for i, name in enumerate(COLUMN_ORDER)}691 692 # Subject is handled by the cascade when taxonomy is present; don't also give693 # it a flat scraped-list dropdown (that would be a duplicate validation).694 cascade_cols = {"subject", "chapter", "topic", "subtopic"} if cascade_active else set()695 696 lcol = 0697 for field in SCRAPE_FIELDS:698 col = field["col"]699 if col in cascade_cols:700 continue701 values = options.get(col) or field.get("default", [])702 # Skip when there's nothing to offer, or the list is too big for a picker.703 if not values or len(values) > MAX_DROPDOWN_OPTIONS:704 continue705 lcol += 1706 lcol_letter = get_column_letter(lcol)707 lists.cell(row=1, column=lcol, value=col) # header for clarity708 for r, val in enumerate(values, start=2):709 lists.cell(row=r, column=lcol, value=val)710 ref = f"_lists!${lcol_letter}$2:${lcol_letter}${len(values) + 1}"711 712 dv = DataValidation(type="list", formula1=ref, allow_blank=True,713 showDropDown=False)714 dv.error = f"Pick a value from the {col} list."715 dv.errorTitle = "Invalid value"716 dv.prompt = f"Choose a {col}"717 dv.promptTitle = col718 ws.add_data_validation(dv)719 720 col_letter = get_column_letter(col_index[col])721 dv.add(f"{col_letter}2:{col_letter}{rows + 1}")722 723 if cascade_active:724 write_cascade(wb, ws, col_index, taxonomy, subjects, rows)725 726 # widen the header columns a touch for readability727 for name, idx in col_index.items():728 ws.column_dimensions[get_column_letter(idx)].width = max(12, min(len(name) + 4, 26))729 730 wb.save(out_path)731 732 733# ── CLI ───────────────────────────────────────────────────────────────────────734 735def main():736 ap = argparse.ArgumentParser(description="Generate the CMS tagging Excel template "737 "with live-scraped dropdowns + data validation.")738 ap.add_argument("--state", help="Playwright storage_state JSON (default: newest saved session)")739 ap.add_argument("--qid", help="Existing question id to open via Edit Details (most reliable)")740 ap.add_argument("--out", default=os.path.join(DATA_DIR, "tagging_template.xlsx"),741 help="Output .xlsx path (default: tagging_template.xlsx)")742 ap.add_argument("--no-scrape", action="store_true",743 help="Skip CMS; build the template from built-in default lists")744 ap.add_argument("--from-lookups", action="store_true",745 help="Fill Other-Details dropdowns from the QB /lookups API "746 "(no browser) instead of scraping the form with Playwright")747 ap.add_argument("--headed", dest="headless", action="store_false",748 help="Show the browser window (default)")749 ap.add_argument("--headless", dest="headless", action="store_true",750 help="Run the browser headless")751 ap.add_argument("--toc", default=TAXONOMY_CSV,752 help="Subject/Chapter/Topic/Sub-Topic CSV for the cascading dropdowns")753 ap.add_argument("--no-cascade", action="store_true",754 help="Skip the dependent Subject→Chapter→Topic→Subtopic dropdowns")755 ap.add_argument("--from-cms", action="store_true",756 help="Build the Subject→Chapter→Topic→Subtopic cascade LIVE from "757 "the CMS community API instead of the offline CSV/xlsx")758 ap.add_argument("--examples", action="store_true",759 help="Include illustrative example rows (for the sample workbook)")760 ap.set_defaults(headless=False)761 args = ap.parse_args()762 763 options = {}764 if args.no_scrape:765 print("Skipping scrape — using built-in default lists.")766 elif args.from_lookups:767 state_file = pick_state_file(args.state)768 if not state_file:769 sys.exit("--from-lookups needs a saved CMS session (none found). "770 "Connect on /tagger first, or pass --state.")771 print(f"Fetching Other-Details options from /lookups using: {state_file}")772 try:773 options = lookups_options(state_file)774 for col, vals in options.items():775 print(f" lookups {col:<14} {len(vals)} option(s)")776 except Exception as e:777 print(f"/lookups fetch failed ({e}). Falling back to built-in default lists.")778 else:779 state_file = pick_state_file(args.state)780 if not state_file:781 print("No saved CMS session found — falling back to built-in default lists.\n"782 " (Set up the CMS login first, or pass --state, to scrape live options.)")783 else:784 print(f"Using CMS session: {state_file}")785 try:786 options = scrape_options(state_file, args.qid, args.headless)787 except Exception as e:788 print(f"Scrape failed ({e}). Falling back to built-in default lists.")789 790 # Report what each scrape-able column will use for its dropdown.791 print("\nColumn dropdown sources:")792 validated = []793 for f in SCRAPE_FIELDS:794 col = f["col"]795 if options.get(col):796 print(f" {col:<14} {len(options[col])} scraped option(s)")797 validated.append(col)798 elif f.get("default"):799 print(f" {col:<14} default list ({len(f['default'])})")800 validated.append(col)801 else:802 print(f" {col:<14} no options found — left as free text")803 804 if args.no_cascade:805 taxonomy = {}806 elif args.from_cms:807 print("\nBuilding Subject -> Chapter -> Topic -> Subtopic cascade LIVE from CMS "808 "(community API)...")809 try:810 taxonomy = cms_taxonomy()811 except Exception as e:812 print(f"CMS taxonomy build failed ({e}). Falling back to offline CSV/xlsx.")813 taxonomy = load_taxonomy(args.toc)814 else:815 taxonomy = load_taxonomy(args.toc)816 if not args.no_cascade:817 if taxonomy:818 print("\nCascading taxonomy (Subject -> Chapter -> Topic -> Subtopic):")819 for s in SUBJECT_LIST:820 t = taxonomy.get(s)821 if t:822 nt = sum(len(v) for v in t.values())823 nst = sum(len(st) for v in t.values() for st in v.values())824 print(f" {s:<12} {len(t)} chapters, {nt} topics, {nst} subtopics")825 else:826 print(f" {s:<12} (no offline data — cascade empty; scrape from CMS later)")827 else:828 print(f"\nNo taxonomy CSV at {args.toc} — Subject/Chapter/Topic/Subtopic "829 "will be plain columns.")830 831 # Pass taxonomy through so build_workbook doesn't re-read the CSV.832 build_workbook(options, args.out, taxonomy=(taxonomy or None),833 sample_rows=(EXAMPLE_ROWS if args.examples else None))834 cascade_on = bool(taxonomy)835 free_cols = [f["col"] for f in FIELDS if f["mode"] == "free"836 and not (cascade_on and f["col"] in ("chapter", "topic", "subtopic"))]837 print(f"\nTemplate written: {args.out}")838 print("Columns:", ", ".join(COLUMN_ORDER))839 if cascade_on:840 print("Cascading dropdowns: subject -> chapter -> topic -> subtopic")841 validated = [c for c in validated if c != "subject"]842 print("Flat dropdowns:", ", ".join(validated))843 print("Free text:", ", ".join(free_cols))844 845 846if __name__ == "__main__":847 main()848