eta_api/models/data_manage/base_from_yongyi.go

package data_manage

import (
	"eta/eta_api/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

type BaseFromYongyiIndex struct {
	YongyiIndexId int `orm:"column(yongyi_index_id);pk"`
	ClassifyId    int
	Interface     string
	IndexCode     string
	IndexName     string
	Frequency     string
	Unit          string
	Sort          int
	CreateTime    time.Time
	ModifyTime    time.Time
}

type BaseFromYongyiIndexList struct {
	YongyiIndexId int `orm:"column(yongyi_index_id);pk"`
	ClassifyId    int
	Interface     string
	IndexCode     string
	IndexName     string
	Frequency     string
	Unit          string
	Sort          int
	CreateTime    string
	ModifyTime    string
	DataList      []*BaseFromYongyiData
	Paging        *paging.PagingItem `description:"分页数据"`
}

func GetYongyiIndex(condition string, pars interface{}) (items []*BaseFromYongyiIndexList, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_yongyi_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC, yongyi_index_id asc`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

func GetYongyiIndexDataCount(indexCode string) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count  FROM base_from_yongyi_data WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&count)
	return
}

func GetYongyiIndexData(indexCode string, startSize, pageSize int) (items []*BaseFromYongyiData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_yongyi_data WHERE index_code=? ORDER BY data_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, indexCode, startSize, pageSize).QueryRows(&items)
	return
}

func GetYongyiDataMaxCount(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM base_from_yongyi_index AS a
				INNER JOIN base_from_yongyi_data AS b ON a.index_code=b.index_code
				WHERE a.classify_id=?
				GROUP BY a.yongyi_index_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetYongyiIndexDataByCodes(indexCode []string) (items []*BaseFromYongyiData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_yongyi_data WHERE index_code in (` + utils.GetOrmInReplace(len(indexCode)) + `) ORDER BY data_time DESC  `
	_, err = o.Raw(sql, indexCode).QueryRows(&items)
	return
}

func GetBaseFromYongyiIndex() (list []*BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_yongyi_index `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

type BaseFromYongyiData struct {
	YongyiDataId  int `orm:"column(yongyi_data_id);pk"`
	YongyiIndexId int
	IndexCode     string
	DataTime      string
	Value         string
	CreateTime    string
	ModifyTime    string
	DataTimestamp int64
}

func AddBaseFromYongyiData(item *BaseFromYongyiData) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	return
}

func GetBaseFromYongyiDataAll() (list []*BaseFromYongyiData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_yongyi_data `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

func ModifyBaseFromYongyiData(YongyiDataId int, value string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_yongyi_data SET value=?,modify_time=NOW() WHERE yongyi_data_id=? `
	_, err = o.Raw(sql, value, YongyiDataId).Exec()
	return
}

func GetBaseFromYongyiIndexByYongyiCode(YongyiCode string) (list []*BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_yongyi_index WHERE interface=? `
	_, err = o.Raw(sql, YongyiCode).QueryRows(&list)
	return
}

func GetBaseFromYongyiMaxOrMinDate(indexCode string) (min_date, max_date string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date FROM base_from_yongyi_data WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&min_date, &max_date)
	return
}

func GetBaseFromYongyiDataAllByIndexCode(indexCode string) (list []*BaseFromYongyiData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_yongyi_data WHERE index_code=? `
	_, err = o.Raw(sql, indexCode).QueryRows(&list)
	return
}

func GetBaseFromYongyiIndexByIndexCode(indexCode string) (list *BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_yongyi_index WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&list)
	return
}

// GetBaseFromYongyiIndexCountByClassifyIds 获取分类下指标的个数
func GetBaseFromYongyiIndexCountByClassifyIds(classifyIds []int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	num := len(classifyIds)
	if num <= 0 {
		return
	}
	sql := `SELECT COUNT(1) AS count FROM base_from_yongyi_index WHERE classify_id IN (` + utils.GetOrmInReplace(num) + `) `
	err = o.Raw(sql, classifyIds).QueryRow(&count)
	return
}

// GetBaseFromYongyiIndexByClassifyId 获取根据分类查询指标
func GetBaseFromYongyiIndexByClassifyId(classifyId int) (list []*BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_yongyi_index WHERE classify_id =? ORDER BY sort ASC, yongyi_index_id ASC `
	_, err = o.Raw(sql, classifyId).QueryRows(&list)
	return
}

// GetYongyiIndexAll 用于分类展示
func GetYongyiIndexAll() (items []*BaseFromYongyiClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT yongyi_index_id, classify_id, index_name as classify_name, index_code as base_from_yongyi_index_code
            FROM base_from_yongyi_index ORDER BY sort ASC, yongyi_index_id ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetBaseFromYongyiIndexByIndexId 根据指标id获取指标信息
func GetBaseFromYongyiIndexByIndexId(indexId int) (item *BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_yongyi_index WHERE yongyi_index_id=? `
	err = o.Raw(sql, indexId).QueryRow(&item)
	return
}

// GetBaseFromYongyiIndexMaxSortByClassifyId 根据指标id获取指标信息
func GetBaseFromYongyiIndexMaxSortByClassifyId(classifyId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT max(sort) FROM base_from_yongyi_index WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&sort)
	return
}

// GetBaseFromYongyiIndexMinSortByClassifyId 获取最小不等于0的排序
func GetBaseFromYongyiIndexMinSortByClassifyId(classifyId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT min(sort) FROM base_from_yongyi_index WHERE classify_id=? and sort <> 0 `
	err = o.Raw(sql, classifyId).QueryRow(&sort)
	return
}

type BaseFromYongyiIndexType struct {
	Type2 string `orm:"column(type_2)"`
	Type3 string `orm:"column(type_3)"`
}

// GetBaseFromYongyiIndexType 获取历史指标信息类型
func GetBaseFromYongyiIndexType() (list []BaseFromYongyiIndexType, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT type_2, type_3 from base_from_yongyi_index where type_2 <> "" GROUP BY type_2, type_3`
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// MoveBaseFromYongyiIndex 移动指标分类
func MoveBaseFromYongyiIndex(chartInfoId, classifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` UPDATE base_from_yongyi_index
			SET
			  classify_id = ?, modify_time=NOW() 
			WHERE yongyi_index_id = ?`
	_, err = o.Raw(sql, classifyId, chartInfoId).Exec()
	return
}

// UpdateBaseFromYongyiIndexByClassifyId 根据指标id更新排序
func UpdateBaseFromYongyiIndexByClassifyId(classifyId, nowSort, prevIndexInfoId int, updateSort string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update base_from_yongyi_index set sort = ` + updateSort + ` WHERE classify_id=? AND  `
	if prevIndexInfoId > 0 {
		sql += ` ( sort > ? or (yongyi_index_id > ` + fmt.Sprint(prevIndexInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `))`
	}
	_, err = o.Raw(sql, classifyId, nowSort).Exec()
	return
}

// MoveUpYongyiIndexBySort 往上移动
func MoveUpYongyiIndexBySort(classifyId, nextSort, currentSort int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `update base_from_yongyi_index set sort = sort + 1 where classify_id=? and sort >= ? and sort< ?`
	_, err = o.Raw(sql, classifyId, nextSort, currentSort).Exec()
	return
}

// MoveDownYongyiIndexBySort 往下移动
func MoveDownYongyiIndexBySort(classifyId, prevSort, currentSort int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `update base_from_yongyi_index set sort = sort - 1 where classify_id=? and sort <= ? and sort> ? `
	_, err = o.Raw(sql, classifyId, prevSort, currentSort).Exec()
	return
}

// GetFirstBaseFromYongyiIndexByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据
func GetFirstBaseFromYongyiIndexByClassifyId(classifyId int) (item *BaseFromYongyiIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_yongyi_index WHERE classify_id=? order by sort asc,yongyi_index_id asc limit 1`
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

// Update 更新Yongyi指标基础信息
func (item *BaseFromYongyiIndex) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(item, cols...)
	return
}

// DeleteBaseFromYongyiIndexById 根据指标id删除对应的指标、指标数据
func DeleteBaseFromYongyiIndexById(YongyiIndexId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	//删除指标
	sql := `DELETE FROM base_from_yongyi_index WHERE yongyi_index_id = ? `
	_, err = to.Raw(sql, YongyiIndexId).Exec()
	if err != nil {
		return
	}

	//删除指标数据
	sql = `DELETE FROM base_from_yongyi_data WHERE yongyi_index_id = ? `
	_, err = to.Raw(sql, YongyiIndexId).Exec()
	if err != nil {
		return
	}

	return
}

// EditYongyiIndexInfoResp 新增指标的返回
type EditYongyiIndexInfoResp struct {
	YongyiIndexId int    `description:"指标ID"`
	IndexCode     string `description:"指标code"`
}