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