hz_crm_api/models/yb/chart_visit_log.go

package yb

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

// ChartDailyVisitLog 每日访问记录
type ChartDailyVisitLog struct {
	Id                  int       `orm:"column(id);pk"`
	CompanyId           int       `description:"客户ID"`
	CompanyName         string    `description:"客户名称"`
	UserId              int       `description:"用户ID"`
	RealName            string    `description:"用户名称"`
	Mobile              string    `description:"用户手机号"`
	Email               string    `description:"电子邮箱"`
	ChartInfoId         int       `description:"图表ID"`
	ChartName           string    `description:"图表名称"`
	MyChartClassifyId   int       `description:"图表分类ID"`
	MyChartClassifyName string    `description:"图表分类名称"`
	CreateTime          time.Time `description:"访问时间"`
}

// ChartVisitPageListResp 图表阅读统计列表响应体
type ChartVisitPageListResp struct {
	Paging *paging.PagingItem
	List   []*ChartVisitList `description:"列表数据"`
}

// ChartVisitList 图表阅读统计列表
type ChartVisitList struct {
	ClassifyId    int    `description:"分类ID"`
	ClassifyName  string `description:"分类名称"`
	VisitCount    int    `description:"阅读量"`
	LastVisitTime string `description:"最后阅读时间"`
}

// GetChartVisitPageList 获取图表阅读统计列表-分页
func GetChartVisitPageList(classifyId, startSize, pageSize, order int) (total int, list []*ChartVisitList, err error) {
	o := orm.NewOrm()
	var pars []interface{}
	sql := ` SELECT
				my_chart_classify_id AS classify_id,
				my_chart_classify_name AS classify_name,
				COUNT(1) AS visit_count,
				MAX(create_time) AS last_visit_time
			FROM
				yb_chart_daily_visit_log AS a
			WHERE
				1 = 1 `
	if classifyId > 0 {
		sql += ` AND my_chart_classify_id = ? `
		pars = append(pars, classifyId)
	}
	sql += ` GROUP BY my_chart_classify_id `
	orderBy := ""
	switch order {
	case 1:
		orderBy = "visit_count ASC "
	case 2:
		orderBy = "visit_count DESC "
	case 3:
		orderBy = "last_visit_time ASC "
	case 4:
		orderBy = "last_visit_time DESC "
	default:
		orderBy = "visit_count DESC "
	}
	sql += ` ORDER BY ` + orderBy

	totalSql := `select count(1) total from (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)

	return
}

// ChartVisitDetailPageListResp 图表阅读详情统计列表响应体
type ChartVisitDetailPageListResp struct {
	Paging     *paging.PagingItem
	List       []*ChartVisitDetailList `description:"列表数据"`
	VisitTotal int                     `description:"阅读总人数"`
}

// ChartVisitDetailList 图表阅读统计详情
type ChartVisitDetailList struct {
	UserId        int    `description:"联系人ID"`
	UserName      string `description:"联系人姓名"`
	CompanyId     int    `description:"客户ID"`
	CompanyName   string `description:"客户名称"`
	CompanyStatus string `description:"客户状态"`
	SellerName    string `description:"销售名称"`
	VisitCount    int    `description:"阅读量"`
	LastVisitTime string `description:"最后阅读时间"`
}

// GetChartVisitDetailPageList 获取图表阅读统计详情列表-分页
func GetChartVisitDetailPageList(condition string, pars []interface{}, order, startSize, pageSize int) (total int, list []*ChartVisitDetailList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
				l.user_id,
				l.company_id,
				l.company_name,
				l.real_name AS user_name,
				p.seller_name,
				p.status AS company_status,
				COUNT(1) AS visit_count,
				MAX(l.create_time) AS last_visit_time
			FROM
				yb_chart_daily_visit_log AS l
			INNER JOIN company AS c ON l.company_id = c.company_id
			INNER JOIN company_product AS p ON c.company_id = p.company_id AND p.product_id = 1
			WHERE
				l.my_chart_classify_id = ? `
	sql += condition
	sql += ` GROUP BY l.user_id `
	orderBy := ""
	switch order {
	case 1:
		orderBy = "last_visit_time ASC "
	case 2:
		orderBy = "last_visit_time DESC "
	default:
		orderBy = "last_visit_time DESC "
	}
	sql += ` ORDER BY ` + orderBy

	totalSql := `select count(1) total from (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	pars = append(pars, startSize, pageSize)
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

// GetClassifyChartTotalVisit 获取分类阅读总人数
func GetClassifyChartTotalVisit(condition string, pars []interface{}) (total int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
				COUNT(1)
			FROM
				yb_chart_daily_visit_log AS l
			INNER JOIN company AS c ON l.company_id = c.company_id
			INNER JOIN company_product AS p ON c.company_id = p.company_id AND p.product_id = 1
			WHERE
				l.my_chart_classify_id = ? `
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&total)

	return
}

