eta_api/models/english_report_email_pv.go

package models

import (
	"time"

	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
)

// EnglishReportEmailPV 英文研报-邮箱pv
type EnglishReportEmailPV struct {
	Id         int       `orm:"column(id);pk;auto"`
	ReportId   int       `description:"英文报告ID"`
	EmailId    int       `description:"邮箱ID"`
	ReportType int       `description:"类型：0英文研报，1英文线上路演"`
	CreateTime time.Time `description:"创建时间"`
}

func (item *EnglishReportEmailPV) TableName() string {
	return "english_report_email_pv"
}

func (item *EnglishReportEmailPV) Create() (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Insert(item)
	return
}

// EnglishReportEmailPvPageListResp 分页列表响应体
type EnglishReportEmailPvPageListResp struct {
	List   []*EnglishReportEmailPvResp
	Paging *paging.PagingItem `description:"分页数据"`
}
type EnglishReportEmailUvPageListResp struct {
	List   []*EnglishReportEmailUvResp
	Paging *paging.PagingItem `description:"分页数据"`
}

// EnglishReportEmailPvResp 邮箱响应体
type EnglishReportEmailPvResp struct {
	Name            string `description:"客户名称"`
	Email           string `description:"邮箱地址"`
	ClickNum        int    `description:"点击量"`
	RecentClickTime string `description:"最近一次点击时间"`
}

type EnglishReportEmailUvResp struct {
	Name            string `description:"客户名称"`
	Email           string `description:"邮箱地址"`
	RecentClickTime string `description:"最近一次点击时间"`
}

// GetEnglishReportEmailPageList 获取邮箱pv列表-分页
func GetEnglishReportEmailPvPageList(condition string, pars []interface{}, startSize, pageSize int) (total int, list []*EnglishReportEmailPvResp, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
				b.name,
				b.email,
				COUNT(a.id) AS click_num,
				MAX(a.create_time) AS recent_click_time
			FROM
				english_report_email_pv AS a
			JOIN english_report_email AS b ON a.email_id = b.id
			WHERE 1 = 1 `
	if condition != `` {
		sql += condition
	}
	sql += ` GROUP BY a.email_id `

	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` ORDER BY recent_click_time DESC LIMIT ?,?`

	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetEnglishReportEmailUvPageList 获取邮箱uv列表-分页
func GetEnglishReportEmailUvPageList(condition string, pars []interface{}, startSize, pageSize int) (total int, list []*EnglishReportEmailUvResp, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
				b.name,
				b.email,
				MAX(a.create_time) AS recent_click_time
			FROM
				english_report_email_pv AS a
			JOIN english_report_email AS b ON a.email_id = b.id
			WHERE 1 = 1 `
	if condition != `` {
		sql += condition
	}
	sql += ` GROUP BY a.email_id `

	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	sql += ` ORDER BY recent_click_time DESC LIMIT ?,?`

	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// EnglishEmailViewPageListResp  邮箱/联系人阅读分页列表响应体
type EnglishEmailViewPageListResp struct {
	List   []*EnglishEmailViewResp
	Paging *paging.PagingItem `description:"分页数据"`
}

// EnglishEmailViewResp 邮箱/联系人阅读数据响应体
type EnglishEmailViewResp struct {
	ReportId     int    `description:"英文研报ID"`
	ClickType    int    `description:"类型：0英文研报，1英文线上路演"`
	ReportTitle  string `description:"报告标题"`
	ReportType   string `description:"报告类型"`
	ViewTotal    int    `description:"点击量"`
	LastViewTime string `description:"最近一次点击时间"`
}

// todo GetEnglishEmailPageList 获取英文邮箱/联系人阅读列表-分页
func GetEnglishEmailViewPageList(condition string, pars []interface{}, orderRule string, startSize, pageSize int) (total int, list []*EnglishEmailViewResp, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
				report_id,
                report_type as click_type,
				COUNT(1) AS view_total,
				MAX(create_time) AS last_view_time
			FROM
				english_report_email_pv
			WHERE
				1 = 1 `
	if condition != `` {
		sql += condition
	}
	sql += ` GROUP BY report_id, report_type`

	totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
	if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
		return
	}
	if orderRule != `` {
		sql += orderRule
	}
	sql += ` LIMIT ?,?`

	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}