eta_api/models/sandbox/sandbox_classify.go

package sandbox

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

type SandboxClassify struct {
	SandboxClassifyId   int       `gorm:"primaryKey;column:sandbox_classify_id;type:int(11);not null"`
	SandboxClassifyName string    `gorm:"column:sandbox_classify_name;type:varchar(255);not null;default:''"` // 分类名称
	ParentId            int       `gorm:"column:parent_id;type:int(11);not null;default:0"`                   // 父级id
	HasData             int       `gorm:"column:has_data;type:tinyint(4);default:0"`                          // 是否存在指标数据:1有，2：无
	CreateTime          time.Time `gorm:"column:create_time;type:datetime"`                                   // 创建时间
	ModifyTime          time.Time `gorm:"column:modify_time;type:datetime"`                                   // 修改时间
	SysUserId           int       `gorm:"column:sys_user_id;type:int(11)"`                                    // 创建人id
	SysUserRealName     string    `gorm:"column:sys_user_real_name;type:varchar(50)"`                         // 创建人姓名
	Level               int       `gorm:"column:level;type:int(11);default:0"`                                // 层级
	Sort                int       `gorm:"column:sort;type:int(9) unsigned;default:10"`                        // 排序字段，越小越靠前，默认值：10
	ChartPermissionId   int       `gorm:"column:chart_permission_id;type:int(9) unsigned;not null;default:0"` // 品种id
	ChartPermissionName string    `gorm:"column:chart_permission_name;type:varchar(32);not null;default:''"`  // 品种名称（冗余字段，避免列表页查询时再去关联表查询）
}

func AddSandboxClassify(item *SandboxClassify) (lastId int64, err error) {
	err = global.DmSQL["data"].Create(item).Error
	return
}

// GetSandboxClassifyByParentId
func GetSandboxClassifyByParentId(parentId int) (items []*SandboxClassifyItems, err error) {
	sql := ` SELECT * FROM sandbox_classify WHERE parent_id=? order by sort asc,sandbox_classify_id asc`
	err = global.DmSQL["data"].Raw(sql, parentId).Find(&items).Error
	return
}

// GetSandboxClassifyAll
func GetSandboxClassifyAll() (items []*SandboxClassifyItems, err error) {
	sql := ` SELECT * FROM sandbox_classify WHERE parent_id<>0 order by sort asc,sandbox_classify_id asc`
	err = global.DmSQL["data"].Raw(sql).Find(&items).Error
	return
}

type SandboxClassifyItems struct {
	SandboxClassifyId   int                     `gorm:"primaryKey;column:sandbox_classify_id;type:int(11);not null"`
	SandboxClassifyName string                  `gorm:"column:sandbox_classify_name;type:varchar(255);not null;default:''"` // 分类名称
	ParentId            int                     `gorm:"column:parent_id;type:int(11);not null;default:0"`                   // 父级id
	HasData             int                     `gorm:"column:has_data;type:tinyint(4);default:0"`                          // 是否存在指标数据:1有，2：无
	CreateTime          time.Time               `gorm:"column:create_time;type:datetime"`                                   // 创建时间
	ModifyTime          time.Time               `gorm:"column:modify_time;type:datetime"`                                   // 修改时间
	SysUserId           int                     `gorm:"column:sys_user_id;type:int(11)"`                                    // 创建人id
	SysUserRealName     string                  `gorm:"column:sys_user_real_name;type:varchar(50)"`                         // 创建人姓名
	Level               int                     `gorm:"column:level;type:int(11);default:0"`                                // 层级
	Sort                int                     `gorm:"column:sort;type:int(9) unsigned;default:10"`                        // 排序字段，越小越靠前，默认值：10
	ChartPermissionId   int                     `gorm:"column:chart_permission_id;type:int(9) unsigned;not null;default:0"` // 品种id
	ChartPermissionName string                  `gorm:"column:chart_permission_name;type:varchar(32);not null;default:''"`  // 品种名称（冗余字段，避免列表页查询时再去关联表查询）
	Children            []*SandboxClassifyItems `gorm:"-"`
	SandboxId           int                     `gorm:"column:sandbox_id" description:"沙盘id"`
}

type SandboxClassifyListResp struct {
	AllNodes []*SandboxClassifyItems
}

type AddSandboxClassifyReq struct {
	SandboxClassifyName string `description:"分类名称"`
	ParentId            int    `description:"父级id,第一级传0"`
	Level               int    `description:"层级,第一级传0，其余传上一级的层级"`
	ChartPermissionId   int    `description:"品种id"`
	ChartPermissionName string `description:"品种名称"`
}

