eta_api/models/data_manage/edb_classify.go

package data_manage

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

type EdbClassify struct {
	ClassifyId      int       `orm:"column(classify_id);pk"`
	ClassifyType    uint8     `description:"分类类型，0：普通指标分类，1：预测指标分类"`
	ClassifyName    string    `description:"分类名称"`
	ParentId        int       `description:"父级id"`
	RootId          int       `description:"顶级id"`
	HasData         int       `description:"是否含有指标数据"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"修改时间"`
	SysUserId       int       `description:"创建人id"`
	SysUserRealName string    `description:"创建人姓名"`
	Level           int       `description:"层级"`
	UniqueCode      string    `description:"唯一编码"`
	Sort            int       `description:"排序字段，越小越靠前，默认值：10"`
}

func AddEdbClassify(item *EdbClassify) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	return
}

type AddEdbClassifyReq struct {
	ClassifyName string `description:"分类名称"`
	ParentId     int    `description:"父级id,第一级传0"`
	Level        int    `description:"层级,第一级传0，其余传上一级的层级"`
	ClassifyType uint8  `description:"分类类型，0：普通指标分类，1：预测指标分类"`
}

func GetEdbClassifyCount(classifyName string, parentId int, classifyType uint8) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT COUNT(1) AS count FROM edb_classify WHERE parent_id=? AND classify_name=? AND classify_type = ? `
	err = o.Raw(sql, parentId, classifyName, classifyType).QueryRow(&count)
	return
}

type EditEdbClassifyReq struct {
	ClassifyName string `description:"分类名称"`
	ClassifyId   int    `description:"分类名称"`
}

func GetEdbClassifyById(classifyId int) (item *EdbClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM edb_classify WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

func GetEdbClassifyCountById(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT count(1) AS count FROM edb_classify WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func EditEdbClassify(classifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE edb_classify SET classify_name=?,modify_time=NOW() WHERE classify_id=? `
	_, err = o.Raw(sql, classifyName, classifyId).Exec()
	return
}

type DeleteEdbClassifyReq struct {
	ClassifyId int `description:"分类id"`
	EdbInfoId  int `description:"指标id"`
}

func GetEdbInfoCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM edb_info AS a
				WHERE a.classify_id IN(
				SELECT t.classify_id FROM 
				(
				SELECT rd.*
				FROM (SELECT * FROM edb_classify WHERE parent_id IS NOT NULL) rd,
					 (SELECT @pid := ?) pd 
				WHERE FIND_IN_SET(parent_id, @pid) > 0 
				  AND @pid := CONCAT(@pid, ',', classify_id) 
				UNION SELECT * FROM edb_classify WHERE classify_id = @pid
				)AS t
				) `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func DeleteEdbClassify(classifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` DELETE FROM edb_classify
				WHERE classify_id IN(
				SELECT t.classify_id FROM
				(
				SELECT rd.*
				FROM (SELECT * FROM edb_classify WHERE parent_id IS NOT NULL) rd,
				(SELECT @pid := ?) pd
				WHERE FIND_IN_SET(parent_id, @pid) > 0
				AND @pid := CONCAT(@pid, ',', classify_id)
				UNION SELECT * FROM edb_classify WHERE classify_id = @pid
				)AS t
				) `
	_, err = o.Raw(sql, classifyId).Exec()
	return
}

func GetEdbClassifyByParentId(parentId int, classifyType int8) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE parent_id=? AND classify_type = ?  order by sort asc,classify_id asc`
	_, err = o.Raw(sql, parentId, classifyType).QueryRows(&items)
	return
}

func GetEdbClassifyByRootIdLevel(rootId int, classifyType uint8, orderStr string) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE root_id=? AND classify_type = ? `
	if orderStr != "" {
		sql += orderStr
	} else {
		sql += ` order by level desc, sort asc, classify_id asc`
	}

	_, err = o.Raw(sql, rootId, classifyType).QueryRows(&items)
	return
}

func GetEdbClassifyByRootIdLevelOrder(rootId int, classifyType uint8) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE root_id=? AND classify_type = ? order by level desc, sort asc, classify_id asc`
	_, err = o.Raw(sql, rootId, classifyType).QueryRows(&items)
	return
}

