hz_crm_api/models/data_manage/base_from_sci.go

package data_manage

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"time"
)

type BaseFromSci struct {
	BaseFromSciId int `orm:"column(base_from_sci_id);pk"`
	Aid           int
	Name          string
	Interface     string
	ApiUpdate     int
	ApiUpdateType string
	ApiType       int
	ApiTypeAll    string
	Type1         string `orm:"column(type_1)"`
	Type2         string `orm:"column(type_2)"`
	Type3         string `orm:"column(type_3)"`
	ApiStartTime  string
	ApiUpdateTime string
	StartTime     string
	FinishTime    string
	AuthModule    string
	AuthLang      string
	ApiLabel      string
	Enable        string
	EditPerson    string
	EditTime      string
	AuthDur       int
	AuthDurType   string
}

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

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

func GetBaseFromSciBySciCode(sciCode string) (item *BaseFromSci, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_sci WHERE interface=?`
	err = o.Raw(sql, sciCode).QueryRow(&item)
	return
}

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

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

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

type BaseFromSciData struct {
	SciDataId          int `orm:"column(sci_data_id);pk"`
	BaseFromSciIndexId int
	IndexCode          string
	DataTime           string
	Value              string
	CreateTime         time.Time
	ModifyTime         time.Time
	DataTimestamp      int64
}

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

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

func ModifyBaseFromSciData(sciDataId int, value string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_sci_data SET value=?,modify_time=NOW() WHERE sci_data_id=? `
	_, err = o.Raw(sql, value, sciDataId).Exec()
	return
}

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

func GetBaseFromSciMaxOrMinDate(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_sci_data WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&min_date, &max_date)
	return
}

func ModifyBaseFromSciMinDateAndMaxDate(baseFromSciIndexId int, minDate, maxDate string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` UPDATE base_from_sci_index SET start_date=?,end_date=?,modify_time=NOW() WHERE base_from_sci_index_id=? `
	_, err = o.Raw(sql, minDate, maxDate, baseFromSciIndexId).Exec()
	return
}

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

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

// GetBaseFromSciIndexCountByClassifyIds 获取分类下指标的个数
func GetBaseFromSciIndexCountByClassifyIds(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_sci_index WHERE classify_id IN (` + utils.GetOrmInReplace(num) + `) `
	err = o.Raw(sql, classifyIds).QueryRow(&count)
	return
}

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

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

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

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

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

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

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

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

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

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

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

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

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

// DeleteBaseFromSciIndexById 根据指标id删除对应的指标、指标数据
func DeleteBaseFromSciIndexById(sciIndexId 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_sci_index WHERE base_from_sci_index_id = ? `
	_, err = to.Raw(sql, sciIndexId).Exec()
	if err != nil {
		return
	}

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

	return
}

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

type SciSingleDataResp struct {
	BaseFromSciIndexId int
	ClassifyId         int
	Interface          string
	Name               string
	IndexCode          string
	IndexName          string
	Frequency          string
	Unit               string
	ApiStartTime       string
	ApiUpdateTime      string
	StartTime          string
	FinishTime         string
	CreateTime         string
	ModifyTime         string
	Data               []*SciIndexData
}