184 lines
7.1 KiB
Python
184 lines
7.1 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Query script to get payment information for members:
|
|
- Payments by active members
|
|
- Payments by members who became inactive in 2025
|
|
- Payments by members who died in 2025
|
|
"""
|
|
|
|
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_active_members_payments():
|
|
"""Get payment data for active members"""
|
|
session = SessionLocal()
|
|
try:
|
|
query = text("""
|
|
SELECT m.id, m.member_firstname, m.member_surname, m.member_email,
|
|
COUNT(p.id) as payment_count,
|
|
SUM(p.payment_amount) as total_paid,
|
|
MAX(p.payment_date) as last_payment_date,
|
|
STRING_AGG(DISTINCT EXTRACT(YEAR FROM p.payment_year)::text, ', ' ORDER BY EXTRACT(YEAR FROM p.payment_year)::text) as payment_years
|
|
FROM members m
|
|
LEFT JOIN payments p ON m.id = p.member_id
|
|
WHERE m.member_active = true AND m.member_died IS NULL
|
|
GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email
|
|
ORDER BY m.member_surname, m.member_firstname
|
|
""")
|
|
result = session.execute(query).fetchall()
|
|
return result
|
|
finally:
|
|
session.close()
|
|
|
|
def get_inactive_2025_payments():
|
|
"""Get payment data for members who became inactive in 2025"""
|
|
session = SessionLocal()
|
|
try:
|
|
query = text("""
|
|
SELECT DISTINCT m.id, m.member_firstname, m.member_surname, m.member_email,
|
|
COUNT(p.id) as payment_count,
|
|
SUM(p.payment_amount) as total_paid,
|
|
MAX(p.payment_date) as last_payment_date,
|
|
STRING_AGG(DISTINCT EXTRACT(YEAR FROM p.payment_year)::text, ', ' ORDER BY EXTRACT(YEAR FROM p.payment_year)::text) as payment_years
|
|
FROM members m
|
|
LEFT JOIN payments p ON m.id = p.member_id
|
|
JOIN member_state_log msl ON m.id = msl.member_id
|
|
WHERE msl.state_code = 'I'
|
|
AND EXTRACT(YEAR FROM msl.changed_at) = 2025
|
|
GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email
|
|
ORDER BY m.member_surname, m.member_firstname
|
|
""")
|
|
result = session.execute(query).fetchall()
|
|
return result
|
|
finally:
|
|
session.close()
|
|
|
|
def get_died_2025_payments():
|
|
"""Get payment data for members who died in 2025"""
|
|
session = SessionLocal()
|
|
try:
|
|
query = text("""
|
|
SELECT m.id, m.member_firstname, m.member_surname, m.member_email, m.member_died,
|
|
COUNT(p.id) as payment_count,
|
|
SUM(p.payment_amount) as total_paid,
|
|
MAX(p.payment_date) as last_payment_date,
|
|
STRING_AGG(DISTINCT EXTRACT(YEAR FROM p.payment_year)::text, ', ' ORDER BY EXTRACT(YEAR FROM p.payment_year)::text) as payment_years
|
|
FROM members m
|
|
LEFT JOIN payments p ON m.id = p.member_id
|
|
WHERE m.member_died >= '2025-01-01' AND m.member_died < '2026-01-01'
|
|
GROUP BY m.id, m.member_firstname, m.member_surname, m.member_email, m.member_died
|
|
ORDER BY m.member_surname, m.member_firstname
|
|
""")
|
|
result = session.execute(query).fetchall()
|
|
return result
|
|
finally:
|
|
session.close()
|
|
|
|
def print_active_payments(data):
|
|
"""Print payment data for active members"""
|
|
if not data:
|
|
print("No payment data found for active members.")
|
|
return
|
|
|
|
print("\n" + "="*130)
|
|
print(f"PAYMENTS BY ACTIVE MEMBERS ({len(data)} total)")
|
|
print("="*130)
|
|
print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid':<15} {'Last Payment':<15} {'Payment Years':<20}")
|
|
print("-"*130)
|
|
|
|
total_all = 0
|
|
count_all = 0
|
|
|
|
for idx, row in enumerate(data, 1):
|
|
payment_count = row[4] or 0
|
|
total_paid = float(row[5]) if row[5] else 0
|
|
last_payment = row[6].strftime("%Y-%m-%d") if row[6] else "N/A"
|
|
payment_years = row[7] or "None"
|
|
|
|
total_all += total_paid
|
|
count_all += payment_count
|
|
|
|
print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15} {payment_years:<20}")
|
|
|
|
print("-"*130)
|
|
print(f"{'TOTAL':<84} {count_all:<8} {total_all:<15.2f}")
|
|
|
|
def print_inactive_2025_payments(data):
|
|
"""Print payment data for members who became inactive in 2025"""
|
|
if not data:
|
|
print("No payment data found for members who became inactive in 2025.")
|
|
return
|
|
|
|
print("\n" + "="*130)
|
|
print(f"PAYMENTS BY MEMBERS WHO BECAME INACTIVE IN 2025 ({len(data)} total)")
|
|
print("="*130)
|
|
print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid':<15} {'Last Payment':<15} {'Payment Years':<20}")
|
|
print("-"*130)
|
|
|
|
total_all = 0
|
|
count_all = 0
|
|
|
|
for idx, row in enumerate(data, 1):
|
|
payment_count = row[4] or 0
|
|
total_paid = float(row[5]) if row[5] else 0
|
|
last_payment = row[6].strftime("%Y-%m-%d") if row[6] else "N/A"
|
|
payment_years = row[7] or "None"
|
|
|
|
total_all += total_paid
|
|
count_all += payment_count
|
|
|
|
print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15} {payment_years:<20}")
|
|
|
|
print("-"*130)
|
|
print(f"{'TOTAL':<84} {count_all:<8} {total_all:<15.2f}")
|
|
|
|
def print_died_2025_payments(data):
|
|
"""Print payment data for members who died in 2025"""
|
|
if not data:
|
|
print("No payment data found for members who died in 2025.")
|
|
return
|
|
|
|
print("\n" + "="*130)
|
|
print(f"PAYMENTS BY MEMBERS WHO DIED IN 2025 ({len(data)} total)")
|
|
print("="*130)
|
|
print(f"{'#':<4} {'First Name':<20} {'Surname':<25} {'Email':<35} {'Count':<8} {'Total Paid':<15} {'Last Payment':<15} {'Payment Years':<20}")
|
|
print("-"*130)
|
|
|
|
total_all = 0
|
|
count_all = 0
|
|
|
|
for idx, row in enumerate(data, 1):
|
|
payment_count = row[5] or 0
|
|
total_paid = float(row[6]) if row[6] else 0
|
|
last_payment = row[7].strftime("%Y-%m-%d") if row[7] else "N/A"
|
|
payment_years = row[8] or "None"
|
|
|
|
total_all += total_paid
|
|
count_all += payment_count
|
|
|
|
print(f"{idx:<4} {(row[1] or ''):<20} {(row[2] or ''):<25} {(row[3] or 'N/A'):<35} {payment_count:<8} {total_paid:<15.2f} {last_payment:<15} {payment_years:<20}")
|
|
|
|
print("-"*130)
|
|
print(f"{'TOTAL':<84} {count_all:<8} {total_all:<15.2f}")
|
|
|
|
if __name__ == "__main__":
|
|
print("Querying SUST member payment data...")
|
|
|
|
# Get payment data for active members
|
|
active_payments = get_active_members_payments()
|
|
print_active_payments(active_payments)
|
|
|
|
# Get payment data for members who became inactive in 2025
|
|
inactive_2025_payments = get_inactive_2025_payments()
|
|
print_inactive_2025_payments(inactive_2025_payments)
|
|
|
|
# Get payment data for members who died in 2025
|
|
died_2025_payments = get_died_2025_payments()
|
|
print_died_2025_payments(died_2025_payments)
|
|
|
|
print("\n" + "="*130 + "\n")
|