from django.db.models import Avg, Sum, Count, Max, Min, F, Q
from django.http import HttpResponse
from django.shortcuts import render

from app.models import Student, StudentInfo, Grade


def hello(request):
    return HttpResponse('hello world')


def add_stu(request):
    if request.method == 'GET':
        # 第一種方式: 新增學(xué)生信息
        # stu = Student()
        # stu.s_name = '小明'
        # stu.age = 29
        # stu.save()
        # 第二種方式: create()
        # objects: 管理器
        for i in range(10):
            Student.objects.create(s_name='小花' + str(i), age=20 + i)

        # flask中使用flask-sqlalchemy拳缠，Student.query
        # tornado中使用sqlalchemy刹泄，session.query(Student).filter
        # Django中使用自帶的ORM匠童，Student.objects

        return HttpResponse('創(chuàng)建成功')


def del_stu(request):
    if request.method == 'GET':
        # 實(shí)現(xiàn)刪除功能
        # filter(字段=值)
        # 刪除單條數(shù)據(jù)
        stu = Student.objects.filter(s_name='小明').first()
        stu.delete()
        # 刪除多條數(shù)據(jù)
        Student.objects.filter(s_name='小花').delete()
        return HttpResponse('刪除成功')


def update_stu(request):
    if request.method == 'GET':
        # 第一種方式: 修改數(shù)據(jù)毛肋， save()
        # stu = Student.objects.filter(s_name='小花').first()
        # stu.age = 19
        # stu.save()
        # 第二種方式: update()
        Student.objects.filter(s_name='小花').update(age=18, s_name='小強(qiáng)')

        return HttpResponse('修改數(shù)據(jù)')


def sel_stu(request):
    if request.method == 'GET':
        # 查詢數(shù)據(jù)
        stu = Student.objects.filter(s_name='小明').all()
        # stu的結(jié)果類型為QuerySet向瓷，取其中的數(shù)據(jù)，first()，last()族壳，[下標(biāo)]

        # flask: get(主鍵值) ，查詢結(jié)果為獲取主鍵所在行的對(duì)象
        # django: get(任何條件)趣些，查詢滿足條件的結(jié)果即可
        stu = Student.objects.get(s_name='小明')
        print(stu)
        stu = Student.objects.filter(s_name='小明').first()
        print(stu)
        # django: get(條件)仿荆，查詢條件必須查找到結(jié)果，
        # 如果查詢不到結(jié)果則報(bào)錯(cuò)‘DoesNotExist’
        # stu = Student.objects.get(id=1000)
        # 如果查詢結(jié)果超過一個(gè)對(duì)象喧务，則報(bào)錯(cuò)‘MultipleObjectsReturned ’
        # stu = Student.objects.get(age=23)

        # 總結(jié): get()： 查詢條件必須成立且結(jié)果只能有一個(gè)
        print(stu)

        # filter()過濾滿足條件赖歌， exclude()過濾不滿足條件
        stus = Student.objects.exclude(age=23).all()
        print(stus)

        # count()統(tǒng)計(jì)多少條數(shù)據(jù)
        stus = Student.objects.all()
        print(len(stus))
        stus = Student.objects.all().count()
        print(stus)

        # 排序, 升序order_by('id')  降序order_by('-id')
        stus = Student.objects.all().order_by('-id')
        stus = Student.objects.all().order_by('id')
        print(stus)

        # values(): 序列化，將對(duì)象的屬性轉(zhuǎn)化為字典格式數(shù)據(jù)
        stus = Student.objects.all().values('s_name', 'age')
        print(stus)

        # contains功茴，字段__contains = ’值‘
        stus = Student.objects.filter(s_name__contains='花').all()
        stus = Student.objects.filter(s_name__startswith='花').all()
        print(stus)
        stus = Student.objects.filter(s_name__endswith='9').all()
        print(stus)

        # in,  pk=id
        stus = Student.objects.filter(id__in=[1, 2, 3, 4, 5, 6, 7]).all()
        stus = Student.objects.filter(pk__in=[1, 2, 3, 4, 5, 6, 7]).all()
        print(stus)

        # 大于23庐冯，gt,gte,lt,lte
        stus = Student.objects.filter(age__gt=23).all()
        stus = Student.objects.filter(age__gte=23).all()
        print(stus)

        # 聚合，Count(), Max(), Min()
        # select s_name, max(chengji) from student;
        # from django.db.models import Avg
        stus = Student.objects.all().aggregate(Avg('age'))
        print(stus)

        # 查詢語文成績大于數(shù)據(jù)成績的學(xué)生信息
        # sql: select * from student where yuwen > math;
        stus = Student.objects.filter(yuwen__gt=F('math'))
        stus = Student.objects.filter(yuwen__gt=F('math') + 10)
        print(stus)

        # Q,且坎穿，或展父，非
        stus = Student.objects.filter(s_name__contains='花', age=23).all()
        # 或者條件
        stus = Student.objects.filter(Q(s_name__contains='花') | Q(age=23)).all()
        # ~非
        stus = Student.objects.filter(~Q(s_name__contains='花')).all()
        stus = Student.objects.exclude(s_name__contains='花').all()
        print(stus)

        return HttpResponse('查詢數(shù)據(jù)成功')


