class GiftThankYouLetterModel(database.Model):
"""A gift thank you model"""
__tablename__ = 'gift_thank_you_letter'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
gift_id = database.Column(database.Integer, nullable=False)
gift = database.relationship(
'GiftModel',
foreign_keys=[GiftModel.id],
primaryjoin='GiftThankYouLetterModel.gift_id == GiftModel.id',
uselist=False)
@hybrid_property
def user(self):
"""Hybrid user attribute"""
try:
user = get_ultsys_user({'ID': {'eq': self.gift.user_id}}).json()[0]
except (AttributeError, IndexError, KeyError):
user = None
if user:
return {
'firstname': user['firstname'],
'lastname': user['lastname'],
'address': user['address'],
'city': user['city'],
'state': user['state'],
'email': user['email'],
}
return None
class MethodUsedModel(database.Model):
"""Agents model that defines who is modifying records."""
__tablename__ = 'method_used'
id = database.Column(BIGINT,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.VARCHAR(128),
nullable=False,
default='Web Form Credit Card')
billing_address_required = database.Column(TINYINT,
nullable=False,
default=0)
@staticmethod
def get_method_used(field, field_value):
"""Place latest transaction status on the Gift."""
equals_operator = '__eq__'
column = getattr(MethodUsedModel, field, None)
filter_by_equals = getattr(column, equals_operator)(field_value)
method_used_model = MethodUsedModel.query.filter(
filter_by_equals).one_or_none()
if not method_used_model:
column = getattr(MethodUsedModel, 'name', None)
filter_by_equals = getattr(column,
equals_operator)('Unknown Method Used')
method_used_model = MethodUsedModel.query.filter(
filter_by_equals).one_or_none()
return method_used_model
class CandidateModel(database.Model):
"""Details the SQL schema for candidate"""
__tablename__ = 'candidate'
id = database.Column(SMALLINT,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.String(200), nullable=False)
election_id = database.Column(SMALLINT, nullable=False)
class PaypalETLModel( database.Model ):
"""PaypalETLs model"""
__tablename__ = 'paypal_etl'
id = database.Column(
database.Integer, primary_key=True,
autoincrement=True, nullable=False
)
enacted_by_agent_id = database.Column( database.Integer, nullable=False )
file_name = database.Column( database.VARCHAR( 128 ), nullable=False )
date_in_utc = database.Column( database.DateTime, nullable=False )
class UserModel(database.Model):
"""Details the SQL schema for user"""
__tablename__ = 'user'
id = database.Column(SMALLINT,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.String(20), nullable=False)
username_hash = database.Column(database.String(50), nullable=False)
password_hash = database.Column(database.String(100), nullable=False)
class VoteModel(database.Model):
"""Details the SQL schema for vote"""
__tablename__ = 'vote'
id = database.Column(SMALLINT,
primary_key=True,
autoincrement=True,
nullable=False)
user_id = database.Column(SMALLINT, nullable=False)
election_id = database.Column(SMALLINT, nullable=False)
candidate_id = database.Column(SMALLINT, nullable=False)
cast_date_utc = database.Column(database.DateTime, nullable=True)
class ElectionModel(database.Model):
"""Details the SQL schema for election"""
__tablename__ = 'election'
id = database.Column(SMALLINT,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.String(20), nullable=False)
start_date_utc = database.Column(database.DateTime, nullable=False)
duration = database.Column(MEDIUMINT, nullable=False)
type = database.Column(database.String(10), nullable=False)
class CampaignAmountsModel(database.Model):
"""A user model that associates an amount ID with a campaign ID."""
__tablename__ = 'campaign_amounts'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
amount = database.Column(database.DECIMAL(10, 2), nullable=False)
weight = database.Column(database.Integer, nullable=True)
campaign_id = database.Column(database.Integer,
nullable=True,
default=None)
class AgentModel(database.Model):
"""Agents model that defines who is modifying records."""
__tablename__ = 'agent'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.VARCHAR(64), nullable=False, default='')
user_id = database.Column(database.Integer, nullable=True, default=None)
staff_id = database.Column(database.Integer, nullable=True, default=None)
type = database.Column(database.Enum('Staff Member',
'Organization',
'Automated',
native_enum=False),
nullable=False,
default='Staff Member')
@staticmethod
def get_agent(agent_type, field, field_value):
"""Place latest transaction status on the Gift."""
equals_operator = '__eq__'
column = getattr(AgentModel, field, None)
filter_by_equals = getattr(column, equals_operator)(field_value)
agent_model = AgentModel.query.filter(filter_by_equals).one_or_none()
if not agent_model:
column = getattr(AgentModel, 'name', None)
if agent_type == 'Staff Member':
filter_by_equals = getattr(
column, equals_operator)('Unknown Staff Member')
elif agent_type in ('Automated', 'Organization'):
filter_by_equals = getattr(
column, equals_operator)('Unknown Organization')
agent_model = AgentModel.query.filter(
filter_by_equals).one_or_none()
return agent_model
class GiftModel(database.Model):
"""Head, or master table, for donations."""
__tablename__ = 'gift'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
searchable_id = database.Column(BinaryUUID,
nullable=False,
default=uuid.uuid4)
user_id = database.Column(database.Integer, nullable=True, default=None)
campaign_id = database.Column(database.Integer,
nullable=True,
default=None)
customer_id = database.Column(database.VARCHAR(36),
nullable=True,
default='')
method_used_id = database.Column(TINYINT, nullable=False, default=1)
sourced_from_agent_id = database.Column(database.Integer,
nullable=True,
default=None)
given_to = database.Column(database.Enum('ABI',
'ACTION',
'BECK',
'GREEN',
'INTER',
'MCRI',
'NERF',
'P-USA',
'PROD',
'UNRES',
'VIDEO',
'TBD',
'SUPPORT',
native_enum=False),
default='ACTION',
nullable=False)
recurring_subscription_id = database.Column(database.VARCHAR(32),
nullable=True,
default=None)
transactions = database.relationship(
'TransactionModel',
order_by='desc( TransactionModel.date_in_utc )',
foreign_keys=[TransactionModel.gift_id],
primaryjoin='GiftModel.id == TransactionModel.gift_id')
@hybrid_property
def date_in_utc(self):
"""Place latest transaction date_in_utc on the Gift."""
if self.transactions:
return self.transactions[0].date_in_utc
return None
@hybrid_property
def status(self):
"""Place latest transaction status on the Gift."""
if self.transactions:
return self.transactions[0].status
return None
@hybrid_property
def gross_gift_amount(self):
"""Place latest transaction gross_gift_amount on the Gift."""
if self.transactions:
return self.transactions[0].gross_gift_amount
return None
class CampaignModel(database.Model):
"""A campaign model to persist ."""
__tablename__ = 'campaign'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
name = database.Column(database.VARCHAR(80), nullable=True)
description = database.Column(database.VARCHAR(80), nullable=True)
date_from_utc = database.Column(database.DateTime, nullable=True)
date_to_utc = database.Column(database.DateTime, nullable=True)
message = database.Column(database.TEXT(), nullable=True)
photo_type = database.Column(database.VARCHAR(80), nullable=True)
background = database.Column(database.Integer, nullable=True)
video_name = database.Column(database.VARCHAR(80), nullable=True)
video_url = database.Column(database.VARCHAR(80), nullable=True)
is_active = database.Column(database.Integer, nullable=True, default=1)
is_default = database.Column(database.Integer, nullable=True, default=0)
class UltsysUserModel( database.Model ):
"""A user model for testing.
The Ultsys user model is part of the test database and allows the behavior of the Ultsys user endpoints to be
mocked more thoroughly.
"""
__tablename__ = 'user'
ID = database.Column( database.Integer, primary_key=True, autoincrement=True, nullable=False )
email = database.Column( database.VARCHAR( 255 ), nullable=False )
firstname = database.Column( database.VARCHAR( 64 ), nullable=True, default='' )
lastname = database.Column( database.VARCHAR( 64 ), nullable=True, default=None )
address = database.Column( database.VARCHAR( 255 ), nullable=True, default=None )
state = database.Column( database.VARCHAR( 2 ), nullable=True, default=None )
city = database.Column( database.VARCHAR( 64 ), nullable=True, default=None )
zip = database.Column( database.VARCHAR( 5 ), nullable=True, default=None )
phone = database.Column( database.VARCHAR( 16 ), nullable=True, default=None )
donation_prior_amount = database.Column( database.VARCHAR( 255 ), nullable=True, default=None )
donation_sum = database.Column( database.VARCHAR( 255 ), nullable=True, default=None )
donation_time = database.Column( database.DateTime, nullable=True )
uid = database.Column( database.Integer, nullable=True )
class TransactionModel( database.Model ):
"""A general transaction model to include Braintree and other donations."""
__tablename__ = 'transaction'
id = database.Column( database.Integer, primary_key=True, autoincrement=True, nullable=False )
gift_id = database.Column( database.Integer, nullable=False )
date_in_utc = database.Column( database.DateTime, nullable=False )
receipt_sent_in_utc = database.Column( database.DateTime, nullable=True )
enacted_by_agent_id = database.Column( database.Integer, nullable=True, default=None )
type = database.Column(
database.Enum(
'Gift', 'Correction', 'Refund', 'Deposit to Bank', 'Bounced', 'Void', 'Dispute', 'Note',
'Fine',
native_enum=False
),
default='Gift',
nullable=False
)
status = database.Column(
database.Enum(
'Accepted', 'Completed', 'Declined', 'Denied', 'Failed', 'Forced', 'Lost', 'Refused', 'Requested',
'Won', 'Thank You Sent', native_enum=False
),
default='Accepted',
nullable=True
)
reference_number = database.Column( database.VARCHAR( 32 ), nullable=True, default='' )
gross_gift_amount = database.Column( database.DECIMAL( 10, 2 ), nullable=False )
fee = database.Column( database.DECIMAL( 8, 2 ), nullable=False )
notes = database.Column( database.Text, nullable=True )
gift = database.relationship(
'GiftModel',
foreign_keys=[ gift_id ],
primaryjoin='TransactionModel.gift_id == GiftModel.id',
uselist=False
)
@hybrid_property
def gift_searchable_id( self ):
"""Add gift_searchable_id property to Transaction object"""
if self.gift:
return self.gift.searchable_id
return None
class UnresolvedPaypalETLTransactionModel(database.Model):
"""In the PayPal CSV upload there are a class of transactions that cannot be resolved."""
__tablename__ = 'unresolved_paypal_etl_transaction'
id = database.Column(database.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
enacted_by_agent_id = database.Column(database.Integer, nullable=True)
date = database.Column(database.VARCHAR(20), nullable=True, default=None)
time = database.Column(database.VARCHAR(20), nullable=True, default=None)
time_zone = database.Column(database.VARCHAR(5),
nullable=True,
default=None)
name = database.Column(database.VARCHAR(255), nullable=True, default=None)
type = database.Column(database.VARCHAR(255), nullable=True, default=None)
status = database.Column(database.VARCHAR(64), nullable=True, default=None)
subject = database.Column(database.VARCHAR(255),
nullable=True,
default=None)
gross = database.Column(database.VARCHAR(64), nullable=True, default=None)
fee = database.Column(database.VARCHAR(64), nullable=True, default=None)
from_email_address = database.Column(database.VARCHAR(255),
nullable=True,
default=None)
to_email_address = database.Column(database.VARCHAR(255),
nullable=True,
default=None)
transaction_id = database.Column(database.VARCHAR(255),
nullable=True,
default=None)
reference_txn_id = database.Column(database.VARCHAR(255),
nullable=True,
default=None)
class QueuedDonorModel( database.Model ):
"""If a donor cannot be confidently associated with an existing user cage them."""
__tablename__ = 'queued_donor'
id = database.Column( database.Integer, primary_key=True, autoincrement=True, nullable=False )
gift_id = database.Column( database.Integer, nullable=True )
gift_searchable_id = database.Column( BinaryUUID, nullable=True )
campaign_id = database.Column( database.Integer, nullable=True, default=None )
customer_id = database.Column( database.VARCHAR( 36 ), nullable=True, default='' )
user_email_address = database.Column( database.VARCHAR( 255 ), nullable=False, default=None )
user_first_name = database.Column( database.VARCHAR( 64 ), nullable=True, default='' )
user_last_name = database.Column( database.VARCHAR( 64 ), nullable=True, default=None )
user_address = database.Column( database.VARCHAR( 255 ), nullable=True, default=None )
user_state = database.Column( database.CHAR( 2 ), nullable=True, default=None )
user_city = database.Column( database.VARCHAR( 64 ), nullable=True, default=None )
user_zipcode = database.Column( database.VARCHAR( 5 ), nullable=True, default=None )
user_phone_number = database.Column( database.BigInteger, nullable=True, default=0 )
times_viewed = database.Column( database.Integer, nullable=True )
