金蝶按树型组织汇总数据扩展报表实现总结

最新推荐文章于 2021-05-23 21:38:35 发布
最新推荐文章于 2021-05-23 21:38:35 发布
阅读量1.7k 收藏 3
点赞数
分类专栏： sql EAS　BOS
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fenyu8/article/details/49336039
版权
EAS　BOS 同时被 2 个专栏收录
128 篇文章 5 订阅
订阅专栏
sql
27 篇文章 1 订阅
订阅专栏

1.新装java数据集


2.java数据集的实现

   学习：

   1.客户端执行sql的方法

    2.递归实现遍历组织树的方法

    3.count,sum与distinct,case when then的混合运用进行复杂数据的统计

    4.按树形组织汇总统计的实现方案

    5.如何构建IRowSet对象

    6.代码执行言的方法

package com.kingdee.eas.vk.weixun.rpts;

import java.awt.Window;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.kingdee.bos.dao.query.SQLExecutorFactory;
import com.kingdee.eas.base.form.SqlExecutorFacade;
import com.kingdee.eas.base.multiapprove.SqlFacadeFactory;
import com.kingdee.eas.dynbusiness.util.SqlBuilderHelper;
import com.kingdee.eas.rpts.ctrlsqldesign.model.ExtDBUtil;
import com.kingdee.eas.rpts.ctrlsqldesign.param.AbstractJavaDataSet;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.jdbc.rowset.impl.ColInfo;
import com.kingdee.jdbc.rowset.impl.DynamicRowSet;

public class BizAccountBillFromPhoneRptDataSet extends AbstractJavaDataSet
{
	@Override
	public IRowSet[] getCustomRowSet(Window parent, String otherDataCenter) throws Exception
	{
		return getCustomRowSet1(parent,otherDataCenter);
	}
	