// GetEdbClassifyAll 获取普通指标的分类列表
func GetEdbClassifyAll() (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE parent_id<>0  AND classify_type = 0  order by sort asc,classify_id asc`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetNormalEdbClassifyAll 获取普通指标的分类列表
func GetNormalEdbClassifyAll() (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE  classify_type = 0  order by sort asc,classify_id asc`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetPredictEdbClassifyAll 获取预测指标的分类列表
func GetPredictEdbClassifyAll() (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE classify_type = 1  order by sort asc,classify_id asc`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type EdbClassifyItems struct {
	ClassifyId      int `description:"分类id"`
	EdbInfoId       int `description:"指标id"`
	ClassifyName    string
	ClassifyNameEn  string
	ParentId        int
	RootId          int    `description:"顶级id"`
	Level           int    `description:"层级"`
	Sort            int    `description:"排序字段，越小越靠前，默认值：10"`
	UniqueCode      string `description:"唯一编码"`
	Source          int    `description:"来源id"`
	SourceName      string `description:"来源名称"`
	SysUserId       int    `description:"创建人id"`
	SysUserRealName string `description:"创建人姓名"`
	StartDate       string
	EdbCode         string
	EdbType         int `description:"指标类型:1:基础指标,2:计算指标"`
	Children        []*EdbClassifyItems
	Button          EdbClassifyItemsButton `description:"操作权限"`
}

type EdbClassifyIdItems struct {
	ClassifyId   int `description:"分类id"`
	ClassifyName string
	UniqueCode   string `description:"唯一编码"`
	ParentId     int    `description:"父级分类id"`
	Level        int    `description:"层级"`
	RootId       int    `description:"顶级分类id"`
}

type EdbClassifyItemList []*EdbClassifyItems

func (m EdbClassifyItemList) Len() int {
	return len(m)
}

func (m EdbClassifyItemList) Less(i, j int) bool {
	return m[i].Sort < m[j].Sort
}

func (m EdbClassifyItemList) Swap(i, j int) {
	m[i], m[j] = m[j], m[i]
}

// EdbClassifyItemsButton 操作按钮
type EdbClassifyItemsButton struct {
	AddButton           bool `description:"是否可添加"`
	OpButton            bool `description:"是否可编辑"`
	DeleteButton        bool `description:"是否可删除"`
	MoveButton          bool `description:"是否可移动"`
	ShowEdbRelation     bool `description:"是否展示关联指标"`
	ShowChartRelation   bool `description:"是否展示关联图表"`
	InsertNewDataButton bool `description:"是否可插入最新值"`
}

type EdbClassifyListResp struct {
	AllNodes      EdbClassifyItemList
	CanOpClassify bool   `description:"是否允许操作分类"`
	Language      string `description:"指标的展示语言，CN：中文，EN：英文"`
}

type ClassifyDeleteCheckResp struct {
	DeleteStatus int    `description:"检测状态:0:默认值，如果为0，继续走其他校验，1:若目录关联指标不可删除,2:确认删除当前目录及包含的子目录吗,3:当前指标已用作画图，不可删除"`
	TipsMsg      string `description:"提示信息"`
}

type ClassifyDeleteCheckReq struct {
	ClassifyId int `description:"分类id"`
	EdbInfoId  int `description:"指标id"`
}

func GetClassifyCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM (
			SELECT rd.*
			FROM (SELECT * FROM edb_classify WHERE parent_id IS NOT NULL) rd,
				 (SELECT @pid := ?) pd 
			WHERE FIND_IN_SET(parent_id, @pid) > 0 
			  AND @pid := CONCAT(@pid, ',', classify_id) 
			UNION SELECT * FROM edb_classify WHERE classify_id = @pid
			)AS t
			WHERE t.classify_id<>? `
	err = o.Raw(sql, classifyId, classifyId).QueryRow(&count)
	return
}

type EdbClassifySimplify struct {
	ClassifyId   int    `description:"分类id"`
	ClassifyName string `description:"分类名称"`
	ParentId     int
}

// 递归获取所有目录
func GetClassifyAllByClassifyId(classifyId int) (resultList []*EdbClassifySimplify, err error) {
	fmt.Println("185")
	o := orm.NewOrmUsingDB("data")
	for {
		sql := ` SELECT * FROM edb_classify AS a
			WHERE a.classify_id=? order by sort asc,classify_id asc`
		item := new(EdbClassifySimplify)
		err = o.Raw(sql, classifyId).QueryRow(&item)
		if err != nil {
			break
		}
		if item.ClassifyId > 0 {
			resultList = append(resultList, item)
			classifyId = item.ParentId
		} else {
			break
		}
	}
	return resultList, err
}

// MoveEdbClassifyReq 移动分类请求参数
type MoveEdbClassifyReq struct {
	ClassifyId       int `description:"分类id"`
	ParentClassifyId int `description:"父级分类id"`
	PrevClassifyId   int `description:"上一个兄弟节点分类id"`
	NextClassifyId   int `description:"下一个兄弟节点分类id"`
	EdbInfoId        int `description:"指标ID, 如果指标ID有值，则移动对象为指标，否则认为移动对象为分类"`
	PrevEdbInfoId    int `description:"上一个指标ID"`
	NextEdbInfoId    int `description:"下一个指标ID"`
}

// GetFirstEdbClassifyByParentId 获取当前父级分类下，且排序数相同 的排序第一条的数据
func GetFirstEdbClassifyByParentId(parentId int) (item *EdbClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE parent_id=? order by sort asc,classify_id asc limit 1`
	err = o.Raw(sql, parentId).QueryRow(&item)
	return
}

// UpdateEdbClassifySortByParentId 根据父类id更新排序
func UpdateEdbClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string, classifyType uint8) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update edb_classify set sort = ` + updateSort + ` WHERE parent_id=? AND sort > ? AND classify_type = ? `
	if classifyId > 0 {
		sql += ` or ( classify_id > ` + fmt.Sprint(classifyId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
	}
	_, err = o.Raw(sql, parentId, nowSort, classifyType).Exec()
	return
}

// Update 更新分类基础信息
func (edbClassify *EdbClassify) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(edbClassify, cols...)
	return
}

// GetEdbClassifyMaxSort 获取分类下最大的排序数
func GetEdbClassifyMaxSort(parentId int, classifyType uint8) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT Max(sort) AS sort FROM edb_classify WHERE parent_id=? AND classify_type=? `
	err = o.Raw(sql, parentId, classifyType).QueryRow(&sort)
	return
}

// GetEdbInfoMaxSortByClassifyId 获取分类下指标的最大的排序数
func GetEdbInfoMaxSortByClassifyId(classifyId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT Max(sort) AS sort FROM edb_info WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&sort)
	return
}

type ClassifyEdbInfoListResp struct {
	EdbInfoList []*EdbClassifyItems
}

// GetEdbClassifyAllV2 获取普通指标的分类列表
func GetEdbClassifyAllV2(classifyType int8) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE parent_id <>0  AND classify_type = ?  order by sort asc,classify_id asc`
	_, err = o.Raw(sql, classifyType).QueryRows(&items)
	return
}

