def most_bought_of_one_product_by_groups_from_group(drinkid, groupid,
parsedbegin, parsedend):
count = {}
purchases = Purchase.query.filter(
and_(Purchase.product_id == drinkid, Purchase.timestamp >= parsedbegin,
Purchase.timestamp <= parsedend, Purchase.round == False)).all()
for pur in purchases:
if User.query.get(pur.user_id).usergroup_id == groupid:
if pur.user_id not in count:
count[pur.user_id] = pur.amount
else:
count[pur.user_id] = count[pur.user_id] + pur.amount
count_groups = {}
for u_id, amount in count.items():
group = User.query.get(u_id).usergroup_id
if group not in count_groups:
count_groups[group] = amount
else:
count_groups[group] = count_groups[group] + amount
datag = []
for g_id, amount in count_groups.items():
datag.append(
(g_id, Usergroup.query.get(g_id).name, round_down(amount)))
idsg, valuesg, labelsg = top_n(count_groups, datag, 20)
return idsg, valuesg, labelsg
def borrel_mode(drink_id=None):
global borrel_mode_enabled, borrel_mode_drinks
# Get the user that is paying for everything in borrel mode
borrel_mode_user = int(settings['borrel_mode_user'])
# If the user is -1, borrel mode is disabled so we do not have to execute anything
if borrel_mode_enabled:
# If the current drink is in the products...
if drink_id is None or drink_id in borrel_mode_drinks:
total_bought = 0
# We count the total amount of borrel mode products bought (also before the borrel started)
for p1 in borrel_mode_drinks:
for p in Purchase.query.filter(Purchase.user_id == borrel_mode_user, Purchase.product_id == p1,
Purchase.price > 0).all():
total_bought += p.amount
total_bought = round_up(total_bought)
# Calculate how many products are left
left_over = round_up(float(settings['borrel_mode_amount']), 2) + round_up(float(settings['borrel_mode_start_amount']), 2) - total_bought
# If it is zero or less...
if left_over <= 0:
# We disable borrel mode by changing some settings and variables
update_settings('borrel_mode_user', -1)
borrel_mode_enabled = False
return None
# If borrel mode is on, we set the boolean and return how many is left and who is paying
borrel_mode_enabled = True
return {'left': round_down(left_over, 0),
'user': User.query.get(borrel_mode_user).name}
return None
def most_bought_products_by_users(parsedbegin, parsedend):
count = {}
purchases = Purchase.query.filter(
and_(Purchase.timestamp >= parsedbegin,
Purchase.timestamp <= parsedend,
Purchase.product_id != settings['dinner_product_id'],
Purchase.round == False)).all()
for pur in purchases:
if pur.product_id not in count:
count[pur.product_id] = pur.amount
else:
count[pur.product_id] = count[pur.product_id] + pur.amount
datap = []
for p_id, amount in count.items():
datap.append((p_id, Product.query.get(p_id).name, round_down(amount)))
return topall(count, datap)
def most_bought_of_one_product_by_users(drinkid, parsedbegin, parsedend):
count = {}
purchases = Purchase.query.filter(
and_(Purchase.product_id == drinkid, Purchase.timestamp >= parsedbegin,
Purchase.timestamp <= parsedend, Purchase.round == False)).all()
for pur in purchases:
if pur.user_id not in count:
count[pur.user_id] = pur.amount
else:
count[pur.user_id] = count[pur.user_id] + pur.amount
datau = []
for u_id, amount in count.items():
datau.append((u_id, User.query.get(u_id).name, round_down(amount)))
idsu, valuesu, labelsu = top_n(count, datau, 20)
return idsu, valuesu, labelsu
def most_bought_products_per_user(userid, parsedbegin, parsedend, n=20):
count = {}
purchases = Purchase.query.filter(
and_(Purchase.user_id == userid, Purchase.timestamp >= parsedbegin,
Purchase.timestamp <= parsedend, Purchase.round == False,
Purchase.product_id != settings['dinner_product_id'])).all()
for p in purchases:
if p.product_id not in count:
count[p.product_id] = p.amount
else:
count[p.product_id] = count[p.product_id] + p.amount
data = []
for p_id, amount in count.items():
data.append((p_id, Product.query.get(p_id).name, round_down(amount)))
ids, values, labels = top_n(count, data, n)
return ids, values, labels
def addpurchase(drink_id, user_id, quantity, rondje, price_per_one):
# Round quantity to at most two decimals
quantity = round_up(quantity)
# Get drink and user objects from database
drink = Product.query.get(drink_id)
user = User.query.get(user_id)
# If the price is zero, we do not have to take any inventory
if price_per_one > 0:
# If we product is not a mix, we can simply take it from inventory
if drink.recipe_input is None:
inventory = take_from_inventory(user, drink_id, quantity)
# However, if the product is a mix...
else:
inventory = {'costs': 0, 'inventory_usage': []}
# For every ingredient of the mix...
for r in Recipe.query.filter(Recipe.product_id == drink.id).all():
# Take the respective quantity from inventory
result = take_from_inventory(user, r.ingredient_id, round_up(r.quantity * quantity))
# Add the costs of the ingredient to the total costs
inventory['costs'] = inventory['costs'] + result['costs']
# Add the inventory usage
inventory['inventory_usage'] = inventory['inventory_usage'] + result['inventory_usage']
else:
# We take no inventory if the price is zero, so the costs are zero
inventory = {'costs': 0, 'inventory_usage': []}
# Get the profitgroup of the user
profitgroup = Usergroup.query.get(user.profitgroup_id)
# Calculate the profit
prof = round_down((price_per_one * quantity - inventory['costs']) * app.config['PROFIT_PERCENTAGE'])
# Add the profit to the group
profitgroup.profit = profitgroup.profit + prof
# Create a row object for the profit table
profit_obj = Profit(profitgroup_id=profitgroup.id, timestamp=datetime.now(),
percentage=app.config['PROFIT_PERCENTAGE'], change=prof, new=profitgroup.profit)
db.session.add(profit_obj)
# Save to database
db.session.commit()
# Calculate the new user balance
user.balance = user.balance - round_up(float(price_per_one) * quantity)
# Create a purchase entry in the table, so we can use its purchase ID to create the transaction
purchase = Purchase(user_id=user.id, timestamp=datetime.now(), product_id=drink.id, price=price_per_one,
amount=quantity, round=rondje)
db.session.add(purchase)
db.session.commit()
# Create all inventory usage entries
for x in inventory['inventory_usage']:
i_u = Inventory_usage(purchase_id=purchase.id, inventory_id=x['id'], quantity=x['quantity'])
db.session.add(i_u)
db.session.commit()
# Set the object to None to clean up after creation
i_u = None
# Calculate the change in balance
balchange = round_down(-price_per_one * quantity)
# Create a transaction entry and add it to the database
transaction = Transaction(user_id=user.id, timestamp=datetime.now(), purchase_id=purchase.id,
profit_id=profit_obj.id, balchange=balchange, newbal=user.balance)
db.session.add(transaction)
db.session.commit()
# Update the daily stats with the new purchase
statshandler.update_daily_stats('euros', balchange)
statshandler.update_daily_stats_purchase(user_id, drink_id, quantity, rondje, price_per_one)
return quantity, drink.name, user.name, "success"
def correct_inventory(json):
# Create a list of all groups that will participate in this inventory correction
all_groups = set()
for i in json:
s = set(i['groups'])
all_groups.update(s)
all_groups = list(all_groups)
per_group_costs = [0] * len(all_groups)
total_costs = 0
# Create a document for the overview of this correction
now = datetime.now()
document = Document()
document.add_heading('Inventaris Correctie', 0)
document.add_paragraph("Op {} om {} is er een inventariscorrectie uitgevoerd. Hierbij zijn de volgende wijzigingen "
"doorgevoerd in de inventaris.".format(now.strftime("%Y-%m-%d"), now.strftime("%H:%M:%S")))
# Table layout: Product name | Inventory in Tikker | Real inventory | Difference | Costs | <per group its costs or
# profit
table = document.add_table(rows=1, cols=5 + len(all_groups))
header_cells = table.rows[0].cells
header_cells[0].text = "Productnaam"
header_cells[1].text = "Inv in Tikker"
header_cells[2].text = "Echte inv"
header_cells[3].text = "Verschil"
header_cells[4].text = "Kosten"
for i in range(0, len(all_groups)):
header_cells[5 + i].text = "Kosten {}".format(Usergroup.query.get(all_groups[i]).name)
for i in json:
# Get the product object
p = Product.query.get(i['product_id'])
# Calculate the inventory difference
tikker_inv = calcStock(p.id)
diff = int(i['stock']) - int(tikker_inv)
# Create a new row for the document table
row_cells = table.add_row().cells
row_cells[0].text = p.name
row_cells[1].text = str(tikker_inv)
row_cells[2].text = str(i['stock'])
row_cells[3].text = str(diff)
if diff < 0:
row_cells[4].text = '-€ %.2f' % round_up(-diff * p.price)
total_costs += round_up(-diff * p.price)
elif diff > 0:
row_cells[4].text = '€ %.2f' % round_down(diff * p.price)
total_costs += round_down(diff * p.price)
# If the difference is less than 0, we have lost some inventory, which costs money
if diff < 0:
take_from_inventory(None, p.id, -diff)
for g_id in i['groups']:
# Get the group object
g = Usergroup.query.get(g_id)
# Calculate the costs for this group
cost = round_down(diff * p.price / len(i['groups']))
# Change its profit
g.profit = g.profit + cost
profit = Profit(profitgroup_id=g_id, timestamp=datetime.now(), percentage=1.0,
change=cost, new=g.profit, description="{} {} inventariscorrectie".format(str(diff), p.name))
db.session.add(profit)
db.session.commit()
# Now, take it from inventory
# Add it to the row in the table and to the cumulative costs
index = all_groups.index(g_id)
per_group_costs[index] += cost
row_cells[5 + index].text = '-€ %.2f' % -cost
# Reset the group object
g = None
elif diff > 0:
new_price = find_newest_product_price(p.id)
add_inventory(p.id, diff, new_price, "Inventariscorrectie")
for g_id in i['groups']:
g = Usergroup.query.get(g_id)
profit_for_group = round_down(diff * new_price / len(i['groups']))
g.profit = g.profit + profit_for_group
profit = Profit(profitgroup_id=g_id, timestamp=datetime.now(), percentage=1.0,
change=profit_for_group, new=g.profit,
description="{} {} inventariscorrectie".format(str(diff), p.name))
db.session.add(profit)
db.session.commit()
# Add it to the row in the table and to the cumulative costs
index = all_groups.index(g_id)
per_group_costs[index] += profit_for_group
row_cells[5 + index].text = '€ %.2f' % profit_for_group
# Reset the group object
g = None
row_cells = table.add_row().cells
if total_costs < 0:
row_cells[4].text = '-€ %.2f' % -total_costs
else:
row_cells[4].text = '€ %.2f' % total_costs
for i in range(0, len(per_group_costs)):
if per_group_costs[i] < 0:
row_cells[5 + i].text = '-€ %.2f' % -per_group_costs[i]
else:
row_cells[5 + i].text = '€ %.2f' % per_group_costs[i]
# Try to save the file
# If it is not possible because the file is locked, try it again with a different filename until it works
count = 0
while True:
filename = os.path.join(app.config['DOCUMENT_FOLDER'], 'inventariscorrectie_{}_{}.docx'
.format(now.strftime("%Y%m%d"), count))
# If the file exists, raise the file name and try again
if os.path.exists(filename):
count += 1
continue
try:
# If saving the file is successful, escape the loop and finish
document.save(filename)
break
# If the file is opened, catch the error and try again
except PermissionError:
count += 1
return "Inventaris correctie succesvol doorgevoerd! Het rapport is opgeslagen in {}".format(filename), "success"
def admin_treasurer():
check_if_local_machine()
filters_inv = InventoryFilterForm()
filters_users = UsersFilterForm()
now = datetime.now()
three_months_ago = now - timedelta(days=7 * 12)
'''
Query the data from the database and calculate other necessary values
'''
# Query the inventory quantity left for each product
rawquery = db.session.query(Product.name, Product.id, Product.category, Product.inventory_warning,
func.sum(Inventory.quantity.cast(Integer)).label('quantity'),
func.sum(Inventory.quantity * Inventory.price_before_profit)
.label('inventory_value'))\
.filter(and_(Product.id == Inventory.product_id, Product.purchaseable == True,
Product.id != dbhandler.settings['dinner_product_id']))\
.group_by(Inventory.product_id)
# Apply the filters to the query
if 'f_product_category' in request.args and request.args.get(
'f_product_category') != 'all':
rawquery = rawquery.filter(
Product.category == request.args.get('f_product_category'))
# Convert the result from a list of tuples to a list of dicts
products = [p._asdict() for p in rawquery.all()]
# Calculate the total value of the inventory
total_p_value = sum([p['inventory_value'] for p in products])
# Query the consumption of each product per week over a period of 12 weeks
purchases = db.session.query(Purchase.product_id.label('id'),
func.sum(Purchase.amount / 12).label('per_week'))\
.filter(Purchase.round == False, Purchase.timestamp > three_months_ago)\
.group_by(Purchase.product_id).all()
# Convert the result to a list of dicts
purchases = [p._asdict() for p in purchases]
# Function to calculate when the stock will be empty
def stock_empty(p, source):
return datetime.now() + timedelta(days=int(p['quantity'] /
p['per_week'] * 7))
# Merge the products and purchases table
products = merge_queries(products, purchases, ['per_week', 'stock_empty'],
[0, None], [None, None], [None, stock_empty])
'''
Create the analysis graphs
'''
# Create two empty lists for parsed data for the graphs
qdata, vdata = [], []
# For each product...
for p in products:
# Add a tuple to the lists
qdata.append((p['id'], p['name'], round(p['quantity'])))
vdata.append((p['id'], p['name'], round_down(p['inventory_value'])))
# Process the data lists for the graphs on the page
product_q, product_v = {}, {}
product_q['ids'], product_q['value'], product_q[
'labels'] = statshandler.topall(None, qdata)
product_v['ids'], product_v['value'], product_v[
'labels'] = statshandler.topall(None, vdata)
'''
Create the category graphs
'''
# Create two empty lists for parsed data for the graphs
qdata, vdata = [], []
# Create a dictionary to store all the cumulative data
categories = {}
# Loop over all products
for p in products:
# If a product category is not yet in the dictionary...
if p['category'] not in categories:
# Add it with the corresponding values
categories[p['category']] = {
'quantity': p['quantity'],
'value': p['inventory_value']
}
# If is is, increment the values in the dictionary
else:
categories[p['category']]['quantity'] += p['quantity']
categories[p['category']]['value'] += p['inventory_value']
# Remove the empty category if it is present
if "" in categories:
categories.pop("")
# Add all the data to the two lists
for category, v in categories.items():
qdata.append((0, category, round(v['quantity'])))
vdata.append((0, category, round(v['value'])))
# Parse the data into readable data for the graphs
category_q, category_v = {}, {}
category_q['ids'], category_q['value'], category_q[
'labels'] = statshandler.topall(None, qdata)
category_v['ids'], category_v['value'], category_v[
'labels'] = statshandler.topall(None, vdata)
'''
Query the users table
'''
# The base query for all the users
users_query = db.session.query(User.id, User.name, User.balance, Usergroup.name.label('group'))\
.filter(User.usergroup_id == Usergroup.id)\
.order_by(User.usergroup_id)
# Stolen from the function above, because the filter function cannot be called.
# There are two tables on this page and applying filters on both causes problems
if 'f_user_usergroup' in request.args and int(
request.args.get('f_user_usergroup')) > 0:
users_query = users_query.filter(
User.usergroup_id == int(request.args.get('f_user_usergroup')))
if 'f_user_profitgroup' in request.args and int(
request.args.get('f_user_profitgroup')) > 0:
users_query = users_query.filter(
User.profitgroup_id == int(request.args.get('f_user_profitgroup')))
# Finish the query and convert all items to dictionaries
users = [u._asdict() for u in users_query.all()]
# Query the amount every user spends per week (to merge later)
purchase1 = db.session.query(Purchase.user_id.label('id'),
func.sum(Purchase.amount * Purchase.price / 12).label('per_week'))\
.filter(Purchase.timestamp > three_months_ago).group_by(Purchase.user_id).all()
# Convert it to a dictionary
purchase1 = [p._asdict() for p in purchase1]
# Query the average balance of every user during three months
transaction1 = db.session.query(Transaction.user_id.label('id'),
func.avg(Transaction.newbal).label('average_balance'))\
.filter(Transaction.timestamp > three_months_ago).group_by(Transaction.user_id).all()
# Convert it to a dictionary
transaction1 = [t._asdict() for t in transaction1]
# Query the last time the user had a positive balance (earliest from now on)
transaction2 = db.session.query(Transaction.user_id.label('id'), Transaction.timestamp.label('last_positive'))\
.filter(Transaction.newbal > 0).group_by(Transaction.user_id).all()
# Convert it to a dictionary
transaction2 = [t._asdict() for t in transaction2]
# Condition that will be used later when merging
def positive_balance(user):
if user['balance'] > 0:
return True
return False
# Function to calculate the (expected) time until the user has no more balance
def no_balance_left(u, source):
return datetime.now() + timedelta(days=int(u['balance'] /
u['per_week'] * 7))
# Merge all the three seperate queries with the users query
users = merge_queries(users, purchase1, ['per_week', 'no_balance_left'],
[0, None], [None, positive_balance],
[None, no_balance_left])
users = merge_queries(users, transaction1, ['average_balance'], [None],
[None], [None])
users = merge_queries(users, transaction2, ['last_positive'],
[datetime.now()], [None], [None])
# Because not every user has an average balance and because it is hard to parameterize
# in the merge_queries function above, we add this average balance manually
for u in users:
if u['average_balance'] is None:
u['average_balance'] = u['balance']
# Calculate the sum of all the balances and other numbers
total_u_balance = sum([u['balance'] for u in users])
total_u_average = sum([u['average_balance'] for u in users])
total_u_per_week = sum([u['per_week'] for u in users])
'''
Finalize
'''
# Calculate the render time
render_time = int((datetime.now() - now).microseconds / 1000)
return render_template('admin/treasurer.html',
title='Penno Panel',
h1='Penno Panel',
products=products,
filters_inv=filters_inv,
filters_users=filters_users,
total_p_value=total_p_value,
product_q=product_q,
product_v=product_v,
category_q=category_q,
category_v=category_v,
rendertime=render_time,
users=users,
total_u_balance=total_u_balance,
total_u_average=total_u_average,
total_u_per_week=total_u_per_week), 200