oilseal-exam/dao/dao-exam-record.go

package dao

import (
	"database/sql"
	"encoding/json"
	"exam_system/config"
	"exam_system/entity"
	"exam_system/result"
	"exam_system/utils"
	"exam_system/vo"
	"fmt"
	"strconv"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/jmoiron/sqlx"
	uuid "github.com/satori/go.uuid"
	"github.com/xuri/excelize/v2"
)

func AddExamRecord(er *entity.ExamRecord) *result.Result {

	// 2、插入
	uuId := uuid.NewV4().String()

	sqlStr := "update exam_record er set create_at=?,update_at=?,token=?,ip=inet_aton(?),status=? where er.id = ? and er.user_id=?"

	_, err := config.DB.Exec(sqlStr, time.Now(), time.Now(), uuId, er.Ip, entity.TESTING, er.Id, er.UserId)

	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}
	return result.SuccessResult(gin.H{
		"id":    er.Id,
		"token": uuId,
	})
}

func ExamRecordDetail(id, uid int) *result.Result {

	// 1、考试详情
	sqlStr := `select e.name,e.create_at,e.update_at,e.duration,e.start_at,e.end_at,e.publish_at,tp.score,tp.pass_score,er.score user_score,timestampdiff(minute,er.create_at,er.update_at) user_take_time
from user u left join  exam_record er on u.id = er.user_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.id = ? and u.id = ? and e.delete_at is null `

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

	if t.PublishAt.After(time.Now()) {
		t.UserScore = nil
	}

	// 题目答案, 用户答案
	sqlStr = "select er.answer from exam_record er where er.id = ? and er.delete_at is null"
	var answerStr string
	err = config.DB.Get(&answerStr, sqlStr, id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	var avs []*vo.AnswerVo
	err = json.Unmarshal([]byte(answerStr), &avs)
	if err != nil {
		return result.FORMATE_ERROR
	}

	for i := 0; i < len(avs); i++ {
		var av vo.AnswerVo
		sqlStr = "select * from subject where id = ?"
		err = config.DB.Get(&av, sqlStr, avs[i].Id)
		if err != nil {
			if err == sql.ErrNoRows {
				continue
			}
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		if t.PublishAt.After(time.Now()) {
			av.Answer = ""
		}
		av.OwnAnswer = avs[i].OwnAnswer
		avs[i] = &av
	}
	return result.SuccessResult(gin.H{
		"exam_detail": &t,
		"subject":     &avs,
	})
}

func ExamRecordList(page, size int, query map[string]interface{}, uid int) *result.Result {
	// 1、获取list
	sqlStr := `select er.id,e.name,e.create_at,e.update_at,e.duration,e.start_at,e.end_at,e.publish_at,inet_ntoa(er.ip) ip,tp.score,tp.pass_score,er.score user_score,timestampdiff(minute,er.create_at,er.update_at) user_take_time
from user u left join  exam_record er on u.id = er.user_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 u.id = ? and e.delete_at is null and er.status!=0 `

	params := []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	sqlStr += "group by er.id "

	if query["create_at"] != nil {
		if query["create_at"] == "desc" {
			sqlStr += "order by create_at desc "
		} else {
			sqlStr += "order by create_at asc "
		}
	} else if query["user_take_time"] != nil {
		if query["user_take_time"] == "desc" {
			sqlStr += "order by user_take_time desc "
		} else {
			sqlStr += "order by user_take_time asc "
		}
	} else if query["user_score"] != nil {
		if query["user_score"] == "desc" {
			sqlStr += "order by user_score desc "
		} else {
			sqlStr += "order by user_score asc "
		}
	} else {
		sqlStr += "order by create_at desc "
	}

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

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

	// 2、总数
	sqlStr = `select count(distinct(er.id))
from user u left join  exam_record er on u.id = er.user_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 u.id = ? and e.delete_at is null and er.status!=0 `
	params = []interface{}{uid}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	sqlStr += "group by er.id"
	for _, e := range evos {
		if e.PublishAt.After(time.Now()) {
			e.UserScore = nil
		}
	}

	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(evos), total, evos))
}

