eta_bridge/models/jiayue/dict.go

package jiayue

import (
	"database/sql"
	"eta/eta_bridge/global"
	"fmt"
	"time"
)

var (
	IndexTableName = "DICT_INDEX" // 指标主表
)

func GetDictIndex(condition string, pars []interface{}, orderRule string) (dictIndexList []DictIndex, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("获取桥接服务指标信息失败 Err:" + err.Error())
		}
	}()
	fields := "ID, CODE, NAME, UNIT, FREQUENCY, DESCRIPTION, TABLE_NAME, SOURCE_TYPE, SOURCE_CODE, SOURCE_DESCRIPTION, INDUSTRY, TYPE, COMMODITY, SJB_ID, USER_ID, ROWS_COUNT, DATE_FIRST, DATE_LAST, TIME_LAST_UPDATE, TIME_LAST_REQUEST, PRIORITY, STATUS, SHORT_NAME, UPDATE_DESCRIPTION, FORECAST_FLAG, MANUAL_FLAG, VARIABLE_FLAG, MARKETDATA_FLAG, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME"
	order := ``
	if orderRule != "" {
		order += fmt.Sprintf(`ORDER BY %s`, orderRule)
	}
	sqlStatement := fmt.Sprintf("SELECT %s, 0 no FROM %s WHERE %s %s", fields, IndexTableName, condition, order)
	global.LOG.Info("GetDictIndex SQL: ", sqlStatement)
	dictIndexList, err = getDictIndex(sqlStatement, pars)
	if err != nil {
		err = fmt.Errorf("查询指标信息失败 %s", err)
		return
	}
	//fmt.Printf("查询指标信息成功")
	return
}

// GetDictPageIndex 分页获取指标
func GetDictPageIndex(condition string, pars []interface{}, pageIndex, pageSize, sortField, sortRule int) (dictIndexList []DictIndex, err error) {
	defer func() {
		if err != nil {
			fmt.Printf("查询指标信息失败 %s", err)
			global.LOG.Info("获取桥接服务指标信息失败 Err:" + err.Error())
		}
	}()

	fields := "ID, CODE, NAME, UNIT, FREQUENCY, DESCRIPTION, TABLE_NAME, SOURCE_TYPE, SOURCE_CODE, SOURCE_DESCRIPTION, INDUSTRY, TYPE, COMMODITY, SJB_ID, USER_ID, ROWS_COUNT, DATE_FIRST, DATE_LAST, TIME_LAST_UPDATE, TIME_LAST_REQUEST, PRIORITY, STATUS, SHORT_NAME, UPDATE_DESCRIPTION, FORECAST_FLAG, MANUAL_FLAG, VARIABLE_FLAG, MARKETDATA_FLAG, CREATE_USER, CREATE_TIME, UPDATE_USER, UPDATE_TIME"

	// 排序
	order := `ORDER BY ID DESC`
	sortFMap := map[int]string{1: "DATE_FIRST", 2: "DATE_LAST", 3: "TIME_LAST_UPDATE"}
	sortRMap := map[int]string{1: "ASC", 2: "DESC"}
	if sortFMap[sortField] != "" && sortRMap[sortRule] != "" {
		order = fmt.Sprintf("ORDER BY %s %s", sortFMap[sortField], sortRMap[sortRule])
	}

	//sqlBase := fmt.Sprintf(`SELECT %s, no FROM (SELECT rownum no, a.* FROM %s a WHERE %s %s) WHERE no BETWEEN %d AND %d`, fields, IndexTableName, condition, order, (pageIndex-1)*pageSize+1, pageIndex*pageSize)
	sqlBase := fmt.Sprintf(`SELECT * FROM (SELECT tmp.*, rownum no FROM (SELECT %s FROM %s WHERE %s %s) tmp) WHERE no BETWEEN %d AND %d`, fields, IndexTableName, condition, order, (pageIndex-1)*pageSize+1, pageIndex*pageSize)
	global.LOG.Info("GetDictPageIndex SQL: ", sqlBase)
	dictIndexList, err = getDictIndex(sqlBase, pars)
	if err != nil {
		err = fmt.Errorf("查询指标信息失败 %s", err)
		return
	}
	return
}

