def add_pedido(p):
try:
sql = "INSERT INTO PEDIDOS(data_pedido, status_pedido, id_user) VALUES(%s, %s, %s)"
data = (p.getDataPedido(), p.getStatusPedido(), p.getIdUser())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
idPedido = cursor.lastrowid
valorTotalProduto = None
conn.commit()
cursor.close()
conn.close()
for prod in p.listaProdutos:
sql_pp = "INSERT INTO PEDIDO_PRODUTOS(id_pedido, id_produto, preco_produto, tipo_volume, quantidade_produto, valor_total_produto) VALUES(%s, %s, %s, %s, %s, %s)"
valorTotalProduto = float(prod.getPreco()) * int(
prod.getQuantidade())
data = (idPedido, prod.getIdProduto(), prod.getPreco(),
prod.getTipoVolume(), prod.getQuantidade(),
valorTotalProduto)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql_pp, data)
conn.commit()
cursor.close()
conn.close()
resp = jsonify('Pedido added successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
def add_student():
conn = mysql.connect()
cursor = conn.cursor()
try:
_json = request.json
_name = _json['name']
_email = _json['email']
_phone = _json['phone']
if _name and _email and _phone and request.method == 'POST':
sqlQuery = "INSERT INTO student_info(name, email, phone) VALUES(%s, %s, %s)"
bindData = (_name, _email, _phone)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('Student added successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
# if cursor != "None":
cursor.close()
# if conn != "None":
conn.close()
def add_curso():
try:
_json = request.get_json(force=True)
_nome = _json['nome']
_descricao = _json['descricao']
_carga = _json['carga']
_totaulas = _json['totaulas']
_ano = _json['ano']
_preco = _json['preco']
_ativo = _json['ativo']
if _nome and _descricao and _carga and _totaulas and _ano and _preco and _ativo and request.method == 'POST':
sqlQuery = "INSERT INTO db_produtos.tbl_cursos (nome, descricao, carga, totaulas, ano, preco, ativo ) VALUES (%s,%s,%s,%s,%s,%s,%s)"
bindData = (_nome, _descricao, _carga, _totaulas, _ano, _preco,
_ativo)
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sqlQuery, bindData)
conn.commit()
response = jsonify('Curso adicionado com sucesso!')
response.status_code = 200
return response
else:
return not_found()
except Exception as error:
print(error)
finally:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.close()
conn.close()
def add_compra():
try:
_json = request.get_json(force=True)
_data = _json['data']
_idCliente = _json['idCliente']
_idCurso = _json['idCurso']
if _data and _idCliente and _idCurso and request.method == 'POST':
sqlQuery = "INSERT INTO db_vendas.tbl_cliente_compra_cursos (data, idCliente, idCurso ) VALUES (%s,%s,%s)"
bindData = (_data, _idCliente, _idCurso)
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sqlQuery, bindData)
conn.commit()
response = jsonify('Compra adicionado com sucesso!')
response.status_code = 200
return response
else:
return not_found()
except Exception as error:
return error, 500
finally:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.close()
conn.close()
def add_user():
try:
_json = request.get_json(force=True)
_nome = _json['nome']
_cpf = _json['cpf']
_email = _json['email']
_telefone = _json['telefone']
_senha = _json['senha']
if _nome and _cpf and _email and _telefone and _senha and request.method == 'POST':
sqlQuery = "INSERT INTO db_clientes.tbl_clientes (nome, cpf, email, telefone, senha) VALUES (%s,%s,%s,%s,%s)"
bindData = (_nome, _cpf, _email, _telefone, _senha)
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sqlQuery, bindData)
conn.commit()
response = jsonify('Cliente adicionado com sucesso!')
response.status_code = 200
return response
else:
return not_found()
except Exception as error:
print(error)
finally:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.close()
conn.close()
def add_produto(p):
try:
if p.getIdFornecedorPF() == None:
sql = "INSERT INTO PRODUTOS(desc_produto, tipo_volume, preco, id_fornecedorpj, status) VALUES(%s, %s, %s, %s, %s)"
data = (p.getDescProduto(), p.getTipoVolume(), p.getPreco(),
p.getIdFornecedorPJ(), p.getStatus())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('PRODUTO ' + p.getDescProduto() +
' added successfully!')
resp.status_code = 200
return resp
else:
if p.getIdFornecedorPJ() == None:
sql = "INSERT INTO PRODUTOS(desc_produto, tipo_volume, preco, id_fornecedorpf, status) VALUES(%s, %s, %s, %s, %s)"
data = (p.getDescProduto(), p.getTipoVolume(), p.getPreco(),
p.getIdFornecedorPF(), p.getStatus())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('PRODUTO ' + p.getDescProduto() +
' added successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_produto(p):
try:
if p.getIdFornecedorPF() == None:
sql = "UPDATE PRODUTOS SET desc_produto=%s, tipo_volume=%s, preco=%s, id_fornecedorpj=%s, status=%s WHERE id_produto=%s"
data = (p.getDescProduto(), p.getTipoVolume(), p.getPreco(),
p.getIdFornecedorPJ(), p.getStatus(), p.getIdProduto())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('PRODUTO: updated successfully!')
resp.status_code = 200
return resp
else:
if p.getIdFornecedorPJ() == None:
sql = "UPDATE PRODUTOS SET desc_produto=%s, tipo_volume=%s, preco=%s, id_fornecedorpf=%s, status=%s WHERE id_produto=%s"
data = (p.getDescProduto(), p.getTipoVolume(), p.getPreco(),
p.getIdFornecedorPF(), p.getStatus(), p.getIdProduto())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('PRODUTO: ' + p.getDescProduto() +
' updated successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def equipment():
if request.method == 'GET':
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM equipment")
rows = cursor.fetchall()
res = jsonify(rows)
res.status_code = 200
return res
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
if request.method == 'POST':
try:
_json = request.json
_Eq_ID = _json['eqId']
_Name = _json['name']
_Location = _json['location']
_Quantity = _json['quantity']
sqlQuery = "INSERT INTO Equipment (Eq_ID, Name, Location, Quantity) VALUES(%s, %s, %s, %s)"
data = (_Eq_ID, _Name, _Location, _Quantity)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, data)
conn.commit()
res = jsonify('Equipment created successfully.')
res.status_code = 200
return res
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
if request.method == 'PUT':
try:
_json = request.json
_Eq_ID = _json['eqId']
_Name = _json['name']
_Location = _json['location']
_Quantity = _json['quantity']
sql = "UPDATE Equipment SET Name=%s, Location=%s, Quantity=%s WHERE Eq_ID=%s"
data = (_Name, _Location, _Quantity, _Eq_ID)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
res = jsonify('Equipment updated successfully.')
res.status_code = 200
return res
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def routerPortUp(id, case):
print("INFO - Entro en el método routerPortUp")
if id in ROUTERS:
if case in PORTS:
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if (id != ROUTERS[0] or case != PORTS[2]):
cursor.execute("SELECT DURATION FROM STATISTICS WHERE ROUTER=%s AND PORT=%s",(id,case))
for row in cursor:
outQuery = row
response = jsonify(outQuery)
response.status_code = 200
return response
else:
return badRequest()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
elif case=="max" or case=="min":
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
total = []
for y in PORT:
while (id != ROUTERS[0] or y != PORT[2]):
cursor.execute("SELECT DURATION FROM STATISTICS WHERE ROUTER=%s AND PORT=%s",(id,y))
for row in cursor:
outQuery = row
total.append(valor(outQuery))
break
if case=="max":
tiempo = max(total)
else:
tiempo = min(total)
pos = (total.index(tiempo))+1
resultado = {"Port:" +str(pos) +" Duration: ": str(tiempo)}
response = jsonify(resultado)
response.status_code = 200
return response
except Exception as e:
print(e)
finally:
del total [:]
cursor.close()
conn.close()
else:
return badRequest()
def add_emp():
try:
_json = request.json
_name = _json['name']
_email = _json['email']
_phone = _json['phone']
_address = _json['address']
if _name and _email and _phone and _address and request.method \
== 'POST':
sqlQuery = \
'INSERT INTO rest_emp(name, email, phone, address) VALUES(%s, %s, %s, %s, %s)'
bindData = (_name, _email, _phone, _address)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('Employee added successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_emp(id):
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_age = _json['age']
_department = _json['department']
_subject = _json['subject']
if _id and _name and _age and _department and _subject and request.method == 'PUT':
sqlQuery = "UPDATE user SET name=%s, age=%s, department=%s, subject=%s WHERE id=%s"
bindData = (_name, _age, _department, _subject, _id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('User updated successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def total_price(duration_from, duration_to, car_id):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
datetimeFormat = '%Y-%m-%d %H:%M:%S'
date1 = duration_from
date2 = duration_to
diff = datetime.datetime.strptime(date2, datetimeFormat)\
- datetime.datetime.strptime(date1, datetimeFormat)
hrs_diff = diff.days * 24 + diff.seconds / 3600
#print(hrs_diff)
cursor.execute("SELECT pph from car where car_id=%s", (car_id))
empRows = cursor.fetchall()
result = empRows[0]["pph"]
val = int(result) * int(hrs_diff)
#print(val)
respone = jsonify(val)
#print(respone.data.decode('utf-8'))
respone.status_code = 200
return respone
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_email = _json['email']
_password = _json['pwd']
# validate the received values
if _name and _email and _password and _id and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
# save edits
sql = "UPDATE tbl_user SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
data = (_name, _email, _hashed_password, _id,)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User updated successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def confirm_email(token):
try:
email = key.loads(token, salt="email-confirm-key", max_age=172800)
if "'" in email or '"' in email or "(" in email or " )" in email:
raise Exception
if ',' in email or ";" in email or "%" in email:
raise Exception
except Exception as e:
return str(e)
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('SELECT confirmed FROM users WHERE email=%s', (email,))
data = cursor.fetchall()
except Exception as e:
return str(e)
if str(data[0][0]) == "1":
return render_template("already-confirmed.html")
else:
try:
cursor.execute("UPDATE users SET confirmed='1' WHERE email=%s",
(email,))
conn.commit()
return render_template("activated.html")
except Exception as e:
return str(e)
cursor.close()
conn.close()
def delete_emp():
try:
_json = request.json
_id = _json['id']
_id = _id.split(",")
print(_id)
if _id and request.method == 'POST':
for id in _id:
#id = int(id)
print(id)
sqlQuery = 'DELETE FROM user WHERE id in (%s)'
bindData = (id, )
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
message = 'Deleted'
status = 'true'
return jsonify(status=status, message=message), 200
else:
message = 'Error'
status = 'false'
return jsonify(status=status, message=message), 200
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_status():
try:
_json = request.json
_id = _json['id']
_id = _id.split(",")
#print(_status)
if _id and request.method == 'POST':
for id in _id:
id = int(id)
sqlQuery = \
'UPDATE user SET status = "Aproved" WHERE ID in (%s)'
bindData = (id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
message = 'Status updated'
status = 'true'
return jsonify(status=status, message=message), 200
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def vehiclesearch():
try:
_json = request.json
_vehiclenumber = _json['vehiclenumber']
if _vehiclenumber and request.method == 'POST':
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
'SELECT id, traveler, vehiclenum, phone, startpoint, endpoint, comments FROM rest_veh WHERE vehiclenum =%s',
_vehiclenumber)
empRow = cursor.fetchone()
if not empRow:
status = 'false'
user = ''
message = ''
return jsonify(status=status, user=user, message=message), 200
status = 'true'
user = empRow[1]
message = ''
return jsonify(status=status, user=user, message=message), 200
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def user_check():
try:
conn = mysql.connect()
cursor = conn.cursor()
json = request.get_json(force=True)
pname = json['pname']
# pid = json['pid']
cursor.execute("select * from patient where pname=%s;", pname)
row_headers = [x[0] for x in cursor.description]
empRows = cursor.fetchall()
json_data = []
for r in empRows:
json_data.append(dict(zip(row_headers, r)))
# print(r)
respone = jsonify(json_data)
respone.status_code = 200
return respone
cursor.close()
conn.close()
except Exception as e:
# print(e)
message = {
'status': 500,
'message': 'error in method',
}
print(message)
respone = jsonify(message)
respone.status_code = 500
return respone
finally:
print("finished")
def router(id):
print("INFO - Entro en el método router")
if request.args.get('time') == None:
tiempo = -1
else:
tiempo = int(request.args.get('time'))
if id in ROUTERS:
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if tiempo == -1:
cursor.execute("SELECT * FROM STATISTICS WHERE ROUTER=%s", id)
elif tiempo <= MAXTIME and tiempo > 0:
ts = calendar.timegm(time.gmtime(tiempo))
nuevo = ACTUAL - ts
cursor.execute("SELECT * FROM STATISTICS WHERE ROUTER=%s AND TIMESTAMP<%s",(id,nuevo))
else:
return badRequest()
outQuery = cursor.fetchall()
response = jsonify(outQuery)
response.status_code = 200
return response
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
else:
return notFound()
def create_student():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_email = _json['email']
_password = _json['password']
# insert record in database
sqlQuery = "INSERT INTO student(id, name, email, password) VALUES(%s, %s, %s, %s)"
data = (_id, _name, _email, _password)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, data)
conn.commit()
res = jsonify('Student created successfully.')
res.status_code = 200
return res
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
_json = request.json
_user_id = _json['user_id']
_name = _json['name']
_phone_number = _json['phone_number']
if _user_id and _name and _phone_number and request.method == 'PUT':
sqlQuery = (
"UPDATE user SET name=%s, phone_number=%s WHERE user_id=%s")
bindData = (_name, _phone_number, _user_id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('User updated successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def liststudent():
conn = mysql.connect()
cursor = conn.cursor()
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT id, name, email, phone FROM student_info")
studentRows1 = cursor.fetchall()
respone = jsonify(studentRows1)
respone.status_code = 200
return respone
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def post_comment():
try:
_json = request.json
# _post_id = _json['_post_id']
_stud_id = _json['_stud_id']
_course_id = _json['_course_id']
_title = _json['_title']
_desc = _json['_desc']
# insert record in database
sqlQuery = "INSERT INTO post (StudentId, CourseId, Title, Description,created_at) VALUES(%s, %s, %s, %s, %s)"
data = (_stud_id, _course_id, _title, _desc, datetime.now())
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, data)
conn.commit()
res = jsonify('comment posted successfully.')
res.status_code = 200
return res
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def add_user():
try:
_json = request.json
_ID = _json['ID']
_Name = _json['Name']
_Age = _json['Age']
_Department = _json['Department']
_Subject = _json['Subject']
if _ID and _Name and _Age and _Department and _Subject and request.method == 'POST':
sqlQuery = "INSERT INTO users(ID, Name, Age, Department, Subject) VALUES(%s, %s, %s, %s, %s)"
bindData = (_ID, _Name, _Age, _Department, _Subject)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
respone = jsonify('user added successfully!')
respone.status_code = 200
conn.commit()
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
_json = request.json
_ID = _json['ID']
_Name = _json['Name']
_Age = _json['Age']
_Department = _json['Department']
_Subject = _json['Subject']
if _ID and _Name and _Age and _Department and _Subject and request.method == 'PUT':
sqlQuery = "UPDATE users SET ID=%s, Name=%s, Age=%s, Department=%s, Subject=%s WHERE ID=%s"
bindData = (_ID, _Name, _Age, _Department, _Subject, _ID)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('user updated successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def add_emp():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_age = _json['age']
_department = _json['department']
_subject = _json['subject']
if _id and _name and _age and _department and _subject and request.method == 'POST':
sqlQuery = "INSERT INTO user(id, name, age, department, subject) VALUES(%s, %s, %s, %s, %s)"
bindData = (_id, _name, _age, _department, _subject)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('User added successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def book_car():
try:
_json = request.json
_user_id = _json['user_id']
_car_id = _json['car_id']
_duration_from = _json['duration_from']
_duration_to = _json['duration_to']
cars = check_availablity(_duration_from, _duration_to, _car_id)
res = cars.data.decode('utf-8')
#print(len(res))
#print(res)
if len(res) == 3:
_res = total_price(_duration_from, _duration_to, _car_id)
_cost = _res.data.decode('utf-8')
if _user_id and _car_id and _duration_from and _duration_to and _cost and request.method == 'POST':
sqlQuery = "INSERT INTO booking_details(user_id, car_id, duration_from,duration_to,total_price) VALUES(%s, %s, %s,%s,%s)"
bindData = (_user_id, _car_id, _duration_from, _duration_to,
_cost)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('Booking successfull!')
respone.status_code = 200
return respone
else:
return print("booking unsuccessful")
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_emp():
try:
_json = request.json
_id = _json['id']
_name = _json['name']
_email = _json['email']
_phone = _json['phone']
_address = _json['address']
if _name and _email and _phone and _address and _id \
and request.method == 'PUT':
sqlQuery = \
'UPDATE rest_emp SET name=%s, email=%s, phone=%s, address=%s WHERE id=%s'
bindData = (_name, _email, _phone, _address, _id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
respone = jsonify('Employee updated successfully!')
respone.status_code = 200
return respone
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
_json = request.get_json(force=True)
_id = _json['id']
_nome = _json['nome']
_cpf = _json['cpf']
_email = _json['email']
_telefone = _json['telefone']
_senha = _json['senha']
if _nome and _cpf and _email and _telefone and _senha and _id and request.method == 'PUT':
sqlQuery = "UPDATE db_clientes.tbl_clientes SET nome=%s, cpf=%s, email=%s, telefone=%s, senha=%s WHERE id=%s"
bindData = (_nome, _cpf, _email, _telefone, _senha, _id)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sqlQuery, bindData)
conn.commit()
response = jsonify('Dados alterados com sucesso!')
response.status_code = 200
return response
else:
return not_found()
except Exception as error:
print(error)
finally:
cursor.close()
conn.close()
def signUp():
try:
# read the posted values from the UI
address = request.form['inputAddress']
email = request.form['inputEmail']
# validate the received values
if address and email:
conn = mysql.connect()
cursor = conn.cursor()
# adds user to db
if cursor.execute('select (1) from users where email = %s limit 1', (email)):
return render_template("alreadyused.html")
else:
# creates user
cursor.execute('insert into users (email,zone) values (%s,%s)', (email,address))
# sends confirmation email
token = key.dumps(email, salt='email-confirm-key')
confirm_url = url_for('confirm_email',token=token,_external=True)
subject = "Confirm Your Email"
html = render_template('emailconfirm.html',confirm_url=confirm_url)
send_email(email, subject, html)
conn.commit()
return render_template('confirmation.html')
else:
return json.dumps({'html':'<span>Enter the required fields</span>'})
except Exception as e:
return json.dumps({'error2':str(e)})
finally:
cursor.close()
conn.close()
def update_curso(id):
try:
conn = mysql.connect()
cursor = conn.cursor()
_json = request.get_json(force=True)
_idCompra = _json['idCompra']
_data = _json['data']
_idCliente = _json['idCliente']
_idCurso = _json['idCurso']
if _data and _idCliente and _idCurso and _idCompra and request.method == 'PUT':
sqlQuery = "SELECT * FROM db_vendas.tbl_cliente_compra_cursos WHERE idCompra=%s"
cursor.execute(sqlQuery, id)
select = cursor.fetchone()
if not select:
return Response('Compra não cadastrada', status=400)
sqlQuery = "UPDATE db_vendas.tbl_cliente_compra_cursos SET data=%s, idCliente=%s, idCurso=%s, idCompra=%s WHERE idCompra=%s"
bindData = (_data, _idCliente, _idCurso, _idCompra, id)
cursor.execute(sqlQuery, bindData)
conn.commit()
response = jsonify('Dados alterados com sucesso!')
response.status_code = 200
return response
else:
return not_found()
except Exception as error:
return jsonify({"error": f"{error}"}), 500
finally:
cursor.close()
conn.close()
def signUp():
try:
conn = mysql.connect()
cursor = conn.cursor()
# read the posted values from the UI
address = request.form['inputAddress']
email = request.form['inputEmail']
# validate the received values
if address and email:
if "'" in email or '"' in email or "(" in email or " )" in email:
raise Exception
if ',' in email or ";" in email or "%" in email:
raise Exception
if '"' in address or "(" in address or " )" in address:
raise Exception
if "'" in address or ";" in address or "%" in address:
raise Exception
query = "SELECT * FROM users WHERE email=%s AND address=%s"
cursor.execute(query, (email, address))
data = cursor.fetchall()
if len(data) is 0:
query = "INSERT INTO users (email, address) values (%s,%s)"
cursor.execute(query, (email, address))
conn.commit()
cursor.close()
conn.close()
if cursor.execute('select (1) from users where email = %s limit 1',
(email)):
return render_template("alreadyused.html")
else:
# creates user
cursor.execute('insert into users (email,zone) values (%s,%s)',
(email, address))
# sends confirmation email
token = key.dumps(email, salt='email-confirm-key')
confirm_url = url_for('confirm_email',
token=token, _external=True)
subject = "Confirm Your Email"
html = render_template('emailconfirm.html',
confirm_url=confirm_url)
send_email(email, subject, html)
return render_template('confirmation.html')
else:
cursor.close()
conn.close()
return json.dumps({'html':
'<span>Enter the required fields</span>'})
except Exception as e:
return json.dumps({'error2': str(e)})
def signUp():
try:
conn = mysql.connect()
cursor = conn.cursor()
# read the posted values from the UI
name = request.form['inputName']
email = request.form['inputEmail']
# validate the received values
if name and email:
if "'" in email or '"' in email or "(" in email or " )" in email:
raise Exception
if ',' in email or ";" in email or "%" in email:
raise Exception
if '"' in name or "(" in name or " )" in name:
raise Exception
if "'" in name or ";" in name or "%" in name:
raise Exception
# checks if user is already registered
if cursor.execute('SELECT (1) FROM users WHERE email = %s LIMIT 1',
(email)):
return render_template("already-used.html")
else:
# sends confirmation email
token = key.dumps(email, salt='email-confirm-key')
confirm_url = url_for('confirm_email',
token=token, _external=True)
subject = "Confirm Your Email"
html = render_template('email-confirm.html',
confirm_url=confirm_url,
confirm_name=name)
send_email(email, subject, html)
#creates user
cursor.execute('INSERT INTO users (name,email) VALUES (%s,%s)',
(name, email))
conn.commit()
return render_template('confirmation.html')
else:
return json.dumps({'html':
'<span>Enter the required fields</span>'})
cursor.close()
conn.close()
except Exception as e:
print e
if "not a valid RFC-5321 address" in str(e):
return render_template('invalid.html')
else:
return json.dumps({'error1': str(e)})
def confirm_email(token):
try:
email = key.loads(token, salt="email-confirm-key", max_age=86400)
except:
return "error"
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('SELECT confirmed FROM users WHERE email=%s', (email))
data = cursor.fetchall()
# checks if user is already confirmed
if str(data[0][0]) == "1":
return render_template("alreadyconfirmed.html")
else:
# updates confirmed column to '1'
cursor.execute("UPDATE users SET confirmed='1' WHERE email=%s", (email))
conn.commit()
return render_template("activated.html")
cursor.close()
conn.close()
from config import key
from config import mysql
from config import send_email
from config import send_bulk_email
import feedparser
feed = feedparser.parse('https://uwaterloo.ca/events/events/events.xml')
yourevents = []
for x in range(len(feed.entries)):
yourevents.append((feed.entries[x].title, feed.entries[x].link))
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users;")
subscribers = cursor.fetchall()
if subscribers:
send_bulk_email(subscribers, yourevents, feed.feed.link)
cursor.close()
conn.close()
