custom.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332
  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,is_hide 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,a.is_hide,a.overseas_status,b.overseas_label
  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,t.is_hide,t.overseas_status,t.overseas_label
  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,a.is_hide,a.overseas_status,b.overseas_label
  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,t.is_hide,t.overseas_status,t.overseas_label
  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,t.road_show_total,t.create_time,t.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. } else if sortField == "LastViewTime" {
  187. sortFieldStr = "last_view_time"
  188. }
  189. }
  190. if sortDesc == 1 {
  191. sortDescStr = "DESC"
  192. } else {
  193. sortDescStr = "ASC"
  194. }
  195. sql += ` ORDER BY %s %s`
  196. sql += ` LIMIT ?,? `
  197. sql = fmt.Sprintf(sql, databaseName, sortFieldStr, sortDescStr)
  198. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  199. return
  200. }
  201. type CustomListResp struct {
  202. Paging *paging.PagingItem
  203. List []*Custom
  204. StatisticsData []*CustomTotal
  205. OverseasLabelData []*OverseasLabelTotal
  206. }
  207. type CustomHideReq struct {
  208. CompanyId int `description:"客户ID"`
  209. }
  210. func (obj *Custom) GetCustomByCompanyId(companyId int) (item *Custom, err error) {
  211. o := orm.NewOrm()
  212. sql := ``
  213. var databaseName string
  214. if utils.RunMode == "debug" {
  215. databaseName = "test_v2_hongze_rddp"
  216. } else {
  217. databaseName = "hongze_rddp"
  218. }
  219. //if companyId > utils.EnCompanyIdStep {
  220. // companyId = companyId - utils.EnCompanyIdStep
  221. //}
  222. sql = `SELECT * FROM (
  223. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  224. CASE b.status
  225. WHEN '正式' THEN '正式'
  226. WHEN '永续' THEN '正式'
  227. WHEN '试用' THEN '试用'
  228. ELSE '关闭' END AS company_status,
  229. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status
  230. FROM company AS a
  231. INNER JOIN company_product AS b ON a.company_id=b.company_id
  232. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  233. WHERE b.is_overseas = 0`
  234. sql += ` UNION ALL
  235. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  236. CASE t.enabled
  237. WHEN 0 THEN '关闭'
  238. ELSE '试用' END AS company_status,
  239. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status
  240. FROM %s.english_company AS t
  241. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  242. WHERE 1=1 `
  243. sql += ` )AS m WHERE 1=1 `
  244. sql += ` AND m.company_id=? `
  245. sql = fmt.Sprintf(sql, databaseName)
  246. err = o.Raw(sql, companyId).QueryRow(&item)
  247. return
  248. }
  249. type OverseasLabelTotal struct {
  250. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  251. Total int `description:"总数"`
  252. }
  253. func (obj *Custom) GetCustomOverseasLabelTotal(condition string, pars []interface{}) (list []*OverseasLabelTotal, err error) {
  254. o := orm.NewOrm()
  255. sql := ``
  256. var databaseName string
  257. if utils.RunMode == "debug" {
  258. databaseName = "test_v2_hongze_rddp"
  259. } else {
  260. databaseName = "hongze_rddp"
  261. }
  262. sql = `SELECT overseas_label,COUNT(1) AS total FROM (
  263. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  264. CASE b.status
  265. WHEN '正式' THEN '正式'
  266. WHEN '永续' THEN '正式'
  267. WHEN '试用' THEN '试用'
  268. ELSE '关闭' END AS company_status,
  269. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,b.overseas_label
  270. FROM company AS a
  271. INNER JOIN company_product AS b ON a.company_id=b.company_id
  272. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  273. WHERE b.is_overseas = 0
  274. UNION ALL
  275. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  276. CASE t.enabled
  277. WHEN 0 THEN '关闭'
  278. ELSE '试用' END AS company_status,
  279. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  280. FROM %s.english_company AS t
  281. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  282. )AS m
  283. WHERE 1=1
  284. `
  285. if condition != "" {
  286. sql += condition
  287. }
  288. sql += ` GROUP BY m.overseas_label `
  289. sql = fmt.Sprintf(sql, databaseName)
  290. _, err = o.Raw(sql, pars).QueryRows(&list)
  291. return
  292. }
  293. type CustomOverseasLabelSetReq struct {
  294. CompanyId int `description:"客户ID"`
  295. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  296. }