package data_manage import ( "eta/eta_mobile/services/alarm_msg" "eta/eta_mobile/utils" "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "time" ) type EdbInfo struct { EdbInfoId int `orm:"column(edb_info_id);pk"` EdbInfoType int `description:"指标类型，0：普通指标，1：预测指标"` SourceName string `description:"来源名称"` Source int `description:"来源id"` EdbCode string `description:"指标编码"` EdbName string `description:"指标名称"` EdbNameEn string `description:"英文指标名称"` EdbNameSource string `description:"指标名称来源"` Frequency string `description:"频率"` Unit string `description:"单位"` UnitEn string `description:"英文单位"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` ClassifyId int `description:"分类id"` SysUserId int SysUserRealName string UniqueCode string `description:"指标唯一编码"` CreateTime time.Time ModifyTime time.Time MinValue float64 `description:"指标最小值"` MaxValue float64 `description:"指标最大值"` CalculateFormula string `description:"计算公式"` EdbType int `description:"指标类型:1:基础指标,2:计算指标"` Sort int `description:"排序字段"` LatestDate string `description:"数据最新日期"` LatestValue float64 `description:"数据最新值"` MoveType int `description:"移动方式:1:领先(默认),2:滞后"` MoveFrequency string `description:"移动频度"` NoUpdate int8 `description:"是否停止更新，0：继续更新；1：停止更新"` ServerUrl string `description:"服务器地址"` ChartImage string `description:"图表图片"` Calendar string `description:"公历/农历" orm:"default(公历);"` DataDateType string `orm:"column(data_date_type);size(255);null;default(交易日)"` ManualSave int `description:"是否有手动保存过上下限: 0-否; 1-是"` } func AddEdbInfo(item *EdbInfo) (lastId int64, err error) { o := orm.NewOrmUsingDB("data") lastId, err = o.Insert(item) return } type EdbInfoItem struct { EdbInfoId int `description:"指标id"` EdbName string `description:"指标名称"` ClassifyId int `description:"指标分类"` } // GetEdbInfoAll 用于分类展示 func GetEdbInfoAll(edbInfoType uint8) (items []*EdbClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT edb_info_id,classify_id,edb_name_source AS classify_name,unique_code,source_name,source,sys_user_id,sys_user_real_name,start_date,edb_code,edb_type FROM edb_info WHERE edb_info_type = ? order by sort asc,edb_info_id asc` _, err = o.Raw(sql, edbInfoType).QueryRows(&items) return } // GetPredictEdbInfoAll 用于分类展示（预测指标） func GetPredictEdbInfoAll(edbInfoType uint8) (items []*EdbClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT edb_info_id,classify_id,edb_name AS classify_name,unique_code,source_name,source,sys_user_id,sys_user_real_name,start_date,edb_code,edb_type FROM edb_info WHERE edb_info_type = ? order by sort asc,edb_info_id asc` _, err = o.Raw(sql, edbInfoType).QueryRows(&items) return } // 用于分类展示 func GetEdbInfoAllList() (items []*EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info ` _, err = o.Raw(sql).QueryRows(&items) return } // 指标检索数据 type EdbInfoSearch struct { EdbCode string `description:"指标编码"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` EdbName string `description:"指标名称"` Unit string `description:"单位"` Frequency string `description:"频率"` DataList []*EdbInfoSearchData } type EdbInfoSearchData struct { DataTime string `description:"数据日期"` Value float64 `description:"数据"` } type EdbInfoSearchResp struct { SearchItem *EdbInfoSearch `description:"指标分类"` Status int `description:"1:数据已存在于弘则数据库，2:新数据，3:数据已存在于弘则数据库，但是当前账号无权限"` ClassifyList []*EdbClassifySimplify } func GetEdbInfoByEdbCode(source int, edbCode string) (item *EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE source=? AND edb_code=? ` err = o.Raw(sql, source, edbCode).QueryRow(&item) return } func GetEdbInfoById(edbInfoId int) (item *EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&item) return } // GetEdbInfoByIdList 根据指标id集合 获取 指标列表 func GetEdbInfoByIdList(edbInfoIdList []int) (items []*EdbInfo, err error) { num := len(edbInfoIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_info_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, edbInfoIdList).QueryRows(&items) return } type AddEdbInfoReq struct { Source int `description:"来源id"` EdbCode string `description:"指标编码"` EdbName string `description:"指标名称"` Frequency string `description:"频率"` Unit string `description:"单位"` ClassifyId int `description:"分类id"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` } func DeleteEdbInfoAndData(edbInfoId, source int) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() // 删除指标信息 sql := ` DELETE FROM edb_info WHERE edb_info_id=? ` _, err = to.Raw(sql, edbInfoId).Exec() if err != nil { return } // 删除指标数据 tableName := GetEdbDataTableName(source) if tableName != "" { sql = ` DELETE FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, tableName) _, err = to.Raw(sql, edbInfoId).Exec() if err != nil { return } } //calculateTableName := GetEdbInfoCalculateTableName(source) //if calculateTableName != "" { // sql = ` DELETE FROM %s WHERE edb_info_id=? ` // sql = fmt.Sprintf(sql, calculateTableName) // _, err = to.Raw(sql, edbInfoId).Exec() // return //} //return // 删除计算指标的关系 sql = ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id=? ` _, err = to.Raw(sql, edbInfoId).Exec() // 删除预测指标的配置 sql = ` DELETE FROM predict_edb_conf WHERE predict_edb_info_id=? ` _, err = to.Raw(sql, edbInfoId).Exec() return } func GetEdbInfoCountByCondition(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } type EditEdbInfoReq struct { EdbInfoId int `description:"指标ID"` EdbName string `description:"指标名称"` Frequency string `description:"频率"` Unit string `description:"单位"` ClassifyId int `description:"分类id"` CalculateFormula string `description:"计算公式"` } type EditEdbEnInfoReq struct { EdbInfoId int `description:"指标ID"` EdbNameEn string `description:"英文指标名称"` UnitEn string `description:"英文单位"` } func ModifyEdbInfo(item *EditEdbInfoReq) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET edb_name =?, edb_name_source =?, frequency = ?, unit = ?, classify_id = ?, modify_time = NOW() WHERE edb_info_id = ?` _, err = o.Raw(sql, item.EdbName, item.EdbName, item.Frequency, item.Unit, item.ClassifyId, item.EdbInfoId).Exec() return } // ModifyEdbEnInfo 修改指标英文信息 func ModifyEdbEnInfo(item *EditEdbEnInfoReq) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET edb_name_en =?, unit_en = ?, modify_time = NOW() WHERE edb_info_id = ?` _, err = o.Raw(sql, item.EdbNameEn, item.UnitEn, item.EdbInfoId).Exec() return } type EdbInfoList struct { EdbInfoId int `orm:"column(edb_info_id);pk"` EdbInfoType int `description:"指标类型，0：普通指标，1：预测指标"` SourceName string `description:"来源名称"` Source int `description:"来源id"` EdbCode string `description:"指标编码"` EdbNameEn string `description:"英文指标名称"` EdbName string `description:"指标名称"` Frequency string `description:"频率"` FrequencyEn string `description:"英文频率"` Unit string `description:"单位"` UnitEn string `description:"英文单位"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` LatestDate string `description:"数据最新日期"` LatestValue float64 `description:"数据最新值"` ClassifyId int `description:"分类id"` UniqueCode string `description:"指标唯一编码"` SysUserId int `description:"创建人id"` SysUserRealName string `description:"创建人姓名"` ModifyTime string `description:"最新修改时间"` CreateTime string `description:"创建时间"` EdbNameAlias string `json:"-" description:"指标名称,别名"` EdbType int `description:"指标类型:1:基础指标,2:计算指标"` ChartImage string `description:"图表图片"` RuleType int `description:"预测规则，1：最新，2：固定值"` FixedValue float64 `description:"固定值"` DataList []*EdbData `description:"实际指标数据"` PredictDataList []*EdbData `description:"预测指标数据"` Button EdbClassifyItemsButton `description:"操作权限"` IsEnEdb bool `description:"是否展示英文标识"` DataInsertConfig EdbDataInsertConfigItem `description:"指标数据插入配置"` DataDateType string `description:"数据日期类型，枚举值：交易日、自然日"` } type EdbDataInsertConfigItem struct { Date string `description:"插入的日期"` RealDate string `description:"实际最晚的日期"` Value string `description:"插入的值"` } type EdbData struct { EdbDataId int `orm:"column(edb_data_id);pk"` EdbInfoId int DataTime string Value float64 } type EdbInfoListResp struct { Paging *paging.PagingItem Item *EdbInfoList ClassifyList []*EdbClassifySimplify } func GetEdbInfoByCondition(condition string, pars []interface{}) (item *EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&item) return } func GetEdbDataCountByCondition(condition string, pars []interface{}, source int) (count int, err error) { o := orm.NewOrmUsingDB("data") tableName := GetEdbDataTableName(source) sql := ` SELECT COUNT(1) AS count FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } func GetEdbDataListByCondition(condition string, pars []interface{}, source, pageSize, startSize int) (item []*EdbData, err error) { o := orm.NewOrmUsingDB("data") tableName := GetEdbDataTableName(source) sql := ` SELECT * FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } sql += ` ORDER BY data_time DESC ` sql += ` LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item) return } // GetAllEdbDataListByCondition 根据条件获取所有的数据 func GetAllEdbDataListByCondition(condition string, pars []interface{}, source int) (item []*EdbData, err error) { o := orm.NewOrmUsingDB("data") tableName := GetEdbDataTableName(source) sql := ` SELECT * FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } sql += ` ORDER BY data_time DESC ` _, err = o.Raw(sql, pars).QueryRows(&item) return } func GetEdbInfoByNewest() (item *EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE 1=1 ORDER BY modify_time DESC LIMIT 1 ` err = o.Raw(sql).QueryRow(&item) return } func ModifyEdbInfoModifyTime(edbInfoId int64) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET modify_time = NOW() WHERE edb_info_id = ? ` _, err = o.Raw(sql, edbInfoId).Exec() return } type MoveEdbInfoReq struct { EdbInfoId int `description:"指标ID"` PrevEdbInfoId int `description:"上一个指标ID"` NextEdbInfoId int `description:"下一个指标ID"` ClassifyId int `description:"分类id"` } func MoveEdbInfo(edbInfoId, classifyId int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET classify_id = ? WHERE edb_info_id = ?` _, err = o.Raw(sql, classifyId, edbInfoId).Exec() return } // GetEdbInfoByName 根据名称获取普通的指标 func GetEdbInfoByName(edbName string) (items []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_name=? AND edb_info_type = 0 ` _, err = o.Raw(sql, edbName).QueryRows(&items) return } // GetPredictEdbInfoByName 根据指标名称获取预测指标 func GetPredictEdbInfoByName(edbName string) (items []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_name=? AND edb_info_type = 1 ` _, err = o.Raw(sql, edbName).QueryRows(&items) return } func GetEdbInfoByEnName(edbNameEn string) (items []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_name_en=? AND edb_info_type = 0 ` _, err = o.Raw(sql, edbNameEn).QueryRows(&items) return } func ModifyEdbInfoNameSource(edbNameSource string, edbInfoId int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET edb_name_source=? WHERE edb_info_id = ? ` _, err = o.Raw(sql, edbNameSource, edbInfoId).Exec() return } func GetChartEdbMappingCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM chart_edb_mapping WHERE edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } type ChartEdbInfo struct { EdbInfoId int `description:"指标id"` EdbName string `description:"指标名称"` SourceName string `json:"-"` EdbNameAlias string `json:"-" description:"指标名称,别名"` } func EdbInfoSearchByKeyWord(KeyWord string) (searchList []*ChartEdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT edb_info_id,edb_name,source_name FROM edb_info WHERE 1=1 AND edb_info_type = 0 ` if KeyWord != "" { sql += ` AND (edb_name LIKE '%` + KeyWord + `%' OR edb_code LIKE '%` + KeyWord + `%' ) ` } sql += ` ORDER BY create_time DESC ` _, err = o.Raw(sql).QueryRows(&searchList) return } type EdbInfoMaxAndMinInfo struct { MinDate string `description:"最小日期"` MaxDate string `description:"最大日期"` MinValue float64 `description:"最小值"` MaxValue float64 `description:"最大值"` LatestValue float64 `description:"最新值"` } func GetEdbInfoMaxAndMinInfo(source int, edbCode string) (item *EdbInfoMaxAndMinInfo, err error) { o := orm.NewOrmUsingDB("data") sql := `` tableName := GetEdbDataTableName(source) sql = ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date,MIN(value) AS min_value,MAX(value) AS max_value FROM %s WHERE edb_code=? ` sql = fmt.Sprintf(sql, tableName) err = o.Raw(sql, edbCode).QueryRow(&item) if err != nil { return } var latest_value float64 sql = ` SELECT value AS latest_value FROM %s WHERE edb_code=? ORDER BY data_time DESC LIMIT 1 ` sql = fmt.Sprintf(sql, tableName) err = o.Raw(sql, edbCode).QueryRow(&latest_value) item.LatestValue = latest_value return } func ModifyEdbInfoMaxAndMinInfo(edbInfoId int, item *EdbInfoMaxAndMinInfo) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET start_date=?,end_date=?,min_value=?,max_value=?,is_update=2,latest_date=?,latest_value=?,modify_time=NOW() WHERE edb_info_id=? ` _, err = o.Raw(sql, item.MinDate, item.MaxDate, item.MinValue, item.MaxValue, item.MaxDate, item.LatestValue, edbInfoId).Exec() return } func GetEdbInfoFilter(condition string, pars []interface{}) (list []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE 1=1 AND edb_info_type = 0 ` if condition != "" { sql += condition } sql += ` ORDER BY create_time DESC ` _, err = o.Raw(sql, pars).QueryRows(&list) return } //order:1升序，其余值为降序 func GetEdbDataListAll(condition string, pars []interface{}, source, order int) (item []*EdbInfoSearchData, err error) { o := orm.NewOrmUsingDB("data") sql := `` tableName := GetEdbDataTableName(source) sql = ` SELECT * FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } if order == 1 { sql += ` ORDER BY data_time ASC ` } else { sql += ` ORDER BY data_time DESC ` } _, err = o.Raw(sql, pars).QueryRows(&item) return } // GetLastEdbData 获取最近的一条指标数据 func GetLastEdbData(condition string, pars []interface{}, source int) (item *EdbInfoSearchData, err error) { o := orm.NewOrmUsingDB("data") sql := `` tableName := GetEdbDataTableName(source) sql = ` SELECT * FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } sql += ` ORDER BY data_time DESC ` err = o.Raw(sql, pars).QueryRow(&item) return } //order:1升序，其余值为降序 func GetEdbDataCount(condition string, pars []interface{}, source int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := `` tableName := GetEdbDataTableName(source) sql = ` SELECT COUNT(1) AS count FROM %s WHERE 1=1 ` sql = fmt.Sprintf(sql, tableName) if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } func ModifyCalculateEdbInfo(item *EditEdbInfoReq) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE edb_info SET edb_name =?, edb_name_source =?, frequency = ?, unit = ?, classify_id = ?, calculate_formula=?, modify_time = NOW() WHERE edb_info_id = ?` _, err = o.Raw(sql, item.EdbName, item.EdbName, item.Frequency, item.Unit, item.ClassifyId, item.CalculateFormula, item.EdbInfoId).Exec() return } type AddEdbInfoResp struct { EdbInfoId int `description:"指标ID"` UniqueCode string `description:"指标唯一编码"` } // 根据基础指标获取所有关联指标 func GetNeedRefreshCalculateEdbInfoFromBase(fromEdbInfo int) (list []*EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM edb_info_calculate AS a INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id WHERE from_edb_info_id=? ` _, err = o.Raw(sql, fromEdbInfo).QueryRows(&list) return } func GetEdbInfoCalculateCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate WHERE from_edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } func GetEdbInfoCalculateLjzzyCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate_ljzzy WHERE from_edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } func GetEdbInfoCalculateTbzCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate_tbz WHERE from_edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } func GetEdbInfoCalculateTczCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate_tcz WHERE from_edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } func GetEdbInfoCalculateNszydpjjsCount(edbInfoId int) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate_nszydpjjs WHERE from_edb_info_id=? ` err = o.Raw(sql, edbInfoId).QueryRow(&count) return } func GetEdbInfoCalculateCountByCondition(source int, condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") //calculateTableName := GetEdbInfoCalculateTableName(source) //if calculateTableName == "" { // err = errors.New("无效的表名") // return //} sql := ` SELECT COUNT(1) AS count FROM edb_info_calculate_mapping WHERE 1=1 ` //sql = fmt.Sprintf(sql, calculateTableName) if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } // GetEdbInfoCalculateListByCondition 获取指标关系列表 func GetEdbInfoCalculateListByCondition(condition string, pars []interface{}) (items []*EdbInfoCalculateMapping, err error) { o := orm.NewOrmUsingDB("data") //calculateTableName := GetEdbInfoCalculateTableName(source) //if calculateTableName == "" { // err = errors.New("无效的表名") // return //} sql := ` SELECT * FROM edb_info_calculate_mapping WHERE 1=1 ` //sql = fmt.Sprintf(sql, calculateTableName) if condition != "" { sql += condition } _, err = o.Raw(sql, pars).QueryRows(&items) return } func GetRefreshEdbInfoFromCalculate(edbInfoId, source int) (baseEdbInfoArr, calculateInfoArr []*EdbInfo, err error) { calculateList, err := GetEdbInfoCalculateMap(edbInfoId, source) if err != nil && err.Error() != utils.ErrNoRow() { return } for _, item := range calculateList { if item.EdbType == 1 { baseEdbInfoArr = append(baseEdbInfoArr, item) } else { calculateInfoArr = append(calculateInfoArr, item) newBaseEdbInfoArr, newCalculateInfoArr, _ := GetRefreshEdbInfoFromCalculate(item.EdbInfoId, item.Source) baseEdbInfoArr = append(baseEdbInfoArr, newBaseEdbInfoArr...) calculateInfoArr = append(calculateInfoArr, newCalculateInfoArr...) } } return } // 获取基础指标对应的所有计算指标 func GetEdbInfoAllCalculate(edbInfoId int) (list []*EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM edb_info_calculate_mapping AS a INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id WHERE a.from_edb_info_id=? GROUP BY a.edb_info_id ORDER BY a.edb_info_id ASC ` _, err = o.Raw(sql, edbInfoId).QueryRows(&list) return } // GetEdbInfoAllCalculateByEdbInfoIdList 根据指标id集合 获取基础指标对应的所有计算指标 func GetEdbInfoAllCalculateByEdbInfoIdList(edbInfoIdList []int) (list []*EdbInfo, err error) { num := len(edbInfoIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM edb_info_calculate_mapping AS a INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id WHERE a.from_edb_info_id in (` + utils.GetOrmInReplace(num) + `) GROUP BY a.edb_info_id ORDER BY a.edb_info_id ASC ` _, err = o.Raw(sql, edbInfoIdList).QueryRows(&list) return } func GetRefreshEdbInfoFromBase(edbInfoId, source int) (baseEdbInfoArr, calculateInfoArr []*EdbInfo, err error) { calculateList, err := GetEdbInfoCalculateMap(edbInfoId, source) if err != nil && err.Error() != utils.ErrNoRow() { return } for _, item := range calculateList { if item.EdbInfoId == edbInfoId { // 如果查出来关联的指标就是自己的话，那么就过滤 continue } if item.EdbType == 1 { baseEdbInfoArr = append(baseEdbInfoArr, item) } else { calculateInfoArr = append(calculateInfoArr, item) newBaseEdbInfoArr, newCalculateInfoArr, _ := GetRefreshEdbInfoFromBase(item.EdbInfoId, item.Source) baseEdbInfoArr = append(baseEdbInfoArr, newBaseEdbInfoArr...) calculateInfoArr = append(calculateInfoArr, newCalculateInfoArr...) } } return } func ModifyEdbInfoDataStatus(edbInfoId int64, source int, edbCode string) (err error) { o := orm.NewOrmUsingDB("data") sql := `` tableName := GetEdbDataTableName(source) sql = ` UPDATE %s SET edb_info_id=?,modify_time=NOW() WHERE edb_code=? ` sql = fmt.Sprintf(sql, tableName) _, err = o.Raw(sql, edbInfoId, edbCode).Exec() return } // GetFirstEdbInfoByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据 func GetFirstEdbInfoByClassifyId(classifyId int) (item *EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE classify_id=? order by sort asc,edb_info_id asc limit 1` err = o.Raw(sql, classifyId).QueryRow(&item) return } // UpdateEdbInfoSortByClassifyId 根据分类id更新排序 func UpdateEdbInfoSortByClassifyId(classifyId, nowSort int, prevEdbInfoId int, updateSort string) (err error) { o := orm.NewOrmUsingDB("data") sql := ` update edb_info set sort = ` + updateSort + ` WHERE classify_id=? AND ` if prevEdbInfoId > 0 { sql += ` ( sort > ? or ( edb_info_id > ` + fmt.Sprint(prevEdbInfoId) + ` and sort=` + fmt.Sprint(nowSort) + ` )) ` } _, err = o.Raw(sql, classifyId, nowSort).Exec() return } // Update 更新指标基础信息 func (edbInfo *EdbInfo) Update(cols []string) (err error) { o := orm.NewOrmUsingDB("data") _, err = o.Update(edbInfo, cols...) return } type EdbInfoDataResp struct { EdbInfo *EdbInfo DataList []*EdbDataList } type EdbInfoReplaceReq struct { OldEdbInfoId int `description:"原指标ID"` NewEdbInfoId int `description:"替换为指标ID"` } // ReplaceChartEdb 替换图表中的指标 func ReplaceChartEdb(oldEdbInfo, newEdbInfo *EdbInfo) (mappingList []*EdbInfoCalculateMapping, replaceChartTotal, replaceCalculateTotal int, err error) { return replaceChartEdb(oldEdbInfo, newEdbInfo) } // replaceChartEdb 替换图表中的指标 func replaceChartEdb(oldEdbInfo, newEdbInfo *EdbInfo) (mappingList []*EdbInfoCalculateMapping, replaceChartTotal, replaceCalculateTotal int, err error) { var errmsg string o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } if errmsg != "" { fmt.Println("errmsg:" + errmsg) } if err != nil && errmsg != "" { go alarm_msg.SendAlarmMsg("替换图表中的指标失败提醒,errmsg："+errmsg, 3) //go utils.SendEmail(utils.APPNAME+"【"+utils.RunMode+"】"+"替换指标失败提醒", "errmsg:"+errmsg, utils.EmailSendToUsers) } }() //替换图表 chartEdbMappingList := make([]*ChartEdbInfoMapping, 0) csql := `SELECT * FROM chart_edb_mapping WHERE edb_info_id=?` _, err = to.Raw(csql, oldEdbInfo.EdbInfoId).QueryRows(&chartEdbMappingList) if err != nil { errmsg = "获取指标关联图表信息失败：Err:" + err.Error() return } replaceChartTotal = len(chartEdbMappingList) for _, mv := range chartEdbMappingList { //获取图表所有指标信息 chartEdbList := make([]*ChartEdbInfoMapping, 0) csql := `SELECT * FROM chart_edb_mapping WHERE chart_info_id=?` _, err = to.Raw(csql, mv.ChartInfoId).QueryRows(&chartEdbList) if err != nil { errmsg = "获取图表所有指标信息失败：Err:" + err.Error() return } var minData, maxData float64 minData = newEdbInfo.MinValue maxData = newEdbInfo.MaxValue for _, cv := range chartEdbList { if mv.IsAxis == cv.IsAxis { if minData > cv.MinData { minData = cv.MinData } if maxData < cv.MaxData { maxData = cv.MaxData } } } //修改图表关联指标 rsql := ` UPDATE chart_edb_mapping SET edb_info_id=?,max_data=?,min_data=?,modify_time=NOW() WHERE chart_edb_mapping_id=? ` _, err = to.Raw(rsql, newEdbInfo.EdbInfoId, maxData, minData, mv.ChartEdbMappingId).Exec() if err != nil { errmsg = "更新图库指标信息失败：Err:" + err.Error() return } } //替换计算指标 //获取所有包含的计算指标 mappingList = make([]*EdbInfoCalculateMapping, 0) msql := ` SELECT * FROM edb_info_calculate_mapping WHERE from_edb_info_id=? GROUP BY edb_info_id ` _, err = to.Raw(msql, oldEdbInfo.EdbInfoId).QueryRows(&mappingList) if err != nil { errmsg = "获取计算指标关联基础指标信息失败：Err:" + err.Error() return } replaceCalculateTotal = len(mappingList) //计算指标 for _, mv := range mappingList { //如果即将替换的指标与当前指标id一致，那么就退出当前循环，进入下一循环 if mv.EdbInfoId == newEdbInfo.EdbInfoId { continue } //替换原指标 msql := `UPDATE edb_info_calculate_mapping SET from_edb_info_id=?,from_edb_code=?,from_edb_name=?,from_source=?,from_source_name=? WHERE edb_info_calculate_mapping_id=? ` _, err = to.Raw(msql, newEdbInfo.EdbInfoId, newEdbInfo.EdbCode, newEdbInfo.EdbName, newEdbInfo.Source, newEdbInfo.SourceName, mv.EdbInfoCalculateMappingId).Exec() if err != nil { return } } return } type EdbInfoView struct { EdbInfoId int `orm:"column(edb_info_id);pk"` EdbInfoType int `description:"指标类型，0：普通指标，1：预测指标"` SourceName string `description:"来源名称"` Source int `description:"来源id"` EdbCode string `description:"指标编码"` EdbName string `description:"指标名称"` EdbNameSource string `description:"指标名称来源"` Frequency string `description:"频率"` Unit string `description:"单位"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` ClassifyId int `description:"分类id"` SysUserId int SysUserRealName string UniqueCode string `description:"指标唯一编码"` CreateTime string ModifyTime string MinValue float64 `description:"指标最小值"` MaxValue float64 `description:"指标最大值"` CalculateFormula string `description:"计算公式"` EdbType int `description:"指标类型:1:基础指标,2:计算指标"` Sort int `description:"排序字段"` IsUpdate int `description:"当天是否已更新，1:未更新,2:已更新"` LatestDate string `description:"数据最新日期"` LatestValue float64 `description:"数据最新值"` } // 获取指标的所有计算指标，以及计算指标所依赖计算指标 func GetAllCalculateByEdbInfoId(edbInfoId int) (mappingList []*EdbInfoCalculateMapping, err error) { o := orm.NewOrmUsingDB("data") msql := ` SELECT * FROM edb_info_calculate_mapping WHERE from_edb_info_id=? GROUP BY edb_info_id ` _, err = o.Raw(msql, edbInfoId).QueryRows(&mappingList) if err != nil { return } return } func GetAllCalculate(edbInfoId int, edbInfoMap map[int]int) (mappingList []*EdbInfoCalculateMapping, err error) { calculateList, err := GetAllCalculateByEdbInfoId(edbInfoId) if err != nil && err.Error() != utils.ErrNoRow() { return } for _, item := range calculateList { _, ok := edbInfoMap[item.EdbInfoId] if !ok { edbInfoMap[item.EdbInfoId] = item.EdbInfoId mappingList = append(mappingList, item) newCalculateInfoArr, _ := GetAllCalculate(item.EdbInfoId, edbInfoMap) mappingList = append(mappingList, newCalculateInfoArr...) } } return } // GetAllCalculateByEdbInfoIdV2 获取计算指标的所有所依赖的指标（基础指标+计算指标） func GetAllCalculateByEdbInfoIdV2(edbInfoId int) (mappingList []*EdbInfoCalculateMapping, err error) { o := orm.NewOrmUsingDB("data") msql := ` SELECT * FROM edb_info_calculate_mapping WHERE edb_info_id=? GROUP BY from_edb_info_id ` _, err = o.Raw(msql, edbInfoId).QueryRows(&mappingList) if err != nil { return } return } // GetAllCalculateV2 获取所有的指标（如果下层指标包含了待替换指标，那么就不加入） func GetAllCalculateV2(oldEdbInfoId, replaceEdbInfoId int, edbInfoMap map[int]int) (mappingList []*EdbInfoCalculateMapping, err error) { calculateList, err := GetAllCalculateByEdbInfoId(oldEdbInfoId) if err != nil && err.Error() != utils.ErrNoRow() { return } tmpMappingList := make([]*EdbInfoCalculateMapping, 0) for _, item := range calculateList { //如果计算指标里面存在即将替换的指标id，那么退出当前循环，进入下一循环 if item.EdbInfoId == replaceEdbInfoId { err = fmt.Errorf("指标重复1") return } //获取该指标的依赖指标列表，如果依赖指标列表中存在即将替换的指标，那么过滤该指标 childCalculateList, tmpErr := GetAllCalculateByEdbInfoIdV2(item.EdbInfoId) if tmpErr != nil && tmpErr.Error() != utils.ErrNoRow() { err = tmpErr return } for _, childCalculate := range childCalculateList { if childCalculate.FromEdbInfoId == replaceEdbInfoId { err = fmt.Errorf("指标重复2") return } } _, ok := edbInfoMap[item.EdbInfoId] if !ok { edbInfoMap[item.EdbInfoId] = item.EdbInfoId newCalculateInfoArr, tmpErr := GetAllCalculateV2(item.EdbInfoId, replaceEdbInfoId, edbInfoMap) if tmpErr != nil { err = tmpErr continue } else { mappingList = append(mappingList, item) tmpMappingList = append(tmpMappingList, newCalculateInfoArr...) } } } mappingList = append(mappingList, tmpMappingList...) return } // EdbInfoFilterDataResp 搜索指标列表数据返回 type EdbInfoFilterDataResp struct { Paging *paging.PagingItem List []*EdbInfoList } // GetEdbInfoFilterList 获取指指标列表数据接口 func GetEdbInfoFilterList(condition string, pars []interface{}, startSize, pageSize int) (total int64, list []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT count(1) total FROM edb_info where 1=1 ` + condition + ` ORDER BY edb_info_id DESC ` err = o.Raw(sql, pars).QueryRow(&total) if err != nil { return } sql = ` SELECT * FROM edb_info where 1=1 ` + condition + ` ORDER BY edb_info_id DESC LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list) return } // SetEdbInfoImageReq 设置指标图片 type SetEdbInfoImageReq struct { EdbInfoId int `description:"指标ID"` ImageUrl string `description:"指标图片地址"` } // GetNextEdbInfoByCondition 根据条件获取下一个指标 func GetNextEdbInfoByCondition(condition string, pars []interface{}) (item *EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE 1=1 ` if condition != "" { sql += condition } sql += " ORDER BY sort asc , edb_info_id asc LIMIT 1 " err = o.Raw(sql, pars).QueryRow(&item) return } // GetNextEdbInfo 根据分类id获取下一个 指标 func GetNextEdbInfo(classifyId, classifySort, classifyType, edbInfoType int) (item *EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM edb_classify AS a INNER JOIN edb_info AS b ON a.classify_id=b.classify_id WHERE (a.sort>? OR (a.sort=? and a.classify_id>?) ) AND a.classify_type=? AND b.edb_info_type=? ORDER BY a.sort ASC,b.sort asc,b.edb_info_id asc LIMIT 1 ` err = o.Raw(sql, classifySort, classifySort, classifyId, classifyType, edbInfoType).QueryRow(&item) return } // GetEdbInfoByEdbCodeList 根据指标code集合获取指标列表 func GetEdbInfoByEdbCodeList(source int, edbCodeList []string) (items []*EdbInfo, err error) { num := len(edbCodeList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE source=? AND edb_code in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, source, edbCodeList).QueryRows(&items) return } // GetEdbInfoByConditionCount 根据条件获取指标列表数据总数 func GetEdbInfoByConditionCount(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM edb_info WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } // GetEdbInfoListByCondition 根据条件获取指标列表数据 func GetEdbInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*EdbInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE 1=1 ` if condition != "" { sql += condition } sql += ` ORDER BY edb_info_id ASC LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } // GetEdbInfoListGroupByUserId 根据指标id列表、用户分组获取指标信息 func GetEdbInfoListGroupByUserId(edbIdList []string) (items []*EdbInfo, err error) { num := len(edbIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM edb_info WHERE edb_info_id in (` + utils.GetOrmInReplace(num) + `) GROUP BY sys_user_id ` _, err = o.Raw(sql, edbIdList).QueryRows(&items) return } // ModifyEdbInfoUserIdByCodeList 根据指标code列表修改创建人 func ModifyEdbInfoUserIdByCodeList(edbIdList []string, userId int, userName string) (err error) { num := len(edbIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := `UPDATE edb_info SET sys_user_id=?,sys_user_real_name=? WHERE edb_info_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, userId, userName, edbIdList).Exec() return } // MoveEdbChartList 图表/指标信息 type MoveEdbChartList struct { DataId string `description:"指标/图表唯一id"` Code string `description:"指标code"` Name string `description:"指标/图表名称"` ClassifyName string `description:"分类名称"` CreateUserId int `description:"创建人id"` CreateUserName string `description:"创建人名称"` } // MoveEdbChartListResp 更改创建人返回数据 type MoveEdbChartListResp struct { Paging *paging.PagingItem List []MoveEdbChartList } // MoveEdbChartReq 转移指标/图表的请求 type MoveEdbChartReq struct { Source int `description:"来源id"` DataIdList []string `description:"指标/图表唯一id列表"` NewUserId int `description:"新的创建人id"` } type EdbChartClassifyResp struct { List []*EdbChartClassify } type EdbChartClassify struct { ClassifyId int ClassifyName string ParentId int Child []*EdbChartClassify } // EdbInfoDataSeasonalResp 指标季节性数据返回 type EdbInfoDataSeasonalResp struct { EdbInfo *EdbInfo DataList interface{} } // SetEdbDataInsertConfigReq 设置插入规则的请求 type SetEdbDataInsertConfigReq struct { EdbInfoId int `description:"指标ID"` Date string `description:"日期"` Value float64 `description:"值"` } // GetEdbInfoAll 用于分类展示 func GetEdbInfoByClassifyId(classifyId, edbInfoType int) (items []*EdbClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT edb_info_id,classify_id,edb_name_source AS classify_name,unique_code,source_name,source,sys_user_id,sys_user_real_name,start_date,edb_code,edb_type FROM edb_info WHERE classify_id = ? AND edb_info_type = ? order by sort asc,edb_info_id asc` _, err = o.Raw(sql, classifyId, edbInfoType).QueryRows(&items) return } type EdbAndClassify struct { EdbInfoId int `description:"指标id"` EdbCode string `description:"指标code"` UniqueCode string `description:"唯一code"` EdbName string `description:"指标名称"` FirstClassifyId int `description:"第1级的分类id"` FirstClassifyName string `description:"第1级的分类名称"` SecondClassifyId int `description:"第2级的分类id"` SecondClassifyName string `description:"第2级的分类名称"` } // GetEdbInfoAndClassifyListByEdbIdList 根据指标id列表获取 指标及分类信息 func GetEdbInfoAndClassifyListByEdbIdList(edbIdList []int) (items []*EdbAndClassify, err error) { num := len(edbIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT a.edb_info_id,a.edb_code,a.unique_code,a.edb_name,b.classify_id as second_classify_id,b.classify_name as second_classify_name,c.classify_id as first_classify_id,c.classify_name as first_classify_name FROM edb_info AS a JOIN edb_classify b on a.classify_id=b.classify_id JOIN edb_classify c on b.parent_id=c.classify_id WHERE a.edb_info_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, edbIdList).QueryRows(&items) return } // TraceEdbInfoResp 指标追溯数据返回 type TraceEdbInfoResp struct { //EdbInfo *EdbInfo EdbInfoId int `description:"指标id"` EdbName string `description:"指标名称"` RuleTitle string `description:"指标规则"` Child []TraceEdbInfoResp `description:"下级来源"` }