custom.go 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377
  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. func (obj *Custom) GetCustomOverseasStatusTotal(condition string, pars []interface{}) (total int, err error) {
  69. o := orm.NewOrm()
  70. sql := ``
  71. var databaseName string
  72. if utils.RunMode == "debug" {
  73. databaseName = "test_v2_hongze_rddp"
  74. } else {
  75. databaseName = "hongze_rddp"
  76. }
  77. sql = `SELECT COUNT(1) AS total FROM (
  78. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  79. CASE b.status
  80. WHEN '正式' THEN '正式'
  81. WHEN '永续' THEN '正式'
  82. WHEN '试用' THEN '试用'
  83. ELSE '关闭' END AS company_status,
  84. 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
  85. FROM company AS a
  86. INNER JOIN company_product AS b ON a.company_id=b.company_id
  87. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  88. WHERE b.is_overseas = 0
  89. UNION ALL
  90. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  91. CASE t.enabled
  92. WHEN 0 THEN '关闭'
  93. ELSE '试用' END AS company_status,
  94. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  95. FROM %s.english_company AS t
  96. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  97. )AS m
  98. WHERE 1=1
  99. AND m.is_hide=0
  100. AND m. overseas_status='正式' `
  101. if condition != "" {
  102. sql += condition
  103. }
  104. sql += ` GROUP BY m.overseas_status `
  105. sql = fmt.Sprintf(sql, databaseName)
  106. err = o.Raw(sql, pars).QueryRow(&total)
  107. return
  108. }
  109. // GetCompanyIdsByKeyword 关键词获取客户IDs
  110. func (obj *Custom) GetCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
  111. o := orm.NewOrm()
  112. sql := ` SELECT DISTINCT b.company_id FROM wx_user AS a
  113. INNER JOIN company AS b ON a.company_id=b.company_id
  114. WHERE 1=1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ? OR b.credit_code LIKE ? ) `
  115. _, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
  116. return
  117. }
  118. func (obj *Custom) GetCustomListCount(condition string, pars []interface{}, companyStatus string) (count int, err error) {
  119. o := orm.NewOrm()
  120. sql := ``
  121. var databaseName string
  122. if utils.RunMode == "debug" {
  123. databaseName = "test_v2_hongze_rddp"
  124. } else {
  125. databaseName = "hongze_rddp"
  126. }
  127. sql = `SELECT COUNT(1) AS count FROM (
  128. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  129. CASE b.status
  130. WHEN '正式' THEN '正式'
  131. WHEN '永续' THEN '正式'
  132. WHEN '试用' THEN '试用'
  133. ELSE '关闭' END AS company_status,
  134. 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
  135. FROM company AS a
  136. INNER JOIN company_product AS b ON a.company_id=b.company_id
  137. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  138. WHERE b.is_overseas = 0`
  139. if companyStatus == "正式" {
  140. sql += ` AND b.status IN('正式','永续') `
  141. } else if companyStatus == "试用" {
  142. sql += ` AND b.status='试用' `
  143. } else if companyStatus == "关闭" {
  144. sql += ` AND b.status IN('冻结','流失','关闭') `
  145. }
  146. sql += `
  147. UNION ALL
  148. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  149. CASE t.enabled
  150. WHEN 0 THEN '关闭'
  151. ELSE '试用' END AS company_status,
  152. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  153. FROM %s.english_company AS t
  154. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  155. WHERE 1=1 `
  156. if companyStatus == "试用" {
  157. sql += ` AND t.enabled IN(1,2)`
  158. } else if companyStatus == "禁用" {
  159. sql += ` AND t.enabled = 0 `
  160. }
  161. sql += ` )AS m
  162. WHERE 1=1 `
  163. if condition != "" {
  164. sql += condition
  165. }
  166. sql = fmt.Sprintf(sql, databaseName)
  167. err = o.Raw(sql, pars).QueryRow(&count)
  168. return
  169. }
  170. func (obj *Custom) GetCustomList(condition string, pars []interface{}, companyStatus, sortField string, startSize, pageSize, sortDesc int) (list []*Custom, err error) {
  171. o := orm.NewOrm()
  172. sql := ``
  173. var databaseName string
  174. if utils.RunMode == "debug" {
  175. databaseName = "test_v2_hongze_rddp"
  176. } else {
  177. databaseName = "hongze_rddp"
  178. }
  179. sql = `SELECT * FROM (
  180. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  181. CASE b.status
  182. WHEN '正式' THEN '正式'
  183. WHEN '永续' THEN '正式'
  184. WHEN '试用' THEN '试用'
  185. ELSE '关闭' END AS company_status,
  186. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,
  187. 2 AS source,b.overseas_label
  188. FROM company AS a
  189. INNER JOIN company_product AS b ON a.company_id=b.company_id
  190. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  191. WHERE b.product_id=1 AND b.is_overseas = 0`
  192. if companyStatus == "正式" {
  193. sql += ` AND b.status IN('正式','永续') `
  194. } else if companyStatus == "试用" {
  195. sql += ` AND b.status='试用' `
  196. } else if companyStatus == "关闭" {
  197. sql += ` AND b.status IN('冻结','流失','关闭') `
  198. }
  199. sql += `
  200. UNION ALL
  201. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  202. CASE t.enabled
  203. WHEN 0 THEN '关闭'
  204. ELSE '试用' END AS company_status,
  205. t.view_total,t.road_show_total,t.create_time,t.last_view_time,t.is_hide,t.overseas_status,
  206. 1 AS source,t.overseas_label
  207. FROM %s.english_company AS t
  208. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  209. WHERE 1=1 `
  210. if companyStatus == "试用" {
  211. sql += ` AND t.enabled IN(1,2)`
  212. } else if companyStatus == "禁用" {
  213. sql += ` AND t.enabled = 0 `
  214. }
  215. sql += ` )AS m WHERE 1=1 `
  216. if condition != "" {
  217. sql += condition
  218. }
  219. var sortFieldStr, sortDescStr string
  220. if sortField != "" {
  221. if sortField == "ViewTotal" {
  222. sortFieldStr = "view_total"
  223. } else if sortField == "RoadShowTotal" {
  224. sortFieldStr = "road_show_total"
  225. } else if sortField == "CreateTime" {
  226. sortFieldStr = "create_time"
  227. } else if sortField == "LastViewTime" {
  228. sortFieldStr = "last_view_time"
  229. }
  230. }
  231. if sortDesc == 1 {
  232. sortDescStr = "DESC"
  233. } else {
  234. sortDescStr = "ASC"
  235. }
  236. sql += ` ORDER BY %s %s`
  237. sql += ` LIMIT ?,? `
  238. sql = fmt.Sprintf(sql, databaseName, sortFieldStr, sortDescStr)
  239. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  240. return
  241. }
  242. type CustomListResp struct {
  243. Paging *paging.PagingItem
  244. List []*Custom
  245. }
  246. type CustomHideReq struct {
  247. CompanyId int `description:"客户ID"`
  248. }
  249. func (obj *Custom) GetCustomByCompanyId(companyId int) (item *Custom, 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. //if companyId > utils.EnCompanyIdStep {
  259. // companyId = companyId - utils.EnCompanyIdStep
  260. //}
  261. sql = `SELECT * FROM (
  262. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  263. CASE b.status
  264. WHEN '正式' THEN '正式'
  265. WHEN '永续' THEN '正式'
  266. WHEN '试用' THEN '试用'
  267. ELSE '关闭' END AS company_status,
  268. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status
  269. FROM company AS a
  270. INNER JOIN company_product AS b ON a.company_id=b.company_id
  271. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  272. WHERE b.is_overseas = 0`
  273. sql += ` UNION ALL
  274. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  275. CASE t.enabled
  276. WHEN 0 THEN '关闭'
  277. ELSE '试用' END AS company_status,
  278. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status
  279. FROM %s.english_company AS t
  280. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  281. WHERE 1=1 `
  282. sql += ` )AS m WHERE 1=1 `
  283. sql += ` AND m.company_id=? `
  284. sql = fmt.Sprintf(sql, databaseName)
  285. err = o.Raw(sql, companyId).QueryRow(&item)
  286. return
  287. }
  288. type OverseasLabelTotal struct {
  289. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  290. Total int `description:"总数"`
  291. }
  292. func (obj *Custom) GetCustomOverseasLabelTotal(condition string, pars []interface{}) (list []*OverseasLabelTotal, err error) {
  293. o := orm.NewOrm()
  294. sql := ``
  295. var databaseName string
  296. if utils.RunMode == "debug" {
  297. databaseName = "test_v2_hongze_rddp"
  298. } else {
  299. databaseName = "hongze_rddp"
  300. }
  301. sql = `SELECT overseas_label,COUNT(1) AS total FROM (
  302. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  303. CASE b.status
  304. WHEN '正式' THEN '正式'
  305. WHEN '永续' THEN '正式'
  306. WHEN '试用' THEN '试用'
  307. ELSE '关闭' END AS company_status,
  308. 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
  309. FROM company AS a
  310. INNER JOIN company_product AS b ON a.company_id=b.company_id
  311. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  312. WHERE b.is_overseas = 0
  313. UNION ALL
  314. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  315. CASE t.enabled
  316. WHEN 0 THEN '关闭'
  317. ELSE '试用' END AS company_status,
  318. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  319. FROM %s.english_company AS t
  320. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  321. )AS m
  322. WHERE 1=1
  323. `
  324. if condition != "" {
  325. sql += condition
  326. }
  327. sql += ` GROUP BY m.overseas_label `
  328. sql = fmt.Sprintf(sql, databaseName)
  329. _, err = o.Raw(sql, pars).QueryRows(&list)
  330. return
  331. }
  332. type CustomOverseasLabelSetReq struct {
  333. CompanyId int `description:"客户ID"`
  334. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  335. }