def __executeAndGetAllRowsAndKeys(sqlQuery): cursor = connection.cursor() cursor.execute(sqlQuery) allRows = cursor.fetchall() columns_names = [i[0] for i in cursor.description] cursor.close() return {'data':allRows,'keys':columns_names}
def __unbookedBooksBy(orderID,adminID): query = 'select * from Забронированная_книга where order_id = {};'.format(orderID) Logger.log(adminID,query) bookedBooks = Admin.__executeAndGetAllRowsAndKeys(query) cursor = connection.cursor() query = 'delete from Забронированная_книга where order_id = {};'.format(orderID) cursor.execute(query) Logger.log(adminID,query) for row in bookedBooks['data']: query = 'update Товар set quantity=quantity+{} where id = {};'.format(row['count'],row['product_id']) Logger.log(adminID,query) cursor.execute(query) cursor.close() connection.commit()
def incQuantityOfBook(productID,quantity,adminID): result = {} if(quantity<0): result['status'] = 130 result['message'] = 'ADMIN:incorrect input data' return result cursor = connection.cursor() query = 'update Товар set quantity=quantity+{} where id = {};'.format(quantity,productID) cursor.execute(query) Logger.log(adminID,query) lastrowid = cursor.lastrowid cursor.close() connection.commit() result['status'] = 0 result['message'] = 'OK' result['data'] = [lastrowid] result['keys'] = ['updated id'] return result
def addNewSupplier(supplierName,adminID): cursor = connection.cursor() query = 'insert into Поставщик(`company_name`) values("{}");'.format(supplierName) cursor.execute(query) lastrowid = cursor.lastrowid cursor.close() Logger.log(adminID,query) connection.commit() result = {} result['keys'] = ['new id'] if(lastrowid > 0): result['data'] = [lastrowid] result['message'] = 'OK' result['status'] = 0 else: result['data'] = [] result['message'] = 'Not > 0' result['status'] = 125 return result
def setNewStatusOfOrder(newStatus,orderID,adminID): cursor = connection.cursor() query = 'update Заказ set status = {} where id = {};'.format(newStatus,orderID) Logger.log(adminID,query) cursor.execute(query) lastrowid = cursor.lastrowid cursor.close() if(newStatus == 4 or newStatus == 5): Admin.__unbookedBooksBy(orderID,adminID) result = {} result['keys'] = ['updated id'] if(lastrowid>0): result['status'] = 0 result['message'] = 'OK' result['data'] = [lastrowid,] else: result['status'] = 125 result['message'] = 'Not > 0' result['data'] = [] return result
def setNewValueBook(productID,column,value,adminID): result = {} cursor = connection.cursor() try: if(type(value) is int or type(value) is float): query = 'update Товар set {}={} where id = {};'.format(column,value,productID) else: query = 'update Товар set {}="{}" where id = {};'.format(column,value,productID) cursor.execute(query) Logger.log(adminID,query) except: result['status'] = 130 result['message'] = 'ADMIN: sql error because admin entered incorrect input data' return result lastrowid = cursor.lastrowid cursor.close() connection.commit() result['status'] = 0 result['message'] = 'OK' result['data'] = [lastrowid] result['keys'] = ['updated id'] return result
def insertNewBook(title,author,desc,cost_sale,cost_purchase,quantity,imageName,tags,adminID): cursor = connection.cursor() query = 'insert into Товар(`book_title`,`author`,`description`,`cost_sale`,`quantity`,`imageLink`,`tags`) \ values("{}","{}","{}",{},{},"{}","{}")'.format(title,author,desc,cost_sale,quantity,imageName,tags) cursor.execute(query) Logger.log(adminID,query) lastrowid = cursor.lastrowid query = 'insert into Закупки(`product_id`,`cost_purchase`,`quantity`) values({},{},{});'.format(lastrowid,cost_purchase,quantity) cursor.execute(query) connection.commit() cursor.close() result = {} result['keys'] = ['new id'] if(lastrowid>0): result['status'] = 0 result['message'] = 'OK' result['data'] = [lastrowid,] else: result['status'] = 125 result['message'] = 'Not > 0' result['data'] = [] return result
def is_admin_can_write(adminID): query = 'select * from Админ where user_id = {};'.format(adminID) cursor = connection.cursor() cursor.execute(query) row = cursor.fetchone() return row['level_of_access']> 0
def addNewDeliveryOfProducts(productID,quantity,cost_purhase,supplier_id,adminID): query = 'insert into Закупки(`quantity`,`cost_purchase`,`supplier_id`,`product_id`) values({},{},{},{})'.format(quantity,cost_purhase,supplier_id,productID) cursor = connection.cursor() cursor.execute(query) Logger.log(adminID,query) return Admin.incQuantityOfBook(productID,quantity,adminID)
def log(adminID,operation): dumpsOperation = json.dumps(operation,ensure_ascii = False) cursor = connection.cursor() cursor.execute('insert into логи(`admin_id`,`operation`) values({},"{}");'.format(adminID,dumpsOperation[1:len(dumpsOperation)-2])) cursor.close() connection.commit()