eta_api/models/data_manage/base_from_sci_hq_index.go

package data_manage

import (
	"eta/eta_api/utils"
	"time"

	"github.com/beego/beego/v2/client/orm"
)

type BaseFromSciHqIndex struct {
	BaseFromSciHqIndexId int    `orm:"pk"`
	ClassifyId           int    `description:"指标分类id"`
	IndexCode            string `description:"指标编码"`
	IndexName            string `description:"指标名称"`
	Unit                 string `description:"单位"`
	Frequency            string `description:"频度"`
	StartDate            string `description:"开始日期"`
	EndDate              string
	Sort                 int
	TerminalCode         string
	FilePath             string `json:"-"`
	CreateTime           time.Time
	ModifyTime           time.Time
}

type BaseFromSciHqIndexView struct {
	BaseFromSciHqIndexId int    `orm:"pk"`
	EdbInfoId            int    `description:"指标库id"`
	ClassifyId           int    `description:"指标分类id"`
	IndexCode            string `description:"指标编码"`
	IndexName            string `description:"指标名称"`
	UniqueCode           string `description:"唯一code"`
	Frequency            string `description:"频度"`
	Unit                 string `description:"单位"`
	StartDate            string `description:"开始日期"`
	EndDate              string `description:"结束日期"`
	Sort                 int    `description:"排序"`
	EdbExist             int    `description:"edb是否存在"`
	ModifyTime           string
}

func (b *BaseFromSciHqIndex) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(b, cols...)
	return
}

// GetSciHqIndexByCondition 根据条件获取卓创红期指标列表
func GetSciHqIndexByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC, base_from_sci_hq_index_id ASC`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// GetSciHqIndexByCondition 根据条件获取卓创红期指标列表
func GetSciHqIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` AND frequency=?`
	sql += ` ORDER BY sort ASC, base_from_sci_hq_index_id ASC`
	_, err = o.Raw(sql, pars, frequency).QueryRows(&items)
	return
}

func GetSciHqIndexCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(*) AS count FROM base_from_sci_hq_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC, base_from_sci_hq_index_id ASC`
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetSciHqIndexAndEdbInfoByCondition 根据条件获取卓创红期index和指标库的信息
func GetSciHqIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id FROM base_from_sci_hq_index AS b LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=? WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC `
	_, err = o.Raw(sql, utils.DATA_SOURCE_SCI_HQ, pars).QueryRows(&items)
	return
}

// GetSciHqIndexByIndexCode 根据指标编码获取指标信息
func GetSciHqIndexByIndexCode(indexCode string) (item *BaseFromSciHqIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&item)
	return
}

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

// GetSciHqIndexListByIndexIds 根据指标id获取指标信息
func GetSciHqIndexListByIndexIds(indexIds []int) (items []*BaseFromSciHqIndex, err error) {
	if len(indexIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE base_from_sci_hq_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	_, err = o.Raw(sql, indexIds).QueryRows(&items)
	return
}

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

// GetSciHqIndexByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexByClassifyId(classifyIds []int, startSize, pageSize int) (items []*BaseFromSciHqIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id,
	CASE WHEN e.edb_info_id IS NULL THEN 0 ELSE 1 END AS edb_exist
	FROM base_from_sci_hq_index AS b
	LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=88
	WHERE b.classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `) ORDER BY b.sort ASC LIMIT ?,? `
	_, err = o.Raw(sql, classifyIds, startSize, pageSize).QueryRows(&items)
	return
}

// GetSciHqIndexCountByClassifyId 根据分类id获取卓创红期指标数量
func GetSciHqIndexCountByClassifyId(classifyIds []int) (count int, err error) {
	if len(classifyIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(*) AS count FROM base_from_sci_hq_index WHERE classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `) `
	err = o.Raw(sql, classifyIds).QueryRow(&count)
	return
}

// GetSciHqIndexCount 获取卓创红期指标数量
func GetSciHqIndexCount() (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(*) AS count FROM base_from_sci_hq_index `
	err = o.Raw(sql).QueryRow(&count)
	return
}

