eta_api/models/data_manage/edb_classify.go

package data_manage

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"time"
)

type EdbClassify struct {
	ClassifyId             int       `gorm:"primaryKey" `
	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"`
	LastModifyUserId       int       `description:"最后修改人ID"`
	LastModifyUserRealName string    `description:"最后修改人姓名"`
	IsJoinPermission       int       `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	ClassifyNameEn         string    `description:"英文分类名称"`
}

func AddEdbClassify(item *EdbClassify) (lastId int64, err error) {
	err = global.DmSQL["data"].Create(item).Error
	if err != nil {
		return
	}
	lastId = int64(item.ClassifyId)

	return
}

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

// GetEdbClassifyCount
// @Description: 根据父级id获取同级中文名称的数量
// @author: Roc
// @datetime 2024-04-17 14:12:22
// @param classifyName string
// @param parentId int
// @param classifyType uint8
// @return count int
// @return err error
func GetEdbClassifyCount(classifyName string, parentId int, classifyType uint8) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM edb_classify WHERE parent_id=? AND classify_name=? AND classify_type = ? `
	err = global.DmSQL["data"].Raw(sql, parentId, classifyName, classifyType).Scan(&count).Error

	return
}

// GetEdbClassifyEnCount
// @Description: 根据父级id获取同级英文名称的数量
// @author: Roc
// @datetime 2024-04-17 14:12:09
// @param classifyNameEn string
// @param parentId int
// @param classifyType uint8
// @return count int
// @return err error
func GetEdbClassifyEnCount(classifyNameEn string, parentId int, classifyType uint8) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM edb_classify WHERE parent_id=? AND classify_name_en = ? AND classify_type = ? `
	err = global.DmSQL["data"].Raw(sql, parentId, classifyNameEn, classifyType).Scan(&count).Error

	return
}

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

func GetEdbClassifyById(classifyId int) (item *EdbClassify, err error) {
	sql := `SELECT * FROM edb_classify WHERE classify_id=? `
	err = global.DmSQL["data"].Raw(sql, classifyId).First(&item).Error

	return
}

func GetEdbClassifyCountById(classifyId int) (count int, err error) {
	sql := `SELECT count(1) AS count FROM edb_classify WHERE classify_id=? `
	err = global.DmSQL["data"].Raw(sql, classifyId).Scan(&count).Error

	return
}

// EditEdbClassify
// @Description: 修改指标分类的中文名称
// @author: Roc
// @datetime 2024-04-17 14:14:48
// @param classifyId int
// @param classifyName string
// @param userId int
// @param userRealName string
// @return err error
func EditEdbClassify(classifyId int, classifyName string, userId int, userRealName string) (err error) {
	sql := `UPDATE edb_classify SET classify_name=?,modify_time=NOW(), last_modify_user_id = ?, last_modify_user_real_name = ? WHERE classify_id=? `
	err = global.DmSQL["data"].Exec(sql, classifyName, userId, userRealName, classifyId).Error

	return
}

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

// TODO: sql优化，后续解决
func GetEdbInfoCountByClassifyId(classifyId int) (count int, err error) {
	sql := `WITH RECURSIVE classify_cte(classify_id, parent_id) AS (
    SELECT classify_id, parent_id  
    FROM edb_classify  
    WHERE parent_id = ?
  
    UNION ALL  

    SELECT e.classify_id, e.parent_id  
    FROM edb_classify e  
    INNER JOIN classify_cte c ON e.parent_id = c.classify_id  
)  
SELECT COUNT(1) AS COUNT  
FROM edb_info AS a  
WHERE a.classify_id IN (SELECT classify_id FROM classify_cte);`
	err = global.DmSQL["data"].Raw(sql, classifyId).Scan(&count).Error

	return
}

func DeleteEdbClassify(classifyId int) (err error) {
	sql := `WITH RECURSIVE ClassifyHierarchy ( classify_id, parent_id ) AS 
     (
      SELECT classify_id, 
             parent_id
        FROM edb_classify
       WHERE parent_id = ? OR classify_id=?
     UNION ALL
         SELECT e.classify_id, 
                e.parent_id
           FROM edb_classify e
     INNER JOIN ClassifyHierarchy ch 
             ON e.parent_id = ch.classify_id
     )
DELETE 
  FROM edb_classify
 WHERE classify_id IN ( SELECT classify_id
          FROM ClassifyHierarchy
       );`
	err = global.DmSQL["data"].Exec(sql, classifyId, classifyId).Error

	return
}

func GetEdbClassifyByParentId(parentId int, classifyType int8) (items []*EdbClassifyItems, err error) {
	o := global.DmSQL["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).Scan(&items).Error

	return
}

func GetEdbClassifyByRootIdLevel(rootId int, classifyType uint8, orderStr string) (items []*EdbClassifyItems, err error) {
	o := global.DmSQL["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).Scan(&items).Error

	return
}

// GetAllEdbClassify 获取所有的普通指标的分类列表（包含第一级）
func GetAllEdbClassify() (items []*EdbClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM edb_classify WHERE  classify_type = 0  order by sort asc,classify_id asc`
	err = o.Raw(sql).Scan(&items).Error

	return
}

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

	return
}

