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