hongze_task/models/user_view_statistics.go

package models

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

// UserViewStatistics 用户报告报告阅读数量统计表
type UserViewStatistics struct {
	Id           int       `orm:"column(id)" description:"自增Id"`
	Mobile       string    `description:"手机号"`
	Email        string    `description:"手机号"`
	RealName     string    `description:"联系人名称"`
	CompanyName  string    `description:"客户名称"`
	ViewNum      int       `description:"阅读总数"`
	LastViewTime time.Time `description:"最近一次阅读时间"`
	Date         time.Time `description:"阅读日期"`
	CreateTime   time.Time `description:"添加时间"`
}

// AddUserViewStatistics 添加用户报告报告阅读数量统计记录
func AddUserViewStatistics(item *UserViewStatistics) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

// AddMultiUserViewStatistics 添加用户报告报告阅读数量统计记录
func AddMultiUserViewStatistics(list []*UserViewStatistics) (err error) {
	if len(list) <= 0 {
		return
	}
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(list), list)
	return
}

// Update 更新用户报告报告阅读数量统计表
func (userViewStatistics *UserViewStatistics) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(userViewStatistics, cols...)
	return
}

// GetUserViewStatisticsByMobile 根据手机号、日期获取数据
func GetUserViewStatisticsByMobile(mobile, date string) (item *UserViewStatistics, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from  user_view_statistics  WHERE mobile=? and date = ?`
	err = o.Raw(sql, mobile, date).QueryRow(&item)
	return
}

// GetUserViewStatisticsByMobile 根据邮箱、日期获取数据
func GetUserViewStatisticsByEmail(email, date string) (item *UserViewStatistics, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from  user_view_statistics  WHERE email=? and date = ?`
	err = o.Raw(sql, email, date).QueryRow(&item)
	return
}

// UpdateLastViewTimeByMobile 根据手机号修改最近阅读时间
func UpdateLastViewTimeByMobile(mobile, date string, lastViewTime time.Time) (err error) {
	o := orm.NewOrm()
	sql := `update user_view_statistics set last_view_time =? WHERE mobile=? and date = ?`
	_, err = o.Raw(sql, lastViewTime, mobile, date).Exec()
	return
}

// UpdateLastViewTimeByEmail 根据邮箱修改最近阅读时间
func UpdateLastViewTimeByEmail(mobile, date string, lastViewTime time.Time) (err error) {
	o := orm.NewOrm()
	sql := `update user_view_statistics set last_view_time =? WHERE mobile=? and date = ?`
	_, err = o.Raw(sql, lastViewTime, mobile, date).Exec()
	return
}

