# sales/views/reports.py import logging import json import csv import string from datetime import datetime, timedelta from decimal import Decimal from django.shortcuts import render, redirect, get_object_or_404 from django.contrib import messages from django.contrib.auth.decorators import login_required from django.http import JsonResponse, HttpResponse from django.views.decorators.csrf import csrf_exempt from django.utils import timezone from django.utils.dateparse import parse_date from django.db import transaction from django.db.models import Sum, Count, Q, F from django.core.exceptions import PermissionDenied from inventory.models import Product from core.models import Branch from core.utils import check_limit_or_block, filter_orders_by_user_branch from accounts.decorators import role_required, perm_required from sales.models import ( POSOrder, POSOrderItem, PaymentTransaction, DailySalesSummary, UnusualTransaction, Customer, ) from companies.permissions import company_feature_required from sales.forms import POSOrderForm, POSOrderItemForm logger = logging.getLogger(__name__) @login_required @perm_required('can_view_reports') def payment_summary(request): """ Payment summary view for displaying analytics and sales data. This function dynamically generates summaries for today and the last 7 days. """ # Get today's date today = timezone.now().date() # Generate today's summary today_orders = POSOrder.objects.filter(company=request.company, created_at__date=today, status='completed') today_summary_data = { 'date': today, 'total_sales': today_orders.aggregate(total=Sum('final_amount'))['total'] or 0, 'total_transactions': today_orders.count(), } # Calculate method-specific totals for today for method in ['pos', 'transfer', 'cash', 'mobile_money']: method_orders = today_orders.filter(payment_method=method) today_summary_data[f'{method}_sales'] = method_orders.aggregate( total=Sum('final_amount') )['total'] or 0 today_summary_data[f'{method}_transactions'] = method_orders.count() # Save or update today's summary in the database today_summary, created = DailySalesSummary.objects.update_or_create( date=today, company=request.company, defaults={ 'total_revenue': today_summary_data['total_sales'], 'total_transactions': today_summary_data['total_transactions'], 'cash_revenue': today_summary_data.get('cash_sales', 0), 'cash_transactions': today_summary_data.get('cash_transactions', 0), 'transfer_revenue': today_summary_data.get('transfer_sales', 0), 'transfer_transactions': today_summary_data.get('transfer_transactions', 0), 'pos_revenue': today_summary_data.get('pos_sales', 0), 'pos_transactions': today_summary_data.get('pos_transactions', 0), 'mobile_money_revenue': today_summary_data.get('mobile_money_sales', 0), 'mobile_money_transactions': today_summary_data.get('mobile_money_transactions', 0), } ) # Generate summaries for the last 7 days recent_dates = [today - timedelta(days=i) for i in range(7)] recent_summaries = [] for date in recent_dates: orders = POSOrder.objects.filter(company=request.company, created_at__date=date, status='completed') summary_data = { 'date': date, 'total_sales': orders.aggregate(total=Sum('final_amount'))['total'] or 0, 'total_transactions': orders.count(), } # Calculate method-specific totals for each day for method in ['pos', 'transfer', 'cash', 'mobile_money']: method_orders = orders.filter(payment_method=method) summary_data[f'{method}_sales'] = method_orders.aggregate( total=Sum('final_amount') )['total'] or 0 summary_data[f'{method}_transactions'] = method_orders.count() # Save or update the summary in the database summary, _ = DailySalesSummary.objects.update_or_create( date=date, company=request.company, defaults={ 'total_revenue': summary_data['total_sales'], 'total_transactions': summary_data['total_transactions'], 'cash_revenue': summary_data.get('cash_sales', 0), 'cash_transactions': summary_data.get('cash_transactions', 0), 'transfer_revenue': summary_data.get('transfer_sales', 0), 'transfer_transactions': summary_data.get('transfer_transactions', 0), 'pos_revenue': summary_data.get('pos_sales', 0), 'pos_transactions': summary_data.get('pos_transactions', 0), 'mobile_money_revenue': summary_data.get('mobile_money_sales', 0), 'mobile_money_transactions': summary_data.get('mobile_money_transactions', 0), } ) recent_summaries.append(summary) # Fetch recent summaries (last 7 days) for display recent_summaries = DailySalesSummary.objects.filter( company=request.company, date__gte=today - timedelta(days=7) ).order_by('-date') # Calculate payment method distribution for today payment_methods = POSOrder.objects.filter(company=request.company, created_at__date=today, status='completed' ).values('payment_method').annotate( total=Sum('final_amount'), count=Count('id') ).order_by('-total') # Calculate percentages for payment methods total_amount = sum(pm['total'] or 0 for pm in payment_methods) for pm in payment_methods: pm['percentage'] = round(((pm['total'] or 0) / total_amount) * 100, 2) if total_amount > 0 else 0 # Map payment method codes to human-readable names PAYMENT_METHOD_CHOICES = dict(POSOrder.PAYMENT_METHODS) for pm in payment_methods: pm['display_name'] = PAYMENT_METHOD_CHOICES.get(pm['payment_method'], "Unknown") # Context dictionary with detailed explanation of each variable context = { 'summary': today_summary, # Today's summary 'today_summary': today_summary, # Duplicate for template consistency 'recent_summaries': recent_summaries, # Last 7 days' summaries 'payment_methods': payment_methods, # Payment method distribution 'user_role': request.user.role, # User role (e.g., admin, cashier) 'user_name': request.user.get_full_name() or request.user.username, # User name } return render(request, 'sales/payment_summary.html', context) @login_required @perm_required('can_view_reports') def daily_dashboard(request): """Today's sales dashboard - branch aware""" today = timezone.now().date() user = request.user selected_branch_id = request.GET.get("branch", "all") # Base queryset: today's completed orders orders_qs = POSOrder.objects.filter(company=request.company, created_at__date=today, status="completed", ) # --- ROLE / BRANCH LOGIC (Option A strict) --- if user.role in ("manager", "cashier"): # Force their own branch if user.branch: orders_qs = orders_qs.filter(branch=user.branch) selected_branch_id = str(user.branch.id) else: # No branch assigned = no orders orders_qs = POSOrder.objects.none() selected_branch_id = "none" else: # superadmin / admin can choose branch from dropdown if selected_branch_id and selected_branch_id != "all": try: branch_id = int(selected_branch_id) orders_qs = orders_qs.filter(branch_id=branch_id) except ValueError: pass # ignore bad input # --- Compute summary from filtered orders_qs --- total_revenue = orders_qs.aggregate(total=Sum("final_amount"))["total"] or 0 total_transactions = orders_qs.count() # Payment method breakdown def pm_totals(method_code): m_qs = orders_qs.filter(payment_method=method_code) return ( m_qs.aggregate(total=Sum("final_amount"))["total"] or 0, m_qs.count(), ) cash_revenue, cash_transactions = pm_totals("cash") transfer_revenue, transfer_transactions = pm_totals("transfer") pos_revenue, pos_transactions = pm_totals("pos") mobile_revenue, mobile_transactions = pm_totals("mobile_money") # Profit + credit outstanding based on filtered orders from decimal import Decimal total_profit = Decimal("0") order_items = list(POSOrderItem.objects.filter(order__in=orders_qs).select_related("product")) for item in order_items: selling = item.unit_price or Decimal("0") cost = (item.product.cost_price or Decimal("0")) if item.product else Decimal("0") qty = item.quantity or 0 total_profit += (selling - cost) * qty credit_outstanding = ( orders_qs.filter(balance_amount__gt=0) .aggregate(total=Sum("balance_amount"))["total"] or 0 ) # Build a simple "summary" object so template works unchanged class SummaryObj: pass summary = SummaryObj() summary.total_revenue = total_revenue summary.total_transactions = total_transactions summary.cash_revenue = cash_revenue summary.cash_transactions = cash_transactions summary.transfer_revenue = transfer_revenue summary.transfer_transactions = transfer_transactions summary.pos_revenue = pos_revenue summary.pos_transactions = pos_transactions summary.mobile_money_revenue = mobile_revenue summary.mobile_money_transactions = mobile_transactions summary.total_profit = total_profit summary.credit_outstanding = credit_outstanding # --- Low stock products (global for now) --- low_stock_products = Product.objects.filter(company=request.company, stock_status__in=["low_stock", "out_of_stock"] ).order_by("stock_quantity")[:10] # --- Top selling items (last 24h) from filtered orders --- last_24h_start = timezone.now() - timedelta(hours=24) top_items_qs = POSOrderItem.objects.filter( order__company=request.company, order__created_at__gte=last_24h_start, order__status="completed", ) # Apply same branch visibility rules to items if user.role in ("manager", "cashier"): if user.branch: top_items_qs = top_items_qs.filter(order__branch=user.branch) else: top_items_qs = POSOrderItem.objects.none() else: if selected_branch_id and selected_branch_id not in ("all", "none"): try: branch_id = int(selected_branch_id) top_items_qs = top_items_qs.filter(order__branch_id=branch_id) except ValueError: pass top_selling_items = ( top_items_qs.values("product__name", "product__sku") .annotate( total_quantity=Sum("quantity"), total_revenue=Sum("total_price"), ) .order_by("-total_quantity")[:5] ) # For branch dropdown on the page (if your template uses it) branches = Branch.objects.filter(company=request.company, is_active=True).order_by("name") context = { "summary": summary, "top_selling_items": top_selling_items, "low_stock_products": low_stock_products, "today_orders": orders_qs.order_by("-created_at"), "branches": branches, "selected_branch_id": selected_branch_id, "user_role": user.role, "user_name": user.get_full_name() or user.username, } return render(request, "sales/daily_dashboard.html", context) # sales/views.py @login_required @perm_required('can_view_reports') def yesterday_summary(request): """Yesterday's sales summary – branch aware""" yesterday = timezone.now().date() - timedelta(days=1) user = request.user selected_branch_id = request.GET.get("branch", "all") # Base queryset: yesterday's completed orders orders_qs = POSOrder.objects.filter(company=request.company, created_at__date=yesterday, status="completed", ) # --- ROLE / BRANCH LOGIC --- if user.role in ("manager", "cashier"): if user.branch: orders_qs = orders_qs.filter(branch=user.branch) selected_branch_id = str(user.branch.id) else: orders_qs = POSOrder.objects.none() selected_branch_id = "none" else: if selected_branch_id and selected_branch_id != "all": try: branch_id = int(selected_branch_id) orders_qs = orders_qs.filter(branch_id=branch_id) except ValueError: pass # Summary (local, not saved to DB to avoid corrupting global summary) from decimal import Decimal total_revenue = orders_qs.aggregate(total=Sum("final_amount"))["total"] or Decimal("0") total_transactions = orders_qs.count() def pm_totals(method_code): m_qs = orders_qs.filter(payment_method=method_code) return ( m_qs.aggregate(total=Sum("final_amount"))["total"] or Decimal("0"), m_qs.count(), ) cash_revenue, cash_transactions = pm_totals("cash") transfer_revenue, transfer_transactions = pm_totals("transfer") pos_revenue, pos_transactions = pm_totals("pos") mobile_revenue, mobile_transactions = pm_totals("mobile_money") class SummaryObj: pass summary = SummaryObj() summary.total_revenue = total_revenue summary.total_transactions = total_transactions summary.cash_revenue = cash_revenue summary.cash_transactions = cash_transactions summary.transfer_revenue = transfer_revenue summary.transfer_transactions = transfer_transactions summary.pos_revenue = pos_revenue summary.pos_transactions = pos_transactions summary.mobile_money_revenue = mobile_revenue summary.mobile_money_transactions = mobile_transactions # Unusual transactions should also follow branch filter unusual_transactions = UnusualTransaction.objects.filter(company=request.company, order__in=orders_qs ).select_related("order") # Apply extra filters payment_method = request.GET.get("payment_method", "") min_amount = request.GET.get("min_amount", "") filtered_orders = orders_qs if payment_method: filtered_orders = filtered_orders.filter(payment_method=payment_method) if min_amount: try: min_amount_value = float(min_amount) filtered_orders = filtered_orders.filter(final_amount__gte=min_amount_value) except ValueError: pass # CSV export uses branch + filters if request.GET.get("export") == "csv": return export_yesterday_orders_csv(filtered_orders) branches = Branch.objects.filter(company=request.company, is_active=True).order_by("name") context = { "summary": summary, "yesterday_orders": filtered_orders.order_by("-created_at"), "unusual_transactions": unusual_transactions, "payment_method": payment_method, "min_amount": min_amount, "branches": branches, "selected_branch_id": selected_branch_id, "user_role": user.role, "user_name": user.get_full_name() or user.username, } return render(request, "sales/yesterday_summary.html", context) def export_yesterday_orders_csv(orders): """Export yesterday's orders to CSV""" response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="yesterday_orders.csv"' writer = csv.writer(response) writer.writerow([ 'Order Number', 'Date', 'Cashier', 'Payment Method', 'Total Amount', 'Customer Name', 'Customer Phone' ]) for order in orders: writer.writerow([ order.order_number, order.created_at.strftime('%Y-%m-%d %H:%M'), order.cashier, order.get_payment_method_display(), order.final_amount, order.customer_name, order.customer_phone ]) return response @login_required @perm_required('can_view_reports') def transaction_history(request): """Show recent POS orders with search, day/range filter, pagination, and per-order profit.""" from django.core.paginator import Paginator today = timezone.now().date() date_str = request.GET.get("date") or "" date_from = request.GET.get("date_from") or "" date_to = request.GET.get("date_to") or "" search_query = request.GET.get("q") or "" orders = POSOrder.objects.filter(company=request.company, status="completed").order_by("-created_at") # Date range takes priority over single date if date_from or date_to: if date_from: df = parse_date(date_from) if df: orders = orders.filter(created_at__date__gte=df) if date_to: dt = parse_date(date_to) if dt: orders = orders.filter(created_at__date__lte=dt) elif date_str: selected_date = parse_date(date_str) or today orders = orders.filter(created_at__date=selected_date) else: selected_date = today orders = orders.filter(created_at__date=selected_date) if search_query: orders = orders.filter( Q(order_number__icontains=search_query) | Q(customer_name__icontains=search_query) | Q(customer_phone__icontains=search_query) ) paginator = Paginator(orders, 30) page_number = request.GET.get("page") page_obj = paginator.get_page(page_number) # ── Bulk profit calculation ────────────────────────────────────────────── # Fetch all line items for orders on THIS page only (one query). page_order_ids = [o.id for o in page_obj] items_qs = POSOrderItem.objects.filter( order_id__in=page_order_ids ).select_related('product') # Build a dict: order_id -> total_profit from decimal import Decimal profit_map = {} for item in items_qs: oid = item.order_id selling = item.unit_price or Decimal('0') cost = (item.product.cost_price or Decimal('0')) if item.product else Decimal('0') qty = item.quantity or 0 profit_map[oid] = profit_map.get(oid, Decimal('0')) + (selling - cost) * qty # Attach profit to each order object for the template for order in page_obj: order.profit = profit_map.get(order.id, Decimal('0')) # ──────────────────────────────────────────────────────────────────────── context = { "orders": page_obj, "page_obj": page_obj, "selected_date": date_str or today, "date_from": date_from, "date_to": date_to, "search_query": search_query, "user_role": request.user.role, } return render(request, "sales/transaction_history.html", context) @login_required @perm_required('can_export_data') def export_transactions(request): """Export filtered POS orders (same filters as transaction_history).""" today = timezone.now().date() date_str = request.GET.get("date") or "" search_query = request.GET.get("q") or "" if date_str: # Use parse_date to convert the date string into a date object selected_date = parse_date(date_str) or today else: selected_date = today orders = POSOrder.objects.filter(company=request.company, status="completed").order_by("-created_at") if selected_date: orders = orders.filter(created_at__date=selected_date) if search_query: orders = orders.filter( Q(order_number__icontains=search_query) | Q(customer_name__icontains=search_query) | Q(customer_phone__icontains=search_query) ) response = HttpResponse(content_type="text/csv") filename = f"transactions_{selected_date.isoformat()}.csv" response["Content-Disposition"] = f'attachment; filename="{filename}"' writer = csv.writer(response) writer.writerow( [ "order_number", "date", "time", "customer_name", "customer_phone", "payment_method", "total_amount", "discount_amount", "final_amount", "status", ] ) for order in orders: dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at writer.writerow( [ order.order_number, dt.date().isoformat(), dt.time().strftime("%H:%M:%S"), order.customer_name or "", order.customer_phone or "", order.payment_method, order.total_amount, order.discount_amount, order.final_amount, order.status, ] ) return response @login_required @perm_required('can_export_data') def export_all_orders(request): """Export all POS orders as CSV (summary).""" response = HttpResponse(content_type="text/csv") filename = f"orders_all_{timezone.now().date().isoformat()}.csv" response["Content-Disposition"] = f'attachment; filename="{filename}"' writer = csv.writer(response) writer.writerow( [ "order_number", "created_date", "created_time", "customer_name", "customer_phone", "payment_method", "total_amount", "discount_amount", "final_amount", "status", ] ) orders = POSOrder.objects.filter(company=request.company).order_by("-created_at") for order in orders: dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at writer.writerow( [ order.order_number, dt.date().isoformat(), dt.time().strftime("%H:%M:%S"), order.customer_name or "", order.customer_phone or "", order.payment_method, order.total_amount, order.discount_amount, order.final_amount, order.status, ] ) return response @login_required @perm_required('can_export_data') def export_order_items(request): """Export all order line items as CSV.""" response = HttpResponse(content_type="text/csv") filename = f"order_items_{timezone.now().date().isoformat()}.csv" response["Content-Disposition"] = f'attachment; filename="{filename}"' writer = csv.writer(response) writer.writerow( [ "order_number", "order_date", "product_name", "product_sku", "quantity", "unit_price", "line_total", ] ) items = POSOrderItem.objects.select_related("order", "product").filter(order__company=request.company).order_by( "-order__created_at" ) for item in items: order = item.order product = item.product dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at writer.writerow( [ order.order_number, dt.date().isoformat(), product.name if product else "", product.sku if product else "", item.quantity, item.unit_price, item.total_price, ] ) return response @login_required @perm_required('can_export_data') def export_payments(request): """Export all payment transactions as CSV.""" response = HttpResponse(content_type="text/csv") filename = f"payments_{timezone.now().date().isoformat()}.csv" response["Content-Disposition"] = f'attachment; filename="{filename}"' writer = csv.writer(response) writer.writerow( [ "order_number", "payment_method", "amount", "reference_number", "transaction_id", "status", "created_at", ] ) payments = PaymentTransaction.objects.select_related("order").filter(company=request.company).order_by( "-created_at" ) for p in payments: writer.writerow( [ p.order.order_number if p.order else "", p.payment_method, p.amount, p.reference_number, p.transaction_id, p.status, p.created_at, ] ) return response @login_required @perm_required('can_view_pnl') @company_feature_required('feature_profit_loss') def pnl_report(request): """One-click P&L report with date range filter and export.""" from accounts.audit import log_action log_action(request, 'view_pnl', target='P&L Report') today = timezone.now().date() date_from_str = request.GET.get('date_from', str(today)) date_to_str = request.GET.get('date_to', str(today)) try: date_from = datetime.strptime(date_from_str, '%Y-%m-%d').date() except ValueError: date_from = today try: date_to = datetime.strptime(date_to_str, '%Y-%m-%d').date() except ValueError: date_to = today # All completed orders in range orders = POSOrder.objects.filter( company=request.company, created_at__date__gte=date_from, created_at__date__lte=date_to, status='completed', ) # Apply branch filter user = request.user if user.role in ('manager', 'cashier') and getattr(user, 'branch', None): orders = orders.filter(branch=user.branch) total_revenue = orders.aggregate(t=Sum('final_amount'))['t'] or Decimal('0') total_orders_count = orders.count() credit_outstanding = orders.filter(balance_amount__gt=0).aggregate(t=Sum('balance_amount'))['t'] or Decimal('0') total_cost = Decimal('0') gross_profit = Decimal('0') items = POSOrderItem.objects.filter(order__in=orders).select_related('product') for item in items: cost = (item.product.cost_price or Decimal('0')) total_cost += cost * item.quantity gross_profit += (item.unit_price - cost) * item.quantity from analytics.models import OperatingExpense operating_expenses = OperatingExpense.objects.filter( company=request.company, expense_date__gte=date_from, expense_date__lte=date_to ) total_operating_expenses = operating_expenses.aggregate(t=Sum('amount'))['t'] or Decimal('0') true_net_profit = gross_profit - total_operating_expenses # Payment breakdown payment_breakdown = [] for code, label in POSOrder.PAYMENT_METHODS: m_rev = orders.filter(payment_method=code).aggregate(t=Sum('final_amount'))['t'] or Decimal('0') m_count = orders.filter(payment_method=code).count() if m_count > 0: payment_breakdown.append({'code': code, 'label': label, 'revenue': m_rev, 'count': m_count}) # Per-day breakdown from django.db.models.functions import TruncDate daily_data = ( orders.annotate(day=TruncDate('created_at')) .values('day') .annotate(rev=Sum('final_amount'), cnt=Count('id')) .order_by('day') ) context = { 'date_from': date_from, 'date_to': date_to, 'total_revenue': total_revenue, 'total_cost': total_cost, 'total_profit': gross_profit, # Renamed internally for gross profit 'gross_profit': gross_profit, 'total_operating_expenses': total_operating_expenses, 'true_net_profit': true_net_profit, 'credit_outstanding': credit_outstanding, 'total_orders_count': total_orders_count, 'payment_breakdown': payment_breakdown, 'daily_data': daily_data, 'user_role': user.role, 'user_name': user.get_full_name() or user.username, } return render(request, 'sales/pnl_report.html', context) @login_required @perm_required('can_export_data') @company_feature_required('feature_profit_loss') def export_pnl(request): """Export P&L as Excel or CSV.""" import openpyxl from openpyxl.styles import Font, PatternFill, Alignment today = timezone.now().date() date_from_str = request.GET.get('date_from', str(today)) date_to_str = request.GET.get('date_to', str(today)) try: date_from = datetime.strptime(date_from_str, '%Y-%m-%d').date() except ValueError: date_from = today try: date_to = datetime.strptime(date_to_str, '%Y-%m-%d').date() except ValueError: date_to = today orders = POSOrder.objects.filter( company=request.company, created_at__date__gte=date_from, created_at__date__lte=date_to, status='completed', ).order_by('created_at') user = request.user if user.role in ('manager', 'cashier') and getattr(user, 'branch', None): orders = orders.filter(branch=user.branch) wb = openpyxl.Workbook() ws = wb.active ws.title = 'P&L Report' header_fill = PatternFill('solid', fgColor='667EEA') header_font = Font(bold=True, color='FFFFFF') bold = Font(bold=True) headers = ['Order #', 'Date', 'Cashier', 'Customer', 'Payment', 'Revenue', 'Cost', 'Profit', 'Credit Balance'] for col, h in enumerate(headers, 1): cell = ws.cell(row=1, column=col, value=h) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal='center') row_idx = 2 totals = {'revenue': Decimal('0'), 'cost': Decimal('0'), 'profit': Decimal('0')} for order in orders: items = POSOrderItem.objects.filter(order=order).select_related('product') # Calculate precise cost cost = sum(((item.product.cost_price or Decimal('0')) if item.product else Decimal('0')) * item.quantity for item in items) profit = float(order.final_amount) - float(cost) totals['revenue'] += order.final_amount totals['cost'] += cost totals['profit'] += Decimal(str(profit)) ws.append([ order.order_number, order.created_at.strftime('%Y-%m-%d %H:%M'), order.cashier, order.customer_name or '—', order.get_payment_method_display(), float(order.final_amount), float(cost), profit, float(order.balance_amount), ]) row_idx += 1 # Totals row ws.append(['', '', '', '', 'TOTALS', float(totals['revenue']), float(totals['cost']), float(totals['profit']), '']) total_row = ws.max_row for col in range(1, 10): ws.cell(row=total_row, column=col).font = bold response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="PnL_{date_from}_to_{date_to}.xlsx"' wb.save(response) from accounts.audit import log_action log_action(request, 'export_data', target=f'P&L {date_from} to {date_to}') return response # ═══════════════════════════════════════════════════════════ # FEATURE 7: SUSPICIOUS ACTIVITY # ═══════════════════════════════════════════════════════════ @login_required @perm_required('can_do_end_of_day') def end_of_day(request): """End-of-day cash reconciliation view.""" from sales.models import CashReconciliation from django.db.models import Sum today = timezone.now().date() company_settings = getattr(request.company, 'settings', None) if getattr(request, 'company', None) else None currency_symbol = getattr(company_settings, 'currency_symbol', '₦') or '₦' expected = ( POSOrder.objects.filter( company=request.company, status='completed', payment_method='cash', created_at__date=today, ).aggregate(total=Sum('final_amount'))['total'] or 0 ) existing = CashReconciliation.objects.filter(company=request.company, date=today).first() if request.method == 'POST': try: actual = Decimal(str(request.POST.get('actual', '0'))) except Exception: actual = Decimal('0') notes = request.POST.get('notes', '') rec, _ = CashReconciliation.objects.update_or_create( company=request.company, date=today, defaults={ 'expected': expected, 'actual': actual, 'cashier': request.user.username, 'notes': notes, } ) messages.success(request, f"Reconciliation saved. Variance: {currency_symbol}{rec.variance:+.2f}") return redirect('sales:end_of_day') history = CashReconciliation.objects.filter(company=request.company).order_by('-date')[:10] context = { 'today': today, 'expected': expected, 'existing': existing, 'history': history, 'currency_symbol': currency_symbol, } return render(request, 'sales/end_of_day.html', context) # ───────────────────────────────────────────────────────────── # CUSTOMER DETAIL (#11) # ───────────────────────────────────────────────────────────── @login_required @role_required(['admin', 'manager']) def sales_by_item_report(request): """Sales by Item Summary Report with CSV Export. Defaults to today's data when no date filter is supplied, to prevent a server timeout caused by scanning all historical orders. All data is scoped to request.company. """ today = timezone.now().date() start_date = request.GET.get('start_date') or str(today) end_date = request.GET.get('end_date') or str(today) # Branch filter — managers/cashiers see only their branch user = request.user orders = POSOrder.objects.filter(company=request.company, status='completed') orders = orders.filter(created_at__date__gte=start_date, created_at__date__lte=end_date) if user.role in ('manager', 'cashier') and getattr(user, 'branch', None): orders = orders.filter(branch=user.branch) # Single query for all matching line items — avoids N+1 order_items = POSOrderItem.objects.filter( order__in=orders ).select_related('product', 'product__category', 'order') from decimal import Decimal item_summaries = {} for item in order_items: key = item.product.id if item.product else f"Unlinked-{item.product_name}" if key not in item_summaries: item_summaries[key] = { 'item_name': item.product.name if item.product else item.product_name, 'sku': item.product.sku if item.product else '', 'category': item.product.category.name if item.product and item.product.category else 'No Category', 'quantity': 0, 'gross_sales': Decimal('0.00'), 'refunds': Decimal('0.00'), 'discounts': Decimal('0.00'), 'net_sales': Decimal('0.00'), 'cost_of_goods': Decimal('0.00'), 'gross_profit': Decimal('0.00'), 'margin': Decimal('0.00'), 'taxes': Decimal('0.00'), } summary = item_summaries[key] summary['quantity'] += item.quantity gross = item.unit_price * item.quantity summary['gross_sales'] += gross order_subtotal = item.order.total_amount if order_subtotal > 0: ratio = gross / order_subtotal item_discount = item.order.discount_amount * ratio item_tax = item.order.tax_amount * ratio else: item_discount = Decimal('0.00') item_tax = Decimal('0.00') summary['discounts'] += item_discount summary['taxes'] += item_tax summary['refunds'] += Decimal('0.00') net = gross - item_discount summary['net_sales'] += net cost = (item.product.cost_price or Decimal('0.00')) if item.product else Decimal('0.00') cog = cost * item.quantity summary['cost_of_goods'] += cog profit = net - cog summary['gross_profit'] += profit for key, summary in item_summaries.items(): if summary['net_sales'] > 0: summary['margin'] = (summary['gross_profit'] / summary['net_sales']) * 100 report_data = sorted(item_summaries.values(), key=lambda x: x['quantity'], reverse=True) # Grand totals for the summary row grand_totals = { 'quantity': sum(r['quantity'] for r in report_data), 'gross_sales': sum(r['gross_sales'] for r in report_data), 'refunds': sum(r['refunds'] for r in report_data), 'discounts': sum(r['discounts'] for r in report_data), 'net_sales': sum(r['net_sales'] for r in report_data), 'cost_of_goods': sum(r['cost_of_goods'] for r in report_data), 'gross_profit': sum(r['gross_profit'] for r in report_data), 'taxes': sum(r['taxes'] for r in report_data), } if grand_totals['net_sales'] > 0: grand_totals['margin'] = (grand_totals['gross_profit'] / grand_totals['net_sales']) * 100 else: grand_totals['margin'] = Decimal('0') if request.GET.get('export') == 'csv': response = HttpResponse(content_type='text/csv') filename = f"sales_by_item_{start_date}_to_{end_date}.csv" response['Content-Disposition'] = f'attachment; filename="{filename}"' writer = csv.writer(response) writer.writerow(['Item name', 'SKU', 'Category', 'Items sold', 'Gross sales', 'Refunds', 'Discounts', 'Net sales', 'Cost of goods', 'Gross profit', 'Margin', 'Taxes']) for row in report_data: writer.writerow([ row['item_name'], row['sku'], row['category'], row['quantity'], round(row['gross_sales'], 2), round(row['refunds'], 2), round(row['discounts'], 2), round(row['net_sales'], 2), round(row['cost_of_goods'], 2), round(row['gross_profit'], 2), f"{round(row['margin'], 2)}%", round(row['taxes'], 2) ]) # Grand totals row in CSV writer.writerow([ 'TOTAL', '', '', grand_totals['quantity'], round(grand_totals['gross_sales'], 2), round(grand_totals['refunds'], 2), round(grand_totals['discounts'], 2), round(grand_totals['net_sales'], 2), round(grand_totals['cost_of_goods'], 2), round(grand_totals['gross_profit'], 2), f"{round(grand_totals['margin'], 2)}%", round(grand_totals['taxes'], 2) ]) return response context = { 'start_date': start_date, 'end_date': end_date, 'report_data': report_data, 'grand_totals': grand_totals, 'user_role': request.user.role, 'user_name': request.user.get_full_name() or request.user.username, } return render(request, 'sales/sales_by_item.html', context)