func GetDictData(tableName string, condition string, pars []interface{}) (dictIndexData []DictData, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("获取桥接服务指标数据失败 Err:" + err.Error())
		}
	}()
	sqlStatement := fmt.Sprintf("SELECT ID, INDEX_ID, INDEX_VALUE, INDEX_DATE, UPDATE_TIME FROM %s WHERE %s", tableName, condition)

	dictIndexData, err = getDictData(sqlStatement, pars)
	if err != nil {
		fmt.Printf("查询指标数据失败 %s", err)
		err = fmt.Errorf("查询指标数据失败 %s", err)
		return
	}
	fmt.Printf("查询指标信息成功")
	return
}

type DictIndex struct {
	Id                int       `description:"主键"`
	Code              string    `description:"指标编码"`
	Name              string    `description:"指标名称"`
	Unit              string    `description:"单位"`
	Frequency         string    `description:"更新频率"`
	Description       string    `description:"描述"`
	TableName         string    `description:"指标数据存储库表"`
	SourceType        string    `description:"指标来源，如：wind：万德;manual：手工导入;formula：公式;webisite：网页爬取"`
	SourceCode        string    `description:"来源编码"`
	SourceDescription string    `description:"来源说明"`
	Industry          string    `description:"品种板块"`
	Type              string    `description:"指标类型"`
	Commodity         string    `description:"商品名称"`
	SjbId             int       `description:"SJB_ID"`
	UserId            int       `description:"所属用户"`
	RowsCount         int       `description:"指标数据数量"`
	DateFirst         time.Time `description:"指标开始时间"`
	DateLast          time.Time `description:"指标最新时间"`
	TimeLastUpdate    time.Time `description:"最新更新时间"`
	TimeLastRequest   time.Time `description:"下次更新时间"`
	Priority          int       `description:"更新优先级"`
	Status            int       `description:"指标状态"`
	ShortName         string    `description:"指标简称"`
	UpdateDescription string    `description:"更新说明"`
	ForecastFlag      int       `description:"预测标识"`
	ManualFlag        int       `description:"手动标识"`
	VariableFlag      int       `description:"有效标识"`
	MarketDataFlag    int       `description:"市场价标识"`
	CreateUser        int       `description:"创建用户"`
	CreateTime        time.Time `description:"创建时间"`
	UpdateUser        int       `description:"更新用户"`
	UpdateTime        time.Time `description:"更新时间"`
}

