eta_api/models/data_manage/base_from_clarksons_index.go

package data_manage

import (
	"database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"github.com/rdlucklib/rdluck_tools/paging"
	"gorm.io/gorm"
	"time"
)

type BaseFromClarksonsIndex struct {
	//BaseFromClarksonsIndexId int    `orm:"pk"`
	BaseFromClarksonsIndexId int    `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:"排序"`
	CreateTime               time.Time
	ModifyTime               time.Time
}

func (baseFromClarksonsIndex *BaseFromClarksonsIndex) AfterFind(tx *gorm.DB) (err error) {
			baseFromClarksonsIndex.StartDate = utils.GormDateStrToDateStr(baseFromClarksonsIndex.StartDate)
			baseFromClarksonsIndex.EndDate = utils.GormDateStrToDateStr(baseFromClarksonsIndex.EndDate)
	return
}

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
}

type BaseFromClarksonsIndexList struct {
	BaseFromClarksonsIndexId int                      `orm:"pk"`
	IndexCode                string                   // 指标编码
	IndexName                string                   // 指标名称
	ClassifyId               int                      // 分类Id
	Unit                     string                   // 单位
	Frequency                string                   // 频度
	Describe                 string                   // 指标描述
	CreateTime               string                   // 创建时间
	ModifyTime               string                   // 修改时间
	DataList                 []*BaseFromClarksonsData `gorm:"-"`
	Paging                   *paging.PagingItem       `description:"分页数据" gorm:"-"`
}

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

// GetClarksonsIndexByCondition 根据条件获取克拉克森指标列表
func GetClarksonsIndexByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	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 = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	return
}

// GetClarksonsIndexByCondition 根据条件获取克拉克森指标列表
func GetClarksonsIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	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`
	pars = append(pars, frequency)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	return
}

func GetClarksonsIndexCountByCondition(condition string, pars []interface{}) (count int, err error) {
	sqlStr := ` SELECT COUNT(*) AS count FROM base_from_clarksons_index WHERE 1=1 `

	if condition != "" {
		sqlStr += condition
	}
	sqlStr += ` ORDER BY sort ASC, base_from_clarksons_index_id ASC`
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, pars...).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}

	return
}

// GetClarksonsIndexAndEdbInfoByCondition 根据条件获取克拉克森index和指标库的信息
func GetClarksonsIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndexView, err error) {
	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 `
	pars = utils.ForwardPars(pars, utils.DATA_SOURCE_SCI_HQ)
	//err = global.DbMap[utils.DbNameIndex].Raw(sql, utils.DATA_SOURCE_SCI_HQ, pars).Find(&items).Error
	err = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	return
}

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

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

// GetClarksonsIndexListByIndexIds 根据指标id获取指标信息
func GetClarksonsIndexListByIndexIds(indexIds []int) (items []*BaseFromClarksonsIndex, err error) {
	if len(indexIds) == 0 {
		return
	}
	sql := ` SELECT * FROM base_from_clarksons_index WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexIds).Find(&items).Error
	return
}

// GetClarksonsIndexCountByClassifyIds 获取分类下指标的个数
func GetClarksonsIndexCountByClassifyIds(classifyIds []int) (count int, err error) {
	num := len(classifyIds)
	if num <= 0 {
		return
	}
	sqlStr := `SELECT COUNT(1) AS count FROM base_from_clarksons_index WHERE classify_id IN (` + utils.GetOrmInReplace(num) + `) `
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, classifyIds).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

// GetClarksonsIndexByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexByClassifyId(classifyIds []int, startSize, pageSize int) (items []*BaseFromClarksonsIndexView, err error) {
	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=101
	WHERE b.classify_id IN ? ORDER BY b.sort ASC LIMIT ?,? `
	//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=101
	//WHERE b.classify_id IN (` + utils.GetOrmInReplace(len(classifyIds)) + `) ORDER BY b.sort ASC LIMIT ?,? `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, classifyIds, startSize, pageSize).Find(&items).Error
	return
}

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

	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, classifyIds).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

