hz_crm_api/models/yb/price_driven.go

package yb

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

// PriceDriven 价格驱动表
type PriceDriven struct {
	PriceDrivenId int `orm:"column(price_driven_id);pk" description:"价格驱动ID"`
	//ChartPermissionId   int       `json:"chart_permission_id" description:"品种权限ID"`
	VarietyTagId        int       `json:"variety_tag_id" description:"标签ID"`
	VarietyTagName      string    `json:"variety_tag_name" description:"标签名称"`
	MainVariable        string    `json:"main_variable" description:"关键变量"`
	CoreDrivenType      int       `json:"core_driven_type" description:"核心驱动类型 0-多 1-空"`
	CoreDrivenContent   string    `json:"core_driven_content" description:"核心驱动内容"`
	CoreContent         string    `json:"core_content" description:"核心内容"`
	LastUpdateAdminId   int       `json:"last_update_admin_id" description:"最后更新人ID"`
	LastUpdateAdminName string    `json:"last_update_admin_name" description:"最后更新人名称"`
	ThsMsgState         int       `json:"ths_msg_state" description:"同花顺推送状态：0-未推送 1-已推送"`
	TemplateMsgState    int       `json:"template_msg_state" description:"模板消息推送状态：0-未推送 1-已推送"`
	PublishState        int       `json:"publish_state" description:"发布状态：0-未发布 1-已发布"`
	SendThsMsgTime      time.Time `json:"last_ths_msg_time" description:"最后推送同花顺客群消息时间"`
	SendTemplateMsgTime time.Time `json:"last_template_msg_time" description:"最后推送模板消息时间"`
	CreateTime          time.Time `json:"create_time" description:"创建时间"`
	ModifyTime          time.Time `json:"modify_time" description:"更新时间"`
}

// TableName 表名变更
func (priceDrivenInfo *PriceDriven) TableName() string {
	return "yb_price_driven"
}

// Add 新增
func (priceDrivenInfo *PriceDriven) Add() (err error) {
	o := orm.NewOrm()
	id, err := o.Insert(priceDrivenInfo)
	if err != nil {
		return
	}
	priceDrivenInfo.PriceDrivenId = int(id)
	return
}

// Update 更新
func (priceDrivenInfo *PriceDriven) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(priceDrivenInfo, cols...)
	return
}

// GetPriceDrivenByChartPermissionId 通过品种获取价格驱动
//func GetPriceDrivenByChartPermissionId(chartPermissionId int) (item *PriceDriven, err error) {
//	o := orm.NewOrm()
//	sql := `SELECT * FROM yb_price_driven WHERE chart_permission_id = ? ORDER BY create_time DESC LIMIT 1`
//	err = o.Raw(sql, chartPermissionId).QueryRow(&item)
//	return
//}

// GetPriceDrivenByVarietyTagId 通过标签ID获取价格驱动
func GetPriceDrivenByVarietyTagId(chartPermissionId int) (item *PriceDriven, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM yb_price_driven WHERE variety_tag_id = ? ORDER BY create_time DESC LIMIT 1`
	err = o.Raw(sql, chartPermissionId).QueryRow(&item)
	return
}

// GetPriceDrivenById 主键获取价格驱动
func GetPriceDrivenById(priceDrivenId int) (item *PriceDriven, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM yb_price_driven WHERE price_driven_id = ? LIMIT 1`
	err = o.Raw(sql, priceDrivenId).QueryRow(&item)
	return
}

// PriceDrivenSaveLog 价格驱动保存记录表
type PriceDrivenSaveLog struct {
	Id            int `orm:"column(id);pk"`
	PriceDrivenId int `json:"price_driven_id" description:"价格驱动ID"`
	//ChartPermissionId int       `json:"chart_permission_id" description:"品种权限ID"`
	VarietyTagId      int       `json:"variety_tag_id" description:"标签ID"`
	MainVariable      string    `json:"main_variable" description:"关键变量"`
	CoreDrivenType    int       `json:"core_driven_type" description:"核心驱动类型 0-多 1-空"`
	CoreDrivenContent string    `json:"core_driven_content" description:"核心驱动内容"`
	CoreContent       string    `json:"core_content" description:"核心内容"`
	AdminId           int       `json:"admin_id" description:"更新人ID"`
	AdminName         string    `json:"admin_name" description:"更新人名称"`
	CreateTime        time.Time `json:"create_time" description:"创建时间"`
}

// TableName 表名变更
func (priceDrivenSaveLogInfo *PriceDrivenSaveLog) TableName() string {
	return "yb_price_driven_save_log"
}

// Add 新增保存记录
func (priceDrivenSaveLogInfo *PriceDrivenSaveLog) Add() (err error) {
	o := orm.NewOrm()
	lastId, err := o.Insert(priceDrivenSaveLogInfo)
	if err != nil {
		return
	}
	priceDrivenSaveLogInfo.PriceDrivenId = int(lastId)
	return
}