type DictIndexSql struct {
	Id                int            `description:"主键" json:"ID"`
	Code              sql.NullString `description:"指标编码" json:"CODE"`
	Name              sql.NullString `description:"指标名称" json:"NAME"`
	Unit              sql.NullString `description:"单位" json:"UNIT"`
	Frequency         sql.NullString `description:"更新频率" json:"FREQUENCY"`
	Description       sql.NullString `description:"描述" json:"DESCRIPTION"`
	TableName         sql.NullString `description:"指标数据存储库表" json:"TABLE_NAME"`
	SourceType        sql.NullString `description:"指标来源，如：wind：万德;manual：手工导入;formula：公式;webisite：网页爬取" json:"SOURCE_TYPE"`
	SourceCode        sql.NullString `description:"来源编码" json:"SOURCE_CODE"`
	SourceDescription sql.NullString `description:"来源说明" json:"SOURCE_DESCRIPTION"`
	Industry          sql.NullString `description:"品种板块" json:"INDUSTRY"`
	Type              sql.NullString `description:"指标类型" json:"TYPE"`
	Commodity         sql.NullString `description:"商品名称" json:"COMMODITY"`
	SjbId             sql.NullInt32  `json:"SJB_ID"`
	UserId            sql.NullInt32  `description:"所属用户" json:"USER_ID"`
	RowsCount         sql.NullInt32  `description:"指标数据数量" json:"ROWS_COUNT"`
	DateFirst         sql.NullTime   `description:"指标开始时间" json:"DATE_FIRST"`
	DateLast          sql.NullTime   `description:"指标最新时间" json:"DATE_LAST"`
	TimeLastUpdate    sql.NullTime   `description:"最新更新时间" json:"TIME_LAST_UPDATE"`
	TimeLastRequest   sql.NullTime   `description:"下次更新时间" json:"TIME_LAST_REQUEST"`
	Priority          sql.NullInt32  `description:"更新优先级" json:"PRIORITY"`
	Status            sql.NullInt32  `description:"指标状态" json:"STATUS"`
	ShortName         sql.NullString `description:"指标简称" json:"SHORT_NAME"`
	UpdateDescription sql.NullString `description:"更新说明" json:"UPDATE_DESCRIPTION"`
	ForecastFlag      sql.NullInt32  `description:"预测标识" json:"FORECAST_FLAG"`
	ManualFlag        sql.NullInt32  `description:"手动标识" json:"MANUAL_FLAG"`
	VariableFlag      sql.NullInt32  `description:"有效标识" json:"VARIABLE_FLAG"`
	MarketDataFlag    sql.NullInt32  `description:"市场价标识" json:"MARKETDATA_FLAG"`
	CreateUser        sql.NullInt32  `description:"创建用户" json:"CREATE_USER"`
	CreateTime        sql.NullTime   `description:"创建时间" json:"CREATE_TIME"`
	UpdateUser        sql.NullInt32  `description:"更新用户" json:"UPDATE_USER"`
	UpdateTime        sql.NullTime   `description:"更新时间" json:"UPDATE_TIME"`
	No                sql.NullInt32  `description:"分页查询数" json:"no"`
}

type DictDataSql struct {
	Id         int             `description:"主键" json:"ID"`
	IndexId    int             `json:"INDEX_ID"`
	IndexValue sql.NullFloat64 `json:"INDEX_VALUE"`
	IndexDate  sql.NullTime    `json:"INDEX_DATE"`
	UpdateTime sql.NullTime    `json:"UPDATE_TIME"`
}

type DictData struct {
	Id         int `description:"主键" json:"ID"`
	IndexId    int
	IndexValue float64
	IndexDate  time.Time
	UpdateTime time.Time
}

