oilseal-exam/dao/dao-question-bank.go

package dao

import (
	"database/sql"
	"exam_system/config"
	"exam_system/entity"
	"exam_system/result"
	"exam_system/utils"
	"exam_system/vo"
	"fmt"
	"time"
)

func AddQuestionBank(t *entity.QuestionBank) *result.Result {

	sqlStr := "insert into question_bank(name,status,description,create_at,update_at) values (?,?,?,?,?)"
	res, err := config.DB.Exec(sqlStr, t.Name, t.Status, t.Description, time.Now(), time.Now())
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}

	id, _ := res.LastInsertId()

	return result.SuccessResult(id)
}

func QuestionBankDetail(id string) *result.Result {
	sqlStr := "select q.*,count(s.id) sub_num from question_bank q left join subject s on q.id=s.qb_id where q.id=? and q.delete_at is null group by q.id"

	var t vo.QuestionBankVo
	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 QuestionBankList(page, size int, sort string, query map[string]interface{}) *result.Result {


	// 1、查询列表
	sqlStr := "select q.*,count( s.id ) sub_num  from question_bank q left join subject s on q.id=s.qb_id and s.delete_at is null where q.delete_at is null "

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

	sqlStr += "group by q.id  order by create_at "
	if sort == "desc" {
		sqlStr += "desc "
	}
	sqlStr += "limit ?,?"

	params = append(params, utils.PostionSize(page,size), size)
	var ts []*vo.QuestionBankVo
	err := config.DB.Select(&ts, sqlStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 3、查询所有
	totalStr := "select count(id) from question_bank where delete_at is null "
	params = make([]interface{}, 0)
	if query["status"] != nil {
		totalStr += "and status = ? "
		params = append(params, query["status"])
	}
	if query["key"] != nil {
		totalStr += "and name like ? "
		params = append(params, "%"+query["key"].(string))
	}
	var total int
	err = config.DB.Get(&total, totalStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

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

func UpdateQuestionBank(q *entity.QuestionBank) *result.Result {
	sqlStr := "update  question_bank set name=?,description=?,status=?,update_at=? where id=? and delete_at is  null "

	res, err := config.DB.Exec(sqlStr, q.Name, q.Description, q.Status, time.Now(), q.Id)
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}
	affected, _ := res.RowsAffected()

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

func DeleteQuestionBanks(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 question_bank 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))
}