from django.shortcuts import render, redirect, get_object_or_404 from django.contrib.auth.decorators import login_required from django.contrib import messages from django.http import JsonResponse, HttpResponse from django.core.paginator import Paginator from django.db.models import Q, Sum, F from django.db import transaction, models from decimal import Decimal from .models import Customer from .forms import CustomerForm from sales.models import Sale, SaleItem import csv import io @login_required(login_url='/auth/login/') def customer_list(request): query = request.GET.get('q') customers = Customer.objects.all().order_by('-date_added') if query: customers = customers.filter( Q(first_name__icontains=query) | Q(last_name__icontains=query) | Q(phone__icontains=query) | Q(customer_id__icontains=query) ) # Calculate outstanding balance for each customer customers_with_balance = [] for customer in customers: # Calculate outstanding balance (amount owed) credit_sales = Sale.objects.filter( customer=customer, payment_method='credit' ).aggregate( total_amount=Sum('total_amount'), total_paid=Sum('credit_amount') ) total_amount = credit_sales['total_amount'] or Decimal('0.00') total_paid = credit_sales['total_paid'] or Decimal('0.00') outstanding_balance = total_amount - total_paid # Add outstanding balance to customer object customer.outstanding_balance = outstanding_balance customers_with_balance.append(customer) return render(request, 'customers/customer_list.html', { 'customers': customers_with_balance, 'query': query, }) @login_required(login_url='/auth/login/') def customer_add(request): if request.method == 'POST': form = CustomerForm(request.POST) if form.is_valid(): form.save() messages.success(request, 'Customer added successfully!') return redirect('customer_list') else: form = CustomerForm() return render(request, 'customers/add_customer.html', {'form': form}) @login_required(login_url='/auth/login/') def customer_detail(request, id): customer = get_object_or_404(Customer, customer_id=id) recent_sales = Sale.objects.filter(customer=customer).order_by('-sale_date')[:5] # Calculate correct credit balance (amount owed) total_credit_sales = Sale.objects.filter( customer=customer, payment_method='credit' ).aggregate( total_amount=Sum('total_amount'), total_paid=Sum('credit_amount') ) total_amount = total_credit_sales['total_amount'] or Decimal('0.00') total_paid = total_credit_sales['total_paid'] or Decimal('0.00') current_balance_owed = total_amount - total_paid # Calculate available credit (credit limit minus amount owed) available_credit = customer.credit_limit - current_balance_owed if available_credit < 0: available_credit = Decimal('0.00') context = { 'customer': customer, 'recent_sales': recent_sales, 'current_balance_owed': current_balance_owed, 'available_credit': available_credit, } return render(request, 'customers/customer_detail.html', context) @login_required(login_url='/auth/login/') def customer_edit(request, id): customer = get_object_or_404(Customer, customer_id=id) if request.method == 'POST': form = CustomerForm(request.POST, instance=customer) if form.is_valid(): form.save() messages.success(request, 'Customer updated successfully!') return redirect('customer_detail', id=customer.customer_id) else: form = CustomerForm(instance=customer) return render(request, 'customers/customer_edit.html', { 'form': form, 'customer': customer, }) @login_required(login_url='/auth/login/') def customer_delete(request, id): customer = get_object_or_404(Customer, customer_id=id) if request.method == 'POST': customer.delete() messages.success(request, 'Customer deleted successfully!') return redirect('customer_list') return render(request, 'customers/customer_detail.html', {'customer': customer}) @login_required(login_url='/auth/login/') @transaction.atomic def make_credit_payment(request, id): customer = get_object_or_404(Customer, customer_id=id) # Calculate current balance owed total_credit_sales = Sale.objects.filter( customer=customer, payment_method='credit' ).aggregate( total_amount=Sum('total_amount'), total_paid=Sum('credit_amount') ) total_amount = total_credit_sales['total_amount'] or Decimal('0.00') total_paid = total_credit_sales['total_paid'] or Decimal('0.00') current_balance_owed = total_amount - total_paid if request.method == 'POST': amount = Decimal(request.POST.get('amount', '0')) payment_method = request.POST.get('payment_method', '') notes = request.POST.get('notes', '') cheque_number = request.POST.get('cheque_number', '') cheque_date = request.POST.get('cheque_date', '') mpesa_reference = request.POST.get('mpesa_reference', '') if amount <= 0: messages.error(request, 'Payment amount must be greater than zero.') return redirect('customer_detail', id=customer.customer_id) if amount > current_balance_owed: messages.error(request, 'Payment amount cannot exceed the outstanding balance.') return redirect('customer_detail', id=customer.customer_id) # Validate cheque fields if payment method is cheque if payment_method == 'cheque': if not cheque_number: messages.error(request, 'Cheque number is required for cheque payments.') return redirect('customer_detail', id=customer.customer_id) if not cheque_date: messages.error(request, 'Cheque date is required for cheque payments.') return redirect('customer_detail', id=customer.customer_id) # Check for duplicate cheque number for this customer existing_cheque = Sale.objects.filter( customer=customer, cheque_number=cheque_number, payment_method='cheque' ).exists() if existing_cheque: messages.error(request, f'Cheque number {cheque_number} already exists for this customer.') return redirect('customer_detail', id=customer.customer_id) # Validate M-Pesa reference if payment method is mpesa if payment_method == 'mpesa' and not mpesa_reference: messages.error(request, 'M-Pesa reference is required for M-Pesa payments.') return redirect('customer_detail', id=customer.customer_id) try: # Create a payment record by updating existing credit sales # Find the oldest unpaid or partially paid credit sale unpaid_sales = Sale.objects.filter( customer=customer, payment_method='credit' ).annotate( remaining=models.F('total_amount') - models.F('credit_amount') ).filter(remaining__gt=0).order_by('sale_date') remaining_payment = amount for sale in unpaid_sales: if remaining_payment <= 0: break current_remaining = sale.total_amount - (sale.credit_amount or Decimal('0.00')) payment_for_this_sale = min(remaining_payment, current_remaining) # Update the sale's credit_amount sale.credit_amount = (sale.credit_amount or Decimal('0.00')) + payment_for_this_sale sale.save() remaining_payment -= payment_for_this_sale payment_method_display = dict([ ('cash', 'Cash'), ('mpesa', 'M-Pesa'), ('bank', 'Bank Transfer'), ('cheque', 'Cheque'), ]).get(payment_method, payment_method) success_message = f'Payment of Ksh {amount} recorded successfully via {payment_method_display}!' # Add additional info based on payment method if payment_method == 'cheque' and cheque_number: success_message += f' (Cheque #{cheque_number})' elif payment_method == 'mpesa' and mpesa_reference: success_message += f' (Ref: {mpesa_reference})' if notes: success_message += f' Notes: {notes}' messages.success(request, success_message) except Exception as e: messages.error(request, f'Error processing payment: {str(e)}') return redirect('customer_detail', id=customer.customer_id) context = { 'customer': customer, 'current_balance_owed': current_balance_owed, } return render(request, 'customers/make_credit_payment.html', context) @login_required(login_url='/auth/login/') def invoice_detail(request, customer_id, sale_id): customer = get_object_or_404(Customer, customer_id=customer_id) sale = get_object_or_404(Sale, id=sale_id, customer=customer) context = { 'customer': customer, 'sale': sale, } return render(request, 'customers/invoice_detail.html', context) @login_required(login_url='/auth/login/') def customer_statement(request, customer_id): from datetime import datetime, timedelta from django.utils import timezone customer = get_object_or_404(Customer, customer_id=customer_id) # Get date range parameters start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') sort_order = request.GET.get('sort_order', 'desc') # Set default dates if not provided if not start_date: start_date = (timezone.now() - timedelta(days=30)).date() else: start_date = datetime.strptime(start_date, '%Y-%m-%d').date() if not end_date: end_date = timezone.now().date() else: end_date = datetime.strptime(end_date, '%Y-%m-%d').date() # Calculate opening balance (transactions before start_date) opening_balance = Decimal('0.00') opening_sales = Sale.objects.filter( customer=customer, payment_method='credit', sale_date__date__lt=start_date ).order_by('sale_date') for sale in opening_sales: opening_balance += sale.total_amount credit_paid = sale.credit_amount or Decimal('0.00') if credit_paid > 0: opening_balance -= credit_paid # Get all transactions for the specified period all_sales = Sale.objects.filter( customer=customer, sale_date__date__gte=start_date, sale_date__date__lte=end_date ).order_by('sale_date' if sort_order == 'asc' else '-sale_date') # Calculate running balance starting from opening balance running_balance = opening_balance statement_entries = [] # Initialize totals total_debits = Decimal('0.00') total_credits = Decimal('0.00') total_cash_sales = Decimal('0.00') # Sort sales by date for proper balance calculation sales_for_balance = all_sales.order_by('sale_date') for sale in sales_for_balance: payment_method_display = dict(Sale.PAYMENT_METHOD_CHOICES).get(sale.payment_method, sale.payment_method) if sale.payment_method == 'credit': # For credit sales, show the invoice amount as debit running_balance += sale.total_amount total_debits += sale.total_amount statement_entries.append({ 'date': sale.sale_date, 'description': f'Invoice #{sale.id} ({payment_method_display})', 'cash_sale': None, 'credit_sale': sale.total_amount, 'credit_payment': None, 'balance': running_balance, 'sale': sale }) # If there was a credit payment, show it as credit if sale.credit_amount and sale.credit_amount > 0: running_balance -= sale.credit_amount total_credits += sale.credit_amount statement_entries.append({ 'date': sale.sale_date, 'description': f'Payment for Invoice #{sale.id}', 'cash_sale': None, 'credit_sale': None, 'credit_payment': sale.credit_amount, 'balance': running_balance, 'sale': sale }) else: # For cash, M-Pesa, and cheque sales, show as paid transaction total_credits += sale.total_amount total_cash_sales += sale.total_amount statement_entries.append({ 'date': sale.sale_date, 'description': f'Sale #{sale.id} ({payment_method_display})', 'cash_sale': sale.total_amount, 'credit_sale': None, 'credit_payment': None, 'balance': running_balance, # Balance doesn't change for paid transactions 'sale': sale }) # Sort statement entries by date and sort_order statement_entries.sort(key=lambda x: x['date'], reverse=(sort_order == 'desc')) context = { 'customer': customer, 'statement_entries': statement_entries, 'opening_balance': opening_balance, 'final_balance': running_balance, 'start_date': start_date, 'end_date': end_date, 'sort_order': sort_order, 'total_debits': total_debits, 'total_credits': total_credits, 'total_cash_sales': total_cash_sales, } return render(request, 'customers/customer_statement.html', context) @login_required(login_url='/auth/login/') def export_customers(request): response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="customers.csv"' writer = csv.writer(response) writer.writerow(['Customer ID', 'First Name', 'Last Name', 'Phone', 'Email', 'Address', 'Credit Limit', 'Credit Balance', 'Date Added']) customers = Customer.objects.all() for customer in customers: writer.writerow([ customer.customer_id, customer.first_name, customer.last_name, customer.phone, customer.email, customer.address, customer.credit_limit, customer.credit_balance, customer.date_added.strftime('%Y-%m-%d'), ]) return response @login_required(login_url='/auth/login/') def import_customers(request): if request.method == 'POST': csv_file = request.FILES.get('csv_file') if not csv_file: messages.error(request, 'Please select a CSV file.') return redirect('import_customers') if not csv_file.name.endswith('.csv'): messages.error(request, 'Please upload a valid CSV file.') return redirect('import_customers') try: data_set = csv_file.read().decode('UTF-8') io_string = io.StringIO(data_set) next(io_string) # Skip header row imported_count = 0 for column in csv.reader(io_string, delimiter=',', quotechar='"'): if len(column) >= 4: # Ensure minimum required fields customer_data = { 'first_name': column[0], 'last_name': column[1], 'phone': column[2], 'email': column[3], 'address': column[4] if len(column) > 4 else '', 'credit_limit': Decimal(column[5]) if len(column) > 5 and column[5] else Decimal('0.00'), } # Check if customer already exists if not Customer.objects.filter(phone=customer_data['phone']).exists(): Customer.objects.create(**customer_data) imported_count += 1 messages.success(request, f'Successfully imported {imported_count} customers.') return redirect('customer_list') except Exception as e: messages.error(request, f'Error importing customers: {str(e)}') return redirect('import_customers') return render(request, 'customers/import_customers.html')