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")