cygx_user.go 44 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106
  1. package cygx
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "github.com/rdlucklib/rdluck_tools/paging"
  5. "hongze/hz_crm_api/models/company"
  6. "strconv"
  7. "strings"
  8. "time"
  9. )
  10. type CygxCompanyUser struct {
  11. UserId int64 `orm:"column(user_id);pk"`
  12. Mobile string `description:"手机号"`
  13. Email string `description:"邮箱"`
  14. CompanyId int `description:"公司id"`
  15. CompanyName string `description:"公司名称"`
  16. RealName string `description:"姓名"`
  17. CreatedTime string `description:"创建时间"`
  18. IsMaker int `description:"是否决策人,1:是,0:否"`
  19. IsRegister bool `description:"是否注册,true:已注册,false:未注册"`
  20. Status string `description:"客户状态"`
  21. RegisterTime string `description:"注册时间"`
  22. SellerName string `description:"销售名称"`
  23. InteractionNum int `description:"互动量"`
  24. CompanyInteractionNum int `description:"企业互动量"`
  25. Labels string `description:"标签,用英文,隔开"`
  26. ActivityLabel string `description:"活动标签,用英文,隔开"`
  27. IsShowSee bool `description:"是否展示查看"`
  28. IsShowSeeNum int `description:"是否展示查看"`
  29. HistoryNum int `description:"报告阅读"`
  30. CountNum int `description:"报告收藏"`
  31. IndustryFllowNum int `description:"产业关注"`
  32. DepartmentFollowNum int `description:"作者关注"`
  33. KeyWordNum int `description:"搜索关键词"`
  34. OnLineNum int `description:"线上互动活动"`
  35. OfficeNum int `description:"线下互动活动"`
  36. ChartNum int `description:"图表收藏数量"`
  37. TripNum int `description:"图表数量"`
  38. RoadshowVideoNum int `description:"产业视频播放量"`
  39. ActivityVideoNum int `description:"活动视频播放量"`
  40. ActivityVoiceNum int `description:"活动音频播放量"`
  41. PackageType int `description:"套餐类型,0:无,1:大套餐,2:小套餐"`
  42. TryStage int `description:"试用客户子标签:0全部、1未分类、2 推进、3 跟踪、4 预备"`
  43. Content string `description:"备注信息"`
  44. IsRemind bool `description:"是否添加互动提醒"`
  45. IsSubscribeCygx int `description:"是否关注了查研观向微信公众号: 0-未关注; 1-已关注"`
  46. }
  47. type CompanyUserListResp struct {
  48. Paging *paging.PagingItem
  49. List []*CygxCompanyUser
  50. }
  51. // 获取数量
  52. func GetCompanyListCount(userCondition, keyWord, kwywordcondition, condition, roleTypeCode string, pars []interface{}) (count int, err error) {
  53. o := orm.NewOrm()
  54. companyCondition := `SELECT a.company_id FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 `
  55. kwywordcondition = companyCondition + kwywordcondition
  56. if condition != "" {
  57. companyCondition += condition
  58. }
  59. sql := `SELECT COUNT(DISTINCT u.user_id) AS count
  60. FROM
  61. wx_user AS u
  62. INNER JOIN company AS c ON c.company_id = u.company_id
  63. INNER JOIN company_product AS cp ON cp.company_id = c.company_id
  64. INNER JOIN admin AS m ON m.admin_id = cp.seller_id
  65. INNER JOIN user_seller_relation AS sr ON sr.user_id = u.user_id
  66. WHERE u.company_id IN (` + companyCondition + `)`
  67. if userCondition != "" {
  68. sql += userCondition
  69. }
  70. if keyWord != "" {
  71. if strings.Count(roleTypeCode, "admin") > 0 {
  72. sql += ` OR ( cp.product_id = 2 AND cp.status IN ('正式','试用','冻结') AND ( u.real_name LIKE '%` + keyWord + `%' OR u.mobile LIKE '%` + keyWord + `%' OR u.email LIKE '%` + keyWord + `%' ) ` + userCondition + ` )`
  73. } else {
  74. sql += ` OR ( cp.product_id = 2 AND u.company_id IN (` + kwywordcondition + `) AND cp.status IN ('正式','试用','冻结') AND ( u.real_name LIKE '%` + keyWord + `%' OR u.mobile LIKE '%` + keyWord + `%' OR u.email LIKE '%` + keyWord + `%' ) ` + userCondition + ` )`
  75. }
  76. }
  77. err = o.Raw(sql, pars).QueryRow(&count)
  78. return
  79. }
  80. // 列表
  81. func GetCygxCompanyUserList(userCondition, keyWord, kwywordcondition, condition, roleTypeCode, sqlOrder string, pars []interface{}, startSize, pageSize int) (items []*CygxCompanyUser, err error) {
  82. o := orm.NewOrm()
  83. companyCondition := `SELECT a.company_id FROM company AS a INNER JOIN company_product AS b ON a.company_id=b.company_id WHERE a.enabled=1 `
  84. kwywordcondition = companyCondition + kwywordcondition
  85. if condition != "" {
  86. companyCondition += condition
  87. }
  88. sql := `SELECT
  89. u.user_id,
  90. u.mobile,
  91. u.email,
  92. date_format(u.register_time,'%Y-%m-%d') as register_time,
  93. u.real_name,
  94. u.is_register,
  95. u.is_maker,
  96. u.user_label as labels,
  97. u.cygx_subscribe as is_subscribe_cygx,
  98. c.company_name,
  99. c.company_id,
  100. c.interaction_num as company_interaction_num,
  101. cp.status,
  102. cp.try_stage,
  103. cp.package_type,
  104. m.real_name as seller_name ,
  105. ( SELECT COUNT( 1 ) FROM user_remarks AS rm WHERE rm.user_id = u.user_id ) AS is_show_see_num,
  106. ( SELECT COUNT( 1 ) FROM cygx_user_remind AS rm WHERE rm.user_id = u.user_id ) AS is_remind
  107. FROM
  108. wx_user AS u
  109. INNER JOIN company AS c ON c.company_id = u.company_id
  110. INNER JOIN company_product AS cp ON cp.company_id = c.company_id
  111. INNER JOIN admin AS m ON m.admin_id = cp.seller_id
  112. INNER JOIN user_seller_relation AS sr ON sr.user_id = u.user_id
  113. WHERE u.company_id IN (` + companyCondition + `)`
  114. if userCondition != "" {
  115. sql += userCondition
  116. }
  117. if keyWord != "" {
  118. if strings.Count(roleTypeCode, "admin") > 0 {
  119. sql += ` OR ( cp.product_id = 2 AND cp.status IN ('正式','试用','冻结') AND ( u.real_name LIKE '%` + keyWord + `%' OR u.mobile LIKE '%` + keyWord + `%' OR u.email LIKE '%` + keyWord + `%' ) ` + userCondition + ` )`
  120. } else {
  121. sql += ` OR ( cp.product_id = 2 AND u.company_id IN (` + kwywordcondition + `) AND cp.status IN ('正式','试用','冻结') AND ( u.real_name LIKE '%` + keyWord + `%' OR u.mobile LIKE '%` + keyWord + `%' OR u.email LIKE '%` + keyWord + `%' ) ` + userCondition + ` )`
  122. }
  123. }
  124. if sqlOrder != "" {
  125. sql += sqlOrder + ` LIMIT ?,? `
  126. } else {
  127. sql += ` GROUP by u.user_id ORDER BY c.created_time DESC, u.register_time DESC LIMIT ?,? `
  128. }
  129. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
  130. return
  131. }
  132. // 对于上面的SQL的拆分优化查询速度
  133. func GetCygxCompanyUserListSplit(userIds string) (items []*CygxCompanyUser, err error) {
  134. o := orm.NewOrm()
  135. sql := `SELECT
  136. u.user_id,
  137. ( 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,
  138. ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.user_id = u.user_id ) AS count_num,
  139. ( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h WHERE h.user_id = u.user_id ) AS chart_num,
  140. ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id AND h.type = 1 ) AS industry_fllow_num,
  141. ( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = u.user_id AND h.type = 1 ) AS department_follow_num,
  142. ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = u.user_id ) AS key_word_num,
  143. ( SELECT COUNT(DISTINCT h.activity_id) FROM cygx_activity_signup_detail 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
  144. AND t.activity_type = 1 AND h.do_fail_type = 0 ) AS on_line_num,
  145. ( SELECT COUNT( 1 ) FROM cygx_activity_signup_detail 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
  146. AND t.activity_type = 0 AND h.do_fail_type = 0 ) AS office_num,
  147. ( SELECT COUNT( 1 ) FROM cygx_activity_special_meeting_detail AS h INNER JOIN cygx_activity_special AS a ON a.activity_id = h.activity_id WHERE h.mobile = u.mobile AND a.publish_status = 1 AND a.activity_time_end < NOW()) AS trip_num,
  148. ( SELECT COUNT( 1 ) FROM cygx_micro_roadshow_video_history AS h INNER JOIN cygx_micro_roadshow_video as v ON v.video_id = h.video_id WHERE h.mobile = u.mobile ) AS roadshow_video_num,
  149. ( SELECT COUNT( 1 ) FROM cygx_activity_video_history AS h INNER JOIN cygx_activity_video as v ON v.video_id = h.video_id WHERE h.mobile = u.mobile ) AS activity_video_num,
  150. ( SELECT COUNT( 1 ) FROM cygx_activity_voice_history AS h INNER JOIN cygx_activity_voice as v ON v.activity_voice_id = h.activity_voice_id WHERE h.mobile = u.mobile ) AS activity_voice_num,
  151. (
  152. SELECT
  153. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR ',' )
  154. FROM
  155. cygx_industrial_management AS man
  156. WHERE
  157. 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 )
  158. ) AS labels,
  159. (
  160. SELECT
  161. GROUP_CONCAT( DISTINCT man.label SEPARATOR ',' )
  162. FROM
  163. cygx_activity AS man
  164. WHERE
  165. man.activity_id IN ( SELECT activity_id FROM cygx_activity_signup AS f WHERE f.user_id = u.user_id AND label != '')
  166. ) AS activity_label
  167. FROM
  168. wx_user AS u WHERE u.user_id IN( ` + userIds + `) `
  169. _, err = o.Raw(sql).QueryRows(&items)
  170. return
  171. }
  172. type UserRemarks struct {
  173. Id int `orm:"column(id);pk"`
  174. UserId int `description:"用户ID"`
  175. AdminId int `description:"销售/管理员ID"`
  176. Content string `description:"备注信息"`
  177. CreateTime time.Time `description:"创建时间"`
  178. }
  179. type UserRemarksResp struct {
  180. Id int `orm:"column(id);pk"`
  181. UserId int `description:"用户ID"`
  182. AdminId int `description:"销售/管理员ID"`
  183. Content string `description:"备注信息"`
  184. CreateTime string `description:"创建时间"`
  185. }
  186. type UserRemarAddReq struct {
  187. UserId int `description:"用户ID"`
  188. Content string `description:"备注信息"`
  189. }
  190. // 添加UserRemarks
  191. func AddUserRemarks(item *UserRemarks) (err error) {
  192. o := orm.NewOrm()
  193. _, err = o.Insert(item)
  194. return
  195. }
  196. // 获取备注列表
  197. func GetUserRemarksList(userId int) (items []*UserRemarksResp, err error) {
  198. o := orm.NewOrm()
  199. sql := ` SELECT * FROM user_remarks WHERE user_id=? ORDER BY create_time DESC`
  200. _, err = o.Raw(sql, userId).QueryRows(&items)
  201. return
  202. }
  203. // 获取备注列表(多用户)
  204. func GetUserRemarksListByUserIds(uids string) (items []*UserRemarksResp, err error) {
  205. o := orm.NewOrm()
  206. sql := ` SELECT * FROM user_remarks WHERE user_id IN(` + uids + `) ORDER BY create_time DESC`
  207. _, err = o.Raw(sql).QueryRows(&items)
  208. return
  209. }
  210. type UserRemarksListResp struct {
  211. List []*UserRemarksResp
  212. }
  213. // 切换列表
  214. type CygxUserTableRep struct {
  215. PermissionName string `description:"名称"`
  216. Source int `description:"类型"`
  217. TotalNum int `description:"总数"`
  218. }
  219. type CygxUserTableListRep struct {
  220. List []*CygxUserTableRep
  221. UserName string `description:"用户名称"`
  222. ComapnyName string `description:"公司名称"`
  223. Mobile string `description:"手机号"`
  224. }
  225. // @Param TryStage query int false "试用客户子标签:0全部、1未分类、2 推进、3 跟踪、4 预备"
  226. // 状态搜索列表
  227. type CygxTryStageRep struct {
  228. Name string `description:"试用客户子标签"`
  229. TryStage int `description:"试用客户子标签:0全部、1未分类、2 推进、3 跟踪、4 预备"`
  230. }
  231. type CygxUserTableStatusRep struct {
  232. List []*CygxTryStageRep
  233. Name string `description:"状态"`
  234. TryStage string `description:"状态"`
  235. }
  236. type CygxUserTableStatusListRep struct {
  237. List []*CygxUserTableStatusRep
  238. }
  239. type UserInteractionListResp struct {
  240. Paging *paging.PagingItem
  241. List []*UserInteraction
  242. MeetingNum int `description:"已到会"`
  243. NoMeetingNum int `description:"未到会"`
  244. }
  245. type UserInteraction struct {
  246. ArticleId int `description:"文章id"`
  247. ActivityId int `description:"活动ID"`
  248. ArticleType int `description:"文章类型 1:查研观向, 2:策略平台"`
  249. ArticleIdMd5 string `description:"文章MD5id"`
  250. Title string `description:"标题"`
  251. MediaTitle string `description:"音视频标题"`
  252. CategoryName string `description:"行业2"`
  253. PublishDate string `description:"发布时间"`
  254. CreateTime string `description:"创建时间"`
  255. CreateDate string `orm:"column(create_time);" description:"创建时间"`
  256. PermissionName string `orm:"column(chart_permission_name);" description:"行业"`
  257. ChartPermissionId int `description:"行业id"`
  258. IndustryName string `description:"产业名称"`
  259. SubjectNameStr string `description:"关联标的"`
  260. MatchTypeName string `description:"匹配类型"`
  261. StopTime string `description:"阅读停留时间"`
  262. NickName string `description:"作者昵称"`
  263. DepartmentId int `description:"作者ID "`
  264. KeyWord string `description:"关键词"`
  265. Source int `description:"关键词来源;1:纪要、2:图表、3:纪要/图表、4:产业资源包、5:报告、6:活动、7:搜索关键词、8:专项产业调研、9音视频播放"`
  266. ActivityName string `description:"活动名称"`
  267. ActivityType int `description:"1 线上,0、2线下"`
  268. IsMeeting int `description:"是否到会, 1是,0否"`
  269. ActivityTypeName string `description:"活动类型名称"`
  270. Label string `description:"标签"`
  271. ActivityTime string `description:"活动时间"`
  272. ActivityTimeText string `description:"活动时间"`
  273. FirstMeetingTime string `description:"首次入会时间"`
  274. LastMeetingTime string `description:"最后退出时间"`
  275. Duration string `description:"参会时长"`
  276. MeetingTypeStr string `description:"参会方式"`
  277. MeetingAuthentication string `description:"参会权鉴"`
  278. MeetingStatusStr string `description:"参会状态"`
  279. SignupType string `description:"参会方式 "`
  280. InteractionType string `description:"互动类型 "`
  281. ReminderNum int `description:"预约外呼数量 "`
  282. TitleEn string `description:"英文标题 "`
  283. PtagName string `description:"父类名称"`
  284. CtagName string `description:"子类名称"`
  285. PtagNameTwo string `description:"父类名称"`
  286. CtagNameTwo string `description:"子类名称"`
  287. CtagNamePc string `description:"Pc端所有的分类名称"`
  288. BodyHtml string `orm:"column(cover)";description:"图片链接"`
  289. HttpUrl string `orm:"column(iframe)";description:"文章链接跳转地址"`
  290. ChartId int `description:"图表id"`
  291. RealName string `description:"姓名"`
  292. CompanyName string `description:"公司名称"`
  293. SellerName string `description:"所属销售"`
  294. Mobile string `description:"手机号"`
  295. UserId int `description:"用户ID"`
  296. City string `description:"城市"`
  297. FileType string `description:"文件类型"`
  298. ListChart []*HomeChartListResp
  299. Paging *paging.PagingItem
  300. IsEnd bool `description:"是否最后一页,配合前端分页添加的参数"`
  301. CurrentIndex int `description:"当前页页码,配合前端分页添加的参数"`
  302. RegisterPlatform int `description:"来源 1小程序,2:网页 ,3:策略平台"`
  303. SourcePlatform string `description:"来源 'MOBILE:移动端小程序','PC:PC端小程序','CELUE:上海策略平台','WEB:查研观向网页版'"`
  304. }
  305. // 查研观向图表
  306. type CygxChartResp struct {
  307. Title string `description:"标题"`
  308. PublishDate string `description:"发布时间"`
  309. CreateTime string `description:"创建时间"`
  310. TitleEn string `description:"英文标题 "`
  311. PtagName string `description:"父类名称"`
  312. CtagName string `description:"子类名称"`
  313. PtagNameTwo string `description:"父类名称"`
  314. CtagNameTwo string `description:"子类名称"`
  315. CtagNamePc string `description:"Pc端所有的分类名称"`
  316. BodyHtml string `orm:"column(cover)";description:"图片链接"`
  317. HttpUrl string `orm:"column(iframe)";description:"文章链接跳转地址"`
  318. ChartId int `description:"图表id"`
  319. RealyName string `description:"姓名"`
  320. Mobile string `description:"手机号"`
  321. }
  322. // 获取阅读记录数量
  323. func GetCygxArticleHistoryCount(mobile, email, condition string) (count int, err error) {
  324. o := orm.NewOrm()
  325. sqlCount := ` SELECT COUNT(1) AS count FROM cygx_article_history_record_all as r INNER JOIN cygx_article as art ON art.article_id = r.article_id WHERE r.mobile = ? AND is_del = 0 ` + condition + ` OR ( email = ? AND email <> '' AND is_del = 0 ` + condition + `) `
  326. err = o.Raw(sqlCount, mobile, email).QueryRow(&count)
  327. return
  328. }
  329. // 阅读记录列表
  330. func GetCygxArticleHistoryRecordByUser(mobile, email, condition string, startSize, pageSize int) (items []*UserInteraction, err error) {
  331. o := orm.NewOrm()
  332. sql := ` SELECT
  333. art.title,
  334. art.article_id,
  335. art.article_id_md5,
  336. art.publish_date,
  337. art.category_name,
  338. re.chart_permission_name,
  339. re.chart_permission_id,
  340. r.create_time,
  341. r.stop_time,
  342. r.source as source_platform,
  343. (
  344. SELECT
  345. GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' )
  346. FROM
  347. cygx_industrial_subject AS s
  348. WHERE
  349. s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id )
  350. ) AS subject_name_str,
  351. (
  352. SELECT
  353. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' )
  354. FROM
  355. cygx_industrial_management AS man
  356. WHERE
  357. 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 )
  358. ) AS industry_name
  359. FROM
  360. cygx_article_history_record_all AS r
  361. INNER JOIN cygx_article AS art ON art.article_id = r.article_id
  362. LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
  363. LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id
  364. WHERE
  365. r.mobile = ? AND is_del = 0 ` + condition + ` OR ( email = ? AND email <>'' AND is_del = 0 ` + condition + ` )
  366. GROUP BY r.id ORDER BY r.create_time DESC `
  367. if startSize > 0 || pageSize > 0 {
  368. sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
  369. }
  370. _, err = o.Raw(sql, mobile, email).QueryRows(&items)
  371. return
  372. }
  373. // 收藏列表数量
  374. func GetCygxArticleCollectCount(uid int) (count int, err error) {
  375. o := orm.NewOrm()
  376. sqlCount := ` SELECT COUNT(1) AS count FROM cygx_article_collect as r INNER JOIN cygx_article AS art ON art.article_id = r.article_id WHERE user_id = ? `
  377. err = o.Raw(sqlCount, uid).QueryRow(&count)
  378. return
  379. }
  380. // 收藏列表
  381. func GetCygxArticleCollectByUser(uid, startSize, pageSize int, condition string) (items []*UserInteraction, err error) {
  382. o := orm.NewOrm()
  383. sql := ` SELECT
  384. art.title,
  385. art.article_id,
  386. art.article_id_md5,
  387. art.publish_date,
  388. re.chart_permission_name,
  389. re.chart_permission_id,
  390. r.create_time,
  391. re.match_type_name,
  392. (
  393. SELECT
  394. GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' )
  395. FROM
  396. cygx_industrial_subject AS s
  397. WHERE
  398. s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id )
  399. ) AS subject_name_str,
  400. (
  401. SELECT
  402. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' )
  403. FROM
  404. cygx_industrial_management AS man
  405. WHERE
  406. 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 )
  407. ) AS industry_name
  408. FROM
  409. cygx_article_collect AS r
  410. INNER JOIN cygx_article AS art ON art.article_id = r.article_id
  411. LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
  412. LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id
  413. WHERE
  414. user_id = ? ` + condition + ` GROUP BY r.article_id
  415. ORDER BY r.create_time DESC `
  416. if startSize > 0 || pageSize > 0 {
  417. sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
  418. }
  419. _, err = o.Raw(sql, uid).QueryRows(&items)
  420. return
  421. }
  422. // 关注作者数量
  423. func GetCygArticleDepartmentFollowCount(uid int) (count int, err error) {
  424. o := orm.NewOrm()
  425. sqlCount := ` SELECT COUNT(1) AS count FROM cygx_article_department_follow WHERE user_id = ? AND type = 1 `
  426. err = o.Raw(sqlCount, uid).QueryRow(&count)
  427. return
  428. }
  429. // 关注作者列表
  430. func GetCygArticleDepartmentFollowByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) {
  431. o := orm.NewOrm()
  432. sql := `SELECT
  433. m.department_id,
  434. m.nick_name,
  435. f.modify_time as create_time,
  436. m.department_id,
  437. i.industry_name,
  438. a.title,
  439. a.article_id,
  440. ( SELECT GROUP_CONCAT( DISTINCT i.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS i WHERE i.industrial_management_id = mg.industrial_management_id ) AS industry_name,
  441. ( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_management_id = i.industrial_management_id ) AS subject_name_str
  442. FROM
  443. cygx_article_department_follow AS f
  444. INNER JOIN cygx_article_department AS m ON m.department_id = f.department_id
  445. LEFT JOIN cygx_article AS a ON a.department_id = m.department_id
  446. LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
  447. LEFT JOIN cygx_industrial_management AS i ON i.industrial_management_id = mg.industrial_management_id
  448. WHERE user_id = ? AND f.type = 1 GROUP BY m.department_id
  449. ORDER BY f.create_time DESC LIMIT ?,? `
  450. _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items)
  451. return
  452. }
  453. // 用户搜索关键词统计
  454. func GetCygxSearchKeyWordCount(uid int) (count int, err error) {
  455. o := orm.NewOrm()
  456. sqlCount := ` SELECT COUNT(1) AS count FROM cygx_search_key_word WHERE user_id = ? `
  457. err = o.Raw(sqlCount, uid).QueryRow(&count)
  458. return
  459. }
  460. // 用户搜索关键词列表
  461. func GetCygxSearchKeyWordByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) {
  462. o := orm.NewOrm()
  463. sql := `SELECT
  464. key_word,create_time
  465. FROM
  466. cygx_search_key_word as k
  467. WHERE user_id = ?
  468. ORDER BY k.create_time DESC LIMIT ?,? `
  469. _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items)
  470. return
  471. }
  472. // 用户专项产业调研统计
  473. func GetCygxActivitySpecialTripCount(condition string, pars []interface{}) (count int, err error) {
  474. o := orm.NewOrm()
  475. sqlCount := `SELECT
  476. COUNT( 1 )
  477. FROM
  478. cygx_activity_special_meeting_detail AS h
  479. INNER JOIN cygx_activity_special AS a ON a.activity_id = h.activity_id
  480. INNER JOIN wx_user AS u ON h.mobile = u.mobile
  481. WHERE
  482. 1 = 1
  483. AND a.publish_status = 1 AND a.activity_time_end < NOW() ` + condition
  484. err = o.Raw(sqlCount, pars).QueryRow(&count)
  485. return
  486. }
  487. // 用户专项产业调研列表
  488. func GetCygxActivitySpecialTripByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) {
  489. o := orm.NewOrm()
  490. sql := `SELECT
  491. a.research_theme AS activity_name,
  492. a.chart_permission_name,
  493. a.activity_time_text_by_day AS activity_time_text,
  494. a.city,
  495. a.activity_id,
  496. a.special_type AS activity_type,
  497. h.is_meeting
  498. FROM
  499. cygx_activity_special_meeting_detail AS h
  500. INNER JOIN cygx_activity_special AS a ON a.activity_id = h.activity_id
  501. INNER JOIN wx_user AS u ON h.mobile = u.mobile
  502. WHERE
  503. 1 = 1
  504. AND a.publish_status = 1 AND a.activity_time_end < NOW()
  505. AND u.user_id = ?
  506. ORDER BY a.activity_time DESC LIMIT ?,? `
  507. _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items)
  508. return
  509. }
  510. // 用户音视频浏览统计
  511. func GetCygxRoadshowCount(uid int) (count int, err error) {
  512. o := orm.NewOrm()
  513. sqlCount := `SELECT
  514. COUNT( 1 ) total
  515. FROM
  516. (
  517. SELECT
  518. ""
  519. FROM
  520. cygx_micro_roadshow_video_history AS r
  521. INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id
  522. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  523. WHERE
  524. u.user_id = ? UNION ALL
  525. SELECT
  526. ""
  527. FROM
  528. cygx_activity_video_history AS r
  529. INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id
  530. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  531. WHERE
  532. u.user_id = ? UNION ALL
  533. SELECT
  534. ""
  535. FROM
  536. cygx_activity_voice_history AS r
  537. INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id
  538. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  539. WHERE
  540. u.user_id = ?
  541. )z `
  542. err = o.Raw(sqlCount, uid, uid, uid).QueryRow(&count)
  543. return
  544. }
  545. // 用户专项产业调研列表
  546. func GetCygxRoadshowByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) {
  547. o := orm.NewOrm()
  548. sql := `SELECT
  549. v.video_name AS media_title,
  550. r.create_time,
  551. "逻辑解析" AS file_type
  552. FROM
  553. cygx_micro_roadshow_video_history AS r
  554. INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id
  555. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  556. WHERE
  557. u.user_id = ? UNION ALL
  558. SELECT
  559. v.video_name AS media_title,
  560. r.create_time,
  561. "路演回放" AS file_type
  562. FROM
  563. cygx_activity_video_history AS r
  564. INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id
  565. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  566. WHERE
  567. u.user_id = ? UNION ALL
  568. SELECT
  569. v.voice_name AS media_title,
  570. r.create_time,
  571. "路演回放" AS file_type
  572. FROM
  573. cygx_activity_voice_history AS r
  574. INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id
  575. INNER JOIN wx_user AS u ON r.mobile = u.mobile
  576. WHERE
  577. u.user_id = ?
  578. ORDER BY create_time DESC LIMIT ?,? `
  579. _, err = o.Raw(sql, uid, uid, uid, startSize, pageSize).QueryRows(&items)
  580. return
  581. }
  582. // 线上已到会列表
  583. func GetAttendanceDetailListByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) {
  584. o := orm.NewOrm()
  585. sql := `SELECT
  586. s.signup_type,
  587. a.activity_name,
  588. a.chart_permission_name,
  589. a.activity_type_name,
  590. a.label,
  591. a.activity_time,
  592. d.first_meeting_time,
  593. d.last_meeting_time,
  594. d.meeting_type_str,
  595. d.meeting_authentication,
  596. d.meeting_status_str,
  597. d.duration
  598. FROM
  599. cygx_activity_signup AS s
  600. INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
  601. INNER JOIN cygx_activity_attendance_detail AS d ON d.activity_id = s.activity_id
  602. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  603. WHERE
  604. 1 = 1
  605. AND s.do_fail_type = 0
  606. AND s.is_cancel = 0
  607. AND s.is_meeting = ?
  608. AND s.mobile = ?
  609. AND a.is_submit_meeting = 1
  610. AND t.activity_type = 1
  611. GROUP BY
  612. a.activity_id
  613. ORDER BY
  614. a.activity_time DESC
  615. LIMIT ?,?`
  616. _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item)
  617. return
  618. }
  619. // 线上未来到会列表
  620. func GetAttendanceDetailListNoMeetingByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) {
  621. o := orm.NewOrm()
  622. sql := `SELECT
  623. s.signup_type,
  624. a.activity_name,
  625. a.chart_permission_name,
  626. a.activity_type_name,
  627. a.label,
  628. a.activity_time,
  629. ( SELECT COUNT( 1 ) FROM cygx_activity_meeting_reminder AS rm WHERE rm.activity_id = s.activity_id AND rm.mobile = s.mobile ) AS reminder_num
  630. FROM
  631. cygx_activity_signup AS s
  632. INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
  633. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  634. WHERE
  635. 1 = 1
  636. AND s.do_fail_type = 0
  637. AND s.is_cancel = 0
  638. AND s.is_meeting = ?
  639. AND s.mobile = ?
  640. AND a.is_submit_meeting = 1
  641. AND t.activity_type = 1
  642. GROUP BY
  643. a.activity_id
  644. ORDER BY
  645. a.activity_time DESC
  646. LIMIT ?,?`
  647. _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item)
  648. return
  649. }
  650. // 用户参会统计
  651. func GetAttendanceDetaiCount(mobile string, meetType, isMeeting int) (count int, err error) {
  652. o := orm.NewOrm()
  653. sqlCount := ` SELECT
  654. COUNT( DISTINCT a.activity_id ) as count
  655. FROM
  656. cygx_activity_signup AS s
  657. INNER JOIN cygx_activity as a ON a.activity_id = s.activity_id
  658. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  659. WHERE
  660. mobile = ?
  661. AND is_meeting =?
  662. AND s.do_fail_type = 0
  663. AND a.is_submit_meeting = 1 `
  664. if meetType == 1 {
  665. sqlCount += ` AND t.activity_type = 1 `
  666. } else {
  667. sqlCount += ` AND t.activity_type = 0 `
  668. }
  669. err = o.Raw(sqlCount, mobile, isMeeting).QueryRow(&count)
  670. return
  671. }
  672. // 线下已到会列表
  673. func GetAttendanceOfflineListByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) {
  674. o := orm.NewOrm()
  675. sql := `SELECT
  676. a.activity_name,
  677. a.chart_permission_name,
  678. a.activity_type_name,
  679. a.label,
  680. a.activity_time
  681. FROM
  682. cygx_activity_signup AS s
  683. INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
  684. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  685. WHERE
  686. 1 = 1
  687. AND s.do_fail_type = 0
  688. AND s.is_cancel = 0
  689. AND s.is_meeting = ?
  690. AND s.mobile = ?
  691. AND a.is_submit_meeting = 1
  692. AND t.activity_type = 0
  693. GROUP BY
  694. a.activity_id
  695. ORDER BY
  696. a.activity_time DESC
  697. LIMIT ?,?`
  698. _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item)
  699. return
  700. }
  701. // 获取用户参会记录数量
  702. func GetActivityMeetByUserCount(condition string) (count int, err error) {
  703. o := orm.NewOrm()
  704. sqlCount := ` SELECT
  705. COUNT( DISTINCT a.activity_id ) as count
  706. FROM
  707. cygx_activity_signup_detail AS s
  708. INNER JOIN cygx_activity as a ON a.activity_id = s.activity_id
  709. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  710. WHERE
  711. 1=1
  712. AND s.do_fail_type = 0 `
  713. if condition != "" {
  714. sqlCount += condition
  715. }
  716. err = o.Raw(sqlCount).QueryRow(&count)
  717. return
  718. }
  719. // 获取用户参会记录
  720. func GetActivityMeetByUser(condition string, startSize, pageSize int) (item []*UserInteraction, err error) {
  721. o := orm.NewOrm()
  722. sql := `SELECT
  723. t.activity_type,
  724. s.signup_type,
  725. s.is_meeting,
  726. a.activity_name,
  727. a.chart_permission_name,
  728. a.activity_type_name,
  729. a.label,
  730. a.activity_time,
  731. s.first_meeting_time,
  732. s.last_meeting_time,
  733. s.meeting_type_str,
  734. s.meeting_authentication,
  735. s.meeting_status_str,
  736. s.duration
  737. FROM
  738. cygx_activity_signup_detail AS s
  739. INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id
  740. INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id
  741. WHERE
  742. 1 = 1
  743. AND s.do_fail_type = 0 ` + condition + `
  744. GROUP BY
  745. s.activity_id,s.outbound_mobile
  746. ORDER BY
  747. a.activity_time DESC `
  748. if startSize > 0 || pageSize > 0 {
  749. sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
  750. }
  751. _, err = o.Raw(sql).QueryRows(&item)
  752. return
  753. }
  754. // 获取用户参会记录
  755. func GetActivitySpecialMeetByUser(condition string, startSize, pageSize int) (item []*UserInteraction, err error) {
  756. o := orm.NewOrm()
  757. sql := `SELECT
  758. a.activity_id,
  759. a.research_theme AS activity_name,
  760. a.chart_permission_name,
  761. special_type AS activity_type,
  762. a.activity_time,
  763. a.activity_time_text_by_day AS activity_time_text
  764. FROM
  765. cygx_activity_special_meeting_detail AS s
  766. INNER JOIN cygx_activity_special AS a ON a.activity_id = s.activity_id
  767. WHERE
  768. 1 = 1 ` + condition + `
  769. GROUP BY
  770. s.activity_id,
  771. s.mobile
  772. ORDER BY
  773. a.activity_time DESC `
  774. if startSize > 0 || pageSize > 0 {
  775. sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize)
  776. }
  777. _, err = o.Raw(sql).QueryRows(&item)
  778. return
  779. }
  780. type GetUserInteractionTableCountResp struct {
  781. HistoryNum int `description:"报告阅读"`
  782. CountNum int `description:"报告收藏"`
  783. IndustryFllowNum int `description:"产业关注"`
  784. DepartmentFollowNum int `description:"作者关注"`
  785. KeyWordNum int `description:"搜索关键词"`
  786. OnLineNum int `description:"线上互动活动"`
  787. OfficeNum int `description:"线下互动活动"`
  788. ActivityNum int `description:"活动数量"`
  789. ChartNum int `description:"图表数量"`
  790. TripNum int `description:"图表数量"`
  791. RoadshowVideoNum int `description:"产业视频播放量"`
  792. ActivityVideoNum int `description:"活动视频播放量"`
  793. ActivityVoiceNum int `description:"活动音频播放量"`
  794. }
  795. // 用户互动总数统计
  796. func GetUserInteractionTableCount(uid int) (item *GetUserInteractionTableCountResp, err error) {
  797. o := orm.NewOrm()
  798. sql := `SELECT
  799. ( 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 OR ( h.email = u.email AND h.email <>'' AND h.is_del = 0) ) AS history_num,
  800. ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.user_id = u.user_id AND h.article_id > 0 ) AS count_num,
  801. ( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h INNER JOIN cygx_chart_all AS a ON h.chart_id = a.chart_id WHERE h.user_id = u.user_id ) AS chart_num,
  802. ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id AND h.type = 1 ) AS industry_fllow_num,
  803. ( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = u.user_id AND h.type = 1 ) AS department_follow_num,
  804. ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = u.user_id ) AS key_word_num,
  805. ( SELECT COUNT( DISTINCT a.activity_id ) as count FROM cygx_activity_signup_detail 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
  806. AND t.activity_type=1 AND h.do_fail_type = 0 ) AS on_line_num,
  807. ( SELECT COUNT( DISTINCT a.activity_id ) as count FROM cygx_activity_signup_detail 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
  808. AND t.activity_type = 0 AND h.do_fail_type = 0 ) AS office_num,
  809. ( SELECT COUNT( 1 ) FROM cygx_activity_special_meeting_detail AS h INNER JOIN cygx_activity_special AS a ON a.activity_id = h.activity_id WHERE h.mobile = u.mobile AND a.publish_status = 1 AND a.activity_time_end < NOW()) AS trip_num,
  810. ( SELECT COUNT( 1 ) FROM cygx_micro_roadshow_video_history AS h INNER JOIN cygx_micro_roadshow_video as v ON v.video_id = h.video_id WHERE h.mobile = u.mobile ) AS roadshow_video_num,
  811. ( SELECT COUNT( 1 ) FROM cygx_activity_video_history AS h INNER JOIN cygx_activity_video as v ON v.video_id = h.video_id WHERE h.mobile = u.mobile ) AS activity_video_num,
  812. ( SELECT COUNT( 1 ) FROM cygx_activity_voice_history AS h INNER JOIN cygx_activity_voice as v ON v.activity_id = h.activity_id WHERE h.mobile = u.mobile ) AS activity_voice_num
  813. FROM
  814. wx_user as u
  815. WHERE
  816. u.user_id = ?`
  817. err = o.Raw(sql, uid).QueryRow(&item)
  818. return
  819. }
  820. // 根据时间获取用户互动总数统计
  821. func GetUserInteractionTableCountBytime(uid int, startDate, endDate string) (item *GetUserInteractionTableCountResp, err error) {
  822. o := orm.NewOrm()
  823. sql := `SELECT
  824. ( 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 create_time BETWEEN '` + startDate + `'AND '` + endDate + `' OR ( h.email = u.email AND h.email <>'' AND h.is_del = 0 AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) ) AS history_num,
  825. ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.user_id = u.user_id AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS count_num,
  826. ( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h WHERE h.user_id = u.user_id AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS chart_num,
  827. ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id AND h.type = 1 AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS industry_fllow_num,
  828. ( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = u.user_id AND h.type = 1 AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS department_follow_num,
  829. ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = u.user_id AND create_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS key_word_num,
  830. ( SELECT COUNT( DISTINCT a.activity_id ) as count 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
  831. AND a.is_submit_meeting = 1 AND t.activity_type=1 AND h.do_fail_type = 0 AND a.activity_time BETWEEN '` + startDate + `'AND '` + endDate + `' ) AS on_line_num,
  832. ( SELECT COUNT( DISTINCT a.activity_id ) as count 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
  833. AND a.is_submit_meeting = 1 AND t.activity_type = 0 AND h.do_fail_type = 0 AND a.activity_time BETWEEN '` + startDate + `'AND '` + endDate + `') AS office_num
  834. FROM
  835. wx_user as u
  836. WHERE
  837. u.user_id = ?`
  838. err = o.Raw(sql, uid).QueryRow(&item)
  839. return
  840. }
  841. type ArticlePvCountResp struct {
  842. ArticleId int `description:"文章ID"`
  843. Pv int `description:"Pv"`
  844. }
  845. // 阅读记录
  846. func GetArticlePvCount(mobile, email, dateTime string) (item []*ArticlePvCountResp, err error) {
  847. o := orm.NewOrm()
  848. sql := `SELECT
  849. article_id,
  850. COUNT(article_id) as pv
  851. FROM
  852. cygx_article_history_record_all as h
  853. WHERE
  854. mobile = ?
  855. AND create_time >= ?
  856. AND is_del = 0
  857. OR ( email = ? AND email <>'' AND create_time >= ? AND is_del = 0 )
  858. GROUP BY article_id `
  859. _, err = o.Raw(sql, mobile, dateTime, email, dateTime).QueryRows(&item)
  860. return
  861. }
  862. type ArticleIndustryNameResp struct {
  863. ArticleId int `description:"文章ID"`
  864. IndustryName string `description:"产业名称"`
  865. SubjectName string `description:"标的名称"`
  866. }
  867. // 产业名称
  868. func GetArticleGroupyIndustryName(articleIds string) (item []*ArticleIndustryNameResp, err error) {
  869. o := orm.NewOrm()
  870. sql := `SELECT
  871. art.article_id,
  872. (
  873. SELECT
  874. GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' )
  875. FROM
  876. cygx_industrial_subject AS s
  877. WHERE
  878. s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id )
  879. ) AS subject_name,
  880. (
  881. SELECT
  882. GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' )
  883. FROM
  884. cygx_industrial_management AS man
  885. WHERE
  886. 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 )
  887. ) AS industry_name
  888. FROM
  889. cygx_article_history_record_all AS r
  890. INNER JOIN cygx_article AS art ON art.article_id = r.article_id
  891. LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two
  892. LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id
  893. WHERE
  894. art.article_id IN ( ` + articleIds + `) GROUP BY art.article_id `
  895. _, err = o.Raw(sql).QueryRows(&item)
  896. return
  897. }
  898. // 标的名称
  899. func GetArticleGroupySubjectName(articleIds string) (item []*ArticleIndustryNameResp, err error) {
  900. o := orm.NewOrm()
  901. sql := `SELECT
  902. article_id,
  903. subject_name
  904. FROM
  905. cygx_industrial_article_group_subject AS mg
  906. INNER JOIN cygx_industrial_subject AS m ON m.industrial_subject_id = mg.industrial_subject_id
  907. WHERE
  908. article_id IN ( ` + articleIds + `)`
  909. _, err = o.Raw(sql).QueryRows(&item)
  910. return
  911. }
  912. type UserLabel struct {
  913. Label string `description:"标签 多个用 , 隔开"`
  914. }
  915. type ActivityLabelCountResp struct {
  916. Label string `description:"标签"`
  917. Pv int `description:"Pv"`
  918. }
  919. // 活动标签记录(我的日程)
  920. func GetActivitySignCount(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) {
  921. o := orm.NewOrm()
  922. sql := `SELECT
  923. label ,
  924. COUNT( label ) AS pv
  925. FROM
  926. cygx_activity
  927. WHERE
  928. activity_id IN ( SELECT activity_id FROM cygx_my_schedule WHERE mobile = ? )
  929. AND label != ''
  930. AND activity_time >= ?
  931. GROUP BY
  932. label`
  933. _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item)
  934. return
  935. }
  936. // 活动标签记录(会议提醒)
  937. func GetActivityMeetingReminder(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) {
  938. o := orm.NewOrm()
  939. sql := `SELECT
  940. label ,
  941. COUNT( label ) AS pv
  942. FROM
  943. cygx_activity
  944. WHERE
  945. activity_id IN ( SELECT activity_id FROM cygx_activity_meeting_reminder WHERE mobile = ? )
  946. AND label != ''
  947. AND activity_time >= ?
  948. GROUP BY
  949. label`
  950. _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item)
  951. return
  952. }
  953. func GetCygxArticleCollectId(uid int, dateTime string) (articleIds string, err error) {
  954. sql := `SELECT
  955. GROUP_CONCAT( DISTINCT article_id SEPARATOR ',' ) AS permission
  956. FROM
  957. cygx_article_collect
  958. WHERE
  959. user_id = ?
  960. AND create_time >= ? `
  961. o := orm.NewOrm()
  962. err = o.Raw(sql, uid, dateTime).QueryRow(&articleIds)
  963. return
  964. }
  965. // 用户互动量
  966. type CygxInteractionNumResp struct {
  967. DateTime string `description:"时间"`
  968. InteractionNum int `description:"互动量"`
  969. }
  970. type CygxCygxInteractionNumRespListResp struct {
  971. List []*CygxInteractionNumResp
  972. }
  973. // 用户互动详情
  974. type UserInteractionrelevantListResp struct {
  975. IndustryName string `description:"产业名称"`
  976. IsFllow bool `description:"是否关注"`
  977. List []*UserInteraction
  978. }
  979. type EsUserInteraction struct {
  980. Id int `description:"主键ID"`
  981. ArticleId int `description:"文章id"`
  982. ArticleType int `description:"文章类型 1:查研观向, 2:策略平台"`
  983. Title string `description:"标题"`
  984. PublishDate string `description:"发布时间"`
  985. CreateTime string `description:"创建时间"`
  986. StopTime string `description:"阅读停留时间"`
  987. RealName string `description:"姓名"`
  988. CompanyName string `description:"公司名称"`
  989. CompanyId int `description:"公司ID"`
  990. SellerName string `description:"所属销售"`
  991. SellerId int `description:"所属销售ID"`
  992. Mobile string `description:"手机号"`
  993. Email string `description:"邮箱"`
  994. UserId int `description:"用户ID"`
  995. UserArticleHistoryNum int `description:"用户阅读数量"`
  996. CompanyArticleHistoryNum int `description:"机构阅读数量"`
  997. }
  998. func GetCompanyIdsCondition(ondition string) (companyIds string, err error) {
  999. sql := ondition
  1000. o := orm.NewOrm()
  1001. err = o.Raw(sql).QueryRow(&companyIds)
  1002. return
  1003. }
  1004. type InteractiveList struct {
  1005. TotalMap map[int]int
  1006. EsList []*EsUserInteraction
  1007. ActivityList []*company.CompanyReportRecordGroup
  1008. CollectList []*company.CompanyReportRecordGroup
  1009. IndustryFllowList []*company.CompanyReportRecordGroup
  1010. DepartmentFollowList []*company.CompanyReportRecordGroup
  1011. KeyWordList []*company.CompanyReportRecordGroup
  1012. Chartist []*company.CompanyReportRecordGroup
  1013. }
  1014. func GetAllOverseasRaiEmail() (emails []string, err error) {
  1015. sql := `
  1016. SELECT
  1017. *
  1018. FROM
  1019. (
  1020. SELECT
  1021. wu.email
  1022. FROM
  1023. wx_user AS wu
  1024. INNER JOIN company AS c
  1025. WHERE
  1026. wu.company_id = c.company_id
  1027. AND wu.email <> ""
  1028. AND c.region_type = "海外"
  1029. ) AS a UNION
  1030. (
  1031. SELECT
  1032. wu.email
  1033. FROM
  1034. company_product AS cp
  1035. INNER JOIN wx_user AS wu
  1036. WHERE
  1037. cp.industry_id = 10
  1038. AND cp.product_id = 2
  1039. AND wu.email <> ""
  1040. AND cp.status IN ( "正式", "试用" )
  1041. )`
  1042. o := orm.NewOrm()
  1043. _, err = o.Raw(sql).QueryRows(&emails)
  1044. return
  1045. }