金蝶EAS获取PBC工具类

package com.kingdee.eas.fi.gr.cslrpt.rpt;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.dao.query.SQLExecutorFactory;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.custom.jinmao.bean.RptData;
import com.kingdee.eas.custom.jinmao.bean.RptItem;
import com.kingdee.eas.custom.jinmao.util.DateUtil;
import com.kingdee.eas.custom.jinmao.util.LogUtil;
import com.kingdee.eas.custom.jinmao.util.MathUtil;
import com.kingdee.eas.custom.jinmao.util.SqlBuffer;
import com.kingdee.eas.custom.jinmao.util.Utils;
import com.kingdee.eas.custom.jinmao.util.Verify;
import com.kingdee.jdbc.rowset.IRowSet;

/**
 * 获取PBC数
 * @author skf
 */
public class RptPBCTool {
	
	/**
	 * 获取合并报表数据（PBC数据）
	 * @param ctx 上下文
	 * @param scope 合并范围编码
	 * @param year,month 年月
	 * @param element 取数类型
	 * @param itemNumber 报表项目编码
	 * @param orgUnitId 组织ID
	 * @return RptData
	 * 
	 */
	public static RptData query(String scope, int year, int month,String element, String[] itemNumber, String... orgUnitId) throws BOSException, EASBizException {

		if(Verify.isNull(itemNumber)){
			Utils.throwing("报表项目编码不能为空！");
		}
		if(Verify.isNull(orgUnitId)){
			Utils.throwing("组织主键（orgUnitId）不能为空！");
		}
		long start = DateUtil.currentTime();
		Map<String, RptItem> itemMapp = getItemMap(itemNumber); //获取项目对照
		LogUtil.info("报表项目对照关系：", itemMapp);
		RptData values = getRptDatas(scope, year,month, element, itemMapp, orgUnitId); //获取报表数据（数值）
		long time = DateUtil.executeTime(start);
		LogUtil.info("合并报表取数完成，耗时（秒）：", time);
		
		return values;
		
	}
	
	/**
	 * 获取报表项目对照关系
	 * 查询报表项目对应的数值所在的具体映射表和映射字段
	 * 返回值定义：KEY = 报表项目编码，VALUE = 报表项目业务对象
	 * @param ctx 上下文
	 * @param number 报表项目编码
	 * @return Map
	 * 
	 */
	private static Map<String, RptItem> getItemMap(String... number) throws BOSException {
		
		//KEY = 报表项目编码，VALUE = 报表项目业务对象
		Map<String, RptItem> map = new HashMap<String, RptItem>();
		SqlBuffer buffer = SqlBuffer.instance();
		String filters = Utils.filters(number); //过滤字符串
		buffer.add("SELECT DISTINCT item.FID itemId, item.FNumber itemNumber,").ln();
		buffer.add("mapp.FTableName tableName, mapp.FFieldName fieldName").ln();
		buffer.add("FROM T_CSL_ItemFieldMapped mapp").ln();
		buffer.add("INNER JOIN T_CSL_RptItem item ON mapp.FItemID = item.FID").ln();
		buffer.add("WHERE item.FNumber IN (").add(filters).add(")");
		IRowSet rs = SQLExecutorFactory.getRemoteInstance(buffer.sql()).executeSQL();
		try {
			while(rs.next()){
				
				String itemId = rs.getString("itemId");
				String itemNumber = rs.getString("itemNumber");
				String tableName = rs.getString("tableName"); //映射表
				String fieldName = rs.getString("fieldName"); //映射字段
				RptItem item = new RptItem();
				item.setId(itemId);
				item.setNumber(itemNumber);
				item.setTableName(tableName);
				item.setFieldName(fieldName);
				map.put(itemNumber, item);
				
			}
		} catch (SQLException e) {
			throw new BOSException(e);
		}

		return map;
		
	}

