eta_api/models/english_report_email_pv.go

package models

import (
	sql2 "database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"time"

	"github.com/rdlucklib/rdluck_tools/paging"
)

// EnglishReportEmailPV 英文研报-邮箱pv
type EnglishReportEmailPV struct {
	Id         int       `gorm:"column:id;primaryKey;autoIncrement"`
	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) {
	err = global.DbMap[utils.DbNameReport].Create(item).Error
	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 := global.DbMap[utils.DbNameReport]
	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`
	var totalNull sql2.NullInt64
	err = global.DbMap[utils.DbNameReport].Raw(totalSql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	if totalNull.Valid {
		total = int(totalNull.Int64)
	}
	sql += ` ORDER BY recent_click_time DESC LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&list).Error
	return
}

// GetEnglishReportEmailUvPageList 获取邮箱uv列表-分页
func GetEnglishReportEmailUvPageList(condition string, pars []interface{}, startSize, pageSize int) (total int, list []*EnglishReportEmailUvResp, err error) {
	o := global.DbMap[utils.DbNameReport]
	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`
	var totalNull sql2.NullInt64
	err = global.DbMap[utils.DbNameReport].Raw(totalSql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	if totalNull.Valid {
		total = int(totalNull.Int64)
	}
	sql += ` ORDER BY recent_click_time DESC LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&list).Error
	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:"最近一次点击时间"`
}

// GetEnglishEmailViewPageList 获取英文邮箱/联系人阅读列表-分页
func GetEnglishEmailViewPageList(condition string, pars []interface{}, orderRule string, startSize, pageSize int) (total int, list []*EnglishEmailViewResp, err error) {
	o := global.DbMap[utils.DbNameReport]
	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`
	var totalNull sql2.NullInt64
	err = global.DbMap[utils.DbNameReport].Raw(totalSql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	if totalNull.Valid {
		total = int(totalNull.Int64)
	}
	if orderRule != `` {
		sql += orderRule
	}
	sql += ` LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&list).Error
	return
}