300 lines
9.3 KiB
Go
300 lines
9.3 KiB
Go
package dao
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"strings"
|
|
"time"
|
|
|
|
"go-common/app/service/openplatform/anti-fraud/model"
|
|
"go-common/library/database/sql"
|
|
"go-common/library/log"
|
|
"go-common/library/xstr"
|
|
)
|
|
|
|
const (
|
|
_getQusSQL = "SELECT question_id ,question_type,answer_type,question_name,question_bank_id,difficulty,is_deleted FROM question where question_id=? and is_deleted =0 "
|
|
_addQusSQL = "INSERT INTO question(question_id ,question_type,answer_type,question_name,question_bank_id,difficulty) VALUES(?,?,?,?,?,?)"
|
|
_getQuslistBywhereSQL = "SELECT question_id ,question_type,answer_type,question_name,question_bank_id,difficulty,is_deleted FROM question where is_deleted = 0 and question_bank_id = ? order by id desc limit ? ,? "
|
|
_getAllQusByBankID = "SELECT question_id FROM question WHERE question_bank_id = ? AND is_deleted = 0"
|
|
_getQuslistSQL = "SELECT question_id ,question_type,answer_type,question_name,question_bank_id,difficulty,is_deleted FROM question where is_deleted = ? limit ? ,? "
|
|
_getQusCntSQL = "SELECT COUNT(*) FROM question where is_deleted = 0 "
|
|
_delQusSQL = "UPDATE question set is_deleted = ? where question_id =? "
|
|
_updateQusSQL = "UPDATE question set question_type = ? ,answer_type=? ,question_name = ? , question_bank_id = ? ,difficulty = ? where question_id =? and is_deleted =0 "
|
|
_addAnswerSQL = "INSERT INTO question_answer(answer_id ,question_id,answer_content,is_correct ) VALUES(?,?,?,?)"
|
|
_multyAddAnswerSQL = "INSERT INTO question_answer(answer_id ,question_id,answer_content,is_correct ) VALUES %s "
|
|
_updateAnswerSQL = "UPDATE question_answer set answer_content = ? , is_correct =? , is_deleted =0 where answer_id = ? "
|
|
_delAnswerSQL = "UPDATE question_answer set is_deleted = 1 where question_id =? "
|
|
_getAnswerListSQL = "select answer_id ,question_id, answer_content,is_correct from question_answer where question_id =? and is_deleted =0 "
|
|
_addUserAnswerSQL = "INSERT INTO question_user_answer(uid ,question_id,platform,source,answers,is_correct ) VALUES(?,?,?,?,?,?)"
|
|
_checkAnswerSQL = "SELECT count(1) from question_answer where is_correct =1 and question_id = ? and answer_id IN(%s) "
|
|
_getRandomPicSQL = "SELECT x,y,src from question_verify_pic where id =? "
|
|
_getListPicSQL = "SELECT id from question_verify_pic limit ? ,? "
|
|
_getPicCntSQL = "SELECT COUNT(*) FROM question_verify_pic "
|
|
)
|
|
|
|
// GetQusInfo info
|
|
func (d *Dao) GetQusInfo(c context.Context, qid int64) (oi *model.Question, err error) {
|
|
oi = &model.Question{}
|
|
row := d.db.QueryRow(c, _getQusSQL, qid)
|
|
if err = row.Scan(&oi.QsID, &oi.QsType, &oi.AnswerType, &oi.QsName, &oi.QsBId, &oi.QsDif, &oi.IsDeleted); err != nil {
|
|
if err == sql.ErrNoRows {
|
|
oi = nil
|
|
err = nil
|
|
} else {
|
|
log.Error("row.Scan error(%v)", err)
|
|
}
|
|
}
|
|
return
|
|
}
|
|
|
|
// InsertQus add
|
|
func (d *Dao) InsertQus(c context.Context, oi *model.Question) (lastID int64, err error) {
|
|
res, err := d.db.Exec(c, _addQusSQL, oi.QsID, oi.QsType, oi.AnswerType, oi.QsName, oi.QsBId, oi.QsDif)
|
|
if err != nil {
|
|
log.Error("[dao.question|GetQusList] d.db.Query err: %v", err)
|
|
return
|
|
}
|
|
lastID, err = res.LastInsertId()
|
|
return
|
|
}
|
|
|
|
// GetQusList list
|
|
func (d *Dao) GetQusList(c context.Context, offset int, limitnum int, qBid int64) (res []*model.Question, err error) {
|
|
res = make([]*model.Question, 0)
|
|
_sql := _getQuslistSQL
|
|
if qBid > 0 {
|
|
_sql = _getQuslistBywhereSQL
|
|
}
|
|
rows, err := d.db.Query(c, _sql, qBid, offset, limitnum)
|
|
|
|
if err != nil {
|
|
log.Error("[dao.question|GetQusList] d.db.Query err: %v %d,%d", err, offset, limitnum)
|
|
return
|
|
}
|
|
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
oi := &model.Question{}
|
|
if err = rows.Scan(&oi.QsID, &oi.QsType, &oi.AnswerType, &oi.QsName, &oi.QsBId, &oi.QsDif, &oi.IsDeleted); err != nil {
|
|
log.Error("[dao.question|GetOrder] rows.Scan err: %v", res)
|
|
return
|
|
}
|
|
res = append(res, oi)
|
|
}
|
|
return
|
|
}
|
|
|
|
// GetQusIds ids
|
|
func (d *Dao) GetQusIds(c context.Context, bankID int64) (ids []int64, err error) {
|
|
if ids = d.GetBankQuestionsCache(c, bankID); len(ids) > 1 {
|
|
return
|
|
}
|
|
rows, err := d.db.Query(c, _getAllQusByBankID, bankID)
|
|
if err != nil {
|
|
log.Error("d.GetQusIds(%d) error(%v)", bankID, err)
|
|
return
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var temp int64
|
|
if err = rows.Scan(&temp); err != nil {
|
|
log.Error("d.GetQusIds(%d) rows.Scan() error(%v)", bankID, err)
|
|
return
|
|
}
|
|
ids = append(ids, temp)
|
|
}
|
|
d.SetBankQuestionsCache(c, bankID, ids)
|
|
return
|
|
}
|
|
|
|
// DelQus del
|
|
func (d *Dao) DelQus(c context.Context, qid int64) (affect int64, err error) {
|
|
res, err := d.db.Exec(c, _delQusSQL, 1, qid)
|
|
if err != nil {
|
|
log.Error("d.DelQus(qbid:%d, dmid:%d) error(%v)", qid, 1, err)
|
|
return
|
|
}
|
|
return res.RowsAffected()
|
|
}
|
|
|
|
// UpdateQus update
|
|
func (d *Dao) UpdateQus(c context.Context, update *model.ArgUpdateQus, answers []model.Answer) (affect int64, err error) {
|
|
res, err := d.db.Exec(c, _updateQusSQL, update.Type, update.AnType, update.Name, update.BId, update.Dif, update.QsID)
|
|
if err != nil {
|
|
log.Error("d.UpdateQus(qbid:%d, dmid:%d) error(%v)", update.QsID, update.QsID, err)
|
|
return
|
|
}
|
|
|
|
return res.RowsAffected()
|
|
}
|
|
|
|
// GetQusCount cnt
|
|
func (d *Dao) GetQusCount(c context.Context, bid int64) (total int64, err error) {
|
|
var cntSQL string
|
|
if bid == 0 {
|
|
cntSQL = _getQusCntSQL
|
|
err = d.db.QueryRow(c, cntSQL).Scan(&total)
|
|
} else {
|
|
cntSQL = _getQusCntSQL + "and question_bank_id = ?"
|
|
err = d.db.QueryRow(c, cntSQL, bid).Scan(&total)
|
|
}
|
|
if err != nil {
|
|
log.Error("d.GetQusCount error(%v)", err)
|
|
return
|
|
}
|
|
return
|
|
|
|
}
|
|
|
|
// InserAnwser add
|
|
func (d *Dao) InserAnwser(c context.Context, answer *model.AnswerAdd) (affect int64, err error) {
|
|
res, err := d.db.Exec(c, _addAnswerSQL, answer.AnswerID, answer.QsID, answer.AnswerContent, answer.IsCorrect)
|
|
if err != nil {
|
|
log.Error("d.InserAnwser() error(%v)", err)
|
|
return
|
|
}
|
|
affect, err = res.LastInsertId()
|
|
return
|
|
}
|
|
|
|
// MultiAddAnwser add
|
|
func (d *Dao) MultiAddAnwser(c context.Context, answers []*model.AnswerAdd) (err error) {
|
|
length := len(answers)
|
|
if length == 0 {
|
|
return
|
|
}
|
|
values := strings.Trim(strings.Repeat("(?, ?, ?, ?),", length), ",")
|
|
args := make([]interface{}, 0)
|
|
for _, ins := range answers {
|
|
AnswerID := time.Now().UnixNano() / 1e6
|
|
time.Sleep(time.Millisecond)
|
|
args = append(args, AnswerID, ins.QsID, ins.AnswerContent, ins.IsCorrect)
|
|
}
|
|
_, err = d.db.Exec(c, fmt.Sprintf(_multyAddAnswerSQL, values), args...)
|
|
if err != nil {
|
|
log.Error("d.InserAnwser() error(%v)", err)
|
|
return
|
|
}
|
|
|
|
return
|
|
}
|
|
|
|
// UpdateAnwser upd
|
|
func (d *Dao) UpdateAnwser(c context.Context, answer *model.AnswerAdd) (affect int64, err error) {
|
|
res, err := d.db.Exec(c, _updateAnswerSQL, answer.AnswerContent, answer.IsCorrect, answer.AnswerID)
|
|
if err != nil {
|
|
log.Error("d.UpdateAnwser() error(%v)", err)
|
|
return
|
|
}
|
|
affect, err = res.RowsAffected()
|
|
return
|
|
}
|
|
|
|
// DelAnwser del
|
|
func (d *Dao) DelAnwser(c context.Context, qusID int64) (affect int64, err error) {
|
|
res, err := d.db.Exec(c, _delAnswerSQL, qusID)
|
|
if err != nil {
|
|
log.Error("d.UpdateAnwser() error(%v)", err)
|
|
return
|
|
}
|
|
affect, err = res.RowsAffected()
|
|
return
|
|
}
|
|
|
|
// GetAnswerList list
|
|
func (d *Dao) GetAnswerList(c context.Context, qusID int64) (res []*model.Answer, err error) {
|
|
res = make([]*model.Answer, 0)
|
|
rows, err := d.db.Query(c, _getAnswerListSQL, qusID)
|
|
if err != nil {
|
|
log.Error("[dao.GetAnswerList] d.db.Query err: %v", err)
|
|
return
|
|
}
|
|
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
oi := &model.Answer{}
|
|
if err = rows.Scan(&oi.AnswerID, &oi.QsID, &oi.AnswerContent, &oi.IsCorrect); err != nil {
|
|
log.Error("[dao.question|GetOrder] rows.Scan err: %v", res)
|
|
return
|
|
}
|
|
res = append(res, oi)
|
|
}
|
|
return
|
|
}
|
|
|
|
// AddUserAnwser add
|
|
func (d *Dao) AddUserAnwser(c context.Context, answer *model.ArgCheckAnswer, isCorrect int8) (affect int64, err error) {
|
|
|
|
ids := xstr.JoinInts(answer.Answers)
|
|
res, err := d.db.Exec(c, _addUserAnswerSQL, answer.UID, answer.QsID, answer.Platform, answer.Source, ids, isCorrect)
|
|
if err != nil {
|
|
log.Error("d.InserAnwser() error(%v)", err)
|
|
return
|
|
}
|
|
affect, err = res.LastInsertId()
|
|
return
|
|
}
|
|
|
|
// CheckAnswer check
|
|
func (d *Dao) CheckAnswer(c context.Context, qsid int64, ids []int64) (total int, err error) {
|
|
err = d.db.QueryRow(c, fmt.Sprintf(_checkAnswerSQL, xstr.JoinInts(ids)), qsid).Scan(&total)
|
|
if err != nil {
|
|
log.Error("d.GetQusBankCount error(%v)", err)
|
|
return
|
|
}
|
|
return
|
|
}
|
|
|
|
// GetRandomPic get
|
|
func (d *Dao) GetRandomPic(c context.Context, id int) (oi *model.QuestBkPic, err error) {
|
|
|
|
oi = &model.QuestBkPic{}
|
|
|
|
row := d.db.QueryRow(c, _getRandomPicSQL, id)
|
|
if err = row.Scan(&oi.X, &oi.Y, &oi.Src); err != nil {
|
|
if err == sql.ErrNoRows {
|
|
oi = nil
|
|
err = nil
|
|
} else {
|
|
log.Error("row.Scan error(%v)", err)
|
|
}
|
|
|
|
}
|
|
return
|
|
|
|
}
|
|
|
|
// GetAllPicIds ids
|
|
func (d *Dao) GetAllPicIds(c context.Context, offset int, limitnum int) (ids []int, err error) {
|
|
|
|
rows, err := d.db.Query(c, _getListPicSQL, offset, limitnum)
|
|
if err != nil {
|
|
log.Error("[dao.GetAllPicIds] d.db.Query err: %v %d,%d", err, offset, limitnum)
|
|
return
|
|
}
|
|
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var oi int
|
|
if err = rows.Scan(&oi); err != nil {
|
|
log.Error("[dao.question|GetOrder] rows.Scan err: %v", ids)
|
|
return
|
|
}
|
|
ids = append(ids, oi)
|
|
}
|
|
return
|
|
|
|
}
|
|
|
|
// GetPicCount cnt
|
|
func (d *Dao) GetPicCount(c context.Context) (total int, err error) {
|
|
err = d.db.QueryRow(c, _getPicCntSQL).Scan(&total)
|
|
if err != nil {
|
|
log.Error("d.GetQusCount error(%v)", err)
|
|
return
|
|
}
|
|
return
|
|
|
|
}
|