from __future__ import annotations from datetime import datetime, time from pathlib import Path from typing import Optional from django.core.management.base import BaseCommand, CommandError from django.db import transaction from django.utils import timezone from apps.accounts.models import User try: from openpyxl import load_workbook except ImportError: # pragma: no cover load_workbook = None class Command(BaseCommand): help = ( "Import legacy registration rows from Excel, create missing users, " "resolve sponsor via legacy ID -> referral code mapping, and update status/referral cells." ) def add_arguments(self, parser): parser.add_argument( "--file", required=True, help="Absolute path to the legacy Excel file (.xlsx).", ) parser.add_argument( "--sheet", default=None, help="Optional sheet name. Defaults to active sheet.", ) parser.add_argument( "--default-pin", default="123456", help="Temporary 6-digit login PIN for newly created users.", ) parser.add_argument( "--output", default=None, help="Optional output path for the updated workbook. Defaults to overwriting --file.", ) parser.add_argument( "--progress-every", type=int, default=200, help="Print progress every N rows (default: 200).", ) @staticmethod def _clean_string(value) -> str: if value is None: return "" return str(value).strip() @staticmethod def _clean_phone(value) -> str: raw = Command._clean_string(value) if not raw: return "" # Handle Excel numeric values rendered like 9876543210.0 if raw.endswith(".0") and raw.replace(".", "", 1).isdigit(): raw = raw[:-2] digits = "".join(ch for ch in raw if ch.isdigit()) return digits if digits else raw @staticmethod def _split_name(full_name: str) -> tuple[str, str]: full_name = full_name.strip() if not full_name: return "", "" parts = full_name.split() if len(parts) == 1: return parts[0], "" return parts[0], " ".join(parts[1:]) @staticmethod def _parse_join_date(value) -> timezone.datetime: if value is None or str(value).strip() == "": return timezone.now() if isinstance(value, datetime): dt = value else: text = str(value).strip() parsed = None for fmt in ("%d-%m-%Y", "%d/%m/%Y", "%Y-%m-%d", "%d-%m-%y", "%d/%m/%y"): try: parsed = datetime.strptime(text, fmt) break except ValueError: continue if parsed is None: # Fallback to now if unexpected format; status will include warning. return timezone.now() dt = parsed if timezone.is_naive(dt): dt = timezone.make_aware(datetime.combine(dt.date(), time.min)) return dt def handle(self, *args, **options): if load_workbook is None: raise CommandError("openpyxl is required. Install dependencies before running this command.") file_path = Path(options["file"]) sheet_name = options.get("sheet") default_pin = str(options.get("default_pin", "")).strip() output_path_raw = options.get("output") output_path = Path(output_path_raw) if output_path_raw else file_path progress_every = int(options.get("progress_every") or 0) if not file_path.exists(): raise CommandError(f"File not found: {file_path}") if file_path.suffix.lower() != ".xlsx": raise CommandError("Only .xlsx files are supported.") if not (default_pin.isdigit() and len(default_pin) == 6): raise CommandError("--default-pin must be exactly 6 digits.") wb = load_workbook(filename=str(file_path)) ws = wb[sheet_name] if sheet_name else wb.active # Expected columns: # A: S.No, B: ID, C: Name, D: Referral code, E: Join Date, F: Phone, G: Sponsor, H: Status ID_COL = 2 NAME_COL = 3 REFERRAL_COL = 4 JOIN_DATE_COL = 5 PHONE_COL = 6 SPONSOR_COL = 7 STATUS_COL = 8 # Build map of legacy ID -> row index once. id_to_row: dict[str, int] = {} for row in range(2, ws.max_row + 1): legacy_id = self._clean_string(ws.cell(row=row, column=ID_COL).value) if legacy_id: id_to_row[legacy_id] = row created_count = 0 skipped_count = 0 failed_count = 0 total_rows = max(0, ws.max_row - 1) self.stdout.write(f"Starting import for {total_rows} data rows...") for row in range(2, ws.max_row + 1): if progress_every > 0: processed = row - 1 if processed == 1 or processed % progress_every == 0 or processed == total_rows: self.stdout.write( f"Progress: {processed}/{total_rows} | " f"created={created_count}, skipped={skipped_count}, failed={failed_count}" ) name = self._clean_string(ws.cell(row=row, column=NAME_COL).value) phone = self._clean_phone(ws.cell(row=row, column=PHONE_COL).value) join_date_raw = ws.cell(row=row, column=JOIN_DATE_COL).value sponsor_legacy_id = self._clean_string(ws.cell(row=row, column=SPONSOR_COL).value) if not name or not phone: ws.cell(row=row, column=STATUS_COL).value = "SKIPPED: missing name/phone" skipped_count += 1 continue existing_user = User.objects.filter(mobile=phone).first() if existing_user: if not self._clean_string(ws.cell(row=row, column=REFERRAL_COL).value): ws.cell(row=row, column=REFERRAL_COL).value = existing_user.referral_code ws.cell(row=row, column=STATUS_COL).value = "SKIPPED: already registered" skipped_count += 1 continue sponsor_referral = "" sponsor_note = "" if sponsor_legacy_id and sponsor_legacy_id in id_to_row: sponsor_row = id_to_row[sponsor_legacy_id] sponsor_referral = self._clean_string(ws.cell(row=sponsor_row, column=REFERRAL_COL).value) if not sponsor_referral: sponsor_note = " (sponsor referral empty; registered without referral)" elif sponsor_legacy_id: sponsor_note = " (sponsor ID not found; registered without referral)" referred_by = None if sponsor_referral: referred_by = User.objects.filter(referral_code=sponsor_referral).first() if referred_by is None: sponsor_note = " (sponsor referral not found in users; registered without referral)" first_name, last_name = self._split_name(name) joined_at = self._parse_join_date(join_date_raw) try: with transaction.atomic(): user = User.objects.create_user( mobile=phone, login_pin=default_pin, first_name=first_name, last_name=last_name, auth_provider="mobile", is_active=True, is_mobile_verified=True, must_change_login_pin=True, referred_by=referred_by, ) user.date_joined = joined_at user.save(update_fields=["date_joined", "updated_at"]) ws.cell(row=row, column=REFERRAL_COL).value = user.referral_code ws.cell(row=row, column=STATUS_COL).value = f"REGISTERED{ sponsor_note }" created_count += 1 except Exception as exc: # pragma: no cover ws.cell(row=row, column=STATUS_COL).value = f"FAILED: {str(exc)[:180]}" failed_count += 1 try: output_path.parent.mkdir(parents=True, exist_ok=True) wb.save(str(output_path)) except PermissionError as exc: raise CommandError( f"Could not save workbook to '{output_path}'. File may be open in Excel. " f"Close it or pass --output to a writable path." ) from exc self.stdout.write(self.style.SUCCESS("Legacy registration import complete.")) self.stdout.write(f"Created: {created_count}") self.stdout.write(f"Skipped: {skipped_count}") self.stdout.write(f"Failed: {failed_count}") self.stdout.write(f"Saved workbook: {output_path}")