hongze_yb/models/tables/edb_data/query.go

package edb_data

import (
	"errors"
	"fmt"
	"go.mongodb.org/mongo-driver/bson"
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/models/mgo"
	"hongze/hongze_yb/models/tables/edb_source"
	"hongze/hongze_yb/utils"
	"strconv"
	"time"
)

// GetEdbDataTableName 指标数据->存储表
func GetEdbDataTableName(source, subSource int) (tableName string) {
	switch source {
	case utils.DATA_SOURCE_THS:
		tableName = "edb_data_ths"
	case utils.DATA_SOURCE_WIND:
		if subSource == utils.DATA_SUB_SOURCE_DATE {
			tableName = "edb_data_wind_wsd"
		} else {
			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"
	case utils.DATA_SOURCE_CALCULATE_CJJX:
		tableName = "edb_data_calculate_cjjx"
	case utils.DATA_SOURCE_EIA_STEO:
		tableName = "edb_data_eia_steo"
	case utils.DATA_SOURCE_CALCULATE_NHCC:
		tableName = "edb_data_calculate_nhcc"
	case utils.DATA_SOURCE_COM_TRADE:
		tableName = "edb_data_com_trade"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_NSZYDPJJS:
		tableName = "edb_data_predict_calculate_nszydpjjs"
	case utils.DATA_SOURCE_CALCULATE_ADJUST:
		tableName = "edb_data_calculate_adjust"
	case utils.DATA_SOURCE_SCI:
		tableName = "edb_data_sci"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_LJZZY:
		tableName = "edb_data_predict_calculate_ljzzy"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_TIME_SHIFT:
		tableName = "edb_data_predict_calculate_time_shift"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_ZJPJ:
		tableName = "edb_data_predict_calculate_zjpj"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_LJZTBPJ:
		tableName = "edb_data_predict_calculate_ljztbpj"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_NHCC:
		tableName = "edb_data_predict_calculate_nhcc"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_CJJX:
		tableName = "edb_data_predict_calculate_cjjx"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_HBZ:
		tableName = "edb_data_predict_calculate_hbz"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_HCZ:
		tableName = "edb_data_predict_calculate_hcz"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_BP:
		tableName = "edb_data_predict_calculate_bp"
	case utils.DATA_SOURCE_CALCULATE_JP:
		tableName = "edb_data_calculate_jp"
	case utils.DATA_SOURCE_CALCULATE_NH:
		tableName = "edb_data_calculate_nh"
	case utils.DATA_SOURCE_CALCULATE_KSZS:
		tableName = "edb_data_calculate_kszs"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_JP:
		tableName = "edb_data_predict_calculate_jp"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_NH:
		tableName = "edb_data_predict_calculate_nh"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_KSZS:
		tableName = "edb_data_predict_calculate_kszs"
	case utils.DATA_SOURCE_BAIINFO:
		tableName = "edb_data_baiinfo"
	case utils.DATA_SOURCE_STOCK_PLANT:
		tableName = "edb_data_stock_plant"
	case utils.DATA_SOURCE_CALCULATE_CORRELATION:
		tableName = "edb_data_calculate_correlation"
	case utils.DATA_SOURCE_NATIONAL_STATISTICS:
		tableName = "edb_data_national_statistics"
	case utils.DATA_SOURCE_CALCULATE_LJZZJ: //累计值转季 -> 61
		tableName = "edb_data_calculate_ljzzj"
	case utils.DATA_SOURCE_CALCULATE_LJZ: //累计值 -> 62
		tableName = "edb_data_calculate_ljz"
	case utils.DATA_SOURCE_CALCULATE_LJZNCZJ: //累计值(年初至今） -> 63
		tableName = "edb_data_calculate_ljznczj"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_LJZZJ: // 预测指标 - 累计值 -> 65
		tableName = "edb_data_predict_calculate_ljzzj"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_LJZ: //预测指标 - 累计值转季->64
		tableName = "edb_data_predict_calculate_ljz"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_LJZNCZJ: //预测指标 - 累计值(年初至今） -> 66
		tableName = "edb_data_predict_calculate_ljznczj"
	case utils.DATA_SOURCE_CALCULATE_STANDARD_DEVIATION: //标准差->67
		tableName = "edb_data_calculate_standard_deviation"
	case utils.DATA_SOURCE_CALCULATE_PERCENTILE: //百分位->68
		tableName = "edb_data_calculate_percentile"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_STANDARD_DEVIATION: //预测标准差->69
		tableName = "edb_data_predict_ccalculate_standard_deviation"
	case utils.DATA_SOURCE_PREDICT_CALCULATE_PERCENTILE: //预测百分位->70
		tableName = "edb_data_predict_ccalculate_percentile"
	case utils.DATA_SOURCE_FUBAO: //富宝 -> 71
		tableName = "edb_data_fubao"
	case utils.DATA_SOURCE_CALCULATE_ZSXY:
		tableName = "edb_data_calculate_zsxy" // 指数修匀->72
	case utils.DATA_SOURCE_PREDICT_CALCULATE_ZSXY:
		tableName = "edb_data_predict_calculate_zsxy" // 预测指数修匀->73
	case utils.DATA_SOURCE_CALCULATE_ZDYFX:
		tableName = "edb_data_calculate_zdyfx" // 自定义分析->74
	case utils.DATA_SOURCE_CALCULATE_RJZ: //日均值->75
		tableName = "edb_data_calculate_rjz"
	default:
		edbSource, _ := edb_source.GetEdbSourceBySource(source)
		if edbSource != nil {
			tableName = edbSource.TbName
		}
	}
	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                 string
	DataList             []*EdbDataList
	CuttingDataTimestamp int64 `description:"切割的时间戳"`
	ChartLegend          string
	Years                string
}

type QuarterXDateItem struct {
	StartDate            time.Time
	EndDate              time.Time
	ShowName             string
	ChartLegend          string
	CuttingDataTimestamp int64 `description:"切割的时间戳"`
}

type SeasonExtraItem struct {
	ChartLegend []SeasonChartLegend `description:"自定义的图例名称"`
	XStartDate  string              `description:"横坐标显示的起始日"`
	XEndDate    string              `description:"横坐标显示的截止日"`
	JumpYear    int                 `description:"横坐标日期是否跨年，1跨年，0不跨年"`
}

type SeasonChartLegend struct {
	Name  string
	Value string
}

type QuarterDataList []*QuarterData

func (m QuarterDataList) Len() int {
	return len(m)
}

func (m QuarterDataList) Less(i, j int) bool {
	return m[i].Years < m[j].Years
}

func (m QuarterDataList) Swap(i, j int) {
	m[i], m[j] = m[j], m[i]
}

// GetEdbDataList 获取指标数据
func GetEdbDataList(source, subSource, edbInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
	// 自有数据需要额外处理（从mongo获取）
	if source == utils.DATA_SOURCE_BUSINESS {
		return getEdbDataListByMongo(source, subSource, edbInfoId, startDate, endDate)
	}

	return getEdbDataListByMysql(source, subSource, edbInfoId, startDate, endDate)
}

// getEdbDataListByMysql
// @Description: 通过mysql查询指标数据
// @author: Roc
// @datetime 2024-05-08 17:52:21
// @param source int
// @param subSource int
// @param edbInfoId int
// @param startDate string
// @param endDate string
// @return list []*EdbDataList
// @return err error
func getEdbDataListByMysql(source, subSource, edbInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
	tableName := GetEdbDataTableName(source, subSource)
	if tableName == "" {
		err = errors.New("无效的渠道：" + strconv.Itoa(source))
		list = make([]*EdbDataList, 0)
		return list, err
	}
	var pars []interface{}
	pars = append(pars, edbInfoId)
	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
}

// getEdbDataListByMongo
// @Description: 通过mongo查询指标数据
// @author: Roc
// @datetime 2024-05-07 10:40:43
// @param source int
// @param subSource int
// @param endInfoId int
// @param startDate string
// @param endDate string
// @return list []*EdbDataList
// @return err error
func getEdbDataListByMongo(source, subSource, edbInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
	list = make([]*EdbDataList, 0)

	mogDataObj := mgo.EdbDataBusiness{}
	// 构建查询条件
	queryConditions := bson.M{
		"edb_info_id": edbInfoId,
	}
	// 日期
	dateCondition, err := mgo.BuildDateCondition(startDate, endDate)
	if err != nil {
		return
	}
	if len(dateCondition) > 0 {
		queryConditions["data_time"] = dateCondition
	}

	// 获取列表数据
	tmpDataList, tmpErr := mogDataObj.GetAllDataList(queryConditions, []string{"data_time"})
	if tmpErr != nil {
		err = tmpErr
		return
	}
	for k, v := range tmpDataList {
		list = append(list, &EdbDataList{
			EdbDataId:     k + 1,
			EdbInfoId:     v.EdbInfoId,
			DataTime:      v.DataTime.Format(utils.FormatDate),
			DataTimestamp: v.DataTimestamp,
			Value:         v.Value,
		})
	}

	return
}