hz_crm_api/models/yb/comment.go

package yb

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"time"
)

// 研报 用户留言表
type Comment struct {
	CommentId          uint64    `orm:"column(comment_id);pk;" description:"留言ID"`
	UserId             uint64    `orm:"column(user_id)" description:"用户id"`
	AdminId            uint64    `orm:"column(admin_id)" description:"发布留言回复的管理员ID"`
	ReportId           int       `orm:"column(report_id)" description:"报告ID"`
	ReportChapterId    int       `orm:"column(report_chapter_id)" description:"报告章节ID"`
	Content            string    `orm:"column(content)" description:"留言内容"`
	SysIsRead          int8      `orm:"column(sys_is_read)" description:"管理员是否已读 0-未读，1-已读"`
	SysReadTime        time.Time `orm:"column(sys_read_time)" description:"管理员已读时间"`
	SysReadAdminId     int64     `orm:"column(sys_read_admin_id)" description:"已读的管理员id"`
	ReplyCommentId     uint64    `orm:"column(reply_comment_id)" description:"回复的留言ID"`
	IsTop              int8      `orm:"column(is_top)" description:"是否置顶（0-未置顶，1-置顶）"`
	IsHot              int8      `orm:"column(is_hot)" description:"是否设置精选（0-未设置，1-已设置）"`
	HotTopTime         time.Time `orm:"column(hot_top_time)" description:"设置精选或者设置置顶的时间"`
	TopTime            time.Time `orm:"column(top_time)" description:"设置置顶的时间"`
	HotTime            time.Time `orm:"column(hot_time)" description:"设置精选的时间"`
	Type               int8      `orm:"column(type)" description:"留言类型 1-评论 2-回复"`
	Enabled            int8      `orm:"column(enabled)" description:"是否有效, 0-无效留言 1-有效留言"`
	IsShowName         int8      `orm:"column(is_show_name)" description:"是否匿名 0-匿名，1-不匿名"`
	SourceAgent        int       `orm:"column(source_agent)" description:"留言入口来源，1：小程序，2：小程序pc, 4：web pc"`
	CreateTime         time.Time `orm:"column(create_time)" description:"创建时间"`
	ModifyTime         time.Time `orm:"column(modify_time)" description:"修改时间"`
	OldReportId        int       `orm:"column(old_report_id)" description:"老报告ID"`
	OldReportChapterId int       `orm:"column(old_report_chapter_id)" description:"老报告章节ID"`
}
type CommentUser struct {
	Comment
	NickName   string `description:"昵称"`
	Headimgurl string `description:"用户头像"`
}

func (yc *Comment) TableName() string {
	return "yb_comment"
}

// CommentIdList 评论id列表
type CommentIdList struct {
	CommentId int
}

// GetCommentReportIdsByCondition 根据条件查询相关的 report
func GetCommentReportIdsByCondition(condition string, pars []interface{}) (list []*Comment, err error) {
	o := orm.NewOrm()
	var commentIdList []*CommentIdList
	//groupSql := `SELECT * FROM (`
	//查找最大评论的id
	sql := `SELECT  MAX(c.comment_id) comment_id FROM yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE 1=1 and report_id > 0 and c.type = 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY c.report_id, c.report_chapter_id `
	_, err = o.Raw(sql, pars).QueryRows(&commentIdList)
	if err != nil {
		return
	}

	commentIdArr := make([]int, 0)
	for _, v := range commentIdList {
		commentIdArr = append(commentIdArr, v.CommentId)
	}
	lenCommentIdArr := len(commentIdArr)
	if lenCommentIdArr <= 0 {
		return
	}

	groupSql := `SELECT c.report_id, c.report_chapter_id FROM yb_comment AS c
WHERE comment_id in (` + utils.GetOrmInReplace(lenCommentIdArr) + `) ORDER BY c.sys_is_read asc,c.comment_id desc `
	_, err = o.Raw(groupSql, commentIdArr).QueryRows(&list)
	return
}

