def edit_category(id):
db = get_db()
category = db.execute('SELECT * FROM word_category WHERE id = ?', (id, )).fetchone()
if request.method == 'POST':
try:
name = request.form['name']
description = request.form['description']
except:
flash('Deine mitgegebenen Daten konnten nicht gefunden werden. Bitte versuche es noch einmal.')
return render_template('language/create.html', title='Wortart hinzufügen')
if not name or not description:
flash('Sie haben nicht alle benötigten Werte angegeben!')
return render_template('language/create.html', title='Wortart hinzufügen')
db = get_db()
category = db.execute('SELECT * FROM word_category WHERE name = ? AND id != ?', (name, id)).fetchone()
if category is not None:
flash('Diese Wortart existiert bereits.')
return render_template('language/create.html', title='Wortart hinzufügen')
db.execute('UPDATE word_category SET name = ?, description = ? WHERE id = ?', (name, description, id))
db.commit()
return redirect(url_for('language.categories'))
return render_template('language/edit_category.html', title='Wortart bearbeiten', category=category)
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 benötigt"
if type(int(number)) != int:
error = "Es wird eine Zahl benötigt"
db = get_db()
check = db.execute(
"SELECT * FROM tree t, area a WHERE a.id = t.area_id AND t.number = ? AND a.name = ?",
(int(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 (?, ?)",
(int(number), area_id['id']))
params = db.execute('SELECT * FROM tree_param_type').fetchall()
tree = db.execute(
'SELECT * FROM tree WHERE number = ? AND area_id = ?',
(int(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 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('auth.login'))
else:
return redirect(url_for('home.index'))
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) VALUES (?, ?)',
(name, type))
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 comment(discussion_id, id):
db = get_db()
comment = db.execute('SELECT * FROM comment WHERE id = ?', (id,)).fetchone()
discussion = db.execute('SELECT d.title, u.name, d.created, d.id, d.author FROM discussion d, user u '
'WHERE d.author = u.id AND d.id = ?', (discussion_id,)).fetchone()
if discussion is None or comment is None:
abort(404)
return
if g.user['id'] != comment['author'] and g.user['id'] != discussion['author'] and g.user['level'] < getAdminKey():
return redirect(url_for('discussions.discussion', id=discussion_id))
if request.method == 'POST':
try:
title = request.form['value']
except:
flash('Post incorrect!')
return redirect(url_for('home.index'))
if title:
import time
db.execute('UPDATE comment SET value = ?, author = ? '
'WHERE id = ?', (title, g.user['id'], id))
db.commit()
return redirect(url_for('discussions.discussion', id=discussion_id))
return render_template('discussion/edit_comment.html', comment=comment, discussion=discussion)
def delete_tag(position):
db = get_db()
tag = db.execute('SELECT * FROM information_order WHERE position = ?', (position, )).fetchone()
db.execute('DELETE FROM character_information WHERE title = ?', (tag['information_title'], ))
db.execute('DELETE FROM information_order WHERE position = ?', (position, ))
db.commit()
return redirect(url_for('characters.tags'))
def discussion(id):
db = get_db()
comments = db.execute('SELECT c.created, c.value, u.name, c.author, c.id, c.discussion_id FROM comment c, user u'
' WHERE c.discussion_id = ? AND c.author = u.id ORDER BY c.created DESC', (id,)).fetchall()
discussion = db.execute('SELECT d.title, u.name, d.created, d.id, d.author FROM discussion d, user u '
'WHERE d.author = u.id AND d.id = ?', (id,)).fetchone()
if not discussion:
abort(404)
return
if request.method == 'POST':
try:
title = request.form['theme']
description = request.form['value']
except:
flash('Post incorrect!')
return redirect(url_for('home.index'))
if title:
import time
db.execute('INSERT INTO discussion (title, author, created, description) '
'VALUES (?, ?, ?, ?)', (title, g.user['id'], time.strftime('%Y-%m-%d %H:%M:%S'), description))
db.commit()
return redirect(url_for('discussions.index'))
return render_template('discussion/comment_overview.html', comments=comments, discussion=discussion)
def login():
if request.method == 'POST':
try:
username = request.form['username']
password = request.form['password']
except:
flash('Post incorrect!')
print('Post incorrect!')
return redirect(url_for('home.index'))
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE name = ? OR email = ?', (username, username,)).fetchone()
if user is None:
error = 'Der Benutzername existiert nicht.'
elif not check_password_hash(user['pwd_hash'], password):
error = 'Das Passwort war 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('home.index'))
flash(error)
return render_template('home/login.html')
def home():
if request.method == 'POST':
try:
type = request.form['select_tag']
filter = request.form['tag']
except:
type = ""
filter = ""
import dfs.excel as e
file = e.create_table(type, filter)
attachment_name = "Baumbestand_" + filter + ".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 a.name, t.number"
).fetchall()
values = db.execute(
"SELECT * FROM tree_param ORDER BY param_id").fetchall()
params = db.execute(
"SELECT * FROM tree_param_type ORDER BY name").fetchall()
return render_template('naju/main.html',
trees=trees,
values=values,
params=params)
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 create_language():
if request.method == 'POST':
try:
name = request.form['name']
description = request.form['description']
except:
flash('Deine mitgegebenen Daten konnten nicht gefunden werden. Bitte versuche es noch einmal.')
return render_template('language/create.html', title='Sprache hinzufügen')
if not name or not description:
flash('Sie haben nicht alle benötigten Werte angegeben!')
return render_template('language/create.html', title='Sprache hinzufügen')
db = get_db()
language = db.execute('SELECT * FROM language WHERE name = ?', (name, )).fetchone()
if language is not None:
flash('Diese Sprache existiert bereits.')
return render_template('language/create.html', title='Sprache hinzufügen')
db.execute('INSERT INTO language (name, description) VALUES (?, ?)', (name, description))
db.commit()
return redirect(url_for('language.index'))
return render_template('language/create.html', title='Sprache hinzufügen')
def create_tag(id):
if request.method == 'POST':
try:
name = request.form['name']
except:
flash('Deine mitgegebenen Daten konnten nicht gefunden werden. Bitte versuche es noch einmal.')
return render_template('characters/add_tag.html')
if not name:
flash('Sie haben keinen Werte angegeben!')
return render_template('characters/add_tag.html')
db = get_db()
character = db.execute('SELECT * FROM information_order'
' WHERE information_title = ? ', (name, )).fetchone()
if character is not None:
flash('Dieser Tag existiert bereits!')
return render_template('characters/create.html')
db.execute('INSERT INTO information_order (information_title, position)'
' VALUES (?, (SELECT MAX(position) FROM information_order) + 1)', (name, ))
db.commit()
return redirect(url_for('characters.character', id=id))
return render_template('characters/add_tag.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 delete_character(id):
db = get_db()
character_exists = db.execute('SELECT * FROM user_permissions WHERE user_id = ? AND character_id = ?',
(g.user['id'], id)).fetchone()
if g.user['level'] < 2 and character_exists is None:
flash('Du benötigst höhere Berechtigungen!')
return redirect(url_for('characters.index'))
db = get_db()
db.execute('DELETE FROM character_information WHERE character_id = ?', (id, ))
db.execute('DELETE FROM user_permissions WHERE character_id = ?', (id, ))
db.execute('DELETE FROM characters WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('characters.index'))
def character(id):
db = get_db()
character = db.execute('SELECT * FROM characters c, user_permissions up'
' WHERE c.id = up.character_id AND c.id = ?', (id, )).fetchone()
all_character_information = db.execute('SELECT * FROM character_information ci, information_order io'
' WHERE ci.character_id = ? AND io.information_title = ci.title'
' ORDER BY io.position ASC', (id, )).fetchall()
return render_template('characters/character.html', all_character_information=all_character_information,
character=character)
def profiles():
db = get_db()
if g.user is not None and g.user['level'] >= 2:
users = db.execute('SELECT * FROM user').fetchall()
else:
users = db.execute(
'SELECT * FROM user WHERE visible = 1 OR visible = 2').fetchall()
return render_template('home/accounts.html', users=users)
def index():
db = get_db()
stories = db.execute(
'SELECT * FROM user u, short_stories s WHERE u.id = s.author ORDER BY time'
).fetchall()
print(stories)
return render_template('short_stories/stories.html', stories=stories)
def edit_language(id):
if request.method == 'POST':
try:
name = request.form['name']
description = request.form['description']
writing = request.form['select_writing']
except:
flash('Deine mitgegebenen Daten konnten nicht gefunden werden. Bitte versuche es noch einmal.')
return render_template('language/create_language.html')
if not name or not description:
flash('Sie haben nicht alle benötigten Werte angegeben!')
return render_template('language/create_language.html')
db = get_db()
language = db.execute('SELECT * FROM language WHERE id = ?', (id, )).fetchone()
if language is None:
flash('Diese Sprache existiert noch nicht. Du musst sie zunächst erstellen')
return render_template('language/create_language.html')
if language['name'] != name:
name_check = db.execute('SELECT * FROM language WHERE name = ?', (name, )).fetchone()
if name_check is not None:
flash('Diese Sprache existiert bereits. Wähle einen anderen Namen!')
return render_template('language/create_language.html')
db.execute('UPDATE language SET name = ? WHERE id = ?', (name, id))
if language['description'] != description:
db.execute('UPDATE language SET description = ? WHERE id = ?', (description, id))
db.commit()
return redirect(url_for('language.language', id=id))
db = get_db()
language = db.execute('SELECT * FROM language WHERE id = ?', (id, )).fetchone()
writing = db.execute('SELECT * FROM writing').fetchall()
return render_template('language/edit_language.html', language=language, writing=writing)
def index():
db = get_db()
flash('Diese Seite wird in Zukunft hinzugefügt!')
if 1 == 1:
return redirect(url_for('home.index'))
languages = db.execute('SELECT * FROM language ORDER BY name ASC').fetchall()
return render_template('language/languages.html', languages=languages)
def index():
db = get_db()
discussions = db.execute('SELECT * FROM discussion d, user u '
'WHERE d.author = u.id ORDER BY d.created DESC').fetchall()
comments = db.execute('SELECT d.id AS id, COUNT(c.id) AS comments '
'FROM comment c, discussion d '
'WHERE d.id = c.discussion_id '
'GROUP BY d.id').fetchall()
return render_template('discussion/discussion.html', discussions=discussions, comments=comments)
def set_status(id, status):
db = get_db()
user = db.execute('SELECT * FROM user WHERE id = ?', (id,)).fetchone()
if not user:
abort(400)
return
db.execute('UPDATE user SET level=? WHERE id=?', (status, id))
db.commit()
return redirect(url_for('home.visit_profile', id=id))
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:
password = request.form['password']
check = request.form['passwordcheck']
except:
flash('Post incorrect!')
return redirect(url_for('home.index'))
if password == check:
db = get_db()
db.execute('UPDATE user SET pwd_hash = ?, password_reset_token = ?',
(generate_password_hash(password), None,))
db.commit()
return redirect(url_for('auth.login'))
return render_template('home/reset_password.html')
else:
return redirect(url_for('home.index'))
def visit_profile(id):
user = get_db().execute('SELECT * FROM user WHERE id = ?',
(id, )).fetchone()
if user['visible'] == 1 or user['visible'] == 2 or (g.user is not None and
g.user['level'] >= 2):
discussions = get_db().execute(
'SELECT * FROM discussion d, user u WHERE d.author = ? AND d.author = u.id',
(id, )).fetchall()
short_stories = get_db().execute(
'SELECT * FROM short_stories s, user u WHERE s.author = ? AND s.author = u.id',
(id, )).fetchall()
time_events = get_db().execute(
'SELECT * FROM time_event t, user u WHERE t.author = ? AND t.author = u.id',
(id, )).fetchall()
characters = get_db().execute(
'SELECT c.name, c.family, c.id FROM user_permissions up, user u, characters c'
' WHERE up.user_id = ? AND up.user_id = u.id AND up.character_id = c.id',
(id, )).fetchall()
all_character_information = get_db().execute(
'SELECT * FROM character_information ci, information_order io'
' WHERE io.information_title = ci.title AND io.position <= 5'
' ORDER BY io.position ASC').fetchall()
print(characters)
return render_template(
'home/account.html',
user=user,
discussions=discussions,
short_stories=short_stories,
time_events=time_events,
characters=characters,
all_character_information=all_character_information)
else:
return redirect(url_for('home.profiles'))
def tag_down(position):
db = get_db()
tags = db.execute('SELECT * FROM information_order ORDER BY position DESC').fetchall()
for tag in tags:
if tag['position'] == position + 1:
db.execute('DELETE FROM information_order WHERE position = ?', (tag['position'],))
db.execute('UPDATE information_order SET position = ? WHERE position = ?', (position + 1, position))
db.execute('INSERT INTO information_order (position, information_title) VALUES (?, ?)',
(position, tag['information_title']))
db.commit()
return redirect(url_for('characters.tags'))
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']
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=? WHERE id=?',
(name, type, id))
db.commit()
return redirect(url_for('naju.home'))
flash(error)
return render_template('naju/edit_param.html', param=param)
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 delete_discussion(id):
db = get_db()
discussion = db.execute('SELECT d.title, u.name, d.created, d.id, d.author FROM discussion d, user u '
'WHERE d.author = u.id AND d.id = ?', (id,)).fetchone()
if g.user['id'] != discussion['author'] and g.user['level'] < getAdminKey():
return redirect(url_for('discussions.index'))
db.execute('DELETE FROM discussion WHERE id = ?', (id, ))
db.execute('DELETE FROM comment WHERE discussion_id = ?', (id,))
db.commit()
return redirect(url_for('discussions.index'))
def delete_language(id):
db = get_db()
db.execute('DELETE FROM language WHERE id = ?', (id, ))
db.execute('DELETE FROM word WHERE language_id = ?', (id, ))
words = db.execute('SELECT * FROM word WHERE language_id = ?', (id, )).fetchall()
for word in words:
db.execute('DELETE FROM word_declinations WHERE parent_word_id = ?', (word['id'], ))
db.execute('DELETE FROM word WHERE language_id = ?', (id, ))
db.commit()
return redirect(url_for('language.index'))
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_link(link):
error = "Es wird ein Link benötigt"
link = get_link(link)
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 upload():
if request.method == 'POST':
if 'file' in request.files:
file = request.files['file']
try:
title = request.form['title']
description = request.form['story_description']
except:
flash('Du hast keinen Titel angegeben.')
return redirect(url_for('document.index'))
if file.filename == '':
flash('No File')
return redirect(url_for('document.index'))
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
path = os.path.join(current_app.instance_path,
'assets/stories')
os.makedirs(path, exist_ok=True)
db = get_db()
import time
t = time.strftime('%Y-%m-%d %H:%M:%S')
db.execute(
'INSERT INTO short_stories (title, story, author, time, show) VALUES (?, ?, ?, ?, ?)',
(title, description, g.user['id'], t, 0))
story_id = db.execute(
'SELECT id FROM short_stories '
'WHERE title = ? AND story = ? AND author = ? AND time = ?',
(title, description, g.user['id'], t)).fetchone()
file.save(
os.path.join(
path,
str(story_id['id']) + '.' +
filename.rsplit('.')[1].lower()))
flash('Upload Complete')
else:
flash('Wrong encoding')
return render_template('short_stories/add_story.html')
return redirect(url_for('document.index'))
return render_template('short_stories/add_story.html')
