from django.shortcuts import render,redirect
from student import models
# Create your views here.
def index(request):
    return render(request,'index.html')

def toIndex(request):
    return render(request,'index.html')

def studentLogin(request):
    if request.method=='POST':
        stuId=request.POST.get('id')
        password=request.POST.get('password')
        print("id",stuId,"password",password)
        student=models.Student.objects.get(id=stuId)
        print(student)
        if password==student.password:
            paper=models.Paper.objects.filter(major=student.major)
            grade=models.Grade.objects.filter(sid=student.id)
            return render(request,'index.html',{'student':student,'paper':paper,'grade':grade})
        else:
            return render(request,'index.html',{'message':'密码不正确'})

def teacherLogin(request):
    if request.method == 'POST':
        teaId = request.POST.get('id')
        password = request.POST.get('password')
        teacher=models.Teacher.objects.get(id=teaId)
        print(teacher)
        if password == teacher.password:
            paper = models.Paper.objects.filter(tid=teacher.id)

            data1=models.Grade.objects.filter(subject='python',grade__lt=60).count()
            data2=models.Grade.objects.filter(subject='python',grade__gte=60,grade__lt=70).count()
            data3 = models.Grade.objects.filter(subject='python', grade__gte=70, grade__lt=80).count()
            data4 = models.Grade.objects.filter(subject='python', grade__gte=80, grade__lt=90).count()
            data5 = models.Grade.objects.filter(subject='python', grade__gte=90).count()

            data6 = models.Grade.objects.filter(subject='java', grade__lt=60).count()
            data7= models.Grade.objects.filter(subject='java', grade__gte=60, grade__lt=70).count()
            data8= models.Grade.objects.filter(subject='java', grade__gte=70, grade__lt=80).count()
            data9 = models.Grade.objects.filter(subject='java', grade__gte=80, grade__lt=90).count()
            data10= models.Grade.objects.filter(subject='java', grade__gte=90).count()

            data_1={'data1':data1,'data2':data2,'data3':data3,'data4':data4,'data5':data5}
            data_2 = {'data6': data6, 'data7': data7, 'data8': data8, 'data9': data9, 'data10': data10}
            print("数量：",data2)
            return render(request, 'teacher.html', {'teacher': teacher, 'paper':paper,'data_1':data_1,'data_2':data_2})
        else:
            return render(request, 'index.html', {'message': '密码不正确'})

def showGrade(request):
    subject1=request.GET.get('subject')
    grade=models.Grade.objects.filter(subject=subject1)

    data1 = models.Grade.objects.filter(subject=subject1, grade__lt=60).count()
    data2 = models.Grade.objects.filter(subject=subject1, grade__gte=60, grade__lt=70).count()
    data3 = models.Grade.objects.filter(subject=subject1, grade__gte=70, grade__lt=80).count()
    data4 = models.Grade.objects.filter(subject=subject1, grade__gte=80, grade__lt=90).count()
    data5 = models.Grade.objects.filter(subject=subject1, grade__gte=90).count()

    data = {'data1': data1, 'data2': data2, 'data3': data3, 'data4': data4, 'data5': data5}

    return render(request,'showGrade.html',{'grade':grade,'data':data,'subject':subject1})

def queryStudent(request):
    sid=request.GET.get('id')
    sex=request.GET.get('sex')
    subject=request.GET.get('subject')
    tid=request.GET.get('tid')
    teacher = models.Teacher.objects.get(id=tid)
    paper = models.Paper.objects.filter(tid=teacher.id)
    from django.db import connection,transaction
    cursor=connection.cursor()
    sql="select * from grade,student where student.id=grade.sid_id and student.id like %s and grade.subject like %s and student.sex like %s"
    val=('%'+sid+'%','%'+subject+'%','%')
    cursor.execute(sql,val)
    result=dictfetchall(cursor)
    return render(request,'teacher.html',{'teacher':teacher,'result':result,'paper':paper})

def dictfetchall(cursor):
    desc = cursor.description
    return [
    dict(zip([col[0] for col in desc], row))
    for row in cursor.fetchall()
    ]

def startExam(request):
    sid = request.GET.get('sid')
    subject1=request.GET.get('subject')
    student=models.Student.objects.get(id=sid)
    paper=models.Paper.objects.filter(subject=subject1)
    return render(request,'exam.html',{'student':student,'paper':paper,'subject':subject1})

def calGrade(request):
    if request.method=='POST':
        sid=request.POST.get('sid')
        subject1 = request.POST.get('subject')
        student= models.Student.objects.get(id=sid)
        paper = models.Paper.objects.filter(major=student.major)
        grade = models.Grade.objects.filter(sid=student.id)
        question= models.Paper.objects.filter(subject=subject1).values("pid").values('pid__id','pid__answer','pid__score')
        mygrade=0
        for p in question:
            qId=str(p['pid__id'])
            myans=request.POST.get(qId)
            okans=p['pid__answer']
            if myans==okans:
                mygrade+=p['pid__score']
        models.Grade.objects.create(sid_id=sid,subject=subject1,grade=mygrade)
        return render(request,'index.html',{'student':student,'paper':paper,'grade':grade})

def logOut(request):
    return redirect('/toIndex/')