hongze_cygx/models/wx_user.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"time"
)

type WxUser struct {
	UserId              int       `orm:"column(user_id);pk"`
	OpenId              string    `description:"open_id"`
	UnionId             string    `description:"union_id"`
	Subscribe           string    `description:"是否关注"`
	CompanyId           int       `description:"客户id"`
	NickName            string    `description:"用户昵称"`
	RealName            string    `description:"用户实际名称"`
	UserCode            string    `description:"用户编码"`
	Mobile              string    `description:"手机号码"`
	BindAccount         string    `description:"绑定时的账号"`
	WxCode              string    `description:"微信号"`
	Profession          string    `description:"职业"`
	Email               string    `description:"邮箱"`
	Telephone           string    `description:"座机"`
	Sex                 int       `description:"普通用户性别，1为男性，2为女性"`
	Province            string    `description:"普通用户个人资料填写的省份"`
	City                string    `description:"普通用户个人资料填写的城市"`
	Country             string    `description:"国家，如中国为CN"`
	SubscribeTime       int       `description:"关注时间"`
	Remark              string    `description:"备注"`
	Headimgurl          string    `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"`
	Privilege           string    `description:"用户特权信息，json数组，如微信沃卡用户为（chinaunicom）"`
	Unionid             string    `description:"用户统一标识。针对一个微信开放平台帐号下的应用，同一用户的unionid是唯一的。"`
	FirstLogin          int       `description:"是否第一次登陆"`
	Enabled             int       `description:"是否可用"`
	CreatedTime         time.Time `description:"创建时间"`
	LastUpdatedTime     time.Time `description:"最新一次修改时间"`
	Seller              string    `description:"销售员"`
	Note                string    `description:"客户备份信息"`
	IsNote              int       `description:"是否备注过信息"`
	FromType            string    `description:"report' COMMENT 'report:研报，teleconference:电话会"`
	ApplyMethod         int       `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"`
	RegisterTime        time.Time `description:"注册时间"`
	RegisterPlatform    int       `description:"注册平台,1:微信端,2:PC网页端"`
	IsFreeLogin         bool      `description:"是否免登陆,true：免登陆,false:非免登陆"`
	LoginTime           time.Time `description:"最近一次登录时间"`
	SessionKey          string    `description:"微信小程序会话密钥"`
	IsRegister          int       `description:"是否注册:1:已注册,0:未注册"`
	Source              int       `description:"绑定来源,1:微信端,2:pc网页端，3:查研观向小程序,4:每日咨询"`
	CountryCode         string    `description:"区号"`
	OutboundMobile      string    `description:"外呼手机号"`
	OutboundCountryCode string    `description:"外呼手机号区号"`
	TripartiteCode      string    `description:"第三方给过来的用户编码，判断用户是否存在"`
}

//添加用户信息
func AddWxUser(item *WxUser) (lastId int64, err error) {
	o := orm.NewOrm()
	lastId, err = o.Insert(item)
	return
}

type WxUserItem struct {
	UserId              int       `description:"用户id"`
	OpenId              string    `description:"open_id"`
	UnionId             string    `description:"union_id"`
	CompanyId           int       `description:"客户id"`
	NickName            string    `description:"用户昵称"`
	RealName            string    `description:"用户实际名称"`
	Mobile              string    `description:"手机号码"`
	BindAccount         string    `description:"绑定时的账号"`
	Email               string    `description:"邮箱"`
	Headimgurl          string    `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"`
	ApplyMethod         int       `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"`
	FirstLogin          int       `description:"是否第一次登陆"`
	IsFreeLogin         int       `description:"是否免登陆,true：免登陆,false:非免登陆"`
	LoginTime           time.Time `description:"登录时间"`
	CreatedTime         time.Time `description:"创建时间"`
	LastUpdatedTime     time.Time `description:"最近一次修改时间"`
	SessionKey          string    `description:"微信小程序会话密钥"`
	CompanyName         string    `description:"公司名称"`
	IsRegister          int       `description:"是否注册:1:已注册,0:未注册"`
	CountryCode         string    `description:"手机国家区号"`
	OutboundMobile      string    `description:"外呼手机号"`
	OutboundCountryCode string    `description:"外呼手机号区号"`
	IsMsgOutboundMobile int       `description:"是否弹窗过绑定外呼手机号区号"`
	Source              int
}

func GetWxUserItemByUnionid(unionid string) (item *WxUserItem, err error) {
	sql := `SELECT * FROM wx_user WHERE union_id=? `
	err = orm.NewOrm().Raw(sql, unionid).QueryRow(&item)
	return
}

