import os import sys import glob from datetime import date, timedelta, datetime sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..')) from app import app from models import KpiWeeklyData, EmployeeKpiValue, User from extensions import db # Column mapping: category -> [(metric_name, col_index), ...] # Same structure as _seed_kpi_weekly_data in routes/main.py METRICS_DEF = { 'Revenue': [('Plan Total revenue', 1), ('Fact Total revenue', 2), ('LE% execution', 3), ('AVG, Check in transaction', 4), ('Страница Экосистемы', 5)], 'Visits': [('# UNIQUE CLIENTS', 6), ('PLAN UNIQUE CLIENTS', 7), ('% execution', 8), ('#LAS', 9), ('AVG visits', 10)], 'KITs': [('#Plan Kits', 11), ('#KITs sold', 12), ('% execution', 13), ('#Kit LAS', 14), ('Reg Rate kits', 15), ('ADS KIT', 16)], 'HTS': [('Plan HTS packs', 18), ('Fact HTS packs', 19), ('LE% execution', 20), ('% registred Heats', 21), ('%, carton from HTS', 24)], 'Deg Set / Lending': [('% Deg Set + sticks', 27), ('% LAS Lending', 28), ('#Open Lendings', 29), ('PLAN Open Lending LAS', 30), ('#Open Lending LAS', 31), ('% LAS Lending 1-30', 32), ('% Lending 1-30', 33)], 'Education': [('Digital screen (science)', 34), ('Digital screen (superiority)', 35), ('Digital screen (taste)', 36), ('#CO tests', 37), ('Сред. закрытых категорий', 38), ('% успешных визитов', 39), ('% успешных (начинающие)', 40), ('% успешных (лояльные)', 41)], 'Upgrade / Q-club': [('% Restart', 57), ('% Retention', 58), ('% Q-Club Engaged', 59), ('% Wallet', 60), ('Contactability', 61)], 'QOS': [('%Random (общий)', 68), ('Блок % Q-Club', 69), ('Блок % Dual/4.0', 70), ('Brand Building %', 71), ('Discover', 72)], } IMPORT_DIR = os.path.join(os.path.dirname(__file__), '..', 'import_excel') PROCESSED_DIR = os.path.join(IMPORT_DIR, '_processed') def log(msg): ts = datetime.now().strftime('%H:%M:%S') print(f'[{ts}] {msg}') def parse_val(raw): if raw is None: return None s = str(raw).strip().replace('\xa0', '').replace(',', '.').replace('%', '').replace(' ', '') if not s: return None try: return float(s) except ValueError: return None def get_date_from_row(sheet, data_row_idx): """Look upwards from data_row_idx for a row containing a date.""" for i in range(data_row_idx - 1, -1, -1): val = str(sheet.cell(row=i + 1, column=1).value or '').strip() parts = val.replace('.', ' ').split() nums = [p for p in parts if p.isdigit()] if len(nums) >= 3: try: d, m, y = int(nums[0]), int(nums[1]), int(nums[2]) y = y + 2000 if y < 100 else y return date(y, m, d) except Exception: pass return None def get_week_monday(d): return d - timedelta(days=d.weekday()) def process_sheet(sheet): """Process a single sheet. Returns list of (category, metric_name, week_start, value) tuples.""" rows = list(sheet.iter_rows(values_only=True)) results = [] found_dates = {} # row_index -> date # First pass: find all Moscow data rows and their dates data_rows = [] for i, row in enumerate(rows): if not row or not row[0]: continue cell0 = str(row[0]).strip() if 'Moscow' in cell0: wd = get_date_from_row(sheet, i) if wd: week_start = get_week_monday(wd) data_rows.append((i, week_start, row)) if not data_rows: log(f' No Moscow rows found in sheet') return results log(f' Found {len(data_rows)} data row(s)') for row_idx, week_start, row in data_rows: for cat_name, mlist in METRICS_DEF.items(): for mname, col_idx in mlist: if col_idx < len(row): raw = row[col_idx] val = parse_val(raw) if val is not None: results.append((cat_name, mname, week_start, val)) return results def save_plans(entries): """Save plan entries to KpiWeeklyData.""" count = 0 for cat_name, mname, week_start, val in entries: is_plan = any(kw in mname for kw in ['Plan', 'PLAN', '#Plan']) if not is_plan: continue existing = KpiWeeklyData.query.filter_by( category=cat_name, metric_name=mname, week_start=week_start ).first() if existing: existing.value = str(val) else: rec = KpiWeeklyData(category=cat_name, metric_name=mname, week_start=week_start, value=str(val)) db.session.add(rec) count += 1 db.session.commit() return count def save_facts(entries): """Save fact entries to EmployeeKpiValue for all active employees.""" employees = User.query.filter(User.role.in_(['employee', 'senior_expert']), User.is_active == True).all() if not employees: log(' No active employees found') return 0 count = 0 for cat_name, mname, week_start, val in entries: is_plan = any(kw in mname for kw in ['Plan', 'PLAN', '#Plan']) if is_plan: continue # % execution metrics: store as fact value per employee # Flat metrics: distribute evenly among employees for emp in employees: existing = EmployeeKpiValue.query.filter_by( user_id=emp.id, category=cat_name, metric_name=mname, week_start=week_start ).first() if existing: existing.fact_value = str(val) existing.updated_at = datetime.utcnow() else: rec = EmployeeKpiValue( user_id=emp.id, category=cat_name, metric_name=mname, week_start=week_start, fact_value=str(val), ) db.session.add(rec) count += 1 db.session.commit() return count def process_all(): with app.app_context(): log(f'Import directory: {IMPORT_DIR}') os.makedirs(PROCESSED_DIR, exist_ok=True) xlsx_files = glob.glob(os.path.join(IMPORT_DIR, '*.xlsx')) xls_files = glob.glob(os.path.join(IMPORT_DIR, '*.xls')) all_files = sorted(xlsx_files + xls_files) if not all_files: log(f'No Excel files found in {IMPORT_DIR}') log('Place .xlsx files in the import_excel folder and run again.') return log(f'Found {len(all_files)} Excel file(s)') for fpath in all_files: fname = os.path.basename(fpath) log(f'Processing: {fname}') try: import openpyxl wb = openpyxl.load_workbook(fpath, data_only=True) log(f' Sheets: {wb.sheetnames}') all_entries = [] for sheet_name in wb.sheetnames: ws = wb[sheet_name] log(f' Sheet: {sheet_name} ({ws.max_row}r x {ws.max_column}c)') entries = process_sheet(ws) log(f' Extracted {len(entries)} values') all_entries.extend(entries) if not all_entries: log(f' No data extracted, skipping') wb.close() continue plans = [e for e in all_entries if any(kw in e[1] for kw in ['Plan', 'PLAN', '#Plan'])] facts = [e for e in all_entries if e not in plans] saved_plans = save_plans(plans) saved_facts = save_facts(facts) log(f' Saved: {saved_plans} plans, {saved_facts} fact values') # Move processed file dest = os.path.join(PROCESSED_DIR, fname) os.replace(fpath, dest) log(f' Moved to _processed/') wb.close() except Exception as e: log(f' ERROR: {e}') log('Import complete!') if __name__ == '__main__': process_all()