	private IRowSet[] getCustomRowSet1(Window parent, String otherDataCenter) throws Exception
	{
		//存放数据集 
		IRowSet[] rowsets = new IRowSet[1];
		//根据key得到参数
		String companyLongNumber = (String)this.filterParamMap.get("companyLongNumber");
		String level = (String)this.filterParamMap.get("level");
		String start = (String)this.filterParamMap.get("start");
		String end = (String)this.filterParamMap.get("end");
		
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT company.fid         companyid,				\n");
		sql.append("	       company.fname_l2    companyname,				\n");
		sql.append("	       company.fnumber     companynumber,				\n");
		sql.append("	       company.flongnumber companylongnumber,				\n");
		sql.append("	       company.flevel      alevel,				\n");
		sql.append("	       parentcompany.flongnumber parentlongnumber				\n");
		sql.append("	FROM   t_org_company company				\n");
		sql.append("	left join   t_org_company parentcompany				\n");
		sql.append("	on   company.fparentid=parentcompany.fid				\n");
		sql.append("	WHERE  company.flongnumber LIKE '"+companyLongNumber+"%'				\n");
		sql.append("	       AND company.flevel <= "+level+"				\n");
		sql.append("	ORDER BY company.flevel,company.fnumber				\n");
		IRowSet rs = ExtDBUtil.executeQuery(null,sql.toString(),otherDataCenter);
		Map<String,List<Map<String,String>>> companyMap = new HashMap<String,List<Map<String,String>>>();
		Map<String,String> rootCompany = null;
		while(rs.next())
		{
			Map<String,String> company = new HashMap<String,String>();
			String longNumber = rs.getString("companylongnumber");
			if(longNumber==null)
			{
				continue;
			}
			company.put("companyLongNumber", longNumber);
			company.put("companyName", rs.getString("companyname"));
			company.put("level", rs.getString("alevel"));
			
			String parentLongNumber = rs.getString("parentlongnumber");
			if(parentLongNumber!=null)
			{
				List<Map<String, String>> companyList = companyMap.get(parentLongNumber);
				if(companyList==null)
				{
					companyList = new ArrayList<Map<String,String>>();
					companyMap.put(parentLongNumber, companyList);
				}
				companyList.add(company);
			}

			if(longNumber.equals(companyLongNumber))
			{
				rootCompany = company;
			}
		}
		List<Map<String,String>> companyLinkedList = new LinkedList<Map<String,String>>();
		companyLinkedList.add(rootCompany);
		getCompanyList(rootCompany,companyLinkedList,companyMap);
		
		StringBuffer sql1 = new StringBuffer();
		sql1.append("	/*dialect*/ 					\n");//代码执行言的方法
		sql1.append("	create or replace view view_bizaccountbill as				\n");
		sql1.append("	SELECT company.flongnumber   companylongnumber,				\n");
		sql1.append("	       bill.fid              billid,				\n");
		sql1.append("	       bill.cfisfromphone    isfromphone,				\n");
		sql1.append("	       bill.fcreatorid       creatorid,				\n");
		sql1.append("	       bill.cfisimgover      isimgover,				\n");
		sql1.append("	       exptype.cfisindamount isindamount,				\n");
		sql1.append("	       exptype.fname_l2      expname,				\n");
		sql1.append("	       exptype.cfexpenseclass      expenseclass,				\n");
		sql1.append("	       entry.famount         amount				\n");
		sql1.append("	FROM   t_bc_bizAccountBill bill				\n");
		sql1.append("	       LEFT JOIN t_org_company company				\n");
		sql1.append("	              ON bill.fcompanyid = company.fid				\n");
		sql1.append("	       LEFT JOIN T_BC_BizAccountBillEntry entry				\n");
		sql1.append("	              ON entry.fbillid = bill.fid				\n");
		sql1.append("	       LEFT JOIN T_BC_ExpenseType exptype				\n");
		sql1.append("	              ON entry.fExpenseTypeid = exptype.fid				\n");
		sql1.append("	WHERE  1 = 1				\n");
		sql1.append("	       AND company.flongnumber LIKE '"+companyLongNumber+"%'				\n");
		sql1.append("	       AND bill.fcreatetime >= {ts '"+start+"'}				\n");
		sql1.append("	       AND bill.fcreatetime <= {ts '"+end+"'}				\n");
		//客户端执行sql方法
		SqlFacadeFactory.getRemoteInstance().executeSql(sql1.toString());
		
		//count,sum与distinct,case when then的混合运用进行复杂数据的统计
		StringBuffer sql2 = new StringBuffer();
		sql2.append("	select	\n");
		sql2.append("	    companylongnumber,	\n");
		sql2.append("	    count(distinct case when isfromphone=1 then creatorid else null end) phoneqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 then creatorid else null end) fullqty,	\n");
		sql2.append("	    count(distinct case when isfromphone=1 then billid else null end) phonebillqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 then billid else null end) billqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 and isimgover=1 then billid else null end) imgoverqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 and expenseclass=1 then billid else null end) carfeeqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 and expenseclass=2 then billid else null end) nocarfeeqty,	\n");
		sql2.append("	    count(distinct case when isindamount=1 and expenseclass=3 then billid else null end) tongxunfeeqty,	\n");
		sql2.append("	    sum(case when isindamount=1 and expenseclass=1 then amount else 0 end) carfeeamount,	\n");
		sql2.append("	    sum(case when isindamount=1 and expenseclass=2 then amount else 0 end) nocarfeeamount,	\n");
		sql2.append("	    sum(case when isindamount=1 and expenseclass=3 then amount else 0 end)tongxunfeeamount	\n");
		sql2.append("	from view_bizaccountbill	    \n");
		sql2.append("	group by companylongnumber   	\n");
		IRowSet rs4 = ExtDBUtil.executeQuery(null,sql2.toString(),otherDataCenter);
		Map<String,Map<String,String>> rows = new HashMap<String,Map<String,String>>();
		while(rs4.next())
		{
			String acompanyLongNumber = rs4.getString("companylongnumber");
			int phoneQty = rs4.getInt("phoneqty");
			int fullQty = rs4.getInt("fullqty");
			int phoneBillQty = rs4.getInt("phonebillqty");
			int billQty = rs4.getInt("billqty");
			int imgOverQty = rs4.getInt("imgoverqty");
			int carfeeQty = rs4.getInt("carfeeqty");
			int nocarfeeQty = rs4.getInt("nocarfeeqty");
			int tongxunfeeQty = rs4.getInt("tongxunfeeqty");
			BigDecimal carfeeAmount = rs4.getBigDecimal("carfeeamount");
			BigDecimal nocarfeeAmount = rs4.getBigDecimal("nocarfeeamount");
			BigDecimal tongxunfeeAmount = rs4.getBigDecimal("tongxunfeeamount");
			
			Map<String,String> row = new HashMap<String,String>();
			row.put("phoneQty", String.valueOf(phoneQty));
			row.put("fullQty", String.valueOf(fullQty));
			row.put("phoneBillQty", String.valueOf(phoneBillQty));
			row.put("billQty", String.valueOf(billQty));
			row.put("imgOverQty", String.valueOf(imgOverQty));
			row.put("carfeeQty", String.valueOf(carfeeQty));
			row.put("nocarfeeQty", String.valueOf(nocarfeeQty));
			row.put("tongxunfeeQty", String.valueOf(tongxunfeeQty));
			row.put("carfeeAmount", carfeeAmount.toString());
			row.put("nocarfeeAmount", nocarfeeAmount.toString());
			row.put("tongxunfeeAmount", tongxunfeeAmount.toString());
			
			rows.put(acompanyLongNumber, row);
		}

		//如何构建IRowSet对象
		DynamicRowSet drs = new DynamicRowSet(14); 
        drs.setColInfo(1, this.createColInfo(12,"companyname",1)); 
        drs.setColInfo(2, this.createColInfo(12,"phoneqty",1)); 
        drs.setColInfo(3, this.createColInfo(12,"fullqty",1)); 
        drs.setColInfo(4, this.createColInfo(12,"phonerate",1)); 
        drs.setColInfo(5, this.createColInfo(12,"phonebillqty",1)); 
        drs.setColInfo(6, this.createColInfo(12,"billqty",1)); 
        drs.setColInfo(7, this.createColInfo(12,"phonerate1",1)); 
        drs.setColInfo(8, this.createColInfo(12,"imgoverqty",1)); 
        drs.setColInfo(9, this.createColInfo(12,"carfeeqty",1)); 
        drs.setColInfo(10, this.createColInfo(12,"carfeeamount",1)); 
        drs.setColInfo(11, this.createColInfo(12,"nocarfeeqty",1)); 
        drs.setColInfo(12, this.createColInfo(12,"nocarfeeamount",1)); 
        drs.setColInfo(13, this.createColInfo(12,"tongxunfeeqty",1)); 
        drs.setColInfo(14, this.createColInfo(12,"tongxunfeeamount",1)); 
        
        String rootLevel = rootCompany.get("level");
		for(int i=0,size=companyLinkedList.size();i<size;i++)
		{
			Map<String, String> company = companyLinkedList.get(i);
	        String longNumber = company.get("companyLongNumber");
	        String companyName = company.get("companyName");
	        String alevel = company.get("level");
	        
	        
			int phoneQty = 0;
			int fullQty = 0;
			int phoneBillQty = 0;
			int billQty = 0;
			int imgOverQty = 0;
			int carfeeQty = 0;
			int nocarfeeQty = 0;
			int tongxunfeeQty = 0;
			BigDecimal carfeeAmount = BigDecimal.ZERO;
			BigDecimal nocarfeeAmount = BigDecimal.ZERO;
			BigDecimal tongxunfeeAmount = BigDecimal.ZERO;
	        for(String key:rows.keySet())
	        {
	        	if(key.startsWith(longNumber+"!") || key.equals(longNumber))
	        	{
	        		Map<String, String> row = rows.get(key);
	        		phoneQty = phoneQty+Integer.valueOf(row.get("phoneQty"));
	        		fullQty = fullQty+Integer.valueOf(row.get("fullQty"));
	        		phoneBillQty = phoneBillQty+Integer.valueOf(row.get("phoneBillQty"));
	        		billQty = billQty+Integer.valueOf(row.get("billQty"));
	        		imgOverQty = imgOverQty+Integer.valueOf(row.get("imgOverQty"));
	        		carfeeQty = carfeeQty+Integer.valueOf(row.get("carfeeQty"));
	        		nocarfeeQty = nocarfeeQty+Integer.valueOf(row.get("nocarfeeQty"));
	        		tongxunfeeQty = tongxunfeeQty+Integer.valueOf(row.get("tongxunfeeQty"));
	        		carfeeAmount = carfeeAmount.add(new BigDecimal(row.get("carfeeAmount")));
	        		nocarfeeAmount = nocarfeeAmount.add(new BigDecimal(row.get("nocarfeeAmount")));
	        		tongxunfeeAmount = tongxunfeeAmount.add(new BigDecimal(row.get("tongxunfeeAmount")));
	        	}
	        }
	        
	        if(billQty==0)
	        {
	        	continue;
	        }
	        
	        drs.moveToInsertRow();
	        int j = Integer.valueOf(alevel)-Integer.valueOf(rootLevel);
	        String acompanyName = companyName;
	        for(int m=0;m<j;m++)
	        {
	        	acompanyName = "    "+acompanyName;
	        }
	        drs.updateString("companyname", acompanyName);
	        drs.updateString("phoneqty", String.valueOf(phoneQty)); 
	        drs.updateString("fullqty", String.valueOf(fullQty)); 
	        BigDecimal phonerate = BigDecimal.ZERO;
	        if(fullQty!=0)
	        {
	        	phonerate = new BigDecimal(phoneQty*100).divide(new BigDecimal(fullQty), 2, RoundingMode.HALF_DOWN);
	        }
	        drs.updateString("phonerate", phonerate.toString()); 
	        drs.updateString("phonebillqty", String.valueOf(phoneBillQty)); 
	        drs.updateString("billqty", String.valueOf(billQty)); 
	        BigDecimal phonerate1 = BigDecimal.ZERO;
	        if(billQty!=0)
	        {
	        	phonerate1 = new BigDecimal(phoneBillQty*100).divide(new BigDecimal(billQty), 2, RoundingMode.HALF_DOWN);
	        }
	        drs.updateString("phonerate1", phonerate1.toString()); 
	        drs.updateString("imgoverqty", String.valueOf(imgOverQty)); 
	        drs.updateString("carfeeqty", String.valueOf(carfeeQty)); 
	        drs.updateString("carfeeamount", carfeeAmount.toString()); 
	        drs.updateString("nocarfeeqty", String.valueOf(nocarfeeQty)); 
	        drs.updateString("nocarfeeamount", nocarfeeAmount.toString()); 
	        drs.updateString("tongxunfeeqty", String.valueOf(tongxunfeeQty)); 
	        drs.updateString("tongxunfeeamount", tongxunfeeAmount.toString()); 
	        
	        drs.insertRow();
		}
		
        drs.beforeFirst();
		rowsets[0] = drs;
		return rowsets;
	}
	
