wechat_send_msg.go 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476
  1. package models
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "hongze/hz_eta_api/utils"
  5. "strings"
  6. )
  7. type OpenIdList struct {
  8. OpenId string
  9. UserId int
  10. }
  11. func GetOpenIdList() (items []*OpenIdList, err error) {
  12. //openIdstr := WxUsersGet()
  13. //sql:=` SELECT DISTINCT open_id FROM wx_user AS wu
  14. // INNER JOIN company AS c ON c.company_id = wu.company_id
  15. // INNER JOIN company_product AS d ON c.company_id=d.company_id
  16. // WHERE wu.open_id IS NOT NULL AND d.status IN('正式','试用','永续') `
  17. sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu
  18. INNER JOIN company AS c ON c.company_id = wu.company_id
  19. INNER JOIN company_product AS d ON c.company_id=d.company_id
  20. INNER join user_record as ur on wu.user_id=ur.user_id
  21. WHERE ur.open_id != "" AND ur.subscribe=1 and ur.create_platform=1 AND d.status IN('正式','试用','永续') `
  22. //if openIdstr != "" {
  23. // sql += ` AND ur.open_id in (` + openIdstr + `) `
  24. //}
  25. _, err = orm.NewOrm().Raw(sql).QueryRows(&items)
  26. return
  27. }
  28. func GetOpenIdListByMobile(mobile string) (items []*OpenIdList, err error) {
  29. //openIdstr := WxUsersGet()
  30. sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu
  31. INNER JOIN company AS c ON c.company_id = wu.company_id
  32. INNER join user_record as ur on wu.user_id=ur.user_id
  33. WHERE ur.open_id != "" AND ur.subscribe=1 and ur.create_platform=1 AND wu.mobile=? `
  34. //if openIdstr != "" {
  35. // sql += ` AND ur.open_id in (` + openIdstr + `) `
  36. //}
  37. _, err = orm.NewOrm().Raw(sql, mobile).QueryRows(&items)
  38. return
  39. }
  40. // 获取预约活动的用户的openID
  41. func GetActivityOpenIdList(activityId int) (items []*OpenIdList, err error) {
  42. sql := `SELECT DISTINCT cr.open_id,u.user_id
  43. FROM
  44. cygx_my_schedule AS m
  45. INNER JOIN user_record AS u ON u.bind_account = m.mobile
  46. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  47. WHERE m.activity_id = ? AND u.create_platform = 4 `
  48. _, err = orm.NewOrm().Raw(sql, activityId).QueryRows(&items)
  49. return
  50. }
  51. // 获取预约活动的用户的openID测试环境
  52. func GetActivityOpenIdListByDeBug(activityId int) (items []*OpenIdList, err error) {
  53. sql := `SELECT
  54. u.open_id,
  55. u.user_id
  56. FROM
  57. cygx_my_schedule AS s
  58. INNER JOIN wx_user AS wx ON wx.user_id = s.user_id
  59. INNER JOIN user_record AS u ON u.bind_account = wx.mobile
  60. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  61. WHERE
  62. s.activity_id = ?
  63. AND u.create_platform = 1
  64. AND p.STATUS IN ( '正式', '试用', '永续' )
  65. GROUP BY
  66. u.open_id`
  67. _, err = orm.NewOrm().Raw(sql, activityId).QueryRows(&items)
  68. return
  69. }
  70. // 获取预约活动的用户的openID
  71. func GetActivitySpecialOpenIdList() (items []*OpenIdList, err error) {
  72. sql := `SELECT DISTINCT cr.open_id,u.user_id
  73. FROM
  74. cygx_user_follow_special AS m
  75. INNER JOIN user_record AS u ON u.bind_account = m.mobile
  76. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  77. WHERE u.create_platform = 4 `
  78. _, err = orm.NewOrm().Raw(sql).QueryRows(&items)
  79. return
  80. }
  81. // 获取预约活动的用户的openID测试环境
  82. func GetActivitySpecialOpenIdListByDeBug() (items []*OpenIdList, err error) {
  83. sql := `SELECT
  84. u.open_id,
  85. u.user_id
  86. FROM
  87. cygx_user_follow_special AS s
  88. INNER JOIN wx_user AS wx ON wx.user_id = s.user_id
  89. INNER JOIN user_record AS u ON u.bind_account = wx.mobile
  90. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  91. WHERE
  92. u.create_platform = 1
  93. AND p.STATUS IN ( '正式', '试用', '永续' )
  94. GROUP BY
  95. u.open_id`
  96. _, err = orm.NewOrm().Raw(sql).QueryRows(&items)
  97. return
  98. }
  99. // 获取关注作者的用户的openID
  100. func GetFollowDepartmentOpenIdList(departmentId int) (items []*OpenIdList, err error) {
  101. sql := `SELECT cr.open_id,u.user_id
  102. FROM
  103. cygx_article_department_follow AS f
  104. INNER JOIN user_record AS u ON u.bind_account = f.mobile
  105. INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
  106. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  107. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  108. WHERE
  109. f.department_id = ?
  110. AND u.create_platform = 4
  111. AND f.type = 1
  112. AND p.status IN ('正式','试用','永续')
  113. GROUP BY
  114. cr.open_id `
  115. _, err = orm.NewOrm().Raw(sql, departmentId).QueryRows(&items)
  116. return
  117. }
  118. // 获取关注作者的用户的openID测试环境
  119. func GetFollowDepartmentOpenIdListByDeBug(departmentId int) (items []*OpenIdList, err error) {
  120. sql := `SELECT
  121. u.open_id,
  122. u.user_id
  123. FROM
  124. cygx_article_department_follow AS f
  125. INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
  126. INNER JOIN user_record AS u ON u.bind_account = wx.mobile
  127. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  128. WHERE
  129. f.department_id = ?
  130. AND u.create_platform = 1
  131. AND f.type = 1
  132. AND p.STATUS IN ( '正式', '试用', '永续' )
  133. GROUP BY
  134. u.open_id`
  135. _, err = orm.NewOrm().Raw(sql, departmentId).QueryRows(&items)
  136. return
  137. }
  138. // 获取关注产业的用户的openID
  139. func GetFollowindustrialOpenIdList(industrialManagementId int) (items []*OpenIdList, err error) {
  140. sql := `SELECT cr.open_id,u.user_id
  141. FROM
  142. cygx_industry_fllow AS f
  143. INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
  144. INNER JOIN user_record AS u ON u.bind_account = wx.mobile
  145. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  146. WHERE
  147. f.industrial_management_id = ?
  148. AND u.create_platform = 4
  149. AND f.type = 1 GROUP BY cr.open_id `
  150. _, err = orm.NewOrm().Raw(sql, industrialManagementId).QueryRows(&items)
  151. return
  152. }
  153. // 获取关注产业的用户的openID 测试环境
  154. func GetFollowindustrialOpenIdListByDeBug(industrialManagementId int) (items []*OpenIdList, err error) {
  155. sql := `SELECT
  156. u.open_id,
  157. u.user_id
  158. FROM
  159. cygx_industry_fllow AS f
  160. INNER JOIN wx_user AS wx ON wx.user_id = f.user_id
  161. INNER JOIN user_record AS u ON u.bind_account = wx.mobile
  162. WHERE
  163. f.industrial_management_id = ?
  164. AND u.create_platform = 1
  165. AND u.bind_account != ""
  166. AND f.type = 1
  167. GROUP BY
  168. u.open_id`
  169. _, err = orm.NewOrm().Raw(sql, industrialManagementId).QueryRows(&items)
  170. return
  171. }
  172. // GetUserOpenidListByUserIds 根据用户id字符串集合来获取他的openid列表集合
  173. func GetUserOpenidListByUserIds(userIdStr []string) (list []*OpenIdList, err error) {
  174. if len(userIdStr) <= 0 {
  175. return
  176. }
  177. sql := `SELECT open_id,u.user_id FROM user_record WHERE user_id in (` + strings.Join(userIdStr, ",") + `) and create_platform = 1`
  178. _, err = orm.NewOrm().Raw(sql).QueryRows(&list)
  179. return
  180. }
  181. func GetAdminOpenIdByMobile(mobile string) (items []*OpenIdList, err error) {
  182. sql := `SELECT DISTINCT ur.open_id,wu.user_id FROM wx_user AS wu
  183. INNER JOIN company AS c ON c.company_id = wu.company_id
  184. INNER join user_record as ur on wu.user_id=ur.user_id
  185. WHERE ur.open_id != "" and ur.create_platform=1 AND wu.mobile=? `
  186. _, err = orm.NewOrm().Raw(sql, mobile).QueryRows(&items)
  187. return
  188. }
  189. // 根据手机号获取用户的openid查研观向小助手专用
  190. func GetUserRecordListByMobile(platform int, bindAccount string) (items []*OpenIdList, err error) {
  191. var sql string
  192. if utils.RunMode == "release" {
  193. sql = `SELECT cr.open_id FROM user_record as u
  194. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  195. WHERE create_platform=? AND bind_account IN (` + bindAccount + `)`
  196. } else {
  197. platform = 1
  198. sql = `SELECT open_id FROM user_record WHERE create_platform =? AND bind_account IN (` + bindAccount + `)`
  199. }
  200. _, err = orm.NewOrm().Raw(sql, platform).QueryRows(&items)
  201. return
  202. }
  203. // 获取单个用户openid
  204. func GetCompanyDetailByIdGroup(platform int, bindAccount string) (item *OpenIdList, err error) {
  205. o := orm.NewOrm()
  206. var sql string
  207. sql = `SELECT cr.open_id,wu.user_id FROM user_record as u
  208. INNER JOIN cygx_user_record AS cr ON cr.union_id = u.union_id
  209. INNER JOIN wx_user AS wu ON wu.mobile = u.bind_account
  210. WHERE create_platform=? AND u.bind_account = ?`
  211. err = o.Raw(sql, platform, bindAccount).QueryRow(&item)
  212. return
  213. }
  214. // 获取小助手所有的用户的openid
  215. func GetCygxUserAllOpneid() (items []*OpenIdList, err error) {
  216. o := orm.NewOrm()
  217. sql := `SELECT
  218. cr.open_id,
  219. r.user_id
  220. FROM
  221. user_record AS r
  222. INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
  223. WHERE
  224. r.create_platform = 4`
  225. _, err = o.Raw(sql).QueryRows(&items)
  226. return
  227. }
  228. func GetOpenIdArr() (items []string, err error) {
  229. sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu
  230. INNER JOIN company AS c ON c.company_id = wu.company_id
  231. INNER JOIN company_product AS d ON c.company_id=d.company_id
  232. INNER JOIN user_record AS ur ON wu.user_id=ur.user_id
  233. WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND d.status IN('正式','试用','永续')
  234. ORDER BY FIELD(c.company_id, 16) desc, ur.user_record_id asc`
  235. _, err = orm.NewOrm().Raw(sql).QueryRows(&items)
  236. return
  237. }
  238. // 获取预约活动的用户的openID测试环境
  239. func GetActivitySpecialOpenIdListMobile(condition string, pars []interface{}) (items []*OpenIdList, err error) {
  240. sql := `SELECT
  241. cr.open_id,
  242. u.user_id
  243. FROM
  244. company_report_permission AS p
  245. INNER JOIN wx_user AS u ON u.company_id = p.company_id
  246. INNER JOIN user_record AS r ON r.user_id = u.user_id
  247. INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
  248. WHERE
  249. r.create_platform = 4 AND p.STATUS IN ('正式','试用','永续') ` + condition + ` GROUP BY cr.open_id`
  250. _, err = orm.NewOrm().Raw(sql, pars).QueryRows(&items)
  251. return
  252. }
  253. // 获取所有关注了该产业用户的openid
  254. func GetCygxUserIndustryFllowOpneid(IndustrialManagementId int) (items []*OpenIdList, err error) {
  255. o := orm.NewOrm()
  256. sql := `SELECT
  257. cr.open_id,
  258. r.user_id
  259. FROM
  260. user_record AS r
  261. INNER JOIN cygx_user_record AS cr ON cr.union_id = r.union_id
  262. INNER join cygx_industry_fllow as cf on cf.user_id = r.user_id
  263. WHERE
  264. r.create_platform = 4
  265. AND cf.industrial_management_id = ?`
  266. _, err = o.Raw(sql, IndustrialManagementId).QueryRows(&items)
  267. return
  268. }
  269. // 获取所有关注了该产业用户的openid
  270. func GetCygxUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
  271. o := orm.NewOrm()
  272. sql := `
  273. SELECT
  274. cr.open_id,
  275. r.user_id
  276. FROM
  277. cygx_industrial_activity_group_management AS agm
  278. INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
  279. INNER JOIN user_record AS r
  280. INNER JOIN cygx_user_record AS cr
  281. WHERE
  282. agm.activity_id = ?
  283. AND cr.union_id = r.union_id
  284. AND r.create_platform = 4
  285. AND r.user_id = f.user_id;`
  286. _, err = o.Raw(sql, activityId).QueryRows(&items)
  287. return
  288. }
  289. // 获取所有关注了该产业永续客户的openid
  290. func GetCygxForeverUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
  291. o := orm.NewOrm()
  292. sql := `
  293. SELECT
  294. cr.open_id,
  295. r.user_id
  296. FROM
  297. cygx_industrial_activity_group_management AS agm
  298. INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
  299. INNER JOIN user_record AS r
  300. INNER JOIN cygx_user_record AS cr
  301. INNER JOIN wx_user AS wx ON wx.user_id = r.user_id
  302. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  303. WHERE
  304. agm.activity_id = ?
  305. AND cr.union_id = r.union_id
  306. AND r.create_platform = 4
  307. AND r.user_id = f.user_id
  308. AND p.status = "永续";`
  309. _, err = o.Raw(sql, activityId).QueryRows(&items)
  310. return
  311. }
  312. // 获取所有关注了该产业试用客户的openid
  313. func GetCygxTryOutUserIndustryFllowOpneidByActivityIds(activityId int) (items []*OpenIdList, err error) {
  314. o := orm.NewOrm()
  315. sql := `
  316. SELECT
  317. cr.open_id,
  318. r.user_id
  319. FROM
  320. cygx_industrial_activity_group_management AS agm
  321. INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
  322. INNER JOIN user_record AS r
  323. INNER JOIN cygx_user_record AS cr
  324. INNER JOIN wx_user AS wx ON wx.user_id = r.user_id
  325. INNER JOIN company_product AS p ON p.company_id = wx.company_id
  326. WHERE
  327. agm.activity_id = ?
  328. AND cr.union_id = r.union_id
  329. AND r.create_platform = 4
  330. AND r.user_id = f.user_id
  331. AND p.status = "试用";`
  332. _, err = o.Raw(sql, activityId).QueryRows(&items)
  333. return
  334. }
  335. // 获取所有关注了该产业用户的companyIds
  336. func GetCygxIndustryFollowCompanyIdsByActivityId(activityId int) (item *string, err error) {
  337. o := orm.NewOrm()
  338. sql := `
  339. SELECT
  340. GROUP_CONCAT( DISTINCT f.company_id SEPARATOR ',' ) AS company_ids
  341. FROM
  342. cygx_industrial_activity_group_management AS agm
  343. INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
  344. INNER JOIN user_record AS r
  345. INNER JOIN cygx_user_record AS cr
  346. WHERE
  347. agm.activity_id = ?
  348. AND r.create_platform = 4
  349. AND r.user_id = f.user_id;`
  350. err = o.Raw(sql, activityId).QueryRow(&item)
  351. return
  352. }
  353. // 获取所有关注了该产业用户的openidBy公司id
  354. func GetCygxUserIndustryFllowOpneidByActivityIdAndCompanyIds(activityId int, companyIds string) (items []*OpenIdList, err error) {
  355. o := orm.NewOrm()
  356. sql := `
  357. SELECT
  358. cr.open_id,
  359. r.user_id
  360. FROM
  361. cygx_industrial_activity_group_management AS agm
  362. INNER JOIN cygx_industry_fllow AS f ON f.industrial_management_id = agm.industrial_management_id
  363. INNER JOIN user_record AS r
  364. INNER JOIN cygx_user_record AS cr
  365. WHERE
  366. agm.activity_id = ?
  367. AND cr.union_id = r.union_id
  368. AND r.create_platform = 4
  369. AND r.user_id = f.user_id
  370. AND f.company_id IN (` + companyIds + `);`
  371. _, err = o.Raw(sql, activityId).QueryRows(&items)
  372. return
  373. }
  374. // 获取所有互动过的用户id
  375. func GetCygxInteractiveUserByActivityId(activityId int) (items []*int, err error) {
  376. o := orm.NewOrm()
  377. sql := `
  378. SELECT user_id FROM cygx_activity_signup WHERE activity_id =? AND fail_type = 0
  379. UNION ALL
  380. SELECT user_id FROM cygx_activity_appointment WHERE activity_id = ?
  381. UNION ALL
  382. SELECT user_id FROM cygx_activity_meeting_reminder WHERE activity_id = ?
  383. UNION ALL
  384. SELECT user_id FROM cygx_activity_help_ask WHERE activity_id = ? `
  385. _, err = o.Raw(sql, activityId, activityId, activityId, activityId).QueryRows(&items)
  386. return
  387. }
  388. // 获取所有用户的openid
  389. func GetCygxUserOpneidByUserIds(userIds string) (items []*OpenIdList, err error) {
  390. o := orm.NewOrm()
  391. sql := `
  392. SELECT
  393. cr.open_id,
  394. r.user_id
  395. FROM
  396. user_record AS r
  397. INNER JOIN cygx_user_record AS cr
  398. WHERE
  399. cr.union_id = r.union_id
  400. AND r.create_platform = 4
  401. AND r.user_id IN (` + userIds + `);`
  402. _, err = o.Raw(sql).QueryRows(&items)
  403. return
  404. }
  405. func GetOpenIdArrByClassifyNameSecond(classifyNameSecond string) (items []string, err error) {
  406. sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu
  407. INNER JOIN company AS c ON c.company_id = wu.company_id
  408. INNER JOIN company_product AS d ON c.company_id=d.company_id
  409. INNER JOIN user_record AS ur ON wu.user_id=ur.user_id
  410. INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
  411. INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
  412. INNER JOIN chart_permission_search_key_word_mapping AS g ON f.chart_permission_id=g.chart_permission_id
  413. WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND d.status IN('正式','试用','永续') AND e.status IN('正式','试用','永续')
  414. AND g.from='rddp'
  415. AND g.key_word=?
  416. ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC `
  417. _, err = orm.NewOrm().Raw(sql, classifyNameSecond).QueryRows(&items)
  418. return
  419. }
  420. func GetOpenIdArrByChartPermissionIds(chartPermissionIds string) (items []string, err error) {
  421. sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu
  422. INNER JOIN company AS c ON c.company_id = wu.company_id
  423. INNER JOIN company_product AS d ON c.company_id=d.company_id
  424. INNER JOIN user_record AS ur ON wu.user_id=ur.user_id
  425. INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
  426. INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
  427. WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND d.status IN('正式','试用','永续')
  428. AND f.chart_permission_id IN(` + chartPermissionIds + `)
  429. ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC `
  430. _, err = orm.NewOrm().Raw(sql).QueryRows(&items)
  431. return
  432. }
  433. func GetOpenIdArrByVarietyTag(varietyTagId int) (items []string, err error) {
  434. sql := ` SELECT DISTINCT ur.open_id FROM wx_user AS wu
  435. INNER JOIN company AS c ON c.company_id = wu.company_id
  436. INNER JOIN company_product AS d ON c.company_id=d.company_id
  437. INNER JOIN user_record AS ur ON wu.user_id=ur.user_id
  438. INNER JOIN company_report_permission AS e ON d.company_id=e.company_id
  439. INNER JOIN chart_permission AS f ON e.chart_permission_id=f.chart_permission_id
  440. INNER JOIN variety_tag AS g ON f.chart_permission_id=g.chart_permission_id
  441. WHERE ur.open_id != "" AND ur.subscribe=1 AND ur.create_platform=1 AND d.status IN('正式','试用','永续')
  442. AND g.variety_tag_id=?
  443. ORDER BY FIELD(c.company_id, 16) DESC, ur.user_record_id ASC `
  444. _, err = orm.NewOrm().Raw(sql, varietyTagId).QueryRows(&items)
  445. return
  446. }