func GetSandboxClassifyCount(sandboxClassifyName string, parentId int) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM sandbox_classify WHERE parent_id=? AND sandbox_classify_name=? `
	err = global.DmSQL["data"].Raw(sql, parentId, sandboxClassifyName).Scan(&count).Error
	return
}

// GetSandboxClassifyMaxSort 获取沙盘分类下最大的排序数
func GetSandboxClassifyMaxSort(parentId int) (sort int, err error) {
	sql := `SELECT COALESCE(MAX(sort), 0) AS sort FROM sandbox_classify WHERE parent_id=? `
	err = global.DmSQL["data"].Raw(sql, parentId).Scan(&sort).Error
	return
}

type EditSandboxClassifyReq struct {
	SandboxClassifyName string `description:"分类名称"`
	SandboxClassifyId   int    `description:"分类id"`
	ChartPermissionId   int    `description:"品种id"`
	ChartPermissionName string `description:"品种名称"`
}

func GetSandboxClassifyById(classifyId int) (item *SandboxClassify, err error) {
	sql := `SELECT * FROM sandbox_classify WHERE sandbox_classify_id=? `
	err = global.DmSQL["data"].Raw(sql, classifyId).First(&item).Error
	return
}

func EditSandboxClassify(classifyId, ChartPermissionId int, sandboxClassifyName, ChartPermissionName string) (err error) {
	sql := `UPDATE sandbox_classify SET sandbox_classify_name=?,chart_permission_id = ?, chart_permission_name = ?, modify_time=NOW() WHERE sandbox_classify_id=? `
	err = global.DmSQL["data"].Exec(sql, sandboxClassifyName, ChartPermissionId, ChartPermissionName, classifyId).Error
	return
}

type SandboxClassifyDeleteCheckReq struct {
	SandboxClassifyId int `description:"分类id"`
}

func GetSandboxInfoCountByClassifyId(classifyId int) (count int, err error) {
	var sql string
	var pars []interface{}
	sql = `WITH RECURSIVE classify_tree (sandbox_classify_id, parent_id, level) AS (
    -- 基础查询：获取起始节点
    SELECT sandbox_classify_id, parent_id, 1 as level
    FROM sandbox_classify
    WHERE sandbox_classify_id = ?
    
    UNION ALL
    
    -- 递归查询：获取所有子节点
    SELECT c.sandbox_classify_id, c.parent_id, t.level + 1
    FROM sandbox_classify c
    INNER JOIN classify_tree t ON c.parent_id = t.sandbox_classify_id
    WHERE c.parent_id IS NOT NULL
)
SELECT COUNT(1) AS count 
FROM sandbox a
WHERE a.sandbox_classify_id IN (
    SELECT sandbox_classify_id 
    FROM classify_tree
) 
AND a.is_delete = 0`
	pars = append(pars, classifyId, classifyId)
	err = global.DmSQL["data"].Raw(sql, pars...).Scan(&count).Error
	return
}

type SandboxClassifyDeleteCheckResp struct {
	DeleteStatus int    `description:"检测状态:0:默认值，如果为0，继续走其他校验，1:该分类下关联图表不可删除,2:确认删除当前目录及包含的子目录吗"`
	TipsMsg      string `description:"提示信息"`
}

type DeleteSandboxClassifyReq struct {
	SandboxClassifyId int `description:"分类id"`
	SandboxId         int `description:"指标id"`
}

func DeleteSandboxClassify(classifyId int) (err error) {
	var pars []interface{}
	var sql string
	sql = `WITH RECURSIVE sandbox_classify_cte (sandbox_classify_id, parent_id) AS (
			SELECT sandbox_classify_id, parent_id
			FROM sandbox_classify
			WHERE parent_id = ?
			UNION ALL
			SELECT c.sandbox_classify_id, c.parent_id
			FROM sandbox_classify c
			INNER JOIN sandbox_classify_cte ct ON c.parent_id = ct.sandbox_classify_id
		)
		DELETE FROM sandbox_classify
		WHERE sandbox_classify_id IN (
			SELECT sandbox_classify_id
			FROM sandbox_classify_cte
			UNION
			SELECT sandbox_classify_id
			FROM sandbox_classify
			WHERE sandbox_classify_id = ?
		)`
	pars = append(pars, classifyId, classifyId)
	err = global.DmSQL["data"].Exec(sql, pars...).Error

	return
}

// MoveSandboxClassifyReq 移动沙盘分类请求参数
type MoveSandboxClassifyReq struct {
	ClassifyId       int `description:"分类id"`
	SandboxId        int `description:"沙盘ID"`
	ParentClassifyId int `description:"父级分类id 移动沙盘时为目标分类id"`
	PrevId           int `description:"上一个兄弟节点分类id"`
	NextId           int `description:"下一个兄弟节点分类id"`
	PrevType         int `description:"上一个兄弟节点类型 1分类 2沙盘 "`
	NextType         int `description:"上一个兄弟节点类型 1分类 2沙盘 "`
}

// UpdateSandboxClassifySortByParentId 根据沙盘父类id更新排序
func UpdateSandboxClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string) (err error) {
	sql := ` update sandbox_classify set sort = ` + updateSort + ` WHERE parent_id=? and sort > ? `
	if classifyId > 0 {
		sql += ` or ( sandbox_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)`
	}
	err = global.DmSQL["data"].Exec(sql, parentId, nowSort).Error
	return
}

// GetFirstSandboxClassifyByParentId 获取当前父级沙盘分类下的排序第一条的数据
func GetFirstSandboxClassifyByParentId(parentId int) (item *SandboxClassify, err error) {
	sql := ` SELECT * FROM sandbox_classify WHERE parent_id=? order by sort asc,sandbox_classify_id asc limit 1`
	err = global.DmSQL["data"].Raw(sql, parentId).First(&item).Error
	return
}

// Update 更新沙盘分类基础信息
func (sandboxClassify *SandboxClassify) Update(cols []string) (err error) {
	err = global.DmSQL["data"].Select(cols).Updates(sandboxClassify).Error
	return
}

// GetSandboxClassifyAndInfoByParentId
func GetSandboxClassifyAndInfoByParentId(parentId int) (items []*SandboxClassifyItems, err error) {
	sql := ` SELECT
	0 AS sandbox_id,
	sandbox_classify_id,
	sandbox_classify_name,
	parent_id,
	create_time,
	modify_time,
	sys_user_id,
	sys_user_real_name AS sys_user_name,
	sort,
	level,
	chart_permission_id,
	chart_permission_name,
	0 AS is_delete
