eta_index_lib/models/edb_data_base.go

package models

import (
	"eta/eta_index_lib/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strings"
	"time"
)

// 指标检索数据
type EdbDataItem struct {
	EdbCode   string `description:"指标编码"`
	StartDate string `description:"起始日期"`
	EndDate   string `description:"终止日期"`
	EdbName   string `description:"指标名称"`
	Unit      string `description:"单位"`
	Frequency string `description:"频率"`
	DataList  []*EdbData
}

type EdbData struct {
	EdbDataId     int `orm:"column(edb_data_id);pk"`
	EdbInfoId     int
	EdbCode       string
	DataTime      string
	Value         string
	Status        int
	CreateTime    time.Time
	ModifyTime    time.Time
	DataTimestamp int64
}

func GetAddSql(edbInfoId, edbCode, dataTime, timestampStr string, value string) (addSql string) {
	nowStr := time.Now().Format(utils.FormatDateTime)
	addSql += "("
	addSql += edbInfoId + "," + "'" + edbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" +
		"," + "'" + nowStr + "'"
	addSql += "," + "'" + timestampStr + "'"
	addSql += "),"
	return
}

type AddEdbInfoReq struct {
	EdbCode   string `description:"指标编码"`
	Source    int    `description:"指标来源ID"`
	StockCode string `description:"证券代码"`
	Frequency string `description:"频度"`
}

