eta_api/models/data_manage/base_from_ly_index.go

// Package data_manage
// @Author gmy 2024/8/7 9:38:00
package data_manage

import (
	"eta/eta_api/utils"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

type BaseFromLyIndex struct {
	BaseFromLyIndexId    int       `orm:"column(base_from_ly_index_id);pk" description:"指标ID"`
	CreateTime           time.Time `orm:"column(create_time)" description:"创建时间"`
	ModifyTime           time.Time `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-是"`
	StartDate            time.Time `orm:"column(start_date)"`
	EndDate              time.Time `orm:"column(end_date)"`
	LatestValue          float64   `orm:"column(latest_value)" description:"数据值"`
}

func (m *BaseFromLyIndex) ToIndexAndData() (item *BaseFromLyIndexAndData) {
	return &BaseFromLyIndexAndData{
		BaseFromLyIndexId:    m.BaseFromLyIndexId,
		CreateTime:           m.CreateTime.Format(utils.FormatDateTime),
		ModifyTime:           m.ModifyTime.Format(utils.FormatDateTime),
		BaseFromLyClassifyId: m.BaseFromLyClassifyId,
		IndexCode:            m.IndexCode,
		IndexName:            m.IndexName,
		Frequency:            m.Frequency,
		Unit:                 m.Unit,
		EdbExist:             m.EdbExist,
		//ModifyTimeMax:        "",
		//Value:                0,
	}
}

// 在 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) {
	num := len(classifyIds)
	if num <= 0 {
		return
	}
	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) {
	var ormList []*BaseFromLyIndex
	items = make([]*BaseFromLyIndexAndData, 0)

	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(&ormList)
	if err != nil {
		return nil, err
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToIndexAndData())
	}

	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
}