wx_user.go 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526
  1. package models
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "time"
  5. )
  6. type WxUser struct {
  7. UserId int `orm:"column(user_id);pk"`
  8. OpenId string `description:"open_id"`
  9. UnionId string `description:"union_id"`
  10. Subscribe string `description:"是否关注"`
  11. CompanyId int `description:"客户id"`
  12. NickName string `description:"用户昵称"`
  13. RealName string `description:"用户实际名称"`
  14. UserCode string `description:"用户编码"`
  15. Mobile string `description:"手机号码"`
  16. BindAccount string `description:"绑定时的账号"`
  17. WxCode string `description:"微信号"`
  18. Profession string `description:"职业"`
  19. Email string `description:"邮箱"`
  20. Telephone string `description:"座机"`
  21. Sex int `description:"普通用户性别,1为男性,2为女性"`
  22. Province string `description:"普通用户个人资料填写的省份"`
  23. City string `description:"普通用户个人资料填写的城市"`
  24. Country string `description:"国家,如中国为CN"`
  25. SubscribeTime int `description:"关注时间"`
  26. Remark string `description:"备注"`
  27. Headimgurl string `description:"用户头像,最后一个数值代表正方形头像大小(有0、46、64、96、132数值可选,0代表640*640正方形头像),用户没有头像时该项为空"`
  28. Privilege string `description:"用户特权信息,json数组,如微信沃卡用户为(chinaunicom)"`
  29. Unionid string `description:"用户统一标识。针对一个微信开放平台帐号下的应用,同一用户的unionid是唯一的。"`
  30. FirstLogin int `description:"是否第一次登陆"`
  31. Enabled int `description:"是否可用"`
  32. CreatedTime time.Time `description:"创建时间"`
  33. LastUpdatedTime time.Time `description:"最新一次修改时间"`
  34. Seller string `description:"销售员"`
  35. Note string `description:"客户备份信息"`
  36. IsNote int `description:"是否备注过信息"`
  37. FromType string `description:"report' COMMENT 'report:研报,teleconference:电话会"`
  38. ApplyMethod int `description:"0:未申请,1:已付费客户申请试用,2:非客户申请试用"`
  39. RegisterTime time.Time `description:"注册时间"`
  40. RegisterPlatform int `description:"注册平台,1:微信端,2:PC网页端"`
  41. IsFreeLogin bool `description:"是否免登陆,true:免登陆,false:非免登陆"`
  42. LoginTime time.Time `description:"最近一次登录时间"`
  43. SessionKey string `description:"微信小程序会话密钥"`
  44. IsRegister int `description:"是否注册:1:已注册,0:未注册"`
  45. Source int `description:"绑定来源,1:微信端,2:pc网页端,3:查研观向小程序,4:每日咨询"`
  46. CountryCode string `description:"区号"`
  47. OutboundMobile string `description:"外呼手机号"`
  48. OutboundCountryCode string `description:"外呼手机号区号"`
  49. TripartiteCode string `description:"第三方给过来的用户编码,判断用户是否存在"`
  50. }
  51. //添加用户信息
  52. func AddWxUser(item *WxUser) (lastId int64, err error) {
  53. o := orm.NewOrm()
  54. lastId, err = o.Insert(item)
  55. return
  56. }
  57. type WxUserItem struct {
  58. UserId int `description:"用户id"`
  59. OpenId string `description:"open_id"`
  60. UnionId string `description:"union_id"`
  61. CompanyId int `description:"客户id"`
  62. NickName string `description:"用户昵称"`
  63. RealName string `description:"用户实际名称"`
  64. Mobile string `description:"手机号码"`
  65. BindAccount string `description:"绑定时的账号"`
  66. Email string `description:"邮箱"`
  67. Headimgurl string `description:"用户头像,最后一个数值代表正方形头像大小(有0、46、64、96、132数值可选,0代表640*640正方形头像),用户没有头像时该项为空"`
  68. ApplyMethod int `description:"0:未申请,1:已付费客户申请试用,2:非客户申请试用"`
  69. FirstLogin int `description:"是否第一次登陆"`
  70. IsFreeLogin int `description:"是否免登陆,true:免登陆,false:非免登陆"`
  71. LoginTime time.Time `description:"登录时间"`
  72. CreatedTime time.Time `description:"创建时间"`
  73. LastUpdatedTime time.Time `description:"最近一次修改时间"`
  74. SessionKey string `description:"微信小程序会话密钥"`
  75. CompanyName string `description:"公司名称"`
  76. IsRegister int `description:"是否注册:1:已注册,0:未注册"`
  77. CountryCode string `description:"手机国家区号"`
  78. OutboundMobile string `description:"外呼手机号"`
  79. OutboundCountryCode string `description:"外呼手机号区号"`
  80. IsMsgOutboundMobile int `description:"是否弹窗过绑定外呼手机号区号"`
  81. Source int
  82. }
  83. func GetWxUserItemByUnionid(unionid string) (item *WxUserItem, err error) {
  84. sql := `SELECT * FROM wx_user WHERE union_id=? `
  85. err = orm.NewOrm().Raw(sql, unionid).QueryRow(&item)
  86. return
  87. }
  88. //根据用户ID获取相关信息
  89. func GetWxUserItemByUserId(userId int) (item *WxUserItem, err error) {
  90. sql := `SELECT a.*,b.company_name FROM wx_user AS a
  91. LEFT JOIN company AS b on a.company_id=b.company_id
  92. WHERE a.user_id=? `
  93. err = orm.NewOrm().Raw(sql, userId).QueryRow(&item)
  94. return
  95. }
  96. func GetWxUserItemByOpenId(openId string) (item *WxUserItem, err error) {
  97. sql := `SELECT * FROM wx_user WHERE open_id=? `
  98. err = orm.NewOrm().Raw(sql, openId).QueryRow(&item)
  99. return
  100. }
  101. type WxLoginResp struct {
  102. Authorization string
  103. UserId int
  104. FirstLogin int
  105. Headimgurl string `description:"用户头像"`
  106. Mobile string `description:"手机号"`
  107. Email string `description:"邮箱"`
  108. CompanyName string `description:"客户名称"`
  109. Status string `description:"状态"`
  110. EndDate string `description:"到期日期"`
  111. ProductName string `description:"客户类型名称"`
  112. }
  113. type WxGetUserInfoReq struct {
  114. RawData string `description:"rawData"`
  115. Signature string `description:"signature"`
  116. EncryptedData string `description:"encryptedData"`
  117. Iv string `description:"iv"`
  118. }
  119. //修改用户会话key
  120. func ModifyWxUserSessionKey(sessionKey string, userId int) (err error) {
  121. o := orm.NewOrm()
  122. sql := `UPDATE wx_user SET session_key=? WHERE user_id=? `
  123. _, err = o.Raw(sql, sessionKey, userId).Exec()
  124. return
  125. }
  126. //添加用户信息
  127. func ModifyWxUserInfo(unionId, nickName, province, city, country, avatar string, gender, userId int) (err error) {
  128. o := orm.NewOrm()
  129. sql := `UPDATE wx_user SET union_id=?,unionid=?,nick_name=?,sex=?,province=?,city=?,country=?,headimgurl=? WHERE user_id=? `
  130. _, err = o.Raw(sql, unionId, unionId, nickName, gender, province, city, country, avatar, userId).Exec()
  131. return
  132. }
  133. //修改用户会话key
  134. func DeleteWxUserByUserId(userId int) (err error) {
  135. o := orm.NewOrm()
  136. sql := `DELETE FROM wx_user WHERE user_id=? `
  137. _, err = o.Raw(sql, userId).Exec()
  138. return
  139. }
  140. type WxGetUserInfoResp struct {
  141. //UsersId int `orm:"column(id);pk"`
  142. //Mobile string `description:"手机号"`
  143. //NickName string `description:"昵称"`
  144. //Gender int `description:"用户性别 1:男性,2:女性,0:未知(默认)"`
  145. //CreateTime time.Time `description:"注册时间"`
  146. //ModifyTime time.Time `description:"修改时间"`
  147. //AvatarUrl string `description:"头像地址"`
  148. //City string `description:"城市"`
  149. //Province string `description:"省"`
  150. //Country string `description:"国家"`
  151. //Language string `description:"语言"`
  152. //Appid string `description:"Appid"`
  153. //Timestamp int64 `description:"时间戳"`
  154. Authorization string `description:"登陆凭证,后续接口调用时,带在请求头里面Key:Authorization"`
  155. }
  156. type WxGetPhoneNumberReq struct {
  157. EncryptedData string `description:"encryptedData"`
  158. Iv string `description:"iv"`
  159. }
  160. func ModifyUsersMobile(usersId int, phoneNumber string) (err error) {
  161. o := orm.NewOrm()
  162. sql := `UPDATE wx_user SET mobile=? WHERE user_id=? `
  163. _, err = o.Raw(sql, phoneNumber, usersId).Exec()
  164. return
  165. }
  166. type WxGetPhoneNumberResp struct {
  167. PhoneNumber string `description:"用户绑定的手机号(国外手机号会有区号)"`
  168. PurePhoneNumber string `description:"没有区号的手机号"`
  169. CountryCode string `description:"区号"`
  170. }
  171. //根据用户手机号获取相关信息
  172. func GetWxUserItemByMobile(mobile string) (item *WxUserItem, err error) {
  173. sql := `SELECT a.*,b.company_name FROM wx_user AS a
  174. LEFT JOIN company AS b on a.company_id=b.company_id
  175. WHERE a.mobile= '` + mobile + `' ORDER BY a.company_id DESC LIMIT 1 `
  176. err = orm.NewOrm().Raw(sql).QueryRow(&item)
  177. return
  178. }
  179. func GetWxUserItemByEmail(email string) (item *WxUserItem, err error) {
  180. sql := `SELECT * FROM wx_user WHERE email=? `
  181. err = orm.NewOrm().Raw(sql, email).QueryRow(&item)
  182. return
  183. }
  184. func ModifyReportLastViewTime(uid int) (err error) {
  185. sql := ` UPDATE wx_user SET report_last_view_time=NOW()
  186. WHERE user_id=? `
  187. _, err = orm.NewOrm().Raw(sql, uid).Exec()
  188. return
  189. }
  190. //变更联系人是否已注册状态
  191. func ModifyWxUserRegisterStatus(userId int) (err error) {
  192. o := orm.NewOrm()
  193. sql := `UPDATE wx_user SET is_register=?,source=3,register_time=NOW() WHERE user_id = ? `
  194. _, err = o.Raw(sql, 1, userId).Exec()
  195. return
  196. }
  197. //修改用户是否绑定外呼手机号弹窗
  198. func ModifyWxUserIsMsgOutboundMobile(userId int) (err error) {
  199. o := orm.NewOrm()
  200. sql := `UPDATE wx_user SET is_msg_outbound_mobile=1 WHERE user_id=? `
  201. _, err = o.Raw(sql, userId).Exec()
  202. return
  203. }
  204. //列表
  205. func GetUserListAll() (items []*WxUserItem, err error) {
  206. o := orm.NewOrm()
  207. sql := `SELECT * FROM wx_user WHERE mobile <>'' AND outbound_mobile = ''`
  208. _, err = o.Raw(sql).QueryRows(&items)
  209. return
  210. }
  211. //修改手机号区号 8位号码+852,9位号码+886,10位号码+1,11位及以上号码+86
  212. func UPdateUserCountryCode(item *WxUserItem) (err error) {
  213. o := orm.NewOrm()
  214. if item.CountryCode == "" && len(item.Mobile) >= 11 {
  215. sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=86 , country_code=86 WHERE user_id = ?`
  216. _, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
  217. } else if item.CountryCode == "" && len(item.Mobile) == 8 {
  218. sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=852 , country_code=852 WHERE user_id = ?`
  219. _, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
  220. } else if item.CountryCode == "" && len(item.Mobile) == 9 {
  221. sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=886 , country_code=886 WHERE user_id = ?`
  222. _, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
  223. } else if item.CountryCode == "" && len(item.Mobile) == 10 {
  224. sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=1 , country_code=1 WHERE user_id = ?`
  225. _, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
  226. } else {
  227. sql := ` UPDATE wx_user SET outbound_mobile= ? , outbound_country_code=? WHERE user_id = ?`
  228. _, err = o.Raw(sql, item.Mobile, item.CountryCode, item.UserId).Exec()
  229. }
  230. return
  231. }
  232. //判断公司下用户名称是否存在
  233. func GetUserCountByName(companyId int, name string) (count int, err error) {
  234. sql := `SELECT COUNT(1) AS count FROM wx_user WHERE company_id = ? AND real_name = ?`
  235. err = orm.NewOrm().Raw(sql, companyId, name).QueryRow(&count)
  236. return
  237. }
  238. //判断这个用户是否被设置消息提醒
  239. func GetUserRemind(uid int) (count int, err error) {
  240. sql := `SELECT COUNT(1) AS count FROM cygx_user_remind WHERE user_id = ? `
  241. err = orm.NewOrm().Raw(sql, uid).QueryRow(&count)
  242. return
  243. }
  244. //判断公司下用户名称是否存在
  245. func GetUserCountByThirdName(companyId int, name string) (count int, err error) {
  246. sql := `SELECT COUNT(1) AS count FROM wx_user WHERE company_id = ? AND tripartite_code = ?`
  247. err = orm.NewOrm().Raw(sql, companyId, name).QueryRow(&count)
  248. return
  249. }
  250. func UpdateUserMobile(uid int, mobile string) (err error) {
  251. sql := ` UPDATE wx_user SET mobile=?
  252. WHERE user_id=? `
  253. _, err = orm.NewOrm().Raw(sql, mobile, uid).Exec()
  254. return
  255. }
  256. //获取公司下用户详情详情详情
  257. func GetUserByName(companyId int, name string) (item *WxUser, err error) {
  258. o := orm.NewOrm()
  259. sql := `SELECT * FROM wx_user WHERE company_id = ? AND real_name = ? `
  260. err = o.Raw(sql, companyId, name).QueryRow(&item)
  261. return
  262. }
  263. //获取公司下用户详情详情详情
  264. func GetUserByThirdName(companyId int, name string) (item *WxUser, err error) {
  265. o := orm.NewOrm()
  266. sql := `SELECT * FROM wx_user WHERE company_id = ? AND tripartite_code = ? `
  267. err = o.Raw(sql, companyId, name).QueryRow(&item)
  268. return
  269. }
  270. //获取所有注册的权益用户
  271. func GetUserRegisterList() (items []*WxUser, err error) {
  272. o := orm.NewOrm()
  273. sql := `SELECT
  274. u.user_id,
  275. u.company_id,
  276. u.mobile,
  277. u.email,
  278. u.real_name,
  279. u.is_register,
  280. u.is_maker,
  281. u.register_time
  282. FROM
  283. wx_user AS u
  284. INNER JOIN company AS c ON c.company_id = u.company_id
  285. INNER JOIN company_product AS cp ON cp.company_id = c.company_id
  286. WHERE
  287. u.company_id IN (
  288. SELECT
  289. a.company_id
  290. FROM
  291. company AS a
  292. INNER JOIN company_product AS b ON a.company_id = b.company_id
  293. WHERE
  294. a.enabled = 1
  295. AND b.STATUS IN ( '正式', '试用', '冻结' )
  296. AND cp.product_id = 2
  297. )
  298. AND cp.product_id = 2
  299. GROUP BY
  300. u.user_id `
  301. _, err = o.Raw(sql).QueryRows(&items)
  302. //AND u.register_time IS NOT NULL OR u.report_last_view_time <>'' 统计阅读时间与注册时间不为空的用户
  303. return
  304. }
  305. type CygxCompanyUser struct {
  306. Labels string `description:"标签,用英文,隔开"`
  307. }
  308. type UserLabel struct {
  309. Label string `description:"标签 多个用 , 隔开"`
  310. }
  311. //对获取关注的产业
  312. func GetCygxCompanyUserListSplit(userIds string) (labels string, err error) {
  313. o := orm.NewOrm()
  314. sql := `SELECT
  315. (
  316. SELECT
  317. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR ',' )
  318. FROM
  319. cygx_industrial_management AS man
  320. WHERE
  321. man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industry_fllow AS f WHERE f.user_id = u.user_id AND f.type = 1 )
  322. ) AS labels
  323. FROM
  324. wx_user AS u WHERE u.user_id = ? `
  325. err = o.Raw(sql, userIds).QueryRow(&labels)
  326. return
  327. }
  328. //阅读记录
  329. type ArticlePvCountResp struct {
  330. ArticleId int `description:"文章ID"`
  331. Pv int `description:"Pv"`
  332. }
  333. func GetArticlePvCount(mobile, email, dateTime string) (item []*ArticlePvCountResp, err error) {
  334. o := orm.NewOrm()
  335. sql := `SELECT
  336. article_id,
  337. COUNT(article_id) as pv
  338. FROM
  339. cygx_article_history_record_newpv as h
  340. WHERE
  341. mobile = ?
  342. AND email = ?
  343. AND create_time >= ?
  344. GROUP BY article_id `
  345. _, err = o.Raw(sql, mobile, email, dateTime).QueryRows(&item)
  346. return
  347. }
  348. //产业名称
  349. type ArticleIndustryNameResp struct {
  350. ArticleId int `description:"文章ID"`
  351. IndustryName string `description:"产业名称"`
  352. SubjectName string `description:"标的名称"`
  353. }
  354. func GetArticleGroupyIndustryName(articleIds string) (item []*ArticleIndustryNameResp, err error) {
  355. o := orm.NewOrm()
  356. sql := `SELECT
  357. art.article_id,
  358. (
  359. SELECT
  360. GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' )
  361. FROM
  362. cygx_industrial_subject AS s
  363. WHERE
  364. s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id )
  365. ) AS subject_name,
  366. (
  367. SELECT
  368. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' )
  369. FROM
  370. cygx_industrial_management AS man
  371. WHERE
  372. man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industrial_article_group_management AS man_g WHERE man_g.article_id = art.article_id )
  373. ) AS industry_name
  374. FROM
  375. cygx_article_history_record_newpv AS r
  376. INNER JOIN cygx_article AS art ON art.article_id = r.article_id
  377. LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
  378. LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id
  379. WHERE
  380. art.article_id IN ( ` + articleIds + `) GROUP BY art.article_id `
  381. _, err = o.Raw(sql).QueryRows(&item)
  382. return
  383. }
  384. func GetCygxArticleCollectId(uid int, dateTime string) (articleIds string, err error) {
  385. sql := `SELECT
  386. GROUP_CONCAT( DISTINCT article_id SEPARATOR ',' ) AS permission
  387. FROM
  388. cygx_article_collect
  389. WHERE
  390. user_id = ?
  391. AND create_time >= ? `
  392. o := orm.NewOrm()
  393. err = o.Raw(sql, uid, dateTime).QueryRow(&articleIds)
  394. return
  395. }
  396. //活动标签记录
  397. type ActivityLabelCountResp struct {
  398. Label string `description:"标签"`
  399. Pv int `description:"Pv"`
  400. }
  401. func GetActivitySignCount(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) {
  402. o := orm.NewOrm()
  403. sql := `SELECT
  404. label ,
  405. COUNT( label ) AS pv
  406. FROM
  407. cygx_activity
  408. WHERE
  409. activity_id IN ( SELECT activity_id FROM cygx_activity_signup WHERE mobile = ? )
  410. AND label != ''
  411. AND activity_time >= ?
  412. AND is_submit_meeting = 1
  413. GROUP BY
  414. label`
  415. _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item)
  416. return
  417. }
  418. type CygxUserInteractionNumDetail struct {
  419. UserId int64 `orm:"column(user_id);pk"`
  420. Mobile string `description:"手机号"`
  421. Email string `description:"邮箱"`
  422. CompanyId int `description:"公司id"`
  423. CompanyName string `description:"公司名称"`
  424. RealName string `description:"姓名"`
  425. CreatedTime string `description:"创建时间"`
  426. IsMaker int `description:"是否决策人,1:是,0:否"`
  427. IsRegister bool `description:"是否注册,true:已注册,false:未注册"`
  428. Status string `description:"客户状态"`
  429. RegisterTime string `description:"注册时间"`
  430. SellerName string `description:"销售名称"`
  431. InteractionNum int `description:"互动量"`
  432. Labels string `description:"标签,用英文,隔开"`
  433. ActivityLabel string `description:"活动标签,用英文,隔开"`
  434. IsShowSee bool `description:"是否展示查看"`
  435. IsShowSeeNum int `description:"是否展示查看"`
  436. HistoryNum int `description:"报告阅读"`
  437. CountNum int `description:"报告收藏"`
  438. ChartCountNum int `description:"图表收藏"`
  439. IndustryFllowNum int `description:"产业关注"`
  440. DepartmentFollowNum int `description:"作者关注"`
  441. KeyWordNum int `description:"搜索关键词"`
  442. OnLineNum int `description:"线上互动活动"`
  443. OfficeNum int `description:"线下互动活动"`
  444. PackageType int `description:"套餐类型,0:无,1:大套餐,2:小套餐"`
  445. TryStage int `description:"试用客户子标签:0全部、1未分类、2 推进、3 跟踪、4 预备"`
  446. }
  447. //获取用户的互动量
  448. func GetCygxCompanyUserUserInteraction(userIds string) (items []*CygxUserInteractionNumDetail, err error) {
  449. o := orm.NewOrm()
  450. sql := `SELECT
  451. u.user_id,
  452. u.company_id,
  453. ( SELECT COUNT( 1 ) FROM cygx_article_history_record_all AS h INNER JOIN cygx_article as art ON art.article_id = h.article_id WHERE h.mobile = u.mobile AND h.is_del = 0 AND h.mobile <>'' ) AS history_num,
  454. ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.user_id = u.user_id ) AS count_num,
  455. ( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h WHERE h.user_id = u.user_id ) AS chart_count_num,
  456. ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id ) AS industry_fllow_num,
  457. ( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = u.user_id ) AS department_follow_num,
  458. ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = u.user_id ) AS key_word_num,
  459. ( SELECT COUNT( 1 ) FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE h.mobile = u.mobile
  460. AND t.activity_type = 1 AND h.do_fail_type = 0 ) AS on_line_num,
  461. ( SELECT COUNT( 1 ) FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE h.mobile = u.mobile
  462. AND t.activity_type = 0 AND h.do_fail_type = 0 ) AS office_num,
  463. (
  464. SELECT
  465. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR ',' )
  466. FROM
  467. cygx_industrial_management AS man
  468. WHERE
  469. man.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industry_fllow AS f WHERE f.user_id = u.user_id AND f.type = 1 )
  470. ) AS labels,
  471. (
  472. SELECT
  473. GROUP_CONCAT( DISTINCT man.label SEPARATOR ',' )
  474. FROM
  475. cygx_activity AS man
  476. WHERE
  477. man.activity_id IN ( SELECT activity_id FROM cygx_activity_signup AS f WHERE f.user_id = u.user_id AND label != '')
  478. ) AS activity_label
  479. FROM
  480. wx_user AS u WHERE u.user_id IN( ` + userIds + `) `
  481. _, err = o.Raw(sql).QueryRows(&items)
  482. return
  483. }