func GetEdbClassifyByParentIdTwo(classifyType int8) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE level=2 AND classify_type = ?  order by sort asc,classify_id asc`
	_, err = o.Raw(sql, classifyType).QueryRows(&items)
	return
}

type AddEdbClassifyReqV2 struct {
	ClassifyName string `description:"分类名称"`
	ParentId     int    `description:"父级id,第一级传0"`
	Level        int    `description:"层级,第一级传0，其余传上一级的层级"`
	ClassifyType uint8  `description:"分类类型，0：普通指标分类，1：预测指标分类"`
}

// GetEdbClassifyMaxSortV2 获取分类下最大的排序数
func GetEdbClassifyMaxSortV2(parentId int, classifyType uint8) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT Max(sort) AS sort FROM edb_classify WHERE parent_id=? AND parent_id_two=? AND classify_type=? `
	err = o.Raw(sql, parentId, classifyType).QueryRow(&sort)
	return
}

func GetEdbClassifyByLevel(level, source int) (items []*EdbClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_classify WHERE level=? AND classify_type = ? `
	_, err = o.Raw(sql, level, source).QueryRows(&items)
	return
}

func FIXEdbClassifyId(newId, oldId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` UPDATE edb_info SET classify_id=? WHERE classify_id=? `
	_, err = o.Raw(sql, newId, oldId).Exec()
	return
}

func FixEdbClassify() {
	//新增三级分类
	list, err := GetEdbClassifyByLevel(2, 0)
	if err != nil {
		return
	}

	for _, v := range list {
		timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
		classifyInfo := &EdbClassify{
			//ClassifyId:      0,
			ClassifyType:    0,
			ClassifyName:    v.ClassifyName,
			ParentId:        v.ClassifyId,
			HasData:         0,
			CreateTime:      time.Now(),
			ModifyTime:      time.Now(),
			SysUserRealName: "admin",
			Level:           3,
			UniqueCode:      utils.MD5(utils.DATA_PREFIX + "_" + timestamp),
			Sort:            v.Sort,
		}
		if utils.RunMode == "debug" {
			classifyInfo.SysUserId = 11
		} else {
			classifyInfo.SysUserId = 163
		}
		id, err := AddEdbClassify(classifyInfo)
		if err != nil {
			return
		}

		err = FIXEdbClassifyId(int(id), v.ClassifyId)
		if err != nil {
			return
		}
	}
}

func FixPredictEdbClassify() {
	//新增三级分类
	list, err := GetEdbClassifyByLevel(2, 1)
	if err != nil {
		return
	}

	for _, v := range list {
		timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
		classifyInfo := &EdbClassify{
			//ClassifyId:      0,
			ClassifyType:    1,
			ClassifyName:    v.ClassifyName,
			ParentId:        v.ClassifyId,
			HasData:         0,
			CreateTime:      time.Now(),
			ModifyTime:      time.Now(),
			SysUserRealName: "admin",
			Level:           3,
			UniqueCode:      utils.MD5(utils.DATA_PREFIX + "_" + timestamp),
			Sort:            v.Sort,
		}
		if utils.RunMode == "debug" {
			classifyInfo.SysUserId = 11
		} else {
			classifyInfo.SysUserId = 163
		}
		id, err := AddEdbClassify(classifyInfo)
		if err != nil {
			return
		}

		err = FIXEdbClassifyId(int(id), v.ClassifyId)
		if err != nil {
			return
		}
	}
}

func GetEdbClassify(classifyId int) (classifyIds string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT GROUP_CONCAT(t.classify_id) AS classify_ids FROM (
			SELECT a.classify_id FROM edb_classify AS a 
			WHERE a.classify_id=?
			UNION ALL
			SELECT a.classify_id FROM edb_classify AS a 
			WHERE a.parent_id=? UNION ALL
	SELECT
		classify_id 
	FROM
		edb_classify 
WHERE
	parent_id IN ( SELECT classify_id FROM edb_classify WHERE parent_id = ? )
			)AS t`
	err = o.Raw(sql, classifyId, classifyId, classifyId).QueryRow(&classifyIds)
	return
}

