custom.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341
  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. ResetBtn int `description:"转正式和重置按钮:同步过来默认为0:显示转正式为1:显示重置为2"`
  24. }
  25. type CustomTotal struct {
  26. CompanyStatus string `description:"状态"`
  27. Total int `description:"总数"`
  28. }
  29. func (obj *Custom) GetCustomTotal(condition string, pars []interface{}) (list []*CustomTotal, err error) {
  30. o := orm.NewOrm()
  31. sql := ``
  32. var databaseName string
  33. if utils.RunMode == "debug" {
  34. databaseName = "test_v2_hongze_rddp"
  35. } else {
  36. databaseName = "hongze_rddp"
  37. }
  38. sql = `SELECT company_status,COUNT(1) AS total,is_hide FROM (
  39. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  40. 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
  41. FROM company AS a
  42. INNER JOIN company_product AS b ON a.company_id=b.company_id
  43. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  44. WHERE b.is_overseas = 0
  45. UNION ALL
  46. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  47. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  48. FROM %s.english_company AS t
  49. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  50. WHERE 1=1 AND t.is_deleted=0
  51. )AS m
  52. WHERE 1=1
  53. `
  54. if condition != "" {
  55. sql += condition
  56. }
  57. sql += ` GROUP BY m.company_status `
  58. sql = fmt.Sprintf(sql, databaseName)
  59. _, err = o.Raw(sql, pars).QueryRows(&list)
  60. return
  61. }
  62. func (obj *Custom) GetCustomOverseasStatusTotal(condition string, pars []interface{}) (total int, err error) {
  63. o := orm.NewOrm()
  64. sql := ``
  65. var databaseName string
  66. if utils.RunMode == "debug" {
  67. databaseName = "test_v2_hongze_rddp"
  68. } else {
  69. databaseName = "hongze_rddp"
  70. }
  71. sql = `SELECT COUNT(1) AS total FROM (
  72. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  73. 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
  74. FROM company AS a
  75. INNER JOIN company_product AS b ON a.company_id=b.company_id
  76. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  77. WHERE b.is_overseas = 0
  78. UNION ALL
  79. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  80. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  81. FROM %s.english_company AS t
  82. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  83. WHERE 1=1 AND t.is_deleted=0
  84. )AS m
  85. WHERE 1=1
  86. AND m.is_hide=0
  87. AND m. overseas_status='正式' `
  88. if condition != "" {
  89. sql += condition
  90. }
  91. sql += ` GROUP BY m.overseas_status `
  92. sql = fmt.Sprintf(sql, databaseName)
  93. err = o.Raw(sql, pars).QueryRow(&total)
  94. return
  95. }
  96. // GetCompanyIdsByKeyword 关键词获取客户IDs
  97. func (obj *Custom) GetCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
  98. o := orm.NewOrm()
  99. sql := ` SELECT DISTINCT b.company_id FROM wx_user AS a
  100. INNER JOIN company AS b ON a.company_id=b.company_id
  101. WHERE 1=1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ? OR b.credit_code LIKE ? ) `
  102. _, err = o.Raw(sql, keyword, keyword, keyword, keyword).QueryRows(&companyIds)
  103. return
  104. }
  105. func (obj *Custom) GetCustomListCount(condition string, pars []interface{}, companyStatus string) (count int, err error) {
  106. o := orm.NewOrm()
  107. sql := ``
  108. var databaseName string
  109. if utils.RunMode == "debug" {
  110. databaseName = "test_v2_hongze_rddp"
  111. } else {
  112. databaseName = "hongze_rddp"
  113. }
  114. sql = `SELECT COUNT(1) AS count FROM (
  115. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  116. 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
  117. FROM company AS a
  118. INNER JOIN company_product AS b ON a.company_id=b.company_id
  119. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  120. WHERE b.is_overseas = 0`
  121. if companyStatus == "正式" {
  122. sql += ` AND b.status IN('正式','永续') `
  123. } else if companyStatus == "试用" {
  124. sql += ` AND b.status='试用' `
  125. } else if companyStatus == "关闭" {
  126. sql += ` AND b.status IN('冻结','流失','关闭') `
  127. }
  128. sql += `
  129. UNION ALL
  130. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  131. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  132. FROM %s.english_company AS t
  133. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  134. WHERE 1=1 AND t.is_deleted=0 `
  135. if companyStatus == "试用" {
  136. sql += ` AND t.enabled IN(1,2)`
  137. } else if companyStatus == "禁用" {
  138. sql += ` AND t.enabled = 0 `
  139. }
  140. sql += ` )AS m
  141. WHERE 1=1 `
  142. if condition != "" {
  143. sql += condition
  144. }
  145. sql = fmt.Sprintf(sql, databaseName)
  146. err = o.Raw(sql, pars).QueryRow(&count)
  147. return
  148. }
  149. func (obj *Custom) GetCustomList(condition string, pars []interface{}, companyStatus, sortField string, startSize, pageSize, sortDesc int) (list []*Custom, err error) {
  150. o := orm.NewOrm()
  151. sql := ``
  152. var databaseName string
  153. if utils.RunMode == "debug" {
  154. databaseName = "test_v2_hongze_rddp"
  155. } else {
  156. databaseName = "hongze_rddp"
  157. }
  158. sql = `SELECT * FROM (
  159. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  160. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status,
  161. 2 AS source,b.overseas_label,a.reset_btn
  162. FROM company AS a
  163. INNER JOIN company_product AS b ON a.company_id=b.company_id
  164. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  165. WHERE b.product_id=1 AND b.is_overseas = 0`
  166. if companyStatus == "正式" {
  167. sql += ` AND b.status IN('正式','永续') `
  168. } else if companyStatus == "试用" {
  169. sql += ` AND b.status='试用' `
  170. } else if companyStatus == "关闭" {
  171. sql += ` AND b.status IN('冻结','流失','关闭') `
  172. }
  173. sql += `
  174. UNION ALL
  175. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  176. t.view_total,t.road_show_total,t.create_time,t.last_view_time,t.is_hide,t.overseas_status,
  177. 1 AS source,t.overseas_label,t.reset_btn
  178. FROM %s.english_company AS t
  179. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  180. WHERE 1=1 AND t.is_deleted=0 `
  181. if companyStatus == "试用" {
  182. sql += ` AND t.enabled IN(1,2)`
  183. } else if companyStatus == "禁用" {
  184. sql += ` AND t.enabled = 0 `
  185. }
  186. sql += ` )AS m WHERE 1=1 `
  187. if condition != "" {
  188. sql += condition
  189. }
  190. var sortFieldStr, sortDescStr string
  191. if sortField != "" {
  192. if sortField == "ViewTotal" {
  193. sortFieldStr = "view_total"
  194. } else if sortField == "RoadShowTotal" {
  195. sortFieldStr = "road_show_total"
  196. } else if sortField == "CreateTime" {
  197. sortFieldStr = "create_time"
  198. } else if sortField == "LastViewTime" {
  199. sortFieldStr = "last_view_time"
  200. }
  201. }
  202. if sortDesc == 1 {
  203. sortDescStr = "DESC"
  204. } else {
  205. sortDescStr = "ASC"
  206. }
  207. sql += ` ORDER BY %s %s`
  208. sql += ` LIMIT ?,? `
  209. sql = fmt.Sprintf(sql, databaseName, sortFieldStr, sortDescStr)
  210. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  211. return
  212. }
  213. type CustomListResp struct {
  214. Paging *paging.PagingItem
  215. List []*Custom
  216. }
  217. type CustomHideReq struct {
  218. CompanyId int `description:"客户ID"`
  219. }
  220. func (obj *Custom) GetCustomByCompanyId(companyId int) (item *Custom, err error) {
  221. o := orm.NewOrm()
  222. sql := ``
  223. var databaseName string
  224. if utils.RunMode == "debug" {
  225. databaseName = "test_v2_hongze_rddp"
  226. } else {
  227. databaseName = "hongze_rddp"
  228. }
  229. //if companyId > utils.EnCompanyIdStep {
  230. // companyId = companyId - utils.EnCompanyIdStep
  231. //}
  232. sql = `SELECT * FROM (
  233. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  234. b.view_total,b.road_show_total,a.created_time AS create_time,b.last_view_time,a.is_hide,a.overseas_status
  235. FROM company AS a
  236. INNER JOIN company_product AS b ON a.company_id=b.company_id
  237. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  238. WHERE b.is_overseas = 0`
  239. sql += ` UNION ALL
  240. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  241. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status
  242. FROM %s.english_company AS t
  243. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  244. WHERE 1=1 `
  245. sql += ` )AS m WHERE 1=1 `
  246. sql += ` AND m.company_id=? `
  247. sql = fmt.Sprintf(sql, databaseName)
  248. err = o.Raw(sql, companyId).QueryRow(&item)
  249. return
  250. }
  251. type OverseasLabelTotal struct {
  252. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  253. Total int `description:"总数"`
  254. }
  255. func (obj *Custom) GetCustomOverseasLabelTotal(condition string, pars []interface{}) (list []*OverseasLabelTotal, err error) {
  256. o := orm.NewOrm()
  257. sql := ``
  258. var databaseName string
  259. if utils.RunMode == "debug" {
  260. databaseName = "test_v2_hongze_rddp"
  261. } else {
  262. databaseName = "hongze_rddp"
  263. }
  264. sql = `SELECT overseas_label,COUNT(1) AS total FROM (
  265. SELECT a.company_id,a.company_name,a.nation,b.seller_id,b.seller_name,
  266. 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
  267. FROM company AS a
  268. INNER JOIN company_product AS b ON a.company_id=b.company_id
  269. INNER JOIN overseas_custom_seller AS c ON b.seller_id=c.seller_id
  270. WHERE b.is_overseas = 0
  271. UNION ALL
  272. SELECT t.company_id+10000000,t.company_name,t.nation,t.seller_id,t.seller_name,
  273. t.view_total,0 AS road_show_total,t.create_time,'' AS last_view_time,t.is_hide,t.overseas_status,t.overseas_label
  274. FROM %s.english_company AS t
  275. INNER JOIN overseas_custom_seller AS n ON t.seller_id=n.seller_id
  276. WHERE 1=1 AND t.is_deleted=0
  277. )AS m
  278. WHERE 1=1
  279. `
  280. if condition != "" {
  281. sql += condition
  282. }
  283. sql += ` GROUP BY m.overseas_label `
  284. sql = fmt.Sprintf(sql, databaseName)
  285. _, err = o.Raw(sql, pars).QueryRows(&list)
  286. return
  287. }
  288. type CustomOverseasLabelSetReq struct {
  289. CompanyId int `description:"客户ID"`
  290. OverseasLabel int `description:"海外客户试用子标签:1未分类、2 推进、3 跟踪、4 预备、"`
  291. }
  292. // 更新客户产品信息
  293. func (obj *Custom) UpdateCompanyProductOverseasLabel(overseasLabel, companyId int) (err error) {
  294. o := orm.NewOrm()
  295. sql := ` UPDATE company_product SET overseas_label=? WHERE company_id=? AND product_id=1 `
  296. _, err = o.Raw(sql, overseasLabel, companyId).Exec()
  297. return
  298. }