hz_crm_api/models/cygx/cygx_user.go

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:"研选专栏查看数量"`
	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-已关注"`
}

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
		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"`
	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:"关键词"`
	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    // 粉丝数量
}

// 查研观向图表
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.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.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.company_id,
			art.company_name,
			art.mobile_init as  mobile,
			r.create_time,
			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.company_id,
			art.company_name,
			art.mobile_init as  mobile,
			r.create_time,
			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.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:"研选专栏作者关注数量"`
}

// 用户互动总数统计
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
			FROM  dual `
	err = o.Raw(sql, 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
}