hz_crm_api/models/wx_user.go

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:"查研观向用户标签"`
	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
}

// 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
}

// 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
}