// GetClarksonsIndexCount 获取克拉克森指标数量
func GetClarksonsIndexCount() (count int, err error) {
	sqlStr := ` SELECT COUNT(*) AS count FROM base_from_clarksons_index `
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

func GetClarksonsIndexByPage(startSize, pageSize int) (items []*BaseFromClarksonsIndexView, err error) {
	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=101
	ORDER BY b.modify_time DESC LIMIT ?,?`
	err = global.DbMap[utils.DbNameIndex].Raw(sql, startSize, pageSize).Find(&items).Error
	return
}

// GetClarksonsIndexBaseInfoByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexBaseInfoByClassifyId(classifyId int) (items []*BaseFromClarksonsIndexView, err error) {
	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 = global.DbMap[utils.DbNameIndex].Raw(sql, classifyId).Find(&items).Error
	return
}

// GetClarksonsIndexBaseInfoByClassifyId 根据分类id获取克拉克森指标列表
func GetClarksonsIndexBaseInfoByCondition(condition string, pars []interface{}) (items []*BaseFromClarksonsIndex, err error) {
	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 = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	return
}

func GetClarksonsDataMaxCount(condition string, pars []interface{}) (count int, err error) {
	sqlStr := `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 != "" {
		sqlStr += condition
	}
	sqlStr += ` GROUP BY a.base_from_clarksons_index_id) AS t `

	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, pars...).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

// GetClarksonsIndexMaxSortByClassifyId 根据分类id获取指标最大排序
func GetClarksonsIndexMaxSortByClassifyId(classifyId int) (sort int, err error) {
	sqlStr := `SELECT MAX(sort) FROM base_from_clarksons_index WHERE classify_id=? `
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, classifyId).Scan(&totalNull).Error
	if !totalNull.Valid {
		sort = 0
	} else {
		sort = int(totalNull.Int64)
	}
	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,'日度','周度','月度','季度','半年','年度') `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, classifyId).Find(&items).Error
	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,'日度','周度','月度','季度','半年','年度') `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	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,'日度','周度','月度','季度','半年','年度') `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, code).Find(&items).Error
	return
}

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

func BatchModifyClarksonsIndexClassify(items []*BaseFromClarksonsIndex) (err error) {
	sql := `UPDATE base_from_clarksons_index SET classify_id=?, sort=? WHERE base_from_clarksons_index_id=? `
	for _, v := range items {
		err = global.DbMap[utils.DbNameIndex].Exec(sql, v.ClassifyId, v.Sort, v.BaseFromClarksonsIndexId).Error
		if err != nil {
			return
		}
	}
	return
}

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

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

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

	sql := `DELETE FROM base_from_clarksons_index WHERE base_from_clarksons_index_id=? `
	err = tx.Exec(sql, indexId).Error
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_clarksons_data WHERE base_from_clarksons_index_id=? `
	err = tx.Exec(sql, indexId).Error
	if err != nil {
		return
	}
	return
}

func DeleteClarksonsIndexByIds(indexIds []int) (err error) {
	if len(indexIds) == 0 {
		return
	}
	tx := global.DbMap[utils.DbNameIndex].Begin()
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()
	sql := `DELETE FROM base_from_clarksons_index WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	err = tx.Exec(sql, indexIds).Error
	if err != nil {
		return
	}
	sql = `DELETE FROM base_from_clarksons_data WHERE base_from_clarksons_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `) `
	err = tx.Exec(sql, indexIds).Error
	if err != nil {
		return
	}
	return
}

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

// GetClarksonsIndexMinSortByClassifyId 获取最小不等于0的排序
func GetClarksonsIndexMinSortByClassifyId(classifyId int) (sort int, err error) {
	sqlStr := `SELECT min(sort) FROM base_from_clarksons_index WHERE classify_id=? and sort <> 0 `
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, classifyId).Scan(&totalNull).Error
	if !totalNull.Valid {
		sort = 0
	} else {
		sort = int(totalNull.Int64)
	}
	return
}

// GetClarksonsIndexInfoCount 分页查询指标信息行数
func GetClarksonsIndexInfoCount(condition string, pars []interface{}) (count int, err error) {
	sqlStr := ` SELECT count(1) FROM base_from_clarksons_index WHERE index_code not in (select edb_code from edb_info) `
	if condition != "" {
		sqlStr += condition
	}
	var totalNull sql.NullInt64
	err = global.DbMap[utils.DbNameIndex].Raw(sqlStr, pars...).Scan(&totalNull).Error
	if !totalNull.Valid {
		count = 0
	} else {
		count = int(totalNull.Int64)
	}
	return
}

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

}

func GetClarksonsIndex(condition string, pars []interface{}) (items []*BaseFromClarksonsIndexView, err error) {
	sql := ` SELECT * FROM base_from_clarksons_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += `ORDER BY base_from_clarksons_index_id ASC `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, pars...).Find(&items).Error
	return
}

func GetClarksonsIndexLatestDate(indexCode string) (ModifyTime string, err error) {
	sql := ` SELECT modify_time FROM base_from_clarksons_data WHERE index_code=? ORDER BY modify_time DESC limit 1 `
	err = global.DbMap[utils.DbNameIndex].Raw(sql, indexCode).Scan(&ModifyTime).Error
	return
}