def searchItem(self,keywords):
"""
:param keywords: search keyword
:return:
- False if not found, add to Notification DB
- list of item, if found
"""
qry = ("INSERT INTO searchKeyword(keyword) VALUES ('%s') ON DUPLICATE KEY UPDATE frequency =frequency+1;" % (keywords))
self.cursor.execute(qry)
self.cnx.commit()
# qry = ("SELECT itemID FROM ItemInfo WHERE (title LIKE '%s' OR description LIKE '%s') AND saleStatus = True;"
# % (('%'+keywords+'%','%'+keywords+'%')))
qry = ("SELECT itemID FROM ItemInfo WHERE title LIKE '%s' AND saleStatus = True;"
% ('%' + keywords + '%'))
self.cursor.execute(qry)
allItem = []
allItems = self.cursor.fetchall()
if not allItems:
self.cursor.execute("SELECT EXISTS(SELECT * FROM Notification WHERE keyword = '%s')"% keywords.lower())
if not self.cursor.fetchone()[0]:
self.cursor.execute("INSERT INTO Notification VALUE ('%s');"% keywords.lower())
self.cnx.commit()
return False
else:
for info in allItems:
allItem.append(Item(cnx=self.cnx,cursor=self.cursor,itemID=info[0]))
return allItem
def initInfo(self, index, refresh=False):
if refresh:
item = Item(itemID=self.itemID,
cursor=globalV.cursor,
cnx=globalV.cnx)
else:
item = globalV.itemList[index]
item.addView()
self.purchased = False
self.itemID = item.itemID
self.itemIndex = index
self.price = item.price
self.numberAva = item.available
self.user = not globalV.root.login
self.ids['itemImage'].texture = item.image
self.ids['itemTitle'].text = item.title
self.ids['itemDescription'].text = item.descrpition
self.ids['itemRates'].text = str(
item.rating) if item.rating else "None"
self.ids['itemPrice'].text = "$" + str(item.price)
self.ids['itemAvailable'].text = str(item.available)
self.ids['itemLike'].text = str(item.likeness)
self.ids['itemDislike'].text = str(item.dislike)
self.ids['itemRate'].data = item.getRating()
self.disableAction = globalV.root.checkDisable(item.itemID)
def getAllItem(self):
qry = "SELECT itemID FROM ItemOwner NATURAL JOIN ItemInfo;"
self.cursor.execute(qry)
self.items = []
allitem = self.cursor.fetchall()
for item in allitem:
self.items.append(
Item(cnx=self.cnx, cursor=self.cursor, itemID=item[0]))
return self.items
def popularItem(self):
"""
:return: list of Item()
"""
qry = "SELECT itemID FROM ItemInfo WHERE saleStatus = True;"
self.cursor.execute(qry)
allItem = []
allItems = self.cursor.fetchall()
for info in allItems:
allItem.append(Item(cnx=self.cnx,cursor=self.cursor,itemID=info[0]))
return allItem
def ouPopularItem(self, ouID):
# qry = ("SELECT itemID FROM ItemInfo NATURAL JOIN ItemView LEFT JOIN OUlike ON title LIKE CONCAT('%', keyword ,'%') AND ouID = %s WHERE saleStatus = TRUE ORDER BY OUlike.frequency DESC, ItemView.frequency DESC;")% ouID
qry = ( "SELECT itemID FROM ItemInfo NATURAL JOIN ItemView LEFT JOIN OUlike ON title LIKE CONCAT('%', keyword ,'%')")
qry2 = " AND ouID = %s WHERE saleStatus = TRUE ORDER BY OUlike.frequency DESC, ItemView.frequency DESC;" % ouID
qry += qry2
self.cursor.execute(qry)
allItem = []
allItems = self.cursor.fetchall()
for info in allItems:
allItem.append(Item(cnx=self.cnx,cursor=self.cursor,itemID=info[0]))
return allItem
def getItem(self):
''' Get items that own by current OU, in list of Item class '''
qry = "SELECT itemID FROM ItemOwner NATURAL JOIN ItemInfo WHERE ownerID = %s;" % self.ID
self.cursor.execute(qry)
self.items = []
allitem = self.cursor.fetchall()
for item in allitem:
self.items.append(
Item(cnx=self.cnx, cursor=self.cursor, itemID=item[0]))
return self.items
def getAllItem(self):
'''
:return: initial self.items
self.items -> list of Item object
'''
qry = "SELECT itemID FROM ItemOwner NATURAL JOIN ItemInfo;"
self.cursor.execute(qry)
self.items = []
allitem = self.cursor.fetchall()
for item in allitem:
self.items.append(
Item(cnx=self.cnx, cursor=self.cursor, itemID=item[0]))
return self.items
def getItem(self):
'''
:return: self.items
self.items - list of item object owned by current OU object
'''
qry = "SELECT itemID FROM ItemOwner NATURAL JOIN ItemInfo WHERE ownerID = %s;" % self.ID
self.cursor.execute(qry)
self.items = []
allitem = self.cursor.fetchall()
for item in allitem:
self.items.append(Item(cnx=self.cnx, cursor=self.cursor,itemID=item[0]))
return self.items
def removeItem(self, itemID, justification='removed By SU'):
# remove all the occurance of this item in DB
# add to Blacklist and warning to owner
item = Item(self.cnx, self.cursor, itemID)
# add warning
self.cursor.execute(
"INSERT INTO Warning(ouID,warningID,description) VALUE (%s,3,'%s')"
% (item.owner, str(itemID) + '_' + justification))
# Blacklist Item
self.cursor.execute(
"INSERT INTO itemBlackList(itemID,title) VALUE (%s,'%s');" %
(item.itemID, item.title))
# Delete Item
self.cursor.execute("DELETE FROM ItemInfo WHERE itemID = %s;" % itemID)
self.cnx.commit()
def purchaseBidding(self, itemID):
'''
:param itemID: bidding item id
:return:
assign second highest bidder to purchase the item, update transaction
delete all bid recod for this item
'''
item = Item(cnx=self.cnx,cursor=self.cursor,itemID=itemID)
finalPrice = self.calculatePurchase(itemID=itemID, price=item.secondPrice)[-1]
qry = ("INSERT INTO Transaction(itemID, buyerID, singlePrice, priceTotal) VALUES (%s,%s,%s,%s);"
%(itemID,item.secondBidder,item.secondPrice,finalPrice))
self.cursor.execute(qry)
self.cursor.execute("DELETE FROM BidRecord WHERE itemID = %s;"% itemID)
self.cnx.commit()
def popularItem(self):
"""
:return: list of Item object order by popular search keyword and view frequency
"""
qry =("SELECT itemID FROM ItemInfo NATURAL JOIN ItemView "
"LEFT JOIN searchKeyword ON title LIKE CONCAT('%', keyword ,'%') "
"WHERE saleStatus = TRUE ORDER BY searchKeyword.frequency DESC, ItemView.frequency DESC;")
self.cursor.execute(qry)
itemIds =[]
allItem = []
allItems = self.cursor.fetchall()
for info in allItems:
if info[0] not in itemIds:
allItem.append(Item(cnx=self.cnx,cursor=self.cursor,itemID=info[0]))
itemIds.append(info[0])
return allItem
def removeItem(self, itemID, justification='item removed By SU'):
'''
:param itemID: item to be remove
:param justification: note from su reason, default: 'item removed By SU'
:return:
remove all the occurance of this item in DB
add to Blacklist and warning to owner
'''
item = Item(self.cnx, self.cursor, itemID)
# add warning
self.cursor.execute(
"INSERT INTO Warning(ouID,warningID,description) VALUE (%s,3,'%s')"
% (item.owner, str(itemID) + '_' + justification))
# Blacklist Item
self.cursor.execute(
"INSERT INTO itemBlackList(itemID,title) VALUE (%s,'%s');" %
(item.itemID, item.title))
# Delete Item
self.cursor.execute("DELETE FROM ItemInfo WHERE itemID = %s;" % itemID)
self.cnx.commit()
