package models

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hongze_mobile_admin/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,mobile,max(create_time) as created_time FROM cygx_article_history_record_newpv WHERE mobile in (` + mobiles + `) AND company_id != 16 group by mobile`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

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

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

// GetCountUserViewHistoryByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountUserViewHistoryByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	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 !="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountUserViewHistoryByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountUserViewHistoryByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	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=""  GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountAdvisoryArticleViewRecordByCompanyIdsMobile 根据手机号获取客户的浏览次数
func GetCountAdvisoryArticleViewRecordByCompanyIdsMobile(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	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 !="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountAdvisoryArticleViewRecordByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountAdvisoryArticleViewRecordByCompanyIdsEmail(companyIds string) (items []*CompanyViewTotalSlice, err error) {
	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="" GROUP BY company_id`
	_, err = o.Raw(sql).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) {
	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 !="" GROUP BY company_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetCountReportViewRecordByCompanyIdsEmail 根据邮箱获取客户的浏览次数
func GetCountReportViewRecordByCompanyIdsEmail(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 ` + dataName + `.report_view_record 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
}