package kded.online.demo;
import kd.bos.algo.*;
import kd.bos.algo.input.CollectionInput;
import kd.bos.dataentity.entity.LocaleString;
import kd.bos.entity.report.*;
import kd.bos.servicehelper.QueryServiceHelper;
import java.util.*;
//合计行 须在报表界面规则设置 shkd_00 =‘合计’ 设置 背景色为黄色 前景色为红色
public class LYReportListDataPlugin extends AbstractReportListDataPlugin {
/**
* 报表所有字段集合
*/
private String[] FIELDS=new String[]{"shkd_00","shkd_01","shkd_02","shkd_03","shkd_04","shkd_05","shkd_06","shkd_07","shkd_08","shkd_09","shkd_10","shkd_11","shkd_12","shkd_year"};
/**
*报表所有字段的数据类型集合
*/
public DataType[] DATATYPES=new DataType[]{DataType.StringType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,
DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType,DataType.IntegerType};
private String[] Hiden=new String[]{"shkd_01","shkd_02","shkd_03","shkd_04","shkd_05","shkd_06","shkd_07","shkd_08","shkd_09","shkd_10","shkd_11","shkd_12"};
@Override
public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
//获取创建时间月份
String timeMonth="Month(createtime) as shkd_12";
//把查询字段的创建时间改为月份
String fields="billno,osa6_entryentity.osa6_basedatafield1.name shkd_00,osa6_entryentity.osa6_qtyfield1 shkd_year,"+timeMonth;
//查询领用单据
DataSet dataSet= QueryServiceHelper.queryDataSet(this.getClass().getName(), "osa6_bgscg_lingyongdan",
fields, null, null);
//分组查询 group 物品 月份 数量 result:物品 月份
DataSet group=dataSet.executeSql("select shkd_00,shkd_year,shkd_12 "+" group by shkd_00,shkd_12,shkd_year");
DataSet result=group.executeSql("select shkd_00, shkd_12,sum(shkd_year) as shkd_year"+" group by shkd_00,shkd_12");
//行转列 根据创建时间的不同月份创建不同列 把实际领取数量复制给月份列
DataSet copy=result.copy();//复制一份数据
Map<String,Object[]>values=new HashMap<>();//定义一个map集合
Object[] tempData=null; //object数组
for (Row row:copy){//遍历数据
String name=row.getString("shkd_00");
tempData=values.getOrDefault(name,new Object[FIELDS.length]);
tempData[0]=row.getString("shkd_00");
Integer i=row.getInteger("shkd_12");//获取 月份
//把Hiden数组ArrayList化 便于移除String元素
List<String> list = new ArrayList<>(Arrays.asList(Hiden));
if (i<10) {
list.remove("shkd_0"+i);//移除 hidden中的月份列
}else {
list.remove("shkd_"+i);
}
Hiden = list.toArray(new String[0]);
tempData[i]=row.getInteger("shkd_year");
tempData[13]=row.getInteger("shkd_year");
values.put(name,tempData);
}
Collection<Object[]> coll=values.values();
RowMeta rowMeta= RowMetaFactory.createRowMeta(FIELDS,DATATYPES);
CollectionInput input=new CollectionInput(rowMeta,coll);
DataSet resultDataSet= Algo.create(this.getClass().getName()).createDataSet(input);
//合计
DataSet dataSet2 =resultDataSet.groupBy().sum("shkd_01").sum("shkd_02").sum("shkd_03").sum("shkd_04").sum("shkd_05").sum("shkd_06").sum("shkd_07").sum("shkd_08").sum("shkd_09").sum("shkd_10").sum("shkd_11").sum("shkd_12").sum("shkd_year").finish();
DataSet ds=dataSet2.addField("'合计'","shkd_00");
//重新查询 使ds字段与resultDataSet字段顺序一致
ds=ds.select("shkd_00,shkd_01,shkd_02,shkd_03,shkd_04,shkd_05,shkd_06,shkd_07,shkd_08,shkd_09,shkd_10,shkd_11,shkd_12,shkd_year");
return resultDataSet.union(ds);
}
@Override
public List<AbstractReportColumn> getColumns(List<AbstractReportColumn> columns) throws Throwable {
columns.add(createReportColumn("shkd_00","text","物品名称"));
columns.add(createReportColumn("shkd_01","text","一月"));
columns.add(createReportColumn("shkd_02","text","二月"));
columns.add(createReportColumn("shkd_03","text","三月"));
columns.add(createReportColumn("shkd_04","text","四月"));
columns.add(createReportColumn("shkd_05","text","五月"));
columns.add(createReportColumn("shkd_06","text","六月"));
columns.add(createReportColumn("shkd_07","text","七月"));
columns.add(createReportColumn("shkd_08","text","八月"));
columns.add(createReportColumn("shkd_09","text","九月"));
columns.add(createReportColumn("shkd_10","text","十月"));
columns.add(createReportColumn("shkd_11","text","十一月"));
//columns.set(0, createReportColumn("osa6_entryentity.osa6_basedatafield1.name", "text", "物品名称"));
columns.add(createReportColumn("shkd_12","text","十二月"));
columns.add(createReportColumn("shkd_year","text","年"));
//隐藏列 测试
for(int i = 0; i < columns.size(); i ++) {
ReportColumn rColumn = (ReportColumn) columns.get(i);
String key = rColumn.getFieldKey();
for (int j=0;j< Hiden.length;j++){
if(key.equals(Hiden[j])) {
rColumn.setHide(true);
columns.set(i, rColumn);
}
}
}
return columns;
}
public ReportColumn createReportColumn(String fieldKey,String fieldType,String caption){
ReportColumn column = new ReportColumn();
column.setFieldKey(fieldKey);
column.setFieldType(fieldType);
column.setCaption(new LocaleString(caption));
return column;
}
}
推荐阅读