eta_api/models/data_manage/base_from_sci_hq_index.go

package data_manage

import (
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"gorm.io/gorm"
	"time"
)

type BaseFromSciHqIndex struct {
	BaseFromSciHqIndexId int       `orm:"pk" gorm:"primaryKey"`
	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:"排序"`
	LatestValue          float64   `description:"最新值"`
	LatestDate           time.Time `description:"最后更新时间"`
	TerminalCode         string
	FilePath             string `json:"-"`
	CreateTime           time.Time
	ModifyTime           time.Time
}

type BaseFromSciHqIndexView struct {
	BaseFromSciHqIndexId int     `orm:"pk" gorm:"primaryKey"`
	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:"排序"`
	LatestValue          float64 `description:"最新值"`
	LatestDate           string  `description:"最后更新时间"`
	EdbExist             int     `description:"edb是否存在"`
	ModifyTime           string
}

func (b *BaseFromSciHqIndexView) AfterFind(db *gorm.DB) (err error) {
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		if b.LatestDate != "" {
			b.LatestDate = utils.GormDateStrToDateStr(b.LatestDate)
		}
		if b.StartDate != "" {
			b.StartDate = utils.GormDateStrToDateStr(b.StartDate)
		}
		if b.EndDate != "" {
			b.EndDate = utils.GormDateStrToDateStr(b.EndDate)
		}
		if b.ModifyTime != "" {
			b.ModifyTime = utils.GormDateStrToDateTimeStr(b.ModifyTime)
		}
	}

	return
}
func (b *BaseFromSciHqIndex) Update(cols []string) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	err = o.Model(b).Select(cols).Updates(b).Error
	return
}

// GetSciHqIndexByCondition 根据条件获取卓创红期指标列表
func GetSciHqIndexByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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...).Find(&items).Error
	return
}

// GetSciHqIndexByCondition 根据条件获取卓创红期指标列表
func GetSciHqIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

func GetSciHqIndexCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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...).Scan(&count).Error
	return
}

// GetSciHqIndexAndEdbInfoByCondition 根据条件获取卓创红期index和指标库的信息
func GetSciHqIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndexView, err error) {
	wherePars := make([]interface{}, 0)
	wherePars = append(wherePars, utils.DATA_SOURCE_SCI_HQ)
	wherePars = append(wherePars, pars...)
	o := global.DbMap[utils.DbNameIndex]
	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, wherePars...).Find(&items).Error
	return
}

// GetSciHqIndexByIndexCode 根据指标编码获取指标信息
func GetSciHqIndexByIndexCode(indexCode string) (item *BaseFromSciHqIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE index_code=? `
	err = o.Raw(sql, indexCode).First(&item).Error
	return
}

// GetSciHqIndexByIndexId 根据指标id获取指标信息
func GetSciHqIndexByIndexId(indexId int) (item *BaseFromSciHqIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT * FROM base_from_sci_hq_index WHERE base_from_sci_hq_index_id=? `
	err = o.Raw(sql, indexId).First(&item).Error
	return
}

// GetSciHqIndexListByIndexIds 根据指标id获取指标信息
func GetSciHqIndexListByIndexIds(indexIds []int) (items []*BaseFromSciHqIndex, err error) {
	if len(indexIds) == 0 {
		return
	}
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

// GetSciHqIndexCountByClassifyIds 获取分类下指标的个数
func GetSciHqIndexCountByClassifyIds(classifyIds []int) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Scan(&count).Error
	return
}

// GetSciHqIndexByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexByClassifyId(classifyIds []int, startSize, pageSize int) (items []*BaseFromSciHqIndexView, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

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

