def get_reward_user(userID):
sql_statement = "SELECT * FROM myrewardinventory ri INNER JOIN myrewards r ON ri.rewardId = r.rewardId where " \
"ri.userID = '" + userID + "';"
response = mysql_helper.select_statement(sql_statement)
if response == False:
return [1]
response_list = []
for record in response:
if record[5] != None:
time = datetime.datetime.strptime(record[5], "%Y-%m-%d %H:%M:%S")
time = time.strftime("%I:%M %p")
print(time)
else:
time = record[5]
response_list.append({
"VOUCHER_ID": record[0],
"REWARD_ID": record[2],
"REWARD_EXPIRY": record[3],
"END_TIME": time,
"REWARD_NAME": record[7],
"REWARD_DESCRIPTION": record[8],
"REWARD_IMG": record[10],
"REWARD_CATEGORY": record[12]
})
return [0, response_list]
def getHeroAvailability(UserID):
sql_statement = "SELECT isAvailable FROM myuser WHERE id = '" + str(
UserID) + "';"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
return [0, response[0][0]]
def get_ratings(starting_address, ending_address):
sql_statement = "SELECT starting_address, ending_address, AVG(rating) FROM myratings " \
"WHERE starting_address = '" + starting_address + "' " \
"AND ending_address = '" + ending_address + "'GROUP BY starting_address"
response = mysql_helper.select_statement(sql_statement)
print(response)
if response == False:
return [1]
elif len(response) == 0:
return [
0, {
"STARTING_ADDRESS": starting_address,
"ENDING_ADDRESS": ending_address,
"AVG_RATING": "0"
}
]
else:
return [
0, {
"STARTING_ADDRESS": response[0][0],
"ENDING_ADDRESS": response[0][1],
"AVG_RATING": str(response[0][2])
}
]
def getUserObj(id):
sql_statement = "SELECT * FROM myuser WHERE id = '" + str(id) + "';"
# self, id, username, fullName, contactNumber, email, age, equipment, powerLevel, hTokens, steps
record = mysql_helper.select_statement(sql_statement)[0]
user = userClass(record[0], record[1], record[2], record[3], record[4],
record[5], record[6], record[7], record[8], record[9])
return user
def get_milestone_number(userId):
# Select milestone number from database
sql_statement = "SELECT milestone FROM myuser WHERE id = '" + str(
userId) + "';"
record = mysql_helper.select_statement(sql_statement)
if len(record) is 0:
return "Invalid userId"
return record[0][0]
def getExpeditions(User):
expeditionObjList = []
sql_statement = "SELECT * FROM myexpedition"
expeditionList = mysql_helper.select_statement(sql_statement)
for ex in expeditionList:
expedition = expeditionClass(
ex[0], ex[1], ex[2], ex[3], ex[4], ex[5], ex[6],
checkEligibleExpedition(ex[1], User.PowerLevel))
expeditionObjList.append(expedition)
return expeditionObjList
def get_reward_by_id(reward_id):
sql_statement = "SELECT * FROM myrewards WHERE rewardId = '" + str(
reward_id) + "';"
try:
record = mysql_helper.select_statement(sql_statement)[0]
except IndexError:
print("ERROR: Reward ID does not exist")
return False
reward = rewardsClass(record[0], record[1], record[2], record[3],
record[4], record[5], record[6])
return reward
def getExpeditionByID(expedition_id):
sql_statement = "SELECT * FROM myexpedition WHERE expeditionID = '" + expedition_id + "';"
try:
ex = mysql_helper.select_statement(sql_statement)[0]
except IndexError:
print("ERROR: Expedition ID does not exist.")
return [1]
expedition = expeditionClass(ex[0], ex[1], ex[2], ex[3], ex[4], ex[5],
ex[6], 'Y')
return [0, expedition]
def get_all_rewards():
sql_statement = "SELECT * FROM myrewards;"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
rewards_list = []
for record in response:
reward = rewardsClass(record[0], record[1], record[2], record[3],
record[4], record[5], record[6])
rewards_list.append(reward)
return [0, rewards_list]
def update_total_power(userID):
sql_statement = "SELECT SUM(e.equipmentPowerLevel) FROM myheroinventory hi INNER JOIN myequipment e " \
"ON hi.equipmentID = e.equipmentID WHERE userId = '" + str(userID) + "' AND isEquipped = 'Y'"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
print(response[0][0])
if response[0][0] is None:
return [0, 0]
else:
return [0, response[0][0]]
def get_endtime(userID, voucherID):
sql_statement = "SELECT endTime FROM myrewardinventory WHERE userId = " + userID + " AND voucherId = " + voucherID + ";"
print(sql_statement)
response = mysql_helper.select_statement(sql_statement)
print("firstLAYER", response[0][0])
time = datetime.datetime.strptime(response[0][0], "%Y-%m-%d %H:%M:%S")
time = time.strftime("%I:%M %p")
print(time)
if not time:
return 1
else:
return time
def add_user(password, username, email_address, age, contact_number):
sql_statement = "INSERT INTO myuser(username, contactNumber, emailAddress, age, password) " \
"VALUES(%s, %s, %s, %s, %s)"
if mysql_helper.sql_operation(
sql_statement,
(username, contact_number, email_address, age, password)):
sql_statement = "SELECT id FROM myuser WHERE username = '******';"
sql_response = mysql_helper.select_statement(sql_statement)
if not sql_response: # If it doesnt work
return 1
else:
print(sql_response[0][0])
return 0, sql_response[0][0]
else:
return 1
def get_bookmarks(userID):
sql_statement = "SELECT * FROM mybookmark WHERE userID = '" + userID + "';"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
else:
bookmarks_list = []
for x in response:
bookmarks_list.append({"BOOKMARK_ID" : x[0],
"USER_ID" : x[1],
"BOOKMARK_NAME": x[2],
"STARTING_ADDR" : x[3],
"ENDING_ADDR": x[4]})
return [0, bookmarks_list]
def randomize_equipment_by_rarity(rarity):
sql_statement = "SELECT * FROM myequipment WHERE equipmentRarity = '" + rarity + "'"
equipment_list = mysql_helper.select_statement(sql_statement)
equipment_obj_list = []
for eq in equipment_list:
print(eq)
equipment_obj = equipmentClass(eq[0], eq[1], eq[2], eq[3], eq[4], eq[5], eq[6], 'N')
equipment_obj_list.append(equipment_obj)
if len(equipment_obj_list) == 0:
print ("ERROR: Equipment with '%s' rarity does not exist " % rarity)
return False
else:
randomized_number = random.randint(0, len(equipment_obj_list) - 1)
print ("SUCCESS: Returned equipment object with %s rarity." % rarity)
return equipment_obj_list[randomized_number]
def get_existing_equipment(User_id):
sql_statement = "SELECT * FROM myheroinventory hi INNER JOIN myequipment eq " \
"ON hi.equipmentId = eq.equipmentId WHERE hi.userId = '" + User_id + "'"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
equipment_list = response
print("%s results returned " % (str(len(equipment_list))))
equipment_obj_list = []
try:
for eq in equipment_list:
equipment_obj = equipmentClass(eq[3], eq[4], eq[5], eq[6], eq[7], eq[8], eq[9], eq[2])
equipment_obj_list.append(equipment_obj)
except IndexError:
return []
return [0, equipment_obj_list]
def get_completed_expeditions(userID):
sql_statement = "SELECT * FROM myuserexpedition as ue INNER JOIN myexpedition as e on ue.expeditionId = e.expeditionId WHERE ue.userId = '" + str(
userID) + "' AND ue.isOngoing = 'P';"
response = mysql_helper.select_statement(sql_statement)
if not response:
return [1]
return [
0, {
"USER_ID": response[0][0],
"IS_ONGOING": response[0][1],
"EXPEDITION_ID": response[0][2],
"START_TIME": response[0][3],
"END_TIME": response[0][4],
"H_TOKENS": response[0][5],
"POWER_LEVEL": response[0][8],
"IMAGE": response[0][9],
"DESCRIPTION": response[0][10],
"TITLE": response[0][11],
"TIME_TAKEN": response[0][12],
"EST_H_TOKEN": response[0][13]
}
]
def updateHToken(HToken, UserID, method):
sql_statement = "SELECT hTokens FROM myuser WHERE id = '" + str(
UserID) + "';"
response = mysql_helper.select_statement(sql_statement)
if not response:
return 1
current_HToken = int(response[0][0])
# Add
if method is 1:
current_HToken += int(HToken)
# Subtract
if method is 0:
current_HToken -= HToken
# Update back into the database
sql_statement = "UPDATE myuser set hTokens = %s WHERE id = %s;"
mysql_helper.sql_operation(
sql_statement, [str(current_HToken), str(UserID)])
if not response:
return 1
return 0