work-statistics/backend/app/services/export_service.py

"""Export service for generating Excel reports."""
from datetime import date
from io import BytesIO
from calendar import monthrange
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from app import db
from app.models.work_record import WorkRecord
from app.models.person import Person


class ExportService:
    """Service class for Excel export operations."""
    
    # Column headers for detail sheet
    DETAIL_HEADERS = ['人员', '日期', '物品', '单价', '数量', '总价']
    
    @staticmethod
    def _apply_header_style(cell):
        """Apply header style to a cell."""
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.border = thin_border
    
    @staticmethod
    def _apply_cell_style(cell, is_number=False):
        """Apply standard cell style."""
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.border = thin_border
        if is_number:
            cell.alignment = Alignment(horizontal='right')
    
    @staticmethod
    def _auto_adjust_column_width(worksheet):
        """Auto-adjust column widths based on content."""
        for column_cells in worksheet.columns:
            max_length = 0
            column = column_cells[0].column_letter
            for cell in column_cells:
                try:
                    if cell.value:
                        cell_length = len(str(cell.value))
                        if cell_length > max_length:
                            max_length = cell_length
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            worksheet.column_dimensions[column].width = max(adjusted_width, 10)

    @staticmethod
    def _get_work_records_for_period(start_date, end_date):
        """Get work records for a date range.
        
        Args:
            start_date: Start date (inclusive)
            end_date: End date (inclusive)
            
        Returns:
            List of WorkRecord objects ordered by person name, date
        """
        return WorkRecord.query.filter(
            WorkRecord.work_date >= start_date,
            WorkRecord.work_date <= end_date
        ).join(Person).order_by(
            Person.name,
            WorkRecord.work_date
        ).all()
    
    @staticmethod
    def _create_detail_sheet(workbook, records, sheet_name='明细表'):
        """Create detail sheet with work records.
        
        Args:
            workbook: openpyxl Workbook
            records: List of WorkRecord objects
            sheet_name: Name for the sheet
            
        Returns:
            The created worksheet
        """
        ws = workbook.active
        ws.title = sheet_name
        
        # Write headers
        for col, header in enumerate(ExportService.DETAIL_HEADERS, 1):
            cell = ws.cell(row=1, column=col, value=header)
            ExportService._apply_header_style(cell)
        
        # Write data rows
        for row_idx, record in enumerate(records, 2):
            data = [
                record.person.name,
                record.work_date.strftime('%Y-%m-%d'),
                record.item.name,
                record.item.unit_price,
                record.quantity,
                record.total_price
            ]
            for col_idx, value in enumerate(data, 1):
                cell = ws.cell(row=row_idx, column=col_idx, value=value)
                is_number = col_idx in [4, 5, 6]  # unit_price, quantity, total_price
                ExportService._apply_cell_style(cell, is_number)
        
        ExportService._auto_adjust_column_width(ws)
        return ws
    
    @staticmethod
    def _create_monthly_summary_sheet(workbook, records):
        """Create monthly summary sheet grouped by person.
        
        Args:
            workbook: openpyxl Workbook
            records: List of WorkRecord objects
            
        Returns:
            The created worksheet
        """
        ws = workbook.create_sheet(title='月度汇总')
        
        # Write headers
        headers = ['人员', '总金额']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            ExportService._apply_header_style(cell)
        
        # Calculate totals by person
        person_totals = {}
        for record in records:
            person_name = record.person.name
            if person_name not in person_totals:
                person_totals[person_name] = 0.0
            person_totals[person_name] += record.total_price
        
        # Write data rows
        row_idx = 2
        grand_total = 0.0
        for person_name in sorted(person_totals.keys()):
            total = person_totals[person_name]
            grand_total += total
            
            cell = ws.cell(row=row_idx, column=1, value=person_name)
            ExportService._apply_cell_style(cell)
            
            cell = ws.cell(row=row_idx, column=2, value=round(total, 2))
            ExportService._apply_cell_style(cell, is_number=True)
            
            row_idx += 1
        
        # Write grand total row
        cell = ws.cell(row=row_idx, column=1, value='合计')
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell)
        
        cell = ws.cell(row=row_idx, column=2, value=round(grand_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        
        ExportService._auto_adjust_column_width(ws)
        return ws

    @staticmethod
    def _create_yearly_summary_sheet(workbook, records, year):
        """Create yearly summary sheet with monthly breakdown.
        
        Args:
            workbook: openpyxl Workbook
            records: List of WorkRecord objects
            year: The year being exported
            
        Returns:
            The created worksheet
        """
        ws = workbook.create_sheet(title='年度汇总')
        
        # Write headers: 人员, 1月, 2月, ..., 12月, 年度合计
        headers = ['人员'] + [f'{m}月' for m in range(1, 13)] + ['年度合计']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            ExportService._apply_header_style(cell)
        
        # Calculate totals by person and month
        person_monthly_totals = {}
        for record in records:
            person_name = record.person.name
            month = record.work_date.month
            
            if person_name not in person_monthly_totals:
                person_monthly_totals[person_name] = {m: 0.0 for m in range(1, 13)}
            
            person_monthly_totals[person_name][month] += record.total_price
        
        # Write data rows
        row_idx = 2
        monthly_grand_totals = {m: 0.0 for m in range(1, 13)}
        
        for person_name in sorted(person_monthly_totals.keys()):
            monthly_totals = person_monthly_totals[person_name]
            yearly_total = sum(monthly_totals.values())
            
            # Person name
            cell = ws.cell(row=row_idx, column=1, value=person_name)
            ExportService._apply_cell_style(cell)
            
            # Monthly values
            for month in range(1, 13):
                value = monthly_totals[month]
                monthly_grand_totals[month] += value
                cell = ws.cell(row=row_idx, column=month + 1, value=round(value, 2))
                ExportService._apply_cell_style(cell, is_number=True)
            
            # Yearly total
            cell = ws.cell(row=row_idx, column=14, value=round(yearly_total, 2))
            ExportService._apply_cell_style(cell, is_number=True)
            
            row_idx += 1
        
        # Write grand total row
        cell = ws.cell(row=row_idx, column=1, value='合计')
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell)
        
        grand_total = 0.0
        for month in range(1, 13):
            value = monthly_grand_totals[month]
            grand_total += value
            cell = ws.cell(row=row_idx, column=month + 1, value=round(value, 2))
            cell.font = Font(bold=True)
            ExportService._apply_cell_style(cell, is_number=True)
        
        cell = ws.cell(row=row_idx, column=14, value=round(grand_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        
        ExportService._auto_adjust_column_width(ws)
        return ws

    @staticmethod
    def export_monthly(year, month):
        """Export monthly work records to Excel.
        
        Args:
            year: Year (e.g., 2024)
            month: Month (1-12)
            
        Returns:
            Tuple of (BytesIO containing Excel file, error_message)
            On success: (excel_bytes, None)
            On failure: (None, error_message)
        """
        # Validate inputs
        if not isinstance(year, int) or year < 1900 or year > 9999:
            return None, "年份无效，必须在 1900 到 9999 之间"
        
        if not isinstance(month, int) or month < 1 or month > 12:
            return None, "月份无效，必须在 1 到 12 之间"
        
        # Calculate date range
        start_date = date(year, month, 1)
        _, last_day = monthrange(year, month)
        end_date = date(year, month, last_day)
        
        # Get work records
        records = ExportService._get_work_records_for_period(start_date, end_date)
        
        # Create workbook
        workbook = Workbook()
        
        # Create detail sheet
        ExportService._create_detail_sheet(workbook, records, f'{year}年{month}月明细')
        
        # Create summary sheet
        ExportService._create_monthly_summary_sheet(workbook, records)
        
        # Save to BytesIO
        output = BytesIO()
        workbook.save(output)
        output.seek(0)
        
        return output, None
    
    @staticmethod
    def export_yearly(year):
        """Export yearly work records to Excel.
        
        Args:
            year: Year (e.g., 2024)
            
        Returns:
            Tuple of (BytesIO containing Excel file, error_message)
            On success: (excel_bytes, None)
            On failure: (None, error_message)
        """
        # Validate inputs
        if not isinstance(year, int) or year < 1900 or year > 9999:
            return None, "年份无效，必须在 1900 到 9999 之间"
        
        # Calculate date range
        start_date = date(year, 1, 1)
        end_date = date(year, 12, 31)
        
        # Get work records
        records = ExportService._get_work_records_for_period(start_date, end_date)
        
        # Create workbook
        workbook = Workbook()
        
        # Create detail sheet
        ExportService._create_detail_sheet(workbook, records, f'{year}年明细')
        
        # Create yearly summary sheet with monthly breakdown
        ExportService._create_yearly_summary_sheet(workbook, records, year)
        
        # Save to BytesIO
        output = BytesIO()
        workbook.save(output)
        output.seek(0)
        
        return output, None