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