eta_api/models/data_manage/base_from_clarksons_data.go

package data_manage

import (
	"database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"gorm.io/gorm"
	"time"
)

type BaseFromClarksonsData struct {
	BaseFromClarksonsDataId  int       `gorm:"column:base_from_clarksons_data_id;primaryKey"`
	BaseFromClarksonsIndexId int       `description:"指标id"`
	IndexCode                string    `description:"指标编码"`
	DataTime                 string    `description:"数据日期"`
	Value                    float64   `description:"数据值"`
	CreateTime               time.Time `description:"创建时间"`
	ModifyTime               time.Time `description:"修改时间"`
	DataTimestamp            int64     `description:"数据时间戳"`
}

func (baseFromClarksonsData *BaseFromClarksonsData) AfterFind(tx *gorm.DB) (err error) {
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		baseFromClarksonsData.DataTime = utils.GormDateStrToDateStr(baseFromClarksonsData.DataTime)
	}
	return
}

// GetClarksonsDataByIndexId 根据指标id获取指标数据
func GetClarksonsDataByIndexId(indexId int) (items []*BaseFromClarksonsData, err error) {
	sql := ` SELECT * FROM base_from_clarksons_data WHERE base_from_clarksons_index_id=? ORDER BY data_time DESC`
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexId).Find(&items).Error
	return
}

// GetClarksonsDataDataTimeByIndexId 根据指标id获取指标数据的日期列表
func GetClarksonsDataDataTimeByIndexId(indexIdList []int) (items []string, err error) {
	if len(indexIdList) == 0 {
		return
	}
	sql := ` SELECT DISTINCT data_time FROM base_from_clarksons_data WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIdList)) + `) ORDER BY data_time DESC`
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexIdList).Find(&items).Error
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		for i, item := range items {
			items[i] = utils.GormDateStrToDateStr(item)
		}
	}
	return
}

func GetClarksonsIndexDataByCode(indexCode string) (items []*BaseFromClarksonsData, err error) {
	sql := ` SELECT *  FROM base_from_clarksons_data WHERE index_code=? ORDER BY data_time DESC  `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexCode).Find(&items).Error
	return
}

func GetClarksonsIndexDataCount(indexCode string) (count int, err error) {
	sqlStr := ` SELECT COUNT(1) AS count  FROM base_from_clarksons_data WHERE index_code=? `
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, indexCode).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

// GetClarksonsLastUpdateTimeLastByIndexCode 根据指标编码查询 返回ModifyTime最后一条数据
func GetClarksonsLastUpdateTimeLastByIndexCode(indexCodes []string) (items []*BaseFromClarksonsData, err error) {
	// 构造 SQL 查询
	sql := `SELECT t1.index_code, t1.data_time, t2.value
			FROM (
    			SELECT index_code, MAX(data_time) AS data_time
   				 FROM base_from_clarksons_data
    			WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodes)) + `)
    			GROUP BY index_code
			) AS t1
			JOIN base_from_clarksons_data AS t2 ON t1.index_code = t2.index_code AND t1.data_time = t2.data_time`

	// 执行 SQL 查询
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexCodes).Find(&items).Error
	if err != nil {
		return nil, err
	}
	return items, nil
}

func GetClarksonsIndexData(indexCode string, startSize, pageSize int) (items []*BaseFromClarksonsData, err error) {
	sql := ` SELECT *  FROM base_from_clarksons_data WHERE index_code=? ORDER BY data_time DESC LIMIT ?,? `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexCode, startSize, pageSize).Find(&items).Error
	return
}