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:"下载链接"` }