def interactive(): lista = [] dict = {} d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] email = session['email'] #data = cursor.execute("SELECT date,ref_cat, municipality_name, original_src FROM d.accuracy WHERE user_gid = %s",[test]) data = cursor.execute( "SELECT b.date,b.ref_cat, b.municiplity_name, b.original_src, b.deleted FROM d.similarity AS b INNER JOIN d.user AS a ON a.gid = b.user_gid WHERE a.email= %s", [email]) #data = cursor.execute("SELECT date,ref_cat, municiplity_name, original_src FROM d.similarity WHERE user_gid = 2") data = cursor.fetchall() for row in data: if row[4] is False: date = row[0] ref_cat = row[1] muni = row[2] crs = row[3] meas = { "Fecha": date, "ReferenciaCadastral": ref_cat, "Municipalidad": muni, "SRC": crs } lista.append(meas) dict["data"] = lista d_conn = pg_operations2.pg_disconnect2(d_conn) return jsonify(dict)
def stats(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor = d_conn['cursor'] cons = 'select COUNT(*) from d.point' cursor.execute(cons) lista = cursor.fetchall() cons = "select timestamp without time zone 'epoch' + max(extract(epoch from trash_time)) * interval '1 second' from d.point" cursor.execute(cons) lista2 = cursor.fetchall() cons = "select timestamp without time zone 'epoch' + min(extract(epoch from trash_time)) * interval '1 second' from d.point" cursor.execute(cons) lista3 = cursor.fetchall() cons = "select timestamp without time zone 'epoch' + (max(extract(epoch from trash_time)) - min(extract(epoch from trash_time))) * interval '1 second' from d.point" cursor.execute(cons) lista4 = cursor.fetchall() time = None for i in lista4: time = str(i[0]) lista4 = time[11:19] cons = 'select trash_type, count(trash_type) from d.point group by trash_type order by trash_type;' cursor.execute(cons) lista5 = cursor.fetchall() d_conn = pg_operations2.pg_disconnect2(d_conn) return render_template("statsTrashReporter.html", lista=lista, lista2=lista2, lista3=lista3, lista4=lista4, lista5=lista5)
def register(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] form = RegistrationForm(request.form) try: if request.method == 'POST' and form.validate(): if 1 == 1: email = form.email.data passwordUser = sha256_crypt.encrypt(form.password.data) cursor = d_conn['cursor'] requestAccess = form.requestAccess.data params = { '_email': email, '_password': passwordUser, '_requestAccess': requestAccess } query = """insert into d.user (email, encrypted_password, requestAccess) values (%(_email)s,%(_password)s, %(_requestAccess)s)""" cursor.execute(query, params) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) #cursor.fetchall() flash('Thanks for registering!', 'success') return redirect(url_for('index')) return render_template('register.html', form=form) except: conn = d_conn['conn'] conn.rollback() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('ERROR! Email ({}) already exists.'.format(form.email.data), 'error') return render_template('register.html', form=form)
def register(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] form = RegistrationForm(request.form) try: if request.method == 'POST' and form.validate(): if 1 == 1: email = form.email.data passwordUser = sha256_crypt.encrypt(form.password.data) cursor = d_conn['cursor'] requestAccess = form.requestAccess.data if requestAccess == 'No': fk_request_access = 1 else: fk_request_access = 0 params = paramsRegister(email, passwordUser, fk_request_access) query = queryRegister() cursor.execute(query, params) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) newUserEmail(email) flash( 'Thanks for registering! Please approve your registration via email', 'success') return redirect(url_for('index')) return render_template('registerLBS.html', form=form) except: conn = d_conn['conn'] conn.rollback() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('ERROR! Email ({}) already exists.'.format(form.email.data), 'danger') return render_template('registerLBS.html', form=form)
def confirmUser_with_token(token): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] try: form = PasswordForm(request.form) password_reset_serializer = URLSafeTimedSerializer( app.config['SECRET_KEY']) email = password_reset_serializer.loads(token, salt='password-reset-salt', max_age=3600) print 1 #if form.validate(): if 1 == 1: #password2 = sha256_crypt.encrypt(form.password.data) cursor = d_conn['cursor'] cursor.execute( "UPDATE d.user set confirmed = True WHERE email = %s;", [email]) print 1 conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('Your password has been updated!', 'success') return redirect(url_for('login')) return render_template('newUser_withToken.html', form=form, token=token) except: flash('The password reset link is invalid or has expired.', 'danger') return redirect(url_for('login'))
def forgot_password(): token = request.args.get('token', None) form = EmailForm(request.form) #form if form.validate(): email = form.email.data d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute( "SELECT encrypted_password FROM d.user WHERE email = %s", [email]) if cursor.fetchone() is None: print 0 flash('Not such email.', 'danger') return render_template('emailForgotPass.html', form=form) else: d_conn = pg_operations2.pg_disconnect2(d_conn) send_password_reset_email(email) flash('Please check your email for a password reset link.', 'success') return redirect(url_for('login')) return render_template('emailForgotPass.html', form=form)
def stats(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) #import sys;sys.path.append(r'/opt/liclipse/plugins/org.python.pydev_4.5.5.201603221237/pysrc') #import pydevd;pydevd.settrace() cursor = d_conn['cursor'] cons = 'select visit_num from d.visit_num' cursor.execute(cons) lista = cursor.fetchall() cons2 = 'select count(gid) from d.user' cursor.execute(cons2) lista2 = cursor.fetchall() cons3 = 'select count(gid) from d.similarity' cursor.execute(cons3) lista3 = cursor.fetchall() cons4 = 'select count(gid) from d.accuracy' cursor.execute(cons4) lista4 = cursor.fetchall() d_conn = pg_operations2.pg_disconnect2(d_conn) #cursor.close() return render_template("stats.html", lista=lista, lista2=lista2, lista3=lista3, lista4=lista4)
def login(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor=d_conn['cursor'] if request.method == 'POST': email = request.form['email'] password_candidate = request.form['password'] cursor.execute("SELECT encrypted_password FROM d.user WHERE email = %s",[email]) if cursor.fetchone() is None: flash('Not such email/password', 'danger') d_conn = pg_operations2.pg_disconnect2(d_conn) return render_template('login.html') else: cursor.execute("SELECT encrypted_password FROM d.user WHERE email = %s",[email]) data = cursor.fetchone()[0] if sha256_crypt.verify(password_candidate, data): data = cursor.execute("SELECT remove_access FROM d.user WHERE email = %s",[email]) data = cursor.fetchone()[0] if data: session['remove_access'] = True session['logged_in'] = True #session['email'] = request.form['email'] flash('You are now logged in as ' + email, 'success') d_conn = pg_operations2.pg_disconnect2(d_conn) return redirect(url_for('map')) else: flash('Not such email/password', 'danger') d_conn = pg_operations2.pg_disconnect2(d_conn) return render_template('login.html') return render_template('login.html')
def login(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] if request.method == 'POST': # Get Form Fields email = request.form['email'] password_candidate = request.form['password'] data = cursor.execute( "SELECT encrypted_password FROM d.user WHERE email = %s", [email]) data = cursor.fetchone()[0] if sha256_crypt.verify(password_candidate, data): session['logged_in'] = True session['email'] = request.form['email'] #getEmail = cursor.execute("SELECT email FROM d.user WHERE email = %s",[email]) #getEmail = cursor.fetchone()[0] #emailSplit = session['email'].split("@")[0] flash('You are now logged in', 'success') d_conn = pg_operations2.pg_disconnect2(d_conn) return redirect(url_for('profile', email=session['email'])) else: error = 'Invalid login' return render_template('login.html', error=error) # Close connection cursor.close() conn.close() d_conn = pg_operations2.pg_disconnect2(d_conn) d_conn = pg_operations2.pg_disconnect2(d_conn) return render_template('login.html')
def Delete(nom_tabla,gid): d_conn=pg_operations2.pg_connect2(database, user, password, host, port) print gid pg_operations2.pg_delete2(d_conn, table_name=nom_tabla, cond_where='where gid=%s', list_values_cond_where=[gid]) pg_operations2.pg_disconnect2(d_conn) resp_json=json.dumps({"ok":True, 'message':'Delete successful', 'deleted gid':gid}) #print 'Selection: ' + resp_json return resp_json
def deleteRowSim(gid): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute("DELETE FROM d.supermarket WHERE gid = %s", [gid]) conn.commit() data = interactive2() d_conn = pg_operations2.pg_disconnect2(d_conn) return data
def fkTrashType(trashType): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor=d_conn['cursor'] cursor.execute("select id from d.trash_type where trash = %s",[trashType]) fk_trash_type = cursor.fetchall() for row in fk_trash_type: fk_trash_type = row[0] d_conn = pg_operations2.pg_disconnect2(d_conn) return fk_trash_type
def dbMaleChoices(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor = d_conn['cursor'] cursor.execute("select gender from gender") data = cursor.fetchall() choices = [] for row in data: choices.append((row[0], row[0])) d_conn = pg_operations2.pg_disconnect2(d_conn) return choices
def dbCountryChoicesEmpty(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor = d_conn['cursor'] cursor.execute( "select distinct(country_name) from countries order by country_name") data = cursor.fetchall() choices = [] for row in data: choices.append((row[0], row[0])) d_conn = pg_operations2.pg_disconnect2(d_conn) return choices
def dbTrashChoices(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute("select trash from d.trash_type") data = cursor.fetchall() choices = [] for row in data: choices.append((row[0], row[0])) d_conn = pg_operations2.pg_disconnect2(d_conn) return choices
def dbAccessChoices(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor=d_conn['cursor'] cursor.execute("select acess_type from d.remove_access") data = cursor.fetchall() choices = [] for row in data: choices.append((row[0], row[0])) choices = (choices[2], choices[1]) d_conn = pg_operations2.pg_disconnect2(d_conn) print choices return choices
def upload_file(): try: form = addPointForm(request.form) d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] if request.method == 'POST': if form.validate(): geolocator = Nominatim(user_agent="app") if form.lat.data == '' or form.lat.data is None or form.lon.data == '' or form.lon.data is None: location = geolocator.geocode(form.superAddress.data) address = form.superAddress.data lat = location.latitude lon = location.longitude else: lat = form.lat.data lon = form.lon.data address = nominatim(str(lat), str(lon)) checkPol(lat, lon, form) supermarket_name = form.supermarket_name.data phone = form.phone.data email = form.email.data web = form.web.data contact_person = form.contact_person.data custEvaluation = form.choice.data fk_customer_importance_evaluation = fkEvluation(custEvaluation) first_contact = form.first_contact.data last_contact = form.last_contact.data outdoor_person = form.outdoor_person.data params = paramsInsert(lat, lon, supermarket_name, address, phone, email, web, contact_person, fk_customer_importance_evaluation, first_contact, last_contact, outdoor_person) print params query = queryInsert() cursor.execute(query, params) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('Point uploaded', 'success') return redirect(url_for('map')) except: conn = d_conn['conn'] conn.rollback() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('ERROR! Coords already exist', 'danger') return render_template('addSupermarket.html', form=form) return render_template('addSupermarket.html', form=form)
def fkEvluation(custEvaluation): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute("select id from d.choices where choice = %s", [custEvaluation]) fk_customer_importance_evaluation = cursor.fetchall() for row in fk_customer_importance_evaluation: fk_customer_importance_evaluation = row[0] d_conn = pg_operations2.pg_disconnect2(d_conn) return fk_customer_importance_evaluation
def deleteRowSim(gid): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] #data = cursor.execute("SELECT b.date,b.ref_cat, b.municipality_name, b.original_src, b.deleted FROM d.accuracy AS b INNER JOIN d.user AS a ON a.gid = b.user_gid WHERE a.email= %s",[email]) #cursor.execute("UPDATE d.accuracy AS a set deleted = True FROM d.user as b where b.gid = a.user_gid and b.email = %s",[gid]) test = cursor.execute( "UPDATE d.similarity set deleted = True where gid = %s", [gid]) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) return 123
def resetPass_with_token(token): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] try: form = PasswordForm(request.form) password_reset_serializer = URLSafeTimedSerializer( app.config['SECRET_KEY']) print password_reset_serializer email = password_reset_serializer.loads(token, salt='password-reset-salt', max_age=3600) print email if form.validate(): if 1 == 1: print 1 password2 = sha256_crypt.encrypt(form.password.data) print password2 print d_conn print conn cursor = d_conn['cursor'] print cursor print 2 #cursor.execute("UPDATE d.user set encrypted_password = '******' WHERE email = '{1}';".format(password,email)) cursor.execute( "UPDATE d.user set encrypted_password = %s WHERE email = %s;", [password2, email]) #cons2 = "UPDATE d.user set encrypted_password=(%s) WHERE email = (%s)", (password,email,) #cons3 = "UPDATE d.user set encrypted_password=(%s) WHERE email = (%s)", (password,[email]) #cons2='update d.user set encrypted_password = '******'where email = ' + [email] #print cons2 #print cons3 #cursor.execute(cons3) #cursor.execute(cons3) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('Your password has been updated!', 'success') return redirect(url_for('login')) return render_template('emailForgotPassToken.html', form=form, token=token) except: flash('The password reset link is invalid or has expired.', 'error') return redirect(url_for('login'))
def confirmUser_with_token(token): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn=d_conn['conn'] try: reset_serializer = URLSafeTimedSerializer(app.config['SECRET_KEY']) email = reset_serializer.loads(token, salt='password-reset-salt', max_age=3600) cursor=d_conn['cursor'] cursor.execute("UPDATE d.user set confirmed = True WHERE email = %s;",[email]) conn.commit() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('Your are now confirmed!', 'success') return redirect(url_for('map')) except: flash('The link is invalid or has expired.', 'danger') return redirect(url_for('login'))
def checkPol(lat, lon, form): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor=d_conn['cursor'] cursor.execute("SELECT st_xmin(ST_Extent(geom)), st_xmax(ST_Extent(geom)),st_ymin(ST_Extent(geom)),st_ymax(ST_Extent(geom)) as bextent FROM bbox") data = cursor.fetchall() for row in data: minX = row[0] maxX = row[1] minY = row[2] maxY = row[3] print data if lon < minX or lon > maxX or lat < minY or lat > maxY: d_conn = pg_operations2.pg_disconnect2(d_conn) #flash('ERROR! Coordinates are out of Karlsruhe.', 'danger') d_conn = pg_operations2.pg_disconnect2(d_conn)
def interactive2(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute("SELECT st_y(geom), st_x(geom), address FROM d.supermarket") data = cursor.fetchall() x = [] y = [] address = [] for row in data: x.append(row[0]) y.append(row[1]) address.append(row[2]) data = geoJsonMaker(x, y, address) d_conn = pg_operations2.pg_disconnect2(d_conn) return jsonify(data)
def stats(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor = d_conn['cursor'] cons = """select COUNT(gid), timestamp without time zone 'epoch' + max(extract(epoch from created_at)) * interval '1 second', timestamp without time zone 'epoch' + min(extract(epoch from created_at)) * interval '1 second' from d.supermarket""" cursor.execute(cons) data = cursor.fetchall() for i in data: counter = i[0] maxTime = str(i[1]) minTime = str(i[2]) maxTime = str(maxTime[:19]) fmt = '%Y-%m-%d %H:%M:%S' maxTime = datetime.strptime(maxTime, fmt) minTime = str(minTime[:19]) minTime = datetime.strptime(minTime, fmt) timeDiff = str(maxTime - minTime) lista = [] lista2 = [] dict = {} cons = """ select a.choice, count(b.fk_customer_importance_evaluation) from d.choices AS a INNER JOIN d.supermarket AS b ON a.id = b.fk_customer_importance_evaluation group by a.choice, b.fk_customer_importance_evaluation order by b.fk_customer_importance_evaluation """ cursor.execute(cons) for i in cursor.fetchall(): superMarketType = i[0] superMarketCounter = i[1] lista2.append(superMarketType) lista2.append(superMarketCounter) meas = { "counter": counter, "maxTime": maxTime, "minTime": minTime, "timeDiff": timeDiff, "lista2": lista2 } lista.append(meas) dict["data"] = lista d_conn = pg_operations2.pg_disconnect2(d_conn) return jsonify(dict)
def Select(nom_tabla,gid): d_conn=pg_operations2.pg_connect2(database, user, password, host, port) if (nom_tabla=='tabla.placas'): fields = 'gid,motivo,fecha_ini,fecha_fin,zona,st_asgeojson(geom)' elif (nom_tabla=='tabla.incidencias'): fields = 'gid,descripcion,fecha,tipo,st_asgeojson(geom)' elif (nom_tabla=='tabla.zonas_metro'): fields = 'gid,zona,precio,st_asgeojson(geom)' else: fields = '*' res = pg_operations2.pg_select2(d_conn, table_name=nom_tabla, string_fields_to_select=fields, cond_where='where gid=%s', list_val_cond_where=[gid]) print res res = res[0] #print (js) pg_operations2.pg_disconnect2(d_conn) resp_json=json.dumps({"ok":True, 'data':res, 'message':'Select successful', 'gid':gid}) print 'Selection: ' + resp_json return resp_json
def Insert(nom_tabla,tipo_geom,json_data): d_form_data = json.loads(json_data) d_form_data['geom']=pg_operations2.transform_coords_ol_to_postgis(coords_geom=d_form_data['geom']) d_conn=pg_operations2.pg_connect2(database, user, password, host, port) d2=pg_operations2.dict_to_string_fields_and_vector_values2(d=d_form_data, list_fields_to_remove=['gid'],geom_field_name='geom', epsg='25830', geometry_type=tipo_geom, epsg_to_reproject=None) print "d2" print d2 list_returning=pg_operations2.pgInsert2(d_conn=d_conn, nom_tabla=nom_tabla, d_str=d2,str_fields_returning='gid') new_gid=list_returning[0][0] pg_operations2.pg_disconnect2(d_conn) resp_json=json.dumps({"ok":True, 'data':d_form_data, 'message':'Row inserted', 'new_gid':new_gid}) print 'insert_building: ' + resp_json return resp_json
def interactive2(): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) cursor = d_conn['cursor'] cursor.execute("""SELECT b.longitude, b.latitude, a.trash from d.trash_type as a inner join d.point as b on a.id = b.fk_trash_type""") data = cursor.fetchall() x = [] y = [] trash = [] for row in data: x.append(row[0]) y.append(row[1]) trash.append(row[2]) data = geoJsonMaker(x, y, trash) d_conn = pg_operations2.pg_disconnect2(d_conn) return jsonify(data)
def visitor_counter(d_conn): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) counter = 1 conn = d_conn['conn'] cursor = d_conn['cursor'] cons = 'select * from d.visit_num' cursor.execute(cons) lista = cursor.fetchall() for i in lista: #print i[1] updateCounter = i[1] + counter cons2 = 'update d.visit_num set visit_num = ' + str(updateCounter) cursor.execute(cons2) conn.commit() #cursor.Close() r = cursor.rowcount d_conn = pg_operations2.pg_disconnect2(d_conn) return r
def interactive(): lista = [] dict = {} d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] data = cursor.execute( "SELECT gid, st_astext(geom), address FROM d.supermarket") data = cursor.fetchall() print data for row in data: gid = row[0] geom = row[1] address = row[2] meas = {"gid": gid, "geom": geom, "address": address} lista.append(meas) dict["data"] = lista #print dict d_conn = pg_operations2.pg_disconnect2(d_conn) return jsonify(dict)
def checkPol(lat, lon, form): d_conn = pg_operations2.pg_connect2(database, user, password, host, port) conn = d_conn['conn'] cursor = d_conn['cursor'] cursor.execute( "select min(st_y(geom)),max(st_y(geom)),min(st_x(geom)),max(st_x(geom)) from gis_osm_places_free_1" ) data = cursor.fetchall() for row in data: minX = row[0] maxX = row[1] minY = row[2] maxY = row[3] if lat < minX or lat > maxX or lon < minY or lon > maxY: conn.rollback() d_conn = pg_operations2.pg_disconnect2(d_conn) flash('ERROR! Coords are out of Karlsruhe.', 'danger') return render_template('registerTrashReporter..html', form=form) d_conn = pg_operations2.pg_disconnect2(d_conn)