def post(self):
data = request.get_json() or request.form
courseId = int(data['courseId'])
title = data['title']
db = Mysql()
topicid = get_id()
db.insertOne(
"""
insert into Topic (`topic_id`, `title`, `course_id`, `section_count`) values(%s, %s, %s, %s);
""",(topicid, title, courseId, 0)
)
db.modify(
"""
update Course set topic_count=topic_count+1 where course_id=%s;
""", (courseId,)
)
db.close()
return {
"state": "success",
"topic_id": str(topicid)
}
def get(self):
topicId = int(request.args.get("topicId"))
db = Mysql()
title = db.getOne(
"""
select title from Topic where topic_id=%s;
""", (topicId, ))
if title:
db.close()
return title
def get(self):
courseId = int(request.args.get("courseId"))
db = Mysql()
title = db.getOne(
"""
select title from Course where course_id=%s;
""", (courseId, ))
if title:
db.close()
return title
def post(self):
"""
POST /api/createCourse
"""
# Deal with request.data
data = request.get_json() or request.form
userId = int(data['userId'])
print(userId)
courseTitle = data['courseTitle']
courseDescription = data['courseDescription']
# topicCount = data['topics']['count']
# topics = data['topics']['details']
# Generate course_id
courseId = get_id()
respData = {}
db = Mysql()
# SQL Statements
db.insertOne(
"""
insert into Course (`course_id`, `title`, `description`, `teacher_id`, `is_valid`)
values
(%s, %s, %s, %s, %s);
""", (courseId, courseTitle, courseDescription, userId, 0))
res = db.getOne(
"""
select * from Course where course_id=%s;
""", (courseId, ))
res = db.getOne(
"""
select course_count from User where user_id=%s;
""", (userId, ))
if res:
courseCount = res['course_count'] + 1
db.modify(
"""
update User set course_count=%s where user_id=%s;
""", (courseCount, userId))
respData = {"status": True, "course_id": str(courseId), "data": res}
db.close()
return respData
def get(self):
# data = request.args.get("userId")
userId = request.args.get("userId")
# character = request.args.get("character")
db = Mysql()
# 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({
"id": str(courseRes[i]["course_id"]),
"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
}
db.close()
return respData
def post(self):
data = request.get_json() or request.form
username = data['username']
password = data['password']
db = Mysql()
res = db.getOne(
"""
select password from User where school_id=%s;
""", (username, ))
if (res == False):
db.close()
return {"state": False, "reason": "User Not Found."}
elif password == res['password']:
d = db.getOne(
"""
select * from User where school_id=%s;
""", (username, ))
# print(d["user_id"])
db.close()
return {
"state": True,
"reason": "Success!",
"data": {
"username": username,
"character": d['character'],
"uid": str(d["user_id"])
# "origindata": d
}
}
else:
db.close()
return {"state": False, "reason": "Wrong User/Password."}
#!/bin/python
'''
return the args type information as xml format:
for example:
<vms>
<vm>
<id></id>
<name></name>
</vm>
<vm>...</vm>
.
.
.
</vms>
'''
from dbop import Mysql
vms_xml = '<?xml version="1.0" encoding="utf-8"?>\n'
vms_xml += '<vms>\n'
sql_conn = Mysql()
sql_conn.connect()
data = sql_conn.query("select * from vmtb")
for x in data:
vms_xml+= '<vm>\n'
vms_xml+= '<id>'+str(x[0])+'</id>\n'
vms_xml+= '<name>'+str(x[1])+'</name>\n'
vms_xml+= '</vm>\n'
vms_xml+='</vms>'
sql_conn.close()
print vms_xml
#!/bin/python
'''
return the args type information as xml format:
for example:
<argtypes>
<types>
<id></id>
<name></name>
</types>
<types>...</types>
.
.
.
</argtypes>
'''
from dbop import Mysql
types_xml = '<?xml version="1.0" encoding="utf-8"?>\n'
types_xml += '<argtypes>\n'
sql_conn = Mysql()
sql_conn.connect()
data = sql_conn.query("select * from argtypestb")
for x in data:
types_xml+= '<types>\n'
types_xml+= '<id>'+str(x[0])+'</id>\n'
types_xml+= '<name>'+str(x[1])+'</name>\n'
types_xml+= '</types>\n'
types_xml+='</argtypes>'
sql_conn.close()
print types_xml
def delete(self):
topicId = int(request.args.get("topicId"))
courseId = int(request.args.get("courseId"))
db = Mysql()
# Delete Topic itself
db.delete(
"""
delete from Topic where topic_id=%s;
""",(topicId,)
)
db.modify(
"""
update Practice set relation=3 where topic_id=%s and relation=1;
""", (topicId,)
)
# Get Section list and delete
sectionIds = db.getAll(
"""
select section_id as id from Section where topic_id=%s;
""",(topicId,)
)
if sectionIds:
cnt = len(sectionIds)
# Delete all sections related with the topic
db.delete(
"""
delete from Section where topic_id=%s;
""",(topicId,)
)
# Delete the entities related with sections
for section in sectionIds:
id = section["id"]
db.delete(
"""
delete from Entity where section_id=%s;
""",(id,)
)
# Untie the relationship of practice
for section in sectionIds:
id = section["id"]
db.modify(
"""
update Practice set relation=3 where section_id=%s and relation=2;
""", (id,)
)
# Delete the learning process related with deleted sections
pd = db.getAll(
"""
select `student_id`,`process_detail` where course_id=%s;
""", (courseId,)
)
if pd:
for each in pd:
process = eval(each["process_detail"])
for section in sectionIds:
process.pop(str(section["id"]))
db.modify(
"""
update StudentCourse set process=detail=%s where student_id=%s and course_id=%s;
""", (str(process),each["student_id"],courseId)
)
else :
cnt = 0
db.modify(
"""
update Course set topic_count=topic_count-1, section_count=section_count-%s where course_id=%s;
""", (cnt, courseId)
)
db.close()
return {
"state": "success"
}
def get(self):
print ("Get the Request!")
userId = request.args.get("userId")
# character = request.args.get("character")
db = Mysql()
# 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.*, 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;
""",
(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):
print (courseRes[i])
courseId = courseRes[i]['course_id']
teacherId = courseRes[i]['teacher_id']
teacherRes = db.getOne(
"""
select name, icon from User where user_id=%s;
""",
(teacherId,)
)
if courseRes[i]['last_section_id'] is not None:
sectionTitle = db.getOne(
"""
select title from Section where section_id=%s;
""",
(courseRes[i]['last_section_id'],)
)["title"]
else:
sectionTitle = ''
courseDetail.append({
"title": courseRes[i]['title'],
"desc": courseRes[i]['description'],
"id": str(courseRes[i]['course_id']),
"teacher": teacherRes["name"],
"lastProgress": {
"title": sectionTitle,
"id": str(courseRes[i]['last_section_id'])
}
})
respData = {
"state": "success",
"count": courseCount,
"page": page,
"courses": courseDetail
}
db.close()
return respData
def get(self):
courseId = int(request.args.get("courseId"))
studentId = int(request.args.get("studentId"))
db = Mysql()
res = db.getOne(
"""
select * from Course where course_id=%s;
""", (courseId,)
)
d = db.getOne(
"""
select process_detail from StudentCourse where course_id=%s and student_id=%s;
""", (courseId, studentId)
)
t = db.getOne(
"""
select name from User where user_id=%s;
""", (res["teacher_id"])
)
processDetail = eval(d["process_detail"])
resp = {}
if res:
resp = {
"title": res["title"],
"desc": res["description"],
"teacher": t["name"]
}
if res["topic_count"] != 0:
topics = db.getAll(
"""
select * from Topic where course_id=%s;
""",(courseId,)
)
if topics:
for topic in topics:
sections = []
s = db.getAll(
"""
select * from Section where topic_id=%s;
""",(topic["topic_id"],)
)
print (s)
if s:
for section in s:
section["section_id"] = str(section["section_id"])
section["topic_id"] = str(section["topic_id"])
section["process"] = processDetail[section["section_id"]]
sections = s
else:
sections = []
topic["sections"] = sections
topic["course_id"] = str(topic["course_id"])
topic["topic_id"] = str(topic["topic_id"])
else:
topics = []
resp["topics"] = topics
resp["id"] = str(courseId)
db.close()
return resp
