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