ls -la /tmp/workspace/ 2>/dev/null || mkdir -p /tmp/workspace && echo "workspace ready"

mkdir -p /tmp/workspace/study-tracker && cat > /tmp/workspace/study-tracker/build_tracker.py << 'PYEOF' import openpyxl from openpyxl.styles import ( PatternFill, Font, Alignment, Border, Side, GradientFill ) from openpyxl.utils import get_column_letter from openpyxl.chart import BarChart, Reference, PieChart from openpyxl.chart.series import DataPoint from openpyxl.formatting.rule import DataBarRule, ColorScaleRule, CellIsRule, FormulaRule from openpyxl.worksheet.datavalidation import DataValidation import datetime wb = openpyxl.Workbook() # ─── COLOR PALETTE ─────────────────────────────────────────────── DARK_NAVY = "1B2A4A" TEAL = "0D7377" LIGHT_TEAL = "14BDAC" GOLD = "F4A261" WHITE = "FFFFFF" LIGHT_GRAY = "F5F7FA" MID_GRAY = "D9DEE6" DARK_GRAY = "6C757D" GREEN = "2DC653" ORANGE = "FFA500" RED = "E63946" YELLOW_BG = "FFF9C4" BLUE_LIGHT = "E3F2FD" TEAL_LIGHT = "E0F7F7" SUBJECT_COLORS = [ "D6EAF8", "D5F5E3", "FDEBD0", "F9EBEA", "E8DAEF", "FDFEFE", "EAF2FF", "FFF3CD" ] def solid(hex_color): return PatternFill("solid", fgColor=hex_color) def font(bold=False, size=11, color="000000", italic=False): return Font(name="Arial", bold=bold, size=size, color=color, italic=italic) def border_thin(): s = Side(style="thin", color="BBBBBB") return Border(left=s, right=s, top=s, bottom=s) def border_medium(): s = Side(style="medium", color=DARK_NAVY) return Border(left=s, right=s, top=s, bottom=s) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) # ═══════════════════════════════════════════════════════════════════ # DATA: All 8 subjects and their topics # ═══════════════════════════════════════════════════════════════════ subjects_data = { "BRDT201 - Anatomy & Physiology": { "code": "BRDT201", "color": SUBJECT_COLORS[0], "marks": 80, "topics": [ "Basic Anatomy of Urinary System (Kidney, Bladder, Ureter, Urethra, Prostate)", "Histology of Kidney", "Blood Supply of Kidney", "Development of Kidney in Brief", "Anatomy of Peritoneum & Concept of Abdominal Hernias", "Anatomy of Vascular System - Upper Limb Vessels", "Anatomy of Vascular System - Neck Vessels", "Anatomy of Vascular System - Femoral Vessels", "Mechanism of Urine Formation", "Glomerular Filtration Rate (GFR)", "Clearance Studies", "Physiological Values (Urea, Creatinine, Electrolytes, Calcium, Phosphorus, Uric Acid, Magnesium, Glucose)", "24-Hour Urinary Indices", "Physiology of Renal Circulation - Factors Contributing & Modifying", "Autoregulation of Renal Circulation", "Hormones Produced by Kidney", "Physiological Alterations in Pregnancy", "Haemostasis - Coagulation Cascade & Factors, BT, CT, PT, PTT, Thrombin Time", "Acid-Base Balance - Basic Principles", "Acid-Base Abnormalities (Hypokalemia, Hyperkalemia, Hyponatremia, Hypernatremia, Hypocalcemia, Hypercalcemia)", "Basic Nutrition in Renal Diseases", ] }, "BRDT202 - Pharmacology": { "code": "BRDT202", "color": SUBJECT_COLORS[1], "marks": 80, "topics": [ "IV Fluid Therapy with Special Emphasis in Renal Diseases", "Diuretics - Classification, Actions, Dosage, Side Effects & Contraindications", "Antihypertensives - Classification, Actions, Dosage, Side Effects & Contraindications", "Reference During Dialysis, Vasopressors, Drugs Used in Hypotension", "Drugs & Dialysis - Dose & Duration of Administration", "Dialysable Drugs (Phenobarbitone, Lithium, Methanol etc.)", "Vitamin D & Analogues, Phosphate Binders, Iron, Folic Acid & Other Vitamins", "Erythropoietin in Detail", "Heparin Including Low Molecular Weight Heparin", "Protamine Sulphate", "Formalin, Sodium Hypochlorite, Hydrogen Peroxide - Disinfectants & Adverse Effects", "Haemodialysis Concentrates - Composition & Dilution (Acetate & Bicarbonates)", "Peritoneal Dialysis Fluid & Hypertonic Solutions - Composition", "Potassium Exchange Resins - Mode of Administration", ] }, "BRDT203 - Renal Diseases": { "code": "BRDT203", "color": SUBJECT_COLORS[2], "marks": 80, "topics": [ "Acute Renal Failure - Clinical Manifestations, Evaluation & Management", "Nephrotic Syndrome - Primary & Secondary", "Nephritic Syndrome", "UTI - Urinary Tract Infections", "Asymptomatic Urinary Abnormalities", "Chronic Renal Failure", "Renal Stone Diseases", "Obstructive Uropathies", "Congenital & Inherited Renal Diseases", "Tumors of Kidney", "Pregnancy Associated Renal Diseases", "Renal Vascular Disorders & Hypertension Associated Renal Diseases", ] }, "BRDT204 - Pathology & Microbiology": { "code": "BRDT204", "color": SUBJECT_COLORS[3], "marks": 100, "topics": [ "Congenital Abnormalities of Urinary System", "Classification of Renal Diseases", "Glomerular Diseases - Causes, Types & Pathology", "Tubulointerstitial Diseases", "Renal Vascular Disorders", "End Stage Renal Diseases - Causes & Pathology", "Pathology of Kidney in Hypertension, Diabetes Mellitus, Pregnancy", "Pathology of Peritoneum - Peritonitis (Bacterial, Tubular, Sclerosing, Dialysis-induced)", "Pathology of Urinary Tract Infections", "Pyelonephritis & Tuberculous Pyelonephritis", "Hepatotrophic Viruses - Mode of Transfusion, Universal Precautions, Vaccinations", "HIV - Mode of Transfusion, Universal Precautions", "Opportunistic Infections", "Microbiology of Urinary Tract Infections", "Microbiology of Vascular Access Infection (Femoral, Jugular, Subclavian Catheters)", "Sampling Methodologies for Culture & Sensitivity", ] }, "BRDT205 - Sociology": { "code": "BRDT205", "color": SUBJECT_COLORS[4], "marks": 0, "topics": [ "Introduction: Definition, Scope & Methods of Sociological Investigations", "Relation to Anthropology, Psychology, Social Psychology", "Social Factors in Health and Disease", "Socialization: Meaning, Nature, Types & Agencies", "Social Groups: Formal, Informal, Primary, Secondary", "Family: Meaning, Functions, Types, Changing Patterns", "Community: Rural, Urban, Tribal - Features & Health Hazards", "Culture and Health: Concept, Culture & Health Disorders", "Social Change: Meaning, Factors, Stress, Deviance, Health Programmes", "Social Problems of Disabled (Population, Poverty, Beggary, Juvenile Delinquency etc.)", "Social Security & Social Legislation", "Social Work: Meaning & Role of Medical Social Worker", ] }, "BRDT206 - Indian Constitution": { "code": "BRDT206", "color": SUBJECT_COLORS[5], "marks": 0, "topics": [ "Unit I: Meaning & Making of the Indian Constitution (1946-1949)", "Unit II: Democratic Institutions - Bicameral Legislature (Centre & States)", "Unit III: Fundamental Rights and Duties", "Unit IV: Directive Principles of State Policy", "Unit V: Special Rights for Dalits, Backwards, Women, Children & Minorities", "Unit VI: Doctrine of Separation of Powers (Legislature, Executive, Judiciary)", "Unit VII: Election Commission & State Public Service Commissions", "Unit VIII: Method of Amending the Constitution", "Unit IX: Enforcing Rights through Writs (Habeas Corpus, Mandamus, etc.)", "Unit X: Constitution and Sustainable Development in India", ] }, "BRDT208 - Nutrition": { "code": "BRDT208", "color": SUBJECT_COLORS[6], "marks": 0, "topics": [ "Balanced Diet - Definition & Concept", "Caloric Values of Carbohydrates, Proteins and Fats", "Total Daily Caloric Requirements (Adult Male & Female)", "RDA - Definition & Standard Values for Nutrients", "Basal Metabolic Rate (BMR) - Definition, Magnitude & Factors", "Thermic Effect / SDA of Food", "Carbohydrates - Daily Requirement & Dietary Fibers", "Proteins - Daily Requirement, Biological Value & Mutual Supplementation", "Fats - Essential Fatty Acids, Saturated & Unsaturated Fatty Acids", "Malnutrition", "Introduction to Science of Nutrition - Food Patterns & Influencing Factors", "Classification of Nutrients (Macro & Micronutrients) - All Types", "Planning Diets - Balanced Diet & Renal Diet", "Introduction to Cookery", ] }, "BRDT209 - Basics of Dialysis Tech": { "code": "BRDT209", "color": SUBJECT_COLORS[7], "marks": 0, "topics": [ "Indications of Dialysis", "Types of Dialysis", "Principles of Dialysis - Definitions", "Haemodialysis Apparatus - Types of Dialyser & Membrane", "Types of Vascular Access for Haemodialysis", "Introduction to Haemodialysis Machine", "Priming of Dialysis Apparatus", "Dialyser Reuse", "Common Complications of Haemodialysis", "Monitoring of Patients During Dialysis", "Nutrition - Introduction to Science of Nutrition", "Classification of Nutrients (Proteins, Carbs, Fats, Water, Minerals, Vitamins)", "Planning Diets - Balanced Diet, Food Groups, Renal Diet", "Introduction to Cookery", ] }, } # ═══════════════════════════════════════════════════════════════════ # SHEET 1: DASHBOARD # ═══════════════════════════════════════════════════════════════════ ws_dash = wb.active ws_dash.title = "📊 Dashboard" # Set column widths col_widths = [2, 28, 14, 14, 14, 14, 14, 2] for i, w in enumerate(col_widths, 1): ws_dash.column_dimensions[get_column_letter(i)].width = w # Row heights ws_dash.row_dimensions[1].height = 8 ws_dash.row_dimensions[2].height = 50 ws_dash.row_dimensions[3].height = 20 ws_dash.row_dimensions[4].height = 18 ws_dash.row_dimensions[5].height = 8 # Header banner ws_dash.merge_cells("B2:G2") ws_dash["B2"] = "📚 B.Sc RENAL DIALYSIS TECHNOLOGY — 2nd Year Study Tracker" ws_dash["B2"].font = Font(name="Arial", bold=True, size=18, color=WHITE) ws_dash["B2"].fill = solid(DARK_NAVY) ws_dash["B2"].alignment = center() ws_dash.merge_cells("B3:G3") ws_dash["B3"] = "Exam Preparation Roadmap | August – November 2026 | Track Your Daily Progress" ws_dash["B3"].font = Font(name="Arial", size=11, color=WHITE, italic=True) ws_dash["B3"].fill = solid(TEAL) ws_dash["B3"].alignment = center() ws_dash.merge_cells("B4:G4") ws_dash["B4"] = f"Last Updated: {datetime.date.today().strftime('%d %B %Y')}" ws_dash["B4"].font = font(size=10, color=DARK_GRAY, italic=True) ws_dash["B4"].alignment = center() # Section header row 6 ws_dash.row_dimensions[6].height = 28 headers = ["", "SUBJECT", "TOTAL TOPICS", "COMPLETED", "PROGRESS %", "WEAK TOPICS", "STATUS", ""] for col, h in enumerate(headers, 1): c = ws_dash.cell(row=6, column=col, value=h) c.font = Font(name="Arial", bold=True, size=11, color=WHITE) c.fill = solid(DARK_NAVY) c.alignment = center() c.border = border_thin() # Subject rows (7 to 14) subject_names = list(subjects_data.keys()) sheet_names = [f"{''.join(n.split()[:1])} {subjects_data[n]['code']}" for n in subject_names] # We'll use a simpler sheet reference approach tracker_sheet_titles = [] for i, (sname, sdata) in enumerate(subjects_data.items()): row = 7 + i ws_dash.row_dimensions[row].height = 24 total = len(sdata["topics"]) color = sdata["color"] # Col B - Subject name c_sub = ws_dash.cell(row=row, column=2, value=sname) c_sub.font = Font(name="Arial", bold=True, size=11) c_sub.fill = solid(color) c_sub.alignment = left() c_sub.border = border_thin() # Col C - Total topics (hardcoded) c_tot = ws_dash.cell(row=row, column=3, value=total) c_tot.font = font(bold=True, size=12) c_tot.fill = solid(color) c_tot.alignment = center() c_tot.border = border_thin() # Col D - Completed (will be formula linked later - for now placeholder 0) c_done = ws_dash.cell(row=row, column=4, value=0) c_done.font = font(bold=True, size=12, color=TEAL) c_done.fill = solid(LIGHT_GRAY) c_done.alignment = center() c_done.border = border_thin() c_done.number_format = "0" # Col E - Progress % c_pct = ws_dash.cell(row=row, column=5) c_pct.value = f"=IF(C{row}=0,0,D{row}/C{row})" c_pct.font = Font(name="Arial", bold=True, size=12) c_pct.fill = solid(LIGHT_GRAY) c_pct.alignment = center() c_pct.border = border_thin() c_pct.number_format = "0%" # Col F - Weak Topics placeholder c_weak = ws_dash.cell(row=row, column=6, value=0) c_weak.font = font(size=11) c_weak.fill = solid(LIGHT_GRAY) c_weak.alignment = center() c_weak.border = border_thin() # Col G - Status formula c_status = ws_dash.cell(row=row, column=7) c_status.value = f'=IF(E{row}=0,"⬜ Not Started",IF(E{row}<0.5,"🔄 In Progress",IF(E{row}<1,"✅ Almost Done","🏆 Complete")))' c_status.font = font(size=11) c_status.fill = solid(LIGHT_GRAY) c_status.alignment = center() c_status.border = border_thin() # Color scale on progress column from openpyxl.formatting.rule import ColorScaleRule ws_dash.conditional_formatting.add( "E7:E14", ColorScaleRule( start_type="num", start_value=0, start_color="E63946", mid_type="num", mid_value=0.5, mid_color="FFA500", end_type="num", end_value=1, end_color="2DC653" ) ) # Summary totals row 15 ws_dash.row_dimensions[15].height = 28 ws_dash.merge_cells("B15:B15") total_label = ws_dash.cell(row=15, column=2, value="📈 OVERALL PROGRESS") total_label.font = Font(name="Arial", bold=True, size=12, color=WHITE) total_label.fill = solid(DARK_NAVY) total_label.alignment = center() total_label.border = border_medium() total_topics = ws_dash.cell(row=15, column=3, value="=SUM(C7:C14)") total_topics.font = Font(name="Arial", bold=True, size=12, color=WHITE) total_topics.fill = solid(DARK_NAVY) total_topics.alignment = center() total_topics.border = border_medium() total_done = ws_dash.cell(row=15, column=4, value="=SUM(D7:D14)") total_done.font = Font(name="Arial", bold=True, size=12, color=GOLD) total_done.fill = solid(DARK_NAVY) total_done.alignment = center() total_done.border = border_medium() total_done.number_format = "0" total_pct = ws_dash.cell(row=15, column=5, value="=IF(C15=0,0,D15/C15)") total_pct.font = Font(name="Arial", bold=True, size=14, color=GOLD) total_pct.fill = solid(DARK_NAVY) total_pct.alignment = center() total_pct.border = border_medium() total_pct.number_format = "0%" total_weak = ws_dash.cell(row=15, column=6, value="=SUM(F7:F14)") total_weak.font = Font(name="Arial", bold=True, size=12, color=WHITE) total_weak.fill = solid(DARK_NAVY) total_weak.alignment = center() total_weak.border = border_medium() total_status = ws_dash.cell(row=15, column=7, value='=IF(E15=1,"🏆 EXAM READY!",IF(E15>=0.8,"✅ Almost Ready","📖 Keep Going!"))') total_status.font = Font(name="Arial", bold=True, size=12, color=GOLD) total_status.fill = solid(DARK_NAVY) total_status.alignment = center() total_status.border = border_medium() # Gap row 16 ws_dash.row_dimensions[16].height = 14 # ─── HOW TO USE section ────────────────────────────────────────── ws_dash.row_dimensions[17].height = 22 ws_dash.merge_cells("B17:G17") how = ws_dash.cell(row=17, column=2, value="ℹ️ HOW TO USE THIS TRACKER") how.font = Font(name="Arial", bold=True, size=12, color=WHITE) how.fill = solid(TEAL) how.alignment = center() instructions = [ ("Step 1", "Click on any subject tab below (e.g. '📖 BRDT201') to open that subject's topic list."), ("Step 2", "For each topic you study, change the STATUS column to: Not Started / In Progress / Completed / Need Revision."), ("Step 3", "After you update a topic sheet, manually update the 'Completed' count (Column D) on this Dashboard."), ("Step 4", "Use the 'Notes / Weak Areas' column to write things you found difficult or want to revise again."), ("Step 5", "Set your Target Date for each topic and track if you are on schedule."), ("Tip 🌟", "The PROGRESS % column will auto-color from Red → Orange → Green as you complete topics!"), ] for i, (step, text) in enumerate(instructions): r = 18 + i ws_dash.row_dimensions[r].height = 20 c1 = ws_dash.cell(row=r, column=2, value=step) c1.font = Font(name="Arial", bold=True, size=10, color=DARK_NAVY) c1.fill = solid(YELLOW_BG) c1.alignment = center() c1.border = border_thin() ws_dash.merge_cells(f"C{r}:G{r}") c2 = ws_dash.cell(row=r, column=3, value=text) c2.font = font(size=10) c2.fill = solid(LIGHT_GRAY) c2.alignment = left() c2.border = border_thin() print("Dashboard created.") # ═══════════════════════════════════════════════════════════════════ # SHEET 2: MONTHLY PLANNER (3 months) # ═══════════════════════════════════════════════════════════════════ ws_plan = wb.create_sheet("📅 Monthly Planner") # Column widths plan_widths = [2, 12, 12, 35, 30, 18, 18, 18, 2] for i, w in enumerate(plan_widths, 1): ws_plan.column_dimensions[get_column_letter(i)].width = w # Header ws_plan.merge_cells("B1:H1") ws_plan["B1"] = "📅 3-MONTH WEEKLY STUDY PLANNER | Aug 5 – Nov 4, 2026" ws_plan["B1"].font = Font(name="Arial", bold=True, size=16, color=WHITE) ws_plan["B1"].fill = solid(DARK_NAVY) ws_plan["B1"].alignment = center() ws_plan.row_dimensions[1].height = 36 ws_plan.row_dimensions[2].height = 8 # Column headers row 3 ws_plan.row_dimensions[3].height = 26 plan_headers = ["", "WEEK", "DATES", "SUBJECTS & TOPICS TO COVER", "KEY TASKS / FOCUS", "STATUS", "SELF-SCORE (1-10)", "NOTES", ""] for col, h in enumerate(plan_headers, 1): c = ws_plan.cell(row=3, column=col, value=h) c.font = Font(name="Arial", bold=True, size=11, color=WHITE) c.fill = solid(TEAL) c.alignment = center() c.border = border_thin() # Weekly plan data weekly_plan = [ # Month 1 ("MONTH 1", "", "FOUNDATION — Core Anatomy, Physiology & Pharmacology", "", "", ""), ("Week 1", "Aug 5–11", "BRDT201 – Applied Anatomy", "Urinary system anatomy, Histology of kidney, Blood supply, Development, Peritoneum, Vascular system", "⬜ Not Started", ""), ("Week 2", "Aug 12–18", "BRDT201 – Physiology", "GFR, Clearance studies, Physiological values, Renal circulation, Hormones, Haemostasis, Acid-Base balance", "⬜ Not Started", ""), ("Week 3", "Aug 19–25", "BRDT202 – Pharmacology Part 1", "IV fluids, Diuretics, Antihypertensives, Vasopressors, Dialysable drugs, Vitamin D, Phosphate binders", "⬜ Not Started", ""), ("Week 4", "Aug 26–Sep 1", "BRDT202 – Pharmacology Part 2", "EPO, Heparin/LMWH, Protamine sulphate, Disinfectants, HD concentrates, PD fluid, K-exchange resins", "⬜ Not Started", ""), ("Week 4B", "Sep 2–4", "BRDT201 + BRDT202 REVISION", "Practice 2 Long Essays + 6 Short Essays on Pharmacology. Review weak topics from Week 1-4.", "⬜ Not Started", ""), # Month 2 ("MONTH 2", "", "MASTERY — Renal Diseases, Pathology & Microbiology", "", "", ""), ("Week 5", "Sep 5–11", "BRDT203 – Renal Diseases Part 1", "ARF, Nephrotic syndrome, Nephritic syndrome, UTI, Asymptomatic abnormalities, CRF, Renal stones", "⬜ Not Started", ""), ("Week 6", "Sep 12–18", "BRDT203 – Renal Diseases Part 2 + Revision", "Obstructive uropathy, Congenital diseases, Tumors, Pregnancy renal diseases, Vascular disorders. Full revision.", "⬜ Not Started", ""), ("Week 7", "Sep 19–25", "BRDT204 – Pathology Part 1", "Congenital abnormalities, Glomerular diseases, Tubulointerstitial diseases, ESRD, Pathology of peritoneum", "⬜ Not Started", ""), ("Week 8", "Sep 26–Oct 4", "BRDT204 – Pathology Part 2 + Microbiology", "UTI pathology, Pyelonephritis, Hepatitis viruses, HIV, Opportunistic infections, Vascular access infections, Culture methods", "⬜ Not Started", ""), # Month 3 ("MONTH 3", "", "COMPLETE & REVISE — Subsidiary Subjects + Mega Revision", "", "", ""), ("Week 9", "Oct 5–11", "BRDT209 – Dialysis Tech + Nutrition Part 1", "Indications/types of dialysis, HD machine, Vascular access, Complications, Monitoring. Intro to Nutrition.", "⬜ Not Started", ""), ("Week 10", "Oct 12–18", "BRDT208 – Nutrition + BRDT205 – Sociology Part 1", "Caloric values, BMR, RDA, Proteins/Fats/Carbs, Renal diet. Sociology: Introduction, Social factors, Socialization", "⬜ Not Started", ""), ("Week 11", "Oct 19–25", "BRDT205 – Sociology Part 2 + BRDT206 – Constitution", "Social groups, Family, Community, Culture, Social change, Social problems. Constitution Units I-V", "⬜ Not Started", ""), ("Week 12A", "Oct 26–28", "BRDT206 – Constitution + Sociology Completion", "Constitution Units VI-X (Separation of powers, Writs, Amending, Sustainable Dev). Complete Sociology.", "⬜ Not Started", ""), ("Week 12B", "Oct 29–Nov 1", "MEGA REVISION — All Core Subjects", "Day 1: BRDT201 | Day 2: BRDT202 | Day 3: BRDT203 | Day 4: BRDT204", "⬜ Not Started", ""), ("Week 12C", "Nov 2–4", "FINAL PREP — Mock Exams & Rest", "Day 1: Mock exam (any 2 subjects) | Day 2: Subsidiary subjects quick review | Day 3: Light revision only. REST!", "⬜ Not Started", ""), ] current_row = 4 month_rows = [] for entry in weekly_plan: ws_plan.row_dimensions[current_row].height = 40 if entry[0].startswith("MONTH") else 36 if entry[0].startswith("MONTH"): # Month header ws_plan.merge_cells(f"B{current_row}:H{current_row}") mc = ws_plan.cell(row=current_row, column=2, value=f"━━━ {entry[0]} — {entry[2]} ━━━") mc.font = Font(name="Arial", bold=True, size=13, color=WHITE) mc.fill = solid(TEAL) mc.alignment = center() mc.border = border_medium() month_rows.append(current_row) else: week, dates, subj, tasks, status, notes = entry data_row = [("", 1), (week, 2), (dates, 3), (subj, 4), (tasks, 5), (status, 6), ("", 7), (notes, 8), ("", 9)] bg = LIGHT_GRAY if (current_row % 2 == 0) else WHITE c2 = ws_plan.cell(row=current_row, column=2, value=week) c2.font = Font(name="Arial", bold=True, size=11) c2.fill = solid(GOLD) if "REVISION" in subj.upper() or "REVISION" in tasks.upper() else solid(bg) c2.alignment = center() c2.border = border_thin() c3 = ws_plan.cell(row=current_row, column=3, value=dates) c3.font = font(size=10, italic=True) c3.fill = solid(bg) c3.alignment = center() c3.border = border_thin() c4 = ws_plan.cell(row=current_row, column=4, value=subj) c4.font = Font(name="Arial", bold=True, size=11, color=DARK_NAVY) c4.fill = solid(bg) c4.alignment = left() c4.border = border_thin() c5 = ws_plan.cell(row=current_row, column=5, value=tasks) c5.font = font(size=10) c5.fill = solid(bg) c5.alignment = left() c5.border = border_thin() c6 = ws_plan.cell(row=current_row, column=6, value=status) c6.font = font(size=11) c6.fill = solid(LIGHT_GRAY) c6.alignment = center() c6.border = border_thin() c7 = ws_plan.cell(row=current_row, column=7, value="") c7.font = font(size=11) c7.fill = solid(LIGHT_GRAY) c7.alignment = center() c7.border = border_thin() c8 = ws_plan.cell(row=current_row, column=8, value=notes) c8.font = font(size=10, italic=True, color=DARK_GRAY) c8.fill = solid(bg) c8.alignment = left() c8.border = border_thin() # Add dropdown validation for status dv = DataValidation( type="list", formula1='"⬜ Not Started,🔄 In Progress,✅ Completed,🔁 Need Revision"', allow_blank=True ) ws_plan.add_data_validation(dv) dv.add(c6) current_row += 1 print("Monthly planner created.") # ═══════════════════════════════════════════════════════════════════ # FUNCTION: Create individual subject sheets # ═══════════════════════════════════════════════════════════════════ def create_subject_sheet(wb, sheet_title, subject_name, code, color, marks, topics): ws = wb.create_sheet(sheet_title) # Column widths widths = [2, 6, 52, 20, 15, 18, 30, 18, 2] for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w # Header ws.row_dimensions[1].height = 40 ws.merge_cells("B1:H1") ws["B1"] = f"📖 {subject_name} ({code})" ws["B1"].font = Font(name="Arial", bold=True, size=15, color=WHITE) ws["B1"].fill = solid(DARK_NAVY) ws["B1"].alignment = center() ws.row_dimensions[2].height = 22 ws.merge_cells("B2:H2") sub_info = f"Total Topics: {len(topics)} | Exam Marks: {marks if marks > 0 else 'Theory'} | Exam Duration: 3 Hours" ws["B2"] = sub_info ws["B2"].font = Font(name="Arial", size=10, color=WHITE, italic=True) ws["B2"].fill = solid(TEAL) ws["B2"].alignment = center() ws.row_dimensions[3].height = 8 # Column headers row 4 ws.row_dimensions[4].height = 28 col_headers = ["", "#", "TOPIC", "TARGET DATE", "DATE STUDIED", "STATUS", "NOTES / WEAK AREAS", "SELF-SCORE (1-10)", ""] for col, h in enumerate(col_headers, 1): c = ws.cell(row=4, column=col, value=h) c.font = Font(name="Arial", bold=True, size=11, color=WHITE) c.fill = solid(DARK_NAVY) c.alignment = center() c.border = border_thin() # Topic rows for i, topic in enumerate(topics): row = 5 + i ws.row_dimensions[row].height = 28 bg = color if (i % 2 == 0) else LIGHT_GRAY # Serial number c_num = ws.cell(row=row, column=2, value=i + 1) c_num.font = Font(name="Arial", bold=True, size=11, color=DARK_NAVY) c_num.fill = solid(bg) c_num.alignment = center() c_num.border = border_thin() # Topic name c_topic = ws.cell(row=row, column=3, value=topic) c_topic.font = font(size=11) c_topic.fill = solid(bg) c_topic.alignment = left() c_topic.border = border_thin() # Target date c_target = ws.cell(row=row, column=4, value="") c_target.font = font(size=11, color=DARK_GRAY) c_target.fill = solid(LIGHT_GRAY) c_target.alignment = center() c_target.border = border_thin() c_target.number_format = "DD-MMM-YYYY" # Date studied c_date = ws.cell(row=row, column=5, value="") c_date.font = font(size=11, color=DARK_GRAY) c_date.fill = solid(LIGHT_GRAY) c_date.alignment = center() c_date.border = border_thin() c_date.number_format = "DD-MMM-YYYY" # Status dropdown c_status = ws.cell(row=row, column=6, value="⬜ Not Started") c_status.font = font(size=11) c_status.fill = solid(LIGHT_GRAY) c_status.alignment = center() c_status.border = border_thin() dv = DataValidation( type="list", formula1='"⬜ Not Started,🔄 In Progress,✅ Completed,🔁 Need Revision"', allow_blank=True ) ws.add_data_validation(dv) dv.add(c_status) # Notes c_notes = ws.cell(row=row, column=7, value="") c_notes.font = font(size=10, italic=True, color=DARK_GRAY) c_notes.fill = solid(LIGHT_GRAY) c_notes.alignment = left() c_notes.border = border_thin() # Self score c_score = ws.cell(row=row, column=8, value="") c_score.font = Font(name="Arial", bold=True, size=12, color=TEAL) c_score.fill = solid(LIGHT_GRAY) c_score.alignment = center() c_score.border = border_thin() score_dv = DataValidation( type="whole", operator="between", formula1="1", formula2="10", allow_blank=True, showErrorMessage=True, errorTitle="Invalid Score", error="Please enter a number between 1 and 10" ) ws.add_data_validation(score_dv) score_dv.add(c_score) # Conditional formatting for status column completed_fill = PatternFill("solid", fgColor="D5F5E3") inprog_fill = PatternFill("solid", fgColor="FFF9C4") revision_fill = PatternFill("solid", fgColor="FAD7A0") status_range = f"F5:F{4+len(topics)}" ws.conditional_formatting.add(status_range, FormulaRule(formula=[f'F5="✅ Completed"'], fill=completed_fill)) ws.conditional_formatting.add(status_range, FormulaRule(formula=[f'F5="🔄 In Progress"'], fill=inprog_fill)) ws.conditional_formatting.add(status_range, FormulaRule(formula=[f'F5="🔁 Need Revision"'], fill=revision_fill)) # Color scale for self-score score_range = f"H5:H{4+len(topics)}" ws.conditional_formatting.add(score_range, ColorScaleRule( start_type="num", start_value=1, start_color="E63946", mid_type="num", mid_value=5, mid_color="FFA500", end_type="num", end_value=10, end_color="2DC653" ) ) # Summary row at bottom summary_row = 5 + len(topics) + 1 ws.row_dimensions[summary_row].height = 8 summary_row += 1 ws.row_dimensions[summary_row].height = 26 ws.merge_cells(f"B{summary_row}:C{summary_row}") s1 = ws.cell(row=summary_row, column=2, value="📊 SUMMARY") s1.font = Font(name="Arial", bold=True, size=11, color=WHITE) s1.fill = solid(DARK_NAVY) s1.alignment = center() s1.border = border_thin() s2 = ws.cell(row=summary_row, column=4, value=f"Total: {len(topics)}") s2.font = Font(name="Arial", bold=True, size=11, color=WHITE) s2.fill = solid(DARK_NAVY) s2.alignment = center() s2.border = border_thin() completed_formula = f'=COUNTIF(F5:F{4+len(topics)},"✅ Completed")' s3 = ws.cell(row=summary_row, column=5) s3.value = completed_formula s3.font = Font(name="Arial", bold=True, size=11, color=GOLD) s3.fill = solid(DARK_NAVY) s3.alignment = center() s3.border = border_thin() s3.number_format = '"✅ Done: "0' pct_formula = f'=IF({len(topics)}=0,0,{completed_formula}/{len(topics)})' s4 = ws.cell(row=summary_row, column=6) s4.value = pct_formula s4.font = Font(name="Arial", bold=True, size=12, color=GOLD) s4.fill = solid(DARK_NAVY) s4.alignment = center() s4.border = border_thin() s4.number_format = '"Progress: "0%' need_rev = ws.cell(row=summary_row, column=7) need_rev.value = f'=COUNTIF(F5:F{4+len(topics)},"🔁 Need Revision")' need_rev.font = Font(name="Arial", bold=True, size=11, color=ORANGE) need_rev.fill = solid(DARK_NAVY) need_rev.alignment = center() need_rev.border = border_thin() need_rev.number_format = '"⚠ Revise: "0' avg_score = ws.cell(row=summary_row, column=8) avg_score.value = f'=IFERROR(AVERAGEIF(H5:H{4+len(topics)},">0"),"N/A")' avg_score.font = Font(name="Arial", bold=True, size=11, color=WHITE) avg_score.fill = solid(DARK_NAVY) avg_score.alignment = center() avg_score.border = border_thin() avg_score.number_format = '"Avg Score: "0.0' return ws # Create all 8 subject sheets sheet_map = {} for sname, sdata in subjects_data.items(): short = sdata["code"] sheet_title = f"📖 {short}" ws_sub = create_subject_sheet( wb, sheet_title=sheet_title, subject_name=sname, code=sdata["code"], color=sdata["color"], marks=sdata["marks"], topics=sdata["topics"] ) sheet_map[sname] = (sheet_title, len(sdata["topics"])) print(f"Created sheet: {sheet_title}") # ═══════════════════════════════════════════════════════════════════ # Update Dashboard: link Completed counts to subject sheets # ═══════════════════════════════════════════════════════════════════ ws_dash = wb["📊 Dashboard"] subject_sheet_order = list(subjects_data.keys()) for i, sname in enumerate(subject_sheet_order): row = 7 + i code = subjects_data[sname]["code"] sheet_title = f"📖 {code}" n_topics = len(subjects_data[sname]["topics"]) # Completed count from subject sheet summary row summary_row_num = 5 + n_topics + 2 safe_title = sheet_title.replace("'", "''") formula = f"='{safe_title}'!E{summary_row_num}" # Actually let's use COUNTIF directly countif_formula = f"=COUNTIF('{safe_title}'!F5:F{4+n_topics},\"✅ Completed\")" ws_dash.cell(row=row, column=4).value = countif_formula ws_dash.cell(row=row, column=4).number_format = "0" ws_dash.cell(row=row, column=4).font = Font(name="Arial", bold=True, size=12, color=TEAL) ws_dash.cell(row=row, column=4).fill = solid(LIGHT_GRAY) ws_dash.cell(row=row, column=4).alignment = center() ws_dash.cell(row=row, column=4).border = border_thin() # Weak topics (Need Revision count) weak_formula = f"=COUNTIF('{safe_title}'!F5:F{4+n_topics},\"🔁 Need Revision\")" ws_dash.cell(row=row, column=6).value = weak_formula ws_dash.cell(row=row, column=6).number_format = "0" ws_dash.cell(row=row, column=6).font = Font(name="Arial", bold=True, size=11, color=RED) ws_dash.cell(row=row, column=6).fill = solid(LIGHT_GRAY) ws_dash.cell(row=row, column=6).alignment = center() ws_dash.cell(row=row, column=6).border = border_thin() print("Dashboard formulas linked.") # ═══════════════════════════════════════════════════════════════════ # SHEET: Daily Log # ═══════════════════════════════════════════════════════════════════ ws_log = wb.create_sheet("📝 Daily Log") log_widths = [2, 14, 22, 40, 20, 16, 30, 2] for i, w in enumerate(log_widths, 1): ws_log.column_dimensions[get_column_letter(i)].width = w ws_log.row_dimensions[1].height = 36 ws_log.merge_cells("B1:G1") ws_log["B1"] = "📝 DAILY STUDY LOG — Track Every Study Session" ws_log["B1"].font = Font(name="Arial", bold=True, size=15, color=WHITE) ws_log["B1"].fill = solid(DARK_NAVY) ws_log["B1"].alignment = center() ws_log.row_dimensions[2].height = 8 ws_log.row_dimensions[3].height = 26 log_headers = ["", "DATE", "SUBJECT", "TOPIC(S) STUDIED", "HOURS SPENT", "MOOD (1-5 ⭐)", "WHAT TO REVISE TOMORROW", ""] for col, h in enumerate(log_headers, 1): c = ws_log.cell(row=3, column=col, value=h) c.font = Font(name="Arial", bold=True, size=11, color=WHITE) c.fill = solid(TEAL) c.alignment = center() c.border = border_thin() # Pre-fill 90 log rows (3 months of daily entries) for i in range(90): r = 4 + i ws_log.row_dimensions[r].height = 22 bg = LIGHT_GRAY if (i % 2 == 0) else WHITE date_cell = ws_log.cell(row=r, column=2, value="") date_cell.number_format = "DD-MMM-YYYY (DDD)" date_cell.fill = solid(bg) date_cell.border = border_thin() date_cell.alignment = center() date_cell.font = font(size=10) for col in [3, 4, 5, 6, 7]: c = ws_log.cell(row=r, column=col, value="") c.fill = solid(bg) c.border = border_thin() c.alignment = left() if col in [3,4,7] else center() c.font = font(size=10) # Hours validation hours_dv = DataValidation(type="decimal", operator="between", formula1="0", formula2="16", allow_blank=True) ws_log.add_data_validation(hours_dv) hours_dv.add(ws_log.cell(row=r, column=5)) # Mood validation mood_dv = DataValidation(type="whole", operator="between", formula1="1", formula2="5", allow_blank=True) ws_log.add_data_validation(mood_dv) mood_dv.add(ws_log.cell(row=r, column=6)) # Subject dropdown subj_dv = DataValidation( type="list", formula1='"BRDT201,BRDT202,BRDT203,BRDT204,BRDT205,BRDT206,BRDT208,BRDT209,Multiple,Revision"', allow_blank=True ) ws_log.add_data_validation(subj_dv) subj_dv.add(ws_log.cell(row=r, column=3)) # Summary row at bottom sum_row = 4 + 90 + 1 ws_log.row_dimensions[sum_row].height = 8 sum_row += 1 ws_log.row_dimensions[sum_row].height = 26 ws_log.merge_cells(f"B{sum_row}:D{sum_row}") s = ws_log.cell(row=sum_row, column=2, value="📊 TOTAL STUDY HOURS (3 Months)") s.font = Font(name="Arial", bold=True, size=12, color=WHITE) s.fill = solid(DARK_NAVY) s.alignment = center() s.border = border_medium() total_hrs = ws_log.cell(row=sum_row, column=5, value=f"=SUM(E4:E{4+89})") total_hrs.font = Font(name="Arial", bold=True, size=14, color=GOLD) total_hrs.fill = solid(DARK_NAVY) total_hrs.alignment = center() total_hrs.border = border_medium() total_hrs.number_format = '0.0" hrs"' print("Daily log created.") # ═══════════════════════════════════════════════════════════════════ # FREEZE PANES on all sheets # ═══════════════════════════════════════════════════════════════════ wb["📊 Dashboard"].freeze_panes = "B7" wb["📅 Monthly Planner"].freeze_panes = "B4" wb["📝 Daily Log"].freeze_panes = "B4" for sname, sdata in subjects_data.items(): code = sdata["code"] sheet_title = f"📖 {code}" wb[sheet_title].freeze_panes = "C5" # ═══════════════════════════════════════════════════════════════════ # Save # ═══════════════════════════════════════════════════════════════════ output_path = "/tmp/workspace/study-tracker/BSc_RDT_2ndYear_Study_Tracker.xlsx" wb.save(output_path) print(f"\n✅ Study tracker saved to: {output_path}") PYEOF echo "Script written successfully"

