eta_api/models/data_manage/base_from_ly_data.go

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

import (
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"strings"
)

type BaseFromLyData struct {
	BaseFromLyDataId  int     `orm:"column(base_from_ly_data_id);pk" description:"数据ID"`
	CreateTime        string  `orm:"column(create_time)" description:"创建时间"`
	ModifyTime        string  `orm:"column(modify_time)" description:"修改时间"`
	BaseFromLyIndexId int     `orm:"column(base_from_ly_index_id)" description:"指标id"`
	IndexCode         string  `orm:"column(index_code)" description:"指标编码"`
	DataTime          string  `orm:"column(data_time)" description:"数据日期"`
	Value             float64 `orm:"column(value)" description:"数据值"`
}

func init() {
	orm.RegisterModel(new(BaseFromLyData))
}

type BaseFromLyDataPage struct {
	List   []*BaseFromLyData  `description:"指标数据列表"`
	Paging *paging.PagingItem `description:"分页数据"`
}

// GetLyDataCountByIndexId 获取指标数据总数
func GetLyDataCountByIndexId(indexId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT count(*) FROM base_from_ly_data WHERE base_from_ly_index_id=?`
	err = o.Raw(sql, indexId).QueryRow(&count)
	return
}

// GetLyDataPageByIndexId 获取指标数据分页列表
func GetLyDataPageByIndexId(indexId int, startSize, pageSize int) (items []*BaseFromLyData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_ly_data WHERE base_from_ly_index_id=? ORDER BY data_time desc LIMIT ?,?`
	_, err = o.Raw(sql, indexId, (startSize-1)*pageSize, pageSize).QueryRows(&items)
	return
}

// GetBaseFromLyDataByIndexCode 根据指标编码查询
func GetBaseFromLyDataByIndexCode(indexCode string) (items []*BaseFromLyData, err error) {
	sql := `SELECT * FROM base_from_ly_data WHERE index_code=? ORDER BY data_time desc`
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, indexCode).QueryRows(&items)
	return
}

func GetLyDataListByIndexCodes(IndexCodes string) (items []string, err error) {
	sql := ` SELECT data_time FROM base_from_ly_data WHERE index_code IN(` + IndexCodes + `)  GROUP BY data_time DESC `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetLyLastUpdateTimeLastByIndexCode 根据指标编码查询 返回ModifyTime最后一条数据
func GetLyLastUpdateTimeLastByIndexCode(indexCodes []string) (items []*BaseFromLyData, err error) {
	o := orm.NewOrmUsingDB("data")

	// 将 indexCodes 切片转换为逗号分隔的字符串
	placeholders := strings.Repeat("?,", len(indexCodes)-1) + "?"

	// 构造 SQL 查询
	sql := `SELECT index_code, MAX(modify_time) AS modify_time
            FROM base_from_ly_data
            WHERE index_code IN (` + placeholders + `)
            GROUP BY index_code`

	// 执行 SQL 查询
	_, err = o.Raw(sql, indexCodes).QueryRows(&items)
	if err != nil {
		return nil, err
	}
	return items, nil
}

// GetLyLastDataTimeByIndexCode 根据指标编码查询 返回data_time最后一条数据的value
func GetLyLastDataTimeByIndexCode(indexCodes []string) (items []*BaseFromLyData, err error) {
	o := orm.NewOrmUsingDB("data")

	// 将 indexCodes 切片转换为逗号分隔的字符串
	placeholders := strings.Repeat("?,", len(indexCodes)-1) + "?"

	// 构造 SQL 查询
	sql := `
       SELECT t1.*
       FROM base_from_ly_data t1
       INNER JOIN (
           SELECT index_code, MAX(data_time) AS data_time
			FROM base_from_ly_data
			WHERE index_code IN (` + placeholders + `)
			GROUP BY index_code
       ) t2 
       ON t1.index_code = t2.index_code AND t1.data_time = t2.data_time
   `

	// 执行 SQL 查询
	_, err = o.Raw(sql, indexCodes).QueryRows(&items)
	if err != nil {
		return nil, err
	}
	return items, nil
}