eta_mobile/models/data_manage/mysteel_chemical_index.go

package data_manage

import (
	"eta/eta_mobile/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

// BaseFromMysteelChemicalIndex 上海钢联指标表
type BaseFromMysteelChemicalIndex struct {
	BaseFromMysteelChemicalIndexId    int       `orm:"column(base_from_mysteel_chemical_index_id);pk"`
	BaseFromMysteelChemicalClassifyId int       `orm:"column(base_from_mysteel_chemical_classify_id)" description:"上海钢联指标分类id"`
	IndexCode                         string    `description:"指标编码"`
	IndexName                         string    `description:"指标名称"`
	Unit                              string    `description:"单位"`
	Source                            string    `description:"数据来源"`
	Frequency                         string    `description:"频度"`
	StartDate                         string    `description:"开始日期"`
	EndDate                           string    `description:"结束日期"`
	Describe                          string    `description:"指标描述"`
	UpdateWeek                        string    `description:"更新周期"`
	UpdateTime                        string    `description:"更新时间1"`
	UpdateTime2                       string    `description:"更新时间2"`
	SysUserId                         int       `description:"创建人id"`
	SysUserRealName                   string    `description:"创建人姓名"`
	ModifyTime                        time.Time `description:"修改时间"`
	CreateTime                        time.Time `description:"创建时间"`
	Sort                              int       `description:"排序字段"`
	MergeFilePath                     string    `description:"合并文件"`
	TerminalCode                      string    `description:"终端编码"`
	IsStop                            int       `description:"是否停更:1:停更,0:未停更"`
	EndValue                          float64   `description:"指标的最新值"`
}

// Update 更新上海钢联指标基础信息
func (item *BaseFromMysteelChemicalIndex) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(item, cols...)
	return
}

// AddBaseFromMysteelChemicalIndex 添加上海钢联指标
func AddBaseFromMysteelChemicalIndex(item *BaseFromMysteelChemicalIndex) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("data")
	lastId, err = o.Insert(item)
	if err != nil {
		return
	}
	item.BaseFromMysteelChemicalIndexId = int(lastId)

	return
}

// BaseFromMysteelChemicalData 上海钢联指标数据表
type BaseFromMysteelChemicalData struct {
	BaseFromMysteelChemicalDataId  int       `orm:"column(base_from_mysteel_chemical_data_id);pk"`
	BaseFromMysteelChemicalIndexId int       `orm:"column(base_from_mysteel_chemical_index_id)" description:"上海钢联指标id"`
	IndexCode                      string    `description:"指标编码"`
	DataTime                       time.Time `description:"数据日期"`
	Value                          float64   `description:"数据值"`
	ModifyTime                     time.Time `description:"修改时间"`
	CreateTime                     time.Time `description:"创建时间"`
}

// MysteelChemicalFrequency 上海钢联频度
type MysteelChemicalFrequency struct {
	Frequency string `description:"频度:1-日度 2-周度 3-月度 4-季度 5-年度 99-无固定频率"`
}