cd /tmp/workspace/study-tracker && python build_tracker.py

python -c " import openpyxl wb = openpyxl.load_workbook('/tmp/workspace/study-tracker/BSc_RDT_2ndYear_Study_Tracker.xlsx') print('Sheets:', wb.sheetnames) ws = wb['📊 Dashboard'] print('Dashboard D7 (completed formula):', ws['D7'].value) print('Dashboard E7 (progress formula):', ws['E7'].value) ws2 = wb['📖 BRDT201'] print('BRDT201 row 5 topic:', ws2['C5'].value) print('BRDT201 row 5 status:', ws2['F5'].value) print('Total rows in BRDT201:', ws2.max_row) import os size = os.path.getsize('/tmp/workspace/study-tracker/BSc_RDT_2ndYear_Study_Tracker.xlsx') print(f'File size: {size/1024:.1f} KB') "

best YouTube lectures renal dialysis technology anatomy physiology kidney GFR haemodialysis BSc

cat > /tmp/workspace/study-tracker/build_tracker_v2.py << 'PYEOF' import openpyxl from openpyxl.styles import PatternFill, Font, Alignment, Border, Side, GradientFill from openpyxl.utils import get_column_letter from openpyxl.formatting.rule import ColorScaleRule, FormulaRule from openpyxl.worksheet.datavalidation import DataValidation import datetime wb = openpyxl.Workbook() # ─── COLORS ────────────────────────────────────────────────────── DARK_NAVY = "1B2A4A" TEAL = "0D7377" GOLD = "F4A261" WHITE = "FFFFFF" LIGHT_GRAY = "F5F7FA" MID_GRAY = "D9DEE6" DARK_GRAY = "6C757D" RED = "E63946" YELLOW_BG = "FFF9C4" ORANGE = "FFA500" SUBJ_COLORS = ["D6EAF8","D5F5E3","FDEBD0","F9EBEA","E8DAEF","EAF0FB","EAF2FF","FFF3CD"] def solid(h): return PatternFill("solid", fgColor=h) def fnt(bold=False,size=11,color="000000",italic=False): return Font(name="Arial",bold=bold,size=size,color=color,italic=italic) def thin(): s=Side(style="thin",color="BBBBBB") return Border(left=s,right=s,top=s,bottom=s) def med(): s=Side(style="medium",color=DARK_NAVY) return Border(left=s,right=s,top=s,bottom=s) def ctr(): return Alignment(horizontal="center",vertical="center",wrap_text=True) def lft(): return Alignment(horizontal="left",vertical="center",wrap_text=True) # ═══════════════════════════════════════════════════════════════════ # LECTURE LINKS — curated YouTube resources per topic # ═══════════════════════════════════════════════════════════════════ lecture_links = { # BRDT201 - Anatomy & Physiology "Basic Anatomy of Urinary System (Kidney, Bladder, Ureter, Urethra, Prostate)": "https://www.youtube.com/watch?v=ctGkLYuUCvU", "Histology of Kidney": "https://www.youtube.com/watch?v=V9HDaOKEtOk", "Blood Supply of Kidney": "https://www.youtube.com/watch?v=ctGkLYuUCvU", "Development of Kidney in Brief": "https://www.youtube.com/playlist?list=PLYcLrRDaR8_fP4WuVjZRuhQl_RcFw2-Sc", "Anatomy of Peritoneum & Concept of Abdominal Hernias": "https://www.youtube.com/results?search_query=peritoneum+anatomy+abdominal+hernias+lecture", "Anatomy of Vascular System - Upper Limb Vessels": "https://www.youtube.com/results?search_query=upper+limb+vessels+anatomy+lecture", "Anatomy of Vascular System - Neck Vessels": "https://www.youtube.com/results?search_query=neck+vessels+anatomy+lecture", "Anatomy of Vascular System - Femoral Vessels": "https://www.youtube.com/results?search_query=femoral+vessels+anatomy+lecture", "Mechanism of Urine Formation": "https://www.youtube.com/watch?v=rwZIT_N75Bs", "Glomerular Filtration Rate (GFR)": "https://www.youtube.com/watch?v=h1pvqj4z9aM", "Clearance Studies": "https://www.youtube.com/playlist?list=PLO2O9UjkQxLfYXhx_pkf8o_uxhArrRzUq", "Physiological Values (Urea, Creatinine, Electrolytes, Calcium, Phosphorus, Uric Acid, Magnesium, Glucose)": "https://www.youtube.com/results?search_query=renal+physiological+values+urea+creatinine+electrolytes+lecture", "24-Hour Urinary Indices": "https://www.youtube.com/results?search_query=24+hour+urine+collection+indices+kidney+lecture", "Physiology of Renal Circulation - Factors Contributing & Modifying": "https://www.youtube.com/playlist?list=PLbKSbFnKYVY2NV3CWR7UR9VLRYkVJVpfG", "Autoregulation of Renal Circulation": "https://www.youtube.com/results?search_query=renal+autoregulation+physiology+lecture", "Hormones Produced by Kidney": "https://www.youtube.com/watch?v=V9HDaOKEtOk", "Physiological Alterations in Pregnancy": "https://www.youtube.com/results?search_query=renal+physiology+changes+pregnancy+lecture", "Haemostasis - Coagulation Cascade & Factors, BT, CT, PT, PTT, Thrombin Time": "https://www.youtube.com/results?search_query=coagulation+cascade+factors+BT+PT+PTT+lecture", "Acid-Base Balance - Basic Principles": "https://www.youtube.com/results?search_query=acid+base+balance+physiology+lecture", "Acid-Base Abnormalities (Hypokalemia, Hyperkalemia, Hyponatremia, Hypernatremia, Hypocalcemia, Hypercalcemia)": "https://www.youtube.com/results?search_query=electrolyte+imbalance+hypokalemia+hyperkalemia+lecture", "Basic Nutrition in Renal Diseases": "https://www.youtube.com/results?search_query=nutrition+in+renal+disease+kidney+diet+lecture", # BRDT202 - Pharmacology "IV Fluid Therapy with Special Emphasis in Renal Diseases": "https://www.youtube.com/results?search_query=IV+fluid+therapy+renal+disease+lecture", "Diuretics - Classification, Actions, Dosage, Side Effects & Contraindications": "https://www.youtube.com/results?search_query=diuretics+classification+pharmacology+lecture", "Antihypertensives - Classification, Actions, Dosage, Side Effects & Contraindications": "https://www.youtube.com/results?search_query=antihypertensive+drugs+classification+pharmacology+lecture", "Reference During Dialysis, Vasopressors, Drugs Used in Hypotension": "https://www.youtube.com/results?search_query=vasopressors+hypotension+dialysis+pharmacology+lecture", "Drugs & Dialysis - Dose & Duration of Administration": "https://www.youtube.com/results?search_query=drug+dosing+dialysis+renal+failure+pharmacology", "Dialysable Drugs (Phenobarbitone, Lithium, Methanol etc.)": "https://www.youtube.com/results?search_query=dialysable+drugs+phenobarbitone+lithium+dialysis", "Vitamin D & Analogues, Phosphate Binders, Iron, Folic Acid & Other Vitamins": "https://www.youtube.com/results?search_query=vitamin+D+phosphate+binders+renal+disease+pharmacology", "Erythropoietin in Detail": "https://www.youtube.com/results?search_query=erythropoietin+EPO+pharmacology+lecture+renal+anemia", "Heparin Including Low Molecular Weight Heparin": "https://www.youtube.com/results?search_query=heparin+LMWH+pharmacology+anticoagulant+lecture", "Protamine Sulphate": "https://www.youtube.com/results?search_query=protamine+sulphate+heparin+antidote+pharmacology", "Formalin, Sodium Hypochlorite, Hydrogen Peroxide - Disinfectants & Adverse Effects": "https://www.youtube.com/results?search_query=disinfectants+formalin+sodium+hypochlorite+dialysis+lecture", "Haemodialysis Concentrates - Composition & Dilution (Acetate & Bicarbonates)": "https://www.youtube.com/results?search_query=haemodialysis+concentrate+acetate+bicarbonate+composition+lecture", "Peritoneal Dialysis Fluid & Hypertonic Solutions - Composition": "https://www.youtube.com/results?search_query=peritoneal+dialysis+fluid+hypertonic+solution+composition+lecture", "Potassium Exchange Resins - Mode of Administration": "https://www.youtube.com/results?search_query=potassium+exchange+resin+kayexalate+pharmacology+lecture", # BRDT203 - Renal Diseases "Acute Renal Failure - Clinical Manifestations, Evaluation & Management": "https://www.youtube.com/results?search_query=acute+renal+failure+AKI+clinical+manifestations+management+lecture", "Nephrotic Syndrome - Primary & Secondary": "https://www.youtube.com/results?search_query=nephrotic+syndrome+primary+secondary+lecture+nephrology", "Nephritic Syndrome": "https://www.youtube.com/results?search_query=nephritic+syndrome+lecture+nephrology", "UTI - Urinary Tract Infections": "https://www.youtube.com/results?search_query=urinary+tract+infection+UTI+lecture+pathology", "Asymptomatic Urinary Abnormalities": "https://www.youtube.com/results?search_query=asymptomatic+urinary+abnormalities+proteinuria+hematuria+lecture", "Chronic Renal Failure": "https://www.youtube.com/results?search_query=chronic+renal+failure+CKD+lecture+nephrology", "Renal Stone Diseases": "https://www.youtube.com/results?search_query=renal+stone+disease+nephrolithiasis+lecture", "Obstructive Uropathies": "https://www.youtube.com/results?search_query=obstructive+uropathy+hydronephrosis+lecture", "Congenital & Inherited Renal Diseases": "https://www.youtube.com/results?search_query=polycystic+kidney+disease+congenital+renal+lecture", "Tumors of Kidney": "https://www.youtube.com/results?search_query=renal+cell+carcinoma+wilms+tumor+kidney+lecture", "Pregnancy Associated Renal Diseases": "https://www.youtube.com/results?search_query=pregnancy+renal+disease+preeclampsia+lecture+nephrology", "Renal Vascular Disorders & Hypertension Associated Renal Diseases": "https://www.youtube.com/results?search_query=renal+vascular+disorders+renovascular+hypertension+lecture", # BRDT204 - Pathology & Microbiology "Congenital Abnormalities of Urinary System": "https://www.youtube.com/results?search_query=congenital+abnormalities+urinary+system+pathology+lecture", "Classification of Renal Diseases": "https://www.youtube.com/results?search_query=classification+renal+diseases+pathology+lecture", "Glomerular Diseases - Causes, Types & Pathology": "https://www.youtube.com/results?search_query=glomerular+diseases+glomerulonephritis+pathology+lecture", "Tubulointerstitial Diseases": "https://www.youtube.com/results?search_query=tubulointerstitial+nephritis+pathology+lecture", "Renal Vascular Disorders": "https://www.youtube.com/results?search_query=renal+vascular+disorders+pathology+lecture", "End Stage Renal Diseases - Causes & Pathology": "https://www.youtube.com/results?search_query=end+stage+renal+disease+ESRD+pathology+lecture", "Pathology of Kidney in Hypertension, Diabetes Mellitus, Pregnancy": "https://www.youtube.com/results?search_query=diabetic+nephropathy+hypertensive+nephropathy+pathology+lecture", "Pathology of Peritoneum - Peritonitis (Bacterial, Tubular, Sclerosing, Dialysis-induced)": "https://www.youtube.com/results?search_query=peritonitis+sclerosing+peritonitis+pathology+dialysis+lecture", "Pathology of Urinary Tract Infections": "https://www.youtube.com/results?search_query=UTI+pathology+urinary+tract+infection+lecture", "Pyelonephritis & Tuberculous Pyelonephritis": "https://www.youtube.com/results?search_query=pyelonephritis+tuberculous+pyelonephritis+pathology+lecture", "Hepatotrophic Viruses - Mode of Transfusion, Universal Precautions, Vaccinations": "https://www.youtube.com/results?search_query=hepatitis+B+C+hepatotrophic+viruses+microbiology+lecture", "HIV - Mode of Transfusion, Universal Precautions": "https://www.youtube.com/results?search_query=HIV+AIDS+microbiology+universal+precautions+lecture", "Opportunistic Infections": "https://www.youtube.com/results?search_query=opportunistic+infections+HIV+immunocompromised+microbiology+lecture", "Microbiology of Urinary Tract Infections": "https://www.youtube.com/results?search_query=microbiology+UTI+E+coli+urinary+tract+infection+lecture", "Microbiology of Vascular Access Infection (Femoral, Jugular, Subclavian Catheters)": "https://www.youtube.com/results?search_query=vascular+access+infection+catheter+microbiology+dialysis", "Sampling Methodologies for Culture & Sensitivity": "https://www.youtube.com/results?search_query=culture+sensitivity+sampling+methodology+microbiology+lecture", # BRDT205 - Sociology "Introduction: Definition, Scope & Methods of Sociological Investigations": "https://www.youtube.com/results?search_query=introduction+sociology+definition+scope+methods+lecture", "Relation to Anthropology, Psychology, Social Psychology": "https://www.youtube.com/results?search_query=sociology+anthropology+psychology+relation+lecture", "Social Factors in Health and Disease": "https://www.youtube.com/results?search_query=social+factors+health+disease+sociology+lecture", "Socialization: Meaning, Nature, Types & Agencies": "https://www.youtube.com/results?search_query=socialization+types+agencies+sociology+lecture", "Social Groups: Formal, Informal, Primary, Secondary": "https://www.youtube.com/results?search_query=social+groups+primary+secondary+formal+informal+sociology", "Family: Meaning, Functions, Types, Changing Patterns": "https://www.youtube.com/results?search_query=family+types+functions+sociology+lecture", "Community: Rural, Urban, Tribal - Features & Health Hazards": "https://www.youtube.com/results?search_query=community+rural+urban+tribal+sociology+health+hazards", "Culture and Health: Concept, Culture & Health Disorders": "https://www.youtube.com/results?search_query=culture+health+sociology+lecture", "Social Change: Meaning, Factors, Stress, Deviance, Health Programmes": "https://www.youtube.com/results?search_query=social+change+factors+deviance+sociology+lecture", "Social Problems of Disabled (Population, Poverty, Beggary, Juvenile Delinquency etc.)": "https://www.youtube.com/results?search_query=social+problems+disability+poverty+sociology+lecture", "Social Security & Social Legislation": "https://www.youtube.com/results?search_query=social+security+social+legislation+India+lecture", "Social Work: Meaning & Role of Medical Social Worker": "https://www.youtube.com/results?search_query=medical+social+worker+role+social+work+lecture", # BRDT206 - Indian Constitution "Unit I: Meaning & Making of the Indian Constitution (1946-1949)": "https://www.youtube.com/results?search_query=making+Indian+constitution+constituent+assembly+lecture", "Unit II: Democratic Institutions - Bicameral Legislature (Centre & States)": "https://www.youtube.com/results?search_query=Indian+parliament+bicameral+legislature+lok+sabha+rajya+sabha+lecture", "Unit III: Fundamental Rights and Duties": "https://www.youtube.com/results?search_query=fundamental+rights+duties+Indian+constitution+lecture", "Unit IV: Directive Principles of State Policy": "https://www.youtube.com/results?search_query=directive+principles+state+policy+DPSP+Indian+constitution+lecture", "Unit V: Special Rights for Dalits, Backwards, Women, Children & Minorities": "https://www.youtube.com/results?search_query=special+provisions+SC+ST+women+children+Indian+constitution+lecture", "Unit VI: Doctrine of Separation of Powers (Legislature, Executive, Judiciary)": "https://www.youtube.com/results?search_query=separation+powers+legislature+executive+judiciary+India+lecture", "Unit VII: Election Commission & State Public Service Commissions": "https://www.youtube.com/results?search_query=election+commission+India+UPSC+state+PSC+lecture", "Unit VIII: Method of Amending the Constitution": "https://www.youtube.com/results?search_query=amendment+Indian+constitution+Article+368+lecture", "Unit IX: Enforcing Rights through Writs (Habeas Corpus, Mandamus, etc.)": "https://www.youtube.com/results?search_query=writs+habeas+corpus+mandamus+certiorari+Indian+constitution+lecture", "Unit X: Constitution and Sustainable Development in India": "https://www.youtube.com/results?search_query=constitution+sustainable+development+India+environment+lecture", # BRDT208 - Nutrition "Balanced Diet - Definition & Concept": "https://www.youtube.com/results?search_query=balanced+diet+definition+nutrition+lecture", "Caloric Values of Carbohydrates, Proteins and Fats": "https://www.youtube.com/results?search_query=caloric+values+carbohydrates+proteins+fats+nutrition+lecture", "Total Daily Caloric Requirements (Adult Male & Female)": "https://www.youtube.com/results?search_query=daily+caloric+requirements+adult+nutrition+lecture", "RDA - Definition & Standard Values for Nutrients": "https://www.youtube.com/results?search_query=RDA+recommended+dietary+allowance+nutrition+lecture", "Basal Metabolic Rate (BMR) - Definition, Magnitude & Factors": "https://www.youtube.com/results?search_query=basal+metabolic+rate+BMR+definition+factors+lecture", "Thermic Effect / SDA of Food": "https://www.youtube.com/results?search_query=thermic+effect+food+SDA+specific+dynamic+action+nutrition+lecture", "Carbohydrates - Daily Requirement & Dietary Fibers": "https://www.youtube.com/results?search_query=carbohydrates+dietary+fiber+nutrition+daily+requirement+lecture", "Proteins - Daily Requirement, Biological Value & Mutual Supplementation": "https://www.youtube.com/results?search_query=protein+biological+value+mutual+supplementation+nutrition+lecture", "Fats - Essential Fatty Acids, Saturated & Unsaturated Fatty Acids": "https://www.youtube.com/results?search_query=essential+fatty+acids+omega+3+6+saturated+unsaturated+lecture", "Malnutrition": "https://www.youtube.com/results?search_query=malnutrition+types+causes+effects+nutrition+lecture", "Introduction to Science of Nutrition - Food Patterns & Influencing Factors": "https://www.youtube.com/results?search_query=introduction+nutrition+food+patterns+science+lecture", "Classification of Nutrients (Macro & Micronutrients) - All Types": "https://www.youtube.com/results?search_query=classification+nutrients+macronutrients+micronutrients+lecture", "Planning Diets - Balanced Diet & Renal Diet": "https://www.youtube.com/results?search_query=diet+planning+renal+diet+balanced+diet+lecture", "Introduction to Cookery": "https://www.youtube.com/results?search_query=introduction+cookery+methods+cooking+food+lecture", # BRDT209 - Basics of Dialysis Technology "Indications of Dialysis": "https://www.youtube.com/results?search_query=indications+dialysis+AEIOU+renal+failure+lecture", "Types of Dialysis": "https://www.youtube.com/results?search_query=types+dialysis+hemodialysis+peritoneal+CRRT+lecture", "Principles of Dialysis - Definitions": "https://www.youtube.com/results?search_query=principles+dialysis+diffusion+ultrafiltration+osmosis+lecture", "Haemodialysis Apparatus - Types of Dialyser & Membrane": "https://www.youtube.com/results?search_query=haemodialysis+apparatus+dialyser+membrane+types+lecture", "Types of Vascular Access for Haemodialysis": "https://www.youtube.com/results?search_query=vascular+access+AV+fistula+graft+catheter+haemodialysis+lecture", "Introduction to Haemodialysis Machine": "https://www.youtube.com/results?search_query=haemodialysis+machine+introduction+circuit+lecture", "Priming of Dialysis Apparatus": "https://www.youtube.com/results?search_query=priming+dialysis+apparatus+haemodialysis+lecture", "Dialyser Reuse": "https://www.youtube.com/results?search_query=dialyser+reuse+reprocessing+haemodialysis+lecture", "Common Complications of Haemodialysis": "https://www.youtube.com/results?search_query=complications+haemodialysis+hypotension+cramps+lecture", "Monitoring of Patients During Dialysis": "https://www.youtube.com/results?search_query=monitoring+patients+during+haemodialysis+dialysis+lecture", "Nutrition - Introduction to Science of Nutrition": "https://www.youtube.com/results?search_query=nutrition+introduction+science+dialysis+renal+lecture", "Classification of Nutrients (Proteins, Carbs, Fats, Water, Minerals, Vitamins)": "https://www.youtube.com/results?search_query=classification+nutrients+proteins+carbs+fats+minerals+vitamins+lecture", "Planning Diets - Balanced Diet, Food Groups, Renal Diet": "https://www.youtube.com/results?search_query=renal+diet+planning+food+groups+dialysis+nutrition+lecture", "Introduction to Cookery": "https://www.youtube.com/results?search_query=cookery+methods+cooking+food+preparation+lecture", } # ═══════════════════════════════════════════════════════════════════ # SUBJECTS DATA # ═══════════════════════════════════════════════════════════════════ subjects_data = { "BRDT201 - Anatomy & Physiology": { "code": "BRDT201", "color": SUBJ_COLORS[0], "marks": 80, "week": "Week 1-2 (Aug 5-18)", "topics": [ "Basic Anatomy of Urinary System (Kidney, Bladder, Ureter, Urethra, Prostate)", "Histology of Kidney", "Blood Supply of Kidney", "Development of Kidney in Brief", "Anatomy of Peritoneum & Concept of Abdominal Hernias", "Anatomy of Vascular System - Upper Limb Vessels", "Anatomy of Vascular System - Neck Vessels", "Anatomy of Vascular System - Femoral Vessels", "Mechanism of Urine Formation", "Glomerular Filtration Rate (GFR)", "Clearance Studies", "Physiological Values (Urea, Creatinine, Electrolytes, Calcium, Phosphorus, Uric Acid, Magnesium, Glucose)", "24-Hour Urinary Indices", "Physiology of Renal Circulation - Factors Contributing & Modifying", "Autoregulation of Renal Circulation", "Hormones Produced by Kidney", "Physiological Alterations in Pregnancy", "Haemostasis - Coagulation Cascade & Factors, BT, CT, PT, PTT, Thrombin Time", "Acid-Base Balance - Basic Principles", "Acid-Base Abnormalities (Hypokalemia, Hyperkalemia, Hyponatremia, Hypernatremia, Hypocalcemia, Hypercalcemia)", "Basic Nutrition in Renal Diseases", ] }, "BRDT202 - Pharmacology": { "code": "BRDT202", "color": SUBJ_COLORS[1], "marks": 80, "week": "Week 3-4 (Aug 19 - Sep 1)", "topics": [ "IV Fluid Therapy with Special Emphasis in Renal Diseases", "Diuretics - Classification, Actions, Dosage, Side Effects & Contraindications", "Antihypertensives - Classification, Actions, Dosage, Side Effects & Contraindications", "Reference During Dialysis, Vasopressors, Drugs Used in Hypotension", "Drugs & Dialysis - Dose & Duration of Administration", "Dialysable Drugs (Phenobarbitone, Lithium, Methanol etc.)", "Vitamin D & Analogues, Phosphate Binders, Iron, Folic Acid & Other Vitamins", "Erythropoietin in Detail", "Heparin Including Low Molecular Weight Heparin", "Protamine Sulphate", "Formalin, Sodium Hypochlorite, Hydrogen Peroxide - Disinfectants & Adverse Effects", "Haemodialysis Concentrates - Composition & Dilution (Acetate & Bicarbonates)", "Peritoneal Dialysis Fluid & Hypertonic Solutions - Composition", "Potassium Exchange Resins - Mode of Administration", ] }, "BRDT203 - Renal Diseases": { "code": "BRDT203", "color": SUBJ_COLORS[2], "marks": 80, "week": "Week 5-6 (Sep 5-18)", "topics": [ "Acute Renal Failure - Clinical Manifestations, Evaluation & Management", "Nephrotic Syndrome - Primary & Secondary", "Nephritic Syndrome", "UTI - Urinary Tract Infections", "Asymptomatic Urinary Abnormalities", "Chronic Renal Failure", "Renal Stone Diseases", "Obstructive Uropathies", "Congenital & Inherited Renal Diseases", "Tumors of Kidney", "Pregnancy Associated Renal Diseases", "Renal Vascular Disorders & Hypertension Associated Renal Diseases", ] }, "BRDT204 - Pathology & Microbiology": { "code": "BRDT204", "color": SUBJ_COLORS[3], "marks": 100, "week": "Week 7-8 (Sep 19 - Oct 4)", "topics": [ "Congenital Abnormalities of Urinary System", "Classification of Renal Diseases", "Glomerular Diseases - Causes, Types & Pathology", "Tubulointerstitial Diseases", "Renal Vascular Disorders", "End Stage Renal Diseases - Causes & Pathology", "Pathology of Kidney in Hypertension, Diabetes Mellitus, Pregnancy", "Pathology of Peritoneum - Peritonitis (Bacterial, Tubular, Sclerosing, Dialysis-induced)", "Pathology of Urinary Tract Infections", "Pyelonephritis & Tuberculous Pyelonephritis", "Hepatotrophic Viruses - Mode of Transfusion, Universal Precautions, Vaccinations", "HIV - Mode of Transfusion, Universal Precautions", "Opportunistic Infections", "Microbiology of Urinary Tract Infections", "Microbiology of Vascular Access Infection (Femoral, Jugular, Subclavian Catheters)", "Sampling Methodologies for Culture & Sensitivity", ] }, "BRDT205 - Sociology": { "code": "BRDT205", "color": SUBJ_COLORS[4], "marks": 0, "week": "Week 10-11 (Oct 17-25)", "topics": [ "Introduction: Definition, Scope & Methods of Sociological Investigations", "Relation to Anthropology, Psychology, Social Psychology", "Social Factors in Health and Disease", "Socialization: Meaning, Nature, Types & Agencies", "Social Groups: Formal, Informal, Primary, Secondary", "Family: Meaning, Functions, Types, Changing Patterns", "Community: Rural, Urban, Tribal - Features & Health Hazards", "Culture and Health: Concept, Culture & Health Disorders", "Social Change: Meaning, Factors, Stress, Deviance, Health Programmes", "Social Problems of Disabled (Population, Poverty, Beggary, Juvenile Delinquency etc.)", "Social Security & Social Legislation", "Social Work: Meaning & Role of Medical Social Worker", ] }, "BRDT206 - Indian Constitution": { "code": "BRDT206", "color": SUBJ_COLORS[5], "marks": 0, "week": "Week 11-12 (Oct 24 - Nov 1)", "topics": [ "Unit I: Meaning & Making of the Indian Constitution (1946-1949)", "Unit II: Democratic Institutions - Bicameral Legislature (Centre & States)", "Unit III: Fundamental Rights and Duties", "Unit IV: Directive Principles of State Policy", "Unit V: Special Rights for Dalits, Backwards, Women, Children & Minorities", "Unit VI: Doctrine of Separation of Powers (Legislature, Executive, Judiciary)", "Unit VII: Election Commission & State Public Service Commissions", "Unit VIII: Method of Amending the Constitution", "Unit IX: Enforcing Rights through Writs (Habeas Corpus, Mandamus, etc.)", "Unit X: Constitution and Sustainable Development in India", ] }, "BRDT208 - Nutrition": { "code": "BRDT208", "color": SUBJ_COLORS[6], "marks": 0, "week": "Week 10 (Oct 12-18)", "topics": [ "Balanced Diet - Definition & Concept", "Caloric Values of Carbohydrates, Proteins and Fats", "Total Daily Caloric Requirements (Adult Male & Female)", "RDA - Definition & Standard Values for Nutrients", "Basal Metabolic Rate (BMR) - Definition, Magnitude & Factors", "Thermic Effect / SDA of Food", "Carbohydrates - Daily Requirement & Dietary Fibers", "Proteins - Daily Requirement, Biological Value & Mutual Supplementation", "Fats - Essential Fatty Acids, Saturated & Unsaturated Fatty Acids", "Malnutrition", "Introduction to Science of Nutrition - Food Patterns & Influencing Factors", "Classification of Nutrients (Macro & Micronutrients) - All Types", "Planning Diets - Balanced Diet & Renal Diet", "Introduction to Cookery", ] }, "BRDT209 - Basics of Dialysis Tech": { "code": "BRDT209", "color": SUBJ_COLORS[7], "marks": 0, "week": "Week 9 (Oct 5-11)", "topics": [ "Indications of Dialysis", "Types of Dialysis", "Principles of Dialysis - Definitions", "Haemodialysis Apparatus - Types of Dialyser & Membrane", "Types of Vascular Access for Haemodialysis", "Introduction to Haemodialysis Machine", "Priming of Dialysis Apparatus", "Dialyser Reuse", "Common Complications of Haemodialysis", "Monitoring of Patients During Dialysis", "Nutrition - Introduction to Science of Nutrition", "Classification of Nutrients (Proteins, Carbs, Fats, Water, Minerals, Vitamins)", "Planning Diets - Balanced Diet, Food Groups, Renal Diet", "Introduction to Cookery", ] }, } # ═══════════════════════════════════════════════════════════════════ # SHEET 1: DASHBOARD # ═══════════════════════════════════════════════════════════════════ ws_dash = wb.active ws_dash.title = "📊 Dashboard" col_widths_dash = [2, 30, 12, 12, 14, 14, 18, 2] for i, w in enumerate(col_widths_dash, 1): ws_dash.column_dimensions[get_column_letter(i)].width = w ws_dash.row_dimensions[1].height = 8 ws_dash.row_dimensions[2].height = 46 ws_dash.merge_cells("B2:G2") ws_dash["B2"] = "📚 B.Sc RENAL DIALYSIS TECHNOLOGY — 2nd Year Study Tracker" ws_dash["B2"].font = Font(name="Arial", bold=True, size=18, color=WHITE) ws_dash["B2"].fill = solid(DARK_NAVY) ws_dash["B2"].alignment = ctr() ws_dash.row_dimensions[3].height = 22 ws_dash.merge_cells("B3:G3") ws_dash["B3"] = "Exam Preparation | August – November 2026 | 8 Subjects | Track Every Topic + Watch Lectures" ws_dash["B3"].font = Font(name="Arial", size=11, color=WHITE, italic=True) ws_dash["B3"].fill = solid(TEAL) ws_dash["B3"].alignment = ctr() ws_dash.row_dimensions[4].height = 8 ws_dash.row_dimensions[5].height = 28 hdrs = ["", "SUBJECT", "TOTAL\nTOPICS", "COMPLETED", "LECTURES\nWATCHED", "PROGRESS %", "STATUS", ""] for col, h in enumerate(hdrs, 1): c = ws_dash.cell(row=5, column=col, value=h) c.font = Font(name="Arial", bold=True, size=10, color=WHITE) c.fill = solid(DARK_NAVY) c.alignment = ctr() c.border = thin() for i, (sname, sd) in enumerate(subjects_data.items()): row = 6 + i ws_dash.row_dimensions[row].height = 26 total = len(sd["topics"]) color = sd["color"] code = sd["code"] sheet_t = f"📖 {code}" c2 = ws_dash.cell(row=row, column=2, value=sname) c2.font = Font(name="Arial", bold=True, size=11) c2.fill = solid(color); c2.alignment = lft(); c2.border = thin() c3 = ws_dash.cell(row=row, column=3, value=total) c3.font = fnt(bold=True, size=12); c3.fill = solid(color) c3.alignment = ctr(); c3.border = thin() # Completed = COUNTIF on subject sheet status column c4 = ws_dash.cell(row=row, column=4) c4.value = f"=COUNTIF('{sheet_t}'!G5:G{4+total},\"✅ Completed\")" c4.font = Font(name="Arial", bold=True, size=12, color=TEAL) c4.fill = solid(LIGHT_GRAY); c4.alignment = ctr(); c4.border = thin() c4.number_format = "0" # Lectures watched = COUNTIF on subject sheet lecture-watched column c5 = ws_dash.cell(row=row, column=5) c5.value = f"=COUNTIF('{sheet_t}'!I5:I{4+total},\"✅ Watched\")" c5.font = Font(name="Arial", bold=True, size=12, color="0D7377") c5.fill = solid(LIGHT_GRAY); c5.alignment = ctr(); c5.border = thin() c5.number_format = "0" c6 = ws_dash.cell(row=row, column=6) c6.value = f"=IF(C{row}=0,0,D{row}/C{row})" c6.font = Font(name="Arial", bold=True, size=12) c6.fill = solid(LIGHT_GRAY); c6.alignment = ctr(); c6.border = thin() c6.number_format = "0%" c7 = ws_dash.cell(row=row, column=7) c7.value = f'=IF(F{row}=0,"⬜ Not Started",IF(F{row}<0.5,"🔄 In Progress",IF(F{row}<1,"✅ Almost Done","🏆 Complete")))' c7.font = fnt(size=11); c7.fill = solid(LIGHT_GRAY) c7.alignment = ctr(); c7.border = thin() ws_dash.conditional_formatting.add("F6:F13", ColorScaleRule(start_type="num",start_value=0,start_color="E63946", mid_type="num",mid_value=0.5,mid_color="FFA500", end_type="num",end_value=1,end_color="2DC653")) # Totals row 14 ws_dash.row_dimensions[14].height = 30 for col, val, fmt in [ (2, "📈 OVERALL PROGRESS", "@"), (3, "=SUM(C6:C13)", "0"), (4, "=SUM(D6:D13)", "0"), (5, "=SUM(E6:E13)", "0"), (6, "=IF(C14=0,0,D14/C14)", "0%"), (7, '=IF(F14=1,"🏆 EXAM READY!",IF(F14>=0.8,"✅ Almost Ready","📖 Keep Going!"))', "@"), ]: c = ws_dash.cell(row=14, column=col, value=val) c.font = Font(name="Arial", bold=True, size=12, color=GOLD if col in [4,5,6,7] else WHITE) c.fill = solid(DARK_NAVY); c.alignment = ctr(); c.border = med() c.number_format = fmt # How to use ws_dash.row_dimensions[15].height = 10 ws_dash.row_dimensions[16].height = 22 ws_dash.merge_cells("B16:G16") ws_dash["B16"] = "ℹ️ HOW TO USE — Go to a subject tab → change STATUS → Dashboard auto-updates | Click lecture links to open YouTube" ws_dash["B16"].font = Font(name="Arial", size=10, color=WHITE, italic=True) ws_dash["B16"].fill = solid(TEAL) ws_dash["B16"].alignment = ctr() print("Dashboard built.") # ═══════════════════════════════════════════════════════════════════ # SHEET 2: MONTHLY PLANNER # ═══════════════════════════════════════════════════════════════════ ws_plan = wb.create_sheet("📅 Monthly Planner") plan_widths = [2,10,14,36,30,20,20,2] for i,w in enumerate(plan_widths,1): ws_plan.column_dimensions[get_column_letter(i)].width = w ws_plan.row_dimensions[1].height = 38 ws_plan.merge_cells("B1:G1") ws_plan["B1"] = "📅 3-MONTH WEEKLY STUDY PLANNER | Aug 5 – Nov 4, 2026" ws_plan["B1"].font = Font(name="Arial", bold=True, size=15, color=WHITE) ws_plan["B1"].fill = solid(DARK_NAVY) ws_plan["B1"].alignment = ctr() ws_plan.row_dimensions[2].height = 8 ws_plan.row_dimensions[3].height = 28 for col, h in enumerate(["","WEEK","DATES","SUBJECTS & TOPICS","KEY TASKS / FOCUS","STATUS","SELF-SCORE (1-10)",""],1): c = ws_plan.cell(row=3,column=col,value=h) c.font = Font(name="Arial",bold=True,size=11,color=WHITE) c.fill = solid(TEAL); c.alignment = ctr(); c.border = thin() weekly_plan = [ ("MONTH 1","","FOUNDATION — Core Anatomy, Physiology & Pharmacology","","",""), ("Week 1","Aug 5–11","BRDT201 – Applied Anatomy","Urinary system anatomy, Histology of kidney, Blood supply, Development, Peritoneum, Vascular system","⬜ Not Started",""), ("Week 2","Aug 12–18","BRDT201 – Physiology","GFR, Clearance studies, Physiological values, Renal circulation, Hormones, Haemostasis, Acid-Base balance","⬜ Not Started",""), ("Week 3","Aug 19–25","BRDT202 – Pharmacology Part 1","IV fluids, Diuretics, Antihypertensives, Vasopressors, Dialysable drugs, Vitamin D, Phosphate binders","⬜ Not Started",""), ("Week 4","Aug 26 – Sep 1","BRDT202 – Pharmacology Part 2","EPO, Heparin/LMWH, Protamine sulphate, Disinfectants, HD concentrates, PD fluid, K-exchange resins","⬜ Not Started",""), ("Week 4B","Sep 2–4","BRDT201 + BRDT202 REVISION","Practice 2 Long Essays + 6 Short Essays on Pharmacology. Review weak topics from Week 1-4.","⬜ Not Started",""), ("MONTH 2","","MASTERY — Renal Diseases, Pathology & Microbiology","","",""), ("Week 5","Sep 5–11","BRDT203 – Renal Diseases Part 1","ARF, Nephrotic syndrome, Nephritic syndrome, UTI, Asymptomatic abnormalities, CRF, Renal stones","⬜ Not Started",""), ("Week 6","Sep 12–18","BRDT203 – Renal Diseases Part 2 + Revision","Obstructive uropathy, Congenital diseases, Tumors, Pregnancy renal diseases, Vascular disorders","⬜ Not Started",""), ("Week 7","Sep 19–25","BRDT204 – Pathology Part 1","Congenital abnormalities, Glomerular diseases, Tubulointerstitial diseases, ESRD, Pathology of peritoneum","⬜ Not Started",""), ("Week 8","Sep 26 – Oct 4","BRDT204 – Pathology Part 2 + Microbiology","UTI pathology, Pyelonephritis, Hepatitis viruses, HIV, Opportunistic infections, Vascular access infections","⬜ Not Started",""), ("MONTH 3","","COMPLETE & REVISE — Subsidiary Subjects + Mega Revision","","",""), ("Week 9","Oct 5–11","BRDT209 – Dialysis Tech + Nutrition Part 1","Indications/types of dialysis, HD machine, Vascular access, Complications, Monitoring. Intro to Nutrition.","⬜ Not Started",""), ("Week 10","Oct 12–18","BRDT208 – Nutrition + BRDT205 – Sociology Part 1","Caloric values, BMR, RDA, Proteins/Fats/Carbs, Renal diet. Sociology: Introduction, Socialization, Social factors","⬜ Not Started",""), ("Week 11","Oct 19–25","BRDT205 – Sociology Part 2 + BRDT206 – Constitution","Social groups, Family, Community, Culture, Social change, Social problems. Constitution Units I-V","⬜ Not Started",""), ("Week 12A","Oct 26–28","BRDT206 – Constitution Complete","Constitution Units VI-X (Separation of powers, Writs, Amending, Sustainable Development)","⬜ Not Started",""), ("Week 12B","Oct 29 – Nov 1","MEGA REVISION — All Core Subjects","Day 1: BRDT201 | Day 2: BRDT202 | Day 3: BRDT203 | Day 4: BRDT204","⬜ Not Started",""), ("Week 12C","Nov 2–4","FINAL PREP — Mock Exams & Rest","Mock exam (any 2 subjects) | Subsidiary subjects quick review | Light revision. REST!","⬜ Not Started",""), ] cr = 4 for entry in weekly_plan: ws_plan.row_dimensions[cr].height = 38 if entry[0].startswith("MONTH") else 36 if entry[0].startswith("MONTH"): ws_plan.merge_cells(f"B{cr}:G{cr}") mc = ws_plan.cell(row=cr,column=2,value=f"━━━ {entry[0]} — {entry[2]} ━━━") mc.font = Font(name="Arial",bold=True,size=13,color=WHITE) mc.fill = solid(TEAL); mc.alignment = ctr(); mc.border = med() else: week,dates,subj,tasks,status,notes = entry bg = LIGHT_GRAY if cr%2==0 else WHITE is_rev = "REVISION" in subj.upper() or "MEGA" in subj.upper() or "FINAL" in subj.upper() row_bg = GOLD if is_rev else bg for col,val,b,al in [ (2,week,True,ctr()),(3,dates,False,ctr()),(4,subj,True,lft()), (5,tasks,False,lft()),(6,status,False,ctr()),(7,"",False,ctr()) ]: c = ws_plan.cell(row=cr,column=col,value=val) c.font = Font(name="Arial",bold=b,size=11 if b else 10, color=DARK_NAVY if col==4 else "000000") c.fill = solid(row_bg if col==2 else bg) c.alignment = al; c.border = thin() dv = DataValidation(type="list", formula1='"⬜ Not Started,🔄 In Progress,✅ Completed,🔁 Need Revision"', allow_blank=True) ws_plan.add_data_validation(dv) dv.add(ws_plan.cell(row=cr,column=6)) cr += 1 print("Monthly planner built.") # ═══════════════════════════════════════════════════════════════════ # FUNCTION: Create subject sheets (with Lecture Link + Lecture Notes + Lecture Watched) # ═══════════════════════════════════════════════════════════════════ def make_subject_sheet(wb, sheet_title, sname, code, color, marks, week_info, topics): ws = wb.create_sheet(sheet_title) # Column widths: # B=# | C=Topic | D=Target Date | E=Date Studied | F=Lecture Link | G=Status | H=Lecture Watched | I=Lecture Notes | J=Self-Score col_ws = [2, 6, 50, 14, 14, 42, 18, 18, 34, 14, 2] for i, w in enumerate(col_ws, 1): ws.column_dimensions[get_column_letter(i)].width = w # Header ws.row_dimensions[1].height = 42 ws.merge_cells("B1:J1") ws["B1"] = f"📖 {sname} ({code}) | Exam Marks: {marks if marks else 'Theory'} | Study Period: {week_info}" ws["B1"].font = Font(name="Arial", bold=True, size=14, color=WHITE) ws["B1"].fill = solid(DARK_NAVY) ws["B1"].alignment = ctr() ws.row_dimensions[2].height = 8 # Column headers row 3 ws.row_dimensions[3].height = 34 col_hdrs = ["","#","TOPIC","TARGET\nDATE","DATE\nSTUDIED", "🎬 YOUTUBE LECTURE LINK","📋 STATUS", "▶ LECTURE\nWATCHED","📝 LECTURE NOTES /\nWEAK AREAS","⭐ SCORE\n(1-10)",""] for col, h in enumerate(col_hdrs, 1): c = ws.cell(row=3, column=col, value=h) c.font = Font(name="Arial", bold=True, size=10, color=WHITE) if col == 6: c.fill = solid("0D5C6B") # darker teal for lecture column elif col == 8: c.fill = solid("1A5276") # dark blue for watched column else: c.fill = solid(DARK_NAVY) c.alignment = ctr() c.border = thin() # Topic rows for i, topic in enumerate(topics): row = 4 + i ws.row_dimensions[row].height = 32 bg = color if (i % 2 == 0) else LIGHT_GRAY # # serial c_num = ws.cell(row=row, column=2, value=i+1) c_num.font = Font(name="Arial", bold=True, size=11, color=DARK_NAVY) c_num.fill = solid(bg); c_num.alignment = ctr(); c_num.border = thin() # Topic c_topic = ws.cell(row=row, column=3, value=topic) c_topic.font = fnt(size=11); c_topic.fill = solid(bg) c_topic.alignment = lft(); c_topic.border = thin() # Target date c_tgt = ws.cell(row=row, column=4, value="") c_tgt.font = fnt(size=10, color=DARK_GRAY) c_tgt.fill = solid(LIGHT_GRAY); c_tgt.alignment = ctr() c_tgt.border = thin(); c_tgt.number_format = "DD-MMM" # Date studied c_ds = ws.cell(row=row, column=5, value="") c_ds.font = fnt(size=10, color=DARK_GRAY) c_ds.fill = solid(LIGHT_GRAY); c_ds.alignment = ctr() c_ds.border = thin(); c_ds.number_format = "DD-MMM" # Lecture Link — hyperlink link = lecture_links.get(topic, f"https://www.youtube.com/results?search_query={topic.replace(' ','+')}+lecture") c_link = ws.cell(row=row, column=6, value="▶ Click to Watch Lecture") c_link.hyperlink = link c_link.font = Font(name="Arial", size=10, color="0563C1", underline="single") c_link.fill = solid("EBF5FB") c_link.alignment = ctr(); c_link.border = thin() # Status dropdown c_stat = ws.cell(row=row, column=7, value="⬜ Not Started") c_stat.font = fnt(size=10); c_stat.fill = solid(LIGHT_GRAY) c_stat.alignment = ctr(); c_stat.border = thin() dv_s = DataValidation(type="list", formula1='"⬜ Not Started,🔄 In Progress,✅ Completed,🔁 Need Revision"', allow_blank=True) ws.add_data_validation(dv_s); dv_s.add(c_stat) # Lecture watched dropdown c_lw = ws.cell(row=row, column=8, value="⬜ Not Watched") c_lw.font = fnt(size=10); c_lw.fill = solid("EBF5FB") c_lw.alignment = ctr(); c_lw.border = thin() dv_lw = DataValidation(type="list", formula1='"⬜ Not Watched,🔄 Watching,✅ Watched,🔁 Re-watch Needed"', allow_blank=True) ws.add_data_validation(dv_lw); dv_lw.add(c_lw) # Lecture Notes c_notes = ws.cell(row=row, column=9, value="") c_notes.font = fnt(size=10, italic=True, color=DARK_GRAY) c_notes.fill = solid(LIGHT_GRAY) c_notes.alignment = lft(); c_notes.border = thin() # Self-score c_score = ws.cell(row=row, column=10, value="") c_score.font = Font(name="Arial", bold=True, size=12, color=TEAL) c_score.fill = solid(LIGHT_GRAY) c_score.alignment = ctr(); c_score.border = thin() dv_sc = DataValidation(type="whole", operator="between", formula1="1", formula2="10", allow_blank=True, showErrorMessage=True, errorTitle="Invalid", error="Enter 1-10") ws.add_data_validation(dv_sc); dv_sc.add(c_score) # Conditional formatting n = len(topics) stat_range = f"G4:G{3+n}" lw_range = f"H4:H{3+n}" score_range = f"J4:J{3+n}" ws.conditional_formatting.add(stat_range, FormulaRule(formula=[f'G4="✅ Completed"'],fill=PatternFill("solid",fgColor="D5F5E3"))) ws.conditional_formatting.add(stat_range, FormulaRule(formula=[f'G4="🔄 In Progress"'],fill=PatternFill("solid",fgColor="FFF9C4"))) ws.conditional_formatting.add(stat_range, FormulaRule(formula=[f'G4="🔁 Need Revision"'],fill=PatternFill("solid",fgColor="FAD7A0"))) ws.conditional_formatting.add(lw_range, FormulaRule(formula=[f'H4="✅ Watched"'],fill=PatternFill("solid",fgColor="D6EAF8"))) ws.conditional_formatting.add(lw_range, FormulaRule(formula=[f'H4="🔁 Re-watch Needed"'],fill=PatternFill("solid",fgColor="FADBD8"))) ws.conditional_formatting.add(score_range, ColorScaleRule(start_type="num",start_value=1,start_color="E63946", mid_type="num",mid_value=5,mid_color="FFA500", end_type="num",end_value=10,end_color="2DC653")) # Summary footer sr = 4 + n + 1 ws.row_dimensions[sr].height = 10 sr += 1 ws.row_dimensions[sr].height = 28 cells_summary = [ (2, "📊 SUMMARY", "@", WHITE), (3, f"Total: {n}", "@", WHITE), (4, f"=COUNTIF(G4:G{3+n},\"✅ Completed\")", '"✅ Done: "0', GOLD), (5, f"=IFERROR(COUNTIF(G4:G{3+n},\"✅ Completed\")/{n},0)", '"Progress: "0%', GOLD), (6, f"=COUNTIF(H4:H{3+n},\"✅ Watched\")", '"▶ Watched: "0', "A9CCE3"), (7, f"=COUNTIF(G4:G{3+n},\"🔁 Need Revision\")", '"⚠ Revise: "0', ORANGE), (8, "", "@", WHITE), (9, f'=IFERROR(AVERAGEIF(J4:J{3+n},">0"),"—")', '"Avg Score: "0.0', WHITE), (10, "", "@", WHITE), ] ws.merge_cells(f"B{sr}:C{sr}") for col, val, nfmt, fc in cells_summary: c = ws.cell(row=sr, column=col, value=val) c.font = Font(name="Arial", bold=True, size=11, color=fc) c.fill = solid(DARK_NAVY) c.alignment = ctr(); c.border = thin() c.number_format = nfmt ws.freeze_panes = "C4" return ws # Create all 8 subject sheets for sname, sd in subjects_data.items(): code = sd["code"] st = f"📖 {code}" make_subject_sheet(wb, st, sname, code, sd["color"], sd["marks"], sd["week"], sd["topics"]) print(f" ✓ {st}") # ═══════════════════════════════════════════════════════════════════ # SHEET: LECTURE SCHEDULE — daily topic-to-lecture mapping # ═══════════════════════════════════════════════════════════════════ ws_lec = wb.create_sheet("🎬 Lecture Schedule") lec_widths = [2, 13, 12, 20, 46, 42, 20, 18, 2] for i, w in enumerate(lec_widths, 1): ws_lec.column_dimensions[get_column_letter(i)].width = w ws_lec.row_dimensions[1].height = 40 ws_lec.merge_cells("B1:H1") ws_lec["B1"] = "🎬 LECTURE SCHEDULE — Which YouTube Lecture to Watch Each Day | Aug 5 – Nov 4, 2026" ws_lec["B1"].font = Font(name="Arial", bold=True, size=14, color=WHITE) ws_lec["B1"].fill = solid(DARK_NAVY) ws_lec["B1"].alignment = ctr() ws_lec.row_dimensions[2].height = 8 ws_lec.row_dimensions[3].height = 30 lec_hdrs = ["","DATE","DAY","SUBJECT","TOPIC TO STUDY","🎬 YOUTUBE LECTURE LINK","STATUS","WATCHED",""] for col, h in enumerate(lec_hdrs, 1): c = ws_lec.cell(row=3, column=col, value=h) c.font = Font(name="Arial", bold=True, size=11, color=WHITE) c.fill = solid("0D5C6B") if col in [6] else solid(DARK_NAVY) c.alignment = ctr(); c.border = thin() # Build the daily schedule list import datetime start_date = datetime.date(2026, 8, 5) days_abbr = ["Mon","Tue","Wed","Thu","Fri","Sat","Sun"] daily_schedule = [] # Week 1 - Aug 5-11: Anatomy anatomy_w1 = [ ("BRDT201","Basic Anatomy of Urinary System (Kidney, Bladder, Ureter, Urethra, Prostate)"), ("BRDT201","Histology of Kidney"), ("BRDT201","Blood Supply of Kidney"), ("BRDT201","Development of Kidney in Brief"), ("BRDT201","Anatomy of Peritoneum & Concept of Abdominal Hernias"), ("BRDT201","Anatomy of Vascular System - Upper Limb Vessels"), ("BRDT201","REVISION DAY — Week 1 Anatomy Topics"), ] # Week 2 - Aug 12-18: Physiology phys_w2 = [ ("BRDT201","Anatomy of Vascular System - Neck Vessels"), ("BRDT201","Anatomy of Vascular System - Femoral Vessels"), ("BRDT201","Mechanism of Urine Formation"), ("BRDT201","Glomerular Filtration Rate (GFR)"), ("BRDT201","Clearance Studies"), ("BRDT201","Physiological Values (Urea, Creatinine, Electrolytes, Calcium, Phosphorus, Uric Acid, Magnesium, Glucose)"), ("BRDT201","REVISION DAY — Week 2 Physiology Topics"), ] phys_w2b = [ ("BRDT201","24-Hour Urinary Indices"), ("BRDT201","Physiology of Renal Circulation - Factors Contributing & Modifying"), ("BRDT201","Autoregulation of Renal Circulation"), ("BRDT201","Hormones Produced by Kidney"), ("BRDT201","Physiological Alterations in Pregnancy"), ("BRDT201","Haemostasis - Coagulation Cascade & Factors, BT, CT, PT, PTT, Thrombin Time"), ("BRDT201","Acid-Base Balance - Basic Principles"), ] pharm_w3 = [ ("BRDT201","Acid-Base Abnormalities (Hypokalemia, Hyperkalemia, Hyponatremia, Hypernatremia, Hypocalcemia, Hypercalcemia)"), ("BRDT201","Basic Nutrition in Renal Diseases"), ("BRDT202","IV Fluid Therapy with Special Emphasis in Renal Diseases"), ("BRDT202","Diuretics - Classification, Actions, Dosage, Side Effects & Contraindications"), ("BRDT202","Antihypertensives - Classification, Actions, Dosage, Side Effects & Contraindications"), ("BRDT202","Reference During Dialysis, Vasopressors, Drugs Used in Hypotension"), ("BRDT202","REVISION DAY — Week 3 Pharmacology Topics"), ] pharm_w4 = [ ("BRDT202","Drugs & Dialysis - Dose & Duration of Administration"), ("BRDT202","Dialysable Drugs (Phenobarbitone, Lithium, Methanol etc.)"), ("BRDT202","Vitamin D & Analogues, Phosphate Binders, Iron, Folic Acid & Other Vitamins"), ("BRDT202","Erythropoietin in Detail"), ("BRDT202","Heparin Including Low Molecular Weight Heparin"), ("BRDT202","Protamine Sulphate"), ("BRDT202","Formalin, Sodium Hypochlorite, Hydrogen Peroxide - Disinfectants & Adverse Effects"), ] pharm_w4b = [ ("BRDT202","Haemodialysis Concentrates - Composition & Dilution (Acetate & Bicarbonates)"), ("BRDT202","Peritoneal Dialysis Fluid & Hypertonic Solutions - Composition"), ("BRDT202","Potassium Exchange Resins - Mode of Administration"), ("BRDT202","REVISION — BRDT201 Full Review"), ("BRDT202","REVISION — BRDT202 Full Review"), ("BRDT202","PRACTICE — Write 2 Long Essays (Pharmacology)"), ("BRDT202","PRACTICE — Write 6 Short Essays (Pharmacology)"), ] renal_w5 = [ ("BRDT203","Acute Renal Failure - Clinical Manifestations, Evaluation & Management"), ("BRDT203","Nephrotic Syndrome - Primary & Secondary"), ("BRDT203","Nephritic Syndrome"), ("BRDT203","UTI - Urinary Tract Infections"), ("BRDT203","Asymptomatic Urinary Abnormalities"), ("BRDT203","Chronic Renal Failure"), ("BRDT203","REVISION DAY — Week 5 Renal Disease Topics"), ] renal_w6 = [ ("BRDT203","Renal Stone Diseases"), ("BRDT203","Obstructive Uropathies"), ("BRDT203","Congenital & Inherited Renal Diseases"), ("BRDT203","Tumors of Kidney"), ("BRDT203","Pregnancy Associated Renal Diseases"), ("BRDT203","Renal Vascular Disorders & Hypertension Associated Renal Diseases"), ("BRDT203","REVISION — BRDT203 Full Review"), ] path_w7 = [ ("BRDT204","Congenital Abnormalities of Urinary System"), ("BRDT204","Classification of Renal Diseases"), ("BRDT204","Glomerular Diseases - Causes, Types & Pathology"), ("BRDT204","Tubulointerstitial Diseases"), ("BRDT204","Renal Vascular Disorders"), ("BRDT204","End Stage Renal Diseases - Causes & Pathology"), ("BRDT204","REVISION DAY — Week 7 Pathology Topics"), ] path_w8 = [ ("BRDT204","Pathology of Kidney in Hypertension, Diabetes Mellitus, Pregnancy"), ("BRDT204","Pathology of Peritoneum - Peritonitis (Bacterial, Tubular, Sclerosing, Dialysis-induced)"), ("BRDT204","Pathology of Urinary Tract Infections"), ("BRDT204","Pyelonephritis & Tuberculous Pyelonephritis"), ("BRDT204","Hepatotrophic Viruses - Mode of Transfusion, Universal Precautions, Vaccinations"), ("BRDT204","HIV - Mode of Transfusion, Universal Precautions"), ("BRDT204","Opportunistic Infections"), ] path_w8b = [ ("BRDT204","Microbiology of Urinary Tract Infections"), ("BRDT204","Microbiology of Vascular Access Infection (Femoral, Jugular, Subclavian Catheters)"), ("BRDT204","Sampling Methodologies for Culture & Sensitivity"), ("BRDT204","REVISION — BRDT204 Full Review"), ("BRDT204","PRACTICE — Write 2 Long Essays (Pathology & Microbiology)"), ("BRDT204","REST / Catch-up Day"), ("BRDT204","REVISION — BRDT203 + BRDT204 Weak Topics"), ] dial_w9 = [ ("BRDT209","Indications of Dialysis"), ("BRDT209","Types of Dialysis"), ("BRDT209","Principles of Dialysis - Definitions"), ("BRDT209","Haemodialysis Apparatus - Types of Dialyser & Membrane"), ("BRDT209","Types of Vascular Access for Haemodialysis"), ("BRDT209","Introduction to Haemodialysis Machine"), ("BRDT209","Priming of Dialysis Apparatus"), ] dial_w9b = [ ("BRDT209","Dialyser Reuse"), ("BRDT209","Common Complications of Haemodialysis"), ("BRDT209","Monitoring of Patients During Dialysis"), ("BRDT208","Balanced Diet - Definition & Concept"), ("BRDT208","Caloric Values of Carbohydrates, Proteins and Fats"), ("BRDT208","Total Daily Caloric Requirements (Adult Male & Female)"), ("BRDT208","REVISION DAY — BRDT209 + Nutrition basics"), ] nutr_w10 = [ ("BRDT208","RDA - Definition & Standard Values for Nutrients"), ("BRDT208","Basal Metabolic Rate (BMR) - Definition, Magnitude & Factors"), ("BRDT208","Thermic Effect / SDA of Food"), ("BRDT208","Carbohydrates - Daily Requirement & Dietary Fibers"), ("BRDT208","Proteins - Daily Requirement, Biological Value & Mutual Supplementation"), ("BRDT208","Fats - Essential Fatty Acids, Saturated & Unsaturated Fatty Acids"), ("BRDT208","Malnutrition"), ] nutr_w10b = [ ("BRDT208","Introduction to Science of Nutrition - Food Patterns & Influencing Factors"), ("BRDT208","Classification of Nutrients (Macro & Micronutrients) - All Types"), ("BRDT208","Planning Diets - Balanced Diet & Renal Diet"), ("BRDT208","Introduction to Cookery"), ("BRDT205","Introduction: Definition, Scope & Methods of Sociological Investigations"), ("BRDT205","Relation to Anthropology, Psychology, Social Psychology"), ("BRDT205","Social Factors in Health and Disease"), ] soc_w11 = [ ("BRDT205","Socialization: Meaning, Nature, Types & Agencies"), ("BRDT205","Social Groups: Formal, Informal, Primary, Secondary"), ("BRDT205","Family: Meaning, Functions, Types, Changing Patterns"), ("BRDT205","Community: Rural, Urban, Tribal - Features & Health Hazards"), ("BRDT205","Culture and Health: Concept, Culture & Health Disorders"), ("BRDT205","Social Change: Meaning, Factors, Stress, Deviance, Health Programmes"), ("BRDT205","Social Problems of Disabled (Population, Poverty, Beggary, Juvenile Delinquency etc.)"), ] soc_w11b = [ ("BRDT205","Social Security & Social Legislation"), ("BRDT205","Social Work: Meaning & Role of Medical Social Worker"), ("BRDT206","Unit I: Meaning & Making of the Indian Constitution (1946-1949)"), ("BRDT206","Unit II: Democratic Institutions - Bicameral Legislature (Centre & States)"), ("BRDT206","Unit III: Fundamental Rights and Duties"), ("BRDT206","Unit IV: Directive Principles of State Policy"), ("BRDT206","REVISION DAY — Sociology complete"), ] const_w12 = [ ("BRDT206","Unit V: Special Rights for Dalits, Backwards, Women, Children & Minorities"), ("BRDT206","Unit VI: Doctrine of Separation of Powers (Legislature, Executive, Judiciary)"), ("BRDT206","Unit VII: Election Commission & State Public Service Commissions"), ("BRDT206","Unit VIII: Method of Amending the Constitution"), ("BRDT206","Unit IX: Enforcing Rights through Writs (Habeas Corpus, Mandamus, etc.)"), ("BRDT206","Unit X: Constitution and Sustainable Development in India"), ("BRDT206","REVISION — Indian Constitution complete"), ] mega_rev = [ ("ALL","MEGA REVISION — BRDT201: Anatomy & Physiology (Full review of all 21 topics)"), ("ALL","MEGA REVISION — BRDT202: Pharmacology (All 14 topics quick recall)"), ("ALL","MEGA REVISION — BRDT203: Renal Diseases (All 12 diseases)"), ("ALL","MEGA REVISION — BRDT204: Pathology & Microbiology (All 16 topics)"), ("ALL","MOCK EXAM — Attempt full paper for 2 subjects (3 hrs timed)"), ("ALL","REVISION — BRDT205 + BRDT206 + BRDT208 + BRDT209 quick notes"), ("ALL","FINAL PREP — Light review only. Check cheat sheets. REST WELL! 🌟"), ] all_weeks = (anatomy_w1 + phys_w2 + phys_w2b + pharm_w3 + pharm_w4 + pharm_w4b + renal_w5 + renal_w6 + path_w7 + path_w8 + path_w8b + dial_w9 + dial_w9b + nutr_w10 + nutr_w10b + soc_w11 + soc_w11b + const_w12 + mega_rev) lec_row = 4 for idx, (code, topic) in enumerate(all_weeks): date = start_date + datetime.timedelta(days=idx) day_name = days_abbr[date.weekday()] ws_lec.row_dimensions[lec_row].height = 28 is_rev = "REVISION" in topic.upper() or "PRACTICE" in topic.upper() or "REST" in topic.upper() or "MOCK" in topic.upper() or "MEGA" in topic.upper() or "FINAL" in topic.upper() is_mega = "MEGA" in topic.upper() or "MOCK" in topic.upper() or "FINAL" in topic.upper() bg = "FAD7A0" if is_mega else ("FFF9C4" if is_rev else (LIGHT_GRAY if idx%2==0 else WHITE)) link = lecture_links.get(topic, f"https://www.youtube.com/results?search_query={topic[:60].replace(' ','+')}+lecture") # Subject color pill subj_color = SUBJ_COLORS[list(subjects_data.keys()).index(next( (k for k in subjects_data if subjects_data[k]["code"]==code), list(subjects_data.keys())[0]))] if code != "ALL" else "E8DAEF" c_date = ws_lec.cell(row=lec_row, column=2, value=date.strftime("%d %b %Y")) c_date.font = fnt(size=10, bold=True); c_date.fill = solid(bg) c_date.alignment = ctr(); c_date.border = thin() c_day = ws_lec.cell(row=lec_row, column=3, value=day_name) c_day.font = fnt(size=10, color="FFFFFF" if day_name in ["Sat","Sun"] else "000000", bold=True) c_day.fill = solid("1B2A4A" if day_name in ["Sat","Sun"] else bg) c_day.alignment = ctr(); c_day.border = thin() c_code = ws_lec.cell(row=lec_row, column=4, value=code) c_code.font = Font(name="Arial", bold=True, size=10) c_code.fill = solid(subj_color) c_code.alignment = ctr(); c_code.border = thin() c_topic = ws_lec.cell(row=lec_row, column=5, value=topic) c_topic.font = Font(name="Arial", bold=is_rev, size=10, color="8B0000" if is_mega else ("555500" if is_rev else "000000")) c_topic.fill = solid(bg); c_topic.alignment = lft(); c_topic.border = thin() if is_rev: c_link_cell = ws_lec.cell(row=lec_row, column=6, value="— Revision / Practice Day —") c_link_cell.font = fnt(size=10, italic=True, color=DARK_GRAY) c_link_cell.fill = solid(bg) else: c_link_cell = ws_lec.cell(row=lec_row, column=6, value="▶ Watch Lecture on YouTube") c_link_cell.hyperlink = link c_link_cell.font = Font(name="Arial", size=10, color="0563C1", underline="single") c_link_cell.fill = solid("EBF5FB") c_link_cell.alignment = ctr(); c_link_cell.border = thin() c_stat = ws_lec.cell(row=lec_row, column=7, value="⬜ Not Started") c_stat.font = fnt(size=10); c_stat.fill = solid(LIGHT_GRAY) c_stat.alignment = ctr(); c_stat.border = thin() dv_ls = DataValidation(type="list", formula1='"⬜ Not Started,🔄 In Progress,✅ Done,🔁 Need Revision"', allow_blank=True) ws_lec.add_data_validation(dv_ls); dv_ls.add(c_stat) c_watched = ws_lec.cell(row=lec_row, column=8, value="⬜ Not Watched") c_watched.font = fnt(size=10); c_watched.fill = solid("EBF5FB") c_watched.alignment = ctr(); c_watched.border = thin() dv_lw2 = DataValidation(type="list", formula1='"⬜ Not Watched,✅ Watched,🔁 Re-watch"', allow_blank=True) ws_lec.add_data_validation(dv_lw2); dv_lw2.add(c_watched) lec_row += 1 ws_lec.freeze_panes = "B4" print(f"Lecture Schedule built ({lec_row-4} daily entries).") # ═══════════════════════════════════════════════════════════════════ # DAILY LOG # ═══════════════════════════════════════════════════════════════════ ws_log = wb.create_sheet("📝 Daily Log") log_widths = [2,15,18,42,14,14,32,2] for i,w in enumerate(log_widths,1): ws_log.column_dimensions[get_column_letter(i)].width = w ws_log.row_dimensions[1].height = 36 ws_log.merge_cells("B1:G1") ws_log["B1"] = "📝 DAILY STUDY LOG — Log Every Session | 90 Days" ws_log["B1"].font = Font(name="Arial",bold=True,size=14,color=WHITE) ws_log["B1"].fill = solid(DARK_NAVY); ws_log["B1"].alignment = ctr() ws_log.row_dimensions[2].height = 8 ws_log.row_dimensions[3].height = 28 for col,h in enumerate(["","DATE","SUBJECT","TOPICS STUDIED","HOURS","MOOD\n(1-5 ⭐)","WHAT TO REVISE NEXT",""],1): c = ws_log.cell(row=3,column=col,value=h) c.font = Font(name="Arial",bold=True,size=11,color=WHITE) c.fill = solid(TEAL); c.alignment = ctr(); c.border = thin() for i in range(90): r = 4+i ws_log.row_dimensions[r].height = 22 bg = LIGHT_GRAY if i%2==0 else WHITE for col in [2,3,4,5,6,7]: c = ws_log.cell(row=r,column=col,value="") c.fill = solid(bg); c.border = thin() c.alignment = ctr() if col in [2,3,5,6] else lft() c.font = fnt(size=10) ws_log.cell(row=r,column=2).number_format = "DD-MMM-YYYY (DDD)" dv_subj = DataValidation(type="list", formula1='"BRDT201,BRDT202,BRDT203,BRDT204,BRDT205,BRDT206,BRDT208,BRDT209,Multiple,Revision"', allow_blank=True) ws_log.add_data_validation(dv_subj); dv_subj.add(ws_log.cell(row=r,column=3)) # Total hours summary sr2 = 4+90+1; ws_log.row_dimensions[sr2].height = 8; sr2+=1 ws_log.row_dimensions[sr2].height = 26 ws_log.merge_cells(f"B{sr2}:D{sr2}") c_tot_label = ws_log.cell(row=sr2,column=2,value="📊 TOTAL STUDY HOURS (3 Months)") c_tot_label.font = Font(name="Arial",bold=True,size=12,color=WHITE) c_tot_label.fill = solid(DARK_NAVY); c_tot_label.alignment = ctr(); c_tot_label.border = med() c_tot_hrs = ws_log.cell(row=sr2,column=5,value=f"=SUM(E4:E{4+89})") c_tot_hrs.font = Font(name="Arial",bold=True,size=14,color=GOLD) c_tot_hrs.fill = solid(DARK_NAVY); c_tot_hrs.alignment = ctr(); c_tot_hrs.border = med() c_tot_hrs.number_format = '0.0" hrs total"' ws_log.freeze_panes = "B4" print("Daily log built.") # FREEZE PANES wb["📊 Dashboard"].freeze_panes = "B6" wb["📅 Monthly Planner"].freeze_panes = "B4" # SAVE out = "/tmp/workspace/study-tracker/BSc_RDT_StudyTracker_v2_WithLectures.xlsx" wb.save(out) print(f"\n✅ Saved: {out}") PYEOF echo "Script ready."