func UpdateExamRecord(erv *vo.ExamRecordVo) *result.Result {
	tx, err := config.DB.Beginx()
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 1、查询是否存在该记录
		sqlStr := `select er.id, er.exam_id
from exam_record er
         left join exam e on e.id = er.exam_id
where er.id = ?
  and er.token = ?
and er.update_at - er.create_at <= e.duration
		and e.end_at > ?
`
		err = tx.Get(erv, sqlStr, erv.Id, erv.Token, time.Now())
		if err != nil {
			if err == sql.ErrNoRows {
				return result.DATA_NOT_FOUND.SetMsg("该场考试已过期")
			}
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		sqlStr = "update exam_record set token = null,status = ? where id = ?"
		var res sql.Result
		_, err = tx.Exec(sqlStr, entity.TESTED, erv.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		// 2、查询出所需id
		sqlStr = "select c.sub_ids from chapter c left join exam e on  c.tp_id = e.tp_id where e.id=?"

		var subIdStrs []string
		err = tx.Select(&subIdStrs, sqlStr, erv.ExamId)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		subIds := make([]*vo.SubIdsVo, 0)
		for _, subIdStr := range subIdStrs {
			var subIdsVo []*vo.SubIdsVo
			err = json.Unmarshal([]byte(subIdStr), &subIdsVo)
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
			subIds = append(subIds, subIdsVo...)
		}

		// 2.1 构造一个key为id的 map
		subIdMap := make(map[int]int, 0)
		for _, subId := range subIds {
			subIdMap[*subId.Id] = *subId.Score
		}

		// 2、 批量查询id
		toatalScore := 0
		for _, answerVo := range erv.AnswerVo {
			// 判断提交的试题是否在考试范围内
			if _, ok := subIdMap[*answerVo.Id]; !ok {
				return result.UNKNOW_ERROR.SetMsg("不存在该试题")
			}

			var score int
			sqlStr = "select count(id) from subject where id=? and answer=?"
			err = tx.Get(&score, sqlStr, answerVo.Id, utils.SubjectSort(answerVo.OwnAnswer))
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}
			toatalScore += score * subIdMap[*answerVo.Id]
			delete(subIdMap, *answerVo.Id)
		}
		if len(subIdMap) != 0 {
			return result.UNKNOW_ERROR.SetMsg("试题提交不完整")
		}

		var anwserData []byte
		anwserData, err = json.Marshal(erv.AnswerVo)

		// 插入记录表
		sqlStr = "update exam_record set score=?,answer=?,update_at=? where id=? and delete_at is null "

		res, err = tx.Exec(sqlStr, toatalScore, string(anwserData), time.Now(), erv.Id)

		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		affected, _ := res.RowsAffected()

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

func DeleteExamRecords(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 exam_record 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())
	}
	affected, _ := res.RowsAffected()

	return result.SUCCESS.SetData(result.NewResultChange(affected))
}

