def get(self):
userId = int(request.args.get("userId"))
courseId = int(request.args.get("courseId"))
db = Mysql()
res = db.getOne(
"""
select process_detail from StudentCourse where course_id=%s and student_id=%s;
""", (courseId, userId))["process_detail"]
d = []
if res:
res = eval(res)
for key, values in res.items():
sectionId = int(key)
status = values
section = db.getOne(
"""
select * from Section where section_id=%s;
""", (sectionId))
d.append({
"id": key,
"title": section["title"],
"entity": {
"type": section["entity_type"],
},
"status": status
})
return d
def get(self):
courseId = int(request.args.get("courseId"))
db = Mysql()
# Get Student List
d = db.getAll(
"""
select student_id from StudentCourse where course_id=%s;
""", (courseId,)
)
students = []
i = 0
if not d:
return students
for each in d:
studentId = db.getOne(
"""
select school_id from User where user_id=%s;
""", (each["student_id"],)
)["school_id"]
students.append({"number": i,"uid":str(studentId)})
i += 1
return students
def post(self):
data = request.get_json() or request.form
uid = int(data["uid"])
practiceId = int(data["practiceId"])
p = data["problems"]
db = Mysql()
# Get correct answer list
d = db.getAll(
"""
select problem_id as id, answer, choices from Problem where practice_id=%s;
""", (practiceId, ))
cl = {}
for problem in d:
problem["choices"] = json.loads(problem["choices"])
# value not index
cl[problem["id"]] = problem["choices"].index(problem["answer"])
# Handle format of post data & save draft into a dict
draft = {}
for problem in p:
pid = int(problem["id"])
problem["status"] = 2
problem["correctAnswer"] = cl[pid]
draft[pid] = problem["draft"]
db.modify(
"""
update StudentPractice set status=2,draft=%s where student_id=%s and practice_id=%s;
""", (str(draft), uid, practiceId))
return p
def get(self):
teacherId = int(request.args.get("uid"))
db = Mysql()
cl = db.getAll(
"""
select * from Course where teacher_id=%s;
""", (teacherId,)
)
if not cl:
return {
"success": True,
"courses": []
}
courses = []
for course in cl:
data = {
"value": str(course["course_id"]),
"title": course["title"]
}
courses.append(data)
return {
"success": True,
"courses": courses
}
def get(self):
courseId = int(request.args.get("courseId"))
db = Mysql()
tl = db.getAll(
"""
select * from Topic where course_id=%s;
""", (courseId,)
)
if not tl:
return {
"success": True,
"topics": []
}
topics = []
for topic in tl:
data = {
"value": str(topic["topic_id"]),
"title": topic["title"]
}
topics.append(data)
return {
"success": True,
"topics": topics
}
def get(self):
topicId = int(request.args.get("topicId"))
db = Mysql()
sl = db.getAll(
"""
select * from Section where topic_id=%s;
""", (topicId,)
)
if not sl:
return {
"success": True,
"sections": []
}
sections = []
for section in sl:
data = {
"value": str(section["section_id"]),
"title": section["title"]
}
sections.append(data)
return {
"success": True,
"sections": sections
}
def post(self):
data = request.get_json() or request.form
courseId = int(data["courseId"])
sectionId = data["sectionId"]
uid = int(data["uid"])
db = Mysql()
# Get current process
curProcess = db.getOne(
"""
select process_detail from StudentCourse where student_id=%s and course_id=%s;
""", (uid,courseId)
)["process_detail"]
if not curProcess:
curProcess = {}
elif curProcess:
curProcess = eval(curProcess)
# Change the Status
curProcess[sectionId] = 1
db.modify(
"""
update StudentCourse set process_detail=%s,last_section_id=%s where student_id=%s and course_id=%s;
""", (str(curProcess),sectionId,uid,courseId)
)
return {"success": True};
def get(self):
sectionId = int(request.args.get("sectionId"))
db = Mysql()
# Get section info and related entity
s = db.getOne(
"""
select * from Section where section_id=%s;
""", (sectionId,)
)
entityId = s["entity_id"]
e = db.getOne(
"""
select * from Entity where entity_id=%s;
""", (entityId,)
)
respData = {}
respData["sectionTitle"] = s["title"]
respData["entity"] = {}
if s["entity_type"] == 0:
# video
respData["entity"]["entityType"] = "video"
respData["entity"]["url"] = e["url"]
elif s["entity_type"] == 1:
# doc
respData["entity"]["entityType"] = "doc"
respData["entity"]["url"] = e["url"]
respData["entity"]["content"] = e["content"]
return respData
def get(self):
practiceId = request.args.get('practiceId')
uid = request.args.get("uid")
db = Mysql()
draft = db.getOne(
"""
select * from StudentPractice where student_id=%s and practice_id=%s;
""", (uid, practiceId)
)
problemSet = db.getAll(
"""
select problem_id as id, position, content, choices from Problem where practice_id=%s;
""", (practiceId,)
)
status = draft["status"]
# problemSet["status"] = status
if status == 0:
for problem in problemSet:
problem["status"] = 0
problem["choices"] = json.loads(problem["choices"])
problem["id"] = str(problem["id"])
elif status == 1:
d = eval(draft["draft"])
for problem in problemSet:
id = problem["id"]
problem["status"] = 1
problem["choices"] = json.loads(problem["choices"])
problem["draft"] = d[id]
problem["id"] = str(problem["id"])
elif status == 2:
d = eval(draft["draft"])
for problem in problemSet:
id = problem["id"]
problem["status"] = 2
problem["choices"] = json.loads(problem["choices"])
problem["draft"] = d[id]
answer = db.getOne(
"""
select answer from Problem where problem_id=%s;
""", (problem["id"],)
)["answer"]
problem["correctAnswer"] = problem["choices"].index(answer)
problem["id"] = str(problem["id"])
respData = {
"problemSet":problemSet,
"status": status
}
return respData
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 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 put(self):
data = request.get_json() or request.form
topicId = data['topicId']
title = data['topicTitle']
db = Mysql()
db.modify(
"""
update Topic set title=%s where topic_id=%s;
""", (title, topicId))
return {"success": True}
def delete(self):
problemId = int(request.args.get("problemId"))
db = Mysql()
# Get practice
practiceId = db.getOne(
"""
select practice_id from Problem where problem_id=%s;
""", (problemId,)
)["practice_id"]
# Delete Problem
db.delete(
"""
delete from Problem where problem_id=%s;
""", (problemId,)
)
# Reset Status of Practice
sl = db.getAll(
"""
select student_id from StudentPractice where practice_id=%s;
""", (practiceId,)
)
if sl:
for student in sl:
id = student["student_id"]
db.modify(
"""
update StudentPractice set status=0 where student_id=%s and practice_id=%s;
""", (id,practiceId)
)
p = db.getAll(
"""
select * from Problem where practice_id=%s;
""", (practiceId,)
)
pl = []
if p:
for each in p:
ch = json.loads(each["choices"])
d = {
"id": str(each["problem_id"]),
"problem": each["content"],
"choices": [{"cid": i, "value": ch[i]} for i in range(0, len(ch))],
"correctAnswer": each["answer"]
}
pl.append(d)
return {
"success": True,
"problemList": pl
}
def get(self):
uid = int(request.args.get("uid"))
db = Mysql()
p = db.getAll(
"""
select * from Practice where teacher_id=%s;
""", (uid, ))
pl = []
for each in p:
# format relation string
r = each["relation"]
if r == 0:
relation = db.getOne(
"""
select title from Course where course_id=%s;
""", (each["course_id"], ))["title"]
elif r == 1:
courseTitle = db.getOne(
"""
select title from Course where course_id=%s;
""", (each["course_id"], ))["title"]
topicTitle = db.getOne(
"""
select title from Topic where topic_id=%s;
""", (each["topic_id"], ))["title"]
relation = "{} / {}".format(courseTitle, topicTitle)
elif r == 2:
courseTitle = db.getOne(
"""
select title from Course where course_id=%s;
""", (each["course_id"], ))["title"]
topicTitle = db.getOne(
"""
select title from Topic where topic_id=%s;
""", (each["topic_id"], ))["title"]
sectionTitle = db.getOne(
"""
select title from Section where section_id=%s;
""", (each["section_id"], ))["title"]
relation = "{} / {} / {}".format(courseTitle, topicTitle,
sectionTitle)
else:
relation = ""
d = {
"title": each["title"],
"relation": relation,
"id": str(each["practice_id"])
}
pl.append(d)
return {"success": True, "practiceList": pl}
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):
courseId = request.args.get("courseId")
studentId = request.args.get("studentId")
db = Mysql()
d = db.getOne(
"""
select process_detail from StudentCourse where course_id=%s and student_id=%s;
""", (courseId, studentId))
processDetail = eval(d["process_detail"])
return {"process": processDetail}
def get(self):
practiceId = int(request.args.get("practiceId"))
db = Mysql()
d = db.getOne(
"""
select * from Practice where practice_id=%s;
""", (practiceId, ))
print(d)
d["practice_id"] = str(d["practice_id"])
d["teacher_id"] = str(d["teacher_id"])
relation = d["relation"]
if relation == 0:
courseTitle = db.getOne(
"""
select title from Course where course_id=%s;
""", (d["course_id"], ))["title"]
d["relation"] = courseTitle
d["course_id"] = str(d["course_id"])
elif relation == 1:
courseTitle = db.getOne(
"""
select title from Course where course_id=%s;
""", (d["course_id"], ))["title"]
topicTitle = db.getOne(
"""
select title from Topic where topic_id=%s;
""", (d["topic_id"], ))["title"]
d["relation"] = "{} / {}".format(courseTitle, topicTitle)
d["course_id"] = str(d["course_id"])
d["topic_id"] = str(d["topic_id"])
elif relation == 2:
courseTitle = db.getOne(
"""
select title from Course where course_id=%s;
""", (d["course_id"], ))["title"]
topicTitle = db.getOne(
"""
select title from Topic where topic_id=%s;
""", (d["topic_id"], ))["title"]
sectionTitle = db.getOne(
"""
select title from Section where section_id=%s;
""", (d["section_id"], ))["title"]
d["relation"] = "{} / {} / {}".format(courseTitle, topicTitle,
sectionTitle)
d["course_id"] = str(d["course_id"])
d["topic_id"] = str(d["topic_id"])
d["section_id"] = str(d["section_id"])
return d
def put(self):
data = request.get_json() or request.form
courseId = int(data['courseId'])
title = data['title']
desc = data['desc']
db = Mysql()
db.modify(
"""
update Course set title=%s, description=%s where course_id=%s;
""", (title, desc, courseId))
return {"state": "success"}
def put(self):
data = request.get_json() or request.form
practiceId = int(data["practiceId"])
title = data["title"]
db = Mysql()
db.modify(
"""
update Practice set title=%s where practice_id=%s;
""", (title, practiceId))
# Relation modify here
return {"success": True}
def get(self):
userId = int(request.args.get("userId"))
courseId = int(request.args.get("courseId"))
db = Mysql()
res = db.getOne(
"""
select process_detail from StudentCourse where course_id=%s and student_id=%s;
""", (courseId, userId)
)["process_detail"]
sl = db.getAll(
"""
select * from Section where topic_id in (select topic_id from Topic where course_id=%s);
""", (courseId,)
)
d = []
if sl:
res = eval(res)
for section in sl:
sectionId = section["section_id"]
status = res[str(sectionId)]
d.append({
"id": str(sectionId),
"title": section["title"],
"entity": {
"type": section["entity_type"]
},
"status": status
})
# for key, values in res.items():
# sectionId = int(key)
# status = values
#
# section = db.getOne(
# """
# select * from Section where section_id=%s;
# """, (sectionId)
# )
# d.append({
# "id": key,
# "title": section["title"],
# "entity": {
# "type": section["entity_type"],
# },
# "status": status
# })
return d
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 post(self):
data = request.get_json() or request.form
practiceId = int(data["practiceId"])
position = data["index"]
content = data["content"]
choices = data["choices"]
answer = data["answer"]
problemId = get_id()
db = Mysql()
# Handle choice list -> json str
choices = json.dumps(choices)
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)
)
# Get Students list and reset the status of practice
sl = db.getAll(
"""
select student_id from StudentPractice where practice_id=%s;
""", (practiceId,)
)
if sl:
# Has student
for student in sl:
id = student["student_id"]
db.modify(
"""
update StudentPractice set status=0 where practice_id=%s and student_id=%s;
""", (practiceId, id)
)
p = db.getAll(
"""
select * from Problem where practice_id=%s;
""", (practiceId,)
)
pl = []
for each in p:
ch = json.loads(each["choices"])
d = {
"id": str(each["problem_id"]),
"problem": each["content"],
"choices": [{"cid": i, "value": ch[i]} for i in range(0, len(ch))],
"correctAnswer": each["answer"]
}
pl.append(d)
return {
"success": True,
"problemList": pl
}
def put(self):
data = request.get_json() or request.form
practiceId = int(data["practiceId"])
problemId = int(data["problemId"])
position = data["index"]
content = data["content"]
choices = data["choices"]
answer = data["answer"]
db = Mysql()
# Edit Problem
choices = json.dumps(choices)
db.modify(
"""
update Problem set position=%s,content=%s,choices=%s,answer=%s where problem_id=%s;
""", (position,content,choices,answer,problemId)
)
# Reset Status of Practice
sl = db.getAll(
"""
select student_id from StudentPractice where practice_id=%s;
""", (practiceId,)
)
if sl:
for student in sl:
id = student["student_id"]
db.modify(
"""
update StudentPractice set status=0 where student_id=%s and practice_id=%s;
""", (id,practiceId)
)
p = db.getAll(
"""
select * from Problem where practice_id=%s;
""", (practiceId,)
)
pl = []
for each in p:
ch = json.loads(each["choices"])
d = {
"id": str(each["problem_id"]),
"problem": each["content"],
"choices": [{"cid": i, "value": ch[i]} for i in range(0, len(ch))],
"correctAnswer": each["answer"]
}
pl.append(d)
return {
"success": True,
"problemList": pl
}
def post(self):
data = request.get_json() or request.form
uid = int(data["uid"])
practiceId = int(data["practiceId"])
p = data["problems"]
db = Mysql()
draft = {}
for problem in p:
pid = int(problem["id"])
draft[pid] = problem["draft"]
db.modify(
"""
update StudentPractice set status=1,draft=%s where student_id=%s and practice_id=%s;
""", (str(draft), uid, practiceId))
return {"success": True}
def get(self):
courseId = int(request.args.get("courseId"))
db = Mysql()
# Get Course Info
d = db.getOne(
"""
select `title`,`description` from Course where course_id=%s;
""", (courseId,)
)
t = db.getAll(
"""
select topic_id as id, title as topicTitle from Topic where course_id=%s;
""", (courseId,)
)
if t:
for topic in t:
sl = db.getAll(
"""
select section_id as id, title as sectionTitle, entity_type as entityType from Section where topic_id=%s;
""", (topic["id"],)
)
if sl:
for section in sl:
section["id"] = str(section["id"])
else:
sl = []
topic["sections"] = sl
topic["id"] = str(topic["id"])
else :
t = []
respData = {
"id": str(courseId),
"title": d["title"],
"desc": d["description"],
"courseDetail": t
}
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."}
def get(self):
practiceId = int(request.args.get("practiceId"))
db = Mysql()
p = db.getOne(
"""
select * from Practice where practice_id=%s;
""", (practiceId, ))
problems = db.getAll(
"""
select * from Problem where practice_id=%s;
""", (practiceId, ))
pl = []
if problems:
# Has Problems
for each in problems:
choices = json.loads(each["choices"])
d = {
"id":
str(each["problem_id"]),
"problem":
each["content"],
"choices": [{
"cid": i,
"value": choices[i]
} for i in range(0, len(choices))],
"correctAnswer":
each["answer"]
}
pl.append(d)
else:
# No Problems
pl = []
return {"success": True, "title": p["title"], "problemList": pl}
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 get(self):
sectionId = int(request.args.get("sectionId"))
studentId = int(request.args.get("uid"))
db = Mysql()
res = db.getOne(
"""
select * from Section where section_id=%s;
""", (sectionId,)
)
if res:
entityId = res["entity_id"]
topicId = res["topic_id"]
entity = db.getOne(
"""
select * from Entity where entity_id=%s;
""", (entityId,)
)
courseId = db.getOne(
"""
select course_id from Topic where topic_id=%s;
""", (topicId,)
)["course_id"]
process = db.getOne(
"""
select process_detail from StudentCourse where course_id=%s and student_id=%s;
""", (courseId,studentId)
)["process_detail"]
process = eval(process)
status = process[str(sectionId)]
return {
"id": str(res["section_id"]),
"relatedCourse": str(courseId),
"relatedTopic": str(topicId),
"entity": {
"id": str(entityId),
"type": entity["entity_type"],
"url": entity["url"],
"content": entity["content"]
},
"status": status
}
def post(self):
data = request.get_json() or request.form
userId = int(data["userId"])
title = data["title"]
db = Mysql()
practiceId = get_id()
db.insertOne(
"""
insert into Practice (`practice_id`,`title`,`teacher_id`) values (%s,%s,%s);
""", (practiceId, title, userId))
db.modify(
"""
update User set practice_count=practice_count+1 where user_id=%s;
""", (userId, ))
return {"state": "success", "id": str(practiceId)}
#!/bin/python
from dbop import Mysql
conn = Mysql()
conn.connect()
conn.insertsvtb('sv1','2','192.168.1.1','8090','http://asdfasdfasd')
<type_name></type_name>
</argtype>
<argtype>...</argtype>
<argtypes>
</sv>
<sv>...</sv>
.
.
.
</svs>
"""
from dbop import Mysql
sv_xml = '<?xml version="1.0" encoding="utf-8"?>\n'
sv_xml += "<svs>\n"
sql_conn = Mysql()
sql_conn.connect()
sql = "select svtb.sv_id,sv_name,svargtb.type_id,arg_index,typename from svtb,svargtb,argtypestb where svtb.sv_id = svargtb.sv_id and svargtb.type_id = argtypestb.type_id order by sv_id,arg_index;"
data = sql_conn.query(sql)
old_sv_id = -1
for x in data:
sv_id = x[0]
if sv_id != old_sv_id:
if old_sv_id != -1:
sv_xml += "</argtypes>\n"
sv_xml += "</sv>\n"
sv_xml += "<sv>\n"
sv_xml += "<id>" + str(x[0]) + "</id>\n"
sv_xml += "<name>" + str(x[1]) + "</name>\n"
sv_xml += "<argtypes>\n"
old_sv_id = sv_id
#!/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