eta_mini_crm/models/user.go

package models

import (
	"context"
	"eta/eta_mini_crm/utils"
	"fmt"
	"strings"
	"time"

	"github.com/beego/beego/v2/client/orm"
)

type User struct {
	UserId               int       `orm:"pk" description:"用户id"`
	OpenId               string    `description:"openid"`
	UnionId              string    `description:"unionid"`
	NickName             string    `description:"用户昵称"`
	RealName             string    `description:"姓名"`
	Phone                string    `description:"手机号"`
	AreaCode             string    `description:"区号"`
	Email                string    `description:"邮箱"`
	SellerId             int       `description:"销售id（SysUserId）"`
	SellerDepartmentId   int       `description:"营业部门id"`
	SellerDepartmentName string    `description:"营业部门名称"`
	Company              string    `description:"所属公司"`
	ValidStartTime       time.Time `description:"有效期开始时间"`
	ValidEndTime         time.Time `description:"有效期结束时间"`
	Status               int       `description:"用户类型: 0表示禁用，1表示潜在客户，2表示正式客户"`
	ApplyStatus          int       `description:"申请状态: 0表示未申请，1表示已申请"`
	CreateTime           time.Time `description:"系统中首次新增用户的时间"`
	ModifyTime           time.Time `description:"系统中用户信息变更的时间"`
	RegisterTime         time.Time `description:"用户首次登录小程序的时间"`
	ApplyTime            time.Time `description:"用户提交申请的时间"`
	IsSubscribed         bool      `description:"是否关注公众号: 0表示没有关注，1表示关注"`
	IsRegistered         bool      `description:"是否注册: 0表示没有注册，1表示注册"`
	AccessToken          string    `description:"用户token"`
	Headimgurl           string    `description:"用户头像，最后一个数值代表正方形头像大小（有0、46、64、96、132数值可选，0代表640*640正方形头像），用户没有头像时该项为空"`
}

type UserView struct {
	UserId               int    `orm:"pk" description:"用户id"`
	RealName             string `description:"姓名"`
	Phone                string `description:"手机号"`
	AreaCode             string `description:"区号"`
	Email                string `description:"邮箱"`
	SellerDepartmentId   int    `description:"营业部门id"`
	SellerDepartmentName string `description:"营业部门名称"`
	Company              string `description:"所属公司"`
	ValidStartTime       string `description:"有效期开始时间"`
	ValidEndTime         string `description:"有效期结束时间"`
	RestDate             int    `description:"剩余天数"`
	Status               int    `description:"用户类型: 0表示禁用，1表示潜在客户，2表示正式客户"`
	ApplyStatus          int    `description:"申请状态: 0表示未申请，1表示已申请"`
	ReadCnt              int    `description:"用户阅读量"`
	ApplyTime            string `description:"用户提交申请的时间"`
	CreateTime           string `description:"系统中首次新增用户的时间"`
	ModifyTime           string `description:"系统中用户信息变更的时间"`
	LastUpdateTime       string `description:"最近一次阅读时间"`
	RegisterTime         string `description:"用户首次登录小程序的时间"`
	IsSubscribed         bool   `description:"是否关注公众号: 0表示没有关注，1表示关注"`
	IsRegistered         bool   `description:"是否注册: 0表示没有注册，1表示注册"`
}

func (u *User) Save() (err error) {
	o := orm.NewOrm()
	_, err = o.InsertOrUpdate(u)
	return
}

func (u *User) Update(cols []string) (err error) {
	o := orm.NewOrm()
	_, err = o.Update(u, cols...)
	return
}

func UpdateUserStatus(condition string, pars []interface{}) (err error) {
	o := orm.NewOrm()
	sql := ` UPDATE user SET status=0 WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).Exec()
	return
}

func GetUserIdListByCondition(condition string, pars []interface{}) (items []int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT user_id FROM user WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func SaveUser(user *User, chartPermissionIds []int) (err error) {
	o := orm.NewOrm()
	err = o.DoTx(func(ctx context.Context, txOrm orm.TxOrmer) error {
		insertId, er := txOrm.InsertOrUpdate(user)
		if er != nil {
			return er
		}
		if user.UserId != 0 {
			insertId = int64(user.UserId)
		} else {
			user.UserId = int(insertId)
		}
		// 先删除再增加
		sql := `DELETE FROM user_chart_permission_mapping WHERE user_id=?`
		_, er = txOrm.Raw(sql, insertId).Exec()
		if er != nil {
			return er
		}
		for _, id := range chartPermissionIds {
			userChartPermissionMapping := new(UserChartPermissionMapping)
			userChartPermissionMapping.UserId = int(insertId)
			userChartPermissionMapping.ChartPermissionId = id
			_, er = txOrm.Insert(userChartPermissionMapping)
			if er != nil {
				return er

			}
		}
		return nil
	})
	return
}

func GetUserByPhone(phone, areaCode string) (item *User, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM user WHERE phone=? AND area_code=?`
	err = o.Raw(sql, phone, areaCode).QueryRow(&item)
	return
}

