92 lines
3.5 KiB
Python
92 lines
3.5 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Query script to get payment information with member_count breakdown for 2025 and 2026
|
|
"""
|
|
|
|
import sys
|
|
from datetime import datetime
|
|
sys.path.insert(0, '/var/git/SUST/sustApp')
|
|
|
|
from sqlalchemy import text
|
|
from models.database import SessionLocal
|
|
|
|
def get_payment_stats_by_year(year):
|
|
"""Get payment statistics for active primary members by member_count"""
|
|
session = SessionLocal()
|
|
try:
|
|
query = text(f"""
|
|
SELECT COALESCE(m.member_count, 1) as member_count,
|
|
COUNT(DISTINCT m.id) as num_households,
|
|
SUM(m.member_count) as total_people,
|
|
SUM(CASE WHEN p.id IS NOT NULL THEN 1 ELSE 0 END) as households_paid,
|
|
SUM(CASE WHEN p.id IS NOT NULL THEN m.member_count ELSE 0 END) as people_paid,
|
|
SUM(COALESCE(p.payment_amount, 0)) as total_revenue
|
|
FROM members m
|
|
LEFT JOIN payments p ON m.id = p.member_id AND EXTRACT(YEAR FROM p.payment_year) = {year}
|
|
WHERE m.member_active = true
|
|
AND m.member_died IS NULL
|
|
AND m.member_primary = true
|
|
GROUP BY COALESCE(m.member_count, 1)
|
|
ORDER BY COALESCE(m.member_count, 1) DESC
|
|
""")
|
|
result = session.execute(query).fetchall()
|
|
return result
|
|
finally:
|
|
session.close()
|
|
|
|
def print_statistics(year, data):
|
|
"""Print payment statistics"""
|
|
if not data:
|
|
print(f"No data found for {year}.")
|
|
return
|
|
|
|
print("\n" + "="*110)
|
|
print(f"PAYMENT STATISTICS FOR ACTIVE PRIMARY MEMBERS - {year}")
|
|
print("="*110)
|
|
print(f"{'Member Cnt':<12} {'Households':<15} {'Total People':<15} {'Paid (HH)':<15} {'Paid (People)':<15} {'Revenue (€)':<15}")
|
|
print("-"*110)
|
|
|
|
total_households = 0
|
|
total_people = 0
|
|
total_paid_households = 0
|
|
total_paid_people = 0
|
|
total_revenue = 0
|
|
|
|
for row in data:
|
|
member_count = int(row[0])
|
|
num_households = row[1]
|
|
total_people_count = row[2]
|
|
households_paid = row[3]
|
|
people_paid = row[4]
|
|
revenue = float(row[5]) if row[5] else 0
|
|
|
|
total_households += num_households
|
|
total_people += total_people_count
|
|
total_paid_households += households_paid
|
|
total_paid_people += people_paid
|
|
total_revenue += revenue
|
|
|
|
pct_households = (households_paid / num_households * 100) if num_households > 0 else 0
|
|
pct_people = (people_paid / total_people_count * 100) if total_people_count > 0 else 0
|
|
|
|
print(f"{member_count:<12} {num_households:<15} {total_people_count:<15} {households_paid:<15} {people_paid:<15} {revenue:<15.2f}")
|
|
print(f"{'':12} {f'{pct_households:.1f}%':<15} {f'{pct_people:.1f}%':<15}")
|
|
|
|
print("-"*110)
|
|
pct_households_total = (total_paid_households / total_households * 100) if total_households > 0 else 0
|
|
pct_people_total = (total_paid_people / total_people * 100) if total_people > 0 else 0
|
|
print(f"{'TOTAL':<12} {total_households:<15} {total_people:<15} {total_paid_households:<15} {total_paid_people:<15} {total_revenue:<15.2f}")
|
|
print(f"{'':12} {f'{pct_households_total:.1f}%':<15} {f'{pct_people_total:.1f}%':<15}")
|
|
print("="*110)
|
|
|
|
if __name__ == "__main__":
|
|
print("Querying SUST payment statistics by member count...")
|
|
|
|
# Get 2025 statistics
|
|
print_statistics(2025, get_payment_stats_by_year(2025))
|
|
|
|
# Get 2026 statistics
|
|
print_statistics(2026, get_payment_stats_by_year(2026))
|
|
|
|
print("\n")
|