1. SUMIFS函数
SUMIFS 函数用于计算子表单中满足多个条件的数字相加并返回和。
2. 函数用法
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
-
sum_range:必需;用于求和的计算字段。支持的字段包括:子表单中的数字、单行文本、下拉框、单选按钮组;
-
criteria_range1:必需;根据 criteria1 的条件规则进行检测的判断字段 1。支持的字段包括:子表单中的数字、单行文本、下拉框、单选按钮组;
-
criteria1:必需;用于判断的条件规则 1。支持的形式和使用规则如下表:
支持形式 | 是否需要加引号 | 示例 | 注意事项 |
数字 | 不需要 | 20、32 | |
表达式 | 需要 | “>32”、"!=苹果" | 支持的运算符号包括:>、<、==、!=、>=、<= |
文本 | 需要 | “苹果”、"水果" | |
字段 | 不需要 | 字段 | 1)在主表字段中使用 SUMIFS 函数时,只能选择主表字段2)在子表字段中使用 SUMIFS 函数时,只能选择择主表字段和当前子表字段 |
-
[criteria_range2, criteria2], ...:非必需;规则同上。
注:
1)所有的 criteria_range 必须与 sum_range 来自同一个子表单,否则将计算失败;criteria_range 可以与 sum_range 选择同一字段;
2)数量上限:最多可以输入 127 个条件对,即本函数最少需要3个参数,最多可输入 255 个参数。
3. 函数示例
如,计算入库明细中水果名称为「苹果」,且种类为「红富士」的全部入库数量,则可以在「红富士苹果数量总计」字段设置公式为:
SUMIFS(入库明细.数量,入库明细.水果名称,"苹果",入库明细.水果种类,"红富士")
4. 代码实战
首先我们在function包下创建math包,在math包下创建SumIfsFunction类,代码如下:
package com.ql.util.express.self.combat.function.math;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ql.util.express.Operator;
import com.ql.util.express.self.combat.exception.FormulaException;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.Predicate;
import java.util.stream.Collectors;
/**
* 类描述: SUMIFS函数
*
* @author admin
* @version 1.0.0
* @date 2023/11/24 10:46
*/
public class SumIfsFunction extends Operator {
public SumIfsFunction(String name) {
this.name = name;
}
@Override
public Object executeInner(Object[] lists) throws Exception {
if (lists.length<=2) {
throw new FormulaException("操作数异常");
}
String json = lists[0].toString();
List<Map<String,Object>> subFormVal = JSON.parseObject(json,List.class);
String sumRange = lists[1].toString();
String criteriaRange = lists[2].toString();
// 动态参数
BigDecimal res = BigDecimal.ZERO;
JSONArray pl = JSON.parseArray(criteriaRange);
CriteriaRangeParam[] pArr = new CriteriaRangeParam[pl.size()];
for (int i=0;i<pl.size();i++) {
JSONObject object = (JSONObject)pl.get(i);
String _criteriaRange = (String)object.get("criteriaRange");
String _criteria = (String)object.get("criteria");
CriteriaRangeParam param = new CriteriaRangeParam(_criteriaRange,_criteria);
pArr[i] = param;
}
res = sumifs(subFormVal,sumRange,pArr);
return res;
}
// 计算结果
private BigDecimal sumifs(List<Map<String, Object>> list, String sumRange, CriteriaRangeParam ...params) {
if (params == null || params.length == 0) {
throw new RuntimeException("[sumifs]参数有问题!");
}
// 根据criteria类型 生成Predicate
List<Map<String, Object>> collect =null;
for (CriteriaRangeParam param:params) {
String criteriaRange = param.getCriteriaRange();
String criteria = param.getCriteria();// 规则
// 根据规则转换
// criteria判断类型
boolean isNum = SumIfsFunction.CriteriaUtil.isNum(criteria);
boolean isExpress = SumIfsFunction.CriteriaUtil.isExpress(criteria);
if (isExpress) {// 如果是表达式
// 提取符号
String symbol = SumIfsFunction.CriteriaUtil.extractSymbol(criteria);
String symbol_value = criteria.replaceAll(symbol,"");
boolean sybolValueIsNum = SumIfsFunction.CriteriaUtil.isNum(symbol_value);
if (sybolValueIsNum) {// 如果是数字
collect = list.stream().filter(paramMap -> {
boolean res = false;
if ("==".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) == Double.parseDouble(symbol_value)?true:false;
} else if (">".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) > Double.parseDouble(symbol_value)?true:false;
} else if (">=".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) >= Double.parseDouble(symbol_value)?true:false;
} else if ("<".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) < Double.parseDouble(symbol_value)?true:false;
} else if ("<=".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) <= Double.parseDouble(symbol_value)?true:false;
} else if ("!=".equals(symbol)) {
res = Double.parseDouble(paramMap.get(criteriaRange).toString()) != Double.parseDouble(symbol_value)?true:false;
}
return res;
}).collect(Collectors.toList());
} else {
collect = list.stream().filter(paramMap -> {
boolean res = false;
if ("==".equals(symbol)) {
res = String.valueOf(paramMap.get(criteriaRange)).equals(symbol_value);
} else if ("!=".equals(symbol)) {
res = !(String.valueOf(paramMap.get(criteriaRange)).equals(symbol_value));
} else {
throw new RuntimeException("字符暂不支持的操作符号为:"+symbol);
}
return res;
}).collect(Collectors.toList());
}
} else {// 没有表达式 直接默认为==
if (isNum) {// 如果是数字
collect = list.stream().filter(paramMap -> Double.parseDouble(paramMap.get(criteriaRange).toString()) == Double.parseDouble(criteria)?true:false).collect(Collectors.toList());
} else {
collect = list.stream().filter(paramMap -> String.valueOf(paramMap.get(criteriaRange)).equals(criteria)).collect(Collectors.toList());
}
}
// 重新赋值
list = collect;
}
// 满足条件的集合统计出来后,按照sumRange字段统计求和
BigDecimal sum = BigDecimal.ZERO;
for (Map<String,Object> map:collect) {
BigDecimal tmp = new BigDecimal(map.get(sumRange).toString());
sum=sum.add(tmp);
}
return sum;
}
public static class CriteriaRangeParam {
private String criteriaRange;
private String criteria;
public CriteriaRangeParam() {
}
public CriteriaRangeParam(String criteriaRange,String criteria) {
this.criteriaRange = criteriaRange;
this.criteria = criteria;
}
public String getCriteriaRange() {
return criteriaRange;
}
public void setCriteriaRange(String criteriaRange) {
this.criteriaRange = criteriaRange;
}
public String getCriteria() {
return criteria;
}
public void setCriteria(String criteria) {
this.criteria = criteria;
}
}
static class CriteriaUtil {
public static boolean isNum (String criteria) {
return StrUtil.isNumeric(criteria);
}
public static boolean isExpress(String criteria) {
List<String> symbols = Arrays.asList("gt","ge","lt","le","eq","nq");
boolean res = symbols.stream().anyMatch(s -> criteria.contains(s));
return res;
}
/***
* 提取表达式中的符号
* @param criteria
* @return
*/
public static String extractSymbol(String criteria) {
List<String> symbols = Arrays.asList("gt","ge","lt","le","eq","nq");
final Optional<String> first = symbols.stream().filter(new Predicate<String>() {
@Override
public boolean test(String s) {
return criteria.contains(s);
}
}).findFirst();
return first.get();
}
}
}
把SumIfsFunction类注册到公式函数入口类中,代码如下:
package com.ql.util.express.self.combat.ext;
import com.ql.util.express.ExpressRunner;
import com.ql.util.express.IExpressResourceLoader;
import com.ql.util.express.parse.NodeTypeManager;
import com.ql.util.express.self.combat.function.logic.*;
import com.ql.util.express.self.combat.function.math.*;
/**
* 类描述: 仿简道云公式函数实战入口类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:29
*/
public class FormulaRunner extends ExpressRunner {
public FormulaRunner() {
super();
}
public FormulaRunner(boolean isPrecise, boolean isTrace) {
super(isPrecise,isTrace);
}
public FormulaRunner(boolean isPrecise, boolean isStrace, NodeTypeManager nodeTypeManager) {
super(isPrecise,isStrace,nodeTypeManager);
}
public FormulaRunner(boolean isPrecise, boolean isTrace, IExpressResourceLoader iExpressResourceLoader, NodeTypeManager nodeTypeManager) {
super(isPrecise,isTrace,iExpressResourceLoader,nodeTypeManager);
}
@Override
public void addSystemFunctions() {
// ExpressRunner 的内部系统函数
super.addSystemFunctions();
// 扩展公式函数
this.customFunction();
}
/***
* 自定义公式函数
*/
public void customFunction() {
// 逻辑公式函数
this.addLogicFunction();
// 数学公式函数
this.addMathFunction();
}
public void addLogicFunction() {
// AND函数
this.addFunction("AND",new AndFunction("AND"));
// IF函数
this.addFunction("IF",new IfFunction("IF"));
// IFS函数
this.addFunction("IFS",new IfsFunction("IFS"));
// XOR函数
this.addFunction("XOR",new XorFunction("XOR"));
// TRUE函数
this.addFunction("TRUE",new TrueFunction("TRUE"));
// FALSE函数
this.addFunction("FALSE",new FalseFunction("FALSE"));
// NOT函数
this.addFunction("NOT",new NotFunction("NOT"));
// OR函数
this.addFunction("OR",new OrFunction("OR"));
}
public void addMathFunction() {
// ABS函数
this.addFunction("ABS",new AbsFunction("ABS"));
// AVERAGE函数
this.addFunction("AVERAGE",new AvgFunction("AVERAGE"));
// CEILING函数
this.addFunction("CEILING",new CeilingFunction("CEILING"));
// RADIANS函数
this.addFunction("RADIANS",new RadiansFunction("RADIANS"));
// COS函数
this.addFunction("COS",new CosFunction("COS"));
// COT函数
this.addFunction("COT",new CotFunction("COT"));
// COUNT函数
this.addFunction("COUNT",new CountFunction("COUNT"));
// COUNTIF函数
this.addFunction("COUNTIF",new CountIfFunction("COUNTIF"));
// FIXED函数
this.addFunction("FIXED",new FixedFunction("FIXED"));
// FLOOR函数
this.addFunction("FLOOR",new FloorFunction("FLOOR"));
// INT函数
this.addFunction("INT",new IntFunction("INT"));
// LARGE函数
this.addFunction("LARGE",new LargeFunction("LARGE"));
// LOG函数
this.addFunction("LOG",new LogFunction("LOG"));
// MAX函数
this.addFunction("MAX",new MaxFunction("MAX"));
// MIN函数
this.addFunction("MIN",new MinFunction("MIN"));
// MOD函数
this.addFunction("MOD",new ModFunction("MOD"));
// POWER函数
this.addFunction("POWER",new PowerFunction("POWER"));
// PRODUCT函数
this.addFunction("PRODUCT",new ProductFunction("PRODUCT"));
// RAND函数
this.addFunction("RAND",new RandFunction("RAND"));
// ROUND函数
this.addFunction("ROUND",new RoundFunction("ROUND"));
// SIN函数
this.addFunction("SIN",new SinFunction("SIN"));
// SMALL函数
this.addFunction("SMALL",new SmallFunction("SMALL"));
// SQRT函数
this.addFunction("SQRT",new SqrtFunction("SQRT"));
// SUM函数
this.addFunction("SUM",new SumFunction("SUM"));
// SUMIF函数
this.addFunction("SUMIF",new SumIfFunction("SUMIF"));
// SUMIFS函数
this.addFunction("SUMIFS",new SumIfsFunction("SUMIFS"));
}
}
创建测试用例
package com.ql.util.express.self.combat;
import com.alibaba.fastjson.JSON;
import com.ql.util.express.DefaultContext;
import com.ql.util.express.self.combat.ext.FormulaRunner;
import com.ql.util.express.self.combat.function.math.SumIfsFunction;
import org.junit.Test;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 类描述: 实战测试类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:45
*/
public class CombatTest {
@Test
public void SUMIFS() throws Exception{
FormulaRunner formulaRunner = new FormulaRunner(true,true);
// 创建上下文
DefaultContext<String, Object> context = new DefaultContext<>();
List<Map<String,Object>> list = new ArrayList<>();
Map<String,Object> map = new HashMap<>();
map.put("record.type","红富士");
map.put("record.name","苹果");
map.put("record.num",20.0);
Map<String,Object> map2 = new HashMap<>();
map2.put("record.type","红富士");
map2.put("record.name","苹果");
map2.put("record.num", BigInteger.valueOf((long) Integer.MAX_VALUE * 2));
Map<String,Object> map3 = new HashMap<>();
map3.put("record.type","红星");
map3.put("record.name","苹果");
map3.put("record.num",30.0);
Map<String,Object> map4 = new HashMap<>();
map4.put("record.type","美国");
map4.put("record.name","苹果");
map4.put("record.num",13000.0);
Map<String,Object> map5 = new HashMap<>();
map5.put("record.type","夏黑");
map5.put("record.name","葡萄");
map5.put("record.num",15);
Map<String,Object> map6 = new HashMap<>();
map6.put("record.type","阳光玫瑰");
map6.put("record.name","葡萄");
map6.put("record.num",30);
Map<String,Object> map7 = new HashMap<>();
map7.put("record.type","芝麻蕉");
map7.put("record.name","香蕉");
map7.put("record.num","20");
Map<String,Object> map8 = new HashMap<>();
map8.put("record.type","红富士");
map8.put("record.name","香蕉");
map8.put("record.num","20");
list.add(map);
list.add(map2);
list.add(map3);
list.add(map4);
list.add(map5);
list.add(map6);
list.add(map7);
list.add(map8);
System.out.println(BigInteger.valueOf((long) Integer.MAX_VALUE * 2));
String s = JSON.toJSONString(list);
String express = "SUMIFS(key,sum_range,cerial_range)";
List<SumIfsFunction.CriteriaRangeParam> paramList = new ArrayList<>();
paramList.add(new SumIfsFunction.CriteriaRangeParam("record.type","红富士"));
paramList.add(new SumIfsFunction.CriteriaRangeParam("record.name","苹果"));
String paramListStr = JSON.toJSONString(paramList);
context.put("key",s);
context.put("sum_range","record.num");
context.put("cerial_range",paramListStr);
Object object = formulaRunner.execute(express, context, null, true, true);
System.out.println(object);
}
}
运行结果