//根据用户ID获取相关信息
func GetWxUserItemByUserId(userId int) (item *WxUserItem, err error) {
	sql := `SELECT a.*,b.company_name FROM wx_user AS a
			LEFT JOIN company AS b on a.company_id=b.company_id
			WHERE a.user_id=? `
	err = orm.NewOrm().Raw(sql, userId).QueryRow(&item)
	return
}

func GetWxUserItemByOpenId(openId string) (item *WxUserItem, err error) {
	sql := `SELECT * FROM wx_user WHERE open_id=? `
	err = orm.NewOrm().Raw(sql, openId).QueryRow(&item)
	return
}

type WxLoginResp struct {
	Authorization string
	UserId        int
	FirstLogin    int
	Headimgurl    string `description:"用户头像"`
	Mobile        string `description:"手机号"`
	Email         string `description:"邮箱"`
	CompanyName   string `description:"客户名称"`
	Status        string `description:"状态"`
	EndDate       string `description:"到期日期"`
	ProductName   string `description:"客户类型名称"`
}

type WxGetUserInfoReq struct {
	RawData       string `description:"rawData"`
	Signature     string `description:"signature"`
	EncryptedData string `description:"encryptedData"`
	Iv            string `description:"iv"`
}

//修改用户会话key
func ModifyWxUserSessionKey(sessionKey string, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET session_key=? WHERE user_id=? `
	_, err = o.Raw(sql, sessionKey, userId).Exec()
	return
}

//添加用户信息
func ModifyWxUserInfo(unionId, nickName, province, city, country, avatar string, gender, userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET union_id=?,unionid=?,nick_name=?,sex=?,province=?,city=?,country=?,headimgurl=? WHERE user_id=? `
	_, err = o.Raw(sql, unionId, unionId, nickName, gender, province, city, country, avatar, userId).Exec()
	return
}

//修改用户会话key
func DeleteWxUserByUserId(userId int) (err error) {
	o := orm.NewOrm()
	sql := `DELETE FROM wx_user WHERE user_id=? `
	_, err = o.Raw(sql, userId).Exec()
	return
}

type WxGetUserInfoResp struct {
	//UsersId       int       `orm:"column(id);pk"`
	//Mobile        string    `description:"手机号"`
	//NickName      string    `description:"昵称"`
	//Gender        int       `description:"用户性别 1:男性,2:女性，0:未知(默认)"`
	//CreateTime    time.Time `description:"注册时间"`
	//ModifyTime    time.Time `description:"修改时间"`
	//AvatarUrl     string    `description:"头像地址"`
	//City          string    `description:"城市"`
	//Province      string    `description:"省"`
	//Country       string    `description:"国家"`
	//Language      string    `description:"语言"`
	//Appid         string    `description:"Appid"`
	//Timestamp     int64     `description:"时间戳"`
	Authorization string `description:"登陆凭证，后续接口调用时，带在请求头里面Key:Authorization"`
}

type WxGetPhoneNumberReq struct {
	EncryptedData string `description:"encryptedData"`
	Iv            string `description:"iv"`
}

func ModifyUsersMobile(usersId int, phoneNumber string) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET mobile=? WHERE user_id=? `
	_, err = o.Raw(sql, phoneNumber, usersId).Exec()
	return
}

type WxGetPhoneNumberResp struct {
	PhoneNumber     string `description:"用户绑定的手机号（国外手机号会有区号）"`
	PurePhoneNumber string `description:"没有区号的手机号"`
	CountryCode     string `description:"区号"`
}

//根据用户手机号获取相关信息
func GetWxUserItemByMobile(mobile string) (item *WxUserItem, err error) {
	sql := `SELECT a.*,b.company_name FROM wx_user AS a
			LEFT JOIN company AS b on a.company_id=b.company_id
			WHERE a.mobile=  '` + mobile + `'  ORDER BY a.company_id DESC LIMIT 1 `
	err = orm.NewOrm().Raw(sql).QueryRow(&item)
	return
}

func GetWxUserItemByEmail(email string) (item *WxUserItem, err error) {
	sql := `SELECT * FROM wx_user WHERE email=? `
	err = orm.NewOrm().Raw(sql, email).QueryRow(&item)
	return
}

func ModifyReportLastViewTime(uid int) (err error) {
	sql := ` UPDATE wx_user SET report_last_view_time=NOW()
			WHERE user_id=? `
	_, err = orm.NewOrm().Raw(sql, uid).Exec()
	return
}

//变更联系人是否已注册状态
func ModifyWxUserRegisterStatus(userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET is_register=?,source=3,register_time=NOW() WHERE user_id = ? `
	_, err = o.Raw(sql, 1, userId).Exec()
	return
}

