eta_mini_crm/models/users.go

package models

import (
	"eta/eta_mini_crm/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strings"
	"time"
)

// Users 用户表
type Users struct {
	UserId          int       `orm:"column(user_id);pk"`
	RealName        string    `description:"用户姓名"`
	Mobile          string    `description:"手机号"`
	AreaCode        string    `description:"区号"`
	SellerId        int       `description:"销售ID"`
	SellerName      string    `description:"销售名称"`
	SysUserId       int       `description:"操作人ID"`
	SysUserRealName string    `description:"操作人姓名"`
	CompanyId       int       `description:"所属机构ID"`
	CompanyName     string    `description:"所属机构名称"`
	Status          int       `description:"用户类型：1-试用用户；2-正式用户"`
	AuthStatus      int       `description:"权限状态：0-关闭；1-开启"`
	IsRegistered    int       `description:"注册状态：0-未注册；1-已注册"`
	RegisterTime    time.Time `description:"注册时间"`
	Subscribe       int       `description:"是否关注公众号：0-未关注；1-已关注"`
	LastReadTime    time.Time `description:"最后一次阅读时间"`
	ReadTimes       int       `description:"阅读次数"`
	OpenId          string    `description:"open_id"`
	UnionId         string    `description:"用户统一标识"`
	IsDeleted       int       `description:"删除状态：0-正常；1-已删除"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"修改时间"`
	UserLevelId     int       `description:"用户等级ID"`
	UserLevelName   string    `description:"用户等级名称"`
}

func (m *Users) TableName() string {
	return "users"
}

type UsersCols struct {
	PrimaryId       string
	RealName        string
	Mobile          string
	AreaCode        string
	SellerId        string
	SellerName      string
	SysUserId       string
	SysUserRealName string
	CompanyId       string
	CompanyName     string
	Status          string
	AuthStatus      string
	IsRegistered    string
	RegisterTime    string
	LastReadTime    string
	ReadTimes       string
	OpenId          string
	UnionId         string
	IsDeleted       string
	CreateTime      string
	ModifyTime      string
	UserLevelId     string
	UserLevelName   string
}

func (m *Users) Cols() UsersCols {
	return UsersCols{
		PrimaryId:       "user_id",
		RealName:        "real_name",
		Mobile:          "mobile",
		AreaCode:        "area_code",
		SellerId:        "seller_id",
		SellerName:      "seller_name",
		SysUserId:       "sys_user_id",
		SysUserRealName: "sys_user_real_name",
		CompanyId:       "company_id",
		CompanyName:     "company_name",
		Status:          "status",
		AuthStatus:      "auth_status",
		IsRegistered:    "is_registered",
		RegisterTime:    "register_time",
		LastReadTime:    "last_read_time",
		ReadTimes:       "read_times",
		OpenId:          "open_id",
		UnionId:         "union_id",
		IsDeleted:       "is_deleted",
		CreateTime:      "create_time",
		ModifyTime:      "modify_time",
		UserLevelId:     "user_level_id",
		UserLevelName:   "user_level_name",
	}
}

func (m *Users) Create() (err error) {
	o := orm.NewOrm()
	id, err := o.Insert(m)
	if err != nil {
		return
	}
	m.UserId = int(id)
	return
}

func (m *Users) CreateMulti(items []*Users) (err error) {
	if len(items) == 0 {
		return
	}
	o := orm.NewOrm()
	_, err = o.InsertMulti(len(items), items)
	return
}

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

func (m *Users) Remove() (err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = %d WHERE %s = ? LIMIT 1`, m.TableName(), m.Cols().IsDeleted, RowDeleted, m.Cols().PrimaryId)
	_, err = o.Raw(sql, m.UserId).Exec()
	return
}

func (m *Users) MultiRemove(ids []int) (err error) {
	if len(ids) == 0 {
		return
	}
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = %d WHERE %s IN (%s)`, m.TableName(), m.Cols().IsDeleted, RowDeleted, m.Cols().PrimaryId, utils.GetOrmInReplace(len(ids)))
	_, err = o.Raw(sql, ids).Exec()
	return
}

func (m *Users) RemoveByCondition(condition string, pars []interface{}) (err error) {
	if condition == "" {
		return
	}
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = %d WHERE %s`, m.TableName(), m.Cols().IsDeleted, RowDeleted, condition)
	_, err = o.Raw(sql, pars).Exec()
	return
}

func (m *Users) GetItemById(id int) (item *Users, err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`SELECT * FROM %s WHERE %s = %d AND %s = ? LIMIT 1`, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, m.Cols().PrimaryId)
	err = o.Raw(sql, id).QueryRow(&item)
	return
}

func (m *Users) GetItemByCondition(condition string, pars []interface{}, orderRule string) (item *Users, err error) {
	o := orm.NewOrm()
	order := ``
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT * FROM %s WHERE %s = %d %s %s LIMIT 1`, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, condition, order)
	err = o.Raw(sql, pars).QueryRow(&item)
	return
}

