package excel import ( "encoding/json" "errors" "eta/eta_mobile/models/data_manage" "eta/eta_mobile/models/data_manage/excel/request" "eta/eta_mobile/services/data" "eta/eta_mobile/utils" "fmt" "github.com/shopspring/decimal" "github.com/yidane/formula" "sort" "strconv" "strings" "time" ) // BaseCalculate // @Description: 指标数据计算请求 type BaseCalculate struct { DataList []*data_manage.EdbDataList Frequency string `description:"需要转换的频度"` Formula interface{} Calendar string `description:"公历/农历"` MoveType int `description:"移动方式:1:领先(默认),2:滞后"` MoveFrequency string `description:"移动频度"` FromFrequency string `description:"来源的频度"` Source int `description:"1：累计值转月；2：累计值转季；3：同比值；4：同差值；5：N数值移动平均数计算；6：环比值；7：环差值；8：升频；9：降频；10：时间移位；11：超季节性；12：年化；13：累计值；14：累计值年初至今；15：指数修匀；16：日均值"` } // Cell // @Description: 单元格位置 type Cell struct { Column int `description:"行"` Row int `description:"列"` CellInfo request.MixedTableCellDataReq `description:"对应的单元格信息"` } // GetMixedTableCellData 获取混合表格数据 func GetMixedTableCellData(mixedTableReq request.MixedTableReq, lang string) (newMixedTableCellDataList [][]request.MixedTableCellDataReq, err error, errMsg string) { cellRelationConf := mixedTableReq.CellRelation config := mixedTableReq.Data // 单元格关系配置x信息 cellRelationConfMap := make(map[string]request.CellRelationConf) cellRelationConfList := make([]request.CellRelationConf, 0) if cellRelationConf != `` { err = json.Unmarshal([]byte(cellRelationConf), &cellRelationConfList) if err != nil { return } for _, v := range cellRelationConfList { cellRelationConfMap[v.Key] = v } } // 找出所有的关联指标id config, edbInfoIdList, _, err, errMsg := handleConfig(config) if err != nil { return } // 查询所有关联的指标信息 edbInfoList, err := data_manage.GetEdbInfoByIdList(edbInfoIdList) if err != nil { return } // 指标信息map edbInfoMap := make(map[int]*data_manage.EdbInfo) // 日度指标数据map edbDayDataListMap := make(map[int]map[string]float64) // 月度指标数据map edbMonthDataListMap := make(map[int]map[string]string) // 日度指标数据map edbDataListMap := make(map[int][]*data_manage.EdbDataList) for _, edbInfo := range edbInfoList { edbInfoMap[edbInfo.EdbInfoId] = edbInfo dataList := make([]*data_manage.EdbDataList, 0) switch edbInfo.EdbInfoType { case 0: dataList, _ = data_manage.GetEdbDataList(edbInfo.Source, edbInfo.SubSource, edbInfo.EdbInfoId, ``, ``) case 1: _, dataList, _, _, _, _ = data.GetPredictDataListByPredictEdbInfoId(edbInfo.EdbInfoId, ``, ``, false) default: err = errors.New(fmt.Sprint("获取失败,指标类型异常", edbInfo.EdbInfoType)) } dateValMap := make(map[string]float64) monthDateMap := make(map[string]string) for _, tmpData := range dataList { // 日度数据 dateValMap[tmpData.DataTime] = tmpData.Value // 月度数据（取该月份的第一个数据） yearMonth := strings.Join(strings.Split(tmpData.DataTime, "-")[0:2], "-") if _, ok := monthDateMap[yearMonth]; !ok { // 存最早的时间 monthDateMap[yearMonth] = tmpData.DataTime } } edbDayDataListMap[edbInfo.EdbInfoId] = dateValMap edbMonthDataListMap[edbInfo.EdbInfoId] = monthDateMap edbDataListMap[edbInfo.EdbInfoId] = dataList } // 单元格实际绑定的信息map cellDataRelationMap := make(map[string]request.MixedTableCellDataReq, 0) // 处理指定指标的日期 for k, row := range config { for i, cell := range row { // 单元格是日期类型，且是日导入指标日期（指标库的最新日期） if cell.DataType == request.DateDT && cell.DataTimeType == request.EdbDateDT { // 指标id是在配置里面 var edbDateConfig request.EdbDateConf err = json.Unmarshal([]byte(cell.Value), &edbDateConfig) if err != nil { return } if dataList, ok := edbDataListMap[edbDateConfig.EdbInfoId]; ok { // todo 获取配置信息，根据配置信息进行日期变换, 是否需要更新当前记录，将历史记录逐渐转换成新的记录 var newDate string newDate, err = GetEdbDateByMoveForward(config[k][i].Value, dataList) if err != nil { return } newDate, err = HandleMixTableDateChange(newDate, config[k][i].Value) if err != nil { return } cell.ShowValue = newDate cell.DataTime = newDate config[k][i] = cell } } row[i] = cell cellDataRelationMap[cell.Uid] = cell } config[k] = row } // 指标计算的结果map edbSourceDataMap := make(map[string]data.BaseCalculateDataResp) // 单元格对应的key与他的值（只处理数据类型） cellKeyVal := make(map[string]float64) // 基础计算单元格的位置信息 calculateCellMap := make(map[string]Cell) calculateChainList := make([]string, 0) dateCalculateList := make([]string, 0) showStyleList := make([]string, 0) // 处理单元格中的数据类型（除去基础计算，因为这个是依赖于其他） for k, row := range config { for i, cell := range row { switch cell.DataType { case request.EdbDT: // 指标类型 if cell.Value == `` { if edbInfo, ok := edbInfoMap[cell.EdbInfoId]; ok { cell.ShowValue = edbInfo.EdbName } } else { cell.ShowValue = cell.Value } case request.InsertDataDT, request.PopInsertDataDT: // 数据类型 // 数值先清空 cell.ShowValue = `` //cell.Value = `` // 日期关系配置不存在，则默认最新数据 if relationConf, ok := cellRelationConfMap[cell.Uid]; ok { //表示表格日期 if relationConf.RelationDate.Key == `` { // 日期关系配置未绑定 continue } // 配置 relationCell, ok := cellDataRelationMap[relationConf.RelationDate.Key] if !ok { // 找不到对应日期的单元格 continue } // 确实是找到了这个关联日期的单元格，那么通过日期重新获取数据值 tmpDateValMap := make(map[string]float64) // 日度数据 if dateValMap, ok := edbDayDataListMap[cell.EdbInfoId]; ok { tmpDateValMap = dateValMap } // todo 根据配置进行日期变换 relationDate := relationCell.DataTime if strings.Contains(cell.Value, "{") { relationDate, err = HandleMixTableDateChange(relationDate, cell.Value) if err != nil { return } } else { cell.Value = "" } if val, ok2 := tmpDateValMap[relationDate]; ok2 { //cell.ShowValue = fmt.Sprint(val) cellKeyVal[cell.Uid] = val cell.ShowValue = utils.FormatMixTableDataShowValue(val) } } else { // 如果不是取得一个关联的日期，那么就是指定日期 // 如果没有指定日期，则默认最新数据 if cell.DataTime == `` { // 指标的最新日期 if dateValList, ok := edbDataListMap[cell.EdbInfoId]; ok { tmpLenData := len(dateValList) if tmpLenData > 0 { //做期数前移动和日期变换 if !strings.Contains(cell.Value, "{") { cell.Value = "" } var newDate string newDate, err = GetEdbDateByMoveForward(cell.Value, dateValList) if err != nil { return } newDate, err = HandleMixTableDateChange(newDate, cell.Value) if err != nil { return } var finalVal string for _, v := range dateValList { if v.DataTime == newDate { finalVal = utils.FormatMixTableDataShowValue(v.Value) cellKeyVal[cell.Uid] = v.Value break } } cell.ShowValue = finalVal } } } else { tmpDateList := strings.Split(cell.DataTime, "-") tmpDateValMap := make(map[string]float64) var newDate string if len(tmpDateList) == 2 { //月度数据 if dateMap, ok1 := edbMonthDataListMap[cell.EdbInfoId]; ok1 { if d, ok2 := dateMap[cell.DataTime]; ok2 { newDate = d } } } else { // 日度数据 newDate = cell.DataTime } // 日期变换后才能确定最后的时间 //做期数前移动和日期变换 if !strings.Contains(cell.Value, "{") { cell.Value = "" } newDate, err = HandleMixTableDateChange(newDate, cell.Value) if err != nil { return } if dateValMap, ok3 := edbDayDataListMap[cell.EdbInfoId]; ok3 { tmpDateValMap = dateValMap if val, ok2 := tmpDateValMap[cell.DataTime]; ok2 { //cell.ShowValue = fmt.Sprint(val) cellKeyVal[cell.Uid] = val cell.ShowValue = utils.FormatMixTableDataShowValue(val) } } } } case request.CustomTextDT: //自定义文本 if cell.Value == `` { continue } // 处理看下能否转成float，如果可以的话，说明这个也是可以参与计算的 tmpDeci, tmpErr := decimal.NewFromString(cell.Value) if tmpErr == nil { tmpVal, _ := tmpDeci.Float64() cellKeyVal[cell.Uid] = tmpVal } case request.FormulateCalculateDataDT: // 公式计算（A+B这种） calculateChainList = append(calculateChainList, cell.Uid) case request.InsertEdbCalculateDataDT: // 插入指标系统计算公式生成的值 // 处理value的值 if !strings.Contains(cell.Value, "EdbInfoId") && cell.EdbInfoId > 0 { cell.Value, _ = fixCalculateValueConfig(cell.Value, cell.EdbInfoId) row[i] = cell } // 日期 var cellDateTime string // 日期关系配置不存在，则默认最新数据 // 从绑定的单元格中获取数据的日期 if relationConf, ok := cellRelationConfMap[cell.Uid]; ok { if relationConf.RelationDate.Key == `` { // 日期关系配置未绑定 continue } // 配置 relationCell, ok := cellDataRelationMap[relationConf.RelationDate.Key] if !ok { // 找不到对应日期的单元格 continue } cellDateTime = relationCell.DataTime } var tmpDataMap map[string]float64 key := utils.MD5(cell.Value) respItemData, ok := edbSourceDataMap[key] if !ok { // 对应的配置值 var tmpConfig request.CalculateConf err = json.Unmarshal([]byte(cell.Value), &tmpConfig) if err != nil { return } tmpDataList, ok := edbDataListMap[tmpConfig.EdbInfoId] if !ok { continue } edbInfo, ok := edbInfoMap[tmpConfig.EdbInfoId] if !ok { continue } req2 := &BaseCalculate{ DataList: tmpDataList, Frequency: tmpConfig.Frequency, Formula: tmpConfig.Formula, Calendar: tmpConfig.Calendar, MoveType: tmpConfig.MoveType, MoveFrequency: tmpConfig.MoveFrequency, FromFrequency: edbInfo.Frequency, Source: tmpConfig.Source, } // 调用指标库去更新 reqJson, tmpErr := json.Marshal(req2) if tmpErr != nil { utils.FileLog.Error(fmt.Sprintf("计算失败1，配置信息;%s;错误原因：%s", cell.Value, tmpErr.Error())) err = tmpErr return } respItem, tmpErr := data.BaseCalculate(string(reqJson), lang) if tmpErr != nil { utils.FileLog.Error(fmt.Sprintf("计算失败2，配置信息;%s;错误原因：%s", cell.Value, tmpErr.Error())) err = tmpErr return } if respItem.Ret != 200 { utils.FileLog.Error(fmt.Sprintf("计算失败3，配置信息;%s;原因：%s;错误原因：%s", cell.Value, respItem.Msg, respItem.ErrMsg)) continue } //tmpDataMap = respItem.Data.DataMap // 计算结果存一份，万一存在重复的计算方式，那么省的重新计算一下 edbSourceDataMap[key] = respItem.Data respItemData = respItem.Data } lenDataList := len(respItemData.DateList) tmpDataMap = respItemData.DataMap if cellDateTime == `` && lenDataList > 0 { //判断是否需要做期数前移动 cellDateTime = respItemData.DateList[lenDataList-1] cellDateTime, err = GetEdbDateByMoveForwardByDateList(cell.Value, respItemData.DateList) if err != nil { utils.FileLog.Error(fmt.Sprintf("日期前移失败，配置信息;%s", cell.Value)) continue } } // 进行日期变换 cellDateTime, err = HandleMixTableDateChange(cellDateTime, cell.Value) if err != nil { utils.FileLog.Error(fmt.Sprintf("日期变换失败，配置信息;%s， 日期：%s", cell.Value, cellDateTime)) continue } val, ok := tmpDataMap[cellDateTime] if ok { cellKeyVal[cell.Uid] = val cell.ShowValue = utils.FormatMixTableDataShowValue(val) } else { cell.ShowValue = "" } case request.DateCalculateDataDT: //日期计算 dateCalculateList = append(dateCalculateList, cell.Uid) // 遍历数组，根据公式进行计算，并将得到的结果放到对应的单元格中 } row[i] = cell } config[k] = row } // 处理指定指标的日期 for k, row := range config { for i, cell := range row { calculateCellMap[cell.Uid] = Cell{ Column: k, Row: i, CellInfo: cell, } cell.ShowFormatValue = cell.ShowValue if cell.ShowStyle != `` { showStyleList = append(showStyleList, cell.Uid) } row[i] = cell cellDataRelationMap[cell.Uid] = cell } config[k] = row } // 公式链计算 if len(calculateChainList) > 0 { for _, cellKey := range calculateChainList { // 查找这个单元格的位置，直接map找了，而不是遍历整个单元格 cellPosition, ok := calculateCellMap[cellKey] if !ok { utils.FileLog.Error("找不到单元格位置：", cellKey) continue } cell := config[cellPosition.Column][cellPosition.Row] if cell.DataType != request.FormulateCalculateDataDT { // 判断公式计算（A+B这种）类型，不是的话也过滤了 continue } val, has, tmpErr, tmpErrMsg := getCalculateValueByCell(calculateCellMap, cellKey, cellKeyVal) if tmpErr != nil { errMsg = tmpErrMsg err = tmpErr return } if !has { continue } cellKeyVal[cell.Uid] = val cell.ShowValue = utils.FormatMixTableDataShowValue(val) config[cellPosition.Column][cellPosition.Row] = cell } } // 日期计算 config, err, errMsg = handlerDateCalculate(dateCalculateList, calculateCellMap, config) if err != nil { return } // 格式化展示 config, err, errMsg = handleMixCellShowStyle(showStyleList, calculateCellMap, config) if err != nil { return } newMixedTableCellDataList = config return } // getCalculateValue 获取公式计算的结果 func getCalculateValueByCell(calculateCellMap map[string]Cell, key string, cellKeyValMap map[string]float64) (val float64, has bool, err error, errMsg string) { // 单元格的标签名 val, ok := cellKeyValMap[key] if ok { has = true return } // 查找单元格数据 cell, ok := calculateCellMap[key] if !ok { err = errors.New("查找单元格" + key + "的数据失败") return } colData := cell.CellInfo // 如果不是基础计算单元格,直接返回 if colData.DataType != request.FormulateCalculateDataDT { return } // 如果是计算单元格 tagList := make([]utils.CellPosition, 0) // 计算单元格relationCellList var relationCellList []request.RelationCell if colData.Extra == `` { err = errors.New(colData.Uid + "没有绑定关系") return } err = json.Unmarshal([]byte(colData.Extra), &relationCellList) if err != nil { return } for _, relation := range relationCellList { //relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row tmpVal, _, tmpErr, tmpErrMsg := getCalculateValueByCell(calculateCellMap, relation.Key, cellKeyValMap) if tmpErr != nil { errMsg = tmpErrMsg err = tmpErr return } cellKeyValMap[relation.Key] = tmpVal rowInt, tmpErr := strconv.Atoi(relation.Row) if tmpErr != nil { err = tmpErr return } tagList = append(tagList, utils.CellPosition{ Tag: relation.Tag, Row: rowInt, Value: tmpVal, }) } // 计算 val, errMsg, err = calculateByCellList(strings.ToUpper(colData.Value), tagList) if err != nil { return } // 重新赋值 has = true cellKeyValMap[key] = val return } // handleConfig // @Description: 处理混合表格配置 // @author: Roc // @datetime2023-10-27 13:24:53 // @param configList [][]request.MixedTableCellDataReq // @return newConfig [][]request.MixedTableCellDataReq // @return edbInfoIdList []int // @return dataEdbInfoIdList []int // @return err error // @return errMsg string func handleConfig(configList [][]request.MixedTableCellDataReq) (newConfig [][]request.MixedTableCellDataReq, edbInfoIdList []int, dataEdbInfoIdList []int, err error, errMsg string) { edbInfoIdList = make([]int, 0) dataEdbInfoIdList = make([]int, 0) for ck, rowList := range configList { for rk, cell := range rowList { switch cell.DataType { case request.EdbDT: // 指标信息 edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId) case request.InsertDataDT, request.PopInsertDataDT: // 插值、弹框插值 dataEdbInfoIdList = append(dataEdbInfoIdList, cell.EdbInfoId) edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId) case request.InsertEdbCalculateDataDT: // 插入指标计算公式生成的值 var config request.CalculateConf err = json.Unmarshal([]byte(cell.Value), &config) if err != nil { return } if config.EdbInfoId == 0 && cell.EdbInfoId > 0 { edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId) } else { edbInfoIdList = append(edbInfoIdList, config.EdbInfoId) } dataEdbInfoIdList = append(dataEdbInfoIdList, cell.EdbInfoId) case request.DateDT: // 日期类型 date, newVal, tmpErr, tmpErrMsg := handleDate(cell.DataTimeType, cell.Value) if tmpErr != nil { err = tmpErr errMsg = tmpErrMsg return } rowList[rk].DataTime = date rowList[rk].ShowValue = date rowList[rk].Value = newVal //兼容原有的历史数据中系统导入日期 // 指标日期类型的单元格需要额外将指标id取出来 if cell.DataTimeType == request.EdbDateDT { var config request.EdbDateConf err = json.Unmarshal([]byte(cell.Value), &config) if err != nil { return } edbInfoIdList = append(edbInfoIdList, config.EdbInfoId) } } } configList[ck] = rowList } newConfig = configList return } // HandleDate // @Description: 日期处理 // @author: Roc // @datetime2023-10-27 09:37:02 // @param dataTimeType int // @param val string // @return date string // @return err error // @return errMsg string func HandleDate(dataTimeType int, val string) (date string, newVal string, err error, errMsg string) { return handleDate(dataTimeType, val) } // handleDate // @Description: 日期处理 // @author: Roc // @datetime2023-10-27 09:36:49 // @param dataTimeType int // @param val string // @return date string // @return err error // @return errMsg string func handleDate(dataTimeType int, val string) (date string, newVal string, err error, errMsg string) { newVal = val if val == `` { errMsg = "错误的日期数据" err = errors.New(errMsg) return } switch dataTimeType { case request.CustomDateT: //手动输入日期 /*if !strings.Contains(val, "{") { newVal, err, errMsg = handleOldCustomerDateT(val) if err != nil { return } } date, err = HandleMixTableDateChange("", newVal) if err != nil { return }*/ date = val return case request.SystemDateT: // 系统日期 date = time.Now().Format(utils.FormatDate) newVal, err, errMsg = handleOldSystemDateT(val) if err != nil { return } date, err = HandleMixTableDateChange(date, newVal) if err != nil { return } case request.EdbDateDT: // 导入指标日期（指标库的最新日期） default: errMsg = "错误的日期类型" err = errors.New(errMsg) return } return } func GetEdbDateByMoveForward(conf string, edbDataList []*data_manage.EdbDataList) (date string, err error) { dateList := make([]string, 0) for _, v := range edbDataList { dateList = append(dateList, v.DataTime) } date, err = GetEdbDateByMoveForwardByDateList(conf, dateList) return } func GetEdbDateByMoveForwardByDateList(conf string, dateList []string) (date string, err error) { moveForward := 0 if conf != "" { var edbDateConf request.EdbDateChangeConf err = json.Unmarshal([]byte(conf), &edbDateConf) if err != nil { err = fmt.Errorf("日期变换配置json解析失败失败: %s", err.Error()) return } moveForward = edbDateConf.MoveForward } // 根据日期进行排序 index := len(dateList) - 1 - moveForward for k, v := range dateList { if k == index { date = v return } } return } // HandleMixTableDateChange 处理表格中的日期变换 func HandleMixTableDateChange(date, conf string) (newDate string, err error) { newDate = date if conf == "" { return } var edbDateConf request.EdbDateConf err = json.Unmarshal([]byte(conf), &edbDateConf) if err != nil { err = fmt.Errorf("日期变换配置json解析失败失败: %s, Err:%s", conf, err.Error()) return } if newDate != "" { if len(edbDateConf.DateChange) > 0 { var dateTime time.Time dateTime, err = time.ParseInLocation(utils.FormatDate, newDate, time.Local) if err != nil { err = fmt.Errorf("日期解析失败: %s", err.Error()) return } for _, v := range edbDateConf.DateChange { if v.ChangeType == 1 { dateTime = dateTime.AddDate(v.Year, v.Month, v.Day) newDate = dateTime.Format(utils.FormatDate) } else if v.ChangeType == 2 { newDate, err, _ = handleSystemAppointDateT(dateTime, v.FrequencyDay, v.Frequency) if err != nil { return } dateTime, err = time.ParseInLocation(utils.FormatDate, newDate, time.Local) if err != nil { err = fmt.Errorf("日期解析失败: %s", err.Error()) return } } } } } return } // handleOldSystemDateT // @Description: 历史数据中的导入系统日期 // @author: Roc // @datetime2023-10-27 09:36:21 // @param confStr string // @return date string // @return err error // @return errMsg string func handleOldSystemDateT(confStr string) (newConf string, err error, errMsg string) { newConf = confStr var config request.SystemDateConf err = json.Unmarshal([]byte(confStr), &config) if err != nil { return } newConfig := new(request.EdbDateConf) dateChange := new(request.EdbDateConfDateChange) dateChangeList := make([]*request.EdbDateConfDateChange, 0) switch config.Source { case request.SystemCurrDateT: return case request.SystemCalculateDateT: // todo 是否直接更新该excel记录， dateChange.Day = config.CalculateNum dateChange.ChangeType = 1 dateChangeList = append(dateChangeList, dateChange) newConfig.DateChange = dateChangeList newConfByte, e := json.Marshal(newConfig) if e != nil { err = fmt.Errorf("日期计算额外配置，json序列化失败: %s", e.Error()) return } newConf = string(newConfByte) return case request.SystemFrequencyDateT: // 处理系统日期相关的指定频率（所在周/旬/月/季/半年/年的最后/最早一天） dateChange.FrequencyDay = config.Day dateChange.Frequency = config.Frequency dateChange.ChangeType = 1 dateChangeList = append(dateChangeList, dateChange) newConfig.DateChange = dateChangeList newConfByte, e := json.Marshal(newConfig) if e != nil { err = fmt.Errorf("日期计算额外配置，json序列化失败: %s", e.Error()) return } newConf = string(newConfByte) return default: //errMsg = "错误的日期日期导入方式" //err = errors.New(fmt.Sprint("错误的日期日期导入方式:", config.Source)) return } return } // handleSystemCalculateDateT // @Description: 处理系统日期计算后的日期 // @author: Roc // @datetime2023-10-27 09:31:22 // @param num int // @param frequency string // @return date string // @return err error // @return errMsg string func handleSystemCalculateDateT(num int, frequency string) (date string, err error, errMsg string) { if err != nil { return } currDate := time.Now() switch frequency { case "", "日": date = currDate.AddDate(0, 0, num).Format(utils.FormatDate) default: errMsg = "错误的日期频度：" + frequency err = errors.New(errMsg) return } return } // handleSystemAppointDateT // @Description: 处理系统日期相关的指定频率（所在周/旬/月/季/半年/年的最后/最早一天） // @author: Roc // @datetime2023-10-27 09:31:35 // @param Frequency string // @param Day string // @return date string // @return err error // @return errMsg string func handleSystemAppointDateT(currDate time.Time, appointDay, frequency string) (date string, err error, errMsg string) { //currDate := time.Now() switch frequency { case "本周": day := int(currDate.Weekday()) if day == 0 { // 周日 day = 7 } num := 0 switch appointDay { case "周一": num = 1 case "周二": num = 2 case "周三": num = 3 case "周四": num = 4 case "周五": num = 5 case "周六": num = 6 case "周日": num = 7 } day = num - day date = currDate.AddDate(0, 0, day).Format(utils.FormatDate) case "本旬": day := currDate.Day() var tmpDate time.Time switch appointDay { case "第一天": if day <= 10 { tmpDate = time.Date(currDate.Year(), currDate.Month(), 1, 0, 0, 0, 0, currDate.Location()) } else if day <= 20 { tmpDate = time.Date(currDate.Year(), currDate.Month(), 11, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), currDate.Month(), 21, 0, 0, 0, 0, currDate.Location()) } case "最后一天": if day <= 10 { tmpDate = time.Date(currDate.Year(), currDate.Month(), 10, 0, 0, 0, 0, currDate.Location()) } else if day <= 20 { tmpDate = time.Date(currDate.Year(), currDate.Month(), 20, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), currDate.Month()+1, 1, 0, 0, 0, 0, currDate.Location()).AddDate(0, 0, -1) } } date = tmpDate.Format(utils.FormatDate) case "本月": var tmpDate time.Time switch appointDay { case "第一天": tmpDate = time.Date(currDate.Year(), currDate.Month(), 1, 0, 0, 0, 0, currDate.Location()) case "最后一天": tmpDate = time.Date(currDate.Year(), currDate.Month()+1, 1, 0, 0, 0, 0, currDate.Location()).AddDate(0, 0, -1) } date = tmpDate.Format(utils.FormatDate) case "本季": month := currDate.Month() var tmpDate time.Time switch appointDay { case "第一天": if month <= 3 { tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location()) } else if month <= 6 { tmpDate = time.Date(currDate.Year(), 4, 1, 0, 0, 0, 0, currDate.Location()) } else if month <= 9 { tmpDate = time.Date(currDate.Year(), 7, 1, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), 10, 1, 0, 0, 0, 0, currDate.Location()) } case "最后一天": if month <= 3 { tmpDate = time.Date(currDate.Year(), 3, 31, 0, 0, 0, 0, currDate.Location()) } else if month <= 6 { tmpDate = time.Date(currDate.Year(), 6, 30, 0, 0, 0, 0, currDate.Location()) } else if month <= 9 { tmpDate = time.Date(currDate.Year(), 9, 30, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location()) } } date = tmpDate.Format(utils.FormatDate) case "本半年": month := currDate.Month() var tmpDate time.Time switch appointDay { case "第一天": if month <= 6 { tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), 7, 1, 0, 0, 0, 0, currDate.Location()) } case "最后一天": if month <= 6 { tmpDate = time.Date(currDate.Year(), 6, 30, 0, 0, 0, 0, currDate.Location()) } else { tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location()) } } date = tmpDate.Format(utils.FormatDate) case "本年": var tmpDate time.Time switch appointDay { case "第一天": tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location()) case "最后一天": tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location()) } date = tmpDate.Format(utils.FormatDate) default: errMsg = "错误的日期频度：" + frequency err = errors.New(errMsg) return } return } // calculateByCellList // @Description: 根据单元格来进行公式计算 // @author: Roc // @datetime2023-11-14 16:17:38 // @param calculateFormula string // @param tagList []utils.CellPosition // @return calVal string // @return errMsg string // @return err error func calculateByCellList(calculateFormula string, tagList []utils.CellPosition) (calVal float64, errMsg string, err error) { if calculateFormula == "" { errMsg = "公式异常" err = errors.New(errMsg) return } calculateFormula = strings.TrimPrefix(calculateFormula, "=") calculateFormula = strings.Replace(calculateFormula, "（", "(", -1) calculateFormula = strings.Replace(calculateFormula, "）", ")", -1) calculateFormula = strings.Replace(calculateFormula, "，", ",", -1) calculateFormula = strings.Replace(calculateFormula, "。", ".", -1) calculateFormula = strings.Replace(calculateFormula, "%", "*0.01", -1) rowList := make([]int, 0) rowListMap := make(map[int][]utils.CellPosition) for _, v := range tagList { tmpRowList, ok := rowListMap[v.Row] if !ok { rowList = append(rowList, v.Row) tmpRowList = make([]utils.CellPosition, 0) } tmpRowList = append(tmpRowList, v) rowListMap[v.Row] = tmpRowList } sort.Ints(rowList) list := make([]utils.CellPosition, 0) for _, row := range rowList { list = append(list, rowListMap[row]...) } formulaFormStr := utils.ReplaceFormulaByCellList(list, calculateFormula) //计算公式异常，那么就移除该指标 if formulaFormStr == `` { errMsg = "公式异常" err = errors.New(errMsg) return } expression := formula.NewExpression(formulaFormStr) calResult, err := expression.Evaluate() if err != nil { errMsg = "计算失败" err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr) // 分母为0的报错 if strings.Contains(err.Error(), "divide by zero") { errMsg = "分母不能为0" err = errors.New("分母不能为空，计算公式：" + formulaFormStr) } return } // 如果计算结果是NAN，那么就提示报错 if calResult.IsNan() { errMsg = "计算失败" err = errors.New("计算失败：计算结果是:NAN;formulaStr:" + formulaFormStr) return } calVal, err = calResult.Float64() if err != nil { return } // 转Decimal然后四舍五入 calVal, _ = decimal.NewFromFloat(calVal).Round(4).Float64() return } // handlerDateCalculate 处理日期计算 func handlerDateCalculate(dateCalculateList []string, calculateCellMap map[string]Cell, oldConfig [][]request.MixedTableCellDataReq) (config [][]request.MixedTableCellDataReq, err error, errMsg string) { config = oldConfig if len(dateCalculateList) == 0 { return } if len(dateCalculateList) > 0 { for _, cellKey := range dateCalculateList { // 查找这个单元格的位置，直接map找了，而不是遍历整个单元格 cellPosition, ok := calculateCellMap[cellKey] if !ok { utils.FileLog.Error("找不到单元格位置：", cellKey) continue } cell := config[cellPosition.Column][cellPosition.Row] if cell.DataType != request.DateCalculateDataDT { // 判断公式计算（A+B这种）类型，不是的话也过滤了 continue } val, tmpErr, tmpErrMsg := DateCalculatePrepare(calculateCellMap, cell.Value) if tmpErr != nil { cell.ShowValue = "" config[cellPosition.Column][cellPosition.Row] = cell utils.FileLog.Error(fmt.Sprintf("%s 日期计算报错：Err：%s:%s", cellKey, tmpErr, tmpErrMsg)) continue } cell.ShowValue = utils.FormatMixTableDataShowValue(val) config[cellPosition.Column][cellPosition.Row] = cell } } return } // DateCalculatePrepare 单个单元格的日期计算 func DateCalculatePrepare(calculateCellMap map[string]Cell, config string) (val float64, err error, errMsg string) { var edbDateConf request.MixDateCalculateConf err = json.Unmarshal([]byte(config), &edbDateConf) if err != nil { err = fmt.Errorf("日期计算配置json解析失败失败: %s, Err:%s", config, err.Error()) return } if len(edbDateConf.RelationCellList) == 0 { err = fmt.Errorf("日期计算 未配置日期单元格失败: %s", config) return } valMap := make(map[string]int) for _, v := range edbDateConf.RelationCellList { // 查找单元格数据 cell, ok := calculateCellMap[v.Uid] if !ok { err = fmt.Errorf("查找单元格:%s 的数据失败", v.Uid) return } colData := cell.CellInfo // 如果不是基础计算单元格,直接返回 _, err = time.ParseInLocation(utils.FormatDate, colData.ShowValue, time.Local) if err != nil { err = fmt.Errorf("%s 的单元格非日期类型, Err: %s", colData.ShowValue, err.Error()) return } // todo 把日期转换成excel里的天数 realDiffDay := utils.GetDaysDiff1900(colData.ShowValue) valMap[strings.ToUpper(v.Tag)] = realDiffDay } // 计算 val, errMsg, err = DateCalculateFormula(valMap, strings.ToUpper(edbDateConf.Formula)) if err != nil { return } return } func DateCalculateFormula(valTagMap map[string]int, calculateFormula string) (calVal float64, errMsg string, err error) { if calculateFormula == "" { errMsg = "公式异常" err = errors.New(errMsg) return } calculateFormula = strings.TrimPrefix(calculateFormula, "=") calculateFormula = strings.Replace(calculateFormula, "（", "(", -1) calculateFormula = strings.Replace(calculateFormula, "）", ")", -1) calculateFormula = strings.Replace(calculateFormula, "，", ",", -1) calculateFormula = strings.Replace(calculateFormula, "。", ".", -1) calculateFormula = strings.Replace(calculateFormula, "%", "*0.01", -1) formulaFormStr := utils.ReplaceFormulaByTagMap(valTagMap, calculateFormula) if formulaFormStr == `` { errMsg = "公式异常" err = errors.New(errMsg) return } fmt.Println("公式：" + formulaFormStr) expression := formula.NewExpression(formulaFormStr) calResult, err := expression.Evaluate() if err != nil { errMsg = "公式错误,请重新填写" err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr) // 分母为0的报错 if strings.Contains(err.Error(), "divide by zero") { errMsg = "分母不能为0" err = errors.New("分母不能为空，计算公式：" + formulaFormStr) } return } // 如果计算结果是NAN，那么就提示报错 if calResult.IsNan() { errMsg = "公式错误,请重新填写" err = errors.New("计算失败：计算结果是:NAN;formulaStr:" + formulaFormStr) return } calVal, err = calResult.Float64() if err != nil { return } // 转Decimal然后四舍五入 calVal, _ = decimal.NewFromFloat(calVal).Round(4).Float64() return } // handleMixCellShowStyle 处理混合表格中，显示计算的逻辑 func handleMixCellShowStyle(showStyleList []string, calculateCellMap map[string]Cell, oldConfig [][]request.MixedTableCellDataReq) (config [][]request.MixedTableCellDataReq, err error, errMsg string) { config = oldConfig if len(showStyleList) == 0 { return } if len(showStyleList) > 0 { for _, cellKey := range showStyleList { // 查找这个单元格的位置，直接map找了，而不是遍历整个单元格 cellPosition, ok := calculateCellMap[cellKey] if !ok { utils.FileLog.Error("找不到单元格位置：", cellKey) continue } cell := config[cellPosition.Column][cellPosition.Row] val := cell.ShowValue isPercent := false if strings.Contains(val, "%") { isPercent = true val = strings.Trim(val, "%") } _, e := strconv.ParseFloat(val, 64) // 将字符串转换成float类型 if e != nil { // 如果没有错误发生则返回true，说明该字符串是一个合法的数字 continue } var styleConf request.MixCellShowStyle err = json.Unmarshal([]byte(cell.ShowStyle), &styleConf) if err != nil { err = fmt.Errorf("日期计算配置json解析失败失败: %s, Err:%s", config, err.Error()) return } if styleConf.Pn != 0 || styleConf.Nt != "" { val = changePointDecimalPlaces(val, styleConf.Pn, styleConf.Nt, isPercent) cell.ShowFormatValue = val } else { cell.ShowFormatValue = cell.ShowValue } config[cellPosition.Column][cellPosition.Row] = cell } } return } // changePointDecimalPlaces 小数点位数加减和百分比格式 func changePointDecimalPlaces(str string, changeNum int, numberType string, isPercent bool) (newStr string) { newStr = str var decimalPlaces int dotIndex := strings.Index(newStr, ".") // 查找小数点的位置 if dotIndex == -1 { decimalPlaces = 0 } else { decimalPlaces = len(newStr) - dotIndex - 1 } // 把字符串转成浮点数 val, _ := strconv.ParseFloat(str, 64) if isPercent { if numberType == "number" { //百分数转成小数 val = val / 100 if decimalPlaces > 2 { decimalPlaces += 2 } else if decimalPlaces == 1 { decimalPlaces += 1 } else if decimalPlaces == 0 { if len(str) == 1 { decimalPlaces = 2 } else if len(str) == 2 { if str[1] == '0' { decimalPlaces = 1 } else { decimalPlaces = 2 } } } isPercent = false } } else { if numberType == "percent" { if decimalPlaces > 2 { decimalPlaces -= 2 } else if decimalPlaces == 1 { decimalPlaces = 0 } val = val * 100 } } if decimalPlaces > 0 { val, _ = decimal.NewFromFloat(val).Round(int32(decimalPlaces)).Float64() newStr = strconv.FormatFloat(val, 'f', decimalPlaces, 64) } else { newStr = fmt.Sprintf("%v", val) } // 计算小数位数 decimalPlaces = 0 dotIndex = strings.Index(newStr, ".") // 查找小数点的位置 if dotIndex == -1 { decimalPlaces = 0 } else { decimalPlaces = len(newStr) - dotIndex - 1 } decimalPlaces += changeNum if decimalPlaces < 0 { decimalPlaces = 0 } val, _ = decimal.NewFromFloat(val).Round(int32(decimalPlaces)).Float64() newStr = strconv.FormatFloat(val, 'f', decimalPlaces, 64) if numberType == "percent" || isPercent { newStr += "%" } return } func fixCalculateValueConfig(conf string, edbInfoId int) (newConf string, err error) { newConf = conf if edbInfoId == 0 { return } var tmpConfig request.CalculateConf err = json.Unmarshal([]byte(conf), &tmpConfig) if err != nil { return } if tmpConfig.EdbInfoId == 0 { tmpConfig.EdbInfoId = edbInfoId newConfByte, _ := json.Marshal(tmpConfig) newConf = string(newConfByte) return } return }