报表取数插件分享原创
像孩子一样笑啊
6人赞赏了该文章
1,855次浏览
编辑于2021年12月15日 16:26:21
花了几天时间边学边做的苍穹报表，学到了很多东西，这里把自己写的插件分享一下，有不好的地方也希望大家批评指正
package zrt.contract.report;
import kd.bos.algo.*;
import kd.bos.algo.datatype.DateType;
import kd.bos.algo.input.CollectionInput;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.entity.report.*;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import kd.bos.servicehelper.QueryServiceHelper;
import kd.tmc.cim.common.util.StringUtils;
import java.text.SimpleDateFormat;
import java.util.*;
public class ContractReportPlugin extends AbstractReportListDataPlugin{
//字段映射Map
public static Map<String,String> mapping = new HashMap<>();
public static Map<String,DataType> filer_type_mapping = new HashMap<String,DataType>();
static {
mapping.put("zrt_contype.id","zrt_contype");
mapping.put("zrt_statusfilter","zrt_constatus");
mapping.put("zrt_deptfilter.id","zrt_dept");
mapping.put("zrt_supplierfilter.id","zrt_supplier");
mapping.put("zrt_creatorfilter.id","zrt_creator");
mapping.put("zrt_conenddatefilter","zrt_conenddate ");
mapping.put("zrt_constartdatefilter","zrt_constartdate");
mapping.put("zrt_amounttotalfilter","zrt_amounttotal");
filer_type_mapping.put("zrt_contype.id",DataType.LongType);
filer_type_mapping.put("zrt_statusfilter",DataType.StringType);
filer_type_mapping.put("zrt_deptfilter.id",DataType.LongType);
filer_type_mapping.put("zrt_supplierfilter.id",DataType.LongType);
filer_type_mapping.put("zrt_creatorfilter.id",DataType.LongType);
filer_type_mapping.put("zrt_conenddatefilter",DataType.DateType);
filer_type_mapping.put("zrt_constartdatefilter",DataType.DateType);
filer_type_mapping.put("zrt_amounttotalfilter",DataType.BigDecimalType);
}
public static String[] FILTER_FIELDS={
"zrt_contype.id","zrt_statusfilter","zrt_deptfilter.id","zrt_supplierfilter.id","zrt_creatorfilter.id",
"zrt_conenddatefilter","zrt_constartdatefilter"
};
// 付款计划标识
String LEASE_PAY_PLAN= "fa_lease_pay_plan";
// 付款计划字段
String []LEASE_PAY_PLAN_FILED = {
// 关联合同 ，计划付款 ，实际付款 ，实际未付
"curlease","rent","realpayamount","real_unpay"
};
// 供应商标识
String SUPPLIER="bd_supplier";
// 供应商字段
String [] SUPPLIER_FIELD = {"id","zrt_bank","zrt_account"};
// 供应商字段类型
DataType [] SUPPLIER_FIELD_DATATYPES={DateType.LongType,DateType.LongType,DateType.StringType};
// 租赁合同标识
String LEASE_CONTRACT = "fa_lease_contract";
// 租赁合同select字段
String[] LEASE_CONTRACT_FILED = {
// 单据编码，制单人，租赁合同，单据状态， 合同签订日期， 0 - 6
"id","isbak","number","creator","zrt_contracttype","status","contrsigndate",
// 合同开始日期，合同截至日期，部门，供应商 7 - 10
"leasestartdate","leaseenddate","zrt_costeddept","leaser"
};
// 非租赁合同标识
String COMCONTRACT = "zrt_comcontract";
// 非租赁合同字段
String[] COMCONREACT_FILED={
// 单据编码 ，制单人， 非租赁合同 ，单据状态 ，单据状态 ，地址 ， 0 - 4
"billno","creator","zrt_contracttype","billstatus","zrt_engineeraddress"
// 合同签订日期 5
,"zrt_signdate"
// 合同开始日期，合同截至日期，部门，供应商 6- 9
,"zrt_startdate","zrt_enddate","zrt_costeddept","zrt_contractother"
// 开户行 ，银行账户，事项内容 10 - 12
,"zrt_payerbank","zrt_payeeaccount","zrt_contentorname"
// 合同总金额，已付金额，未付金额 13 - 15
,"zrt_amounttax","zrt_amounttaxpaid","zrt_amounttaxnotpaid"
};
// 报表标识
String CONREPORT = "zrt_cus_conreport";
// 报表界面显示字段
String[] FIELDS = {
//("合同编码，经办人，合同类型，合同状态，地址信息")0 -4
"zrt_connumber","zrt_creator","zrt_contype","zrt_constatus","zrt_address",
// 行政区划，签订日期，合同开始日期,合同截至日期 5 - 8
"zrt_admindivision","zrt_signdate","zrt_constartdate","zrt_conenddate",
// 使用部门，合同对方，银行，账户，事项内容，9 - 12
"zrt_dept","zrt_supplier","zrt_bank","zrt_account","zrt_itemcontent",
// 合同总金额，已付金额，未付金额 13 - 15
"zrt_amounttotal","zrt_amountpaid","zrt_amountnotpaid"
};
public static DataType[] FILTER_FIELDS_DATATYPES={
DataType.LongType,DataType.StringType,DataType.LongType,DataType.LongType,DataType.LongType,
DataType.DateType,DataType.DateType
};
// 显示字段对应字段类型
DataType []DATATYPES = {
DataType.StringType,DataType.StringType,DataType.StringType,DataType.StringType,DataType.StringType,
DataType.StringType,DataType.StringType,DataType.DateType,DataType.DateType,
DataType.StringType,DataType.StringType,DataType.StringType,DataType.StringType,
DataType.BigDecimalType,DataType.BigDecimalType,DataType.BigDecimalType
};
@Override
public DataSet query(ReportQueryParam arg0, Object arg1) throws Throwable {
// 查询非租赁合同的相关信息。
StringBuilder comContractselectField = new StringBuilder();
comContractselectField.append(COMCONREACT_FILED[0]).append(" AS ").append(FIELDS[0]).append(", ")
.append(COMCONREACT_FILED[1]).append(" AS ").append(FIELDS[1]).append(", ")
.append(COMCONREACT_FILED[2]).append(" AS ").append(FIELDS[2]).append(", ")
.append(COMCONREACT_FILED[3]).append(" AS ").append(FIELDS[3]).append(", ")
.append(COMCONREACT_FILED[4]).append(" AS ").append(FIELDS[4]).append(", ") // 地址
.append(COMCONREACT_FILED[5]).append(" AS ").append(FIELDS[6]).append(", ")
.append(COMCONREACT_FILED[6]).append(" AS ").append(FIELDS[7]).append(", ")
.append(COMCONREACT_FILED[7]).append(" AS ").append(FIELDS[8]).append(", ") // 截止日期
.append(COMCONREACT_FILED[8]).append(" AS ").append(FIELDS[9]).append(", ") // 部门
.append(COMCONREACT_FILED[9]).append(" AS ").append(FIELDS[10]).append(", ") // 供应商
.append(COMCONREACT_FILED[10]).append(" AS ").append(FIELDS[11]).append(", ") // 银行
.append(COMCONREACT_FILED[11]).append(" AS ").append(FIELDS[12]).append(", ") // 账户
.append(COMCONREACT_FILED[12]).append(" AS ").append(FIELDS[13]).append(", ") // 事项
.append(COMCONREACT_FILED[13]).append(" AS ").append(FIELDS[14]).append(", ") // 合同总金额
.append(COMCONREACT_FILED[14]).append(" AS ").append(FIELDS[15]).append(", ") // 合同已付
.append(COMCONREACT_FILED[15]).append(" AS ").append(FIELDS[16]); // 合同未付
DataSet comContractDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName()+LEASE_CONTRACT, COMCONTRACT,
comContractselectField.toString(), null, null);
// 租赁合同没有地址信息
StringBuilder leaseContractSelectField = new StringBuilder();
leaseContractSelectField.append(LEASE_CONTRACT_FILED[0]).append(", ")
.append(LEASE_CONTRACT_FILED[1]).append(", ")
.append(LEASE_CONTRACT_FILED[2]).append(" AS ").append(FIELDS[0]).append(", ") // 编码
.append(LEASE_CONTRACT_FILED[3]).append(" AS ").append(FIELDS[1]).append(", ") //
.append(LEASE_CONTRACT_FILED[4]).append(" AS ").append(FIELDS[2]).append(", ") //
.append(LEASE_CONTRACT_FILED[5]).append(" AS ").append(FIELDS[3]).append(", ") //
.append("0").append(" AS ").append(FIELDS[4]).append(", ") // 地址
.append(LEASE_CONTRACT_FILED[6]).append(" AS ").append(FIELDS[6]).append(", ") // 截止日期
.append(LEASE_CONTRACT_FILED[7]).append(" AS ").append(FIELDS[7]).append(", ") //
.append(LEASE_CONTRACT_FILED[8]).append(" AS ").append(FIELDS[8]).append(", ") //
.append(LEASE_CONTRACT_FILED[9]).append(" AS ").append(FIELDS[9]).append(", ") // 部门
.append(LEASE_CONTRACT_FILED[10]).append(" AS ").append(FIELDS[10]).append(", ")// 供应商
.append("''").append(" AS ").append(FIELDS[13]);
DataSet leaseContractBaseDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName()+LEASE_CONTRACT, LEASE_CONTRACT,
leaseContractSelectField.toString(), QFilter.of("isbak = 0").toArray(), null);
DataSet supplierDataSet = getSupplierDataSet();
String[] tempSelect = new String[]{LEASE_CONTRACT_FILED[0],LEASE_CONTRACT_FILED[1]
,FIELDS[0],FIELDS[1],FIELDS[2],FIELDS[3],FIELDS[4]
,FIELDS[6],FIELDS[7],FIELDS[8],FIELDS[9],FIELDS[10],FIELDS[13]
};
// 租赁合同 连接了供应商信息的dataset
DataSet withSupplierDataSet = leaseContractBaseDataSet.join(supplierDataSet).on(FIELDS[10],SUPPLIER_FIELD[0])
.select(tempSelect,new String[]{SUPPLIER_FIELD[1],SUPPLIER_FIELD[2]}).finish();
StringBuilder payPlanSelectField = new StringBuilder();
payPlanSelectField.append(LEASE_PAY_PLAN_FILED[0]).append(", ")
.append(LEASE_PAY_PLAN_FILED[1]).append(" AS ").append(FIELDS[14]).append(", ")
.append(LEASE_PAY_PLAN_FILED[2]).append(" AS ").append(FIELDS[15]).append(", ")
.append(LEASE_PAY_PLAN_FILED[3]).append(" AS ").append(FIELDS[16]);
DataSet payPlanDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName()+LEASE_PAY_PLAN,LEASE_PAY_PLAN,
payPlanSelectField.toString(),null,null);
// 计算合同总金额
DataSet rentSumDataSet = payPlanDataSet.groupBy(new String[]{LEASE_PAY_PLAN_FILED[0]}).sum(FIELDS[14]).finish();
// 实际已付金额
DataSet realPaySumDataSet = payPlanDataSet.groupBy(new String[]{LEASE_PAY_PLAN_FILED[0]}).sum(FIELDS[15]).finish();
// 实际未付金额
DataSet realunPaySumDataSet = payPlanDataSet.groupBy(new String[]{LEASE_PAY_PLAN_FILED[0]}).sum(FIELDS[16]).finish();
String[] s1 = addSelectField(tempSelect,SUPPLIER_FIELD[1],SUPPLIER_FIELD[2]);
String[] s2 = addSelectField(s1,FIELDS[14]);
String[] s3 = addSelectField(s2,FIELDS[15]);
// 拼接了合同金额 的 租赁合同 dataset
DataSet leaseContractDataSet =withSupplierDataSet.join(rentSumDataSet).on(LEASE_CONTRACT_FILED[0],LEASE_PAY_PLAN_FILED[0])
.select(s1,new String[]{FIELDS[14]}).finish()
.join(realPaySumDataSet).on(LEASE_CONTRACT_FILED[0],LEASE_PAY_PLAN_FILED[0])
.select(s2,new String[]{FIELDS[15]}).finish()
.join(realunPaySumDataSet).on(LEASE_CONTRACT_FILED[0],LEASE_PAY_PLAN_FILED[0])
.select(s3,new String[]{FIELDS[16]}).finish();
leaseContractDataSet = leaseContractDataSet.select(FIELDS[0],FIELDS[1],FIELDS[2],FIELDS[3],FIELDS[4]
,FIELDS[6],FIELDS[7],FIELDS[8],FIELDS[9],FIELDS[10],SUPPLIER_FIELD[1],SUPPLIER_FIELD[2],FIELDS[13]
,FIELDS[14],FIELDS[15],FIELDS[16]
);
FastFilter fastFilter = arg0.getFilter().getFastFilter();
StringBuilder fastExpr = new StringBuilder();
if (fastFilter!=null){
for (int j = 0; j < fastFilter.getFastFilter().size(); j++) {
Map<String, List<Object>> l = fastFilter.getFastFilter().get(j);
List<Object> prop = l.get("Value");
fastExpr.append(" ( ");
for (int i = 0; i < prop.size(); i++) {
Object o = prop.get(i);
String value = String.valueOf(o);
fastExpr.append("zrt_connumber like '%" + value + "%' ");
if (i != prop.size() - 1) {
fastExpr.append(" OR ");
}
}
fastExpr.append(" ) ");
if (j!=fastFilter.getFastFilter().size()-1){
fastExpr.append(" AND ");
}
}
}
FilterInfo f = arg0.getFilter();
DataSet result = comContractDataSet.union(leaseContractDataSet);
StringBuilder filterExpr = new StringBuilder();
List<FilterItemInfo> items = f.getFilterItems();
for (int i = 0; i < items.size(); i++) {
FilterItemInfo item = items.get(i);
String expr = parseExpr(item);
filterExpr.append(expr);
if (i!=items.size()-1){
filterExpr.append(" AND ");
}
}
// result = result.filter("zrt_connumber like %0001% ");
if(fastFilter!=null && StringUtils.isNotBlank(fastExpr) && filterExpr!=null && StringUtils.isNotBlank(filterExpr)){
System.out.println("过滤条件(方案查询和快速查询)："+filterExpr.append(" AND ").append(fastExpr).toString());
result = result.filter("("+filterExpr.append(") AND (").append(fastExpr).append(")").toString());
}else if (filterExpr!=null && StringUtils.isNotBlank(filterExpr)){
System.out.println("过滤条件(只有方案查询)："+filterExpr);
result = result.filter(filterExpr.toString());
}else if(fastFilter!=null && StringUtils.isNotBlank(fastExpr) ){
System.out.println("过滤条件(只有快速过滤)："+fastExpr);
result = result.filter(fastExpr.toString());
}
return result;
}
/**
* 向数组中增加元素
* @param selectField
* @param field
* @return
*/
public String[] addSelectField(String[] selectField,String ... field){
List<String> list = new ArrayList<>();
list.addAll(Arrays.asList(selectField));
for(String f :field){
list.add(f);
}
return list.toArray(new String[list.size()]);
}
public String parseExpr(FilterItemInfo filterItemInfo){
String expr="";
String filterProp = filterItemInfo.getPropName();
DataType dataType = filer_type_mapping.get(filterProp);
String repProp = mapping.get(filterProp);
if(dataType.equals(DataType.LongType)||dataType.equals(DataType.IntegerType)||dataType.equals(DataType.BigDecimalType)){
if (filterItemInfo.getCompareType().equals("IN")){
String filterItemInfoValue = filterItemInfo.getValue().toString();
filterItemInfoValue = filterItemInfoValue.replace("[","(");
filterItemInfoValue = filterItemInfoValue.replace("]",")");
return repProp+" "+filterItemInfo.getCompareType()+" "+filterItemInfoValue +" ";
}
return repProp+" "+filterItemInfo.getCompareType()+" "+filterItemInfo.getValue() +" ";
}else if(dataType.equals(DataType.StringType)){
if (filterItemInfo.getCompareType().equals("IN")){
String filterItemInfoValue = filterItemInfo.getValue().toString();
filterItemInfoValue = filterItemInfoValue.replace("[","('");
filterItemInfoValue = filterItemInfoValue.replace("]","')");
filterItemInfoValue = filterItemInfoValue.replaceAll(",","','");
filterItemInfoValue = filterItemInfoValue.replaceAll(" ","");
return repProp+" "+filterItemInfo.getCompareType()+" "+filterItemInfoValue +" ";
}
return repProp+" "+filterItemInfo.getCompareType()+" '"+filterItemInfo.getValue() +"' ";
}else if(dataType.equals(DataType.DateType)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return repProp+" "+filterItemInfo.getCompareType()+" TO_DATE(\""+sdf.format(filterItemInfo.getDate())+"\",\"yyyy-MM-dd\" ) ";
}
return expr;
}
/**
* @Author fk
* 获取供应商 默认银行 及 账户信息
* @return supplierDataSet
*/
public DataSet getSupplierDataSet(){
// 构建供应商银行信息 dataset
Collection<Object[]> coll = new ArrayList<Object[]>();
RowMeta rowMeta = RowMetaFactory.createRowMeta(SUPPLIER_FIELD, SUPPLIER_FIELD_DATATYPES);
CollectionInput inputs = new CollectionInput(rowMeta, coll);
// 查询供应商信息
DynamicObject[] suppliers = BusinessDataServiceHelper.load(SUPPLIER,"id,number,name,entry_bank.bankaccount,entry_bank.accountname,entry_bank.bank,entry_bank.isdefault_bank",null);
for (int i = 0; i < suppliers.length; i++) {
int flag = 0;
List<Object> list = new ArrayList<>();
list.add(suppliers[i].get("id"));
DynamicObjectCollection bankcoll = suppliers[i].getDynamicObjectCollection("entry_bank");
for (DynamicObject o:bankcoll){
if (o.getBoolean("isdefault_bank")){
String id = o.getDynamicObject("bank").getString("id");
list.add(Long.valueOf(id));
list.add(o.getString("bankaccount"));
flag = 1;
break;
}
}
if(flag == 0){
// 如果没有关联的银行信息 默认赋值为零 和 空
list.add((long)0);
list.add("");
}
coll.add(list.toArray(new Object[list.size()]));
}
DataSet supplierDataSet = Algo.create(this.getClass().getName()).createDataSet(inputs);
return supplierDataSet;
}
}ContractReportPlugin.zip(4.01KB)
推荐阅读