def deleteAuction(self):
sqlCheckSubasta = "SELECT ID_SUBASTA WHERE ID_SUBASTA = :idSubasta"
sqlSubasta = "DELETE FROM SUBASTA WHERE ID_SUBASTA = :1"
connection = OracleConnect.makeConn()
try:
if len(self.id_subasta) > 0:
#? Create an instance of the cursor
cursor = connection.cursor()
#? Check if the auction exist and fetch results
cursor.execute(sqlCheckSubasta, idSubasta=self.id_subasta)
fetchSub = cursor.fetchall()
if len(fetchSub) > 0:
#? Update values of the auction
cursor.execute(sqlSubasta, (self.id_subasta))
connection.commit()
return returnActionSuccess("Auction", "eliminada")
else:
# Return error no auction found
return returnError("AuctionNotFound")
else:
# Return error no id given
return returnError("AuctionNotID")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
finally:
if connection != "":
connection.close()
def updateUser(self):
sqlUsuario = "UPDATE USUARIO SET USUARIO = :1, CLAVE_USER = :2, ESTADO = :3 WHERE ID_USUARIO = :4"
sqlPersona = "UPDATE PERSONA SET EMAIL = :1, DIRECCION = :2, FONO = :3, CELULAR = :4 WHERE RUT = :5"
# sqlCliente = "UPDATE CLIENTE SET ID_TIPOCLIENTE = :1 WHERE ID_CLIENTE = :2"
sqlCheckUser = "******"
connection = OracleConnect.makeConn()
# TODO: GET VALUES FOR CLIENTE
try:
cursor = connection.cursor()
cursor.execute(sqlCheckUser, usrName=self.username.upper())
fetchedQuery = cursor.fetchall()
if fetchedQuery:
cursor.execute(
sqlUsuario,
(self.username, self.password, self.estado, self.id))
cursor.execute(sqlPersona, (self.email, self.direccion,
self.fono, self.celular, self.rut))
#cursor.execute(sqlCliente, (self.username.upper(), self.rut, self.tipoCliente))
connection.commit()
return returnActionSuccess("newUSR", "actualizado")
else:
return returnError("noUSR")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
finally:
if connection != "":
connection.close()
def updateAuction(self):
sqlCheckSubasta = "SELECT ID_SUBASTA WHERE ID_SUBASTA = :idSubasta"
sqlSubasta = "UPDATE SUBASTA SET VALOR = :1, DESCRIPCION = :2, ID_TRANSPORTISTA = :3, ESTADO = :4, FEC_INICIO = :5, FEC_TERMINO = :6 WHERE ID_SUBASTA = :7"
connection = OracleConnect.makeConn()
try:
if len(self.id_subasta) > 0:
#? Create an instance of the cursor
cursor = connection.cursor()
#? Check if the auction exist and fetch results
cursor.execute(sqlCheckSubasta, idSubasta=self.id_subasta)
fetchSub = cursor.fetchall()
if len(fetchSub) > 0:
#? Update values of the auction
cursor.execute(
sqlSubasta,
(self.value, self.desc, self.id_trans, self.status,
self.startData, self.endDate, self.id_subasta))
connection.commit()
return returnActionSuccess("Auction", "actualizada")
else:
# Return error no auction found
return returnError("AuctionNotFound")
else:
# Return error no id given
return returnError("AuctionNotID")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
finally:
if connection != "":
connection.close()
def createProduct(self):
sql = "INSERT INTO PRODUCTO (NOMBRE, DESCRIPCION, PRECIO, CALIDAD, FEC_INGRESO, ID_COMERCIANTE, STOCK, TIPO_PRODUCTO) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)"
connection = OracleConnect.makeConn()
#? Attemp creation of new products
try:
cursor = connection.cursor()
cursor.execute(sql,
(self.name, self.desc, self.price, self.quality,
self.registerDate, self.id_comerciante, self.stock,
self.productType))
connection.commit()
return returnActionSuccess("Producto", "creado")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
else:
return returnError("ProductErr", "crear")
finally:
if connection != "":
connection.close()
return "created"
def createClientType(self):
sql = "INSERT INTO TIPO_CLIENTE (NOMBRE_TIPO, DESCRIPCION_TIPO) VALUES (:1, :2)"
connection = OracleConnect.makeConn()
#? Attempt to insert all the data
try:
cursor = connection.cursor()
cursor.execute(sql, (self.name, self.desc))
connection.commit()
finally:
if connection != "":
connection.close()
def createUser(self):
sqlUsuario = "USUARIO_security.add_user"
sqlPersona = "INSERT INTO PERSONA (RUT, NOMBRE, APPAT, APMAT, EMAIL, DIRECCION, FONO, CELULAR) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)"
sqlCliente = "INSERT INTO CLIENTE (ID_USUARIO, ID_PERSONA, ID_TIPOCLIENTE) VALUES ((SELECT ID_USUARIO FROM USUARIO WHERE USUARIO = :1), (SELECT ID_PERSONA FROM PERSONA WHERE RUT = :2), :3)"
#? Queries to check if user exist before insert
sqlCheckUser = "******"
sqlCheckRUT = "SELECT ID_PERSONA FROM PERSONA WHERE RUT = :usrRUT"
#? Make connection with the DB
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
#? Check if user exist
cursor.execute(sqlCheckUser, usrName=self.username.upper())
fetchedQuery = cursor.fetchall()
if len(fetchedQuery) == 0:
#? Check if RUT exist
cursor.execute(sqlCheckRUT, usrRUT=self.rut)
fetchedQuery = cursor.fetchall()
if len(fetchedQuery) == 0:
#? Do inserts
cursor.callproc(sqlUsuario, [self.username, self.password])
cursor.execute(
sqlPersona,
(self.rut, self.name, self.appat, self.apmat,
self.email, self.direccion, self.fono, self.celular))
cursor.execute(
sqlCliente,
(self.username.upper(), self.rut, self.tipoCliente))
connection.commit()
return returnActionSuccess("newUSR", "creado")
else:
#* RETURN ERROR BY RUT
return returnError("newUSR", "rut")
else:
#* RETURN ERROR BY USERNAME
return returnError("newUSR", "nombre de usuario")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
else:
return returnError("actionUSR", "crear")
finally:
if connection != "":
connection.close()
def getAllUsers(self):
sql = "SELECT USR.USUARIO, USR.CLAVE_USER, USR.ESTADO, USR.ID_USUARIO, CLT.ID_TIPOCLIENTE, PRS.RUT, PRS.NOMBRE, PRS.APPAT, PRS.APMAT, PRS.EMAIL, PRS.DIRECCION, PRS.FONO, PRS.CELULAR, CLT.ID_CLIENTE FROM USUARIO USR JOIN CLIENTE CLT ON USR.ID_USUARIO = CLT.ID_USUARIO JOIN PERSONA PRS ON CLT.ID_PERSONA = PRS.ID_PERSONA"
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sql)
fetchQuery = cursor.fetchall()
if len(fetchQuery) > 0:
foundUsers = []
for row in fetchQuery:
#* Create an object with the results and update the main array
userObject = {
'USUARIO': row[0],
'CLAVE_USER': row[1],
'ESTADO': row[2],
'ID_USUARIO': row[3],
'ID_TIPOCLIENTE': row[4],
'RUT': row[5],
'NOMBRE': row[6],
'APPAT': row[7],
'APMAT': row[8],
'EMAIL': row[9],
'DIRECCION': row[10],
'FONO': row[11],
'CELULAR': row[12],
'ID_CLIENTE': row[13]
}
#* Append the object to the user array
foundUsers.append(userObject)
#* Return array with the users
return jsonify(foundUsers), 200
else:
return jsonify({"err": "Failed to fetch the users"}), 400
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def getAuctionWinner(winnerParams):
#* Define queries
sqlUpdateSubasta = "UPDATE SUBASTA SET ID_TRANSPORTISTA = :1, ESTADO = 2 WHERE ID_SUBASTA = :2"
sqlUpdateDetalle = "UPDATE DETALLE_SUBASTA SET GANADORA = 1 WHERE ID_SUBASTA = :1 AND ID_TRANSPORTISTA = :2"
sqlInsertNacional = "INSERT INTO VENTA_LOCAL (DESCRIPCION_VENTA, BRUTO, IVA, NETO, ID_CLIENTE, ID_SUBASTA) VALUES ( :1, :2, :3, :4, :5, :6)"
sqlInsertInternacional = "INSERT INTO VENTA_EXTRANJERA (DESCRIPCION_VENTA, BRUTO, IVA, NETO, ID_CLIENTE, ID_SUBASTA) VALUES ( :1, :2, :3, :4, :5, :6)"
#* Get values from request
id_subasta = winnerParams["id_subasta"]
id_cliente = winnerParams["id_cliente"]
id_transportista = winnerParams["id_transportista"]
descripcion_venta = winnerParams["DESCRIPCION_VENTA"]
bruto = winnerParams["BRUTO"]
iva = winnerParams["IVA"]
neto = winnerParams["NETO"]
id_cliente = winnerParams["ID_CLIENTE"]
id_subasta = winnerParams["ID_SUBASTA"]
tipo_venta = winnerParams["TIPO_VENTA"]
#* Attempt Update && Insert
#* Make connection
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sqlUpdateSubasta, (id_transportista, id_subasta))
cursor.execute(sqlUpdateDetalle, (id_subasta, id_transportista))
if tipo_venta == 'NAC':
cursor.execute(
sqlInsertNacional,
(descripcion_venta, bruto, iva, neto, id_cliente, id_subasta))
else:
cursor.execute(
sqlInsertInternacional,
(descripcion_venta, bruto, iva, neto, id_cliente, id_subasta))
connection.commit()
return returnActionSuccess("Winner")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def getAllAuctions():
sqlGetAll = "SELECT * FROM SUBASTA"
# Query with joins
#sqlGetAll = "SELECT SU.ID_SUBASTA, SU.VALOR, SU.DESCRIPCION, SU.ID_TRANSPORTISTA, SU.ESTADO, SU.FEC_INICIO, SU.FEC_TERMINO FROM SUBASTA SU"
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sqlGetAll)
fetchQuery = cursor.fetchall()
if len(fetchQuery) > 0:
#do something
foundAuctions = []
for row in fetchQuery:
auctionObj = {
'ID_SUBASTA': row[0],
'VALOR': row[1],
'DESCRIPCION': row[2],
'ID_TRANSPORTISTA': row[3],
'ESTADO': row[4],
'FEC_INICIO': row[5],
'FEC_TERMINO': row[6],
'ID_CLIENTE': row[7],
'TIPO_SUBASTA': row[8],
'CIUDAD': row[9],
'PAIS': row[10],
'DIRECCION': row[11]
}
#? Append object to the array
foundAuctions.append(auctionObj)
#? Return array with all auctions objects
return jsonify(foundAuctions), 200
else:
return jsonify({"err": "Failed to fetch the auctions"}), 400
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def disableUser(self, opt):
sql = "UPDATE USUARIO SET ESTADO = 0 WHERE USUARIO = :1 AND CLAVE_USER = :2"
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sql, (opt['username'], opt['password']))
connection.commit()
return jsonify({"msg": "User disabled"}), 200
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def makeLogin(self):
sql = "SELECT * FROM USUARIO WHERE USUARIO = :usrName"
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sql, usrName=self.username)
fetchQuery = cursor.fetchall()
result = fetchQuery[0] if len(fetchQuery) != 0 else fetchQuery
#* Check if user and password are the same
if result and result[1] == self.username and result[
2] == self.password:
#* Create access token
access_token = create_access_token(identity=self.username)
foundUser = {
"id_usuario": result[0],
"username": result[1],
"estado_usuario": result[3],
"accessToken": access_token
}
#!-----
#TODO: INSERT ACCESS TOKEN TO DB
#* Return session_token with user data
return jsonify(userLogin=foundUser), 200
else:
return jsonify({"err": "Invalid username or password"}), 400
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def getAllAvailableProducts():
# sql = "SELECT DISTINCT NOMBRE FROM PRODUCTO ORDER BY NOMBRE ASC"
sql = "SELECT NOMBRE, DESCRIPCION, PRECIO, CALIDAD, FEC_INGRESO, ID_COMERCIANTE, STOCK, TIPO_PRODUCTO, ID_PRODUCTO FROM PRODUCTO"
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
productList = []
if len(result) > 0:
for product in result:
productObject = {
'NOMBRE': product[0],
'DESCRIPCION': product[1],
'PRECIO': product[2],
'CALIDAD': product[3],
'FEC_INGRESO': product[4],
'ID_COMERCIANTE': product[5],
'STOCK': product[6],
'TIPO_PRODUCTO': product[7],
'ID_PRODUCTO': product[8]
}
productList.append(productObject)
# productList.append(product[0])
return jsonify(productList), 200
else:
return jsonify({"err": "Failed to fetch the products"}), 400
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def getAllClientTypes(self):
sql = "SELECT * FROM TIPO_CLIENTE"
connection = OracleConnect.makeConn()
#? Attempt to fetch all the data
try:
clientTypesResponse = []
cursor = connection.cursor()
cursor.execute(sql)
results = cursor.fetchall()
#? Create the object in order to store the data
for ctype in results:
clientTypesResponse.append({
"id": ctype[0],
"name": ctype[1],
"desc": ctype[2]
})
#? Convert the object to JSON and return it
json_format = json.dumps(clientTypesResponse)
return json_format
finally:
if connection != "":
connection.close()
def getCarrierPerAuction(request):
sql = "SELECT DTS.* FROM SUBASTA SU JOIN DETALLE_SUBASTA DTS ON SU.ID_SUBASTA = DTS.ID_SUBASTA WHERE SU.ID_SUBASTA = :idSubasta"
connection = OracleConnect.makeConn()
#? Attemp creation of new auction
try:
cursor = connection.cursor()
cursor.execute(sql, idSubasta=request)
fetchQuery = cursor.fetchall()
if len(fetchQuery) > 0:
#do something
foundAuctions = []
for row in fetchQuery:
auctionObj = {
'ID_DETALLE_SUBASTA': row[0],
'OFERTA': row[1],
'TRANSPORTE_TIPO': row[2],
'ID_TRANSPORTISTA': row[3],
'ID_SUBASTA': row[4],
'GANADORA': row[5]
}
#? Append object to the array
foundAuctions.append(auctionObj)
#? Return array with all auctions objects
return jsonify(foundAuctions), 200
else:
return jsonify([]), 200
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
else:
return returnError("AuctionErr", "crear")
finally:
if connection != "":
connection.close()
def createAuction(self, productData):
sqlNewAuction = "INSERT INTO SUBASTA (VALOR, DESCRIPCION, ESTADO, FEC_INICIO, FEC_TERMINO, ID_CLIENTE, TIPO_SUBASTA, CIUDAD, PAIS, DIRECCION) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)"
sqlGetLastAuctionID = "SELECT ID_SUBASTA FROM (SELECT * FROM SUBASTA ORDER BY ID_SUBASTA DESC) WHERE ROWNUM = 1"
connection = OracleConnect.makeConn()
#? Attemp creation of new auction
try:
cursor = connection.cursor()
cursor.execute(sqlNewAuction,
(self.value, self.desc, 0, self.startDate,
self.endDate, self.client_id, self.auctionType,
self.city, self.country, self.address))
connection.commit()
#? FETCH ID FROM LAST INSERT
cursor = connection.cursor()
cursor.execute(sqlGetLastAuctionID)
fetchSub = cursor.fetchall()
if (fetchSub and fetchSub[0][0]):
details = {
"idLastAuction": fetchSub[0][0],
"products": productData
}
productsPerAuction(details)
return returnActionSuccess("Auction", "creada")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
else:
return returnError("AuctionErr", "crear")
finally:
if connection != "":
connection.close()
def auctionUpdate(auctionVal):
sqlUpdate = "UPDATE SUBASTA SET ESTADO = :1 WHERE ID_SUBASTA = :2"
id_subasta = auctionVal['id_subasta']
id_estado = auctionVal['id_estado']
#* Attempt Update && Insert
#* Make connection
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sqlUpdate, (id_estado, id_subasta))
connection.commit()
return returnActionSuccess("Auction", "actualizada")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()
def getProductPerAuction(request):
sql = "SELECT SU.ID_SUBASTA, PRD.ID_PRODUCTO, PRD.NOMBRE, PRD.PRECIO FROM SUBASTA SU JOIN SUB_OFERTA SBO ON SU.ID_SUBASTA = SBO.ID_SUBASTA JOIN PRODUCTO PRD ON SBO.ID_PRODUCTO = PRD.ID_PRODUCTO WHERE SU.ID_SUBASTA = :idSubasta"
connection = OracleConnect.makeConn()
#? Attemp creation of new auction
try:
cursor = connection.cursor()
cursor.execute(sql, idSubasta=request)
fetchQuery = cursor.fetchall()
if len(fetchQuery) > 0:
#do something
foundAuctions = []
for row in fetchQuery:
auctionObj = {
'ID_SUBASTA': row[0],
'ID_PRODUCTO': row[1],
'NOMBRE': row[2],
'PRECIO': row[3]
}
#? Append object to the array
foundAuctions.append(auctionObj)
#? Return array with all auctions objects
return jsonify(foundAuctions), 200
else:
return jsonify({"err": "Failed to fetch the products"}), 400
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
return returnDBError(errorCode)
else:
return returnError("AuctionErr", "crear")
finally:
if connection != "":
connection.close()
def productsPerAuction(details):
products = details['products']
auctionID = details['idLastAuction']
sql = 'INSERT INTO SUB_OFERTA (ID_SUBASTA, ID_PRODUCTO) VALUES (:1, :2)'
for product in products:
connection = OracleConnect.makeConn()
#? Attempt association of products and auctions
try:
cursor = connection.cursor()
cursor.execute(sql, (auctionID, product["productID"]))
connection.commit()
print("Ascociar ok!")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
regexSearch = GetORAerrCode(errorObj)
if regexSearch:
errorCode = regexSearch.group(0)
print("Error again")
else:
print("Error al asociar productos")
finally:
if connection != "":
connection.close()
def auctionDetails(auctionDetails):
aucValue = auctionDetails['value']
aucTransport = auctionDetails['transport_type']
aucIDCarrier = auctionDetails['id_transportista']
aucIDAuction = auctionDetails['id_subasta']
sqlInsertCarrier = "INSERT INTO DETALLE_SUBASTA (OFERTA, TRANSPORTE_TIPO, ID_TRANSPORTISTA, ID_SUBASTA, GANADORA) VALUES (:1, :2, :3, :4, 0)"
#? Make connection
connection = OracleConnect.makeConn()
try:
cursor = connection.cursor()
cursor.execute(sqlInsertCarrier,
(aucValue, aucTransport, aucIDCarrier, aucIDAuction))
connection.commit()
return returnActionSuccess("Participation")
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
return jsonify({
"err": "An error has ocurred",
"Error Code": errorObj.code,
"Error Message": errorObj.message
}), 400
finally:
if connection != "":
connection.close()