func FixEdbClassifySysUser() {
	list, err := GetEdbClassifyByLevel(2, 0)
	if err != nil {
		return
	}

	for _, v := range list {
		err := EditEdbClassifySysUser(v.ClassifyId, v.SysUserId, v.SysUserRealName)
		if err != nil {
			return
		}
		fmt.Println("ClassifyId:", v.ClassifyId)
	}
	fmt.Println("FixEdbClassifySysUser end")
}

func EditEdbClassifySysUser(classifyId, sysUserId int, chartClassifyName string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE edb_classify SET sys_user_id=?,sys_user_real_name=?,modify_time=NOW() WHERE parent_id=?  AND level=3 `
	_, err = o.Raw(sql, sysUserId, chartClassifyName, classifyId).Exec()
	return
}

func FixPredictEdbClassifySysUser() {
	list, err := GetEdbClassifyByLevel(2, 1)
	if err != nil {
		return
	}

	for _, v := range list {
		err := EditEdbClassifySysUser(v.ClassifyId, v.SysUserId, v.SysUserRealName)
		if err != nil {
			return
		}
		fmt.Println("ClassifyId:", v.ClassifyId)
	}
	fmt.Println("EditChartClassifySysUser end")
}

func UpdateEdbClassifyChildByParentClassifyId(classifyIds []int, rootId int, levelStep int) (err error) {
	o := orm.NewOrmUsingDB("data")
	var pars []interface{}
	pars = append(pars, rootId, levelStep)
	pars = append(pars, classifyIds)
	// 更新相关联的二级分类的parentId,和classify_name_second
	sql := `update edb_classify 
SET root_id = ?, level = level+?
where classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `)`
	_, err = o.Raw(sql, pars).Exec()
	if err != nil {
		return
	}
	return
}