diff options
Diffstat (limited to 'nutrinoweb/controllers/database_manager.py')
| -rw-r--r-- | nutrinoweb/controllers/database_manager.py | 94 |
1 files changed, 65 insertions, 29 deletions
diff --git a/nutrinoweb/controllers/database_manager.py b/nutrinoweb/controllers/database_manager.py index c2e7618..d3128ee 100644 --- a/nutrinoweb/controllers/database_manager.py +++ b/nutrinoweb/controllers/database_manager.py @@ -5,7 +5,7 @@ from flask import Flask, request, session, g, redirect, url_for, abort, \ render_template, flash # python import -import os, re +import os, re, time # server import from server.tools import storage @@ -26,16 +26,18 @@ DATABASE3 = os.path.join(DATA, 'aliments.db') # SCHEMA paths SCHEMA1 = os.path.join(SCHEMA, 'schema1.sql') SCHEMA2 = os.path.join(SCHEMA, 'schema2.sql') +SCHEMA3 = os.path.join(SCHEMA, 'schema3.sql') # FILE paths -FILE_DINPUT = os.path.join(FILES, 'dbinput.txt') +FILE_NINPUT = os.path.join(FILES, 'dbinputnutrition.txt') FILE_SPARTI = os.path.join(FILES, 'dbinputsparetime.txt') # .. DATA_ALIMTS = os.path.join(DATA, 'aliments.txt') DATA_STATUS = os.path.join(DATA, 'status.txt') +DATA_LOG = os.path.join(DATA, 'log.txt') # ensure FILES -storage.check_file(FILE_DINPUT) +storage.check_file(FILE_NINPUT) storage.check_file(FILE_SPARTI) # .. storage.check_file(DATA_ALIMTS) @@ -81,7 +83,7 @@ def init_db(database): db.commit() elif database == 'activity': with closing(connect_db('activity')) as db: - with app.open_resource(SCHEMA1) as f: + with app.open_resource(SCHEMA3) as f: db.cursor().executescript(f.read()) db.commit() @@ -94,18 +96,18 @@ def load_inputfileactivities(): for line in fobj: zuordnung = line.split("|") - g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [zuordnung[0], zuordnung[1], zuordnung[2], zuordnung[3], zuordnung[4], zuordnung[5], zuordnung[6], zuordnung[7], zuordnung[8], zuordnung[9], zuordnung[10], zuordnung[11], zuordnung[12], zuordnung[13], zuordnung[14], zuordnung[15], zuordnung[16], zuordnung[17], zuordnung[18], zuordnung[19], zuordnung[20], zuordnung[21], zuordnung[22]]) + g.db.execute('insert into activities (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [zuordnung[0], zuordnung[1], zuordnung[2], zuordnung[3], zuordnung[4], zuordnung[5], zuordnung[6], zuordnung[7], zuordnung[8], zuordnung[9], zuordnung[10], zuordnung[11], zuordnung[12], zuordnung[13], zuordnung[14], zuordnung[15], zuordnung[16], zuordnung[17], zuordnung[18], zuordnung[19], zuordnung[20], zuordnung[21], zuordnung[22]]) g.db.commit() fobj.close() def load_inputfilenutrition(): g.db = connect_db('nutrition') - fobj = open(FILE_DINPUT) + fobj = open(FILE_NINPUT) for line in fobj: zuordnung = line.split("|") - g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, path, scenario) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [zuordnung[0], zuordnung[1], zuordnung[2], zuordnung[3], zuordnung[4], zuordnung[5], zuordnung[6], zuordnung[7], zuordnung[8], zuordnung[9], zuordnung[10], zuordnung[11], zuordnung[12], zuordnung[13], zuordnung[14], zuordnung[15], zuordnung[16], zuordnung[17], zuordnung[18], zuordnung[19], zuordnung[20], zuordnung[21], zuordnung[22], zuordnung[23], zuordnung[24]]) + g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, path, scenario, tablealiment, serving) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [zuordnung[0], zuordnung[1], zuordnung[2], zuordnung[3], zuordnung[4], zuordnung[5], zuordnung[6], zuordnung[7], zuordnung[8], zuordnung[9], zuordnung[10], zuordnung[11], zuordnung[12], zuordnung[13], zuordnung[14], zuordnung[15], zuordnung[16], zuordnung[17], zuordnung[18], zuordnung[19], zuordnung[20], zuordnung[21], zuordnung[22], zuordnung[23], zuordnung[24], 'no', zuordnung[25]]) g.db.commit() fobj.close() @@ -113,15 +115,15 @@ def entries(): """Make sure we are connected to the database.""" g.db = connect_db('nutrition') # TODO - check the table exist or create it - cur = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario from entries order by id desc') - entries = [dict(title=row[0], water=row[1], energie=row[2], calcium=row[3], iron=row[4], magnesium=row[5], phosphorus=row[6], potassium=row[7], sodium=row[8], zinc=row[9], copper=row[10], vit_c=row[11], thiamin=row[12], riboflavin=row[13], niacin=row[14], panto_acid=row[15], vit_b6=row[16], folate_tot=row[17], vit_b12=row[18], vit_a=row[19], vit_e=row[20], vit_d=row[21], protein=row[22], scenario=row[23] ) for row in cur.fetchall()] + cur = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario, serving from entries order by id desc') + entries = [dict(title=row[0], water=row[1], energie=row[2], calcium=row[3], iron=row[4], magnesium=row[5], phosphorus=row[6], potassium=row[7], sodium=row[8], zinc=row[9], copper=row[10], vit_c=row[11], thiamin=row[12], riboflavin=row[13], niacin=row[14], panto_acid=row[15], vit_b6=row[16], folate_tot=row[17], vit_b12=row[18], vit_a=row[19], vit_e=row[20], vit_d=row[21], protein=row[22], scenario=row[23], serving=row[24] ) for row in cur.fetchall()] return entries def entriessparetime(): """Make sure we are connected to the database.""" g.db = connect_db('activity') # TODO - check the table exist or create it - cur = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein from entries order by id desc') + cur = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein from activities order by id desc') entries = [dict(title=row[0], water=row[1], energie=row[2], calcium=row[3], iron=row[4], magnesium=row[5], phosphorus=row[6], potassium=row[7], sodium=row[8], zinc=row[9], copper=row[10], vit_c=row[11], thiamin=row[12], riboflavin=row[13], niacin=row[14], panto_acid=row[15], vit_b6=row[16], folate_tot=row[17], vit_b12=row[18], vit_a=row[19], vit_e=row[20], vit_d=row[21], protein=row[22]) for row in cur.fetchall()] return entries @@ -130,33 +132,45 @@ def add_entry(database): g.db = connect_db(database) if database == 'nutrition': - g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', - [request.form['title'].upper(), request.form['water'], request.form['energie'], request.form['calcium'], request.form['iron'], request.form['magnesium'], request.form['phosphorus'], request.form['potassium'], request.form['sodium'], request.form['zinc'], request.form['copper'], request.form['vit_c'], request.form['thiamin'], request.form['riboflavin'], request.form['niacin'], request.form['panto_acid'], request.form['vit_b6'], request.form['folate_tot'], request.form['vit_b12'], request.form['vit_a'], request.form['vit_e'], request.form['vit_d'], request.form['protein'], request.form['scenario']]) + g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario, tablealiment, serving) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', + [request.form['title'].upper(), request.form['water'], request.form['energie'], request.form['calcium'], request.form['iron'], request.form['magnesium'], request.form['phosphorus'], request.form['potassium'], request.form['sodium'], request.form['zinc'], request.form['copper'], request.form['vit_c'], request.form['thiamin'], request.form['riboflavin'], request.form['niacin'], request.form['panto_acid'], request.form['vit_b6'], request.form['folate_tot'], request.form['vit_b12'], request.form['vit_a'], request.form['vit_e'], request.form['vit_d'], request.form['protein'], request.form['scenario'], request.form['tablealiment'], request.form['serving']]) g.db.commit() elif database == 'activity': - g.db.execute('insert into entries (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', + g.db.execute('insert into activities (title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [request.form['title'].upper(), request.form['water'], request.form['energie'], request.form['calcium'], request.form['iron'], request.form['magnesium'], request.form['phosphorus'], request.form['potassium'], request.form['sodium'], request.form['zinc'], request.form['copper'], request.form['vit_c'], request.form['thiamin'], request.form['riboflavin'], request.form['niacin'], request.form['panto_acid'], request.form['vit_b6'], request.form['folate_tot'], request.form['vit_b12'], request.form['vit_a'], request.form['vit_e'], request.form['vit_d'], request.form['protein']]) g.db.commit() - + + line = request.form['title'] + '|' + request.form['water'] + '|' + request.form['energie'] + '|' + request.form['calcium'] + '|' + request.form['iron'] + '|' + request.form['magnesium'] + '|' + request.form['phosphorus'] + '|' + request.form['potassium'] + '|' + request.form['sodium'] + '|' + request.form['zinc'] + '|' + request.form['copper'] + '|' + request.form['vit_c'] + '|' + request.form['thiamin'] + '|' + request.form['riboflavin'] + '|' + request.form['niacin'] + '|' + request.form['panto_acid'] + '|' + request.form['vit_b6'] + '|' + request.form['folate_tot'] + '|' + request.form['vit_b12'] + '|' + request.form['vit_a'] + '|' + request.form['vit_e'] + '|' + request.form['vit_d'] + '|' + request.form['protein'] + '|' + request.form['scenario'] + '|' + request.form['tablealiment'] + '|' + request.form['serving'] + write_log(line, 's') flash('New entry was successfully posted') return redirect(url_for('addentry')) def delete_entry(database): g.db = connect_db(database) - g.db.execute('delete from entries where title=?', [request.form['title'].upper()]) - g.db.commit() + if database == 'nutrition': + g.db.execute('delete from entries where title=?', [request.form['title'].upper()]) + g.db.commit() + elif database == 'activity': + g.db.execute('delete from activities where title=?', [request.form['title'].upper()]) + g.db.commit() flash('Entry is successfully deleted') return redirect(url_for('addentry')) -def show_entry(database): +def show_entry(database, name): g.db = connect_db(database) # TODO - check the table exist or create it if database == 'nutrition': - entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario from entries where title=?', [request.form['title'].upper()]).fetchone() + entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario, tablealiment, serving from entries where title=?', [name.upper()]).fetchone() elif database == 'activity': - entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein from entries where title=?', [request.form['title'].upper()]).fetchone() + entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein from activities where title=?', [name.upper()]).fetchone() + + return entry +def show_aliment(name): + g.db = connect_db('nutrition') + # TODO - check the table exist or create it + entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, scenario, tablealiment, serving from entries where title=?', [name]).fetchone() return entry def check_values(): @@ -168,23 +182,17 @@ def check_values(): return error def return_entry(): - entry = [request.form['title'].upper(), request.form['water'], request.form['energie'], request.form['calcium'], request.form['iron'], request.form['magnesium'], request.form['phosphorus'], request.form['potassium'], request.form['sodium'], request.form['zinc'], request.form['copper'], request.form['vit_c'], request.form['thiamin'], request.form['riboflavin'], request.form['niacin'], request.form['panto_acid'], request.form['vit_b6'], request.form['folate_tot'], request.form['vit_b12'], request.form['vit_a'], request.form['vit_e'], request.form['vit_d'], request.form['protein'], request.form['scenario']] + entry = [request.form['title'].upper(), request.form['water'], request.form['energie'], request.form['calcium'], request.form['iron'], request.form['magnesium'], request.form['phosphorus'], request.form['potassium'], request.form['sodium'], request.form['zinc'], request.form['copper'], request.form['vit_c'], request.form['thiamin'], request.form['riboflavin'], request.form['niacin'], request.form['panto_acid'], request.form['vit_b6'], request.form['folate_tot'], request.form['vit_b12'], request.form['vit_a'], request.form['vit_e'], request.form['vit_d'], request.form['protein'], request.form['scenario'], request.form['tablealiment'], request.form['serving']] flash('Correct entry') return entry - -def reset_status(): - fobj = open(DATA_STATUS, 'wb') - fobj.truncate() - fobj.write('|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0') + '|' + str('0')) - fobj.close() def add_entry_tolist(aliment, path, quantity, unit): g.db = connect_db('nutrition') # TODO - check the table exist or create it - entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein from entries where title=?', [aliment]).fetchone() + entry = g.db.execute('select title, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein, serving from entries where title=?', [aliment]).fetchone() g.db = connect_db3() # TODO - check the table exist or create it - g.db.execute('insert into aliments (title, path, quantity, unit, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [aliment.lower(), path, quantity, unit, entry[1], entry[2], entry[3], entry[4], entry[5], entry[6], entry[7], entry[8], entry[9], entry[10], entry[11], entry[12], entry[13], entry[14], entry[15], entry[16], entry[17], entry[18], entry[19], entry[20], entry[21], entry[22]]) + g.db.execute('insert into aliments (title, path, quantity, unit, serving, water, energie, calcium, iron, magnesium, phosphorus, potassium, sodium, zinc, copper, vit_c, thiamin, riboflavin, niacin, panto_acid, vit_b6, folate_tot, vit_b12, vit_a, vit_e, vit_d, protein) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [aliment.lower(), path, quantity, unit, entry[23], entry[1], entry[2], entry[3], entry[4], entry[5], entry[6], entry[7], entry[8], entry[9], entry[10], entry[11], entry[12], entry[13], entry[14], entry[15], entry[16], entry[17], entry[18], entry[19], entry[20], entry[21], entry[22]]) g.db.commit() def aliments(): @@ -202,4 +210,32 @@ def save_aliments(aliments): fobj.write('|' + str(aliment['quantity']) + ',' + aliment['title']) fobj.close() - +def return_tablealiments(scenario): + g.db = connect_db('nutrition') + if scenario == 'fruits': + cur = g.db.execute('select title from entries where tablealiment=? and scenario=?', ['yes', scenario]) + tablealiments = [row[0] for row in cur.fetchall()] + elif scenario == 'meatcounter': + cur = g.db.execute('select title from entries where tablealiment=? and scenario=?', ['yes', scenario]) + tablealiments = [row[0] for row in cur.fetchall()] + elif scenario == 'fridge': + cur = g.db.execute('select title from entries where tablealiment=? and scenario=?', ['yes', scenario]) + tablealiments = [row[0] for row in cur.fetchall()] + elif scenario == 'rack': + cur = g.db.execute('select title from entries where tablealiment=? and scenario=?', ['yes', scenario]) + tablealiments = [row[0] for row in cur.fetchall()] + else: + pass + return tablealiments + +def write_log(entry, t): + fh = open(DATA_LOG, 'a') + timeFrame = '\n' + time.strftime("%d.%m.%Y %H:%M") + ': ' + fh.write(timeFrame) + if t == 'a': + for aliment in entry: + log_entry = str(aliment['quantity']) + ' ' + aliment['unit'] + ' from ' + aliment['title'] + ', ' + fh.write(log_entry) + elif t == 's': + fh.write(entry) + fh.close() |