def balance(self, username):
"""
Return a list :
[ <balance in account currency>, <balance in preferred currency> ]
"""
balance = db.session.query(
db.func.sum(TransactionAccount.amount)
).filter(
TransactionAccount.account_id == self.id
).one()[0]
if balance:
balances = [ self.start_balance + balance ]
else:
balances = [ self.start_balance ]
balances.append(
helpers.rate(
username,
self.currency.isocode,
coremodels.User.query.options(
db.joinedload(coremodels.User.preferred_currency)
).get(
username
).preferred_currency.isocode
) * balances[0]
)
return balances
def http_rate(self, fromisocode, toisocode):
response = helpers.rate(self.username, fromisocode, toisocode)
if response:
return jsonify(
status=200,
response=response
)
else:
self.badrequest("Rate cannot be calculated")
def update(self, categoryid):
category = self.__own_category(categoryid)
if not category:
self.notfound(
'Nonexistent category cannot be modified (or you do not own it)')
if 'name' in self.args:
category.name = self.args['name']
if 'currency' in self.args:
currency = core.Currency.query.filter(
db.and_(
core.Currency.isocode == self.args['currency'],
db.or_(
core.Currency.owner_username == self.username,
core.Currency.owner_username == None
)
)
).first()
if currency:
rate = helpers.rate(
self.username,
category.currency.isocode,
currency.isocode
)
category.currency = currency
for tc in models.TransactionCategory.query.filter(
models.TransactionCategory.category == category
).all():
tc.category_amount = tc.category_amount * rate
if 'parent' in self.args:
if self.args['parent'] == 'NONE':
category.parent_id = None
else:
parent = self.__own_category(self.args['parent'])
if not parent:
self.badrequest("This parent category does not exist")
if category.contains_category(parent.id):
self.badrequest(
"The parent is already a child of this category")
if parent.id != category.parent_id:
allparents = set([parent.id, category.parent_id] + \
parent.all_parents_ids())
if category.parent_id:
allparents.update(category.parent.all_parents_ids())
for parentid in allparents:
if parentid:
self.add_to_response('categoriesbalance', parentid)
category.parent = parent
db.session.commit()
return category.as_dict(self.username)
def totalbalance(username):
accounts = models.Account.query.options(
db.joinedload(models.Account.currency)).join(
models.AccountOwner).filter(
models.AccountOwner.owner_username == username).all()
# Calculate the total balance, in the user's preferred currency
totalbalance = 0
totalcurrency = core.User.query.options(
db.joinedload(
core.User.preferred_currency)).get(username).preferred_currency
for account in accounts:
totalbalance += account.balance(username)[0] * \
helpers.rate(username,
account.currency.isocode,
totalcurrency.isocode)
return {'balance': totalbalance, 'currency': totalcurrency.isocode}
def balance(self, username):
"""
Return a list :
[ <balance in account currency>, <balance in preferred currency> ]
"""
balance = db.session.query(db.func.sum(
TransactionAccount.amount)).filter(
TransactionAccount.account_id == self.id).one()[0]
if balance:
balances = [self.start_balance + balance]
else:
balances = [self.start_balance]
balances.append(
helpers.rate(
username, self.currency.isocode,
coremodels.User.query.options(
db.joinedload(coremodels.User.preferred_currency)).get(
username).preferred_currency.isocode) * balances[0])
return balances
def update(self, categoryid):
category = self.__own_category(categoryid)
if not category:
self.notfound(
'Nonexistent category cannot be modified (or you do not own it)'
)
if 'name' in self.args:
category.name = self.args['name']
if 'currency' in self.args:
currency = core.Currency.query.filter(
db.and_(
core.Currency.isocode == self.args['currency'],
db.or_(core.Currency.owner_username == self.username,
core.Currency.owner_username == None))).first()
if currency:
rate = helpers.rate(self.username, category.currency.isocode,
currency.isocode)
category.currency = currency
for tc in models.TransactionCategory.query.filter(
models.TransactionCategory.category == category).all():
tc.category_amount = tc.category_amount * rate
if 'parent' in self.args:
if self.args['parent'] == 'NONE':
category.parent_id = None
else:
parent = self.__own_category(self.args['parent'])
if not parent:
self.badrequest("This parent category does not exist")
if category.contains_category(parent.id):
self.badrequest(
"The parent is already a child of this category")
if parent.id != category.parent_id:
allparents = set([parent.id, category.parent_id] + \
parent.all_parents_ids())
if category.parent_id:
allparents.update(category.parent.all_parents_ids())
for parentid in allparents:
if parentid:
self.add_to_response('categoriesbalance', parentid)
category.parent = parent
db.session.commit()
return category.as_dict(self.username)
def totalbalance(username):
accounts = models.Account.query.options(
db.joinedload(models.Account.currency)
).join(models.AccountOwner).filter(
models.AccountOwner.owner_username == username
).all()
# Calculate the total balance, in the user's preferred currency
totalbalance = 0
totalcurrency = core.User.query.options(
db.joinedload(core.User.preferred_currency)
).get(username).preferred_currency
for account in accounts:
totalbalance += account.balance(username)[0] * \
helpers.rate(username,
account.currency.isocode,
totalcurrency.isocode)
return {
'balance': totalbalance,
'currency': totalcurrency.isocode
}
def balance(self, username):
today = datetime.date.today()
balance = cache.get('categorybalance-{0}'.format(self.id))
if not balance:
balance = {'currency': self.currency.isocode}
balance['year'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)
).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
datetime.date(today.year, 1, 1),
datetime.date(today.year, 12, 31)
)
)
).one()[0] or 0
if today.month == 12:
lastdayofmonth = datetime.date(today.year, 12, 31)
else:
lastdayofmonth = datetime.date(today.year, today.month+1, 1) -\
datetime.timedelta(1)
balance['month'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)
).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
datetime.date(today.year, today.month, 1),
lastdayofmonth
)
)
).one()[0] or 0
firstdayofweek = today - datetime.timedelta(today.weekday())
lastdayofweek = today + datetime.timedelta(6-today.weekday())
balance['week'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)
).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
firstdayofweek,
lastdayofweek
)
)
).one()[0] or 0
balance['7days'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)
).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
today - datetime.timedelta(6),
today
)
)
).one()[0] or 0
balance['30days'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)
).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
today - datetime.timedelta(29),
today
)
)
).one()[0] or 0
# Cache the balance only for 5 seconds : it helps when listing
# multiple categories by reducing sql requests
cache.set('categorybalance-{0}'.format(self.id), balance, 5)
for child in self.children:
child_balance = child.balance(username)
rate = helpers.rate(username,
child_balance['currency'], self.currency.isocode)
balance['year'] = balance['year'] + child_balance['year'] * rate
balance['month'] = balance['month'] + child_balance['month'] * rate
balance['week'] = balance['week'] + child_balance['week'] * rate
balance['7days'] = balance['7days'] + child_balance['7days'] * rate
balance['30days'] = balance['30days'] +child_balance['30days']*rate
return balance
def balance(self, username):
today = datetime.date.today()
balance = cache.get('categorybalance-{0}'.format(self.id))
if not balance:
balance = {'currency': self.currency.isocode}
balance['year'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
datetime.date(today.year, 1, 1),
datetime.date(today.year, 12, 31)))).one()[0] or 0
if today.month == 12:
lastdayofmonth = datetime.date(today.year, 12, 31)
else:
lastdayofmonth = datetime.date(today.year, today.month+1, 1) -\
datetime.timedelta(1)
balance['month'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
datetime.date(today.year, today.month, 1),
lastdayofmonth))).one()[0] or 0
firstdayofweek = today - datetime.timedelta(today.weekday())
lastdayofweek = today + datetime.timedelta(6 - today.weekday())
balance['week'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(firstdayofweek,
lastdayofweek))).one()[0] or 0
balance['7days'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(today - datetime.timedelta(6),
today))).one()[0] or 0
balance['30days'] = db.session.query(
db.func.sum(TransactionCategory.category_amount)).filter(
db.and_(
TransactionCategory.category_id == self.id,
TransactionCategory.transaction_id == Transaction.id,
Transaction.date.between(
today - datetime.timedelta(29),
today))).one()[0] or 0
# Cache the balance only for 5 seconds : it helps when listing
# multiple categories by reducing sql requests
cache.set('categorybalance-{0}'.format(self.id), balance, 5)
for child in self.children:
child_balance = child.balance(username)
rate = helpers.rate(username, child_balance['currency'],
self.currency.isocode)
balance['year'] = balance['year'] + child_balance['year'] * rate
balance['month'] = balance['month'] + child_balance['month'] * rate
balance['week'] = balance['week'] + child_balance['week'] * rate
balance['7days'] = balance['7days'] + child_balance['7days'] * rate
balance[
'30days'] = balance['30days'] + child_balance['30days'] * rate
return balance
def create(username, wizard, locale, prefcurrency):
"""Create entries from wizard files"""
# /!\ Does not work in Python < 2.7
#
# (sections order is important)
########## Initialization
data = ConfigParser.RawConfigParser()
if wizard in ('basic', 'demo'):
try:
datafile = codecs.open(
os.path.join(config.WIZARD_DATA,'%s.basic'%locale),
'r', 'utf8'
)
data.readfp(datafile)
datafile.close()
except:
abort(400)
if wizard == 'demo':
try:
datafile = codecs.open(
os.path.join(config.WIZARD_DATA, '%s.demo'%locale),
'r', 'utf8'
)
data.readfp(datafile)
datafile.close()
except:
abort(400)
sections = data.sections()
if not sections:
return 200, 'OK'
try:
preferred_currency = core.Currency.query.filter(
db.and_(
core.Currency.isocode == prefcurrency,
core.Currency.owner_username == None
)
).one()
except:
abort(400)
today = datetime.date.today()
########## Helper functions
def subsections(name):
return [ i for i in sections if i.startswith(name) ]
########## Currency
currencies = { prefcurrency: preferred_currency }
for cur in subsections('currency-'):
symbol = data.get(cur, 'symbol')
currency = core.Currency(
owner_username = username,
isocode = symbol,
symbol = symbol,
name = data.get(cur, 'name'),
rate = data.get(cur, 'rate')
)
db.session.add(currency)
currencies[symbol] = currency
db.session.commit()
########## Account
accounts = {}
for acc in subsections('account-'):
if data.has_option(acc, 'currency'):
curname = data.get(acc, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(
core.Currency.owner_username == None,
core.Currency.isocode == curname
)
).one()
else:
curname = prefcurrency
account = transaction.Account(
name = data.get(acc, 'name'),
currency = currencies[curname],
start_balance = data.get(acc, 'balance')
)
db.session.add(account)
accounts[acc.split('-')[1]] = account
db.session.add(
transaction.AccountOwner(
account = account,
owner_username = username
)
)
db.session.commit()
########## Category
categories = {}
for cat in subsections('category-'):
if data.has_option(cat, 'currency'):
curname = data.get(cat, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(
core.Currency.owner_username == None,
core.Currency.isocode == curname
)
).one()
else:
curname = prefcurrency
category = transaction.Category(
owner_username = username,
currency = currencies[curname],
name = data.get(cat, 'name')
)
try:
category.parent = categories[data.get(cat, 'parent')]
except:
pass
db.session.add(category)
categories[cat.split('-')[1]] = category
db.session.commit()
########## Transaction
for tra in subsections('transaction-'):
if data.has_option(tra, 'currency'):
curname = data.get(tra, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(
core.Currency.owner_username == None,
core.Currency.isocode == curname
)
).one()
else:
curname = prefcurrency
currency = currencies[curname]
# Calculate date
year, month, day = data.get(tra, 'date').split('/')
# Month
if month:
if month[0] in ('-', '+'):
month = today.month + int(month)
else:
month = int(month)
else:
month = today.month
# Year
if year:
if year[0] in ('-', '+'):
year = today.year + int(year)
elif year == '?':
# If year = "?", point to the last passed year containing the
# defined month (either current year or previous year)
if month < today.month:
year = today.year
else:
year = today.year - 1
else:
year = int(year)
else:
year = today.year
# Check the month is not outside bounds, or correct the year
def month_outside_bounds(month, year):
if month < 1:
month = month + 12
year = year - 1
month, year = month_outside_bounds(month, year)
elif month > 12:
month = month - 12
year = year + 1
month, year = month_outside_bounds(month, year)
return month, year
month, year = month_outside_bounds(month, year)
# Day
if day:
if day[0] in ('-', '+'):
daydeltafromfirst = today.day + int(day) - 1
day = 1
else:
daydeltafromfirst = int(day) - 1
day = 1
else:
day = today.day
daydeltafromfirst = 0
transactiondate = datetime.date(year,month,day)
if daydeltafromfirst:
transactiondate = transactiondate + \
datetime.timedelta(days=daydeltafromfirst)
# Create transaction
trans = transaction.Transaction(
owner_username = username,
description = data.get(tra, 'description'),
amount = data.get(tra, 'amount'),
currency = currency,
date = transactiondate
)
try:
trans.original_description = data.get(tra, 'original_description')
except:
trans.original_description = data.get(tra, 'description')
db.session.add(trans)
# Links to accounts
for accountdata in data.get(tra, 'accounts').split():
accountdatatb = accountdata.split(':')
accountnum = accountdatatb[0]
if len(accountdatatb) > 1:
amount = accountdatatb[1]
else:
amount = trans.amount
account = accounts[accountnum]
accountamount = amount * helpers.rate(
username,
currency.isocode,
account.currency.isocode
)
db.session.add(
transaction.TransactionAccount(
transaction = trans,
account = account,
amount = accountamount
)
)
# Links to categories
for categorydata in data.get(tra, 'categories').split():
categorydatatb = categorydata.split(':')
categorynum = categorydatatb[0]
if len(categorydatatb) > 1:
amount = categorydatatb[1]
else:
amount = trans.amount
category = categories[categorynum]
categoryamount = amount * helpers.rate(
username,
currency.isocode,
category.currency.isocode
)
db.session.add(
transaction.TransactionCategory(
transaction = trans,
category = category,
transaction_amount = amount,
category_amount = categoryamount
)
)
db.session.commit()
########## OK, finished
return 200, 'OK'
def http_rate(self, fromisocode, toisocode):
response = helpers.rate(self.username, fromisocode, toisocode)
if response:
return jsonify(status=200, response=response)
else:
self.badrequest("Rate cannot be calculated")
def create(username, wizard, locale, prefcurrency):
"""Create entries from wizard files"""
# /!\ Does not work in Python < 2.7
#
# (sections order is important)
########## Initialization
data = ConfigParser.RawConfigParser()
if wizard in ('basic', 'demo'):
try:
datafile = codecs.open(
os.path.join(config.WIZARD_DATA, '%s.basic' % locale), 'r',
'utf8')
data.readfp(datafile)
datafile.close()
except:
abort(400)
if wizard == 'demo':
try:
datafile = codecs.open(
os.path.join(config.WIZARD_DATA, '%s.demo' % locale), 'r',
'utf8')
data.readfp(datafile)
datafile.close()
except:
abort(400)
sections = data.sections()
if not sections:
return 200, 'OK'
try:
preferred_currency = core.Currency.query.filter(
db.and_(core.Currency.isocode == prefcurrency,
core.Currency.owner_username == None)).one()
except:
abort(400)
today = datetime.date.today()
########## Helper functions
def subsections(name):
return [i for i in sections if i.startswith(name)]
########## Currency
currencies = {prefcurrency: preferred_currency}
for cur in subsections('currency-'):
symbol = data.get(cur, 'symbol')
currency = core.Currency(owner_username=username,
isocode=symbol,
symbol=symbol,
name=data.get(cur, 'name'),
rate=data.get(cur, 'rate'))
db.session.add(currency)
currencies[symbol] = currency
db.session.commit()
########## Account
accounts = {}
for acc in subsections('account-'):
if data.has_option(acc, 'currency'):
curname = data.get(acc, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(core.Currency.owner_username == None,
core.Currency.isocode == curname)).one()
else:
curname = prefcurrency
account = transaction.Account(name=data.get(acc, 'name'),
currency=currencies[curname],
start_balance=data.get(acc, 'balance'))
db.session.add(account)
accounts[acc.split('-')[1]] = account
db.session.add(
transaction.AccountOwner(account=account, owner_username=username))
db.session.commit()
########## Category
categories = {}
for cat in subsections('category-'):
if data.has_option(cat, 'currency'):
curname = data.get(cat, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(core.Currency.owner_username == None,
core.Currency.isocode == curname)).one()
else:
curname = prefcurrency
category = transaction.Category(owner_username=username,
currency=currencies[curname],
name=data.get(cat, 'name'))
try:
category.parent = categories[data.get(cat, 'parent')]
except:
pass
db.session.add(category)
categories[cat.split('-')[1]] = category
db.session.commit()
########## Transaction
for tra in subsections('transaction-'):
if data.has_option(tra, 'currency'):
curname = data.get(tra, 'currency')
if curname not in currencies:
currencies[curname] = core.Currency.query.filter(
db.and_(core.Currency.owner_username == None,
core.Currency.isocode == curname)).one()
else:
curname = prefcurrency
currency = currencies[curname]
# Calculate date
year, month, day = data.get(tra, 'date').split('/')
# Month
if month:
if month[0] in ('-', '+'):
month = today.month + int(month)
else:
month = int(month)
else:
month = today.month
# Year
if year:
if year[0] in ('-', '+'):
year = today.year + int(year)
elif year == '?':
# If year = "?", point to the last passed year containing the
# defined month (either current year or previous year)
if month < today.month:
year = today.year
else:
year = today.year - 1
else:
year = int(year)
else:
year = today.year
# Check the month is not outside bounds, or correct the year
def month_outside_bounds(month, year):
if month < 1:
month = month + 12
year = year - 1
month, year = month_outside_bounds(month, year)
elif month > 12:
month = month - 12
year = year + 1
month, year = month_outside_bounds(month, year)
return month, year
month, year = month_outside_bounds(month, year)
# Day
if day:
if day[0] in ('-', '+'):
daydeltafromfirst = today.day + int(day) - 1
day = 1
else:
daydeltafromfirst = int(day) - 1
day = 1
else:
day = today.day
daydeltafromfirst = 0
transactiondate = datetime.date(year, month, day)
if daydeltafromfirst:
transactiondate = transactiondate + \
datetime.timedelta(days=daydeltafromfirst)
# Create transaction
trans = transaction.Transaction(owner_username=username,
description=data.get(
tra, 'description'),
amount=data.get(tra, 'amount'),
currency=currency,
date=transactiondate)
try:
trans.original_description = data.get(tra, 'original_description')
except:
trans.original_description = data.get(tra, 'description')
db.session.add(trans)
# Links to accounts
for accountdata in data.get(tra, 'accounts').split():
accountdatatb = accountdata.split(':')
accountnum = accountdatatb[0]
if len(accountdatatb) > 1:
amount = accountdatatb[1]
else:
amount = trans.amount
account = accounts[accountnum]
accountamount = amount * helpers.rate(username, currency.isocode,
account.currency.isocode)
db.session.add(
transaction.TransactionAccount(transaction=trans,
account=account,
amount=accountamount))
# Links to categories
for categorydata in data.get(tra, 'categories').split():
categorydatatb = categorydata.split(':')
categorynum = categorydatatb[0]
if len(categorydatatb) > 1:
amount = categorydatatb[1]
else:
amount = trans.amount
category = categories[categorynum]
categoryamount = amount * helpers.rate(username, currency.isocode,
category.currency.isocode)
db.session.add(
transaction.TransactionCategory(
transaction=trans,
category=category,
transaction_amount=amount,
category_amount=categoryamount))
db.session.commit()
########## OK, finished
return 200, 'OK'
