eta_index_lib/models/base_predict_from_calculate.go

package models

import (
	"encoding/json"
	"errors"
	"eta/eta_index_lib/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/shopspring/decimal"
	"github.com/yidane/formula"
	"strconv"
	"strings"
	"time"
)

// PredictEdbInfoCalculateSaveReq 预测计算（运算）指标请求参数
type PredictEdbInfoCalculateSaveReq struct {
	AdminId          int    `description:"添加人id"`
	AdminName        string `description:"添加人名称"`
	EdbName          string `description:"指标名称"`
	Frequency        string `description:"频率"`
	Unit             string `description:"单位"`
	ClassifyId       int    `description:"分类id"`
	CalculateFormula string `description:"计算公式"`
	EdbInfoIdArr     []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `description:"指标对应标签"`
	}
}

// PredictCalculateItems 预测计算（运算）指标信息
type PredictCalculateItems struct {
	EdbInfoId int
	DataMap   map[string]float64
}

// EditPredictCalculate 新增预测计算（运算）指标
func EditPredictCalculate(edbInfo *EdbInfo, calculateMappingList []*EdbInfoCalculateMapping) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			tmpErr := to.Rollback()
			errMsg := fmt.Sprintf("AddPredictCalculate Err:%s" + err.Error())
			if tmpErr != nil {
				errMsg += `;rollback失败：` + tmpErr.Error()
			}
			utils.FileLog.Info(errMsg)
			return
		}

		tmpErr := to.Commit()
		if tmpErr != nil {
			errMsg := fmt.Sprintf("AddPredictCalculate commit Err:%s" + tmpErr.Error())
			utils.FileLog.Info(errMsg)
		}
	}()

	// 修改指标信息
	_, err = to.Update(edbInfo, "EdbName", "Frequency", "Unit", "ClassifyId", "CalculateFormula", "EmptyType", "MaxEmptyType", "Extra")
	if err != nil {
		return
	}

	//删除，计算指标关联的，基础指标的关联关系
	sql := ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id = ? `
	_, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
	if err != nil {
		err = errors.New("删除计算指标关联关系失败,Err:" + err.Error())
		return
	}

	// 插入新的关系表
	if len(calculateMappingList) > 0 {
		_, err = to.InsertMulti(len(calculateMappingList), calculateMappingList)
	}

	//数据表
	tableName := GetEdbDataTableName(edbInfo.Source, edbInfo.SubSource)
	//清空原有数据
	sql = ` DELETE FROM ` + tableName + ` WHERE edb_info_id = ? `
	_, err = o.Raw(sql, edbInfo.EdbInfoId).Exec()
	if err != nil {
		return
	}

	return
}

// AddPredictCalculateData 新增预测计算（运算）指标的数据
//func AddPredictCalculateDataBak(edbInfoIdList []*EdbInfo, edbInfoId int, edbCode, formulaStr string, edbInfoIdBytes []string) (latestDateStr string, latestValue float64, err error) {
//	o := orm.NewOrm()
//	defer func() {
//		if err != nil {
//			utils.FileLog.Info(fmt.Sprintf("Calculate Err:%s" + err.Error()))
//		}
//	}()
//
//	var minLatestDate time.Time
//	oldSaveDataMap := make(map[string]map[int]float64)
//	saveDataMap := make(map[string]map[int]float64)
//	dateList := make([]string, 0)
//
//	for edbInfoIndex, v := range edbInfoIdList {
//		tmpLatestDate, tmpErr := time.ParseInLocation(utils.FormatDate, v.LatestDate, time.Local)
//		if tmpErr != nil {
//			err = errors.New(`最近的日期格式化失败;日期：` + v.LatestDate + `;err:` + tmpErr.Error())
//			return
//		}
//		// 如果不存在最小的真实数据日期，那么将当前指标的真实数据日期给赋值；
//		// 如果存在最小的真实数据日期，同时当前指标的真实数据日期 早于 最小的真实数据日期， 那么将当前指标的真实数据日期给赋值；
//		if minLatestDate.IsZero() || tmpLatestDate.Before(minLatestDate) {
//			minLatestDate = tmpLatestDate
//		}
//
//		dataList, tmpErr := GetPredictEdbDataListAll(v, 1)
//		if tmpErr != nil {
//			err = tmpErr
//			return
//		}
//		dataMap := make(map[string]float64)
//		for _, dv := range dataList {
//			// 现有实际数据
//			if val, ok := oldSaveDataMap[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
//				oldSaveDataMap[dv.DataTime] = temp
//			}
//
//			// 待处理的数据
//			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
//			}
//
//			// 以第一个指标的日期作为基准日期
//			if edbInfoIndex == 0 {
//				dateList = append(dateList, dv.DataTime)
//			}
//		}
//		item := new(PredictCalculateItems)
//		item.EdbInfoId = v.EdbInfoId
//		item.DataMap = dataMap
//	}
//
//	//数据处理，将日期内不全的数据做补全
//	handleDateSaveDataMap(dateList, oldSaveDataMap, saveDataMap, edbInfoIdList)
//
//	formulaMap := utils.CheckFormula(formulaStr)
//	addSql := ` INSERT INTO edb_data_predict_calculate (edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
//	nowStr := time.Now().Format(utils.FormatDateTime)
//	var isAdd bool
//
//	if !minLatestDate.IsZero() {
//		latestDateStr = minLatestDate.Format(utils.FormatDate)
//	}
//	for sk, sv := range saveDataMap {
//		formulaStr = strings.ToUpper(formulaStr)
//		formulaFormStr := ReplaceFormula(edbInfoIdList, sv, formulaMap, formulaStr, edbInfoIdBytes)
//		if formulaStr == "" {
//			return
//		}
//		if formulaFormStr != "" {
//			expression := formula.NewExpression(formulaFormStr)
//			calResult, tmpErr := expression.Evaluate()
//			if tmpErr != nil {
//				err = errors.New("计算失败：Err:" + tmpErr.Error() + ";formulaStr:" + formulaFormStr)
//				fmt.Println(err)
//				return
//			}
//			calVal, tmpErr := calResult.Float64()
//			if tmpErr != nil {
//				err = errors.New("计算失败：获取计算值失败 Err:" + tmpErr.Error() + ";formulaStr:" + formulaFormStr)
//				fmt.Println(err)
//				return
//			}
//
//			//需要存入的数据
//			{
//				if latestDateStr == sk {
//					nanCheck := fmt.Sprintf("%0.f", calVal)
//					// 分母为0.0的报错
//					if nanCheck == "NaN" || nanCheck == "+Inf" || nanCheck == "-Inf" {
//						err = errors.New("计算失败 分母为0：Err: formulaStr:" + formulaFormStr)
//						fmt.Println(err)
//						return
//					}
//					latestValue, _ = decimal.NewFromFloat(calVal).Truncate(4).Float64() //保留4位小数
//				}
//				dataTime, _ := time.ParseInLocation(utils.FormatDate, sk, time.Local)
//				timestamp := dataTime.UnixNano() / 1e6
//				timeStr := fmt.Sprintf("%d", timestamp)
//				addSql += "("
//				addSql += strconv.Itoa(edbInfoId) + "," + "'" + edbCode + "'" + "," + "'" + sk + "'" + "," + utils.SubFloatToString(calVal, 4) + "," + "'" + nowStr + "'" +
//					"," + "'" + nowStr + "'" + "," + "'" + timeStr + "'"
//				addSql += "),"
//				isAdd = true
//			}
//		} else {
//			fmt.Println("formulaFormStr is empty")
//		}
//	}
//	if isAdd {
//		addSql = strings.TrimRight(addSql, ",")
//		_, err = o.Raw(addSql).Exec()
//		if err != nil {
//			fmt.Println("AddPredictCalculate Err:" + err.Error())
//			//errMsg = " tx.Exec Err :" + err.Error()
//			return
//		}
//	}
//	return
//}

func AddPredictCalculateData(edbInfoIdList []*EdbInfo, edbInfoTag map[string]int, edbInfo *EdbInfo, edbCode, formulaStr string, edbInfoIdBytes []string) (latestDateStr string, latestValue float64, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("AddPredictCalculateData Calculate,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	latestDateStr, latestValue, err = refreshAllPredictCalculate(to, edbInfoIdList, edbInfoTag, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.SubSource, edbCode, formulaStr, "", edbInfoIdBytes, edbInfo.EmptyType, edbInfo.MaxEmptyType, edbInfo.Extra)
	return
}

// RefreshAllPredictCalculate 刷新预测计算指标的全部数据
func RefreshAllPredictCalculate(edbInfoIdList []*EdbInfo, edbInfoTag map[string]int, edbInfoId, source, subSource int, edbCode, formulaStr, startDate string, edbInfoIdBytes []string, emptyType, maxEmptyType int, extra string) (latestDateStr string, latestValue float64, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("RefreshAllCalculate,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	latestDateStr, latestValue, err = refreshAllPredictCalculate(to, edbInfoIdList, edbInfoTag, edbInfoId, source, subSource, edbCode, formulaStr, startDate, edbInfoIdBytes, emptyType, maxEmptyType, extra)

	return
}

// refreshAllPredictCalculate 刷新预测计算指标的全部数据
func refreshAllPredictCalculate(to orm.TxOrmer, edbInfoIdList []*EdbInfo, edbInfoTag map[string]int, edbInfoId, source, subSource int, edbCode, formulaStr, startDate string, edbInfoIdBytes []string, emptyType, maxEmptyType int, extra string) (latestDateStr string, latestValue float64, err error) {
	fmt.Println("startDate:", startDate)
	var startDateValue float64
	// 最小的结束日期 , 最晚的数据开始日期
	var newLatestDate, minLatestDate, maxStartDate time.Time
	dateList := make([]string, 0) // 第一个指标的日期数据

	realSaveDataMap := make(map[string]map[int]float64)
	saveDataMap := make(map[string]map[int]float64)
	dateMap := make(map[string]struct{}) // 最终的日期数据

	dateTagConfig := ""
	if extra != "" {
		var dateConfig CalculateEdbExtra
		err = json.Unmarshal([]byte(extra), &dateConfig)
		if err != nil {
			err = fmt.Errorf("refreshAllCalculate,extra解析失败,Err:%s", err.Error())
			return
		}
		dateTagConfig = dateConfig.DateTag
	}

	for edbInfoIndex, v := range edbInfoIdList {
		// 单独存储max、min函数里的指标的数据
		tmpNewLatestDate, tmpErr := time.ParseInLocation(utils.FormatDate, v.LatestDate, time.Local)
		if tmpErr != nil {
			err = errors.New(`最近的日期格式化失败;日期：` + v.LatestDate + `;err:` + tmpErr.Error())
			return
		}
		if newLatestDate.IsZero() || tmpNewLatestDate.Before(minLatestDate) {
			newLatestDate = tmpNewLatestDate
		}
		/*tmpStartDate, tmpErr := time.ParseInLocation(utils.FormatDate, v.StartDate, time.Local)
		if tmpErr != nil {
			err = errors.New(`最近的日期格式化失败;日期：` + v.StartDate + `;err:` + tmpErr.Error())
			return
		}
		if maxStartDate.IsZero() || maxStartDate.Before(tmpStartDate) {
			maxStartDate = tmpStartDate
		}*/

		// 获取关联指标数据
		dataList, tmpErr := GetPredictEdbDataListAllByStartDate(v, 1, startDate)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		for _, dv := range dataList { // 现有实际数据
			if val, ok := realSaveDataMap[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
				realSaveDataMap[dv.DataTime] = temp
			}

			// 待处理的数据
			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
			}

			if dateTagConfig == "all" {
				if _, ok := dateMap[dv.DataTime]; !ok {
					dateList = append(dateList, dv.DataTime)
					dateMap[dv.DataTime] = struct{}{}
				}
			} else if dateTagConfig == "" { // 默认取第一个指标的时间序列
				if edbInfoIndex == 0 {
					if _, ok := dateMap[dv.DataTime]; !ok {
						dateList = append(dateList, dv.DataTime)
						dateMap[dv.DataTime] = struct{}{}
					}
				}
			} else {
				if eId, ok := edbInfoTag[dateTagConfig]; ok {
					if v.EdbInfoId == eId {
						if _, ok1 := dateMap[dv.DataTime]; !ok1 {
							dateList = append(dateList, dv.DataTime)
							dateMap[dv.DataTime] = struct{}{}
						}
					}
				}
			}

			/*if edbInfoIndex == 0 {
				dateList = append(dateList, dv.DataTime)
				tmpDate, _ := time.ParseInLocation(utils.FormatDate, dv.DataTime, time.Local)
				if minLatestDate.IsZero() || tmpDate.After(minLatestDate) {
					minLatestDate = tmpDate
				}
				if maxStartDate.IsZero() || tmpDate.Before(maxStartDate) {
					maxStartDate = tmpDate
				}
			}*/
		}
	}
	// 处理最大日期和最小日期
	for _, v := range dateList {
		tmpDate, _ := time.ParseInLocation(utils.FormatDate, v, time.Local)
		if minLatestDate.IsZero() || tmpDate.After(minLatestDate) {
			minLatestDate = tmpDate
		}
		if maxStartDate.IsZero() || tmpDate.Before(maxStartDate) {
			maxStartDate = tmpDate
		}
	}
	//数据处理，将日期内不全的数据做填补
	HandleDateSaveDataMap(dateList, maxStartDate, minLatestDate, realSaveDataMap, saveDataMap, edbInfoIdList, emptyType)

	formulaDateSlice, formulaDateMap, err := utils.HandleFormulaJson(formulaStr, minLatestDate)
	if err != nil {
		return
	}
	addSql := ` INSERT INTO edb_data_predict_calculate (edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
	var isAdd bool

	//获取指标所有数据
	dataList := make([]*EdbData, 0)
	dataTableName := GetEdbDataTableName(source, subSource)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&dataList)
	if err != nil {
		return
	}
	dataMap := make(map[string]string)
	for _, v := range dataList {
		dataMap[v.DataTime] = v.Value
	}
	edbInfoIdStr := strconv.Itoa(edbInfoId)
	existDataMap := make(map[string]string)

	removeDateList := make([]string, 0) //需要移除的日期
	// 判断是否特殊处理max和min函数
	maxDealFlag := false
	if emptyType == 4 && maxEmptyType == 2 {
		maxDealFlag = true
	}

	for sk, sv := range saveDataMap {
		if _, ok := dateMap[sk]; !ok { //不在最终的日期序列里面不计算
			continue
		}
		// 当空值处理类型选择了不计算时，只要有一个指标在某个日期没有值(即空值)，则计算指标在该日期没有值
		if emptyType == 1 {
			if len(sv) != len(edbInfoIdList) {
				continue
			}
		}
		//fmt.Println(sk, sv)
		// 根据时间范围，选择对应的公式
		formulaMap := make(map[string]string)
		formulaStr = ""
		for _, fv := range formulaDateSlice {
			if sk < fv {
				if f, ok := formulaDateMap[fv]; ok {
					formulaStr = f
					formulaMap, err = utils.CheckFormula(formulaStr)
					if err != nil {
						err = fmt.Errorf("公式错误，请重新填写")
						return
					}
				}
				break
			}
		}
		if formulaStr == "" {
			continue
		}
		svMax := make(map[int]float64)
		if maxDealFlag {
			// 特殊处理max和min函数，如果原本的值为空，则选择空值参与运算
			if svMaxData, ok := realSaveDataMap[sk]; ok {
				svMax = svMaxData
			}
		}
		formulaStr = strings.ToUpper(formulaStr)
		formulaFormStr := ReplaceFormula(edbInfoIdList, sv, svMax, formulaMap, formulaStr, edbInfoIdBytes, maxDealFlag)
		if formulaFormStr != "" {
			//utils.FileLog.Info(fmt.Sprintf("formulaFormStr:%s", formulaFormStr))
			fmt.Println(fmt.Sprintf("date %s:formulaFormStr:%s", sk, formulaFormStr))
			expression := formula.NewExpression(formulaFormStr)
			calResult, tmpErr := expression.Evaluate()
			if tmpErr != nil {
				// 分母为0的报错
				if strings.Contains(err.Error(), "divide by zero") {
					removeDateList = append(removeDateList, sk)
					continue
				}
				err = errors.New("计算失败：Err:" + tmpErr.Error() + ";formulaStr:" + formulaFormStr)
				fmt.Println(err)
				return
			}
			calVal, tmpErr := calResult.Float64()
			if tmpErr != nil {
				err = errors.New("计算失败：获取计算值失败 Err:" + tmpErr.Error() + ";formulaStr:" + formulaFormStr)
				fmt.Println(err)
				return
			}
			nanCheck := fmt.Sprintf("%0.f", calVal)
			// 分母为0.0的报错
			if nanCheck == "NaN" || nanCheck == "+Inf" || nanCheck == "-Inf" {
				removeDateList = append(removeDateList, sk)
				continue
			}
			// 最近实际日期的实际数据
			if maxStartDate.Format(utils.FormatDate) == sk {
				startDateValue, _ = decimal.NewFromFloat(calVal).Truncate(4).Float64() //保留4位小数
			}
			saveValue := decimal.NewFromFloat(calVal).RoundCeil(4).String() //utils.SubFloatToString(calVal, 4)
			if existVal, ok := dataMap[sk]; !ok {
				dataTime, _ := time.ParseInLocation(utils.FormatDate, sk, time.Local)
				timestamp := dataTime.UnixNano() / 1e6
				timeStr := fmt.Sprintf("%d", timestamp)

				if _, existOk := existDataMap[sk]; !existOk {
					addSql += GetAddSql(edbInfoIdStr, edbCode, sk, timeStr, saveValue)
					isAdd = true
				}
				existDataMap[sk] = sk
			} else {
				existValDecimal, tmpErr := decimal.NewFromString(existVal)
				if tmpErr != nil {
					err = tmpErr
					return
				}
				existStr := existValDecimal.String()
				if existStr != saveValue {
					sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
					sql = fmt.Sprintf(sql, dataTableName)
					_, err = to.Raw(sql, saveValue, edbInfoId, sk).Exec()
					if err != nil {
						return
					}
				}
			}
		} else {
			//计算公式异常，那么就移除该指标
			removeDateList = append(removeDateList, sk)
			continue
		}
	}

	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			fmt.Println("RefreshAllCalculate add Err", err.Error())
			return
		}
	}

	if len(removeDateList) > 0 {
		removeDateStr := strings.Join(removeDateList, `","`)
		removeDateStr = `"` + removeDateStr + `"`
		//如果拼接指标变更了，那么需要删除所有的指标数据
		tableName := GetEdbDataTableName(source, subSource)
		sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (%s) `, tableName, removeDateStr)

		_, err = to.Raw(sql, edbInfoId).Exec()
		if err != nil {
			err = fmt.Errorf("删除计算失败的计算指标数据失败,Err:" + err.Error())
			return
		}
	}

	//查询预测日期
	if !newLatestDate.IsZero() {
		latestDateStr = newLatestDate.Format(utils.FormatDate)
		sql = fmt.Sprintf(` SELECT * FROM %s WHERE edb_info_id = ? and data_time <= ? ORDER BY data_time desc limit 1`, dataTableName)
		var latestDate EdbData
		e := to.Raw(sql, edbInfoId, latestDateStr).QueryRow(&latestDate)
		if e != nil {
			if e.Error() == utils.ErrNoRow() {
				//首个日期
				latestDateStr = maxStartDate.Format(utils.FormatDate)
				latestValue = startDateValue
			} else {
				err = fmt.Errorf("查询最新日期失败,Err:" + e.Error())
				return
			}

		} else {
			floatValue, _ := strconv.ParseFloat(latestDate.Value, 64)
			latestDateStr = latestDate.DataTime
			latestValue = floatValue
		}
	}

	return
}