eta_index_lib/models/edb_data_calculate_cjjx.go

package models

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

// AddCalculateCjjx 超季节性
func AddCalculateCjjx(req *EdbInfoCalculateBatchSaveReq, fromEdbInfo *EdbInfo, edbCode, uniqueCode string, sysUserId int, sysUserRealName string, formulaInt int) (edbInfo *EdbInfo, err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("AddCalculateCjjx,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	fmt.Println("req.EdbInfoId:", req.EdbInfoId)
	if req.EdbInfoId <= 0 {
		edbInfo = new(EdbInfo)
		edbInfo.Source = utils.DATA_SOURCE_CALCULATE_CJJX
		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.Formula
		edbInfo.Calendar = req.Calendar
		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_CJJX)
		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 = refreshAllCalculateCjjx(to, edbInfo.EdbInfoId, edbInfo.Source, fromEdbInfo, edbInfo.EdbCode, "", "", edbInfo.Calendar, formulaInt)

	return
}

// EditCalculateCjjx 超季节性
func EditCalculateCjjx(req *EdbInfoCalculateBatchEditReq, edbInfo, fromEdbInfo *EdbInfo, formulaInt int) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("EditCalculateCjjx,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	oldCalculateFormula := edbInfo.CalculateFormula //原先的n值
	oldCalendar := edbInfo.Calendar                 //原先的公历、农历
	edbInfo, err = GetEdbInfoById(req.EdbInfoId)
	if err != nil {
		return
	}

	//修改指标信息
	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.Calendar = req.Calendar
	edbInfo.ModifyTime = time.Now()
	_, err = to.Update(edbInfo, "EdbName", "EdbNameSource", "Frequency", "Unit", "ClassifyId", "CalculateFormula", "Calendar", "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 && oldCalculateFormula == req.Formula && oldCalendar == req.Calendar { // 指标未被替换，同时N值未修改，同时公历/农历未变更，无需重新计算
		return
	}

	// 指标被替换，或者N值未修改，那么需要重新计算数据
	//基础指标被替换了，需要删除原先的 计算指标关联的，基础指标的关联关系
	if count <= 0 {
		// 需要删除原先的 计算指标关联的，基础指标的关联关系
		sql := ` DELETE FROM edb_info_calculate_mapping 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_CJJX,
				SourceName:                "超季节性",
				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
			}
		}
	}

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

	//计算数据
	err = refreshAllCalculateCjjx(to, edbInfo.EdbInfoId, edbInfo.Source, fromEdbInfo, edbInfo.EdbCode, "", "", edbInfo.Calendar, formulaInt)

	return
}

// RefreshAllCalculateCjjx 刷新全部超季节性数据
func RefreshAllCalculateCjjx(edbInfoId, source int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, calendar string, formulaInt int) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("RefreshAllCalculateCjjx,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	// 重新计算
	err = refreshAllCalculateCjjx(to, edbInfoId, source, fromEdbInfo, edbCode, startDate, endDate, calendar, formulaInt)

	return
}

// refreshAllCalculateCjjx 刷新全部超季节性数据
func refreshAllCalculateCjjx(to orm.TxOrmer, edbInfoId, source int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, calendar string, formulaInt int) (err error) {
	if err != nil {
		return
	}
	edbInfoIdStr := strconv.Itoa(edbInfoId)
	//计算数据

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

	//if startDate != "" {
	//	condition += " AND data_time>=? "
	//	pars = append(pars, startDate)
	//}

	//if endDate != "" {
	//	condition += " AND data_time<=? "
	//	pars = append(pars, endDate)
	//}

	dataList, err := GetEdbDataListAllByTo(to, condition, pars, fromEdbInfo.Source, 0)
	if err != nil {
		return err
	}
	var dateArr []string
	dataMap := make(map[string]*EdbInfoSearchData)
	for _, v := range dataList {
		dateArr = append(dateArr, v.DataTime)
		dataMap[v.DataTime] = v
	}

	// 通过插值法补全所有数据(包含周末)
	handleDataMap := make(map[string]float64)
	err = handleDataByLinearRegression(dataList, handleDataMap)
	if err != nil {
		return
	}
	//获取指标所有数据
	existDataList := make([]*EdbData, 0)
	dataTableName := GetEdbDataTableName(source)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
	if err != nil {
		return err
	}
	existDataMap := make(map[string]string)
	removeDataTimeMap := make(map[string]int) //需要移除的日期数据
	for _, v := range existDataList {
		existDataMap[edbCode+v.DataTime] = v.Value
		removeDataTimeMap[v.DataTime] = 1
	}

	addSql := ` INSERT INTO edb_data_calculate_cjjx(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
	var isAdd bool
	//日度/周度/季度/月度
	//isCompatibility := false //是否向上下兼容35天
	//if utils.InArrayByStr([]string{"日度", "周度", "季度", "月度"}, fromEdbInfo.Frequency) {
	//	isCompatibility = true
	//}

	// 每个年份的日期数据需要平移的天数
	moveDayMap := make(map[int]int, 0) // 每个年份的春节公历
	var lastDataDay time.Time
	if len(dataList) > 0 {
		lastDataDay, _ = time.ParseInLocation(utils.FormatDate, dataList[0].DataTime, time.Local)
	}
	for _, av := range dateArr {
		// 如果遇到闰二月，如2.29，去掉该天数据
		if strings.Contains(av, "02-29") {
			continue
		}
		currentDate, tmpErr := time.Parse(utils.FormatDate, av)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		//超季节性指标计算只计算11月--次年5月，分段计算，与数据区间和N数值有关
		if currentDate.Month() > 5 && currentDate.Month() < 11 {
			continue
		}

		currentItem, ok := dataMap[av]
		// 找不到数据就退出当前循环,进入下一循环
		if !ok {
			continue
		}

		pastValueList := make([]float64, 0) // 过去几期的数据
		//当前日期
		pastValueList = append(pastValueList, currentItem.Value)

		for i := 1; i < formulaInt; i++ {
			//前几年当天公历的日期
			historyPreDate := currentDate.AddDate(-i, 0, 0)
			moveDay := 0
			if calendar == "农历" {
				if tmpMoveDay, ok := moveDayMap[historyPreDate.Year()]; !ok {
					moveDay, err = getMoveDay(lastDataDay, historyPreDate)
					if err != nil {
						return
					}
				} else {
					moveDay = tmpMoveDay
				}

				// 移动天数到对应农历 的 公历 日期
				historyPreDate = historyPreDate.AddDate(0, 0, moveDay)
			}

			historyPreDateStr := historyPreDate.Format(utils.FormatDate)
			if tmpValue, ok := handleDataMap[historyPreDateStr]; ok { //上一年同期找到
				pastValueList = append(pastValueList, tmpValue)
			}
			//if findItem, ok := dataMap[hisoryPreDateStr]; ok { //上一年同期找到
			//	pastValueList = append(pastValueList, findItem.Value)
			//} else if isCompatibility { // 如果需要兼容上下35天
			//	nextDateDay := hisoryPreDate
			//	preDateDay := hisoryPreDate
			//	for i := 0; i < 35; i++ {
			//		nextDateDayStr := nextDateDay.Format(utils.FormatDate)
			//		if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到
			//			pastValueList = append(pastValueList, findItem.Value)
			//			break
			//		} else {
			//			preDateDayStr := preDateDay.Format(utils.FormatDate)
			//			if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到
			//				pastValueList = append(pastValueList, findItem.Value)
			//				break
			//			}
			//		}
			//		nextDateDay = nextDateDay.AddDate(0, 0, 1)
			//		preDateDay = preDateDay.AddDate(0, 0, -1)
			//	}
			//}
		}

		if len(pastValueList) == formulaInt {
			delete(removeDataTimeMap, av) //将待删除的日期给移除

			val := CjjxSub(currentItem.Value, pastValueList)

			if existVal, ok := existDataMap[edbCode+av]; !ok {
				timestamp := currentDate.UnixNano() / 1e6
				timestampStr := fmt.Sprintf("%d", timestamp)
				addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val)
				isAdd = true
			} else {
				existValDecimal, err := decimal.NewFromString(existVal)
				existStr := existValDecimal.String()
				if existStr != val {
					sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
					sql = fmt.Sprintf(sql, dataTableName)
					_, err = to.Raw(sql, val, edbInfoId, av).Exec()
					if err != nil {
						return err
					}
				}
			}
		}
		existDataMap[edbCode+av] = av
	}

	//删除已经不存在的超季节性指标数据（由于该指标当日的数据删除了）
	{
		removeDateList := make([]string, 0)
		for dateTime := range removeDataTimeMap {
			removeDateList = append(removeDateList, dateTime)
		}
		removeNum := len(removeDateList)
		if removeNum > 0 {
			//如果拼接指标变更了，那么需要删除所有的指标数据
			tableName := GetEdbDataTableName(source)
			sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (`+utils.GetOrmInReplace(removeNum)+`) `, tableName)
			_, err = to.Raw(sql, edbInfoId, removeDateList).Exec()
			if err != nil {
				err = fmt.Errorf("删除不存在的超季节性指标数据失败,Err:" + err.Error())
				return
			}
		}
	}

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

// getMoveDay 获取两个日期的平移天数
func getMoveDay(lastDataDay, currentDataDay time.Time) (moveDay int, err error) {
	if lastDataDay.Month() >= 11 { //最新数据的日期如果大于等于11月份，那么用的是下一年的春节
		lastDataDay = lastDataDay.AddDate(1, 0, 0)
	}

	currentYear := lastDataDay.Year()
	currentYearCjnl := fmt.Sprintf("%d-01-01", currentYear)            //当年的春节农历
	currentYearCjgl := solarlunar.LunarToSolar(currentYearCjnl, false) //当年的春节公历
	currentYearCjglTime, tmpErr := time.ParseInLocation(utils.FormatDate, currentYearCjgl, time.Local)
	if tmpErr != nil {
		err = errors.New("当前春节公历日期转换失败:" + tmpErr.Error())
		return
	}

	//指定年的春节农历
	tmpYearCjnl := fmt.Sprintf("%d-01-01", currentDataDay.Year())
	//指定年的春节公历
	tmpYearCjgl := solarlunar.LunarToSolar(tmpYearCjnl, false)

	tmpYearCjglTime, tmpErr := time.ParseInLocation(utils.FormatDate, tmpYearCjgl, time.Local)
	if tmpErr != nil {
		err = errors.New(fmt.Sprintf("%d公历日期转换失败:%s", currentDataDay.Year(), tmpErr.Error()))
		return
	}

	// 将两个日期同步到同一年，然后计算两个日期相差的天数
	tmpCurrentYearCjglTime := currentYearCjglTime.AddDate(currentDataDay.Year()-currentYear, 0, 0)
	moveDay = utils.GetTimeSubDay(tmpYearCjglTime, tmpCurrentYearCjglTime)

	return
}

// CjjxSub 计算超季节性值
// 计算公式=现值-过去n年（包括今年）均值，n为取数个数，需大于等于1；
// 举例：A指标  2022-10-13值100，2021-10-13值120，2020-10-13值110，设置n=3，则“超季节性”指标计算值为100-（100+120+110）/3=-10。
func CjjxSub(currValue float64, pastValue []float64) (value string) {
	num := len(pastValue)
	if num == 0 {
		return
	}
	numDecimal := decimal.NewFromInt(int64(num))

	af := decimal.NewFromFloat(currValue)
	//fmt.Println(af)

	bf := decimal.NewFromFloat(pastValue[0])

	for k := 1; k < num; k++ {
		tmpVal := decimal.NewFromFloat(pastValue[k])
		bf = bf.Add(tmpVal)
	}
	val, _ := af.Sub(bf.Div(numDecimal)).Float64()
	//valStr := utils.SubFloatToString(val, 4)
	valStr := decimal.NewFromFloat(val).RoundCeil(4).String()
	return valStr
}

// 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).RoundCeil(4).Float64()
				handleDataMap[tmpDataTime.Format(utils.FormatDate)] = val
			}
		}

		startEdbInfoData = v
	}

	return
}