package data_manage
import (
"errors"
"eta/eta_api/utils"
"fmt"
"github.com/beego/beego/v2/client/orm"
"github.com/yidane/formula"
"strconv"
"strings"
"time"
)
type EdbInfoCalculateSaveReq struct {
EdbName string `description:"指标名称"`
Frequency string `description:"频率"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
CalculateFormula string `description:"计算公式"`
Calendar string `description:"公历/农历"`
EdbInfoIdArr []EdbInfoFromTag `description:"指标信息"`
}
// EdbInfoFromTag 计算指标的关联指标
type EdbInfoFromTag struct {
EdbInfoId int `description:"指标id"`
FromTag string `description:"指标对应标签"`
MoveValue int `description:"移动的值"`
}
type EdbInfoCalculate struct {
EdbInfoCalculateId int `orm:"column(edb_info_calculate_id);pk"`
EdbInfoId int `description:"指标id"`
EdbCode string `description:"指标编码"`
FromEdbInfoId int `description:"计算指标id"`
FromEdbCode string `description:"计算指标编码"`
FromEdbName string `description:"计算指标名称"`
FromSource int `description:"计算指标来源"`
FromSourceName string `description:"计算指标来源名称"`
FromTag string `description:"来源指标标签"`
Sort int `description:"计算指标名称排序"`
CreateTime time.Time `description:"创建时间"`
ModifyTime time.Time `description:"修改时间"`
}
func AddEdbInfoCalculateMulti(items []*EdbInfoCalculate) (err error) {
o := orm.NewOrmUsingDB("data")
_, err = o.InsertMulti(1, items)
return
}
type FromEdbInfoData struct {
EdbInfoId int `description:"指标id"`
EdbName string `description:"指标名称"`
DataList []*EdbInfoSearchData
}
type EdbInfoCalculateDetail struct {
EdbInfoCalculateId int `orm:"column(edb_info_calculate_id);pk"`
EdbInfoId int `description:"指标id"`
EdbCode string `description:"指标编码"`
FromEdbInfoId int `description:"计算指标id"`
FromEdbCode string `description:"计算指标编码"`
FromEdbName string `description:"计算指标名称"`
FromSource int `description:"计算指标来源"`
FromSourceName string `description:"计算指标来源名称"`
FromTag string `description:"来源指标标签"`
MoveValue int `description:"移动的值，小于0是提前，0是不变，大于0是滞后"`
Sort int `description:"计算指标名称排序"`
CreateTime time.Time `description:"创建时间"`
ModifyTime time.Time `description:"修改时间"`
StartDate string `description:"开始日期"`
EndDate string `description:"结束日期"`
LatestDate string `description:"实际的结束日期"`
LatestValue float64 `description:"最近实际数据的值"`
EndValue float64 `description:"结束日期的值（可能是插入值）"`
EdbType int `description:"指标类型:1:基础指标,2:计算指标"`
}
func GetEdbInfoCalculateDetail(edbInfoId, source int) (list []*EdbInfoCalculateDetail, err error) {
o := orm.NewOrmUsingDB("data")
//calculateTableName := GetEdbInfoCalculateTableName(source)
//sql := ` SELECT a.*,b.start_date,b.end_date,b.edb_type FROM %s AS a
// INNER JOIN edb_info AS b ON a.from_edb_info_id=b.edb_info_id
// WHERE a.edb_info_id=? ORDER BY sort ASC `
//
//sql = fmt.Sprintf(sql, calculateTableName)
sql := ` SELECT a.edb_info_calculate_mapping_id,a.edb_info_id,a.source,a.source_name,a.edb_code,a.from_edb_info_id,a.from_edb_code,a.from_source,a.from_source_name,a.sort,a.create_time,a.modify_time,a.from_tag,a.move_value,b.edb_name_source as from_edb_name,b.start_date,b.end_date,b.latest_date,b.latest_value,b.edb_type FROM edb_info_calculate_mapping AS a
INNER JOIN edb_info AS b ON a.from_edb_info_id=b.edb_info_id
WHERE a.edb_info_id=? ORDER BY sort ASC `
_, err = o.Raw(sql, edbInfoId).QueryRows(&list)
return
}
type CalculateDetailResp struct {
EdbInfoDetail *EdbInfo
CalculateList []*EdbInfoCalculateDetail
}
type EdbInfoCalculateEditReq struct {
EdbInfoId int `description:"指标id"`
EdbName string `description:"指标名称"`
Frequency string `description:"频率"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
CalculateFormula string `description:"计算公式"`
EdbInfoIdArr []EdbInfoFromTag `description:"指标信息"`
}
func GetCalculateEdbInfo(edbInfoId int) (from_edb_info_id string, err error) {
o := orm.NewOrmUsingDB("data")
sql := ` SELECT GROUP_CONCAT(from_edb_info_id ORDER BY sort ASC SEPARATOR ',') AS from_edb_info_id
FROM edb_info_calculate_mapping
WHERE edb_info_id=?
GROUP BY edb_info_id
`
err = o.Raw(sql, edbInfoId).QueryRow(&from_edb_info_id)
return
}
func DeleteCalculateEdbInfo(edbInfoId 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_data_calculate WHERE edb_info_id=?`
_, err = to.Raw(sql, edbInfoId).Exec()
if err != nil {
return
}
sql = `DELETE FROM edb_info_calculate_mapping WHERE edb_info_id=?`
_, err = to.Raw(sql, edbInfoId).Exec()
return
}
func DeleteCalculateData(edbInfoId int) (err error) {
o := orm.NewOrmUsingDB("data")
sql := `DELETE FROM edb_data_calculate WHERE edb_info_id=?`
_, err = o.Raw(sql, edbInfoId).Exec()
return
}
type EdbInfoCalculateBatchSaveReq struct {
EdbInfoId int `description:"指标id"`
EdbName string `description:"指标名称"`
Frequency string `description:"频度"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
Formula string `description:"N值/移动天数"`
FromEdbInfoId int `description:"计算来源指标id"`
Source int `description:"来源:1:同花顺,2:wind,3:彭博,4:指标运算,5:累计值转月,6:同比值,7:同差值,8:N数值移动平均计算,12:环比值,13:环差值,14:变频"`
CalculateFormula string `description:"计算公式"`
EdbInfoIdArr []EdbInfoFromTag `description:"指标信息"`
MoveType int `description:"移动方式:1:领先(默认),2:滞后"`
MoveFrequency string `description:"移动频度:天/周/月/季/年"`
Calendar string `description:"公历/农历"`
}
type EdbInfoCalculateBatchEditReq struct {
EdbName string `description:"指标名称"`
Frequency string `description:"频度"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
Formula string `description:"N值"`
EdbInfoId int `description:"编辑指标id"`
FromEdbInfoId int `description:"计算来源指标id"`
Source int `description:"来源:1:同花顺,2:wind,3:彭博,4:指标运算,5:累计值转月,6:同比值,7:同差值,8:N数值移动平均计算,12:环比值,13:环差值,14:变频"`
MoveType int `description:"移动方式:1:领先(默认),2:滞后"`
MoveFrequency string `description:"移动频度:天/周/月/季/年"`
Calendar string `description:"公历/农历"`
EdbInfoIdArr []EdbInfoFromTag
}
// EdbInfoCalculateBatchSaveReqByEdbLib edb指标库的请求逻辑
type EdbInfoCalculateBatchSaveReqByEdbLib struct {
AdminId int `description:"添加人id"`
AdminName string `description:"添加人名称"`
EdbInfoId int `description:"指标id"`
EdbName string `description:"指标名称"`
Frequency string `description:"频度"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
Formula string `description:"N值/移动天数"`
FromEdbInfoId int `description:"计算来源指标id"`
Source int `description:"来源:1:同花顺,2:wind,3:彭博,4:指标运算,5:累计值转月,6:同比值,7:同差值,8:N数值移动平均计算,12:环比值,13:环差值,14:变频"`
CalculateFormula string `description:"计算公式"`
EdbInfoIdArr []EdbInfoFromTag `description:"指标信息"`
MoveType int `description:"移动方式:1:领先(默认),2:滞后"`
MoveFrequency string `description:"移动频度:天/周/月/季/年"`
Calendar string `description:"公历/农历"`
}
// EdbInfoCalculateBatchEditReq 编辑计算指标的请求参数
type EdbInfoCalculateBatchEditReqByEdbLib struct {
EdbName string `description:"指标名称"`
Frequency string `description:"频度"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
Formula string `description:"N值"`
EdbInfoId int `description:"编辑指标id"`
FromEdbInfoId int `description:"计算来源指标id"`
Source int `description:"来源:1:同花顺,2:wind,3:彭博,4:指标运算,5:累计值转月,6:同比值,7:同差值,8:N数值移动平均计算,12:环比值,13:环差值,14:变频"`
MoveType int `description:"移动方式:1:领先(默认),2:滞后"`
MoveFrequency string `description:"移动频度:天/周/月/季/年"`
Calendar string `description:"公历/农历"`
EdbInfoIdArr []EdbInfoFromTag
}
func GetEdbInfoCalculateMap(edbInfoId, source int) (list []*EdbInfo, err error) {
o := orm.NewOrmUsingDB("data")
//calculateTableName := GetEdbInfoCalculateTableName(source)
//sql := ` SELECT b.* FROM %s AS a
// INNER JOIN edb_info AS b ON a.from_edb_info_id=b.edb_info_id
// WHERE a.edb_info_id=? ORDER BY sort ASC `
//sql = fmt.Sprintf(sql, calculateTableName)
sql := ` SELECT b.* FROM edb_info_calculate_mapping AS a
INNER JOIN edb_info AS b ON a.from_edb_info_id=b.edb_info_id
WHERE a.edb_info_id=? ORDER BY sort ASC `
_, err = o.Raw(sql, edbInfoId).QueryRows(&list)
return
}
type CalculateItems struct {
EdbInfoId int
DataMap map[string]float64
}
func CheckFormula(formula string) map[string]string {
mathFormula := []string{"MAX", "MIN", "ABS", "ACOS", "ASIN", "CEIL", "MOD", "POW", "ROUND", "SIGN", "SIN", "TAN", "LOG10", "LOG2", "LOG", "LN"}
str := strings.ToUpper(formula)
for _, v := range mathFormula {
str = strings.Replace(str, v, "", -1)
}
str = strings.Replace(str, "(", "", -1)
str = strings.Replace(str, ")", "", -1)
byteMap := make(map[string]string)
for i := 0; i < len(str); i++ {
byteInt := str[i]
if byteInt >= 65 && byteInt <= 90 {
byteStr := string(byteInt)
if _, ok := byteMap[byteStr]; !ok {
byteMap[byteStr] = byteStr
}
}
}
return byteMap
}
func ReplaceFormula(edbInfoIdArr []*EdbInfo, valArr map[int]float64, formulaMap map[string]string, formulaStr string, edbInfoIdBytes []string) string {
funMap := GetFormulaMap()
for k, v := range funMap {
formulaStr = strings.Replace(formulaStr, k, v, -1)
}
replaceCount := 0
for dk, dv := range edbInfoIdArr {
if dk == 0 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 1 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 2 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 3 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 4 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 5 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 6 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 7 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
if dk == 8 {
dKey := edbInfoIdBytes[dk]
if _, ok := formulaMap[dKey]; ok { //公式中存在
if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在
dvStr := fmt.Sprintf("%v", val)
formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1)
replaceCount++
}
}
}
}
for k, v := range funMap {
formulaStr = strings.Replace(formulaStr, v, k, -1)
}
if replaceCount == len(formulaMap) {
return formulaStr
} else {
return ""
}
}
func GetFormulaMap() map[string]string {
funMap := make(map[string]string)
funMap["MAX"] = "[@@]"
funMap["MIN"] = "[@!]"
funMap["ABS"] = "[@#]"
funMap["CEIL"] = "[@$]"
funMap["COS"] = "[@%]"
funMap["FLOOR"] = "[@^]"
funMap["MOD"] = "[@&]"
funMap["POW"] = "[@*]"
funMap["ROUND"] = "[@(]"
return funMap
}
func Calculate(edbInfoIdArr []*EdbInfo, edbInfoId int, edbCode, formulaStr string, edbInfoIdBytes []string) (err error) {
defer func() {
if err != nil {
utils.FileLog.Info("Calculate Err:%s" + err.Error())
}
}()
saveDataMap := make(map[string]map[int]float64)
for _, v := range edbInfoIdArr {
var condition string
var pars []interface{}
condition += " AND edb_info_id=? "
pars = append(pars, v.EdbInfoId)
dataList, err := GetEdbDataListAll(condition, pars, v.Source, 1)
if err != nil {
return err
}
dataMap := make(map[string]float64)
for _, dv := range dataList {
if val, ok := saveDataMap[dv.DataTime]; ok {
if _, ok := val[v.EdbInfoId]; !ok {
val[v.EdbInfoId] = dv.Value
}
} else {
temp := make(map[int]float64)
temp[v.EdbInfoId] = dv.Value
saveDataMap[dv.DataTime] = temp
}
}
item := new(CalculateItems)
item.EdbInfoId = v.EdbInfoId
item.DataMap = dataMap
}
formulaMap := CheckFormula(formulaStr)
addSql := ` INSERT INTO edb_data_calculate(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
nowStr := time.Now().Format(utils.FormatDateTime)
var isAdd bool
for sk, sv := range saveDataMap {
formulaStr = strings.ToUpper(formulaStr)
formulaFormStr := ReplaceFormula(edbInfoIdArr, sv, formulaMap, formulaStr, edbInfoIdBytes)
if formulaStr == "" {
return
}
if formulaFormStr != "" {
utils.FileLog.Info("formulaFormStr:%s", formulaFormStr)
expression := formula.NewExpression(formulaFormStr)
calResult, err := expression.Evaluate()
if err != nil {
err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr)
fmt.Println(err)
return err
}
calVal, err := calResult.Float64()
if err != nil {
err = errors.New("计算失败：获取计算值失败 Err:" + err.Error() + ";formulaStr:" + formulaFormStr)
fmt.Println(err)
return err
}
//需要存入的数据
{
dataTime, _ := time.Parse(utils.FormatDate, sk)
timestamp := dataTime.UnixNano() / 1e6
timeStr := fmt.Sprintf("%d", timestamp)
addSql += "("
addSql += strconv.Itoa(edbInfoId) + "," + "'" + edbCode + "'" + "," + "'" + sk + "'" + "," + utils.SubFloatToString(calVal, 4) + "," + "'" + nowStr + "'" +
"," + "'" + nowStr + "'" + "," + "1"
addSql += "," + "'" + timeStr + "'"
addSql += "),"
isAdd = true
}
} else {
fmt.Println("formulaFormStr is empty")
}
}
if isAdd {
addSql = strings.TrimRight(addSql, ",")
AddEdbDataCalculateBySql(addSql)
if err != nil {
fmt.Println("AddEdbDataCalculate Err:" + err.Error())
return err
}
}
return
}
// SaveAdjustEdbInfoReq 保存数据调整请求参数（请求指标服务）
type SaveAdjustEdbInfoReq struct {
AdminId int `description:"添加人id"`
AdminName string `description:"添加人名称"`
EdbInfoId int `description:"指标id"`
FromEdbInfoId int `description:"来源指标id"`
EdbName string `description:"指标名称"`
Frequency string `description:"频度"`
Unit string `description:"单位"`
ClassifyId int `description:"分类id"`
DataList []SaveAdjustEdbDataReq `description:"指标对应的数据值"`
}
// SaveAdjustEdbDataReq 保存数据调整请求的数据的参数
type SaveAdjustEdbDataReq struct {
Date string `description:"数据日期"`
Value float64 `description:"数据值"`
}
// BatchEdbInfoCalculateBatchSaveReq 批量添加 计算指标
type BatchEdbInfoCalculateBatchSaveReq struct {
CalculateId string `description:"当前请求时,单个计算的唯一标识"`
CalculateInfo EdbInfoCalculateBatchSaveReq
}
// BatchEdbInfoCalculateBatchSaveResp 批量添加 计算指标 返回数据
type BatchEdbInfoCalculateBatchSaveResp struct {
Fail []BatchEdbInfoCalculateBatchSaveFailResp `description:"添加失败的指标"`
Success []BatchEdbInfoCalculateBatchSaveSuccessResp `description:"添加成功的指标"`
}
// BatchEdbInfoCalculateBatchSaveFailResp 添加失败的指标信息
type BatchEdbInfoCalculateBatchSaveFailResp struct {
CalculateId string `description:"当前请求时,单个计算的唯一标识"`
Msg string `description:"用户提示信息"`
ErrMsg string `description:"错误信息,内部查看"`
}
// BatchEdbInfoCalculateBatchSaveSuccessResp 添加成功的指标信息
type BatchEdbInfoCalculateBatchSaveSuccessResp struct {
ClassifyId int `description:"分类id"`
CalculateId string `description:"当前请求时,单个计算的唯一标识"`
EdbInfoId int `description:"指标ID"`
UniqueCode string `description:"指标唯一编码"`
}