// GetCommentReportIdsByConditionBak 根据条件查询相关的 report
func GetCommentReportIdsByConditionBak(condition string, pars []interface{}) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT DISTINCT c.report_id, c.report_chapter_id
FROM
	yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE 1=1 and report_id > 0`
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY c.sys_is_read asc,c.create_time desc`
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// GetCommentIdsByCondition 根据条件查询相关的 留言ID
func GetCommentIdsByCondition(condition string, pars []interface{}) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT comment_id
FROM
	yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

type ReportCommentNum struct {
	ReportId        int  `description:"报告Id"`
	ReportChapterId int  `description:"报告章节ID"`
	SysIsRead       int8 `description:"管理员是否已读 0-未读，1-已读"`
	Num             int  `description:"汇总数"`
}

// StatReportCommentNum 统计报告的留言数
func StatReportCommentNum(reportIds, reportChapterIds string, condition string, pars []interface{}) (list []*ReportCommentNum, err error) {
	o := orm.NewOrm()
	sql := `SELECT c.report_id, c.report_chapter_id, c.sys_is_read, COUNT(c.comment_id) as num
FROM
	yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE type=1 `
	if reportIds != "" && reportChapterIds != "" {
		sql += `  and (c.report_id in (` + reportIds + `)  or c.report_chapter_id in (` + reportChapterIds + `) )`
	} else if reportIds != "" {
		sql += `  and c.report_id in (` + reportIds + `)`
	} else if reportChapterIds != "" {
		sql += `  and c.report_chapter_id in (` + reportChapterIds + `)`
	} else {
		return
	}
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY report_id,report_chapter_id,sys_is_read`
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// GetCommentListByByCondition 根据报告ID，查询留言列表
func GetCommentListByByCondition(condition string, pars []interface{}, orderStr string, startSize, pageSize int) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT c.*
FROM
	yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE 1=1 and c.type=1`
	if condition != "" {
		sql += condition
	}
	if orderStr != "" {
		sql += orderStr
	} else {
		sql += " ORDER BY create_time DESC , comment_id DESC"
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetCommentListTotalByByCondition 根据报告ID，查询留言列表总数
func GetCommentListTotalByByCondition(condition string, pars []interface{}) (total int64, err error) {
	o := orm.NewOrm()
	sql := `select count(*) from (SELECT c.comment_id
FROM
	yb_comment c
	LEFT JOIN wx_user u ON c.user_id = u.user_id 
WHERE 1=1 and c.type=1`
	if condition != "" {
		sql += condition
	}
	sql += ") f"
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// GetReplyListByReplyCommentIds 获取报告的精选留言的回复列表
func GetReplyListByReplyCommentIds(reportId, reportChapterId int, replyCommentIds string) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT *
FROM
	yb_comment 
	WHERE report_id =? and report_chapter_id=? and type=2 and reply_comment_id in` + replyCommentIds

	sql += ` ORDER BY comment_id ASC`
	_, err = o.Raw(sql, reportId, reportChapterId).QueryRows(&list)
	return
}

// GetReplyByReplyCommentIds 获取报告的精选留言的回复列表
func GetReplyByReplyCommentIds(replyCommentIds string) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT *
FROM
	yb_comment 
	WHERE type=2 and reply_comment_id in` + replyCommentIds

	sql += ` ORDER BY comment_id ASC`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// UpdateCommentSysIsReadByCommentIds 设置报告相关的留言为已读状态
func UpdateCommentSysIsReadByCommentIds(adminId int, commentIds string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE yb_comment SET sys_is_read=1, sys_read_admin_id=?, sys_read_time = NOW(), modify_time=NOW() WHERE sys_is_read=0 and comment_id in (` + commentIds + `)`
	_, err = o.Raw(sql, adminId).Exec()
	return
}

// UpdateCommentSysIsRead 设置所有留言为已读状态
func UpdateCommentSysIsRead(adminId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE yb_comment SET sys_is_read=1, sys_read_admin_id=?, sys_read_time = NOW(), modify_time=NOW() WHERE sys_is_read=0`
	_, err = o.Raw(sql, adminId).Exec()
	return
}

// AddComment 新增回复
func AddComment(item *Comment) (id int64, err error) {
	o := orm.NewOrm()
	id, err = o.Insert(item)
	return
}

// DeleteComment 标记处理用户所有待处理申请记录
func DeleteComment(commentId int) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	//删除评论
	sql := "delete from yb_comment where comment_id=? and type=1"
	_, err = to.Raw(sql, commentId).Exec()
	if err != nil {
		return
	}
	//删除回复
	sql = "delete from yb_comment where reply_comment_id=? and type=2"
	_, err = to.Raw(sql, commentId).Exec()
	return
}

// Update 更新留言状态
func (c *Comment) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(c, cols...)
	return
}

// DeleteCommentReply 删除管理员回复
func DeleteCommentReply(commentId int) (err error) {
	sql := `DELETE FROM yb_comment WHERE comment_id=? and type=2 `
	o := orm.NewOrm()
	_, err = o.Raw(sql, commentId).Exec()
	return
}

// GetCommentByCommentId 根据留言ID，查询留言详情
func GetCommentByCommentId(commentId int) (item *Comment, err error) {
	sql := `SELECT
	*
FROM
	yb_comment 
where comment_id=?`
	err = orm.NewOrm().Raw(sql, commentId).QueryRow(&item)
	return
}

// GetCommentUserByCommentId 根据留言ID，查询留言详情
func GetCommentUserByCommentId(commentId int) (item *CommentUser, err error) {
	sql := `SELECT
	y.*,u.nick_name,u.headimgurl
FROM
	yb_comment y left join wx_user u on y.user_id=u.user_id
where y.comment_id=?`
	err = orm.NewOrm().Raw(sql, commentId).QueryRow(&item)
	return
}

// GetReplyTotalByCommentId 查询该留言的回复次数
func GetReplyTotalByCommentId(commentId int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(*)
FROM
	yb_comment 
	WHERE reply_comment_id=? and type=2`
	err = o.Raw(sql, commentId).QueryRow(&total)
	return
}

// GetReplyByReplyCommentId 查询该留言的回复次数
func GetReplyByReplyCommentId(commentId uint64) (list []*Comment, err error) {
	o := orm.NewOrm()
	sql := `SELECT *
FROM
	yb_comment 
	WHERE reply_comment_id=? and type=2 ORDER BY comment_id ASC`
	_, err = o.Raw(sql, commentId).QueryRows(&list)
	return
}

type ProductCommentItem struct {
	CommentId           int       `description:"研报留言ID或者问答社区评论ID"`
	UserId              int       `description:"用户ID"`
	ReportId            int       `description:"报告Id"`
	ReportChapterId     int       `description:"报告章节Id"`
	OldReportId         int       `description:"老报告Id"`
	CommunityQuestionId int       `description:"问答ID"`
	Content             string    `description:"留言内容"`
	CreateTime          time.Time `description:"创建时间"`
	ProductType         int       `description:"留言板块ID： 1-研报留言，2-问答社区评论"`
	IsTop               int8      `description:"是否置顶（0-未置顶，1-置顶）"`
	RealName            string    `description:"用户姓名"`
}

// GetProductCommentList 获取研报留言和问答社区评论列表
func GetProductCommentList(condition string, pars []interface{}, startSize, pageSize int) (list []*ProductCommentItem, total int64, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from (( SELECT
	comment_id,
	user_id,
	report_id,
	report_chapter_id,
	0 AS community_question_id,
	content,
	create_time,
	1 AS product_type,
	is_top,
	real_name,
	old_report_id
FROM
	yb_comment 
WHERE
	type = 1)
	UNION
(
SELECT
	community_question_comment_id AS comment_id,
	user_id,
	0 AS report_id,
	0 AS report_chapter_id,
	community_question_id,
	content,
	create_time,
	source+1 AS product_type,
	0 as is_top,
	real_name,
	0 as old_report_id
FROM
	yb_community_question_comment 
WHERE
	type = 1 and enabled=1
	) 
	) as t
	where 1=1`
	if condition != "" {
		sql += condition
	}
	//汇总数据
	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) as a`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` ORDER BY t.create_time DESC,t.comment_id DESC`
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}