// GetAllEdbClassifyByType 根据类型获取分类列表
func GetAllEdbClassifyByType(classifyType int) (items []*EdbClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM edb_classify WHERE classify_type = ? order by sort ASC,classify_id ASC`
	err = o.Raw(sql, classifyType).Scan(&items).Error

	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    `gorm:"-"`
	Button           EdbClassifyItemsButton `gorm:"-" description:"操作权限"`
	IsJoinPermission int                    `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	HaveOperaAuth    bool                   `description:"是否有数据权限"`
}

type EdbClassifyIdItems struct {
	ClassifyId       int `description:"分类id"`
	ClassifyName     string
	ClassifyNameEn   string
	UniqueCode       string `description:"唯一编码"`
	ParentId         int    `description:"父级分类id"`
	Level            int    `description:"层级"`
	RootId           int    `description:"顶级分类id"`
	IsJoinPermission int    `description:"是否加入权限管控，0：不加入；1：加入；默认：0" json:"-"`
	HaveOperaAuth    bool   `description:"是否有该数据权限，默认：false"`
}

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:"提示信息"`
	TableList    []*ExcelBaseInfo `description:"关联的表格"`
}
type ExcelBaseInfo struct {
	ExcelInfoId     int    `orm:"column(excel_info_id);pk" gorm:"primaryKey" `
	Source          int    `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
	ExcelType       int    `description:"表格类型，1：指标列，2：日期列，默认：1"`
	ExcelName       string `description:"表格名称"`
	UniqueCode      string `description:"表格唯一编码"`
	ExcelClassifyId int    `description:"表格分类id"`
	//SysUserId       int    `description:"操作人id"`
	//SysUserRealName string `description:"操作人真实姓名"`
}
type ClassifyDeleteCheckReq struct {
	ClassifyId int `description:"分类id"`
	EdbInfoId  int `description:"指标id"`
}

func GetClassifyCountByClassifyId(classifyId int) (count int, err error) {
	o := global.DmSQL["data"]
	sql := `WITH RECURSIVE classify_cte(classify_id, parent_id) AS (  
  SELECT classify_id, parent_id  
  FROM edb_classify  
  WHERE parent_id = ?  
  
  UNION ALL  
  
  SELECT e.classify_id, e.parent_id  
  FROM edb_classify e  
  INNER JOIN classify_cte c ON e.parent_id = c.classify_id  
)  
SELECT COUNT(1) AS COUNT  
FROM classify_cte  
WHERE classify_id <> ? ;`
	err = o.Raw(sql, classifyId, classifyId).Scan(&count).Error

	return
}

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

// 递归获取所有目录
func GetClassifyAllByClassifyId(classifyId int) (resultList []*EdbClassifySimplify, err error) {
	for {
		sql := ` SELECT * FROM edb_classify AS a
			WHERE a.classify_id=? order by sort asc,classify_id asc`
		item := new(EdbClassifySimplify)
		err = global.DmSQL["data"].Raw(sql, classifyId).Scan(&item).Error
		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"`
	ClassifyType     int `description:"分类类型：0-指标库；2-计算指标"`
}

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

	return
}

// UpdateEdbClassifySortByParentId 根据父类id更新排序
func UpdateEdbClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string, classifyType uint8) (err error) {
	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 = global.DmSQL["data"].Exec(sql, parentId, nowSort, classifyType).Error

	return
}

// Update 更新分类基础信息
func (edbClassify *EdbClassify) Update(cols []string) (err error) {
	err = global.DmSQL["data"].Select(cols).Updates(edbClassify).Error

	return
}

// GetEdbClassifyMaxSort 获取分类下最大的排序数
func GetEdbClassifyMaxSort(parentId int, classifyType uint8) (sort int, err error) {
	sql := `SELECT COALESCE(Max(sort), 0) AS sort FROM edb_classify WHERE parent_id=? AND classify_type=? `
	err = global.DmSQL["data"].Raw(sql, parentId, classifyType).Scan(&sort).Error

	return
}

// GetEdbInfoMaxSortByClassifyId 获取分类下指标的最大的排序数
func GetEdbInfoMaxSortByClassifyId(classifyId int) (sort int, err error) {
	sql := `SELECT COALESCE(Max(sort), 0) AS sort FROM edb_info WHERE classify_id=? `
	err = global.DmSQL["data"].Raw(sql, classifyId).Scan(&sort).Error

	return
}

type ClassifyEdbInfoListResp struct {
	EdbInfoList []*EdbClassifyItems
}

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

	return
}

func GetEdbClassifyByParentIdTwo(classifyType int8) (items []*EdbClassifyItems, err error) {
	sql := ` SELECT * FROM edb_classify WHERE level=2 AND classify_type = ?  order by sort asc,classify_id asc`
	err = global.DmSQL["data"].Raw(sql, classifyType).Scan(&items).Error

	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) {
	sql := `SELECT COALESCE(MAX(sort), 0) AS sort FROM edb_classify WHERE parent_id=? AND parent_id_two=? AND classify_type=? `
	err = global.DmSQL["data"].Raw(sql, parentId, classifyType).Scan(&sort).Error

	return
}