def add_stu_info(request):
    if request.method == 'GET':
        # 向拓展表中添加信息
        stu_info = StudentInfo()
        stu_info.phone = '13551370671'
        stu_info.save()
        # 拓展表主鍵id值
        s_id = stu_info.id
        stu= Student.objects.filter(s_name='小明').first()
        # 第一種寫法: 學(xué)生對(duì)象.OneToOneFiled字段 = 關(guān)聯(lián)對(duì)象
        stu.stuinfo = stu_info
        # 第二種寫法: 學(xué)生對(duì)象.OneToOneFiled字段_id = 關(guān)聯(lián)對(duì)象.id
        stu.stuinfo_id = stu_info.id
        stu.save()
        return HttpResponse('添加拓展表信息')


def sel_stuinfo_by_stu(request):
    if request.method == 'GET':
        # 通過學(xué)生信息找拓展表信息
        stu = Student.objects.get(s_name='小明')
        # StudentInfo.objects.get(pk=stu.stuinfo_id)
        # 學(xué)生對(duì)象.OneToOneFiled字段
        stu.stuinfo
        return HttpResponse('獲取拓展表信息成功')


def sel_stu_by_info(request):
    if request.method == 'GET':
        # 通過拓展表中的手機(jī)號(hào)碼13551370673找學(xué)生信息
        stuinfo = StudentInfo.objects.filter(phone='13551370673').first()
        # stu = Student.objects.filter(stuinfo=stuinfo)
        # stu = Student.objects.filter(stuinfo_id=stuinfo.id)
        # print(stu)
        # 拓展表對(duì)象.關(guān)聯(lián)的模型名稱小寫
        stu = stuinfo.student
        # 定義related_name參數(shù)，拓展表對(duì)象.related_name值
        stu = stuinfo.stu
        print(stu)
        return HttpResponse('通過拓展表信息查詢學(xué)生表信息')


def add_grade(request):
    if request.method == 'GET':
        # 添加班級(jí)信息玲昧，并給學(xué)生分配班級(jí)
        names = ['Python班級(jí)', 'Java班級(jí)', 'Php班級(jí)', 'C++班級(jí)']
        for name in names:
            if not Grade.objects.filter(g_name=name).exists():
                Grade.objects.create(g_name=name)
        # 分配班級(jí)
        stus = Student.objects.filter(pk__in=[1, 2, 3, 4, 5, 6]).all()
        g = Grade.objects.filter(g_name='Python班級(jí)').first()
        for stu in stus:
            stu.grade = g
            # stu.grade_id = g.id
            stu.save()
        return HttpResponse('添加班級(jí)和學(xué)生信息')


def sel_grade_by_stu(request):
    if request.method == 'GET':
        # 通過學(xué)生查詢班級(jí)信息
        stu = Student.objects.filter(s_name='小明').first()
        g = stu.grade
        print(g)
        # 班級(jí)查詢學(xué)生
        stus = g.student_set.all()
        stus = g.stu.all()

        return HttpResponse('通過學(xué)生查詢班級(jí)成功')