from django.shortcuts import render # Create your views here. from django.shortcuts import render from payments.models import CommissionConfig from django.urls import reverse def commission_config_list(request): configs = CommissionConfig.objects.all().order_by("-created_at") return render(request, "commission/commission_config_list.html", { "configs": configs, "pagetitle": "⚙️ Commission Configuration", "breadcrumbs": [{"label": "Dashboard", "url": "dashboard"}, {"label": "Commission Config"}], }) import openpyxl from django.shortcuts import render, redirect from django.contrib import messages from .models import GeoLocation from django.contrib.admin.views.decorators import staff_member_required @staff_member_required def upload_geolocations(request): if request.method == 'POST' and request.FILES.get('excel_file'): file = request.FILES['excel_file'] wb = openpyxl.load_workbook(file) sheet = wb.active count = 0 for row in sheet.iter_rows(min_row=2, values_only=True): pincode, location_name, district, state = row if not pincode: continue obj, created = GeoLocation.objects.update_or_create( pincode=str(pincode).strip(), defaults={ 'location_name': location_name.strip() if location_name else '', 'district': district.strip() if district else '', 'state': state.strip() if state else '', } ) count += 1 messages.success(request, f"{count} records uploaded successfully.") return redirect('upload_geolocations') return render(request, 'upload_geolocations.html') # ------------------------------------------------------------------------------------------------------ # ------------------------------------------------------------------------------------------------------ # ------------------------------------------------------------------------------------------------------ # commissions/views.py import datetime import logging from django.shortcuts import render from django.http import JsonResponse, HttpResponse from django.db.models import Sum, F, Value from django.db.models.functions import Coalesce from django.db.models import DecimalField from django.views.decorators.http import require_GET from .models import CommissionSplit, CommissionSummary # optional if present import openpyxl from openpyxl.utils import get_column_letter logger = logging.getLogger(__name__) # utils AGG_FIELDS = { "pincode_amount": "pincode_amount", "district_amount": "district_amount", "state_amount": "state_amount", "company_amount": "company_amount", "tnm_amount": "tnm_amount", "commission_amount": "commission_amount", "transaction_amount": "transaction_amount", "gst_amount": "gst_amount", "user_amount": "user_amount", "referral_amount": "referral_amount", } DISPLAY_COLUMNS = [ ("location_code", "Location Code"), ("location_name", "Location Name"), ("pincode_amount", "Pincode Amount"), ("district_amount", "District Amount"), ("state_amount", "State Amount"), ("company_amount", "Company Amount"), ("tnm_amount", "TnM Amount"), ("commission_amount", "Commission Amount"), ("transaction_amount", "Transaction Amount"), ("gst_amount", "GST Amount"), ("user_amount", "User Amount"), ("referral_amount", "Referral Amount"), ] # Default DecimalField used for aggregated numeric outputs DEFAULT_DECIMAL_OUTPUT = DecimalField(max_digits=18, decimal_places=2) def commission_report_view(request): """ Renders the report page (template will initialize DataTables and call /data/). Provides lists for filter dropdowns. """ states = CommissionSplit.objects.order_by('state').values_list('state', flat=True).distinct() districts = CommissionSplit.objects.order_by('district').values_list('district', flat=True).distinct() pincodes = CommissionSplit.objects.order_by('pincode').values_list('pincode', flat=True).distinct() context = { "states": states, "districts": districts, "pincodes": pincodes, } return render(request, "commissions/report.html", context) def _build_agg_queryset(params): """ Build the aggregated queryset based on params dict-like. Returns (qs_agg, group_field) """ qs = CommissionSplit.objects.all() # filters state = params.get("state") district = params.get("district") pincode = params.get("pincode") date_from = params.get("date_from") date_to = params.get("date_to") if state: qs = qs.filter(state=state) if district: qs = qs.filter(district=district) if pincode: qs = qs.filter(pincode=pincode) if date_from: qs = qs.filter(credited_on__date__gte=date_from) if date_to: qs = qs.filter(credited_on__date__lte=date_to) # grouping group_by = params.get("group_by", "pincode") if group_by not in ("pincode", "district", "state"): group_by = "pincode" if group_by == "pincode": group_field = "pincode" elif group_by == "district": group_field = "district" else: group_field = "state" # build annotations - ensure output_field specified to avoid mixed-type errors annotations = {} for key, fieldname in AGG_FIELDS.items(): # Sum(fieldname) might be Decimal or Integer depending on column type. # Use Coalesce(Sum(...), 0, output_field=DecimalField) to enforce a consistent numeric type. annotations[f"sum__{key}"] = Coalesce(Sum(fieldname), Value(0), output_field=DEFAULT_DECIMAL_OUTPUT) # values + annotate # We expose 'code' and 'location_name' both taken from the group_field qs_agg = qs.values(code=F(group_field)).annotate(location_name=F(group_field), **annotations) return qs_agg, group_field @require_GET def commission_report_data(request): """ Returns JSON for DataTables server-side processing. Accepts: - group_by: pincode|district|state - state, district, pincode - date_from, date_to (YYYY-MM-DD) - DataTables params: start, length, search[value], order[0][column], order[0][dir], draw """ try: params = request.GET group_by = params.get("group_by", "pincode") qs_agg, group_field = _build_agg_queryset(params) # global search (safe: cast search to str) search_value = params.get("search[value]", "") if search_value: # location_name is coming from values(), could be numeric string or text. # Use __icontains which works when the underlying column is text - if values are numeric strings it's fine. qs_agg = qs_agg.filter(location_name__icontains=search_value.strip()) # totals records_total = CommissionSplit.objects.count() # records_filtered should be count of aggregated rows records_filtered = qs_agg.count() # ordering order_col_index = params.get("order[0][column]") order_dir = params.get("order[0][dir]", "asc") # columns mapping (first column 'code' then the numeric fields) columns = ["code"] + [c[0] for c in DISPLAY_COLUMNS[2:]] order_col = None if order_col_index is not None: try: idx = int(order_col_index) if 0 <= idx < len(columns): order_col = columns[idx] except Exception: order_col = None if order_col: if order_col == "code": orm_order = "code" else: orm_order = f"sum__{order_col}" if order_dir == "desc": orm_order = f"-{orm_order}" qs_agg = qs_agg.order_by(orm_order) else: qs_agg = qs_agg.order_by("code") # pagination try: start = int(params.get("start", 0)) length = int(params.get("length", 25)) except Exception: start = 0 length = 25 page_qs = qs_agg[start:start + length] # build response rows data = [] for row in page_qs: code = row.get("code") location_name = row.get("location_name") or code r = { "code": code, "location_name": location_name, } for key in AGG_FIELDS.keys(): # annotated fields are named sum__ val = row.get(f"sum__{key}", 0) # val is Decimal (because of output_field) -> convert to float safely try: r[key] = float(val) except Exception: # fallback: just cast to string then float if possible, else zero try: r[key] = float(str(val)) except Exception: r[key] = 0.0 # optional drilldown: when grouping by state you might want to link to pincode view (example) r["drilldown"] = ( f"/admin/commissions/report/?group_by=pincode&state={location_name}" if group_by == "state" else None ) data.append(r) out = { "draw": int(params.get("draw", 1)), "recordsTotal": records_total, "recordsFiltered": records_filtered, "data": data, } return JsonResponse(out, safe=False) except Exception as exc: # Log full exception and return a JSON error for DataTables to display logger.exception("Error in commission_report_data: %s", exc) # DataTables will treat a non-200 as an error and show "Ajax error" return JsonResponse({"error": str(exc)}, status=500) @require_GET def commission_report_export_xlsx(request): """ Export the aggregated result (no pagination) to XLSX. Uses same filters & grouping as the /data/ endpoint. """ try: params = request.GET qs_agg, group_field = _build_agg_queryset(params) search_value = params.get("search", "") if search_value: qs_agg = qs_agg.filter(location_name__icontains=search_value.strip()) rows = list(qs_agg.order_by("code")) wb = openpyxl.Workbook() ws = wb.active ws.title = "Commission Report" headers = ["Code", "Location"] + [label for key, label in DISPLAY_COLUMNS[2:]] ws.append(headers) for row in rows: code = row.get("code") name = row.get("location_name") or code out_row = [code, name] for key in AGG_FIELDS.keys(): val = row.get(f"sum__{key}", 0) try: out_row.append(float(val)) except Exception: try: out_row.append(float(str(val))) except Exception: out_row.append(0.0) ws.append(out_row) # auto width for i, column_cells in enumerate(ws.columns, 1): length = max(len(str(cell.value or "")) for cell in column_cells) + 2 ws.column_dimensions[get_column_letter(i)].width = min(50, length) response = HttpResponse( content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) filename = f"commission_report_{datetime.date.today().isoformat()}.xlsx" response["Content-Disposition"] = f'attachment; filename="{filename}"' wb.save(response) return response except Exception as exc: logger.exception("Error exporting commission report to XLSX: %s", exc) return HttpResponse(f"Error exporting report: {exc}", status=500)