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) { if utils.NeedDateOrTimeFormat(utils.DbDriverName) { if baseFromClarksonsIndex.StartDate != "" { baseFromClarksonsIndex.StartDate = utils.GormDateStrToDateStr(baseFromClarksonsIndex.StartDate) } if baseFromClarksonsIndex.EndDate != "" { 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 }