func (m *Users) GetCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %s WHERE %s = %d %s`, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, condition)
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func (m *Users) GetItemsByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string) (items []*Users, err error) {
	o := orm.NewOrm()
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := fmt.Sprintf(`ORDER BY %s DESC`, m.Cols().CreateTime)
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE %s = %d %s %s`, fields, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, condition, order)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func (m *Users) GetPageItemsByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string, startSize, pageSize int) (items []*Users, err error) {
	o := orm.NewOrm()
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := fmt.Sprintf(`ORDER BY %s DESC`, m.Cols().CreateTime)
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE %s = %d %s %s LIMIT ?,?`, fields, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, condition, order)
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// UsersItem 用户信息
type UsersItem struct {
	UserId        int    `description:"用户ID"`
	RealName      string `description:"用户姓名"`
	Mobile        string `description:"手机号"`
	MobileHidden  string `description:"手机号(隐藏中间四位)"`
	AreaCode      string `description:"区号"`
	SellerId      int    `description:"销售ID"`
	SellerName    string `description:"销售名称"`
	CompanyId     int    `description:"所属机构ID"`
	CompanyName   string `description:"所属机构名称"`
	Status        int    `description:"用户类型：1-试用用户；2-正式用户"`
	AuthStatus    int    `description:"权限状态：0-关闭；1-开启"`
	IsRegistered  int    `description:"注册状态：0-未注册；1-已注册"`
	RegisterTime  string `description:"注册时间"`
	Subscribe     int    `description:"是否关注公众号：0-未关注；1-已关注"`
	LastReadTime  string `description:"最后一次阅读时间"`
	ReadTimes     int    `description:"阅读次数"`
	TrialExpired  bool   `description:"试用到期：true-已到期"`
	CreateTime    string `description:"创建时间"`
	ModifyTime    string `description:"修改时间"`
	UserLevelId   int    `description:"用户等级ID"`
	UserLevelName string `description:"用户等级名称"`
}

func (m *Users) Format2Item() (item *UsersItem) {
	item = new(UsersItem)
	item.UserId = m.UserId
	item.RealName = m.RealName
	item.Mobile = m.Mobile
	item.MobileHidden = utils.HideMobileMiddle(m.Mobile)
	item.AreaCode = m.AreaCode
	item.SellerId = m.SellerId
	item.SellerName = m.SellerName
	item.CompanyId = m.CompanyId
	item.CompanyName = m.CompanyName
	item.Status = m.Status
	item.AuthStatus = m.AuthStatus
	item.IsRegistered = m.IsRegistered
	item.RegisterTime = utils.TimeTransferString(utils.FormatDateTime, m.RegisterTime)
	item.Subscribe = m.Subscribe
	item.LastReadTime = utils.TimeTransferString(utils.FormatDateTime, m.LastReadTime)
	item.ReadTimes = m.ReadTimes
	// 试用到期
	if m.Status == UserStatusTrial && !m.RegisterTime.IsZero() {
		expiredTime := m.RegisterTime.Add(3 * 24 * time.Hour)
		if expiredTime.Before(time.Now().Local()) {
			item.TrialExpired = true
		}
	}
	item.CreateTime = utils.TimeTransferString(utils.FormatDateTime, m.CreateTime)
	item.ModifyTime = utils.TimeTransferString(utils.FormatDateTime, m.ModifyTime)
	item.UserLevelId = m.UserLevelId
	item.UserLevelName = m.UserLevelName
	return
}

func (m *Users) UpdateCompanyNameById(companyId int, companyName string) (err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = ? WHERE %s = ?`, m.TableName(), m.Cols().CompanyName, m.Cols().CompanyId)
	_, err = o.Raw(sql, companyName, companyId).Exec()
	return
}

func (m *Users) GetCompanyIdsByKeywords(keywords string) (companyIds []int, err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`SELECT DISTINCT %s FROM %s WHERE %s = %d AND (%s LIKE ? OR %s LIKE ?) AND %s > 0`, m.Cols().CompanyId, m.TableName(), m.Cols().IsDeleted, RowDeletedDefault, m.Cols().RealName, m.Cols().Mobile, m.Cols().CompanyId)
	_, err = o.Raw(sql, keywords, keywords).QueryRows(&companyIds)
	return
}

func (m *Users) RemoveUsersAndUserRecord(userId int) (err error) {
	if userId <= 0 {
		err = fmt.Errorf("user id is zero")
		return
	}
	o := orm.NewOrm()
	tx, e := o.Begin()
	if e != nil {
		err = fmt.Errorf("orm begin err: %v", e)
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	sql := fmt.Sprintf(`UPDATE %s SET %s = ? WHERE %s = ? LIMIT 1`, m.TableName(), m.Cols().IsDeleted, m.Cols().PrimaryId)
	if _, e = tx.Raw(sql, RowDeleted, userId).Exec(); e != nil {
		err = fmt.Errorf("delete user err: %v", e)
		return
	}

	// 移除微信用户的UserId关联
	recordOb := new(UserRecord)
	sql = fmt.Sprintf(`UPDATE %s SET %s = ? WHERE %s = ?`, recordOb.TableName(), recordOb.Cols().UserId, recordOb.Cols().UserId)
	if _, e = tx.Raw(sql, 0, userId).Exec(); e != nil {
		err = fmt.Errorf("delete company users err: %v", e)
		return
	}
	return
}

func (m *Users) UpdateUserLevelName(levelId int, levelName string) (err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = ? WHERE %s = ?`, m.TableName(), m.Cols().UserLevelName, m.Cols().UserLevelId)
	_, err = o.Raw(sql, levelName, levelId).Exec()
	return
}

func (m *Users) UpdateUserLevelNameByCompanyId(companyId, levelId int, levelName string) (err error) {
	o := orm.NewOrm()
	sql := fmt.Sprintf(`UPDATE %s SET %s = ?, %s = ? WHERE %s = ?`, m.TableName(), m.Cols().UserLevelId, m.Cols().UserLevelName, m.Cols().CompanyId)
	_, err = o.Raw(sql, levelId, levelName, companyId).Exec()
	return
}