oilseal-exam/dao/dao-subject.go

package dao

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

	"github.com/jmoiron/sqlx"
	"github.com/xuri/excelize/v2"
)

func AddSubject(s *entity.Subject) *result.Result {

	sqlStr := "insert into subject(question,type,opt_a,opt_b,opt_c,opt_d,answer,analysis,status,qb_id,create_at,update_at) values (?,?,?,?,?,?,?,?,?,?,?,?)"
	res, err := config.DB.Exec(sqlStr, s.Question, s.Type, s.OptA, s.OptB, s.OptC, s.OptD, utils.SubjectSort(s.Answer), s.Analysis, s.Status, s.QbId, time.Now(), time.Now())
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}

	id, _ := res.LastInsertId()

	return result.SuccessResult(id)
}

func SubjectDetail(id string) *result.Result {
	sqlStr := "select * from subject where id=? and delete_at is null"

	var t entity.Subject
	err := config.DB.Get(&t, sqlStr, id)
	if err != nil {
		if err == sql.ErrNoRows {
			return result.DATA_NOT_FOUND
		}
		return result.UNKNOW_ERROR.SetData(err.Error())
	}

	return result.SuccessResult(&t)
}

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

	// 1、获取列表
	parms := make([]interface{}, 0)
	sqlStr := "select * from subject where delete_at is null "

	if query["key"] != nil {
		sqlStr += "and question like ? "
		parms = append(parms, "%"+query["key"].(string))
	}
	if query["qb_id"] != nil {
		sqlStr += "and qb_id = ? "
		parms = append(parms, query["qb_id"])
	}
	if query["status"] != nil {
		sqlStr += "and status=? "
		parms = append(parms, query["status"])
	}
	if query["type"] != nil {
		sqlStr += "and type=? "
		parms = append(parms, query["type"])
	}

	sqlStr += "order by create_at "

	if sort == "desc" {
		sqlStr += "desc "
	} else {
		sqlStr += "asc "
	}
	sqlStr += "limit ?,?"
	parms = append(parms, utils.PostionSize(page, size), size)

	var ts []*entity.Subject
	err := config.DB.Select(&ts, sqlStr, parms...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 2、获取元素个数
	sqlStr = "select count(id) from subject where delete_at is null "
	parms = make([]interface{}, 0)
	if query["key"] != nil {
		sqlStr += "and question like ? "
		parms = append(parms, "%"+query["key"].(string))
	}
	if query["qb_id"] != nil {
		sqlStr += "and qb_id = ? "
		parms = append(parms, query["qb_id"])
	}
	if query["status"] != nil {
		sqlStr += "and status=? "
		parms = append(parms, query["status"])
	}
	if query["type"] != nil {
		sqlStr += "and type=? "
		parms = append(parms, query["type"])
	}

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

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

func UpdateSubject(s *entity.Subject) *result.Result {
	sqlStr := "update  subject set question=?,type=?,opt_a=?,opt_b=?,opt_c=?,opt_d=?,answer=?,analysis=?,status=?,qb_id=?,update_at=? where id=? and delete_at is null "

	res, err := config.DB.Exec(sqlStr, s.Question, s.Type, s.OptA, s.OptB, s.OptC, s.OptD, utils.SubjectSort(s.Answer), s.Analysis, s.Status, s.QbId, time.Now(), s.Id)
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}
	affected, _ := res.RowsAffected()

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

func DeleteSubjects(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 subject 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 AddSubjectBatch(file multipart.File, id string) *result.Result {

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

	typeName, err := xlsFile.GetCellValue("Sheet", "A1")
	typeName = strings.TrimSpace(typeName)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 1.设置插入类型
	tp := 0
	switch typeName {
	case "单选题":
		tp = entity.SINGEL_CHOICE
	case "填空题":
		tp = entity.Completion
	case "判断题":
		tp = entity.Judgement
	case "多选题":
		tp = entity.MULTIPLE_CHOICE
	default:
		return result.UNKNOW_ERROR.SetMsg("excel格式错误")
	}

	tx, err := config.DB.Beginx()
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	return utils.Transation(tx, err, func(_ *sqlx.Tx) *result.Result {
		// 1.sql准备
		sqlStr := "insert into subject(question,type,opt_a,opt_b,opt_c,opt_d,answer,analysis,status,qb_id,create_at,update_at) values (?,?,?,?,?,?,?,?,?,?,?,?)"
		var stmt *sql.Stmt
		stmt, err = config.DB.Prepare(sqlStr)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		var count int64
		i := 3
		for {
			question, _ := xlsFile.GetCellValue("Sheet", "A"+strconv.Itoa(i))
			var optA, optB, optC, optD, answer string
			if typeName == "判断题" {
				answer, _ = xlsFile.GetCellValue("Sheet", "B"+strconv.Itoa(i))
			} else {
				optA, _ = xlsFile.GetCellValue("Sheet", "B"+strconv.Itoa(i))
				optB, _ = xlsFile.GetCellValue("Sheet", "C"+strconv.Itoa(i))
				optC, _ = xlsFile.GetCellValue("Sheet", "D"+strconv.Itoa(i))
				optD, _ = xlsFile.GetCellValue("Sheet", "E"+strconv.Itoa(i))
				answer, _ = xlsFile.GetCellValue("Sheet", "F"+strconv.Itoa(i))
				answer = utils.SubjectFormat(answer)
			}

			if question == "" {
				break
			}

			var res sql.Result
			res, err = stmt.Exec(question, tp, optA, optB, optC, optD, answer, "", entity.USE, id, time.Now(), time.Now())
			if err != nil {
				continue
			}
			affected, _ := res.RowsAffected()
			count += affected
			i++
		}

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

}

func DownloadSubject(file *excelize.File, id, tp int) *result.Result {

	sqlStr := "select s.*,q.name qb_name from subject s left join question_bank q on s.qb_id = q.id where q.id = ? and s.type =? and s.delete_at is null"
	var subs []*vo.SubjectVo
	err := config.DB.Select(&subs, sqlStr, id, tp)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 题型
	name, err := entity.GetSubjectTypeName(tp)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	if len(subs) == 0 {
		return result.SUCCESS.SetData("没有" + name)
	}

	i := 3

	for _, sub := range subs {
		file.SetCellValue("Sheet", "A"+strconv.Itoa(i), sub.Id)
		file.SetCellValue("Sheet", "B"+strconv.Itoa(i), sub.Question)
		if tp == entity.Judgement {
			file.SetCellValue("Sheet", "C"+strconv.Itoa(i), utils.SubjectParse(sub.Answer))
		} else {
			file.SetCellValue("Sheet", "C"+strconv.Itoa(i), sub.OptA)
			file.SetCellValue("Sheet", "D"+strconv.Itoa(i), sub.OptB)
			file.SetCellValue("Sheet", "E"+strconv.Itoa(i), sub.OptC)
			file.SetCellValue("Sheet", "F"+strconv.Itoa(i), sub.OptD)
			sub.Answer = utils.SubjectParse(sub.Answer)
			file.SetCellValue("Sheet", "G"+strconv.Itoa(i), utils.SubjectParse(sub.Answer))
		}
		i++
	}

	return result.SUCCESS.SetData(subs[0].QbName + "_" + name)
}