//修改用户是否绑定外呼手机号弹窗
func ModifyWxUserIsMsgOutboundMobile(userId int) (err error) {
	o := orm.NewOrm()
	sql := `UPDATE wx_user SET is_msg_outbound_mobile=1 WHERE user_id=? `
	_, err = o.Raw(sql, userId).Exec()
	return
}

//列表
func GetUserListAll() (items []*WxUserItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM wx_user  WHERE mobile <>'' AND outbound_mobile = ''`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

//修改手机号区号  8位号码+852，9位号码+886,10位号码+1,11位及以上号码+86
func UPdateUserCountryCode(item *WxUserItem) (err error) {
	o := orm.NewOrm()
	if item.CountryCode == "" && len(item.Mobile) >= 11 {
		sql := ` UPDATE wx_user SET  outbound_mobile= ? , outbound_country_code=86 , country_code=86  WHERE user_id = ?`
		_, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
	} else if item.CountryCode == "" && len(item.Mobile) == 8 {
		sql := ` UPDATE wx_user SET  outbound_mobile= ? , outbound_country_code=852 , country_code=852  WHERE user_id = ?`
		_, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
	} else if item.CountryCode == "" && len(item.Mobile) == 9 {
		sql := ` UPDATE wx_user SET  outbound_mobile= ? , outbound_country_code=886 , country_code=886  WHERE user_id = ?`
		_, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
	} else if item.CountryCode == "" && len(item.Mobile) == 10 {
		sql := ` UPDATE wx_user SET  outbound_mobile= ? , outbound_country_code=1 , country_code=1  WHERE user_id = ?`
		_, err = o.Raw(sql, item.Mobile, item.UserId).Exec()
	} else {
		sql := ` UPDATE wx_user SET  outbound_mobile= ? , outbound_country_code=?  WHERE user_id = ?`
		_, err = o.Raw(sql, item.Mobile, item.CountryCode, item.UserId).Exec()
	}

	return
}

//判断公司下用户名称是否存在
func GetUserCountByName(companyId int, name string) (count int, err error) {
	sql := `SELECT  COUNT(1) AS count FROM wx_user WHERE company_id = ? AND real_name = ?`
	err = orm.NewOrm().Raw(sql, companyId, name).QueryRow(&count)
	return
}

//判断这个用户是否被设置消息提醒
func GetUserRemind(uid int) (count int, err error) {
	sql := `SELECT  COUNT(1) AS count FROM cygx_user_remind WHERE user_id = ? `
	err = orm.NewOrm().Raw(sql, uid).QueryRow(&count)
	return
}

//判断公司下用户名称是否存在
func GetUserCountByThirdName(companyId int, name string) (count int, err error) {
	sql := `SELECT  COUNT(1) AS count FROM wx_user WHERE company_id = ? AND tripartite_code = ?`
	err = orm.NewOrm().Raw(sql, companyId, name).QueryRow(&count)
	return
}

func UpdateUserMobile(uid int, mobile string) (err error) {
	sql := ` UPDATE wx_user SET mobile=?
			WHERE user_id=? `
	_, err = orm.NewOrm().Raw(sql, mobile, uid).Exec()
	return
}

//获取公司下用户详情详情详情
func GetUserByName(companyId int, name string) (item *WxUser, err error) {
	o := orm.NewOrm()
	sql := `SELECT *  FROM wx_user WHERE company_id = ? AND real_name = ? `
	err = o.Raw(sql, companyId, name).QueryRow(&item)
	return
}

//获取公司下用户详情详情详情
func GetUserByThirdName(companyId int, name string) (item *WxUser, err error) {
	o := orm.NewOrm()
	sql := `SELECT *  FROM wx_user WHERE company_id = ? AND tripartite_code = ? `
	err = o.Raw(sql, companyId, name).QueryRow(&item)
	return
}

//获取所有注册的权益用户
func GetUserRegisterList() (items []*WxUser, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			u.user_id,
			u.company_id,
			u.mobile,
			u.email,
			u.real_name,
			u.is_register,
			u.is_maker,
			u.register_time 
		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 
		WHERE
			u.company_id IN (
			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 
				AND b.STATUS IN ( '正式', '试用', '冻结' ) 
				AND cp.product_id = 2 
			)
			AND cp.product_id = 2
		GROUP BY
			u.user_id `
	_, err = o.Raw(sql).QueryRows(&items)
	//AND u.register_time IS NOT NULL OR u.report_last_view_time <>'' 统计阅读时间与注册时间不为空的用户
	return
}

