def deleteAddress(UserID, AddrID):
query = "DELETE FROM addresses where id={} and client_id={}"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(AddrID, UserID))
mysqlConnector.get_db().commit()
cursor.close()
return Response(status=200)
def createUser():
userData = request.get_json()
query = "INSERT INTO clients (rut, name, password, email, phone, wholesaler) VALUES (\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',{}) "
wholesaler = (userData['wholesaler'] == 1)
cursor = mysqlConnector.get_db().cursor()
try:
cursor.execute(
query.format(
userData['rut'], userData['name'],
bcrypt.hashpw(userData['pass'].encode('utf-8'),
bcrypt.gensalt(12)).decode('utf-8'),
userData['email'], userData['phone'], wholesaler))
mysqlConnector.get_db().commit()
cursor.execute("SELECT id FROM clients where rut=\'{}\';".format(
userData['rut']))
id = cursor.fetchone()[0]
cursor.close()
return Response(json.dumps({"id": id}),
mimetype='application/json',
status=201)
except pymysql.err.IntegrityError:
cursor.close()
return Response(json.dumps({"error": "Rut ya existente"}),
mimetype='application/json',
status=400)
def paymentReturn():
print("PAYMENT CAME BACK!")
token = request.form['token_ws']
#token = request.get_json()['token']
try:
transaction = transbankInitializer.getWebpay().get_transaction_result(
token)
except tbk.soap.exceptions.SoapServerException:
return json.dumps({"msg": "Timeout", "tr": transaction})
transaction_detail = transaction['detailOutput'][0]
print(transaction_detail)
transbankInitializer.getWebpay().acknowledge_transaction(token)
if transaction_detail['responseCode'] == 0:
#Update, Payment validated
query = "UPDATE orders SET payment_status=2 where id=" + transaction_detail[
'buyOrder']
print(query)
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query)
mysqlConnector.get_db().commit()
cursor.close()
#Redirect para ver el menu. Sale todo bien
return redirect("http://" + getip() + ":8080/#/success", code=302)
else:
return Response(json.dumps({"msg": "PAYMENT FAILED"}))
def editAddress(UserID, AddrID):
addrInfo = request.get_json()
query = "UPDATE addresses SET town_id={}, address=\'{}\',alias=\'{}\' where client_id={} AND id={};"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(
query.format(addrInfo['townID'], addrInfo['addr'], addrInfo['alias'],
UserID, AddrID))
mysqlConnector.get_db().commit()
cursor.close()
return Response(status=200)
def createAddress(UserID):
addrInfo = request.get_json()
insertQuery = "INSERT INTO addresses (town_id,address, alias,client_id) VALUES ({}, \'{}\', \'{}\', {})"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(
insertQuery.format(addrInfo['townID'], addrInfo['addr'],
addrInfo['alias'], UserID))
mysqlConnector.get_db().commit()
cursor.close()
return Response(status=201)
def login():
userData = request.get_json()
if not userData['name']:
return Response(mimetype='application/json',
status=400,
response=json.dumps({"msg": "Username missing"}))
if not userData['pass']:
return Response(mimetype='application/json',
status=400,
response=json.dumps({"msg": "Password missing"}))
query = "SELECT password, id FROM clients where rut=\'{}\'"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(userData['name']))
pwd = cursor.fetchone()
cursor.close()
if pwd is None:
return Response(status=400)
if not (bcrypt.checkpw(userData['pass'].encode('utf-8'),
pwd[0].encode('utf-8'))):
return Response(mimetype='application/json', status=401)
else:
return Response(mimetype='application/json',
status=200,
response=json.dumps({
"id":
pwd[1],
"token":
create_access_token(identity=(userData['name'] +
"::" + pwd[0])),
"refresh":
create_refresh_token(identity=userData['name'] +
"::" + pwd[0])
}))
def ordersByClient(ID):
orderQuery = "SELECT t.name, a.address, o.delivery_status, o.payment_status, o.amount, o.delivery_date, p.name, op.quantity, op.product_format_id FROM addresses a INNER JOIN towns t on (a.town_id = t.id) INNER JOIN orders o on o.address_id = a.id INNER JOIN order_product op on op.order_id= o.id INNER JOIN products p on p.id = op.product_id where a.client_id={}"
prodFormatQuery = "SELECT pf.name FROM product_formats pf where id={}"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(orderQuery.format(ID))
orders = []
for order in cursor.fetchall():
formatedOrder = {
'town': order[0],
'address': order[1],
'delivery_status': str(order[2]),
'payment_status': order[3],
'amount': order[4],
'delivery_date': str(order[5]),
'product_name': order[6],
'quantity': order[7]
}
if (order[8] != 'NULL' or order[8] != None):
cursor.execute(orderQuery.format(ID))
prodformat = cursor.fetchone()
formatedOrder['format'] = prodformat[0]
orders.append(formatedOrder)
cursor.close()
return Response(status=200,
response=json.dumps(orders),
mimetype="application/json")
def getAvailableTimeBlocks(date):
query = "SELECT otb.time_block_id, COUNT(*) FROM orders o INNER JOIN order_time_block otb on o.id=otb.order_id where o.delivery_date = \'{}\' AND o.delivery_status=1 GROUP BY o.delivery_date, otb.time_block_id ORDER BY o.delivery_date;"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(date))
ocupacion = cursor.fetchall()
cursor.execute("SELECT id, max_orders, start, end FROM time_blocks")
capacidad = cursor.fetchall()
bloques = []
diaBase = []
for hora in capacidad:
diaBase.append({
"id": hora[0],
"start": str(hora[2]),
"end": str(hora[3])
})
for bloque in capacidad:
#Reset, dia terminado
for ocupado in ocupacion:
if (bloque[0] == ocupado[0]):
if (bloque[1] < ocupado[1]):
diaBase.remove({
"id": bloque[0],
"start": str(bloque[2]),
"end": str(bloque[3])
})
return Response(json.dumps(diaBase), mimetype='application/json')
def getTowns():
cursor = mysqlConnector.get_db().cursor()
cursor.execute("SELECT id, name FROM towns;")
result = cursor.fetchall()
towns = []
for town in result:
towns.append({
"id": town[0],
"name": town[1]
})
cursor.close()
return Response(json.dumps(towns), mimetype='application/json')
def getAll():
productQuery = "SELECT p.id, p.name, p.price FROM products p;"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(productQuery)
result = cursor.fetchAll()
#result as list of list
for product in result:
cursor.close()
def getProductByID():
pass
def getUserInfo():
query = "SELECT name, email, phone FROM clients where rut = \'{}\'"
rut = get_jwt_identity().split("::")[0]
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(rut))
results = cursor.fetchone()
cursor.close()
return Response(json.dumps({
"rut": rut,
"name": results[0],
"email": results[1],
"phone": results[2]
}),
mimetype='application/json')
def getFormatByProduct(ProdId, FormId):
sqlQuery = "SELECT name, capacity, added_price, minimum_quantity FROM product_formats where product_id={} and id={};"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery.format(ProdId, FormId))
result = cursor.fetchone()
cursor.close()
if (result is None):
return Response(json.dumps({}), mimetype='application/json')
format = {
"name": result[0],
"capacity": result[1],
"added_price": result[2],
"minimum_quantity": result[3]
}
return Response(json.dumps(format), mimetype='application/json')
def editUser():
newUserData = request.get_json()
query = "UPDATE clients SET"
params = []
if 'name' in newUserData:
query = query + " name=\'{}\'"
params.append(newUserData['name'])
if 'pass' in newUserData:
if len(params) > 0:
query = query + ","
query = query + " password=\'{}\'"
params.append(
bcrypt.hashpw(newUserData['pass'].encode('utf-8'),
bcrypt.gensalt(12)).decode('utf-8'))
if 'email' in newUserData:
if len(params) > 0:
query = query + ","
query = query + " email=\'{}\'"
params.append(newUserData['email'])
if 'phone' in newUserData:
if len(params) > 0:
query = query + ","
query = query + " phone=\'{}\'"
params.append(newUserData['phone'])
if 'wholesaler' in newUserData:
if len(params) > 0:
query = query + ","
query = query + " wholesaler=\'{}\'"
params.append((newUserData['wholesaler'] == 1))
query = query + " WHERE rut= \'{}\'"
params.append(get_jwt_identity().split("::")[0])
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(*params))
cursor.close()
mysqlConnector.get_db().commit()
return Response(mimetype='application/json', status=200)
def getAllProductDiscounts(ProdID):
sqlQuery = "SELECT id, discount_per_liter, min_quantity, max_quantity FROM product_discounts where product_id={}"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery.format(ProdID))
result = cursor.fetchall()
if (result is None):
cursor.close()
return Response(json.dumps({}), mimetype='application/json')
discounts = []
for discount in result:
discounts.append({
"id": discount[0],
"discount_per_liter": discount[1],
"min_qty": discount[2],
"max_qty": discount[3]
})
cursor.close()
return Response(json.dumps(discounts), mimetype='application/json')
def getAllAdresses(UserID):
sqlQuery = "SELECT a.id, t.name, a.address, a.alias FROM addresses a INNER JOIN towns t on t.id = a.town_id where a.client_id ={}"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery.format(UserID))
result = cursor.fetchall()
if (result is None):
cursor.close()
return Response(json.dumps({}), mimetype='application/json')
addresses = []
for addr in result:
addresses.append({
"id": addr[0],
"town": addr[1],
"addr": addr[2],
"alias": addr[3]
})
cursor.close()
return Response(json.dumps(addresses), mimetype='application/json')
def getProductByID(Id):
sqlQuery = "SELECT name, price FROM products where id={};"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery.format(Id))
result = cursor.fetchone()
if (result is None):
product = {}
else:
cursor.execute(
"SELECT COUNT(*) FROM product_formats where product_id={};".format(
Id))
product = {
"name": result[0],
"price": result[1],
"has_formats": cursor.fetchone()[0] > 0
}
cursor.close()
return Response(json.dumps(product), mimetype='application/json')
def getFormatsByProduct(Id):
sqlQuery = "SELECT id, name, capacity, added_price, minimum_quantity FROM product_formats where product_id={};"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery.format(Id))
result = cursor.fetchall()
cursor.close()
if (result is None):
return Response(json.dumps({}), mimetype='application/json')
formats = []
for row in result:
format = {
"id": row[0],
"name": row[1],
"capacity": row[2],
"added_price": row[3],
"minimum_quantity": row[4]
}
formats.append(format)
return Response(json.dumps(formats), mimetype='application/json')
def wrapper(*args, **kwargs):
verify_jwt_in_request()
identity = get_jwt_identity()
splited = identity.split("::")
cursor = mysqlConnector.get_db().cursor()
cursor.execute("SELECT password FROM clients WHERE rut=\'{}\'".format(
splited[0]))
pwd = cursor.fetchone()
cursor.close()
if (pwd is None):
return Response(mimetype='application/json',
status=400,
response=json.dumps({"msg": "No user/pass found"}))
elif (pwd[0] != splited[1]):
return Response(mimetype='application/json',
status=400,
response=json.dumps({"msg": "pass missed"}))
else:
return fn(*args, **kwargs)
def getAllProducts():
sqlQuery = "SELECT id, name, price FROM products;"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(sqlQuery)
result = cursor.fetchall()
if (result is None):
cursor.close()
return Response(json.dumps({}), mimetype='application/json')
products = []
for product in result:
cursor.execute(
"SELECT COUNT(*) FROM product_formats where product_id={};".format(
product[0]))
newResult = cursor.fetchone()
products.append({
"id": product[0],
"name": product[1],
"price": product[2],
"has_formats": newResult[0] > 0
})
cursor.close()
return Response(json.dumps(products), mimetype='application/json')
def refresh():
userData = get_jwt_identity().split("::")
query = "SELECT password FROM clients where rut=\'{}\'"
cursor = mysqlConnector.get_db().cursor()
cursor.execute(query.format(userData[0]))
pwd = cursor.fetchone()
cursor.close()
if pwd is None:
return Response(status=400,
mimetype='application/json',
response=json.dumps({"msg": "User doesn't exist"}))
if not (userData[1].encode('utf-8') == pwd[0].encode('utf-8')):
return Response(mimetype='application/json',
status=401,
response=json.dumps({"msg": "Invalid Password"}))
else:
return Response(mimetype='application/json',
status=200,
response=json.dumps({
"token":
create_access_token(identity=(userData[0] + "::" +
pwd[0]))
}))
def createOrder(ID):
orderDetails = request.get_json()
orderQuery = "INSERT INTO orders (address_id, delivery_status, payment_status, amount, delivery_date) VALUES ({},{},{},{},\'{}\')"
orderTimeBlockQuery = "INSERT INTO order_time_block (order_id, time_block_id) VALUES ({}, {})"
orderProductQuery = "INSERT INTO order_product (order_id, product_id, product_format_id, quantity) VALUES ({},{},{},{})"
getOrderID = "SELECT id FROM orders where (address_id={} AND delivery_status={} AND payment_status={} AND amount={} AND delivery_date=\'{}\')"
cursor = mysqlConnector.get_db().cursor()
amount = 0
#Verify identity with JWT and address
identity = get_jwt_identity()
splited = identity.split("::")
cursor.execute("SELECT id FROM clients where rut=\'{}\'".format(
splited[0]))
vari = cursor.fetchone()
#Verify if id from DB is same as id in URL
if (int(vari[0]) != int(ID)):
#Bad Request
return Response(status=400,
response=json.dumps(
{"msg": "Token doesn't belongs to UserID"}),
mimetype="application/json")
#Verify if address belongs to user
cursor.execute(
"SELECT count(*) FROM addresses where id={} and client_id={}".format(
int(orderDetails['addressID']), ID))
if not (cursor.fetchone()[0]):
#Bad Request (address doesn't valid)
return Response(status=400,
response=json.dumps(
{"msg": "Address doesn't belong to user"}),
mimetype="application/json")
cursor.execute("SELECT wholesaler FROM clients where id={}".format(ID))
isWholesaler = cursor.fetchone()[0]
if (isWholesaler):
priceQuery = "SELECT wholesaler_price FROM products where id={}"
else:
priceQuery = "SELECT price FROM products where id={}"
for product in orderDetails['products']:
print(product)
#Normal price
cursor.execute(priceQuery.format(product['id']))
amount = int(cursor.fetchone()[0]) * int(product['quantity']) + amount
#Apply Discounts
#cursor.execute("SELECT discount_per_liter FROM product_discounts WHERE (min_quantity>{0} OR min_quantity={0}) AND (max_quantity<{0} OR min_quantity={0})".format(formatID))
try:
if not isWholesaler:
cursor.execute(
"SELECT discount_per_liter FROM product_discounts WHERE min_quantity <= {} ORDER BY min_quantity DESC"
.format(product['quantity']))
amount = -1 * cursor.fetchone()[0] * int(
product['quantity']) + amount
except TypeError:
pass
try:
if ('format' in product):
#Add format price
cursor.execute(
"SELECT added_price, capacity FROM product_formats where id={} and capacity>0"
.format(int(product['format'])))
formatInfo = cursor.fetchone()
#Apply format
amount = amount + (math.ceil(
int(product['quantity']) / formatInfo[1])) * formatInfo[0]
print(amount)
except TypeError:
pass
#first insert order query
#Status are false by default
cursor.execute(
orderQuery.format(int(orderDetails['addressID']), 1, 1, amount,
orderDetails['delivery_date']))
cursor.execute(
getOrderID.format(int(orderDetails['addressID']), 1, 1, amount,
orderDetails['delivery_date']))
mysqlConnector.get_db().commit()
orderID = cursor.fetchall()[-1][0]
#Then orderTimeBlock
for time_block in orderDetails['time_block']:
cursor.execute(
orderTimeBlockQuery.format(orderID, int(time_block['id'])))
for product in orderDetails['products']:
if ('format' not in product):
product['format'] = 'NULL'
cursor.execute(
orderProductQuery.format(orderID, product['id'], product['format'],
product['quantity']))
mysqlConnector.get_db().commit()
cursor.close()
#PAYMENT Section
transaction = transbankInitializer.getWebpay().init_transaction(
amount=amount,
buy_order=orderID,
return_url='http://' + getip() + ':5000/v1/payment',
final_url='http://' + getip() + ':5000' + '',
session_id=identity)
return Response(status=201,
response=json.dumps({
"payurl":
transaction['url'] + '?token_ws=' +
transaction['token']
}),
mimetype="application/json")