package dao

import (
	"database/sql"
	"exam_system/config"
	"exam_system/entity"
	"exam_system/result"
	"exam_system/utils"
	"exam_system/vo"
	"fmt"
	"log"
	"mime/multipart"
	"strconv"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/xuri/excelize/v2"
	"golang.org/x/crypto/bcrypt"
)

// FindUserbySid 是否存在
func FindUserbySid(sid string) *result.Result {
	sqlStr := `SELECT
	u.id,
	u.username,
	u.sid,
	u.create_at,
	u.update_at,
	u.password,
	u.status,
	group_concat( DISTINCT(r.name) ) role_name,
	t.name term_name,
	c.name class_name
FROM
	user u
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term t ON c.term_id = t.id
	LEFT JOIN user_role ur ON u.id = ur.user_id
	LEFT JOIN role r ON ur.role_id = r.id 
WHERE
	u.sid =? and u.delete_at is null
	GROUP BY u.id`

	var u vo.UserVo
	err := config.DB.Get(&u, sqlStr, sid)
	if err != nil {
		if err == sql.ErrNoRows {
			return result.DATA_NOT_FOUND
		}
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SuccessResult(u)
}

// FindUserbyId 通过Id 查询
func FindUserbyId(id int) *result.Result {
	sqlStr := `SELECT
	u.id,
	u.username,
	u.sid,
	u.create_at,
	u.update_at,
	group_concat( DISTINCT(r.NAME) ) role_name,
	t.name term_name,
	c.name class_name
FROM
	user u
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term t ON c.term_id = t.id
	LEFT JOIN user_role ur ON u.id = ur.user_id
	LEFT JOIN role r ON ur.role_id = r.id 
WHERE
	u.id =? and u.delete_at is null
	GROUP BY u.id`

	var user vo.UserVo
	err := config.DB.Get(&user, sqlStr, id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SUCCESS.SetData(user)
}

// InsertUser 插入
func InsertUser(user *entity.User, userType string) *result.Result {

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 1.先查询用户是否存在
		sqlStr := "select id,username,password,sid,class_id from user where sid=? and delete_at is null"
		var u entity.User
		err = tx.Get(&u, sqlStr, user.Sid)
		if err != nil && err.Error() != "sql: no rows in result set" {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		if u.ID != 0 {
			return result.USER_IS_EXISTED
		}

		// 2、插入用户表
		sqlStr = "insert into user(username, password,sid,create_at,update_at,status,class_id) values (?,?,?,?,?,?,?)"

		var ret sql.Result
		ret, err = tx.Exec(sqlStr, user.Username, user.Password, user.Sid, time.Now(), time.Now(), user.Status, user.ClassId)
		if err != nil {
			tx.Rollback()
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		id, _ := ret.LastInsertId()

		// 3、通过名字获取角色

		sqlStr = "select id from role where name=? limit 1"

		var r entity.Role
		err = tx.Get(&r, sqlStr, userType)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 4、插入用户角色表
		sqlStr = "insert into user_role(user_id, role_id,create_at,update_at) values (?,?,?,?)"
		_, err = tx.Exec(sqlStr, id, r.Id, time.Now(), time.Now())
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 5、查询班级还未开始的考试
		sqlStr = "select e.id from exam_class ec left join exam e on e.id = ec.exam_id where ec.class_id=? and e.end_at>=?"
		var count []int64
		err = tx.Select(&count, sqlStr, user.ClassId, time.Now())
		if err != nil && err != sql.ErrNoRows {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		// count == examId []
		if len(count) == 0 {
			return result.SUCCESS.SetData(id)
		}

		// 6、添加考试记录
		sqlStr = "insert into exam_record(create_at, update_at, user_id, exam_id,status) values (?,?,?,?,?)"
		prepare, _ := tx.Prepare(sqlStr)

		for _, examId := range count {
			_, err = prepare.Exec(time.Now(), time.Now(), id, examId, entity.NOT_TESTED)
			if err != nil && err != sql.ErrNoRows {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		}

		// 7、更新考试人数
		sqlStr = "update exam set user_count = user_count+1 where id in (?)"
		var updateExamStr string
		var param []interface{}
		updateExamStr, param, err = sqlx.In(sqlStr, count)
		_, err = tx.Exec(updateExamStr, param...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		return result.SUCCESS.SetData(id)
	})
}

// UpdateUser 根据id
func UpdateUser(user *entity.User, userType string) *result.Result {

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}

	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 1.先查询用户
		sqlStr := "select id,username,password,sid,class_id from user where id=?"

		var u entity.User
		err = tx.Get(&u, sqlStr, user.ID)
		if err != nil && err.Error() != "sql: no rows in result set" {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 2、更新用户表(管理员插入默认为正常)
		parms := make([]interface{}, 0)
		sqlStr = `update user set username = ?, `
		parms = append(parms, user.Username)

		if user.Password != "" {
			hash, _ := bcrypt.GenerateFromPassword([]byte(user.Password), bcrypt.DefaultCost)
			user.Password = string(hash)
			parms = append(parms, user.Password)
			sqlStr += `password = ?, `
		}
		if user.Sid != "" && user.Sid != u.Sid {
			parms = append(parms, user.Sid)
			sqlStr += `sid = ?, `
		}
		sqlStr += "class_id = ?,update_at=?,status=? where id = ?"
		parms = append(parms, user.ClassId)
		parms = append(parms, time.Now())
		parms = append(parms, user.Status)
		parms = append(parms, user.ID)

		var ret sql.Result
		ret, err = tx.Exec(sqlStr, parms...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		affected, _ := ret.RowsAffected()

		// 3、获取角色
		var r entity.Role
		sqlStr = "select id from role where name=? limit 1"
		err = tx.Get(&r, sqlStr, userType)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 4、更新用户角色表
		sqlStr = "select id from user_role where user_id=?"
		var id int
		var ret2 sql.Result
		_ = tx.Get(&id, sqlStr, u.ID)
		if id == 0 {
			sqlStr = "insert into user_role(user_id, role_id,create_at,update_at) values (?,?,?,?)"
			ret2, err = tx.Exec(sqlStr, u.ID, r.Id, time.Now(), time.Now())
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		} else {
			sqlStr = "update user_role set  role_id=?  ,update_at=? where user_id=?"
			ret2, err = tx.Exec(sqlStr, r.Id, time.Now(), user.ID)
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		}

		affected2, _ := ret2.RowsAffected()
		return result.SUCCESS.SetData(result.NewResultChange(affected + affected2))
	})
}

// DeleteUsers  删除
func DeleteUsers(ids []string) *result.Result {

	parm := []interface{}{time.Now()}
	parmStr := ""
	for k, id := range ids {
		parm = append(parm, id)
		if k == (len(ids) - 1) {
			parmStr += "?"
		} else {
			parmStr += "?,"
		}
	}

	sqlStr := fmt.Sprintf("update user set delete_at=? where id in (%s)", parmStr)

	res, err := config.DB.Exec(sqlStr, parm...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SUCCESS.SetData(res)
}

func FindUserList(page, size int, sort string, query map[string]interface{}) *result.Result {

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		sqlStr := `
SELECT
	u.id,
	u.username,
	u.sid,
	group_concat(DISTINCT ( r.name )) role_name,
	u.create_at,
	u.update_at,
	t.name term_name,
	c.name class_name
FROM
	user u
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term t ON c.term_id = t.id
	LEFT JOIN user_role ur ON u.id = ur.user_id
	LEFT JOIN role r ON ur.role_id = r.id 
WHERE
	u.delete_at IS NULL `

		params := make([]interface{}, 0)
		if query["username"] != nil {
			sqlStr += "and u.username like ? "
			params = append(params, "%"+query["username"].(string)+"%")
		}

		if query["sid"] != nil {
			sqlStr += "and u.sid = ? "
			params = append(params, query["sid"])
		}

		if query["class_id"] != nil {
			sqlStr += "and u.class_id=? "
			params = append(params, query["class_id"])
		}

		if query["role"] != nil {
			sqlStr += "and r.name = ? "
			params = append(params, query["role"])
		}

		sqlStr += `GROUP BY u.id `
		if sort == "asc" {
			sqlStr += `Order By u.create_at asc
`
		} else {
			sqlStr += `Order By u.create_at desc
`
		}
		sqlStr += `LIMIT ?,?`

		params = append(params, utils.PostionSize(page, size), size)

		var users []vo.UserVo
		err = tx.Select(&users, sqlStr, params...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		sqlStr = `SELECT
	count(u.id)
FROM
	user u
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term t ON c.term_id = t.id
	LEFT JOIN user_role ur ON u.id = ur.user_id
	LEFT JOIN role r ON ur.role_id = r.id 
WHERE
	u.delete_at IS NULL `
		params = make([]interface{}, 0)
		if query["username"] != nil {
			sqlStr += "and u.username like ? "
			params = append(params, "%"+query["username"].(string)+"%")
		}

		if query["sid"] != nil {
			sqlStr += "and u.sid = ? "
			params = append(params, query["sid"].(string))
		}

		if query["class_id"] != nil {
			sqlStr += "and u.class_id=? "
			params = append(params, query["class_id"])
		}

		if query["role"] != nil {
			sqlStr += "and r.name = ? "
			params = append(params, query["role"])
		}

		total := 0
		err = tx.Get(&total, sqlStr, params...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		return result.SuccessResult(result.NewPage(page, len(users), total, users))
	})
}

// InsertBatchUserByClassId 导入xlsx
func InsertBatchUserByClassId(file multipart.File, classId int) *result.Result {

	xlsFile, err := excelize.OpenReader(file)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {

		var total int64 = 0

		// 1.查询用户是否已存在
		userStr := "select id,sid from user where sid = ? and delete_at is null limit 1"

		// 2.插入用户
		userAddStr := "insert into user(username, password,sid,create_at,update_at,status,class_id) values (?,?,?,?,?,?,?)"

		var userAddStmt *sql.Stmt
		userAddStmt, err = tx.Prepare(userAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 3.获取角色列表
		rolesStr := "select id,name from role"
		var roles []*entity.Role
		err = tx.Select(&roles, rolesStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 4、插入用户角色表
		roleAddStr := "insert into user_role(user_id, role_id,create_at,update_at) values (?,?,?,?)"
		var roleAddStmt *sql.Stmt
		roleAddStmt, err = tx.Prepare(roleAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 5、更新用户
		userUpdateStr := "update user set username=?,password=?,sid=?,update_at=?,status=?,class_id=? where id = ?"
		var userUpdateStmt *sql.Stmt
		userUpdateStmt, err = tx.Prepare(userUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 6、更新用户角色
		roleUpdateStr := "update user_role set  role_id=?  ,update_at=? where user_id=?"
		var roleUpdateStmt *sql.Stmt
		roleUpdateStmt, err = tx.Prepare(roleUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		i := 2
		for {
			sid, _ := xlsFile.GetCellValue("Sheet1", "A"+strconv.Itoa(i))
			username, _ := xlsFile.GetCellValue("Sheet1", "B"+strconv.Itoa(i))
			roleName, _ := xlsFile.GetCellValue("Sheet1", "D"+strconv.Itoa(i))
			password, _ := xlsFile.GetCellValue("Sheet1", "E"+strconv.Itoa(i))
			if sid == "" {
				break
			}
			i++
			hash, _ := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
			password = string(hash)

			// 1、查询用户是否存在
			var user entity.User
			err = tx.Get(&user, userStr, sid)
			// 存在插入，不存在更新
			var ret sql.Result
			if err != nil {
				if err == sql.ErrNoRows {
					// 2、插入用户
					ret, err = userAddStmt.Exec(username, password, sid, time.Now(), time.Now(), entity.NORMAL, classId)
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
					id, _ := ret.LastInsertId()
					affected, _ := ret.RowsAffected()
					total += affected

					// 3、通过名字获取角色
					var role entity.Role
					for _, r := range roles {
						if r.Name == roleName {
							role = *r
							break
						}
					}

					// 4、插入角色
					_, err = roleAddStmt.Exec(id, role.Id, time.Now(), time.Now())
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
				} else {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			} else {
				// 1、更新用户
				ret, err = userUpdateStmt.Exec(username, password, sid, time.Now(), entity.NORMAL, classId, user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
				affected, _ := ret.RowsAffected()
				total += affected

				// 2、获取角色
				var role entity.Role
				for _, r := range roles {
					if r.Name == roleName {
						role = *r
						break
					}
				}
				// 3、更新角色
				_, err = roleUpdateStmt.Exec(role.Id, time.Now(), user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}

		}

		res := result.NewResultChange(total)
		res.RowsFailed = int64(i-2) - total

		return result.SuccessResult(res)
	})

}

// InsertBatchUserByTermId 导入xlsx
func InsertBatchUserByTermId(file multipart.File, termId, classId int) *result.Result {

	xlsFile, err := excelize.OpenReader(file)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {

		var total int64 = 0

		// 1.查询班级
		classStr := "SELECT c.id,c.term_id FROM class c,term t WHERE t.name=? and  c.name = ?  and c.term_id = t.id AND c.delete_at IS NULL limit 1"

		// 1.查询用户是否已存在
		userStr := "select id,sid from user where sid = ? and delete_at is null limit 1"

		// 2.插入用户
		userAddStr := "insert into user(username, password,sid,create_at,update_at,status,class_id) values (?,?,?,?,?,?,?)"

		var userAddStmt *sql.Stmt
		userAddStmt, err = tx.Prepare(userAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 3.获取角色列表
		rolesStr := "select id,name from role"
		var roles []*entity.Role
		err = tx.Select(&roles, rolesStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 4、插入用户角色表
		roleAddStr := "insert into user_role(user_id, role_id,create_at,update_at) values (?,?,?,?)"
		var roleAddStmt *sql.Stmt
		roleAddStmt, err = tx.Prepare(roleAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 5、更新用户
		userUpdateStr := "update user set username=?,password=?,sid=?,update_at=?,status=?,class_id=? where id = ?"
		var userUpdateStmt *sql.Stmt
		userUpdateStmt, err = tx.Prepare(userUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 6、更新用户角色
		roleUpdateStr := "update user_role set  role_id=?  ,update_at=? where user_id=?"
		var roleUpdateStmt *sql.Stmt
		roleUpdateStmt, err = tx.Prepare(roleUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		i := 2
		for {
			sid, _ := xlsFile.GetCellValue("Sheet1", "A"+strconv.Itoa(i))
			username, _ := xlsFile.GetCellValue("Sheet1", "B"+strconv.Itoa(i))
			termClassName, _ := xlsFile.GetCellValue("Sheet1", "C"+strconv.Itoa(i))
			roleName, _ := xlsFile.GetCellValue("Sheet1", "D"+strconv.Itoa(i))
			password, _ := xlsFile.GetCellValue("Sheet1", "E"+strconv.Itoa(i))
			if sid == "" {
				break
			}
			i++
			hash, _ := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
			password = string(hash)

			// 1.查询班级
			var class entity.Class
			termClass := strings.Split(termClassName, ">")

			if len(termClass) == 2 {
				fmt.Println(classStr)
				err = tx.Get(&class, classStr, termClass[0], termClass[1])
				if err != nil {
					if err == sql.ErrNoRows {
						err = nil
						continue
					}
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}
			fmt.Println()
			// !TODO 注释为之前的 选择班级不能导入的问题
			// if roleName == "student" && class.TermId != termId || (classId != 0 && class.Id != nil && *class.Id == classId) {
			if roleName == "student" && class.TermId != termId || (classId != 0 && class.Id != nil && *class.Id != classId) {
				fmt.Println(111)
				continue
			}

			// 2、查询用户是否存在
			var user entity.User
			err = tx.Get(&user, userStr, sid)
			// 存在插入，不存在更新
			var ret sql.Result
			if err != nil {
				if err == sql.ErrNoRows {
					// 2、插入用户
					ret, err = userAddStmt.Exec(username, password, sid, time.Now(), time.Now(), entity.NORMAL, class.Id)
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
					id, _ := ret.LastInsertId()
					user.ID = int(id)
					affected, _ := ret.RowsAffected()
					total += affected

					// 3、通过名字获取角色
					var role entity.Role
					for _, r := range roles {
						if r.Name == roleName {
							role = *r
							break
						}
					}

					// 4、插入角色
					_, err = roleAddStmt.Exec(id, role.Id, time.Now(), time.Now())
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
				} else {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			} else {
				// 1、更新用户
				ret, err = userUpdateStmt.Exec(username, password, sid, time.Now(), entity.NORMAL, class.Id, user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
				affected, _ := ret.RowsAffected()
				total += affected

				// 2、获取角色
				var role entity.Role
				for _, r := range roles {
					if r.Name == roleName {
						role = *r
						break
					}
				}
				// 3、更新角色
				_, err = roleUpdateStmt.Exec(role.Id, time.Now(), user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}

			// 更新考试
			// 5、查询班级还未开始的考试
			sqlStr := "select e.id from exam_class ec left join exam e on e.id = ec.exam_id where ec.class_id=? and e.end_at>=?"
			var count []int64
			err = tx.Select(&count, sqlStr, user.ClassId, time.Now())
			if err != nil && err != sql.ErrNoRows {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
			// !TODO 修复count为nil时报错
			if count == nil {
				continue
			}

			// 6、添加考试记录
			sqlStr = "insert into exam_record(create_at, update_at, user_id, exam_id,status) values (?,?,?,?,?)"
			prepare, _ := tx.Prepare(sqlStr)

			for _, examId := range count {
				_, err = prepare.Exec(time.Now(), time.Now(), user.ID, examId, entity.NOT_TESTED)
				if err != nil && err != sql.ErrNoRows {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}

			// 7、更新考试人数
			sqlStr = "update exam set user_count = user_count+1 where id in (?)"
			var updateExamStr string
			var param []interface{}
			updateExamStr, param, err = sqlx.In(sqlStr, count)
			_, err = tx.Exec(updateExamStr, param...)
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		}

		res := result.NewResultChange(total)
		res.RowsFailed = int64(i-2) - total
		return result.SuccessResult(res)
	})

}

// InsertBatchUser 导入xlsx(excel读内存)
func InsertBatchUser(file multipart.File) *result.Result {

	xlsFile, err := excelize.OpenReader(file)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	tx, err := config.DB.Beginx() // 开启事务
	if err != nil {
		log.Printf("begin trans failed, err:%v\n", err)
		return result.UNKNOW_ERROR
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		i := 2
		var total int64 = 0
		// 1.查询班级
		classStr := "SELECT c.id FROM class c,term t WHERE t.name=? and  c.name = ?  and c.term_id = t.id AND c.delete_at IS NULL limit 1"

		// 2.查询用户是否已存在
		userStr := "select id,sid from user where sid = ? and delete_at is null limit 1"

		// 3.插入用户
		userAddStr := "insert into user(username, password,sid,create_at,update_at,status,class_id) values (?,?,?,?,?,?,?)"

		var userAddStmt *sql.Stmt
		userAddStmt, err = tx.Prepare(userAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 3.获取角色列表
		rolesStr := "select id,name from role"
		var roles []*entity.Role
		err = tx.Select(&roles, rolesStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 4、插入用户角色表
		roleAddStr := "insert into user_role(user_id, role_id,create_at,update_at) values (?,?,?,?)"
		var roleAddStmt *sql.Stmt
		roleAddStmt, err = tx.Prepare(roleAddStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 5、更新用户
		userUpdateStr := "update user set username=?,password=?,sid=?,update_at=?,status=?,class_id=? where id = ?"
		var userUpdateStmt *sql.Stmt
		userUpdateStmt, err = tx.Prepare(userUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 6、更新用户角色
		roleUpdateStr := "update user_role set  role_id=?  ,update_at=? where user_id=?"
		var roleUpdateStmt *sql.Stmt
		roleUpdateStmt, err = tx.Prepare(roleUpdateStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		var users []*vo.UserVo
		for {
			sid, _ := xlsFile.GetCellValue("Sheet1", "A"+strconv.Itoa(i))
			username, _ := xlsFile.GetCellValue("Sheet1", "B"+strconv.Itoa(i))
			className, _ := xlsFile.GetCellValue("Sheet1", "C"+strconv.Itoa(i))
			roleName, _ := xlsFile.GetCellValue("Sheet1", "D"+strconv.Itoa(i))
			password, _ := xlsFile.GetCellValue("Sheet1", "E"+strconv.Itoa(i))
			if sid == "" {
				break
			}

			user := entity.User{Sid: sid, Username: username, Password: password}
			users = append(users, &vo.UserVo{User: user, RoleName: &roleName, ClassName: &className})
			i++
		}

		for _, v := range users {
			// 1、查询学期班级
			termClass := strings.Split(*v.ClassName, ">")
			var class entity.Class
			if len(termClass) == 2 {
				err = tx.Get(&class, classStr, termClass[0], termClass[1])
				if err != nil {
					if err == sql.ErrNoRows {
						err = nil
						continue
					}
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}

			hash, _ := bcrypt.GenerateFromPassword([]byte(v.Password), bcrypt.DefaultCost)
			v.Password = string(hash)

			// 2、查询用户是否存在
			var user entity.User
			err = tx.Get(&user, userStr, v.Sid)
			// 存在插入，不存在更新
			var ret sql.Result
			if err != nil {
				if err == sql.ErrNoRows {
					// 2、插入用户
					ret, err = userAddStmt.Exec(v.Username, v.Password, v.Sid, time.Now(), time.Now(), entity.NORMAL, class.Id)
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
					id, _ := ret.LastInsertId()
					affected, _ := ret.RowsAffected()
					total += affected

					// 3、通过名字获取角色
					var role entity.Role
					for _, r := range roles {
						if r.Name == *v.RoleName {
							role = *r
							break
						}
					}

					// 4、插入角色
					_, err = roleAddStmt.Exec(id, role.Id, time.Now(), time.Now())
					if err != nil {
						return result.UNKNOW_ERROR.SetMsg(err.Error())
					}
				} else {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			} else {
				// 2、更新用户
				ret, err = userUpdateStmt.Exec(v.Username, v.Password, v.Sid, time.Now(), entity.NORMAL, class.Id, user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
				affected, _ := ret.RowsAffected()
				total += affected

				// 获取角色
				var role entity.Role
				for _, r := range roles {
					if r.Name == *v.RoleName {
						role = *r
						break
					}
				}
				// 3、更新角色
				_, err = roleUpdateStmt.Exec(role.Id, time.Now(), user.ID)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}
		}

		res := result.NewResultChange(total)
		res.RowsFailed = int64(i-2) - total

		return result.SuccessResult(res)
	})

}

// ImportUserInfo 导入学生信息
func DownloadUserInfo(file *excelize.File) *result.Result {

	sqlStr := `	select
	u.id,
	u.username,
	u.password,
	u.sid,
	u.create_at,
	u.update_at,
	group_concat( DISTINCT(r.NAME) ) role_name,
	t.name term_name,
	c.name class_name
FROM
	user u
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term t ON c.term_id = t.id
	LEFT JOIN user_role ur ON u.id = ur.user_id
	LEFT JOIN role r ON ur.role_id = r.id 
WHERE
	 u.delete_at is null
	GROUP BY u.id`

	var users []*vo.UserVo
	err := config.DB.Select(&users, sqlStr)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	i := 2
	for _, user := range users {
		file.SetCellValue("Sheet1", "A"+strconv.Itoa(i), user.Sid)
		file.SetCellValue("Sheet1", "B"+strconv.Itoa(i), user.Username)
		if user.ClassName != nil && user.TermName != nil {
			file.SetCellValue("Sheet1", "C"+strconv.Itoa(i), (*user.TermName + ">" + *user.ClassName))
		}
		if user.RoleName != nil {
			file.SetCellValue("Sheet1", "D"+strconv.Itoa(i), *user.RoleName)
		}
		file.SetCellValue("Sheet1", "E"+strconv.Itoa(i), user.Password)
		i++
	}
	return result.SUCCESS
}