def POST(self):
post_params = web.input()
logger.debug('select_time.POST just got an input')
MM = post_params['MM']
dd = post_params['dd']
yyyy = post_params['yyyy']
HH = post_params['HH']
mm = post_params['mm']
ss = post_params['ss']
enter_name = post_params['entername']
selected_time = '%s-%s-%s %s:%s:%s' % (yyyy, MM, dd, HH, mm, ss)
update_message = '(Hello, %s. Previously selected time was: %s.)' % (
enter_name, selected_time)
# save the selected time as the current time in the database
t = sqlitedb.transaction()
try:
query_string = "update CurrentTime set the_time = $selected_time"
sqlitedb.query(query_string, {'selected_time': selected_time})
except Exception as e:
t.rollback()
msg = 'tried to update time to ' + selected_time + '\nERROR: ' + str(
e)
return render_template('select_time.html', message=msg)
else:
t.commit()
return render_template('select_time.html', message=update_message)
def GET(self):
itemID = web.input(id='web')
details = web.websafe(itemID.id)
q = "select * from Item where itemID = %s" % (details)
r = "select name from Category where itemID = %s" % (details)
s = "select userID, amount, currtime from Bid where itemID = %s ORDER BY Bid.amount DESC" % (
details)
Q = sqlitedb.query(q)
R = sqlitedb.query(r)
S = sqlitedb.query(s)
current_time = sqlitedb.getTime()
item_row = sqlitedb.getItemById(details)
winner = False
if (string_to_time(item_row.ends) <= string_to_time(current_time)):
#winner = sqlitedb.getWinnerId(details)
winner = True
return render_template('bid_details.html',
Item=Q,
Cat=R,
Bid=S,
Win=winner)
def POST(self):
current_time = sqlitedb.getTime()
post_params = web.input()
itemID = post_params['itemID']
userID = post_params['userID']
price = float(post_params['price'])
if sqlitedb.getUser(userID):
if sqlitedb.getItemById(itemID) is None:
result = False
update_message = 'Item is invalid'
return render_template('add_bid.html',
add_result=result,
message=update_message)
else:
if sqlitedb.bidisOpen(itemID):
t = sqlitedb.transaction()
query_string = 'INSERT INTO Bids (itemID, UserID, Amount, Time) VALUES ($itemID, $userid, $price, $time) '
try:
if user_id == '' or item_id == '' or amount == '':
return render_template('add_bid.html',
message='empty fields')
sqlitedb.query(
query_string, {
'itemID': itemID,
'userid': userID,
'price': price,
'time': current_time
})
except Exception as e:
t.rollback()
print str(e)
update_message = 'An error has occurred'
result = False
return render_template('add_bid.html',
add_result=result,
message=update_message)
else:
t.commit()
update_message = 'Add bid successfully!'
result = True
return render_template('add_bid.html',
add_result=result,
message=update_message)
else:
update_message = 'Bid is closed'
result = False
return render_template('add_bid.html',
add_result=result,
message=update_message)
else:
result = False
update_message = 'User is invalid'
return render_template('add_bid.html',
add_result=result,
message=update_message)
def GET(self):
itemID = web.input(id='web')
details = web.websafe(itemID.id)
q = "select * from Items where ItemId = %s" % (details)
r = "select Category from Categories where ItemId = %s" % (details)
s = "select UserId, Amount, BidTime from Bids where ItemId = %s" % (
details)
Q = sqlitedb.query(q)
R = sqlitedb.query(r)
S = sqlitedb.query(s)
return render_template('item_details.html', Item=Q, Cat=R, Bid=S)
def GET(self):
data = web.input()
itemID = int(data.itemID)
# return render_template('item.html', itemID = itemID
item = sqlitedb.query(
'select distinct * from Items where ItemID = $ID', {'ID': itemID})
cate = sqlitedb.query(
'select Category from Categories where ItemID = $ID',
{'ID': itemID})
start = sqlitedb.query(
'select distinct Started from Items where ItemID = $ID',
{'ID': itemID})
end = sqlitedb.query(
'select distinct Ends from Items where ItemID = $ID',
{'ID': itemID})
buyprice = sqlitedb.query(
'select distinct Buy_Price from Items where ItemID = $ID',
{'ID': itemID})
cur = sqlitedb.query('select Currently from Items where ItemID = $ID',
{'ID': itemID})
count = 0
cur_time = string_to_time(sqlitedb.getTime())
start = string_to_time(start[0]['Started'])
end = string_to_time(end[0]['Ends'])
print "****"
print cur_time
print start
print end
print buyprice
if (cur_time > start and cur_time > end):
status = "CLOSE"
if (cur_time > start and cur_time < end):
status = "OPEN"
if (cur_time < start and cur_time < end):
status = "NOT START"
if (buyprice[0]['Buy_Price'] != None):
if (cur >= buyprice):
status = "CLOSE"
print status
print "****"
winner = ""
auction = sqlitedb.query(
'select UserID, Amount, Time from Bids where ItemID = $ID',
{'ID': itemID})
if (status == "CLOSE"):
winner = sqlitedb.query(
'select UserID from Bids where ItemID = $ID order by Amount DESC LIMIT 1',
{'ID': itemID})
print winner
if (winner):
winner = (winner[0]['UserID'])
return render_template('item.html',
item_result=item,
category_result=cate,
status_result=status,
auction_result=auction,
winner_result=winner)
def POST(self):
current_time = sqlitedb.getTime()
post_params = web.input()
itemID = post_params['itemID']
userID = post_params['userID']
price = float(post_params['price'])
# if the user is valid
if sqlitedb.getUserByUserId(userID):
# if the item is valid
if sqlitedb.getItemById(itemID) is not None:
# if the bid is active
if sqlitedb.isBidActive(itemID):
t = sqlitedb.transaction()
query_string = 'INSERT INTO Bids (itemID, UserID, Amount, Time) VALUES ($itemID, $userID, $price, $time) '
try:
if itemID == '':
return render_template('add_bid.html', message = 'no itemID')
if userID == '':
return render_template('add_bid.html', message = 'no userID')
if price == '':
return render_template('add_bid.html', message = 'no amount')
sqlitedb.query(query_string, {'itemID': itemID, 'userid': userID, 'price': price, 'time': current_time})
except Exception as exception:
t.rollback()
print str(exception)
result = False
update_message = 'Error when add a bid'
return render_template('add_bid.html', add_result = result, message = update_message)
else:
t.commit()
result = True
update_message = 'Successfully add a bid'
return render_template('add_bid.html', add_result = result, message = update_message)
# if the bid is not active
else:
result = False
update_message = 'The bid is closed now'
return render_template('add_bid.html', add_result = result, message = update_message)
# if the item can't be found
else:
result = False
update_message = 'Cannot get the item with item id'
return render_template('add_bid.html', add_result = result, message = update_message)
# if the user can't be found
else:
result = False
update_message = 'Cannot get the user with user id'
return render_template('add_bid.html', add_result = result, message = update_message)
def GET(self):
post_params = web.input()
itemID = dict(post_params)
#pprint.pprint(itemID)
itemID = int(itemID["itemID"])
#print("itemID: " + str(itemID))
values = {
'itemID': itemID
}
items_query = "SELECT * FROM Items WHERE ItemID = $itemID"
itemsResult = sqlitedb.query(items_query, values)
itemsResult = itemsResult[0]
categories_query = "SELECT Category FROM Categories WHERE ItemID = $itemID"
categoryResult = sqlitedb.query(categories_query, values)
bids_query = "SELECT UserID, Amount, Time FROM BIDS WHERE ItemID = $itemID"
bidsResult = sqlitedb.query(bids_query, values)
if len(categoryResult) != 0:
itemsResult['Category'] = categoryResult
if len(bidsResult) != 0:
itemsResult['Bids'] = bidsResult
started = string_to_time(str(itemsResult['Started']))
currtime = string_to_time(sqlitedb.getTime())
#print("started: " + str(started))
#print("ends: " + str(currtime))
status = dict()
if started > currtime:
status["Status"] = "Not Started"
else:
retObj = isAuctionClosedForItem(itemID)
if retObj["error"]:
status["Status"] = "Closed"
status["Winner"] = self.findWinner(bidsResult)
else:
status["Status"] = "Open"
itemsResult["Status"] = status
#pprint.pprint(categoryResult)
return render_template('item_details.html', details = itemsResult)
def POST(self):
post_params = web.input()
category = post_params['category']
description = post_params['description']
itemID = post_params['itemID']
minPrice = post_params['minPrice']
maxPrice = post_params['maxPrice']
status = post_params['status']
#print("category: " + str(category) + ", description: " + description + ", itemID: " + str(itemID) + ", minPrice: " + str(minPrice) + ", maxPrice: " + str(maxPrice) + ", status: " + str(status))
inputResults = self.validateInputs(category, description, itemID, minPrice, maxPrice, status)
if inputResults["error"]:
message = "Error: " + inputResults["msg"]
return render_template('search.html', message = message)
query = self.constructQuery(category, description, itemID, minPrice, maxPrice, status)
result = sqlitedb.query(query["query_string"], query["values"])
#pprint.pprint(result)
#print("result len is " + str(len(result)))
#print("Query is")
#pprint.pprint(query)
#pprint.pprint(inputResults)
return render_template('search.html', search_result = result)
def isAuctionClosedForItem(itemID):
#print("Inside isAuctionClosedForItem")
query_string1 = 'SELECT Started, ends, Buy_Price, Currently FROM ITEMS WHERE ItemID = $itemID'
query_string2 = 'SELECT COUNT(*) FROM BIDS WHERE ItemID = $itemID'
values = {
'itemID': int(itemID)
}
result = sqlitedb.query(query_string1, values)
count = sqlitedb.query(query_string2, values)
count = count[0]["COUNT(*)"]
#print("count: " + str(count))
#print("result length is " + str(len(result)))
started = string_to_time(result[0].Started)
ends = string_to_time(result[0].Ends)
buy_price = result[0].Buy_Price
currently = result[0].Currently
currTime = string_to_time(sqlitedb.getTime())
#print("currTime: " + str(currTime) + ", startTime: " + str(started) + ", endTime: " + str(ends))
#print("buy_price: " + str(buy_price) + ", currently: " + str(currently))
if started > currTime:
return createReturnObject(True, "Cannot bid on an item whose auction has not started yet")
if ends < currTime:
return createReturnObject(True, "Cannot bid on an item whose auction has closed")
# cannot check the condition if buy_price is reached or not if buy_price is None
if buy_price is not None:
# if buy_price is not null need to check if any bids for the item exists
# if not - then auction cannot be closed
# if yes need to check if buy_price is met
# compare buy_price and currently here
buy_price = float(buy_price)
currently = float(currently)
if currently >= buy_price and count != 0:
return createReturnObject(True, "This item is removed from bidding because buy_price is already been met")
return createReturnObject(False, "Item is open for bidding")
def POST(self):
current_time = sqlitedb.getTime()
post_params = web.input()
itemID = post_params['itemID']
userID = post_params['userID']
price = post_params['price']
if sqlitedb.isUserValid(userID):
try:
if sqlitedb.isBidAlive(itemID):
# Add bid into db
t = sqlitedb.transaction()
try:
#sqlitedb.update('update CurrentTime set time = $updated_time', {'updated_time': selected_time})
sqlitedb.query(
'INSERT INTO Bids (itemID, UserID, Amount, Time) VALUES ($itemID, $userid, $price, $time) ',
{
'itemID': itemID,
'userid': userID,
'price': price,
'time': current_time
}, True)
except Exception as e:
t.rollback()
print str(e)
update_message = 'An error occur'
result = False
else:
t.commit()
update_message = 'Success!'
result = True
else:
update_message = 'Auction on this item is closed'
result = False
except Exception as e:
# item not exist
print(e)
result = False
update_message = 'Item not exist'
else:
result = False
update_message = 'User is not exist'
return render_template('add_bid.html',
add_result=result,
message=update_message)
def GET(self, itemid=None):
if not itemid:
get_params = web.input(itemID=None)
itemid = get_params.itemID
if itemid and itemid != '':
itemid = int(itemid)
qd1 = {}
q1 = """
SELECT *
FROM Items i, Auctions a
WHERE i.item_id = a.item_id
AND i.item_id = $item_id
"""
qd1['item_id'] = itemid
result = sqlitedb.query(q1, qd1)
current_time = string_to_time(sqlitedb.getTime())
start_time = string_to_time(result[0].started)
end_time = string_to_time(result[0].ends)
if start_time <= current_time and end_time > current_time:
status = 'open'
elif start_time <= current_time and end_time <= current_time:
status = 'closed'
else:
status = 'not yet started'
q2 = """
SELECT category_name
FROM Categories
WHERE item_id = $item_id
"""
categories = sqlitedb.query(q2, qd1)
q3 = """
SELECT * from Bids
WHERE item_id = $item_id
ORDER BY time DESC
"""
bids = sqlitedb.query(q3, qd1)
else:
result = None
status = None
categories = None
bids = None
return render_template('view.html',
result=result,
status=status,
categories=categories,
bids=bids)
def POST(self):
current_time = sqlitedb.getTime()
post_params = web.input()
userID = post_params['userID']
price = post_params['price']
itemID = post_params['itemID']
if sqlitedb.checkUser(userID):
try:
if sqlitedb.checkBid(itemID):
t = sqlitedb.transaction()
try:
sqlitedb.query(
'INSERT INTO Bids (itemID, UserID, Amount, Time) VALUES ($itemID, $userid, $price, $time) ',
{
'itemID': itemID,
'userid': userID,
'price': price,
'time': current_time
}, True)
except Exception as e:
t.rollback()
print str(e)
update_message = 'Failed'
result = False
else:
t.commit()
update_message = 'Success'
result = True
else:
update_message = 'Closed item'
result = False
except Exception as e:
print(e)
update_message = 'Item does not exist'
result = False
else:
update_message = 'User does not exist'
result = False
return render_template('add_bid.html',
add_result=result,
message=update_message)
def GET(self):
get_params = web.input(query=None,
results=None,
page=None,
query_dict=None)
query = get_params.query
query_dict = literal_eval(get_params.query_dict)
results = get_params.results
page = int(get_params.page)
logging.debug(str(query) + ' ' + str(results))
if page == 1:
results = sqlitedb.query(query, query_dict)
else:
offset = (page - 1) * 50
query_dict['offset'] = offset
results = sqlitedb.query(query + '\n OFFSET $offset', query_dict)
return render_template('results.html',
query=query,
query_dict=query_dict,
results=results,
page=page)
def POST(self):
post_params = web.input()
item_id = int(post_params['itemID'])
user_id = post_params['userID']
price = float(post_params['price'])
current_time = sqlitedb.getTime()
test = "select * from Bids where (userID = $user_id) and (itemID = $item_id)"
message = "A bid of $%s has been placed by %s for item %s" % (price,user_id,item_id)
add_result = ""
t = sqlitedb.transaction()
try:
started = sqlitedb.query("select Started from Items where ItemID = $item_id", {'item_id': item_id})
ends = sqlitedb.query("select Ends from Items where ItemID = $item_id", {'item_id': item_id})
buyPrice = sqlitedb.query("select Buy_Price from Items where ItemID = $item_id", {'item_id': item_id})[0].Buy_Price
s = sqlitedb.query("select * from Bids where (ItemID = $item_id) and (Amount >= $buyPrice)",{'item_id': item_id, 'buyPrice': buyPrice})
if (sqlitedb.isResultEmpty(s)):
add_result = "done"
sqlitedb.query("insert into Bids values ($user_id,$currTime,$price,$item_id)",
{'user_id':user_id,'currTime':current_time, 'price':price,'item_id':item_id, 'started': started[0].Started, 'ends': ends[0].Ends})
else:
message = "INSERT FAILED!"
add_result = ""
except Exception as e:
message = str(e)
add_result = ""
else:
t.commit()
return render_template('add_bid.html',message = message, add_result = add_result)
def POST(self):
post_params = web.input()
MM = post_params['MM']
dd = post_params['dd']
yyyy = post_params['yyyy']
HH = post_params['HH']
mm = post_params['mm']
ss = post_params['ss'];
enter_name = post_params['entername']
selected_time = '%s-%s-%s %s:%s:%s' % (yyyy, MM, dd, HH, mm, ss)
update_message = '(Hello, %s. Previously selected time was: %s.)' % (enter_name, selected_time)
t = sqlitedb.transaction()
try:
sqlitedb.query('update CurrentTime set currTime = $time', {'time': selected_time})
except Exception as e:
update_message = str(e)
t.rollback()
else:
t.commit()
# Here, we assign `update_message' to `message', which means
# we'll refer to it in our template as `message'
return render_template('select_time.html', message = update_message)
def isCategoryNotPresent(category):
retObj = None
query_string = 'SELECT * FROM Categories WHERE UPPER(Category) = UPPER($category) LIMIT 1'
values = {
'category': category
}
result = sqlitedb.query(query_string, values)
if len(result) == 0:
retObj = createReturnObject(True, "Category does not exists in the database")
else:
retObj = createReturnObject(False, "Category exists in the database")
return retObj
def isUserNotPresent(userID):
retObj = None
query_string = 'SELECT * FROM USERS WHERE userID = $userID'
values = {
'userID': userID
}
result = sqlitedb.query(query_string, values)
if len(result) == 0:
retObj = createReturnObject(True, "UserID: " + str(userID) + " does not exists in the database")
else:
retObj = createReturnObject(False, "User exists in the database")
return retObj
def isItemNotPresent(itemID):
retObj = None
query_string = 'SELECT * FROM ITEMS WHERE ITEMID = $itemID'
values = {
'itemID': int(itemID)
}
result = sqlitedb.query(query_string, values)
if len(result) == 0:
retObj = createReturnObject(True, "ItemID: " + str(itemID) + " does not exists in the database")
else:
retObj = createReturnObject(False, "Item exists in the database")
return retObj
def isDescriptionNotPresent(description):
retObj = None
query_string = 'SELECT * FROM Items WHERE UPPER(Description) LIKE UPPER($description) LIMIT 1'
values = {
'description': "%" + description + "%"
}
result = sqlitedb.query(query_string, values)
if len(result) == 0:
retObj = createReturnObject(True, "Description substring does not exists in the database")
else:
retObj = createReturnObject(False, "Description substring exists in the database")
return retObj
def POST(self):
#transaction and test
t = sqlitedb.transaction()
try:
post_params = web.input()
item_id = int(post_params['itemID'])
user_id = post_params['userID']
price = float(post_params['price'])
current_time = sqlitedb.getTime()
started = sqlitedb.query(
"select Started from Items where ItemID = $item_id",
{'item_id': item_id})
ends = sqlitedb.query(
"select Ends from Items where ItemID = $item_id",
{'item_id': item_id})
buyPrice = sqlitedb.query(
"select Buy_Price from Items where ItemID = $item_id",
{'item_id': item_id})[0].Buy_Price
#current_price = sqlitedb.query("select Currently from Items where ItemID = $item_id", {'item_id': item_id})[0].Currently
if (buyPrice != None):
closed = sqlitedb.query(
"select * from Bids where (ItemID = $item_id) and (Amount >= $buyPrice)",
{
'item_id': item_id,
'buyPrice': buyPrice
})
else:
closed = None
if (closed is None):
#add bid to the database
sqlitedb.updateTime(
"insert into Bids values ($item_id, $user_id, $price, $currTime)",
{
'item_id': item_id,
'user_id': user_id,
'price': price,
'currTime': current_time
})
sqlitedb.updateTime(
"update Items set Currently = $currently where ItemID = $item_id",
{
'currently': price,
'item_id': item_id
})
add_result = "done"
update_message = 'Hello, %s. Previously added a bid for %d. of price $%d.' % (
user_id, item_id, price)
else:
update_message = "Failed. Try again."
add_result = ""
except Exception as e:
t.rollback()
print str(e)
add_result = ""
update_message = "Failed. Try again."
else:
t.commit()
return render_template('add_bid.html',
message=update_message,
add_result=add_result)
def POST(self):
post_params = web.input()
itemID = post_params['itemID']
userID = post_params['userID']
itemCategory = post_params['itemCategory']
minPrice = post_params['minPrice']
maxPrice = post_params['maxPrice']
status = post_params['status']
itemDescription = post_params['itemDescription']
userInfo = {
'itemID': itemID,
'itemCategory': itemCategory,
'itemDescription': itemDescription,
'userID': userID,
'minPrice': minPrice,
'maxPrice': maxPrice,
'status': status
}
searchinfo = {}
searchlen = {'select': ['distinct', '*'], 'from': [], 'where': []}
searchstring = ""
for key, value in userInfo.items():
if value != "":
if "itemID" == key:
if not sqlitedb.checkItem(value):
error_message = 'Following Item ID does not exist:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append('Items.itemID = $itemID')
elif "userID" == key:
if not sqlitedb.checkUser(value):
error_message = 'Following User ID does not exist:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append(
'exists(select Users.userID from Users where Users.userID = $userID and Items.Seller_UserID==Users.userID)'
)
elif "itemCategory" == key:
if not sqlitedb.checkCategory(value):
error_message = 'Following Item Category does not exist :' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append(
'exists(select Categories.itemID from Categories where Categories.category = $itemCategory and Categories.itemID = Items.itemID)'
)
elif "itemDescription" == key:
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append(
'Items.Description like $itemDescription')
value = '%' + value + '%'
elif "minPrice" == key:
if not sqlitedb.checkMin(value):
error_message = 'Following price does not exist:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append('Items.Currently >= $minPrice')
elif "maxPrice" == key:
if not sqlitedb.checkMax(value):
error_message = 'Following price does not exist:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
searchlen['where'].append('Items.Currently <= $maxPrice')
elif "status" == key:
currentTime = sqlitedb.getTime()
currentTimeString = '\'' + currentTime + '\''
if not sqlitedb.checkStatus(value, currentTimeString):
error_message = 'An item does not exist with Status:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchlen['from']:
searchlen['from'].append('Items')
if value == 'open':
searchlen['where'].append('Items.Ends>' +
currentTimeString +
' and Currently<Buy_Price')
elif value == 'close':
searchlen['where'].append('(Items.Ends<' +
currentTimeString +
' or Currently>=Buy_Price)')
elif value == 'notStarted':
searchlen['where'].append('Items.Started>' +
currentTimeString)
else:
pass
searchinfo[key] = value
else:
print(key + "does not have data.")
searchstring = 'select'
for index, value in enumerate(searchlen['select']):
if (index + 1 != len(searchlen['from'])):
searchstring = searchstring + ' ' + value
else:
searchstring = searchstring + ' ' + value
if len(searchlen['from']) != 0:
searchstring = searchstring + ' from'
for index, value in enumerate(searchlen['from']):
if (index + 1 != len(searchlen['from'])):
searchstring = searchstring + ' ' + value + ','
else:
searchstring = searchstring + ' ' + value
if len(searchlen['where']) != 0:
searchstring = searchstring + ' where'
for index, value in enumerate(searchlen['where']):
if (index + 1 != len(searchlen['where'])):
searchstring = searchstring + ' ' + value + ' and'
else:
searchstring = searchstring + ' ' + value
t = sqlitedb.transaction()
try:
search_result = sqlitedb.query(searchstring, searchinfo)
except Exception as e:
t.rollback()
print str(e)
else:
t.commit()
return render_template('search.html',
search_result=search_result,
search_params=userInfo)
def POST(self):
post_params = web.input()
itemid = post_params['itemID']
userid = post_params['userID']
try:
name = post_params['name']
except KeyError:
name = ''
category = post_params['category']
description = post_params['description']
minprice = post_params['minPrice']
maxprice = post_params['maxPrice']
status = post_params['status']
query_dict = {}
current_time = sqlitedb.getTime()
if len(category) > 0:
query_string = """
SELECT i.name, i.item_id, a.seller_id, a.currently, a.number_of_bids
FROM Items i, Auctions a, Categories c
WHERE i.item_id = a.item_id
AND i.item_id = c.item_id
"""
query_string += "AND c.category_name LIKE $category \n"
query_dict['category'] = '%' + str(category) + '%'
else:
query_string = """
SELECT i.name, i.item_id, a.seller_id, a.currently, a.number_of_bids
FROM Items i, Auctions a
WHERE i.item_id = a.item_id
"""
if len(itemid) > 0:
query_string += "AND i.item_id LIKE $itemid \n"
query_dict['itemid'] = '%' + str(itemid) + '%'
if len(userid) > 0:
query_string += "AND a.seller_id LIKE $sellerid \n"
query_dict['sellerid'] = '%' + str(userid) + '%'
if len(name) > 0:
query_string += "AND i.name LIKE $name \n"
query_dict['name'] = '%' + str(name) + '%'
if len(description) > 0:
query_string += "AND i.description LIKE $description \n"
query_dict['description'] = '%' + str(description) + '%'
if len(minprice) > 0:
query_string += "AND a.currently > $minprice \n"
query_dict['minprice'] = minprice
if len(maxprice) > 0:
query_string += "AND a.currently < $maxprice \n"
query_dict['maxprice'] = maxprice
if status == 'all':
pass
elif status == 'open':
query_string += "AND a.started <= $the_time and a.ends > $the_time \n"
query_dict['the_time'] = current_time
elif status == 'closed':
query_string += "AND a.ends <= $the_time \n"
query_dict['the_time'] = current_time
elif status == 'not started':
query_string += "AND a.started > $the_time \n"
query_dict['the_time'] = current_time
query_string += 'LIMIT 50'
results = sqlitedb.query(query_string, query_dict)
page = 1
query_string = ' '.join(query_string.split())
return render_template('browse.html',
query=query_string,
results=results,
query_dict=query_dict,
page=page)
def POST(self):
post_params = web.input()
itemID = post_params['itemID']
itemCategory = post_params['itemCategory']
itemDescription = post_params['itemDescription']
userID = post_params['userID']
minPrice = post_params['minPrice']
maxPrice = post_params['maxPrice']
status = post_params['status']
userQuery = {
'itemID': itemID,
'itemCategory': itemCategory,
'itemDescription': itemDescription,
'userID': userID,
'minPrice': minPrice,
'maxPrice': maxPrice,
'status': status
}
searchQuery = {}
searchSentenceDict = {
'select': ['distinct', '*'],
'from': [],
'where': []
}
searchSentence = ""
#Get the query information that user added, save that in the searchQuery dictionary for variable passing.
#Add the necessary syntax to execute those querys into the searchSentenceDict.
for key, value in userQuery.items():
#check to see if the value is not empty
if value != "":
#Translations for each into the search sentence
#ItemID query
if "itemID" == key:
#check if item is valid
if not sqlitedb.isItemValid(value):
#if the item is not valid return html itemID is wrong
error_message = 'Item ID:' + value + ' does not exist'
return render_template('search.html',
message=error_message)
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
searchSentenceDict['where'].append(
'Items.itemID = $itemID')
#ItemCategory query
elif "itemCategory" == key:
#check if category is valid
if not sqlitedb.isCategoryValid(value):
#if the item is not valid return html itemID is wrong
error_message = 'Item Category:' + value + ' does not exist'
return render_template('search.html',
message=error_message)
#Nested EXISTS in WHERE will work as intended requries Items to be in above FROM
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
#return all items matching the itemID (does all if no specific itemID) and the specified category
searchSentenceDict['where'].append(
'exists(select Categories.itemID from Categories where Categories.category = $itemCategory and Categories.itemID = Items.itemID)'
)
#ItemDescription query
elif "itemDescription" == key:
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
searchSentenceDict['where'].append(
'Items.Description like $itemDescription')
#Adjust the value so it will execute a pattern match on the string
value = '%' + value + '%'
#UserID querey
elif "userID" == key:
#check if user is valid
if not sqlitedb.isUserValid(value):
#if the user is not valid return html userID is wrong
error_message = 'User ID:' + value + ' does not exist'
return render_template('search.html',
message=error_message)
#Nested Exists Similar to itemCategory, requires Items to be in above FROM
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
#return all items matching the userID.
searchSentenceDict['where'].append(
'exists(select Users.userID from Users where Users.userID = $userID and Items.Seller_UserID==Users.userID)'
)
#MinPrice query
elif "minPrice" == key:
#check if user is valid
if not sqlitedb.isMinPriceValid(value):
#if the user is not valid return html userID is wrong
error_message = 'An item does not exist with a Min Price:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
#return all items whose "Currently" price is greater than or equal too minPrice.
searchSentenceDict['where'].append(
'Items.Currently >= $minPrice')
#MinPrice query
elif "maxPrice" == key:
if not sqlitedb.isMaxPriceValid(value):
#if the user is not valid return html userID is wrong
error_message = 'An item does not exist with a Max Price:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
#return all items whose "Currently" price is greater than or equal too minPrice.
searchSentenceDict['where'].append(
'Items.Currently <= $maxPrice')
#Status query
elif "status" == key:
#get the current time of the system
currentTime = sqlitedb.getTime()
currentTimeString = '\'' + currentTime + '\''
if not sqlitedb.isStatusValid(value, currentTimeString):
#if the user is not valid return html userID is wrong
error_message = 'An item does not exist with Status:' + value
return render_template('search.html',
message=error_message)
if "Items" not in searchSentenceDict['from']:
searchSentenceDict['from'].append('Items')
#if we are looking for open items, check for the ending time greater than current
if value == 'open':
searchSentenceDict['where'].append(
'Items.Ends>' + currentTimeString +
' and Currently<Buy_Price')
#if we are looking for closed items, check for the ending time less than current
elif value == 'close':
searchSentenceDict['where'].append(
'(Items.Ends<' + currentTimeString +
' or Currently>=Buy_Price)')
#if we are looking for not started items, check for the starting time greater than current
elif value == 'notStarted':
searchSentenceDict['where'].append('Items.Started>' +
currentTimeString)
#otherwise all is the value, thus add no where clause
else:
pass
#Finally add the value to the official searchQuery
searchQuery[key] = value
else:
print(key + "was empty")
#Contructs the querey searchSentence for db query from the searchSentenceDict
#SELECT
searchSentence = 'select'
for index, value in enumerate(searchSentenceDict['select']):
if (index + 1 != len(searchSentenceDict['from'])):
searchSentence = searchSentence + ' ' + value
else:
searchSentence = searchSentence + ' ' + value
#FROM
if len(searchSentenceDict['from']) != 0:
searchSentence = searchSentence + ' from'
for index, value in enumerate(searchSentenceDict['from']):
if (index + 1 != len(searchSentenceDict['from'])):
searchSentence = searchSentence + ' ' + value + ','
else:
searchSentence = searchSentence + ' ' + value
#WHERE
if len(searchSentenceDict['where']) != 0:
searchSentence = searchSentence + ' where'
for index, value in enumerate(searchSentenceDict['where']):
if (index + 1 != len(searchSentenceDict['where'])):
searchSentence = searchSentence + ' ' + value + ' and'
else:
searchSentence = searchSentence + ' ' + value
#DEBUG print messages
#print(searchQuery)
#print(searchSentence)
#searchSentence=searchSentence + ' limit 10'
#Create the transcation,query the db, return the search results based off query,update html.
t = sqlitedb.transaction()
try:
search_result = sqlitedb.query(searchSentence, searchQuery)
except Exception as e:
t.rollback()
print str(e)
else:
t.commit()
return render_template('search.html',
search_result=search_result,
search_params=userQuery)
def POST(self):
#Get parameters from form
params = web.input() #If form not filled, defaults entry to null
item_id = params['itemID']
user_id = params['userID']
min_price = params['minPrice']
max_price = params['maxPrice']
status = params['status']
#LOGIC BLOCK - to determine what query to run
#start w/ base query and add up
if (status == 'open'):
query_string = "select * from Items i, CurrentTime ct where ct.Time < i.Ends"
if (item_id):
query_string += " AND i.ItemID = '%(itemID)s' " % {
'itemID': item_id
}
if (user_id):
query_string += " AND s.UserID = '%(userID)s' " % {
'userID': user_id
}
if (min_price):
query_string += " AND i.Currently >= '%(minPrice)s' " % {
'minPrice': min_price
}
if (max_price):
query_string += " AND i.Currently <= '%(maxPrice)s' AND i.Buy_Price <= '%(maxPrice)s' " % {
'maxPrice': max_price
}
elif (status == 'close'):
query_string = "select * from Items i, CurrentTime ct where ct.Time >= i.Ends"
if (item_id):
query_string += " AND i.ItemID = '%(itemID)s' " % {
'itemID': item_id
}
if (user_id):
query_string += " AND i.Seller_UserID = '%(userID)s' " % {
'userID': user_id
}
if (min_price):
query_string += " AND i.Currently >= '%(minPrice)s' " % {
'minPrice': min_price
}
if (max_price):
query_string += " AND i.Currently <= '%(maxPrice)s' AND i.Buy_Price <= '%(maxPrice)s' " % {
'maxPrice': max_price
}
elif (status == 'notStarted'):
query_string = "select * from Items i, CurrentTime ct where ct.Time < i.Started"
if (item_id):
query_string += " AND i.ItemID = '%(itemID)s' " % {
'itemID': item_id
}
if (user_id):
query_string += " AND i.Seller_UserID = '%(userID)s' " % {
'userID': user_id
}
if (min_price):
query_string += " AND i.Currently >= '%(minPrice)s' " % {
'minPrice': min_price
}
if (max_price):
query_string += " AND i.Currently <= '%(maxPrice)s' AND i.Buy_Price <= '%(maxPrice)s' " % {
'maxPrice': max_price
}
else: #status == all
query_string = "select * from Items i"
if ((not item_id) or (not user_id) or (not min_price)
or (not max_price)):
query_string += " where i.Buy_Price > 0"
if (item_id):
query_string += " AND i.ItemID = '%(itemID)s' " % {
'itemID': item_id
}
if (user_id):
query_string += " AND i.Seller_UserID = '%(userID)s' " % {
'userID': user_id
}
if (min_price):
query_string += " AND i.Currently >= '%(minPrice)s' " % {
'minPrice': min_price
}
if (max_price):
query_string += " AND i.Currently <= '%(maxPrice)s' AND i.Buy_Price <= '%(maxPrice)s' " % {
'maxPrice': max_price
}
#END OF LOGIC BLOCK
print str(query_string) #for debug
#Run the query
t = sqlitedb.transaction()
try:
results = sqlitedb.query(query_string)
except Exception as e:
t.rollback()
print str(e)
return render_template('search.html',
message="A problem occurred.",
key=results)
else:
t.commit()
return render_template('search.html', key=results)
def POST(SELF):
post_params = web.input()
item_id = post_params['itemID']
user_id = post_params['userID']
min_price = post_params['minPrice']
max_price = post_params['maxPrice']
category = post_params['category']
status = post_params['status']
description = post_params['description']
line1 = ""
line2 = ""
line3 = ""
line4 = ""
line5 = ""
line6 = ""
line7 = ""
Item_id = ""
userId = ""
minPrice = ""
maxPrce = ""
#grab current time
current_time = sqlitedb.getTime()
print current_time
#do some query based on the input
try:
if (item_id != ""):
Item_id = int(item_id)
line1 = "(select ItemID from Items where ItemID = $Item_id) "
if (user_id != ""):
userId = user_id
line2 = "(select ItemID from Items where Seller_UserID = $userId) "
if (min_price != ""):
minPrice = float(min_price)
line3 = "(select ItemID from Items where Currently >= $minPrice) "
if (max_price != ""):
maxPrce = float(max_price)
line4 = "(select ItemID from Items where Currently <= $maxPrice) "
if (category != ""):
line5 = "(select ItemID from Categories where Category = $category)"
except Exception as e:
message = "please write valid input!"
return render_template('search.html', message=message)
if (status == "open"):
line6 = "(select ItemID from Items where (Started <= $current_time) and (Ends >= $current_time) or Currently > Buy_Price)"
if (status == "all"):
line6 = "(select ItemID from Items)"
if (status == "close"):
line6 = "(select ItemID from Items where (Started < $current_time) and (Ends < $current_time))"
if (status == "notStarted"):
line6 = "(select ItemID from Items where (Started > $current_time) and (Ends > $current_time))"
if (description != ""):
line7 = "(select ItemID from Items where instr(Description, $description) > 0)"
res = [line1, line2, line3, line4, line5, line6, line7]
query_string = "select distinct * from Items inner join Categories on Items.ItemID = Categories.ItemID where Items.ItemID in "
isFirst = False
for i in range(len(res)):
if isFirst == False and res[i] != "":
isFirst = True
query_string += res[i]
elif res[i] != "":
query_string += "and Items.ItemID in "
query_string += res[i]
query_string += " group by Items.ItemID"
result = sqlitedb.query(
query_string, {
'Item_id': Item_id,
'userId': userId,
'minPrice': minPrice,
'maxPrice': maxPrce,
'category': category,
'current_time': current_time,
'description': description
})
#check status
count = 0
status = range(len(result))
itemID = range(len(result))
counter = 0
cur_time = string_to_time(sqlitedb.getTime())
for a in result:
for b in a:
count += 1
if (count % 11 == 1):
itemID[counter] = int(a[b])
if (count % 11 == 6):
start = string_to_time(a[b])
if (count % 11 == 3):
end = string_to_time(a[b])
if (count % 11 == 8):
cur = int(a[b])
if (count % 11 == 9):
buyprice = a[b]
if (cur_time > start and cur_time > end):
status[counter] = "CLOSE"
if (buyprice != None):
buy = int(buyprice)
if (cur >= buy):
status[counter] = "CLOSE"
if (cur_time > start and cur_time < end):
status[counter] = "OPEN"
if (cur_time < start and cur_time < end):
status[counter] = "NOT START"
count = 0
counter += 1
return render_template('search.html',
search_result=result,
status=status,
itemID=itemID)
def POST(self):
post_params = web.input()
item_id = post_params['itemID']
user_id = post_params['userID']
min_price = post_params['minPrice']
max_price = post_params['maxPrice']
category = post_params['category']
status = post_params['status']
search_result = [status]
src1 = ""
src2 = ""
src3 = ""
src4 = ""
src5 = ""
src6 = ""
itemId = ""
minPrice = ""
maxPrice = ""
winnerStr = ""
currTime = sqlitedb.getTime()
if (item_id != ""):
itemId = int(item_id)
src1 = " (select ItemID from Items where ItemID = $itemId) "
if (user_id != ""):
src2 = " (select ItemID from Sales where UserID = $userId) "
if (min_price != ""):
minPrice = float(min_price)
src3 = " (select ItemID from Items where Currently > $minPrice) "
if (max_price != ""):
maxPrice = float(max_price)
src4 = " (select ItemID from Items where Currently < $maxPrice) "
if (status == "open" ):
src5 = " (select ItemID from Items where (Started < $currTime) and (Ends > $currTime)) "
if (status == "close"):
winnerStr = "select distinct ItemID, UserID as Winner from Bids where UserID in (select UserID from (select UserID, max(Amount) from Bids group by ItemID))"
src5 = " (select ItemID from Items where ((Started < $currTime) and (Ends < $currTime)) or Currently > Buy_Price)"
if (status == "notStarted"):
src5 = " (select ItemID from Items where (Started > $currTime) and (Ends > $currTime)) "
if (status == "all") :
src5 = " (select ItemID from Items) "
if (category != ""):
src6 = " (select ItemID from Categories where (Category = $category)) "
arr = [src1,src2,src3,src4,src5,src6]
query_string = "select distinct * from Items inner join Sales on Items.ItemID = Sales.ItemID where Items.ItemID in"
first = False
for i in xrange(len(arr)):
if first == False and arr[i] != "":
first = True
query_string += arr[i]
elif arr[i] != "":
query_string += "and Items.ItemID in" + arr[i]
t = sqlitedb.transaction()
if (winnerStr != ""):
cols = "q1.ItemID,Ends,First_Bid,Name,Started,Number_of_Bids,UserID,Buy_Price,Currently,Description,Winner"
query_string = "select distinct " + cols + " from (" +query_string + ") q1 inner join (" + winnerStr + ") q2 on q1.ItemID = q2.ItemID"
try:
result = sqlitedb.query(query_string, {'userId' : user_id, 'itemId': itemId, 'maxPrice':maxPrice, 'minPrice': minPrice, 'currTime':currTime, 'category':category})
search_result = result
except Exception as e:
search_result = []
else:
t.commit()
return render_template('search.html',search_result = search_result)
