class UsersRoles(TimeTrackedModel):
__tablename__ = 'users_roles'
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(
db.Integer(),
db.ForeignKey('users.id', onupdate='cascade', ondelete='cascade'))
role_id = db.Column(db.Integer(),
db.ForeignKey('roles.id', ondelete='cascade'))
class Role(TimeTrackedModel):
__tablename__ = 'roles'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False,
unique=True) # for @roles_accepted()
def __str__(self):
return self.name
class Access(db.Model):
__tablename__ = 'accesses'
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id', onupdate="cascade"),
nullable=False)
user = db.relationship('User', back_populates='accesses')
account_id = db.Column(db.Integer(),
db.ForeignKey('accounts.id', onupdate="cascade"),
nullable=False)
account = db.relationship('Account', back_populates='accesses')
def __init__(self, user_id=None, account_id=None):
self.user_id = user_id
self.account_id = account_id
class Transfer(TimeTrackedModel):
"""
Represents a transfer of money TO or FROM a Bank Account.
Transferring TO:
- payment to vendors
- counter_party becomes "origin"
Transferring FROM:
- refund from vendors
- counter_party becomes "destination"
"""
__tablename__ = 'transfers'
id = db.Column(db.Integer, primary_key=True)
bank_account_id = db.Column(db.Integer(), db.ForeignKey('bank_accounts.id', onupdate="cascade"))
bank_account = db.relationship('BankAccount', back_populates='transfers')
counter_party = db.Column(db.String(96))
gross = db.Column(db.Float(), nullable=False)
net = db.Column(db.Float())
currency = db.Column(db.String(12))
exchange_rate = db.Column(db.Float(), nullable=False)
is_refund = db.Column(db.Boolean, default=False)
notes = db.Column(db.Text())
date = db.Column(db.Date(), nullable=False)
@property
def suggested_net(self):
pcts = set()
for vendor in self.bank_account.vendors:
pcts.add(vendor.service_fee)
if not pcts:
return "Not Set"
pcts = sorted(list(pcts))
ret = Markup('<ul>')
for pct in pcts:
ret += Markup('<li>')
amt = self.gross * (100-pct) / 100.0
ret += '%.2f (%s%%)' % (amt, pct)
ret += Markup('</li>')
ret += Markup('</ul>')
return ret
class Permission(TimeTrackedModel):
__tablename__ = 'permissions'
__table_args__ = (db.UniqueConstraint('vendor_id',
'user_id',
name='permission_vendor_user'), )
id = db.Column(db.Integer, primary_key=True)
vendor_id = db.Column(db.Integer(),
db.ForeignKey('vendors.id', onupdate="cascade"),
nullable=False)
vendor = db.relationship('Vendor', back_populates='permissions')
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id', onupdate="cascade"),
nullable=False)
user = db.relationship('User',
foreign_keys=[user_id],
back_populates='permissions')
created_by_id = db.Column(db.Integer(),
db.ForeignKey('users.id', onupdate="cascade"))
created_by = db.relationship('User', foreign_keys=[created_by_id])
notes = db.Column(db.Text())
def __str__(self):
return '%s - %s' % (self.vendor, self.user)
@classmethod
def check(self, vendor, user):
"""https://stackoverflow.com/questions/32938475/flask-sqlalchemy-check-if-row-exists-in-table
"""
if vendor and user:
return db.session.query(db.exists().where(
and_(Permission.vendor_id == vendor.id,
Permission.user_id == user.id))).scalar()
class Account(db.Model):
__tablename__ = 'accounts'
__versioned__ = {'exclude': ['VPSs']}
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.Enum(AccountStatus, name='account_status'),
nullable=False,
default=AccountStatus.UNINITIALIZED)
adwords_id = db.Column(db.String(20), nullable=False, unique=True)
nickname = db.Column(db.String(48))
account_budget = db.Column(db.Float())
account_budget_override = db.Column(db.Float())
remaining_account_budget = db.Column(db.Float())
remaining_account_budget_override = db.Column(db.Float())
daily_budget = db.Column(db.Float())
currency = db.Column(db.String(12))
exchange_rate = db.Column(db.Float())
is_unlimited = db.Column(db.Boolean(),
server_default=expression.false(),
default=False)
login = db.Column(db.String(48))
password = db.Column(db.String(48))
batch = db.Column(db.String(48))
country = db.Column(db.String(24))
external_comment = db.Column(db.Text())
internal_comment = db.Column(db.Text())
auto_tag_on = db.Column(db.Boolean(), default=False)
VPSs = db.relationship("Vps",
secondary=association_table,
back_populates="accounts")
client_id = db.Column(db.Integer(),
db.ForeignKey('users.id', onupdate="cascade"))
client = db.relationship('User', back_populates='accounts')
vendor_id = db.Column(db.Integer(),
db.ForeignKey('vendors.id', onupdate="cascade"))
vendor = db.relationship('Vendor', back_populates='accounts')
accesses = db.relationship('Access', back_populates='account')
# We still need this for sorting
updated_at = db.Column(db.DateTime(timezone=True),
server_default=func.now(),
onupdate=func.now())
last_visited_by_eve = db.Column(db.DateTime(timezone=True))
def __str__(self):
if self.adwords_id:
return self.adwords_id
return 'Empty Account'
def __repr__(self):
return "%s(%s)" % (self.__class__.__name__, self.id)
def get_account_budget(self):
if self.account_budget_override is not None:
return self.account_budget_override
return self.account_budget
def get_remaining_account_budget(self):
if self.remaining_account_budget_override is not None:
return self.remaining_account_budget_override
return self.remaining_account_budget
@hybrid_property
def days_left(self):
"""Returns the number of days left before the budget exhausts.
Finance dept sorts by days_left asc to alert us so that they can alert us
which accounts that are running out of money. For accounts with budget=0
or empty, we do not want to see them there so we will return a large
number, i.e. 99.
"""
if no_none(self.get_remaining_account_budget(), self.daily_budget) \
and self.daily_budget:
return math.floor(self.get_remaining_account_budget() /
self.daily_budget)
else:
return 99
@days_left.expression
def days_left(cls):
return sqlalchemy.func.floor(cls.remaining_account_budget /
(cls.daily_budget + 1)) # hack
@property
def percentage_spent(self):
ab, rab = self.get_account_budget(), self.get_remaining_account_budget(
)
if no_none(ab, rab) and ab:
return 100 * (ab - rab) / ab
@property
def spent(self):
ab, rab = self.get_account_budget(), self.get_remaining_account_budget(
)
if no_none(ab, rab):
return ab - rab
@hybrid_property
def spent_in_hkd(self):
if no_none(self.spent, self.exchange_rate):
return self.spent * self.exchange_rate
return 0
@spent_in_hkd.expression
def spent_in_hkd(cls):
return (cls.account_budget -
cls.remaining_account_budget) * cls.exchange_rate
@property
def daily_budget_in_hkd(self):
if no_none(self.daily_budget, self.exchange_rate):
return self.daily_budget * self.exchange_rate
@property
def remaining_in_hkd(self):
if no_none(self.get_remaining_account_budget(), self.exchange_rate):
return self.get_remaining_account_budget() * self.exchange_rate
@property
@cache.memoize()
def days_to_topup(self):
"""Rather than using inline model form, just reflect this
"""
return self.vendor.days_to_topup
@property
@cache.memoize()
def suspended_on(self):
"""Returns a dt for when suspension occurs. If account is not suspended return None.
"""
for v in self.versions:
if v.status == AccountStatus.SUSPENDED:
return v.updated_at
@property
def clients_allowed(self):
"""Returns a string which shows which clients are allowed.
Not memoizing here because this could be used by other widgets. So it makes sense to use
cache_helper as we are sharing this between different classes.
"""
key = 'clients_allowed-%s' % self.vendor_id
if self.vendor:
ret = cache.get(key)
if not ret:
pems = Permission.query.filter(
Permission.vendor_id == self.vendor_id).order_by(
Permission.user_id)
if pems.count():
ret = ranges([p.user_id for p in pems])
else:
ret = lazy_gettext("Vendor permissions not defined.")
cache.set(key, ret)
return ret
return lazy_gettext("Vendor is empty.")
@property
@cache.memoize()
def VPSs_jinja(self):
"""Show only AWS VPSs if there are multiple VPSs.
"""
VPSs = self.VPSs
has_aws_vps = False
pos_aws_vps = 0
for i, vps in enumerate(VPSs):
if vps.provider and vps.provider.upper().startswith('AWS'):
has_aws_vps = True
pos_aws_vps = i
break
if has_aws_vps:
return str(VPSs[pos_aws_vps])
else:
return ", ".join(sorted([str(v) for v in VPSs]))
@property
@cache.memoize()
def created_at(self):
"""Running this operation even after memoization has caused the request to take longer than
30 seconds. Hence, we will only execute this method on the relevant vendors.
"""
VENDORS = [40, 41, 42]
if self.vendor_id in VENDORS:
return self.versions.first().transaction.issued_at.strftime(
'%m/%d %H:%M')
return ""