package models
import (
"github.com/beego/beego/v2/client/orm"
"github.com/rdlucklib/rdluck_tools/paging"
"hongze/hz_crm_api/utils"
"time"
)
type WxUser struct {
UserId int64 `orm:"column(user_id);pk"`
Mobile string
Email string
CompanyId int
RealName string `description:"姓名"`
NickName string `description:"昵称"`
CreatedTime time.Time
MobileTwo string `description:"备用手机号"`
BusinessCardUrl string `description:"名片"`
IsMaker int `description:"是否决策人，1:是，0:否"`
Position string `description:"职位"`
Sex int `description:"普通用户性别，1为男性，2为女性"`
DepartmentName string `description:"联系人部门"`
RegisterTime time.Time
RegisterPlatform int
Remark string `description:"备注"`
CountryCode string `description:"区号，86、852、886等"`
OutboundMobile string `description:"外呼手机号"`
OutboundCountryCode string `description:"外呼手机号区号，86、852、886等"`
LastUpdatedTime time.Time `description:"最近一次更新时间"`
IsDeal int `description:"是否标记处理 0-未处理 1-已处理"`
OpenId string `orm:"column(open_id)" description:"微信openid"`
Headimgurl string `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"`
UserLabel string `description:"查研观向用户标签"`
MarkGroup string `description:"标记分组"`
FiccViewTotal int `description:"ficc报告的阅读次数"`
FiccLastViewTime string `description:"ficc报告最近一次阅读时间"`
RaiViewTotal int `description:"权益报告的阅读次数"`
RaiLastViewTime string `description:"权益报告的最近一次阅读时间"`
}
func AddWxUser(item *WxUser) (lastId int64, err error) {
o := orm.NewOrm()
lastId, err = o.Insert(item)
return
}
func GetWxUserByMobile(mobile string) (item *WxUser, err error) {
o := orm.NewOrm()
sql := `SELECT * FROM wx_user WHERE mobile = ? LIMIT 1`
err = o.Raw(sql, mobile).QueryRow(&item)
return
}
// GetWxUserByMobileCountryCode 根据手机号和区号获取用户信息
func GetWxUserByMobileCountryCode(mobile, countryCode string) (item *WxUser, err error) {
o := orm.NewOrm()
sql := `SELECT * FROM wx_user WHERE mobile = ? `
sql += ` and country_code in ("","` + countryCode + `") `
sql += ` LIMIT 1 `
err = o.Raw(sql, mobile).QueryRow(&item)
return
}
func GetWxUserByUserId(userId int) (item *WxUser, err error) {
o := orm.NewOrm()
sql := `SELECT * FROM wx_user WHERE user_id=? `
err = o.Raw(sql, userId).QueryRow(&item)
return
}
// 更新wxUser信息
func (wxUser *WxUser) Update(cols []string) (err error) {
o := orm.NewOrm()
_, err = o.Update(wxUser, cols...)
return
}
type PotentialUserItem struct {
UserId int `description:"用户id"`
RealName string `description:"姓名"`
CountryCode string `description:"区号，86、852、886等"`
Mobile string `description:"手机号"`
Email string `description:"邮箱"`
CreatedTime string `description:"注册时间"`
ApplyMethod int `description:"0:未申请，1:已付费客户申请试用，2:非客户申请试用"`
CompanyName string `description:"客户名称"`
ViewTotal int `description:"累计阅读次数"`
LastViewTime time.Time `json:"-" description:"最后一次阅读时间"`
LastViewTimeStr string `description:"最后一次阅读时间"`
FromType string `description:"report:研报，teleconference:电话会"`
BusinessCardUrl string `description:"名片"`
Source int `description:"来源,1:微信端,2:pc网页端，3:查研观向小程序,4:每日咨询，5:电话会"`
IsDeal int `description:"是否标记处理，0是未处理，1是已处理"`
}
type PotentialUserListResp struct {
List []*PotentialUserItem
Paging *paging.PagingItem `description:"分页数据"`
}
// 联系人导入预览数据返回
type ImportListResp struct {
ValidUser []*WxUser `description:"有效客户数据"`
RepeatUser []*WxUser `description:"重复客户数据"`
}
func GetPotentialUserListCount(condition string, pars []interface{}) (count int, err error) {
o := orm.NewOrm()
sql := `SELECT COUNT(1) AS count
FROM wx_user AS a
LEFT JOIN company AS b ON a.company_id = b.company_id
LEFT JOIN user_record AS c ON a.user_id=c.user_id
WHERE b.enabled = 1 AND b.company_id = 1
AND (a.mobile IS NOT NULL || a.email IS NOT NULL)
AND (a.mobile<>'' OR a.email<>'')
AND (c.create_platform<>4 or c.create_platform is null)`
if condition != "" {
sql += condition
}
err = o.Raw(sql, pars).QueryRow(&count)
return
}
func GetPotentialUserList(condition string, pars []interface{}, startSize, pageSize int) (items []*PotentialUserItem, err error) {
o := orm.NewOrm()
sql := `SELECT a.*,a.note AS company_name,
IF(b.type IN (1,2),1,0) AS is_fee_customer,
(SELECT count(1) FROM user_view_history AS uvh WHERE uvh.user_id=a.user_id GROUP BY a.user_id) AS view_total,
a.report_last_view_time AS last_view_time_str,
c.create_platform
FROM wx_user AS a
LEFT JOIN company AS b ON a.company_id = b.company_id
LEFT JOIN user_record AS c ON a.user_id=c.user_id
WHERE b.enabled = 1 AND b.company_id = 1
AND (a.mobile IS NOT NULL || a.email IS NOT NULL)
AND (a.mobile<>'' OR a.email<>'')
AND (c.create_platform<>4 or c.create_platform is null) `
if condition != "" {
sql += condition
}
sql += ` GROUP BY a.user_id `
sql += `ORDER BY a.created_time DESC LIMIT ?,? `
_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
return
}
type PotentialUserDeleteReq struct {
UserId int `description:"用户id"`
}
// 标记潜在用户请求
type PotentialUserDealReq struct {
UserId int `description:"用户id"`
}
func DeletePotentialUser(userId int) (err error) {
sql := ` DELETE FROM wx_user WHERE user_id=? `
_, err = orm.NewOrm().Raw(sql, userId).Exec()
return
}
// DeleteWxUserByUserIds 根据用户id字符串批量删除用户信息
func DeleteWxUserByUserIds(userIds string) (err error) {
sql := ` DELETE FROM wx_user WHERE user_id in (` + userIds + `) `
_, err = orm.NewOrm().Raw(sql).Exec()
return
}
// 移动潜在客户名下的联系人 请求数据
type PotentialUserMoveReq struct {
UserId int `description:"用户id"`
CompanyId int `description:"移动到的客户id"`
SellerId int `description:"需要移动到的销售id"`
RealName string `description:"姓名"`
Mobile string `description:"手机号"`
Email string `description:"邮箱"`
Remark string `description:"备注信息"`
//crm4.0新增校验字段
Sex int `description:"用户性别，1为男性，2为女性"`
RegionType string `description:"区域，枚举值：国内、海外"`
Position string `description:"职位"`
IsMaker int `description:"是否决策人，1:是，0:否"`
BusinessCardUrl string `description:"名片地址"`
DepartmentName string `description:"联系人部门"`
CountryCode string `description:"区号，86、852、886等"`
}
// 移动联系人请求数据
type UserMoveReq struct {
UserId int `description:"用户id"`
CompanyId int `description:"移动到的客户id"`
SellerId int `description:"需要移动到的销售id"`
}
// FICC申请单-移动潜在客户名下的联系人 请求数据
type ApplyRecordPotentialUserMoveReq struct {
UserId int `description:"用户id"`
CompanyId int `description:"移动到的客户id"`
SellerId int `description:"需要移动到的销售id"`
RealName string `description:"姓名"`
Mobile string `description:"手机号"`
Email string `description:"邮箱"`
Remark string `description:"备注信息"`
ApplyRecordId int `description:"申请单ID"`
Sex int `description:"用户性别，1为男性，2为女性"`
RegionType string `description:"区域，枚举值：国内、海外"`
Position string `description:"职位"`
IsMaker int `description:"是否决策人，1:是，0:否"`
BusinessCardUrl string `description:"名片地址"`
DepartmentName string `description:"联系人部门"`
CountryCode string `description:"区号，86、852、886等"`
}
func CheckUserMobileIsRegister(userId int, mobile string) (count int, err error) {
sql := `SELECT COUNT(1) AS count FROM wx_user AS a
INNER JOIN company AS b ON a.company_id = b.company_id
WHERE a.mobile = ? AND a.user_id<> ? `
o := orm.NewOrm()
err = o.Raw(sql, mobile, userId).QueryRow(&count)
return
}
func CheckUserEmailIsRegister(userId int, email string) (count int, err error) {
sql := `SELECT COUNT(1) AS count FROM wx_user AS a
INNER JOIN company AS b ON a.company_id = b.company_id
WHERE a.email = ? AND a.user_id<> ? `
o := orm.NewOrm()
err = o.Raw(sql, email, userId).QueryRow(&count)
return
}
// 移动潜在客户名下的联系人
func MovePotentialUser(userId, companyId int, realName, mobile, email, remark string) (err error) {
if realName != "" {
sql := ` UPDATE wx_user
SET
company_id = ?,
real_name = ?,
mobile = ?,
email = ?,
remark = ?,
last_updated_time = NOW(),
is_note = 1
WHERE user_id = ? `
_, err = orm.NewOrm().Raw(sql, companyId, realName, mobile, email, remark, userId).Exec()
} else {
sql := ` UPDATE wx_user
SET
company_id = ?,
mobile = ?,
email = ?,
remark = ?,
last_updated_time = NOW(),
is_note = 1
WHERE user_id = ? `
_, err = orm.NewOrm().Raw(sql, companyId, mobile, email, remark, userId).Exec()
}
return
}
// 移动联系人
func MoveUser(userId, companyId int) (err error) {
sql := ` UPDATE wx_user
SET
company_id = ?,
last_updated_time = NOW(),
is_note = 1
WHERE user_id = ? `
_, err = orm.NewOrm().Raw(sql, companyId, userId).Exec()
return
}
type ReportViewRecordTotal struct {
ViewTotal int
LastViewTime time.Time
}
func GetReportViewMaxTime(uid int) (item *ReportViewRecordTotal, err error) {
o := orm.NewOrmUsingDB("rddp")
rddpSql := "SELECT MAX(create_time) AS last_view_time,COUNT(1) AS view_total FROM report_view_record WHERE user_id=? "
err = o.Raw(rddpSql, uid).QueryRow(&item)
return
}
// 根据用户id字符串获取用户的浏览数
func GetReportViewMaxTimeByUserIds(userIds string) (items []*ReportViewRecord, err error) {
o := orm.NewOrmUsingDB("rddp")
rddpSql := `SELECT user_id,MAX(create_time) AS last_view_time,COUNT(1) AS view_total FROM report_view_record WHERE user_id in (` + userIds + `) group by user_id`
_, err = o.Raw(rddpSql).QueryRows(&items)
return
}
// 根据用户手机号字符串获取用户的浏览数
type ReportViewMobileRecord struct {
Mobile string
ViewTotal int
LastViewTime time.Time
}
func GetReportViewMaxTimeByMobiles(mobiles string) (items []*ReportViewMobileRecord, err error) {
o := orm.NewOrmUsingDB("rddp")
rddpSql := `SELECT mobile,MAX(create_time) AS last_view_time,COUNT(1) AS view_total FROM report_view_record WHERE mobile in (` + mobiles + `) group by mobile`
_, err = o.Raw(rddpSql).QueryRows(&items)
return
}
// 根据用户邮箱字符串获取用户的浏览数
type ReportViewEmailRecord struct {
Email string
ViewTotal int
LastViewTime time.Time
}
func GetReportViewMaxTimeByEmails(emails string) (items []*ReportViewEmailRecord, err error) {
o := orm.NewOrmUsingDB("rddp")
rddpSql := `SELECT email,MAX(create_time) AS last_view_time,COUNT(1) AS view_total FROM report_view_record WHERE email in (` + emails + `) group by email`
_, err = o.Raw(rddpSql).QueryRows(&items)
return
}
func GetPotentialUserListExport(condition string, pars []interface{}) (items []*PotentialUserItem, err error) {
o := orm.NewOrm()
sql := `SELECT DISTINCT a.*,b.company_name,
IF(b.type IN (1,2),1,0) AS is_fee_customer,
(SELECT count(1) FROM user_view_history AS uvh WHERE uvh.user_id=a.user_id GROUP BY a.user_id) AS view_total,
(SELECT max(uvh.created_time) FROM user_view_history AS uvh WHERE uvh.user_id=a.user_id GROUP BY a.user_id) AS last_view_time
FROM wx_user AS a
LEFT JOIN company AS b ON a.company_id = b.company_id
INNER JOIN user_record AS c ON a.user_id=c.user_id
WHERE b.enabled = 1 AND b.company_id = 1
AND (a.mobile IS NOT NULL || a.email IS NOT NULL)
AND (a.mobile<>'' OR a.email<>'')
AND c.create_platform<>4 `
if condition != "" {
sql += condition
}
sql += `ORDER BY a.last_updated_time DESC `
_, err = o.Raw(sql, pars).QueryRows(&items)
return
}
type WxUserDetail struct {
UserId int64 `orm:"column(user_id);pk"`
CountryCode string `description:"区号，86、852、886等"`
Mobile string
Email string
CompanyId int
RealName string `description:"姓名"`
CreatedTime time.Time
MobileTwo string `description:"备用手机号"`
BusinessCardUrl string `description:"名片"`
IsMaker int `description:"是否决策人，1:是，0:否"`
Position string `description:"职位"`
Sex int `description:"普通用户性别，1为男性，2为女性"`
CompanyName string `description:"公司名称"`
DepartmentName string `description:"联系人部门"`
SellerRealName string `description:"销售"`
}
// GetWxUserListByUserIds 根据用户ID集合获取用户
func GetWxUserListByUserIds(userIds string) (list []*WxUser, err error) {
o := orm.NewOrm()
sql := ` SELECT * FROM wx_user WHERE user_id IN (` + userIds + `) `
_, err = o.Raw(sql).QueryRows(&list)
return
}
// GetWxUserByCompanyIdAndMobile 根据客户ID及手机号获取用户
func GetWxUserByCompanyIdAndMobile(companyId int, mobile string) (item *WxUser, err error) {
o := orm.NewOrm()
sql := ` SELECT * FROM wx_user WHERE company_id = ? AND mobile = ? LIMIT 1 `
err = o.Raw(sql, companyId, mobile).QueryRow(&item)
return
}
// DeleteWxUserAndRecordByUserId 删除用户及第三方信息
func DeleteWxUserAndRecordByUserId(userId int) (err error) {
o := orm.NewOrm()
to, err := o.Begin()
if err != nil {
return
}
defer func() {
if err != nil {
_ = to.Rollback()
} else {
_ = to.Commit()
}
}()
// 删除wx_user
userSql := ` DELETE FROM wx_user WHERE user_id = ? LIMIT 1 `
_, err = to.Raw(userSql, userId).Exec()
// 删除user_record
if err == nil {
recordSql := ` DELETE FROM user_record WHERE user_id = ? `
_, err = to.Raw(recordSql, userId).Exec()
}
return
}
// 获取这个公司下面所有用户的手机号
func GetCompanyUserMobilesByCompanyId(companyId int) (mobiles string, err error) {
o := orm.NewOrm()
sql := ` SELECT GROUP_CONCAT( DISTINCT a.mobile SEPARATOR ',' ) AS mobiles FROM
wx_user AS a WHERE company_id = ? AND mobile != '' `
err = o.Raw(sql, companyId).QueryRow(&mobiles)
return
}
// 获取这个公司下面所有用户的邮箱号
func GetCompanyUserEmailsByCompanyId(companyId int) (emails string, err error) {
o := orm.NewOrm()
sql := ` SELECT GROUP_CONCAT( DISTINCT a.email SEPARATOR ',' ) AS emails FROM
wx_user AS a WHERE company_id = ? AND email != '' `
err = o.Raw(sql, companyId).QueryRow(&emails)
return
}
// 获取这个公司下面所有用户的手机号
func SetUserSubscribe(openId string) (err error) {
o := orm.NewOrm()
sql := ` UPDATE user_record SET subscribe=1,subscribe_time=NOW() WHERE open_id=? `
_, err = o.Raw(sql, openId).Exec()
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:"是否弹窗过绑定外呼手机号区号"`
IsMaker int `description:"是否是决策人"`
Source int
Position string `description:"职位"`
}
// GetWxUserListByUserIds 根据用户ID集合获取用户有公司名称
func GetWxUserListByUserIdsHaveCompany(userIds string) (list []*WxUserItem, err error) {
o := orm.NewOrm()
sql := ` SELECT u.*, c.company_name FROM wx_user AS u
INNER JOIN company AS c ON c.company_id = u.company_id
WHERE user_id IN (` + userIds + `) `
_, err = o.Raw(sql).QueryRows(&list)
return
}
// GetWxUserByAdminId 通过后台用户ID及微信平台获取对应的微信用户
func GetWxUserByAdminId(platform, adminId int) (item *WxUser, err error) {
o := orm.NewOrm()
sql := `SELECT
wu.user_id
FROM
wx_user AS wu
JOIN user_record AS ur ON wu.user_id = ur.user_id AND ur.create_platform = ?
JOIN admin AS ad ON wu.mobile = ad.mobile
WHERE
ad.admin_id = ?`
err = o.Raw(sql, platform, adminId).QueryRow(&item)
return
}
// GetWxUserByOutboundMobiles 根据用户手机号获取用户详情
func GetWxUserByOutboundMobiles(mobiles []string) (items []*WxUser, err error) {
lenmobiles := len(mobiles)
if lenmobiles == 0 {
return
}
sql := `SELECT* FROM wx_user WHERE outbound_mobile in (` + utils.GetOrmInReplace(lenmobiles) + `) `
o := orm.NewOrm()
_, err = o.Raw(sql, mobiles).QueryRows(&items)
return
}
// GetWxUserByUserIds 根据用户ID获取用户详情
func GetWxUserByUserIds(userIds []int) (items []*WxUser, err error) {
lenuserIds := len(userIds)
if lenuserIds == 0 {
return
}
sql := `SELECT* FROM wx_user WHERE user_id in (` + utils.GetOrmInReplace(lenuserIds) + `) `
o := orm.NewOrm()
_, err = o.Raw(sql, userIds).QueryRows(&items)
return
}
// GetWxUserListByUserMobileHaveCompany 根据用户手机号集合获取用户有公司名称
func GetWxUserListByUserMobileHaveCompany(mobiles []string) (list []*WxUserItem, err error) {
lenmobiles := len(mobiles)
if lenmobiles == 0 {
return
}
o := orm.NewOrm()
sql := ` SELECT u.*, c.company_name FROM wx_user AS u
INNER JOIN company AS c ON c.company_id = u.company_id
WHERE mobile in (` + utils.GetOrmInReplace(lenmobiles) + `) `
_, err = o.Raw(sql, mobiles).QueryRows(&list)
return
}
// GetWxUserListCompanyId 根据公司ID获取所有用户信息
func GetWxUserListCompanyId(companyId int) (list []*WxUserItem, err error) {
o := orm.NewOrm()
sql := ` SELECT* FROM wx_user WHERE company_id = ? `
_, err = o.Raw(sql, companyId).QueryRows(&list)
return
}
type WxUserSller struct {
UserId int `description:"用户id"`
RealName string `description:"姓名"`
Mobile string `description:"手机号"`
CompanyId int `description:"公司id"`
CompanyName string `description:"公司名称"`
SellerName string `description:"所属销售"`
}
// GetWxUserRaiSllerListByUserIds 根据用户id获取权益相关用户信息
func GetWxUserRaiSllerListByUserIds(userIds []int) (list []*WxUserSller, err error) {
lenarr := len(userIds)
if lenarr == 0 {
return
}
o := orm.NewOrm()
sql := ` SELECT
w.user_id,
w.real_name,
w.mobile,
c.company_name,
p.seller_name
FROM
wx_user AS w
INNER JOIN company_product AS p ON p.company_id = w.company_id
INNER JOIN company AS c ON c.company_id = w.company_id
WHERE
1 = 1
AND p.product_id = 2
AND w.user_id IN (` + utils.GetOrmInReplace(lenarr) + `) `
_, err = o.Raw(sql, userIds).QueryRows(&list)
return
}
// GetWxUserRaiSllerListByUserMobile 根据用户手机号获取权益相关用户信息
func GetWxUserRaiSllerListByUserMobile(mobiles []string) (list []*WxUserSller, err error) {
lenarr := len(mobiles)
if lenarr == 0 {
return
}
o := orm.NewOrm()
sql := ` SELECT
w.user_id,
w.real_name,
w.mobile,
c.company_name,
c.company_id,
p.seller_name
FROM
wx_user AS w
INNER JOIN company_product AS p ON p.company_id = w.company_id
INNER JOIN company AS c ON c.company_id = w.company_id
WHERE
1 = 1
AND p.product_id = 2
AND w.mobile IN (` + utils.GetOrmInReplace(lenarr) + `) `
_, err = o.Raw(sql, mobiles).QueryRows(&list)
return
}
// GetWxUserByCompanyIds 根据客户ID集合获取用户列表
func GetWxUserByCompanyIds(companyIds []int) (items []*WxUser, err error) {
num := len(companyIds)
if num == 0 {
return
}
sql := `SELECT * FROM wx_user WHERE company_id in (` + utils.GetOrmInReplace(num) + `) `
o := orm.NewOrm()
_, err = o.Raw(sql, companyIds).QueryRows(&items)
return
}
// GetWxUserList 用户列表查询
func GetWxUserList(condition string, pars []interface{}) (items []*WxUser, err error) {
o := orm.NewOrm()
sql := `SELECT *
FROM wx_user WHERE 1 = 1 `
if condition != "" {
sql += condition
}
_, err = o.Raw(sql, pars).QueryRows(&items)
return
}
func SetUserFollow(userId, follow int) (err error) {
o := orm.NewOrm()
sql := ` UPDATE wx_user SET is_follow=? WHERE user_id=? `
_, err = o.Raw(sql, follow, userId).Exec()
return
}
func GetCompanyFollowCount(companyId int) (count int, err error) {
o := orm.NewOrm()
sql := ` SELECT COUNT(1) FROM wx_user WHERE company_id=? AND is_follow = 1 `
err = o.Raw(sql, companyId).QueryRow(&count)
return
}
// 根据用户ID获取相关信息
func GetWxUserItemByUserId(userId int) (item *WxUserItem, err error) {
o := orm.NewOrm()
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 = o.Raw(sql, userId).QueryRow(&item)
return
}