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 }