hz_crm_api/models/yb/community_video_play_log.go

package yb

import "github.com/beego/beego/v2/client/orm"

type CommunityVideoPlayLog struct {
	Id               int    `gorm:"primaryKey;column:id;type:int(10) unsigned;not null" `
	CommunityVideoID int    `gorm:"column:community_video_id;type:int(10) unsigned;not null"`                   //视频ID
	UserID           int    `gorm:"index:idx_user_id;column:user_id;type:int(10) unsigned;not null;default:0" ` // 点击视频的用户ID
	SourceAgent      int    `gorm:"column:source_agent;type:tinyint(4);default:1" `                             // 操作来源，1：小程序，2：小程序 pc 3:弘则研究公众号，4:web pc
	CreateTime       string `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP"`                 // 创建日志时间
	Type             string `description:"统计类型（1 视频社区，2 路演视频）"`
}

// TableName get sql table name.获取数据库表名
func (l *CommunityVideoPlayLog) TableName() string {
	return "yb_community_video_play_log"
}

type VideoPlayLogItem struct {
	UserId         int
	NewSourceAgent int
	CreateTime     string
	StopSeconds    int
	//LastCreateTime string
	//ClickNum       int `description:"点击量"`
}

// GetVideoPlayLogListByVideoId 查询视频点击量列表
func GetVideoPlayLogListByVideoId(videoId, videoType, startSize, pageSize int) (list []*VideoPlayLogItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	user_id,
	IF(source_agent=4,2,source_agent) as new_source_agent,
	count(*) as click_num,
	MAX( create_time ) as last_create_time
FROM
	yb_community_video_play_log 
	WHERE community_video_id=? and type=?
GROUP BY
	user_id,
	new_source_agent
	ORDER BY MAX( create_time ) DESC, id DESC
	LIMIT ?,?`
	_, err = o.Raw(sql, videoId, videoType, startSize, pageSize).QueryRows(&list)
	return
}

// GetVideoPlayListTotalByVideoId 查询视频点击量列表总数
func GetVideoPlayListTotalByVideoId(videoId, videoType int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `select count(*) from (SELECT
	user_id,
	IF(source_agent=4,2,source_agent) as new_source_agent
FROM
	yb_community_video_play_log 
	WHERE community_video_id=? and type=?
GROUP BY
	user_id,
	new_source_agent) as b
	`
	err = o.Raw(sql, videoId, videoType).QueryRow(&total)
	return
}

// GetVideoPlayTotalByVideoId 查询
func GetVideoPlayTotalByVideoId(videoId, videoType int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `select count(*)
FROM
	yb_community_video_play_log 
	WHERE community_video_id=? and type=?
	`
	err = o.Raw(sql, videoId, videoType).QueryRow(&total)
	return
}

// GetVideoPlayLogListByVideoIdV2 查询视频点击量列表
func GetVideoPlayLogListByVideoIdV2(videoId, videoType, startSize, pageSize int, orderRule string) (list []*VideoPlayLogItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				user_id,
				IF(source_agent=4,2,source_agent) as new_source_agent,
				create_time,
				stop_seconds
			FROM
				yb_community_video_play_log 
			WHERE
				community_video_id = ? and type = ? `
	order := ` ORDER BY stop_seconds DESC, create_time DESC`
	if orderRule != `` {
		order = orderRule
	}
	sql += order
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, videoId, videoType, startSize, pageSize).QueryRows(&list)
	return
}

// GetVideoPlayTotalByVideoId 查询
func GetVideoPlayTotalByVideoIdV2(videoId, videoType int) (total int64, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				count(*)
			FROM
				yb_community_video_play_log
			WHERE
				community_video_id = ? AND type = ?`
	err = o.Raw(sql, videoId, videoType).QueryRow(&total)
	return
}

// UserCommunityVideoVisitCount 用户视频点击量
type UserCommunityVideoVisitCount struct {
	VisitCount       int    `json:"visit_count"`
	CommunityVideoId int    `json:"community_video_id"`
	NewSource        int    `json:"new_source"`
	RecentTime       string `json:"recent_time"`
}

// GetCommunityVideoVisitCountByUserId 视频社区点击量统计-根据用户
func GetCommunityVideoVisitCountByUserId(userId, videoType, startSize, pageSize int, orderRule string) (total int, list []*UserCommunityVideoVisitCount, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS visit_count, community_video_id, IF(source_agent=4,2,source_agent) as new_source, MAX(create_time) AS recent_time
			FROM yb_community_video_play_log WHERE user_id = ? and type=?
			GROUP BY community_video_id, new_source`

	if orderRule != `` {
		sql += ` ORDER BY ` + orderRule
	} else {
		sql += ` ORDER BY recent_time DESC`
	}

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, userId, videoType).QueryRow(&total)
	if err != nil {
		return
	}

	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, userId, videoType, startSize, pageSize).QueryRows(&list)
	return
}