def add_tree():
db = get_db()
if request.method == 'POST':
try:
area = request.form['area']
number = request.form['number']
except:
flash('Post incorrect!')
print('Post incorrect!')
return redirect(url_for('naju.add_tree'))
error = None
if area is None:
error = "Es muss eine Fläche ausgewählt werden"
if number is None:
error = "Es wird eine Nummer benötigt"
try:
number = nbit_int(number)
except ValueError:
error = "Es wird eine Zahl benötigt"
db = get_db()
check = None
if error is None:
check = db.execute(
"SELECT * FROM tree t, area a WHERE a.id = t.area_id AND t.number = ? AND a.name = ?",
(number, area)).fetchone()
if check is not None:
error = "Dieser Baum existiert bereits!"
if error is None:
area_id = db.execute("SELECT id FROM area WHERE name = ?",
(area, )).fetchone()
db.execute("INSERT INTO tree (number, area_id) VALUES (?, ?)",
(number, area_id['id']))
params = db.execute('SELECT * FROM tree_param_type').fetchall()
tree = db.execute(
'SELECT * FROM tree WHERE number = ? AND area_id = ?',
(number, area_id['id'])).fetchone()
for param in params:
db.execute(
"INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)",
(tree['id'], param['id'], ""))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
areas = db.execute('SELECT * FROM area ORDER BY name').fetchall()
return render_template('naju/add_tree.html', areas=areas)
def add_param():
db = get_db()
if request.method == 'POST':
try:
name = request.form['name']
type = request.form['type']
except:
flash('Post incorrect!')
print('Post incorrect!')
return redirect(url_for('naju.add_tree'))
error = None
if name is None:
error = "Es muss ein Name angegeben werden"
if type is None or not is_type_valid(type):
error = "Es wird ein valider Datentyp benötigt"
db = get_db()
check = db.execute("SELECT * FROM tree_param_type WHERE name = ?",
(name, )).fetchone()
if check is not None:
error = "Dieser Parameter existirt bereits"
if error is None:
db.execute(
'INSERT INTO tree_param_type (name, type, order_id) VALUES (?, ?, ?)',
(name, type, 0))
param = db.execute(
'SELECT * FROM tree_param_type WHERE name=? AND type=?',
(name, type)).fetchone()
trees = db.execute('SELECT * FROM tree').fetchall()
for tree in trees:
db.execute(
"INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)",
(tree['id'], param['id'], ""))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/add_param.html')
def read(file):
document = minidom.parse(file)
users = document.getElementsByTagName('user')
db = get_db()
valid_user = False
for user in users:
to_check = db.execute('SELECT * FROM user WHERE name=? OR email=?',
(user.attributes['name'].value,
user.attributes['name'].value)).fetchone()
if to_check is not None and check_password_hash(
to_check['pwd_hash'], user.attributes['pass'].value):
valid_user = True
if not valid_user:
return False
handle_area(document, db)
handle_trees(document, db)
handle_param_types(document, db)
handle_params(document, db)
db.commit()
import os
if os.path.exists(file):
os.remove(file)
return True
def handle_params(document, db=get_db()):
params = document.getElementsByTagName('param')
for param in params:
tree = db.execute(
'SELECT a.name, t.number, t.id FROM area a, tree t WHERE t.area_id = a.id AND t.number = ? '
'AND a.name = ?',
(param.attributes['tree_number'].value,
param.attributes['tree_area_name'].value)).fetchone()
param_type = db.execute(
'SELECT * FROM tree_param_type WHERE name = ?',
(param.attributes['param_type'].value, )).fetchone()
if tree is None or param_type is None:
continue
to_edit = db.execute(
'SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ?',
(tree['id'], param_type['id'])).fetchone()
if to_edit is None:
db.execute(
'INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)',
(tree['id'], param_type['id'],
param.attributes['value'].value))
to_edit = db.execute(
'SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ?',
(tree['id'], param_type['id'])).fetchone()
if to_edit['value'] != param.attributes['value'].value:
db.execute(
"UPDATE tree_param SET value=? WHERE tree_id=? AND param_id=?",
(param.attributes['value'].value, tree['id'],
param_type['id']))
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def sql_access():
""" SQL-Zugriffsseite """
reload_response = redirect(url_for(".sql_access"), code=303)
if request.method != "POST":
return render_template("naju/sql_access.html", query="", result=None)
# ELSE
query = request.form["query"]
if not query:
flash("Bitte geben Sie eine SQL-Abfrage ein.", "error")
return reload_response
# Query ausführen
db = get_db()
try:
result = db.execute(query)
except DatabaseError as exc:
flash(
f"Die SQL-Abfrage konnte nicht ausgeführt werden.\n\nFehlerbeschreibung:\n{exc}",
"error")
return render_template("naju/sql_access.html",
query=query,
result=None)
db.commit()
flash("Die SQL-Abfrage wurde erfolgreich ausgeführt.", "success")
return render_template("naju/sql_access.html", query=query, result=result)
def del_user(id):
db = get_db()
db.execute('DELETE FROM user WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('naju.home'))
def update_data():
if request.method == 'POST':
try:
data = request.files['data']
except:
return '<error>Fehler: Daten sind falsch angegeben</error>'
if data and allowed_file(data.filename):
from . import read_xml
filename = secure_filename(data.filename)
filename = filename.rsplit('.')[0]
import time
path = os.path.join(current_app.instance_path, 'assets', 'uploads')
os.makedirs(path, exist_ok=True)
path = os.path.join(path, str(time.time()) + filename + '.xml')
data.save(path)
if read_xml.read(path):
db = get_db()
trees = db.execute('SELECT * FROM tree').fetchall()
areas = db.execute('SELECT * FROM area').fetchall()
tree_params = db.execute('SELECT * FROM tree_param').fetchall()
tree_param_types = db.execute(
'SELECT * FROM tree_param_type').fetchall()
return render_template('export.html',
trees=trees,
areas=areas,
tree_params=tree_params,
tree_param_types=tree_param_types)
return '<error>Fehler: Falscher Benutzername oder Passwort</error>'
return render_template('import.html')
def delete_tree(id):
db = get_db()
db.execute('DELETE FROM tree WHERE id=?', (id, ))
db.execute('DELETE FROM tree_param WHERE tree_id=?', (id, ))
db.commit()
return redirect(url_for('naju.home'))
def confirm_mail(token):
db = get_db()
user = db.execute('SELECT * FROM user WHERE confirmation_token = ?',
(token, )).fetchone()
if user is not None:
db = get_db()
db.execute(
'UPDATE user SET email_confirmed = ?, confirmation_token = ?', (
1,
None,
))
db.commit()
return redirect(url_for('naju.index'))
else:
return redirect(url_for('home.index'))
def edit_param(id):
db = get_db()
param = db.execute('SELECT * FROM tree_param_type WHERE id=?',
(id, )).fetchone()
if request.method == 'POST':
try:
name = request.form['name']
type = request.form['type']
order_id = request.form['number']
except:
flash('Post incorrect!')
print('Post incorrect!')
return redirect(url_for('naju.add_tree'))
error = None
if name is None:
error = "Es muss ein Name angegeben werden"
if type is None or not is_type_valid(type):
error = "Es wird ein valider Datentyp benötigt"
db = get_db()
check = db.execute("SELECT * FROM tree_param_type WHERE name = ?",
(name, )).fetchone()
if check is not None and check['id'] != id:
error = "Dieser Parameter existirt bereits"
if error is None:
db.execute(
'UPDATE tree_param_type SET name=?, type=?, order_id=? WHERE id=?',
(name, type, order_id, id))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/edit_param.html', param=param)
def tree(id):
db = get_db()
tree = db.execute(
'SELECT * FROM tree t, area a WHERE t.area_id = a.id AND t.id = ?',
(id, )).fetchone()
params = db.execute(
'SELECT * FROM tree_param tp, tree_param_type tpt '
'WHERE tp.tree_id = ? AND tpt.id = tp.param_id ORDER BY tpt.name',
(id, )).fetchall()
return render_template('naju/tree.html', tree=tree, params=params)
def password_reset(token):
db = get_db()
user = db.execute('SELECT * FROM user WHERE password_reset_token = ?',
(token, )).fetchone()
if user is not None:
if request.method == 'POST':
try:
name = str(request.form['username']).lower()
password = request.form['password']
check = request.form['passwordcheck']
except:
flash('Post incorrect!')
return redirect(url_for('home.index'))
if password == '' or str(password).isspace():
flash('You must give a password')
return render_template('naju/reset_password.html')
user = db.execute(
'SELECT * FROM user WHERE password_reset_token = ? AND name = ?',
(
token,
name,
)).fetchone()
if user is not None and password == check:
db = get_db()
db.execute(
'UPDATE user SET pwd_hash = ?, password_reset_token = ? WHERE id = ?',
(
generate_password_hash(password),
None,
user['id'],
))
db.commit()
return redirect(url_for('naju.index'))
return render_template('naju/reset_password.html')
else:
return redirect(url_for('naju.index'))
def reset_password(token):
if token is None or token == "" or token == 'None':
return redirect('naju.index')
if request.method == 'POST':
try:
username = str(request.form['username']).lower()
password = request.form['password']
check = request.form['passwordcheck']
agreement = request.form['agreement']
except:
flash('Post incorrect!')
flash(
'Unter umständen wurde der Datenschutzvereinbarung nicht zugestimmt! Diese Zustimmung ist '
'erforderlich!')
return render_template('naju/activate_user.html')
if not agreement:
flash('Es wurde der Datenschutzvereinbarung nicht zugestimmt!')
return render_template('naju/activate_user.html')
if password == '' or str(password).isspace():
flash('Es muss ein Password angegeben sein!')
return render_template('naju/activate_user.html')
if check != password:
flash('Die Passwörter müssen übereinstimmen!')
return render_template('naju/activate_user.html')
db = get_db()
user = db.execute(
'SELECT * FROM user WHERE confirmation_token=? AND name=?', (
token,
username,
)).fetchone()
if user is not None:
db.execute(
'UPDATE user SET pwd_hash=?, confirmation_token=?, email_confirmed=? WHERE id=?',
(
generate_password_hash(password),
None,
1,
user['id'],
))
db.commit()
return redirect(url_for('naju.index'))
flash('Maybe your username was typed wrong. Please try again!')
return render_template('naju/activate_user.html')
def delete_area(id):
db = get_db()
trees = db.execute('SELECT * FROM tree WHERE area_id=?', (id, )).fetchall()
for tree in trees:
db.execute('DELETE FROM tree WHERE id=?', (tree['id'], ))
db.execute('DELETE FROM tree_param WHERE tree_id=?', (tree['id'], ))
db.execute('DELETE FROM area WHERE id=?', (id, ))
db.commit()
return redirect(url_for('naju.home'))
def register():
if request.method == 'POST':
try:
username = str(request.form['username']).lower()
mail = str(request.form['mail']).lower()
agreement = request.form['agreement']
except:
flash('Post incorrect!')
flash(
'Unter umständen wurde der Datenschutzvereinbarung nicht zugestimmt! Diese Zustimmung ist '
'erforderlich!')
return redirect(url_for('naju.index'))
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE name = ? or email = ?', (
username,
mail,
)).fetchone()
if not agreement:
error = 'Es wurde der Datenschutzvereinbarung nicht zugestimmt!'
elif user is not None:
error = 'Der Benutzername oder die E-Mailaddresse sind bereits in Benutzung.'
elif not username:
error = 'Sie haben keinen Benutzernamen angegeben.'
elif not mail or not util.check_email(mail):
error = 'Sie haben keine E-Mail Addresse angegeben.'
if error is None:
token = random_uri_safe_string(64)
db.execute(
'INSERT INTO user (name, email, pwd_hash, level, email_confirmed, confirmation_token)'
' VALUES (?, ?, ?, ?, ?, ?)',
(username, mail, '', 0, 0, token))
db.commit()
user = db.execute(
'SELECT * FROM user WHERE name = ? AND email = ?', (
username,
mail,
)).fetchone()
from naju.emails import send_confirmation_email
send_confirmation_email(user['email'], user, token)
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/register.html')
def edit_area(id):
db = get_db()
my_area = db.execute('SELECT * FROM area WHERE id=?', (id, )).fetchone()
if request.method == 'POST':
try:
name = request.form['name']
short = request.form['short']
address = request.form['address']
link = request.form['link']
except:
flash('Post incorrect!')
print('Post incorrect!')
return render_template('naju/add_area.html')
error = None
if name is None:
error = "Es wird ein Name benötigt"
if short is None:
error = "Es wird ein kürzel benötigt"
if address is None:
error = "Es wird eine Addresse benötigt"
if link is None or not is_http_url(link):
error = "Es wird ein Link benötigt"
check = db.execute("SELECT * FROM area WHERE short = ? OR name = ?",
(short, name)).fetchone()
if check is not None and check['id'] != id:
error = "Diese Fläche existiert bereits!"
if error is None:
if my_area['name'] != name:
db.execute("UPDATE area SET name=? WHERE id=?", (name, id))
if my_area['short'] != short:
db.execute("UPDATE area SET short=? WHERE id=?", (short, id))
if my_area['address'] != name:
db.execute("UPDATE area SET address=? WHERE id=?",
(address, id))
if my_area['link'] != short:
db.execute("UPDATE area SET link=? WHERE id=?", (link, id))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/edit_area.html', area=my_area)
def handle_param_types(document, db=get_db()):
param_types = document.getElementsByTagName('type')
for param_type in param_types:
to_edit = db.execute(
'SELECT * FROM tree_param_type WHERE name = ?',
(param_type.attributes['name'].value, )).fetchone()
if to_edit is None:
db.execute(
'INSERT INTO tree_param_type (name, type, order_id) VALUES (?, ?, ?)',
(param_type.attributes['name'].value,
param_type.attributes['type'].value,
param_type.attributes['order_id'].value))
to_edit = db.execute(
'SELECT * FROM tree_param_type WHERE name = ?',
(param_type.attributes['name'].value, )).fetchone()
trees = db.execute('SELECT * FROM tree').fetchall()
for tree in trees:
db.execute(
"INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)",
(tree['id'], to_edit['id'], ""))
if param_type.attributes['delete'].value == 'delete':
db.execute('DELETE FROM tree_param WHERE param_id=?',
(to_edit['id'], ))
db.execute('DELETE FROM tree_param_type WHERE id=?',
(to_edit['id'], ))
continue
if to_edit['name'] != param_type.firstChild.data:
db.execute("UPDATE tree_param_type SET name=? WHERE id=?",
(param_type.firstChild.data, to_edit['id']))
if to_edit['type'] != param_type.attributes['type'].value:
db.execute("UPDATE tree_param_type SET type=? WHERE id=?",
(param_type.attributes['type'].value, to_edit['id']))
if to_edit['order_id'] != param_type.attributes['order_id'].value:
db.execute(
"UPDATE tree_param_type SET order_id=? WHERE id=?",
(param_type.attributes['order_id'].value, to_edit['id']))
def handle_area(document, db=get_db()):
areas = document.getElementsByTagName('area')
for area in areas:
to_edit = db.execute('SELECT * FROM area WHERE name = ?',
(area.attributes['name'].value, )).fetchone()
if to_edit is None:
db.execute(
'INSERT INTO area (name, address, link, short) VALUES (?, ?, ?, ?)',
(area.attributes['name'].value,
area.attributes['address'].value,
area.attributes['link'].value,
area.attributes['short'].value))
to_edit = db.execute('SELECT * FROM area WHERE name = ?',
(area.attributes['name'].value, )).fetchone()
id = to_edit['id']
if area.attributes['delete'].value == 'delete':
trees = db.execute('SELECT * FROM tree WHERE area_id=?',
(id, )).fetchall()
for tree in trees:
db.execute('DELETE FROM tree WHERE id=?', (tree['id'], ))
db.execute('DELETE FROM tree_param WHERE tree_id=?',
(tree['id'], ))
db.execute('DELETE FROM area WHERE id=?', (id, ))
continue
if area.firstChild.data != to_edit['name']:
db.execute("UPDATE area SET name=? WHERE id=?",
(area.firstChild.data, id))
if area.attributes['short'].value != to_edit['short']:
db.execute("UPDATE area SET short=? WHERE id=?",
(area.attributes['short'].value, id))
if area.attributes['address'].value != to_edit['address']:
db.execute("UPDATE area SET address=? WHERE id=?",
(area.attributes['address'].value, id))
if area.attributes['link'].value != to_edit['link']:
db.execute("UPDATE area SET link=? WHERE id=?",
(area.attributes['link'].value, id))
def handle_trees(document, db=get_db()):
trees = document.getElementsByTagName('tree')
for tree in trees:
area = db.execute('SELECT * FROM area WHERE name = ?',
(tree.attributes['area_name'].value, )).fetchone()
print(area)
if area is None:
continue
to_edit = db.execute(
'SELECT * FROM tree WHERE number = ? AND area_id = ?',
(tree.attributes['number'].value, area['id'])).fetchone()
if to_edit is None:
db.execute('INSERT INTO tree (number, area_id) VALUES (?, ?)',
(tree.attributes['number'].value, area['id']))
to_edit = db.execute(
'SELECT * FROM tree WHERE number = ? AND area_id = ?',
(tree.attributes['number'].value, area['id'])).fetchone()
params = db.execute('SELECT * FROM tree_param_type').fetchall()
for param in params:
db.execute(
"INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)",
(to_edit['id'], param['id'], ""))
print(to_edit)
if tree.attributes['delete'].value == 'delete':
db.execute('DELETE FROM tree WHERE id=?', (to_edit['id'], ))
db.execute('DELETE FROM tree_param WHERE tree_id=?',
(to_edit['id'], ))
continue
if to_edit['number'] != tree.firstChild.data:
db.execute("UPDATE tree SET number=? WHERE id=?",
(tree.firstChild.data, to_edit['id']))
def index():
if g.user is not None:
return redirect(url_for('naju.home'))
if request.method == 'POST':
try:
test_name = request.form['username']
password = request.form['password']
username = str(test_name).lower()
except:
flash('Post incorrect!')
print('Post incorrect!')
return render_template('naju/login.html')
db = get_db()
error = None
user = db.execute(
'SELECT * FROM user WHERE name = ? OR email = ? OR name = ?',
(username, username, test_name)).fetchone()
if password == '':
error = 'Es wurde kein Passwort angegeben!'
elif user is None:
error = 'Der Benutzername existiert nicht.'
elif not check_password_hash(user['pwd_hash'], password):
error = 'Das Passwort ist falsch.'
elif user['email_confirmed'] == 0:
error = 'Ihre E-Mail ist noch nicht verifiziert.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/login.html')
def add_area():
if request.method == 'POST':
try:
name = request.form['name']
short = request.form['short']
address = request.form['address']
link = request.form['link']
except:
flash('Post incorrect!')
print('Post incorrect!')
return render_template('naju/add_area.html')
error = None
if name is None:
error = "Es wird ein Name benötigt"
if short is None:
error = "Es wird ein kürzel benötigt"
if address is None:
error = "Es wird eine Addresse benötigt"
if link is None or not is_http_url(link):
error = "Es wird ein Link benötigt"
db = get_db()
check = db.execute("SELECT * FROM area WHERE short = ? OR name = ?",
(short, name)).fetchone()
if check is not None:
error = "Diese Fläche existiert bereits!"
if error is None:
db.execute(
"INSERT INTO area (name, short, address, link) VALUES (?, ?, ?, ?)",
(name, short, address, link))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/add_area.html')
def reset_password_request():
if request.method == 'POST':
try:
username = str(request.form['username']).lower()
mail = str(request.form['mail']).lower()
except:
flash('Post incorrect!')
print('Post incorrect!')
return render_template('naju/login.html')
db = get_db()
user = db.execute('SELECT * FROM user WHERE name = ? AND email = ?', (
username,
mail,
)).fetchone()
print(str(user))
users = db.execute('SELECT * FROM user').fetchone()
print(users['email'])
if user is not None:
token = random_uri_safe_string(64)
db.execute('UPDATE user SET password_reset_token = ? WHERE id = ?',
(
token,
user['id'],
))
db.commit()
from naju.emails import send_password_reset_email
send_password_reset_email(user['email'], user, token)
return redirect(url_for('naju.index'))
return render_template('naju/reset_request.html')
def home():
if request.method == 'POST':
import naju.excel as e
file = e.create_table()
attachment_name = "Baumbestand.xlsx"
return send_file(file,
as_attachment=True,
attachment_filename=attachment_name)
db = get_db()
trees = db.execute(
"SELECT * FROM tree t, area a WHERE t.area_id = a.id ORDER BY name, number"
).fetchall()
params = db.execute(
"SELECT * FROM tree_param_type ORDER BY order_id, name").fetchall()
areas = db.execute("SELECT * FROM area a ORDER BY name").fetchall()
datas = []
from markupsafe import escape
for tree in trees:
data = '<tr><td class="searchable Fläche"><a href="' + url_for(
'naju.area', id=tree['area_id']) + '">' + str(escape(
tree['name'])) + '</a></td>'
data += '<td class="searchable Nummer">' + str(escape(
tree['number'])) + '</td>'
for par in params:
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if value is None:
data += '<td class="searchable ' + str(escape(
par['name'])) + '">None</td>'
elif par['type'] == 'Link':
data += '<td class="searchable ' + str(
escape(par['name'])) + '"><a href="' + str(
escape(value['value'])) + '">' + str(
escape(value['value'])) + '</a></td>'
else:
data += '<td class="searchable ' + str(escape(
par['name'])) + '">' + str(escape(
value['value'])) + '</td>'
data += '<td class="editable-cell"><a href="' + url_for(
'naju.edit_tree',
id=tree['id']) + '"><i class="far fa-edit"></i></a></td>'
data += '<td class="editable-cell"><a href="' + url_for(
'naju.delete_tree', id=tree['id']
) + """" onclick="return confirm('Wollen sie diesen Baum wirklich löschen? Er kann nicht wiederhergestellt werden!')"><i class="fas fa-trash"></i></a></td>"""
data += '</tr>'
datas.append(data)
return render_template('naju/main.html',
datas=datas,
areas=areas,
params=params,
trees=trees,
filters=[])
def area(id):
area = get_db().execute('SELECT * FROM area WHERE id = ?',
(id, )).fetchone()
return render_template('naju/area.html', area=area)
def accounts():
users = get_db().execute('SELECT * FROM user ORDER BY name ASC').fetchall()
return render_template('naju/accounts.html', users=users)
def edit_tree(id):
db = get_db()
tree = db.execute(
'SELECT * FROM tree t, area a WHERE t.area_id = a.id AND t.id = ?',
(id, )).fetchone()
params = db.execute(
'SELECT * FROM tree_param tp, tree_param_type tpt '
'WHERE tp.tree_id = ? AND tpt.id = tp.param_id ORDER BY tpt.name',
(id, )).fetchall()
if request.method == 'POST':
try:
number = request.form['number']
except:
flash('Post incorrect!')
print('Post incorrect!')
return render_template('naju/edit_tree.html',
tree=tree,
params=params)
if number != tree['number']:
try:
number = nbit_int(number)
except ValueError:
flash("Bitte gebe eine valide Zahl an!")
return render_template('naju/edit_tree.html',
tree=tree,
params=params)
check = db.execute(
"SELECT * FROM tree t, area a WHERE a.id = t.area_id AND t.number = ? AND a.id = ?",
(number, tree['area_id'])).fetchone()
if check is not None and check['id'] != id:
flash("Dieser Baum existiert bereits!")
return render_template('naju/edit_tree.html',
tree=tree,
params=params)
db.execute('UPDATE tree SET number=? WHERE id=?', (number, id))
for param in params:
try:
p = request.form[param['name']]
except:
p = None
if p is not None or param['type'] == "Wahr / Falsch":
if param['type'] == "Wahr / Falsch" or is_param_valid(
p, param['type']):
if param['type'] == "Wahr / Falsch":
if p:
db.execute(
'UPDATE tree_param SET value=? WHERE tree_id=? AND param_id=?',
("Ja", id, param['id']))
else:
db.execute(
'UPDATE tree_param SET value=? WHERE tree_id=? AND param_id=?',
("Nein", id, param['id']))
else:
db.execute(
'UPDATE tree_param SET value=? WHERE tree_id=? AND param_id=?',
(p, id, param['id']))
else:
flash('Incorrect input')
print('Post incorrect!')
return render_template('naju/edit_tree.html',
tree=tree,
params=params)
db.commit()
return redirect(url_for('naju.home'))
return render_template('naju/edit_tree.html', tree=tree, params=params)
def load_table():
print('Hi')
path = os.path.join(current_app.instance_path, 'assets', 'uploads',
'workbook.xlsx')
print(path)
wb = load_workbook(path)
db = get_db()
for sheet in wb.worksheets:
area = db.execute('SELECT * FROM area WHERE name = ?',
(sheet.title, )).fetchone()
if area is None:
db.execute(
'INSERT INTO area (name, address, link, short) VALUES (?, ?, ?, ?)',
(sheet.title, "", "https://juhu.selhost.co/naju", ""))
area = db.execute('SELECT * FROM area WHERE name = ?',
(sheet.title, )).fetchone()
param_names = sheet[2]
params = []
first = False
for p in param_names:
print(p.value)
if not first:
first = True
continue
if p.value is None:
break
p_type = db.execute('SELECT * FROM tree_param_type WHERE name = ?',
(p.value, )).fetchone()
if p_type is None:
db.execute(
'INSERT INTO tree_param_type (name, type, order_id) VALUES (?, ?, ?)',
(p.value, "Text", 0))
param = db.execute('SELECT * FROM tree_param_type WHERE name=? AND type=?', (p.value, "Text"))\
.fetchone()
trees = db.execute('SELECT * FROM tree').fetchall()
for tree in trees:
db.execute(
"INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)",
(tree['id'], param['id'], ""))
p_type = db.execute(
'SELECT * FROM tree_param_type WHERE name = ?',
(p.value, )).fetchone()
params.append(p_type)
print(params)
if area is not None:
for row in sheet.rows:
digit = str(row[0].value).replace('.', '')
if str(digit).isnumeric():
if db.execute(
'SELECT * FROM tree t, area a '
'WHERE t.area_id = a.id AND t.area_id = ? AND t.number = ?',
(
area['id'],
int(digit),
)).fetchone() is None:
db.execute(
"INSERT INTO tree (number, area_id) VALUES (?, ?)",
(
int(digit),
area['id'],
))
print(digit)
tree = db.execute(
'SELECT * FROM tree WHERE number = ? AND area_id = ?',
(
int(digit),
area['id'],
)).fetchone()
row_num = 1
for param in params:
print(param['id'])
print(tree['id'])
if row[row_num].value is not None and param is not None:
if db.execute(
'SELECT * FROM tree_param '
'WHERE tree_id = ? AND param_id = ?',
(tree['id'], param['id'])).fetchone() is None:
db.execute(
'INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)',
(tree['id'], param['id'],
row[row_num].value))
elif row[row_num].value is None and param is not None:
if db.execute(
'SELECT * FROM tree_param '
'WHERE tree_id = ? AND param_id = ?',
(tree['id'], param['id'])).fetchone() is None:
db.execute(
'INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)',
(tree['id'], param['id'], ""))
row_num += 1
tree_param_types = db.execute(
'SELECT * FROM tree_param_type').fetchall()
for tree_param_type in tree_param_types:
if db.execute(
'SELECT * FROM tree_param '
'WHERE tree_id = ? AND param_id = ?',
(tree['id'],
tree_param_type['id'])).fetchone() is None:
db.execute(
'INSERT INTO tree_param (tree_id, param_id, value) VALUES (?, ?, ?)',
(tree['id'], tree_param_type['id'], ""))
wb.close()
db.commit()
def filtered_home(search=''):
if search == '':
return redirect(url_for('naju.home'))
filters = search.split('|', -1)
filter_datas = []
for search_filter in filters:
expressions = search_filter.split(':', 1)
if len(expressions) < 2:
continue
filter_type = expressions[0]
filter_search = expressions[1]
filter_datas.append([filter_type, filter_search])
if request.method == 'POST':
import naju.excel as e
file = e.create_table(filters=filters)
attachment_name = "Baumbestand_" + search + ".xlsx"
return send_file(file,
as_attachment=True,
attachment_filename=attachment_name)
db = get_db()
trees = db.execute(
"SELECT * FROM tree t, area a WHERE t.area_id = a.id ORDER BY name, number"
).fetchall()
params = db.execute(
"SELECT * FROM tree_param_type ORDER BY order_id, name").fetchall()
areas = db.execute("SELECT * FROM area a ORDER BY name").fetchall()
datas = []
filtered_trees = []
from markupsafe import escape
for tree in trees:
is_tree_valid = True
for search_filter in filters:
expressions = search_filter.rsplit(':')
if len(expressions) < 2:
continue
filter_type = expressions[0]
filter_search = expressions[1]
if filter_type.lower() == 'alle':
is_filter_in = False
if str(tree['number']).lower() == filter_search.lower():
is_filter_in = True
if str(tree['name']).lower().find(filter_search.lower()) > -1:
is_filter_in = True
for par in params:
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if str(value['value']).lower().find(
filter_search.lower()) > -1:
is_filter_in = True
break
if not is_filter_in:
is_tree_valid = False
break
if filter_type.lower() == 'nummer':
if str(tree['number']).lower() != filter_search.lower():
is_tree_valid = False
break
elif filter_type.lower() == 'Fläche'.lower():
if str(tree['name']).lower().find(filter_search.lower()) == -1:
is_tree_valid = False
break
else:
for par in params:
if str(par['name']).lower() == filter_type.lower():
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if value is None:
is_tree_valid = False
break
elif str(value['value']).lower().find(
filter_search.lower()) == -1 or str(
value['value']) == '':
is_tree_valid = False
break
if not is_tree_valid:
continue
filtered_trees.append(tree)
data = '<tr><td class="searchable Fläche"><a href="' + url_for(
'naju.area', id=tree['area_id']) + '">' + str(escape(
tree['name'])) + '</a></td>'
data += '<td class="searchable Nummer">' + str(escape(
tree['number'])) + '</td>'
for par in params:
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if value is None:
data += '<td class="searchable ' + str(escape(
par['name'])) + '">None</td>'
elif par['type'] == 'Link':
data += '<td class="searchable ' + str(
escape(par['name'])) + '"><a href="' + str(
escape(value['value'])) + '">' + str(
escape(value['value'])) + '</a></td>'
else:
data += '<td class="searchable ' + str(escape(
par['name'])) + '">' + str(escape(
value['value'])) + '</td>'
data += '<td class="editable-cell"><a href="' + url_for(
'naju.edit_tree',
id=tree['id']) + '"><i class="far fa-edit"></i></a></td>'
data += '<td class="editable-cell"><a href="' + url_for(
'naju.delete_tree', id=tree['id']
) + """" onclick="return confirm('Wollen sie diesen Baum wirklich löschen? Er kann nicht wiederhergestellt werden!')"><i class="fas fa-trash"></i></a></td>"""
data += '</tr>'
datas.append(data)
return render_template('naju/main.html',
datas=datas,
areas=areas,
params=params,
trees=filtered_trees,
filters=filter_datas)
def create_table(filters=None):
if filters is None:
filters = []
path = os.path.join(current_app.instance_path, 'tables')
file = os.path.join(path, "type-filter.xlsx")
os.makedirs(path, exist_ok=True)
workbook = wr.Workbook(file)
workbook.set_properties({
'title': 'Baumbestand NAJU Essen/Mülheim)',
'author': 'Fabius Mettner',
'company': 'NAJU - Essen/Mülheim',
'category': 'Baumbestand',
'keywords': 'NAJU, Baumbestand',
'created': datetime.now(),
'comments': 'Created with Python and XlsxWriter'
})
merge_format = workbook.add_format({
'bold': True,
'border': 5,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
'font_size': 24,
'bg_color': 'gray'
})
header_format = workbook.add_format({
'bold': True,
'border': 2,
'align': 'center',
'font_size': 16,
'bg_color': 'gray'
})
format_1 = workbook.add_format({
'bold': False,
'border': 1,
'align': 'left',
'font_size': 12,
'bg_color': 'white'
})
format_2 = workbook.add_format({
'bold': False,
'border': 1,
'align': 'left',
'font_size': 12,
'bg_color': '#9b9b9b'
})
db = get_db()
areas = db.execute('SELECT * FROM area').fetchall()
for area in areas:
try:
is_tree_valid = True
for search_filter in filters:
expressions = search_filter.rsplit(':')
if len(expressions) < 2:
continue
filter_type = expressions[0]
filter_search = expressions[1]
if filter_type.lower() == 'Fläche'.lower() and str(
area['name']).lower().find(
filter_search.lower()) == -1:
is_tree_valid = False
break
if not is_tree_valid:
continue
except ValueError:
continue
worksheet = workbook.add_worksheet(str(area['name']))
row = 0
col = 0
row += 2
worksheet.write(row, col, 'Nummer', header_format)
col += 1
params = db.execute('SELECT * FROM tree_param_type ORDER BY name')
for param in params:
worksheet.write(row, col, param['name'], header_format)
col += 1
row += 1
worksheet.merge_range(0, 0, 0, col - 1, area['name'], merge_format)
trees = db.execute(
'SELECT * FROM tree WHERE area_id=? ORDER BY number',
(area['id'], )).fetchall()
for tree in trees:
jump = False
params = db.execute(
'SELECT * FROM tree_param tp, tree_param_type tpt '
'WHERE tp.param_id = tpt.id AND tree_id=? ORDER BY name',
(tree['id'], )).fetchall()
is_tree_valid = True
for search_filter in filters:
expressions = search_filter.rsplit(':')
if len(expressions) < 2:
continue
filter_type = expressions[0]
filter_search = expressions[1]
if filter_type.lower() == 'alle':
is_filter_in = False
if str(tree['number']).lower() == filter_search.lower():
is_filter_in = True
if str(area['name']).lower().find(
filter_search.lower()) > -1:
is_filter_in = True
for par in params:
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if str(value['value']).lower().find(
filter_search.lower()) > -1:
is_filter_in = True
break
if not is_filter_in:
is_tree_valid = False
break
if filter_type.lower() == 'nummer':
if str(tree['number']).lower() != filter_search.lower():
is_tree_valid = False
break
elif filter_type.lower() == 'Fläche'.lower():
if str(area['name']).lower().find(
filter_search.lower()) == -1:
is_tree_valid = False
break
else:
for par in params:
if str(par['name']).lower() == filter_type.lower():
value = db.execute(
"SELECT * FROM tree_param WHERE tree_id = ? AND param_id = ? ",
(tree['id'], par['id'])).fetchone()
if value is None:
is_tree_valid = False
break
elif str(value['value']).lower().find(
filter_search.lower()) == -1 or str(
value['value']) == '':
is_tree_valid = False
break
if not is_tree_valid:
continue
col = 0
if row % 2 == 1:
worksheet.write(row, col, tree['number'], format_1)
else:
worksheet.write(row, col, tree['number'], format_2)
col += 1
for param in params:
if row % 2 == 1:
worksheet.write(row, col, param['value'], format_1)
else:
worksheet.write(row, col, param['value'], format_2)
col += 1
row += 1
for i in range(0, col):
set_column_autowidth(worksheet, i)
workbook.close()
return file
