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" "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, 1) 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.ParseInLocation(utils.FormatDate, av, time.Local) if tmpErr != nil { err = tmpErr return } //农历的超季节性运算,只计算11月--次年5月,分段计算,与数据区间和N数值有关 if calendar == "农历" && 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 }