oilseal-exam/dao/dao-term.go

package dao

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

	"github.com/jmoiron/sqlx"
)

func AddTerm(t *entity.Term) *result.Result {

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

	id, _ := res.LastInsertId()

	return result.SuccessResult(id)
}

func TermDetail(id string) *result.Result {
	sqlStr := "select * from term where id=?"

	var t entity.Term
	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 TermList(page, size int, sort string, _ map[string]interface{}) *result.Result {
	sqlStr := `SELECT
	t.*,
	count( u.id ) user_num 
FROM
	term t
	LEFT JOIN class c ON t.id = c.term_id
	LEFT JOIN user u ON c.id = u.class_id and u.delete_at is null
WHERE
	t.delete_at IS NULL
GROUP BY
	t.id 
ORDER BY
	t.create_at `
	if sort == "asc" {
		sqlStr += "asc "
	} else {
		sqlStr += "desc "
	}
	sqlStr += "limit ?,?"

	var tv []*vo.TermVo
	err := config.DB.Select(&tv, sqlStr, utils.PostionSize(page, size), size)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}
	for _, t := range tv {
		var c []*vo.ClassVo
		sqlStr = "select c.*,count(u.id) user_num from class c left join user u on c.id=u.class_id and u.delete_at is null where c.delete_at is null and c.term_id=? group by c.id"

		err = config.DB.Select(&c, sqlStr, t.Id)
		if err != nil {
			return result.UNKNOW_ERROR.SetMsg(err.Error())
		}
		t.Classes = c
	}

	var count int
	sqlStr = "select count(id) from term where delete_at is null"
	err = config.DB.Get(&count, sqlStr)
	if err != nil {
		return result.UNKNOW_ERROR.SetMsg(err.Error())
	}
	return result.SUCCESS.SetData(result.NewPage(page, len(tv), count, tv))
}

func UpdateTerm(t *entity.Term) *result.Result {
	sqlStr := "update  term set name=?,update_at=? where id=? and delete_at is  null "

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

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

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

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

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