// GetMysteelChemicalIndexByClassifyId 用于分类展示
func GetMysteelChemicalIndexByClassifyId(classifyId int) (items []*BaseFromMysteelChemicalClassifyItems, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT base_from_mysteel_chemical_index_id,base_from_mysteel_chemical_classify_id,index_name AS classify_name,
             sys_user_id,sys_user_real_name,sort,index_code 
            FROM base_from_mysteel_chemical_index where base_from_mysteel_chemical_classify_id=? ORDER BY sort asc, create_time ASC `
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

// MysteelChemicalFrequencyByClassifyId 根据分类id获取上海钢联频度数据列表
func MysteelChemicalFrequencyByClassifyId(classifyId int) (items []*MysteelChemicalFrequency, err error) {
	o := orm.NewOrmUsingDB("data")
	if classifyId == 0 {
		sql := ` SELECT frequency  FROM base_from_mysteel_chemical_index WHERE frequency != "" GROUP BY frequency ORDER BY frequency ASC `
		_, err = o.Raw(sql).QueryRows(&items)
		return
	} else {
		sql := ` SELECT frequency  FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_classify_id = ? AND frequency != "" GROUP BY frequency ORDER BY frequency ASC `
		_, err = o.Raw(sql, classifyId).QueryRows(&items)
		return
	}
}

// GetMysteelChemicalFrequency 获取上海钢联频度数据列表
func GetMysteelChemicalFrequency(condition string, pars []interface{}) (items []*MysteelChemicalFrequency, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT frequency FROM base_from_mysteel_chemical_index WHERE 1=1 AND frequency != "" `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY frequency ORDER BY frequency ASC `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// MysteelChemicalList 上海钢联指标列表
type MysteelChemicalList struct {
	Id                                int                `orm:"column(base_from_mysteel_chemical_index_id)"`
	BaseFromMysteelChemicalClassifyId int                `orm:"column(base_from_mysteel_chemical_classify_id)" description:"上海钢联指标分类id"`
	ParentClassifyId                  int                `description:"上海钢联指标父级分类id"`
	IndexCode                         string             `description:"指标编码"`
	IndexName                         string             `description:"指标名称"`
	UnitName                          string             `orm:"column(unit)"`
	UniqueCode                        string             `description:"唯一编码"`
	FrequencyName                     string             `orm:"column(frequency)"`
	UpdateTime                        string             `orm:"column(modify_time)"`
	Paging                            *paging.PagingItem `description:"分页数据"`
	DataList                          []*MysteelChemicalData
}

// MysteelChemicalData 上海钢联数据列表
type MysteelChemicalData struct {
	InputValue string `orm:"column(value)" description:"值"`
	DataTime   string `orm:"column(data_time)" description:"日期"`
}

// GetMysteelChemicalIndex 根据分类id获取上海钢联频度数据列表
func GetMysteelChemicalIndex(condition string, pars []interface{}) (items []*MysteelChemicalList, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE 1=1 `

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

// GetMysteelChemicalIndexData 根据指标code获取上海钢联数据列表
func GetMysteelChemicalIndexData(indexCode string, startSize, pageSize int) (items []*MysteelChemicalData, err error) {
	sql := ` SELECT * FROM (
	SELECT DISTINCT a.index_code,a.value,a.data_time FROM base_from_mysteel_chemical_data AS a WHERE index_code=? 
	ORDER BY data_time DESC
	)AS t
	GROUP BY t.data_time
	ORDER BY t.data_time DESC LIMIT ?,? `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, indexCode, startSize, pageSize).QueryRows(&items)
	return
}

// GetMysteelChemicalIndexDataCount 根据指标code获取上海钢联数据列表 获取钢联数据总数
func GetMysteelChemicalIndexDataCount(indexCode string) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM (
			SELECT * FROM (
	SELECT DISTINCT a.index_code,a.value,a.data_time FROM base_from_mysteel_chemical_data AS a WHERE index_code=? 
	ORDER BY data_time DESC
	)AS t
	GROUP BY t.data_time
	ORDER BY t.data_time DESC
			)AS n `
	o := orm.NewOrmUsingDB("data")
	err = o.Raw(sql, indexCode).QueryRow(&count)
	return
}

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

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

// GetBaseFromMysteelChemicalIndexByClassifyIdAndName 根据分类id和指标名名获取指标信息
func GetBaseFromMysteelChemicalIndexByClassifyIdAndName(classifyId int, chartName string) (item *BaseFromMysteelChemicalIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_classify_id = ? and index_name = ? `
	err = o.Raw(sql, classifyId, chartName).QueryRow(&item)
	return
}

