def createAllocation(userId, itemId, alloAmount): if not itemExists(itemId): return -1 leftAmount = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, )[0]['left_amount'] if alloAmount > leftAmount: return -1 if not deductItemAmount(itemId, alloAmount): # Modify left_amount return -1 if not q.insertRecordTo( "Allocations", "(item_id, user_id, allo_amount)", (str(itemId), str(userId), str(alloAmount)), "(%s, %s, %s)", ): return -1 return q.selectInfoByConditions( "Allocations", "allo_id", "item_id = '%s' AND user_id = '%s'", (itemId, userId), )[-1]['allo_id']
def addFriend(first_user_id, second_user_id): result_list = q.selectInfoByConditions( "Friends", "user_id", "user_id = '%s' AND friend_id = '%s'", (first_user_id, second_user_id), ) res = {} if len(result_list) > 0 or first_user_id == second_user_id: res["status"] = False else: first_res = q.selectInfoByConditions( "Users", "user_name", "user_id = '%s'", (first_user_id), ) second_res = q.selectInfoByConditions( "Users", "user_name", "user_id = '%s'", (second_user_id), ) if len(first_res) == 0 or len(second_res) == 0: res["status"] = False return res print(first_res) res["status"] = True q.insertRecordTo( "Friends", "(user_id, friend_id, friend_name, title)", ( first_user_id, second_user_id, second_res[0]["user_name"], second_res[0]["user_name"][0].upper(), ), "(%s, %s, %s, %s)", ) q.insertRecordTo( "Friends", "(user_id, friend_id, friend_name, title)", ( second_user_id, first_user_id, first_res[0]["user_name"], first_res[0]["user_name"][0].upper(), ), "(%s, %s, %s, %s)", ) return res
def test_modify_item_amount(self): groupId = f.createEmptyGroup("Group", f.signUp("Owner", "12345")) orderId = f.createOrder(groupId, "Order", 5) itemPrevAmount = 124.3 itemCurAmount_valid = 150 itemCurAmount_small = -34 itemCurAmount_Large = 1001 itemId = f.createItem("itemOld", 124.3, orderId) check = f.modifyItemAmount(itemId, itemCurAmount_valid) result_list = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, ) self.assertTrue(check) self.assertEqual(len(result_list), 1) self.assertEqual(result_list[0]['left_amount'], itemCurAmount_valid) f.modifyItemAmount(itemId, itemPrevAmount) # reset # Too small check = f.modifyItemAmount(itemId, itemCurAmount_small) result_list = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, ) self.assertFalse(check) self.assertEqual(len(result_list), 1) self.assertEqual( result_list[0]['left_amount'], itemPrevAmount, ) # remains # Too large check = f.modifyItemAmount(itemId, itemCurAmount_Large) result_list = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, ) self.assertFalse(check) self.assertEqual(len(result_list), 1) self.assertEqual( result_list[0]['left_amount'], itemPrevAmount, ) # remains
def getAllMembersByGroupId(groupId): result_list = q.selectInfoByConditions( "GroupUsers INNER JOIN Users ON GroupUsers.member_id = Users.user_id", "Users.user_id as member_id, Users.user_name as member_name", "group_id = '%s'", (groupId), ) return result_list
def getAllGroups(userId): result_list = q.selectInfoByConditions( "GroupUsers INNER JOIN gGroups ON gGroups.group_id = GroupUsers.group_id", "gGroups.group_id as group_id, group_name, owner_id", "member_id = '%s'", (userId), ) return result_list
def getAllMemberIds(groupId): result_list = q.selectInfoByConditions( "GroupUsers", "member_id", "group_id = '%s'", str(groupId), ) return q.getValsByKey(result_list, 'member_id')
def test_create_allocation_invalid(self): # No exsiting allocation self.assertEqual( q.getNumOfRecordByConditions("Allocations", ), 0, ) # userId = f.signUp("Amy", "12345") groupId = f.createEmptyGroup("Group", userId) orderId = f.createOrder(groupId, "Order", 5) oriAmount = 124.3 itemId = f.createItem("item", oriAmount, orderId) alloAmount = 150 a_id = f.createAllocation(userId, itemId, alloAmount) result_list = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, ) self.assertEqual(a_id, -1) self.assertEqual(len(result_list), 1) self.assertEqual(result_list[0]['left_amount'], oriAmount) # remains numOfRecords = q.getNumOfRecordByConditions( "Allocations", "item_id = '%s' AND user_id = '%s'", (itemId, userId), ) self.assertEqual(numOfRecords, 0)
def removeFriend(first_user_id, second_user_id): result_list = q.selectInfoByConditions( "Friends", "user_id", "user_id = '%s' AND friend_id = '%s'", (first_user_id, second_user_id), ) res = {} if len(result_list) == 0: res["status"] = False else: res["status"] = True q.deleteRecordByCondition( "Friends", "user_id = '%s' AND friend_id = '%s'", (first_user_id, second_user_id), ) q.deleteRecordByCondition( "Friends", "user_id = '%s' AND friend_id = '%s'", (second_user_id, first_user_id), ) return res
def test_delete_allocation(self): userId = f.signUp("Amy", "12345") groupId = f.createEmptyGroup("Group", userId) orderId = f.createOrder(groupId, "Order", 5) oriAmount = 124.3 itemId = f.createItem("item", 124.3, orderId) alloAmount = 110 a_id = f.createAllocation(userId, itemId, alloAmount) check = f.deleteAllocation(a_id) self.assertTrue(check) self.assertEqual( q.getNumOfRecordByConditions( "Allocations", "allo_id = '%s'", a_id, ), 0, ) self.assertEqual( q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, )[0]['left_amount'], oriAmount, )
def test_modify_allocation_invalid(self): userId = f.signUp("Amy", "12345") groupId = f.createEmptyGroup("Group", userId) orderId = f.createOrder(groupId, "Order", 5) oriAmount = 124.3 itemId = f.createItem("item", 124.3, orderId) prevAlloAmount = 110 curAlloAmount = 150 a_id = f.createAllocation(userId, itemId, prevAlloAmount) check = f.modifyAllocatedAmount(a_id, curAlloAmount) self.assertFalse(check) result = q.selectAllByConditions( "Allocations", "allo_id = '%s'", str(a_id), )[0] self.assertEqual(result['allo_amount'], prevAlloAmount) self.assertEqual( q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", result['item_id'], )[0]['left_amount'], round(oriAmount - prevAlloAmount, 4), )
def pollingMessage(receiver): result_list = q.selectInfoByConditions( "MessageQueue", "sender, receiver, event, receipt_id, data", "receiver = '%s'", ( receiver ), ) q.deleteRecordByCondition("MessageQueue", "receiver = '%s'", receiver) return result_list
def deductItemAmount(itemId, allocated): # diff >= 0 # Assume the item exists prevAmount = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", str(itemId), )[0]['left_amount'] return modifyItemAmount(itemId, prevAmount - allocated)
def getUserIdByUsername(username): result_list = q.selectInfoByConditions( "Users", "user_id", "user_name = '%s'", username, ) if (len(result_list) > 0): return result_list[0]['user_id'] else: return -1
def getGroupIdBygName(gName): result_list = q.selectInfoByConditions( "gGroups", "group_id", "group_name = '%s'", gName, ) if (len(result_list) > 0): return result_list[0]['group_id'] else: return -1
def getUserIdByAuthentication(username, userpwd): result_list = q.selectInfoByConditions( "Users", "user_id", "user_name = '%s' AND user_pwd = '%s'", (username, userpwd), ) if (len(result_list) > 0): return result_list[0]['user_id'] else: return -1
def getUserReceipts(userId): result_list = q.selectInfoByConditions( "Receipts", "info", "user_id = '%s'", (userId), ) if (result_list.__len__() > 0): return result_list[0] else: return ''
def getAllGroups(userId): result_list = q.selectInfoByConditions( "GroupUsers INNER JOIN gGroups ON gGroups.group_id = GroupUsers.group_id", "gGroups.group_id as group_id, group_name, owner_id", "member_id = '%s'", (userId), ) for index, data in enumerate(result_list): result_list[index]['members'] = getAllMembersByGroupId( data['group_id']) return result_list
def pollingMessage(receiver): if not receiver: return [] result_list = q.selectInfoByConditions( "MessageQueue", "sender_id, receiver_id, event, receipt_id, data", "receiver_id = '%s'", ( receiver ), ) if result_list and result_list.__len__() > 0: q.deleteRecordByCondition( "MessageQueue", "receiver_id = '%s'", receiver) return result_list
def insertMemberToGroup(groupId, memberId): result_list = q.selectInfoByConditions( "GroupUsers", "group_id", "group_id = '%s' AND member_id = '%s'", (groupId, memberId), ) if (len(result_list) > 0): print("MATCH") return True return q.insertRecordTo( "GroupUsers", "(group_id, member_id)", (groupId, memberId), "(%s, %s)", )
def createItem(itemName, leftAmount, orderId): q.insertRecordTo( "Items", "(item_name, left_amount, order_id)", (itemName, leftAmount, orderId), "(%s, %s, %s)", ) result_list = q.selectInfoByConditions( "Items", "item_id", "order_id = '%s' AND item_name = '%s'", (orderId, itemName), ) if len(result_list) > 0: return result_list[0]['item_id'] else: return -1
def test_login_exist(self): # Pre-cond username = "******" userpwd = "12345" f.signUp(username, userpwd) result_list = q.selectInfoByConditions( "Users", "user_id", "user_name = '%s' AND user_pwd = '%s'", (username, userpwd), ) self.assertEqual(len(result_list), 1) # Exist now u_id = f.login(username, userpwd) u_id_db = result_list[0]['user_id'] self.assertEqual(u_id, u_id_db)
def createOrder(groupId, orderName, numOfItems): check = q.insertRecordTo( "Orders", "(group_id, order_name, item_unres_num)", (groupId, orderName, numOfItems), "(%s, %s, %s)", ) if check: result_list = q.selectInfoByConditions( "Orders", "order_id", "order_name = '%s' AND group_id = '%s'", (orderName, groupId), ) if len(result_list) > 0: return result_list[-1]['order_id'] # get the newly inserted return -1 # failed
def test_modify_item_name(self): groupId = f.createEmptyGroup("Group", f.signUp("Owner", "12345")) orderId = f.createOrder(groupId, "Order", 5) itemOldName = "itemOld" itemNewName = "itemNew" itemId = f.createItem(itemOldName, 124.3, orderId) check = f.modifyItemName(itemId, itemNewName) result_list = q.selectInfoByConditions( "Items", "item_name", "item_id = '%s'", itemId, ) self.assertTrue(check) self.assertEqual(len(result_list), 1) self.assertEqual(result_list[0]['item_name'], itemNewName)
def deleteAllocation(alloId): if not allocationExists(alloId): return False result = q.selectAllByConditions( "Allocations", "allo_id = '%s'", alloId, )[0] itemId = result['item_id'] alloAmount = result['allo_amount'] curAmount = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, )[0]['left_amount'] if not modifyItemAmount(itemId, curAmount + alloAmount): return False return q.deleteRecordByCondition("Allocations", "allo_id = '%s'", alloId)
def test_create_group_with_members(self): # numOfGroups = q.getNumOfRecordByConditions("gGroups") self.assertEqual(q.getNumOfRecordByConditions("gGroups"), 0) # No exsiting group # Create group members memberIds = [] ownerId = f.signUp("Amy", "1234") # Amy is the group owner # Create member list memberIds.append(f.signUp("Bob", "1234")) memberIds.append(f.signUp("Candy", "1234")) groupName = "group1" # Groupname: "group1", ownerId = 1, memberIds = [2,3] groupId = f.createGroupWithMembers(groupName, ownerId, memberIds) # check if group exists now check = q.checkRecordExistByConditions( "gGroups", "group_name = '%s' AND owner_id = '%s'", (groupName, ownerId), ) self.assertTrue(check) # The group exsits now # For convenience, add the owner to the memberlist memberIds.append(ownerId) # Member list result_list = q.selectInfoByConditions( "GroupUsers", "member_id", "group_id = '%s'", str(groupId), ) self.assertEqual(len(result_list), len(memberIds)) # All members in the tables from memberIds membersDB = u.getAllMemberIds(groupId) self.assertEqual(set(membersDB), set(memberIds))
def modifyAllocatedAmount(alloId, alloAmount): if not allocationExists(alloId): return False result = q.selectAllByConditions( "Allocations", "allo_id = '%s'", alloId, )[0] itemId = result['item_id'] curAllo = result['allo_amount'] curAmount = q.selectInfoByConditions( "Items", "left_amount", "item_id = '%s'", itemId, )[0]['left_amount'] recoverAmount = curAmount + curAllo if alloAmount > recoverAmount: return False if not q.updateRecordByConditions( "Allocations", "allo_amount = '%s'", "allo_id = '%s'", (alloAmount, alloId), ): return False return q.updateRecordByConditions( "Items", "left_amount = '%s'", "item_id = '%s'", (recoverAmount - alloAmount, itemId), )