#!/usr/bin/env python3 """Generate dragon_items.xlsx — focused spreadsheet showing every NPC that drops any dragon weapon/armor/part, with chance (1/N) and percentage. One row per (NPC, dragon item) pair. Columns: NPC | Combat Lvl | Item | Amount | Weight | Rate (1/N) | Percentage """ import sys, os, re sys.path.insert(0, '/Users/tomassimkus/VS/openrsc-develop') os.chdir('/Users/tomassimkus/VS/openrsc-develop') from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from compare_drops import load_enums from collections import defaultdict _, npc_id_to_name, _, item_id_to_name = load_enums() # Dragon items: weapons, armor, parts DRAGON_ITEMS = { 593: ("DRAGON_SWORD", "Weapon"), 594: ("DRAGON_AXE", "Weapon"), 795: ("DRAGON_MEDIUM_HELMET", "Helmet"), 1276: ("RIGHT_HALF_DRAGON_SQUARE_SHIELD","Shield Part"), 1277: ("LEFT_HALF_DRAGON_SQUARE_SHIELD", "Shield Part"), 1278: ("DRAGON_SQUARE_SHIELD", "Shield (assembled)"), 1346: ("DRAGON_2_HANDED_SWORD", "Weapon"), 1425: ("LARGE_DRAGON_HELMET", "Helmet"), 1426: ("DRAGON_KITE_SHIELD", "Shield"), 1427: ("DRAGON_PLATE_MAIL_BODY", "Armor"), 1428: ("DRAGON_PLATE_MAIL_TOP", "Armor"), 1429: ("DRAGON_PLATE_MAIL_LEGS", "Armor"), 1430: ("DRAGON_PLATED_SKIRT", "Armor"), 1447: ("DRAGON_DAGGER", "Weapon"), 1448: ("POISONED_DRAGON_DAGGER", "Weapon"), 1449: ("DRAGON_ARROWS", "Ammo"), 1450: ("POISON_DRAGON_ARROWS", "Ammo"), 1451: ("DRAGON_BOLTS", "Ammo"), 1452: ("POISON_DRAGON_BOLTS", "Ammo"), 1453: ("DRAGON_CROSSBOW", "Weapon"), 1454: ("DRAGON_LONGBOW", "Weapon"), 1480: ("DRAGON_WOODCUTTING_AXE", "Skilling"), 1368: ("DRAGON_SCALE_MAIL", "Armor"), 1537: ("DRAGON_SCALE_MAIL_TOP", "Armor"), } def parse_combat_levels(): levels = {} with open('pk_npcdef.sql') as f: content = f.read() for ins in re.finditer(r"INSERT INTO `pk_npcdef`[^;]*?VALUES\s*(.+?);", content, re.DOTALL): body = ins.group(1) i, n = 0, len(body) while i < n: while i < n and body[i] in ' \t\n\r,': i += 1 if i >= n or body[i] != '(': break depth, j, in_str = 1, i + 1, False while j < n and depth > 0: c = body[j] if in_str: if c == '\\' and j + 1 < n: j += 2; continue if c == "'": if j + 1 < n and body[j + 1] == "'": j += 2; continue in_str = False elif c == "'": in_str = True elif c == '(': depth += 1 elif c == ')': depth -= 1 j += 1 tb = body[i + 1:j - 1] i = j cols, k, m, cur, in_str = [], 0, len(tb), [], False while k < m: c = tb[k] if in_str: if c == '\\' and k + 1 < m: cur.append(c); cur.append(tb[k+1]); k += 2; continue if c == "'": if k + 1 < m and tb[k+1] == "'": cur.append("''"); k += 2; continue in_str = False; cur.append(c) else: cur.append(c) else: if c == "'": in_str = True; cur.append(c) elif c == ',': cols.append(''.join(cur).strip()); cur = [] else: cur.append(c) k += 1 if cur: cols.append(''.join(cur).strip()) if len(cols) >= 9: try: nid = int(cols[0]); lvl = int(cols[8]) levels[nid] = lvl except ValueError: pass return levels combat_levels = parse_combat_levels() def parse_sql(path): with open(path) as f: c = f.read() out = defaultdict(list) for m in re.finditer(r"\((\d+),\s*'(-?\d+)',\s*(-?\d+),\s*(\d+),\s*\d+\)", c): out[int(m.group(1))].append((int(m.group(3)), int(m.group(2)), int(m.group(4)))) return out sql = parse_sql("pk_npcdrops.sql") # Build records: (npc_id, item_id, amount, weight, total_weight) records = [] for nid, drops in sql.items(): total_w = sum(w for _, _, w in drops if w > 0) for iid, amt, w in drops: if iid in DRAGON_ITEMS and w > 0: records.append((nid, iid, amt, w, total_w)) # Workbook wb = Workbook() ws = wb.active ws.title = "Dragon Items by NPC" header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") header_font = Font(color="FFFFFF", bold=True) weapon_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # red-pink helmet_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid") # yellow shield_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid") # green armor_fill = PatternFill(start_color="BDD7EE", end_color="BDD7EE", fill_type="solid") # blue thin = Side(border_style="thin", color="BFBFBF") border = Border(left=thin, right=thin, top=thin, bottom=thin) CATEGORY_FILL = { "Weapon": weapon_fill, "Helmet": helmet_fill, "Shield": shield_fill, "Shield Part": shield_fill, "Shield (assembled)": shield_fill, "Armor": armor_fill, "Ammo": weapon_fill, "Skilling": armor_fill, } headers = ["NPC", "Combat Lvl", "Item", "Category", "Amount", "Weight", "NPC Total Weight", "Rate (1/N)", "Percentage"] for col, h in enumerate(headers, 1): c = ws.cell(row=1, column=col, value=h) c.fill = header_fill c.font = header_font c.alignment = Alignment(horizontal="center") c.border = border ws.freeze_panes = "A2" # Sort: by item category > item name > rate desc (most common first) records.sort(key=lambda r: ( DRAGON_ITEMS[r[1]][1], # category DRAGON_ITEMS[r[1]][0], # item name -r[3] / r[4] if r[4] else 0 # rate desc within item )) r_idx = 2 for nid, iid, amt, w, tw in records: iname, category = DRAGON_ITEMS[iid] nname = npc_id_to_name.get(nid, f"#{nid}") lvl = combat_levels.get(nid, "") odds = round(tw / w) if w > 0 else 0 pct = w / tw if tw > 0 else 0 vals = [nname, lvl, iname, category, amt, w, tw, f"1/{odds:,}", pct] fill = CATEGORY_FILL.get(category) for col, v in enumerate(vals, 1): c = ws.cell(row=r_idx, column=col, value=v) c.border = border if fill: c.fill = fill if col == 9: c.number_format = "0.0000%" if col in (5, 6, 7, 8, 9): c.alignment = Alignment(horizontal="right") r_idx += 1 # Column widths widths = {1: 32, 2: 10, 3: 38, 4: 18, 5: 8, 6: 14, 7: 18, 8: 14, 9: 12} for col, w in widths.items(): ws.column_dimensions[get_column_letter(col)].width = w # Second sheet: summary by item (how many NPCs drop each) ws2 = wb.create_sheet("Summary by Item") ws2.append(["Item", "Category", "# NPCs dropping", "Best rate (1/N)", "Worst rate (1/N)"]) for col in range(1, 6): c = ws2.cell(row=1, column=col) c.fill = header_fill; c.font = header_font; c.border = border c.alignment = Alignment(horizontal="center") item_summary = defaultdict(list) # iid -> [(rate_odds, npc_id)] for nid, iid, amt, w, tw in records: item_summary[iid].append((tw / w if w > 0 else float('inf'), nid)) for iid, (iname, category) in sorted(DRAGON_ITEMS.items(), key=lambda x: (x[1][1], x[1][0])): drops = item_summary.get(iid, []) if not drops: ws2.append([iname, category, 0, "—", "—"]) else: drops.sort() best = f"1/{round(drops[0][0]):,}" worst = f"1/{round(drops[-1][0]):,}" ws2.append([iname, category, len(set(n for _, n in drops)), best, worst]) # Style sheet 2 for r_idx in range(2, ws2.max_row + 1): cat = ws2.cell(row=r_idx, column=2).value fill = CATEGORY_FILL.get(cat) for col in range(1, 6): c = ws2.cell(row=r_idx, column=col) c.border = border if fill: c.fill = fill if col >= 3: c.alignment = Alignment(horizontal="right") widths2 = {1: 38, 2: 20, 3: 18, 4: 18, 5: 18} for col, w in widths2.items(): ws2.column_dimensions[get_column_letter(col)].width = w ws2.freeze_panes = "A2" out_path = "/Users/tomassimkus/VS/openrsc-develop/dragon_items.xlsx" wb.save(out_path) print(f"Wrote {r_idx-1} rows to {out_path}") print(f"Sheets: 'Dragon Items by NPC' (one row per NPC+item), 'Summary by Item'")