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 (
	"eta/eta_api/utils"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"strings"
	"time"
)

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

func (m *BaseFromLyData) ToItem() (item *BaseFromLyDataItem) {
	return &BaseFromLyDataItem{
		BaseFromLyDataId:  m.BaseFromLyDataId,
		CreateTime:        m.CreateTime.Format(utils.FormatDateTime),
		ModifyTime:        m.ModifyTime.Format(utils.FormatDateTime),
		BaseFromLyIndexId: m.BaseFromLyIndexId,
		IndexCode:         m.IndexCode,
		DataTime:          m.DataTime.Format(utils.FormatDate),
		Value:             m.Value,
	}
}

type BaseFromLyDataItem 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   []*BaseFromLyDataItem `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 []*BaseFromLyDataItem, err error) {
	var ormList []*BaseFromLyData
	items = make([]*BaseFromLyDataItem, 0)
	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(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

// GetBaseFromLyDataByIndexCode 根据指标编码查询
func GetBaseFromLyDataByIndexCode(indexCode string) (items []*BaseFromLyDataItem, err error) {
	var ormList []*BaseFromLyData
	items = make([]*BaseFromLyDataItem, 0)
	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(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

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

// GetLyLastUpdateTimeLastByIndexCode 根据指标编码查询 返回ModifyTime最后一条数据
func GetLyLastUpdateTimeLastByIndexCode(indexCodes []string) (items []*BaseFromLyDataItem, err error) {
	var ormList []*BaseFromLyData
	items = make([]*BaseFromLyDataItem, 0)

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

	return items, nil
}

// GetLyLastDataTimeByIndexCode 根据指标编码查询 返回data_time最后一条数据的value
func GetLyLastDataTimeByIndexCode(indexCodes []string) (items []*BaseFromLyDataItem, err error) {
	var ormList []*BaseFromLyData
	items = make([]*BaseFromLyDataItem, 0)

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

	return items, nil
}