// UserClassifyChartVisitList 用户指定分类的图表访问列表
type UserClassifyChartVisitList struct {
	ChartName  string `description:"图表名称"`
	VisitCount int    `description:"访问次数"`
}

// GetUserClassifyChartVisitList 获取用户指定分类的图表访问列表
func GetUserClassifyChartVisitList(classifyId, userId int) (list []*UserClassifyChartVisitList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
				chart_name,
				COUNT(1) AS visit_count
			FROM
				yb_chart_daily_visit_log 
			WHERE
				my_chart_classify_id = ? 
				AND user_id = ? 
			GROUP BY
				chart_info_id `
	_, err = o.Raw(sql, classifyId, userId).QueryRows(&list)

	return
}

// CompanyAuthCensusPageListResp 图表权限开通统计响应体
type CompanyAuthCensusPageListResp struct {
	Paging       *paging.PagingItem
	List         []*CompanyAuthCensusList `description:"列表数据"`
	UserTotal    int                      `description:"联系人总数"`
	CompanyTotal int                      `description:"用户总数"`
}

// CompanyAuthCensusList 图库权限开通统计
type CompanyAuthCensusList struct {
	UserId        int    `description:"联系人ID"`
	UserName      string `description:"联系人姓名"`
	CompanyId     int    `description:"客户ID"`
	CompanyName   string `description:"客户名称"`
	CompanyStatus string `description:"客户状态"`
	SellerName    string `description:"销售名称"`
	StartTime     string `description:"权限开始日期"`
	EndTime       string `description:"权限结束日期"`
	RestDay       int    `description:"剩余天数"`
	CreateTime    string `description:"权限开通时间"`
}

// GetCompanyAuthCensusPageList 获取图库权限开通统计列表-分页
func GetCompanyAuthCensusPageList(condition string, pars []interface{}, startSize, pageSize int) (total int, list []*CompanyAuthCensusList, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
				a.user_id,
				b.real_name AS user_name,
				c.company_id,
				c.company_name,
				d.seller_id,
				d.seller_name,
				d.status AS company_status,
				a.start_time,
				a.end_time,
				a.create_time
			FROM
				company_user_chart_classify_permission AS a
			INNER JOIN wx_user AS b ON a.user_id = b.user_id
			INNER JOIN company AS c ON b.company_id = c.company_id
			INNER JOIN company_product AS d ON c.company_id = d.company_id AND d.product_id = 1 AND d.status NOT IN ("冻结", "潜在", "流失")
			WHERE
				1 = 1
			AND a.enabled = 1 `
	sql += condition
	sql += ` GROUP BY a.user_id`
	totalSql := `select count(1) total from (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	pars = append(pars, startSize, pageSize)
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars).QueryRows(&list)

	return
}

type CountCompanyChartAuth struct {
	//CountUser    int `description:"开通权限用户数"`
	CountCompany int `description:"开通权限客户数"`
}

// GetCompanyAuthTotal 获取开通图表权限客户总数
func GetCompanyAuthTotal(condition string, pars []interface{}) (total CountCompanyChartAuth, err error) {
	o := orm.NewOrm()
	sql := ` SELECT
				COUNT(DISTINCT c.company_id) AS count_company
			FROM
				company_user_chart_classify_permission AS a
			INNER JOIN wx_user AS b ON a.user_id = b.user_id
			INNER JOIN company AS c ON b.company_id = c.company_id
			INNER JOIN company_product AS d ON c.company_id = d.company_id AND d.product_id = 1 AND d.status NOT IN ("冻结", "潜在", "流失")
			WHERE
				1 = 1
			AND a.enabled = 1 `
	sql += condition
	err = o.Raw(sql, pars).QueryRow(&total)

	return
}