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, PatternFill
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 = ['人员', '日期', '供应商', '物品', '单价', '数量', '总价', '结算状态']
    
    # Style for unsettled status highlighting (light orange #FFF2E8)
    UNSETTLED_FILL = PatternFill(start_color='FFF2E8', end_color='FFF2E8', fill_type='solid')
    
    @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):
            # Get supplier name (empty string if no supplier)
            supplier_name = record.item.supplier.name if record.item and record.item.supplier else ''
            
            # Get settlement status text
            settlement_status = '已结算' if record.is_settled else '未结算'
            
            data = [
                record.person.name,
                record.work_date.strftime('%Y-%m-%d'),
                supplier_name,
                record.item.name,
                record.item.unit_price,
                record.quantity,
                record.total_price,
                settlement_status
            ]
            for col_idx, value in enumerate(data, 1):
                cell = ws.cell(row=row_idx, column=col_idx, value=value)
                is_number = col_idx in [5, 6, 7]  # unit_price, quantity, total_price
                ExportService._apply_cell_style(cell, is_number)
                # Apply unsettled fill to entire row if record is unsettled
                if not record.is_settled:
                    cell.fill = ExportService.UNSETTLED_FILL
        
        ExportService._auto_adjust_column_width(ws)
        return ws
    
    @staticmethod
    def _create_monthly_summary_sheet(workbook, records):
        """Create monthly summary sheet grouped by person, supplier, and settlement status.
        
        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, supplier, and settlement status
        person_supplier_settlement_totals = {}
        for record in records:
            person_name = record.person.name
            supplier_name = record.item.supplier.name if record.item and record.item.supplier else ''
            is_settled = record.is_settled
            key = (person_name, supplier_name, is_settled)
            
            if key not in person_supplier_settlement_totals:
                person_supplier_settlement_totals[key] = 0.0
            person_supplier_settlement_totals[key] += record.total_price
        
        # Write data rows (sorted by person name, then supplier name, then settlement status)
        row_idx = 2
        grand_total = 0.0
        settled_total = 0.0
        unsettled_total = 0.0
        for (person_name, supplier_name, is_settled) in sorted(person_supplier_settlement_totals.keys()):
            total = person_supplier_settlement_totals[(person_name, supplier_name, is_settled)]
            grand_total += total
            if is_settled:
                settled_total += total
            else:
                unsettled_total += total
            
            settlement_status = '已结算' if is_settled else '未结算'
            
            cell = ws.cell(row=row_idx, column=1, value=person_name)
            ExportService._apply_cell_style(cell)
            if not is_settled:
                cell.fill = ExportService.UNSETTLED_FILL
            
            cell = ws.cell(row=row_idx, column=2, value=supplier_name)
            ExportService._apply_cell_style(cell)
            if not is_settled:
                cell.fill = ExportService.UNSETTLED_FILL
            
            cell = ws.cell(row=row_idx, column=3, value=round(total, 2))
            ExportService._apply_cell_style(cell, is_number=True)
            if not is_settled:
                cell.fill = ExportService.UNSETTLED_FILL
            
            cell = ws.cell(row=row_idx, column=4, value=settlement_status)
            ExportService._apply_cell_style(cell)
            if not is_settled:
                cell.fill = ExportService.UNSETTLED_FILL
            
            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='')
        ExportService._apply_cell_style(cell)
        
        cell = ws.cell(row=row_idx, column=3, value=round(grand_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        
        cell = ws.cell(row=row_idx, column=4, value='')
        ExportService._apply_cell_style(cell)
        
        row_idx += 1
        
        # Write settled 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='')
        ExportService._apply_cell_style(cell)
        
        cell = ws.cell(row=row_idx, column=3, value=round(settled_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        
        cell = ws.cell(row=row_idx, column=4, value='')
        ExportService._apply_cell_style(cell)
        
        row_idx += 1
        
        # Write unsettled total row
        cell = ws.cell(row=row_idx, column=1, value='未结算')
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell)
        cell.fill = ExportService.UNSETTLED_FILL
        
        cell = ws.cell(row=row_idx, column=2, value='')
        ExportService._apply_cell_style(cell)
        cell.fill = ExportService.UNSETTLED_FILL
        
        cell = ws.cell(row=row_idx, column=3, value=round(unsettled_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        cell.fill = ExportService.UNSETTLED_FILL
        
        cell = ws.cell(row=row_idx, column=4, value='')
        ExportService._apply_cell_style(cell)
        cell.fill = ExportService.UNSETTLED_FILL
        
        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)
            # Apply unsettled fill to the "未结算" column header (column 16)
            if col == 16:
                cell.fill = ExportService.UNSETTLED_FILL
        
        # Calculate totals by person and month, plus settlement status
        person_monthly_totals = {}
        person_settlement_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_settlement_totals[person_name] = {'settled': 0.0, 'unsettled': 0.0}
            
            person_monthly_totals[person_name][month] += record.total_price
            
            # Track settlement status
            if record.is_settled:
                person_settlement_totals[person_name]['settled'] += record.total_price
            else:
                person_settlement_totals[person_name]['unsettled'] += record.total_price
        
        # Write data rows
        row_idx = 2
        monthly_grand_totals = {m: 0.0 for m in range(1, 13)}
        settled_grand_total = 0.0
        unsettled_grand_total = 0.0
        
        for person_name in sorted(person_monthly_totals.keys()):
            monthly_totals = person_monthly_totals[person_name]
            settlement_totals = person_settlement_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)
            
            # Settled total
            settled = settlement_totals['settled']
            settled_grand_total += settled
            cell = ws.cell(row=row_idx, column=15, value=round(settled, 2))
            ExportService._apply_cell_style(cell, is_number=True)
            
            # Unsettled total
            unsettled = settlement_totals['unsettled']
            unsettled_grand_total += unsettled
            cell = ws.cell(row=row_idx, column=16, value=round(unsettled, 2))
            ExportService._apply_cell_style(cell, is_number=True)
            cell.fill = ExportService.UNSETTLED_FILL
            
            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)
        
        # Settled grand total
        cell = ws.cell(row=row_idx, column=15, value=round(settled_grand_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        
        # Unsettled grand total
        cell = ws.cell(row=row_idx, column=16, value=round(unsettled_grand_total, 2))
        cell.font = Font(bold=True)
        ExportService._apply_cell_style(cell, is_number=True)
        cell.fill = ExportService.UNSETTLED_FILL
        
        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