cd /tmp/workspace/study-tracker && python build_tracker_v2.py

cd /tmp/workspace/study-tracker && python -c " # Fix: the summary row merges B:C but then tries to write to col 2 and 3 separately. # The merge is only B:C, so col 3 becomes a MergedCell. We need to skip col 3 in the loop # or unmerge and only write to B. # Quick fix: remove the merge and just write all cells individually. print('Issue: merge_cells B:C then writing col 3 fails. Fix: dont merge, write separately.') "

~/study-tracker/build_tracker_v2.py

cd /tmp/workspace/study-tracker && python build_tracker_v2.py 2>&1

python -c " import openpyxl, os wb = openpyxl.load_workbook('/tmp/workspace/study-tracker/BSc_RDT_StudyTracker_v2_WithLectures.xlsx') print('Sheets:', wb.sheetnames) ws = wb['📖 BRDT201'] # Check lecture link cell print('BRDT201 Row4 Topic:', ws['C4'].value) print('BRDT201 Row4 Link text:', ws['F4'].value) print('BRDT201 Row4 Link hyperlink:', ws['F4'].hyperlink) ws_lec = wb['🎬 Lecture Schedule'] print('Lecture Schedule row 4:', ws_lec['B4'].value, '|', ws_lec['E4'].value[:50]) print('Lecture Schedule row 4 link:', ws_lec['F4'].hyperlink) size = os.path.getsize('/tmp/workspace/study-tracker/BSc_RDT_StudyTracker_v2_WithLectures.xlsx') print(f'File size: {size/1024:.1f} KB') print('Total sheets:', len(wb.sheetnames)) "