hz_crm_api/models/cygx/search_keyword.go

package cygx

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

type KeyWordList struct {
	Sort       int    `description:"序号"`
	KeyWordNum int    `description:"数量"`
	KeyWord    string `description:"关键词"`
}

type CanDownload struct {
	IsCanDownload bool `description:"是否可以下载"`
}

func GetSearchKeyWordCount(condition string, pars []interface{}) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) FROM( SELECT COUNT( 1 ) FROM cygx_search_key_word WHERE  1=1`
	if condition != "" {
		sqlCount += condition
	}
	sqlCount += ` GROUP BY key_word) as aa`
	o := orm.NewOrm()
	err = o.Raw(sqlCount, pars).QueryRow(&count)
	return
}

//获取搜索关键词列表
func GetSearchKeyWordList(condition string, pars []interface{}, startSize, pageSize int) (items []*KeyWordList, err error) {
	sql := `SELECT COUNT( key_word ) AS key_word_num, key_word FROM cygx_search_key_word WHERE 1= 1`
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY key_word ORDER BY COUNT( key_word ) DESC LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

type SearchKeyWordListResp struct {
	List   []*KeyWordList
	Paging *paging.PagingItem `description:"分页数据"`
}

type KeyWordListDownload struct {
	CompanyId   int    `description:"客户ID"`
	KeyWord     string `description:"关键词"`
	CreateTime  string `description:"搜索时间"`
	Mobile      string `description:"手机号"`
	Email       string `description:"邮箱"`
	RealName    string `description:"客户姓名"`
	CompanyName string `description:"公司名称"`
	ProductName string `description:"产品名称"`
	SellerName  string `description:"销售人员名称"`
}

//获取7天搜索关键词列表
func GetSearchKeyWordListDownload(starTime, endTime string) (items []*KeyWordListDownload, err error) {
	sql := `SELECT
		kw.key_word,
		kw.create_time,
		wx.mobile,
		wx.email,
		wx.real_name,
        wx.company_id,
		co.company_name
	FROM
		cygx_search_key_word as kw
		LEFT   JOIN wx_user as wx ON  wx.user_id = kw.user_id
		LEFT  JOIN company as co ON  co.company_id = wx.company_id
	WHERE
 		kw.create_time > ?
		AND kw.create_time < ?
		AND wx.company_id > 1
	ORDER  BY
		kw.create_time DESC`
	o := orm.NewOrm()
	_, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
	return
}

//销售获取7天搜索关键词列表
func GetSearchKeyWordListDownloadSeller(starTime, endTime, sqlStr string) (items []*KeyWordListDownload, err error) {
	sql := `SELECT
		kw.key_word,
		kw.create_time,
		wx.mobile,
		wx.email,
		wx.real_name,
        wx.company_id,
		co.company_name
	FROM
		cygx_search_key_word as kw
		LEFT   JOIN wx_user as wx ON  wx.user_id = kw.user_id
		LEFT  JOIN company as co ON  co.company_id = wx.company_id
	WHERE
 		kw.create_time > ?
		AND kw.create_time < ?
		AND wx.company_id > 1` + sqlStr +
		` ORDER  BY
		kw.create_time DESC`
	o := orm.NewOrm()
	_, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
	return
}

type CompanyProduct struct {
	CompanyId int `description:"客户ID"`
}

//获取开通产品种类大于一的客户
//func GetCompanyProductMoreOneList() (items []*CompanyProduct, err error) {
//	sql := `SELECT
//			company_id
//		FROM
//			company_product cp
//		WHERE cp.status IN ('试用','永续','正式')
//		GROUP BY
//			cp.company_id
//		HAVING
//			count( 1 )> 0`
//	o := orm.NewOrm()
//	_, err = o.Raw(sql).QueryRows(&items)
//	return
//}

func GetCompanyProductMoreOneList(starTime, endTime string) (items []*CompanyProduct, err error) {
	sql := `SELECT
			wx.company_id
			FROM
			cygx_search_key_word as kw
			LEFT   JOIN wx_user as wx ON  wx.user_id = kw.user_id
			WHERE
			kw.create_time > ?
			AND kw.create_time < ?
			AND wx.company_id > 1
			GROUP BY wx.company_id
			ORDER  BY
			kw.create_time DESC`
	o := orm.NewOrm()
	_, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
	return
}

type CompanyProductNameAndSeller struct {
	CompanyId   int    `description:"客户ID"`
	ProductName string `description:"产品名称"`
	SellerName  string `description:"销售人员名称"`
}

func GetCompanyProductNameAndSellerList(companyId int) (items []*CompanyProductNameAndSeller, err error) {
	sql := `SELECT 	 
		ad.real_name as seller_name,
        cp.product_name
		FROM
		company_product as cp
		INNER JOIN admin AS ad ON cp.seller_id = ad.admin_id
		WHERE company_id = ?`
	o := orm.NewOrm()
	_, err = o.Raw(sql, companyId).QueryRows(&items)
	return
}

//获取开通产品种类大于一的客户
func GetCompanyProductNameAndSellerAllList(pars []interface{}, companyIdStrr string) (items []*CompanyProductNameAndSeller, err error) {
	sql := `SELECT 	 
		ad.real_name as seller_name,
        cp.product_name,
        cp.company_id
		FROM
		company_product as cp
		INNER JOIN admin AS ad ON cp.seller_id = ad.admin_id
		WHERE company_id IN (` + companyIdStrr + `)`
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

type GetKeywordCountUrl struct {
	Url string `description:"下载链接"`
}