class EmailQueueTable(BaseTable):
"""
Works as a poor-man's queue for emailing.
"""
__tablename__ = 'email_queue'
email_to = db.Column(db.String(128), nullable=False)
email_from = db.Column(db.String(128), nullable=False)
subject = db.Column(db.TEXT, nullable=False)
body = db.Column(db.String(256), nullable=False)
def __init__(self, email_to, email_from, subject, body):
super().__init__()
self.email_to = email_to
self.email_from = email_from
self.subject = subject
self.body = body
def __repr__(self):
return 'To {0} from {1} w/ subject {2}'.format(
self.email_to,
self.email_from,
self.subject,
)
class SubscriptionTable(BaseTable):
"""
Houses storing information about a customer account.
"""
__tablename__ = 'braintree_subscription'
braintree_sub_id = db.Column(db.String, nullable=False)
plan_id = db.Column(db.String, nullable=False, default='Basic0x01')
date_started = db.Column(db.DateTime, nullable=False)
date_ended = db.Column(db.DateTime, nullable=True)
user_id = db.Column(
db.String(36),
db.ForeignKey('users.public_id'),
nullable=False,
)
braintree_customer_id = db.Column(
db.String(36),
db.ForeignKey('braintree_customer.public_id'),
nullable=False,
)
# House keeping stuff.
is_deleted = db.Column(db.BOOLEAN, nullable=False, default=False)
def __init__(self,
bt_customer_id,
user_id,
sub_id,
*,
plan_id='Basic0x01'):
super().__init__()
self.braintree_customer_id = bt_customer_id
self.braintree_sub_id = sub_id
self.user_id = user_id
self.plan_id = plan_id
self.date_started = datetime.utcnow()
def __repr__(self):
return 'User: {0} -- PlanID: {1} -- CustomerID: {2}'.format(
self.user_id,
self.plan_id,
self.braintree_customer_id,
)
class ScheduleTable(BaseTable):
"""
Houses the schedules of users.
"""
__tablename__ = 'schedules'
# Foreign keys.
user_id = db.Column(
db.String(36),
db.ForeignKey('users.public_id'),
nullable=False
)
# Actual schedule stuff.
utc_duration = db.Column(TSRANGE, nullable=False)
local_duration = db.Column(TSTZRANGE, nullable=False)
day_number = db.Column(db.SmallInteger, nullable=False)
month_number = db.Column(db.SmallInteger, nullable=False)
# tz stuff
local_tz = db.Column(db.String, nullable=False)
ExcludeConstraint(('utc_duration', '&&'))
ExcludeConstraint(('local_duration', '&&'))
@property
def local_tz_open(self): return self.local_duration.lower
@property
def local_tz_end(self): return self.local_duration.upper
@property
def utc_open(self): return self.utc_duration.lower
@property
def utc_end(self): return self.utc_duration.upper
def __init__(self, open_date, end_date, user_id, local_tz):
super().__init__()
self.utc_duration = DateTimeRange(open_date, end_date)
self.local_duration = DateTimeTZRange(
self._localize(self.utc_duration.lower, local_tz),
self._localize(self.utc_duration.upper, local_tz),
)
self.day_number = open_date.day
self.month_number = open_date.month
self.local_tz = local_tz
self.user_id = str(user_id)
def __repr__(self):
return 'Open: {0} -> End: {1} -- For User: {2}'.format(
self.utc_open,
self.utc_end,
self.user_id
)
class CustomerTable(BaseTable):
"""
Houses storing information about a customer account.
"""
__tablename__ = 'braintree_customer'
braintree_customer_id = db.Column(db.String, nullable=False, unique=True)
credit_card_token = db.Column(db.String, nullable=False, unique=True)
first_name = db.Column(db.String(64), nullable=False)
last_name = db.Column(db.String(64), nullable=False)
user_id = db.Column(
db.String(36),
db.ForeignKey('users.public_id'),
nullable=False,
)
# House keeping stuff.
is_default = db.Column(db.BOOLEAN, nullable=False, default=False)
is_deleted = db.Column(db.BOOLEAN, nullable=False, default=False)
def __init__(self,
bt_customer_id,
cc_token,
first_name,
last_name,
user_id,
*,
is_default=False):
super().__init__()
self.braintree_customer_id = bt_customer_id
self.credit_card_token = cc_token
self.first_name = first_name
self.last_name = last_name
self.user_id = user_id
self.is_default = is_default
def __repr__(self):
return 'User: {0} -- Default: {1} -- CustomerID: {2}'.format(
self.user_id,
self.is_default,
self.braintree_customer_id,
)
class BaseTable(db.Model):
"""Base table which all tables should inherit from."""
__abstract__ = True
id = db.Column(
db.Integer,
autoincrement=True,
primary_key=True,
nullable=False,
unique=True
)
public_id = db.Column(
db.String(36),
unique=True,
nullable=False,
index=True
)
created_at = db.Column(
db.TIMESTAMP,
nullable=False,
default=datetime.utcnow()
)
def __init__(self):
self.public_id = str(uuid.uuid4())
def _localize(self, time, local_tz):
"""
Handles localization for times.
:param datetime time: The time to localize
:param str local_tz: The timezone to use
:rtype: datetime
:return: The localized datetime object.
"""
try:
return pytz.utc.localize(time).astimezone(pytz.timezone(local_tz))
except ValueError:
return time.astimezone(pytz.timezone(local_tz))
class SubmerchantTable(BaseTable):
"""
Houses storing information relevant for submerchants.
"""
__tablename__ = 'braintree_sub_merchant'
master_merchant_id = db.Column(
db.Integer, db.ForeignKey('braintree_master_merchant.id'))
user_id = db.Column(
db.String(36),
db.ForeignKey('users.public_id'),
nullable=False,
)
# House keeping stuff.
is_deleted = db.Column(db.BOOLEAN, nullable=False, default=False)
is_approved = db.Column(db.BOOLEAN, nullable=False, default=False)
is_rejected = db.Column(db.Boolean, nullable=False, default=False)
# Actual shit being put into braintree upon submerchant account creation.
braintree_account_id = db.Column(db.String(24), nullable=False)
service_fee_percent = db.Column(db.DECIMAL, nullable=False, default=.025)
# Individual
first_name = db.Column(db.String(64), nullable=False)
last_name = db.Column(db.String(64), nullable=False)
email = db.Column(db.String(64), nullable=False)
date_of_birth = db.Column(db.DateTime, nullable=False)
address_street_address = db.Column(db.String(128), nullable=False)
address_locality = db.Column(db.String(32), nullable=False)
address_region = db.Column(db.String(32), nullable=False)
address_zip = db.Column(db.String(12), nullable=False)
# Business stuff. Only required if user is registering as a business
register_as_business = db.Column(db.Boolean, nullable=False, default=False)
legal_name = db.Column(db.String(64))
# NOTE: We do NOT store the tax_id.
dba_name = db.Column(db.String(64))
bus_address_street_address = db.Column(db.String(128))
bus_address_locality = db.Column(db.String(32))
bus_address_region = db.Column(db.String(32))
bus_address_zip = db.Column(db.String(12))
def __init__(self,
user_id,
account_id,
first_name,
last_name,
email,
date_of_birth,
address_street_address,
address_locality,
address_region,
address_zip,
register_as_business=False,
legal_name=None,
dba_name=None,
bus_address_street_address=None,
bus_address_locality=None,
bus_address_region=None,
bus_address_zip=None):
super().__init__()
self.user_id = user_id
self.braintree_account_id = account_id
self.first_name = first_name
self.last_name = last_name
self.email = email
self.date_of_birth = date_of_birth
self.address_street_address = address_street_address
self.address_locality = address_locality
self.address_region = address_region
self.address_zip = address_zip
if register_as_business:
self.register_as_business = register_as_business
self.legal_name = legal_name
self.dba_name = dba_name
self.bus_address_street_address = bus_address_street_address
self.bus_address_locality = bus_address_locality
self.bus_address_region = bus_address_region
self.bus_address_zip = bus_address_zip
def __repr__(self):
return 'Account ID: {0} -- User Public ID: {1}'.format(
self.braintree_account_id, self.user_id)
class UserTable(BaseTable):
"""
Houses the DB definition of the users table.
"""
__tablename__ = 'users'
email = db.Column(db.String, nullable=False, unique=True)
username = db.Column(db.String(32), nullable=False, unique=True)
password = db.Column(db.Binary(64), nullable=False)
# House keeping stuff.
is_deleted = db.Column(db.BOOLEAN, nullable=False)
# user info
local_tz = db.Column(db.String(32), nullable=False)
# Schedule and event stuff.
is_premium = db.Column(db.BOOLEAN, nullable=False, default=False)
five_min_price = db.Column(db.DECIMAL, nullable=True)
fifteen_min_price = db.Column(db.DECIMAL, nullable=True)
thirty_min_price = db.Column(db.DECIMAL, nullable=True)
sixty_min_price = db.Column(db.DECIMAL, nullable=True)
verify_token = db.Column(db.String(36),
unique=True,
nullable=True,
index=True)
reset_token = db.Column(db.String(36),
unique=True,
nullable=True,
index=True)
is_validated = db.Column(db.BOOLEAN, nullable=False, default=False)
def compare_password(self, plaintext_password):
"""
Compares a user-input password to the stored hash.
:param str plaintext_password: The password that the user put in.
:rtype: bool
:return: True if valid password--False otherwise.
"""
if isinstance(self.password, bytes):
return bcrypt.checkpw(
plaintext_password.encode('utf-8'),
self.password.decode('utf-8').encode('utf-8'))
else:
return bcrypt.checkpw(plaintext_password.encode('utf-8'),
self.password.encode('utf-8'))
def __init__(self, email, plaintext_password, local_tz, username):
super().__init__()
self.email = email
self.is_deleted = False
self.password = self._bcrypt_password(plaintext_password)
self.username = username
if local_tz in pytz.all_timezones:
self.local_tz = local_tz
else:
raise ModelException(
'Invalid selected timezone: {0}'.format(local_tz))
self.verify_token = str(uuid.uuid4())
def __repr__(self):
return "Email: {0} - Deleted: {1} - GUID: {2}".format(
self.email, self.is_deleted, self.public_id)
@staticmethod
def _bcrypt_password(plaintext_password, work_factor=10):
"""
Bcrypt hashes a password
:param str plaintext_password: The password to hash.
:rtype: str
:return: The bcrypt hash of the password.
"""
return bcrypt.hashpw(plaintext_password.encode('utf-8'),
bcrypt.gensalt(work_factor, b'2b'))
@staticmethod
def _bcrypt_compare(plaintext, stored_password):
"""
Handles comparing a password to the stored password.
:param str plaintext:
:param str stored_password: The password currently stored for the user.
:return:
"""
return bcrypt.checkpw(plaintext.encode('utf-8'), stored_password)
def __add__(self, submerchant):
if not isinstance(submerchant, SubmerchantTable):
raise SQLException(
'Invalid submerchant passed in: {0}'.format(submerchant))
self.has_deposit_account = True
self.is_approved = submerchant.is_approved
self.is_rejected = submerchant.is_rejected
self.service_fee_percent = submerchant.service_fee_percent
return self
class AddressTable(BaseTable):
"""
Houses the schedules of contact form submissions.
"""
__tablename__ = 'addresses'
first_name = db.Column(db.String, nullable=False)
last_name = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False,
index=True)
street_address = db.Column(db.String, nullable=False)
extended_address = db.Column(db.String, nullable=True)
# City
locality = db.Column(db.String, nullable=False)
# State
region = db.Column(db.String, nullable=False)
postal_code = db.Column(db.String, nullable=False)
country_code_alpha2 = db.Column(db.String(2), nullable=False)
is_default = db.Column(db.Boolean, nullable=False, default=False)
is_deleted = db.Column(db.Boolean, nullable=False, default=False)
# Indexes
db.Index('idx_default_addresses', is_default)
db.Index('idx_locality', locality)
db.Index('idx_region', region)
db.Index('idx_postal_code', postal_code)
db.Index('idx_country_code', country_code_alpha2)
def __init__(self,
user_id,
first_name,
last_name,
street_address,
locality,
region,
postal_code,
country_code_alpha2,
is_default=False,
extended_address=None):
super().__init__()
try:
self.user_id = UserDAO().get(user_id).id
except DAOException as e:
logging.error(
'Failed to get user by pub ID {0} w/ exc of {1}'.format(
user_id,
e,
))
raise TableException('Failed to find requested user.')
except AttributeError as e:
logging.error('Requested user ({0}) does not exist when '
'creating new address.'.format(user_id, ))
raise TableException(e)
self.first_name = first_name
self.last_name = last_name
self.street_address = street_address
self.locality = locality
self.region = region
self.postal_code = postal_code
self.country_code_alpha2 = country_code_alpha2
self.is_default = is_default
if extended_address is not None and extended_address != '':
self.extended_address = extended_address
def __repr__(self):
return """
First name: {0} Last name: {1} Default: {2} Locality: {3}
Region: {4} Country2: {5}
""".format(
self.first_name,
self.last_name,
self.is_default,
self.locality,
self.region,
self.country_code_alpha2,
)
class EventTable(BaseTable):
"""
Houses the schedules of users.
"""
__tablename__ = 'events'
# Foreign keys.
scheduling_user_id = db.Column(db.String(36),
db.ForeignKey('users.public_id'),
nullable=False,
index=True)
scheduled_user_id = db.Column(db.String(36),
db.ForeignKey('users.public_id'),
nullable=False,
index=True)
utc_duration = db.Column(TSTZRANGE, nullable=False)
scheduled_tz_duration = db.Column(TSTZRANGE, nullable=False)
scheduling_tz_duration = db.Column(TSTZRANGE, nullable=False)
day_number = db.Column(db.SmallInteger, nullable=False)
month_number = db.Column(db.SmallInteger, nullable=False)
duration = db.Column(db.Integer, nullable=False)
total_price = db.Column(db.DECIMAL, nullable=False)
service_fee = db.Column(db.DECIMAL, nullable=False)
# TODO(ian): Mark this with a payment transaction ID.
transaction_id = db.Column(db.Integer, nullable=True)
notes = db.Column(db.String(512), nullable=True)
ExcludeConstraint(('utc_duration', '&&'))
ExcludeConstraint(('scheduled_tz_duration', '&&'))
ExcludeConstraint(('scheduling_tz_duration', '&&'))
@property
def utc_start(self):
return self.utc_duration.lower
@property
def utc_end(self):
return self.utc_duration.upper
@property
def scheduled_tz_start(self):
return self.scheduled_tz_duration.lower
@property
def scheduled_tz_end(self):
return self.scheduled_tz_duration.upper
@property
def scheduling_tz_start(self):
return self.scheduling_tz_duration.lower
@property
def scheduling_tz_end(self):
return self.scheduling_tz_duration.upper
def __init__(self,
start_time,
end_time,
scheduling,
scheduled,
notes=None):
super().__init__()
scheduling_user_info = db.session.query(User).filter_by(
public_id=scheduling).first()
scheduled_user_info = db.session.query(User).filter_by(
public_id=scheduled).first()
self.utc_duration = DateTimeTZRange(start_time, end_time)
self._set_duration_for_user(scheduling_user_info, is_scheduling=True)
self._set_duration_for_user(scheduled_user_info, is_scheduling=False)
if scheduling_user_info is None or scheduled_user_info is None:
raise ModelException('Invalid requested users.')
self.scheduling_user_id = str(scheduling)
self.scheduled_user_id = str(scheduled)
self.day_number = start_time.day
self.month_number = self.utc_duration.lower.month
self.duration = ((end_time - start_time).seconds // 60)
if self.duration != 60:
if self.duration >= 60 and self.duration % 60 == 0:
pass
else:
self.duration %= 60
self.total_price = self.calculate_total_price(
self.duration,
scheduled_user_info,
)
submerchant_info = db.session.query(SubmerchantTable).filter_by(
user_id=scheduled).first()
self.service_fee = self.calculate_service_fee(submerchant_info)
if notes is not None:
self.notes = notes
def __repr__(self):
return 'Start: {0} - End: {1} - Duration (minutes): {2} - Price {3}'.\
format(
self.utc_start,
self.utc_end,
self.duration,
self.total_price
)
def calculate_total_price(self, duration, scheduled_user):
"""
Calculates the total price for the event.
:param int duration:
:param UserTable scheduled_user: The user which si being scheduled.
:rtype: float
:return: The total price for the duration.
"""
if scheduled_user is None:
raise TableException("Invalid user to schedule.")
if scheduled_user.is_premium:
if duration not in [5, 15, 30, 45, 60]:
if duration >= 60 and duration % 60 == 0:
pass
else:
raise TableException(
"Invalid event duration. Premium users can only accept"
" durations of 5, 15, 30, or 60 minutes.")
else:
if duration not in [60]:
if duration >= 60 and duration % 60 == 0:
pass
else:
raise TableException(
"Invalid event duration. Non-premium users can only "
"accept durations of 60 minutes.")
if scheduled_user.is_premium:
price_lookup = {
5: scheduled_user.five_min_price,
15: scheduled_user.fifteen_min_price,
30: scheduled_user.thirty_min_price,
65: scheduled_user.sixty_min_price,
}
return price_lookup[duration] + decimal.Decimal(price_lookup[
duration]) * decimal.Decimal(0.026) + decimal.Decimal(0.2)
else:
if scheduled_user.sixty_min_price is None:
logging.error('Attempted to create event for user {0}'
', but failed because no sixty_min_price'.format(
scheduled_user.public_id, ))
raise TableException(
'Invalid user to be scheduled. User has no price set'
'for 60 minutes.')
return scheduled_user.sixty_min_price + decimal.Decimal(
scheduled_user.sixty_min_price) * decimal.Decimal(
0.026) + decimal.Decimal(0.2)
def calculate_service_fee(self, submerchant):
return self.total_price * submerchant.service_fee_percent
def _set_duration_for_user(self, user_info, is_scheduling=True):
localized_start = self._localize(self.utc_duration.lower,
user_info.local_tz)
localized_end = self._localize(self.utc_duration.upper,
user_info.local_tz)
if is_scheduling:
self.scheduling_tz_duration = DateTimeTZRange(
localized_start,
localized_end,
)
else:
self.scheduled_tz_duration = DateTimeTZRange(
localized_start,
localized_end,
)