// GetEdbInfoCountByCondition 获取指标数量
func GetEdbInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS count FROM edb_info WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetEdbDataAllByEdbCode 根据指标编码获取指标数据列表
func GetEdbDataAllByEdbCode(edbCode string, source, subSource, limit int) (items []*EdbInfoSearchData, err error) {
	var pars []interface{}
	pars = append(pars, edbCode)
	o := orm.NewOrm()

	tableName := GetEdbDataTableName(source, subSource)
	sql := ` SELECT * FROM %s WHERE edb_code=? ORDER BY data_time DESC`
	if limit > 0 {
		sql += `  LIMIT ?  `
		pars = append(pars, limit)
	}
	sql = fmt.Sprintf(sql, tableName)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func GetEdbDataByCondition(source, subSource int, condition string, pars []interface{}) (items []*EdbInfoSearchData, err error) {
	o := orm.NewOrm()
	tableName := GetEdbDataTableName(source, subSource)
	sql := ` SELECT * FROM %s WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY data_time DESC `
	sql = fmt.Sprintf(sql, tableName)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// GetEdbDataByDate 根据数据日期获取指标数据
func GetEdbDataByDate(source, subSource int, edbCode, dataTime string) (item *EdbInfoSearchData, err error) {
	o := orm.NewOrm()
	tableName := GetEdbDataTableName(source, subSource)
	sql := ` SELECT * FROM %s WHERE 1=1 AND edb_code = ? AND data_time =? ORDER BY data_time DESC `
	sql = fmt.Sprintf(sql, tableName)
	err = o.Raw(sql, edbCode, dataTime).QueryRow(&item)
	return
}

func ModifyEdbDataById(source, subSource, edbDataId int, value string) (err error) {
	o := orm.NewOrm()
	tableName := GetEdbDataTableName(source, subSource)
	sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_data_id=? `
	sql = fmt.Sprintf(sql, tableName)
	_, err = o.Raw(sql, value, edbDataId).Exec()
	return
}

func DeleteEdbDataById(source, subSource, edbDataId int) (err error) {
	sql := ` DELETE FROM %s WHERE edb_data_id=? `
	tableName := GetEdbDataTableName(source, subSource)
	sql = fmt.Sprintf(sql, tableName)

	o := orm.NewOrm()
	_, err = o.Raw(sql, edbDataId).Exec()
	return
}

type RefreshEdbInfoReq struct {
	EdbInfoId int    `description:"指标ID"`
	EdbCode   string `description:"指标编码"`
	StartDate string `description:"开始日期"`
	Source    int    `description:"指标来源ID"`
}

// GetAllEdbDataList 获取所有的指标数据列表
func GetAllEdbDataList(edbInfoId, source, subSource int) (existDataList []*EdbData, err error) {
	o := orm.NewOrm()
	dataTableName := GetEdbDataTableName(source, subSource)
	fmt.Println("dataTableName:", dataTableName)
	sql := `SELECT * FROM %s WHERE edb_info_id=? order by data_time asc`
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList)
	return
}

// GetAllEdbDataListByTo 获取所有的指标数据列表
func GetAllEdbDataListByTo(to orm.TxOrmer, edbInfoId, source, subSource int) (existDataList []*EdbData, err error) {
	dataTableName := GetEdbDataTableName(source, subSource)
	fmt.Println("dataTableName:", dataTableName)
	sql := `SELECT * FROM %s WHERE edb_info_id=? order by data_time asc`
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
	return
}

// GetFinalLastByTo 获取所有的指标数据列表
func GetFinalLastByTo(to orm.TxOrmer, edbInfoId, source, subSource int, latestDate string) (finalLast EdbInfoSearchData, err error) {
	dataTableName := GetEdbDataTableName(source, subSource)
	sql := fmt.Sprintf(` SELECT data_time , value FROM %s WHERE edb_info_id=? and data_time<=? ORDER BY data_time DESC `, dataTableName)
	err = to.Raw(sql, edbInfoId, latestDate).QueryRow(&finalLast)
	if err != nil && err.Error() != utils.ErrNoRow() {
		return
	}
	return
}

// 新版本
type EdbDataV1 struct {
	EdbDataId     int `orm:"column(edb_data_id);pk"`
	EdbInfoId     int
	EdbCode       string
	DataTime      string
	Value         string
	Status        int
	CreateTime    time.Time
	ModifyTime    time.Time
	DataTimestamp int64
}

type EdbDataList struct {
	EdbDataId     int     `description:" 指标数据ID"`
	EdbInfoId     int     `description:"指标ID"`
	DataTime      string  //`json:"-" description:"数据日期"`
	DataTimestamp int64   `description:"数据日期"`
	Value         float64 `description:"数据值"`
}

// GetEdbDataList
// @Description:
// @author: Roc
// @datetime 2024-05-08 16:10:49
// @param source int
// @param subSource int
// @param endInfoId int
// @param startDate string
// @param endDate string
// @return list []*EdbDataList
// @return err error
func GetEdbDataList(source, subSource, endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
	list = make([]*EdbDataList, 0)
	tmpDataList, err := GetEdbDataListAll(source, subSource, FindEdbDataListAllCond{
		EdbInfoId:         endInfoId,
		StartDataTime:     startDate,
		StartDataTimeCond: ">=",
		EndDataTime:       endDate,
		EndDataTimeCond:   "<=",
	}, 1)
	if err != nil {
		return
	}

	// 数据拼接
	for _, v := range tmpDataList {
		list = append(list, &EdbDataList{
			EdbDataId:     v.EdbDataId,
			EdbInfoId:     v.EdbInfoId,
			DataTime:      v.DataTime,
			DataTimestamp: v.DataTimestamp,
			Value:         v.Value,
		})
	}

	return
}

type AddEdbBaseInfoReq struct {
	EdbCode         string `description:"指标编码"`
	EdbName         string `description:"指标名称"`
	Unit            string `description:"单位"`
	ClassifyId      int    `description:"所属分类"`
	SysUserId       int    `description:"用户id"`
	SysUserRealName string `description:"用户真实名称"`
}

// EditEdbBaseInfoReq 编辑基础指标请求参数
type EditEdbBaseInfoReq struct {
	SysUserId       int    `description:"用户id"`
	SysUserRealName string `description:"用户真实名称"`
	EdbInfoId       int    `description:"指标id"`
	EdbName         string `description:"指标名称"`
	EdbNameEn       string `description:"英文指标名称"`
	Frequency       string `description:"频率"`
	Unit            string `description:"单位"`
	UnitEn          string `description:"英文单位"`
	ClassifyId      int    `description:"分类id"`
}

func DelEdbDataByMysql(to orm.TxOrmer, edbInfoId int, tableName string, removeDateList []string) (err error) {
	if len(removeDateList) <= 0 {
		return
	}
	// 移除不存在的日期数据
	removeDateStr := strings.Join(removeDateList, `","`)
	removeDateStr = `"` + removeDateStr + `"`
	sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (%s) `, tableName, removeDateStr)
	_, err = to.Raw(sql, edbInfoId).Exec()
	return
}