eta_index_lib/models/base_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"
)

// EdbInfoCalculateSaveReq 计算（运算）指标请求参数
type EdbInfoCalculateSaveReq 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"`
	CalculateFormula string `description:"计算公式"`
	EdbInfoIdArr     []EdbInfoFromTag
}

// EdbInfoFromTag 计算指标的关联指标
type EdbInfoFromTag struct {
	EdbInfoId int    `description:"指标id"`
	FromTag   string `description:"指标对应标签"`
}

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

// AddCalculate 新增计算（运算）指标
func AddCalculate(edbInfoIdArr []*EdbInfo, edbInfoId int, edbCode, formulaStr string, edbInfoIdBytes []string) (err error) {
	o := orm.NewOrm()
	defer func() {
		if err != nil {
			utils.FileLog.Info(fmt.Sprintf("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 := services.CheckFormula(formulaStr)
	addSql := ` INSERT INTO edb_data_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
	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 + "'" + "," + "'" + 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("AddEdbDataCalculate Err:" + err.Error())
			//errMsg = " tx.Exec Err :" + err.Error()
			return
		}
	}
	return
}

// AddCalculateInfo 新增计算（运算）指标
func AddCalculateInfo(req EdbInfoCalculateSaveReq, calculateMappingList []*EdbInfoCalculateMapping, edbInfoList []*EdbInfo, edbCode, uniqueCode string, edbInfoIdBytes []string) (edbInfo *EdbInfo, err error) {

	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("AddCalculateHcz,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	edbInfo = &EdbInfo{
		EdbInfoId:        0,
		SourceName:       "指标运算",
		Source:           utils.DATA_SOURCE_CALCULATE,
		EdbCode:          edbCode,
		EdbName:          req.EdbName,
		EdbNameSource:    req.EdbName,
		Frequency:        req.Frequency,
		Unit:             req.Unit,
		StartDate:        "",
		EndDate:          "",
		ClassifyId:       req.ClassifyId,
		SysUserId:        req.AdminId,
		SysUserRealName:  req.AdminName,
		UniqueCode:       uniqueCode,
		CreateTime:       time.Now(),
		ModifyTime:       time.Now(),
		MinValue:         0,
		MaxValue:         0,
		CalculateFormula: req.CalculateFormula,
		EdbType:          2,
		Sort:             0,
		MoveType:         0,
		MoveFrequency:    "",
		NoUpdate:         0,
		ServerUrl:        "",
		EdbInfoType:      0,
		EdbNameEn:        "",
		UnitEn:           "",
		LatestDate:       "",
		LatestValue:      0,
		ChartImage:       "",
	}
	lastId, err := to.Insert(edbInfo)
	if err != nil {
		return
	}
	edbInfo.EdbInfoId = int(lastId)
	//关联关系表
	{
		//处理关联指标
		for _, v := range calculateMappingList {
			v.EdbCode = edbCode
			v.EdbInfoId = edbInfo.EdbInfoId
		}
		if len(calculateMappingList) > 0 {
			_, err = to.InsertMulti(len(calculateMappingList), calculateMappingList)
			if err != nil {
				return
			}
		}
	}

	//计算数据
	err = refreshAllCalculate(to, edbInfoList, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.EdbCode, edbInfo.CalculateFormula, "", "", edbInfoIdBytes)

	return
}

// EditCalculateInfo 编辑计算（运算）指标
func EditCalculateInfo(edbInfo *EdbInfo, req EdbInfoCalculateSaveReq, formulaMap map[string]string, edbInfoIdBytes []string, needCalculate bool) (err error, errMsg string) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("AddCalculateHcz,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	// 修改指标信息
	edbInfo.EdbName = req.EdbName
	edbInfo.EdbNameSource = req.EdbName
	edbInfo.Frequency = req.Frequency
	edbInfo.Unit = req.Unit
	edbInfo.ClassifyId = req.ClassifyId
	edbInfo.CalculateFormula = req.CalculateFormula
	edbInfo.ModifyTime = time.Now()
	_, err = to.Update(edbInfo, "EdbName", "EdbNameSource", "Frequency", "Unit", "ClassifyId", "CalculateFormula", "ModifyTime")
	if err != nil {
		return
	}

	if needCalculate {
		// 清除计算数据
		sql := `DELETE FROM edb_data_calculate WHERE edb_info_id=?`
		_, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
		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 {
			return
		}
		edbInfoList := make([]*EdbInfo, 0)

		calculateMappingList := make([]*EdbInfoCalculateMapping, 0)
		for k, v := range req.EdbInfoIdArr {
			fromEdbInfo, tmpErr := GetEdbInfoById(v.EdbInfoId)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			edbInfoList = append(edbInfoList, fromEdbInfo)

			//关联关系表
			{
				calculateMappingItem := &EdbInfoCalculateMapping{
					EdbInfoCalculateMappingId: 0,
					EdbInfoId:                 req.EdbInfoId,
					Source:                    edbInfo.Source,
					SourceName:                edbInfo.SourceName,
					EdbCode:                   edbInfo.EdbCode,
					FromEdbInfoId:             fromEdbInfo.EdbInfoId,
					FromEdbCode:               fromEdbInfo.EdbCode,
					FromEdbName:               fromEdbInfo.EdbName,
					FromSource:                fromEdbInfo.Source,
					FromSourceName:            fromEdbInfo.SourceName,
					FromTag:                   v.FromTag,
					Sort:                      k + 1,
					CreateTime:                time.Now(),
					ModifyTime:                time.Now(),
				}
				calculateMappingList = append(calculateMappingList, calculateMappingItem)
			}
		}

		//预先计算，判断公式是否正常
		ok, _ := CheckFormula2(edbInfoList, formulaMap, req.CalculateFormula, edbInfoIdBytes)
		if !ok {
			errMsg = "生成计算指标失败，请使用正确的计算公式"
			err = errors.New(errMsg)
			return
		}

		//关联关系表
		{
			if len(calculateMappingList) > 0 {
				_, err = to.InsertMulti(len(calculateMappingList), calculateMappingList)
				if err != nil {
					return
				}
			}
		}

		//计算数据
		err = refreshAllCalculate(to, edbInfoList, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.EdbCode, edbInfo.CalculateFormula, "", "", edbInfoIdBytes)

	}

	return
}

// GetCalculateEdbInfo 获取计算指标的关联指标id
func GetCalculateEdbInfo(edbInfoId int) (from_edb_info_id string, err error) {
	o := orm.NewOrm()
	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
}

// DeleteCalculateEdbInfo 删除指标信息
func DeleteCalculateEdbInfo(edbInfoId int) (err error) {
	o := orm.NewOrm()
	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
}

// RefreshAllCalculate 刷新全部数据
func RefreshAllCalculate(edbInfoIdArr []*EdbInfo, edbInfoId, source int, edbCode, formulaStr, startDate, endDate string, edbInfoIdBytes []string) (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()
		}
	}()
	fmt.Println(startDate, endDate)

	//计算数据
	err = refreshAllCalculate(to, edbInfoIdArr, edbInfoId, source, edbCode, formulaStr, startDate, endDate, edbInfoIdBytes)

	return
}

// refreshAllCalculate 刷新全部数据
func refreshAllCalculate(to orm.TxOrmer, edbInfoIdArr []*EdbInfo, edbInfoId, source int, edbCode, formulaStr, startDate, endDate string, edbInfoIdBytes []string) (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)
		//}
		//fmt.Println("v.Source:", v.Source)
		dataList, err := GetEdbDataListAllByTo(to, 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 := services.CheckFormula(formulaStr)
	addSql := ` INSERT INTO edb_data_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)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&dataList)
	if err != nil {
		return err
	}
	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(edbInfoIdArr, sv, formulaMap, formulaStr, edbInfoIdBytes)
		if formulaFormStr != "" {
			utils.FileLog.Info(fmt.Sprintf("formulaFormStr:%s", formulaFormStr))
			expression := formula.NewExpression(formulaFormStr)
			calResult, err := expression.Evaluate()
			if err != nil {
				// 分母为0的报错
				if strings.Contains(err.Error(), "divide by zero") {
					removeDateList = append(removeDateList, sk)
					continue
				}
				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
			}

			saveValue := decimal.NewFromFloat(calVal).RoundCeil(4).String() //utils.SubFloatToString(calVal, 4)
			if existVal, ok := dataMap[sk]; !ok {
				dataTime, _ := time.Parse(utils.FormatDate, sk)
				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, err := decimal.NewFromString(existVal)
				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 err
					}
				}
			}
		} 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
}

