from tkinter import END, INSERT
from tkinter.messagebox import *

from dao.StudentDao import StudentDao
from utils.ExcelUtils import ExcelUtils


class StudentService:

    @staticmethod
    def add_student(list_entry):
        sno = list_entry[0].get()
        if sno == '':
            showwarning(title="警告", message="学号不能为空！\n加入失败！")
            return
        result = StudentDao.query_student(sno)  # 根据学号查询学生
        if result is not None:
            showwarning(title="警告", message="学号重复！\n加入失败！")
            return
        sql = "INSERT INTO t_student(sno,sname,sex,age,phone,dormNo) VALUES(%s,%s,%s,%s,%s,%s)"
        StudentDao.update_student(sql, list_entry[0].get(), list_entry[1].get(), list_entry[2].get(),
                                  list_entry[3].get(), list_entry[4].get(), list_entry[5].get())
        showinfo(title="提示", message="加入成功！")
        return True

    @staticmethod
    def delete_student(sno):
        result = StudentDao.query_student(sno)  # 根据学号查询学生
        if result is None:
            showerror(title="错误", message="不存在该学生！")
            return
        sql = "DELETE FROM t_student WHERE sno = %s"
        StudentDao.update_student(sql, sno)
        showinfo(title="提示", message="删除成功！")
        return True

    @staticmethod
    def query_student(sno, list_entry):
        result = StudentDao.query_student(sno)  # 根据学号查询学生
        if result is None:
            showwarning(title="警告", message="未查询到相关学生信息！")
            return
        list_entry[0].insert(INSERT, result[2])
        list_entry[1].insert(INSERT, result[3])
        list_entry[2].insert(INSERT, result[4])
        list_entry[3].insert(INSERT, result[5])
        list_entry[4].insert(INSERT, result[6])

    @staticmethod
    def update_student(sno, list_entry):
        result = StudentDao.query_student(sno)  # 根据学号查询学生
        if result is None:
            showerror(title="错误", message="不存在该学生！")
            return
        sql = "UPDATE t_student SET sname = %s,sex = %s,age = %s,phone = %s,dormNo = %s WHERE sno = %s"
        StudentDao.update_student(sql, list_entry[0].get(), list_entry[1].get(), list_entry[2].get(),
                                  list_entry[3].get(), list_entry[4].get(), sno)
        showinfo(title="提示", message="修改成功！")
        return True

    @staticmethod
    def query_all(lb):
        result = StudentDao.query_all()
        for row in result:
            lb.insert(END,
                      "ID:%s  学号：%s  姓名：%s  性别：%s  年龄：%s  电话：%s  宿舍号：%s" % (
                          row[0], row[1], row[2], row[3], row[4], row[5], row[6]))

    @staticmethod
    def to_excel():
        data = {}
        list_Id = []
        list_sno = []
        list_name = []
        list_sex = []
        list_age = []
        list_phone = []
        list_dormNo = []
        for row in StudentDao.query_all():
            list_Id.append(row[0])
            list_sno.append(row[1])
            list_name.append(row[2])
            list_sex.append(row[3])
            list_age.append(row[4])
            list_phone.append(row[5])
            list_dormNo.append(row[6])
        data['ID'] = list_Id
        data['学号'] = list_sno
        data['姓名'] = list_name
        data['性别'] = list_sex
        data['年龄'] = list_age
        data['电话'] = list_phone
        data['宿舍'] = list_dormNo
        ExcelUtils.to_excel(data)
        showinfo(title="提示", message="导出成功！")