179 lines
6.4 KiB
Python
179 lines
6.4 KiB
Python
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String, DateTime, Float, Numeric
|
|
from sqlalchemy.types import TypeDecorator, VARCHAR, Integer, CHAR
|
|
from sqlalchemy_utils import EmailType
|
|
from sqlalchemy.orm import relationship
|
|
#from models.database import Base
|
|
from .database import Base
|
|
|
|
import datetime
|
|
|
|
class TrimChar(TypeDecorator):
|
|
impl = CHAR
|
|
cache_key = False
|
|
cache_ok = True
|
|
|
|
def process_result_value(self, value, dialect):
|
|
if value:
|
|
value = value.strip()
|
|
return value
|
|
|
|
class NullCatchingString(TypeDecorator):
|
|
impl = VARCHAR
|
|
|
|
def process_result_value(self, value, dialect):
|
|
if value is None:
|
|
return ''
|
|
return value
|
|
|
|
class NullCatchingInteger(TypeDecorator):
|
|
impl = Integer
|
|
|
|
def process_result_value(self, value, dialect):
|
|
if value is None:
|
|
return ''
|
|
return value
|
|
|
|
class Members(Base):
|
|
__tablename__ = 'members'
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
member_count = Column(Integer)
|
|
member_firstname = Column(String, index=True)
|
|
member_firstname_spouse = Column(NullCatchingString())
|
|
member_surname = Column(String, index=True)
|
|
member_suffix = Column(NullCatchingString(), nullable=True)
|
|
member_street = Column(NullCatchingString(), index=True)
|
|
member_streetnumber = Column(NullCatchingString(), nullable=True)
|
|
member_email = Column(EmailType, index=True)
|
|
member_zip = Column(NullCatchingInteger(), index=True)
|
|
member_city = Column(NullCatchingString(), index=True)
|
|
member_active = Column(Boolean, default=True)
|
|
member_gender = Column(String, nullable=True)
|
|
member_connected = Column(Integer, ForeignKey("members.id"), nullable=True)
|
|
created_date = Column(DateTime, default=datetime.datetime.utcnow)
|
|
updated_date = Column(DateTime, default=datetime.datetime.utcnow)
|
|
member_died = Column(DateTime)
|
|
member_membership_start = Column(DateTime)
|
|
member_printed = Column(Boolean, default=False)
|
|
#member_coordinates = Column(Geometry(geometry_type='POINT'))
|
|
member_geo_latitude = Column(Float, nullable=True)
|
|
member_geo_longitude = Column(Float, nullable=True)
|
|
member_deliveries = relationship('Deliveries', backref='members')
|
|
member_payments = relationship('Payments', backref='members')
|
|
|
|
spouse_id = Column(Integer, ForeignKey('members.id'), nullable=True)
|
|
spouse = relationship(
|
|
"Members",
|
|
remote_side=[id],
|
|
foreign_keys=[spouse_id],
|
|
backref="spouse_of",
|
|
single_parent=True
|
|
)
|
|
|
|
current_state_code = Column(TrimChar, index=True)
|
|
# current_state = relationship(
|
|
# 'MemberStates',
|
|
# foreign_keys=[current_state_code],
|
|
# # primaryjoin="foreign(Members.current_state_code) == remote(MemberStates.state_code)",
|
|
# viewonly=True
|
|
# )
|
|
|
|
current_state_changed_at = Column(DateTime)
|
|
member_primary = Column(Boolean, default=True)
|
|
|
|
def __repr__(self):
|
|
return f'<Comment "{self.content[:20]}...">'
|
|
|
|
class Payments(Base):
|
|
__tablename__ = 'payments'
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
payment_date = Column(DateTime, index=True)
|
|
member_id = Column(Integer, ForeignKey("members.id"))
|
|
payment_amount = Column(Numeric)
|
|
payment_year = Column(DateTime, index=True)
|
|
|
|
def __repr__(self):
|
|
return f'<Comment "{self.content[:20]}...">'
|
|
|
|
class Deliveries(Base):
|
|
__tablename__ = 'deliveries'
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
delivery_date = Column(DateTime, index=True)
|
|
member_id = Column(Integer, ForeignKey("members.id"))
|
|
delivered_by_id = Column(Integer)
|
|
delivery_year = Column(DateTime, index=True)
|
|
|
|
def __repr__(self):
|
|
return f'<Comment "{self.content[:20]}...">'
|
|
|
|
""" delivered_by_id = Column(Integer, ForeignKey("members.id" ))
|
|
member = relationship("Members", foreign_keys=[member_id])
|
|
delivering_member = relationship("Members", foreign_keys=[member_id]) """
|
|
|
|
class Payments_CSV(Base):
|
|
__tablename__ = 'payments_csv'
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
pc_valuta_date = Column(DateTime)
|
|
pc_client = Column(String)
|
|
pc_reference = Column(String)
|
|
pc_intended = Column(String)
|
|
pc_iban = Column(String)
|
|
pc_entry_date = Column(DateTime)
|
|
pc_amount = Column(Numeric)
|
|
pc_currency = Column(String)
|
|
pc_timestamp = Column(DateTime)
|
|
pc_processed = Column(Boolean, default=False)
|
|
pc_year = Column(DateTime)
|
|
|
|
class MemberStates(Base):
|
|
__tablename__ = 'member_states'
|
|
|
|
state_code = Column(TrimChar, primary_key=True, index=True)
|
|
state_name = Column(String(20), nullable=False)
|
|
description = Column(String)
|
|
created_at = Column(DateTime)
|
|
# members = relationship("Members", back_populates="current_state")
|
|
|
|
class MemberStateLog(Base):
|
|
__tablename__ = 'member_state_log'
|
|
|
|
log_id = Column(Integer, primary_key=True)
|
|
member_id = Column(Integer, ForeignKey('members.id'), nullable=False)
|
|
state_code = Column(String(1), ForeignKey('member_states.state_code'), nullable=False)
|
|
changed_at = Column(DateTime)
|
|
changed_by = Column(Integer) # Optional: user ID who made the change
|
|
reason = Column(String)
|
|
|
|
# Relationships
|
|
member = relationship('Members', backref='state_history')
|
|
state = relationship('MemberStates')
|
|
|
|
class MembersPaidYears(Base):
|
|
"""View for members and their payment years"""
|
|
__tablename__ = 'members_paid_years'
|
|
__table_args__ = {'extend_existing': True}
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
member_firstname = Column(String)
|
|
member_surname = Column(String)
|
|
member_suffix = Column(String)
|
|
member_email = Column(String)
|
|
member_active = Column(Boolean)
|
|
# payment_year = Column(DateTime)
|
|
# payment_amount = Column(Float)
|
|
# payment_date = Column(DateTime)
|
|
member_active = Column(Boolean, default=True)
|
|
current_state_code = Column(TrimChar, index=True)
|
|
# current_state = relationship(
|
|
# 'MemberStates', # Ensure this matches the CLASS name exactly
|
|
# primaryjoin='Members.current_state_code == MemberStates.state_code',
|
|
# foreign_keys='[Members.current_state_code]', # Use a string here to avoid registry issues
|
|
# viewonly=True # Recommended if there is no actual ForeignKey constraint in DB
|
|
# )
|
|
member_membership_start = Column(DateTime)
|
|
paid_2024 = Column(Boolean, default=False)
|
|
paid_2025 = Column(Boolean, default=False)
|
|
paid_2026 = Column(Boolean, default=False) |