// ReplaceFormula 替换计算方式
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 {
		var isReplace bool
		formulaStr, isReplace = GetFormulaReplace(dk, dv.EdbInfoId, formulaStr, edbInfoIdBytes, formulaMap, valArr)
		if isReplace {
			replaceCount++
		}
	}
	for k, v := range funMap {
		formulaStr = strings.Replace(formulaStr, v, k, -1)
	}
	if replaceCount == len(formulaMap) {
		return formulaStr
	} else {
		return ""
	}
}

func GetFormulaReplace(index, edbInfoId int, formulaStr string, edbInfoIdBytes []string, formulaMap map[string]string, valArr map[int]float64) (formulaResult string, isReplace bool) {
	formulaResult = formulaStr
	dKey := edbInfoIdBytes[index]
	if _, ok := formulaMap[dKey]; ok { //公式中存在
		if val, valOk := valArr[edbInfoId]; valOk { //值存在
			dvStr := fmt.Sprintf("%v", val)
			formulaResult = strings.Replace(formulaStr, dKey, dvStr, -1)
			isReplace = true
		}
	}
	return
}

// GetFormulaMap 获取计算公式的map
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
}

// EdbInfoCalculateBatchSaveReq 添加计算指标的请求参数
type EdbInfoCalculateBatchSaveReq 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     []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `description:"指标对应标签"`
	}
	MoveType      int    `description:"移动方式:1:领先(默认),2:滞后"`
	MoveFrequency string `description:"移动频度:天/周/月/季/年"`
}

// EdbInfoCalculateBatchEditReq 编辑计算指标的请求参数
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:"移动频度:天/周/月/季/年"`
	EdbInfoIdArr  []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `description:"指标对应标签"`
	}
}

// CheckFormula2 校验公式是否正常（比如说除法的分母不能为0之类的，实际上就是用预设的字段数据做一次计算）
func CheckFormula2(edbInfoArr []*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
}