eta_index_lib/models/base_predict_from_calculate.go

package models

import (
	"errors"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/shopspring/decimal"
	"github.com/yidane/formula"
	"hongze/hongze_edb_lib/services"
	"hongze/hongze_edb_lib/utils"
	"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")
	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)
	//清空原有数据
	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 := services.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 {
					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, 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, edbInfo.EdbInfoId, edbInfo.Source, edbCode, formulaStr, "", edbInfoIdBytes)
	return
}

// RefreshAllPredictCalculate 刷新预测计算指标的全部数据
func RefreshAllPredictCalculate(edbInfoIdList []*EdbInfo, edbInfoId, source int, edbCode, formulaStr, startDate 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("RefreshAllCalculate,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	latestDateStr, latestValue, err = refreshAllPredictCalculate(to, edbInfoIdList, edbInfoId, source, edbCode, formulaStr, startDate, edbInfoIdBytes)

	return
}

// refreshAllPredictCalculate 刷新预测计算指标的全部数据
func refreshAllPredictCalculate(to orm.TxOrmer, edbInfoIdList []*EdbInfo, edbInfoId, source int, edbCode, formulaStr, startDate string, edbInfoIdBytes []string) (latestDateStr string, latestValue float64, err error) {
	fmt.Println("startDate:", startDate)

	var minLatestDate time.Time
	realSaveDataMap := 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 := GetPredictEdbDataListAllByStartDate(v, 1, startDate)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		dataMap := make(map[string]float64)
		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 edbInfoIndex == 0 {
				dateList = append(dateList, dv.DataTime)
			}
		}
		item := new(CalculateItems)
		item.EdbInfoId = v.EdbInfoId
		item.DataMap = dataMap
	}

	//数据处理，将日期内不全的数据做补全
	handleDateSaveDataMap(dateList, realSaveDataMap, saveDataMap, edbInfoIdList)

	formulaMap := services.CheckFormula(formulaStr)
	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
	if !minLatestDate.IsZero() {
		latestDateStr = minLatestDate.Format(utils.FormatDate)
	}

	//获取指标所有数据
	dataList := make([]*EdbData, 0)
	dataTableName := GetEdbDataTableName(source)
	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) //需要移除的日期
	for sk, sv := range saveDataMap {
		//fmt.Println(sk, sv)
		formulaStr = strings.ToUpper(formulaStr)
		formulaFormStr := ReplaceFormula(edbInfoIdList, sv, formulaMap, formulaStr, edbInfoIdBytes)
		if formulaFormStr != "" {
			//utils.FileLog.Info(fmt.Sprintf("formulaFormStr:%s", 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
			}

			// 最近实际日期的实际数据
			if latestDateStr == sk {
				latestValue, _ = 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)
		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
		}
	}
	return
}