// GetBaseFromMysteelChemicalIndexListByClassifyId 根据指标分类id获取指标列表信息
func GetBaseFromMysteelChemicalIndexListByClassifyId(classifyId int) (items []*BaseFromMysteelChemicalIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_classify_id=? `
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

// GetBaseFromMysteelChemicalIndexListByClassifyIdList 根据指标分类id集合获取指标列表信息
func GetBaseFromMysteelChemicalIndexListByClassifyIdList(classifyIdList []int) (items []*BaseFromMysteelChemicalIndex, err error) {
	num := len(classifyIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_classify_id IN (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, classifyIdList).QueryRows(&items)
	return
}

// GetBaseFromMysteelChemicalDataMaxCount 获取分类下指标的最大数量
func GetBaseFromMysteelChemicalDataMaxCount(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_mysteel_chemical_index AS a
				INNER JOIN base_from_mysteel_chemical_data AS b ON a.base_from_mysteel_chemical_index_id=b.base_from_mysteel_chemical_index_id
				WHERE a.base_from_mysteel_chemical_classify_id=?
				GROUP BY a.base_from_mysteel_chemical_index_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

// GetMysteelChemicalIndexDataByCode 通过上海钢联指标code获取所有数据列表
func GetMysteelChemicalIndexDataByCode(indexCode string) (items []*MysteelChemicalData, err error) {
	sql := ` SELECT * FROM (
	SELECT DISTINCT a.index_code,a.value,a.data_time FROM base_from_mysteel_chemical_data AS a WHERE index_code=? 
	ORDER BY data_time DESC
	)AS t
	GROUP BY t.data_time
	ORDER BY t.data_time DESC `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, indexCode).QueryRows(&items)
	return
}

// MoveBaseFromMysteelChemicalIndex 移动上海钢联指标分类
func MoveBaseFromMysteelChemicalIndex(chartInfoId, classifyId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` UPDATE base_from_mysteel_chemical_index
			SET
			  base_from_mysteel_chemical_classify_id = ?
			WHERE base_from_mysteel_chemical_index_id = ?`
	_, err = o.Raw(sql, classifyId, chartInfoId).Exec()
	return
}

// UpdateBaseFromMysteelChemicalIndexByClassifyId 根据指标id更新排序
func UpdateBaseFromMysteelChemicalIndexByClassifyId(classifyId, nowSort, prevIndexInfoId int, updateSort string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update base_from_mysteel_chemical_index set sort = ` + updateSort + ` WHERE base_from_mysteel_chemical_classify_id=? AND  `
	if prevIndexInfoId > 0 {
		sql += ` ( sort > ? or (base_from_mysteel_chemical_index_id > ` + fmt.Sprint(prevIndexInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `))`
	}
	_, err = o.Raw(sql, classifyId, nowSort).Exec()
	return
}

// GetFirstBaseFromMysteelChemicalIndexByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据
func GetFirstBaseFromMysteelChemicalIndexByClassifyId(classifyId int) (item *BaseFromMysteelChemicalIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_classify_id=? order by sort asc,base_from_mysteel_chemical_index_id asc limit 1`
	err = o.Raw(sql, classifyId).QueryRow(&item)
	return
}

// GetMysteelChemicalIndexCount 根据条件获取上海钢联数据
func GetMysteelChemicalIndexCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM base_from_mysteel_chemical_index WHERE 1=1 `

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

// GetMysteelChemicalIndexList 根据分类id获取上海钢联频度数据列表
func GetMysteelChemicalIndexList(condition string, pars []interface{}, startSize, pageSize int, orderDesc string) (items []*BaseFromMysteelChemicalIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}

	if orderDesc == `` {
		orderDesc = ` ASC `
	}
	sql += ` ORDER BY base_from_mysteel_chemical_index_id `
	sql += orderDesc
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)

	return
}

