oilseal-exam/dao/dao-class.go

package dao

import (
	"database/sql"
	"exam_system/config"
	"exam_system/entity"
	"exam_system/result"
	"exam_system/utils"
	"exam_system/vo"
	"github.com/jmoiron/sqlx"
	"time"
)

func AddClass(c *entity.Class) *result.Result {

	tx, err := config.DB.Beginx()
	if err != nil {
		tx.Commit()
		return result.UNKNOW_ERROR.SetData(err)
	}
	return utils.Transation(tx, err, func(tx *sqlx.Tx) *result.Result {
		// 判断是否存在term
		sqlStr := "select id from term where id = ? and delete_at is null limit 1"
		var termId int64
		err = tx.Get(&termId, sqlStr, c.TermId)
		if err != nil {
			if err == sql.ErrNoRows {
				return result.DATA_NOT_FOUND
			}
			return result.UNKNOW_ERROR.SetData(err)
		}

		// 插入
		sqlStr = "insert into class(name,term_id,create_at,update_at) values (?,?,?,?)"
		res, err := config.DB.Exec(sqlStr, c.Name, c.TermId, time.Now(), time.Now())
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}

		id, _ := res.LastInsertId()

		return result.SuccessResult(id)
	})

}

func ClassDetail(id string) *result.Result {
	sqlStr := `SELECT
	c.id,
	c.name,
	c.term_id,
	c.create_at,
	c.update_at,
	t.name term_name
FROM
	class c,
	term t 
WHERE
	c.id =? 
	AND c.delete_at IS NULL 
	AND c.term_id = t.id`

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

	return result.SuccessResult(&c)
}

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

	parm := make([]interface{}, 0)
	sqlStr := "select c.* from class c,term t where c.term_id = t.id and c.delete_at is null"

	if query["term_id"] != "" {
		sqlStr += `and t.id=? `
		parm = append(parm, query["term_id"])
	}
	sqlStr += `order by create_at `

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

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

	// 获取总数
	sqlStr = "select count(c.id) from class c "
	parm = make([]interface{}, 0)
	if query["term_id"] != "" {
		sqlStr += `,term t where c.term_id = t.id and t.id=? and c.delete_at is null`
		parm = append(parm, query["term_id"])
	}

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

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

func UpdateClass(t *entity.Class) *result.Result {
	// 判断是否存在term
	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 := "select id from term where id = ? and delete_at is null limit 1"
		var termId int64
		err = tx.Get(&termId, sqlStr, t.TermId)
		if err != nil {
			if err == sql.ErrNoRows {
				return result.DATA_NOT_FOUND
			}
			return result.UNKNOW_ERROR.SetData(err)
		}

		sqlStr = `update  class set name=?,term_id=?,update_at=? where id=? and delete_at is  null `
		var res sql.Result
		res, err = config.DB.Exec(sqlStr, t.Name, t.TermId, time.Now(), t.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetData(err)
		}
		affected, _ := res.RowsAffected()

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

}

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

	sqlStr := "update class c left join user u on c.id = u.class_id set c.delete_at=?,u.delete_at=? where c.id in (?)"

	query, param, err := sqlx.In(sqlStr, time.Now(),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))
}