from django.shortcuts import render,reverse,redirect
from django.db import connection

# Create your views here.

def get_cursor():
    return connection.cursor()

# 主站首页
def index(request):
    return render(request,'index.html')

# 管理员首页
def admin_index(request):
    return render(request, 'adminstrator/admin.html')

# 统计信息页面
def tables(request):
    return render(request,'adminstrator/tables.html')

# 教师学时及工作量
def table1(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from Term_Teacher_Workload_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/1.html',context={'rows':rows})

# 学生学时及学分
def table2(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from term_student_credit_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/2.html',context={'rows':rows})

# 学年课程成绩
def table3(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_courserascore_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/3.html',context={'rows':rows})

# 单位不同学年课程成绩
def table4(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_courserascore_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/4.html',context={'rows':rows})

# 前10 名学生成绩
def table5(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("call N5_4('2017-2018','下学期')")
        rows = cursor.fetchall()
        # print(rows)
        return render(request,'adminstrator/all_tables/5.html',context={'rows':rows})


# 学生。。。。。
def table6(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_credit_view")
        rows = cursor.fetchall()
        # print(rows)
        return render(request,'adminstrator/all_tables/6.html',context={'rows':rows})


# 班级管理
def find_class(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from classinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_class.html', context={'rows':rows})
    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from classinfo where classid='%s'"%id)
            return redirect(reverse('findclass'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            major = request.POST.get("major")
            size = request.POST.get("size")

            cursor = get_cursor()
            cursor.execute("update classinfo set classname='%s',majorid='%s',classsize='%s' where classid='%s'" % (
            name, major, size, id))
            return redirect(reverse('findclass'))

# 添加班级
def add_class(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        major = request.POST.get("major")
        size = request.POST.get("size")
        cursor = get_cursor()
        try:
            cursor.execute("insert into classinfo (classid,classname,majorid,classsize) values ("
                           "'%s','%s','%s','%s')"%(id,name,major,size))
        except Exception as e:
            return render(request, 'adminstrator/add_class.html',context={'tip':"添加班级失败！"})

        return render(request,'adminstrator/add_class.html',context={'tip':"添加班级成功！"})
    else:
        return render(request,'adminstrator/add_class.html')


# 学生信息
def find_student(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from studentinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_student.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from studentinfo where studentid='%s'"%id)
            return redirect(reverse('findstudent'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            gender = request.POST.get("gender")
            age = request.POST.get("age")
            classname = request.POST.get("classname")

            cursor = get_cursor()
            cursor.execute("update studentinfo set studentname='%s',gender='%s',age='%s',classname='%s' where studentid='%s'"%(name,gender,age,classname,id))
            return redirect(reverse('findstudent'))


# 添加学生
def add_student(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        gender = request.POST.get("gender")
        age = request.POST.get("age")
        classname = request.POST.get("class")

        cursor = get_cursor()
        try:
            cursor.execute("insert into studentinfo (studentid,studentname,gender,age,classname) values ("
                           "'%s','%s','%s','%s','%s')"%(id,name,gender,age,classname))
        except Exception as e:
            return render(request, 'adminstrator/add_student.html',context={'tip':"添加学生失败！"})

        return render(request,'adminstrator/add_student.html',context={'tip':"添加学生成功！"})
    else:
        return render(request,'adminstrator/add_student.html')



# 课程信息
def find_coursera(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from courserainfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_coursera.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from courserainfo where courseraid='%s'"%id)
            return redirect(reverse('findcoursera'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            credit = request.POST.get("credit")
            period = request.POST.get("period")

            cursor = get_cursor()
            cursor.execute("update courserainfo set courseraname='%s',credit='%s',period='%s' where courseraid='%s'"%(name,credit,period,id))
            return redirect(reverse('findcoursera'))

# 添加课程
def add_coursera(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        credit = request.POST.get("credit")
        time = request.POST.get("time")
        cursor = get_cursor()
        try:
            cursor.execute("insert into courserainfo (courseraid,courseraname,credit,period) values ("
                           "'%s','%s','%s','%s')"%(id,name,credit,time))
        except Exception as e:
            return render(request, 'adminstrator/add_coursera.html',context={'tip':"添加课程失败！"})

        return render(request,'adminstrator/add_coursera.html',context={'tip':"添加课程成功！"})
    else:
        return render(request,'adminstrator/add_coursera.html')



# 教师信息
def find_teacher(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from teacherinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_teacher.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from teacherinfo where teacherid='%s'"%id)
            return redirect(reverse('findteacher'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            gender = request.POST.get("gender")
            age = request.POST.get("age")

            cursor = get_cursor()
            cursor.execute("update teacherinfo set teachername='%s',gender='%s',age='%s' where teacherid='%s'"%(name,gender,age,id))
            return redirect(reverse('findteacher'))

# 添加教师
def add_teacher(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        gender = request.POST.get("gender")
        age = request.POST.get("age")
        cursor = get_cursor()
        try:
            cursor.execute("insert into teacherinfo (teacherid,teachername,gender,age) values ("
                           "'%s','%s','%s','%s')"%(id,name,gender,age))
        except Exception as e:
            print(e)
            return render(request, 'adminstrator/add_teacher.html',context={'tip':"添加教师失败！"})

        return render(request,'adminstrator/add_teacher.html',context={'tip':"添加教师成功！"})
    else:
        return render(request,'adminstrator/add_teacher.html')


# 教师个人信息修改
def presonal_info_edit(request):
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        unit = request.POST.get('unit')
        adyear = request.POST.get('adyear')
        id = request.POST.get('id')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        cursor = get_cursor()
        cursor.execute("update teacherprivateinfo,teacherinfo set "
                       "teacherinfo.teachername='%s',teacherinfo.gender='%s',"
                       "teacherinfo.age='%s',teacherprivateinfo.workunit='%s',"
                       "teacherprivateinfo.adyear='%s',teacherprivateinfo.phonenumber='%s',"
                       "teacherprivateinfo.qqnumber='%s',teacherprivateinfo.idnumber='%s',"
                       "teacherprivateinfo.abcnumber='%s',teacherprivateinfo.ssnumber='%s'"
                       "where teacherinfo.teacherid='%s'"
                       %(name,gender,age,unit,adyear,phone,qq,idcard,abc,ssn,id))
        # 更新数据后再次查询，返回结果
        cursor.execute("SELECT * from teacherinfo_view WHERE TeacherID = '%s'" % (id))
        row = cursor.fetchone()
        return render(request, 'adminstrator/find_teacher.html', context={'row': row})




#————————————————我是学生————————————————
# 我是学生首页
def student_index(request):
    return render(request,'student/student.html')

# 学生登陆
def stu_login(request):
    if request.method == "POST":
        id = request.POST.get('id')
        password = request.POST.get('password')
        cursor = get_cursor()
        cursor.execute("select studentid,studentname from studentinfo where studentid = '%s'" % id)
        id_name = cursor.fetchone()

        if id_name:
            if id_name[0] == password:

                request.session['id'] = id_name[0]
                request.session['name'] = id_name[1]

                return redirect(reverse('student'))

            else:
                return render(request, 'student/stu_login.html', context={'tip': "密码错误！"})
        else:
            return render(request, 'student/stu_login.html', context={'tip': "账号不存在！"})

    else:
        return render(request,'student/stu_login.html')


# 学生选课
def select_course(request):
    if request.method == 'GET':
        cursor = get_cursor()

        # 选择所有可选课程
        cursor.execute("select courserainfo.courseraname,teachername,period,credit,specificcourseraid from "
                       "specificcourserainfo,courserainfo,teacherinfo where "
                       "specificcourserainfo.courseraid=courserainfo.courseraid and "
                       "specificcourserainfo.teacherid=teacherinfo.teacherid")
        rows = cursor.fetchall()
        print(rows[1])
        return render(request, 'student/select_course.html', context={'rows': rows})
    else:
        stu_id = request.session['id']
        c_id = request.POST.get('cour_id')
        print("_____")
        print(c_id)
        cursor = get_cursor()
        try:
            cursor.execute(
                "insert into choosecourserainfo (studentid,specificcourseraid) values ('%s','%s')" % (stu_id, c_id))
        except Exception as e:
            print("e")
        return redirect(reverse('select'))


# 我的课程
def my_course(request):
    stu_id = request.session['id']
    cursor = get_cursor()
    cursor.execute("select courserainfo.courseraname,teachername,period,credit from "
                       "choosecourserainfo,specificcourserainfo,courserainfo,teacherinfo where "
                       "choosecourserainfo.studentid='%s' and "
                       "choosecourserainfo.specificcourseraid=specificcourserainfo.specificcourseraid and "
                   "specificcourserainfo.teacherid=teacherinfo.teacherid and "
                   "specificcourserainfo.courseraid=courserainfo.courseraid"%stu_id)
    rows=cursor.fetchall()
    return render(request, 'student/my_course.html', context={'rows':rows})

# 我的成绩
def my_grade(request):
    cursor = get_cursor()
    cursor.execute("select courseraname,credit,year,term,ptimescore,finalscore,totalscore,scorerank "
                   "from student_coursera_view where studentid='%s'"%request.session['id'])
    rows = cursor.fetchall()
    return render(request, 'student/my_grade.html',context={'rows':rows})

# 我的信息
def student_info(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select studentinfo.studentid,studentname,gender,age,classname,adyear,phonenumber,qqnumber,idnumber,abcnumber,ssnumber "
                       "from studentinfo,studentprivateinfo where studentinfo.studentid='%s'" % request.session['id'])
        row = cursor.fetchone()
        return render(request,'student/student_info.html',context={'row':row})
    else:
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        session_id = request.session['id']
        cursor = get_cursor()
        cursor.execute(
            "update studentinfo,studentprivateinfo set studentinfo.studentname='%s',studentinfo.gender='%s',"
            "studentinfo.age='%s',studentprivateinfo.phonenumber='%s',studentprivateinfo.qqnumber='%s',"
            "studentprivateinfo.idnumber='%s',studentprivateinfo.abcnumber='%s',studentprivateinfo.ssnumber='%s' "
            "where studentinfo.studentid='%s' and studentprivateinfo.studentid='%s'" % (
               name,gender,age,phone,qq,idcard,abc,ssn,session_id,session_id))

        return redirect(reverse('student_info'))


# ---------------我是老师---------------
# 我是老师首页
def teacher_index(request):
    return render(request,'teacher/teacher.html')

# 教师登陆
def teacher_login(request):
    if request.method == "POST":
        id = request.POST.get('id')
        password = request.POST.get('password')
        cursor = get_cursor()
        cursor.execute("select teacherid,teachername from teacherinfo where teacherid = '%s'" % id)
        id_name = cursor.fetchone()

        if id_name:
            if id_name[0] == password:

                request.session['id'] = id_name[0]
                request.session['name'] = id_name[1]

                return redirect(reverse('teacher'))

            else:
                return render(request, 'teacher/teacher_login.html', context={'tip': "密码错误！"})
        else:
            return render(request, 'teacher/teacher_login.html', context={'tip': "账号不存在！"})

    else:
        return render(request,'teacher/teacher_login.html')


#教师选课
def tea_select_course(request):
    if request.method == 'GET':
        cursor = get_cursor()
        # 选择所有可选课程
        cursor.execute("select courseraid,courseraname,credit,period,termid from courserainfo")
        rows = cursor.fetchall()
        return render(request, 'teacher/tea_select_course.html', context={'rows': rows})
    else:
        tea_id = request.session['id']
        c_id = request.POST.get('cour_id')
        c_name = request.POST.get('cour_name')
        cursor = get_cursor()
        cursor.execute(
            "insert into specificcourserainfo (specificcourseraid,courseraid,courseraname,teacherid) values ('%s','%s','%s','%s')" % (
                c_id+'3',c_id, c_name, tea_id))
        #
        # try:
        #     cursor.execute(
        #         "insert into specificcourserainfo (courseraid,courseraname,teacherid) values ('%s','%s','%s')" % (c_id, c_name,tea_id))
        # except Exception as e:
        #     print("e")
        return redirect(reverse('tea_select'))

# 教师的课程管理
def tea_my_course(request):
    if request.method == 'GET':
        teacher_id = request.session['id']
        cursor = get_cursor()
        # 查询所有课程
        cursor.execute("select courserainfo.courseraname,courserainfo.courseraid,period,credit from "
                   "specificcourserainfo,courserainfo where "
                   "specificcourserainfo.teacherid='%s'and "
                   "specificcourserainfo.courseraid=courserainfo.courseraid"%teacher_id)
        rows = cursor.fetchall()
        return render(request, 'teacher/tea_course.html', context={'rows': rows})

# 工作进度
def work_load(request):
    if request.method == 'GET':
        cursor = get_cursor()
        print(request.session['id'])
        cursor.execute("select * from teacher_workload_view where 工号 = '%s'" %request.session['id'])
        rows = cursor.fetchall()
        print(rows)
        return render(request,'teacher/work_load.html',context={'rows':rows})

#   教师信息
def teacher_info(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select teacherinfo.teacherid,teachername,gender,age,workunit,adyear,phonenumber,qqnumber,idnumber,abcnumber,ssnumber "
                       "from teacherinfo,teacherprivateinfo where teacherinfo.teacherid='%s'" % request.session['id'])
        row = cursor.fetchone()
        return render(request,'teacher/teacher_info.html',context={'row':row})
    else:
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        session_id = request.session['id']
        cursor = get_cursor()
        cursor.execute(
            "update teacherinfo,teacherprivateinfo set teacherinfo.teachername='%s',teacherinfo.gender='%s',"
            "teacherinfo.age='%s',teacherprivateinfo.phonenumber='%s',teacherprivateinfo.qqnumber='%s',"
            "teacherprivateinfo.idnumber='%s',teacherprivateinfo.abcnumber='%s',teacherprivateinfo.ssnumber='%s' "
            "where teacherinfo.teacherid='%s' and teacherprivateinfo.teacherid='%s'" % (
               name,gender,age,phone,qq,idcard,abc,ssn,session_id,session_id))

        return redirect(reverse('teacher_info'))

from django.shortcuts import render,reverse,redirect
from django.db import connection

# Create your views here.

def get_cursor():
    return connection.cursor()

# 主站首页
def index(request):
    return render(request,'index.html')

# 管理员首页
def admin_index(request):
    return render(request, 'adminstrator/admin.html')

# 统计信息页面
def tables(request):
    return render(request,'adminstrator/tables.html')

# 教师学时及工作量
def table1(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from Term_Teacher_Workload_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/1.html',context={'rows':rows})

# 学生学时及学分
def table2(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from term_student_credit_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/2.html',context={'rows':rows})

# 学年课程成绩
def table3(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_courserascore_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/3.html',context={'rows':rows})

# 单位不同学年课程成绩
def table4(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_courserascore_view;")
        rows = cursor.fetchall()
        #print(rows)
        return render(request,'adminstrator/all_tables/4.html',context={'rows':rows})

# 前10 名学生成绩
def table5(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("call N5_4('2017-2018','下学期')")
        rows = cursor.fetchall()
        # print(rows)
        return render(request,'adminstrator/all_tables/5.html',context={'rows':rows})


# 学生。。。。。
def table6(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from student_credit_view")
        rows = cursor.fetchall()
        # print(rows)
        return render(request,'adminstrator/all_tables/6.html',context={'rows':rows})


# 班级管理
def find_class(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from classinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_class.html', context={'rows':rows})
    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from classinfo where classid='%s'"%id)
            return redirect(reverse('findclass'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            major = request.POST.get("major")
            size = request.POST.get("size")

            cursor = get_cursor()
            cursor.execute("update classinfo set classname='%s',majorid='%s',classsize='%s' where classid='%s'" % (
            name, major, size, id))
            return redirect(reverse('findclass'))

# 添加班级
def add_class(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        major = request.POST.get("major")
        size = request.POST.get("size")
        cursor = get_cursor()
        try:
            cursor.execute("insert into classinfo (classid,classname,majorid,classsize) values ("
                           "'%s','%s','%s','%s')"%(id,name,major,size))
        except Exception as e:
            return render(request, 'adminstrator/add_class.html',context={'tip':"添加班级失败！"})

        return render(request,'adminstrator/add_class.html',context={'tip':"添加班级成功！"})
    else:
        return render(request,'adminstrator/add_class.html')


# 学生信息
def find_student(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from studentinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_student.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from studentinfo where studentid='%s'"%id)
            return redirect(reverse('findstudent'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            gender = request.POST.get("gender")
            age = request.POST.get("age")
            classname = request.POST.get("classname")

            cursor = get_cursor()
            cursor.execute("update studentinfo set studentname='%s',gender='%s',age='%s',classname='%s' where studentid='%s'"%(name,gender,age,classname,id))
            return redirect(reverse('findstudent'))


# 添加学生
def add_student(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        gender = request.POST.get("gender")
        age = request.POST.get("age")
        classname = request.POST.get("class")

        cursor = get_cursor()
        try:
            cursor.execute("insert into studentinfo (studentid,studentname,gender,age,classname) values ("
                           "'%s','%s','%s','%s','%s')"%(id,name,gender,age,classname))
        except Exception as e:
            return render(request, 'adminstrator/add_student.html',context={'tip':"添加学生失败！"})

        return render(request,'adminstrator/add_student.html',context={'tip':"添加学生成功！"})
    else:
        return render(request,'adminstrator/add_student.html')



# 课程信息
def find_coursera(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from courserainfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_coursera.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from courserainfo where courseraid='%s'"%id)
            return redirect(reverse('findcoursera'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            credit = request.POST.get("credit")
            period = request.POST.get("period")

            cursor = get_cursor()
            cursor.execute("update courserainfo set courseraname='%s',credit='%s',period='%s' where courseraid='%s'"%(name,credit,period,id))
            return redirect(reverse('findcoursera'))

# 添加课程
def add_coursera(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        credit = request.POST.get("credit")
        time = request.POST.get("time")
        cursor = get_cursor()
        try:
            cursor.execute("insert into courserainfo (courseraid,courseraname,credit,period) values ("
                           "'%s','%s','%s','%s')"%(id,name,credit,time))
        except Exception as e:
            return render(request, 'adminstrator/add_coursera.html',context={'tip':"添加课程失败！"})

        return render(request,'adminstrator/add_coursera.html',context={'tip':"添加课程成功！"})
    else:
        return render(request,'adminstrator/add_coursera.html')



# 教师信息
def find_teacher(request):

    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select * from teacherinfo")
        rows = cursor.fetchall()
        return render(request, 'adminstrator/find_teacher.html', context={'rows': rows})

    else:
        if 'delete' in request.POST:
            id = request.POST.get("id")
            cursor = get_cursor()
            cursor.execute("delete from teacherinfo where teacherid='%s'"%id)
            return redirect(reverse('findteacher'))

        elif 'save' in request.POST:
            id = request.POST.get("id")
            name = request.POST.get("name")
            gender = request.POST.get("gender")
            age = request.POST.get("age")

            cursor = get_cursor()
            cursor.execute("update teacherinfo set teachername='%s',gender='%s',age='%s' where teacherid='%s'"%(name,gender,age,id))
            return redirect(reverse('findteacher'))

# 添加教师
def add_teacher(request):
    if request.method == 'POST':
        id = request.POST.get("id")
        name = request.POST.get("name")
        gender = request.POST.get("gender")
        age = request.POST.get("age")
        cursor = get_cursor()
        try:
            cursor.execute("insert into teacherinfo (teacherid,teachername,gender,age) values ("
                           "'%s','%s','%s','%s')"%(id,name,gender,age))
        except Exception as e:
            print(e)
            return render(request, 'adminstrator/add_teacher.html',context={'tip':"添加教师失败！"})

        return render(request,'adminstrator/add_teacher.html',context={'tip':"添加教师成功！"})
    else:
        return render(request,'adminstrator/add_teacher.html')


# 教师个人信息修改
def presonal_info_edit(request):
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        unit = request.POST.get('unit')
        adyear = request.POST.get('adyear')
        id = request.POST.get('id')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        cursor = get_cursor()
        cursor.execute("update teacherprivateinfo,teacherinfo set "
                       "teacherinfo.teachername='%s',teacherinfo.gender='%s',"
                       "teacherinfo.age='%s',teacherprivateinfo.workunit='%s',"
                       "teacherprivateinfo.adyear='%s',teacherprivateinfo.phonenumber='%s',"
                       "teacherprivateinfo.qqnumber='%s',teacherprivateinfo.idnumber='%s',"
                       "teacherprivateinfo.abcnumber='%s',teacherprivateinfo.ssnumber='%s'"
                       "where teacherinfo.teacherid='%s'"
                       %(name,gender,age,unit,adyear,phone,qq,idcard,abc,ssn,id))
        # 更新数据后再次查询，返回结果
        cursor.execute("SELECT * from teacherinfo_view WHERE TeacherID = '%s'" % (id))
        row = cursor.fetchone()
        return render(request, 'adminstrator/find_teacher.html', context={'row': row})




#————————————————我是学生————————————————
# 我是学生首页
def student_index(request):
    return render(request,'student/student.html')

# 学生登陆
def stu_login(request):
    if request.method == "POST":
        id = request.POST.get('id')
        password = request.POST.get('password')
        cursor = get_cursor()
        cursor.execute("select studentid,studentname from studentinfo where studentid = '%s'" % id)
        id_name = cursor.fetchone()

        if id_name:
            if id_name[0] == password:

                request.session['id'] = id_name[0]
                request.session['name'] = id_name[1]

                return redirect(reverse('student'))

            else:
                return render(request, 'student/stu_login.html', context={'tip': "密码错误！"})
        else:
            return render(request, 'student/stu_login.html', context={'tip': "账号不存在！"})

    else:
        return render(request,'student/stu_login.html')


# 学生选课
def select_course(request):
    if request.method == 'GET':
        cursor = get_cursor()

        # 选择所有可选课程
        cursor.execute("select courserainfo.courseraname,teachername,period,credit,specificcourseraid from "
                       "specificcourserainfo,courserainfo,teacherinfo where "
                       "specificcourserainfo.courseraid=courserainfo.courseraid and "
                       "specificcourserainfo.teacherid=teacherinfo.teacherid")
        rows = cursor.fetchall()
        print(rows[1])
        return render(request, 'student/select_course.html', context={'rows': rows})
    else:
        stu_id = request.session['id']
        c_id = request.POST.get('cour_id')
        print("_____")
        print(c_id)
        cursor = get_cursor()
        try:
            cursor.execute(
                "insert into choosecourserainfo (studentid,specificcourseraid) values ('%s','%s')" % (stu_id, c_id))
        except Exception as e:
            print("e")
        return redirect(reverse('select'))


# 我的课程
def my_course(request):
    stu_id = request.session['id']
    cursor = get_cursor()
    cursor.execute("select courserainfo.courseraname,teachername,period,credit from "
                       "choosecourserainfo,specificcourserainfo,courserainfo,teacherinfo where "
                       "choosecourserainfo.studentid='%s' and "
                       "choosecourserainfo.specificcourseraid=specificcourserainfo.specificcourseraid and "
                   "specificcourserainfo.teacherid=teacherinfo.teacherid and "
                   "specificcourserainfo.courseraid=courserainfo.courseraid"%stu_id)
    rows=cursor.fetchall()
    return render(request, 'student/my_course.html', context={'rows':rows})

# 我的成绩
def my_grade(request):
    cursor = get_cursor()
    cursor.execute("select courseraname,credit,year,term,ptimescore,finalscore,totalscore,scorerank "
                   "from student_coursera_view where studentid='%s'"%request.session['id'])
    rows = cursor.fetchall()
    return render(request, 'student/my_grade.html',context={'rows':rows})

# 我的信息
def student_info(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select studentinfo.studentid,studentname,gender,age,classname,adyear,phonenumber,qqnumber,idnumber,abcnumber,ssnumber "
                       "from studentinfo,studentprivateinfo where studentinfo.studentid='%s'" % request.session['id'])
        row = cursor.fetchone()
        return render(request,'student/student_info.html',context={'row':row})
    else:
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        session_id = request.session['id']
        cursor = get_cursor()
        cursor.execute(
            "update studentinfo,studentprivateinfo set studentinfo.studentname='%s',studentinfo.gender='%s',"
            "studentinfo.age='%s',studentprivateinfo.phonenumber='%s',studentprivateinfo.qqnumber='%s',"
            "studentprivateinfo.idnumber='%s',studentprivateinfo.abcnumber='%s',studentprivateinfo.ssnumber='%s' "
            "where studentinfo.studentid='%s' and studentprivateinfo.studentid='%s'" % (
               name,gender,age,phone,qq,idcard,abc,ssn,session_id,session_id))

        return redirect(reverse('student_info'))


# ---------------我是老师---------------
# 我是老师首页
def teacher_index(request):
    return render(request,'teacher/teacher.html')

# 教师登陆
def teacher_login(request):
    if request.method == "POST":
        id = request.POST.get('id')
        password = request.POST.get('password')
        cursor = get_cursor()
        cursor.execute("select teacherid,teachername from teacherinfo where teacherid = '%s'" % id)
        id_name = cursor.fetchone()

        if id_name:
            if id_name[0] == password:

                request.session['id'] = id_name[0]
                request.session['name'] = id_name[1]

                return redirect(reverse('teacher'))

            else:
                return render(request, 'teacher/teacher_login.html', context={'tip': "密码错误！"})
        else:
            return render(request, 'teacher/teacher_login.html', context={'tip': "账号不存在！"})

    else:
        return render(request,'teacher/teacher_login.html')


#教师选课
def tea_select_course(request):
    if request.method == 'GET':
        cursor = get_cursor()
        # 选择所有可选课程
        cursor.execute("select courseraid,courseraname,credit,period,termid from courserainfo")
        rows = cursor.fetchall()
        return render(request, 'teacher/tea_select_course.html', context={'rows': rows})
    else:
        tea_id = request.session['id']
        c_id = request.POST.get('cour_id')
        c_name = request.POST.get('cour_name')
        cursor = get_cursor()
        cursor.execute(
            "insert into specificcourserainfo (specificcourseraid,courseraid,courseraname,teacherid) values ('%s','%s','%s','%s')" % (
                c_id+'3',c_id, c_name, tea_id))
        #
        # try:
        #     cursor.execute(
        #         "insert into specificcourserainfo (courseraid,courseraname,teacherid) values ('%s','%s','%s')" % (c_id, c_name,tea_id))
        # except Exception as e:
        #     print("e")
        return redirect(reverse('tea_select'))

# 教师的课程管理
def tea_my_course(request):
    if request.method == 'GET':
        teacher_id = request.session['id']
        cursor = get_cursor()
        # 查询所有课程
        cursor.execute("select courserainfo.courseraname,courserainfo.courseraid,period,credit from "
                   "specificcourserainfo,courserainfo where "
                   "specificcourserainfo.teacherid='%s'and "
                   "specificcourserainfo.courseraid=courserainfo.courseraid"%teacher_id)
        rows = cursor.fetchall()
        return render(request, 'teacher/tea_course.html', context={'rows': rows})

# 工作进度
def work_load(request):
    if request.method == 'GET':
        cursor = get_cursor()
        print(request.session['id'])
        cursor.execute("select * from teacher_workload_view where 工号 = '%s'" %request.session['id'])
        rows = cursor.fetchall()
        print(rows)
        return render(request,'teacher/work_load.html',context={'rows':rows})

#   教师信息
def teacher_info(request):
    if request.method == 'GET':
        cursor = get_cursor()
        cursor.execute("select teacherinfo.teacherid,teachername,gender,age,workunit,adyear,phonenumber,qqnumber,idnumber,abcnumber,ssnumber "
                       "from teacherinfo,teacherprivateinfo where teacherinfo.teacherid='%s'" % request.session['id'])
        row = cursor.fetchone()
        return render(request,'teacher/teacher_info.html',context={'row':row})
    else:
        name = request.POST.get('name')
        gender = request.POST.get('gender')
        age = request.POST.get('age')
        phone = request.POST.get('phone')
        qq = request.POST.get('qq')
        idcard = request.POST.get('idcard')
        abc = request.POST.get('abc')
        ssn = request.POST.get('ssn')

        session_id = request.session['id']
        cursor = get_cursor()
        cursor.execute(
            "update teacherinfo,teacherprivateinfo set teacherinfo.teachername='%s',teacherinfo.gender='%s',"
            "teacherinfo.age='%s',teacherprivateinfo.phonenumber='%s',teacherprivateinfo.qqnumber='%s',"
            "teacherprivateinfo.idnumber='%s',teacherprivateinfo.abcnumber='%s',teacherprivateinfo.ssnumber='%s' "
            "where teacherinfo.teacherid='%s' and teacherprivateinfo.teacherid='%s'" % (
               name,gender,age,phone,qq,idcard,abc,ssn,session_id,session_id))

        return redirect(reverse('teacher_info'))

# Create your views here. # 主站首页 # 管理员首页 # 统计信息页面 # 教师学时及工作量 #print(rows) # 学生学时及学分 #print(rows) # 学年课程成绩 #print(rows) # 单位不同学年课程成绩 #print(rows) # 前10 名学生成绩 # print(rows) # 学生。。。。。 # print(rows) # 班级管理 # 添加班级 # 学生信息 # 添加学生 # 课程信息 # 添加课程 # 教师信息 # 添加教师 # 教师个人信息修改 # 更新数据后再次查询，返回结果 #————————————————我是学生———————————————— # 我是学生首页 # 学生登陆 # 学生选课 # 选择所有可选课程 # 我的课程 # 我的成绩 # 我的信息 # ---------------我是老师--------------- # 我是老师首页 # 教师登陆 #教师选课 # 选择所有可选课程 # # try: #     cursor.execute( #         "insert into specificcourserainfo (courseraid,courseraname,teacherid) values ('%s','%s','%s')" % (c_id, c_name,tea_id)) # except Exception as e: #     print("e") # 教师的课程管理 # 查询所有课程 # 工作进度 #   教师信息