type CygxCompanyUser struct {
	Labels string `description:"标签，用英文,隔开"`
}
type UserLabel struct {
	Label string `description:"标签 多个用  , 隔开"`
}

//对获取关注的产业
func GetCygxCompanyUserListSplit(userIds string) (labels string, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			(
			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_industry_fllow AS f WHERE f.user_id = u.user_id  AND f.type = 1 ) 
			) AS labels
		FROM
			wx_user AS u WHERE  u.user_id  = ? `
	err = o.Raw(sql, userIds).QueryRow(&labels)
	return
}

//阅读记录
type ArticlePvCountResp struct {
	ArticleId int `description:"文章ID"`
	Pv        int `description:"Pv"`
}

func GetArticlePvCount(mobile, email, dateTime string) (item []*ArticlePvCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			article_id,
			COUNT(article_id) as pv
		FROM
			cygx_article_history_record_newpv as h 
		WHERE
			mobile = ? 
			AND email = ?  
			AND create_time >= ?
			GROUP BY article_id `
	_, err = o.Raw(sql, mobile, 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.NewOrm()
	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_newpv 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 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.NewOrm()
	err = o.Raw(sql, uid, dateTime).QueryRow(&articleIds)
	return
}

//活动标签记录
type ActivityLabelCountResp struct {
	Label string `description:"标签"`
	Pv    int    `description:"Pv"`
}

func GetActivitySignCount(mobile, dateTime string) (item []*ActivityLabelCountResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			label ,
			COUNT( label ) AS pv 
		FROM
			cygx_activity 
		WHERE
			activity_id IN ( SELECT activity_id FROM cygx_activity_signup WHERE mobile = ? )
			AND label != ''  
			AND activity_time >= ? 
			AND  is_submit_meeting = 1
		GROUP BY
			label`
	_, err = o.Raw(sql, mobile, dateTime).QueryRows(&item)
	return
}

type CygxUserInteractionNumDetail 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:"互动量"`
	Labels              string `description:"标签，用英文,隔开"`
	ActivityLabel       string `description:"活动标签，用英文,隔开"`
	IsShowSee           bool   `description:"是否展示查看"`
	IsShowSeeNum        int    `description:"是否展示查看"`
	HistoryNum          int    `description:"报告阅读"`
	CountNum            int    `description:"报告收藏"`
	ChartCountNum       int    `description:"图表收藏"`
	IndustryFllowNum    int    `description:"产业关注"`
	DepartmentFollowNum int    `description:"作者关注"`
	KeyWordNum          int    `description:"搜索关键词"`
	OnLineNum           int    `description:"线上互动活动"`
	OfficeNum           int    `description:"线下互动活动"`
	PackageType         int    `description:"套餐类型，0：无，1：大套餐，2：小套餐"`
	TryStage            int    `description:"试用客户子标签：0全部、1未分类、2 推进、3 跟踪、4 预备"`
}

//获取用户的互动量
func GetCygxCompanyUserUserInteraction(userIds string) (items []*CygxUserInteractionNumDetail, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			u.user_id,
			u.company_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_count_num,
			( SELECT COUNT( 1 ) FROM cygx_industry_fllow AS h WHERE h.user_id = u.user_id  ) AS industry_fllow_num,
			( SELECT COUNT( 1 ) FROM cygx_article_department_follow AS h WHERE h.user_id = u.user_id   ) 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( 1 ) FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id   WHERE h.mobile = u.mobile  
					 AND t.activity_type = 1  AND h.do_fail_type = 0 ) AS on_line_num,
			( SELECT COUNT( 1 ) FROM cygx_activity_signup AS h INNER JOIN cygx_activity as a ON a.activity_id = h.activity_id  INNER JOIN cygx_activity_type as t ON t.activity_type_id = a.activity_type_id   WHERE h.mobile = u.mobile  
					  AND t.activity_type = 0 AND h.do_fail_type = 0 ) AS office_num,
			(
			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_industry_fllow AS f WHERE f.user_id = u.user_id  AND f.type = 1 ) 
			) AS labels,
			(
			SELECT
				GROUP_CONCAT( DISTINCT man.label SEPARATOR ',' ) 
			FROM
				cygx_activity AS man 
			WHERE
				man.activity_id IN ( SELECT activity_id FROM cygx_activity_signup AS f WHERE f.user_id = u.user_id  AND label != '') 
			) AS activity_label
		FROM
			wx_user AS u WHERE  u.user_id IN( ` + userIds + `) `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}