eta_index_lib/models/edb_data_calculate_nh.go

package models

import (
	"errors"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/shopspring/decimal"
	"hongze/hongze_edb_lib/utils"
	"math"
	"strconv"
	"strings"
	"time"
)

// AddCalculateNh 年化
func AddCalculateNh(req *EdbInfoCalculateBatchSaveReq, fromEdbInfo *EdbInfo, edbCode, uniqueCode string, sysUserId int, sysUserRealName string) (edbInfo *EdbInfo, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("AddCalculateNh,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	if req.EdbInfoId <= 0 {
		edbInfo = new(EdbInfo)
		edbInfo.Source = utils.DATA_SOURCE_CALCULATE_NH
		edbInfo.SourceName = utils.DATA_SOURCE_NAME_CALCULATE_NH
		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.Formula
		edbInfo.EdbType = 2
		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
			_, err = to.Insert(calculateMappingItem)
			if err != nil {
				return
			}
		}
	} else {
		edbInfo, err = GetEdbInfoById(req.EdbInfoId)
		if err != nil {
			return
		}
		dataTableName := GetEdbDataTableName(utils.DATA_SOURCE_CALCULATE_NH)
		fmt.Println("dataTableName:" + dataTableName)
		deleteSql := ` DELETE FROM %s WHERE edb_info_id=? `
		deleteSql = fmt.Sprintf(deleteSql, dataTableName)
		_, err = to.Raw(deleteSql, req.EdbInfoId).Exec()
		if err != nil {
			return
		}
	}

	//计算数据
	err = refreshAllCalculateNh(to, edbInfo.EdbInfoId, edbInfo.Source, fromEdbInfo, edbInfo.EdbCode)

	return
}

