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 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 BankAccount(TimeTrackedModel):
"""Represents a Bank Account owned by a Vendor.
"""
__tablename__ = 'bank_accounts'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
details = db.Column(db.Text())
vendors = db.relationship('Vendor', back_populates='bank_account')
transfers = db.relationship('Transfer', back_populates='bank_account')
def __str__(self):
return self.name
@property
def transfer_count(self):
url = url_for('transfer.index_view', flt1_0=self.name)
count = Transfer.query.filter(
Transfer.bank_account_id == self.id).count()
return Markup('<u><a href=%s>%s</a></u>' % (url, count))
@property
def total_sent_in_hkd(self):
total = 0
for transfer in Transfer.query.filter(
Transfer.bank_account_id == self.id):
if transfer.net and transfer.exchange_rate:
total += transfer.net * transfer.exchange_rate
return total
@property
def total_spent_in_hkd(self):
total = 0
for act in Account.query.filter(
Account.vendor_id.in_([v.id for v in self.vendors])):
total += act.spent_in_hkd
return total
@property
def total_remaining_in_hkd(self):
total = 0
for act in Account.query.filter(
Account.vendor_id.in_([v.id for v in self.vendors])):
if act.get_remaining_account_budget() and act.exchange_rate:
total += act.get_remaining_account_budget() * act.exchange_rate
return total
@property
def total_outstanding_in_hkd(self):
return self.total_sent_in_hkd - self.total_spent_in_hkd - self.total_remaining_in_hkd
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 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 User(TimeTrackedModel, UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(255), nullable=False, unique=True)
password = db.Column(db.String(255), nullable=False)
name = db.Column(db.String(255), nullable=False)
is_enabled = db.Column(db.Boolean(), nullable=False, default=False)
roles = db.relationship('Role',
secondary='users_roles',
backref=db.backref('users', lazy='dynamic'))
accounts = db.relationship('Account', back_populates='client')
permissions = db.relationship('Permission',
foreign_keys='Permission.user_id',
back_populates='user')
accesses = db.relationship('Access', back_populates='user')
budget_url = db.Column(db.String(1024))
def is_active(self):
return self.is_enabled
def __str__(self):
return self.username
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 Vps(TimeTrackedModel):
__tablename__ = 'vps'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(48), nullable=False)
instance_id = db.Column(db.String(48))
provider = db.Column(db.String(48), nullable=False)
country = db.Column(db.String(48), nullable=False)
ip_addr = db.Column(db.String(48))
is_deleted = db.Column(db.Boolean(), default=False)
login = db.Column(db.String(48), nullable=False)
password = db.Column(db.String(48), nullable=False)
api_key = db.Column(db.String(), default=generate_key)
api_secret = db.Column(db.String(), default=generate_secret)
accounts = db.relationship('Account',
secondary=association_table,
back_populates='VPSs')
def __str__(self):
return self.name
@property
def alive_count(self):
bad = 0
for account in self.accounts:
if account.status in DEAD:
bad += 1
return len(self.accounts) - bad
def _render_markup_list_view(self, is_alive):
"""Returns Markup for List View.
"""
tele = defaultdict(int)
for account in self.accounts:
if is_alive and account.status not in DEAD:
tele[account.status.value] += 1
elif not is_alive and account.status in DEAD:
tele[account.status.value] += 1
ret = ""
for status, count in tele.iteritems():
ret += '<div>%s (%s)</div>' % (status, count)
return Markup(ret)
@property
def alive_accounts(self):
return self._render_markup_list_view(True)
@property
def dead_accounts(self):
return self._render_markup_list_view(False)
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 ""
import sqlalchemy
from flask_admin.babel import gettext
from flask_babelex import lazy_gettext
from portal.cache import cache
from portal.models import db
from portal.permission.models import Permission
from portal.user import RolesEnum
from portal.utils import ranges
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import expression, func
u = lambda s: unicode(s, 'utf-8') # noqa
association_table = db.Table(
'account_vps',
db.Column('account_id', db.Integer, db.ForeignKey('accounts.id')),
db.Column('vps_id', db.Integer, db.ForeignKey('vps.id')))
class AccountStatus(enum.Enum):
UNINITIALIZED = 'uninitialized'
UNASSIGNED = 'unassigned'
RESERVED = 'reserved'
ATTENTION = 'attention'
APPEAL_REQUESTED = 'appeal_requested'
APPEAL_SUBMITTED = 'appeal_submitted'
ACTIVE = 'active'
DISAPPROVED = 'disapproved'
SUSPENDED = 'suspended'
ABANDONED = 'abandoned'