package data import ( "errors" "fmt" "github.com/shopspring/decimal" "github.com/yidane/formula" "hongze/hz_crm_api/models/data_manage" "hongze/hz_crm_api/utils" "math" "strconv" "strings" "time" ) 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 } // CheckFormula2 校验公式是否正常(比如说除法的分母不能为0之类的,实际上就是用预设的字段数据做一次计算) func CheckFormula2(edbInfoArr []*data_manage.EdbInfo, formulaMap map[string]string, formulaStr string, edbInfoIdBytes []string) (ok bool, err error) { valArr := make(map[int]float64) for _, v := range edbInfoArr { valArr[v.EdbInfoId] = 100 } formulaStr = strings.ToUpper(formulaStr) formulaFormStr := ReplaceFormula(edbInfoArr, valArr, formulaMap, formulaStr, edbInfoIdBytes) if formulaFormStr == "" { return } expression := formula.NewExpression(formulaFormStr) _, err = expression.Evaluate() if err != nil { } else { ok = true } return } type CalculateItems struct { EdbInfoId int DataMap map[string]float64 } func Calculate(edbInfoIdArr []*data_manage.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 := data_manage.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 != "" { 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, ",") data_manage.AddEdbDataCalculateBySql(addSql) if err != nil { fmt.Println("AddEdbDataCalculate Err:" + err.Error()) return err } } return } func ReplaceFormula(edbInfoIdArr []*data_manage.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++ } else { fmt.Println("valArr not found:", valArr, valOk) } } else { fmt.Println("formulaMap not found:", dKey, dk) } } 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++ } else { fmt.Println("valArr not found:", valArr, valOk) } } else { fmt.Println("formulaMap not found:", dKey, dk) } } 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++ } } } if dk == 9 { 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 == 10 { 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 == 11 { 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 == 12 { 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 == 13 { 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 == 14 { 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 == 15 { 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 == 16 { 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 == 17 { 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 == 18 { 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 == 19 { 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 == 20 { 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 == 21 { 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 == 22 { 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 == 23 { 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 == 24 { 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 == 25 { 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 RefreshCalculate(edbInfoIdArr []*data_manage.EdbInfo, edbInfoId int, edbCode, formulaStr, startDate, endDate string, edbInfoIdBytes []string) (err error) { defer func() { if err != nil { fmt.Println("RefreshCalculate:Err " + err.Error()) 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) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := data_manage.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 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 } count, err := data_manage.GetEdbDataCalculateByCodeAndDate(edbCode, sk) if err != nil && err.Error() != utils.ErrNoRow() { return err } if count <= 0 { //需要存入的数据 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 { calVal = utils.FixFloat(calVal, 4) err = data_manage.ModifyEdbDataCalculate(int64(edbInfoId), sk, calVal) if err != nil { return err } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") data_manage.AddEdbDataCalculateBySql(addSql) if err != nil { fmt.Println("AddEdbDataCalculate Err:" + err.Error()) return err } } return } // 处理整个数据 func handleDateSaveDataMap(dateList []string, realSaveDataMap, saveDataMap map[string]map[int]float64, edbInfoIdArr []*data_manage.EdbInfo) { for _, date := range dateList { tmpDataMap := realSaveDataMap[date] for _, edbInfo := range edbInfoIdArr { tmpEdbInfoId := edbInfo.EdbInfoId // 当前指标id // 如果该日期不存在该指标数据,那么需要找寻前后日期的数据,进行填补 if _, ok := tmpDataMap[tmpEdbInfoId]; !ok { //day := 0 //switch edbInfo.Frequency { //case "周度": // day = 7 //case "旬度": // day = 15 //case "月度": // day = 30 //case "季度": // day = 90 //case "年度": // day = 365 //} // 需求池 255 指标运算文案修改,补数据遍历区间修改(2023-3-7 09:37:23修改) day := 35 handleDateDataMap(realSaveDataMap, saveDataMap, date, tmpEdbInfoId, day) } } } } // handleDataMap 处理单个日期的数据 func handleDateDataMap(realSaveDataMap, saveDataMap map[string]map[int]float64, date string, edbInfoId, day int) { currDate, _ := time.ParseInLocation(utils.FormatDate, date, time.Local) // 后一天 nextDateDayStr := currDate.AddDate(0, 0, 1).Format(utils.FormatDate) // 前一天 preDateDayStr := currDate.AddDate(0, 0, -1).Format(utils.FormatDate) for i := 1; i <= day; i++ { // 下个日期的数据 { if i >= 1 { nextDateDayStr = currDate.AddDate(0, 0, i).Format(utils.FormatDate) } if findDataMap, hasFindDataMap := realSaveDataMap[nextDateDayStr]; hasFindDataMap { // 下一个日期有数据 if val, hasFindItem := findDataMap[edbInfoId]; hasFindItem { saveDataMap[date][edbInfoId] = val return } } } // 上个日期的数据 { if i >= 1 { preDateDayStr = currDate.AddDate(0, 0, -i).Format(utils.FormatDate) } if findDataMap, hasFindDataMap := realSaveDataMap[preDateDayStr]; hasFindDataMap { // 下一个日期有数据 if val, hasFindItem := findDataMap[edbInfoId]; hasFindItem { saveDataMap[date][edbInfoId] = val return } } } } } // handleDataByLinearRegression 插值法补充数据(线性方程式) func handleDataByLinearRegression(edbInfoDataList []*data_manage.EdbDataList, handleDataMap map[string]float64) (err error) { if len(edbInfoDataList) < 2 { return } var startEdbInfoData *data_manage.EdbDataList for _, v := range edbInfoDataList { handleDataMap[v.DataTime] = v.Value // 第一个数据就给过滤了,给后面的试用 if startEdbInfoData == nil { startEdbInfoData = v continue } // 获取两条数据之间相差的天数 startDataTime, _ := time.ParseInLocation(utils.FormatDate, startEdbInfoData.DataTime, time.Local) currDataTime, _ := time.ParseInLocation(utils.FormatDate, v.DataTime, time.Local) betweenHour := int(currDataTime.Sub(startDataTime).Hours()) betweenDay := betweenHour / 24 // 如果相差一天,那么过滤 if betweenDay <= 1 { startEdbInfoData = v continue } // 生成线性方程式 var a, b float64 { coordinateData := make([]utils.Coordinate, 0) tmpCoordinate1 := utils.Coordinate{ X: 1, Y: startEdbInfoData.Value, } coordinateData = append(coordinateData, tmpCoordinate1) tmpCoordinate2 := utils.Coordinate{ X: float64(betweenDay) + 1, Y: v.Value, } coordinateData = append(coordinateData, tmpCoordinate2) a, b = utils.GetLinearResult(coordinateData) if math.IsNaN(a) || math.IsNaN(b) { err = errors.New("线性方程公式生成失败") return } } // 生成对应的值 { for i := 1; i < betweenDay; i++ { tmpDataTime := startDataTime.AddDate(0, 0, i) aDecimal := decimal.NewFromFloat(a) xDecimal := decimal.NewFromInt(int64(i) + 1) bDecimal := decimal.NewFromFloat(b) val, _ := aDecimal.Mul(xDecimal).Add(bDecimal).Round(4).Float64() handleDataMap[tmpDataTime.Format(utils.FormatDate)] = val } } startEdbInfoData = v } return }