eta_mobile/models/user_view_history.go

package models

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

type UserViewHistory struct {
	ViewHistoryId        int       `orm:"column(id);pk"`
	UserId               int       `description:"用户id"`
	Mobile               string    `description:"手机号"`
	Email                string    `description:"邮箱"`
	RealName             string    `description:"用户实际姓名"`
	CompanyName          string    `description:"公司名称"`
	ViewTitle            string    `description:"访问标题"`
	ViewPage             string    `description:"访问页面"`
	ReportChapterModule  string    `description:"访问核心观点或者图文逻辑"`
	CreatedTime          string    `description:"创建时间"`
	LastUpdatedTime      time.Time `description:"访问历史类型，weekly_report 周报，pdf；默认值：weekly_report"`
	ResearchReportId     int       `description:"研报id"`
	ResearchReportTypeId int       `description:"报告章节id,为0时表示查看目录或者首页"`
}

//根据用户id字符串获取用户的浏览数
type UserViewTotalSlice struct {
	UserId      int       `description:"用户id"`
	Total       int       `description:"总阅读数"`
	CreatedTime time.Time `description:"用户浏览时间"`
}

func GetCountUserViewHistoryByUserIds(userIds string) (items []*UserViewTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,user_id,max(created_time) as created_time FROM user_view_history WHERE user_id in (` + userIds + `) group by user_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
	//return items2,err
}

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

func GetCountUserViewHistoryByMobiles(mobiles string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,max(created_time) as created_time FROM user_view_history WHERE mobile in (` + mobiles + `) group by mobile`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

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

func GetCountUserViewHistoryByEmails(emails string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,email,max(created_time) as created_time FROM user_view_history WHERE email in (` + emails + `) group by email`
	_, err = o.Raw(sql).QueryRows(&items)
	return
	//return items2,err
}

func GetCountCygxArticleHistoryRecordByMobiles(mobiles string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,h.mobile,max(h.create_time) as created_time FROM cygx_article_history_record_all  AS h  INNER JOIN cygx_article  AS art  ON  art.article_id = h.article_id  WHERE h.mobile in (` + mobiles + `) AND h.is_del = 0  AND h.company_id != 16 group by h.mobile`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetCountCygxArticleHistoryRecordByEmails(emails string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,h.email,max(h.create_time) as created_time FROM cygx_article_history_record_all AS h  INNER JOIN cygx_article  AS art  ON  art.article_id = h.article_id  WHERE h.email in (` + emails + `) AND h.is_del = 0 AND h.company_id != 16 group by email`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// CompanyLastViewSlice 根据手机号获取客户的最新浏览时间
type CompanyLastViewSlice struct {
	CompanyId int       `description:"客户id"`
	ViewTime  time.Time `description:"用户浏览时间"`
}

// GetLastUserViewHistoryByCompanyIdsMobile 根据手机号获取客户的最新浏览时间
func GetLastUserViewHistoryByCompanyIdsMobile(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.created_time) view_time
FROM
	wx_user a
	JOIN user_view_history b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and b.created_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastUserViewHistoryByCompanyIdsEmail 根据邮箱获取客户的最新浏览时间
func GetLastUserViewHistoryByCompanyIdsEmail(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	//	sql := `SELECT
	//	a.company_id ,max(b.created_time) view_time
	//FROM
	//	wx_user a
	//	JOIN user_view_history b ON a.email = b.email
	//WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile=""  and b.created_time>=?  GROUP BY company_id`

	sql := `SELECT
	a.company_id ,max(b.created_time) view_time FROM wx_user a
	JOIN user_view_history b ON a.email = b.email 
WHERE b.email !="" and b.mobile=""  and b.created_time>=?  GROUP BY company_id`

	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastAdvisoryArticleViewRecordByCompanyIdsMobile 根据手机号获取客户的最新浏览时间
func GetLastAdvisoryArticleViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.create_time) view_time
FROM
	wx_user a
	JOIN advisory_user_chart_article_record b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and b.create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastAdvisoryArticleViewRecordByCompanyIdsEmail 根据邮箱获取客户的最新浏览时间
func GetLastAdvisoryArticleViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.create_time) view_time
FROM
	wx_user a
	JOIN advisory_user_chart_article_record b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile=""  and b.create_time>=?  GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastCygxArticleViewRecordByCompanyIdsMobile 根据手机号获取客户的最新浏览时间
func GetLastCygxArticleViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyLastViewSlice, err error) {
	o := orm.NewOrm()
	//dataName := ""
	//if utils.RunMode == "debug" {
	//	dataName = "test_v2_hongze_rddp"
	//} else {
	//	dataName = "hongze_rddp"
	//}
	sql := `SELECT
	a.company_id ,max(b.create_time) view_time
FROM
	wx_user a
	JOIN cygx_article_history_record_newpv b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetLastCygxArticleViewRecordByCompanyIdsEmail 根据邮箱获取客户的最新浏览时间
func GetLastCygxArticleViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyLastViewSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.create_time) view_time
FROM
	wx_user a
	JOIN cygx_article_history_record_newpv b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile=""   GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetLastReportViewRecordByCompanyIdsMobile 根据手机号获取客户的最新浏览时间
func GetLastReportViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	dataName := ""
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}
	sql := `SELECT
	a.company_id ,max(b.create_time) view_time
FROM
	wx_user a
	JOIN ` + dataName + `.report_view_record b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and b.create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastReportViewRecordByCompanyIdsEmail 根据邮箱获取客户的最新浏览时间
func GetLastReportViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	dataName := ""
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}
	sql := `SELECT	a.company_id ,max(b.create_time) view_time FROM wx_user a
	JOIN ` + dataName + `.report_view_record b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile=""   and b.create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastUserViewStatisticsByCompanyIdsMobile 根据手机号获取客户的最新浏览时间
func GetLastUserViewStatisticsByCompanyIdsMobile(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.last_view_time) view_time
FROM
	wx_user a
	JOIN user_view_statistics b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and a.mobile !="" and b.mobile !="" and b.date<=? GROUP BY a.company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetLastUserViewStatisticsByCompanyIdsEmail 根据邮箱获取客户的最新浏览时间
func GetLastUserViewStatisticsByCompanyIdsEmail(companyIds string) (items []*CompanyLastViewSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,max(b.last_view_time) view_time
FROM
	wx_user a
	JOIN user_view_statistics b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and a.email !="" and b.email !="" and a.mobile="" and b.mobile="" and b.date<=? GROUP BY a.company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// CompanyViewTotalSlice 获取客户的浏览次数
type CompanyViewTotalSlice struct {
	CompanyId int `description:"客户id"`
	ViewTotal int `description:"用户浏览次数"`
}

// GetCountUserViewHistoryByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountUserViewHistoryByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,count(1) view_total FROM 	wx_user a
	JOIN user_view_history b ON a.mobile = b.mobile 
WHERE 	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and b.created_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetCountUserViewHistoryByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountUserViewHistoryByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	//	sql := `SELECT
	//	a.company_id ,count(1) view_total
	//FROM
	//	wx_user a
	//	JOIN user_view_history b ON a.email = b.email
	//WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile=""  and b.created_time>=? GROUP BY company_id`

	sql := `SELECT a.company_id ,count(1) view_total FROM wx_user a 
JOIN user_view_history b ON a.email = b.email 
WHERE  b.email !="" and b.mobile=""  and b.created_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetCountAdvisoryArticleViewRecordByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountAdvisoryArticleViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,count(1) view_total
FROM
	wx_user a
	JOIN advisory_user_chart_article_record b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetCountAdvisoryArticleViewRecordByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountAdvisoryArticleViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,count(1) view_total
FROM
	wx_user a
	JOIN advisory_user_chart_article_record b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile="" and create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetCountCygxArticleViewRecordByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountCygxArticleViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	o := orm.NewOrm()
	//dataName := ""
	//if utils.RunMode == "debug" {
	//	dataName = "test_v2_hongze_rddp"
	//} else {
	//	dataName = "hongze_rddp"
	//}
	sql := `SELECT
	a.company_id ,count(1) view_total
FROM
	wx_user a
	JOIN cygx_article_history_record_newpv b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountCygxArticleViewRecordByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountCygxArticleViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,count(1) view_total
FROM
	wx_user a
	JOIN cygx_article_history_record_newpv b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountReportViewRecordByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountReportViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	dataName := ""
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}
	sql := `SELECT
	a.company_id ,count(1) view_total
FROM
	wx_user a
	JOIN ` + dataName + `.report_view_record b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and b.mobile !="" and create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetCountReportViewRecordByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountReportViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrm()
	dataName := ""
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}
	sql := `SELECT	a.company_id ,count(1) view_total FROM wx_user a
	JOIN ` + dataName + `.report_view_record b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and b.email !="" and b.mobile="" and create_time>=? GROUP BY company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetUserViewStatisticsByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetUserViewStatisticsByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,sum(b.view_num) view_total
FROM
	wx_user a
	JOIN user_view_statistics b ON a.mobile = b.mobile 
WHERE
	a.company_id IN ( ` + companyIds + ` )  and a.mobile !="" and b.mobile !="" and date<=? GROUP BY a.company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

// GetUserViewStatisticsByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetUserViewStatisticsByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,sum(b.view_num) view_total
FROM
	wx_user a
	JOIN user_view_statistics b ON a.email = b.email 
WHERE a.company_id IN ( ` + companyIds + ` ) and a.email !="" and b.email !="" and a.mobile="" and b.mobile="" and date<=? GROUP BY a.company_id`
	_, err = o.Raw(sql, today).QueryRows(&items)
	return
}

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

// GetUserViewStatisticsByMobile 根据手机号获取联系人的浏览次数
func GetUserViewStatisticsByMobile(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
}

type ResearchReportViewPUV struct {
	ResearchReportId int
	Pv               int
	Uv               int
}

// GetPUVByResearchReportIds 通过报告IDs获取老报告PV、UV
func GetPUVByResearchReportIds(reportIds string) (list []*ResearchReportViewPUV, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				research_report_id,
				COUNT(1) AS pv,
				COUNT(DISTINCT user_id) AS uv
			FROM
				user_view_history
			WHERE
				research_report_id IN (` + reportIds +`)
			GROUP BY
				research_report_id`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}