package data_manage import ( "errors" "eta/eta_mobile/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:"公历/农历"` EmptyType int `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"` MaxEmptyType int `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"` 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:"结束日期"` 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.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 *EdbInfoFullClassify 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:"计算公式"` EmptyType int `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"` MaxEmptyType int `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"` 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:"公历/农历"` EmptyType int `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"` MaxEmptyType int `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"` } // 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, v.SubSource, 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:"指标唯一编码"` }