func getDictIndex(sqlStatement string, pars []interface{}) (dictIndexs []DictIndex, err error) {
	//查询ZHUJI表中code='pt'的sql:SELECT * FROM ZHUJI where CODE = 'pt'
	//sqlStatement := "SELECT * FROM DICT_CATEGORY where CODE = :1"
	//sqlStatement := "SELECT * FROM DICT_INDEX WHERE SOURCE_TYPE = :1 AND ROWNUM < 11"
	stmt, err := global.OracleJy.Prepare(sqlStatement)
	if err != nil {
		err = fmt.Errorf("sql 预处理 失败，Err: %s", err)
		return
	}
	rows, err := stmt.Query(pars...) //输入sql中对应参数的值
	if err != nil {
		err = fmt.Errorf("sql 查询失败，Err: %s", err)
		return
	}

	defer rows.Close() //defer关闭查询连接
	//获取列相关信息
	//获取列相关信息
	strings, _ := rows.Columns()
	global.LOG.Info("获取列表相关信息")
	for i := 0; i < len(strings); i++ {
		fmt.Print(" ", strings[i])
		//global.LOG.Info("显示字段：", strings[i])
	}
	fmt.Print("\n")
	global.LOG.Info("\n")
	for rows.Next() {
		var tmp DictIndexSql
		err = rows.Scan(&tmp.Id, &tmp.Code, &tmp.Name, &tmp.Unit, &tmp.Frequency, &tmp.Description, &tmp.TableName, &tmp.SourceType, &tmp.SourceCode, &tmp.SourceDescription, &tmp.Industry, &tmp.Type, &tmp.Commodity, &tmp.SjbId, &tmp.UserId, &tmp.RowsCount, &tmp.DateFirst, &tmp.DateLast, &tmp.TimeLastUpdate, &tmp.TimeLastRequest, &tmp.Priority, &tmp.Status, &tmp.ShortName, &tmp.UpdateDescription, &tmp.ForecastFlag, &tmp.ManualFlag, &tmp.VariableFlag, &tmp.MarketDataFlag, &tmp.CreateUser, &tmp.CreateTime, &tmp.UpdateUser, &tmp.UpdateTime, &tmp.No)
		if err != nil {
			fmt.Printf("扫描错误:", err)
			err = fmt.Errorf("扫描错误:" + err.Error())
			return
		}
		dictTmp := DictIndex{
			Id:                tmp.Id,
			Code:              tmp.Code.String,
			Name:              tmp.Name.String,
			Unit:              tmp.Unit.String,
			Frequency:         tmp.Frequency.String,
			Description:       tmp.Description.String,
			TableName:         tmp.TableName.String,
			SourceType:        tmp.SourceType.String,
			SourceCode:        tmp.SourceCode.String,
			SourceDescription: tmp.SourceDescription.String,
			Industry:          tmp.Industry.String,
			Type:              tmp.Type.String,
			Commodity:         tmp.Commodity.String,
			SjbId:             int(tmp.SjbId.Int32),
			UserId:            int(tmp.UserId.Int32),
			RowsCount:         int(tmp.RowsCount.Int32),
			DateFirst:         tmp.DateFirst.Time,
			DateLast:          tmp.DateLast.Time,
			TimeLastUpdate:    tmp.TimeLastUpdate.Time,
			TimeLastRequest:   tmp.TimeLastRequest.Time,
			Priority:          int(tmp.Priority.Int32),
			Status:            int(tmp.Status.Int32),
			ShortName:         tmp.ShortName.String,
			UpdateDescription: tmp.UpdateDescription.String,
			ForecastFlag:      int(tmp.ForecastFlag.Int32),
			ManualFlag:        int(tmp.ManualFlag.Int32),
			VariableFlag:      int(tmp.VariableFlag.Int32),
			MarketDataFlag:    int(tmp.MarketDataFlag.Int32),
			CreateUser:        int(tmp.CreateUser.Int32),
			CreateTime:        tmp.CreateTime.Time,
			UpdateUser:        int(tmp.UpdateUser.Int32),
			UpdateTime:        tmp.UpdateTime.Time,
		}
		dictIndexs = append(dictIndexs, dictTmp)
	}
	if err = rows.Err(); err != nil {
		// handle the error here
		err = fmt.Errorf("解析行数据失败" + err.Error())
		return
	}
	defer stmt.Close()

	/*data, err := json.Marshal(dictIndexs)
	if err != nil {
		fmt.Printf("序列化错误 err = %v\n", err)
		global.LOG.Info("序列化错误 err = %v\n", err)
		return
	}
	//输出序列化后的结果 json字符串
	fmt.Printf("序列化后 = %v\n", string(data))
	global.LOG.Info("序列化后 = %v\n", string(data))*/
	return
}

