hz_crm_api/models/yb/yb_user_collection.go

package yb

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

// 研报-用户收藏表
type YbUserCollection struct {
	CollectionId   uint      `orm:"column(collection_id);pk"`
	CollectionType uint8     `orm:"column(collection_type)"` //收藏类型：1-研报; 2-视频社区; 3-微路演视频
	UserId         uint      `orm:"column(user_id)"`         //用户ID
	CompanyId      uint      `orm:"column(company_id)"`      //客户ID
	CompanyStatus  string    `orm:"column(company_status)"`  //客户状态
	CompanyName    string    `orm:"column(company_name)"`    //客户名称
	RealName       string    `orm:"column(real_name)"`       //联系人姓名
	PrimaryId      uint      `orm:"column(primary_id)"`      //不同类型的主ID
	ExtendId       uint      `orm:"column(extend_id)"`       //扩展ID-如晨周报章节ID
	State          uint8     `orm:"column(state)"`           //状态：1-已收藏；0-取消收藏；
	SourceAgent    uint8     `orm:"column(source_agent)"`    //操作来源：1-小程序 2-小程序 PC 3-弘则研究公众号 4-Web PC
	SellerId       int       `orm:"column(seller_id)"`       //客户销售ID
	Title          string    `orm:"column(title)"`           //研报/视频标题-冗余
	PublishTime    time.Time `orm:"column(publish_time)"`    //研报/视频发布时间-冗余
	CreateTime     time.Time `orm:"column(create_time)"`     //创建时间
	ModifyTime     time.Time `orm:"column(modify_time)"`     //修改时间
}
// TableName get sql table name.获取数据库表名
func (l *YbUserCollection) TableName() string {
	return "yb_user_collection"
}

//  联系人收藏统计
type SellerCollectCount struct {
	Num      int `json:"num"`
	SellerId int `json:"seller_id"`
}

//  收藏统计
type SellerCollectCountListItem struct {
	Num           int
	UserId        int
	RealName      string
	CompanyId     int
	CompanyStatus string
	CompanyName   string
}

// GetSellerCollectCountByCompanyStatus 获取销售下的联系人收藏次数
func GetSellerCollectCountByCompanyStatus(companyStatus string, startDate, endDate time.Time) (list []*SellerCollectCount, err error) {
	o := orm.NewOrm()
	pars := make([]interface{}, 0)
	sql := `SELECT COUNT(*) AS num, seller_id FROM yb_user_collection WHERE company_status =? AND state=1 and create_time >= ? and create_time <= ? GROUP BY seller_id`
	pars = append(pars, companyStatus, startDate, endDate)
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

type SellerCollectUserLogReq struct {
	PageSize      int    `description:"每页数据条数"`
	CurrentIndex  int    `description:"当前页页码，从1开始"`
	SortParam     string `description:"排序字段参数，用来排序的字段， 枚举值：'viewTotal':总阅读次数 、 'viewTime':阅读时间 、 'roadShowTotal':累计路演次数 、'expireDay'：到期时间 、 'createTime'：创建时间 、 'formalTime': 转正时间 、 'freezeTime'：冻结时间 、'lossTime'：流失时间" 、'deadline'：距离未完成的任务的截止日期的天数、'ybUserLog': 按照用户的点击量`
	SortType      string `description:"如何排序，是正序还是倒序，枚举值：asc 正序,desc 倒叙 "`
	SellerIds     string `description:"销售ID, 用','拼接 "`
	CompanyStatus string `description:"客户状态"`
	StartDate     string `description:"起始时间"`
	EndDate       string `description:"截止时间"`
}

// 收藏类型
const (
	CollectionTypeReport    = 1         // 研报
	CollectionTypeVideo     = 2           // 视频社区
	CollectionTypeRoadVideo = 3         // 微路演视频
)

// GetSellerUserCollectCountByCompanyStatus 获取销售下用户收藏记录汇总数
func GetSellerUserCollectCountByCompanyStatus(companyStatus string, startDate, endDate time.Time, sellerIds string, startSize, pageSize int, orderStr string) (list []*SellerCollectCountListItem, total int64, err error) {
	o := orm.NewOrm()
	pars := make([]interface{}, 0)

	sql := `SELECT COUNT(*) AS num, user_id, real_name, company_id, company_status, company_name FROM yb_user_collection WHERE company_status =? AND state=1 and create_time >= ? and create_time <= ? and seller_id in `+sellerIds+` GROUP BY user_id, company_id`
	pars = append(pars, companyStatus, startDate, endDate)

	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, pars).QueryRow(&total)
	if err != nil {
		return
	}
	if orderStr != "" {
		sql += orderStr
	}
	sql += ` LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

type CollectListReq struct {
	PageSize       int    `description:"每页数据条数"`
	CurrentIndex   int    `description:"当前页页码，从1开始"`
	SellerIds      string `description:"销售ID, 用','拼接 "`
	UserId         int    `description:"联系人id"`
	CompanyStatus  string `description:"客户状态"`
	StartDate      string `description:"起始时间"`
	EndDate        string `description:"截止时间"`
	CollectionType string `description:"收藏类型：1-研报; 2-视频社区; 3-微路演视频"`
}

// GetYbUserCollectionByCondition 获取收藏列表
func GetYbUserCollectionByCondition(condition string, pars []interface{}, startSize, pageSize int) (list []*YbUserCollection, total int64, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM yb_user_collection WHERE state=1 `
	if condition != "" {
		sql += condition
	}
	totalSQL := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSQL, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` order by create_time desc, collection_id desc LIMIT ?,?`
	pars = append(pars, startSize, pageSize)
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}