eta_mobile/models/data_manage/edb_info_calculate.go

package data_manage

import (
	"fmt"

	"strings"
	"time"

	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
)

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、跳过空值）"`
	Extra            string           `description:"指标的额外配置"`
	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:"计算指标名称"`
	FromEdbNameEn      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:计算指标"`
	Frequency          string    `description:"频率"`
	Unit               string    `description:"单位"`
	ClassifyId         int       `description:"分类id"`
}

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 as from_edb_name,b.edb_name_en as from_edb_name_en,b.start_date,b.end_date,b.latest_date,b.latest_value,b.edb_type, b.frequency, b.unit, b.classify_id 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、跳过空值）"`
	Extra            string           `description:"指标的额外配置"`
	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 EdbInfoBase struct {
	EdbInfoId     int    `description:"指标id"`
	EdbName       string `description:"指标名称"`
	EdbNameEn     string `description:"英文指标名称"`
	Frequency     string `description:"频度"`
	Unit          string `description:"单位"`
	UnitEn        string `description:"英文单位"`
	ClassifyId    int    `description:"分类id"`
	HaveOperaAuth bool   `description:"是否有数据权限，默认：false"`
}

type CalculateEdbInfoItem struct {
	EdbInfoId     int    `description:"指标id"`
	EdbName       string `description:"指标名称"`
	Frequency     string `description:"频度"`
	Unit          string `description:"单位"`
	ClassifyId    int    `description:"分类id"`
	CalculateId   string `description:"当前请求时,单个计算的唯一标识"`
	FromEdbInfoId int    `description:"计算来源指标id"`
}

type EdbInfoCalculateBatchSaveMultiReq struct {
	List             []CalculateEdbInfoItem
	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:"移动频度:天/周/月/季/年"`
	Extra            string           `description:"指标的额外配置"`
	Calendar         string           `description:"公历/农历"`
}

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:"移动频度:天/周/月/季/年"`
	Extra            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:"公历/农历"`
	Extra         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、跳过空值）"`
	Extra            string           `description:"指标的额外配置"`
}

// EdbInfoCalculateBatchEditReqByEdbLib 编辑计算指标的请求参数
type EdbInfoCalculateBatchEditReqByEdbLib struct {
	EdbName       string `description:"指标名称"`
	Frequency     string `description:"频度"`
	Unit          string `description:"单位"`
	ClassifyId    int    `description:"分类id"`
	AdminId       int    `description:"操作人id"`
	AdminName     string `description:"操作人姓名"`
	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:"数据列"`
	Extra         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
}

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
}

// 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 {
	AdminId          int                     `description:"添加人id"`
	AdminName        string                  `description:"添加人名称"`
	EdbList          []*CalculateEdbInfoItem //需要批量计算的指标列表
	Formula          string                  `description:"N值/移动天数"`
	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、跳过空值）"`
	Extra            string                  `description:"指标的额外配置"`
}

// 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:"指标唯一编码"`
}

// CalculateMultiSearchReq 批量计算模块下的指标搜索
type CalculateMultiChoiceReq struct {
	ClassifyIds string `description:"分类ID, 用英文逗号拼接"`
	Frequency   string `description:"频度"`
	SysUserIds  string `description:"创建人ID，用英文逗号拼接"`
	Keyword     string `description:"关键字"`
	SelectAll   bool   `description:"是否全选"`
	EdbInfoIds  string `description:"指标Id, 用英文逗号拼接"`
}

type CalculateMultiChoiceResp struct {
	SearchItem []EdbInfoBase `description:"查询结果"`
}

type CalculateMultiEdbSearchResp struct {
	SearchItem []CalculateMultiEdbSearchItem `description:"查询结果"`
	Paging     *paging.PagingItem
}
type CalculateMultiEdbSearchItem struct {
	EdbInfoId       int    `description:"指标id"`
	EdbName         string `description:"指标名称"`
	EdbNameEn       string `description:"英文指标名称"`
	Frequency       string `description:"频度"`
	Unit            string `description:"单位"`
	UnitEn          string `description:"英文单位"`
	ClassifyId      int    `description:"分类id"`
	SysUserId       int
	SysUserRealName string
	EndValue        float64 `description:"数据的最新值（预测日期的最新值）"`
	EndDate         string  `description:"终止日期"`
	HaveOperaAuth   bool    `description:"是否有数据权限，默认：false"`
}