eta_api/models/data_manage/base_from_clarksons_index.go

package data_manage

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

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

type BaseFromClarksonsIndex struct {
	BaseFromClarksonsIndexId int       `orm:"pk"`
	ClassifyId           int       `description:"指标分类id"`
	IndexCode            string    `description:"指标编码"`
	IndexName            string    `description:"指标名称"`
	Unit                 string    `description:"单位"`
	Frequency            string    `description:"频度"`
	StartDate            string    `description:"开始日期"`
	EndDate              string    `description:"结束日期"`
	Sort                 int       `description:"排序"`
	CreateTime           time.Time
	ModifyTime           time.Time
}

type BaseFromClarksonsIndexView struct {
	BaseFromClarksonsIndexId 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:"排序"`
	LatestDate           string  `description:"最后更新时间"`
	EdbExist             int     `description:"edb是否存在"`
	ModifyTime           string
}

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

// GetClarksonsIndexByCondition 根据条件获取克拉克森指标列表
func GetClarksonsIndexByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_clarksons_index WHERE 1=1 `

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

// GetClarksonsIndexByCondition 根据条件获取克拉克森指标列表
func GetClarksonsIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_clarksons_index WHERE 1=1 `

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

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

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

// GetClarksonsIndexAndEdbInfoByCondition 根据条件获取克拉克森index和指标库的信息
func GetClarksonsIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id FROM base_from_clarksons_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
}

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

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

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

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

// GetClarksonsIndexByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexByClassifyId(classifyIds []int, startSize, pageSize int) (items []*BaseFromClarksonsIndexView, 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_clarksons_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
}

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

// GetClarksonsIndexCount 获取克拉克森指标数量
func GetClarksonsIndexCount() (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(*) AS count FROM base_from_clarksons_index `
	err = o.Raw(sql).QueryRow(&count)
	return
}

func GetClarksonsIndexByPage(startSize, pageSize int) (items []*BaseFromClarksonsIndexView, 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_clarksons_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
}

// GetClarksonsIndexBaseInfoByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexBaseInfoByClassifyId(classifyId int) (items []*BaseFromClarksonsIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT base_from_clarksons_index_id, classify_id, index_code, index_name, CONCAT(classify_id, '_', base_from_clarksons_index_id) AS unique_code  FROM base_from_clarksons_index WHERE classify_id = ? ORDER BY sort ASC `
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

// GetClarksonsIndexBaseInfoByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexBaseInfoByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT base_from_clarksons_index_id, index_code, index_name  FROM base_from_clarksons_index WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC `
	_, err = o.Raw(sql, pars...).QueryRows(&items)
	return
}

func GetClarksonsDataMaxCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT MAX(t.num) AS count FROM ( SELECT COUNT(1) AS num  FROM base_from_clarksons_index AS a INNER JOIN base_from_clarksons_data AS b ON a.index_code=b.index_code WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.base_from_clarksons_index_id) AS t `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetClarksonsIndexMaxSortByClassifyId 根据分类id获取指标最大排序
func GetClarksonsIndexMaxSortByClassifyId(classifyId int) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT MAX(sort) FROM base_from_clarksons_index WHERE classify_id=? `
	err = o.Raw(sql, classifyId).QueryRow(&sort)
	return
}

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

func GetClarksonsFrequencyByCondition(condition string, pars []interface{}) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_clarksons_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 GetClarksonsFrequencyByCode(code string) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_clarksons_index WHERE index_code=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, code).QueryRows(&items)
	return
}

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

func BatchModifyClarksonsIndexClassify(items []*BaseFromClarksonsIndex) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_clarksons_index SET classify_id=?, sort=? WHERE base_from_clarksons_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.BaseFromClarksonsIndexId)
		if err != nil {
			return
		}
	}
	return
}

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

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

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

func DeleteClarksonsIndexByIds(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_clarksons_index WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	_, err = o.Raw(sql, indexIds).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_clarksons_data WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	_, err = o.Raw(sql, indexIds).Exec()
	if err != nil {
		return
	}
	return
}

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

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

// GetClarksonsIndexInfoCount 分页查询指标信息行数
func GetClarksonsIndexInfoCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT count(1) FROM base_from_clarksons_index WHERE index_code not in (select edb_code from edb_info) `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetClarksonsIndexInfoPage 分页查询指标信息
func GetClarksonsIndexInfoPage(condition string, pars []interface{}) (items []*BaseFromRzdIndexAndData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_clarksons_index WHERE index_code not in (select edb_code from edb_info) `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return

}