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)