// GetSciHqIndexCount 获取卓创红期指标数量
func GetSciHqIndexCount() (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT COUNT(*) AS count FROM base_from_sci_hq_index `
	err = o.Raw(sql).Scan(&count).Error
	return
}

func GetSciHqIndexByPage(startSize, pageSize int) (items []*BaseFromSciHqIndexView, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

// GetSciHqIndexBaseInfoByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexBaseInfoByClassifyId(classifyId int) (items []*BaseFromSciHqIndexView, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

// GetSciHqIndexBaseInfoByClassifyId 根据分类id获取卓创红期指标列表
func GetSciHqIndexBaseInfoByCondition(condition string, pars []interface{}) (items []*BaseFromSciHqIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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...).Find(&items).Error
	return
}

func GetSciHqDataMaxCount(condition string, pars []interface{}) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `SELECT COALESCE(MAX(t.num), 0) 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 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY a.base_from_sci_hq_index_id) AS t `
	err = o.Raw(sql, pars...).Scan(&count).Error
	return
}

// GetSciHqIndexMaxSortByClassifyId 根据分类id获取指标最大排序
func GetSciHqIndexMaxSortByClassifyId(classifyId int) (sort int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `SELECT COALESCE(MAX(sort), 0) AS sort FROM base_from_sci_index WHERE classify_id=? `
	err = o.Raw(sql, classifyId).Scan(&sort).Error
	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 := global.DbMap[utils.DbNameIndex]
	err = o.Raw(sql, classifyId).Find(&items).Error
	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 := global.DbMap[utils.DbNameIndex]
	err = o.Raw(sql, pars...).Find(&items).Error
	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 := global.DbMap[utils.DbNameIndex]
	err = o.Raw(sql, code).Find(&items).Error
	return
}

// GetSciHqClassifyMaxSortByClassifyIds 通过分类id获取对应分类的最大sort
func GetSciHqClassifyMaxSortByClassifyIds(classifyIds []int) (items []*BaseFromSciHqClassifyMaxSort, err error) {
	if len(classifyIds) == 0 {
		return
	}
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

func BatchModifySciHqIndexClassify(items []*BaseFromSciHqIndex) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `UPDATE base_from_sci_hq_index SET classify_id=?, sort=? WHERE base_from_sci_hq_index_id=? `

	for _, v := range items {
		err = o.Exec(sql, v.ClassifyId, v.Sort, v.BaseFromSciHqIndexId).Error
		if err != nil {
			return
		}
	}
	return
}

// MoveDownSciHqIndexBySort 往下移动
func MoveDownSciHqIndexBySort(classifyId, prevSort, currentSort int) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `update base_from_sci_hq_index set sort = sort - 1 where classify_id=? and sort <= ? and sort> ? `
	err = o.Exec(sql, classifyId, prevSort, currentSort).Error
	return
}

// MoveUpSciHqIndexBySort 往上移动
func MoveUpSciHqIndexBySort(classifyId, nextSort, currentSort int) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `update base_from_sci_hq_index set sort = sort + 1 where classify_id=? and sort >= ? and sort< ?`
	err = o.Exec(sql, classifyId, nextSort, currentSort).Error
	return
}

func DeleteSciHqIndexById(indexId int) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	tx := o.Begin()
	if tx.Error != nil {
		return tx.Error
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	sql := `DELETE FROM base_from_sci_hq_index WHERE base_from_sci_hq_index_id=? `
	err = tx.Exec(sql, indexId).Error
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_sci_hq_data WHERE base_from_sci_hq_index_id=? `
	err = tx.Exec(sql, indexId).Error
	if err != nil {
		return
	}
	return
}

func DeleteSciHqIndexByIds(indexIds []int) (err error) {
	if len(indexIds) == 0 {
		return
	}
	o := global.DbMap[utils.DbNameIndex]
	tx := o.Begin()
	if tx.Error != nil {
		return tx.Error
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	sql := `DELETE FROM base_from_sci_hq_index WHERE base_from_sci_hq_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	err = tx.Exec(sql, indexIds).Error
	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 = tx.Exec(sql, indexIds).Error
	if err != nil {
		return
	}
	return
}

// MoveSciHqIndex 移动指标分类
func MoveSciHqIndex(indexId, classifyId int) (err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` UPDATE base_from_sci_hq_index
			SET
			  classify_id = ?, modify_time=NOW() 
			WHERE base_from_sci_hq_index_id = ?`
	err = o.Exec(sql, classifyId, indexId).Error
	return
}

// GetSciHqIndexMinSortByClassifyId 获取最小不等于0的排序
func GetSciHqIndexMinSortByClassifyId(classifyId int) (sort int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `SELECT min(sort) FROM base_from_sci_hq_index WHERE classify_id=? and sort <> 0 `
	err = o.Raw(sql, classifyId).Scan(&sort).Error
	return
}