oilseal-exam/dao/dao-exam.go

package dao

import (
	"database/sql"
	"exam_system/config"
	"exam_system/entity"

	"exam_system/result"
	"exam_system/utils"
	"exam_system/vo"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
)

func ExamInfoList(page, size int, sort string, query map[string]interface{}, uid int) *result.Result {
	// 1、获取list
	sqlStr := `select er.id, e.name,e.status, e.start_at, e.end_at, e.duration, e.publish_at,e.tp_id,er.status state,tp.score,tp.pass_score
from exam_record er left join user u on er.user_id = u.id 
		left join exam e on e.id = er.exam_id
         left join test_paper tp on e.tp_id = tp.id
         left join exam_class ec on e.id = ec.exam_id
         left join class c on ec.class_id = c.id
where er.status =0 and u.id = ? `

	params := []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	// 0 进行中 1已结束
	if query["status"] != nil {
		if query["status"].(float64) == 0 {
			sqlStr += "and e.end_at < ? "
		} else {
			sqlStr += "and e.end_at > ? "
		}
		params = append(params, time.Now())
	}
	sqlStr += "group by er.id "

	if sort == "asc" {
		sqlStr += "order by e.create_at asc "
	} else {
		sqlStr += "order by e.create_at desc "
	}

	sqlStr += "limit ?,?"
	params = append(params, utils.PostionSize(page, size), size)

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

	// 2、总数
	sqlStr = `select count(er.id)
from exam_record er left join user u on er.user_id = u.id 
		left join exam e on e.id = er.exam_id
         left join test_paper tp on e.tp_id = tp.id
         left join exam_class ec on e.id = ec.exam_id
         left join class c on ec.class_id = c.id
where er.status =0 and u.id = ? `
	params = []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	// 0 进行中 1已结束
	if query["status"] != nil {
		if query["status"].(float64) == 0 {
			sqlStr += "and e.end_at < ? "
		} else {
			sqlStr += "and e.end_at > ? "
		}
		params = append(params, time.Now())
	}

	var total int
	err = config.DB.Get(&total, sqlStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SUCCESS.SetData(result.NewPage(page, len(evo), total, evo))
}

func ExamInfoList1(page, size int, sort string, query map[string]interface{}, uid int) *result.Result {
	// 1、获取list
	sqlStr := `select e.*,tp.score,tp.pass_score
from exam e
         left join test_paper tp on e.tp_id = tp.id
         left join exam_class ec on e.id = ec.exam_id
         left join class c on ec.class_id = c.id
         left join user u on u.class_id = c.id
where u.id = ? `

	params := []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	// 0 进行中 1已结束
	if query["status"] != nil {
		if query["status"].(float64) == 0 {
			sqlStr += "and e.end_at < ? "
		} else {
			sqlStr += "and e.end_at > ? "
		}
		params = append(params, time.Now())
	}
	sqlStr += "group by e.id "

	if sort == "asc" {
		sqlStr += "order by e.create_at asc "
	} else {
		sqlStr += "order by e.create_at desc "
	}

	sqlStr += "limit ?,?"
	params = append(params, utils.PostionSize(page, size), size)

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

	// 2、总数
	sqlStr = `select count(e.id)
from exam e
         left join test_paper tp on e.tp_id = tp.id
         left join exam_class ec on e.id = ec.exam_id
         left join class c on ec.class_id = c.id
         left join user u on u.class_id = c.id
where u.id = ? `
	params = []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	// 0 进行中 1已结束
	if query["status"] != nil {
		if query["status"].(float64) == 0 {
			sqlStr += "and e.end_at < ? "
		} else {
			sqlStr += "and e.end_at > ? "
		}
		params = append(params, time.Now())
	}

	var total int
	err = config.DB.Get(&total, sqlStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SUCCESS.SetData(result.NewPage(page, len(evo), total, evo))
}

func AddExam(e *entity.Exam) *result.Result {

	tx, err := config.DB.Beginx()
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 1、获取考生人数
		classIds := strings.Split(e.ClassIds, ",")
		var users []*entity.User
		sqlStr := "select id from user where class_id in (?)"
		var query string
		var args []interface{}
		query, args, err = sqlx.In(sqlStr, classIds)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		query = tx.Rebind(query)
		err = tx.Select(&users, query, args...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 2、插入
		sqlStr = "insert into exam(name,create_at,update_at,start_at,end_at,publish_at,duration,status,tp_id,user_count) values (?,?,?,?,?,?,?,?,?,?)"
		var res sql.Result
		res, err = tx.Exec(sqlStr, e.Name, time.Now(), time.Now(), e.StartAt, e.EndAt, e.PublishAt, e.Duration, e.Status, e.TpId, len(users))
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		examId, _ := res.LastInsertId()
		e.Id = int(examId)

		// 3、插入考试班级表
		sqlStr = "insert into exam_class(exam_id,class_id,create_at,update_at) values (?,?,?,?)"
		var stmt *sql.Stmt
		stmt, err = tx.Prepare(sqlStr)
		for _, classId := range classIds {
			_, err = stmt.Exec(examId, classId, time.Now(), time.Now())
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		}

		// 4、插入考试记录表
		sqlStr = "insert into exam_record(create_at, update_at, user_id, exam_id,status) values (?,?,?,?,?)"
		stmt, err = tx.Prepare(sqlStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

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

		return result.SuccessResult(examId)
	})
}

func ExamDetail(id string) *result.Result {

	// 1.查询考试
	sqlStr := "select e.*,t.name tp_name,t.score from exam e left join test_paper t on e.tp_id=t.id where e.id=? and e.delete_at is null"

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

	// 2.查询班级
	var classes []*entity.Class
	sqlStr = `SELECT
	c.*
FROM
	exam e
	LEFT JOIN exam_class ec ON e.id = ec.exam_id
	LEFT JOIN class c ON c.id = ec.class_id 
WHERE
	e.id = ? and c.delete_at is null
GROUP BY
	c.id
	`
	err = config.DB.Select(&classes, sqlStr, id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}
	e.Classes = classes

	return result.SuccessResult(e)
}

func ExamList(page, size int, sort string, query map[string]interface{}) *result.Result {
	sqlStr := "select e.*,t.name tp_name,t.score from exam e left join test_paper t on e.tp_id=t.id where e.delete_at is null "
	params := make([]interface{}, 0)

	if query["name"] != nil {
		params = append(params, "%"+query["name"].(string)+"%")
		sqlStr += "and e.name like ? "
	}

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

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

	sqlStr += "group by e.id order by e.create_at "
	if sort == "asc" {
		sqlStr += "asc "
	} else {
		sqlStr += "desc "
	}
	sqlStr += "limit ?,?"
	params = append(params, utils.PostionSize(page, size), size)

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

	// 2.查询班级
	for _, ev := range evs {
		var classes []*entity.Class
		sqlStr = `SELECT
	c.*
FROM
	exam e
	LEFT JOIN exam_class ec ON e.id = ec.exam_id
	LEFT JOIN class c ON c.id = ec.class_id 
WHERE
	e.id = ? and c.delete_at is null and c.id is not null
GROUP BY
	c.id 
	`
		err = config.DB.Select(&classes, sqlStr, ev.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		ev.Classes = classes
	}

	// 总数
	sqlStr = "select count(e.id) from exam e left join test_paper t on e.tp_id=t.id where e.delete_at is null "
	params = make([]interface{}, 0)

	if query["name"] != nil {
		params = append(params, "%"+query["name"].(string)+"%")
		sqlStr += "and e.name like ? "
	}

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

	if query["end_at"] != nil {
		params = append(params, query["end_at"])
		sqlStr += "and e.end_at = ? "
	}
	var total int
	err = config.DB.Get(&total, sqlStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return result.SUCCESS.SetData(result.NewPage(page, len(evs), total, evs))
}

func UpdateExam(e *entity.Exam) *result.Result {

	tx, err := config.DB.Beginx()

	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 1、获取考生人数
		classIds := strings.Split(e.ClassIds, ",")
		var users []*entity.User
		sqlStr := "select id,class_id from user where class_id in (?)"
		var query string
		var args []interface{}
		query, args, err = sqlx.In(sqlStr, classIds)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err.Error())
		}
		query = config.DB.Rebind(query)
		err = config.DB.Select(&users, query, args...)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 2、更新
		sqlStr = "update  exam set name=?,update_at=?,start_at=?,end_at=?,publish_at=?,duration=?,status=?,tp_id=?,user_count=? where id=? and delete_at is null "

		var res sql.Result
		res, err = config.DB.Exec(sqlStr, e.Name, time.Now(), e.StartAt, e.EndAt, e.PublishAt, e.Duration, e.Status, e.TpId, len(users), e.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		affected, _ := res.RowsAffected()

		// 3、删除不是该班级学生的考试记录(班级 用户)
		sqlStr = `DELETE er FROM exam_record er,exam_class ec WHERE er.exam_id = ec.exam_id and ec.class_id not in (?) and er.exam_id=?`
		query, args, err = sqlx.In(sqlStr, classIds, e.Id)
		_, err = tx.Exec(query, args...)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}

		// 4、插入考试班级表
		// 4.1、 先删除所有关联
		sqlStr = "delete from exam_class where exam_id = ?"
		_, err = tx.Exec(sqlStr, e.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}

		// 4.2、 重新建立关联
		sqlStr = "insert into exam_class(exam_id,class_id,create_at,update_at) values (?,?,?,?)"
		var stmt *sql.Stmt
		stmt, err = tx.Prepare(sqlStr)
		classMap := map[string]byte{}
		for _, classId := range classIds {
			classMap[classId] = 0
			_, err = stmt.Exec(e.Id, classId, time.Now(), time.Now())
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
		}

		// 4、插入考试记录表,如果user的classid存在则(不管,或者插入),不存在删除
		sqlStr = "insert into exam_record(create_at, update_at, user_id, exam_id,status) values (?,?,?,?,?) "
		stmt, err = tx.Prepare(sqlStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		for _, user := range users {
			sqlStr = "select id from exam_record er where user_id = ? limit 1"
			var userId int64
			err = tx.Get(&userId, sqlStr, user.ID)
			if err != nil && err != sql.ErrNoRows {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
			if userId == 0 {
				_, err = stmt.Exec(time.Now(), time.Now(), user.ID, e.Id, entity.NOT_TESTED)
				if err != nil {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}
			}
		}

		return result.SuccessResult(result.NewResultChange(affected))
	})
}

func DeleteExams(ids []string) *result.Result {

	sqlStr := "update exam set delete_at=? where id in (?)"

	query, param, err := sqlx.In(sqlStr, time.Now(), ids)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

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

	affected, _ := res.RowsAffected()
	return result.SUCCESS.SetData(result.NewResultChange(affected))
}