func getDictData(sqlStatement string, pars []interface{}) (dictData []DictData, err error) {
	//查询ZHUJI表中code='pt'的sql:SELECT * FROM ZHUJI where CODE = 'pt'
	//sqlStatement := "SELECT * FROM DICT_CATEGORY where CODE = :1"
	//sqlStatement := "SELECT * FROM DICT_INDEX WHERE SOURCE_TYPE = :1 AND ROWNUM < 11"
	stmt, err := global.OracleJy.Prepare(sqlStatement)
	if err != nil {
		err = fmt.Errorf("sql 预处理 失败，Err: %s", err)
		return
	}
	rows, err := stmt.Query(pars...) //输入sql中对应参数的值
	if err != nil {
		err = fmt.Errorf("sql 查询失败，Err: %s", err)
		return
	}

	defer rows.Close() //defer关闭查询连接

	//获取列相关信息
	strings, _ := rows.Columns()
	for i := 0; i < len(strings); i++ {
		fmt.Print(" ", strings[i])
	}
	fmt.Print("\n")
	for rows.Next() {
		var tmp DictDataSql
		err = rows.Scan(&tmp.Id, &tmp.IndexId, &tmp.IndexValue, &tmp.IndexDate, &tmp.UpdateTime)
		if err != nil {
			fmt.Printf("扫描错误:", err)
			err = fmt.Errorf("扫描错误" + err.Error())
			return
		}
		dictTmp := DictData{
			Id:         tmp.Id,
			IndexId:    tmp.IndexId,
			IndexValue: tmp.IndexValue.Float64,
			IndexDate:  tmp.IndexDate.Time,
			UpdateTime: tmp.UpdateTime.Time,
		}
		dictData = append(dictData, dictTmp)
	}
	if err = rows.Err(); err != nil {
		// handle the error here
		err = fmt.Errorf("解析行数据失败" + err.Error())
		return
	}
	defer stmt.Close()

	/*data, err := json.Marshal(dictData)
	if err != nil {
		fmt.Printf("序列化错误 err = %v\n", err)
	}
	//输出序列化后的结果 json字符串
	fmt.Printf("序列化后 = %v\n", string(data))*/
	return
}

// GetDictFrequency 获取指标频度
func GetDictFrequency() (frequencies []string, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("嘉悦-获取指标频度失败, Err:" + err.Error())
		}
	}()

	sqlBase := fmt.Sprintf(`SELECT DISTINCT FREQUENCY FROM %s`, IndexTableName)
	stmt, e := global.OracleJy.Prepare(sqlBase)
	if e != nil {
		err = fmt.Errorf("预处理sql失败, err: %s", e.Error())
		return
	}
	rows, e := stmt.Query()
	if e != nil {
		err = fmt.Errorf("查询sql失败, err: %s", e.Error())
		return
	}
	defer func() {
		_ = rows.Close() // 关闭查询连接
	}()

	for rows.Next() {
		var frequency string
		if e = rows.Scan(&frequency); e != nil {
			err = fmt.Errorf("扫描错误, err: %s", e.Error())
			return
		}
		frequencies = append(frequencies, frequency)
	}
	if e = rows.Err(); e != nil {
		err = fmt.Errorf("解析行数据失败, err: %s", e.Error())
		return
	}
	defer func() {
		_ = stmt.Close()
	}()
	return
}

// GetDictIndexCount 获取指标总记录数
func GetDictIndexCount(condition string, pars []interface{}) (total int, err error) {
	defer func() {
		if err != nil {
			global.LOG.Info("嘉悦-获取指标总记录数失败, Err:" + err.Error())
		}
	}()

	sqlBase := fmt.Sprintf(`SELECT COUNT(1) FROM %s WHERE %s`, IndexTableName, condition)
	stmt, e := global.OracleJy.Prepare(sqlBase)
	if e != nil {
		err = fmt.Errorf("预处理sql失败, err: %s", e.Error())
		return
	}
	rows, e := stmt.Query(pars...)
	if e != nil {
		err = fmt.Errorf("查询sql失败, err: %s", e.Error())
		return
	}
	defer func() {
		_ = rows.Close()
	}()

	for rows.Next() {
		if e = rows.Scan(&total); e != nil {
			err = fmt.Errorf("扫描错误, err: %s", e.Error())
			return
		}
	}
	if e = rows.Err(); e != nil {
		err = fmt.Errorf("解析行数据失败, err: %s", e.Error())
		return
	}
	defer func() {
		_ = stmt.Close()
	}()
	return
}