// Package data_manage // @Author gmy 2024/8/7 9:38:00 package data_manage import ( "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" ) type BaseFromLyIndex struct { BaseFromLyIndexId int `orm:"column(base_from_ly_index_id);pk" description:"指标ID"` CreateTime string `orm:"column(create_time)" description:"创建时间"` ModifyTime string `orm:"column(modify_time)" description:"修改时间"` BaseFromLyClassifyId int `orm:"column(base_from_ly_classify_id)" description:"原始数据指标分类id"` IndexCode string `orm:"column(index_code)" description:"指标编码"` IndexName string `orm:"column(index_name)" description:"指标名称"` Frequency string `orm:"column(frequency)" description:"频度"` Unit string `orm:"column(unit)" description:"单位"` EdbExist int `orm:"column(edb_exist)" description:"指标库是否已添加:0-否;1-是"` } // 在 init 函数中注册模型 func init() { orm.RegisterModel(new(BaseFromLyIndex)) } type BaseFromLyIndexPage struct { List []*BaseFromLyIndexAndData `description:"指标列表"` Paging *paging.PagingItem `description:"分页数据"` } type BaseFromLyIndexBatchAddCheckReq struct { IndexCodes []string `form:"IndexCodes" description:"指标编码列表"` } type BaseFromLyIndexNameCheck struct { IndexCode string `from:"IndexCode" description:"指标编码"` IndexName string `from:"IndexName" description:"指标名称"` } type NameCheckResult struct { IndexCode string `from:"EdbCode" description:"edb编码"` IndexName string `from:"EdbName" description:"edb名称"` Exist bool } type BaseFromLyIndexAndData struct { BaseFromLyIndexId int `orm:"column(base_from_ly_index_id);pk" description:"指标ID"` CreateTime string `orm:"column(create_time)" description:"创建时间"` ModifyTime string `orm:"column(modify_time)" description:"修改时间"` BaseFromLyClassifyId int `orm:"column(base_from_ly_classify_id)" description:"原始数据指标分类id"` IndexCode string `orm:"column(index_code)" description:"指标编码"` IndexName string `orm:"column(index_name)" description:"指标名称"` Frequency string `orm:"column(frequency)" description:"频度"` Unit string `orm:"column(unit)" description:"单位"` EdbExist int `orm:"column(edb_exist)" description:"指标库是否已添加:0-否;1-是"` ModifyTimeMax string `json:"modify_time_max" description:"最后修改时间"` Value float64 `orm:"column(value)" description:"数据值"` } type IndexCheckData struct { IndexCode string `orm:"column(index_code)" description:"指标编码"` IndexName string `orm:"column(index_name)" description:"指标名称"` Frequency string `orm:"column(frequency)" description:"频度"` Unit string `orm:"column(unit)" description:"单位"` EdbInfoId int `json:"edb_info_id" description:"指标库主键id"` UniqueCode string `json:"unique_code" description:"指标库唯一编码"` ClassifyId int `json:"classify_id" description:"分类id"` } // GetLyIndexByClassifyIds 通过分类ids查询指标列表 func GetLyIndexByClassifyIds(classifyIds []int) (items []*BaseFromLyIndex, err error) { o := orm.NewOrmUsingDB("data") // 创建查询条件 qs := o.QueryTable("base_from_ly_index") // 使用 Filter 进行查询 _, err = qs.Filter("base_from_ly_classify_id__in", classifyIds).All(&items) return } // GetLyIndexCount 获取指标总数 func GetLyIndexCount(classifyId string, searchParam string) (count int, err error) { o := orm.NewOrmUsingDB("data") // 构建 SQL 查询语句 sql := `SELECT COUNT(*) FROM base_from_ly_index WHERE 1=1` var params []interface{} // 添加 classifyId 过滤条件 if classifyId != "" { sql += ` AND base_from_ly_classify_id = ?` params = append(params, classifyId) } // 添加搜索条件 if searchParam != "" { sql += ` AND (index_name LIKE ? OR index_code = ?)` params = append(params, "%"+searchParam+"%", searchParam) } // 执行查询 err = o.Raw(sql, params...).QueryRow(&count) if err != nil { return 0, err } return count, nil } // GetLyIndexPage 获取指标列表 func GetLyIndexPage(classifyId string, searchParam string, currentIndex, pageSize int) (items []*BaseFromLyIndexAndData, err error) { o := orm.NewOrmUsingDB("data") // 构建 SQL 查询语句 sql := `SELECT * FROM base_from_ly_index WHERE 1=1` var params []interface{} // 添加 classifyId 过滤条件 if classifyId != "" { sql += ` AND base_from_ly_classify_id = ?` params = append(params, classifyId) } // 添加搜索条件 if searchParam != "" { sql += ` AND (index_name LIKE ? OR index_code = ?)` params = append(params, "%"+searchParam+"%", searchParam) } // 添加排序和分页条件 sql += ` ORDER BY base_from_ly_index_id DESC LIMIT ?, ?` params = append(params, (currentIndex-1)*pageSize, pageSize) // 执行查询 _, err = o.Raw(sql, params...).QueryRows(&items) if err != nil { return nil, err } return } // UpdateLyIndexEdbExist 指标库标记已添加 func UpdateLyIndexEdbExist(indexCode string, isExist int) (err error) { o := orm.NewOrmUsingDB("data") // 构建 SQL 更新语句 sql := `UPDATE base_from_ly_index SET edb_exist = ? WHERE index_code = ?` // 执行 SQL 语句 _, err = o.Raw(sql, isExist, indexCode).Exec() if err != nil { return err } return nil } // GetLyIndexList 根据传入条件查询指标列表 func GetLyIndexList(condition string, pars interface{}) (items []*BaseFromLyIndex, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM base_from_ly_index WHERE 1=1 ` if condition != "" { sql += condition } sql += `ORDER BY base_from_ly_index_id ASC ` _, err = o.Raw(sql, pars).QueryRows(&items) return } // GetLyDataMaxCount 获取分类下指标最大数据量 func GetLyDataMaxCount(classifyId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := `SELECT MAX(t.num) AS count FROM ( SELECT COUNT(1) AS num FROM base_from_ly_index AS a INNER JOIN base_from_ly_data AS b ON a.base_from_ly_index_id=b.base_from_ly_index_id WHERE a.base_from_ly_classify_id=? GROUP BY a.base_from_ly_index_id )AS t ` err = o.Raw(sql, classifyId).QueryRow(&count) return } // GetLyIndexByCodeAndClassify 根据指标编码和分类查询 indexCode非必传 func GetLyIndexByCodeAndClassify(indexCode string, classifyId int, frequency *string) (items []*BaseFromLyIndex, err error) { o := orm.NewOrmUsingDB("data") // SQL 查询语句 sql := `SELECT a.index_code, a.index_name, a.frequency, a.unit, MAX(b.modify_time) AS modify_time FROM base_from_ly_index AS a INNER JOIN base_from_ly_data AS b ON a.base_from_ly_index_id = b.base_from_ly_index_id WHERE 1=1` var params []interface{} if classifyId != 0 { sql += ` AND a.base_from_ly_classify_id = ?` params = append(params, classifyId) } // 如果 indexCode 不为空,增加过滤条件 if indexCode != "" { sql += ` AND a.index_code = ?` params = append(params, indexCode) } if frequency != nil { sql += ` AND a.frequency = ?` params = append(params, *frequency) } sql += ` GROUP BY a.index_code, a.index_name, a.frequency, a.unit` _, err = o.Raw(sql, params...).QueryRows(&items) if err != nil { return nil, err } return } // GetLyIndexFrequency 获取指标频度 func GetLyIndexFrequency(classifyId int) (items []*string, err error) { sql := `SELECT DISTINCT frequency FROM base_from_ly_index WHERE frequency IS NOT NULL` // 如果 classifyId > 0,则添加该条件 if classifyId > 0 { sql += ` AND base_from_ly_classify_id = ?` } sql += ` ORDER BY FIELD(frequency, '日度', '周度', '月度', '季度', '半年度', '年度')` o := orm.NewOrmUsingDB("data") if classifyId > 0 { _, err = o.Raw(sql, classifyId).QueryRows(&items) } else { _, err = o.Raw(sql).QueryRows(&items) } return items, err }