eta_index_lib/models/edb_data_calculate_percentile.go

package models

import (
	"encoding/json"
	"errors"
	"eta/eta_index_lib/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/shopspring/decimal"
	"reflect"
	"strconv"
	"strings"
	"time"
)

// Percentile 百分位
type Percentile struct {
}

// Add 添加
func (obj Percentile) Add(params AddCalculateBatchParams) (edbInfo *EdbInfo, err error, errMsg string) {
	req := params.Req
	fromEdbInfo := params.FromEdbInfo
	edbCode := params.EdbCode
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
			fmt.Println(reflect.TypeOf(obj).Name(), "；Add,Err:"+err.Error())
		} else {
			_ = to.Commit()
		}
	}()

	edbInfo = new(EdbInfo)
	edbInfo.Source = obj.GetSource()
	edbInfo.SourceName = obj.GetSourceName()
	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 = params.SysUserId
	edbInfo.SysUserRealName = params.SysUserRealName
	edbInfo.CreateTime = time.Now()
	edbInfo.ModifyTime = time.Now()
	edbInfo.UniqueCode = params.UniqueCode
	edbInfo.CalculateFormula = req.Formula
	edbInfo.EdbNameEn = req.EdbName
	edbInfo.UnitEn = req.Unit
	edbInfo.EdbType = obj.GetEdbType()
	newEdbInfoId, tmpErr := to.Insert(edbInfo)
	if tmpErr != nil {
		err = tmpErr
		return
	}
	edbInfo.EdbInfoId = int(newEdbInfoId)

	//关联关系
	{
		calculateMappingItem := new(EdbInfoCalculateMapping)
		calculateMappingItem.CreateTime = time.Now()
		calculateMappingItem.ModifyTime = time.Now()
		calculateMappingItem.Sort = 1
		calculateMappingItem.EdbCode = edbCode
		calculateMappingItem.EdbInfoId = edbInfo.EdbInfoId
		calculateMappingItem.FromEdbInfoId = fromEdbInfo.EdbInfoId
		calculateMappingItem.FromEdbCode = fromEdbInfo.EdbCode
		calculateMappingItem.FromEdbName = fromEdbInfo.EdbName
		calculateMappingItem.FromSource = fromEdbInfo.Source
		calculateMappingItem.FromSourceName = fromEdbInfo.SourceName
		calculateMappingItem.FromTag = ""
		calculateMappingItem.Source = edbInfo.Source
		calculateMappingItem.SourceName = edbInfo.SourceName
		calculateMappingItem.FromSubSource = fromEdbInfo.SubSource
		_, err = to.Insert(calculateMappingItem)
		if err != nil {
			return
		}
	}

	//计算数据
	err, errMsg = obj.refresh(to, edbInfo, fromEdbInfo, edbInfo.EdbCode)

	return
}