// GetUserViewStatisticsCount 获取某天的用户报告报告阅读数量统计记录数量
func GetUserViewStatisticsCount(dayStr string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) count FROM user_view_statistics WHERE date = ?`
	err = o.Raw(sql, dayStr).QueryRow(&count)
	return
}

// GetUserViewStatisticsGroup 获取某天的用户报告报告阅读数量统计记录数量
func GetUserViewStatisticsGroup() (list []*UserViewStatistics, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM user_view_statistics where last_view_time is null group by date order by date asc`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// DeleteUserViewStatisticsCount 获取某天的用户报告报告阅读数量统计记录数量
func DeleteUserViewStatisticsCount(dayStr string) (err error) {
	o := orm.NewOrm()
	//产品权限
	sql := `delete from user_view_statistics WHERE date = ?`

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

// UserViewMobileTotalSlice 根据用户手机号字符串获取用户的浏览数
type UserViewMobileTotalSlice struct {
	Mobile      string    `description:"用户手机号"`
	CompanyName string    `description:"客户名称"`
	RealName    string    `description:"用户名称"`
	Total       int       `description:"总阅读数"`
	ViewTime    time.Time `description:"阅读时间"`
}

// UserViewEmailTotalSlice 根据用户邮箱字符串获取用户的浏览数
type UserViewEmailTotalSlice struct {
	Email       string    `description:"用户邮箱"`
	CompanyName string    `description:"客户名称"`
	RealName    string    `description:"用户名称"`
	Total       int       `description:"总阅读数"`
	ViewTime    time.Time `description:"阅读时间"`
}

func GetCountUserViewHistoryByMobiles(date string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,company_name,real_name,max(created_time) view_time FROM user_view_history WHERE created_time >= ? and created_time <= ? and mobile != "" group by mobile`
	_, err = o.Raw(sql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
}

func GetCountUserViewHistoryByEmails(date string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,email,company_name,real_name,max(created_time) view_time FROM user_view_history WHERE created_time >= ? and created_time <= ? and email != ""  group by email`
	_, err = o.Raw(sql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
	//return items2,err
}

func GetReportViewMaxTimeByMobiles(date string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrmUsingDB("rddp")
	rddpSql := `SELECT mobile,company_name,real_name,COUNT(1) AS total,max(create_time) view_time FROM report_view_record WHERE create_time >= ? and create_time <= ? and mobile != ""  group by mobile`
	_, err = o.Raw(rddpSql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
}

func GetReportViewMaxTimeByEmails(date string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrmUsingDB("rddp")
	rddpSql := `SELECT email,company_name,real_name,COUNT(1) AS total,max(create_time) view_time FROM report_view_record WHERE create_time >= ? and create_time <= ? and email != "" group by email`
	_, err = o.Raw(rddpSql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
}

// GetAdvisoryCountUserViewHistoryByMobiles 每日资讯
func GetAdvisoryCountUserViewHistoryByMobiles(date string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,company_name,real_name,max(create_time) view_time FROM advisory_user_chart_article_record WHERE create_time >= ? and create_time <= ? and mobile != ""  group by mobile`
	_, err = o.Raw(sql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
}

// GetAdvisoryCountUserViewHistoryByEmails 每日资讯
func GetAdvisoryCountUserViewHistoryByEmails(date string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,email,company_name,real_name as created_time,max(create_time) view_time FROM advisory_user_chart_article_record WHERE create_time >= ? and create_time <= ? and email != "" group by email`
	_, err = o.Raw(sql, date+" 00:00:00", date+" 23:59:59").QueryRows(&items)
	return
	//return items2,err
}

func GetMaxUserViewHistoryByMobiles() (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,company_name,real_name,max(created_time) view_time FROM user_view_history WHERE  created_time <= "2022-02-15 00:00:00" and  mobile != "" group by mobile`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetMaxReportViewMaxTimeByMobiles() (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrmUsingDB("rddp")
	rddpSql := `SELECT mobile,company_name,real_name,COUNT(1) AS total,max(create_time) view_time FROM report_view_record WHERE  create_time <= "2022-02-15 00:00:00" and  mobile != ""  group by mobile`
	_, err = o.Raw(rddpSql).QueryRows(&items)
	return
}

// GetAdvisoryCountUserViewHistoryByMobiles 每日资讯
func GetMaxAdvisoryCountUserViewHistoryByMobiles() (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,company_name,real_name,max(create_time) view_time FROM advisory_user_chart_article_record WHERE create_time <= "2022-02-15 00:00:00" and  mobile != ""  group by mobile`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// UserViewStatisticsInfo 根据用户手机号字符串获取用户的浏览数和最晚阅读次数
type UserViewStatisticsInfo struct {
	Mobile       string    `description:"用户手机号"`
	Total        int       `description:"总阅读数"`
	LastViewTime time.Time `description:"用户浏览时间"`
}

// GetUserLastViewStatisticsByMobile 根据手机号获取最新的联系人的浏览次数
func GetUserLastViewStatisticsByMobile(mobile string) (item *UserViewStatisticsInfo, err error) {
	o := orm.NewOrm()
	sql := `SELECT mobile,sum(view_num) total,max(last_view_time) last_view_time FROM  user_view_statistics  WHERE mobile = ? `
	err = o.Raw(sql, mobile).QueryRow(&item)
	return
}