import logging import csv logger = logging.getLogger(__name__) from decimal import Decimal from django.core.paginator import Paginator from django.forms import modelformset_factory from django.shortcuts import render, get_object_or_404, redirect from django.http import JsonResponse, HttpResponse from django.contrib.auth.decorators import login_required from django.db import transaction from django.utils.timezone import timedelta from datetime import datetime from django.views.decorators.http import require_http_methods from django.db.models import Sum, Count, Q, F, DecimalField from django.db.models.functions import TruncMonth, Coalesce from django.core.exceptions import PermissionDenied from .forms import SaleForm, SaleItemForm from .models import Sale, SaleItem from inventory.models import Product from customers.models import Customer from settings.models import Shop # Assuming shop details are in the settings app from django.db.models import Q import json from django.views.decorators.csrf import csrf_exempt from django.utils import timezone from django.contrib import messages from django.utils.timezone import localtime import pytz logger = logging.getLogger('sales') @login_required(login_url='/auth/login/') def dashboard(request): # Schema separation provides tenant isolation - no tenant filtering needed try: today = timezone.now().date() start_of_week = today - timedelta(days=today.weekday()) start_of_month = today.replace(day=1) six_months_ago = today - timedelta(days=6*30) two_weeks_ago = today - timedelta(days=14) except Exception as e: logger.error(f"Error in dashboard date calculations: {e}") # Fallback to a basic dashboard return render(request, 'sales/sale_dashboard.html', { 'daily_sales': {}, 'weekly_sales': {}, 'monthly_sales': {}, 'error_message': 'Unable to load dashboard data. Please try again later.' }) # Daily sales with transaction count - schema isolation provides tenant filtering try: daily_sales = Sale.objects.filter(sale_date__date=today).aggregate( total_sales=Sum('total_amount'), cash_sales=Sum('total_amount', filter=Q(payment_method='cash')), mpesa_sales=Sum('total_amount', filter=Q(payment_method='mpesa')), credit_sales=Sum('total_amount', filter=Q(payment_method='credit')), cheque_sales=Sum('total_amount', filter=Q(payment_method='cheque')), transaction_count=Count('id') ) except Exception as e: logger.error(f"Error calculating daily sales: {e}") daily_sales = { 'total_sales': 0, 'cash_sales': 0, 'mpesa_sales': 0, 'credit_sales': 0, 'cheque_sales': 0, 'transaction_count': 0 } # Weekly sales with growth comparison - schema isolation provides tenant filtering weekly_sales = Sale.objects.filter(sale_date__date__gte=start_of_week).aggregate( total_sales=Sum('total_amount'), cash_sales=Sum('total_amount', filter=Q(payment_method='cash')), mpesa_sales=Sum('total_amount', filter=Q(payment_method='mpesa')), credit_sales=Sum('total_amount', filter=Q(payment_method='credit')), cheque_sales=Sum('total_amount', filter=Q(payment_method='cheque')) ) # Previous week for growth calculation - schema isolation provides tenant filtering prev_week_start = start_of_week - timedelta(days=7) prev_week_sales = Sale.objects.filter( sale_date__date__gte=prev_week_start, sale_date__date__lt=start_of_week ).aggregate(total=Sum('total_amount'))['total'] or 0 # Calculate weekly growth weekly_growth = 0 if prev_week_sales > 0: weekly_growth = ((weekly_sales['total_sales'] or 0) - prev_week_sales) / prev_week_sales * 100 # Monthly sales - schema isolation provides tenant filtering monthly_sales = Sale.objects.filter(sale_date__date__gte=start_of_month).aggregate( total_sales=Sum('total_amount'), cash_sales=Sum('total_amount', filter=Q(payment_method='cash')), mpesa_sales=Sum('total_amount', filter=Q(payment_method='mpesa')), credit_sales=Sum('total_amount', filter=Q(payment_method='credit')), cheque_sales=Sum('total_amount', filter=Q(payment_method='cheque')) ) # Six months sales for chart - schema isolation provides tenant filtering six_months_sales = Sale.objects.filter( sale_date__date__gte=six_months_ago ).annotate( month=TruncMonth('sale_date') ).values('month').annotate( total_sales=Sum('total_amount'), cash_sales=Sum('total_amount', filter=Q(payment_method='cash')), mpesa_sales=Sum('total_amount', filter=Q(payment_method='mpesa')), credit_sales=Sum('total_amount', filter=Q(payment_method='credit')), cheque_sales=Sum('total_amount', filter=Q(payment_method='cheque')) ).order_by('month') # Outstanding credits and debt analysis - schema isolation provides tenant filtering credit_sales_with_balance = Sale.objects.filter( payment_method='credit' ).annotate( outstanding=F('total_amount') - Coalesce(F('credit_amount'), Decimal('0'), output_field=DecimalField()) ).filter(outstanding__gt=0) total_outstanding_credit = credit_sales_with_balance.aggregate( total=Sum('outstanding') )['total'] or 0 customers_with_debt = credit_sales_with_balance.values('customer').distinct().count() # Overdue debts (over 2 weeks old) - schema isolation provides tenant filtering overdue_debts = Sale.objects.filter( payment_method='credit', sale_date__date__lt=two_weeks_ago ).annotate( outstanding_amount=F('total_amount') - Coalesce(F('credit_amount'), Decimal('0'), output_field=DecimalField()) ).filter(outstanding_amount__gt=0).select_related('customer').order_by('-outstanding_amount') total_overdue_amount = overdue_debts.aggregate( total=Sum('outstanding_amount') )['total'] or 0 # Recent sales (last 10) - schema isolation provides tenant filtering recent_sales = Sale.objects.all().select_related('customer').order_by('-sale_date')[:10] # Cheque statistics - schema isolation provides tenant filtering cheque_stats = { 'total_cheques': Sale.objects.filter(payment_method='cheque').count(), 'cleared_cheques': Sale.objects.filter(payment_method='cheque', cheque_cleared=True).count(), 'pending_cheques': Sale.objects.filter(payment_method='cheque', cheque_cleared=False).count(), 'pending_amount': Sale.objects.filter( payment_method='cheque', cheque_cleared=False ).aggregate(Sum('total_amount'))['total_amount__sum'] or 0 } # Monthly target calculation (example: 100k per month) monthly_target = 100000 # You can make this configurable monthly_target_percentage = 0 if monthly_target > 0: monthly_target_percentage = ((monthly_sales['total_sales'] or 0) / monthly_target) * 100 context = { 'daily_sales': daily_sales, 'weekly_sales': weekly_sales, 'monthly_sales': monthly_sales, 'six_months_sales': list(six_months_sales), 'cheque_stats': cheque_stats, 'daily_transactions': daily_sales['transaction_count'] or 0, 'weekly_growth': round(weekly_growth, 1), 'monthly_target_percentage': round(monthly_target_percentage, 1), 'total_outstanding_credit': total_outstanding_credit, 'customers_with_debt': customers_with_debt, 'overdue_debts': overdue_debts[:5], # Show only top 5 in alert 'total_overdue_amount': total_overdue_amount, 'recent_sales': recent_sales, } return render(request, 'sales/sale_dashboard.html', context) @login_required(login_url='/auth/login/') def sales_list(request): # Schema separation provides tenant isolation - no tenant filtering needed sales = Sale.objects.all().select_related('customer', 'user').order_by('-sale_date') # Search functionality search_query = request.GET.get('search', '').strip() if search_query: sales = sales.filter( Q(id__icontains=search_query) | Q(customer__first_name__icontains=search_query) | Q(customer__last_name__icontains=search_query) | Q(customer__phone__icontains=search_query) | Q(mpesa_reference__icontains=search_query) | Q(cheque_number__icontains=search_query) | Q(user__username__icontains=search_query) ) # Filter by payment method payment_method = request.GET.get('payment_method', '') if payment_method: sales = sales.filter(payment_method=payment_method) # Filter by date range date_from = request.GET.get('date_from', '') date_to = request.GET.get('date_to', '') if date_from: try: from_date = datetime.strptime(date_from, '%Y-%m-%d').date() sales = sales.filter(sale_date__date__gte=from_date) except ValueError: pass if date_to: try: to_date = datetime.strptime(date_to, '%Y-%m-%d').date() sales = sales.filter(sale_date__date__lte=to_date) except ValueError: pass # Handle CSV export if request.GET.get('export') == 'csv': response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="sales_list.csv"' writer = csv.writer(response) writer.writerow([ 'Sale ID', 'Date', 'Customer', 'Amount', 'Payment Method', 'Status', 'Served By', 'Mpesa Reference', 'Cheque Number' ]) for sale in sales: customer_name = f"{sale.customer.first_name} {sale.customer.last_name}" if sale.customer else "Walk-in" writer.writerow([ sale.id, sale.sale_date.strftime('%Y-%m-%d %H:%M'), customer_name, sale.total_amount, sale.get_payment_method_display(), 'Complete' if sale.is_complete else 'Pending', sale.user.username, sale.mpesa_reference or '', sale.cheque_number or '' ]) return response # Pagination paginator = Paginator(sales, 25) # Show 25 sales per page page_number = request.GET.get('page') page_obj = paginator.get_page(page_number) context = { 'sales': page_obj, } return render(request, 'sales/sales_list.html', context) @login_required(login_url='/auth/login/') def sale_detail(request, sale_id): # Schema separation provides tenant isolation - no tenant filtering needed sale = get_object_or_404(Sale, id=sale_id) sale_items = SaleItem.objects.filter(sale=sale) context = { 'sale': sale, 'sale_items': sale_items, } return render(request, 'sales/sale_detail.html', context) @csrf_exempt @transaction.atomic @login_required(login_url='/auth/login/') @require_http_methods(["GET", "POST"]) def make_sale(request): if request.method == 'GET': return render(request, 'sales/make_sale.html', { 'shop': Shop.objects.first(), 'user': request.user }) try: data = json.loads(request.body) # Live Search Handlers if 'query' in data: return handle_live_search_with_tenant(request, data) # Main Sale Processing return process_sale(request, data) except json.JSONDecodeError: return JsonResponse({'status': 'error', 'message': 'Invalid JSON'}, status=400) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=500) def handle_live_search_with_tenant(request, data): # Schema separation provides tenant isolation - no tenant filtering needed query = data['query'].strip() if data.get('type') == 'customer': # Schema isolation provides tenant filtering automatically customers = Customer.objects.filter( Q(name__icontains=query) | Q(phone__icontains=query) )[:10] return JsonResponse({'customers': [ {'id': c.id, 'name': c.name, 'phone': c.phone} for c in customers ]}) elif data.get('type') == 'product': # Schema isolation provides tenant filtering automatically products = Product.objects.filter( stock_quantity__gt=0 ).filter( Q(name__icontains=query) | Q(barcode__icontains=query) )[:10] return JsonResponse({'products': [ {'id': p.id, 'name': p.name, 'price': str(p.selling_price), 'stock': p.stock_quantity} for p in products ]}) return JsonResponse({'status': 'error', 'message': 'Invalid search type'}, status=400) def process_sale(request, data): # Schema separation provides tenant isolation - no tenant filtering needed # Validate required fields if not (items := data.get('items')): return JsonResponse({'status': 'error', 'message': 'No sale items provided'}, status=400) payment_methods = data.get('payment_methods', []) if not payment_methods: return JsonResponse({'status': 'error', 'message': 'No payment methods provided'}, status=400) # Process customer - schema isolation provides tenant filtering customer = None if customer_id := data.get('customer_id'): try: customer = Customer.objects.get(id=customer_id) except Customer.DoesNotExist: return JsonResponse({'status': 'error', 'message': 'Customer not found'}, status=404) # Validate payment methods and calculate totals total_amount = sum(Decimal(str(item['price'])) * int(item['quantity']) for item in items) total_paid = sum(Decimal(str(method['amount'])) for method in payment_methods if method.get('amount', 0) > 0) # Check for credit sales without customer has_credit = False for method in payment_methods: if method['type'] == 'credit' and method.get('amount', 0) > 0: has_credit = True if not customer: return JsonResponse({'status': 'error', 'message': 'Customer required for credit sales'}, status=400) # Check customer credit limit if hasattr(customer, 'credit_limit'): current_credit = getattr(customer, 'current_credit_balance', 0) credit_amount = Decimal(str(method['amount'])) if current_credit + credit_amount > customer.credit_limit: return JsonResponse({'status': 'error', 'message': 'Customer credit limit exceeded'}, status=400) # Determine primary payment method if has_credit and total_paid < total_amount: # For partial credit sales, credit is primary primary_method = next((m for m in payment_methods if m['type'] == 'credit'), payment_methods[0]) else: # Highest amount payment is primary primary_method = max(payment_methods, key=lambda x: x.get('amount', 0)) if payment_methods else payment_methods[0] # Create sale record with primary payment method sale = create_multi_payment_sale_record(request, data, customer, primary_method, payment_methods) # Save individual payment records from .models import SalePayment for method in payment_methods: if method.get('amount', 0) > 0: payment_data = { 'sale': sale, 'payment_method': method['type'], 'amount': Decimal(str(method['amount'])) } if method['type'] == 'mpesa' and method.get('details', {}).get('mpesa_reference'): payment_data['mpesa_reference'] = method['details']['mpesa_reference'] elif method['type'] == 'cheque' and method.get('details'): payment_data['cheque_number'] = method['details'].get('cheque_number') payment_data['cheque_date'] = method['details'].get('cheque_date') # Schema isolation automatically ensures tenant separation SalePayment.objects.create(**payment_data) # Process sale items with stock validation try: process_sale_items(sale, items) except ValueError as e: # Rollback sale if stock validation fails sale.delete() return JsonResponse({'status': 'error', 'message': str(e)}, status=400) return JsonResponse({ 'status': 'success', 'sale_id': sale.id, 'total_amount': str(sale.total_amount), 'total_paid': str(total_paid), 'balance_due': str(max(total_amount - total_paid, Decimal('0'))) }) def validate_payment_method(data, payment_method, customer): if payment_method == 'cash': if Decimal(data.get('cash_given', 0)) < Decimal(data['total_amount']): return JsonResponse({ 'status': 'error', 'message': 'Insufficient cash provided' }, status=400) elif payment_method == 'mpesa': if not data.get('mpesa_reference'): return JsonResponse({ 'status': 'error', 'message': 'M-Pesa reference required' }, status=400) elif payment_method == 'cheque': if not data.get('cheque_number') or not data.get('cheque_date'): return JsonResponse({ 'status': 'error', 'message': 'Cheque number and date required' }, status=400) try: datetime.strptime(data['cheque_date'], '%Y-%m-%d') except ValueError: return JsonResponse({ 'status': 'error', 'message': 'Invalid cheque date format (YYYY-MM-DD required)' }, status=400) elif payment_method == 'credit': if not customer: return JsonResponse({ 'status': 'error', 'message': 'Customer required for credit sales' }, status=400) if not customer.can_purchase_on_credit(Decimal(data['total_amount'])): return JsonResponse({ 'status': 'error', 'message': 'Customer credit limit exceeded' }, status=400) return None def create_multi_payment_sale_record(request, data, customer, primary_method, payment_methods): # Schema separation provides tenant isolation - no tenant assignment needed total_amount = sum( Decimal(str(item['price'])) * int(item['quantity']) for item in data['items'] ) total_paid = sum(Decimal(str(method['amount'])) for method in payment_methods if method.get('amount', 0) > 0) sale_data = { 'customer': customer, 'total_amount': total_amount, 'payment_method': primary_method['type'], 'user': request.user, 'is_complete': total_paid >= total_amount } # Handle primary payment method details if primary_method['type'] == 'cash': cash_amount = sum(Decimal(str(m['amount'])) for m in payment_methods if m['type'] == 'cash' and m.get('amount', 0) > 0) sale_data.update({ 'cash_given': cash_amount, 'change_due': max(cash_amount - total_amount, Decimal('0')) }) elif primary_method['type'] == 'mpesa': mpesa_method = next((m for m in payment_methods if m['type'] == 'mpesa'), None) if mpesa_method and mpesa_method.get('details', {}).get('mpesa_reference'): sale_data['mpesa_reference'] = mpesa_method['details']['mpesa_reference'] elif primary_method['type'] == 'cheque': cheque_method = next((m for m in payment_methods if m['type'] == 'cheque'), None) if cheque_method and cheque_method.get('details'): sale_data.update({ 'cheque_number': cheque_method['details'].get('cheque_number'), 'cheque_date': cheque_method['details'].get('cheque_date') }) elif primary_method['type'] == 'credit': # For credit sales, credit_amount is what was paid NOW (deposit), not the total credit amount credit_paid_now = sum(Decimal(str(m['amount'])) for m in payment_methods if m['type'] != 'credit' and m.get('amount', 0) > 0) sale_data['credit_amount'] = credit_paid_now # Deposit amount sale_data['is_complete'] = False # Credit sales are never fully complete initially return Sale.objects.create(**sale_data) def create_sale_record(request, data, customer, payment_method): # Schema separation provides tenant isolation - no tenant assignment needed total_amount = sum( Decimal(item['price']) * int(item['quantity']) for item in data['items'] ) sale_data = { 'customer': customer, 'total_amount': total_amount, 'payment_method': payment_method, 'user': request.user, 'is_complete': payment_method != 'credit' } # Payment method specific fields if payment_method == 'cash': sale_data.update({ 'cash_given': Decimal(data['cash_given']), 'change_due': Decimal(data['cash_given']) - total_amount }) elif payment_method == 'mpesa': sale_data['mpesa_reference'] = data['mpesa_reference'] elif payment_method == 'cheque': sale_data.update({ 'cheque_number': data['cheque_number'], 'cheque_date': data['cheque_date'] }) elif payment_method == 'credit': sale_data['credit_amount'] = Decimal(data.get('credit_paid', 0)) return Sale.objects.create(**sale_data) def process_sale_items(sale, items): for item in items: product = Product.objects.select_for_update().get(id=item['product_id']) quantity = int(item['quantity']) if product.stock_quantity < quantity: raise ValueError(f'Insufficient stock for {product.name}') sale_item = SaleItem( sale=sale, product=product, quantity=quantity, selling_price=Decimal(item['price']), subtotal=Decimal(item['price']) * quantity # Schema isolation automatically ensures tenant separation ) # Skip the model's save method to avoid overriding the selling_price super(SaleItem, sale_item).save() # Manually update stock product.stock_quantity -= quantity product.save() def get_receipt_data(sale): nairobi_tz = pytz.timezone('Africa/Nairobi') return { 'sale_date': localtime(sale.sale_date, nairobi_tz), # Other context variables } @login_required(login_url='/auth/login/') def receipt_view(request, sale_id): sale = get_object_or_404(Sale, id=sale_id) sale_items = sale.items.all() # CORRECTED CALCULATIONS if sale.payment_method == 'credit': credit_paid = sale.credit_amount # What customer ACTUALLY paid now balance_due = sale.total_amount - sale.credit_amount # What's left else: credit_paid = None balance_due = 0 context = { 'shop': Shop.objects.first(), 'sale': sale, 'items': sale_items, 'total_amount': sale.total_amount, 'payment_method': sale.payment_method, 'credit_paid': credit_paid, # Deposit amount (e.g., 250) 'balance_due': balance_due, # Outstanding balance (e.g., 500) 'cash_given': sale.cash_given, 'change_due': sale.change_due, 'mpesa_reference': sale.mpesa_reference, 'served_by': sale.user.username, 'sale_date': sale.sale_date.strftime('%Y-%m-%d %H:%M:%S'), 'customer_name': f"{sale.customer.first_name} {sale.customer.last_name}" if sale.customer else "N/A", 'customer_phone': sale.customer.phone if sale.customer else "N/A", 'cheque_number': sale.cheque_number if sale.payment_method == 'cheque' else None, } return render(request, 'sales/receipt.html', context) @login_required(login_url='/auth/login/') def search_products(request): query = request.GET.get('q', '').strip().lower() products = Product.objects.filter( Q(name__icontains=query) | Q(barcode__icontains=query) ).values('id', 'name', 'barcode', 'selling_price', 'stock_quantity') return JsonResponse(list(products), safe=False) @login_required(login_url='/auth/login/') def search_customers(request): query = request.GET.get('q', '').strip().lower() customers = Customer.objects.filter( Q(first_name__icontains=query) | Q(last_name__icontains=query) | Q(phone__icontains=query) ).values('id', 'first_name', 'last_name', 'phone') return JsonResponse(list(customers), safe=False) @login_required(login_url='/auth/login/') @transaction.atomic def update_sale(request, sale_id): sale = get_object_or_404(Sale, id=sale_id) # Create a formset for SaleItems SaleItemFormSet = modelformset_factory(SaleItem, form=SaleItemForm, extra=0, can_delete=True) if request.method == 'POST': if request.headers.get('x-requested-with') == 'XMLHttpRequest': # Handle AJAX requests try: data = json.loads(request.body) sale.total_amount = Decimal(data.get('total_amount', sale.total_amount)) sale.payment_method = data.get('payment_method', sale.payment_method) customer_id = data.get('customer_id') if customer_id: sale.customer = get_object_or_404(Customer, id=customer_id) sale.save() return JsonResponse({'status': 'success', 'message': 'Sale updated successfully!'}) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) else: # Handle full-page form submission sale_form = SaleForm(request.POST, instance=sale) sale_item_formset = SaleItemFormSet(request.POST, queryset=SaleItem.objects.filter(sale=sale)) if sale_form.is_valid() and sale_item_formset.is_valid(): sale_form.save() sale_items = sale_item_formset.save(commit=False) for item in sale_items: original_item = SaleItem.objects.get(id=item.id) stock_adjustment = original_item.quantity - item.quantity item.batch.product.stock_quantity += stock_adjustment item.batch.product.save() item.save() return redirect('sales_list') else: return JsonResponse({'status': 'error', 'message': 'Invalid form data'}) else: # Handle GET request for editing if request.headers.get('x-requested-with') == 'XMLHttpRequest': # Handle AJAX modal edit data = { 'total_amount': str(sale.total_amount), 'payment_method': sale.payment_method, 'customer_id': sale.customer.id if sale.customer else None } return JsonResponse(data) else: # Handle full-page edit form sale_form = SaleForm(instance=sale) sale_item_formset = SaleItemFormSet(queryset=SaleItem.objects.filter(sale=sale)) context = { 'sale': sale, 'sale_form': sale_form, 'sale_items': sale_item_formset, } return render(request, 'sales/update_sale.html', context) @login_required(login_url='/auth/login/') @transaction.atomic def delete_sale(request, sale_id): sale = get_object_or_404(Sale, id=sale_id) if request.method == 'POST': try: # Return sold items back to stock before deleting the sale for item in sale.items.all(): product = item.product product.stock_quantity += item.quantity # Add the sold quantity back to the product's stock product.save() logger.info(f"Restored {item.quantity} units of {product.name} to stock") # Handle credit sales - update customer credit balance if needed if sale.payment_method == 'credit' and sale.customer and sale.credit_amount: # If customer has credit balance tracking, adjust it if hasattr(sale.customer, 'current_credit_balance'): sale.customer.current_credit_balance = max( sale.customer.current_credit_balance - sale.credit_amount, Decimal('0') ) sale.customer.save() # Delete associated payment records first sale.payments.all().delete() # Delete the sale and its items sale_number = sale.id sale.delete() logger.info(f"Sale #{sale_number} deleted successfully") if request.headers.get('x-requested-with') == 'XMLHttpRequest': # Check for AJAX request return JsonResponse({'success': True, 'message': f'Sale #{sale_number} deleted successfully'}) else: messages.success(request, 'Sale deleted successfully and stock quantities restored.') return redirect('sales:sales_list') except Exception as e: logger.error(f"Error deleting sale #{sale_id}: {str(e)}") if request.headers.get('x-requested-with') == 'XMLHttpRequest': return JsonResponse({'success': False, 'error': str(e)}, status=500) else: messages.error(request, f'Error deleting sale: {str(e)}') return redirect('sales:sales_list') return JsonResponse({'error': 'Invalid request method.'}, status=405)