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 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 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 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 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 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 ""