from admin.dboper import *

# 查询所有学生（前台显示）
def loadAllStudent_Show ():
    sql = "select SID, SNameCN, SNameEN, UniversityNameCN, UniversityNameEN, SType, SImage from StudentInfo where SIsShow=1 and SStatus=1 order by SType, SOrder"
    results = mySQL_Query(sql)
    return results

# 查询一个学生
def loadStudentByID_Show (Sid):
    sql = "select SID, SNameCN, SNameEN, UniversityNameCN, UniversityNameEN, MajorNameCN, MajorNameEN, SType, SYear, Email, SImage, SIntroduce, SResearchwork from Studentinfo where SID=%s" % Sid
    results = mySQL_Query(sql)
    if len(results) > 0: results = results[0]
    return results


# 查询所有学生
def loadAllStudent ():
    sql = "select * from StudentInfo"
    results = mySQL_Query(sql)
    return results


# 查询学生信息
def loadStudentInfo ():
    sql = "Select * From StudentInfo "  # where Studentstatus=1
    results = mySQL_Query(sql)
    return results


# 根据学生编号查询学生信息
def loadStudentInfoByID (Sid):
    sql = "Select * From StudentInfo where SID=" + Sid
    results = mySQL_Query(sql)
    return results


# 添加一个学生
# SID, SNameCN, SNameEN, UniversityNameCN, UniversityNameEN, MajorNameCN, MajorNameEN, SType, SYear, Email, SImage, SIntroduce, SResearchwork, AuthorID, SIsShow, SOrder, SStatus
def add_StudentInfo (data):
    sql = "insert into StudentInfo values (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', NULL, 1, 1, 1) " % (
    data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9], data[10],data[11])
    i, msg = mySQL_Execute(sql)
    return i, msg


# 更新一个学生
# SNameCN, SNameEN, UniversityNameCN, UniversityNameEN, MajorNameCN, MajorNameEN, SType, SYear, Email, SImage, SIntroduce, SResearchwork
def update_Student (data):
    sql = "update Studentinfo set SNameCN='%s', SNameEN='%s', UniversityNameCN='%s', UniversityNameEN='%s', MajorNameCN='%s', MajorNameEN='%s', SType='%s', SYear='%s', Email='%s', SImage='%s', SIntroduce='%s', SResearchwork='%s' where SID=%s" % (
    data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9], data[10], data[11], data[12])
    i, msg = mySQL_Execute(sql)
    return i, sql


# 查询一个学生
def loadStudentByID (Sid):
    sql = "select * from Studentinfo where SID=%s" % Sid
    results = mySQL_Query(sql)
    if len(results) > 0: results = results[0]
    return results


# 删除一个学生
def del_Student (Sid):
    sql = "delete from Studentinfo where SID =%s" % Sid
    i, msg = mySQL_Execute(sql)
    return i, msg