hz_crm_api/models/yb/yb_voice_broadcast_statistics.go

package yb

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

type VoiceBroadcastStatistics struct {
	Id            int    `orm:"column(id);pk"`
	CompanyId     int    `description:"客户ID"`
	CompanyName   string `description:"客户名称"`
	UserId        int    `description:"用户ID"`
	RealName      string `description:"用户名称"`
	Mobile        string `description:"用户手机号"`
	Email         string `description:"电子邮箱"`
	CompanyStatus string `description:"客户状态"`
	Source        int    `description:"点击来源,1手机小程序,2pc小程序,3web端"`
	BroadcastId   int    `description:"语音ID"`
	BroadcastName string `description:"语音名称"`
	SectionId     int    `description:"语音分类ID"`
	SectionName   string `description:"语音分类名称"`
	VarietyId     int    `description:"品种id"`
	VarietyName   string `description:"品种名称"`
	AuthorId      int    `description:"作者id"`
	Author        string `description:"语音管理员"`
	CreateTime    string `description:"访问时间"`
}

// TableName 表名变更
func (voiceBroadcastStatistics *VoiceBroadcastStatistics) TableName() string {
	return "yb_voice_broadcast_statistics"
}

type VoiceBroadcastTotal struct {
	CompanyId     int    `description:"客户ID"`
	CompanyName   string `description:"客户名称"`
	UserId        int    `description:"用户ID"`
	RealName      string `description:"用户名称"`
	Mobile        string `description:"用户手机号"`
	Email         string `description:"电子邮箱"`
	CompanyStatus string `description:"客户状态"`
	Source        int    `description:"点击来源,1手机小程序,2pc小程序,3web端"`
	BroadcastId   int    `description:"语音ID"`
	BroadcastName string `description:"语音名称"`
	SectionId     int    `description:"语音分类ID"`
	SectionName   string `description:"语音分类名称"`
	VarietyId     int    `description:"品种id"`
	VarietyName   string `description:"品种名称"`
	VisitCount    int    `description:"阅读量"`
	AuthorId      int    `description:"作者id"`
	Author        string `description:"作者"`
	CreateTime    string `description:"访问时间"`
	PublishTime   string `description:"发布时间"`
}

// GetVoiceBroadcastCensusPageList 获取语音播报统计列表-分页
func GetVoiceBroadcastCensusPageList(startSize, pageSize int, condition, orderRule string, pars []interface{}) (total int, list []*VoiceBroadcastTotal, err error) {
	o := orm.NewOrm()
	//sql := `SELECT *, COUNT(1) AS visit_count FROM yb_voice_broadcast_statistics WHERE 1=1`
	sql := `SELECT broadcast_id, broadcast_name, section_name, variety_name, author_id, publish_time, COUNT(1) AS visit_count FROM yb_voice_broadcast_statistics WHERE 1=1`
	sql += condition
	sql += ` GROUP BY broadcast_id`
	if orderRule != `` {
		sql += ` ORDER BY ` + orderRule
	}

	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) a `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)

	return
}

type VoiceBroadcastCensusDetail struct {
	UserId     int    `description:"用户ID" json:"user_id"`
	NewSource  int    `description:"点击来源,1手机小程序,2pc小程序,3web端" json:"new_source"`
	VisitCount int    `description:"阅读量" json:"visit_count"`
	CreateTime string `description:"访问时间" json:"create_time"`
}

// GetVoiceBroadcastDetailBySource 获取语音播报统计详情
func GetVoiceBroadcastDetailBySource(broadcastId, startSize, pageSize int) (total int, list []*VoiceBroadcastCensusDetail, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				user_id,
				IF (source = 4,2,source) AS new_source,
				COUNT(1) AS visit_count,
				create_time
			FROM
				yb_voice_broadcast_statistics
			WHERE
				1=1
			AND broadcast_id = ?
			GROUP BY
				user_id, new_source
			ORDER BY
				create_time DESC`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) a `
	err = o.Raw(totalSql, broadcastId).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, broadcastId, startSize, pageSize).QueryRows(&list)

	return
}

func UpdateVoiceBroadcastName(id int, name string) (err error) {
	o := orm.NewOrm()
	sql := "UPDATE yb_voice_broadcast_statistics SET section_name=? WHERE section_id=? "

	_, err = o.Raw(sql, name, id).Exec()
	return
}

// GetVoiceBroadcastDeatilBysourceTwo 获取语音播报点击量详情
func GetVoiceBroadcastDeatilBysourceTwo(broadcastId, startSize, pageSize int) (total int, list []*VoiceBroadcastTotal, err error) {
	o := orm.NewOrm()
	sql := ` SELECT *,COUNT(1)  AS visit_count FROM yb_voice_broadcast_statistics WHERE broadcast_id=? AND source IN (2,4) GROUP BY user_id ORDER BY create_time DESC `

	totalSql := `select count(1) total from (` + sql + `) a `
	err = o.Raw(totalSql, broadcastId).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, broadcastId, startSize, pageSize).QueryRows(&list)

	return
}

// UserVoiceBroadcastVisitCount 用户语音播报点击量
type UserVoiceBroadcastVisitCount struct {
	VisitCount    int    `json:"visit_count"`
	BroadcastId   int    `json:"broadcast_id"`
	BroadcastName string `json:"broadcast_name"`
	NewSource     int    `json:"new_source"`
	RecentTime    string `json:"recent_time"`
}

// GetVoiceBroadcastVisitCountByUserId 语音播报点击量统计-根据用户
func GetVoiceBroadcastVisitCountByUserId(sectionId, userId, startSize, pageSize int, orderRule string) (total int, list []*UserVoiceBroadcastVisitCount, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS visit_count, broadcast_id, broadcast_name, IF(source=4,2,source) as new_source, MAX(create_time) AS recent_time
			FROM yb_voice_broadcast_statistics WHERE user_id = ? AND section_id = ?
			GROUP BY broadcast_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, sectionId, userId).QueryRow(&total)
	if err != nil {
		return
	}

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