	@Override
	public Map getOutputParam() throws Exception
	{
		Map param = new LinkedHashMap();
		param.put("companyname", "公司");
		param.put("phoneqty", "手机报销人数量");
		param.put("fullqty", "报销人数量");
		param.put("phonerate", "手机报销占比");
		param.put("phonebillqty", "手机报销单数量");
		param.put("billqty", "报销单数量");
		param.put("phonerate1", "手机报销占比");
		param.put("imgoverqty", "影像超期数量");
		param.put("carfeeqty", "车辆费手机报销单数量");
		param.put("carfeeamount", "车辆费手机报销单金额");
		param.put("nocarfeeqty", "交通费手机报销单数量");
		param.put("nocarfeeamount", "交通费手机报销单金额");
		param.put("tongxunfeeqty", "通讯费手机报销单数量");
		param.put("tongxunfeeamount", "通讯费手机报销单金额");
		return param;
	}

	@Override
	public void setFilterParam(Map filterParamMap)
	{
		this.filterParamMap = (HashMap)filterParamMap; 
	}
	
	private ColInfo createColInfo(int colType,String columnName,int nullable)
	{
        ColInfo ci = new ColInfo();  
        ci.colType = colType;  
        ci.columnName = columnName;  
        ci.nullable = nullable;  
        return ci;
	}
	
