def get_dis_class_by_type(dis_type):
if dis_type == '1':
table_name = 'dis_behavior'
if dis_type == '2':
table_name = 'dis_item'
cur = yield database.pool.execute(query=("SELECT * FROM `mee_stu_mgr`.%s;" % table_name))
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def get_dis_class_by_type(dis_type):
if dis_type == '1':
table_name = 'dis_behavior'
if dis_type == '2':
table_name = 'dis_item'
cur = yield database.pool.execute(
query=("SELECT * FROM `mee_stu_mgr`.%s;" % table_name))
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def get_dis(info):
sql_str = """SELECT DISTINCT
apartment_discipline.id AS id,
student_info.stu_name AS stu_name,
student_info.stu_sex AS stu_sex,
student_info.stu_id AS stu_id,
student_room.room_name AS stu_room,
apartment_discipline.dis_type AS dis_type,
apartment_discipline.dis_class AS dis_class,
apartment_discipline.dis_date AS dis_date,
apartment_discipline.others AS others,
IF(dis_type = 1,
dis_behavior.dis_class_name,
dis_item.dis_class_name) AS dis_class
FROM
apartment_discipline,
student_info,
student_room,
dis_behavior,
dis_item
WHERE
(apartment_discipline.stu_id = student_info.stu_id)
AND (student_info.stu_room = student_room.id)
AND IF(apartment_discipline.dis_type = 1,
(apartment_discipline.dis_class = dis_behavior.id),
(apartment_discipline.dis_class = dis_item.id))"""
sql_list = []
if info['stu_sex'] != "":
sql_str += " AND (`student_info`.`stu_sex` = %s)"
sql_list.append(info['stu_sex'])
if info['stu_id'] != "":
sql_str += ' AND (`student_info`.`stu_id` LIKE "%s%%%%") ' % info[
'stu_id']
if info['stu_room'] != "":
sql_str += " AND (`student_info`.`stu_room` = %s)"
sql_list.append(info['stu_room'])
if info['dis_type'] != "":
sql_str += " AND (`apartment_discipline`.`dis_type` = %s)"
sql_list.append(info['dis_type'])
if info['dis_class'] != "":
sql_str += " AND (`apartment_discipline`.`dis_class` = %s)"
sql_list.append(info['dis_class'])
sql_str += "ORDER BY `apartment_discipline`.`id`;"
sql_tuple = tuple(sql_list)
cur = yield database.pool.execute(sql_str, params=sql_tuple)
rs = database.cur_to_dict(cur)
for i in rs:
i['dis_date'] = "%s" % i['dis_date']
raise gen.Return(rs)
def get_dis(info):
sql_str = """SELECT DISTINCT
apartment_discipline.id AS id,
student_info.stu_name AS stu_name,
student_info.stu_sex AS stu_sex,
student_info.stu_id AS stu_id,
student_room.room_name AS stu_room,
apartment_discipline.dis_type AS dis_type,
apartment_discipline.dis_class AS dis_class,
apartment_discipline.dis_date AS dis_date,
apartment_discipline.others AS others,
IF(dis_type = 1,
dis_behavior.dis_class_name,
dis_item.dis_class_name) AS dis_class
FROM
apartment_discipline,
student_info,
student_room,
dis_behavior,
dis_item
WHERE
(apartment_discipline.stu_id = student_info.stu_id)
AND (student_info.stu_room = student_room.id)
AND IF(apartment_discipline.dis_type = 1,
(apartment_discipline.dis_class = dis_behavior.id),
(apartment_discipline.dis_class = dis_item.id))"""
sql_list = []
if info['stu_sex'] != "":
sql_str += " AND (`student_info`.`stu_sex` = %s)"
sql_list.append(info['stu_sex'])
if info['stu_id'] != "":
sql_str += ' AND (`student_info`.`stu_id` LIKE "%s%%%%") ' % info['stu_id']
if info['stu_room'] != "":
sql_str += " AND (`student_info`.`stu_room` = %s)"
sql_list.append(info['stu_room'])
if info['dis_type'] != "":
sql_str += " AND (`apartment_discipline`.`dis_type` = %s)"
sql_list.append(info['dis_type'])
if info['dis_class'] != "":
sql_str += " AND (`apartment_discipline`.`dis_class` = %s)"
sql_list.append(info['dis_class'])
sql_str += "ORDER BY `apartment_discipline`.`id`;"
sql_tuple = tuple(sql_list)
cur = yield database.pool.execute(sql_str, params=sql_tuple)
rs = database.cur_to_dict(cur)
for i in rs:
i['dis_date'] = "%s" % i['dis_date']
raise gen.Return(rs)
def insert_dis(dis_data):
"""
dis_data = {
"stu_id": stu_id,
"type": dis_type,
"dis_class": dis_2,
"others": others
}
"""
cur = yield database.pool.execute(
"""INSERT INTO apartment_discipline (`dis_type`, `dis_class`, `others`, `stu_id`, `dis_date`) VALUES (%s, %s, %s,%s,%s);"""
, (dis_data['type'], dis_data['dis_class'], dis_data['others'], dis_data['stu_id'], dis_data['dis_date']))
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def insert_dis(dis_data):
"""
dis_data = {
"stu_id": stu_id,
"type": dis_type,
"dis_class": dis_2,
"others": others
}
"""
cur = yield database.pool.execute(
"""INSERT INTO apartment_discipline (`dis_type`, `dis_class`, `others`, `stu_id`, `dis_date`) VALUES (%s, %s, %s,%s,%s);""",
(dis_data['type'], dis_data['dis_class'], dis_data['others'],
dis_data['stu_id'], dis_data['dis_date']))
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def get_stu(stu_info):
"""
:param stu_info:{
"stu_id": int(stu_id),
"stu_name": stu_name,
"stu_sex": stu_sex,
"stu_nation": stu_nation,
"stu_tel": stu_tel,
"stu_cid": stu_cid,
"stu_adr": stu_adr,
"stu_room": int(stu_room),
"stu_class": int(stu_class),
"stu_parent_name": stu_parent_name,
"stu_parent_relation": stu_parent_relation,
"stu_parent_tel": stu_parent_tel,
"stu_parent_name_vice": stu_parent_name_vice,
"stu_parent_relation_vice": stu_parent_relation_vice,
"stu_parent_tel_vice": stu_parent_tel_vice
"0" means no filter
}
:return: res: raised res of student
"""
sql_str = 'SELECT stu_name,stu_id,stu_sex,room_name AS stu_room,stu_room AS stu_room_id,stu_class AS stu_class_id,stu_cid,stu_tel, stu_nation, stu_adr_id ,stu_adr,class_name AS stu_class , stu_parent_name , stu_parent_relation , stu_parent_tel,stu_parent_name_vice ,stu_parent_relation_vice,stu_parent_tel_vice FROM `student_info` , `student_class`,`student_room` WHERE student_class.id = student_info.stu_class AND student_room.id = student_info.stu_room'
for it in stu_info:
if stu_info[it] != "0":
sql_str += " AND (`"
sql_str += it
sql_str += '` LIKE "'
sql_str += "%s" % stu_info[it]
if it == 'stu_room' or it == 'stu_class':
sql_str += '")'
else:
sql_str += '%")'
sql_str += " ORDER BY stu_id;"
cur = yield database.pool.execute(sql_str)
res = database.cur_to_dict(cur)
raise gen.Return(res)
def get_stu(stu_info):
"""
:param stu_info:{
"stu_id": int(stu_id),
"stu_name": stu_name,
"stu_sex": stu_sex,
"stu_nation": stu_nation,
"stu_tel": stu_tel,
"stu_cid": stu_cid,
"stu_adr": stu_adr,
"stu_room": int(stu_room),
"stu_class": int(stu_class),
"stu_parent_name": stu_parent_name,
"stu_parent_relation": stu_parent_relation,
"stu_parent_tel": stu_parent_tel,
"stu_parent_name_vice": stu_parent_name_vice,
"stu_parent_relation_vice": stu_parent_relation_vice,
"stu_parent_tel_vice": stu_parent_tel_vice
"0" means no filter
}
:return: res: raised res of student
"""
sql_str = 'SELECT stu_name,stu_id,stu_sex,room_name AS stu_room,stu_room AS stu_room_id,stu_class AS stu_class_id,stu_cid,stu_tel, stu_nation, stu_adr_id ,stu_adr,class_name AS stu_class , stu_parent_name , stu_parent_relation , stu_parent_tel,stu_parent_name_vice ,stu_parent_relation_vice,stu_parent_tel_vice FROM `student_info` , `student_class`,`student_room` WHERE student_class.id = student_info.stu_class AND student_room.id = student_info.stu_room'
for it in stu_info:
if stu_info[it] != "0":
sql_str += " AND (`"
sql_str += it
sql_str += '` LIKE "'
sql_str += "%s" % stu_info[it]
if it == 'stu_room' or it == 'stu_class':
sql_str += '")'
else:
sql_str += '%")'
sql_str += " ORDER BY stu_id;"
cur = yield database.pool.execute(sql_str)
res = database.cur_to_dict(cur)
raise gen.Return(res)
def get_room(info):
sql_str = "SELECT room_name FROM student_room WHERE id IN (SELECT DISTINCT stu_room FROM student_info WHERE 1=1)"
sql_list = []
if info['stu_sex'] != 0:
sql_str += " and stu_sex = %s"
sql_list.append(info['stu_sex'] == 1 and '男' or '女')
if info['stu_id'] != 0:
sql_str += " and stu_id = %s"
sql_list.append(info['stu_id'])
if info['dis_type'] != 0:
sql_str += " and dis_type = %s"
sql_list.append(info['dis_type'])
if info['dis_class'] != 0:
sql_str += " and dis_class = %s"
sql_list.append(info['dis_class'])
sql_str += ";"
sql_tuple = tuple(sql_list)
cur = yield database.pool.execute(sql_str, params=sql_tuple)
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def get_room(info):
sql_str = "SELECT room_name FROM student_room WHERE id IN (SELECT DISTINCT stu_room FROM student_info WHERE 1=1)"
sql_list = []
if info['stu_sex'] != 0:
sql_str += " and stu_sex = %s"
sql_list.append(info['stu_sex'] == 1 and '男' or '女')
if info['stu_id'] != 0:
sql_str += " and stu_id = %s"
sql_list.append(info['stu_id'])
if info['dis_type'] != 0:
sql_str += " and dis_type = %s"
sql_list.append(info['dis_type'])
if info['dis_class'] != 0:
sql_str += " and dis_class = %s"
sql_list.append(info['dis_class'])
sql_str += ";"
sql_tuple = tuple(sql_list)
cur = yield database.pool.execute(sql_str, params=sql_tuple)
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def add_room(stu_room):
cur = yield database.pool.execute(
"INSERT INTO student_room (`room_name`) VALUE (%s);SELECT @@IDENTITY AS id;",
stu_room)
res = database.cur_to_dict(cur)
raise gen.Return(res)
def check_user(user, pwd):
cur = yield database.pool.execute(
"SELECT admin_pwd=md5(sha(%s)) AS check_flag, admin_name , admin_id AS id FROM admin WHERE admin_id=%s;",
(pwd, user))
rs = database.cur_to_dict(cur)
raise gen.Return(rs)
def add_room(stu_room):
cur = yield database.pool.execute("INSERT INTO student_room (`room_name`) VALUE (%s);SELECT @@IDENTITY AS id;",
stu_room)
res = database.cur_to_dict(cur)
raise gen.Return(res)