1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
from __future__ import with_statement
from sqlite3 import dbapi2 as sqlite3
from contextlib import closing
from flask import Flask, request, session, g, redirect, url_for, abort, \
render_template, flash
# python import
import os, re
# server import
from server.tools import storage
DATA = storage.get_path(path='data', bundle=False)
SCHEMA = storage.get_path(path='schema', bundle=True)
FILES = storage.get_path(path='files', bundle=True)
# ensure paths
storage.check_dir(DATA)
storage.check_dir(SCHEMA)
storage.check_dir(FILES)
# DB paths
DATABASE1 = os.path.join(DATA, 'nutrition.db')
DATABASE2 = os.path.join(DATA, 'sparetime.db')
DATABASE3 = os.path.join(DATA, 'aliments.db')
# SCHEMA paths
SCHEMA1 = os.path.join(SCHEMA, 'schema1.sql')
SCHEMA2 = os.path.join(SCHEMA, 'schema2.sql')
# FILE paths
FILE_DINPUT = os.path.join(FILES, 'dbinput.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')
# ensure FILES
storage.check_file(FILE_DINPUT)
storage.check_file(FILE_SPARTI)
# ..
storage.check_file(DATA_ALIMTS)
storage.check_file(DATA_STATUS)
# configuration
DEBUG = True
SECRET_KEY = '12345'
app = Flask(__name__)
app.config.from_envvar('NUTRINOWEB_SETTINGS', silent=True)
def connect_db(database):
"""Returns a new connection to the database."""
try:
print DATABASE1
print DATABASE2
print DATABASE3
if database == 'nutrition':
return sqlite3.connect(DATABASE1)
elif database == 'activity':
return sqlite3.connect(DATABASE2)
except:
return redirect(url_for('addentry'))
def connect_db3():
"""Returns a new connection to the database."""
return sqlite3.connect(DATABASE3)
def init_db3():
"""Creates the database tables."""
with closing(connect_db3()) as db:
with app.open_resource(SCHEMA2) as f:
db.cursor().executescript(f.read())
db.commit()
def init_db():
"""Creates the database tables."""
with closing(connect_db()) as db:
with app.open_resource(SCHEMA1) as f:
db.cursor().executescript(f.read())
db.commit()
def load_inputfilenutrition():
g.db = connect_db('activity')
fobj = open(FILE_DINPUT)
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.commit()
fobj.close()
def load_inputfile():
g.db = connect_db('nutrition')
fobj = open(FILE_SPARTI)
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, sceanrio) 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], 0, 0])
g.db.commit()
fobj.close()
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()]
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')
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
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.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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
[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()
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()
flash('Entry is successfully deleted')
return redirect(url_for('addentry'))
def show_entry(database):
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()
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()
return entry
def check_values():
error = False
for val in request.form:
if (val != 'title'):
if (re.match(request.form[val],r"[0-9].")):
error = True
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']]
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()
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.commit()
def aliments():
"""Make sure we are connected to the database."""
g.db = connect_db3()
# TODO - check the table exist or create it
cur = g.db.execute('select 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 from aliments')
aliments = [dict(title=row[0], path=row[1], quantity=row[2], unit=row[3], water=row[4], energie=row[5], calcium=row[6], iron=row[7], magnesium=row[8], phosphorus=row[9], potassium=row[10], sodium=row[11], zinc=row[12], copper=row[13], vit_c=row[14], thiamin=row[15], riboflavin=row[16], niacin=row[17], panto_acid=row[18], vit_b6=row[19], folate_tot=row[20], vit_b12=row[21], vit_a=row[22], vit_e=row[23], vit_d=row[24], protein=row[25]) for row in cur.fetchall()]
return aliments
def save_aliments(aliments):
fobj = open(DATA_ALIMTS, 'wb')
fobj.truncate()
for aliment in aliments:
fobj.write('|' + str(aliment['quantity']) + ',' + aliment['title'])
fobj.close()
|
