def getDetailsOfOrder(userID, orderID):
result = {}
if (not Order.checkIfUserHaveOrderWith(userID, orderID)):
result['status'] = 3
result['message'] = 'Why are you so curious?'
result['data'] = []
return result
if (not Order.checkIfOrderExists(orderID)):
result['status'] = 40
result['message'] = 'Not founded'
result['data'] = []
return result
data = SqlExecuter.getAllRowsPacked(
"select pr.id,pr.imageLink,pr.book_title,bk.count,pr.cost_sale*bk.count as 'total' from Забронированная_книга as bk inner join Товар as pr on bk.order_id = {} and pr.id = bk.product_id;"
.format(orderID))
for row in data:
row['imageLink'] = imageHelper.makeFullPathToImage(
row['imageLink'])
rowAddressPacked = SqlExecuter.getOneRowsPacked(
"select ord.id as 'orderID',addr.*,ord.date,ord.status,ord.total,concat(addr.district,' district,',addr.street,', ',addr.house) as 'address' from Заказ as ord inner join Адрес as addr \
where ord.address_id = addr.id and ord.id = {};".format(orderID))
result['address'] = rowAddressPacked
result['info'] = {
'orderID': rowAddressPacked['orderID'],
'date': rowAddressPacked['date'],
'status': rowAddressPacked['status'],
'total': rowAddressPacked['total']
}
result['status'] = 0
result['message'] = 'OK'
result['data'] = data
return result
def getCartOfUser(userID):
result = {}
try:
data = SqlExecuter.getAllRowsPacked(
'select concat(pr.book_title," - ",pr.author) as "title",pr.cost_sale as "cost",cart.count,cart.count*pr.cost_sale as "total",pr.id,pr.imageLink,pr.author from Товар as pr inner join Корзина as cart on pr.id = product_id and user_id = {};'
.format(userID))
except:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = []
return result
if (data is None or len(data) == 0):
result['status'] = 2
result['message'] = 'Empty cart'
result['data'] = []
return result
for row in data:
row['imageLink'] = imageHelper.makeFullPathToImage(
row['imageLink'])
result['data'] = data
result['count'] = len(data)
result['status'] = 0
result['message'] = 'OK'
result['totalCost'] = SqlExecuter.getOneRowsPacked(
"select SUM(cart.count*pr.cost_sale) as 'totalCost' from Товар as pr inner join Корзина as cart on pr.id = product_id and user_id = {};"
.format(userID))['totalCost']
return result
def getCountOfItemsInCart(userID):
result = {}
try:
row = SqlExecuter.getOneRowsPacked(
'select SUM(count) as "count" from Корзина where user_id = {};'
.format(userID))
if (row is None or row['count'] is None):
result['status'] = 2
result['message'] = 'Empty cart'
result['data'] = [0]
return result
# if(row['count'] is None):
# result['status'] = 2
# result['message'] = 'Empty cart'
# result['data'] = [0]
# return result
result['status'] = 0
result['message'] = 'OK'
result['data'] = [row['count']]
return result
except:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = [-1]
return result
def getUserMarkTo(self, productID):
row = SqlExecuter.getOneRowsPacked(
'select mark from Рейтинг where user_id = {} and product_id = {};'.
format(self.userID, productID))
if (row is None):
return None
else:
return float(row['mark'])
def cancelOrder(userID, orderID):
result = {}
if (not Order.checkIfUserHaveOrderWith(userID, orderID)):
result['status'] = 3
result['message'] = 'Why are you so curious?'
result['data'] = []
return result
if (not Order.checkIfOrderExists(orderID)):
result['status'] = 40
result['message'] = 'Not founded'
result['data'] = []
return result
query = 'update Заказ set status = 4 where id = {};'.format(orderID)
SqlExecuter.executeModif(query)
result['status'] = 0
result['message'] = 'OK'
result['data'] = []
return result
def validateUserAndReturnUserID(email, password):
row = SqlExecuter.getOneRowsPacked(
'select * from Покупатель where email \
= "{}" and password_hash = "{}"'.format(
email, md5helper.ecnrypt(password)))
if (row is None):
return -1
else:
return row['id'] # Возвращаем ID пользователя
def countTotalCostOfUser(userID):
row = SqlExecuter.getOneRowsPacked(
'select SUM(cart.count*pr.cost_sale) as "totalCost" \
from Товар as pr \
inner join Корзина as cart on pr.id = product_id \
and user_id = {};'.format(userID))
if (row is None or row['totalCost'] is None):
return 0.0
else:
return float(row['totalCost'])
def getAllProfuctsFilteredByQuery(query, page=-1, offset=-1):
result = {}
try:
allRows = SqlExecuter.getAllRowsPacked(
'select *,cost_sale as "cost",concat(book_title," - ",author) as "title" from Товар where book_title like "%{0}%" or author like "%{0}%" order by id DESC LIMIT {1} OFFSET {2};'
.format(query, offset, offset * (page - 1)))
except:
result['status'] = 1
result['message'] = "Runtime error while executing sql query"
result['data'] = []
return result
return Product.__preparePackedProducts(allRows)
def getInfo(value, column='id'):
result = {}
row = SqlExecuter.getOneRowsPacked(
'select * from Покупатель where {} = {};'.format(column, value))
if (row is None):
result['data'] = {}
result['status'] = 1
result['message'] = 'User not found'
return result
result['data'] = row
result['status'] = 0
result['message'] = 'OK'
return result
def addNewRate(userID, productID, mark):
result = {}
row = SqlExecuter.getOneRowsPacked(
'select * from Рейтинг where user_id = {} and product_id = {};'.
format(userID, productID))
if (row is not None):
result['status'] = 9
result['message'] = 'User already set mark'
result['data'] = []
return result
lastrowid = SqlExecuter.executeModif(
'insert into Рейтинг values({},{},{});'.format(
productID, userID, mark))
if (lastrowid != -1):
result['status'] = 0
result['message'] = 'OK'
result['data'] = lastrowid
else:
result['status'] = 1
result['message'] = 'SQL Runtime error'
result['data'] = []
return result
def getAllProductsFilteredByTags(tags, page, offset):
result = {}
try:
allRows = SqlExecuter.getAllRowsPacked(
sqlQueryHelper.buildSqlQueryByTagsAndPage(
'select *,cost_sale as "cost",concat(book_title," - ",author) as "title" from Товар',
tags, page, offset))
except:
result['status'] = 1
result['message'] = "Runtime error while executing sql query"
result['data'] = []
return result
return Product.__preparePackedProducts(allRows)
def removeItemInCartOfUser(userID, productID):
result = {}
try:
row = SqlExecuter.getOneRowsPacked(
'select * from Корзина where product_id = {} and user_id = {};'
.format(productID, userID))
except IndexError:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
if (row is not None):
if (row['count'] > 1):
try:
SqlExecuter.executeModif(
'update Корзина set count = count - 1 where \
product_id = {} and user_id = {};'.format(
productID, userID))
except IndexError:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
result['status'] = 0
result['message'] = 'OK'
return result
try:
SqlExecuter.executeModif('delete from Корзина where \
product_id = {} and user_id = {};'.format(
productID, userID))
except IndexError:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
result['status'] = 0
result['message'] = 'OK'
return result
result['status'] = 2
result['message'] = 'Not found item'
return result
def getQuantityOfProductInCart(userID, productID):
result = {}
row = SqlExecuter.getOneRowsPacked(
'select count from Корзина where user_id = {} and product_id = {};'
.format(userID, productID))
print("QUANTITY:" + str(row))
if (row is None):
result['status'] = 2
result['message'] = 'Product doesnt exist in cart'
result['data'] = {'count': 0}
return result
result['status'] = 0
result['message'] = 'OK'
result['data'] = {'count': row['count']}
return result
def registerUser(email, password, last_name, first_name, birthdate):
result = {}
row = SqlExecuter.getOneRowsPacked(
'select * from Покупатель where email="{}";'.format(email))
if (row is not None
): # Проверка,если пользователь с таким email существует
result['status'] = 8
result['message'] = 'User with same email already exists'
return result
if (not validationForm.validationEmail(email)
): # Если неправильно введен email
result['status'] = 7
result['message'] = 'Incorrect email'
return result
lastrowid = SqlExecuter.executeModif(
'insert into Покупатель(`email`,`last_name`,`first_name`,`birthdate`,`password_hash`) \
values("{}","{}","{}","{}","{}")'.format(
email, last_name, first_name, birthdate,
md5helper.ecnrypt(password)))
result["status"] = 0
result["message"] = "OK"
result["userID"] = lastrowid
return result
def addUser(data):
result = {}
try:
last_name = data['last_name']
first_name = data['first_name']
password = data['password']
birthdate = data['birthdate']
email = data['email']
except KeyError:
result['status'] = 6
result['message'] = 'Required field is missing'
return result
try:
SqlExecuter.executeModif(
'insert into Покупатель values("{}","{}",NULL,"{}","{}","{}");'
.format(last_name, first_name, email, birthdate,
md5helper.ecnrypt(password)))
except Exception:
result['status'] = 1
result['message'] = 'SQL runtime error'
return result
result['status'] = 0
result['message'] = 'OK'
return result
def getOrdersOfUser(userID):
result = {}
allRows = SqlExecuter.getAllRowsPacked(
'select ord.*,addr.district,addr.street,addr.flat,addr.floor,\
addr.porch,addr.house,address_id as "address" \
from Заказ as ord inner join Адрес as addr\
where ord.address_id = addr.id and user_id = {} order by id DESC;'.
format(userID))
if (allRows is None or len(allRows) == 0):
result['status'] = 2
result['message'] = 'Empty response'
result['data'] = []
return result
result['status'] = 0
result['message'] = 'OK'
result['data'] = allRows
return result
def getAvailableTags():
result = {}
allRows = SqlExecuter.getAllRowsPacked('select tags from Товар;')
if (allRows is None or len(allRows) == 0):
result['status'] = 2
result['message'] = 'Empty data'
result['data'] = []
return result
tagsArrUNIQUE = []
for row in allRows:
tags = row['tags']
tagsArr = tagsHelper.makeTagsStrToArray(tags)
tagsArrUNIQUE = list(set(tagsArrUNIQUE + tagsArr))
result['status'] = 0
result['message'] = 'OK'
result['data'] = tagsArrUNIQUE
return result
def addItemInCartOfUser(userID, productID):
result = {}
print(productID)
row = SqlExecuter.getOneRowsPacked(
'select * from Товар where id = {};'.format(productID))
if (row is None):
result['status'] = 2
result['message'] = 'No such product'
return result
if (row['quantity'] == 0):
result['status'] = 4
result['message'] = 'Zero quantity of called product'
return result
quantityOfProduct = row['quantity']
try:
row = SqlExecuter.getOneRowsPacked(
'select * from Корзина where product_id = {} and user_id = {};'
.format(productID, userID))
except:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
if (row is not None):
if (row['count'] >= quantityOfProduct):
result['status'] = 5
result['message'] = 'Not enough products'
return result
try:
SqlExecuter.executeModif(
'update Корзина set count = count + 1 \
where product_id = {} and user_id = {}'.format(
productID, userID))
except:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
result['status'] = 0
result['message'] = 'Inc count'
return result
try:
SqlExecuter.executeModif(
'insert into Корзина values({},{},1)'.format(
userID, productID))
except:
result['status'] = 1
result['message'] = 'SQL Runtime error'
return result
result['status'] = 0
result['message'] = 'Add new values'
return result
def getQuantityOfRowsInTable(query):
result = {}
try:
row = SqlExecuter.getOneRowsPacked(query)
except:
result['status'] = 1
result['message'] = "Runtime error while executing sql query"
result['data'] = []
return result
if (row is not None):
result['status'] = 0
result['message'] = 'OK'
result['count'] = row['count']
else:
result['status'] = 2
result['message'] = 'Not founded'
result['count'] = 0
return result
def getDetailsOfProduct(productID):
result = {}
try:
row = SqlExecuter.getOneRowsPacked(
'select *,cost_sale as "cost",concat(book_title," - ",author) as "title",round(avg(rate.mark),2) as "rate" from Товар inner join Рейтинг as rate where id = {};'
.format(productID))
except:
result['status'] = 1
result['message'] = 'SQL Runtime error'
result['data'] = []
return result
if (row is None):
result['status'] = 2
result['message'] = 'Empty data'
result['data'] = []
return result
else:
row['imageLink'] = imageHelper.makeFullPathToImage(
row['imageLink'])
result['status'] = 0
result['message'] = 'OK'
result['data'] = [row]
return result
def addNewOrder(userID,
district,
flat,
house,
floor,
street,
porch='',
email='',
desc='',
phone=''):
result = {}
try:
quantityInCart = Cart.getCountOfItemsInCart(userID)
if (quantityInCart['status'] == 2):
result['status'] = 2
result['message'] = 'Empty cart'
result['data'] = []
return result
except IndexError:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = []
return result
cartOfUser = Cart.getCartOfUser(userID)['data']
isError = False
for row in cartOfUser:
product = SqlExecuter.getOneRowsPacked(
'select * from Товар where id = {};'.format(row['id']))
if (product['quantity'] < row['count']):
isError = True
SqlExecuter.executeModif(
'delete from Корзина where product_id = {} and user_id = {};'
.format(row['id'], userID))
if (isError):
result['status'] = 10
result[
'message'] = 'Attempt to order product with incorrect quantity'
return result
try:
lastrowidAddress = Address.addNewAddress(district, house, floor,
flat, porch, street)
except IndexError:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = []
return result
try:
lastrowid = SqlExecuter.executeModif(
'insert into Заказ(`user_id`,`status`,`total`,`address_id`,`description`,`email`,`phone`) values({},{},{},{},"{}","{}","{}");'
.format(userID, 0, Cart.countTotalCostOfUser(userID),
lastrowidAddress, desc, email, phone))
except IndexError:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = []
return result
data = {'id': lastrowid, "data": cartOfUser}
for row in cartOfUser:
try:
SqlExecuter.executeModif('insert into Забронированная_книга \
values({},{},{},{})'.format(row['id'], row['count'],
lastrowid, row['cost']))
SqlExecuter.executeModif('delete from Корзина where \
user_id = {} and product_id = {};'.format(
userID, row['id']))
SqlExecuter.executeModif(
'update Товар set quantity = quantity - {} where id = {};'.
format(row['count'], row['id']))
except IndexError:
result['status'] = 1
result['message'] = 'SQL runtime error'
result['data'] = []
return result
result['status'] = 0
result['message'] = 'OK'
result['data'] = data
# if(email is not None):
# emailSender.EmailSender.sendEmailTo([email],orderDetails=result['data'])
return result
def validateUser(email, password):
row = SqlExecuter.getOneRowsPacked(
'select * from Покупатель where email \
= "{}" and password_hash = "{}"'.format(
email, md5helper.ecnrypt(password)))
return row is not None
def checkIfOrderExists(orderID):
return SqlExecuter.getOneRowsPacked(
'select * from Заказ where id = {} and status != 4 and status != 5;'
.format(orderID)) is not None
def checkIfUserHaveOrderWith(userID, orderID):
return SqlExecuter.getOneRowsPacked(
'select * from Заказ where user_id = {} and id = {};'.format(
userID, orderID)) is not None
def addNewAddress(district, house, floor, flat, porch, street):
lastrowid = SqlExecuter.executeModif(
'insert into Адрес(`district`,`house`,`floor`,`flat`,`porch`,`street`) values("{}","{}","{}","{}","{}","{}");'
.format(district, house, floor, flat, porch, street))
return lastrowid
def getRateOfProduct(productID):
row = SqlExecuter.getOneRowsPacked(
'select round(coalesce(avg(mark),0),2) as "count" from Рейтинг where product_id = {};'
.format(productID))
return float(row['count'])