def get(self):
userId = request.args.get("userId")
practiceCount = db.getOne(
"""
select practice_count from User where user_id=%s;
""", (userId,)
)['practice_count']
if practiceCount < 5:
page = 0
res = db.getAll(
"""
select * from StudentPractice where student_id=%s;
""", (userId,)
)
else:
page = practiceCount/5+1 if practiceCount%5 else practiceCount/5
res = db.getSome(
"""
select * from StudentPractice where student_id=%s;
""", 5, (userId,)
)
respData = {
"state": "success",
"count": practiceCount if practiceCount<5 else 5,
"page": page,
"detail": res
}
return respData
def post(self):
data = request.get_json() or request.form
userId = data['userId']
title = data['title']
problemList = data['problems']
relation = data['relation']
topicId = data['topicId']
sectionId = data['sectionId']
courseId = data['courseId']
practiceId = get_id()
db.insertOne(
"""
insert into Practice (practice_id, title, teacher_id, relation, topic_id, section_id, course_id)
values (%s, %s, %s, %s, %s, %s, %s);
""", (practiceId, title, userId, relation, topicId, sectionId, courseId)
)
position = 0
for eachp in problemList:
problemId = get_id()
content = eachp['content']
choices = eachp['choices']
answer = eachp['answer']
db.insertOne(
"""
insert into Problem (problem_id, position, content, choices, answer, practice_id)
values (%s, %s, %s, %s, %s, %s);
""", (problemId, position, content, choices, answer, practiceId)
)
position += 1
studentList = db.getAll(
"""
select student_id from StudnetCourse where course_id=%s;
""", (courseId)
)
for each in studentList:
db.insertOne(
"""
insert into StudentPractice (student_id, practice_id, status)
values (%s, %s, %s);
""", (each['student_id'], practiceId, 0)
)
db.modify(
"""
update User set practice_count=practice_count+1 where user_id=%s;
""", (each['student_id'],)
)
return {
"state": "success"
}
def get(self):
# data = request.args.get("userId")
userId = request.args.get("userId")
character = request.args.get("character")
# Query Statements
res = db.getOne(
"""
select course_count from User where user_id=%s;
""",
(userId,)
)['course_count']
if res:
if res <= 5:
page = 0
courseRes = db.getAll(
"""
select * from Course where teacher_id=%s;
""",
(userId,)
)
courseCount = res
else:
page = res/5+1 if res%5 else res/5
courseRes = db.getSome(
"""
select * from Course where teacher_id=%s;
""",
5, (userId,)
)
courseCount = 5
courseDetail = []
for i in range(0, courseCount):
courseDetail.append({
"title": courseRes[i]['title'],
"count": [courseRes[i]['topic_count'], courseRes[i]['section_count'], courseRes[i]['practice_count'], courseRes[i]['student_count']]
})
respData = {
"state": "success",
"count": courseCount,
"page": page,
"courses": courseDetail
}
return respData
def delete(self):
topicId = request.args.get("topicId")
courseId = request.args.get("courseId")
res = db.delete(
"""
delete from Topic where topic_id=%s;
""",
(topicId,)
)
if res:
sectionIds = db.getAll(
"""
select section_id from Section where topic_id=%s;
""",
(topicId,)
)
cnt = len(sectionIds)
db.delete(
"""
delete from Section where topic_id=%s;
""",
(topicId,)
)
for i in range(0, cnt):
db.delete(
"""
delete from Entity where section_id=%s;
""",
(sectionIds[i]['section_id'],)
)
db.modify(
"""
update Course set topic_count=topic_count-1, section_count=section_count-%s;
"""
)
return {
"state": "success"
}
def get(self):
practiceId = data['practiceId']
userId = data['userId']
draft = db.getOne(
"""
select * from StudentPractice where student_id=%s and practice_id=%s;
""", (userId, practiceId)
)
problemSet = db.getAll(
"""
select * from Problem where practice_id=%s;
""", (practiceId,)
)
respData = {
"state": "success",
"problemSet": problemSet,
"draft": draft
}
return respData
def get(self):
userId = request.args.get("userId")
character = request.args.get("character")
# Query Statements
res = db.getOne(
"""
select course_count from User where user_id=%s;
""",
(userId,)
)['course_count']
if res:
if res <= 5:
page = 0
# courseRes = db.getAll(
# """
# select * from Course where course_id in (
# select course_id from StudentCourse where student_id=%s
# );
# """,
# (userId,)
# )
# User JOIN instead of Sub-query --> faster
courseRes = db.getAll(
"""
select a.* from Course a inner join StudentCourse b
on a.course_id=b.course_id
where b.student_id=%s;
""",
(userId,)
)
courseCount = res
else:
page = res/5+1 if res%5 else res/5
courseRes = db.getSome(
"""
select a.*, b.status, b.last_section_id from Course a inner join StudentCourse b
on a.course_id=b.course_id
where b.student_id=%s;
""",
5, (userId,)
)
courseCount = 5
courseDetail = []
for i in range(0, courseCount):
courseId = course[i]['course_id']
teacherId = course[i]['teacher_id']
teacherRes = db.getOne(
"""
select name, icon from User where user_id=%s;
""",
(teacherId,)
)
if courseRes[i]['section_id'] != '':
sectionTitle = db.getOne(
"""
select title from Section where section_id=%s;
""",
(courseRes[i]['section_id'],)
)
else:
sectionTitle = ''
courseDetail.append({
"title": courseRes[i]['title'],
"description": courseRes[i]['description'],
"id": courseRes[i]['course_id'],
"teacher": {
"icon": teacherRes['icon']
"name": teacherRes['name']
},
"last": {
"title": sectionTitle,
"id": courseRes[i]['section_id']
}
})
respData = {
"state": "success",
"count": courseCount,
"page": page,
"courses": courseDetail
}
return respData