shop/modules/system/dao/user_dao.go

package dao

import (
	"github.com/druidcaesa/gotool"
	"time"
	"ulink-admin/frame"
	"ulink-admin/modules/system/models/model"
	"ulink-admin/modules/system/models/req"
	"ulink-admin/modules/system/models/response"
	"ulink-admin/pkg/base"
	"ulink-admin/pkg/page"
	"xorm.io/xorm"
)

type UserDao struct {
	base.BaseDao
}

// 查询公共sql
func (d UserDao) querySql() *xorm.Session {
	return base.GetSession().Session.Table([]string{"sys_user", "u"})
	//.Join("LEFT", []string{"sys_dept", "d"}, "u.dept_id = d.id")
	//Join("LEFT", []string{"sys_user_role", "ur"}, "u.id = ur.user_id").
	//Join("LEFT", []string{"sys_role", "r"}, "r.id = ur.role_id")
}

// Find 查询用户集合
func (d UserDao) Page(query *req.UserQuery) ([]*response.UserResponse, int64) {
	resp := make([]*response.UserResponse, 0)
	sql := d.querySql()
	if !gotool.StrUtils.HasEmpty(query.UserName) {
		sql.And("u.user_name like concat('%',?,'%')", query.UserName)
	}
	if !gotool.StrUtils.HasEmpty(query.Status) {
		sql.And("u.status = ?", query.Status)
	}
	if !gotool.StrUtils.HasEmpty(query.PhoneNumber) {
		sql.And("u.phone_number like concat('%',?,'%')", query.PhoneNumber)
	}
	if !gotool.StrUtils.HasEmpty(query.BeginTime) {
		sql.And("date_format(u.create_time,'%y%m%d') >= date_format(?,'%y%m%d')", query.BeginTime)
	}
	if !gotool.StrUtils.HasEmpty(query.EndTime) {
		sql.And("date_format(u.create_time,'%y%m%d') <= date_format(?,'%y%m%d')", query.EndTime)
	}
	if query.Id > 0 {
		sql.And("u.dept_id = ? OR u.dept_id in ( SELECT t.id FROM sys_dept t WHERE find_in_set(?, ancestors))", query.Id, query.Id)
	}
	if !gotool.StrUtils.HasEmpty(query.UserType) {
		sql.And("u.user_type = ?", query.UserType)
	}
	if !base.GetCurUser().IsAdmin {
		sql.And("u.company_id = ?", base.GetCurUser().ComponyId)
	}
	total, err := sql.Limit(query.PageSize, page.StartSize(query.PageNum, query.PageSize)).FindAndCount(&resp)
	if err != nil {
		gotool.Logs.ErrorLog().Println(err)
		return nil, 0
	}
	return resp, total
}

// Find 查询用户集合
func (d UserDao) List(query *req.UserQuery, list *[]model.SysUser) {
	sql := d.querySql()
	if !gotool.StrUtils.HasEmpty(query.UserName) {
		sql.And("u.user_name like concat('%',?,'%')", query.UserName)
	}
	if !gotool.StrUtils.HasEmpty(query.Status) {
		sql.And("u.status = ?", query.Status)
	}
	if !gotool.StrUtils.HasEmpty(query.PhoneNumber) {
		sql.And("u.phone_number like concat('%',?,'%')", query.PhoneNumber)
	}
	if !gotool.StrUtils.HasEmpty(query.BeginTime) {
		sql.And("date_format(u.create_time,'%y%m%d') >= date_format(?,'%y%m%d')", query.BeginTime)
	}
	if !gotool.StrUtils.HasEmpty(query.EndTime) {
		sql.And("date_format(u.create_time,'%y%m%d') <= date_format(?,'%y%m%d')", query.EndTime)
	}
	if query.Id > 0 {
		sql.And("u.dept_id = ? OR u.dept_id in ( SELECT t.id FROM sys_dept t WHERE find_in_set(?, ancestors))", query.Id, query.Id)
	}
	if !gotool.StrUtils.HasEmpty(query.UserType) {
		sql.And("u.user_type = ?", query.UserType)
	}
	if !base.GetCurUser().IsAdmin {
		sql.And("u.company_id = ?", base.GetCurUser().ComponyId)
	}
	err := sql.Find(list)
	if err != nil {
		frame.Throw(frame.SQL_CODE, err.Error())
	}
}

// GetUserByUserName 根据用户名查询用户数据
func (d UserDao) GetUserByUserName(user model.SysUser) *model.SysUser {
	i, err := base.SqlDB.Get(&user)
	if err != nil {
		gotool.Logs.ErrorLog().Println(err)
		return nil
	}
	if i {
		return &user
	}
	return nil
}

// CheckEmailUnique 校验邮箱是否存在
func (d UserDao) CheckEmailUnique(user *req.UserBody) *model.SysUser {
	if gotool.StrUtils.HasEmpty(user.Email) {
		return nil
	}
	sysUser := model.SysUser{}
	session := base.GetSession().Session.Table("sys_user")
	session.Cols("id", "email")
	session.Where("email = ?", user.Email)
	if user.Id > 0 {
		session.And("id != ?", user.Id)
	}
	get, _ := session.Limit(1).Get(&sysUser)
	if !get {
		return nil
	}
	return &sysUser
}