// GetPriceDrivenSaveLogByPriceDrivenId 获取最新一条保存记录
func GetPriceDrivenSaveLogByPriceDrivenId(priceDrivenId int) (item *PriceDrivenSaveLog, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM yb_price_driven_save_log WHERE price_driven_id = ? ORDER BY create_time DESC LIMIT 1`
	err = o.Raw(sql, priceDrivenId).QueryRow(&item)
	return
}

// GetRecentPriceDrivenList 获取每个标签最新价格驱动信息
func GetRecentPriceDrivenList() (list []*PriceDriven, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				pd.price_driven_id,
				pd.variety_tag_id,
				pd.variety_tag_name,
				pd.main_variable,
				pd.core_driven_type,
				pd.core_driven_content,
				pd.modify_time
			FROM
				( SELECT price_driven_id, MAX( create_time ) AS create_time FROM yb_price_driven WHERE publish_state = 1 GROUP BY variety_tag_id ) AS pd2
				LEFT JOIN yb_price_driven AS pd ON pd.create_time = pd2.create_time 
			WHERE pd.publish_state = 1
			ORDER BY
				pd.modify_time DESC`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// PriceDrivenVarietyTagClick 价格驱动标签点击统计
type PriceDrivenVarietyTagClick struct {
	VarietyTagId    int    `json:"variety_tag_id" description:"标签ID"`
	CountNum        int    `json:"count_num" description:"点击量"`
	RecentClickTime string `json:"recent_click_time" description:"最近点击时间"`
}

// GetPriceDrivenClick 获取价格驱动点击量(根据标签ID分组)
func GetPriceDrivenClick(priceDrivenIds string) (list []*PriceDrivenVarietyTagClick, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				vl.variety_tag_id,
				COUNT(1) AS count_num,
				MAX(vl.create_time) AS recent_click_time
			FROM
				yb_price_driven_visit_log AS vl
			WHERE 1=1 `
	if priceDrivenIds != "" {
		sql += ` AND vl.price_driven_id IN (` + priceDrivenIds + `) `
	}
	sql += ` GROUP BY vl.variety_tag_id`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetPriceDrivenPageClick 获取价格驱动分页点击量(根据标签ID分组)
func GetPriceDrivenPageClick(startSize, pageSize int, priceDrivenIds, joinRule, groupRule, orderRule string) (list []*PriceDrivenVarietyTagClick, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				pd.variety_tag_id,
				COUNT( vl.id ) AS count_num 
			FROM
				( SELECT price_driven_id, variety_tag_id FROM yb_price_driven
				WHERE price_driven_id IN (` + priceDrivenIds + `) ) AS pd
			LEFT JOIN yb_price_driven_visit_log AS vl ON ` + joinRule
	if groupRule != "" {
		sql += ` GROUP BY ` + groupRule
	}
	if orderRule != "" {
		sql += ` ORDER BY ` + orderRule
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&list)
	return
}

// GetDefaultPriceDrivenPageClick 获取价格驱动默认分页点击量统计列表(根据标签ID分组)
func GetDefaultPriceDrivenPageClick(startSize, pageSize int, priceDrivenIds string) (list []*PriceDrivenVarietyTagClick, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				variety_tag_id
			FROM
				yb_price_driven 
			WHERE
				price_driven_id IN (` + priceDrivenIds + `) `
	sql += `ORDER BY modify_time DESC LIMIT ?,?`
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&list)
	return
}

// PriceDrivenVarietyTagClickDetail 价格驱动标签点击统计详情
type PriceDrivenVarietyTagClickDetail struct {
	UserId          int    `json:"user_id" description:"用户ID"`
	RealName        string `json:"real_name" description:"用户姓名"`
	CompanyName     string `json:"company_name" description:"公司名称"`
	CompanyStatus   string `json:"company_status" description:"公司状态"`
	CountNum        int    `json:"count_num" description:"点击量"`
	//SourceAgent     int    `json:"source_agent" description:"来源"`
	NewSourceAgent  int    `json:"new_source_agent" description:"来源"`
	RecentClickTime string `json:"recent_click_time" description:"最近点击时间"`
}

// GetPriceDrivenPageClickDetail 获取价格驱动分页点击量详情(根据用户分组)
func GetPriceDrivenPageClickDetail(startSize, pageSize, varietyTagId, priceDrivenId int) (total int, list []*PriceDrivenVarietyTagClickDetail, err error) {
	o := orm.NewOrm()
	pars := make([]interface{}, 0)
	sql := `SELECT
				user_id,
				real_name,
				IF(source_agent=4,2,source_agent) as new_source_agent,
				company_name,
				company_status,
				COUNT(1) AS count_num,
				MAX(create_time) AS recent_click_time
			FROM
				yb_price_driven_visit_log
			WHERE 1=1 `
	if varietyTagId > 0 {
		sql += ` AND variety_tag_id = ?`
		pars = append(pars, varietyTagId)
	}
	if priceDrivenId > 0 {
		sql += ` AND price_driven_id = ?`
		pars = append(pars, priceDrivenId)
	}
	sql += ` GROUP BY user_id, new_source_agent ORDER BY recent_click_time DESC`

	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
}