search_keyword.go 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182
  1. package cygx
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "github.com/rdlucklib/rdluck_tools/paging"
  5. )
  6. type KeyWordList struct {
  7. Sort int `description:"序号"`
  8. KeyWordNum int `description:"数量"`
  9. KeyWord string `description:"关键词"`
  10. }
  11. type CanDownload struct {
  12. IsCanDownload bool `description:"是否可以下载"`
  13. }
  14. func GetSearchKeyWordCount(condition string, pars []interface{}) (count int, err error) {
  15. sqlCount := ` SELECT COUNT(1) FROM( SELECT COUNT( 1 ) FROM cygx_search_key_word WHERE 1=1`
  16. if condition != "" {
  17. sqlCount += condition
  18. }
  19. sqlCount += ` GROUP BY key_word) as aa`
  20. o := orm.NewOrm()
  21. err = o.Raw(sqlCount, pars).QueryRow(&count)
  22. return
  23. }
  24. //获取搜索关键词列表
  25. func GetSearchKeyWordList(condition string, pars []interface{}, startSize, pageSize int) (items []*KeyWordList, err error) {
  26. sql := `SELECT COUNT( key_word ) AS key_word_num, key_word FROM cygx_search_key_word WHERE 1= 1`
  27. if condition != "" {
  28. sql += condition
  29. }
  30. sql += ` GROUP BY key_word ORDER BY COUNT( key_word ) DESC LIMIT ?,? `
  31. o := orm.NewOrm()
  32. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
  33. return
  34. }
  35. type SearchKeyWordListResp struct {
  36. List []*KeyWordList
  37. Paging *paging.PagingItem `description:"分页数据"`
  38. }
  39. type KeyWordListDownload struct {
  40. CompanyId int `description:"客户ID"`
  41. KeyWord string `description:"关键词"`
  42. CreateTime string `description:"搜索时间"`
  43. Mobile string `description:"手机号"`
  44. Email string `description:"邮箱"`
  45. RealName string `description:"客户姓名"`
  46. CompanyName string `description:"公司名称"`
  47. ProductName string `description:"产品名称"`
  48. SellerName string `description:"销售人员名称"`
  49. }
  50. //获取7天搜索关键词列表
  51. func GetSearchKeyWordListDownload(starTime, endTime string) (items []*KeyWordListDownload, err error) {
  52. sql := `SELECT
  53. kw.key_word,
  54. kw.create_time,
  55. wx.mobile,
  56. wx.email,
  57. wx.real_name,
  58. wx.company_id,
  59. co.company_name
  60. FROM
  61. cygx_search_key_word as kw
  62. LEFT JOIN wx_user as wx ON wx.user_id = kw.user_id
  63. LEFT JOIN company as co ON co.company_id = wx.company_id
  64. WHERE
  65. kw.create_time > ?
  66. AND kw.create_time < ?
  67. AND wx.company_id > 1
  68. ORDER BY
  69. kw.create_time DESC`
  70. o := orm.NewOrm()
  71. _, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
  72. return
  73. }
  74. //销售获取7天搜索关键词列表
  75. func GetSearchKeyWordListDownloadSeller(starTime, endTime, sqlStr string) (items []*KeyWordListDownload, err error) {
  76. sql := `SELECT
  77. kw.key_word,
  78. kw.create_time,
  79. wx.mobile,
  80. wx.email,
  81. wx.real_name,
  82. wx.company_id,
  83. co.company_name
  84. FROM
  85. cygx_search_key_word as kw
  86. LEFT JOIN wx_user as wx ON wx.user_id = kw.user_id
  87. LEFT JOIN company as co ON co.company_id = wx.company_id
  88. WHERE
  89. kw.create_time > ?
  90. AND kw.create_time < ?
  91. AND wx.company_id > 1` + sqlStr +
  92. ` ORDER BY
  93. kw.create_time DESC`
  94. o := orm.NewOrm()
  95. _, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
  96. return
  97. }
  98. type CompanyProduct struct {
  99. CompanyId int `description:"客户ID"`
  100. }
  101. //获取开通产品种类大于一的客户
  102. //func GetCompanyProductMoreOneList() (items []*CompanyProduct, err error) {
  103. // sql := `SELECT
  104. // company_id
  105. // FROM
  106. // company_product cp
  107. // WHERE cp.status IN ('试用','永续','正式')
  108. // GROUP BY
  109. // cp.company_id
  110. // HAVING
  111. // count( 1 )> 0`
  112. // o := orm.NewOrm()
  113. // _, err = o.Raw(sql).QueryRows(&items)
  114. // return
  115. //}
  116. func GetCompanyProductMoreOneList(starTime, endTime string) (items []*CompanyProduct, err error) {
  117. sql := `SELECT
  118. wx.company_id
  119. FROM
  120. cygx_search_key_word as kw
  121. LEFT JOIN wx_user as wx ON wx.user_id = kw.user_id
  122. WHERE
  123. kw.create_time > ?
  124. AND kw.create_time < ?
  125. AND wx.company_id > 1
  126. GROUP BY wx.company_id
  127. ORDER BY
  128. kw.create_time DESC`
  129. o := orm.NewOrm()
  130. _, err = o.Raw(sql, starTime, endTime).QueryRows(&items)
  131. return
  132. }
  133. type CompanyProductNameAndSeller struct {
  134. CompanyId int `description:"客户ID"`
  135. ProductName string `description:"产品名称"`
  136. SellerName string `description:"销售人员名称"`
  137. }
  138. func GetCompanyProductNameAndSellerList(companyId int) (items []*CompanyProductNameAndSeller, err error) {
  139. sql := `SELECT
  140. ad.real_name as seller_name,
  141. cp.product_name
  142. FROM
  143. company_product as cp
  144. INNER JOIN admin AS ad ON cp.seller_id = ad.admin_id
  145. WHERE company_id = ?`
  146. o := orm.NewOrm()
  147. _, err = o.Raw(sql, companyId).QueryRows(&items)
  148. return
  149. }
  150. //获取开通产品种类大于一的客户
  151. func GetCompanyProductNameAndSellerAllList(pars []interface{}, companyIdStrr string) (items []*CompanyProductNameAndSeller, err error) {
  152. sql := `SELECT
  153. ad.real_name as seller_name,
  154. cp.product_name,
  155. cp.company_id
  156. FROM
  157. company_product as cp
  158. INNER JOIN admin AS ad ON cp.seller_id = ad.admin_id
  159. WHERE company_id IN (` + companyIdStrr + `)`
  160. o := orm.NewOrm()
  161. _, err = o.Raw(sql, pars).QueryRows(&items)
  162. return
  163. }
  164. type GetKeywordCountUrl struct {
  165. Url string `description:"下载链接"`
  166. }