FROM
	sandbox_classify 
WHERE
	parent_id = ? UNION ALL
SELECT
	sandbox_id,
	sandbox_classify_id,
	name AS sandbox_classify_name,
	0 AS parent_id,
	create_time,
	modify_time,
	sys_user_id,
	sys_user_name,
	sort,
	0 AS level,
	chart_permission_id,
	chart_permission_name,
	is_delete 
FROM
	sandbox 
WHERE
	sandbox_classify_id = ? AND is_delete = 0
ORDER BY
	sort ASC,
	sandbox_classify_id ASC`
	err = global.DmSQL["data"].Raw(sql, parentId, parentId).Find(&items).Error
	return
}

type SandboxLinkCheckReq struct {
	EdbInfoIdList   []int `description:"指标id列表"`
	ChartInfoIdList []int `description:"图库id列表"`
	ReportIdList    []int `description:"报告id列表"`
}

type SandboxLinkCheckItem struct {
	Id         int    `description:"id"`
	Name       string `description:"名称"`
	UniqueCode string `description:"唯一编码"`
	ClassifyId int    `description:"分类id"`
}

type SandboxLinkCheckResp struct {
	EdbInfoIdList   []*SandboxLinkCheckItem `description:"指标id列表"`
	ChartInfoIdList []*SandboxLinkCheckItem `description:"图库id列表"`
	ReportIdList    []*SandboxLinkCheckItem `description:"报告id列表"`
}

// GetSandboxClassifySubcategories 获取所有子级分类id
func GetSandboxClassifySubcategories(classifyId int) (Ids string, err error) {
	sql := `WITH RECURSIVE Subcategories (sandbox_classify_id) AS (  
    SELECT ? AS sandbox_classify_id  
    FROM DUAL  
    UNION ALL  
    SELECT sc.sandbox_classify_id  
    FROM sandbox_classify sc  
    JOIN Subcategories s  
    ON sc.parent_id = s.sandbox_classify_id  
)  
SELECT LISTAGG(sandbox_classify_id, ',') WITHIN GROUP (ORDER BY sandbox_classify_id) AS ids  
FROM Subcategories;`
	err = global.DmSQL["data"].Raw(sql, classifyId).First(&Ids).Error
	return
}

// UpdateSandboxClassifyChartPermissionById 根据沙盘id更新品种
func UpdateSandboxClassifyChartPermissionById(ChartPermissionId int, ChartPermissionName, Ids string) (err error) {
	sql := ` UPDATE sandbox_classify SET chart_permission_id = ?, chart_permission_name = ? WHERE sandbox_classify_id IN ( ` + Ids + ` ) `
	err = global.DmSQL["data"].Exec(sql, ChartPermissionId, ChartPermissionName).Error
	return
}

// GetAllSandboxClassify
func GetAllSandboxClassify() (items []*SandboxClassifyItems, err error) {
	sql := ` SELECT * FROM sandbox_classify `
	err = global.DmSQL["data"].Raw(sql).Find(&items).Error
	return
}