cp-organization-management/model/view_user.go

// Copyright 2019 getensh.com. All rights reserved.
// Use of this source code is governed by getensh.com.

package model

import (
	"cp-organization-management/errors"
	"fmt"
	"time"

	"encoding/json"
	"github.com/jaryhe/gopkgs/logger"
	"github.com/jaryhe/gopkgs/util"
	"github.com/jinzhu/gorm"
	"go.uber.org/zap"
)

type ViewUser struct {
	Id          int64  `json:"id" gorm:"primary_key"`
	Username    string `json:"username" gorm:"username;comment:'用户名';not null;default:''"`
	Passowrd    string `json:"passowrd"  gorm:"passowrd;comment:'密码';not null;default:''"`
	Phone       string `json:"phone" gorm:"phone;commnet:'手机号';not null;default:''"`
	Email       string `json:"email" gorm:"email;commnet:'电子邮件';not null;default:''"`
	IsSuperUser bool   `json:"is_super_user" gorm:"is_super_user;commnet:'是否是超级账号，超级账号不能删除';not null;default:0"`
	GroupId     int64  `json:"group_id" gorm:"group_id;commnet:'权限组id';not null;default:0"`
	//Zones       string    `json:"zones" gorm:"zones;commnet:'管辖区域列表';not null;default:''"`
	CreatedAt time.Time `json:"created_at" gorm:"created_at"`
	UpdatedAt time.Time `json:"updated_at" gorm:"updated_at"`
	Name      string    `json:"name" gorm:"name"`
	table     string    `json:"table"`
	ZoneCode string `json:"zone_code" gorm:"zone_code"`
	ZoneName string `json:"zone_name" gorm:"zone_name"`
	ZoneLevel int `json:"zone_level" gorm:"zone_level"`
}

func (p *ViewUser) TableName() string {
	return p.table
}

func NewViewUser(database string) *ViewUser {
	return &ViewUser{table: fmt.Sprintf("%s.%s", database, "view_user")}
}

func (p *ViewUser) CreateTable(db *gorm.DB) error {
	err := db.CreateTable(p).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "create table "+p.TableName()),
			zap.String("fields", ""),
			zap.String("error", err.Error()))
	}
	return nil
}

func (p *ViewUser) Find(db *gorm.DB, where map[string]interface{}) error {
	err := db.Table(p.TableName()).Where(where).Find(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select from "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}

	return err
}

func (p *ViewUser) Last(db *gorm.DB) error {
	err := db.Table(p.TableName()).Last(p).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "select last from "+p.TableName()),
			zap.String("fields", ""),
			zap.String("error", err.Error()))
	}

	return err
}

