def addExpense(**kwargs):
""" Добавить позицию в таб. Expense """
db.session.close()
data = Expense(**kwargs)
db.session.add(data)
db.session.commit()
return 'Add an Expense'
def handle_csv_file(csvfile):
"""
stores the file information in the database and returns the expenses amount per-month
in a chronological order
"""
reader = csv.DictReader(csvfile, delimiter=',')
# A dictionnary where we are going to collect expenses per month
expense_per_month = {}
for row in reader:
# Category check, get it or add it to the database
cat = Category.objects.get_or_create(name=row['category'])[0]
# Employee check, get it or add it to the database
emp = Employee.objects.get_or_create(
name=row['employee name'], address=row['employee address'])[0]
# Extract date
date = datetime.strptime(row['date'], '%m/%d/%Y')
# Calculating total expense after tax
pretax_amount = Decimal(row['pre-tax amount'].replace(',', ''))
tax_amount = Decimal(row['tax amount'].replace(',', ''))
after_tax = pretax_amount + tax_amount
# We will collect the total expenses(after tax) represented by every row in the uploaded file
# and group them by month in a dictionnary: the keys will be integers that will be generated
# based on the expense date while the values will be tuples. These tuples will contain a string
# that represents the month in letters and a decimal that represnts the total expense
year = date.strftime("%Y")
month = date.strftime("%m")
# These keys are generated in a way that will help us later on reorganize the expenses per month
# chronologically
key = generate_key(year, month)
try:
# Key already exists, retrieving the total to update it
previous_total = expense_per_month[key][1]
except KeyError:
# Key doesn't exist, no total to update
previous_total = 0
finally:
# Generating first element of the tuple
month_year = date.strftime("%B %Y")
# Updating the dictionnary
expense_per_month.update(
{key: (month_year, previous_total + after_tax)})
# Creating a new expense and saving it to the database
expense = Expense(date=date,
employee=emp,
description=row['expense description'],
pretax_amount=pretax_amount,
tax_name=row['tax name'],
tax_amount=tax_amount,
category=cat)
expense.save()
# Returning a sorted list of tuples
return generate_sorted_list(expense_per_month)
def create_sample_data():
"""
Creates sample data for testing.
There should be 11 expenses totaling $101.00. All have the same category and date.
"""
# Creates a sample category and sample date that can be used to create sample expenses
sample_category = Category(name='Test Category')
sample_category.save()
sample_date = datetime.date(2021, 1, 1)
# Creates 11 sample expenses, last expense created has a lower amount so it can be tested to see if it is excluded in the top ten highest expenses graph
for i in range(10):
i += 1
new_expense = Expense(name='Expense {}'.format(str(i)), category_id=sample_category.id, amount=10, date=sample_date)
new_expense.save()
new_expense = Expense(name='Expense 11', category_id=sample_category.id, amount=1, date=sample_date)
new_expense.save()
def createExpense(self, amount, note, date, cat):
"""
Insert an Expense entry into the db
"""
expense = Expense(amount=amount, note=note, date=date, category_id=cat)
self.session.add(expense)
self.session.flush()
self.session.commit()
return expense
def expense_create_alexa(amount, category):
user = User.query.filter_by(id=1).first()
amount = float(amount)
description = category # MAKE SURE THAT THIS IS CAPITAL
print(user, file=sys.stderr)
new_income_statement = Expense(str(1), amount, description)
db_session.add(new_income_statement)
db_session.commit()
print('Created Expense Statement', file=sys.stderr)
return '{"success": "200"}'
async def create_expense(expense_request: ExpenseRequest,
db: Session = Depends(get_db)):
expense = Expense()
expense.date = expense_request.date
expense.description = expense_request.description
expense.currency = expense_request.currency
expense.amount = expense_request.amount
db.add(expense)
db.commit()
return {'expense created!!'}
def add_user_expense_to_event(user, event, expenses):
for expense in expenses:
if expense["person_key"] == user.key.urlsafe():
exp = Expense(parent=user.key, person=user.key, cost=float(expense["cost"]), to=event.payer)
exp.put()
# expense = Expense(parent=user.key, person=user.key, cost=0)
# expense.put()
event.expenses.append(exp.key)
event.put()
def run_shuttle_expense():
if not current_user.isSuperUser:
abort(401)
cost = float(request.args.get('cost'))
if cost <= 0:
abort(400)
active_users = User.query.filter(or_(User.isSaturdayAbsent == False, User.isSundayAbsent == False))\
.order_by(User.firstname.asc())
if not active_users or active_users.count() < 1:
abort(404)
shuttle_cost_per_player = float("{0:.2f}".format(
float(cost) / float(active_users.count())))
"""Add expense entry"""
expenseEntry = Expense(cost, shuttle_cost_per_player, "Deduction_Shuttles",
active_users.count())
db.session.add(expenseEntry)
db.session.commit()
"""Insert a log"""
description = 'Shuttle expense for $' + str(cost) + ' is run'
log = Log(current_user.email, description)
db.session.add(log)
db.session.commit()
bodyText = 'Hi everyone,\r\n\nA total of $' + str(cost) + ' is charged for shuttles and it is charged ' + \
'equally among ' + str(active_users.count()) + ' active players.' + \
'\r\n\nBalances of players are as follows after deduction:\r\n\n'
email_ids = []
for user in active_users:
user.balance = float("{0:.2f}".format(user.balance -
shuttle_cost_per_player))
db.session.commit()
transaction = Transaction(user.email, shuttle_cost_per_player,
user.balance, "Deduct_Shuttle")
db.session.add(transaction)
db.session.commit()
email_ids.append(user.email)
bodyText += user.firstname + ' ' + user.lastname + ' : ' + '$' + str(
user.balance) + '\r\n'
bodyText += '\nThanks\r\nSydney Badminton Group'
SendGrid.send_email(email_ids, "*****@*****.**",
"Badminton: Balances after Shuttles expenses",
bodyText)
return "Success"
async def answer(event: CallbackQuery):
chat_id = event.chat_id
msg_id = event.message_id
if event.data == b'Confirm':
pending_expense = get_pending_expense(pending_expenses, msg_id, chat_id)
expense = Expense(**pending_expense['expense'])
original_msg_id = pending_expense['original_msg_id']
group = get_group(expense_groups, chat_id)
group.add_expense(expense)
update_group(expense_groups, group)
await event.answer(message='Expense added')
await event.delete()
await tg_client.send_message(chat_id, '**Expense Added**\n' + str(expense))
elif event.data == b'Deny':
pending_expense = get_pending_expense(pending_expenses, msg_id, chat_id)
original_msg_id = pending_expense['original_msg_id']
await event.answer(message='Expense not added')
await event.delete()
await tg_client.send_message(chat_id, '**Expense not Added**', reply_to=original_msg_id)
elif event.data == b'Close':
group = get_group(expense_groups, chat_id)
group.close_group()
update_group(expense_groups, group)
await event.delete()
await tg_client.send_message(chat_id, group.get_payments())
for payment in group.payments:
try:
receiver = await tg_client.get_input_entity(payment.from_whom.username)
receiver = await tg_client.get_entity(receiver)
await tg_client.send_message(receiver,
message='Hi {}! You should send a payment of {} {} to {} to settle the expenses of {}'.format(
payment.from_whom.name,
payment.amount,
payment.currency,
payment.to_whom.name,
group.name
))
except PeerIdInvalidError:
continue
elif event.data == b'Do not close':
await tg_client.send_message(chat_id, '**Group not closed**')
await event.delete()
del_pending_expense(pending_expenses, msg_id, chat_id)
def add_expense():
user = g.user
form = ExpenseForm()
if form.validate_on_submit():
expense = Expense(date=form.date.data,
description=form.description.data.strip(),
cost=''.join(i for i in form.cost.data.strip()
if i.isdigit()),
merchant=form.merchant.data,
category=form.category.data,
creator=user)
db.session.add(expense)
db.session.commit()
flash(
"The Expense from: '{}' was added".format(form.merchant.data.name),
'success')
return redirect(url_for('dashboard'))
return render_template('add_expense.html', user=user, form=form)
def post(self):
c_per = memcache.get('current_person')
if not c_per:
return # ERROR! Invalid attempt to post schedules
if not c_per.schedule:
return # ERROR! Person don't have an schedule ... probably an invalid attempt
## TODO: Do a nice data validation?? could be JScript too ...
exp = Expense( belongs = c_per,
name = self.request.get('name'),
value = self.request.get('cost'),
when = self.request.get('date'),
description = self.request.get('desc') )
exp.put()
memcache.set('current_person',c_per,PERSON_EXPIRE_TIME)
self.redirect('/')
def add_expense():
if len(Category.objects()) < 1:
return list_expenses(error='You cannot create an expense until you create at least one category.')
form = ExpenseForm(request.form)
if request.method == 'POST':
if form.validate():
if __get_expense_by_name(form.name.data) != None:
return render_template('views/expense_form.html', page_name='edit_expense', page_title='Edit Expense', categories=Category.objects(), error='An expense with that name already exists.')
expense = Expense(name=form.name.data, category_id=form.category_id.data, amount=form.amount.data, date=form.date.data)
expense.save()
return list_expenses(success='Created new expense "{}".'.format(form.name.data))
else:
return render_template('views/expense_form.html', page_name='add_expense', page_title='Add Expense', categories=Category.objects(), errors=parse_form_errors(form.errors.items()))
return render_template('views/expense_form.html', page_name='add_expense', page_title='Add Expense', categories=Category.objects())
def update_finance(finances, ftype):
# Delete any finances which have been removed.
new_finances = [finance['id'] for finance in finances if finance['id']]
if ftype == 'revenues':
entity.revenues = [
revenue for revenue in entity.revenues
if revenue.id in new_finances
]
elif ftype == 'expenses':
entity.expenses = [
expense for expense in entity.expenses
if expense.id in new_finances
]
# Do this or else list comprehensions don't work as expected.
db.commit()
# Create or update.
for finance in finances:
if finance['id']:
# Finance exists, update data.
oldfinance = Finance.query.get(finance['id'])
if oldfinance.amount != finance['amount']:
oldfinance.amount = finance['amount']
app.logger.debug('UPDATING ' + ftype + ' AMOUNT: ' +
str(oldfinance.amount))
if oldfinance.year != finance['year']:
oldfinance.year = finance['year']
app.logger.debug('UPDATING ' + ftype + ' YEAR: ' +
str(oldfinance.year))
else:
# Finance doesn't exist, create it.
if ftype is 'revenues':
revenue = Revenue(finance['amount'], finance['year'])
entity.revenues.append(revenue)
app.logger.debug('NEW REVENUE -- ' + str(revenue.year) +
': ' + str(revenue.amount))
elif ftype is 'expenses':
expense = Expense(finance['amount'], finance['year'])
entity.expenses.append(expense)
app.logger.debug('NEW EXPENSE -- ' + str(expense.year) +
': ' + str(expense.amount))
db.commit()
def expense_create():
try:
csrf = session.get('csrf', {})
uid = csrf[csrf.index(":") + 2:len(csrf) - 1]
print(uid, file=sys.stderr)
if len(csrf) == 0:
return render_template("index.html")
else:
user = User.query.filter_by(id=uid).first()
amount = float(request.form["amount"])
description = request.form["category"]
print(user, file=sys.stderr)
new_expense_statement = Expense(str(user.id), amount, description)
db_session.add(new_expense_statement)
db_session.commit()
print('Created Expense Statement', file=sys.stderr)
return redirect(url_for('.home_page'))
except:
print("ERROR OCCURED", file=sys.stderr)
session.pop('csrf', None)
return render_template("index.html")
def post(self, trip_key):
errors = []
output = ""
user = users.get_current_user()
authz = Authz(user)
self.response.headers["Content-type"] = "application/json"
# check for authorization to create expenses for this trip & verify this
# is in fact a request to create a new expense
try:
# get the trip
trip = Trip.get(trip_key)
# verify the user is authorized to create an expense on this trip
authz.createExpense(trip)
except PermissionError as e:
# this permission error could have come from authz or locally
errors.append({"message": e.args})
except db.BadKeyError:
errors.append({"message": "Invalid trip key"})
except Exception as e:
logging.exception(e)
errors.append({"message": "Unexpected error loading trip"})
# bail if we hit authz errors
if len(errors) > 0:
self.response.set_status(400)
output = json.dumps({"error": errors})
self.response.out.write(output)
return
# having passed authz, let's try creating the expense
data = ExpenseUnpacker().unpack_post(self.request)
if data['description'] == "" or data['value'] == "" or data[
'payer'] == "":
errors.append(
{"message": "Description, value, and payer are required."})
elif len(data['travelers']) == 0:
errors.append({
"message":
"At least one person must be specified as a traveler."
})
else:
try:
expense = Expense(
parent=trip.key(),
creator=user,
description=data['description'],
value=int(data['value']),
currency="USD",
)
# get the expense date
expense_date = dateparse(data['expense_date'])
expense.expense_date = expense_date.date()
# TODO: make sure these travelers are actually on the trip
expense.travelers = data['travelers']
# TODO: ensure the payer is actually a traveler
expense.payer = data['payer']
expense.put()
output = GqlEncoder().encode({
"id": "%s" % expense.key(),
'modify_date': expense.modify_date,
'expense_date': expense.expense_date,
'value': expense.value,
})
except Exception as e:
logging.exception(e)
errors.append({"message": "Unexpected error creating expense"})
if len(errors) > 0:
self.response.set_status(400)
output = json.dumps({"error": errors})
self.response.out.write(output)
async def new_message(event: NewMessage):
chat_id = event.chat_id
bot_called = event.message.mentioned
try:
# Check if group exist ... if not, it is a one to one chat or an error!
group = get_group(expense_groups, chat_id)
if group:
msg = event.message
doc = nlp(msg.raw_text)
matches = matcher(doc)
for match_id, start, end in matches:
string_id = nlp.vocab.strings[match_id]
if string_id in {'add_expense', 'nuova_spesa'}:
# Create new expense
new_expense = crea_nuova_spesa(doc)
# find who_paid by name or by username
username_members = list(filter(lambda x: x.username == new_expense["who_paid"], group.members))
name_members = list(filter(lambda x: new_expense["who_paid"] == x.name, group.members))
if len(username_members) == 1 and len(name_members) == 0:
if username_members[0].active_groups[str(group.id)]:
who_paid = username_members[0]
else:
raise TelegramUserNotActive
elif len(name_members) == 1 and len(username_members) == 0:
if name_members[0].active_groups[str(group.id)]:
who_paid = name_members[0]
else:
raise TelegramUserNotActive
elif new_expense['who_paid'] == 'MSG_SENDER':
member = group.__get_member_from_id__(event.from_id)
who_paid = member
else:
raise MalformedMessage('Impossible to determine who paid')
# find list of for_whom by name or by username
for_whom = list()
for string_id in new_expense['for_whom']:
if string_id in {'everyone', 'everybody', 'tutti'}:
for_whom = list(filter(lambda x: x.active_groups[str(group.id)], group.members))
break
username_members = list(filter(lambda x: x.username == string_id, group.members))
name_members = list(filter(lambda x: x.name == string_id, group.members))
if len(username_members) == 1 and len(name_members) == 0:
if username_members[0].active_groups[str(group.id)]:
for_whom.append(username_members[0])
else:
raise TelegramUserNotActive
elif len(name_members) == 1 and len(username_members) == 0:
if name_members[0].active_groups[str(group.id)]:
for_whom.append(name_members[0])
else:
raise TelegramUserNotActive
else:
raise MalformedMessage('Impossible to determine for who it was paid')
expense = Expense(who_paid=who_paid,
for_whom=for_whom,
amount=new_expense['amount'],
currency=new_expense['currency'],
purpose=new_expense['purpose'])
# Confirm or Deny
chat = await event.get_input_chat()
cb_result = await tg_client.send_message(chat, str(expense), buttons=[
[Button.inline('Confirm', data=None), Button.inline('Deny', data=None)]
])
# Global queue for pending msg
create_pending_expense(pending_expenses, expense, event.message.id, cb_result.id, chat_id)
elif string_id == 'disable_member' and bot_called:
name_or_username = remove_member_from_group(doc)
member = find_user(name_or_username, group)
group.disable_chat_member(member.id)
update_group(expense_groups, group)
await event.reply('User disabled')
elif string_id == 'enable_member' and bot_called:
name_or_username = add_member_to_group(doc[1:])
print(name_or_username)
member = find_user(name_or_username, group)
group.enable_chat_member(member.id)
update_group(expense_groups, group)
await event.reply('User enabled')
elif string_id == 'get_balance' and bot_called:
await tg_client.send_message(chat_id, group.get_balance())
pass
elif string_id == 'open_group' and bot_called:
group.open_group()
update_group(expense_groups, group)
elif string_id == 'close_group' and bot_called:
chat = await event.get_input_chat()
cb_result = await tg_client.send_message(chat,
'Are you sure you want to close the expense group?',
buttons=[
[Button.inline('Close', data=None),
Button.inline('Do not close', data=None)]
])
elif string_id == 'get_payments' and bot_called:
#ToDo
pass
except MalformedMessage:
await tg_client.send_message(chat_id, "The message was malformed: retry")
except TelegramUserNotActive:
await tg_client.send_message(chat_id, "One of the involved user is not anymore active in this group")
except ClosedGroup:
await tg_client.send_message(chat_id, "The group is closed to new expenses")
except KeyError:
await tg_client.send_message(chat_id, "The message was malformed, retry")
def allow_connect_to_merchant(json):
user = User.query.filter_by(id=int(json["user"])).first()
new_income_statement = Expense(str(user.id), json["amount"], "Other")
db_session.add(new_income_statement)
db_session.commit()
socketio.emit('transaction-complete', json)
def main():
with app.app_context():
db.metadata.create_all(db.engine)
"""User table"""
with open('_User.json') as user_data_file:
user_data = json.load(user_data_file)
users = user_data["results"]
for user in users:
print 'Firstname = ' + user["firstname"]
print 'Lastname = ' + user["lastname"]
user_row = User(user["firstname"], user["lastname"], user["username"],
user["bcryptPassword"], user["balance"], user["group"],
bool(user["isGroupOwner"]), bool(user["isAdmin"]),
user["saturdayAbsentWeeks"], user["futureSaturdayAbsentWeeks"],
user["sundayAbsentWeeks"], user["futureSundayAbsentWeeks"],
parser.parse(user["createdAt"]), parser.parse(user["updatedAt"]))
db.session.add(user_row)
db.session.commit()
"""CourtsCost table"""
with open('CourtsCost.json') as courts_cost_data_file:
courts_cost_data = json.load(courts_cost_data_file)
courts_costs = courts_cost_data["results"]
for courts_cost in courts_costs:
courts_cost_row = CourtsCost(courts_cost["cost"], courts_cost["day"],
parser.parse(courts_cost["createdAt"]),
parser.parse(courts_cost["updatedAt"]))
db.session.add(courts_cost_row)
db.session.commit()
"""Expenses table"""
with open("Expenses.json") as expenses_data_file:
expenses_data = json.load(expenses_data_file)
expenses = expenses_data["results"]
for expense in expenses:
expense_row = Expense(expense["cost"], expense["costPerPerson"],
expense["description"], expense["numberOfPlayers"],
parser.parse(expense["createdAt"]),
parser.parse(expense["updatedAt"]))
db.session.add(expense_row)
db.session.commit()
"""Transactions table"""
with open("Transactions_Table.json") as transactions_data_file:
transactions_data = json.load(transactions_data_file)
transactions = transactions_data["results"]
for transaction in transactions:
transaction_row = Transaction(transaction["username"], transaction["Amount"],
transaction["Total_Amount"], transaction["Transaction_Type"],
parser.parse(transaction["Date"]["iso"]),
parser.parse(transaction["createdAt"]),
parser.parse(transaction["updatedAt"]))
db.session.add(transaction_row)
db.session.commit()
"""Logs table"""
with open('Logs.json') as data_file:
data = json.load(data_file)
logs = data['results']
for log in logs:
log_row = Log(log["username"], log["description"], parser.parse(log["createdAt"]),
parser.parse(log["updatedAt"]))
db.session.add(log_row)
db.session.commit()
def run_expense(day):
with app.app_context():
if day != "Saturday" and day != "Sunday":
return
courts_cost = CourtsCost.query.filter_by(day=day).first()
if day == "Saturday":
players = User.query.filter_by(isSaturdayAbsent=False).order_by(User.firstname.asc())
absent_players = User.query.filter_by(isSaturdayAbsent=True).order_by(User.firstname.asc())
elif day == "Sunday":
players = User.query.filter_by(isSundayAbsent=False).order_by(User.firstname.asc())
absent_players = User.query.filter_by(isSundayAbsent=True).order_by(User.firstname.asc())
db.session.close()
email_ids = []
body_text = ""
if players.count() > 0:
cost_per_player = float("{0:.2f}".format(float(courts_cost.cost) / float(players.count())))
"""Add expense entry"""
expence_description = ("Deduction_Sunday", "Deduction_Saturday")[day == "Saturday"]
expenseEntry = Expense(courts_cost.cost, cost_per_player, expence_description, players.count())
db.session.add(expenseEntry)
db.session.commit()
body_text += 'Hi everyone,\r\n\nA total of ' + str(players.count()) + ' players attended the play on ' \
+ day + ' and courts booking cost = $' + str(courts_cost.cost) + '. So cost per player = $' + \
str(cost_per_player) + ".\r\n\nBalances of played players are as follows:\r\n\n"
for player in players:
player.balance = float("{0:.2f}".format(player.balance - cost_per_player))
transaction = Transaction(player.email, cost_per_player, player.balance,
("Deduct_Sun", "Deduct_Sat")[day == "Saturday"])
db.session.add(transaction)
db.session.commit()
email_ids.append(player.email);
body_text += player.firstname + ' ' + player.lastname + ' : ' + '$' + str(player.balance) + '\r\n'
body_text += '\nBalances of absent players are as follows\r\n\n'
for absent_player in absent_players:
if day == "Saturday":
absent_player.saturdayAbsentWeeks -= 1
if absent_player.saturdayAbsentWeeks <= 0:
absent_player.saturdayAbsentWeeks = 0
absent_player.isSaturdayAbsent = False
elif day == "Sunday":
absent_player.sundayAbsentWeeks -= 1
if absent_player.sundayAbsentWeeks <= 0:
absent_player.sundayAbsentWeeks = 0
absent_player.isSundayAbsent = False
db.session.commit()
email_ids.append(absent_player.email)
body_text += absent_player.firstname + ' ' + absent_player.lastname + ' : ' + '$' + str(absent_player.balance) + '\r\n'
"""Check if user is absent in future, if so set the absent weeks, absent flag
and set future absent weeks to zero"""
for player in players:
future_absent_weeks = (player.futureSundayAbsentWeeks,
player.futureSaturdayAbsentWeeks)[day == "Saturday"]
if future_absent_weeks > 0:
if day == "Saturday":
player.futureSaturdayAbsentWeeks = 0
player.isSaturdayAbsent = True
player.saturdayAbsentWeeks = future_absent_weeks
elif day == "Sunday":
player.futureSundayAbsentWeeks = 0
player.isSundayAbsent = True
player.sundayAbsentWeeks = future_absent_weeks
db.session.commit()
body_text += '\nThanks\r\nSydney Badminton Group'
# SendGrid.send_email(email_ids, "*****@*****.**",
# "Badminton: Balances after " + day + "'s play", body_text)
return
