eta_api/models/material/material_classify.go

package material

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"time"
)

type MaterialClassify struct {
	ClassifyId      int       `orm:"column(classify_id);pk"`
	ClassifyName    string    `description:"分类名称"`
	ParentId        int       `description:"父级id"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"修改时间"`
	SysUserId       int       `description:"创建人id"`
	SysUserRealName string    `description:"创建人姓名"`
	Level           int       `description:"层级"`
	Sort            int       `description:"排序字段，越小越靠前，默认值：10"`
}

func AddMaterialClassify(item *MaterialClassify) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	lastId, err = o.Insert(item)
	return
}

// GetMaterialClassifyByParentId
func GetMaterialClassifyByParentId(parentId int) (items []*MaterialClassifyItems, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM material_classify WHERE parent_id=? order by sort asc,classify_id asc`
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	return
}

// GetMaterialClassifyAll
func GetMaterialClassifyAll() (items []*MaterialClassifyItems, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM material_classify WHERE parent_id<>0 order by sort asc,classify_id asc`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type MaterialClassifyItems struct {
	ClassifyId   int       `orm:"column(classify_id);pk"`
	ClassifyName string    `description:"分类名称"`
	ParentId     int       `description:"父级id"`
	CreateTime   time.Time `description:"创建时间"`
	ModifyTime   time.Time `description:"修改时间"`
	SysUserId    int       `description:"创建人id"`
	SysUserName  string    `description:"创建人姓名"`
	Level        int       `description:"层级"`
	Sort         int       `description:"排序字段，越小越靠前，默认值：10"`
	Children     []*MaterialClassifyItems
}

type MaterialClassifyListResp struct {
	AllNodes []*MaterialClassifyItems
}

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

func GetMaterialClassifyCount(ClassifyName string, parentId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM material_classify WHERE parent_id=? AND classify_name=? `
	err = o.Raw(sql, parentId, ClassifyName).QueryRow(&count)
	return
}

// GetMaterialClassifyMaxSort 获取沙盘分类下最大的排序数
func GetMaterialClassifyMaxSort(parentId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT Max(sort) AS sort FROM material_classify WHERE parent_id=? `
	err = o.Raw(sql, parentId).QueryRow(&sort)
	return
}

type EditMaterialClassifyReq struct {
	ClassifyName        string `description:"分类名称"`
	ClassifyId          int    `description:"分类id"`
	ChartPermissionId   int    `description:"品种id"`
	ChartPermissionName string `description:"品种名称"`
}

func GetMaterialClassifyById(classifyId int) (item *MaterialClassify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM material_classify WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

func EditMaterialClassify(classifyId, ChartPermissionId int, ClassifyName, ChartPermissionName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE material_classify SET classify_name=?,chart_permission_id = ?, chart_permission_name = ?, modify_time=NOW() WHERE classify_id=? `
	_, err = o.Raw(sql, ClassifyName, ChartPermissionId, ChartPermissionName, classifyId).Exec()
	return
}

type MaterialClassifyDeleteCheckReq struct {
	ClassifyId int `description:"分类id"`
}

func GetMaterialInfoCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT COUNT(1) AS count FROM sandbox AS a
				WHERE a.classify_id IN(
				SELECT t.classify_id FROM 
				(
				SELECT rd.*
				FROM (SELECT * FROM material_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 material_classify WHERE classify_id = @pid 
				)AS t
				) AND a.is_delete = 0 `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

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

type DeleteMaterialClassifyReq struct {
	ClassifyId int `description:"分类id"`
	MaterialId int `description:"素材id"`
}

func DeleteMaterialClassify(classifyId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` DELETE FROM material_classify
				WHERE classify_id IN(
				SELECT t.classify_id FROM
				(
				SELECT rd.*
				FROM (SELECT * FROM material_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 material_classify WHERE classify_id = @pid
				)AS t
				) `
	_, err = o.Raw(sql, classifyId).Exec()
	return
}

// MoveMaterialClassifyReq 移动沙盘分类请求参数
type MoveMaterialClassifyReq struct {
	ClassifyId       int `description:"分类id"`
	MaterialId       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沙盘 "`
}

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

// GetFirstMaterialClassifyByParentId 获取当前父级沙盘分类下的排序第一条的数据
func GetFirstMaterialClassifyByParentId(parentId int) (item *MaterialClassify, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM material_classify WHERE parent_id=? order by sort asc,classify_id asc limit 1`
	err = o.Raw(sql, parentId).QueryRow(&item)
	return
}

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

// GetMaterialClassifyAndInfoByParentId
func GetMaterialClassifyAndInfoByParentId(parentId int) (items []*MaterialClassifyItems, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT
	0 AS sandbox_id,
	classify_id,
	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
	material_classify 
WHERE
	parent_id = ? UNION ALL
SELECT
	sandbox_id,
	classify_id,
	name AS 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
	classify_id = ? AND is_delete = 0
ORDER BY
	sort ASC,
	classify_id ASC`
	_, err = o.Raw(sql, parentId, parentId).QueryRows(&items)
	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列表"`
}

// 获取所有子级分类id
func GetMaterialClassifySubcategories(classifyId int) (Ids string, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT GROUP_CONCAT(classify_id) AS ids
FROM (
SELECT @pv := ? AS classify_id
UNION ALL
SELECT sc.classify_id
FROM material_classify sc
JOIN (SELECT @pv := ?) initial
WHERE sc.parent_id = @pv
) subcategories; `
	err = o.Raw(sql, classifyId, classifyId).QueryRow(&Ids)
	return
}

// UpdateMaterialClassifyChartPermissionById 根据沙盘id更新品种
func UpdateMaterialClassifyChartPermissionById(ChartPermissionId int, ChartPermissionName, Ids string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE material_classify SET chart_permission_id = ?, chart_permission_name = ? WHERE classify_id IN ( ` + Ids + ` ) `
	_, err = o.Raw(sql, ChartPermissionId, ChartPermissionName).Exec()
	return
}