def showusers():
if request.method == "GET":
# on page enter, no id will be available so display table
user_id = request.args.get("user_id")
if not user_id:
users = User.query.all()
return render_template('users.html', users=users)
else:
con = db_connect()
cur = con.cursor()
cur.execute("SELECT id, first_name, surname, access_level, approved FROM users WHERE id = :user_id", {'user_id': user_id})
result = cur.fetchall()
return jsonify(serialize(cur, result))
# update user
if request.method == "POST":
id = request.form.get('id')
first_name = request.form.get('first_name')
surname = request.form.get('surname')
access_level = request.form.get('access_level')
approved = request.form.get('approved')
con = db_connect('update')
cur = con.cursor()
sql = """UPDATE users SET first_name=?, surname=?, access_level=?, approved=? WHERE id=?"""
cur.execute(sql, (first_name, surname, access_level, approved, id))
con.commit()
con.close()
flash('User updated')
return redirect(url_for('users.showusers'))
def editrecipes():
""" Edit recipe ingredient details """
if request.method == "POST":
rname = request.form.get('rname')
ingredient_id = request.form.get('ingredient_id')
ingredient_amount = request.form.get('editAmount')
version_number = request.form.get('version_number')
# connect and update database
con = db_connect()
cur = con.cursor()
sql = """UPDATE recipes SET ingredient_amount=?/100.0 WHERE rname=? AND ingredient_id=? AND version_number=?"""
cur.execute(sql, (ingredient_amount, rname, ingredient_id, version_number))
con.commit()
con.close()
return redirect(url_for('recipes.recipesoverview', rname=rname, version_number=version_number))
elif request.method == "GET":
ingredient_id = request.args.get("product_code")
rname = request.args.get("rname")
version_number = request.args.get('version_number')
is_approved = request.args.get('is_approved')
# for recipe approval
if is_approved:
if is_approved == '0':
approved = 1
elif is_approved == '1':
approved = 0
# connect and update database
con = db_connect()
cur = con.cursor()
# update approval on current version
sql = """UPDATE recipes SET approved=? WHERE rname=? AND version_number=?"""
cur.execute(sql, (approved, rname, version_number))
# remove approval from all other recipes (ensures users can only have one version approved)
sql = """UPDATE recipes SET approved=? WHERE rname=? AND NOT version_number=?"""
cur.execute(sql, (0, rname, version_number))
con.commit()
con.close()
# for ingredient amount
con = db_connect()
cur = con.cursor()
cur.execute(""" SELECT name, rname, ingredient_id, round(ingredient_amount*100, 2) AS ingredient_amount
FROM recipes JOIN ingredients ON ingredients.product_code = recipes.ingredient_id
WHERE rname = :rname AND ingredient_id =:ingredient_id AND version_number =:version_number""", {'rname':rname, 'ingredient_id':ingredient_id, 'version_number':version_number})
ingredient = cur.fetchall()
return jsonify(serialize(cur, ingredient))
def edit():
""" Update Product """
if request.method == "POST":
# get id and name for product
pid = request.form.get('id')
product_sku = request.form.get('editProductSku')
product_name = request.form.get('editProductName')
customer_id = request.form.get('editCustomerId')
recipe_id = request.form.get('editRecipeId')
run_rate = request.form.get('editRunRate')
# update product
product = Product.query.filter_by(id=pid).first()
product.product_sku = product_sku
product.product_name = product_name
product.customer_id = customer_id
product.recipe_id = recipe_id
product.run_rate = run_rate
db.session.commit()
flash('Successful. Product updated.')
return redirect(url_for('products.viewproducts'))
# returns JSON data to display the correct ingredient that user wants to edit on modal
elif request.method == "GET":
id = request.args.get("id")
con = db_connect()
cur = con.cursor()
cur.execute("SELECT * FROM products WHERE id = :id", {'id': id})
result = cur.fetchall()
return jsonify(serialize(cur, result))
def editcustomer():
if request.method == "GET":
# on page enter, no id will be available so display table
customer_id = request.args.get("customer_id")
if not customer_id:
customers = Customer.query.all()
return render_template('customer.html', customers=customers)
else:
con = db_connect()
cur = con.cursor()
cur.execute(
"SELECT id, cname, cnumber, fname, sname, email FROM customers WHERE id = :customer_id",
{'customer_id': customer_id})
result = cur.fetchall()
return jsonify(serialize(cur, result))
if request.method == "POST":
cid = request.form.get('cid')
customer = Customer.query.filter_by(id=cid).first()
customer.cname = request.form.get('cname')
customer.cnumber = request.form.get('cnumber')
customer.fname = request.form.get('fname')
customer.sname = request.form.get('sname')
customer.email = request.form.get('email')
db.session.commit()
flash('Edits successful')
return redirect(url_for('customers.customer'))
def recipesoverview(rname, version_number):
""" viewing recipe details """
con = db_connect()
cur = con.cursor()
cur.execute("""SELECT *, round(protein*ingredient_amount, 2) AS SubTotalProtein,
round(carbohydrates*ingredient_amount, 2) AS SubTotalCarbohydrates,
round(sugars*ingredient_amount, 2) AS SubTotalSugars,
round(fats*ingredient_amount, 2) AS SubTotalFats,
round(saturates*ingredient_amount, 2) AS SubTotalSaturates,
round(fibre*ingredient_amount, 2) AS SubTotalFibre,
round(salt*ingredient_amount, 2) AS SubTotalSalt,
round(sodium*ingredient_amount, 2) AS SubTotalSodium,
round(ingredient_amount*100, 2) AS SubTotalIngredient_amount
FROM recipes JOIN ingredients ON ingredients.product_code = recipes.ingredient_id
WHERE rname = :rname AND version_number = :version_number""", {'rname':rname, 'version_number':version_number})
recipe = cur.fetchall()
cur.execute("""SELECT cname FROM customers
JOIN recipes ON recipes.customer_id = customers.id WHERE rname =:rname""", {'rname':rname})
customer = cur.fetchall()
# if not recipe created, flash message
if not recipe:
flash("Error")
return redirect(url_for('recipes.viewRecipes'))
# else show stock information
return render_template("/recipeinfo.html", recipe=recipe, customer=customer)
def get_hourly_count(oee_id):
""" get the hourly column count from OEE_details and count values above 1
to know get a count score throughout the day in order to update hourly
OEE scores """
con = db_connect()
cur = con.cursor()
cur.execute(
"""SELECT COUNT(DISTINCT _07), COUNT(DISTINCT _08), COUNT(DISTINCT _09),
COUNT(DISTINCT _10), COUNT(DISTINCT _11), COUNT(DISTINCT _12),
COUNT(DISTINCT _13), COUNT(DISTINCT _14), COUNT(DISTINCT _15),
COUNT(DISTINCT _16), COUNT(DISTINCT _17), COUNT(DISTINCT _18),
COUNT(DISTINCT _19), COUNT(DISTINCT _20), COUNT(DISTINCT _21),
COUNT(DISTINCT _22) FROM OEE_details where oee_id =:oee_id""",
{'oee_id': oee_id})
hourly_col = cur.fetchall()
count = 0
for i in hourly_col[0]:
if i > 1:
count += 1
return count
def edit():
""" Update Ingredients """
if request.method == "POST":
# get id and name for ingredient
pid = request.form.get('id')
name = request.form.get('product_name')
protein = request.form.get('editProtein')
carbs = request.form.get('editCarbs')
sugars = request.form.get('editSugars')
fat = request.form.get('editFats')
saturates = request.form.get('editSaturates')
fibre = request.form.get('editFibre')
salt = request.form.get('editSalt')
sodium = request.form.get('editSodium')
# update ingredient
con = db_connect()
cur = con.cursor()
sql = """UPDATE ingredients SET name=?, protein=?, carbohydrates=?, sugars=?, fats=?, saturates=?, fibre=?, salt=?, sodium=? WHERE id=?"""
cur.execute(sql, (name, protein, carbs, sugars, fat, saturates, fibre,
salt, sodium, pid))
con.commit()
con.close()
return redirect(url_for('ingredients.showIngredients'))
# returns JSON data to display the correct ingredient that user wants to edit on modal
elif request.method == "GET":
product_code = request.args.get("product_code")
con = db_connect()
cur = con.cursor()
cur.execute(
"SELECT * FROM ingredients WHERE product_code = :product_code",
{'product_code': product_code})
result = cur.fetchall()
return jsonify(serialize(cur, result))
def editscheduledetails():
if request.method == "POST":
schedule_id = request.form.get('schedule_id')
format_date = request.form.get('format_date')
product_id = request.form.getlist('product_id')
line_num = request.form.getlist('line_num')
shift = request.form.getlist('shift')
#batch_number = request.form.getlist('batch_number')
monday = request.form.getlist('monday')
tuesday = request.form.getlist('tuesday')
wednesday = request.form.getlist('wednesday')
thursday = request.form.getlist('thursday')
friday = request.form.getlist('friday')
saturday = request.form.getlist('saturday')
sunday = request.form.getlist('sunday')
con = db_connect()
cur = con.cursor()
idcheck = ScheduleDetails.query.filter_by(schedule_id=schedule_id).all()
if idcheck:
for ind, product_id in enumerate(product_id):
sql = "UPDATE schedule_details SET monday=?, tuesday=?, wednesday=?, thursday=?, friday=?, saturday=?, sunday=? WHERE product_id=? AND schedule_id=? AND line_num=? AND shift=? "
cur.execute(sql, (monday[ind], tuesday[ind], wednesday[ind], thursday[ind], friday[ind], saturday[ind], sunday[ind], product_id, schedule_id, line_num[ind], shift[ind]))
con.commit()
flash('Update successful.')
return redirect(url_for('schedule.scheduledetails', format_date=format_date))
else:
sql = "INSERT INTO schedule_details (schedule_id, product_id, monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
# adding multiple product lines into schedule
query_args = []
for ind, product in enumerate(product_id):
data = (schedule_id, product, monday[ind], tuesday[ind], wednesday[ind], thursday[ind], friday[ind], saturday[ind], sunday[ind])
query_args.append(data)
cur.executemany(sql, query_args)
con.commit()
con.close()
flash('Product and values successfully added')
return redirect(url_for('schedule.viewschedule'))
def get_downtime_minutes(from_date, to_date, line_num, col_type):
con = db_connect()
cur = con.cursor()
cur.execute(
"""SELECT type, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS sum_downtime_am,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) sum_downtime_pm
FROM OEE_details JOIN OEE ON OEE_details.oee_id = OEE.id
WHERE type != 'Product' AND type !='Rejects'
AND DATE(start_date) >= '{}'
AND DATE(start_date) <= '{}' AND line_num {} GROUP BY {} """
.format(from_date, to_date, line_num, col_type))
results = cur.fetchall()
return results
def add_update_oee_details(oee_id, selectorType, selectTime, units):
""" Function to add / update OEE details and update job status """
# stop editing after two days
start_date = OEEtbl.query.filter_by(id=oee_id).first()
date_today = date.today()
delta = date_today - start_date.start_date
if delta.days >= 2:
flash('Editing is no longer possible.')
return redirect(url_for('oee.oeedetails', oee_id=oee_id))
# connect and update database
con = db_connect()
cur = con.cursor()
cur.execute(
"SELECT oee_id FROM OEE_details WHERE oee_id =:oee_id AND type=:type",
{
'oee_id': oee_id,
'type': selectorType
})
row = cur.fetchall()
if len(row) != 1:
# Insert
sql = """INSERT INTO OEE_details (oee_id, type, {}) VALUES (?, ?, ?)""".format(
selectTime)
cur.execute(sql, (oee_id, selectorType, units))
con.commit()
else:
# update
sql = """UPDATE OEE_details SET {}=? WHERE oee_id=? AND type=? """.format(
selectTime)
cur.execute(sql, (units, oee_id, selectorType))
con.commit()
# check if units produced > ordered units and update orders to 'Completed'
if selectorType == "Product":
try:
update_job_status(oee_id)
except:
flash(
'Job status functionality failed. Speak to administrator.')
return redirect(url_for('oee.oeedetails', oee_id=oee_id))
con.close()
return 'Success'
def get_product_count(from_date, to_date, line_num, unit_type):
con = db_connect()
cur = con.cursor()
if unit_type == 'Product' or 'Rejects':
cur.execute(
"""SELECT start_date, type, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS sum_am_count,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS sum_pm_count
FROM OEE_details JOIN OEE ON OEE_details.oee_id = OEE.id
WHERE DATE(start_date) >= '{}'
AND DATE(start_date) <= '{}' AND line_num {} AND type='{}' GROUP BY start_date """
.format(from_date, to_date, line_num, unit_type))
results = cur.fetchall()
return results
def viewAllStock():
""" View all stock on inital stock page """
con = db_connect()
cur = con.cursor()
cur.execute("SELECT product_code, name FROM ingredients")
ingredients = cur.fetchall()
cur.execute(
"SELECT *, ROUND(site_1_stock+site_2_stock, 2) AS totalStock FROM stock"
)
stock_level = cur.fetchall()
return render_template("stockcontrol.html",
stock_level=stock_level,
ingredients=ingredients)
def pmoform():
if request.method == "POST":
details = request.form
firstname = details.get('first_name')
lastname = details.get('last_name')
emailid = details.get('email')
telephone = details.get('telephone')
print(firstname + ',' + lastname + ',' + emailid + ',' + telephone)
region = 'Europe'
market = 'poland'
query = "INSERT INTO pmo_test_1(region,market) VALUES ('Europe','Poland')"
cursor, conn = utils.db_connect()
cursor.execute(query)
conn.commit()
return 'success'
return render_template('PMOForm.html')
def addOee():
""" Add new OEE sheet """
if request.method == "POST":
# store OEE setup data from form
order_id = request.form.get('wOrder_id')
operator_id = current_user.id
shift = request.form.get('shift')
line_num = request.form.get('lineNum')
line_speed = request.form.get('cpm')
actual_operators = request.form.get('actOperators')
product_type = request.form.get('product_type')
# get date
todays_date = date.today()
# get planned output
planned_output = get_planned_output(line_num)
# stop new sheet creation if one already exists for that line
sheet_check = OEEtbl.query.filter_by(start_date=todays_date, shift=shift, line_num=line_num).first()
if sheet_check:
flash('An OEE sheet has already been created today for that shift and line number.')
return redirect(url_for('oee.viewOee'))
# add new OEE sheet
new_oee = OEEtbl(order_id=order_id, operator_id=operator_id, shift=shift, line_num=line_num, start_date=todays_date, speed=line_speed, actual_operators=actual_operators, planned_output=planned_output, product_type=product_type)
db.session.add(new_oee)
db.session.commit()
# update Order status column to indicate job has started
con = db_connect()
cur = con.cursor()
sql = "UPDATE orders SET status=? WHERE order_id=? "
status = "In Progress"
cur.execute(sql, (status, order_id))
con.commit()
con.close()
flash('New OEE sheet added. Click to view.')
return redirect(url_for('oee.viewOee'))
else:
flash('error')
return redirect(url_for('oee.viewOee'))
def addRecipe():
""" Add recipe to database """
rname = request.form.get('rname')
customer_id = request.form.get('customer_id')
flavour = request.form.get('flavour')
bar_weight = request.form.get('bar_weight')
ingredient_id = request.form.getlist('product_code')
ingredient_amount = request.form.getlist('ingredient_amount')
# For incrementing version number when user duplicates recipe or setting at 1 for new recipes
con = db_connect()
cur = con.cursor()
cur.execute('SELECT MAX(version_number) AS version_number FROM recipes WHERE rname=:rname', {'rname':rname})
maxVersion = cur.fetchall()
if maxVersion[0]['version_number'] == None:
version_number = 1
sql = """INSERT INTO recipes (rname, customer_id, flavour, bar_weight,
version_number, ingredient_id, ingredient_amount) VALUES (?, ?, ?, ?, ?, ?, ?/100.0)"""
else:
version_number = maxVersion[0]['version_number'] + 1
sql = """INSERT INTO recipes (rname, customer_id, flavour, bar_weight,
version_number, ingredient_id, ingredient_amount) VALUES (?, ?, ?, ?, ?, ?, ?)"""
# adding multiple ingredients into database
query_args = []
for ind, ingredient in enumerate(ingredient_id):
data = (rname, customer_id, flavour, bar_weight, version_number, ingredient, ingredient_amount[ind])
query_args.append(data)
cur.executemany(sql, query_args)
con.commit()
con.close()
flash('Recipe successfully added')
return redirect(url_for('recipes.recipesoverview', rname=rname, version_number=version_number))
def orderinfo(order_id):
# Current mix size
mix_size = 30000
# select order info
con = db_connect()
cur = con.cursor()
cur.execute(
"""SELECT * FROM orders JOIN customers ON customers.id = orders.customer_id WHERE order_id = :order_id""",
{'order_id': order_id})
order_info = cur.fetchall()
# get recipe name from order
rname = order_info[0]['rname']
# select all approved recipe names by customer
cur.execute(
"""SELECT DISTINCT rname FROM recipes WHERE approved=1 AND customer_id=:customer_id""",
{'customer_id': order_info[0]['customer_id']})
all_recipe = cur.fetchall()
# select recipe details (changed from approved to version number because user may change approval later and the recipe data for order would be incorrect)
cur.execute(
"""SELECT * FROM recipes JOIN ingredients ON
ingredients.product_code = recipes.ingredient_id
WHERE rname=:rname AND version_number =:vn""", {
'rname': rname,
'vn': order_info[0]['recipe_version_number']
})
recipe = cur.fetchall()
# select OEE unit data for insights
cur.execute(
"""SELECT SUM(_07+_08+_09+_10+_11+_12+_13+_14+_15+_16+_17+_18+_19+_20+_21+_22) AS sum
FROM OEE_details JOIN OEE ON OEE_details.oee_id = OEE.id WHERE type="Product" AND order_id=:order_id""",
{'order_id': order_id})
units_produced = cur.fetchall()
return render_template('/orderdetails.html',
order_info=order_info,
recipe=recipe,
mix_size=mix_size,
all_recipe=all_recipe,
units_produced=units_produced)
def stockOverview(product_code):
""" View individual stock information """
con = db_connect()
cur = con.cursor()
cur.execute(
"""SELECT *, round(site_1_stock+site_2_stock, 2) AS stock_level,
(select SUM(site_1_stock+site_2_stock) from stock WHERE product_code =:product_code) AS total_stock,
(select SUM(site_1_stock) from stock WHERE product_code =:product_code) AS site_1,
(select SUM(site_2_stock) from stock WHERE product_code =:product_code) AS site_2
FROM stock WHERE product_code =:product_code""",
{'product_code': product_code})
stockDetails = cur.fetchall()
# if not stock created, flash message
if not stockDetails:
flash("You haven't setup stock configuration for this item")
return redirect(url_for('ingredients.showIngredients'))
# else show stock information
return render_template("/stockinfo.html", stock=stockDetails)
def deleteIngredient():
""" Delete ingredient
Not implemented yet. Need to consider ramifications of
deleting product linked to other areas (i.e. stock, recipes etc)
"""
if request.method == "POST":
id = request.form.get('id')
con = db_connect()
cur = con.cursor()
cur.execute("DELETE FROM ingredients WHERE id = :id ", {'id': id})
con.commit()
con.close()
flash('Ingredient deleted')
return redirect(url_for('ingredients.showIngredients'))
def update_job_status(oee_id):
""" Update job status to 'Complete' if produced qty > ordered qty """
# connect and update database
con = db_connect()
cur = con.cursor()
# with order_id I can sum all rows in table for orders that span across multiple days
cur.execute(
"""SELECT order_id, SUM(_07+_08+_09+_10+_11+_12+_13+_14+_15+_16+_17+_18+_19+_20+_21+_22) AS sum
FROM OEE_details JOIN OEE ON OEE_details.oee_id = OEE.id
WHERE type = 'Product' AND oee_id =:oee_id """,
{'oee_id': oee_id})
row = cur.fetchall()
order_id = row[0]['order_id']
totalUnits = row[0]['sum']
# get total ordered units
cur.execute(
"""SELECT units, status FROM orders WHERE order_id=:order_id""",
{'order_id': order_id})
row = cur.fetchall()
try:
orderedUnits = row[0]['units']
except:
orderedUnits = 0
if totalUnits >= orderedUnits:
# update
sql = """UPDATE orders SET status=? WHERE order_id=? """
status = "Completed"
cur.execute(sql, (status, order_id))
con.commit()
con.close()
return 1
else:
return 0
def createMass(recipe_id):
""" Step 2 - Create the mass """
if request.method == "POST":
recipe_id = recipe_id
ingredient_name = request.form.getlist('ingredient_name')
ingredient_amount = request.form.getlist('ingredient_amount')
ingredient_location = request.form.get('ingredient_location')
# convert name to id
ingredient_id = ingredient_name_to_id(ingredient_name)
# connect and adding multiple ingredients into database
con = db_connect()
cur = con.cursor()
sql = """INSERT INTO recipe_details (recipe_id, ingredient_id, ingredient_amount, ingredient_location) VALUES (?, ?, ?/100.0, ?)"""
query_args = []
for ind, ingredient in enumerate(ingredient_id):
data = (recipe_id, ingredient, ingredient_amount[ind], ingredient_location)
query_args.append(data)
cur.executemany(sql, query_args)
con.commit()
con.close()
flash('Mass successfully added')
return redirect(url_for('recipes.createCaramel', recipe_id=recipe_id))
ingredients = Ingredient.query.all()
return render_template('create_mass.html', ingredients=ingredients, recipe_id=recipe_id)
def addOrders():
if request.method == "POST":
# store order data from form
cid = request.form.get('customer_id')
order_date = datetime.strptime(request.form.get('order_date'),
'%Y-%m-%d')
rname = request.form.get('recipe')
units = request.form.get('units')
batch_code = request.form.get('batch_code')
con = db_connect()
cur = con.cursor()
cur.execute(
'SELECT bar_weight, version_number FROM recipes WHERE rname =:rname AND approved=1',
{'rname': rname})
row = cur.fetchall()
results = serialize(cur, row)
# calculate batch size
unitWeight = results[0]['bar_weight']
batchSize = unitWeight * int(units)
# get recipe version number
version_number = results[0]['version_number']
# add into database
order = Orders(customer_id=cid,
order_date=order_date,
rname=rname,
recipe_version_number=version_number,
units=units,
batch_size=batchSize,
batch_code=batch_code)
db.session.add(order)
db.session.commit()
return redirect(url_for('orders.viewOrders'))
def index():
con = db_connect()
cur = con.cursor()
# Daily units produced chart
todayIndex = date.weekday(date.today())
todaysDate = date.today()
if todayIndex == 0:
firstDateOfWeek = todaysDate
elif todayIndex == 1:
firstDateOfWeek = todaysDate - timedelta(days=1)
elif todayIndex == 2:
firstDateOfWeek = todaysDate - timedelta(days=2)
elif todayIndex == 3:
firstDateOfWeek = todaysDate - timedelta(days=3)
elif todayIndex == 4:
firstDateOfWeek = todaysDate - timedelta(days=4)
elif todayIndex == 5:
firstDateOfWeek = todaysDate - timedelta(days=5)
else:
firstDateOfWeek = todaysDate - timedelta(days=6)
# get schedule id for displaying number of units required in a week
schedule_id = Schedule.query.filter_by(wc_date=firstDateOfWeek).first()
# exception added in case user doesn't add schedule
try:
cur.execute(
"""SELECT SUM(monday + tuesday + wednesday + thursday + friday + saturday + sunday) as weeklyUnitsRequired
from schedule_details WHERE schedule_id = :schedule_id""",
{'schedule_id': schedule_id.id})
weeklyUnitsRequired = cur.fetchone()
weeklyUnitsRequired = f'{weeklyUnitsRequired["weeklyUnitsRequired"]:,}'
except:
weeklyUnitsRequired = 'N/A'
# unit count
cur.execute(
"""SELECT start_date, SUM(_07+_08+_09+_10+_11+_12+_13+_14+_15+_16+_17+_18+_19+_20+_21+_22) AS totalUnits FROM
OEE_details JOIN OEE ON OEE_details.oee_id = OEE.id WHERE type = 'Product'
AND DATE(start_date) >= '{}' AND DATE(start_date) <= '{}' GROUP BY start_date """
.format(firstDateOfWeek, todaysDate))
totalWeeklyUnits = cur.fetchall()
# count total units produced
totalUnitsProduced = 0
for i in totalWeeklyUnits:
totalUnitsProduced += i[1]
# Chart Data
legend = 'Total Units Produced By Day'
labels = []
for i in totalWeeklyUnits:
day = datetime.strptime(i[0], '%Y-%m-%d')
labels.append(day.strftime('%A'))
values = []
for i in totalWeeklyUnits:
values.append(i[1])
data = {
'weeklyUnitsRequired': weeklyUnitsRequired,
'totalUnitsProduced': f'{totalUnitsProduced:,}',
'legend': legend,
'labels': labels,
'values': values
}
return render_template('index.html', **data)
def productionReport():
if request.method == "POST":
from_date = request.form.get('fromDate')
to_date = request.form.get('toDate')
line_num = 'IS NOT NULL'
con = db_connect()
cur = con.cursor()
# get line speed
cur.execute("""SELECT speed FROM OEE WHERE DATE(start_date) >= '{}'
AND DATE(start_date) <= '{}' AND line_num {} GROUP BY start_date
""".format(from_date, to_date, line_num))
line_speed_result = cur.fetchall()
# Data arrays
line_speed_arr = [i[0] for i in line_speed_result]
daily_unit_count = [
i[2] + i[3]
for i in get_product_count(from_date, to_date, line_num, 'Product')
]
daily_reject_count = [
i[2] + i[3]
for i in get_product_count(from_date, to_date, line_num, 'Rejects')
]
daily_downtime_count = [
i[1] + i[2] for i in get_downtime_minutes(from_date, to_date,
line_num, 'start_date')
]
# sum values of data arrays
day_count = len(
get_product_count(from_date, to_date, line_num, 'Product'))
avg_line_speed = sum(line_speed_arr) / day_count
daily_unit_sum = sum(daily_unit_count)
daily_rejects_sum = sum(daily_reject_count)
daily_downtime_sum = sum(daily_downtime_count)
# add data into object
data = OEEcalc(hourly_count=(day_count * 8),
total_lost_minutes=daily_downtime_sum,
CPM=avg_line_speed,
total_unit_count=daily_unit_sum,
total_rejects=daily_rejects_sum)
# return OEE scores
availability = round((data.availability() * 100), 2)
performance = round((data.performance() * 100), 2)
quality = round((data.quality() * 100), 2)
oee_score = round((data.OEEscore() * 100), 2)
##################################
#
# Chart Data
#
##################################
# dates in array
dates = [
i[0]
for i in get_product_count(from_date, to_date, line_num, 'Product')
]
# Downtime Chart (dt)
dt_legend = 'Downtime Data (Minutes)'
dt_labels = [
i[0]
for i in get_downtime_minutes(from_date, to_date, line_num, 'type')
]
dt_values = [
i[1] + i[2]
for i in get_downtime_minutes(from_date, to_date, line_num, 'type')
]
# Daily production count (dp)
dp_legend = 'Daily Production Count'
dp_labels = dates
dp_values = [
i[2] + i[3]
for i in get_product_count(from_date, to_date, line_num, 'Product')
]
# Daily PAQ-OEE chart (oee)
try:
oee_dict = oee_to_dict(daily_downtime_count, line_speed_arr,
daily_unit_count, daily_reject_count)
except:
flash(
"Today's data is not complete. Use yesterday as the max date.")
return redirect(url_for('reports.productionReport'))
oee_legend = 'PAQ-OEE Chart'
oee_labels = dates
oee_values = [
oee_dict['performance'], oee_dict['availability'],
oee_dict['quality'], oee_dict['OEE']
]
######################################
data = {
'from_date':
from_date,
'to_date':
to_date,
'avg_daily_good_count':
f'{round((daily_unit_sum-daily_rejects_sum) / day_count):,}',
'avg_daily_rejects':
daily_rejects_sum / day_count,
'avg_daily_downtime':
timedelta(minutes=round(daily_downtime_sum / day_count)),
'total_count':
f'{daily_unit_sum:,}',
'total_good_count':
f'{daily_unit_sum - daily_rejects_sum:,}',
'total_rejects':
f'{daily_rejects_sum:,}',
'total_downtime':
timedelta(minutes=round(daily_downtime_sum)),
'oee_score':
oee_score,
'availability':
availability,
'performance':
performance,
'quality':
quality,
'dt_legend':
dt_legend,
'dt_labels':
dt_labels,
'dt_values':
dt_values,
'dp_legend':
dp_legend,
'dp_labels':
dp_labels,
'dp_values':
dp_values,
'oee_legend':
oee_legend,
'oee_labels':
oee_labels,
'oee_p':
oee_values[0],
'oee_a':
oee_values[1],
'oee_q':
oee_values[2],
'oee_o':
oee_values[3]
}
return render_template('productionreport.html', **data)
return render_template('productionreport.html')
def editOrders():
""" Edit order details """
if request.method == "POST":
order_id = request.form.get('order_id')
rname = request.form.get('recipe')
units = request.form.get('units')
status = request.form.get('status')
# get recipe details
con = db_connect()
cur = con.cursor()
cur.execute(
'SELECT bar_weight, version_number FROM recipes WHERE rname =:rname AND approved=1',
{'rname': rname})
row = cur.fetchall()
results = serialize(cur, row)
# re-calculate batch size
unitWeight = results[0]['bar_weight']
batchSize = unitWeight * int(units)
# update the version number
version_number = results[0]['version_number']
"""
Stop edit maybe required, currently unknown.
Editing allowed for the timebeing to allow user
to change status to In Progress - useful if job is
auto-completed and further production is required
# stop edit if the job has started or completed
cur.execute("SELECT status FROM orders WHERE order_id = :order_id", {'order_id':order_id})
results = cur.fetchall()
status = results[0]['status']
if status == 'In Progress':
flash('Cannot edit once the job has started')
return redirect(url_for('orders.orderinfo',order_id=order_id))
elif status == 'Completed':
flash('Cannot edit once the job has completed')
return redirect(url_for('orders.orderinfo',order_id=order_id))
"""
sql = """UPDATE orders SET rname=?, recipe_version_number=?, units=?, status=?, batch_size=? WHERE order_id=?"""
cur.execute(
sql, (rname, version_number, units, status, batchSize, order_id))
con.commit()
con.close()
return redirect(url_for('orders.orderinfo', order_id=order_id))
elif request.method == "GET":
order_id = request.args.get("order_id")
con = db_connect()
cur = con.cursor()
cur.execute("SELECT * FROM orders WHERE order_id = :order_id",
{'order_id': order_id})
row = cur.fetchall()
result = serialize(cur, row)
return jsonify(result)
else:
return redirect(url_for('orders.viewOrders'))
def oeedetails(oee_id):
if request.form.get('selector'):
selectTime = request.form.get('time')
units = request.form.get('unitData')
selectTime = request.form.get('time')
selectorType = request.form.get('selector')
# pass details into add / update function
add_update_oee_details(oee_id, selectorType, selectTime,units)
return redirect(url_for('oee.oeedetails', oee_id=oee_id))
oeeInfo = OEEtbl.query.filter(OEEtbl.id==oee_id).first()
con = db_connect()
cur = con.cursor()
cur.execute("""SELECT *, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amSum,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmSum
FROM OEE_details WHERE oee_id = :oee_id GROUP BY type
ORDER BY (CASE type WHEN 'Product' THEN 1 END) DESC""", {'oee_id':oee_id})
oeeStats = cur.fetchall()
# get planned output (data is stored in OEE but originally set in schedule details. See utils)
query = OEEtbl.query.filter_by(id=oee_id).first()
planned_output = query.planned_output
"""
--------------
# calculate OEE (Availability * Performance * Quality = Overall Equipment Effectiveness)
--------------
"""
cur.execute("""SELECT SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amShift,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmShift FROM
OEE_details WHERE type != 'Product' AND type !='Rejects' AND oee_id = :oee_id """, {'oee_id':oee_id})
downtimeStats = cur.fetchall()
cur.execute(""" SELECT SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amRejects,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmRejects FROM
OEE_details WHERE type = 'Rejects' AND oee_id = :oee_id """, {'oee_id':oee_id})
rejectStats = cur.fetchall()
if downtimeStats[0]['amShift'] or downtimeStats[0]['pmShift']:
# calc total lost minutes
total_lost_minutes = downtimeStats[0]['amShift'] + downtimeStats[0]['pmShift']
# calc total units produced
total_unit_count = oeeStats[0]['amSum'] + oeeStats[0]['pmSum']
# calc total rejected products
if rejectStats[0]['amRejects']:
total_rejects = rejectStats[0]['amRejects'] + rejectStats[0]['pmRejects']
else:
total_rejects = 0
CPM = oeeInfo.speed
# get hourly count
hourly_count = get_hourly_count(oee_id)
# add data into object
data = OEEcalc(hourly_count, total_lost_minutes, CPM, total_unit_count, total_rejects)
# return OEE scores
availability = round((data.availability()*100),2)
performance = round((data.performance()*100),2)
quality = round((data.quality()*100),2)
oeeScore = round((data.OEEscore()*100),2)
return render_template('oeedetails.html', oeeInfo=oeeInfo, oeeStats=oeeStats, availability=availability, performance=performance, quality=quality, oeeScore=oeeScore, planned_output=planned_output)
else:
return render_template('oeedetails.html', oeeInfo=oeeInfo, oeeStats=oeeStats, planned_output=planned_output)
def shiftreport():
if request.method == "POST":
oee_id = request.form.get('shiftSelect')
if oee_id == None:
flash('No shift reports available')
return redirect(url_for('oee.shiftreport'))
con = db_connect()
cur = con.cursor()
# unit count
cur.execute(
"""SELECT SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amShift,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmShift FROM
OEE_details WHERE type = 'Product' AND oee_id = :oee_id """,
{'oee_id': oee_id})
unit_count = cur.fetchall()
# overall shift details
cur.execute(
"""SELECT *, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amSum,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmSum
FROM OEE_details WHERE oee_id = :oee_id GROUP BY type
ORDER BY (CASE type WHEN 'Product' THEN 1 END) DESC""",
{'oee_id': oee_id})
oee_stats = cur.fetchall()
# rejects
cur.execute(
""" SELECT SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amRejects,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmRejects FROM
OEE_details WHERE type = 'Rejects' AND oee_id = :oee_id """,
{'oee_id': oee_id})
reject_stats = cur.fetchall()
# downtime stats
cur.execute(
"""SELECT *, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amShift,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmShift FROM
OEE_details WHERE type != 'Product' AND type !='Rejects' AND oee_id = :oee_id """,
{'oee_id': oee_id})
downtimeStats = cur.fetchall()
# machine running speed (CPM)
query = OEEtbl.query.filter(OEEtbl.id == oee_id).first()
CPM = query.speed
# chart downtime data
cur.execute(
"""SELECT type, SUM(_07+_08+_09+_10+_11+_12+_13+_14) AS amSum,
SUM(_15+_16+_17+_18+_19+_20+_21+_22) AS pmSum
FROM OEE_details WHERE type != 'Product' AND type !='Rejects' AND oee_id = :oee_id GROUP BY type
""", {'oee_id': oee_id})
chart_data = cur.fetchall()
# calc total lost minutes
try:
total_lost_minutes = downtimeStats[0]['amShift'] + downtimeStats[
0]['pmShift']
except TypeError:
total_lost_minutes = 0
# calc total units produced
try:
total_unit_count = unit_count[0]['amShift'] + unit_count[0][
'pmShift']
except TypeError:
total_unit_count = 0
# calc total rejected products
try:
total_rejects = reject_stats[0]['amRejects'] + reject_stats[0][
'pmRejects']
except TypeError:
total_rejects = 0
# calc good count
good_count = total_unit_count - total_rejects
hourly_count = 8
# add data into object
data = OEEcalc(hourly_count, total_lost_minutes, CPM, total_unit_count,
total_rejects)
# return OEE scores
availability = round((data.availability() * 100), 2)
performance = round((data.performance() * 100), 2)
quality = round((data.quality() * 100), 2)
oee_score = round((data.OEEscore() * 100), 2)
# get conformance To Plan (CTP) score
ctp = get_conformance_to_plan(oee_id, good_count)
# format lost time
total_lost_minutes = timedelta(minutes=total_lost_minutes)
# format shift length
shift_length = timedelta(minutes=data.shift_length)
# format good count
good_count = f'{good_count:,}'
# format total unit count
total_unit_count = f'{total_unit_count:,}'
# calc run time
run_time = shift_length - total_lost_minutes
""" Chart Data """
legend = 'Downtime Data (Minutes)'
labels = []
for i in chart_data:
labels.append(i[0])
values = []
for i in chart_data:
values.append(i[1] + i[2])
oee_info = OEEtbl.query.filter_by(id=oee_id).first()
oee_list = OEEtbl.query.order_by(desc(
OEEtbl.start_date)).limit(20).all()
return render_template('shiftreport.html',
good_count=good_count,
oee_list=oee_list,
oee_stats=oee_stats,
availability=availability,
performance=performance,
quality=quality,
oee_score=oee_score,
total_lost_minutes=total_lost_minutes,
total_unit_count=total_unit_count,
total_rejects=total_rejects,
shift_length=shift_length,
run_time=run_time,
legend=legend,
labels=labels,
values=values,
oee_info=oee_info,
ctp=ctp)
oee_list = OEEtbl.query.order_by(desc(OEEtbl.start_date)).limit(20).all()
return render_template('shiftreport.html', oee_list=oee_list)
