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:"修改时间"`
}

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
}

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",
	}
}

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:"修改时间"`
}

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