func AdminExamRecordDetail(id int) *result.Result {

	// 1、考试详情
	//	sqlStr := `select e.name,e.create_at,e.update_at,e.duration,e.start_at,e.end_at,e.publish_at,tp.score,tp.pass_score,er.score user_score,timestampdiff(minute,er.create_at,er.update_at) user_take_time
	//from exam_record er left join exam e on er.exam_id = e.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
	//         left join user u on u.class_id = c.id
	//		 where er.id=? and e.delete_at is null
	//		 GROUP BY er.id`
	//
	//	var t vo.ExamRecordVo1
	//	err := config.DB.Get(&t, sqlStr, id)
	//	if err != nil {
	//		if err == sql.ErrNoRows {
	//			return result.DATA_NOT_FOUND
	//		}
	//		return result.UNKNOW_ERROR.SetMsg(err.Error())
	//	}

	// 题目答案, 用户答案
	sqlStr := "select er.answer from exam_record er where er.id = ? and er.delete_at is null"
	// ! 20240305 这里修改answer为*string类型 数据库为null值会报错
	var answerStr *string
	err := config.DB.Get(&answerStr, sqlStr, id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}
	if answerStr == nil {
		return result.UNKNOW_ERROR.SetMsg("试卷数据不存在!")
	}

	var avs []*vo.AnswerVo
	err = json.Unmarshal([]byte(*answerStr), &avs)
	if err != nil {
		return result.FORMATE_ERROR
	}

	for i := 0; i < len(avs); i++ {
		var av vo.AnswerVo
		sqlStr = "select * from subject where id = ?"
		err = config.DB.Get(&av, sqlStr, avs[i].Id)
		if err != nil {
			if err.Error() == "sql: no rows in result set" {
				continue
			}
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		av.OwnAnswer = avs[i].OwnAnswer
		if avs[i].OwnAnswer == "-1" {
			av.OwnAnswer = ""
		}

		avs[i] = &av
	}

	return result.SuccessResult(gin.H{
		//"exam_detail": &t,
		"subject": &avs,
	})
}

func AdminExamRecordList(page, size int, query map[string]interface{}, examId int) *result.Result {
	// 1、获取list
	sqlStr := `select er.id,
       e.name,
       e.create_at,
       e.update_at,
       e.duration,
       e.start_at,
       e.end_at,
       e.publish_at,
       tp.score,
       tp.pass_score,
       er.score                                          user_score,
       u.username,
       c.name classname,
       timestampdiff(minute, er.create_at, er.update_at) user_take_time
from exam_record er
	LEFT JOIN exam e ON er.exam_id = e.id
	LEFT JOIN test_paper tp ON e.tp_id = tp.id
	LEFT JOIN user u on er.user_id = u.id
	LEFT JOIN class c on u.class_id = c.id
where e.id = ?
  and e.delete_at is null `

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

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

	if query["create_at"] != nil {
		if query["create_at"] == "desc" {
			sqlStr += "order by create_at desc "
		} else {
			sqlStr += "order by create_at asc "
		}
	} else if query["user_take_time"] != nil {
		if query["user_take_time"] == "desc" {
			sqlStr += "order by user_take_time desc "
		} else {
			sqlStr += "order by user_take_time asc "
		}
	} else if query["user_score"] != nil {
		if query["user_score"] == "desc" {
			sqlStr += "order by user_score desc "
		} else {
			sqlStr += "order by user_score asc "
		}
	} else {
		sqlStr += "order by create_at desc "
	}

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

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

	// 2、总数
	sqlStr = `select count(er.id)
from exam_record er
	LEFT JOIN exam e ON er.exam_id = e.id
	LEFT JOIN test_paper tp ON e.tp_id = tp.id
	LEFT JOIN user u on er.user_id = u.id
	LEFT JOIN class c on u.class_id = c.id
where e.id = ? and e.delete_at is null `
	params = []interface{}{examId}
	if query["name"] != nil {
		sqlStr += "and e.name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	if query["class_id"] != nil {
		sqlStr += "and c.id = ? "
		params = append(params, query["class_id"])
	}

	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(evos), total, evos))
}

func AdminDownloadExamRecord(file *excelize.File, id string) *result.Result {
	// 1、获取list
	sqlStr := `SELECT
    e.name exam_name,
	u.username,
	CONCAT(t.name,c.name,'>') term_class,
	u.username name,
	u.sid,
	er.create_at,
	er.update_at,
	inet_ntoa( er.ip ) ip,
	er.score user_score,
	timestampdiff( MINUTE, er.create_at, er.update_at ) user_take_time 
FROM
    exam e left join exam_record er on e.id = er.exam_id
	LEFT JOIN user u  ON u.id = er.user_id 
	LEFT JOIN class c ON u.class_id = c.id
	LEFT JOIN term  t on c.term_id = t.id
WHERE
	u.delete_at IS NULL and e.id = ?
`

	var evos []*vo.ExamRecordVo2
	err := config.DB.Select(&evos, sqlStr, id)
	if err != nil {
		if err == sql.ErrNoRows {
			return result.DATA_NOT_FOUND
		}
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	file.SetCellValue("Sheet1", "A1", evos[0].ExamName)
	i := 4
	for _, evo := range evos {

		if evo.TermClass != nil {
			file.SetCellValue("Sheet1", "A"+strconv.Itoa(i), *evo.TermClass)
		}
		file.SetCellValue("Sheet1", "B"+strconv.Itoa(i), evo.Name)
		file.SetCellValue("Sheet1", "C"+strconv.Itoa(i), evo.Sid)
		file.SetCellValue("Sheet1", "D"+strconv.Itoa(i), evo.CreateAt.Format("2006-01-02 15:04:05"))
		file.SetCellValue("Sheet1", "E"+strconv.Itoa(i), evo.UpdateAt.Format("2006-01-02 15:04:05"))
		if evo.UserScore != nil {
			file.SetCellValue("Sheet1", "F"+strconv.Itoa(i), *evo.UserScore)
		}
		i++
	}

	return result.SUCCESS
}