	/**
	 * 查询合并报表数据，数据封装实现
	 * @param ctx 上下文
	 * @param scope 合并范围编码
	 * @param element 
	 * @param argsPeriod 会计期间ID或编码
	 * @param month2 
	 * @param itemMapp 报表项目
	 * @param orgUnitId 组织主键
	 * @return Map
	 * 
	 */
	private static RptData getRptDatas(String scope, int year, int month, String element, Map<String, RptItem> itemMapp, String... orgUnitId) throws BOSException, EASBizException {
		
		Set<String> items = itemMapp.keySet();
		Iterator<String> it = items.iterator();
		//KEY = 报表项目编码，VALUE = Map<String, BigDecimal>
		//VALUE中：KEY = 组织ID/编码，VALUE = 数值
		Map<String, Map<String, BigDecimal>> map = null;
		map = new HashMap<String, Map<String, BigDecimal>>(); //报表项目&报表数据
		while(it.hasNext()){
			
			String number = it.next(); //报表项目编码
			RptItem rptItem = itemMapp.get(number);
			String tableName = rptItem.getTableName(); //对应表名
			String fieldName = rptItem.getFieldName(); //映射字段
			String sql = getQuerySql(tableName, fieldName,scope, year, month,element, orgUnitId); //拼装查询脚本
			IRowSet rs = SQLExecutorFactory.getRemoteInstance(sql).executeSQL();
			Map<String, BigDecimal> data = new HashMap<String, BigDecimal>(); //组织&数据
			
			try {
				while(rs.next()){
					
					BigDecimal value = rs.getBigDecimal("value");
					if(MathUtil.isZero(value)){
						continue;
					}
					String orgId = rs.getString("orgId"); //组织ID
					String orgNumber = rs.getString("orgNumber"); //组织编码
					data.put(orgId, value);
					data.put(orgNumber, value);
					
				}
			} catch (SQLException e) {
				throw new BOSException(e);
			}
			if(!data.isEmpty()){
				map.put(number, data);
			}
			
		}
		RptData values = new RptData(map);
		
		return values;
		
	}
	
	/**
	 * 获取查询脚本
	 * 用于查询单个报表项目在指定组织范围内指定取数类型对应数据
	 * @param tableName 映射表名
	 * @param fieldName 映射字段
	 * @param orgUnitId 组织主键
	 * @param element 
	 * @param month 
	 * @param year 
	 * @param scope 
	 * @return String
	 * 
	 */
	private static String getQuerySql(String tableName, String fieldName, String scope, int year, int month, String element,String... orgUnitId) {
		
		SqlBuffer buffer = SqlBuffer.instance();
		buffer.add("SELECT ").add(fieldName).add(" value, org.FID orgId,").ln(); //映射字段
		buffer.add("org.FNumber orgNumber, itemData.FDataSource source, dataEntry.FDataElement type,").ln();
		buffer.add("rpt.FConvertStatus conver").ln(); //调整状态（2019-11-11）
		buffer.add("FROM ").add(tableName).add(" temp").ln(); //映射表
		buffer.add("INNER JOIN T_Csl_ItemDataEntry dataEntry ON dataEntry.FID = temp.FID").ln();
		buffer.add("INNER JOIN T_CSL_DataElement  dataElement on dataElement.FINTERSEQ = dataEntry.FDATAELEMENT").ln();
		buffer.add("INNER JOIN T_Csl_ItemData itemData ON itemData.FID = dataEntry.FItemDataID").ln();
		buffer.add("INNER JOIN T_Csl_CslReport rpt ON itemData.FReportID = rpt.FID").ln(); //获取调整状态（2019-11-11）
		buffer.add("INNER JOIN T_Csl_RptReceived received ON received.FReportID = itemData.FReportID").ln();
		buffer.add("INNER JOIN T_ORG_Tree tree ON tree.fid = received.FOrgTreeID").ln();
		buffer.add("INNER JOIN T_Org_BaseUnit org ON itemData.FCompanyID = org.FID").ln();
		buffer.add("INNER JOIN T_BD_PERIOD period ON dataEntry.FYear = period.FPeriodYear").ln();
		buffer.add("AND dataEntry.FPeriod = period.FPeriodNumber").ln();
		buffer.add("WHERE tree.FNumber = '"+ scope +"'").ln(); //合并范围编码
		buffer.add("AND period.FPeriodYear = "+ year +" AND period.FPeriodNumber = "+ month +"").ln(); //年度、期间
		buffer.add("AND itemData.FIsAdjusted = 0").ln(); //0：调整前，1：调整分录，2：调整后
		buffer.add("AND itemData.FPeriodType = 3").ln(); //期间类型，3：月报
		buffer.add("AND itemData.FDataSource IN (1, 2, 3)").ln(); //1：个别数(个别)，2：汇总数，3：合并数
		buffer.add("AND dataEntry.FValueType = 1").ln(); //1：金额，2：数量，3：文本 (个别)
		//buffer.add("AND rpt.FCOMMITTEDSTATUS = 3").ln(); //1:未提交，2：提交，3：集团确认
		if(orgUnitId.length == 1){
			buffer.add("AND org.FID = '").add(orgUnitId[0]).add("'").ln(); //合并单元（财务组织）
		} else {
			buffer.add("AND org.FID IN (").add(Utils.filters(orgUnitId)).add(")").ln(); //合并单元（财务组织）
		}
		if("".equals(element)){
			buffer.add("AND dataElement.FNUMBER = 'dqyamount'").ln(); //取数类型
		} else {
			buffer.add("AND dataElement.FNUMBER = '"+ element +"'").ln(); //取数类型
			
		}
		buffer.add("AND ").add(fieldName).add(" IS NOT NULL").ln();
		buffer.add("ORDER BY org.FNumber, dataEntry.FDataElement, rpt.FConvertStatus");
		
		return buffer.sql();
		
	}



}