oilseal-exam/dao/dao-test-paper.go

package dao

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

	"github.com/jmoiron/sqlx"
)

func AddTestPaper(t *vo.TestPaperVo) *result.Result {

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

	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {

		// 1.插入试卷
		sqlStr := "insert into test_paper(name,create_at,update_at,status,score,pass_score) values (?,?,?,?,?,?)"
		var res sql.Result
		res, err = tx.Exec(sqlStr, t.Name, time.Now(), time.Now(), entity.USE, t.Score, t.PassScore)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		id, _ := res.LastInsertId()

		var score int64
		for _, chapter := range t.Chapters {

			var subData []byte
			subData, err = json.Marshal(chapter.SubList)
			// 2.插入章节
			sqlStr = "insert into chapter(name,description,sub_ids,tp_id,`index`) values (?,?,?,?,?)"
			// sublist试题
			var res2 sql.Result
			res2, err = tx.Exec(sqlStr, chapter.Name, chapter.Description, string(subData), id, chapter.Index)
			if err != nil {
				return result.UNKNOW_ERROR.SetData(err)
			}
			affected, _ := res2.RowsAffected()
			score += affected
		}

		return result.SuccessResult(id)
	})

}

func TestPaperDetail(id string) *result.Result {
	// 1、查询试卷
	sqlStr := "select * from test_paper where id=? and delete_at is null"

	var t vo.TestPaperVo
	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())
	}

	// 2、查询相关章节
	sqlStr = "select * from chapter where tp_id =? order by `index`"
	var chapters []*vo.ChapterVo
	err = config.DB.Select(&chapters, sqlStr, t.Id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 3、获取相关试题,chapter
	for _, chap := range chapters {
		var sidsvo []*vo.SubIdsVo
		err = json.Unmarshal([]byte(chap.SubIds), &sidsvo)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		var subList []*vo.SubjectVo
		for _, sidvo := range sidsvo {
			var sub vo.SubjectVo
			sqlStr = "select * from subject where id = ?"
			err = config.DB.Get(&sub, sqlStr, sidvo.Id)
			if err != nil && err.Error() != "sql: no rows in result set" {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}

			sub.Score = sidvo.Score
			subList = append(subList, &sub)
		}

		chap.SubList = subList
	}
	t.Chapters = chapters

	return result.SuccessResult(t)
}

func TestPaperDetail2(id string) *result.Result {
	// 1、查询试卷
	sqlStr := "select * from test_paper where id=? and delete_at is null"

	var t vo.TestPaperVo
	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())
	}

	// 2、查询相关章节
	sqlStr = "select * from chapter where tp_id =? order by `index`"
	var chapters []*vo.ChapterVo
	err = config.DB.Select(&chapters, sqlStr, t.Id)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}

	// 3、获取相关试题,chapter
	for _, chap := range chapters {
		var sidsvo []*vo.SubIdsVo
		err = json.Unmarshal([]byte(chap.SubIds), &sidsvo)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}

		var subList []*vo.SubjectVo
		for _, sidvo := range sidsvo {
			var sub vo.SubjectVo
			sqlStr = "select id, create_at, update_at, delete_at, type, opt_a, opt_b, opt_c, opt_d, analysis, status, question, qb_id from subject where id = ?"
			err = config.DB.Get(&sub, sqlStr, sidvo.Id)
			if err != nil && err.Error() != "sql: no rows in result set" {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}

			sub.Score = sidvo.Score
			subList = append(subList, &sub)
		}

		chap.SubList = subList
	}
	t.Chapters = chapters

	return result.SuccessResult(t)
}

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

	// 1、获取paper
	sqlStr := "select * from test_paper where delete_at is null "

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

	if query["status"] != nil {
		sqlStr += "and status = ? "
		params = append(params, fmt.Sprintf("%1.0f", query["status"].(float64)))
	}

	sqlStr += "order by create_at "

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

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

	// 2、获取章节
	for _, t := range tp {
		// 2、查询相关章节
		chapterStr := "select * from chapter where tp_id =?"
		var chapters []*vo.ChapterVo
		err = config.DB.Select(&chapters, chapterStr, t.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}

		// 3、获取相关试题,chapter
		for _, chap := range chapters {
			var sidsvo []*vo.SubIdsVo
			err = json.Unmarshal([]byte(chap.SubIds), &sidsvo)
			if err != nil {
				return result.UNKNOW_ERROR.SetMsg(err.Error())
			}

			var subList []*vo.SubjectVo
			for _, sidvo := range sidsvo {
				var sub vo.SubjectVo
				sqlStr = "select * from subject where id = ?"
				err = config.DB.Get(&sub, sqlStr, sidvo.Id)
				if err != nil && err.Error() != "sql: no rows in result set" {
					return result.UNKNOW_ERROR.SetMsg(err.Error())
				}

				sub.Score = sidvo.Score
				subList = append(subList, &sub)
			}

			chap.SubList = subList
		}
		t.Chapters = chapters

	}

	// 获取总数
	sqlStr = "select count(id) from test_paper where delete_at is null "

	var total int
	params = make([]interface{}, 0)
	if query["name"] != nil {
		sqlStr += "and name like ? "
		params = append(params, "%"+query["name"].(string)+"%")
	}
	if query["status"] != nil {
		sqlStr += "and status = ? "
		params = append(params, fmt.Sprintf("%1.0f", query["status"].(float64)))
	}
	err = config.DB.Get(&total, sqlStr, params...)
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err.Error())
	}

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

func UpdateTestPaper(t *vo.TestPaperVo) *result.Result {

	tx, err := config.DB.Beginx()
	if err != nil {
		return result.UNKNOW_ERROR.SetData(err)
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {

		sqlStr := "update  test_paper set name=?,update_at=?,status=?,score=?,pass_score=? where id=? and delete_at is null "

		var res sql.Result
		res, err = config.DB.Exec(sqlStr, t.Name, time.Now(), t.Status, t.Score, t.PassScore, t.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		affected, _ := res.RowsAffected()

		sqlStr = "delete from chapter where tp_id = ?"
		_, err = tx.Exec(sqlStr, t.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}

		for _, chapter := range t.Chapters {

			sqlStr = "insert into chapter(name,description,sub_ids,tp_id) values (?,?,?,?)"

			var subData []byte
			subData, err = json.Marshal(chapter.SubList)

			// sublist试题
			res, err = tx.Exec(sqlStr, chapter.Name, chapter.Description, subData, t.Id)
			if err != nil {
				return result.UNKNOW_ERROR.SetData(err)
			}

			affected2, _ := res.RowsAffected()

			affected += affected2
		}

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

func DeleteTestPapers(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 test_paper 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))
}