// EditCalculateNh 修改年化数据
func EditCalculateNh(edbInfo *EdbInfo, req *EdbInfoCalculateBatchEditReq, fromEdbInfo *EdbInfo) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("EditCalculateNh,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.Formula
	edbInfo.ModifyTime = time.Now()
	_, err = to.Update(edbInfo, "EdbName", "EdbNameSource", "Frequency", "Unit", "ClassifyId", "CalculateFormula", "ModifyTime")
	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 { // 指标未被替换，无需重新计算
		return
	}

	//删除，计算指标关联的，基础指标的关联关系
	sql := ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id = ? `
	_, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
	if err != nil {
		err = errors.New("删除计算指标关联关系失败,Err:" + err.Error())
		return
	}
	//清空原有数据
	tableName := GetEdbDataTableName(edbInfo.Source)
	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:                    utils.DATA_SOURCE_CALCULATE_NH,
			SourceName:                utils.DATA_SOURCE_NAME_CALCULATE_NH,
			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(),
		}
		_, err = to.Insert(calculateMappingItem)
		if err != nil {
			return
		}
	}

	//计算数据
	err = refreshAllCalculateNh(to, edbInfo.EdbInfoId, edbInfo.Source, fromEdbInfo, edbInfo.EdbCode)

	return
}

func RefreshAllCalculateNh(edbInfoId, source int, fromEdbInfo *EdbInfo, edbCode string) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("RefreshAllCalculateNh,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	// 计算数据
	err = refreshAllCalculateNh(to, edbInfoId, source, fromEdbInfo, edbCode)

	return
}

// refreshAllCalculateNh 刷新年化数据
func refreshAllCalculateNh(to orm.TxOrmer, edbInfoId, source int, fromEdbInfo *EdbInfo, edbCode string) (err error) {
	edbInfoIdStr := strconv.Itoa(edbInfoId)
	tableName := GetEdbDataTableName(utils.DATA_SOURCE_CALCULATE_NH)

	//获取年化指标所有数据
	existDataList, err := GetAllEdbDataListByTo(to, edbInfoId, source)
	if err != nil {
		return
	}
	//计算指标的map
	existDataMap := make(map[string]*EdbData, 0)
	removeDateMap := make(map[string]string)
	for _, v := range existDataList {
		existDataMap[v.DataTime] = v
		removeDateMap[v.DataTime] = ``
	}

	//计算数据
	var condition string
	var pars []interface{}
	condition += " AND edb_info_id=? "
	pars = append(pars, fromEdbInfo.EdbInfoId)

	//获取来源指标的数据
	fromDataList, err := GetEdbDataListAllByTo(to, condition, pars, fromEdbInfo.Source, 1)
	if err != nil {
		return err
	}

	// 插值法数据处理
	handleDataMap := make(map[string]float64)
	err = handleDataByLinearRegression(fromDataList, handleDataMap)
	if err != nil {
		return
	}

	lenFromDataList := len(fromDataList)
	// 如果来源指标没有数据,那么就直接返回得了
	if lenFromDataList <= 0 {
		sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? `, tableName)

		_, err = to.Raw(sql, edbInfoId).Exec()
		if err != nil {
			err = fmt.Errorf("删除年化指标数据失败,Err:" + err.Error())
			return
		}
		return
	}
	// 每年的最后一天的数据值
	yearLastValMap := make(map[int]float64)
	startDataTime, _ := time.ParseInLocation(utils.FormatDate, fromDataList[0].DataTime, time.Local)
	endDataTime, _ := time.ParseInLocation(utils.FormatDate, fromDataList[lenFromDataList-1].DataTime, time.Local)
	for i := startDataTime.Year(); i <= endDataTime.Year(); i++ {
		tmpDateStr := fmt.Sprintf("%d-12-31", i)
		if tmpVal, ok := handleDataMap[tmpDateStr]; ok {
			yearLastValMap[i] = tmpVal
		}
	}

	addSql := ` INSERT INTO ` + tableName + ` (edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
	var isAdd bool
	//来源指指标数据
	for _, v := range fromDataList {
		currDateStr := v.DataTime
		currDate, _ := time.ParseInLocation(utils.FormatDate, currDateStr, time.Local)

		perValMap := make(map[time.Time]float64)
		//前3年当日的数据
		for i := 1; i <= 3; i++ {
			tmpDateTime := currDate.AddDate(-i, 0, 0)
			if tmpVal, ok := handleDataMap[tmpDateTime.Format(utils.FormatDate)]; ok {
				perValMap[tmpDateTime] = tmpVal
			}
		}
		lenPerValMap := len(perValMap)
		// 如果数据少于2年,那么就不参与计算,结束当前循环,进入下一个循环
		if lenPerValMap < 2 {
			continue
		}

		// N年 当前值占全年比重 的值列表
		divValList := make([]decimal.Decimal, 0)
		for tmpDateTime, tmpVal := range perValMap {
			yearLastVal, ok2 := yearLastValMap[tmpDateTime.Year()]
			// 如果当年最后一天没有数据
			if !ok2 {
				continue
			}

			// 当前值占全年比重
			divVal := decimal.NewFromFloat(tmpVal).Div(decimal.NewFromFloat(yearLastVal))
			divValList = append(divValList, divVal)
		}

		lenDivValList := len(divValList)
		// 如果 N年 当前值占全年比重 的值 小于 2个,那么就不参与计算,结束当前循环,进入下一个循环
		if lenDivValList < 2 {
			continue
		}

		divValSum := decimal.NewFromFloat(0)
		for _, divVal := range divValList {
			divValSum = divValSum.Add(divVal)
		}

		// 当前计算出来的结果
		currVal, _ := decimal.NewFromFloat(v.Value).Div(divValSum.Div(decimal.NewFromInt(int64(lenDivValList)))).Round(4).Float64()

		// 判断年化指标是否存在数据
		if existData, ok := existDataMap[currDateStr]; ok {
			// 处理年化数据的值
			existValStr := existData.Value
			existValDeci, tmpErr := decimal.NewFromString(existValStr)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			existVal, _ := existValDeci.Round(4).Float64()
			// 判断年化数据的值 与 当前计算出来的结果, 如果两个数据结果不相等的话,那么就修改咯
			if existVal != currVal {
				err = ModifyEdbDataById(source, existData.EdbDataId, fmt.Sprint(currVal))
				if err != nil {
					return err
				}
			}
		} else {
			// 直接入库
			timestamp := currDate.UnixNano() / 1e6
			timestampStr := fmt.Sprintf("%d", timestamp)
			addSql += GetAddSql(edbInfoIdStr, edbCode, currDateStr, timestampStr, fmt.Sprint(currVal))
			isAdd = true
		}

		delete(removeDateMap, currDateStr)
	}

	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
	}

	// 移除不存在的日期数据
	if len(removeDateMap) > 0 {
		removeDateList := make([]string, 0) //需要移除的日期
		for k := range removeDateMap {
			removeDateList = append(removeDateList, k)
		}
		removeDateStr := strings.Join(removeDateList, `","`)
		removeDateStr = `"` + removeDateStr + `"`
		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
}

// handleDataByLinearRegression 插值法补充数据(线性方程式)
func handleDataByLinearRegression(edbInfoDataList []*EdbInfoSearchData, handleDataMap map[string]float64) (err error) {
	if len(edbInfoDataList) < 2 {
		return
	}

	var startEdbInfoData *EdbInfoSearchData
	for _, v := range edbInfoDataList {
		handleDataMap[v.DataTime] = v.Value

		// 第一个数据就给过滤了,给后面的试用
		if startEdbInfoData == nil {
			startEdbInfoData = v
			continue
		}

		// 获取两条数据之间相差的天数
		startDataTime, _ := time.ParseInLocation(utils.FormatDate, startEdbInfoData.DataTime, time.Local)
		currDataTime, _ := time.ParseInLocation(utils.FormatDate, v.DataTime, time.Local)
		betweenHour := int(currDataTime.Sub(startDataTime).Hours())
		betweenDay := betweenHour / 24

		// 如果相差一天,那么过滤
		if betweenDay <= 1 {
			startEdbInfoData = v
			continue
		}

		// 生成线性方程式
		var a, b float64
		{
			coordinateData := make([]utils.Coordinate, 0)
			tmpCoordinate1 := utils.Coordinate{
				X: 1,
				Y: startEdbInfoData.Value,
			}
			coordinateData = append(coordinateData, tmpCoordinate1)
			tmpCoordinate2 := utils.Coordinate{
				X: float64(betweenDay) + 1,
				Y: v.Value,
			}
			coordinateData = append(coordinateData, tmpCoordinate2)

			a, b = utils.GetLinearResult(coordinateData)
			if math.IsNaN(a) || math.IsNaN(b) {
				err = errors.New("线性方程公式生成失败")
				return
			}
		}

		// 生成对应的值
		{
			for i := 1; i < betweenDay; i++ {
				tmpDataTime := startDataTime.AddDate(0, 0, i)
				aDecimal := decimal.NewFromFloat(a)
				xDecimal := decimal.NewFromInt(int64(i) + 1)
				bDecimal := decimal.NewFromFloat(b)

				val, _ := aDecimal.Mul(xDecimal).Add(bDecimal).Round(4).Float64()
				handleDataMap[tmpDataTime.Format(utils.FormatDate)] = val
			}
		}

		startEdbInfoData = v
	}

	return
}