""" Web-based IPN payment recovery tool (avoids manage.py DB connection issues) Access at: /payments/admin/recover-ipn-payments/ """ from django.contrib.auth.decorators import login_required from django.contrib.admin.views.decorators import staff_member_required from django.shortcuts import render, redirect from django.contrib import messages from django.db import connection import json @login_required @staff_member_required def recover_ipn_payments_view(request): """Admin page to recover lost IPN payments""" if request.method == 'POST' and 'commit' in request.POST: # Actually recover the payments return _do_recovery(request, commit=True) elif request.method == 'POST' and 'preview' in request.POST: # Preview what would be recovered return _do_recovery(request, commit=False) # Show the form c = connection.cursor() c.execute("SELECT COUNT(*) FROM sasapay_ipn_logs") total_ipns = c.fetchone()[0] c.execute("SELECT COUNT(*) FROM repayments WHERE payment_source='automatic'") total_repayments = c.fetchone()[0] return render(request, 'payments/recover_ipn_payments.html', { 'total_ipns': total_ipns, 'total_repayments': total_repayments, 'missing': total_ipns - total_repayments, }) def _do_recovery(request, commit=False): """Process the recovery""" c = connection.cursor() # Get all IPNs — optionally filter by borrower name, deduplicated by TransID name_filter = request.POST.get('name_filter', '').strip().upper() c.execute("SELECT id, created_at, raw_data FROM sasapay_ipn_logs ORDER BY created_at ASC") all_ipns_raw = c.fetchall() # Deduplicate: keep only the FIRST occurrence of each TransID seen_trans_ids = set() all_ipns = [] for row in all_ipns_raw: try: d = json.loads(row[2]) if isinstance(row[2], str) else row[2] tid = d.get('TransID', '') if tid and tid in seen_trans_ids: continue if tid: seen_trans_ids.add(tid) except Exception: pass all_ipns.append(row) # Filter by name if provided if name_filter: filtered = [] for row in all_ipns: try: d = json.loads(row[2]) if isinstance(row[2], str) else row[2] fn = (d.get('FirstName', '') or '').upper() ln = (d.get('LastName', '') or '').upper() if name_filter in fn or name_filter in ln: filtered.append(row) except Exception: pass all_ipns = filtered results = [] recovered = 0 skipped = 0 errors = 0 for ipn_id, created_at, raw_data in all_ipns: try: data = json.loads(raw_data) if isinstance(raw_data, str) else raw_data except Exception: continue trans_id = data.get('TransID', '') bill_ref = data.get('BillRefNumber', '') amount = data.get('TransAmount', 0) fname = data.get('FirstName', '') lname = data.get('LastName', '') # Check if already exists c.execute("SELECT COUNT(*) FROM repayments WHERE mpesa_transaction_id = %s", [trans_id]) if c.fetchone()[0] > 0: skipped += 1 continue if commit: # Actually process it — raw SQL with explicit commit, bypassing Repayment.save() try: import uuid as _uuid from django.db import connection as _c2 from payments.sasapay_service import _phone_variants, _normalise_phone from users.models import CustomUser from loans.models import Loan from datetime import datetime bill_ref = data.get('BillRefNumber', '') msisdn = data.get('MSISDN', '') trans_time = datetime.now() # Match borrower borrower = None for variant in _phone_variants(bill_ref): try: borrower = CustomUser.objects.get(phone_number=variant, role='borrower', is_active=True) break except (CustomUser.DoesNotExist, CustomUser.MultipleObjectsReturned): continue if not borrower: errors += 1 results.append({'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'status': 'no_borrower', 'bill_ref': bill_ref}) continue loan = Loan.active_objects.filter(borrower=borrower, status='active').order_by('-created_at').first() if not loan: errors += 1 results.append({'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'status': 'no_loan', 'borrower': borrower.get_full_name()}) continue loan_id_str = str(loan.id).replace('-', '') rep_id = _uuid.uuid4().hex norm_msisdn = _normalise_phone(msisdn)[:17] if msisdn else '' # Get next receipt number — locked to prevent duplicates LOCK_NAME = 'havengrazuri_ipn_receipt_lock' with _c2.cursor() as _cur: _cur.execute("SELECT GET_LOCK(%s, 10)", [LOCK_NAME]) try: with _c2.cursor() as _cur: _cur.execute("SELECT MAX(CAST(SUBSTRING(receipt_number,5) AS UNSIGNED)) FROM repayments WHERE receipt_number REGEXP '^RCP-[0-9]+$'") max_rep = _cur.fetchone()[0] or 0 _cur.execute("SELECT MAX(CAST(SUBSTRING(receipt_number,5) AS UNSIGNED)) FROM receipts WHERE receipt_number REGEXP '^RCP-[0-9]+$'") max_rec = _cur.fetchone()[0] or 0 next_n = max(max_rep, max_rec) + 1 BLACKLISTED = {1618, 2440} while next_n in BLACKLISTED: next_n += 1 receipt_num = f"RCP-{next_n:06d}" with _c2.cursor() as _cur: _cur.execute(""" INSERT INTO repayments (id, loan_id, amount, payment_method, payment_source, mpesa_transaction_id, mpesa_phone_number, receipt_number, payment_date, created_at) VALUES (%s, %s, %s, 'mpesa', 'automatic', %s, %s, %s, %s, NOW()) """, [rep_id, loan_id_str, float(amount), trans_id, norm_msisdn, receipt_num, trans_time]) _cur.execute(""" UPDATE loans SET amount_paid = COALESCE((SELECT SUM(amount) FROM repayments WHERE loan_id=%s), 0), last_payment_date = %s, updated_at = NOW() WHERE id = %s """, [loan_id_str, trans_time, loan_id_str]) _c2.commit() finally: with _c2.cursor() as _cur: _cur.execute("SELECT RELEASE_LOCK(%s)", [LOCK_NAME]) # Create Receipt row so the repayments page shows "Generated" not "Missing" try: from utils.models import Receipt from loans.models import Repayment as _Repayment from decimal import Decimal as _D from django.db.models import Sum as _Sum _rep_obj = _Repayment.objects.get(pk=rep_id) if not Receipt.objects.filter(repayment_id=rep_id).exists(): prev_paid = _Repayment.objects.filter( loan_id=loan.id, payment_date__lt=trans_time, ).exclude(pk=rep_id).aggregate(total=_Sum('amount'))['total'] or _D('0') prev_balance = loan.principal_amount - prev_paid new_balance_r = prev_balance - _D(str(amount)) Receipt.objects.create( repayment=_rep_obj, loan=loan, borrower=borrower, receipt_number=receipt_num, amount_paid=_D(str(amount)), payment_method='mpesa', payment_date=trans_time, previous_balance=prev_balance, new_balance=new_balance_r, ) except Exception as _re: pass # receipt failure must not block recovery recovered += 1 results.append({'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'status': 'success', 'receipt': receipt_num, 'loan': loan.loan_number}) except Exception as e: errors += 1 results.append({'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'status': 'error', 'message': str(e)}) else: # Just preview from payments.sasapay_service import _phone_variants from users.models import CustomUser from loans.models import Loan borrower = None for variant in _phone_variants(bill_ref): try: borrower = CustomUser.objects.get( phone_number=variant, role='borrower', is_active=True ) break except (CustomUser.DoesNotExist, CustomUser.MultipleObjectsReturned): continue if borrower: loan = Loan.active_objects.filter( borrower=borrower, status='active' ).order_by('-created_at').first() if loan: recovered += 1 results.append({ 'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'borrower': borrower.get_full_name(), 'loan': loan.loan_number, 'status': 'would_recover', }) else: errors += 1 results.append({ 'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'borrower': borrower.get_full_name(), 'status': 'no_loan', }) else: errors += 1 results.append({ 'date': created_at, 'trans_id': trans_id, 'name': f'{fname} {lname}', 'amount': amount, 'bill_ref': bill_ref, 'status': 'no_borrower', }) if commit: messages.success(request, f'Recovered {recovered} payments. {skipped} already existed. {errors} failed.') return render(request, 'payments/recover_ipn_results.html', { 'commit': commit, 'results': results, 'recovered': recovered, 'skipped': skipped, 'errors': errors, })