custom.go 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322
  1. package overseas_custom
  2. import (
  3. "fmt"
  4. "github.com/beego/beego/v2/client/orm"
  5. "github.com/rdlucklib/rdluck_tools/paging"
  6. "hongze/hz_crm_api/utils"
  7. )
  8. type Custom struct {
  9. CompanyId int `description:"客户id"`
  10. CompanyName string `description:"客户名称"`
  11. Nation string `description:"国家"`
  12. SellerId int `description:"销售id"`
  13. SellerName string `description:"销售名称"`
  14. CompanyStatus string `description:"状态"`
  15. ViewTotal int `description:"累计点击量"`
  16. RoadShowTotal int `description:"路演数量"`
  17. LastViewTime string `description:"最近阅读时间"`
  18. CreateTime string `description:"创建时间"`
  19. IsHide int `description:"是否隐藏:0:不隐藏,1:隐藏"`
  20. OverseasStatus string `description:"海外客户状态:'正式','试用','关闭'"`
  21. Source int `description:"来源:1,英文客户,2:客户列表"`
  22. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  23. }
  24. type CustomTotal struct {
  25. CompanyStatus string `description:"状态"`
  26. Total int `description:"总数"`
  27. }
  28. func (obj *Custom) GetCustomTotal(condition string, pars []interface{}) (list []*CustomTotal, err error) {
  29. o := orm.NewOrm()
  30. sql := ``
  31. var databaseName string
  32. if utils.RunMode == "debug" {
  33. databaseName = "test_v2_hongze_rddp"
  34. } else {
  35. databaseName = "hongze_rddp"
  36. }
  37. sql = `SELECT company_status,COUNT(1) AS total FROM (
  38. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  39. CASE b.status
  40. WHEN '正式' THEN '正式'
  41. WHEN '永续' THEN '正式'
  42. WHEN '试用' THEN '试用'
  43. ELSE '关闭' END AS company_status,
  44. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time
  45. FROM company AS a
  46. INNER JOIN company_product AS b ON a.company_id=b.company_id
  47. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  48. WHERE b.is_overseas = 0
  49. UNION ALL
  50. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  51. CASE t.enabled
  52. WHEN 0 THEN '关闭'
  53. ELSE '试用' END AS company_status,
  54. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time
  55. FROM %s.english_company AS t
  56. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  57. )AS m
  58. WHERE 1=1
  59. `
  60. if condition != "" {
  61. sql += condition
  62. }
  63. sql += ` GROUP BY m.company_status `
  64. sql = fmt.Sprintf(sql, databaseName)
  65. _, err = o.Raw(sql, pars).QueryRows(&list)
  66. return
  67. }
  68. // GetCompanyIdsByKeyword 关键词获取客户IDs
  69. func (obj *Custom) GetCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
  70. o := orm.NewOrm()
  71. sql := ` SELECT DISTINCT b.company_id FROM wx_user AS a
  72. INNER JOIN company AS b ON a.company_id=b.company_id
  73. WHERE 1=1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ? OR b.credit_code LIKE ? ) `
  74. _, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
  75. return
  76. }
  77. func (obj *Custom) GetCustomListCount(condition string, pars []interface{}, companyStatus string) (count int, err error) {
  78. o := orm.NewOrm()
  79. sql := ``
  80. var databaseName string
  81. if utils.RunMode == "debug" {
  82. databaseName = "test_v2_hongze_rddp"
  83. } else {
  84. databaseName = "hongze_rddp"
  85. }
  86. sql = `SELECT COUNT(1) AS count FROM (
  87. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  88. CASE b.status
  89. WHEN '正式' THEN '正式'
  90. WHEN '永续' THEN '正式'
  91. WHEN '试用' THEN '试用'
  92. ELSE '关闭' END AS company_status,
  93. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time
  94. FROM company AS a
  95. INNER JOIN company_product AS b ON a.company_id=b.company_id
  96. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  97. WHERE b.is_overseas = 0`
  98. if companyStatus == "正式" {
  99. sql += ` AND b.status IN('正式','永续') `
  100. } else if companyStatus == "试用" {
  101. sql += ` AND b.status='试用' `
  102. } else if companyStatus == "关闭" {
  103. sql += ` AND b.status IN('冻结','流失','关闭') `
  104. }
  105. sql += `
  106. UNION ALL
  107. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  108. CASE t.enabled
  109. WHEN 0 THEN '关闭'
  110. ELSE '试用' END AS company_status,
  111. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time
  112. FROM %s.english_company AS t
  113. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  114. WHERE 1=1 `
  115. if companyStatus == "试用" {
  116. sql += ` AND t.enabled IN(1,2)`
  117. } else if companyStatus == "禁用" {
  118. sql += ` AND t.enabled = 0 `
  119. }
  120. sql += ` )AS m
  121. WHERE 1=1 `
  122. if condition != "" {
  123. sql += condition
  124. }
  125. sql = fmt.Sprintf(sql, databaseName)
  126. err = o.Raw(sql, pars).QueryRow(&count)
  127. return
  128. }
  129. func (obj *Custom) GetCustomList(condition string, pars []interface{}, companyStatus, sortField string, startSize, pageSize, sortDesc int) (list []*Custom, err error) {
  130. o := orm.NewOrm()
  131. sql := ``
  132. var databaseName string
  133. if utils.RunMode == "debug" {
  134. databaseName = "test_v2_hongze_rddp"
  135. } else {
  136. databaseName = "hongze_rddp"
  137. }
  138. sql = `SELECT * FROM (
  139. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  140. CASE b.status
  141. WHEN '正式' THEN '正式'
  142. WHEN '永续' THEN '正式'
  143. WHEN '试用' THEN '试用'
  144. ELSE '关闭' END AS company_status,
  145. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,
  146. 2 AS source,b.overseas_label
  147. FROM company AS a
  148. INNER JOIN company_product AS b ON a.company_id=b.company_id
  149. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  150. WHERE b.product_id=1 AND b.is_overseas = 0`
  151. if companyStatus == "正式" {
  152. sql += ` AND b.status IN('正式','永续') `
  153. } else if companyStatus == "试用" {
  154. sql += ` AND b.status='试用' `
  155. } else if companyStatus == "关闭" {
  156. sql += ` AND b.status IN('冻结','流失','关闭') `
  157. }
  158. sql += `
  159. UNION ALL
  160. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  161. CASE t.enabled
  162. WHEN 0 THEN '关闭'
  163. ELSE '试用' END AS company_status,
  164. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,
  165. 1 AS source,t.overseas_label
  166. FROM %s.english_company AS t
  167. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  168. WHERE 1=1 `
  169. if companyStatus == "试用" {
  170. sql += ` AND t.enabled IN(1,2)`
  171. } else if companyStatus == "禁用" {
  172. sql += ` AND t.enabled = 0 `
  173. }
  174. sql += ` )AS m WHERE 1=1 `
  175. if condition != "" {
  176. sql += condition
  177. }
  178. var sortFieldStr, sortDescStr string
  179. if sortField != "" {
  180. if sortField == "ViewTotal" {
  181. sortFieldStr = "view_total"
  182. } else if sortField == "RoadShowTotal" {
  183. sortFieldStr = "road_show_total"
  184. } else if sortField == "CreateTime" {
  185. sortFieldStr = "create_time"
  186. }
  187. }
  188. if sortDesc == 1 {
  189. sortDescStr = "DESC"
  190. } else {
  191. sortDescStr = "ASC"
  192. }
  193. sql += ` ORDER BY %s %s`
  194. sql += ` LIMIT ?,? `
  195. sql = fmt.Sprintf(sql, databaseName, sortFieldStr, sortDescStr)
  196. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  197. return
  198. }
  199. type CustomListResp struct {
  200. Paging *paging.PagingItem
  201. List []*Custom
  202. StatisticsData []*CustomTotal
  203. OverseasLabelData []*OverseasLabelTotal
  204. }
  205. // EnglishCompanySaveReq 英文客户-保存请求体
  206. type CustomHideReq struct {
  207. CompanyId int `description:"客户ID"`
  208. }
  209. func (obj *Custom) GetCustomByCompanyId(companyId int) (list *Custom, err error) {
  210. o := orm.NewOrm()
  211. sql := ``
  212. var databaseName string
  213. if utils.RunMode == "debug" {
  214. databaseName = "test_v2_hongze_rddp"
  215. } else {
  216. databaseName = "hongze_rddp"
  217. }
  218. sql = `SELECT * FROM (
  219. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  220. CASE b.status
  221. WHEN '正式' THEN '正式'
  222. WHEN '永续' THEN '正式'
  223. WHEN '试用' THEN '试用'
  224. ELSE '关闭' END AS company_status,
  225. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status
  226. FROM company AS a
  227. INNER JOIN company_product AS b ON a.company_id=b.company_id
  228. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  229. WHERE b.is_overseas = 0`
  230. sql += ` UNION ALL
  231. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  232. CASE t.enabled
  233. WHEN 0 THEN '关闭'
  234. ELSE '试用' END AS company_status,
  235. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,a.is_hide,a.overseas_status
  236. FROM %s.english_company AS t
  237. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  238. WHERE 1=1 `
  239. sql += ` )AS m WHERE 1=1 `
  240. sql += ` AND m.company_id=? `
  241. sql = fmt.Sprintf(sql, databaseName)
  242. _, err = o.Raw(sql, companyId).QueryRows(&list)
  243. return
  244. }
  245. type OverseasLabelTotal struct {
  246. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  247. Total int `description:"总数"`
  248. }
  249. func (obj *Custom) GetCustomOverseasLabelTotal(condition string, pars []interface{}) (list []*OverseasLabelTotal, err error) {
  250. o := orm.NewOrm()
  251. sql := ``
  252. var databaseName string
  253. if utils.RunMode == "debug" {
  254. databaseName = "test_v2_hongze_rddp"
  255. } else {
  256. databaseName = "hongze_rddp"
  257. }
  258. sql = `SELECT overseas_label,COUNT(1) AS total FROM (
  259. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  260. CASE b.status
  261. WHEN '正式' THEN '正式'
  262. WHEN '永续' THEN '正式'
  263. WHEN '试用' THEN '试用'
  264. ELSE '关闭' END AS company_status,
  265. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,b.overseas_label
  266. FROM company AS a
  267. INNER JOIN company_product AS b ON a.company_id=b.company_id
  268. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  269. WHERE b.is_overseas = 0
  270. UNION ALL
  271. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  272. CASE t.enabled
  273. WHEN 0 THEN '关闭'
  274. ELSE '试用' END AS company_status,
  275. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.overseas_label
  276. FROM %s.english_company AS t
  277. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  278. )AS m
  279. WHERE 1=1
  280. `
  281. if condition != "" {
  282. sql += condition
  283. }
  284. sql += ` GROUP BY m.overseas_label `
  285. sql = fmt.Sprintf(sql, databaseName)
  286. _, err = o.Raw(sql, pars).QueryRows(&list)
  287. return
  288. }