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')