// Edit 编辑
func (obj Percentile) Edit(params EditCalculateBatchParams) (err error, errMsg string) {
	req := params.Req
	edbInfo := params.EdbInfo
	fromEdbInfo := params.FromEdbInfo

	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
			fmt.Println(reflect.TypeOf(obj).Name(), "；Edit,Err:"+err.Error())
		} else {
			_ = to.Commit()
		}
	}()
	tableName := GetEdbDataTableName(edbInfo.Source, edbInfo.SubSource)

	oldEdbInfo := *edbInfo

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

	var existCondition string
	var existPars []interface{}
	existCondition += " AND edb_info_id=? AND from_edb_info_id=? "
	existPars = append(existPars, edbInfo.EdbInfoId, req.FromEdbInfoId)

	//判断计算指标是否被更换
	count, err := GetEdbInfoCalculateCountByCondition(existCondition, existPars)
	if err != nil {
		err = errors.New("判断指标是否改变失败,Err:" + err.Error())
		return
	}
	if count > 0 { // 指标未被替换，无需处理逻辑
		// 如果相关配置更改了，那么重新计算
		if oldEdbInfo.CalculateFormula != edbInfo.CalculateFormula {
			err, errMsg = obj.refresh(to, edbInfo, fromEdbInfo, edbInfo.EdbCode)
		}
		return
	}

	//删除，计算指标关联的，基础指标的关联关系
	sql := ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id = ? `
	_, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
	if err != nil {
		return
	}

	//清空原有数据
	sql = ` DELETE FROM ` + tableName + ` WHERE edb_info_id = ? `
	_, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
	if err != nil {
		return
	}

	//关联关系
	{
		calculateMappingItem := &EdbInfoCalculateMapping{
			EdbInfoCalculateMappingId: 0,
			EdbInfoId:                 edbInfo.EdbInfoId,
			Source:                    obj.GetSource(),
			SourceName:                obj.GetSourceName(),
			EdbCode:                   edbInfo.EdbCode,
			FromEdbInfoId:             fromEdbInfo.EdbInfoId,
			FromEdbCode:               fromEdbInfo.EdbCode,
			FromEdbName:               fromEdbInfo.EdbName,
			FromSource:                fromEdbInfo.Source,
			FromSourceName:            fromEdbInfo.SourceName,
			FromTag:                   "",
			Sort:                      1,
			CreateTime:                time.Now(),
			ModifyTime:                time.Now(),
			FromSubSource:             fromEdbInfo.SubSource,
		}
		_, err = to.Insert(calculateMappingItem)
		if err != nil {
			return
		}
	}

	//计算数据
	err, errMsg = obj.refresh(to, edbInfo, fromEdbInfo, edbInfo.EdbCode)

	return
}

// Refresh 刷新
func (obj Percentile) Refresh(params RefreshParams) (err error, errMsg string) {
	calculateMapping, err := GetEdbInfoCalculateMappingDetail(params.EdbInfo.EdbInfoId)
	if err != nil {
		errMsg = "GetEdbInfoCalculateLjzzyDetail Err:" + err.Error()
		return
	}
	fromEdbInfo, err := GetEdbInfoById(calculateMapping.FromEdbInfoId)
	if err != nil {
		errMsg = "GetEdbInfoById Err:" + err.Error()
		return
	}

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

	// 计算数据
	err, errMsg = obj.refresh(to, params.EdbInfo, fromEdbInfo, params.EdbInfo.EdbCode)

	return
}

// GetSource 获取来源编码id
func (obj Percentile) GetSource() int {
	return utils.DATA_SOURCE_CALCULATE_PERCENTILE
}

// GetSourceName 获取来源名称
func (obj Percentile) GetSourceName() string {
	return utils.DATA_SOURCE_NAME_CALCULATE_PERCENTILE
}

// GetEdbType 获取指标类型
func (obj Percentile) GetEdbType() int {
	return utils.CALCULATE_EDB_TYPE
}

// PercentileConfig 百分位的配置
type PercentileConfig struct {
	CalculateValue int    `description:"时间长度期数"`
	CalculateUnit  string `description:"时间长度频度"`
	PercentType    int    `description:"百分位：0-数据区间(兼容历史数据); 1-数据个数；"`
}

// refresh 刷新
func (obj Percentile) refresh(to orm.TxOrmer, edbInfo, fromEdbInfo *EdbInfo, edbCode string) (err error, errMsg string) {
	edbInfoId := edbInfo.EdbInfoId
	dataTableName := GetEdbDataTableName(edbInfo.Source, edbInfo.SubSource)
	edbInfoIdStr := strconv.Itoa(edbInfoId)

	var percentileConfig PercentileConfig
	err = json.Unmarshal([]byte(edbInfo.CalculateFormula), &percentileConfig)
	if err != nil {
		return
	}

	// 获取百分位的指标数据
	fromDataList, err, errMsg := obj.getPercentileData(fromEdbInfo, percentileConfig.CalculateValue, percentileConfig.CalculateUnit, percentileConfig.PercentType)
	if err != nil {
		return
	}

	//获取指标所有数据
	existDataList := make([]*EdbData, 0)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
	if err != nil {
		return
	}
	existDataMap := make(map[string]string)
	removeDataTimeMap := make(map[string]int) //需要移除的日期数据
	for _, v := range existDataList {
		existDataMap[v.DataTime] = v.Value
		removeDataTimeMap[v.DataTime] = 1
	}
	needAddDateMap := make(map[time.Time]int)

	addSql := ` INSERT INTO ` + dataTableName + `(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
	var isAdd bool
	for _, tmpData := range fromDataList {
		currDateStr := tmpData.DataTime
		currTime, tmpErr := time.ParseInLocation(utils.FormatDate, currDateStr, time.Local)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		// 当前的实际值
		saveValue := decimal.NewFromFloat(tmpData.Value).Round(4).String()

		existVal, ok := existDataMap[currDateStr]
		// 如果库中已经存在该数据的话，那么就进行值的变更操作
		if ok {
			//校验待删除日期数据里面是否存在该元素，如果存在的话，那么移除该日期
			delete(removeDataTimeMap, currDateStr)

			if existVal != 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, currDateStr).Exec()
				if err != nil {
					return
				}
			}

			continue
		}

		// 库中不存在该日期的数据
		timestamp := currTime.UnixNano() / 1e6
		timeStr := fmt.Sprintf("%d", timestamp)
		if _, existOk := needAddDateMap[currTime]; !existOk {
			addSql += GetAddSql(edbInfoIdStr, edbCode, currDateStr, timeStr, saveValue)
			isAdd = true
		}
		needAddDateMap[currTime] = 1
	}

	//删除已经不存在的指标数据（由于该指标当日的数据删除了）
	{
		removeDateList := make([]string, 0)
		for dateTime := range removeDataTimeMap {
			removeDateList = append(removeDateList, dateTime)
		}
		removeNum := len(removeDateList)
		if removeNum > 0 {
			sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (`+utils.GetOrmInReplace(removeNum)+`) `, dataTableName)
			_, err = to.Raw(sql, edbInfo.EdbInfoId, removeDateList).Exec()
			if err != nil {
				fmt.Println(reflect.TypeOf(obj).Name(), " add data ;delete Err", err.Error())
				err = fmt.Errorf("删除不存在的指标数据失败,Err:" + err.Error())
				return
			}
		}
	}

	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			fmt.Println(reflect.TypeOf(obj).Name(), " add data Err", err.Error())
			return
		}
	}

	return
}

func (obj Percentile) calculate(edbInfoId int, date, edbInfoIdStr, edbCode, dataTableName, addSql string, val float64, dataMap, existDataMap map[string]string, to orm.TxOrmer) (newSql string, isAdd bool, err error) {
	newSql = addSql
	saveValue := utils.SubFloatToString(val, 4)
	//判断数据是否存在
	if existVal, ok := dataMap[date]; !ok {
		dataTime, _ := time.ParseInLocation(utils.FormatDate, date, time.Local)
		timestamp := dataTime.UnixNano() / 1e6
		timeStr := fmt.Sprintf("%d", timestamp)
		if _, existOk := existDataMap[date]; !existOk {
			newSql += GetAddSql(edbInfoIdStr, edbCode, date, timeStr, saveValue)
			isAdd = true
		}
		existDataMap[date] = date
	} else {
		if existVal != 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, date).Exec()
		}
	}
	return
}

// GetPercentileData 获取百分位图表的指标数据
func (obj Percentile) getPercentileData(fromEdbInfo *EdbInfo, calculateValue int, calculateUnit string, percentType int) (newDataList []EdbInfoSearchData, err error, errMsg string) {
	// 获取时间基准指标在时间区间内的值
	dataList := make([]*EdbInfoSearchData, 0)
	switch fromEdbInfo.EdbInfoType {
	case 0:
		//获取来源指标的数据
		dataList, err = GetEdbDataListAll(fromEdbInfo.Source, fromEdbInfo.SubSource, FindEdbDataListAllCond{
			EdbInfoId: fromEdbInfo.EdbInfoId,
		}, 1)
	case 1:
		dataList, err = GetPredictEdbDataListAllByStartDate(fromEdbInfo, 1, "")
	default:
		err = errors.New(fmt.Sprint("获取失败,指标base类型异常", fromEdbInfo.EdbInfoType))
		return
	}

	moveUnitDays, ok := utils.FrequencyDaysMap[calculateUnit]
	if !ok {
		errMsg = `错误的周期`
		err = errors.New(errMsg)
		return
	}
	calculateDay := calculateValue * moveUnitDays
	// 指标对应的所有数据

	newDataList = make([]EdbInfoSearchData, 0)

	dataMap := make(map[time.Time]float64, 0)
	for _, tmpData := range dataList {
		currDateTime, _ := time.ParseInLocation(utils.FormatDate, tmpData.DataTime, time.Local)
		dataMap[currDateTime] = tmpData.Value
	}

	//百分位：对所选指标滚动地取对应时间长度的数据值，取最大值Max，最小值Min，计算Max-Min，百分位=(现值-Min)/(Max-Min)，Max=Min时不予计算。
	if percentType == utils.PercentCalculateTypeRange {
		for i, tmpData := range dataList {
			currDateTime, _ := time.ParseInLocation(utils.FormatDate, tmpData.DataTime, time.Local)
			maxVal := tmpData.Value
			minVal := tmpData.Value
			for k := 0; k < calculateDay; k++ {
				preVal, ok2 := dataMap[currDateTime.AddDate(0, 0, -k)]
				if ok2 {
					if preVal > maxVal {
						maxVal = preVal
					}
					if preVal < minVal {
						minVal = preVal
					}
				}
			}

			if maxVal == minVal {
				continue
			}
			tmpV := (tmpData.Value - minVal) / (maxVal - minVal) * 100
			tmpV, _ = decimal.NewFromFloat(tmpV).Round(4).Float64()
			//百分位=(现值-Min)/(Max-Min)
			newDataList = append(newDataList, EdbInfoSearchData{
				EdbDataId: i,
				DataTime:  dataList[i].DataTime,
				Value:     tmpV,
			})
		}
	}
	// 百分位数据个数算法
	// 数据区间第一个和最后一个数据点的时间和数据分别为(T1,S1)(T2,S2); N=T1到T2指标数据个数, n=小于等于S2的数据个数
	// 个数百分位=(n-1)/(N-1)
	maxDay := len(dataList) // 往前找数据的边界
	if percentType == utils.PercentCalculateTypeNum {
		for i, d := range dataList {
			// T2为当前日期
			s2 := decimal.NewFromFloat(d.Value)
			t2, _ := time.ParseInLocation(utils.FormatDate, d.DataTime, time.Local)

			// 计算N和n
			var bigN, tinyN int
			for k := 0; k < maxDay; k++ {
				// 往前找(时间长度)个有数据的, N理论上只有最前面几个日期<calculateDay, 后面的N=calculateDay
				if bigN >= calculateDay {
					break
				}
				preVal, preOk := dataMap[t2.AddDate(0, 0, -k)]
				if !preOk {
					continue
				}
				bigN += 1
				if decimal.NewFromFloat(preVal).LessThanOrEqual(s2) {
					tinyN += 1
				}
			}

			// N<=1时说明计算无效
			if bigN <= 1 {
				continue
			}
			numerator := decimal.NewFromInt(int64(tinyN - 1))
			denominator := decimal.NewFromInt(int64(bigN - 1))
			// 因为是百分位所以这里是要*100, 跟之前的算法保持同步
			percentVal, _ := numerator.Div(denominator).Mul(decimal.NewFromFloat(100)).Round(4).Float64()

			// 写进数组并判断指标最大最小值
			newDataList = append(newDataList, EdbInfoSearchData{
				EdbDataId: i,
				DataTime:  dataList[i].DataTime,
				Value:     percentVal,
			})
		}
	}

	return
}