hongze_yb/models/tables/edb_data/query.go

package edb_data

import (
	"errors"
	"fmt"
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/utils"
	"strconv"
)

// 指标数据->存储表
func GetEdbDataTableName(source int) (tableName string) {
	switch source {
	case utils.DATA_SOURCE_THS:
		tableName = "edb_data_ths"
	case utils.DATA_SOURCE_WIND:
		tableName = "edb_data_wind"
	case utils.DATA_SOURCE_PB, utils.DATA_SOURCE_PB_FINANCE:
		tableName = "edb_data_pb"
	case utils.DATA_SOURCE_CALCULATE:
		tableName = "edb_data_calculate"
	case utils.DATA_SOURCE_CALCULATE_LJZZY:
		tableName = "edb_data_calculate_ljzzy"
	case utils.DATA_SOURCE_CALCULATE_TBZ:
		tableName = "edb_data_calculate_tbz"
	case utils.DATA_SOURCE_CALCULATE_TCZ:
		tableName = "edb_data_calculate_tcz"
	case utils.DATA_SOURCE_CALCULATE_NSZYDPJJS:
		tableName = "edb_data_calculate_nszydpjjs"
	case utils.DATA_SOURCE_MANUAL:
		tableName = "edb_data_manual"
	case utils.DATA_SOURCE_LZ:
		tableName = "edb_data_lz"
	case utils.DATA_SOURCE_YS:
		tableName = "edb_data_ys"
	case utils.DATA_SOURCE_CALCULATE_HBZ:
		tableName = "edb_data_calculate_hbz"
	case utils.DATA_SOURCE_CALCULATE_HCZ:
		tableName = "edb_data_calculate_hcz"
	case utils.DATA_SOURCE_CALCULATE_BP:
		tableName = "edb_data_calculate_bp"
	case utils.DATA_SOURCE_GL:
		tableName = "edb_data_gl"
	case utils.DATA_SOURCE_ZZ:
		tableName = "edb_data_zz"
	case utils.DATA_SOURCE_DL:
		tableName = "edb_data_dl"
	case utils.DATA_SOURCE_SH:
		tableName = "edb_data_sh"
	case utils.DATA_SOURCE_CFFEX:
		tableName = "edb_data_cffex"
	case utils.DATA_SOURCE_SHFE:
		tableName = "edb_data_ine"
	case utils.DATA_SOURCE_GIE:
		tableName = "edb_data_gie"
	case utils.DATA_SOURCE_CALCULATE_ZJPJ:
		tableName = "edb_data_calculate_zjpj"
	case utils.DATA_SOURCE_CALCULATE_TIME_SHIFT:
		tableName = "edb_data_calculate_time_shift"
	case utils.DATA_SOURCE_CALCULATE_LJZTBPJ:
		tableName = "edb_data_calculate_ljztbpj"
	case utils.DATA_SOURCE_LT:
		tableName = "edb_data_lt"
	case utils.DATA_SOURCE_COAL:
		tableName = "edb_data_coal"
	case utils.DATA_SOURCE_PYTHON:
		tableName = "edb_data_python"
	case utils.DATA_SOURCE_GOOGLE_TRAVEL:
		tableName = "edb_data_google_travel"
	case utils.DATA_SOURCE_PREDICT_CALCULATE:
		tableName = "edb_data_predict_calculate"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_TBZ:
		tableName = "edb_data_predict_calculate_tbz"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_TCZ:
		tableName = "edb_data_predict_calculate_tcz"
	case utils.DATA_SOURCE_MYSTEEL_CHEMICAL:
		tableName = "edb_data_mysteel_chemical"
	default:
		tableName = ""
	}
	return
}

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

type EdbDataItems struct {
	Items                []*EdbDataList
	Year                 int
	BetweenDay           int   `json:"-" description:"公历与农历之间相差的天数"`
	CuttingDataTimestamp int64 `description:"切割的时间戳"`
}

type EdbDataResult struct {
	List []*EdbDataItems
}

type QuarterData struct {
	Year                 int
	DataList             []*EdbDataList
	CuttingDataTimestamp int64 `description:"切割的时间戳"`
}

// GetEdbDataList 获取指标数据
func GetEdbDataList(source, endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
	tableName := GetEdbDataTableName(source)
	if tableName == "" {
		err = errors.New("无效的渠道：" + strconv.Itoa(source))
		list = make([]*EdbDataList, 0)
		return list, err
	}
	var pars []interface{}
	pars = append(pars, endInfoId)
	sql := `SELECT edb_data_id,edb_info_id,data_time,value,data_timestamp FROM %s WHERE edb_info_id = ? `
	if startDate != "" {
		sql += ` AND data_time >= ? `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql += ` AND data_time <= ? `
		pars = append(pars, endDate)
	}
	sql += ` ORDER BY data_time ASC `
	sql = fmt.Sprintf(sql, tableName)
	err = global.MYSQL["data"].Raw(sql, pars...).Scan(&list).Error
	// 格式化日期
	if len(list) > 0 {
		for _, row := range list {
			if row.DataTime != "" {
				row.DataTime = row.DataTime[:10] // 此处获取的字符串row.DataTime长度有长有短，截取年月日
				//tempTime, _ := time.Parse("2006-01-02T00:00:00+08:00", row.DataTime)
				//row.DataTime = tempTime.Format(utils.FormatDate)
			}
		}
	}

	return
}

type EdbInfoMaxAndMinInfo struct {
	MinDate     string  `description:"最小日期"`
	MaxDate     string  `description:"最大日期"`
	MinValue    float64 `description:"最小值"`
	MaxValue    float64 `description:"最大值"`
	LatestValue float64 `description:"最新值"`
}

func GetEdbInfoMaxAndMinInfo(source int, edbCode string) (item *EdbInfoMaxAndMinInfo, err error) {
	sql := ``
	tableName := GetEdbDataTableName(source)
	sql = ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date,MIN(value) AS min_value,MAX(value) AS max_value FROM %s WHERE edb_code=? `
	sql = fmt.Sprintf(sql, tableName)
	err = global.MYSQL["data"].Raw(sql, edbCode).Scan(&item).Error

	var latest_value float64
	sql = ` SELECT value AS latest_value FROM %s WHERE edb_code=? ORDER BY data_time DESC LIMIT 1 `
	sql = fmt.Sprintf(sql, tableName)
	err = global.MYSQL["data"].Raw(sql, edbCode).Scan(&latest_value).Error
	item.LatestValue = latest_value
	return
}