eta_chart_lib/models/data_manage/edb_info_calculate.go

package data_manage

import (
	"errors"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/yidane/formula"
	"hongze/hongze_chart_lib/utils"
	"strconv"
	"strings"
	"time"
)

type EdbInfoCalculateSaveReq struct {
	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:"指标对应标签"`
	}
}

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:"来源指标标签"`
	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.*,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 *EdbInfo
	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:"计算公式"`
	EdbInfoIdArr     []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `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")
	defer func() {
		if err != nil {
			o.Rollback()
		} else {
			o.Commit()
		}
	}()
	o.Begin()
	sql := `DELETE FROM edb_data_calculate WHERE edb_info_id=?`
	_, err = o.Raw(sql, edbInfoId).Exec()
	if err != nil {
		return
	}
	sql = `DELETE FROM edb_info_calculate_mapping WHERE edb_info_id=?`
	o.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     []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `description:"指标对应标签"`
	}
	MoveType      int    `description:"移动方式:1:领先(默认),2:滞后"`
	MoveFrequency 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:"移动频度:天/周/月/季/年"`
	EdbInfoIdArr  []struct {
		EdbInfoId int    `description:"指标id"`
		FromTag   string `description:"指标对应标签"`
	}
}

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
}

//指标运算
func AddCalculate(req *EdbInfoCalculateBatchSaveReq, fromEdbInfo []*EdbInfoCalculateDetail, edbCode, uniqueCode, formulaStr string, sysUserId int, sysUserRealName string) (edbInfoId int, err error) {
	o := orm.NewOrmUsingDB("data")
	o.Begin()
	defer func() {
		if err != nil {
			o.Rollback()
		} else {
			o.Commit()
		}
	}()
	if req.EdbInfoId <= 0 {
		edbInfo := new(EdbInfo)
		edbInfo.Source = utils.DATA_SOURCE_CALCULATE
		edbInfo.SourceName = "指标运算"
		edbInfo.EdbCode = edbCode
		edbInfo.EdbName = req.EdbName
		edbInfo.EdbNameSource = req.EdbName
		edbInfo.Frequency = req.Frequency
		edbInfo.Unit = req.Unit
		edbInfo.ClassifyId = req.ClassifyId
		edbInfo.SysUserId = sysUserId
		edbInfo.SysUserRealName = sysUserRealName
		edbInfo.CreateTime = time.Now()
		edbInfo.ModifyTime = time.Now()
		edbInfo.UniqueCode = uniqueCode
		edbInfo.CalculateFormula = req.CalculateFormula
		edbInfo.EdbType = 2
		newEdbInfoId, err := o.Insert(edbInfo)
		if err != nil {
			return int(newEdbInfoId), err
		}
		edbInfoId = int(newEdbInfoId)
		//处理同名指标
		{
			edbNameList, err := GetEdbInfoByName(req.EdbName)
			if err != nil {
				return edbInfoId, err
			}
			if len(edbNameList) >= 2 {
				for _, v := range edbNameList {
					edbName := v.EdbName + "(" + v.SourceName + ")"
					err = ModifyEdbInfoNameSource(edbName, v.EdbInfoId)
					if err != nil {
						return edbInfoId, err
					}
				}
			}
		}

		//calculateList := make([]*EdbInfoCalculate, 0)
		edbInfoList := make([]*EdbInfo, 0)

		for _, v := range req.EdbInfoIdArr {
			edbInfo, err := GetEdbInfoById(v.EdbInfoId)
			if err != nil {
				return edbInfoId, err
			}
			//calculateItem := new(EdbInfoCalculate)
			//calculateItem.CreateTime = time.Now()
			//calculateItem.ModifyTime = time.Now()
			//calculateItem.Sort = k + 1
			//calculateItem.EdbCode = edbCode
			//calculateItem.EdbInfoId = int(edbInfoId)
			//calculateItem.FromEdbInfoId = edbInfo.EdbInfoId
			//calculateItem.FromEdbCode = edbInfo.EdbCode
			//calculateItem.FromEdbName = edbInfo.EdbName
			//calculateItem.FromSource = edbInfo.Source
			//calculateItem.FromSourceName = edbInfo.SourceName
			//calculateItem.FromTag = v.FromTag
			//calculateList = append(calculateList, calculateItem)
			edbInfoList = append(edbInfoList, edbInfo)
		}
		//if len(calculateList) > 0 {
		//	_, err = o.InsertMulti(1, calculateList)
		//	if err != nil {
		//		return edbInfoId, err
		//	}
		//}
	} else {
		edbInfoId = req.EdbInfoId
		dataTableName := GetEdbDataTableName(utils.DATA_SOURCE_CALCULATE)
		deleteSql := ` DELETE FROM %s WHERE edb_info_id=? `
		deleteSql = fmt.Sprintf(deleteSql, dataTableName)
		_, err = o.Raw(deleteSql, req.EdbInfoId).Exec()
	}
	//计算数据
	saveDataMap := make(map[string]map[int]float64)
	for _, v := range fromEdbInfo {
		var condition string
		var pars []interface{}
		condition += " AND edb_info_id=? "
		pars = append(pars, v.FromEdbInfoId)
		dataList, err := GetEdbDataListAll(condition, pars, v.FromSource, 1)
		if err != nil {
			return edbInfoId, 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
	//		}
	//
	//		//需要存入的数据
	//		{
	//			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
	//		}
	//	}
	//}
	//if isAdd {
	//	addSql = strings.TrimRight(addSql, ",")
	//	AddEdbDataCalculateBySql(addSql)
	//	if err != nil {
	//		fmt.Println("AddEdbDataCalculate Err:" + err.Error())
	//		return
	//	}
	//}
	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"}

	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, 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
}