eta_api/models/data_manage/edb_info_calculate.go

package data_manage

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

type EdbInfoCalculateSaveReq struct {
	EdbName          string           `description:"指标名称"`
	Frequency        string           `description:"频率"`
	Unit             string           `description:"单位"`
	ClassifyId       int              `description:"分类id"`
	CalculateFormula string           `description:"计算公式"`
	Calendar         string           `description:"公历/农历"`
	EmptyType        int              `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"`
	MaxEmptyType     int              `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"`
	EdbInfoIdArr     []EdbInfoFromTag `description:"指标信息"`
}

// EdbInfoFromTag 计算指标的关联指标
type EdbInfoFromTag struct {
	EdbInfoId int    `description:"指标id"`
	FromTag   string `description:"指标对应标签"`
	MoveValue int    `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:"来源指标标签"`
	MoveValue          int       `description:"移动的值，小于0是提前，0是不变，大于0是滞后"`
	Sort               int       `description:"计算指标名称排序"`
	CreateTime         time.Time `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	StartDate          string    `description:"开始日期"`
	EndDate            string    `description:"结束日期"`
	LatestDate         string    `description:"实际的结束日期"`
	LatestValue        float64   `description:"最近实际数据的值"`
	EndValue           float64   `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.edb_info_calculate_mapping_id,a.edb_info_id,a.source,a.source_name,a.edb_code,a.from_edb_info_id,a.from_edb_code,a.from_source,a.from_source_name,a.sort,a.create_time,a.modify_time,a.from_tag,a.move_value,b.edb_name_source as from_edb_name,b.start_date,b.end_date,b.latest_date,b.latest_value,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 *EdbInfoFullClassify
	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:"计算公式"`
	EmptyType        int              `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"`
	MaxEmptyType     int              `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"`
	EdbInfoIdArr     []EdbInfoFromTag `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")
	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
}

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     []EdbInfoFromTag `description:"指标信息"`
	MoveType         int              `description:"移动方式:1:领先(默认),2:滞后"`
	MoveFrequency    string           `description:"移动频度:天/周/月/季/年"`
	Calendar         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:"移动频度:天/周/月/季/年"`
	Calendar      string `description:"公历/农历"`
	EdbInfoIdArr  []EdbInfoFromTag
}

// EdbInfoCalculateBatchSaveReqByEdbLib edb指标库的请求逻辑
type EdbInfoCalculateBatchSaveReqByEdbLib 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     []EdbInfoFromTag `description:"指标信息"`
	MoveType         int              `description:"移动方式:1:领先(默认),2:滞后"`
	MoveFrequency    string           `description:"移动频度:天/周/月/季/年"`
	Calendar         string           `description:"公历/农历"`
	Data             interface{}      `description:"数据列"`
	EmptyType        int              `description:"空值处理类型（0查找前后35天，1不计算，2前值填充，3后值填充，4等于0）"`
	MaxEmptyType     int              `description:"MAX、MIN公式空值处理类型（1、等于0；2、跳过空值）"`
}

// EdbInfoCalculateBatchEditReqByEdbLib 编辑计算指标的请求参数
type EdbInfoCalculateBatchEditReqByEdbLib 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:"移动频度:天/周/月/季/年"`
	Calendar      string `description:"公历/农历"`
	EdbInfoIdArr  []EdbInfoFromTag
	Data          interface{} `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
}

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

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, v.SubSource, 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
}

// SaveAdjustEdbInfoReq 保存数据调整请求参数（请求指标服务）
type SaveAdjustEdbInfoReq struct {
	AdminId       int                    `description:"添加人id"`
	AdminName     string                 `description:"添加人名称"`
	EdbInfoId     int                    `description:"指标id"`
	FromEdbInfoId int                    `description:"来源指标id"`
	EdbName       string                 `description:"指标名称"`
	Frequency     string                 `description:"频度"`
	Unit          string                 `description:"单位"`
	ClassifyId    int                    `description:"分类id"`
	DataList      []SaveAdjustEdbDataReq `description:"指标对应的数据值"`
}

// SaveAdjustEdbDataReq 保存数据调整请求的数据的参数
type SaveAdjustEdbDataReq struct {
	Date  string  `description:"数据日期"`
	Value float64 `description:"数据值"`
}

// BatchEdbInfoCalculateBatchSaveReq 批量添加 计算指标
type BatchEdbInfoCalculateBatchSaveReq struct {
	CalculateId   string `description:"当前请求时,单个计算的唯一标识"`
	CalculateInfo EdbInfoCalculateBatchSaveReq
}

// BatchEdbInfoCalculateBatchSaveResp 批量添加 计算指标 返回数据
type BatchEdbInfoCalculateBatchSaveResp struct {
	Fail    []BatchEdbInfoCalculateBatchSaveFailResp    `description:"添加失败的指标"`
	Success []BatchEdbInfoCalculateBatchSaveSuccessResp `description:"添加成功的指标"`
}

// BatchEdbInfoCalculateBatchSaveFailResp 添加失败的指标信息
type BatchEdbInfoCalculateBatchSaveFailResp struct {
	CalculateId string `description:"当前请求时,单个计算的唯一标识"`
	Msg         string `description:"用户提示信息"`
	ErrMsg      string `description:"错误信息,内部查看"`
}

// BatchEdbInfoCalculateBatchSaveSuccessResp 添加成功的指标信息
type BatchEdbInfoCalculateBatchSaveSuccessResp struct {
	ClassifyId  int    `description:"分类id"`
	CalculateId string `description:"当前请求时,单个计算的唯一标识"`
	EdbInfoId   int    `description:"指标ID"`
	UniqueCode  string `description:"指标唯一编码"`
}