package data_manage import ( "eta/eta_api/utils" "fmt" "time" "github.com/beego/beego/v2/client/orm" ) type ChartClassify struct { ChartClassifyId int `orm:"column(chart_classify_id);pk"` ChartClassifyName string `description:"分类名称"` ParentId 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"` Source int `description:"1：ETA图库；2：商品价格曲线"` IsJoinPermission int `description:"是否加入权限管控，0：不加入；1：加入；默认：0"` ChartClassifyNameEn string `description:"英文分类名称"` RootId int `description:"顶级ID"` } func AddChartClassify(item *ChartClassify) (lastId int64, err error) { o := orm.NewOrmUsingDB("data") lastId, err = o.Insert(item) if err != nil { return } item.ChartClassifyId = int(lastId) return } type AddChartClassifyReq struct { ChartClassifyName string `description:"分类名称"` ParentId int `description:"父级id,第一级传0"` Level int `description:"层级,第一级传0，其余传上一级的层级"` } // GetChartClassifyCount // @Description: 根据图表分类名称获取对应的数量 // @author: Roc // @datetime 2024-04-16 18:13:40 // @param chartClassifyName string // @param parentId int // @param source int // @return count int // @return err error func GetChartClassifyCount(chartClassifyName string, parentId, source int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT COUNT(1) AS count FROM chart_classify WHERE parent_id=? AND source = ? AND chart_classify_name=? ` err = o.Raw(sql, parentId, source, chartClassifyName).QueryRow(&count) return } // GetChartClassifyEnCount // @Description: 根据图表分类英文名称获取对应的数量 // @author: Roc // @datetime 2024-04-16 18:13:24 // @param chartClassifyNameEn string // @param parentId int // @param source int // @return count int // @return err error func GetChartClassifyEnCount(chartClassifyNameEn string, parentId, source int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT COUNT(1) AS count FROM chart_classify WHERE parent_id=? AND source = ? AND chart_classify_name_en = ? ` err = o.Raw(sql, parentId, source, chartClassifyNameEn).QueryRow(&count) return } type EditChartClassifyReq struct { ChartClassifyName string `description:"分类名称"` ChartClassifyId int `description:"分类id"` ParentId int `description:"父级分类id"` } func GetChartClassifyById(classifyId int) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE chart_classify_id=? ` err = o.Raw(sql, classifyId).QueryRow(&item) return } func GetChartClassifyCountById(classifyId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT count(1) AS count FROM chart_classify WHERE chart_classify_id=? ` err = o.Raw(sql, classifyId).QueryRow(&count) return } // EditChartClassify // @Description: 修改图表分类的名称 // @author: Roc // @datetime 2024-04-16 18:12:51 // @param classifyId int // @param source int // @param chartClassifyName string // @return err error func EditChartClassify(classifyId, source int, chartClassifyName string) (err error) { o := orm.NewOrmUsingDB("data") sql := `UPDATE chart_classify SET chart_classify_name=?,modify_time=NOW() WHERE chart_classify_id=? AND source = ? ` _, err = o.Raw(sql, chartClassifyName, classifyId, source).Exec() return } // EditChartClassifyEn // @Description: 修改图表分类的英文名称 // @author: Roc // @datetime 2024-04-16 18:12:40 // @param classifyId int // @param source int // @param chartClassifyNameEn string // @return err error func EditChartClassifyEn(classifyId, source int, chartClassifyNameEn string) (err error) { o := orm.NewOrmUsingDB("data") sql := `UPDATE chart_classify SET chart_classify_name_en = ?,modify_time=NOW() WHERE chart_classify_id=? AND source = ? ` _, err = o.Raw(sql, chartClassifyNameEn, classifyId, source).Exec() return } type DeleteChartClassifyReq struct { ChartClassifyId int `description:"分类id"` ChartInfoId int `description:"指标id"` } func GetChartInfoCountByClassifyId(classifyId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM chart_info AS a WHERE a.chart_classify_id IN( SELECT t.chart_classify_id FROM ( SELECT rd.* FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd, (SELECT @pid := ?) pd WHERE FIND_IN_SET(parent_id, @pid) > 0 AND @pid := CONCAT(@pid, ',', chart_classify_id) UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid )AS t ) ` err = o.Raw(sql, classifyId).QueryRow(&count) return } func DeleteChartClassify(classifyId int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` DELETE FROM chart_classify WHERE chart_classify_id IN( SELECT t.chart_classify_id FROM ( SELECT rd.* FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd, (SELECT @pid := ?) pd WHERE FIND_IN_SET(parent_id, @pid) > 0 AND @pid := CONCAT(@pid, ',', chart_classify_id) UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid )AS t ) ` _, err = o.Raw(sql, classifyId).Exec() return } // GetChartClassifyByParentId // @param source int 1：ETA图库；2：商品价格曲线；3：相关性图表 func GetChartClassifyByParentId(parentId, source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id=? AND source = ? order by sort asc,chart_classify_id asc` _, err = o.Raw(sql, parentId, source).QueryRows(&items) return } // GetChartClassifyAll // @param source int 1：ETA图库；2：商品价格曲线；3：相关性图表 func GetChartClassifyAll(source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id<>0 AND source = ? order by sort asc,chart_classify_id asc` _, err = o.Raw(sql, source).QueryRows(&items) return } // GetChartClassifyBySource 获取图表分类列表 func GetChartClassifyBySource(source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE source = ? ORDER BY level ASC,chart_classify_id ASC` _, err = o.Raw(sql, source).QueryRows(&items) return } type ChartClassifyItems struct { ChartClassifyId int `description:"分类id"` ChartInfoId int `description:"指标id"` ChartClassifyName string ChartClassifyNameEn string ParentId int Level int `description:"层级"` Sort int `description:"排序字段，越小越靠前，默认值：10"` UniqueCode string `description:"唯一编码"` Source int `description:"来源id"` SourceName string `description:"来源名称"` SysUserId int `description:"创建人id"` SysUserRealName string `description:"创建人姓名"` DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` ChartType int `description:"生成样式:1:曲线图,2:季节性图"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` Children []*ChartClassifyItems Button ChartClassifyItemsButton `description:"按钮权限"` IsJoinPermission int `description:"是否加入权限管控，0：不加入；1：加入；默认：0"` HaveOperaAuth bool `description:"是否有数据权限，默认：false"` Disable bool `description:"勾选是否禁用"` } // ChartClassifyItemsButton 操作按钮 type ChartClassifyItemsButton struct { AddButton bool `description:"是否可添加"` OpButton bool `description:"是否可编辑"` DeleteButton bool `description:"是否可删除"` MoveButton bool `description:"是否可移动"` } type ChartClassifyListResp struct { AllNodes []*ChartClassifyItems Language string `description:"指标的展示语言，CN：中文，EN：英文"` CanOpClassify bool `description:"是否允许操作分类"` } type ChartClassifyDeleteCheckResp struct { DeleteStatus int `description:"检测状态:0:默认值，如果为0，继续走其他校验，1:该分类下关联图表不可删除,2:确认删除当前目录及包含的子目录吗"` TipsMsg string `description:"提示信息"` } type ChartClassifyDeleteCheckReq struct { ChartClassifyId int `description:"分类id"` ChartInfoId int `description:"指标id"` } func GetChartClassifyCountByClassifyId(chartClassifyId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM ( SELECT rd.* FROM (SELECT * FROM chart_classify WHERE parent_id IS NOT NULL) rd, (SELECT @pid := ?) pd WHERE FIND_IN_SET(parent_id, @pid) > 0 AND @pid := CONCAT(@pid, ',', chart_classify_id) UNION SELECT * FROM chart_classify WHERE chart_classify_id = @pid )AS t WHERE t.chart_classify_id<>? ` err = o.Raw(sql, chartClassifyId, chartClassifyId).QueryRow(&count) return } func GetChartClassifyByCondition(condition string, pars []interface{}) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&item) return } // MoveChartClassifyReq 移动图表分类请求参数 type MoveChartClassifyReq struct { ClassifyId int `description:"分类id"` ParentClassifyId int `description:"父级分类id"` PrevClassifyId int `description:"上一个兄弟节点分类id"` NextClassifyId int `description:"下一个兄弟节点分类id"` ChartInfoId int `description:"图表ID, 如果图表ID有值，则移动对象为图表，否则认为移动对象分类"` PrevChartInfoId int `description:"上一个图表ID"` NextChartInfoId int `description:"下一个图表ID"` } // GetFirstChartClassifyByParentId 获取当前父级图表分类下的排序第一条的数据 func GetFirstChartClassifyByParentId(parentId int) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id=? order by sort asc,chart_classify_id asc limit 1` err = o.Raw(sql, parentId).QueryRow(&item) return } // GetFirstChartClassifyByParentIdAndSource 获取当前父级图表分类下的排序第一条的数据 func GetFirstChartClassifyByParentIdAndSource(parentId, source int) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id=? and source = ? order by sort asc,chart_classify_id asc limit 1` err = o.Raw(sql, parentId, source).QueryRow(&item) return } // UpdateChartClassifySortByParentId 根据图表父类id更新排序 func UpdateChartClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update chart_classify set sort = ` + updateSort + ` WHERE parent_id=? and sort > ? ` if classifyId > 0 { sql += ` or ( chart_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)` } _, err = o.Raw(sql, parentId, nowSort).Exec() return } // Update 更新图表分类基础信息 func (chartClassify *ChartClassify) Update(cols []string) (err error) { o := orm.NewOrmUsingDB("data") _, err = o.Update(chartClassify, cols...) return } // GetChartClassifyMaxSort 获取图表分类下最大的排序数 func GetChartClassifyMaxSort(parentId, source int) (sort int, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT Max(sort) AS sort FROM chart_classify WHERE parent_id=? AND source = ? ` err = o.Raw(sql, parentId, source).QueryRow(&sort) return } type ChartClassifyView struct { ChartClassifyId int `orm:"column(chart_classify_id);pk"` ChartClassifyName string `description:"分类名称"` ParentId int `description:"父级id"` } func GetChartClassifyViewById(classifyId int) (item *ChartClassifyView, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE chart_classify_id=? ` err = o.Raw(sql, classifyId).QueryRow(&item) return } func GetChartClassifyByParentIdFromETA(parentId int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id=? AND source = 1 order by sort asc,chart_classify_id asc` _, err = o.Raw(sql, parentId).QueryRows(&items) return } func GetChartClassifyAllFromETA() (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE parent_id<>0 AND source = 1 order by sort asc,chart_classify_id asc` _, err = o.Raw(sql).QueryRows(&items) return } func GetChartClassifyTwo(source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE level=3 AND source = ? order by sort asc,chart_classify_id asc` _, err = o.Raw(sql, source).QueryRows(&items) return } func GetChartClassifyByLevel(level, source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE level=? AND source = ? order by sort asc,chart_classify_id asc` _, err = o.Raw(sql, level, source).QueryRows(&items) return } func EditChartClassifySysUser(classifyId, sysUserId int, chartClassifyName string) (err error) { o := orm.NewOrmUsingDB("data") sql := `UPDATE chart_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 GetAllChartClassifyItemsBySource(source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE source = ? order by sort ASC,chart_classify_id ASC` _, err = o.Raw(sql, source).QueryRows(&items) return } // GetCrossVarietyChartClassifyBySysUserId // @Description: 根据创建人获取跨品种分析的分类 // @author: Roc // @datetime 2023-11-24 14:05:43 // @param sysUserId int // @return item *ChartClassify // @return err error func GetCrossVarietyChartClassifyBySysUserId(sysUserId int) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE source = ? AND sys_user_id=?` err = o.Raw(sql, utils.CHART_SOURCE_CROSS_HEDGING, sysUserId).QueryRow(&item) return } // GetChartClassifyByParentIdAndName 根据父级ID和名称获取分类 func GetChartClassifyByParentIdAndName(parentId int, classifyName string, classifyId int) (item *ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE parent_id = ? AND chart_classify_name = ? AND chart_classify_id <> ? LIMIT 1` err = o.Raw(sql, parentId, classifyName, classifyId).QueryRow(&item) return } // GetChartClassifyByIdList // @Description: 根据分类ID列表获取分类 // @author: Roc // @datetime 2024-04-01 13:21:48 // @param classifyId []int // @return items []*ChartClassify // @return err error func GetChartClassifyByIdList(classifyIdList []int) (items []*ChartClassify, err error) { num := len(classifyIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE chart_classify_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, classifyIdList).QueryRows(&items) return } // GetChartClassifyBySourceAndIsJoinPermission // @Description: 根据分类类型和是否涉密获取分类列表 // @author: Roc // @datetime 2024-03-29 10:30:21 // @param source int // @param isJoinPermission int // @return items []*ChartClassify // @return err error func GetChartClassifyBySourceAndIsJoinPermission(source, isJoinPermission int) (items []*ChartClassify, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT * FROM chart_classify WHERE source = ? AND is_join_permission = ? ` _, err = o.Raw(sql, source, isJoinPermission).QueryRows(&items) return } type ChartClassifyIdItems struct { ChartClassifyId int `description:"分类id"` ChartClassifyName string `description:"分类名称"` 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"` } func GetChartClassifyByRootIdLevel(rootId, source int, orderStr string) (items []*ChartClassifyIdItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE root_id = ? AND source = ? ` if orderStr != "" { sql += orderStr } else { sql += ` order by level desc, sort asc, chart_classify_id asc` } _, err = o.Raw(sql, rootId, source).QueryRows(&items) return } // UpdateChartClassifySortByParentIdAndSource 根据图表父类id更新排序 func UpdateChartClassifySortByParentIdAndSource(parentId, classifyId, nowSort int, updateSort string, source int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update chart_classify set sort = ` + updateSort + ` WHERE parent_id=? and sort > ? AND source = ? ` if classifyId > 0 { sql += ` or ( chart_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)` } _, err = o.Raw(sql, parentId, nowSort, source).Exec() return } // GetChartClassifyAllBySource 根据来源获取所有分类 func GetChartClassifyAllBySource(source int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE source = ? ORDER BY parent_id ASC, sort ASC, chart_classify_id ASC` _, err = o.Raw(sql, source).QueryRows(&items) return } // GetChartClassifyIdListByAdminId // @Description: 根据用户id和指标类型获取其关联的所有指标分类id列表 // @author: Roc // @datetime 2024-09-11 15:52:48 // @param adminId int // @param source int // @return chartClassifyIdList []int // @return err error func GetChartClassifyIdListByAdminId(adminId, source int) (chartClassifyIdList []int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT chart_classify_id FROM chart_info WHERE sys_user_id = ? AND source = ?` pars := []interface{}{adminId, source} sql += ` group by chart_classify_id ` _, err = o.Raw(sql, pars).QueryRows(&chartClassifyIdList) return } // GetChartClassifyAndInfoByParentId func GetChartClassifyAndInfoByParentId(parentId int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT 0 AS chart_info_id, chart_classify_id, chart_classify_name, chart_classify_name_en, parent_id, create_time, modify_time, sys_user_id, sys_user_real_name, sort, level, unique_code, source, 0 as date_type, '' as start_date, '' as end_date, 0 as chart_type, '' as calendar, '' as season_start_date, '' as season_end_date FROM chart_classify WHERE parent_id = ? and source = 1 UNION ALL SELECT chart_info_id, chart_classify_id, chart_name AS chart_classify_name, chart_name_en AS chart_classify_name_en, 0 AS parent_id, create_time, modify_time, sys_user_id, sys_user_real_name, sort, 0 AS level, unique_code, source, date_type, start_date, end_date, chart_type, calendar, season_start_date, season_end_date FROM chart_info WHERE chart_classify_id = ? AND source = 1 ORDER BY sort ASC, chart_classify_id ASC` _, err = o.Raw(sql, parentId, parentId).QueryRows(&items) return } // GetChartClassifyAndInfoByParentId func GetChartClassifyAndInfoByParentIdForMe(parentId, adminId int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT 0 AS chart_info_id, chart_classify_id, chart_classify_name, chart_classify_name_en, parent_id, create_time, modify_time, sys_user_id, sys_user_real_name, sort, level, unique_code FROM chart_classify WHERE parent_id = ? and source = 1 UNION ALL SELECT chart_info_id, chart_classify_id, chart_name AS chart_classify_name, chart_name_en AS chart_classify_name_en, 0 AS parent_id, create_time, modify_time, sys_user_id, sys_user_real_name, sort, 0 AS level, unique_code FROM chart_info WHERE chart_classify_id = ? AND sys_user_id = ? AND source = 1 ORDER BY sort ASC, chart_classify_id ASC` _, err = o.Raw(sql, parentId, parentId, adminId).QueryRows(&items) return } func GetChartClassifiesById(chartClassifyId int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_classify WHERE chart_classify_id = ? OR parent_id = ? OR parent_id IN ( SELECT chart_classify_id FROM chart_classify WHERE parent_id = ? );` _, err = o.Raw(sql, chartClassifyId, chartClassifyId, chartClassifyId).QueryRows(&items) return }