// GetMysteelChemicalIndexListGroupByUserId 根据指标id列表、用户分组获取指标信息
func GetMysteelChemicalIndexListGroupByUserId(edbIdList []string) (items []*BaseFromMysteelChemicalIndex, err error) {
	num := len(edbIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_index_id in (` + utils.GetOrmInReplace(num) + `) GROUP BY sys_user_id `
	_, err = o.Raw(sql, edbIdList).QueryRows(&items)

	return
}

// GetMysteelChemicalIndexListByIndexId
// @Description: 根据指标id列表获取列表信息
// @param edbIdList
// @return items
// @return err
func GetMysteelChemicalIndexListByIndexId(edbIdList []string) (items []*BaseFromMysteelChemicalIndex, err error) {
	num := len(edbIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_index_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, edbIdList).QueryRows(&items)

	return
}

// GetMysteelChemicalIndexListByUserId
// @Description: 根据用户id列表获取列表信息
// @param userIdList
// @return items
// @return err
func GetMysteelChemicalIndexListByUserId(userIdList []int) (items []*BaseFromMysteelChemicalIndex, err error) {
	num := len(userIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_mysteel_chemical_index WHERE sys_user_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, userIdList).QueryRows(&items)

	return
}

// ModifyMysteelChemicalIndexUserIdByCodeList 根据指标code列表修改创建人
func ModifyMysteelChemicalIndexUserIdByCodeList(edbIdList []string, userId int, userName string) (err error) {
	num := len(edbIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_mysteel_chemical_index SET sys_user_id=?,sys_user_real_name=? WHERE base_from_mysteel_chemical_index_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, userId, userName, edbIdList).Exec()
	return
}

// ModifyMysteelChemicalIndexUserIdByOldUserId
// @Description: 根据旧用户id修改新用户id
// @author: Roc
// @datetime 2024-03-25 17:59:08
// @param oldUserId int
// @param userId int
// @param userName string
// @return err error
func ModifyMysteelChemicalIndexUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) {
	num := len(oldUserIdList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_mysteel_chemical_index SET sys_user_id=?,sys_user_real_name=? WHERE sys_user_id in (` + utils.GetOrmInReplace(num) + `)  `
	_, err = o.Raw(sql, userId, userName, oldUserIdList).Exec()

	return
}

// GetMysteelChemicalIndexAdminList 获取所有指标创建人
func GetMysteelChemicalIndexAdminList() (list []int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT sys_user_id FROM base_from_mysteel_chemical_index GROUP BY sys_user_id `

	_, err = o.Raw(sql).QueryRows(&list)
	return
}

type TerminalNum struct {
	TerminalCode string `description:"terminal_code"`
	Num          int    `description:"num"`
}

// GetMysteelChemicalGroupTerminalNum 获取上海钢联指标的终端分布
func GetMysteelChemicalGroupTerminalNum() (items []*TerminalNum, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT terminal_code,count(1) num FROM base_from_mysteel_chemical_index GROUP BY terminal_code ORDER BY num ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// BaseRefreshEdbInfo
// @Description: 刷新配置的基础指标信息结构体
type BaseRefreshEdbInfo struct {
	EdbInfoId       int
	ClassifyId      int    `description:"上海钢联指标分类id"`
	IndexCode       string `description:"指标编码"`
	IndexName       string `description:"指标名称"`
	EndDate         string `description:"最新日期"`
	EndValue        string `description:"最新值"`
	SysUserId       int    `description:"创建人id"`
	SysUserRealName string `description:"创建人姓名"`
	Frequency       string `description:"频度"`
	IsStop          int    `description:"是否停更:1:停更,0:未停更"`
	TerminalCode    string `description:"终端编码"`
	RefreshTime     string `description:"刷新时间"`
}

// RefreshBaseEdbInfoResp
// @Description: 刷新数据源的数据返回
type RefreshBaseEdbInfoResp struct {
	Paging *paging.PagingItem
	List   []*BaseRefreshEdbInfo
}

// GetMysteelChemicalBaseInfoList
// @Description: 获取上海钢联数据列表
// @author: Roc
// @datetime 2024-01-10 14:28:35
// @param condition string
// @param pars []interface{}
// @param orderBy string
// @param startSize int
// @param pageSize int
// @return total int
// @return items []*BaseRefreshEdbInfo
// @return err error
func GetMysteelChemicalBaseInfoList(condition string, pars []interface{}, orderBy string, startSize, pageSize int) (total int, items []*BaseRefreshEdbInfo, err error) {
	o := orm.NewOrmUsingDB("data")

	// 数量汇总
	totalSql := ` SELECT count(1) FROM base_from_mysteel_chemical_index WHERE 1=1 `
	if condition != "" {
		totalSql += condition
	}
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}

	// 列表数据
	sql := ` SELECT base_from_mysteel_chemical_index_id as edb_info_id, base_from_mysteel_chemical_classify_id as classify_id,index_code,index_name,end_date,end_value,sys_user_id,sys_user_real_name,frequency,is_stop,terminal_code FROM base_from_mysteel_chemical_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}

	if orderBy != "" {
		sql += ` ORDER BY ` + orderBy
	} else {
		sql += ` ORDER BY base_from_mysteel_chemical_index_id ASC `
	}
	sql += `  LIMIT ?,? `

	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// ModifyMysteelChemicalUpdateStatus
// @Description:  修改上海钢联数据停更状态
// @author: Roc
// @datetime 2024-01-08 16:23:31
// @param edbIdList []int
// @param indexCodeList []string
// @param isStop int
// @return err error
func ModifyMysteelChemicalUpdateStatus(edbIdList []int, indexCodeList []string, isStop int) (err error) {
	idNum := len(edbIdList)
	if idNum <= 0 {
		return
	}
	o, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
			return
		}
		_ = o.Commit()
	}()

	// 更改数据源的更新状态
	sql := ` UPDATE base_from_mysteel_chemical_index SET is_stop = ? WHERE base_from_mysteel_chemical_index_id IN (` + utils.GetOrmInReplace(idNum) + `) `
	_, err = o.Raw(sql, isStop, edbIdList).Exec()
	if err != nil {
		return
	}

	codeNum := len(indexCodeList)
	if codeNum <= 0 {
		// 需要通过指标id列表查找code列表
		sql := ` SELECT index_code FROM base_from_mysteel_chemical_index WHERE base_from_mysteel_chemical_index_id IN (` + utils.GetOrmInReplace(idNum) + `) `
		_, err = o.Raw(sql, edbIdList).QueryRows(&indexCodeList)
		if err != nil {
			return
		}
	}

	codeNum = len(indexCodeList)
	// 查出来的编码是空的话，那么就直接返回了
	if codeNum <= 0 {
		return
	}

	// 更改指标的更新状态
	sql = ` UPDATE edb_info SET no_update = ? WHERE source = ? AND sub_source= ? AND edb_code IN (` + utils.GetOrmInReplace(codeNum) + `) `
	_, err = o.Raw(sql, isStop, utils.DATA_SOURCE_MYSTEEL_CHEMICAL, 0, indexCodeList).Exec()
	if err != nil {
		return
	}

	return
}