// Insert 插入一条记录
func (p *ViewUser) Insert(db *gorm.DB) error {
	timeNow := time.Now()
	p.CreatedAt = timeNow
	p.UpdatedAt = timeNow
	err := db.Create(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "insert into "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) Delete(db *gorm.DB, filter map[string]interface{}) error {
	err := db.Where(filter).Delete(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(filter)
		logger.Error("mysql",
			zap.String("sql", "delete from "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) Save(db *gorm.DB) error {
	p.UpdatedAt = time.Now()
	err := db.Save(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "save "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) Update(db *gorm.DB) error {
	p.UpdatedAt = time.Now()
	err := db.Model(p).Updates(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "update "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

// 通过结构体变量更新字段值, gorm库会忽略零值字段。就是字段值等于0, nil, "", false这些值会被忽略掉，不会更新。如果想更新零值，可以使用map类型替代结构体。
func (p *ViewUser) UpdateSome(db *gorm.DB, filed map[string]interface{}) error {
	if filed == nil {
		return errors.ParamsError
	}
	filed["updated_at"] = time.Now()
	err := db.Model(p).Updates(filed).Error
	if err != nil {
		fields, _ := util.MarshalToString(filed)
		logger.Error("mysql",
			zap.String("sql", "update "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) UpdateWithAffectRows(db *gorm.DB, where map[string]interface{}, values map[string]interface{}) (int64, error) {
	cond, val, err := whereBuild(where)
	if err != nil {
		return 0, err
	}

	r := db.Table(p.TableName()).Where(cond, val...).Updates(values)

	if r.Error != nil {
		fields, _ := json.Marshal(where)
		updateValues, _ := json.Marshal(values)
		logger.Error("mysql",
			zap.String("sql", "update  "+p.TableName()),
			zap.String("fields", string(fields)),
			zap.String("values", string(updateValues)),
			zap.String("error", err.Error()))
		return 0, errors.DataBaseError
	}
	return r.RowsAffected, err
}

func (p *ViewUser) QueryAll(db *gorm.DB, filter map[string]interface{}, out interface{}) error {
	err := db.Table(p.TableName()).Where(filter).Find(out).Error
	if err != nil {
		fields, _ := util.MarshalToString(filter)
		logger.Error("mysql",
			zap.String("sql", "select from "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) List(db *gorm.DB, limit, offset int, out interface{}) error {
	err := db.Table(p.TableName()).Limit(limit).Offset(offset).Find(out).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "select from "+p.TableName()),
			zap.String("fields", ""),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *ViewUser) Count(db *gorm.DB, where map[string]interface{}, group string) (int64, error) {

	cond, val, err := whereBuild(where)
	if err != nil {
		return 0, err
	}
	ret := int64(0)
	err = db.Table(p.TableName()).Where(cond, val...).Group(group).Count(&ret).Error
	if err != nil {
		fields, _ := json.Marshal(where)
		logger.Error("mysql",
			zap.String("sql", "select count from "+p.TableName()),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return ret, errors.DataBaseError
	}
	return ret, err

}

func (p *ViewUser) CountOr(db *gorm.DB, where map[string]interface{}, or map[string]interface{}, group string) (int64, error) {

	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return 0, err
	}
	ret := int64(0)
	err = db.Table(p.TableName()).Where(cond, val...).Group(group).Count(&ret).Error
	if err != nil {
		fields, _ := json.Marshal(where)
		logger.Error("mysql",
			zap.String("sql", "select count from "+p.TableName()),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return ret, errors.DataBaseError
	}
	return ret, err

}

func (p *ViewUser) ListNew(db *gorm.DB, where map[string]interface{}, limit, offset int64, group string) (list []ViewUser, err error) {

	cond, val, err := whereBuild(where)
	if err != nil {
		return list, err
	}

	err = db.Table(p.TableName()).Where(cond, val...).Group(group).Limit(limit).Offset(offset).Find(&list).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "select * from "+p.TableName()),
			zap.String("error", err.Error()))
		return list, errors.DataBaseError
	}

	return list, err

}

func (p *ViewUser) ListOr(db *gorm.DB, where map[string]interface{}, or map[string]interface{}, limit, offset int64, group string) (list []ViewUser, err error) {

	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return list, err
	}

	err = db.Table(p.TableName()).Where(cond, val...).Group(group).Limit(limit).Offset(offset).Find(&list).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "select * from "+p.TableName()),
			zap.String("error", err.Error()))
		return list, errors.DataBaseError
	}

	return list, err

}

type UserListReq struct {
	Uid int64
	Limit int64
	Offset int64
	Filter string
	LoginUserSubZoneUserIds []int64
	LoginUserSubZone []string
	ZoneFilterUserIds []int64
	ParentIds string
}

type UserListItem struct {
	Id                   int64
	Name                 string
	Username             string
	Phone                string
	Email                string
	Zones                string
}

func userListSql(req *UserListReq, tablename string) (string, string, []interface{}) {
	where := ""
	args := []interface{}{}
	whereArray1 := []string{}
	whereArray2 := []string{}
	sql := fmt.Sprintf("select *, group_concat(zone_name) as zones from %s", tablename)

	whereArray1 = append(whereArray1, fmt.Sprintf("pids like '%s%%'", req.ParentIds))
	if len(req.LoginUserSubZoneUserIds) > 0 {
		whereArray1 = append(whereArray1, fmt.Sprintf("(id in (?) and zone_code in(?))"))
		args = append(args, req.LoginUserSubZoneUserIds)
		args = append(args, req.LoginUserSubZone)
	}

	countSql := fmt.Sprintf("select 1 from %s", tablename)
	whereArray2 = append(whereArray2, fmt.Sprintf("is_super_user=0"))
	whereArray2 = append(whereArray2, fmt.Sprintf("id<>%d", req.Uid))
	if req.Filter != "" {
		whereArray2 = append(whereArray2, fmt.Sprintf("(username like '%%%s%%' or name like '%%%s%%')", req.Filter, req.Filter))
	}
	if len(req.ZoneFilterUserIds) > 0 {
		whereArray2 = append(whereArray2, fmt.Sprintf("id in(?)"))
		args = append(args, req.ZoneFilterUserIds)
	}

	//whereArray = append(whereArray, fmt.Sprintf())
	for _, v := range whereArray1 {
		if where == "" {
			where = fmt.Sprintf("%s", v)
			continue
		}
		where = fmt.Sprintf("%s or %s", where, v)
	}
	where = fmt.Sprintf("where (%s)", where)

	for _, v := range whereArray2 {

		where = fmt.Sprintf("%s and %s", where, v)
	}

	sql = fmt.Sprintf("%s %s group by id limit %d offset %d", sql, where, req.Limit, req.Offset)
	countSql = fmt.Sprintf("%s %s group by id", countSql, where)
	countSql = fmt.Sprintf("select count(1) as count from (%s) as b", countSql)
	return countSql, sql, args
}

type ResultCount struct {
	Count int64
}

func (p *ViewUser)UserList(db *gorm.DB, req *UserListReq) ([]UserListItem, int64, error) {

	countSql, sql, args := userListSql(req, p.TableName())
	array := []ResultCount{}
	ret := []UserListItem{}
	var err error
	err = db.Raw(countSql, args...).Scan(&array).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", countSql),
			zap.String("error", err.Error()))
		return  nil, 0, errors.DataBaseError
	}
	if len(array) == 0 {
		return nil, 0, nil
	}
	if array[0].Count == 0 {
		return nil, 0, nil
	}

	err = db.Raw(sql, args...).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, array[0].Count, errors.DataBaseError
	}
	return ret, array[0].Count, nil
}

func (p *ViewUser) GetUserIdBySubZones(db *gorm.DB, topLevel int, zones []string) ([]int64, error) {

	sql := fmt.Sprintf("select * from %s where not exists (select id from %s where zone_level <= %d) and zone_code in(?) group by id", p.TableName(), p.TableName(), topLevel)
	array := []ViewUser{}
	err := db.Raw(sql, zones).Scan(&array).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, errors.DataBaseError
	}
	ret := []int64{}
	for _, v := range array {
		ret = append(ret, v.Id)
	}
	return ret, nil
}

/*
func Test(db *gorm.DB) {
	dbname := utils.GetDbName("001")
//	createViewSql := fmt.Sprintf("CREATE VIEW `%s`.`view_user` AS select t1.*, t2.zone_code from %s.t_rbac_user as t1 left join %s.t_user_zone as t2 on t1.id=t2.user_id;", dbname, dbname, dbname)
//	err := db.Exec(createViewSql).Error
//	fmt.Printf("%v\n", err)

	//return
	p := NewViewUser(dbname)
	where := map[string]interface{}{
		"zone_code in":[]string{"1", "2"},
	}
	cond, val, _ := whereBuild(where)
	list := []ViewUser{}
	db.Table(p.TableName()).Where(cond, val...).Group("").Limit(1).Offset(0).Find(&list)
	fmt.Printf("%v\n", list)
	var ret int64
	db.Table(p.TableName()).Where(cond, val...).Group("").Count(&ret)
	fmt.Printf("%v\n", ret)
}
 */