	/**
	 * 递归方法遍历组织树
	 * 版本：EAS7.5
	 * 作者(修改人)：haohan
	 * 修改时间：2015-10-22
	 *		<p>
	 * 描 述：
	 * @param currCompany
	 * @param companyLinkedList
	 * @param companyMap
	 */
	private void getCompanyList(Map<String,String> currCompany,List<Map<String,String>> companyLinkedList,Map<String,List<Map<String,String>>> companyMap)
	{
		if(companyLinkedList==null)
		{
			companyLinkedList = new LinkedList<Map<String,String>>();
		}
		String currLongNumber = currCompany.get("companyLongNumber");
		List<Map<String, String>> companyList = companyMap.get(currLongNumber);
		if(companyList!=null && companyList.size()>0)
		{
			for(int i=0,size=companyList.size();i<size;i++)
			{
				Map<String, String> company = companyList.get(i);
				companyLinkedList.add(company);
				getCompanyList(company,companyLinkedList,companyMap);
			}
		}
	}
}


fenyu8
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
金蝶K3 Cloud 账表服务取数插件示例代码
09-28
账表的服务端取数插件，是通过插件组装账表取数Sql指令，把取数结果返回给平台账表引擎，平台将取数结果绑定到账表页面的过程，简单说，平台在账表开发过程中，只负责把插件取到的账表数据绑定到设计好的账表模型上，取数逻辑由账表服务端插件完成 开发过程中，有几个重要模型
【转】【金蝶K3Cloud】 树形账表
weixin_30270889的博客
10-22 319
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.ComponentModel; using System.ServiceModel; using System.Threading.Tasks; using System.Data; ...
金蝶EAS，扩展报表，Java数据集代码示例
金蝶EAS开发专栏
06-07 2623
业务场景：业务相对复杂时，无法直接通过SQL直接查询出相应的数据集。可以使用Java数据集实现。 Java数据集可以针对查询结果进行相应的加工，向前端扩展报表提供符合要求的结果集。 package com.kingdee.eas.custom.sdic.rpt; import java.util.Map; import java.awt.Window; import com.kingdee.
统计学习（七）：基于树的方法
qq_52737544的博客
05-23 955
本文主要介绍了决策树的基本原理；装袋法、随机森林、提升法等内容
四、DataFrame和DataSet的使用案例
xuehuagongzi000的博客
05-11 407
import org.apache.spark.sql.{DataFrame, SQLContext} import org.apache.spark.{SparkConf, SparkContext} //功能一：采用RDD+case class 的方式来构造dataSets，实现某个电影中男性女性中不同年龄的有多少人 case class Rating(var userId:String,var movieId:String,var Rating:Double,var TimeStamp:Stri.
树型框功能扩展模块.zip易语言项目例子源码下载
03-22
- 可以实时从数据库或其他数据源获取信息，更新树型框的内容，实现数据可视化。 4. **自定义扩展与美化** - 开发者可以扩展树型框的功能，比如添加右键菜单，实现节点的拖放操作，或者自定义节点的渲染方式。 - ...
易语言树型框功能扩展模块
07-18
2. **拖放支持**：用户可以方便地通过拖放操作移动节点，这在处理大量数据组织时非常有用。 3. **节点状态记录**：扩展模块可能会提供保存和恢复节点展开/折叠状态的功能，确保用户在下次打开应用时能保持之前的...
易语言树型框功能扩展
07-22
易语言是一种专为中国人设计的编程语言，它以简体中文作为编程语句，降低了编程的门槛，使得更多非计算机专业的人也...通过学习和应用这些扩展功能，开发者可以更好地利用树型框来展示和管理层次化数据，提升用户体验。
树型数据结构中递归算法的实现.pdf
08-07
#资源达人分享计划#
自定义套打元数据
giianhui的专栏
10-26 1969
public void actionPrintPreview_actionPerformed(ActionEvent e) throws Exception { checkSelected(); ArrayList idList = super.getSelectedIdValues(); if ((idList == null) || (idList.size
java irowset行数,Java CloneableRecord.put方法代码示例
weixin_39741101的博客
03-12 123
import com.taobao.tddl.executor.record.CloneableRecord; //导入方法依赖的package包/类@SuppressWarnings("rawtypes")@Overrideprotected int executePut(ExecutionContext executionContext, IPut put, ITable table, Ind...
金蝶bos工具类方法
MZ199290的博客
04-22 2597
package com.kingdee.eas.custom; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.Prin...
JDBC 操作插入表出现javax.sql.rowset.serial.SerialBlob cannot be cast to oracle.sql.BLOB
weixin_30852451的博客
09-16 1795
/** * 接口方法 */ public void excuteInputDB(SynchServiceConfig synchServiceConfig) throws Exception { try { // if(!"".equals(queryQdParaWayinfo())) // ...
java irowset行数,自定义套打元数据 - giianhui的个人页面 - OSCHINA - 中文开源技术交流社区...
weixin_29963537的博客
03-12 385
public void actionPrintPreview_actionPerformed(ActionEvent e)throws Exception{checkSelected();ArrayList idList = super.getSelectedIdValues();if ((idList == null) || (idList.size() == 0) || (getTDFileN...
凭证套打类，注意如何更新IRowSet结果集。
fenyu8的专栏
09-28 2051
/*jadclipse*/// Decompiled by Jad v1.5.8g. Copyright 2001 Pavel Kouznetsov. package com.kingdee.eas.fi.gl.client; import com.kingdee.bos.BOSException; import com.kingdee.bos.ctrl.kdf.data.datasource
KDTable 绑定query
hujinlong的博客
07-27 224
[code="java"] // 创建列和表头 ... // 指定虚模式分页模式 table.getDataRequestManager().setDataRequestMode(KDTDataRequestManager.VIRTUAL_MODE_PAGE); // 设置总行数为500，可以不指定总行数 table.setRowCount(500); // ...
RowSet自动填充到界面的Table里面
吕飞技术专栏
05-17 1173
RowSet自动填充到界面的Table里面 注意：查询字段和界面的列要一一对应，并且数据类型匹配，该方法适合报表取数 public static void fillDataOnTableByColName(KDTable table, IRowSet rowSet) throws Exception{ int rsColCnt = rowSet.getMetaData().getColumn
java irowset行数,eas之执行sql的方式
weixin_39993301的博客
03-12 550
客户端:1. 有返回集合：查询//查询出DB中所有该字段的值，与其进行比较，若有相同的则报错String sql="select CFWuliaoCode from CT_JC_TestWuliao";//sql执行者接口，sql执行者工厂对象，获取远程实例，sql语句ISQLExecutor isql=SQLExecutorFactory.getRemoteInstance(sql);IRowS...
数据结构之查找课件(与“查找”有关文档共75张).pptx
最新发布
11-30
本资源是一份详尽的数据结构课程讲义，主要聚焦于“查找”相关的知识点。课程涵盖以下几个主要部分： 1. **查找的基本概念**： 查找操作在计算机科学中非常重要，它是指在一组有序的数据中，根据特定的关键字值...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值