import sys, io sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8') exact_matches = [ (7, 37, "A PILLAR OF HOPE"), (11, 13, "DANCE OF THE MONKEYS"), (9, 17, "OF GOATS AND POISONED ORANGES"), (1, 20, "WHAT HAPPENED TO US?"), (2, 21, "BORN TO BE UNSTOPPABLE"), (92, 2, "DAWN OF FORTUNE"), (3, 30, "DESIRE AT DAWN"), (4, 14, "RENDITIONS OF THE SOUL"), (5, 13, "WHEN THE WORLD IN MY VOICE SINGS"), (13, 24, "AN AUTOBIOGRAPHY: AVIATOR PAR EXCELLENCE"), (14, 14, "NEW CONFIDENT AFRICA"), (31, 30, "TOMORROW I WILL BE HERE"), (15, 20, "WHEN RIVERS SAY GOODBYE"), (93, 9, "SINNERS"), (16, 8, "DREAM OF AFRICA: CHAOS BEFORE ORDER"), (8, 20, "THE GEN Z PARENT"), (17, 10, "THE CROOKED SOCIETY"), (19, 8, "BREAKING THE ORPHAN SPIRIT"), (88, 7, "THE PRESENT HINDSIGHT"), (87, 8, "STRANDED"), (20, 7, "A REFLECTION ON OUR CULTURES"), (24, 18, "THE DAYS OF HIS POWER"), (25, 7, "TRUST THE CLOCKMASTER"), (26, 14, "HEART AND SKILL"), (27, 14, "THE HEART OF A WORSHIPPER"), (28, 15, "THE POWER OF CHOICE"), (29, 15, "THE LIFE OF A WORSHIPPER"), (32, 9, "THE INCOMPLETE GRAVESTONE"), (63, 3, "ADAM AND THE GARDEN PROMISE SERIES 1"), (64, 3, "NOAH AND THE RAINBOW PROMISE SERIES 2"), (65, 3, "ABRAHAM AND THE STARRY PROMISE SERIES 3"), (66, 3, "MOSES AND THE MOUNTAIN PROMISE SERIES 4"), (67, 3, "DAVID THE KINGLY PROMISE SERIES 5"), (68, 1, "JESUS THE GREATEST PROMISE SERIES 6"), (76, 3, "RAY'S CULTURAL QUEST PART 1"), (77, 3, "CULTURAL CLUB ADVENTURE PART 2"), (36, 4, "CONFESSIONS OF NAIROBI MEN"), (37, 3, "GUILTY"), (38, 3, "DAMN, GIRL! STOP THAT"), (39, 2, "THINGS I WILL TELL MY DAUGHTER"), (41, 4, "CONFESSIONS OF NAIROBI MEN BOOK 2"), (42, 6, "MAD WOMEN"), (43, 3, "CONFESSIONS OF NAIROBI MEN AND WOMEN: THE LAST CHAPTER"), (44, 5, "CONFESSIONS OF NAIROBI WOMEN"), (45, 1, "EVERYTHING I KNOW ABOUT LIFE"), (46, 3, "LETTERS TO MY SON"), (47, 6, "CONFESSION OF NAIROBI WOMEN: BOOK TWO"), (48, 5, "QUEENEX CRE ACTIVITIES PRACTICE BOOK - PLAY GROUP"), (49, 8, "QUEENEX CRE ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 1 (PP1)"), (50, 5, "QUEENEX CRE ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 2 (PP2)"), (51, 3, "QUEENEX ENVIRONMENTAL ACTIVITIES PRACTICE BOOK - PLAY GROUP"), (52, 7, "QUEENEX LANGUAGE ACTIVITIES PRACTICE BOOK - PLAY GROUP"), (53, 7, "QUEENEX LANGUAGE ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 1 (PP1)"), (54, 7, "QUEENEX LANGUAGE ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 2 (PP2)"), (55, 6, "QUEENEX MATHEMATICAL ACTIVITIES PRACTICE BOOK - PLAY GROUP"), (56, 8, "QUEENEX MATHEMATICAL ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 1 (PP1)"), (57, 3, "QUEENEX MATHEMATICAL ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 2 (PP2)"), (58, 70, "SELLING TO INFORMAL MARKETS"), (59, 5, "THE AMERU HISTORY AND THE RISE OF NJURI NCHEKE"), (60, 1, "RIITHO NTURENE"), (79, 4, "THE FUN ANIMAL PUZZLE BOOK"), (80, 5, "WHERE DID THE WORLD COME FROM"), (81, 10, "BLOSSOM READ ALOUD SOUNDS PRACTICE BOOK 1"), (85, 5, "PREMIER COMBINED PRACTICE BOOK - PRE-PRIMARY 1"), (86, 5, "PREMIER COMBINED PRACTICE BOOK - PRE-PRIMARY 2"), ] # (id, stock, db_title, reason) id_fixes = [ (10, 7, "CUCUU", "Excel: CUCU — DB title has diacritics"), (21, 9, "BASICS OF FINANCIAL LITERACY FOR TEENS AND BEGINNERS", "Excel typo: Begginers"), (30, 17, "THE OVERLAPPER'S MINDSET", "Smart quote in DB title"), (33, 2, "MOUNTAINS DO REMEMBER", "Excel: MOUNTAINS REMEMBER — missing DO"), (61, 3, "THOUGHT LEADER'S CODE", "Smart quote in DB title"), (62, 2, "POWER OF PURPOSE", "Excel: THE POWER OF PURPOSE : A guide..."), (69, 3, "MUNCHING NINJA Book 1", "Kenyan Everyday Fruits, Colouring Book 1"), (70, 2, "MUNCHING NINJA Book 2", "Kenyan Exotic Fruits, Colouring Book 2"), (71, 3, "MUNCHING NINJA Book 3", "Kenyan Garden Veggies, Colouring Book 3"), (72, 3, "MUNCHING NINJA Book 4", "Kenyan Power Veggies, Colouring Book 4"), (73, 2, "MUNCHING NINJA Book 5", "Kenyan Power Snacks, Recipe Book 5"), (74, 2, "MUNCHING NINJA Book 6", "Kenyan Power Meals, Recipe Book 6 (ID=75 is duplicate)"), (91, 7, "QUEENEX CREATIVE ACTIVITIES PRACTICE BOOK FOR PRE-PRIMARY 2", "DB title has double space"), (90, 10, "QUEENEX ENVIRONMENTAL ACTIVITIES PRACTICE BOOK FOR PRE-PRIMARY 2", "Excel title: PP2 / Pp2"), (89, 7, "KISWAHILI WARIDI KITABU CHA KWANZA", "Excel: BLOSSOM KISWAHILI WARIDI — confirm same book"), ] skipped = [ ("THE CROOKED SOCIETY - OLDER VERSION", 6, "DB has only one Crooked Society (ID=17, set to stock=10 above).\n-- If same book: change ID=17 above to stock=16. If separate: add to catalog first."), ("QUEENEX ENVIRONMENTAL ACTIVITIES PRACTICE BOOK - PP1", 10, "No PP1 environmental book in DB (only PLAY GROUP ID=51 and PP2 ID=90). Confirm with admin."), ("QUEENEX MATHEMATICAL ACTIVITIES PRACTICE BOOK - PLAY GROUP (COMPETENCY BASED CURRICULUM)", 2, "DB ID=55 exists without CBE suffix. If same book: change ID=55 above to stock=8 (6+2). If separate: add first."), ("QUEENEX MATHEMATICAL ACTIVITIES PRACTICE BOOK - PRE-PRIMARY 2 (PP2) (COMPETENCY BASED)", 1, "DB ID=57 exists without CBE suffix. If same book: change ID=57 above to stock=4 (3+1). If separate: add first."), ("QUEENEX CREATIVE ACTIVITIES PRACTICE BOOK FOR Playgroup (COMPETENCY BASED)", 3, "Not in DB. Add to catalog first."), ("QUEENEX CREATIVE ACTIVITIES PRACTICE BOOK FOR PRE-PRIMARY 1", 3, "Not in DB — only PP2 exists (ID=91). Add to catalog first."), ("AFRICA'S BOOK OF GREATS V1", 13, "Not in DB. Add to catalog first."), ("BEST BEFORE '09", 5, "Not in DB. Add to catalog first."), ("THE SINS OF OUR FATHER", 3, "Not in DB. Add to catalog first."), ("SHADOWS IN MY SUNSHINE", 5, "Not in DB. Add to catalog first."), ("DEPORTEE", 8, "Not in DB. Add to catalog first."), ("ENDURING LOVE & FAITHFULNESS", 12, "Not in DB. Add to catalog first."), ("The Work Life (Nyambur Muhoro)", 11, "Not in DB. Add to catalog first."), ("GIRL GET OFF THE FLOOR", 3, "Not in DB. Add to catalog first."), ("Family Rise Up Against Gender-Based Violence", 3, "Not in DB. Add to catalog first."), ("DON'T BUY THAT HOUSE", 4, "Not in DB. Add to catalog first."), ("THE ABNORMAL STUDENT", 4, "Not in DB. Add to catalog first."), ("ELIMINATING PROJECT DELAYS COST OVERRUNS", 5, "Not in DB. Add to catalog first."), ("THE 7 RULES OF REAL ESTATE INVESTMENT", 3, "Not in DB. Add to catalog first."), ("PREMIER REVISION ENCYCLOPEDIA PLAY GROUP", 5, "Not in DB. Add to catalog first."), ("THE AMERU SOCIAL ROLE OF GICHIARO", 5, "Not in DB. Add to catalog first."), ("THE ANIMAL TRIVIA GAME", 3, "Not in DB. Add to catalog first."), ] all_books = exact_matches + [(r[0], r[1], r[2]) for r in id_fixes] + [(75, 2, "MUNCHING NINJA Book 6 (duplicate ID=75)")] print("-- =====================================================================") print("-- Book Stock Update — FINAL CORRECTED VERSION — 30 July 2026") print(f"-- {len(exact_matches)} exact matches + {len(id_fixes)+1} ID-resolved = {len(all_books)} books updated") print(f"-- {len(skipped)} entries SKIPPED (see bottom — need confirmation or catalog entry)") print("-- All updates use book ID — immune to title case/encoding differences") print("-- =====================================================================") print() print("-- Step 1: Online Store Website location") print("INSERT INTO `inventory_stocklocation` (`name`,`code`,`location_type`,`address`,`is_active`,`created_at`)") print("VALUES ('Online Store Website','ONLINE-STORE','other','',1,NOW())") print("ON DUPLICATE KEY UPDATE `name`=`name`;") print("SET @loc_id = (SELECT `id` FROM `inventory_stocklocation` WHERE `code`='ONLINE-STORE');") print() print("-- =====================================================================") print(f"-- Step 2: catalog_app_book.stock ({len(all_books)} books)") print("-- =====================================================================") print() print("-- ── 65 exact title matches ─────────────────────────────────────────") for bid, stk, label in exact_matches: print(f"UPDATE `catalog_app_book` SET `stock`={stk:3d} WHERE `id`={bid:3d}; -- {label}") print() print("-- ── 16 ID-resolved (title mismatch between Excel and DB) ───────────") for bid, stk, db_title, reason in id_fixes: print(f"UPDATE `catalog_app_book` SET `stock`={stk:3d} WHERE `id`={bid:3d}; -- DB: {db_title} [{reason}]") print(f"UPDATE `catalog_app_book` SET `stock`= 2 WHERE `id`= 75; -- MUNCHING NINJA Book 6 duplicate (same as ID=74)") print() print("-- =====================================================================") print(f"-- Step 3: inventory_inventorybalance upsert ({len(all_books)} rows)") print("-- =====================================================================") print() print("-- ── exact matches ──────────────────────────────────────────────────") for bid, stk, label in exact_matches: print(f"INSERT INTO `inventory_inventorybalance`(`book_id`,`location_id`,`quantity_on_hand`,`quantity_reserved`)") print(f"VALUES({bid},@loc_id,{stk},0) ON DUPLICATE KEY UPDATE `quantity_on_hand`={stk}; -- {label}") print() print("-- ── ID-resolved ────────────────────────────────────────────────────") for bid, stk, db_title, reason in id_fixes: print(f"INSERT INTO `inventory_inventorybalance`(`book_id`,`location_id`,`quantity_on_hand`,`quantity_reserved`)") print(f"VALUES({bid},@loc_id,{stk},0) ON DUPLICATE KEY UPDATE `quantity_on_hand`={stk}; -- {db_title}") print(f"INSERT INTO `inventory_inventorybalance`(`book_id`,`location_id`,`quantity_on_hand`,`quantity_reserved`)") print(f"VALUES(75,@loc_id,2,0) ON DUPLICATE KEY UPDATE `quantity_on_hand`=2; -- MUNCHING NINJA Book 6 duplicate") print() print("-- =====================================================================") print(f"-- SKIPPED ({len(skipped)} entries) — action required before stock can be set") print("-- =====================================================================") print() for title, stk, reason in skipped: print(f"-- *** stock={stk:3d} | {title}") print(f"-- {reason}") print() print("-- =====================================================================") print("-- END. Verify: every UPDATE and INSERT should affect exactly 1 row.") print("-- Any 0-row result means the book ID is wrong — check before re-running.") print("-- =====================================================================")