# payments/management/commands/import_wallets_from_excel.py from __future__ import annotations import re from decimal import Decimal, InvalidOperation from pathlib import Path from typing import Optional from django.conf import settings from django.core.management.base import BaseCommand, CommandError from django.db import transaction from django.utils import timezone from openpyxl import load_workbook # --- Adjust these imports if your app layout differs --- from users.models import CustomUser # AUTH_USER_MODEL from payments.models import ViralPeWallet, ViralPeWalletUsage # ------------------------------------------------------- # Column names expected in the Excel header row MOBILE_COL = "mobile_number" EWALLET_COL = "Ewallet_Balance" CASHBACK_COL = "Cashback_Balance" # Purposes to use in ViralPeWalletUsage PURPOSE_EWALLET = "Viralpe Old Balance" PURPOSE_CASHBACK = "Viralpe old cashback" def clean_mobile(val) -> Optional[str]: if val is None: return None s = re.sub(r"\D+", "", str(val)) return s or None def parse_decimal(val) -> Optional[Decimal]: if val is None or str(val).strip() == "": return None try: # openpyxl may give numbers (float) or strings; we normalize via str d = Decimal(str(val)).quantize(Decimal("0.01")) return d except (InvalidOperation, ValueError): return None class Command(BaseCommand): help = ( "Import/credit ViralPeWallet balances from Excel (sheet 'Data') using columns: " f"'{MOBILE_COL}', '{EWALLET_COL}', '{CASHBACK_COL}'. " f"Credits create ViralPeWalletUsage rows with purposes '{PURPOSE_EWALLET}' and '{PURPOSE_CASHBACK}'." ) def add_arguments(self, parser): parser.add_argument("excel_path", type=str, help="Path to the Excel file") parser.add_argument( "--sheet", type=str, default="Data", help="Sheet name (default: Data)", ) parser.add_argument( "--skip-if-already-credited", action="store_true", help=( "Idempotency guard: for each user/purpose/amount, " "skip if an identical credit usage already exists." ), ) parser.add_argument( "--dry-run", action="store_true", help="Parse and validate only; no DB writes.", ) def handle(self, *args, **opts): excel_path = Path(opts["excel_path"]) sheet_name = opts["sheet"] dry_run = bool(opts["dry_run"]) skip_if_existing = bool(opts["skip_if_already_credited"]) if not excel_path.exists(): raise CommandError(f"Excel not found: {excel_path}") # Load workbook/sheet wb = load_workbook(excel_path, data_only=True, read_only=True) if sheet_name not in wb.sheetnames: raise CommandError(f"Sheet '{sheet_name}' not found. Available: {', '.join(wb.sheetnames)}") ws = wb[sheet_name] # Read header header_row = next(ws.iter_rows(min_row=1, max_row=1))[0:] header = [str(c.value).strip() if c.value is not None else "" for c in header_row] required = {MOBILE_COL, EWALLET_COL, CASHBACK_COL} missing = [c for c in required if c not in header] if missing: raise CommandError(f"Missing columns in header: {', '.join(missing)}") idx = {name: header.index(name) for name in header} created_wallets = 0 ew_credits = cashback_credits = 0 skipped_mobile_missing = 0 skipped_user_not_found = 0 skipped_zero_or_invalid = 0 skipped_already = 0 updated_wallets = 0 @transaction.atomic def do_import(): nonlocal created_wallets, ew_credits, cashback_credits, skipped_mobile_missing nonlocal skipped_user_not_found, skipped_zero_or_invalid, skipped_already, updated_wallets # Preload a map of mobile -> user id for speed mobile_to_user = dict(CustomUser.objects.values_list("mobile_number", "id")) # Iterate rows for row in ws.iter_rows(min_row=2, values_only=True): mobile = clean_mobile(row[idx[MOBILE_COL]]) if MOBILE_COL in idx else None if not mobile: skipped_mobile_missing += 1 continue user_id = mobile_to_user.get(mobile) if not user_id: skipped_user_not_found += 1 continue # Parse amounts ewallet_amt = parse_decimal(row[idx[EWALLET_COL]]) if EWALLET_COL in idx else None cashback_amt = parse_decimal(row[idx[CASHBACK_COL]]) if CASHBACK_COL in idx else None # nothing to do? if (not ewallet_amt or ewallet_amt == 0) and (not cashback_amt or cashback_amt == 0): skipped_zero_or_invalid += 1 continue # Ensure wallet wallet, created = ViralPeWallet.objects.select_for_update().get_or_create(user_id=user_id) if created: created_wallets += 1 # Helper to credit and log usage def credit_if_needed(amount: Optional[Decimal], purpose: str): nonlocal skipped_zero_or_invalid, skipped_already, updated_wallets if not amount or amount == 0: return # Optional idempotency: check if a matching usage already exists if skip_if_existing: exists = ViralPeWalletUsage.objects.filter( user_id=user_id, transaction_type="credit", purpose=purpose, # exact amount match # (If your model names the amount field differently, adjust here) amount_used=amount, ).exists() if exists: skipped_already += 1 return if not dry_run: # Increase wallet balance and create usage row wallet.balance = (wallet.balance or Decimal("0.00")) + amount wallet.save(update_fields=["balance", "last_updated"]) ViralPeWalletUsage.objects.create( user_id=user_id, transaction_type="credit", purpose=purpose, amount_used=amount, ) updated_wallets += 1 # Apply credits before_bal = wallet.balance credit_if_needed(ewallet_amt, PURPOSE_EWALLET) if ewallet_amt and ewallet_amt != 0: ew_credits += 1 credit_if_needed(cashback_amt, PURPOSE_CASHBACK) if cashback_amt and cashback_amt != 0: cashback_credits += 1 do_import() self.stdout.write(self.style.SUCCESS( "Done.\n" f" Wallets created: {created_wallets}\n" f" Ewallet credits: {ew_credits}\n" f" Cashback credits: {cashback_credits}\n" f" Skipped (no/invalid amounts): {skipped_zero_or_invalid}\n" f" Skipped (mobile missing): {skipped_mobile_missing}\n" f" Skipped (user not found): {skipped_user_not_found}\n" f" Skipped (already credited, idempotency): {skipped_already}\n" f" Wallets updated (balance changed): {updated_wallets}\n" f"{'(dry-run)' if dry_run else ''}" ))