hz_crm_api/models/user_view_history.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_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
}

// MobileOrEmailLastViewSlice 根据手机号/邮箱获取客户的最新浏览时间
type MobileOrEmailLastViewSlice struct {
	Mobile   string    `description:"手机号/邮箱"`
	ViewTime time.Time `description:"用户浏览时间"`
}

// GetLastReportViewRecordByMobileList 根据手机号获取客户的最新浏览时间
func GetLastReportViewRecordByMobileList(mobileList []string) (items []*MobileOrEmailLastViewSlice, err error) {
	num := len(mobileList)
	if num <= 0 {
		return
	}
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrmUsingDB("rddp")

	sql := `SELECT mobile ,max(create_time) view_time FROM report_view_record 
WHERE mobile IN ( ` + utils.GetOrmInReplace(num) + ` )  and mobile !="" and create_time>=? GROUP BY mobile`
	_, err = o.Raw(sql, mobileList, today).QueryRows(&items)
	return
}

// GetLastReportViewRecordByEmailList 根据邮箱获取客户的最新浏览时间
func GetLastReportViewRecordByEmailList(emailList []string) (items []*MobileOrEmailLastViewSlice, err error) {
	num := len(emailList)
	if num <= 0 {
		return
	}
	today := time.Now().Format(utils.FormatDate)
	o := orm.NewOrmUsingDB("rddp")

	sql := `SELECT email AS mobile ,max(create_time) view_time FROM report_view_record 
WHERE email IN ( ` + utils.GetOrmInReplace(num) + ` )  and email !="" and create_time>=? GROUP BY email`
	_, err = o.Raw(sql, emailList, 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
}

// MobileOrEmailViewTotalSlice 获取客户的浏览次数
type MobileOrEmailViewTotalSlice struct {
	Mobile    string `description:"客户手机号/邮箱"`
	ViewTotal int    `description:"用户浏览次数"`
}

// GetCountReportViewRecordByMobileList 根据手机号获取客户的浏览次数
func GetCountReportViewRecordByMobileList(mobileList []string) (items []*MobileOrEmailViewTotalSlice, err error) {
	num := len(mobileList)
	if num <= 0 {
		return
	}

	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrmUsingDB("rddp")

	sql := `SELECT mobile ,count(1) AS view_total FROM report_view_record
WHERE mobile IN ( ` + utils.GetOrmInReplace(num) + ` )  and mobile !="" and create_time>=? GROUP BY mobile`
	_, err = o.Raw(sql, mobileList, today).QueryRows(&items)

	return
}

// GetCountReportViewRecordByEmailList 根据邮箱获取客户的浏览次数
func GetCountReportViewRecordByEmailList(emailList []string) (items []*MobileOrEmailViewTotalSlice, err error) {
	num := len(emailList)
	if num <= 0 {
		return
	}

	today := time.Now().Format(utils.FormatDate) + " 00:00:00"
	o := orm.NewOrmUsingDB("rddp")

	sql := `SELECT email AS mobile ,count(1) AS view_total FROM report_view_record
WHERE email IN ( ` + utils.GetOrmInReplace(num) + ` )  and email !="" and create_time>=? GROUP BY email`
	_, err = o.Raw(sql, emailList, 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
}

//`ficc_view_total` int(9) unsigned DEFAULT '0' COMMENT 'ficc报告的阅读次数',
//`ficc_last_view_time` datetime DEFAULT NULL COMMENT 'ficc报告最近一次阅读时间',
//`rai_view_total` int(9) unsigned DEFAULT '0' COMMENT '权益报告的阅读次数',
//`rai_last_view_time` datetime DEFAULT NULL COMMENT '权益报告的最近一次阅读时间',

// CompanyViewRecord 获取客户ficc和权益的浏览次数/最近浏览时间
type CompanyViewRecord struct {
	CompanyId        int       `description:"客户id"`
	FiccViewTotal    int       `description:"ficc报告的阅读次数"`
	FiccLastViewTime time.Time `description:"ficc报告最近一次阅读时间"`
	RaiViewTotal     int       `description:"权益报告的阅读次数"`
	RaiLastViewTime  time.Time `description:"权益报告最近一次阅读时间"`
}

// GetUserViewHistoryByCompanyIdList 根据company_id列表获取客户的浏览次数
func GetUserViewHistoryByCompanyIdList(companyIdList []int) (items []*CompanyViewRecord, err error) {
	num := len(companyIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT
	a.company_id ,SUM(ficc_view_total) as ficc_view_total,max(ficc_last_view_time) as ficc_last_view_time,SUM(rai_view_total) as rai_view_total,max(rai_last_view_time) as rai_last_view_time
	FROM wx_user AS a WHERE a.company_id IN ( ` + utils.GetOrmInReplace(num) + ` )  GROUP BY a.company_id`
	_, err = o.Raw(sql, companyIdList).QueryRows(&items)
	return
}