481 lines
17 KiB
Python
481 lines
17 KiB
Python
from django.shortcuts import render
|
|
from django.contrib.auth.decorators import login_required, permission_required
|
|
from django.http import HttpResponse
|
|
from django.db.models import F
|
|
from .models import ReportTemplate, GeneratedReport
|
|
from .utils import export_inventory_to_excel, export_inventory_to_pdf, export_sales_to_excel, export_manufacturing_to_excel, export_purchasing_to_excel, export_financial_to_excel
|
|
|
|
|
|
@login_required
|
|
def reports_dashboard(request):
|
|
"""Reports dashboard view"""
|
|
from apps.inventory.models import Product, Category, Inventory
|
|
from apps.sales.models import SalesOrder
|
|
from apps.purchasing.models import PurchaseOrder
|
|
from apps.manufacturing.models import ManufacturingOrder
|
|
|
|
# Get summary statistics
|
|
total_products = Product.objects.count()
|
|
active_orders = SalesOrder.objects.filter(status='processing').count()
|
|
pending_deliveries = SalesOrder.objects.filter(status='processing').count() # Simplified
|
|
low_stock_items = sum(1 for inventory in Inventory.objects.select_related('product')
|
|
if inventory.quantity <= inventory.product.reorder_level)
|
|
|
|
context = {
|
|
'module_title': 'Reports Management',
|
|
'total_products': total_products,
|
|
'active_orders': active_orders,
|
|
'pending_deliveries': pending_deliveries,
|
|
'low_stock_items': low_stock_items,
|
|
}
|
|
return render(request, 'reports/dashboard.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def inventory_report_view(request):
|
|
"""View inventory reports"""
|
|
from apps.inventory.models import Product, Category, Inventory, Warehouse
|
|
from django.db.models import Sum, F, Case, When, IntegerField
|
|
|
|
# Get filter parameters
|
|
warehouse_id = request.GET.get('warehouse')
|
|
category_id = request.GET.get('category')
|
|
status_filter = request.GET.get('status')
|
|
|
|
# Base queryset
|
|
inventory_items = Inventory.objects.select_related(
|
|
'product', 'product__category', 'product__unit_of_measure', 'warehouse'
|
|
)
|
|
|
|
# Apply filters
|
|
if warehouse_id:
|
|
inventory_items = inventory_items.filter(warehouse_id=warehouse_id)
|
|
if category_id:
|
|
inventory_items = inventory_items.filter(product__category_id=category_id)
|
|
|
|
# Add computed fields
|
|
inventory_items = inventory_items.annotate(
|
|
total_value=F('quantity') * F('product__cost_price')
|
|
)
|
|
|
|
# Apply status filter
|
|
if status_filter:
|
|
if status_filter == 'out_of_stock':
|
|
inventory_items = inventory_items.filter(quantity=0)
|
|
elif status_filter == 'low_stock':
|
|
inventory_items = inventory_items.filter(
|
|
quantity__gt=0,
|
|
quantity__lte=F('product__reorder_level')
|
|
)
|
|
elif status_filter == 'in_stock':
|
|
inventory_items = inventory_items.filter(
|
|
quantity__gt=F('product__reorder_level')
|
|
)
|
|
|
|
# Calculate summary statistics
|
|
total_products = Product.objects.count()
|
|
in_stock_count = inventory_items.filter(quantity__gt=F('product__reorder_level')).count()
|
|
low_stock_count = inventory_items.filter(
|
|
quantity__gt=0,
|
|
quantity__lte=F('product__reorder_level')
|
|
).count()
|
|
out_of_stock_count = inventory_items.filter(quantity=0).count()
|
|
|
|
# Calculate total inventory value
|
|
total_inventory_value = inventory_items.aggregate(
|
|
total=Sum('total_value')
|
|
)['total'] or 0
|
|
|
|
# Handle export requests
|
|
export_format = request.GET.get('export')
|
|
if export_format == 'excel':
|
|
return export_inventory_to_excel(inventory_items, total_inventory_value)
|
|
elif export_format == 'pdf':
|
|
return export_inventory_to_pdf(inventory_items, total_inventory_value)
|
|
|
|
context = {
|
|
'module_title': 'Inventory Report',
|
|
'inventory_items': inventory_items,
|
|
'warehouses': Warehouse.objects.filter(is_active=True),
|
|
'categories': Category.objects.all(),
|
|
'total_products': total_products,
|
|
'in_stock_count': in_stock_count,
|
|
'low_stock_count': low_stock_count,
|
|
'out_of_stock_count': out_of_stock_count,
|
|
'total_inventory_value': total_inventory_value,
|
|
}
|
|
return render(request, 'reports/inventory_report.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def sales_report_view(request):
|
|
"""View sales reports"""
|
|
from apps.sales.models import SalesOrder, Customer, SalesOrderItem
|
|
from django.db.models import Sum, Count, Avg
|
|
from django.utils import timezone
|
|
from datetime import timedelta
|
|
|
|
# Get filter parameters
|
|
date_from = request.GET.get('date_from')
|
|
date_to = request.GET.get('date_to')
|
|
customer_id = request.GET.get('customer')
|
|
status_filter = request.GET.get('status')
|
|
|
|
# Set default date range (last 30 days)
|
|
if not date_from:
|
|
date_from = (timezone.now() - timedelta(days=30)).date()
|
|
else:
|
|
date_from = timezone.datetime.strptime(date_from, '%Y-%m-%d').date()
|
|
|
|
if not date_to:
|
|
date_to = timezone.now().date()
|
|
else:
|
|
date_to = timezone.datetime.strptime(date_to, '%Y-%m-%d').date()
|
|
|
|
# Base queryset
|
|
sales_orders = SalesOrder.objects.select_related('customer').filter(
|
|
order_date__gte=date_from,
|
|
order_date__lte=date_to
|
|
)
|
|
|
|
# Apply filters
|
|
if customer_id:
|
|
sales_orders = sales_orders.filter(customer_id=customer_id)
|
|
if status_filter:
|
|
sales_orders = sales_orders.filter(status=status_filter)
|
|
|
|
# Calculate summary statistics
|
|
total_orders = sales_orders.count()
|
|
total_revenue = sales_orders.aggregate(
|
|
total=Sum('total_amount')
|
|
)['total'] or 0
|
|
avg_order_value = total_revenue / total_orders if total_orders > 0 else 0
|
|
pending_orders = sales_orders.filter(status='processing').count()
|
|
|
|
# Get top products
|
|
top_products = SalesOrderItem.objects.select_related('product').filter(
|
|
so__order_date__gte=date_from,
|
|
so__order_date__lte=date_to
|
|
).values('product__name').annotate(
|
|
total_quantity=Sum('quantity'),
|
|
total_revenue=Sum('total_price')
|
|
).order_by('-total_revenue')[:10]
|
|
|
|
# Calculate percentages
|
|
for product in top_products:
|
|
product['percentage'] = round((product['total_revenue'] / total_revenue) * 100, 1) if total_revenue > 0 else 0
|
|
|
|
# Handle export requests
|
|
export_format = request.GET.get('export')
|
|
if export_format == 'excel':
|
|
return export_sales_to_excel(sales_orders, total_revenue, avg_order_value, total_orders)
|
|
|
|
context = {
|
|
'module_title': 'Sales Report',
|
|
'sales_orders': sales_orders,
|
|
'customers': Customer.objects.all(),
|
|
'date_from': date_from,
|
|
'date_to': date_to,
|
|
'total_orders': total_orders,
|
|
'total_revenue': total_revenue,
|
|
'avg_order_value': avg_order_value,
|
|
'pending_orders': pending_orders,
|
|
'top_products': top_products,
|
|
}
|
|
return render(request, 'reports/sales_report.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def purchasing_report_view(request):
|
|
"""View purchasing reports"""
|
|
from apps.purchasing.models import PurchaseOrder, Supplier, PurchaseOrderItem
|
|
from django.db.models import Sum, Count, Avg
|
|
from django.utils import timezone
|
|
from datetime import timedelta
|
|
|
|
# Get filter parameters
|
|
date_from = request.GET.get('date_from')
|
|
date_to = request.GET.get('date_to')
|
|
supplier_id = request.GET.get('supplier')
|
|
status_filter = request.GET.get('status')
|
|
|
|
# Set default date range (last 30 days)
|
|
if not date_from:
|
|
date_from = (timezone.now() - timedelta(days=30)).date()
|
|
else:
|
|
date_from = timezone.datetime.strptime(date_from, '%Y-%m-%d').date()
|
|
|
|
if not date_to:
|
|
date_to = timezone.now().date()
|
|
else:
|
|
date_to = timezone.datetime.strptime(date_to, '%Y-%m-%d').date()
|
|
|
|
# Base queryset
|
|
purchase_orders = PurchaseOrder.objects.select_related('supplier').filter(
|
|
order_date__gte=date_from,
|
|
order_date__lte=date_to
|
|
)
|
|
|
|
# Apply filters
|
|
if supplier_id:
|
|
purchase_orders = purchase_orders.filter(supplier_id=supplier_id)
|
|
if status_filter:
|
|
purchase_orders = purchase_orders.filter(status=status_filter)
|
|
|
|
# Calculate summary statistics
|
|
total_orders = purchase_orders.count()
|
|
total_cost = purchase_orders.aggregate(
|
|
total=Sum('total_amount')
|
|
)['total'] or 0
|
|
avg_order_value = total_cost / total_orders if total_orders > 0 else 0
|
|
pending_orders = purchase_orders.filter(status='ordered').count()
|
|
|
|
# Get top products
|
|
top_products = PurchaseOrderItem.objects.select_related('product').filter(
|
|
po__order_date__gte=date_from,
|
|
po__order_date__lte=date_to
|
|
).values('product__name').annotate(
|
|
total_quantity=Sum('quantity'),
|
|
total_cost=Sum('total_price')
|
|
).order_by('-total_cost')[:10]
|
|
|
|
# Calculate percentages
|
|
for product in top_products:
|
|
product['percentage'] = round((product['total_cost'] / total_cost) * 100, 1) if total_cost > 0 else 0
|
|
|
|
# Handle export requests
|
|
export_format = request.GET.get('export')
|
|
if export_format == 'excel':
|
|
return export_purchasing_to_excel(purchase_orders, total_cost, avg_order_value, total_orders)
|
|
|
|
context = {
|
|
'module_title': 'Purchasing Report',
|
|
'purchase_orders': purchase_orders,
|
|
'suppliers': Supplier.objects.all(),
|
|
'date_from': date_from,
|
|
'date_to': date_to,
|
|
'total_orders': total_orders,
|
|
'total_cost': total_cost,
|
|
'avg_order_value': avg_order_value,
|
|
'pending_orders': pending_orders,
|
|
'top_products': top_products,
|
|
}
|
|
return render(request, 'reports/purchasing_report.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def manufacturing_report_view(request):
|
|
"""View manufacturing reports"""
|
|
from apps.manufacturing.models import ManufacturingOrder, BillOfMaterial, BOMItem
|
|
from django.db.models import Count, Sum
|
|
from django.utils import timezone
|
|
from datetime import timedelta
|
|
|
|
# Get filter parameters
|
|
date_from = request.GET.get('date_from')
|
|
date_to = request.GET.get('date_to')
|
|
status_filter = request.GET.get('status')
|
|
bom_filter = request.GET.get('bom')
|
|
|
|
# Set default date range (last 30 days)
|
|
if not date_from:
|
|
date_from = (timezone.now() - timedelta(days=30)).date()
|
|
else:
|
|
date_from = timezone.datetime.strptime(date_from, '%Y-%m-%d').date()
|
|
|
|
if not date_to:
|
|
date_to = timezone.now().date()
|
|
else:
|
|
date_to = timezone.datetime.strptime(date_to, '%Y-%m-%d').date()
|
|
|
|
# Base queryset
|
|
manufacturing_orders = ManufacturingOrder.objects.select_related('bom__product').filter(
|
|
created_at__date__gte=date_from,
|
|
created_at__date__lte=date_to
|
|
)
|
|
|
|
# Apply filters
|
|
if status_filter:
|
|
manufacturing_orders = manufacturing_orders.filter(status=status_filter)
|
|
if bom_filter:
|
|
manufacturing_orders = manufacturing_orders.filter(bom_id=bom_filter)
|
|
|
|
# Calculate summary statistics
|
|
total_orders = manufacturing_orders.count()
|
|
completed_orders = manufacturing_orders.filter(status='completed').count()
|
|
in_progress_orders = manufacturing_orders.filter(status='in_progress').count()
|
|
|
|
# Get all BOMs for filter dropdown
|
|
boms = BillOfMaterial.objects.filter(is_active=True).select_related('product')
|
|
|
|
# BOM Analysis
|
|
total_boms = boms.count()
|
|
|
|
bom_analysis = BillOfMaterial.objects.select_related('product').annotate(
|
|
components_count=Count('items'),
|
|
mo_count=Count('manufacturingorder'),
|
|
calculated_total_cost=Sum(F('items__quantity') * F('items__component__cost_price'))
|
|
)
|
|
|
|
# Handle export requests
|
|
export_format = request.GET.get('export')
|
|
if export_format == 'excel':
|
|
return export_manufacturing_to_excel(manufacturing_orders, total_orders, completed_orders, total_boms)
|
|
|
|
context = {
|
|
'module_title': 'Manufacturing Report',
|
|
'manufacturing_orders': manufacturing_orders,
|
|
'boms': boms,
|
|
'date_from': date_from,
|
|
'date_to': date_to,
|
|
'total_orders': total_orders,
|
|
'completed_orders': completed_orders,
|
|
'in_progress_orders': in_progress_orders,
|
|
'total_boms': total_boms,
|
|
'bom_analysis': bom_analysis,
|
|
}
|
|
return render(request, 'reports/manufacturing_report.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def financial_report_view(request):
|
|
"""View financial reports"""
|
|
from apps.sales.models import SalesOrder
|
|
from apps.purchasing.models import PurchaseOrder
|
|
from apps.manufacturing.models import ManufacturingOrder
|
|
from apps.inventory.models import Inventory
|
|
from django.db.models import Sum, Count, F
|
|
from django.utils import timezone
|
|
from datetime import timedelta
|
|
|
|
# Get filter parameters
|
|
date_from = request.GET.get('date_from')
|
|
date_to = request.GET.get('date_to')
|
|
period = request.GET.get('period', '30') # Default 30 days
|
|
|
|
# Set default date range
|
|
if not date_from:
|
|
date_from = (timezone.now() - timedelta(days=int(period))).date()
|
|
else:
|
|
date_from = timezone.datetime.strptime(date_from, '%Y-%m-%d').date()
|
|
|
|
if not date_to:
|
|
date_to = timezone.now().date()
|
|
else:
|
|
date_to = timezone.datetime.strptime(date_to, '%Y-%m-%d').date()
|
|
|
|
# Calculate revenue metrics
|
|
sales_orders = SalesOrder.objects.filter(
|
|
order_date__gte=date_from,
|
|
order_date__lte=date_to
|
|
)
|
|
|
|
total_revenue = sales_orders.aggregate(
|
|
total=Sum('total_amount')
|
|
)['total'] or 0
|
|
|
|
total_orders = sales_orders.count()
|
|
|
|
# Calculate cost metrics
|
|
purchase_orders = PurchaseOrder.objects.filter(
|
|
order_date__gte=date_from,
|
|
order_date__lte=date_to
|
|
)
|
|
|
|
total_purchase_cost = purchase_orders.aggregate(
|
|
total=Sum('total_amount')
|
|
)['total'] or 0
|
|
|
|
# Manufacturing costs
|
|
manufacturing_orders = ManufacturingOrder.objects.filter(
|
|
created_at__date__gte=date_from,
|
|
created_at__date__lte=date_to
|
|
)
|
|
|
|
total_manufacturing_cost = sum(
|
|
order.total_cost for order in manufacturing_orders
|
|
if hasattr(order, 'total_cost') and order.total_cost is not None
|
|
)
|
|
|
|
# Inventory value
|
|
total_inventory_value = Inventory.objects.aggregate(
|
|
total=Sum(F('quantity') * F('product__cost_price'))
|
|
)['total'] or 0
|
|
|
|
# Calculate financial metrics
|
|
gross_profit = total_revenue - total_purchase_cost - total_manufacturing_cost
|
|
profit_margin = (gross_profit / total_revenue * 100) if total_revenue > 0 else 0
|
|
total_costs = total_purchase_cost + total_manufacturing_cost
|
|
|
|
# Monthly trends (last 12 months)
|
|
monthly_trends = []
|
|
for i in range(12):
|
|
month_start = timezone.now() - timedelta(days=30 * (i + 1))
|
|
month_end = timezone.now() - timedelta(days=30 * i)
|
|
|
|
monthly_revenue = SalesOrder.objects.filter(
|
|
order_date__gte=month_start.date(),
|
|
order_date__lt=month_end.date()
|
|
).aggregate(total=Sum('total_amount'))['total'] or 0
|
|
|
|
monthly_costs = PurchaseOrder.objects.filter(
|
|
order_date__gte=month_start.date(),
|
|
order_date__lt=month_end.date()
|
|
).aggregate(total=Sum('total_amount'))['total'] or 0
|
|
|
|
monthly_trends.append({
|
|
'period': month_start.strftime('%b %Y'),
|
|
'revenue': monthly_revenue,
|
|
'costs': monthly_costs,
|
|
'profit': monthly_revenue - monthly_costs
|
|
})
|
|
|
|
monthly_trends.reverse() # Show oldest to newest
|
|
|
|
context = {
|
|
'module_title': 'Financial Report',
|
|
'date_from': date_from,
|
|
'date_to': date_to,
|
|
'period': period,
|
|
'total_revenue': total_revenue,
|
|
'total_orders': total_orders,
|
|
'total_purchase_cost': total_purchase_cost,
|
|
'total_manufacturing_cost': total_manufacturing_cost,
|
|
'total_inventory_value': total_inventory_value,
|
|
'total_costs': total_costs,
|
|
'gross_profit': gross_profit,
|
|
'profit_margin': profit_margin,
|
|
'monthly_trends': monthly_trends,
|
|
}
|
|
|
|
# Handle export requests
|
|
export_format = request.GET.get('export')
|
|
if export_format == 'excel':
|
|
return export_financial_to_excel(
|
|
total_revenue, total_purchase_cost, total_manufacturing_cost,
|
|
total_inventory_value, gross_profit, profit_margin, monthly_trends
|
|
)
|
|
|
|
return render(request, 'reports/financial_report.html', context)
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def export_to_excel_view(request, report_id):
|
|
"""Export report to Excel"""
|
|
# In a real app, this would contain Excel export logic
|
|
return HttpResponse("Export to Excel page")
|
|
|
|
|
|
@login_required
|
|
@permission_required('reports.view_report', raise_exception=True)
|
|
def export_to_pdf_view(request, report_id):
|
|
"""Export report to PDF"""
|
|
# In a real app, this would contain PDF export logic
|
|
return HttpResponse("Export to PDF page")
|