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)