549 lines
21 KiB
Python
549 lines
21 KiB
Python
import pandas as pd
|
|
from io import BytesIO
|
|
from django.http import HttpResponse
|
|
from reportlab.pdfgen import canvas
|
|
from reportlab.lib.pagesizes import letter, A4
|
|
from reportlab.lib import colors
|
|
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
|
|
from reportlab.lib.styles import getSampleStyleSheet
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
|
|
from decimal import Decimal
|
|
import locale
|
|
|
|
|
|
def format_currency(amount):
|
|
"""Format amount as Indonesian Rupiah"""
|
|
if amount is None:
|
|
return "Rp 0,00"
|
|
|
|
try:
|
|
# Convert to Decimal for precise handling
|
|
if not isinstance(amount, Decimal):
|
|
amount = Decimal(str(amount))
|
|
|
|
# Format with Indonesian decimal separator (comma)
|
|
formatted = f"{amount:,.2f}"
|
|
|
|
# Convert to Indonesian format: comma to dot, dot to comma
|
|
formatted = formatted.replace(',', 'temp_comma').replace('.', ',').replace('temp_comma', '.')
|
|
|
|
return f"Rp {formatted}"
|
|
except (ValueError, TypeError):
|
|
return "Rp 0,00"
|
|
|
|
|
|
def export_inventory_to_excel(inventory_items, total_inventory_value):
|
|
"""Export inventory data to Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Inventory Report"
|
|
|
|
# Header
|
|
ws['A1'] = "INVENTORY REPORT"
|
|
ws['A1'].font = Font(bold=True, size=14)
|
|
ws.merge_cells('A1:I1')
|
|
|
|
# Column headers
|
|
headers = [
|
|
'Product Code', 'Product Name', 'Category', 'Warehouse',
|
|
'Quantity', 'Unit', 'Unit Cost', 'Total Value', 'Status'
|
|
]
|
|
|
|
for col_num, header in enumerate(headers, 1):
|
|
cell = ws.cell(row=3, column=col_num)
|
|
cell.value = header
|
|
cell.font = Font(bold=True)
|
|
cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Data rows
|
|
for row_num, item in enumerate(inventory_items, 4):
|
|
ws.cell(row=row_num, column=1).value = item.product.code
|
|
ws.cell(row=row_num, column=2).value = item.product.name
|
|
ws.cell(row=row_num, column=3).value = item.product.category.name
|
|
ws.cell(row=row_num, column=4).value = item.warehouse.name
|
|
ws.cell(row=row_num, column=5).value = float(item.quantity)
|
|
ws.cell(row=row_num, column=6).value = item.product.unit_of_measure.abbreviation
|
|
ws.cell(row=row_num, column=7).value = float(item.product.cost_price)
|
|
ws.cell(row=row_num, column=8).value = float(item.total_value)
|
|
ws.cell(row=row_num, column=9).value = 'In Stock' if item.quantity > item.product.reorder_level else 'Low Stock' if item.quantity > 0 else 'Out of Stock'
|
|
|
|
# Add borders to data cells
|
|
for col_num in range(1, 10):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Total row
|
|
total_row = len(inventory_items) + 5
|
|
ws.cell(row=total_row, column=7).value = "TOTAL VALUE:"
|
|
ws.cell(row=total_row, column=7).font = Font(bold=True)
|
|
ws.cell(row=total_row, column=8).value = float(total_inventory_value)
|
|
ws.cell(row=total_row, column=8).font = Font(bold=True)
|
|
|
|
# Auto-adjust column widths
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_cells = [cell for cell in column]
|
|
for cell in column_cells:
|
|
if len(str(cell.value)) > max_length:
|
|
max_length = len(str(cell.value))
|
|
adjusted_width = (max_length + 2)
|
|
# Get the column letter from the first cell that is not a merged cell
|
|
column_letter = None
|
|
for cell in column_cells:
|
|
if hasattr(cell, 'column_letter'):
|
|
column_letter = cell.column_letter
|
|
break
|
|
if column_letter:
|
|
ws.column_dimensions[column_letter].width = min(adjusted_width, 50)
|
|
|
|
# Create response
|
|
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
response['Content-Disposition'] = 'attachment; filename=inventory_report.xlsx'
|
|
|
|
wb.save(response)
|
|
return response
|
|
|
|
|
|
def export_inventory_to_pdf(inventory_items, total_inventory_value):
|
|
"""Export inventory data to PDF"""
|
|
buffer = BytesIO()
|
|
doc = SimpleDocTemplate(buffer, pagesize=A4)
|
|
elements = []
|
|
|
|
styles = getSampleStyleSheet()
|
|
|
|
# Title
|
|
title_style = styles['Heading1']
|
|
title = Paragraph("INVENTORY REPORT", title_style)
|
|
elements.append(title)
|
|
elements.append(Spacer(1, 20))
|
|
|
|
# Table data
|
|
data = [['Product Code', 'Product Name', 'Category', 'Warehouse',
|
|
'Quantity', 'Unit', 'Unit Cost', 'Total Value', 'Status']]
|
|
|
|
for item in inventory_items:
|
|
data.append([
|
|
item.product.code,
|
|
item.product.name,
|
|
item.product.category.name,
|
|
item.warehouse.name,
|
|
str(item.quantity),
|
|
item.product.unit_of_measure.abbreviation,
|
|
format_currency(item.product.cost_price),
|
|
format_currency(item.total_value),
|
|
'In Stock' if item.quantity > item.product.reorder_level else 'Low Stock' if item.quantity > 0 else 'Out of Stock'
|
|
])
|
|
|
|
# Add total row
|
|
data.append(['', '', '', '', '', '', 'TOTAL VALUE:', format_currency(total_inventory_value), ''])
|
|
|
|
# Create table
|
|
table = Table(data)
|
|
table.setStyle(TableStyle([
|
|
('BACKGROUND', (0, 0), (-1, 0), colors.grey),
|
|
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
|
|
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
|
|
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
|
|
('FONTSIZE', (0, 0), (-1, 0), 12),
|
|
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
|
|
('BACKGROUND', (0, -1), (-1, -1), colors.lightgrey),
|
|
('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
|
|
('GRID', (0, 0), (-1, -1), 1, colors.black)
|
|
]))
|
|
|
|
elements.append(table)
|
|
|
|
doc.build(elements)
|
|
|
|
buffer.seek(0)
|
|
response = HttpResponse(content_type='application/pdf')
|
|
response['Content-Disposition'] = 'attachment; filename=inventory_report.pdf'
|
|
response.write(buffer.getvalue())
|
|
buffer.close()
|
|
|
|
return response
|
|
|
|
|
|
def export_sales_to_excel(sales_orders, total_revenue, avg_order_value, total_orders):
|
|
"""Export sales data to Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Sales Report"
|
|
|
|
# Header
|
|
ws['A1'] = "SALES REPORT"
|
|
ws['A1'].font = Font(bold=True, size=14)
|
|
ws.merge_cells('A1:H1')
|
|
|
|
# Column headers
|
|
headers = [
|
|
'Order Number', 'Customer', 'Order Date', 'Expected Delivery',
|
|
'Status', 'Items', 'Total Amount'
|
|
]
|
|
|
|
for col_num, header in enumerate(headers, 1):
|
|
cell = ws.cell(row=3, column=col_num)
|
|
cell.value = header
|
|
cell.font = Font(bold=True)
|
|
cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Data rows
|
|
for row_num, order in enumerate(sales_orders, 4):
|
|
ws.cell(row=row_num, column=1).value = order.so_number
|
|
ws.cell(row=row_num, column=2).value = order.customer.name
|
|
ws.cell(row=row_num, column=3).value = str(order.order_date)
|
|
ws.cell(row=row_num, column=4).value = str(order.expected_delivery_date) if order.expected_delivery_date else "Not set"
|
|
ws.cell(row=row_num, column=5).value = order.get_status_display()
|
|
ws.cell(row=row_num, column=6).value = order.items.count()
|
|
ws.cell(row=row_num, column=7).value = float(order.total_amount)
|
|
|
|
# Add borders to data cells
|
|
for col_num in range(1, 8):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Summary section
|
|
summary_row = len(sales_orders) + 6
|
|
ws.cell(row=summary_row, column=5).value = "TOTAL ORDERS:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = total_orders
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=5).value = "TOTAL REVENUE:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = float(total_revenue)
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=5).value = "AVG ORDER VALUE:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = float(avg_order_value)
|
|
|
|
# Auto-adjust column widths
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_cells = [cell for cell in column]
|
|
for cell in column_cells:
|
|
if len(str(cell.value)) > max_length:
|
|
max_length = len(str(cell.value))
|
|
adjusted_width = (max_length + 2)
|
|
# Get the column letter from the first cell that is not a merged cell
|
|
column_letter = None
|
|
for cell in column_cells:
|
|
if hasattr(cell, 'column_letter'):
|
|
column_letter = cell.column_letter
|
|
break
|
|
if column_letter:
|
|
ws.column_dimensions[column_letter].width = min(adjusted_width, 50)
|
|
|
|
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
response['Content-Disposition'] = 'attachment; filename=sales_report.xlsx'
|
|
|
|
wb.save(response)
|
|
return response
|
|
|
|
|
|
def export_manufacturing_to_excel(manufacturing_orders, total_orders, completed_orders, total_boms):
|
|
"""Export manufacturing data to Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Manufacturing Report"
|
|
|
|
# Header
|
|
ws['A1'] = "MANUFACTURING REPORT"
|
|
ws['A1'].font = Font(bold=True, size=14)
|
|
ws.merge_cells('A1:H1')
|
|
|
|
# Column headers
|
|
headers = [
|
|
'MO Number', 'BOM', 'Product', 'Quantity', 'Status',
|
|
'Start Date', 'End Date', 'Total Cost'
|
|
]
|
|
|
|
for col_num, header in enumerate(headers, 1):
|
|
cell = ws.cell(row=3, column=col_num)
|
|
cell.value = header
|
|
cell.font = Font(bold=True)
|
|
cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Data rows
|
|
for row_num, order in enumerate(manufacturing_orders, 4):
|
|
ws.cell(row=row_num, column=1).value = order.mo_number
|
|
ws.cell(row=row_num, column=2).value = order.bom.bom_code
|
|
ws.cell(row=row_num, column=3).value = order.bom.product.name
|
|
ws.cell(row=row_num, column=4).value = float(order.quantity_to_produce)
|
|
ws.cell(row=row_num, column=5).value = order.get_status_display()
|
|
ws.cell(row=row_num, column=6).value = str(order.actual_start_date) if order.actual_start_date else "Not started"
|
|
ws.cell(row=row_num, column=7).value = str(order.actual_end_date) if order.actual_end_date else "Not completed"
|
|
ws.cell(row=row_num, column=8).value = float(order.total_cost) if order.total_cost else 0
|
|
|
|
# Add borders to data cells
|
|
for col_num in range(1, 9):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Summary section
|
|
summary_row = len(manufacturing_orders) + 6
|
|
ws.cell(row=summary_row, column=6).value = "TOTAL ORDERS:"
|
|
ws.cell(row=summary_row, column=6).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=7).value = total_orders
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=6).value = "COMPLETED:"
|
|
ws.cell(row=summary_row, column=6).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=7).value = completed_orders
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=6).value = "TOTAL BOMS:"
|
|
ws.cell(row=summary_row, column=6).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=7).value = total_boms
|
|
|
|
# Auto-adjust column widths
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_cells = [cell for cell in column]
|
|
for cell in column_cells:
|
|
if len(str(cell.value)) > max_length:
|
|
max_length = len(str(cell.value))
|
|
adjusted_width = (max_length + 2)
|
|
# Get the column letter from the first cell that is not a merged cell
|
|
column_letter = None
|
|
for cell in column_cells:
|
|
if hasattr(cell, 'column_letter'):
|
|
column_letter = cell.column_letter
|
|
break
|
|
if column_letter:
|
|
ws.column_dimensions[column_letter].width = min(adjusted_width, 50)
|
|
|
|
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
response['Content-Disposition'] = 'attachment; filename=manufacturing_report.xlsx'
|
|
|
|
wb.save(response)
|
|
return response
|
|
|
|
|
|
def export_purchasing_to_excel(purchase_orders, total_cost, avg_order_value, total_orders):
|
|
"""Export purchasing data to Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Purchasing Report"
|
|
|
|
# Header
|
|
ws['A1'] = "PURCHASING REPORT"
|
|
ws['A1'].font = Font(bold=True, size=14)
|
|
ws.merge_cells('A1:H1')
|
|
|
|
# Column headers
|
|
headers = [
|
|
'PO Number', 'Supplier', 'Order Date', 'Expected Delivery',
|
|
'Status', 'Items', 'Total Amount'
|
|
]
|
|
|
|
for col_num, header in enumerate(headers, 1):
|
|
cell = ws.cell(row=3, column=col_num)
|
|
cell.value = header
|
|
cell.font = Font(bold=True)
|
|
cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Data rows
|
|
for row_num, order in enumerate(purchase_orders, 4):
|
|
ws.cell(row=row_num, column=1).value = order.po_number
|
|
ws.cell(row=row_num, column=2).value = order.supplier.name
|
|
ws.cell(row=row_num, column=3).value = str(order.order_date)
|
|
ws.cell(row=row_num, column=4).value = str(order.expected_delivery_date) if order.expected_delivery_date else "Not set"
|
|
ws.cell(row=row_num, column=5).value = order.get_status_display()
|
|
ws.cell(row=row_num, column=6).value = order.items.count()
|
|
ws.cell(row=row_num, column=7).value = float(order.total_amount)
|
|
|
|
# Add borders to data cells
|
|
for col_num in range(1, 8):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Summary section
|
|
summary_row = len(purchase_orders) + 6
|
|
ws.cell(row=summary_row, column=5).value = "TOTAL ORDERS:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = total_orders
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=5).value = "TOTAL COST:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = float(total_cost)
|
|
|
|
summary_row += 1
|
|
ws.cell(row=summary_row, column=5).value = "AVG ORDER VALUE:"
|
|
ws.cell(row=summary_row, column=5).font = Font(bold=True)
|
|
ws.cell(row=summary_row, column=6).value = float(avg_order_value)
|
|
|
|
# Auto-adjust column widths
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_cells = [cell for cell in column]
|
|
for cell in column_cells:
|
|
if len(str(cell.value)) > max_length:
|
|
max_length = len(str(cell.value))
|
|
adjusted_width = (max_length + 2)
|
|
# Get the column letter from the first cell that is not a merged cell
|
|
column_letter = None
|
|
for cell in column_cells:
|
|
if hasattr(cell, 'column_letter'):
|
|
column_letter = cell.column_letter
|
|
break
|
|
if column_letter:
|
|
ws.column_dimensions[column_letter].width = min(adjusted_width, 50)
|
|
|
|
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
response['Content-Disposition'] = 'attachment; filename=purchasing_report.xlsx'
|
|
|
|
wb.save(response)
|
|
return response
|
|
|
|
|
|
def export_financial_to_excel(total_revenue, total_purchase_cost, total_manufacturing_cost, total_inventory_value, gross_profit, profit_margin, monthly_trends):
|
|
"""Export financial data to Excel"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Financial Report"
|
|
|
|
# Header
|
|
ws['A1'] = "FINANCIAL REPORT"
|
|
ws['A1'].font = Font(bold=True, size=14)
|
|
ws.merge_cells('A1:F1')
|
|
|
|
# Financial Overview
|
|
ws['A3'] = "Financial Overview"
|
|
ws['A3'].font = Font(bold=True, size=12)
|
|
|
|
# Overview data
|
|
overview_data = [
|
|
['Metric', 'Value'],
|
|
['Total Revenue', total_revenue],
|
|
['Total Purchase Cost', total_purchase_cost],
|
|
['Total Manufacturing Cost', total_manufacturing_cost],
|
|
['Total Inventory Value', total_inventory_value],
|
|
['Gross Profit', gross_profit],
|
|
['Profit Margin (%)', profit_margin]
|
|
]
|
|
|
|
for row_num, row_data in enumerate(overview_data, 3):
|
|
for col_num, cell_data in enumerate(row_data, 1):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.value = cell_data
|
|
if row_num == 3 or col_num == 1: # Header or first column
|
|
cell.font = Font(bold=True)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Monthly Trends Header
|
|
trend_start_row = len(overview_data) + 5
|
|
ws.cell(row=trend_start_row, column=1).value = "Monthly Trends"
|
|
ws.cell(row=trend_start_row, column=1).font = Font(bold=True, size=12)
|
|
|
|
# Monthly Trends data
|
|
trend_headers = ['Period', 'Revenue', 'Costs', 'Profit']
|
|
for col_num, header in enumerate(trend_headers, 1):
|
|
cell = ws.cell(row=trend_start_row + 1, column=col_num)
|
|
cell.value = header
|
|
cell.font = Font(bold=True)
|
|
cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Monthly trends data rows
|
|
for row_num, trend in enumerate(monthly_trends, trend_start_row + 2):
|
|
ws.cell(row=row_num, column=1).value = trend['period']
|
|
ws.cell(row=row_num, column=2).value = float(trend['revenue'])
|
|
ws.cell(row=row_num, column=3).value = float(trend['costs'])
|
|
ws.cell(row=row_num, column=4).value = float(trend['profit'])
|
|
|
|
# Add borders to data cells
|
|
for col_num in range(1, 5):
|
|
cell = ws.cell(row=row_num, column=col_num)
|
|
cell.border = Border(
|
|
left=Side(style='thin'),
|
|
right=Side(style='thin'),
|
|
top=Side(style='thin'),
|
|
bottom=Side(style='thin')
|
|
)
|
|
|
|
# Auto-adjust column widths
|
|
for column in ws.columns:
|
|
max_length = 0
|
|
column_cells = [cell for cell in column]
|
|
for cell in column_cells:
|
|
if len(str(cell.value)) > max_length:
|
|
max_length = len(str(cell.value))
|
|
adjusted_width = (max_length + 2)
|
|
# Get the column letter from the first cell that is not a merged cell
|
|
column_letter = None
|
|
for cell in column_cells:
|
|
if hasattr(cell, 'column_letter'):
|
|
column_letter = cell.column_letter
|
|
break
|
|
if column_letter:
|
|
ws.column_dimensions[column_letter].width = min(adjusted_width, 50)
|
|
|
|
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
response['Content-Disposition'] = 'attachment; filename=financial_report.xlsx'
|
|
|
|
wb.save(response)
|
|
return response |