package cygx import ( "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/models/company" "hongze/hz_crm_api/utils" "strconv" "strings" "time" ) type CygxCompanyUser struct { UserId int64 `orm:"column(user_id);pk"` Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` RealName string `description:"姓名"` CreatedTime string `description:"创建时间"` IsMaker int `description:"是否决策人，1:是，0:否"` IsRegister bool `description:"是否注册,true:已注册，false:未注册"` Status string `description:"客户状态"` RegisterTime string `description:"注册时间"` SellerName string `description:"销售名称"` InteractionNum int `description:"互动量"` CompanyInteractionNum int `description:"企业互动量"` CompanyInteractionNumSeller int `description:"销售可见企业互动量"` Labels string `description:"标签，用英文,隔开"` ActivityLabel string `description:"活动标签，用英文,隔开"` IsShowSee bool `description:"是否展示查看"` IsShowSeeNum int `description:"是否展示查看"` HistoryNum int `description:"报告阅读"` CountNum int `description:"报告收藏"` IndustryFllowNum int `description:"产业关注"` DepartmentFollowNum int `description:"作者关注"` KeyWordNum int `description:"搜索关键词"` OnLineNum int `description:"线上互动活动"` OfficeNum int `description:"线下互动活动"` ChartNum int `description:"图表收藏数量"` TripNum int `description:"图表数量"` RoadshowVideoNum int `description:"产业视频播放量"` ActivityVideoNum int `description:"活动视频播放量"` ActivityVoiceNum int `description:"活动音频播放量"` YanxuanspecialNum int `description:"研选专栏查看数量"` RsCalendarNum int `description:"1V1 路演数量"` FeedbackNum int `description:"交流反馈数量"` PackageType int `description:"套餐类型，0：无，1：大套餐，2：小套餐"` TryStage int `description:"试用客户子标签：0全部、1未分类、2 推进、3 跟踪、4 预备"` Content string `description:"备注信息"` IsRemind bool `description:"是否添加互动提醒"` IsSubscribeCygx int `description:"是否关注了查研观向微信公众号: 0-未关注; 1-已关注"` IsUserMaker int `description:"近四周之内是否包含决策人互动过 ，0否，1是"` HaveMoveButton bool `description:"是否移动过"` } type CompanyUserListResp struct { Paging *paging.PagingItem List []*CygxCompanyUser } // 获取数量 func GetCompanyListCount(userCondition, keyWord, kwywordcondition, condition, roleTypeCode string, pars []interface{}) (count int, err error) { o := orm.NewOrm() 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 ` kwywordcondition = companyCondition + kwywordcondition if condition != "" { companyCondition += condition } sql := `SELECT COUNT(DISTINCT u.user_id) AS count FROM wx_user AS u INNER JOIN company AS c ON c.company_id = u.company_id INNER JOIN company_product AS cp ON cp.company_id = c.company_id INNER JOIN admin AS m ON m.admin_id = cp.seller_id INNER JOIN user_seller_relation AS sr ON sr.user_id = u.user_id WHERE u.company_id IN (` + companyCondition + `)` if userCondition != "" { sql += userCondition } if keyWord != "" { if strings.Count(roleTypeCode, "admin") > 0 { 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 + ` )` } else { 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 + ` )` } } err = o.Raw(sql, pars).QueryRow(&count) return } // 列表 func GetCygxCompanyUserList(userCondition, keyWord, kwywordcondition, condition, roleTypeCode, sqlOrder string, pars []interface{}, startSize, pageSize int) (items []*CygxCompanyUser, err error) { o := orm.NewOrm() 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 ` kwywordcondition = companyCondition + kwywordcondition if condition != "" { companyCondition += condition } sql := `SELECT u.user_id, u.mobile, u.email, date_format(u.register_time,'%Y-%m-%d') as register_time, u.real_name, u.is_register, u.is_maker, u.user_label as labels, u.cygx_subscribe as is_subscribe_cygx, c.company_name, c.company_id, c.interaction_num as company_interaction_num, c.interaction_num_seller as company_interaction_num_seller, cp.status, cp.try_stage, cp.package_type, m.real_name as seller_name , ( SELECT COUNT( 1 ) FROM user_remarks AS rm WHERE rm.user_id = u.user_id ) AS is_show_see_num FROM wx_user AS u INNER JOIN company AS c ON c.company_id = u.company_id INNER JOIN company_product AS cp ON cp.company_id = c.company_id INNER JOIN admin AS m ON m.admin_id = cp.seller_id INNER JOIN user_seller_relation AS sr ON sr.user_id = u.user_id WHERE u.company_id IN (` + companyCondition + `)` if userCondition != "" { sql += userCondition } if keyWord != "" { if strings.Count(roleTypeCode, "admin") > 0 { 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 + ` )` } else { 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 + ` )` } } if sqlOrder != "" { sql += sqlOrder + ` LIMIT ?,? ` } else { sql += ` GROUP by u.user_id ORDER BY c.created_time DESC, u.register_time DESC LIMIT ?,? ` } _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } type CygxCompanyUserAndSellerResp struct { UserId int `description:"用户ID"` Mobile string `description:"手机号"` Email string `description:"邮箱"` CompanyId int `description:"公司id"` CompanyName string `description:"公司名称"` RealName string `description:"姓名"` SellerName string `description:"销售名称"` } // 给所有已绑定手机号的客户（除流失）列表 func GetCygxCompanyUserListByNoLoss() (items []*CygxCompanyUserAndSellerResp, err error) { o := orm.NewOrm() sql := `SELECT u.user_id, u.mobile, u.email, u.real_name, c.company_name, c.company_id, m.real_name AS seller_name FROM wx_user AS u INNER JOIN company AS c ON c.company_id = u.company_id INNER JOIN company_product AS cp ON cp.company_id = c.company_id INNER JOIN admin AS m ON m.admin_id = cp.seller_id INNER JOIN user_seller_relation AS sr ON sr.user_id = u.user_id WHERE 1 = 1 AND c.enabled = 1 AND cp.STATUS != '流失' AND cp.product_id = 2 AND sr.product_id = 2 GROUP BY u.user_id ` _, err = o.Raw(sql).QueryRows(&items) return } // 对于上面的SQL的拆分优化查询速度 func GetCygxCompanyUserListSplit(userIds string) (items []*CygxCompanyUser, err error) { databaseName := utils.GetWeeklyDatabase() o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT u.user_id, ( 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, ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h WHERE h.user_id = u.user_id ) AS count_num, ( SELECT COUNT( 1 ) FROM cygx_chart_collect AS h WHERE h.user_id = u.user_id ) AS chart_num, ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id AND h.type = 1 AND h.source IN (0,1,2) ) AS industry_fllow_num, ( 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, ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = u.user_id ) AS key_word_num, ( 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 AND t.activity_type = 1 AND h.do_fail_type = 0 ) AS on_line_num, ( 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 AND t.activity_type = 0 AND h.do_fail_type = 0 ) AS office_num, ( 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, ( 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, ( 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, ( 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, ( SELECT COUNT( 1 ) FROM cygx_yanxuan_special_record AS h INNER JOIN cygx_yanxuan_special as v ON v.id = h.yanxuan_special_id WHERE h.mobile = u.mobile ) AS yanxuanspecial_num, ( SELECT COUNT(1) FROM cygx_user_feedback as h WHERE h.mobile = u.mobile ) AS feedback_num, ( SELECT COUNT(1) FROM ` + databaseName + `.rs_calendar_meeting_user as h WHERE h.mobile = u.mobile ) AS rs_calendar_num FROM ` + databaseName + `.wx_user AS u WHERE u.user_id IN (` + userIds + `) AND u.mobile != '' GROUP BY u.user_id ` _, err = o.Raw(sql).QueryRows(&items) return } type UserRemarks struct { Id int `orm:"column(id);pk"` UserId int `description:"用户ID"` AdminId int `description:"销售/管理员ID"` Content string `description:"备注信息"` CreateTime time.Time `description:"创建时间"` } type UserRemarksResp struct { Id int `orm:"column(id);pk"` UserId int `description:"用户ID"` AdminId int `description:"销售/管理员ID"` Content string `description:"备注信息"` CreateTime string `description:"创建时间"` } type UserRemarAddReq struct { UserId int `description:"用户ID"` Content string `description:"备注信息"` SourceType int `description:"操作类型，1：对联系人单独设置，2：对机构批量设置"` DoType int `description:"操作方式，1：添加，2：取消"` } // 添加UserRemarks func AddUserRemarks(item *UserRemarks) (err error) { o := orm.NewOrm() _, err = o.Insert(item) return } // 获取备注列表 func GetUserRemarksList(userId int) (items []*UserRemarksResp, err error) { o := orm.NewOrm() sql := ` SELECT * FROM user_remarks WHERE user_id=? ORDER BY create_time DESC` _, err = o.Raw(sql, userId).QueryRows(&items) return } // 获取备注列表(多用户) func GetUserRemarksListByUserIds(uids string) (items []*UserRemarksResp, err error) { o := orm.NewOrm() sql := ` SELECT * FROM user_remarks WHERE user_id IN(` + uids + `) ORDER BY create_time DESC` _, err = o.Raw(sql).QueryRows(&items) return } type UserRemarksListResp struct { List []*UserRemarksResp } // 切换列表 type CygxUserTableRep struct { PermissionName string `description:"名称"` Source int `description:"类型"` TotalNum int `description:"总数"` } type CygxUserTableListRep struct { List []*CygxUserTableRep UserName string `description:"用户名称"` ComapnyName string `description:"公司名称"` Mobile string `description:"手机号"` } // @Param TryStage query int false "试用客户子标签：0全部、1未分类、2 推进、3 跟踪、4 预备" // 状态搜索列表 type CygxTryStageRep struct { Name string `description:"试用客户子标签"` TryStage int `description:"试用客户子标签：0全部、1未分类、2 推进、3 跟踪、4 预备"` } type CygxUserTableStatusRep struct { List []*CygxTryStageRep Name string `description:"状态"` TryStage string `description:"状态"` } type CygxUserTableStatusListRep struct { List []*CygxUserTableStatusRep } type UserInteractionListResp struct { Paging *paging.PagingItem List []*UserInteraction MeetingNum int `description:"已到会"` NoMeetingNum int `description:"未到会"` } type UserInteraction struct { ArticleId int `description:"文章id"` ReportId int `description:"研报文章id"` ActivityId int `description:"活动ID"` ArticleType int `description:"文章类型 1：查研观向, 2:策略平台"` ArticleIdMd5 string `description:"文章MD5id"` Title string `description:"标题"` MediaTitle string `description:"音视频标题"` CategoryName string `description:"行业2"` PublishDate string `description:"发布时间"` CreateTime string `description:"创建时间"` CreateDate string `orm:"column(create_time);" description:"创建时间"` PermissionName string `orm:"column(chart_permission_name);" description:"行业"` ChartPermissionId int `description:"行业id"` IndustryName string `description:"产业名称"` SubjectNameStr string `description:"关联标的"` MatchTypeName string `description:"匹配类型"` StopTime string `description:"阅读停留时间"` NickName string `description:"作者昵称"` DepartmentId int `description:"作者ID "` KeyWord string `description:"关键词"` LabelKeyWord string `description:"标签关键词"` Source int `description:"关键词来源；1:纪要、2：图表、3：纪要/图表、4：产业资源包、5：报告、6：活动、7：搜索关键词、8:专项产业调研、9音视频播放"` ActivityName string `description:"活动名称"` ActivityType int `description:"1 线上，0、2线下"` IsMeeting int `description:"是否到会， 1是，0否"` ActivityTypeName string `description:"活动类型名称"` Label string `description:"标签"` ActivityTime string `description:"活动时间"` ActivityTimeText string `description:"活动时间"` FirstMeetingTime string `description:"首次入会时间"` LastMeetingTime string `description:"最后退出时间"` Duration string `description:"参会时长"` MeetingTypeStr string `description:"参会方式"` MeetingAuthentication string `description:"参会权鉴"` MeetingStatusStr string `description:"参会状态"` SignupType string `description:"参会方式 "` InteractionType string `description:"互动类型 "` ReminderNum int `description:"预约外呼数量 "` TitleEn string `description:"英文标题 "` PtagName string `description:"父类名称"` CtagName string `description:"子类名称"` PtagNameTwo string `description:"父类名称"` CtagNameTwo string `description:"子类名称"` CtagNamePc string `description:"Pc端所有的分类名称"` BodyHtml string `orm:"column(cover)";description:"图片链接"` HttpUrl string `orm:"column(iframe)";description:"文章链接跳转地址"` ChartId int `description:"图表id"` RealName string `description:"姓名"` CompanyName string `description:"公司名称"` CompanyId int `description:"公司ID"` SellerName string `description:"所属销售"` Mobile string `description:"手机号"` UserId int `description:"用户ID"` City string `description:"城市"` FileType string `description:"文件类型"` ListChart []*HomeChartListResp Paging *paging.PagingItem IsEnd bool `description:"是否最后一页，配合前端分页添加的参数"` CurrentIndex int `description:"当前页页码,配合前端分页添加的参数"` RegisterPlatform int `description:"来源 1小程序，2：网页 ，3：策略平台"` RegisterPlatformText string `description:"阅读来源（文本描述）"` SourcePlatform string `description:"来源 'MOBILE：移动端小程序','PC：PC端小程序','CELUE：上海策略平台','WEB：查研观向网页版'"` TagId int TagName string // 标签名 ArticleTypes string // 报告系列 ActivityTypes string // 活动类型 Industries string // 产业 SubjectNames string // 标的 SpecialType int `description:"专栏类型 1:笔记，2:观点"` ArticleNum int // 已发布的文章数量 FansNum int // 粉丝数量 ResearcherName string `description:"研究员姓名"` RoadShowTheme string `description:"路演主题"` } // 查研观向图表 type CygxChartResp struct { Title string `description:"标题"` PublishDate string `description:"发布时间"` CreateTime string `description:"创建时间"` TitleEn string `description:"英文标题 "` PtagName string `description:"父类名称"` CtagName string `description:"子类名称"` PtagNameTwo string `description:"父类名称"` CtagNameTwo string `description:"子类名称"` CtagNamePc string `description:"Pc端所有的分类名称"` BodyHtml string `orm:"column(cover)";description:"图片链接"` HttpUrl string `orm:"column(iframe)";description:"文章链接跳转地址"` ChartId int `description:"图表id"` RealyName string `description:"姓名"` Mobile string `description:"手机号"` } // 获取阅读记录数量 func GetCygxArticleHistoryCount(mobile, email, condition string) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") 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 = '` + mobile + `' AND is_del = 0 ` + condition err = o.Raw(sqlCount).QueryRow(&count) return } // 阅读记录列表 func GetCygxArticleHistoryRecordByUser(mobile, email, condition string, startSize, pageSize int) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := ` SELECT art.title, art.article_id, art.article_id_md5, art.publish_date, art.category_name, re.chart_permission_name, re.chart_permission_id, r.create_time, r.stop_time, r.source as source_platform, ( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) ) AS subject_name_str, ( SELECT GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS man WHERE 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 ) ) AS industry_name FROM cygx_article_history_record_all AS r INNER JOIN cygx_article AS art ON art.article_id = r.article_id LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id WHERE r.mobile = ? AND is_del = 0 ` + condition + ` OR ( email = ? AND email <>'' AND is_del = 0 ` + condition + ` ) GROUP BY r.id ORDER BY r.create_time DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql, mobile, email).QueryRows(&items) return } // 用户阅读记录列表 2023-08-02 优化拆分 func GetCygxArticleHistoryRecordByUserNew(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += `SELECT art.title, art.article_id, art.article_id_md5, art.publish_date, art.category_name, r.create_time, r.stop_time, r.source AS source_platform, '' AS register_platform, '' AS special_type FROM cygx_article_history_record_all AS r INNER JOIN cygx_article AS art ON art.article_id = r.article_id WHERE 1= 1 AND is_del = 0 ` + condition //sql += ` UNION ALL ` //sql += ` SELECT // art.title, // art.id AS article_id, // '' AS article_id_md5, // art.publish_time AS publish_date, // '' AS category_name, // r.create_time, // r.stop_time, // r.register_platform AS source_platform, // r.register_platform, // art.type AS special_type // FROM // cygx_yanxuan_special_record AS r // INNER JOIN cygx_yanxuan_special AS art ON art.id = r.yanxuan_special_id // WHERE 1 = 1 ` + condition totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, article_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuaSspecialHistoryRecordByUser(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.title, art.id AS article_id, art.publish_time AS publish_date, r.create_time, r.stop_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform, art.type AS special_type, art.industry_tags as industry_name FROM cygx_yanxuan_special_record AS r INNER JOIN cygx_yanxuan_special AS art ON art.id = r.yanxuan_special_id WHERE 1 = 1 ` + condition totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, article_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuaSspecialHistoryRecordByUserWeekly(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.title, art.id AS article_id, art.publish_time AS publish_date, r.create_time, r.stop_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform, art.type AS special_type, art.industry_tags as industry_name FROM cygx_yanxuan_special_record AS r INNER JOIN cygx_yanxuan_special AS art ON art.id = r.yanxuan_special_id INNER JOIN %s.user_seller_relation AS us ON us.user_id = r.user_id AND us.product_id = 2 WHERE 1 = 1 ` + condition databaseName := utils.GetWeeklyDatabase() sql = fmt.Sprintf(sql, databaseName) totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, article_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuanSpecialCollectByUser(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.title, art.id AS article_id, art.publish_time AS publish_date, r.create_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform, art.type AS special_type, art.industry_tags as industry_name FROM cygx_yanxuan_special_collect AS r INNER JOIN cygx_yanxuan_special AS art ON art.id = r.yanxuan_special_id WHERE 1 = 1 ` + condition totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, article_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuanSpecialCollectByUserWeekly(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.title, art.id AS article_id, art.publish_time AS publish_date, r.create_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform, art.type AS special_type, art.industry_tags as industry_name FROM cygx_yanxuan_special_collect AS r INNER JOIN cygx_yanxuan_special AS art ON art.id = r.yanxuan_special_id INNER JOIN %s.user_seller_relation AS us ON us.user_id = r.user_id AND us.product_id = 2 WHERE 1 = 1 ` + condition databaseName := utils.GetWeeklyDatabase() sql = fmt.Sprintf(sql, databaseName) totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, article_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuanSpecialAuthorByUser(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.special_name as title, art.id AS department_id, art.nick_name, art.article_num, art.fans_num, art.mobile_init, r.create_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform FROM cygx_yanxuan_special_follow AS r INNER JOIN cygx_yanxuan_special_author AS art ON art.user_id = r.follow_user_id WHERE 1 = 1 ` + condition totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, department_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 用户阅读记录列表 func GetCygxYanxuanSpecialAuthorByUserWeekly(condition string, startSize, pageSize int) (total int, items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") var sql string sql += ` SELECT art.special_name as title, art.id AS department_id, art.nick_name, art.article_num, art.fans_num, art.mobile_init, r.create_time, r.user_id, r.mobile, r.company_id, r.company_name, r.real_name, r.register_platform AS source_platform FROM cygx_yanxuan_special_follow AS r INNER JOIN cygx_yanxuan_special_author AS art ON art.user_id = r.follow_user_id INNER JOIN %s.user_seller_relation AS us ON us.user_id = r.user_id AND us.product_id = 2 WHERE 1 = 1 ` + condition databaseName := utils.GetWeeklyDatabase() sql = fmt.Sprintf(sql, databaseName) totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z ` err = o.Raw(totalSql).QueryRow(&total) if err != nil { return } sql += ` ORDER BY create_time DESC, department_id DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&items) return } // 收藏列表数量 func GetCygxArticleCollectCount(uid int, condition string) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") 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 = ? ` + condition err = o.Raw(sqlCount, uid).QueryRow(&count) return } // 收藏列表 func GetCygxArticleCollectByUser(uid, startSize, pageSize int, condition string) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := ` SELECT art.title, art.article_id, art.report_id, art.article_id_md5, art.publish_date, re.chart_permission_name, re.chart_permission_id, r.create_time, re.match_type_name, ( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) ) AS subject_name_str, ( SELECT GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS man WHERE 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 ) ) AS industry_name FROM cygx_article_collect AS r INNER JOIN cygx_article AS art ON art.article_id = r.article_id LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id WHERE user_id = ? ` + condition + ` GROUP BY r.article_id ORDER BY r.create_time DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql, uid).QueryRows(&items) return } // 关注作者数量 func GetCygArticleDepartmentFollowCount(uid int) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := ` SELECT COUNT(1) AS count FROM cygx_article_department_follow WHERE user_id = ? AND type = 1 ` err = o.Raw(sqlCount, uid).QueryRow(&count) return } // 关注作者列表 func GetCygArticleDepartmentFollowByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT m.department_id, m.nick_name, f.modify_time as create_time, m.department_id, i.industry_name, a.title, a.article_id, ( 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, ( 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 FROM cygx_article_department_follow AS f INNER JOIN cygx_article_department AS m ON m.department_id = f.department_id LEFT JOIN cygx_article AS a ON a.department_id = m.department_id LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id LEFT JOIN cygx_industrial_management AS i ON i.industrial_management_id = mg.industrial_management_id WHERE f.user_id = ? AND f.type = 1 GROUP BY m.department_id ORDER BY f.create_time DESC LIMIT ?,? ` _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items) return } // 用户搜索关键词统计 func GetCygxSearchKeyWordCount(uid int, condition string) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := ` SELECT COUNT(1) AS count FROM cygx_search_key_word WHERE user_id = ? ` + condition err = o.Raw(sqlCount, uid).QueryRow(&count) return } // 用户搜索关键词列表 func GetCygxSearchKeyWordByUser(condition string, uid, startSize, pageSize int) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT key_word,create_time FROM cygx_search_key_word as k WHERE user_id = ? ` + condition + ` ORDER BY k.create_time DESC LIMIT ?,? ` _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items) return } // 用户专项产业调研统计 func GetCygxActivitySpecialTripCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := `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 1 = 1 AND a.publish_status = 1 AND a.activity_time_end < NOW() ` + condition err = o.Raw(sqlCount, pars).QueryRow(&count) return } // 用户专项产业调研列表 func GetCygxActivitySpecialTripByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT a.research_theme AS activity_name, a.chart_permission_name, a.activity_time_text_by_day AS activity_time_text, a.city, a.activity_id, a.special_type AS activity_type, h.is_meeting FROM cygx_activity_special_meeting_detail AS h INNER JOIN cygx_activity_special AS a ON a.activity_id = h.activity_id WHERE 1 = 1 AND a.publish_status = 1 AND a.activity_time_end < NOW() AND h.user_id = ? ORDER BY a.activity_time DESC LIMIT ?,? ` _, err = o.Raw(sql, uid, startSize, pageSize).QueryRows(&items) return } // 用户音视频浏览统计 func GetCygxRoadshowCount(uid int) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := `SELECT COUNT( 1 ) total FROM ( SELECT "" FROM cygx_micro_roadshow_video_history AS r INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id WHERE r.user_id = ? UNION ALL SELECT "" FROM cygx_activity_video_history AS r INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id WHERE r.user_id = ? UNION ALL SELECT "" FROM cygx_activity_voice_history AS r INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id WHERE r.user_id = ? )z ` err = o.Raw(sqlCount, uid, uid, uid).QueryRow(&count) return } // 用户专项产业调研列表 func GetCygxRoadshowByUser(uid, startSize, pageSize int) (items []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT v.video_name AS media_title, r.create_time, "逻辑解析" AS file_type FROM cygx_micro_roadshow_video_history AS r INNER JOIN cygx_micro_roadshow_video AS v ON r.video_id = v.video_id WHERE r.user_id = ? UNION ALL SELECT v.video_name AS media_title, r.create_time, "路演回放" AS file_type FROM cygx_activity_video_history AS r INNER JOIN cygx_activity_video AS v ON r.video_id = v.video_id WHERE r.user_id = ? UNION ALL SELECT v.voice_name AS media_title, r.create_time, "路演回放" AS file_type FROM cygx_activity_voice_history AS r INNER JOIN cygx_activity_voice AS v ON r.activity_id = v.activity_id WHERE r.user_id = ? ORDER BY create_time DESC LIMIT ?,? ` _, err = o.Raw(sql, uid, uid, uid, startSize, pageSize).QueryRows(&items) return } // 线上已到会列表 func GetAttendanceDetailListByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT s.signup_type, a.activity_name, a.chart_permission_name, a.activity_type_name, a.label, a.activity_time, d.first_meeting_time, d.last_meeting_time, d.meeting_type_str, d.meeting_authentication, d.meeting_status_str, d.duration FROM cygx_activity_signup AS s INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_attendance_detail AS d ON d.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE 1 = 1 AND s.do_fail_type = 0 AND s.is_cancel = 0 AND s.is_meeting = ? AND s.mobile = ? AND a.is_submit_meeting = 1 AND t.activity_type = 1 GROUP BY a.activity_id ORDER BY a.activity_time DESC LIMIT ?,?` _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item) return } // 线上未来到会列表 func GetAttendanceDetailListNoMeetingByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT s.signup_type, a.activity_name, a.chart_permission_name, a.activity_type_name, a.label, a.activity_time, ( 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 FROM cygx_activity_signup AS s INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE 1 = 1 AND s.do_fail_type = 0 AND s.is_cancel = 0 AND s.is_meeting = ? AND s.mobile = ? AND a.is_submit_meeting = 1 AND t.activity_type = 1 GROUP BY a.activity_id ORDER BY a.activity_time DESC LIMIT ?,?` _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item) return } // 用户参会统计 func GetAttendanceDetaiCount(mobile string, meetType, isMeeting int) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := ` SELECT COUNT( DISTINCT a.activity_id ) as count FROM cygx_activity_signup AS s INNER JOIN cygx_activity as a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE mobile = ? AND is_meeting =? AND s.do_fail_type = 0 AND a.is_submit_meeting = 1 ` if meetType == 1 { sqlCount += ` AND t.activity_type = 1 ` } else { sqlCount += ` AND t.activity_type = 0 ` } err = o.Raw(sqlCount, mobile, isMeeting).QueryRow(&count) return } // 线下已到会列表 func GetAttendanceOfflineListByUser(isMeeting int, mobile string, startSize, pageSize int) (item []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT a.activity_name, a.chart_permission_name, a.activity_type_name, a.label, a.activity_time FROM cygx_activity_signup AS s INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE 1 = 1 AND s.do_fail_type = 0 AND s.is_cancel = 0 AND s.is_meeting = ? AND s.mobile = ? AND a.is_submit_meeting = 1 AND t.activity_type = 0 GROUP BY a.activity_id ORDER BY a.activity_time DESC LIMIT ?,?` _, err = o.Raw(sql, isMeeting, mobile, startSize, pageSize).QueryRows(&item) return } // 获取用户参会记录数量 func GetActivityMeetByUserCount(condition string) (count int, err error) { o := orm.NewOrmUsingDB("hz_cygx") sqlCount := ` SELECT COUNT( DISTINCT a.activity_id ) as count FROM cygx_activity_signup_detail AS s INNER JOIN cygx_activity as a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE 1=1 AND s.do_fail_type = 0 ` if condition != "" { sqlCount += condition } err = o.Raw(sqlCount).QueryRow(&count) return } // 获取用户参会记录 func GetActivityMeetByUser(condition string, startSize, pageSize int) (item []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT t.activity_type, s.signup_type, s.is_meeting, a.activity_name, a.chart_permission_name, a.activity_type_name, a.label, a.activity_time, s.first_meeting_time, s.last_meeting_time, s.meeting_type_str, s.meeting_authentication, s.meeting_status_str, s.duration FROM cygx_activity_signup_detail AS s INNER JOIN cygx_activity AS a ON a.activity_id = s.activity_id INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id WHERE 1 = 1 AND s.do_fail_type = 0 ` + condition + ` GROUP BY s.activity_id,s.outbound_mobile ORDER BY a.activity_time DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&item) return } // 获取用户参会记录 func GetActivitySpecialMeetByUser(condition string, startSize, pageSize int) (item []*UserInteraction, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT a.activity_id, a.research_theme AS activity_name, a.chart_permission_name, special_type AS activity_type, a.activity_time, a.activity_time_text_by_day AS activity_time_text FROM cygx_activity_special_meeting_detail AS s INNER JOIN cygx_activity_special AS a ON a.activity_id = s.activity_id WHERE 1 = 1 ` + condition + ` GROUP BY s.activity_id, s.mobile ORDER BY a.activity_time DESC ` if startSize > 0 || pageSize > 0 { sql += ` LIMIT ` + strconv.Itoa(startSize) + "," + strconv.Itoa(pageSize) } _, err = o.Raw(sql).QueryRows(&item) return } type GetUserInteractionTableCountResp struct { HistoryNum int `description:"报告阅读"` CountNum int `description:"报告收藏"` IndustryFllowNum int `description:"产业关注"` DepartmentFollowNum int `description:"作者关注"` KeyWordNum int `description:"搜索关键词"` OnLineNum int `description:"线上互动活动"` OfficeNum int `description:"线下互动活动"` ActivityNum int `description:"活动数量"` ChartNum int `description:"图表数量"` TripNum int `description:"图表数量"` RoadshowVideoNum int `description:"产业视频播放量"` ActivityVideoNum int `description:"活动视频播放量"` ActivityVoiceNum int `description:"活动音频播放量"` TagNum int `description:"首页标签点击量"` YanxuanSpecialNum int `description:"研选专栏阅读数量"` YanxuanSpecialCollectNum int `description:"研选专栏收藏数量"` YanxuanSpecialFollowNum int `description:"研选专栏作者关注数量"` FeedbackNum int `description:"交流反馈数量"` } // 用户互动总数统计 func GetUserInteractionTableCount(uid int) (item *GetUserInteractionTableCountResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT ( 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.user_id = ? AND h.is_del = 0 AND art.article_type_id = 0 ) AS history_num, ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h INNER JOIN cygx_article as art ON art.article_id = h.article_id WHERE h.user_id = ? AND art.article_type_id = 0 ) AS count_num, ( 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 = ? ) AS chart_num, ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h INNER JOIN cygx_industrial_management as a ON a.industrial_management_id = h.industrial_management_id WHERE h.user_id = ? AND h.type = 1 AND h.source IN (0,1,2) AND a.chart_permission_id != 31 ) AS industry_fllow_num, ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = ? AND h.register_platform IN(1,2) ) AS key_word_num, ( 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.user_id = ? AND t.activity_type=1 AND h.do_fail_type = 0 AND a.chart_permission_id != 31 ) AS on_line_num, ( 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.user_id = ? AND t.activity_type = 0 AND h.do_fail_type = 0 AND a.chart_permission_id != 31 ) AS office_num, ( 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.user_id = ? AND a.publish_status = 1 AND a.activity_time_end < NOW()) AS trip_num, ( 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.user_id = ? ) AS roadshow_video_num, ( 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.user_id = ? ) AS activity_video_num, ( 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.user_id = ? ) AS activity_voice_num, ( SELECT COUNT(1) FROM cygx_tag_history as h INNER JOIN cygx_tag AS b ON h.tag_id = b.tag_id WHERE h.user_id = ? ) AS tag_num, ( SELECT COUNT(1) FROM cygx_user_feedback as h WHERE h.user_id = ? ) AS feedback_num FROM dual ` err = o.Raw(sql, uid, uid, uid, uid, uid, uid, uid, uid, uid, uid, uid, uid, uid).QueryRow(&item) return } // 用户互动总数统计 func GetUserInteractionTableCountByYanXuan(uid int) (item *GetUserInteractionTableCountResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT ( 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.user_id = ? AND h.is_del = 0 AND art.article_type_id > 0 ) AS history_num, ( SELECT COUNT( 1 ) FROM cygx_article_collect AS h INNER JOIN cygx_article as art ON art.article_id = h.article_id WHERE h.user_id = ? AND art.article_type_id > 0 ) AS count_num, ( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h INNER JOIN cygx_industrial_management as a ON a.industrial_management_id = h.industrial_management_id WHERE h.user_id = ? AND h.type = 1 AND h.source IN (0,1,2) AND a.chart_permission_id = 31 ) AS industry_fllow_num, ( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = ? AND h.type = 1 ) AS department_follow_num, ( SELECT COUNT( 1 ) FROM cygx_search_key_word AS h WHERE h.user_id = ? AND h.register_platform IN(5,6) ) AS key_word_num, ( 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.user_id = ? AND t.activity_type=1 AND h.do_fail_type = 0 AND a.chart_permission_id = 31 ) AS on_line_num, ( 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.user_id = ? AND t.activity_type = 0 AND h.do_fail_type = 0 AND a.chart_permission_id = 31 ) AS office_num, ( SELECT COUNT(1) FROM cygx_yanxuan_special_collect as h INNER JOIN cygx_yanxuan_special AS b ON h.yanxuan_special_id = b.id WHERE h.user_id = ? ) AS yanxuan_special_collect_num, ( SELECT COUNT(1) FROM cygx_yanxuan_special_follow as h WHERE h.user_id = ? ) AS yanxuan_special_follow_num, ( SELECT COUNT(1) FROM cygx_yanxuan_special_record as h INNER JOIN cygx_yanxuan_special AS b ON h.yanxuan_special_id = b.id WHERE h.user_id = ? ) AS yanxuan_special_num FROM dual ` err = o.Raw(sql, uid, uid, uid, uid, uid, uid, uid, uid, uid, uid).QueryRow(&item) return } // 根据时间获取用户互动总数统计 func GetUserInteractionTableCountBytime(uid int, startDate, endDate string) (item *GetUserInteractionTableCountResp, err error) { o := orm.NewOrm() sql := `SELECT ( 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, ( 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, ( 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, ( 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, ( 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, ( 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, ( 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 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, ( 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 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 FROM wx_user as u WHERE u.user_id = ?` err = o.Raw(sql, uid).QueryRow(&item) return } type ArticlePvCountResp struct { ArticleId int `description:"文章ID"` Pv int `description:"Pv"` } // 阅读记录 func GetArticlePvCount(mobile, email, dateTime string) (item []*ArticlePvCountResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT article_id, COUNT(article_id) as pv FROM cygx_article_history_record_all as h WHERE mobile = ? AND create_time >= ? AND is_del = 0 OR ( email = ? AND email <>'' AND create_time >= ? AND is_del = 0 ) GROUP BY article_id ` _, err = o.Raw(sql, mobile, dateTime, email, dateTime).QueryRows(&item) return } type ArticleIndustryNameResp struct { ArticleId int `description:"文章ID"` IndustryName string `description:"产业名称"` SubjectName string `description:"标的名称"` } // 产业名称 func GetArticleGroupyIndustryName(articleIds string) (item []*ArticleIndustryNameResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT art.article_id, ( SELECT GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) FROM cygx_industrial_subject AS s WHERE s.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.article_id = art.article_id ) ) AS subject_name, ( SELECT GROUP_CONCAT( DISTINCT man.industry_name SEPARATOR '/' ) FROM cygx_industrial_management AS man WHERE 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 ) ) AS industry_name FROM cygx_article_history_record_all AS r INNER JOIN cygx_article AS art ON art.article_id = r.article_id LEFT JOIN cygx_report_mapping AS re ON re.category_id = art.category_id_two LEFT JOIN cygx_industrial_article_group_management AS man ON man.article_id = art.article_id WHERE art.article_id IN ( ` + articleIds + `) GROUP BY art.article_id ` _, err = o.Raw(sql).QueryRows(&item) return } // 标的名称 func GetArticleGroupySubjectName(articleIds string) (item []*ArticleIndustryNameResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT article_id, subject_name FROM cygx_industrial_article_group_subject AS mg INNER JOIN cygx_industrial_subject AS m ON m.industrial_subject_id = mg.industrial_subject_id WHERE article_id IN ( ` + articleIds + `)` _, err = o.Raw(sql).QueryRows(&item) return } type UserLabel struct { Label string `description:"标签 多个用 , 隔开"` } type ActivityLabelCountResp struct { Label string `description:"标签"` Pv int `description:"Pv"` } // 活动标签记录(我的日程) func GetActivitySignCount(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT label , COUNT( label ) AS pv FROM cygx_activity WHERE activity_id IN ( SELECT activity_id FROM cygx_my_schedule WHERE mobile = ? ) AND label != '' AND activity_time >= ? GROUP BY label` _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item) return } // 活动标签记录(会议提醒) func GetActivityMeetingReminder(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) { o := orm.NewOrmUsingDB("hz_cygx") sql := `SELECT label , COUNT( label ) AS pv FROM cygx_activity WHERE activity_id IN ( SELECT activity_id FROM cygx_activity_meeting_reminder WHERE mobile = ? ) AND label != '' AND activity_time >= ? GROUP BY label` _, err = o.Raw(sql, mobile, dateTime).QueryRows(&item) return } func GetCygxArticleCollectId(uid int, dateTime string) (articleIds string, err error) { sql := `SELECT GROUP_CONCAT( DISTINCT article_id SEPARATOR ',' ) AS permission FROM cygx_article_collect WHERE user_id = ? AND create_time >= ? ` o := orm.NewOrmUsingDB("hz_cygx") err = o.Raw(sql, uid, dateTime).QueryRow(&articleIds) return } // 用户互动量 type CygxInteractionNumResp struct { DateTime string `description:"时间"` InteractionNum int `description:"互动量"` } type CygxCygxInteractionNumRespListResp struct { List []*CygxInteractionNumResp } // 用户互动详情 type UserInteractionrelevantListResp struct { IndustryName string `description:"产业名称"` IsFllow bool `description:"是否关注"` List []*UserInteraction } type EsUserInteraction struct { Id int `description:"主键ID"` ArticleId int `description:"文章id"` ArticleType int `description:"文章类型 1：查研观向, 2:策略平台"` Title string `description:"标题"` PublishDate string `description:"发布时间"` CreateTime string `description:"创建时间"` StopTime string `description:"阅读停留时间"` RealName string `description:"姓名"` CompanyName string `description:"公司名称"` CompanyId int `description:"公司ID"` SellerName string `description:"所属销售"` SellerId int `description:"所属销售ID"` Mobile string `description:"手机号"` Email string `description:"邮箱"` UserId int `description:"用户ID"` UserArticleHistoryNum int `description:"用户阅读数量"` CompanyArticleHistoryNum int `description:"机构阅读数量"` } func GetCompanyIdsCondition(ondition string) (companyIds string, err error) { sql := ondition o := orm.NewOrm() err = o.Raw(sql).QueryRow(&companyIds) return } type InteractiveList struct { TotalMap map[int]int EsList []*EsUserInteraction ActivityList []*company.CompanyReportRecordGroup CollectList []*company.CompanyReportRecordGroup IndustryFllowList []*company.CompanyReportRecordGroup DepartmentFollowList []*company.CompanyReportRecordGroup KeyWordList []*company.CompanyReportRecordGroup Chartist []*company.CompanyReportRecordGroup } func GetAllOverseasRaiEmail() (emails []string, err error) { sql := ` SELECT * FROM ( SELECT wu.email FROM wx_user AS wu INNER JOIN company AS c WHERE wu.company_id = c.company_id AND wu.email <> "" AND c.region_type = "海外" ) AS a UNION ( SELECT wu.email FROM company_product AS cp INNER JOIN wx_user AS wu WHERE cp.industry_id = 10 AND cp.product_id = 2 AND wu.email <> "" AND cp.status IN ( "正式", "试用" ) )` o := orm.NewOrm() _, err = o.Raw(sql).QueryRows(&emails) return }