def update(self, accountid):
account = self.__own_account(accountid)
if not account:
self.notfound(
'Nonexistent account cannot be modified (or you do not own it)')
if 'name' in self.args:
account.name = self.args['name']
if 'currency' in self.args:
# Do not update currency if account has transactions
if not models.TransactionAccount.query.filter(
models.TransactionAccount.account == account
).count():
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:
account.currency = currency
if 'start_balance' in self.args:
account.start_balance = Decimal(self.args['start_balance'])
self.add_to_response('totalbalance')
db.session.commit()
return account.as_dict(self.username)
def delete_everything(username):
"""Delete all data except user preferences"""
# Transaction
# -> TransactionAccount and TransactionCategory deleted by cascade
transaction.Transaction.query.filter(
transaction.Transaction.owner_username == username).delete()
# Category
transaction.Category.query.filter(
transaction.Category.owner_username == username).delete()
# Account
# ... first get all accounts for the user...
accountowners = transaction.AccountOwner.query.filter(
transaction.AccountOwner.owner_username == username).all()
# ... then delete all "AccountOwner" links for the user...
transaction.AccountOwner.query.filter(
transaction.AccountOwner.owner_username == username).delete()
# ... and delete only accounts which do not have user associated...
deleteaccounts = []
for accountowner in accountowners:
if not transaction.AccountOwner.query.filter(
transaction.AccountOwner.account_id ==
accountowner.account_id).first():
deleteaccounts.append(
transaction.Account.id == accountowner.account_id)
if deleteaccounts:
transaction.Account.query.filter(db.or_(*deleteaccounts)).delete()
# Currency
core.Currency.query.filter(
core.Currency.owner_username == username).delete()
# XXX TransactionAccounts are not deleted : problem with SQLite
# Commit deletes
db.session.commit()
def __search(self, substring):
"""Search on parts of the users name or on exact email address"""
if len(substring) < 3:
self.badrequest('Please give at least 3 characters')
if '@' in substring:
corresponding_rows = models.User.query.join(
models.UserEmail
).filter(
models.UserEmail.email_address == substring,
models.UserEmail.confirmation == 'OK'
)
else:
substring=u'%{0}%'.format(substring)
corresponding_rows = models.User.query.filter(
db.and_(
models.User.username != self.username,
db.or_(
models.User.username.like(substring),
models.User.first_name.like(substring),
models.User.last_name.like(substring),
)
)
)
return [u.as_dict() for u in corresponding_rows.all()]
def create(self):
if not ('currency' in self.args and 'name' in self.args):
self.badrequest("Please provide category name and currency")
if 'parent' in self.args:
parent = self.__own_category(self.args['parent'])
if not parent:
self.badrequest("This parent category does not exist")
else:
parent = None
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 not currency:
self.badrequest("This currency does not exist")
category = models.Category(
owner_username=self.username,
parent=parent,
currency=currency,
name=self.args['name']
)
db.session.add(category)
db.session.commit()
return category.as_dict(self.username)
def list(self):
currencies = models.Currency.query.filter(
db.or_(
models.Currency.owner_username == self.username,
models.Currency.owner_username == None,
))
return [c.as_dict() for c in currencies]
def create(self):
if not (
'currency' in self.args and
'name' in self.args and
'start_balance' in self.args
):
self.badrequest(
"Please provide the account name, currency and start balance")
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 not currency:
self.badrequest("This currency does not exist")
name = self.args['name']
start_balance = self.args['start_balance']
a = models.Account(
name=name,
currency=currency,
start_balance=start_balance
)
ao = models.AccountOwner(account=a, owner_username=self.username)
db.session.add_all((a, ao))
db.session.commit()
self.add_to_response('totalbalance')
return a.as_dict(self.username)
def list(self):
currencies = models.Currency.query.filter(
db.or_(
models.Currency.owner_username == self.username,
models.Currency.owner_username == None,
)
)
return [c.as_dict() for c in currencies]
def __own_currency(self, isocode):
return models.Currency.query.filter(
db.and_(
models.Currency.isocode == isocode,
db.or_(
models.Currency.owner_username == self.username,
models.Currency.owner_username == None,
)))
def __own_currency(self, isocode):
return models.Currency.query.filter(
db.and_(
models.Currency.isocode == isocode,
db.or_(
models.Currency.owner_username == self.username,
models.Currency.owner_username == None,
)
)
)
def rate(username, fromisocode, toisocode):
if fromisocode == toisocode:
return 1
# Request the currencies
fromcurrency = core.Currency.query.filter(
db.and_(
core.Currency.isocode == fromisocode,
db.or_(core.Currency.owner_username == username, core.Currency.owner_username == None),
)
).first()
tocurrency = core.Currency.query.filter(
db.and_(
core.Currency.isocode == toisocode,
db.or_(core.Currency.owner_username == username, core.Currency.owner_username == None),
)
).first()
if not fromcurrency or not tocurrency:
return None
# Both currencies are globally defined
if (fromcurrency.rate is None) and (tocurrency.rate is None):
return exchangerate.getrate(fromcurrency.isocode, tocurrency.isocode)
# Both currencies are user-defined
elif (fromcurrency.rate is not None) and (tocurrency.rate is not None):
return tocurrency.rate / fromcurrency.rate
# Mixed user-defined / globally defined rates
else:
preferred_isocode = core.User.query.filter(core.User.username == username).one().preferred_currency.isocode
# From a user-defined currency to a globally defined currency
if (fromcurrency.rate is not None) and (tocurrency.rate is None):
target_rate = exchangerate.getrate(preferred_isocode, tocurrency.isocode)
if fromcurrency.rate == 0:
return 0
return target_rate / fromcurrency.rate
if (fromcurrency.rate is None) and (tocurrency.rate is not None):
source_rate = exchangerate.getrate(preferred_isocode, fromcurrency.isocode)
if tocurrency.rate == 0:
return 0
return tocurrency.rate / source_rate
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 category_filter(value):
def subcategories(categoryid, categorylist):
for cat in models.Category.query.filter(
models.Category.parent_id == categoryid
):
categorylist.append(cat.id)
categorylist = subcategories(cat.id, categorylist)
return categorylist
categorylist = subcategories(value, [ int(value) ])
return [
models.Transaction.id == models.TransactionCategory.transaction_id,
db.or_(*[ models.TransactionCategory.category_id == value \
for value in categorylist ])
]
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 delete_everything(username):
"""Delete all data except user preferences"""
# Transaction
# -> TransactionAccount and TransactionCategory deleted by cascade
transaction.Transaction.query.filter(
transaction.Transaction.owner_username == username
).delete()
# Category
transaction.Category.query.filter(
transaction.Category.owner_username == username
).delete()
# Account
# ... first get all accounts for the user...
accountowners = transaction.AccountOwner.query.filter(
transaction.AccountOwner.owner_username == username
).all()
# ... then delete all "AccountOwner" links for the user...
transaction.AccountOwner.query.filter(
transaction.AccountOwner.owner_username == username
).delete()
# ... and delete only accounts which do not have user associated...
deleteaccounts = []
for accountowner in accountowners:
if not transaction.AccountOwner.query.filter(
transaction.AccountOwner.account_id == accountowner.account_id
).first():
deleteaccounts.append(
transaction.Account.id == accountowner.account_id
)
if deleteaccounts:
transaction.Account.query.filter(
db.or_(*deleteaccounts)
).delete()
# Currency
core.Currency.query.filter(
core.Currency.owner_username == username
).delete()
# XXX TransactionAccounts are not deleted : problem with SQLite
# Commit deletes
db.session.commit()
def create(self):
if not ('currency' in self.args and 'name' in self.args):
self.badrequest("Please provide category name and currency")
if 'parent' in self.args:
parent = self.__own_category(self.args['parent'])
if not parent:
self.badrequest("This parent category does not exist")
else:
parent = None
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 not currency:
self.badrequest("This currency does not exist")
category = models.Category(owner_username=self.username,
parent=parent,
currency=currency,
name=self.args['name'])
db.session.add(category)
db.session.commit()
return category.as_dict(self.username)
def create(self):
if not (
'currency' in self.args and \
'date' in self.args and \
'description' in self.args and \
'amount' in self.args
):
self.badrequest(
"Please provide transaction description, currency, amount and date")
# First, create the transaction object
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 not currency:
self.badrequest("This currency does not exist")
date = helpers.date_from_string(self.args['date'])
if not date:
self.badrequest("This date cannot be understood")
description = self.args['description']
if 'original_description' in self.args:
original_description = self.args['original_description']
else:
original_description = description
transaction = models.Transaction(
owner_username = self.username,
description = description,
original_description = original_description,
amount = self.args['amount'],
currency = currency,
date = date
)
db.session.add(transaction)
# Next, create the links from the transaction to its accounts
if 'accounts' in self.args:
accounts = json.loads(self.args['accounts'])
for accountdata in accounts:
transaction_accounts = []
if 'amount' in accountdata:
# If no amount is specified, do not associate the account
accountobject = models.Account.query.options(
db.joinedload(models.Account.account_owners)
).filter(
db.and_(
models.Account.id == accountdata['account'],
models.AccountOwner.owner_username == self.username,
)
).first()
if accountobject:
ta = models.TransactionAccount(
transaction = transaction,
account = accountobject,
amount = accountdata['amount'],
verified = False
)
db.session.add(ta)
self.add_to_response('accountbalance',
accountdata['account'])
self.add_to_response('totalbalance')
# Next, create the links from the transaction to its categories
if 'categories' in self.args:
categories = json.loads(self.args['categories'])
for categorydata in categories:
transaction_categories = []
if 'transaction_amount' in categorydata and \
'category_amount' in categorydata:
# If no amount is specified, do not associate the category
categoryobject = models.Category.query.options(
db.joinedload(models.Category.currency)
).filter(
db.and_(
models.Category.id == categorydata['category'],
models.Category.owner_username == self.username,
)
).first()
if categoryobject:
tc = models.TransactionCategory(
transaction = transaction,
category = categoryobject,
transaction_amount = categorydata['transaction_amount'],
category_amount = categorydata['category_amount']
)
db.session.add(tc)
self.add_to_response('categoriesbalance',
categorydata['category'])
# Commit everything...
db.session.commit()
return transaction.as_dict(self.username)
def update(self, transactionid):
transaction = self.__own_transaction(transactionid)
if not transaction:
self.notfound(
'Nonexistent transaction cannot be modified (or you do not own it)')
# First, modifications on the Transaction object itself
if 'description' in self.args:
desc = self.args['description']
if desc.strip() == '':
transaction.description = transaction.original_description
else:
transaction.description = desc
if 'amount' in self.args:
transaction.amount = self.args['amount']
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:
transaction.currency = currency
if 'date' in self.args:
date = helpers.date_from_string(self.args['date'])
if date:
transaction.date = date
# Next, update accounts
if 'accounts' in self.args:
existing_accounts = dict([ta.as_tuple() for ta in
transaction.transaction_accounts])
new_accounts_data = json.loads(self.args['accounts'])
for account_data in new_accounts_data:
if 'amount' in account_data and 'account' in account_data:
amount = account_data['amount']
accountid = account_data['account']
if accountid in existing_accounts.keys():
# Account already linked...
if existing_accounts[accountid] != amount:
# ...but the amount is different
ta = models.TransactionAccount.query.filter(db.and_(
models.TransactionAccount.transaction == transaction,
models.TransactionAccount.account_id == accountid
)).one()
ta.amount = amount
ta.verified = False
self.add_to_response('accountbalance', accountid)
existing_accounts.pop(accountid)
else:
# Account is not already linked
# Verify the account is owned by the user
accountobject = models.Account.query.options(
db.joinedload(models.Account.account_owners)
).filter(
db.and_(
models.Account.id == accountid,
models.AccountOwner.owner_username == self.username
)
).first()
if accountobject:
ta = models.TransactionAccount(
transaction = transaction,
account = accountobject,
amount = amount,
verified = False
)
self.add_to_response('accountbalance', accountid)
db.session.add(ta)
# All accounts to keep have been poped out from "existing_accounts"
# Delete all links remaining from this transaction to accounts
for accountid in existing_accounts.keys():
ta = models.TransactionAccount.query.filter(db.and_(
models.TransactionAccount.transaction == transaction,
models.TransactionAccount.account_id == accountid
)).one()
self.add_to_response('accountbalance', accountid)
db.session.delete(ta)
self.add_to_response('totalbalance')
# Then, update categories
if 'categories' in self.args:
existing_categories = dict([tc.as_tuple() for tc in
transaction.transaction_categories])
new_categories_data = json.loads(self.args['categories'])
for category_data in new_categories_data:
if 'transaction_amount' in category_data and \
'category_amount' in category_data and \
'category' in category_data:
transaction_amount = category_data['transaction_amount']
category_amount = category_data['category_amount']
categoryid = category_data['category']
if categoryid in existing_categories.keys():
# Category already linked...
if existing_categories[categoryid]['category_amount'] \
!= category_amount:
# ...but the amount is different
tc = models.TransactionCategory.query.filter(db.and_(
models.TransactionCategory.transaction == transaction,
models.TransactionCategory.category_id == categoryid
)).one()
tc.transaction_amount = transaction_amount
tc.category_amount = category_amount
tc.verified = False
self.add_to_response('categoriesbalance',
categoryid)
existing_categories.pop(categoryid)
else:
# Category is not already linked
# Verify the category is owned by the user
categoryobject = models.Category.query.filter(
db.and_(
models.Category.id == categoryid,
models.Category.owner_username == self.username
)
).first()
if categoryobject:
tc = models.TransactionCategory(
transaction = transaction,
category = categoryobject,
transaction_amount = transaction_amount,
category_amount = category_amount
)
self.add_to_response('categoriesbalance',
categoryid)
db.session.add(tc)
# All categories to keep have been poped out from
# "existing_categories"
# Delete all links remaining from this transaction to categories
for categoryid in existing_categories.keys():
tc = models.TransactionCategory.query.filter(db.and_(
models.TransactionCategory.transaction == transaction,
models.TransactionCategory.category_id == categoryid
)).one()
self.add_to_response('categoriesbalance', categoryid)
db.session.delete(tc)
db.session.commit()
return transaction.as_dict(self.username)