hongze_task/models/users.go

package models

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

type HongzeUsers struct {
	RealName    string
	CompanyName string
	Mobile      string
	IsRegister  string
	UserType    string
}

func GetHongzeUsers() (items []*HongzeUsers, err error) {
	sql := `select wu.real_name,c.company_name,left(wu.mobile, 11) as mobile ,if(wu.open_id='','否','是') as is_register,if(c.type in (1),'是','否') as user_type
	from wx_user wu
	inner join company c on c.company_id = wu.company_id
	where c.company_id <> 1 and c.company_id <> 16
	and c.type in (1)
	order by company_name desc `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&items)
	return
}

type WxUser struct {
	ExpiredIn           int64
	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正方形头像），用户没有头像时该项为空"`
}

func GetWxUserByUserId(userId int) (item *WxUser, err error) {
	sql := `SELECT * FROM wx_user WHERE user_id=? `
	err = orm.NewOrm().Raw(sql, userId).QueryRow(&item)
	return
}

func GetWxUserByMobile(mobile string) (item *WxUser, err error) {
	sql := `SELECT * FROM wx_user WHERE mobile=? `
	err = orm.NewOrm().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 AddWxUser(companyId int, mobile, realName, email string) (err error) {
	sql := `INSERT INTO wx_user(company_id,real_name,mobile,first_login,enabled,is_note,from_type,apply_method,email) VALUES (?,?,?,1,1,1,'report',0,?);`
	orm.NewOrm().Raw(sql, companyId, realName, mobile, email).Exec()
	return
}

func GetPotentialUser() (items []*WxUser, err error) {
	sql := ` SELECT * FROM wx_user WHERE company_id=1 AND report_last_view_time <> '' `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetMaxReviewTime(uid int) (max_time string, err error) {
	o := orm.NewOrm()
	sql := `SELECT MAX(a.max_time) AS max_time FROM (
			SELECT MAX(created_time)AS max_time FROM user_view_history AS a WHERE user_id=?
			UNION 
			SELECT MAX(create_time)AS max_time FROM  hongze_rddp.report_view_record AS a WHERE user_id=?
			)AS a`
	err = o.Raw(sql, uid, uid).QueryRow(&max_time)
	return
}

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

// GetUserOpenidListByUserId 根据用户id来获取他的openid列表集合
func GetUserOpenidListByUserId(userId int) (list []*OpenIdList, err error) {
	sql := `SELECT open_id FROM user_record WHERE user_id = ? and create_platform = 1`
	_, err = orm.NewOrm().Raw(sql, userId).QueryRows(&list)
	return
}

type UserMobileList struct {
	UserId int
	Mobile string
	CountryCode string
	Email string
}

// GetUserMobileFormal
func GetUserMobileFormal() (list []*UserMobileList, err error) {
	sql := `SELECT
	a.user_id,a.mobile,a.country_code,a.email 
FROM
	wx_user AS a
	INNER JOIN company_product AS p ON a.company_id = p.company_id 
WHERE
	p.status IN (
		'试用',
	'永续',
	'正式') AND p.product_id = 1 `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&list)
	return
}

// GetUserOpenidListByUserIds 根据用户id字符串集合来获取他的openid列表集合
func GetUserOpenidListByUserIds(userIdStr []string) (list []*OpenIdList, err error) {
	if len(userIdStr) <= 0 {
		return
	}
	sql := `SELECT open_id FROM user_record WHERE user_id in (` + strings.Join(userIdStr, ",") + `) and create_platform = 1`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&list)
	return
}

// GetUserMobileNotFormal
func GetUserMobileNotFormal() (list []*UserMobileList, err error) {
	sql := `SELECT
	a.user_id,a.mobile,a.country_code,a.email 
FROM
	wx_user AS a
	INNER JOIN company_product AS p ON a.company_id = p.company_id 
WHERE
	p.status NOT IN (
		'试用',
	'永续',
	'正式') AND p.product_id = 1 `
	_, err = orm.NewOrm().Raw(sql).QueryRows(&list)
	return
}