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