def accounts_get(**kwargs):
if kwargs['id'] == 0:
return DB.query(
Account).filter(
Account.deleted == 'n').order_by(
Account.title).all()
return DB.query(Account).get(kwargs['id'])
def transactions_get(**kwargs):
""" load intervals from database """
if kwargs['id'] == 0:
return DB.query(Transaction).order_by(
desc(Transaction.time)).limit(100).from_self().order_by(
Transaction.time).all()
return DB.query(Transaction).order_by(
desc(Transaction.time)).get(kwargs['id'])
def usercurrency_delete(**kwargs):
''' delete user currency '''
DB.query(UserCurrencies).filter(
and_(
UserCurrencies.user_id == session['user'][0],
UserCurrencies.currency_id == kwargs['id'])
).delete(synchronize_session=False)
DB.commit()
return {'result': 'Ok'}
def balance_get(**kwargs):
"""
return balance forecast for dates
"""
balance = {}
incomes = DB.query(Income).filter(
and_(Income.start_date <= kwargs['end_date'],
or_(
Income.end_date >= kwargs['start_date'],
Income.end_date is None)))
for i in incomes.all():
_sum = i.get_sum(start_date=kwargs['start_date'],
end_date=kwargs['end_date'],
ignore_pf=True)
try:
balance[i.currency.title] += _sum
except KeyError:
balance[i.currency.title] = _sum
total = 0
for currency in currency_get():
if currency['title'] in balance:
total += decimal.Decimal(
currency['rate'] * balance[currency['title']])
balance[currency['title']] = balance[currency['title']]
balance['TOTAL'] = int(total)
balance['start_date'] = kwargs['start_date']
balance['end_date'] = kwargs['end_date']
balance['weeks'] = number_of_weeks(
kwargs['start_date'].strftime('%Y-%m-%d'),
kwargs['end_date'].strftime('%Y-%m-%d'))
today = datetime.date.today()
week_begin = today - datetime.timedelta(today.isoweekday() % 7) # sunday
week_end = week_begin + datetime.timedelta(7)
week_sum = decimal.Decimal(0)
tmp_results = DB.query(Transaction).join(Account).join(Currency).filter(
and_(or_(Transaction.transfer is None, Transaction.transfer <= 0),
and_(or_(
Transaction.income_id <= 0,
Transaction.income_id is None),
and_(
Transaction.sum < 0,
and_(
Transaction.time >= week_begin,
Transaction.time < week_end
)
)
)
)).all()
for k in tmp_results:
week_sum += k.sum * k.account.currency.get_rate().rate
balance['weekly'] = "{}/{}".format(
int(-1*week_sum),
balance['TOTAL'] // balance['weeks'])
return balance
def accounts_delete(**kwargs):
income = DB.query(Account).get(kwargs['id'])
_count = DB.query(
Transaction).filter(
Transaction.account_id == kwargs['id']).count()
if _count > 0:
income.deleted = 'y'
income.show = 'n'
else:
DB.query(Account).filter(Account.record_id == kwargs['id']).delete()
DB.commit()
return {'deleted': kwargs['id']}
def accounts_put(**kwargs):
a = DB.query(Account).get(kwargs['id'])
obj = json.loads(request.data.decode('utf-8', 'strict'))
a.title = obj['title']
a.show = obj['show']
a.currency_id = obj['currency.id']
delta_sum = decimal.Decimal(strip_numbers(obj['sum'])) - a.sum()
if delta_sum != 0:
t = Transaction(time=datetime.date.today(), sum=delta_sum,
account_id=kwargs['id'], comment='fix account summ')
DB.add(t)
DB.commit()
return {'updated': DB.query(Account).get(kwargs['id']), "previous": a}
def transactions_put(**kwargs):
i = DB.query(Transaction).get(kwargs['id'])
obj = json.loads(request.data.decode('utf-8', 'strict'))
# try:
i.time = datetime.datetime.strptime(obj['time'], '%Y-%m-%d').date()
i.account_id = int(obj['account.id']) if obj['account.id'] != '' else None
i.sum = decimal.Decimal(strip_numbers(obj['sum']))
i.transfer = int(obj['transfer']) if obj['transfer'] not in [
'0', ''] else None
i.income_id = int(obj['income.id']) if obj['income.id'] not in [
'0', ''] else None
i.comment = obj['comment']
# except:
# abort(400)
DB.commit()
return {'updated': DB.query(Transaction).get(kwargs['id']), "previous": i}
def currency_get(**kwargs):
'''
get list of currency rates
'''
max_date_q = select([
Rate.currency_b_id.label("b_id"),
func.max(Rate.rate_date).label("rate_date")
]).group_by(Rate.currency_b_id).alias('max_date_q')
rates_query = DB.query(
Rate.currency_b_id, Rate, Rate.rate_date
).join(
max_date_q,
and_(
max_date_q.c.b_id == Rate.currency_b_id,
max_date_q.c.rate_date == Rate.rate_date
)
)
entries = []
if 'id' in kwargs and kwargs['id']:
try:
return rates_query.filter(
Rate.currency_b_id == kwargs['id']).first()[1].to_dict()
except TypeError:
return []
else:
for rate in rates_query.all():
entries.append(rate[1].to_dict())
return entries
def get_backlog(self, max_date):
"""[summary]
Arguments:
max_date {[type]} -- [description]
Returns:
[type] -- [description]
"""
backlog = []
(last_payment,) = (
DB_SESSION.query(func.max(Transaction.time))
.filter(Transaction.income_id == self.record_id)
.first()
)
if last_payment is None:
last_payment = self.start_date
else:
last_payment = last_payment + timedelta(1)
for i in self.get_dates(start_date=last_payment, end_date=max_date):
backlog.append(
{
"id": 0,
"time": i,
"origin_time": i,
"summ": "{:.2f}".format(self.summ),
"income": self,
"comment": "",
}
)
return backlog
def transaction_get(**kwargs): # pylint: disable=C0111
print(kwargs)
if kwargs['id'] == 0:
from .accounts import Account
from .currencies import Currency
_limit = int(request.args.getlist('limit')[0]) if request.args.getlist(
'limit') else 100
_start = int(request.args.getlist('start')[0]) if request.args.getlist(
'start') else 0
_filter = and_(Transaction.user_id == session['user'][0],
Transaction.record_id >= _start)
if request.args.getlist('account'):
_filter = and_(
_filter,
Transaction.account_id.in_(request.args.getlist('account')))
else:
if request.args.getlist('currency'):
_filter = and_(
_filter,
Currency.title.in_(request.args.getlist('currency')))
if request.args.getlist('date'):
_start_date = request.args.getlist('date')[0]
if _start_date[0] == '=':
_start_date = _start_date[1:]
_filter = and_(_filter, Transaction.time == _start_date)
else:
_filter = and_(_filter, Transaction.time >= _start_date)
_end_date = request.args.getlist('date')[-1]
if _end_date != _start_date:
_filter = and_(_filter, Transaction.time <= _end_date)
if request.args.getlist('transfer'):
_filter = and_(_filter, Transaction.transfer != None)
_filter_comments = None
for _match in request.args.getlist('filter'):
if _filter_comments is None:
_filter_comments = Transaction.comments.op('~')(_match)
_filter_comments = or_(_filter_comments,
Transaction.comments.op('~*')(_match))
if _filter_comments is not None:
_filter = and_(_filter, _filter_comments)
print(_filter)
return DB.query(Transaction).join(Account).join(Currency).filter(
_filter).order_by(asc(Transaction.record_id)).limit(_limit).all()
return DB.query(Transaction).get(kwargs['id'])
def incomes_put(**kwargs):
i = DB.query(Income).get(kwargs['id'])
obj = json.loads(request.data.decode('utf-8', 'strict'))
# try:
i.title = obj['title']
i.currency_id = int(obj['currency.id'])
i.sum = decimal.Decimal(strip_numbers(obj['sum']))
i.start_date = datetime.datetime.strptime(
obj['start_date'], '%Y-%m-%d').date()
i.end_date = (
None if obj['end_date'] == '' else datetime.datetime.strptime(
obj['end_date'], '%Y-%m-%d').date())
i.period_id = int(obj['period.id'])
# except:
# abort(400)
DB.commit()
return {'updated': DB.query(Income).get(kwargs['id']), "previous": i}
def update_rates():
default_currency = DB.query(Currency).filter_by(default=1).first()
http = PoolManager(cert_reqs='CERT_REQUIRED', ca_certs=certifi.where())
c_title = {}
objects = []
for currency in DB.query(Currency).all():
if currency == default_currency:
continue
c_title[currency.title] = currency.record_id
_request = http.request(
'GET',
"https://finance.google.com/finance/converter"
"?a=1&from={}&to={}".format(
currency, default_currency))
if _request.status == 200:
for line in _request.data.decode('utf-8', 'replace').split('\n'):
if "id=currency_converter_result" in line:
match = RE_C_EXCHANGE.match(line)
if match:
print("{} {} {}".format(match.group('currency_a'),
match.group('currency_b'),
match.group('rate')))
objects.append(
Rate(
rate_date=datetime.datetime.now(),
currency_a_id=default_currency.record_id,
currency_b_id=c_title[match.group('currency_b')],
rate=match.group('rate')))
else:
print("cannot get rate {}:{}".format(currency, default_currency))
DB.bulk_save_objects(objects)
DB.commit()
try:
if request.method == 'GET':
return redirect(url_for(
'dispatcher',
api='currency',
_external=True,
_scheme='https'))
return True
except (RuntimeError, AttributeError):
return True
def usercurrency_put(**kwargs):
""" update user currencies
Arguments:
**kwargs id -- currency id
Returns:
json -- result code
"""
obj = json.loads(request.data.decode('utf-8', 'strict'))
updated = 0
if 'default' in obj and obj['default']:
# clean previous default
updated = DB.query(UserCurrencies).filter(
and_(
UserCurrencies.user_id == session['user'][0],
UserCurrencies.currency_id != kwargs['id'])
).update(
{UserCurrencies.default: False},
synchronize_session='evaluate')
updated = DB.query(UserCurrencies).filter(
and_(
UserCurrencies.user_id == session['user'][0],
UserCurrencies.currency_id == kwargs['id']
)
).update(
{UserCurrencies.default: True},
synchronize_session='evaluate')
if updated == 0:
# if not updated nothing - inser new record
DB.add(UserCurrencies(
user_id=session['user'][0],
currency_id=kwargs['id'],
default=True
))
else:
DB.add(UserCurrencies(
user_id=session['user'][0],
currency_id=kwargs['id']
))
DB.commit()
return {'result': 'Ok'}
def transaction_post(**kwargs): # pylint: disable=C0111
i = DB.query(Transaction).get(kwargs['id'])
obj = json.loads(request.data.decode('utf-8', 'strict'))
print(obj)
print(obj['time'])
# try:
i.time = datetime.datetime.strptime(obj['time'], '%Y-%m-%d').date()
# TODO: check if account valid for this user
i.account_id = int(obj['account.id']) if obj['account.id'] != '' else None
i.user_id = session['user'][0]
i.summ = decimal.Decimal(strip_numbers(obj['summ']))
# TODO: check if transfer transaction valid for this user
i.transfer = int(obj['transfer']) if obj['transfer'] not in ['0', ''
] else None
i.income_id = int(obj['income.id']) if obj['income.id'] not in ['0', ''
] else None
i.comments = obj['comments']
# except:
# abort(400)
DB.commit()
return {'updated': DB.query(Transaction).get(kwargs['id']), "previous": i}
def get_dates(
self,
start_date=date.today(),
end_date=date.today().replace(year=(date.today().year + 1)),
ignore_pf=False,
): # pylint: disable=C0111,C0301
list_dates = []
# s = 0
_start_date = max(start_date, self.start_date)
if self.end_date:
# print(self.end_date, _start_date)
if self.end_date < _start_date:
return []
_end_date = min(end_date, self.end_date)
else:
_end_date = end_date
if _start_date > _end_date:
return []
if _start_date == self.start_date:
list_dates.append(_start_date)
_next_date = next_date(self.start_date, (self.period.value, self.period.item))
# print(_start_date, _end_date, _next_date)
while _next_date <= _end_date:
if _next_date >= _start_date and _next_date <= _end_date:
# s += int(self.summ)
list_dates.append(_next_date)
_next_date = next_date(_next_date, (self.period.value, self.period.item))
# print(list_dates)
if ignore_pf:
return list_dates
pf_dates = (
DB_SESSION.query(Payforward.income_date)
.filter(
and_(
Payforward.income_id == self.record_id,
and_(
Payforward.income_date >= _start_date,
Payforward.income_date <= _end_date,
),
)
)
.all()
)
# print(pf_dates)
for (i,) in pf_dates:
try:
list_dates.remove(i)
except ValueError:
pass
return list_dates
def balance(self,
end_date=datetime.now().replace(hour=23,
minute=59,
second=59,
microsecond=999999)): # pylint: disable=C0111
from .transactions import Transaction # pylint: disable=cyclic-import
result = DB.query(Transaction.account_id,
func.sum(Transaction.summ).label('total')).filter(
and_(Transaction.account_id == self.record_id,
Transaction.time <= end_date)).group_by(
Transaction.account_id).first()
if result:
return result[1]
return Decimal(0.0)
def transaction_delete(**kwargs): # pylint: disable=C0111
income = DB.query(Transaction).filter_by(record_id=kwargs['id']).delete()
DB.query(Payforward).filter_by(transaction_id=kwargs['id']).delete()
DB.commit()
return {'deleted': income}
def rate(self):
"""return current rate"""
return DB.query(Rate).filter(Rate.currency_b_id == self.record_id).order_by(
Rate.rate_date.desc()).first()
except IndexError:
return 0
__base__.metadata.create_all(__engine__)
# DB = session()
if __name__ == "__main__":
SUB_SELECT = (
select(
[
Rate.currency_b_id.label("b_id"),
func.max(Rate.rate_date).label("rate_date"),
]
)
.group_by(Rate.currency_b_id)
.alias("s")
)
print(
DB_SESSION.query(Rate.currency_b_id, Rate.rate, Rate.rate_date)
.join(
SUB_SELECT,
and_(
SUB_SELECT.c.b_id == Rate.currency_b_id,
SUB_SELECT.c.rate_date == Rate.rate_date,
),
)
.all()
)
def intervals_get(**kwargs):
""" load intervals from database """
return DB.query(Interval).all() if kwargs['id'] == 0 else DB.query(Interval).get(kwargs['id'])
def incomes_delete(**kwargs):
DB.query(Income).filter_by(id=kwargs['id']).delete()
DB.commit()
return {'deleted': kwargs['id']}
def incomes_get(**kwargs):
if kwargs['id'] == 0:
return DB.query(Income).all()
else:
return DB.query(Income).get(kwargs['id'])