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)