func GetUserByEmail(email string) (item *User, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM user WHERE email=? `
	err = o.Raw(sql, email).QueryRow(&item)
	return
}

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

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

func GetUserList(condition string, pars []interface{}, startSize, pageSize int) (items []*UserView, err error) {
	sql := `SELECT u.*, su.sys_real_name AS seller_name FROM user AS u
	LEFT JOIN sys_user AS su
	ON u.seller_id = su.sys_user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY modify_time DESC LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetUserListByConditonSort(condition, sortConditon string, pars []interface{}, startSize, pageSize int) (items []*UserView, err error) {
	sql := `SELECT u.*, su.sys_real_name AS seller_name
	FROM user AS u
	LEFT JOIN sys_user AS su
	ON u.seller_id = su.sys_user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) `
	if condition != "" {
		sql += condition
	}
	if sortConditon != "" {
		sql += sortConditon
	}
	sql += ` LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetPotentialUserCountByConditon(condition string, pars []interface{}) (count int, err error) {
	sql := `SELECT COUNT(DISTINCT u.user_id) AS count
	FROM user AS u
	LEFT JOIN user_read_record AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) AND u.status=1`
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrm()
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetPotentialUserCountByConditonV2(condition string, pars []interface{}) (count int, err error) {
	sql := `SELECT COUNT(u.user_id) AS count
	FROM user AS u
	LEFT JOIN (
		SELECT user_id, MAX(create_time) AS create_time
		FROM user_read_record
		GROUP BY user_id
	) AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) AND u.status=1`
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrm()
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetPotentialUserIdsByConditonV2(condition string, pars []interface{}, sortConditon string, startSize, pageSize int) (items []*UserView, err error) {
	sql := `SELECT DISTINCT u.*, ur.read_cnt, ur.create_time AS last_update_time
	FROM user AS u
	LEFT JOIN (
		SELECT user_id, MAX(create_time) AS create_time, COUNT(user_id) AS read_cnt
		FROM user_read_record
		GROUP BY user_id
	) AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) AND u.status=1`
	if condition != "" {
		sql += condition
	}
	if sortConditon != "" {
		sql += sortConditon
	}
	sql += ` LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetPotentialUserIdsByConditon(condition string, pars []interface{}) (userIds []string, err error) {
	sql := `SELECT DISTINCT u.user_id AS user_id
	FROM user AS u
	LEFT JOIN user_read_record AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) AND u.status=1`
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars).QueryRows(&userIds)
	return
}

func GetPotentialUserListByConditonSort(userIds []string, sortConditon string, startSize, pageSize int) (items []*UserView, err error) {
	sql := `SELECT u.*, COUNT(ur.user_id) AS read_cnt, Max(ur.create_time) AS last_update_time
	FROM user AS u
	LEFT JOIN user_read_record AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) AND u.status=1`
	if len(userIds) > 0 {
		sql += fmt.Sprintf(" AND u.user_id IN (%s)", strings.Join(userIds, ","))
	}
	sql += ` GROUP BY u.user_id`
	if sortConditon != "" {
		sql += sortConditon
	}
	sql += ` LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

func GetPotentialUserTotal() (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(*) AS count FROM user WHERE status=1`
	err = o.Raw(sql).QueryRow(&count)
	return
}

func GetUserReadList(condition, sortCondition string, pars []interface{}, startSize, pageSize int) (items []*UserView, err error) {
	sql := `SELECT u.*, su.sys_real_name AS seller_name, COUNT(ur.user_id) AS read_cnt, Max(ur.create_time) AS last_update_time
	FROM user AS u
	LEFT JOIN sys_user AS su
	ON u.seller_id = su.sys_user_id
	LEFT JOIN user_read_record AS ur
	ON u.user_id = ur.user_id
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY u.user_id `
	if sortCondition != "" {
		sql += sortCondition
	} else {
		sql += ` ORDER BY read_cnt DESC `
	}
	sql += ` LIMIT ?,? `
	o := orm.NewOrm()
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetUserReadCount(condition string, pars []interface{}) (count int, err error) {
	sql := `SELECT COUNT(*) AS count 
	FROM user  AS u
	WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) `
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrm()
	err = o.Raw(sql, pars...).QueryRow(&count)
	return
}

func GetUserCount(condition string, pars []interface{}) (count int, err error) {
	sql := `SELECT COUNT(*) AS count FROM user AS u WHERE 1=1 AND (u.phone IS NOT NULL OR u.email IS NOT NULL) `
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrm()
	err = o.Raw(sql, pars...).QueryRow(&count)
	return
}

func DeleteUserById(userId int) (err error) {
	o := orm.NewOrm()
	err = o.DoTx(func(ctx context.Context, txOrm orm.TxOrmer) error {
		sql := `DELETE FROM user WHERE user_id=?`
		_, e := txOrm.Raw(sql, userId).Exec()
		if e != nil {
			return e
		}
		sql = `DELETE FROM user_chart_permission_mapping WHERE user_id=?`
		_, e = txOrm.Raw(sql, userId).Exec()
		if e != nil {
			return e
		}
		return nil
	})
	return
}

func GetGlobalUserByCondition(userIds []int, sortCondition string, startSize, pageSize int) (items []*UserView, err error) {
	if len(userIds) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := `SELECT u.*, COUNT(ur.user_id) AS read_cnt, MAX(ur.create_time) AS last_update_time
	FROM user AS u
	LEFT JOIN user_read_record AS ur
	ON u.user_id = ur.user_id 
	WHERE u.user_id IN (` + utils.GetOrmReplaceHolder(len(userIds)) + `)
	GROUP BY u.user_id ` + sortCondition + ` LIMIT ?,? `
	_, err = o.Raw(sql, userIds, startSize, pageSize).QueryRows(&items)
	return
}