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),
)