// CheckPhoneNumUnique 校验手机号是否存在
func (d UserDao) CheckPhoneNumUnique(body *req.UserBody) *model.SysUser {
	if gotool.StrUtils.HasEmpty(body.PhoneNumber) {
		return nil
	}
	sysUser := model.SysUser{}
	session := base.GetSession().Session.Table("sys_user")
	session.Cols("id", "phone_number")
	session.Where("phone_number = ?", body.PhoneNumber)
	if body.Id > 0 {
		session.And("id != ?", body.Id)
	}
	get, _ := session.Limit(1).Get(&sysUser)
	if !get {
		return nil
	}
	return &sysUser
}

// ResetPwd 修改用户密码数据库操作
func (d UserDao) ResetPwd(body *req.UserBody) int64 {
	user := model.SysUser{
		Id:       body.Id,
		Password: body.Password,
	}
	session := base.GetSession().Session
	_ = session.Begin()
	_, err := session.Where("id = ?", user.Id).Cols("password").Update(&user)
	if err != nil {
		gotool.Logs.ErrorLog().Println(err)
		_ = session.Rollback()
		return 0
	}
	_ = session.Commit()
	return 1
}

// GetAllocatedList 查询未分配用户角色列表
func (d UserDao) GetAllocatedList(query *req.UserQuery) ([]*response.UserResponse, int64) {
	resp := make([]*response.UserResponse, 0)
	session := base.GetSession().Session
	session.Table([]string{"sys_user", "u"}).Distinct("u.id", "u.user_name", "u.nick_name", "u.email", "u.phone_number", "u.status", "u.create_time").
		//Join("LEFT", []string{"sys_dept", "d"}, "u.dept_id = d.id").
		Join("LEFT", []string{"sys_user_role", "ur"}, "u.id = ur.user_id").
		Join("LEFT", []string{"sys_role", "r"}, "r.id = ur.role_id").Where("u.del_flag = '0'").And("r.id = ?", query.Id)
	if gotool.StrUtils.HasNotEmpty(query.UserName) {
		session.And("u.user_name like concat('%', ?, '%')", query.UserName)
	}
	if gotool.StrUtils.HasNotEmpty(query.PhoneNumber) {
		session.And("u.phone_number like concat('%', ?, '%')", query.PhoneNumber)
	}
	total, err := session.Limit(query.PageSize, page.StartSize(query.PageNum, query.PageSize)).FindAndCount(&resp)
	if err != nil {
		gotool.Logs.ErrorLog().Println(err)
		return nil, 0
	}
	return resp, total
}

// GetUnallocatedList 查询未分配用户角色列表
func (d UserDao) GetUnallocatedList(query *req.UserQuery) ([]*response.UserResponse, int64) {
	resp := make([]*response.UserResponse, 0)
	session := base.GetSession().Session
	session.Table([]string{"sys_user", "u"}).Distinct("u.id", "u.user_name", "u.nick_name", "u.email", "u.phone_number", "u.status", "u.create_time").
		//Join("LEFT", []string{"sys_dept", "d"}, "u.dept_id = d.id").
		Join("LEFT", []string{"sys_user_role", "ur"}, "u.id = ur.user_id").
		Join("LEFT", []string{"sys_role", "r"}, "r.id = ur.role_id").Where("u.del_flag = '0'").And("r.id = ? or r.id IS NULL", query.Id).
		And("u.id not in (select u.id from sys_user u inner join sys_user_role ur on u.id = ur.user_id and ur.role_id = ?)", query.Id)
	if gotool.StrUtils.HasNotEmpty(query.UserName) {
		session.And("u.user_name like concat('%', ?, '%')", query.UserName)
	}
	if gotool.StrUtils.HasNotEmpty(query.PhoneNumber) {
		session.And("u.phone_number like concat('%', ?, '%')", query.PhoneNumber)
	}
	total, err := session.Limit(query.PageSize, page.StartSize(query.PageNum, query.PageSize)).FindAndCount(&resp)
	if err != nil {
		gotool.Logs.ErrorLog().Println(err)
		return nil, 0
	}
	return resp, total
}

// UpdatePwd 修改密码
func (d UserDao) UpdatePwd(id int64, hash string) {
	user := model.SysUser{}
	user.Id = id
	user.Password = hash
	session := base.GetSession().Session
	_ = session.Begin()
	_, err := session.Cols("password").Where("id = ?", id).Update(&user)
	if err != nil {
		_ = session.Rollback()
		frame.Throw(frame.SQL_CODE, "更新密码错误:"+err.Error())
	}
	_ = session.Commit()
}

// UpdateAvatar 修改头像
func (d UserDao) UpdateAvatar(info *model.SysUser) {
	user := model.SysUser{
		Avatar:     info.Avatar,
		Id:         info.Id,
		UpdateBy:   info.UserName,
		UpdateTime: time.Now(),
	}
	session := base.GetSession().Session
	_ = session.Begin()
	_, err := session.Cols("avatar", "update_by", "update_time").Where("id = ?", user.Id).Update(&user)
	if err != nil {
		_ = session.Rollback()
		frame.Throw(frame.SQL_CODE, "更新个人信息错误:"+err.Error())
	}
	_ = session.Commit()
}

func (d UserDao) UpdateStatus(id int64, status string) {
	user := model.SysUser{}
	user.Id = id
	user.Status = status
	session := base.GetSession().Session
	_ = session.Begin()
	_, err := session.Cols("status").Where("id = ?", id).Update(&user)
	if err != nil {
		_ = session.Rollback()
		frame.Throw(frame.SQL_CODE, "更新状态错误:"+err.Error())
	}
	_ = session.Commit()
}