def users():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM tbl_user")
rows = cursor.fetchall()
table = Results(rows)
table.border = True
return render_template('users.html', table=table)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_user(id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id, ))
conn.commit()
resp = jsonify('User deleted successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def user(id):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM users WHERE DEVICE_ID=%s", id)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def get_bikes():
try:
try:
_email = request.form['email']
_token = request.form['token']
except Exception as e:
print(e)
return bad_request()
if _email and _token and request.method == 'POST':
var = authenticate_email_token(_email, _token)
if var:
# Close all reserved rides older than 5 minutes
sql = f"UPDATE rides SET status = 'canceled' WHERE reserveTimeStamp < (NOW() - INTERVAL 5 MINUTE) AND " \
f"status = 'reserved' "
cnx = mysql.connect()
cursor = cnx.cursor()
cursor.execute(sql)
cursor.close()
cnx.commit()
# Remove reservations from reserved bikes older than 5 minutes
sql = f"UPDATE bikes SET status = 'free', reserveTimeStamp = NULL, currentRideID = NULL WHERE " \
f"reserveTimeStamp < (NOW() - INTERVAL 5 MINUTE) AND status = 'reserved' "
cursor = cnx.cursor()
cursor.execute(sql)
cursor.close()
cnx.commit()
sql = f"SELECT bikes.bikeID, bikes.currentStationID, bikes.status, stations.stationName FROM bikes, " \
f"stations, users WHERE users.email = '{_email}' AND bikes.currentStationID = " \
f"stations.stationID AND stations.domain = users.domain "
cursor = cnx.cursor()
cursor.execute(sql)
bike_list = []
for row in cursor:
bike_list.append(row)
res = jsonify(bike_list)
res.status_code = 200
return res
# If POST is empty
else:
return forbidden()
except Exception as e:
print(e)
return internal_server_error()
def delete_user(id):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM users WHERE DEVICE_ID=%s", (id, ))
conn.commit()
resp = jsonify('Attention !!! DATA Deleted')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def users():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def eliminar(cod):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM articulos WHERE codigo=%s", (cod, ))
conn.commit()
resp = jsonify('Artículo eliminado')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def article(cod):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM articulos WHERE codigo=%s", cod)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def index():
if request.method == "POST":
details = request.form
firstName = details['fname']
lastName = details['lname']
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"INSERT INTO MyUsers(firstName, lastName) VALUES (%s, %s)",
(firstName, lastName))
conn.commit()
cursor.close()
return 'success'
return render_template('index.html')
def high_value_addr():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT max(amount),hash from kafkadata GROUP BY hash;")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def setTrainData(self):
# self.train_arr = trainData1
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT mri_id,contrast, energy,entropy,homogeneity,correlation,result FROM mri_classifications_data WHERE data_type LIKE 'Training%' ")
row = cursor.fetchall()
trainData = []
for x in row:
trainData.append({"index":x[0],"contrast":x[1],"energy":x[2],"entropy":x[3],"homogeneity":x[4],"correlation":x[5],"result":x[6]},)
# print(type(x[2]))
# print(row)
# print(trainData)
self.train_arr = trainData
def process():
_fullname = request.form['fullname']
_username = request.form['username']
_email = request.form['email']
_password = request.form['password']
_repassword = request.form['repassword']
# check username in db
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM user where username=%s or email=%s",(_username,_email))
row = cursor.fetchone()
if row:
flash('Maaf username atau email sudah digunakan','info')
return redirect('/signup')
else:
# print("berhasil")
if _fullname and _username and _email and _password == _repassword and request.method == 'POST':
_hashed_password = hashlib.md5(_password.encode())
sql = "INSERT INTO user (name,username,email,password,type_user_id) VALUES (%s,%s,%s,%s,%s)"
data = (_fullname,_username,_email,_hashed_password.hexdigest(),'2',)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql,data)
conn.commit()
flash('Register successfully','success')
return redirect('/signup')
elif _password != _repassword and request.method =='POST':
flash('Password tidak sama','danger')
return redirect('/signup')
else:
flash('Register failed','danger')
return redirect('/signup')
cursor.close()
conn.close()
def delete_bens(id):
'Excluir cadastro de bem a partir do ID.'
if request.method == "POST":
try:
_id = id
if _id:
sql = "DELETE FROM db_inquest.tb_bens WHERE bens_id=%s"
data = (_id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
return redirect(url_for("bens"))
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
elif id == 0:
return render_template("/bens/bens_buscar_id.html")
else:
try:
_id = id
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM db_inquest.tb_bens WHERE bens_id = %s", id)
rows = cursor.fetchall()
return render_template('/bens/bens_excluir.html', rows = rows)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def put_file():
# If there is no filename in the json PUT request,
# or no json at all, then return a 400 (Bad Request) HTTP error code.
if not request.json or not 'filename' in request.json:
return create_JSON_response(400, "Bad request.")
SECONDS_IN_HOUR = 3600
DECIMAL_PLACES = 2
filename = request.json['filename']
response = None
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(f"SELECT filename FROM 3dprints WHERE filename='{filename}'")
filename_data = cursor.fetchall()
# Check if the file already exsits in the database.
# If it does, update the start datetime and increment
# the print count by 1. Otherwise, create a new record..
if len(filename_data) > 0:
update_file_data(conn, cursor, filename)
response = create_JSON_response(200, f"{filename} updated successfully.")
elif not 'lastPrintTime' in request.json: # 'lastPrintTime' is needed to create a new record in 3dprints.
return create_JSON_response(400, "Bed request. 'lastPrintTime' was not provided in request.json.")
else:
print_hours = round(request.json['lastPrintTime'] / SECONDS_IN_HOUR, DECIMAL_PLACES)
sql = "INSERT INTO 3dprints(filename, print_count, print_time) VALUES(%s, %s, %s)"
data = (filename, 1, print_hours)
cursor.execute(sql, data)
new_id_3dprint = cursor.lastrowid
conn.commit()
add_datetime(conn, cursor, new_id_3dprint)
response = create_JSON_response(200, f"{filename} added successfully.")
return response
except Exception as e:
traceback.print_exc(file=sys.stderr)
return create_JSON_response(500, MSG_500)
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def delete_garagem(id):
conn = mysql.connect()
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM garagem WHERE id_garagem=%s", (id, ))
conn.commit()
resp = jsonify('registo apagado com sucesso!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def garagem(id):
conn = mysql.connect()
cursor = conn.cursor()
try:
cursor.execute("SELECT * FROM garagem WHERE id_garagem=%s", id)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_tipo(id):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM Tipo WHERE id=%s", (id, ))
conn.commit()
flash('Género Eliminado Exitosamente!')
return redirect('/Tipo')
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_area(id):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM Area WHERE id=%s", (id, ))
conn.commit()
flash('Area Eliminada Exitosamente!')
return redirect('/Area')
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def bens_por_id(id):
'Consultar bens cadastrados pelo ID.'
try:
_id = id
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM db_inquest.tb_bens WHERE bens_id = %s", id)
rows = cursor.fetchall()
return render_template('/bens/bens.html', rows = rows)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def fetch_skills(naam):
skills = {}
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur1 = conn.cursor(pymysql.cursors.DictCursor)
cur.execute("Select * from resume_skills Where user_id = '" +
str(naam['user_id']) + "';")
if cur.rowcount > 0:
for r in cur:
cur1.execute("Select title from skills Where skill_id = '" +
str(r['skill_id']) + "';")
k = cur1.fetchone()
skills[k['title']] = r['level']
return skills
return 'empty'
def fetch_pd(naam):
conn = mysql.connect()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute("Select * from resume Where user_id = '" +
str(naam['user_id']) + "';")
records = cur.fetchall()
op = {}
if len(records) > 0:
for r in records:
op['email'] = r['email']
op['location'] = r['location']
op['phone_no'] = r['phone_no']
op['additional_details'] = r['additional_details']
return op
return 'empty'
def delete_user(id):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id, ))
conn.commit()
flash('User deleted successfully!')
return redirect('/')
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def cliente_por_id(id): # Consultar cliente pelo ID
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"SELECT * FROM db_desafio.tb_clientes WHERE cliente_id = %s", id)
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def excluir_pedido(id): # Excluir pedido
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM db_desafio.tb_pedidos WHERE pedido_id=%s",
(id, ))
conn.commit()
resp = jsonify('Pedido excluido com sucesso!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def clienteTest_por_id(id): # Consultar cliente pelo ID
try:
_id = id
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"SELECT * FROM db_desafio.tb_clientes WHERE cliente_id = %s", id)
rows = cursor.fetchall()
return render_template('clientes.html', rows=rows)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def patient():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
id = request.json['id']
cursor.execute("SELECT * FROM patient WHERE pat_id=%s", id)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_user(id):
conn=None
cursor=None
try:
conn=mysql.connect()
cursor=conn.cursor()
cursor.execute("DELETE FROM tbl_user WHERE user_id=%s",(id,))
conn.commit()
flash("User deleted successfully!")
return redirect(url_for('users'))
except Exception as e:
return(str(e))
finally:
cursor.close()
conn.close()
def meaning():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
word = request.json['word']
cursor.execute("SELECT meaning FROM conversion WHERE word=%s", word)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def edit_view(id):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM ad_table WHERE ad_id=%s", id)
row = cursor.fetchone()
if row:
return render_template('EditPost.html', row=row)
else:
return 'Error loading #{id}'.format(id=id)
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_patient():
try:
conn = mysql.connect()
cursor = conn.cursor()
id = request.json['id']
cursor.execute("DELETE FROM patient WHERE pat_id=%s", id)
conn.commit()
resp = jsonify('PATIENT deleted successfully')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