func GetSciHqIndexByPage(startSize, pageSize int) (items []*BaseFromSciHqIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id,
	CASE WHEN e.edb_info_id IS NULL THEN 0 ELSE 1 END AS edb_exist
	FROM base_from_sci_hq_index AS b
	LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=88
	ORDER BY b.modify_time DESC LIMIT ?,?`
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&items)
	return
}

// GetSciHqIndexBaseInfoByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexBaseInfoByClassifyId(classifyId int) (items []*BaseFromSciHqIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT base_from_sci_hq_index_id, classify_id, index_code, index_name, CONCAT(classify_id, '_', base_from_sci_hq_index_id) AS unique_code  FROM base_from_sci_hq_index WHERE classify_id = ? ORDER BY sort ASC `
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

// GetSciHqIndexBaseInfoByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexBaseInfoByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT base_from_sci_hq_index_id, index_code, index_name  FROM base_from_sci_hq_index WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC `
	_, err = o.Raw(sql, pars...).QueryRows(&items)
	return
}

func GetSciHqDataMaxCount(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_sci_hq_index AS a
				INNER JOIN base_from_sci_hq_data AS b ON a.index_code=b.index_code
				WHERE a.classify_id=?
				GROUP BY a.base_from_sci_hq_index_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

// GetSciHqIndexMaxSortByClassifyId 根据分类id获取指标最大排序
func GetSciHqIndexMaxSortByClassifyId(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
}

func GetSciHqFrequency(classifyId int) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_sci_hq_index WHERE classify_id=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

func GetSciHqFrequencyByCondition(condition string, pars []interface{}) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_sci_hq_index WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, pars...).QueryRows(&items)
	return
}

func GetSciHqFrequencyByCode(code string) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_sci_hq_index WHERE index_code=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, code).QueryRows(&items)
	return
}

// GetSciHqClassifyMaxSortByClassifyIds 通过分类id获取对应分类的最大sort
func GetSciHqClassifyMaxSortByClassifyIds(classifyIds []int) (items []*BaseFromSciHqClassifyMaxSort, err error) {
	if len(classifyIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT bc.classify_id, COALESCE(MAX(bi.sort), 0) AS max_sort FROM base_from_sci_hq_classify  AS bc
	LEFT JOIN base_from_sci_hq_index AS bi
	ON bc.classify_id=bi.classify_id  
	WHERE bc.classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `)
	GROUP BY bc.classify_id
	`
	// sql = ` SELECT classify_id, MAX(sort) AS max_sort FROM base_from_sci_hq_index WHERE classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `) GROUP BY classify_id `
	_, err = o.Raw(sql, classifyIds).QueryRows(&items)
	return
}

func BatchModifySciHqIndexClassify(items []*BaseFromSciHqIndex) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_sci_hq_index SET classify_id=?, sort=? WHERE base_from_sci_hq_index_id=? `
	p, err := o.Raw(sql).Prepare()
	if err != nil {
		return
	}
	defer func() {
		p.Close()
	}()
	for _, v := range items {
		_, err = p.Exec(v.ClassifyId, v.Sort, v.BaseFromSciHqIndexId)
		if err != nil {
			return
		}
	}
	return
}

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

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

func DeleteSciHqIndexById(indexId 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_hq_index WHERE base_from_sci_hq_index_id=? `
	_, err = to.Raw(sql, indexId).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_sci_hq_data WHERE base_from_sci_hq_index_id=? `
	_, err = to.Raw(sql, indexId).Exec()
	if err != nil {
		return
	}
	return
}

func DeleteSciHqIndexByIds(indexIds []int) (err error) {
	if len(indexIds) == 0 {
		return
	}
	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_hq_index WHERE base_from_sci_hq_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	_, err = o.Raw(sql, indexIds).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_sci_hq_data WHERE base_from_sci_hq_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	_, err = o.Raw(sql, indexIds).Exec()
	if err != nil {
		return
	}
	return
}

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

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