def dashboardData(user):
user_id = user['id']
requests = []
query = (
"SELECT requests.id, users.first_name, users.last_name, services.service, users.rating, requests.date FROM requests INNER JOIN services ON requests.service_id = services.id INNER JOIN users ON requests.reciever_id = users.id INNER JOIN payments p on requests.id != p.request_id WHERE requests.user_servicer_id=%s ORDER BY requests.date DESC;"
% (user_id))
result = db.executeQuery(query)
for i in range(0, len(result)):
request = Request(result[i])
requests.append(vars(request))
feedback = []
query = (
"SELECT feedback.id, users.first_name,users.last_name, services.service, feedback.rating, payments.date FROM requests INNER JOIN users ON requests.reciever_id = users.id INNER JOIN payments ON requests.id = payments.request_id INNER JOIN feedback ON payments.id = feedback.payment_id INNER JOIN services ON requests.service_id = services.id WHERE requests.user_servicer_id=%s ORDER BY payments.date DESC;"
% (user_id))
result = db.executeQuery(query)
for i in range(0, len(result)):
feedback_item = Feeback(result[i])
feedback.append(vars(feedback_item))
query = ("SELECT users.avatar FROM users WHERE users.id = %s;" % (user_id))
result = db.executeQuery(query)[0][0]
encoded_avatar = auth.PngToBase64("./images/" + result)
return {
"requests": requests,
"feedback": feedback,
"avatar": encoded_avatar
}
def deploy_rds_schema(dbEndpoint, dbSecret):
data = urlopen(
'https://s3.us-east-2.amazonaws.com/hindsite-code-repo/schema/1.0/hs.sql',
timeout=1).read()
schema = parse_sql(data.decode('utf-8').split('\n'))
for stmt in schema:
database.executeQuery(stmt, resourceArn=dbEndpoint, secretArn=dbSecret)
def checkGroupAccess(user, group_id):
"""Sprawdzenie czy użytkownik ma dostęp do danej grupy
Args:
user ([type]): [description]
Raises:
HTTPException: [description]
HTTPException: [description]
Returns:
[type]: [description]
"""
query = f'SELECT admin_id FROM groups WHERE group_id == {group_id}'
correct1 = executeQuery(query)
if isinstance(correct1, bool) and not correct1:
raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error')
query = f'SELECT user_id FROM user_group WHERE group_id == {group_id}'
correct2 = executeQuery(query)
if isinstance(correct2, bool) and not correct2:
raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error')
if user.get("user_id") not in [id[0] for id in correct1
] + [id[0] for id in correct2]:
raise HTTPException(
status_code=status.HTTP_401_UNAUTHORIZED,
detail='Only admins and members of the group can see other members'
)
return [id[0] for id in correct2]
def generateToken(username: str, password: str):
"""Stworzenie tokena JWT dla uzytkownia
Args:
username (str): nazwa użytkownika lub email
password (str): hasło niezahashowane
Raises:
HTTPException: nie istnieje uzytkownik o tych danych
Returns:
str: jwt token
"""
# authenticate user
hashed_pass = hashPassword(password)
user = [
user for user in executeQuery(
f'SELECT user_id, password FROM users WHERE username == "{username}" OR email == "{username}"'
) if checkPassword(password, user[1])
]
if len(user) == 0:
raise HTTPException(status_code=status.HTTP_401_UNAUTHORIZED,
detail='Incorrect username/email or password')
# create token
token = jwt.encode({'id': int(user[0][0])}, JWT_SECRET)
return token
def getReactions(group_id: int, post_id: int, user):
"""Pobierz reakcje do danego posta wraz z autorami
Args:
group_id (int): numer id grupy
post_id (int): numer id posta
user (dict): aktualnie zalogowany użytkownik (musi należeć do grupy)
Raises:
HTTPException: jakiś nieoczekiwany błąd - sql injection
Returns:
ResponseReactions: lista z reakcjami
"""
checkGroupAccess(user, group_id)
query = f'SELECT owner_id, reaction FROM reactions WHERE post_id == {post_id}'
correct = executeQuery(query, objectKeys=['user_id', 'reaction'])
if isinstance(correct, bool) and not correct:
raise HTTPException(
status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error'
)
return {
'reactions': correct
}
def getPosts(group_id: int, user, start=None, end=None):
"""Wyświetl posty z zadanego okresu czasu
Args:
group_id (int): numer id grupy
user (dict): aktualnie zalogowany użytkownik (musi należeć do grupy)
start (int, optional): początek zakresu - data postaci DDMMYYYY. Domyslnie zwróci ostatnie 25 postów.
end (int, optional): początek zakresu - data postaci DDMMYYYY. Domyslnie dzisiejszy dzień.
Raises:
HTTPException: jakiś nieoczekiwany błąd - sql injection
HTTPException: próba wyświetlenia danych nie przez członka grupy
Returns:
ResponsePosts: lista z danymi o postach
"""
checkGroupAccess(user, group_id)
query = f'SELECT post_id, author_id, created, text FROM posts WHERE group_id == {group_id}'
if start is not None and end is not None:
start_date = datetime.strptime(start, '%d%m%Y')
start_seconds = math.floor((start_date - datetime(1970, 1, 1)).total_seconds())
end_date = datetime.strptime(end, '%d%m%Y')
end_seconds = math.floor((end_date - datetime(1970, 1, 1)).total_seconds())
start, end = min(start_seconds, end_seconds), max(start_seconds, end_seconds)
query += f' AND created > {start} AND created < {end}'
correct = executeQuery(query, objectKeys=['post_id', 'author_id', 'created', 'text'])
if isinstance(correct, bool) and not correct:
raise HTTPException(
status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error'
)
return {
'posts': correct[:25]
}
def get_active_RFCs():
rfcs_data = database.executeQuery("s_rfc_w_s", pars=("1"))
rfcs = []
for rfc in rfcs_data:
rfcs.append(rfc)
return rfcs
def getSettings(user_id):
query = ("SELECT users.avatar FROM users WHERE users.id = %s;" % (user_id))
result = db.executeQuery(query)[0][0]
encoded_avatar = auth.PngToBase64("./images/" + result)
return {"avatar": encoded_avatar}
def get_single_RFC(id):
sql = " " % id
rfcs_data = database.executeQuery("s_rfcs_w_id", pars=(id))
rfcs = []
for rfc in rfcs_data:
rfcs.append(rfc)
return rfcs
def login(email, password):
user = {}
query = ("SELECT id, first_name, last_name, email, password FROM users WHERE email='%s';" % (email))
result = db.executeQuery(query)[0]
if result!=[] and result[4] == password:
user = vars(User(result))
return user
def getUser(user_id):
query = (
"SELECT id, first_name, last_name, email, avatar FROM users WHERE id='%s';"
% (user_id))
result = db.executeQuery(query)[0]
user = UpdatedUser(result)
return (vars(user))
def existsRFCinDB(rfc_name):
rfc = database.executeQuery("s_rfc_w_name", pars=(rfc_name))
rfcs = []
for r in rfc:
rfcs.append(rfc)
if len(rfcs) >= 1:
return True
else:
return False
def getServices():
services = []
query = "SELECT id, service FROM services ORDER BY service ASC;"
result = db.executeQuery(query)
for s in result:
service = Service(s)
services.append(vars(service))
return services
def getUserServices(user_id):
user_services = []
query = (
"SELECT users_providables.id, users_providables.fee, users_providables.duration, users_providables.rating, services.id, services.service FROM users_providables INNER JOIN services ON users_providables.service_id = services.id WHERE user_id='%s';"
% (user_id))
result = db.executeQuery(query)
for us in result:
user_service = UserService(us)
user_services.append(vars(user_service))
return user_services
def signup(first_name, last_name, birthday, email, password):
signup = False
query = ("select * from users where email=%s;" % (email))
result = db.executeQuery(query)
if(result==[]):
query = "insert into users (first_name, last_name, birthday, email, password, date) values (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\");" % (first_name,last_name,birthday,email,password, datetime.today().strftime('%Y-%m-%d'))
csr.execute(query)
db.commit()
signup = True
return signup
def feature_proposal(event, topic):
""" Selects classes and recommand to users
:param event <event>: the message
:param topic <string>: the topic that users want to learn
"""
query_count = 'SELECT * FROM Python GROUP BY class_title;'
n_class = len(db.executeQuery(query_count))
recommandation = '找到' + str(
n_class) + '堂課\n 輸入你有興趣的_' + topic + '_課程類型\n 最多可選擇三個類型\n 推薦類型:'
# Select 10 features (from table 4)
feature_names = [
"推薦", "簡單", "程式", "好理解", "用心", "豐富", "清晰", "實用", "樂於回答", "應用"
]
features_lst = []
max_features = 10
for i in range(max_features):
features_lst.append(
PostbackAction(
label=feature_names[i],
display_text=feature_names[i] + '已選取', # output as messages
data='action=select_feature&' + str(event.source.user_id) +
'&' + feature_names[i]))
# Create carousel template
car_cols = []
for i in range(4):
action_lst = []
for j in range(3):
if 3 * i + j < max_features:
action_lst.append(features_lst[3 * i + j])
elif 3 * i + j == max_features:
action_lst.append(
URIAction(
label='回報問題',
uri='https://github.com/Chung-Yun/Education-Platform'))
else:
action_lst.append(PostbackAction(label='.', data='NAN'))
car_cols.append(
CarouselColumn(
thumbnail_image_url='https://i.imgur.com/xHzymxe.gif',
title='課程推薦系統',
text=recommandation + '（第' + str(i + 1) + '頁）',
actions=action_lst))
carousel_template = CarouselTemplate(columns=car_cols)
return TemplateSendMessage(alt_text='課程推薦系統', template=carousel_template)
def usersCommunitySearch(user_id, search):
user_search = []
query = (
"SELECT users.id, CONCAT(first_name , ' ', last_name) as name, rating, avatar, a.id as address_id, a.street, a.city,a.state,a.zipcode,a.country FROM users LEFT JOIN addresses a on users.address_id = a.id WHERE users.id != %s;"
% (user_id))
users = db.executeQuery(query)
for user in users:
pretty_user = vars(SearchResult(user))
if search.lower() in pretty_user['name'].lower():
user_search.append(pretty_user)
return user_search
def get_connstring():
result = database.executeQuery("s_server")
servers = []
for r in result:
datos = r
break
ip = datos[1]
sysnr = datos[2]
client = datos[3]
user = datos[4]
passwd = base64.decodestring(datos[5])
conn = easysap.getConnString(ip, sysnr, client, user, passwd)
return conn
def add_service(user_id, service):
query = (
"INSERT INTO users_providables (user_id, service_id, fee, duration, rating) VALUES (%d, %d, %f, %d, %f);"
% (user_id, service['serviceId'], service['fee'], service['duration'],
service['rating']))
db.commitQuery(query)
query = (
"SELECT users_providables.id ,fee, duration, rating, service_id, services.service FROM users_providables INNER JOIN services ON users_providables.service_id = services.id WHERE users_providables.user_id=%d AND users_providables.service_id=%d AND users_providables.fee=%f AND users_providables.duration=%d AND users_providables.rating=%f;"
% (user_id, service['serviceId'], service['fee'], service['duration'],
service['rating']))
new_service = db.executeQuery(query)[0]
user_service = UserService(new_service)
return vars(user_service)
async def getCurrentUser(token: str = Depends(oauth2_scheme)):
"""Pobranie aktualnie zalogowanego użytkownika
Args:
token (str): token przekazany w nagłowku zapytania
Raises:
HTTPException: nie istnieje użytkonik o takim tokenie
Returns:
dict: obiekt użytkownika
"""
payload = jwt.decode(token, JWT_SECRET, algorithms=['HS256'])
user = executeQuery(
f'SELECT user_id, username, email, avatar FROM users WHERE user_id == {payload.get("id")}',
objectKeys=['user_id', 'username', 'email', 'avatar'])
if len(user) == 0:
raise HTTPException(status_code=status.HTTP_401_UNAUTHORIZED,
detail='Unauthorized request')
return user[0]
def getComments(group_id: int, post_id: int, user):
"""Pobierz komentarze pod danym postem
Args:
group_id (int): numer id grupy
post_id (int): numer id posta
user (dict): aktualnie zalogowany użytkownik (musi należeć do grupy)
Raises:
HTTPException: jakiś nieoczekiwany błąd - sql injection
Returns:
ResponseComments: lista z komentarzami
"""
checkGroupAccess(user, group_id)
query = f'SELECT comment_id, author_id, created, text FROM comments WHERE post_id == {post_id}'
correct = executeQuery(
query, objectKeys=['comment_id', 'author_id', 'created', 'text'])
if isinstance(correct, bool) and not correct:
raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error')
return {'comments': correct}
def getGroup(group_id: int):
"""Wyświetl dane o innej grupie
Args:
group_id (int): numer id grupy
Raises:
HTTPException: jakiś nieoczekiwany błąd - sql injection
HTTPException: podany numer id nie istnieje w bazie
Returns:
ResponseGroup: dict z danymi użytkownika
"""
query = f'SELECT name, admin_id, image FROM groups WHERE group_id == {group_id}'
correct = executeQuery(query, objectKeys=['name', 'admin_id', 'image'])
if isinstance(correct, bool) and not correct:
raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail='Internal Server Error')
if len(correct) == 0:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
detail='Group not found')
return correct[0]
def getRFCs():
rfcs_data = database.executeQuery("s_rfcs")
rfcs = []
for rfc in rfcs_data:
rfcs.append(rfc)
return rfcs
def handle_postback(event):
print(event.postback.data)
if event.postback.data == 'demande=learn_sth_else':
line_bot_api.reply_message(event.reply_token, TextSendMessage('請下指令： 買課幫手 我想學 [想學的東西]'))
elif event.postback.data.find('action')==0 :
# action=select_feature&'+str(event.source.user_id)+'&'+featurename
data = event.postback.data[len('action')+1:]
print(data)
# Step 1: Registeration
if data.find('register_user')==0 :
# Interprete user_id and selected features
user_and_label = data[len('register_user')+1:]
first_and = user_and_label.find('&')
user_id = user_and_label[:first_and]
print(user_id)
label = user_and_label[first_and+1:]
print(label)
# Save to DB (status set to 0)
register_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [label], \
"choices" : [''], \
"time_stamp" : ['date'], \
"status" : [0]})
db.addTableFromDF('replies', register_df)
print('user registered to replies')
line_bot_api.reply_message(event.reply_token, feature_proposal(event, label))
# Step 2: Choose features
elif data.find('select_feature')==0:
# Interprete user_id and selected features
user_and_feature = data[len('select_feature')+1:]
first_and = user_and_feature.find('&')
user_id = user_and_feature[:first_and]
print(user_id)
feature = user_and_feature[first_and+1:]
print(feature)
# Get user's latest reply
query = "SELECT * FROM replies WHERE user_id = '"+user_id+"' ORDER BY replies_id DESC LIMIT 1"
last_reply = db.executeQuery(query)[0] # type = dictionary
print(last_reply)
reply = last_reply['reply']
features = last_reply['choices']
status = int(last_reply['status']) ### remove this later
print(status)
if status == 0 :
# add feature 1 and change status
addition_replies_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [reply], \
"choices" : [features+'&'+feature], \
"time_stamp" : ['date'], \
"status" : [1]})
db.addTableFromDF('replies', addition_replies_df)
print('user : '******' | status = 0')
elif status == 1 :
addition_replies_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [reply], \
"choices" : [features+'&'+feature], \
"time_stamp" : ['date'], \
"status" : [2]})
db.addTableFromDF('replies', addition_replies_df)
print('user : '******' | status = 1')
elif status == 2 :
# All three features are here now, do the research
first_and = features[1:].find('&') # features = &feature1&feature2
feature1 = features[1:first_and]
feature2 = features[first_and+1:]
feature3 = feature
#### RECOMMENDATION PART #### # TODO
car_cols = []
for i in range(3):
car_cols.append(CarouselColumn(thumbnail_image_url='https://i.imgur.com/xHzymxe.gif',
title='課程推薦',
text= '第'+str(i+1)+'門',
actions=[
URIAction(
label='課程連結',
uri='https://github.com/Chung-Yun/Education-Platform'
)
]
))
carousel_template = CarouselTemplate(columns=car_cols)
course_proposal = TemplateSendMessage(alt_text='Carousel class proposal', template=carousel_template)
# Propose and ask for confirm
push_back_template(event,course_proposal)
confirm_template_message = TemplateSendMessage(
alt_text='Confirm template',
template=ConfirmTemplate(
text='結果滿意嗎?',
actions=[
PostbackAction(
label='滿意',
data='confirm_suggestion'
),
PostbackAction(
label='不滿意',
data='decline_suggestion'
)
]
)
)
push_back_template(event, confirm_template_message)
elif status > 2 :
push_back_message(event,'超過三個了！')
else:
push_back_message(event,'There is an error, pls contact our staff. Tks!')
elif event.postback.data == 'confirm_suggestion':
line_bot_api.reply_message(event.reply_token, TextSendMessage(text='結束'))
elif event.postback.data == 'decline_suggestion':
label = 'Python'
user_id = target = event.source.user_id
# status set to 0 again
register_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [label], \
"choices" : [''], \
"time_stamp" : ['date'], \
"status" : [0]})
db.addTableFromDF('replies', register_df)
line_bot_api.reply_message(event.reply_token, feature_proposal(event, label)) # back to this
def update_rfc_status(rfc_id, status):
database.executeQuery("u_rfcs_s_status_w_id", q="I", pars=(status, rfc_id))
def insert_rfc(rfc_name):
rfcs = getRFCs()
last_id = len(rfcs) + 1
r = database.executeQuery("i_rfcs", q="I", pars=(str(last_id), rfc_name, "0"))
def insert_config(name, ip, sysnr, client, user, passwd):
passwd.strip()
passwd = base64.encodestring(passwd)
r = database.executeQuery("i_server", q="I", pars=(name, ip, sysnr, client, user, passwd))
def getServers():
servers_data = database.executeQuery("s_server")
servers = []
for server in servers_data:
servers.append(server)
return servers
def handle_postback(event):
print(event.postback.data)
if event.postback.data == 'demande=learn_sth_else':
line_bot_api.reply_message(
event.reply_token,
TextSendMessage('請下指令： 買課幫手 我想學 [想學的東西]\n Example: 買課幫手 我想學 英文'))
elif event.postback.data.find('action') == 0:
# action=select_feature&'+str(event.source.user_id)+'&'+featurename
data = event.postback.data[len('action') + 1:]
print(data)
# Step 1: Registeration
if data.find('register_user') == 0:
# Interprete user_id and selected features
user_and_label = data[len('register_user') + 1:]
first_and = user_and_label.find('&')
user_id = user_and_label[:first_and]
label = user_and_label[first_and + 1:]
# Save to DB (status set to 0)
register_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [label], \
"choices" : [''], \
"time_stamp" : ['date'], \
"status" : [0]})
db.addTableFromDF('replies', register_df)
print('user registered to replies')
line_bot_api.reply_message(event.reply_token,
feature_proposal(event, label))
# Step 2: Choose features
elif data.find('select_feature') == 0:
# Interprete user_id and selected features
user_and_feature = data[len('select_feature') + 1:]
first_and = user_and_feature.find('&')
user_id = user_and_feature[:first_and]
feature = user_and_feature[first_and + 1:]
# Get user's latest reply
query = "SELECT * FROM replies WHERE user_id = '" + user_id + "' ORDER BY replies_id DESC LIMIT 1"
last_reply = db.executeQuery(query)[0] # type = dictionary
print(last_reply)
reply = last_reply['reply']
features = last_reply['choices']
status = int(last_reply['status']) ### remove this later
if status == 0:
# add feature 1 and change status
addition_replies_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [reply], \
"choices" : [features+'&'+feature], \
"time_stamp" : ['date'], \
"status" : [1]})
db.addTableFromDF('replies', addition_replies_df)
print('user : '******' | status = 0')
elif status == 1:
addition_replies_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [reply], \
"choices" : [features+'&'+feature], \
"time_stamp" : ['date'], \
"status" : [2]})
db.addTableFromDF('replies', addition_replies_df)
print('user : '******' | status = 1')
elif status == 2:
# All three features are here now, do the research
first_and = features[1:].find(
'&') # features = &feature1&feature2
feature1 = features[1:first_and]
feature2 = features[first_and + 1:]
feature3 = feature
push_back_message(
event,
feature1 + '、' + feature2 + '、' + feature3 + '⋯⋯稍等我一下')
#### RECOMMENDATION PART #### # TODO
#query = "SELECT * FROM tableThree;hon WHERE class_title = '"+courses[i]+"' LIMIT 1"
#class_info = db.executeQuery(query)[0] # type = dictionary
tableThree = db.fetchTable2Dataframe('tableThree')
#print(tableThree)
scores = []
for i in range(len(tableThree.index)):
score = 0
for j in range(len(tableThree.columns) - 1):
if tableThree.loc[i][j + 1] == feature1:
score += 1
elif tableThree.loc[i][j + 1] == feature2:
score += 1
elif tableThree.loc[i][j + 1] == feature3:
score += 1
scores.append(score)
# sorting
arr_data = np.array(scores)
idx = (-arr_data).argsort()[:10]
courses = []
for i in range(3):
courses.append(tableThree.loc[idx[i]][0])
# Presentation
car_cols = []
for i in range(3):
# Dig data from Table 2 (Python)
print(courses[i])
query = "SELECT * FROM Python WHERE class_title = '" + courses[
i] + "' LIMIT 1"
class_info = db.executeQuery(query)[0] # type = dictionary
class_url = class_info["class_url"]
class_figure = class_info["class_figure"]
stars = class_info["stars"]
teacher_name = class_info["teacher_name"]
price = class_info["price"]
car_cols.append(
CarouselColumn(
thumbnail_image_url=class_figure,
title='推薦課程' + str(i + 1) + '-' + courses[i],
text='老師：' + teacher_name + '/ 星數：' + stars +
'/ 價格：' + price,
actions=[URIAction(label='課程連結', uri=class_url)]))
carousel_template = CarouselTemplate(columns=car_cols)
course_proposal = TemplateSendMessage(
alt_text='課程推薦', template=carousel_template)
# Propose and ask for confirm
push_back_template(event, course_proposal)
confirm_template_message = TemplateSendMessage(
alt_text='Confirm template',
template=ConfirmTemplate(
text='結果滿意嗎?',
actions=[
PostbackAction(label='滿意',
data='confirm_suggestion'),
PostbackAction(label='不滿意',
data='decline_suggestion')
]))
push_back_template(event, confirm_template_message)
elif status > 2:
push_back_message(event, '超過三個了！')
else:
push_back_message(
event, 'There is an error, pls contact our staff. Tks!')
elif event.postback.data == 'confirm_suggestion':
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='結束'))
elif event.postback.data == 'decline_suggestion':
label = 'Python'
user_id = target = event.source.user_id
# status set to 0 again
register_df = pd.DataFrame({\
"user_id" : [user_id], \
"reply" : [label], \
"choices" : [''], \
"time_stamp" : ['date'], \
"status" : [0]})
db.addTableFromDF('replies', register_df)
line_bot_api.reply_message(event.reply_token,
feature_proposal(event,
label)) # back to this
def getSingleRFC(id):
rfcs_data = database.executeQuery("s_rfcs_w_id", pars=(id))
rfcs = []
for rfc in rfcs_data:
rfcs.append(rfc)
return rfcs[0]