def get(self):
param_parser = reqparse.RequestParser()
param_parser.add_argument('cid', type=str)
# Get cid
cid_param = param_parser.parse_args()['cid']
if not cid_param:
abort(400, message='Please provide `cid`')
# Make sql
sql = '''
SELECT * FROM fju_course WHERE course_code=:cid
'''
res = db.session.execute(text(sql), {'cid': cid_param})
rt = None
for row in res:
# Gen WHERE part of sql querying from BIG table `course`
sql_where = ''
sql_where += "name = '{}'".format(row['name'])
sql_where += " AND year = '108'"
# sql_where += " AND sid = '68'"
# tids = FJU_CourseDetail.get_fju_teacher_id(row['teacher'], row['department'])
# for i, tid in enumerate(tids):
# if i:
# sql_where += " OR tid = '{}'".format(tid)
# else:
# sql_where += " AND tid = '{}'".format(tid)
sql = 'SELECT * FROM fju_course_detail WHERE {}'.format(sql_where)
res_detail = db.session.execute(text(sql))
if res_detail.rowcount == 0:
rt = {
'description': '',
'system': '',
'link': '',
'student': '',
'lang': ''
}
else:
row_detail = res_detail.fetchone()
rt = {
'description': check_null(row_detail['description']),
'system': check_null(row_detail['system']),
'link': check_null(row_detail['link']),
'student': check_null(row_detail['student']),
'lang': check_null(row_detail['lang']),
'grade': check_null(row_detail['grade'])
}
return rt, 200
def post(self, stuID, cid):
if check_cid_exists(cid) == False:
abort(400, message='`cid` is not exist.')
uid = get_uid(stuID)
condit = [
'Quiz', 'MidExam', 'FinalExam', 'PersonalReport', 'GroupReport',
'OtherExam', 'OtherWork', 'lvExamAmount', 'lvFun', 'lvLearned',
'lvRequest', 'lvTeachlear', 'lvWork', 'lvRecommend', 'message'
]
param_parser = reqparse.RequestParser()
for i in condit:
param_parser.add_argument(i,
type=str,
help='Please provide `{}`'.format(i))
args = param_parser.parse_args()
# print(args)
res = db.session.execute(
text(
'SELECT name, teacher, department FROM fju_course WHERE course_code=:cid'
), {'cid': cid})
data = res.fetchone()
try:
db.session.execute(
text('''
INSERT INTO comment(uid, className, classOpen, teacher, createDate, Quiz, MidExam, FinalExam, PersonalReport, GroupReport, OtherExam, OtherWork, lvExamAmount, lvFun, lvLearned, lvRequest, lvTeachlear, lvWork, lvRecommend, message, source)
values(:uid, :className, :classOpen, :teacher, :createDate, :Quiz, :MidExam, :FinalExam, :PersonalReport, :GroupReport, :OtherExam, :OtherWork, :lvExamAmount, :lvFun, :lvLearned, :lvRequest, :lvTeachlear, :lvWork, :lvRecommend, :message, :source)
'''), {
'uid':
uid,
'className':
data['name'],
'classOpen':
data['department'],
'teacher':
data['teacher'],
'createDate':
datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
'Quiz':
check_null(args['Quiz']),
'MidExam':
check_null(args['MidExam']),
'FinalExam':
check_null(args['FinalExam']),
'PersonalReport':
check_null(args['PersonalReport']),
'GroupReport':
check_null(args['GroupReport']),
'OtherExam':
check_null(args['OtherExam']),
'OtherWork':
check_null(args['OtherWork']),
'lvExamAmount':
check_null(args['lvExamAmount']),
'lvFun':
check_null(args['lvFun']),
'lvLearned':
check_null(args['lvLearned']),
'lvRequest':
check_null(args['lvRequest']),
'lvTeachlear':
check_null(args['lvTeachlear']),
'lvWork':
check_null(args['lvWork']),
'lvRecommend':
check_null(args['lvRecommend']),
'message':
check_null(args['message']),
'source':
check_null('rish'),
})
except exc.SQLAlchemyError as e:
print(e)
abort(500, message='Internal Server Error (Go to see the log)')
db.session.commit()
commentID = get_recent_commentID(uid, data['name'], data['department'],
data['teacher'])
return {
'message': 'Success to add a comment',
'commentID': commentID
}, 200
def get(self):
param = {}
if 'stuID' in request.args:
stuID = request.args['stuID']
param.update(uid=get_uid(stuID))
if 'teacher' in request.args:
param.update(teacher=request.args['teacher'])
if 'coursename' in request.args:
param.update(className=request.args['coursename'])
if 'cid' in request.args:
res = db.session.execute(
text(
'SELECT name, teacher, department FROM fju_course WHERE course_code=:cid'
), {'cid': request.args['cid']})
if res.rowcount == 0:
return {'message': 'Course_code is not exist'}, 404
for row in res:
if row['name']:
param.update(className=row['name'])
if row['teacher']:
param.update(teacher=row['teacher'])
# diff between comment and fju_course in department column
# if row['department']:
# param.update(classOpen=row['department'])
res = Comment.get_comment_data(param)
if res.rowcount == 0:
abort(404, message='Cannot Found the comment data.')
# Collect the comments
res_list = []
for row in res:
user = models.get_user(row['uid'])
dic = {
'commentID': check_null(row['commentID']),
'stuID': check_null(user['username']),
'nickname': check_null(user['nickname']),
'classOpen': check_null(row['classOpen']),
'className': check_null(row['className']),
'teacher': check_null(row['teacher']),
'createDate': check_null(str(row['createDate'])),
'Quiz': check_null(row['Quiz']),
'MidExam': check_null(row['MidExam']),
'FinalExam': check_null(row['FinalExam']),
'PersonalReport': check_null(row['PersonalReport']),
'GroupReport': check_null(row['GroupReport']),
'OtherExam': check_null(row['OtherExam']),
'OtherWork': check_null(row['OtherWork']),
'lvExamAmount': check_null(row['lvExamAmount']),
'lvFun': check_null(row['lvFun']),
'lvLearned': check_null(row['lvLearned']),
'lvRequest': check_null(row['lvRequest']),
'lvTeachlear': check_null(row['lvTeachlear']),
'lvWork': check_null(row['lvWork']),
'lvRecommend': check_null(row['lvRecommend']),
'message': check_null(row['message']),
'source': check_null(row['source']),
'link': check_null(row['link'])
}
res_list.append(dic)
return res_list, 200
#if you want a specific function
from utils import check_null
# if you want all the functions
from utils import *
check_null("x")
check_length("X")
def get(self, stuID, weekday, timeperiod):
idx = ['', '2', '3']
period = ['D0', 'D1', 'D2', 'D3', 'D4', 'DN', 'D5',
'D6', 'D7', 'D8', 'E0', 'E1', 'E2', 'E3', 'E4']
day = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
day_num2eng = ['', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
timelist = [[0]*15 for i in range(7)]
res = db.session.execute(text('SELECT uid FROM `user` WHERE username=:user'), {'user': stuID})
uid = res.fetchone()[0]
space_time = Auto_course_insert.get_free_period(uid, timelist)
# CoursePeriod
ALL_PERIOD = ['D0', 'D1', 'D2', 'D3', 'D4', 'DN', 'D5', 'D6', 'D7', 'D8', 'E0', 'E1', 'E2', 'E3', 'E4']
PERIOD2IDX = {'D0' : 0, 'D1' : 1, 'D2' : 2, 'D3' : 3, 'D4' : 4, 'DN' : 5, 'D5' : 6, 'D6' : 7, 'D7' : 8, 'D8' : 9, 'E0' : 10, 'E1' : 11, 'E2' : 12, 'E3' : 13, 'E4' : 14}
data = Auto_course_insert.load_schedule_data_if_not_expired(uid)
if not data:
data = {
'Mon': {},
'Tue': {},
'Wed': {},
'Thu': {},
'Fri': {},
'Sat': {},
'Sun': {}
}
print('[*] debug: Collecting pickable courses')
for i in range(0, 7):
for j in space_time[i]:
sql = 'SELECT * FROM fju_course WHERE '
sql += "day='{0}' OR day2='{0}' OR day3='{0}'".format(day[i])
res = db.session.execute(text(sql))
# print(res.rowcount)
time = j.split('-')
time = CoursePeriod(*time)
# print(time)
candi = []
for row in res:
# Check period of a course if necessary
p1 = make_CoursePeriod(row['period']) if row['period'] else None
p2 = make_CoursePeriod(row['period2']) if row['period2'] else None
p3 = make_CoursePeriod(row['period3']) if row['period3'] else None
plist = [p1, p2, p3]
succ = True
for p in plist:
if p and p not in time:
# print(p)
succ = False
if succ:
candi.append({
'course_code': check_null(row['course_code']),
'name' : check_null(row['name']),
'teacher' : check_null(row['teacher']),
'department' : check_null(row['department']),
'score' : check_null(row['score']),
'kind' : check_null(row['kind']),
'times' : check_null(row['times']),
'day' : check_null(row['day']),
'week' : check_null(row['week']),
'period' : check_null(row['period']),
'classroom' : check_null(row['classroom']),
'day2' : check_null(row['day2']),
'week2' : check_null(row['week2']),
'period2' : check_null(row['period2']),
'classroom2' : check_null(row['classroom2']),
'day3' : check_null(row['day3']),
'week3' : check_null(row['week3']),
'period3' : check_null(row['period3']),
'classroom3' : check_null(row['classroom3'])
})
data[day[i]].update({j : candi})
# end for row
# end for j
#end for i
# Updates the data
db.session.execute(text('''
UPDATE user SET schedule_data=:schedule_data, schedule_data_time=:schedule_data_time WHERE uid=:uid
'''), {'schedule_data': json.dumps(data), 'schedule_data_time': utils.time_now(), 'uid': uid})
db.session.commit()
else:
print('[*] debug: deserialize schedule_data')
weekday = day_num2eng[weekday]
tlist = []
for i in data[weekday]: # Turn period into CoursePeriod
tlist.append(CoursePeriod(*(i.split('-'))))
cur_time = CoursePeriod(timeperiod, timeperiod) # e.g. D4 = D4-D4
res_list = None
# See if it exists a CoursePeriod which includes cur_time
for t in tlist:
if cur_time in t:
k = str(t)
res_list = data[weekday][k]
break
return res_list, 200
def get(self):
args = FJU_course_list.param_parser.parse_args()
params = {}
condit = ['course_code', 'name', 'teacher', 'department']
not_query = ['day', 'period', 'include', 'kind']
select_no_query_opt = False
condit += not_query # for checking
selected_condit = []
sql_where = ''
# Check if it provides more than one param
is_none_data = True
for p in condit:
if args[p] != None:
is_none_data = False
break
if is_none_data:
page_num = args['page'] if args['page'] else 0
res = db.session.execute(
text('SELECt * FROM fju_course LIMIT :page, 20'),
{'page': page_num})
items = []
for row in res:
items.append({
'course_code': check_null(row['course_code']),
'name': check_null(row['name']),
'teacher': check_null(row['teacher']),
'department': check_null(row['department']),
'score': check_null(row['score']),
'kind': check_null(row['kind']),
'times': check_null(row['times']),
'day': check_null(row['day']),
'week': check_null(row['week']),
'period': check_null(row['period']),
'classroom': check_null(row['classroom']),
'day2': check_null(row['day2']),
'week2': check_null(row['week2']),
'period2': check_null(row['period2']),
'classroom2': check_null(row['classroom2']),
'day3': check_null(row['day3']),
'week3': check_null(row['week3']),
'period3': check_null(row['period3']),
'classroom3': check_null(row['classroom3']),
})
return items, 200
# Prepare the params depends on the condit
for p in condit:
if p in args and args[p] != None:
selected_condit.append(p)
if p not in not_query:
params[p] = args[p] + '%'
# Prepare weekday part of SQL WHERE
weekday = None
weekday_arg = args[
'day'] if 'day' in selected_condit and 'day' in args else None
if weekday_arg:
if not re.match(r'^[1-8]{1,8}$', weekday_arg):
return {'message': '`day` must be numbers'}, 400
weekday = convert_weekday2list(weekday_arg)
first = True
for i in range(1, len(weekday)):
if weekday[i]:
if not first:
sql_where += ' OR '
sql_where += "day='{0}' OR day2='{0}' OR day3='{0}'".format(
weekday_num_to_eng(i))
first = False
sql_where = '({})'.format(sql_where)
select_no_query_opt = True
# Prepare time (D?-D?)
# Notice: TIME_NONE means empty time and None means it didn't pass time option in
if 'period' in selected_condit:
if args['period'] == 'None':
time = TIME_NONE
sql_where = '' # ignore the weekday
sql_where += "(period='' AND period2='' AND period3='')"
else:
time = args['period'].split('-')
time = CoursePeriod(*time)
else:
time = None
#
inc_flag = args['include']
# Category of a course
kind_opt = ['必', '選', '通', '輔']
kind_selected = []
kind = args['kind'] if 'kind' in args else None
if kind:
for c in kind:
if c in kind_opt:
kind_selected.append(c)
## make sql
if select_no_query_opt:
sql_where += ' AND '
sql_where += '('
for idx, opt in enumerate(kind_selected):
tmp = 'kind=\'{}\''.format(opt)
if idx != 0:
sql_where += ' OR '
sql_where += tmp
sql_where += ')'
print(sql_where)
select_no_query_opt = True
# Make SQL by the given condition
selected_condit = [x for x in selected_condit if x not in not_query
] # remove not_query from condit
for idx, c in enumerate(selected_condit):
if idx == 0:
if select_no_query_opt:
sql_where += ' AND '
sql_where += '{0} LIKE :{0}'.format(c)
else:
sql_where += ' AND {0} LIKE :{0}'.format(c)
# if no option was chosen
if sql_where == '':
sql_where = '1'
sql = 'SELECT * FROM fju_course WHERE {}'.format(sql_where)
print('[*] SQL = {}'.format(sql_where))
res = db.session.execute(text(sql), params)
# Pack the results
items = []
for row in res:
# Check period of a course if necessary
if time and time != TIME_NONE:
p1 = make_CoursePeriod(
row['period']) if row['period'] else None
p2 = make_CoursePeriod(
row['period2']) if row['period2'] else None
p3 = make_CoursePeriod(
row['period3']) if row['period3'] else None
plist = [p1, p2, p3]
succ = False
for p in plist:
if inc_flag: # include
if p and p in time:
succ = True
else: # not include
if p and p == time:
succ = True
if not succ:
continue
#
items.append({
'course_code': check_null(row['course_code']),
'name': check_null(row['name']),
'teacher': check_null(row['teacher']),
'department': check_null(row['department']),
'score': check_null(row['score']),
'kind': check_null(row['kind']),
'times': check_null(row['times']),
'day': check_null(row['day']),
'week': check_null(row['week']),
'period': check_null(row['period']),
'classroom': check_null(row['classroom']),
'day2': check_null(row['day2']),
'week2': check_null(row['week2']),
'period2': check_null(row['period2']),
'classroom2': check_null(row['classroom2']),
'day3': check_null(row['day3']),
'week3': check_null(row['week3']),
'period3': check_null(row['period3']),
'classroom3': check_null(row['classroom3']),
})
return items, 200
def get(self):
args = parse_filter.parse_args()
paremeter = []
condition = []
idx = [
'cid', 'year', 'name', 'teacher', 'semester', 'department',
'college', 'grade', 'school'
]
# Null parameter
none_data = True
for i in idx:
if args[i] != None:
none_data = False
break
if none_data == True:
return {"message": "You need to provide above one parameter"}, 400
# ready for search
for i in idx:
if i == 'school':
condition.append(get_school_id(args[i]))
paremeter.append('sid')
elif i == 'teacher':
# Find the teacher id
res = get_teacher_id(args[i])
condition.append(res)
paremeter.append('tid')
else:
condition.append(args[i])
paremeter.append(i)
# 將有給條件的都拿去SQL搜尋，只能一個一個用AND接起
# 符合teacher的tid 都 return
flag = 0
search_condition = ''
for i in range(0, len(condition)):
if paremeter[i] == 'tid':
if len(condition[i]) != 0:
if flag == 0:
search_condition += 'WHERE ('
for j in range(0, len(condition[i])):
if j == len(condition[i]) - 1:
search_condition += str(paremeter[i]) + \
'=' + '\'' + str(condition[i][j]) + '\''
else:
search_condition += str(paremeter[i]) + \
'=' + '\'' + str(condition[i][j]) + '\'' + ' OR '
search_condition += ')'
flag = 1
else:
search_condition += 'AND'
search_condition += '('
for j in range(0, len(condition[i])):
if j == len(condition[i]) - 1:
search_condition += str(paremeter[i]) + \
'=' + '\'' + str(condition[i][j]) + '\''
else:
search_condition += str(paremeter[i]) + \
'=' + '\'' + \
str(condition[i][j]) + '\'' + ' OR '
search_condition += ')'
else:
search_condition += 'WHERE (' + 'tid' + \
'=' + '\'' + '' + '\'' + ')'
flag = 1
elif condition[i] != None:
if flag == 0:
search_condition += 'WHERE ' + str(paremeter[i]) + \
'=' + '\'' + str(condition[i]) + '\''
flag = 1
else:
search_condition += 'AND ' + \
str(paremeter[i]) + '=' + \
'\'' + str(condition[i]) + '\''
search_condition = 'SELECT * FROM course ' + search_condition
res = db.session.execute(text(search_condition))
print(search_condition)
if res.rowcount == 0:
return {"message": "NOT FOUND"}, 400
items = []
for row in res:
items.append({
'cid': check_null(str(row['cid'])),
'year': check_null(str(row['year'])),
'semester': check_null(str(row['semester'])),
'name': check_null(str(row['name'])),
'teacher': check_null(str(row['tid'])),
'school': check_null(str(row['sid'])),
'college': check_null(str(row['college'])),
'grade': check_null(str(row['grade'])),
'department': check_null(str(row['department'])),
'score': check_null(str(row['score'])),
'description': check_null(str(row['description'])),
'link': check_null(str(row['link'])),
'system': check_null(str(row['system'])),
'subject': check_null(str(row['subject'])),
'required': check_null(str(row['required'])),
'student': check_null(str(row['student'])),
'lang': check_null(str(row['lang']))
})
return items, 200