SUST/sustApp/payment_report.py

import models.models as models
from reportlab.lib.pagesizes import A4, landscape
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import mm
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
from reportlab.lib import colors
from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT
from datetime import datetime
import locale
import os
from config_loader import load_config
from models.database import engine, SessionLocal
from sqlalchemy import func

cfg = load_config()
year = cfg['year']

locale.setlocale(locale.LC_ALL, '')

def get_payment_table_data():
    """Get payment data organized by member with summary columns"""
    
    db = SessionLocal()
    
    try:
        # Query members with their payment information
        members = db.query(models.Members).filter(
            models.Members.member_active == True
        ).order_by(
            models.Members.member_surname,
            models.Members.member_firstname
        ).all()
        
        table_data = []
        
        # Header row
        table_data.append([
            'Paid 2024',
            'Paid 2025'
        ])
        
        # Data rows - one per member
        for member in members:
            payments = db.query(models.MembersPaidYears).filter(
                models.MembersPaidYears.id == member.id
            )

            if payments:
                table_data.append([
                    '✓' if payments.paid_2024 else '✗',
                    '✓' if payments.paid_2025 else '✗'
                ])
        
        return table_data
        
    finally:
        db.close()

def create_payment_summary_table():
    """Create a single table with payment summary for all members"""
    
    try:
        # Get table data
        table_data = get_payment_table_data()
        
        if len(table_data) <= 1:
            print("⚠️ No member data found")
            return None
        
        # Create filename
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        output_path = os.path.join(
            os.path.expanduser("~"), 
            "sust", 
            f"payment_reports_{year}",
            f"payment_summary_{timestamp}.pdf"
        )
        
        # Ensure directory exists
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        
        # Create PDF document
        doc = SimpleDocTemplate(
            output_path, 
            pagesize=landscape(A4), 
            topMargin=15*mm, 
            bottomMargin=15*mm,
            leftMargin=10*mm,
            rightMargin=10*mm
        )
        elements = []
        
        # Title
        styles = getSampleStyleSheet()
        title_style = ParagraphStyle(
            'CustomTitle',
            parent=styles['Heading1'],
            fontSize=16,
            textColor=colors.HexColor('#1f4788'),
            spaceAfter=15,
            alignment=TA_CENTER
        )
        title = Paragraph(f"Zahlungsübersicht Mitgliederbeiträge {year}", title_style)
        elements.append(title)
        
        # Create table
        table = Table(table_data, repeatRows=1)
        
        # Style table
        table.setStyle(TableStyle([
            # Header styling
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4788')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, 0), TA_CENTER),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('TOPPADDING', (0, 0), (-1, 0), 8),
            
            # Body styling
            ('ALIGN', (0, 1), (0, -1), TA_CENTER),  # Member ID centered
            ('ALIGN', (1, 1), (1, -1), TA_LEFT),    # Name left
            ('ALIGN', (2, 1), (2, -1), TA_LEFT),    # Email left
            ('ALIGN', (3, 1), (3, -1), TA_CENTER),  # Member Count centered
            ('ALIGN', (4, 1), (6, -1), TA_CENTER),  # Payment columns centered
            
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f9f9f9')]),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('LEFTPADDING', (0, 0), (-1, -1), 5),
            ('RIGHTPADDING', (0, 0), (-1, -1), 5),
            ('TOPPADDING', (0, 1), (-1, -1), 4),
            ('BOTTOMPADDING', (0, 1), (-1, -1), 4),
            
            # Highlight payment status columns
            ('BACKGROUND', (4, 0), (5, -1), colors.HexColor('#f0f8ff')),
        ]))
        
        elements.append(table)
        
        # Add footer with summary
        elements.append(Spacer(1, 1.5*mm))
        summary_style = ParagraphStyle(
            'Summary',
            parent=styles['Normal'],
            fontSize=8,
            textColor=colors.grey
        )
        summary = Paragraph(
            f"Total Members: {len(table_data) - 1} | Generated: {datetime.now().strftime('%d.%m.%Y %H:%M:%S')}",
            summary_style
        )
        elements.append(summary)
        
        # Build PDF
        doc.build(elements)
        
        print(f"✅ Payment summary created: {output_path}")
        print(f"📊 Total members: {len(table_data) - 1}")
        return output_path
        
    except Exception as e:
        print(f"❌ Error creating payment summary: {str(e)}")
        import traceback
        traceback.print_exc()
        return None

# Function to integrate into existing PDF
def get_payment_table_element():
    """Return a ReportLab Table element for embedding in existing PDFs"""
    
    table_data = get_payment_table_data()
    
    if len(table_data) <= 1:
        return None
    
    table = Table(table_data, repeatRows=1)
    
    table.setStyle(TableStyle([
        # Header styling
        ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f4788')),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, 0), TA_CENTER),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 9),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
        
        # Body styling
        ('ALIGN', (0, 1), (0, -1), TA_CENTER),
        ('ALIGN', (1, 1), (1, -1), TA_LEFT),
        ('ALIGN', (2, 1), (2, -1), TA_LEFT),
        ('ALIGN', (3, 1), (-1, -1), TA_CENTER),
        ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
        ('FONTSIZE', (0, 1), (-1, -1), 7),
        ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f9f9f9')]),
        ('GRID', (0, 0), (-1, -1), 0.5, colors.grey),
        ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
        ('LEFTPADDING', (0, 0), (-1, -1), 3),
        ('RIGHTPADDING', (0, 0), (-1, -1), 3),
        ('TOPPADDING', (0, 1), (-1, -1), 2),
        ('BOTTOMPADDING', (0, 1), (-1, -1), 2),
    ]))
    
    return table

if __name__ == '__main__':
    create_payment_summary_table()