func GetEdbClassifyByLevel(level, source int) (items []*EdbClassifyItems, err error) {
	sql := ` SELECT * FROM edb_classify WHERE level=? AND classify_type = ? `
	err = global.DmSQL["data"].Raw(sql, level, source).Scan(&items).Error

	return
}

func FIXEdbClassifyId(newId, oldId int) (err error) {
	sql := ` UPDATE edb_info SET classify_id=? WHERE classify_id=? `
	err = global.DmSQL["data"].Exec(sql, newId, oldId).Error

	return
}

func EditEdbClassifySysUser(classifyId, sysUserId int, chartClassifyName string) (err error) {
	sql := `UPDATE edb_classify SET sys_user_id=?,sys_user_real_name=?,modify_time=NOW() WHERE parent_id=?  AND level=3 `
	err = global.DmSQL["data"].Exec(sql, sysUserId, chartClassifyName, classifyId).Error

	return
}

func UpdateEdbClassifyChildByParentClassifyId(classifyIds []int, rootId int, levelStep int) (err error) {
	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 = global.DmSQL["data"].Exec(sql, pars...).Error
	if err != nil {
		return
	}

	return
}

// GetEdbClassifyByParentIdAndName 根据父级ID和名称获取分类
func GetEdbClassifyByParentIdAndName(parentId int, classifyName string, classifyId int, classifyType uint8) (item *EdbClassify, err error) {
	sql := `SELECT * FROM edb_classify WHERE parent_id = ? AND classify_name = ? AND classify_id <> ? and classify_type=? LIMIT 1`
	err = global.DmSQL["data"].Raw(sql, parentId, classifyName, classifyId, classifyType).First(&item).Error

	return
}

// GetEdbClassifyByIdList
// @Description: 根据分类id列表获取分类列表
// @author: Roc
// @datetime 2024-03-29 10:30:21
// @param classifyId int
// @return item *EdbClassify
// @return err error
func GetEdbClassifyByIdList(classifyIdList []int) (item []*EdbClassify, err error) {
	num := len(classifyIdList)
	if num <= 0 {
		return
	}
	sql := `SELECT * FROM edb_classify WHERE classify_id in (` + utils.GetOrmInReplace(num) + `) `
	err = global.DmSQL["data"].Raw(sql, classifyIdList).Scan(&item).Error

	return
}

// GetEdbClassifyByClassifyTypeAndIsJoinPermission
// @Description: 根据分类类型和是否涉密获取分类列表
// @author: Roc
// @datetime 2024-03-29 10:30:21
// @param classifyType int
// @param isJoinPermission int
// @return items []*EdbClassify
// @return err error
func GetEdbClassifyByClassifyTypeAndIsJoinPermission(classifyType, isJoinPermission int) (items []*EdbClassify, err error) {
	sql := `SELECT * FROM edb_classify WHERE  classify_type = ? AND is_join_permission = ? `
	err = global.DmSQL["data"].Raw(sql, classifyType, isJoinPermission).Scan(&items).Error

	return
}

// GetEdbClassifyRootIdsByClassifyIds 获取普通指标的顶级分类列表
func GetEdbClassifyRootIdsByClassifyIds(classifyIds []int) (items []int, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT distinct root_id FROM edb_classify WHERE classify_type=0 and classify_id in (` + utils.GetOrmInReplace(len(classifyIds)) + `)`
	err = o.Raw(sql, classifyIds).Scan(&items).Error

	return
}

// GetChildEdbClassifyByClassifyType 根据类型获取非顶级分类
func GetChildEdbClassifyByClassifyType(classifyType int) (items []*EdbClassifyItems, err error) {
	o := global.DmSQL["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).Find(&items).Error
	return
}

// GetIsJoinPermissionEdbClassifyIdList
// @Description: 获取已经加入指标管控的指标分类id列表
// @param classifyType
// @return list
// @return err
func GetIsJoinPermissionEdbClassifyIdList(classifyType int) (list []int, err error) {
	pars := make([]interface{}, 0)
	sql := ` SELECT classify_id FROM edb_classify WHERE 1=1  AND is_join_permission = 1 `
	if classifyType >= 0 {
		sql += ` AND classify_type = ? `
		pars = append(pars, classifyType)
	}
	err = global.DmSQL["data"].Raw(sql, pars...).Scan(&list).Error

	return
}

// GetEdbClassifyByClassifyTypes 根据类型IDs获取分类
func GetEdbClassifyByClassifyTypes(classifyTypes []int) (items []*EdbClassifyItems, err error) {
	if len(classifyTypes) == 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := `SELECT * FROM edb_classify WHERE classify_type IN (?) ORDER BY classify_type ASC, sort ASC, classify_id ASC`
	err = o.Raw(sql, classifyTypes).Scan(&items).Error
	return
}