123 lines
4.3 KiB
Python
123 lines
4.3 KiB
Python
"""
|
|
Generate Excel report of backup runs and file events.
|
|
|
|
Usage: python report.py [output.xlsx]
|
|
|
|
Single sheet with all events from all runs.
|
|
Skips runs where total events > THRESHOLD (mass initial imports).
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
from datetime import datetime as dt
|
|
import pymysql
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
from indexer.config import DB_CONFIG
|
|
|
|
THRESHOLD = 5000 # skip runs with more events than this
|
|
|
|
|
|
def generate_report(output_path: str):
|
|
conn = pymysql.connect(**DB_CONFIG)
|
|
cur = conn.cursor()
|
|
|
|
# ── 1. Load runs ──
|
|
cur.execute("SELECT * FROM runs ORDER BY id")
|
|
runs = cur.fetchall()
|
|
|
|
# ── 2. Collect all events from non-skipped runs ──
|
|
all_events = []
|
|
skipped_runs = []
|
|
for run in runs:
|
|
run_id, started, finished, status, total, new, mod, deleted, unchanged = run
|
|
total_changes = new + mod + deleted
|
|
if total_changes > THRESHOLD:
|
|
skipped_runs.append(run_id)
|
|
continue
|
|
cur.execute(
|
|
"""SELECT fe.event_type, f.relative_path, f.file_name, f.directory,
|
|
fe.old_size, fe.new_size
|
|
FROM file_events fe
|
|
JOIN files f ON fe.file_id = f.id
|
|
WHERE fe.run_id = %s
|
|
ORDER BY fe.event_type, f.relative_path""",
|
|
(run_id,)
|
|
)
|
|
for ev in cur.fetchall():
|
|
all_events.append((run_id, started, *ev))
|
|
|
|
conn.close()
|
|
|
|
# ── 3. Build Excel — single sheet ──
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Events"
|
|
|
|
header_font = Font(bold=True, color="FFFFFF", size=11)
|
|
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
|
|
thin_border = Border(
|
|
left=Side(style="thin"), right=Side(style="thin"),
|
|
top=Side(style="thin"), bottom=Side(style="thin"),
|
|
)
|
|
|
|
type_fills = {
|
|
"CREATED": PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid"),
|
|
"MODIFIED": PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid"),
|
|
"DELETED": PatternFill(start_color="FCE4EC", end_color="FCE4EC", fill_type="solid"),
|
|
}
|
|
|
|
headers = ["Run #", "Run Time", "Event", "Path", "File Name", "Directory",
|
|
"Old Size", "New Size", "Size Change"]
|
|
for col, h in enumerate(headers, 1):
|
|
cell = ws.cell(row=1, column=col, value=h)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = Alignment(horizontal="center")
|
|
cell.border = thin_border
|
|
|
|
for row_idx, ev in enumerate(all_events, 2):
|
|
run_id, started, event_type, rel_path, file_name, directory, old_size, new_size = ev
|
|
|
|
size_change = ""
|
|
if old_size is not None and new_size is not None:
|
|
diff = new_size - old_size
|
|
if diff != 0:
|
|
size_change = f"{'+' if diff > 0 else ''}{diff:,}"
|
|
elif new_size is not None:
|
|
size_change = f"+{new_size:,}"
|
|
elif old_size is not None:
|
|
size_change = f"-{old_size:,}"
|
|
|
|
values = [run_id, started, event_type, rel_path, file_name, directory,
|
|
old_size, new_size, size_change]
|
|
|
|
fill = type_fills.get(event_type)
|
|
for col, val in enumerate(values, 1):
|
|
cell = ws.cell(row=row_idx, column=col, value=val)
|
|
cell.border = thin_border
|
|
if fill:
|
|
cell.fill = fill
|
|
|
|
# Auto-width
|
|
widths = [8, 18, 10, 60, 30, 40, 12, 12, 14]
|
|
for col, w in enumerate(widths, 1):
|
|
ws.column_dimensions[get_column_letter(col)].width = w
|
|
|
|
# Autofilter
|
|
ws.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{len(all_events) + 1}"
|
|
|
|
wb.save(output_path)
|
|
print(f"Report saved to {output_path}")
|
|
print(f" Runs total: {len(runs)}, skipped: {len(skipped_runs)} (threshold: {THRESHOLD})")
|
|
print(f" Events: {len(all_events)} rows")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
REPORT_DIR = r"u:\Dropbox\!!!Days\Downloads Z230"
|
|
timestamp = dt.now().strftime("%Y-%m-%d %H_%M")
|
|
default_name = f"{timestamp} DropboxBackupReport.xlsx"
|
|
output = sys.argv[1] if len(sys.argv) > 1 else os.path.join(REPORT_DIR, default_name)
|
|
generate_report(output)
|