package ncc.impl.arap.report;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import nc.bs.dao.BaseDAO;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.pub.smart.context.SmartContext;
import nc.pub.smart.data.DataSet;
import nc.pub.smart.metadata.Field;
import nc.pub.smart.metadata.MetaData;
import nc.uap.bd.util.BDInSqlUtil;
import nc.vo.bd.bankaccount.BankAccSubVO;
import nc.vo.bd.currtype.CurrtypeVO;
import nc.vo.bd.cust.CustomerVO;
import nc.vo.bd.defdoc.DefdocVO;
import nc.vo.cmp.settlement.SettlementHeadVO;
import nc.vo.jcom.lang.StringUtil;
import nc.vo.org.FundOrgVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.JavaType;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pub.query.ConditionVO;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.vorg.FinanceOrgVersionVO;
import ncc.itf.arap.report.IInternalTransfer;


public class IInternalTransferImpl implements IInternalTransfer {

	
	@Override
	public DataSet queryInternalTransferReport(SmartContext context) throws BusinessException {
		ConditionVO[] conditionVOs = (ConditionVO[])context.getAttribute("freereport_querycondition_vos");
		List<ConditionVO> whereList = conditionVOs!= null ? Arrays.asList(conditionVOs) : new ArrayList<>(0);
		Map<String, String> whereMap = whereList.stream().collect(Collectors.toMap(ConditionVO::getFieldCode, ConditionVO::getValue));

		MetaData meta = new MetaData();
		BaseDAO dao = new BaseDAO();
		
		Map<String, String> map = new LinkedHashMap<String, String>();
		//本方财务组织基础信息
		map.put("b_gqqk", "股权情况");
		map.put("b_ssbk", "所属板块");
		map.put("b_ssqy", "所属区域");
		map.put("b_pk_org_v_code", "账套编码");
		map.put("b_pk_org_v", "账套名称");
		
		//收款单（内转）情况								
		map.put("gdef4", "收方资金中心");
		map.put("gdef19", "收方股权情况");
		map.put("gdef17", "收方所属板块");
		map.put("gdef16","收方所属区域");
		map.put("gpk_org_v_code", "收方账套编码");
		map.put("gpk_org_v", "收方账套名称");
		map.put("gdjlx", "收方单据类型");
		map.put("gdef13", "收方业务类型");
		map.put("gbilldate", "收方单据日期");
		map.put("gsettledate", "收方结算日期");
		map.put("gbusistatus", "收方单据状态");
		map.put("gsettlestatus", "收方结算状态");
		map.put("geffectstatus", "收方生效状态");
		map.put("grecaccount", "收方内部账户");//
		map.put("gscomment", "收方摘要");
		map.put("gbillno", "收方单据号");
		map.put("gcurrtype", "收方币种");///
		map.put("glocal_money", "收方本币收入");
		map.put("gbbzc", "收方本币支出");//
		map.put("gmoney", "收方原币收入");
		map.put("sfybzc", "收方原币支出");//
		map.put("gdef7", "收方是否跨区域");
		//--付款单
		map.put("pdef18", "付方资金中心");
		map.put("pdef9", "付方股权情况");
		map.put("pdef8", "付方所属板块");
		map.put("pdef6", "付方所属区域");
		map.put("ppk_org_v_code", "付方账套编码");
		map.put("ppk_org", "付方账套名称");
		map.put("pdjlx", "付方单据类型");
		map.put("pdef77", "付方业务类型");
		map.put("pbilldate", "付方单据日期");
		map.put("psettledate", "付方结算日期");
		map.put("pbusistatus", "付方单据状态");
		map.put("psettlestatus", "付方结算状态");
		map.put("peffectstatus", "付方生效状态");
		map.put("payaccount", "付方内部账户");
		//
		map.put("pscomment", "付方摘要");//
		map.put("pbillno", "付方单据号");
		map.put("ppk_currtype", "付方币种");
		map.put("pbbsr", "付方本币收入");//
		map.put("plocal_money", "付方本币支出");
		map.put("pybsr", "付方原币收入");
		map.put("pmoney", "付方原币支出");
		map.put("pdef7", "付方是否跨区域");
		
		
		String[] fldnames = map.keySet().toArray(new String[0]);
		String[] captions = map.values().toArray(new String[0]);
		
		Integer[] ilengths = new Integer[] { 250};
		JavaType[] types = new JavaType[] { JavaType.String };
		for (int i = 0; i < captions.length; i++) {
			Field field = new Field();
			field.setCaption(captions[i]);
			field.setFldname(fldnames[i]);
			setFieldByType(field, types[0], ilengths[0]);
			meta.addField(field);
		}
		
		//有必输条件时生效
		if(whereMap.isEmpty()) {
			return new DataSet(meta, new Object[0][0]);
		}
		
		
		
		StringBuilder sql = new StringBuilder();
		sql.append(""
				+ "select *  from ("
				+ "SELECT  DISTINCT \r\n" + 
				"\r\n" + 
				
				"	gather.pk_org AS gpk_org, \r\n" +
				"	gather.def4 AS gdef4, \r\n" + 
				"	gather.def19 AS gdef19, \r\n" + 
				"	gather.def17 AS gdef17, \r\n" + 
				"	gather.def16 AS gdef16, \r\n" + 
				" \r\n" + 
				"	gather.pk_org_v AS gpk_org_v, \r\n" +
				"	gather.pk_org_v AS gpk_org_v_code, \r\n" +
				"	'收款单（内转）' gdjlx, \r\n" + 
				"	gather.def13 AS gdef13, \r\n" + 
				"	gather.billdate AS gbilldate, \r\n" +
				"   gcmp.settledate AS gsettledate, "+
				"	gcmp.busistatus gbusistatus, \r\n" + 
				"	gcmp.settlestatus gsettlestatus, \r\n" + 
//			
				" gather.pk_gatherbill AS gpk_gatherbill, "+
				"	gather.effectstatus geffectstatus, \r\n" + 
				"\r\n" + 
				" \r\n" + 
				"   gitem.recaccount AS grecaccount,  " +
				"   gitem.scomment AS gscomment,  " +
				"	gather.billno gbillno, \r\n" + 
				"   gitem.pk_currtype AS gcurrtype, " +
				" \r\n" + 
				"	gather.local_money glocal_money, \r\n" + 
				"	'-' AS gbbzc, \r\n" + 
				"	gather.money gmoney, \r\n" + 
				"	'-' AS gybzc, \r\n" + 
				"	gather.def7 gdef7, \r\n" +
				
				" \r\n" + 
				"	paybill.def18 pdef18, \r\n" + 
				"	paybill.def8 pdef8, "+

				"	paybill.def9 pdef9, \r\n" + 
				"	paybill.def6 pdef6, \r\n" +
				" \r\n" + 
//				"	payitem.customer pcustomer, \r\n" + 
//				"	payitem.customer AS pcustomer_code, \r\n" + 
				"  paybill.pk_org_v  ppk_org_v_code, " +
				"  paybill.pk_org_v  ppk_org, " +
				"	'付款单（内转）' AS pdjlx, \r\n" + 
				"	paybill.def77 pdef77, \r\n" + 
				"	paybill.billdate pbilldate, \r\n" + 
				"	pcmp.settledate psettledate, \r\n" + 
				"	pcmp.busistatus pbusistatus, \r\n" + 
				"	pcmp.settlestatus psettlestatus, \r\n" +
				"   paybill.pk_paybill ppk_paybill, " +
				"	paybill.effectstatus peffectstatus, \r\n" + 
				" 	payitem.payaccount AS payaccount, \r\n" +
				" 	payitem.scomment AS pscomment, \r\n" +
				" \r\n" + 
				"	paybill.billno pbillno, \r\n" + 
				"	paybill.pk_currtype ppk_currtype, \r\n" + 
				"	0 AS pbbsr, \r\n" + 
				"	paybill.local_money plocal_money, \r\n" + 
				"	0 AS pybsr, \r\n" + 
				"	paybill.money pmoney, \r\n" + 
				"	paybill.def7 pdef7  \r\n" + 
				"	\r\n" + 
				"FROM\r\n" + 
				"	ar_gatherbill gather\r\n" + 
				"  left join (select * from (select recaccount,scomment,pk_currtype,pk_gatherbill,dr,row_number() over(partition by pk_gatherbill order by pk_gatherbill asc )  rn_01  from ar_gatheritem where nvl( dr, 0 ) = 0) where rn_01 = 1 \r\n" + 
				") gitem on gitem.pk_gatherbill = gather.pk_gatherbill " +
				"	LEFT JOIN  (select * from (select payaccount,src_billid,pk_paybill,scomment,pk_tradetype,dr,row_number() over(partition by pk_paybill order by pk_paybill asc )  rn_01  from ap_payitem where nvl( dr, 0 ) = 0 ) where rn_01 = 1 ) payitem ON gather.pk_gatherbill = payitem.SRC_BILLID \r\n" + 
				"	AND payitem.pk_tradetype = 'F3-Cxx-D01' \r\n" + 
				"	AND gather.pk_tradetype = 'F2-Cxx-01'\r\n" + 
				"	LEFT JOIN ap_paybill paybill ON paybill.pk_paybill = payitem.pk_paybill  and nvl( paybill.dr, 0 ) = 0  \r\n" +
				"	left join CMP_SETTLEMENT gcmp on gather.pk_gatherbill = gcmp.pk_busibill and gcmp.tradertypecode = 'F2-Cxx-01' and nvl( gcmp.dr, 0 ) = 0 \r\n" + 
				"	left join CMP_SETTLEMENT pcmp on paybill.pk_paybill = pcmp.pk_busibill  and pcmp.tradertypecode = 'F3-Cxx-D01' and nvl( pcmp.dr, 0 ) = 0  "+
				 " 	where  nvl( gather.dr, 0 ) = 0 \r\n"
				 + ")");
		
		
		sql.append(" where  1=1 ");
		
		//TODO 记得删除
//		sql.append(" and pcustomer is not null ");
		
		joinMustSqlWhere(sql, whereMap);
		joinGatherSqlWhere(sql, whereMap);
		joinPayBillSqlWhere(sql, whereMap);
		
		List<Map<String, Object>> qryRetList = (List<Map<String, Object>>) dao.executeQuery(sql.toString(), new MapListProcessor());
	
		List<String> pk_org_vList = new LinkedList<>();
		List<String> pcustomerList = new LinkedList<>();
		List<String> pk_busibillList = new LinkedList<>();
		List<String> gqqkPkList = new LinkedList<>();
		List<String> ssbkPKList = new LinkedList<>();
		List<String> ssqyPKList = new LinkedList<>();
		List<String> zjzxPkList = new LinkedList<>();
		List<String> ywlxList = new LinkedList<>();
		List<String> pk_currtypeList = new LinkedList<>();
		List<String> pkAccountList = new LinkedList<>();
		for(Map<String, Object> itemMap : qryRetList) {
			
			String pk_org_v = (String)itemMap.get("gpk_org_v");
			if(null != pk_org_v) {
				pk_org_vList.add(pk_org_v);
			}
			
			
			String ppk_org = (String)itemMap.get("ppk_org");
			if(null != ppk_org) {
				pk_org_vList.add(ppk_org);
			}
			
//			String pcustomer = (String)itemMap.get("pcustomer");
//			if(null != pcustomer) {
//				pcustomerList.add(pcustomer);
//			}
			
			String gpk_gatherbill = (String)itemMap.get("gpk_gatherbill");
			if( null != gpk_gatherbill) {
				pk_busibillList.add(gpk_gatherbill);
			}
			
			String ppk_paybill = (String)itemMap.get("ppk_paybill");
			if( null != ppk_paybill ) {
				pk_busibillList.add(ppk_paybill);
			}
			
			
			String gdef19 = (String)itemMap.get("gdef19");
			if(null != gdef19) {
				gqqkPkList.add(gdef19);
			}
			String def9 = (String)itemMap.get("cc");
			if(null != def9) {
				gqqkPkList.add(def9);
			}
			
			//所属板块
			String gdef17 = (String)itemMap.get("gdef17");
			if(null != gdef17) {
				ssbkPKList.add(gdef17);
			}
			String pdef8 = (String)itemMap.get("pdef8");
			if(null != pdef8) {
				ssbkPKList.add(pdef8);
			}
			
			//所属区域
			String gdef16 = (String)itemMap.get("gdef16");
			if(null != gdef16) {
				ssqyPKList.add(gdef16);
			}
			String def6 = (String)itemMap.get("pdef6");
			if(null != def6) {
				ssqyPKList.add(def6);
			}
			
			//资金中心
			String gdef4 = (String)itemMap.get("gdef4");
			if(null != gdef4) {
				zjzxPkList.add(gdef4);
			}
			
			String pdef18 = (String)itemMap.get("pdef18");
			if(null != pdef18) {
				zjzxPkList.add(pdef18);
			}
			//收方业务类型
			String gdef13 = (String)itemMap.get("gdef13");
			if(null != gdef13) {
				ywlxList.add(gdef13);
			}
				
			String pdef77 = (String)itemMap.get("pdef77");
			if(null != pdef77) {
				ywlxList.add(pdef77);
			}
			
			String gbz = (String)itemMap.get("gbz");
			if(null != gbz) {
				pk_currtypeList.add(gbz);
			}
			
			String ppk_currtype = (String)itemMap.get("ppk_currtype");
			if(null != ppk_currtype) {
				pk_currtypeList.add(ppk_currtype);
			}
			String gcurrtype = (String)itemMap.get("gcurrtype");
			if(null != gcurrtype) {
				pk_currtypeList.add(gcurrtype);
			}
			
			
			//内部账账户
			String payaccount = (String)itemMap.get("payaccount");
			if(null != payaccount) {
				pkAccountList.add(payaccount);
			}
			
			String grecaccount = (String)itemMap.get("grecaccount");
			if(null != grecaccount) {
				pkAccountList.add(grecaccount);
			}
		}
		pk_busibillList = pk_busibillList.stream().distinct().collect(Collectors.toList());
		pcustomerList= pcustomerList.stream().distinct().collect(Collectors.toList());
		pk_org_vList= pk_org_vList.stream().distinct().collect(Collectors.toList());
		gqqkPkList = gqqkPkList.stream().distinct().collect(Collectors.toList());
		ssbkPKList = ssbkPKList.stream().distinct().collect(Collectors.toList());
		ssqyPKList = ssqyPKList.stream().distinct().collect(Collectors.toList());
		zjzxPkList = zjzxPkList.stream().distinct().collect(Collectors.toList());
		ywlxList = ywlxList.stream().distinct().collect(Collectors.toList());
		pk_currtypeList = pk_currtypeList.stream().distinct().collect(Collectors.toList());
		pkAccountList = pkAccountList.stream().distinct().collect(Collectors.toList());
		
		Map<String, FinanceOrgVersionVO> fOrgVVOMap =  (Map<String, FinanceOrgVersionVO>) dao.retrieveByClause(FinanceOrgVersionVO.class, "pk_vid in " + BDInSqlUtil.getInSql(pk_org_vList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(FinanceOrgVersionVO::getPk_vid, item -> item));
		
		Map<String, CustomerVO>  customerVOMap =  (Map<String, CustomerVO>) dao.retrieveByClause(CustomerVO.class, " pk_customer in " + BDInSqlUtil.getInSql(pcustomerList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(CustomerVO::getPk_customer, item -> item ));
		
		Map<String, List<SettlementHeadVO>> settlVOMap =  (Map<String, List<SettlementHeadVO>>) dao.retrieveByClause(SettlementHeadVO.class, "pk_busibill in " +BDInSqlUtil.getInSql(pk_busibillList.toArray(new String[0]), false) ).stream().collect(Collectors.groupingBy(SettlementHeadVO::getPk_busibill));
		
		Map<String, DefdocVO> gqqkDefDocVOMap =  (Map<String, DefdocVO>) dao.retrieveByClause(DefdocVO.class, " pk_defdoc in " + BDInSqlUtil.getInSql(gqqkPkList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(DefdocVO::getPk_defdoc, item -> item ));
		
		Map<String, DefdocVO>  ssbkDefDocVOMap =  (Map<String, DefdocVO>) dao.retrieveByClause(DefdocVO.class, " pk_defdoc in " + BDInSqlUtil.getInSql(ssbkPKList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(DefdocVO::getPk_defdoc, item -> item ));
		
		Map<String, DefdocVO> ssqyDefDocVOMap =  (Map<String, DefdocVO>) dao.retrieveByClause(DefdocVO.class, " pk_defdoc in " + BDInSqlUtil.getInSql(ssqyPKList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(DefdocVO::getPk_defdoc, item -> item ));
		
		Map<String, DefdocVO> ywlxDefDocVOMap =  (Map<String, DefdocVO>) dao.retrieveByClause(DefdocVO.class, " pk_defdoc in " + BDInSqlUtil.getInSql(ywlxList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(DefdocVO::getPk_defdoc, item -> item ));
		
		Map<String, FundOrgVO>  fundOrgVOcVOMap =  (Map<String, FundOrgVO>) dao.retrieveByClause(FundOrgVO.class, " pk_fundorg in " + BDInSqlUtil.getInSql(zjzxPkList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(FundOrgVO::getPk_fundorg, item -> item ));
		

		Map<String, CurrtypeVO> currtypeVOVOMap =  (Map<String, CurrtypeVO>) dao.retrieveByClause(CurrtypeVO.class, " pk_currtype in " + BDInSqlUtil.getInSql(pk_currtypeList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(CurrtypeVO::getPk_currtype, item -> item ));
		
		Map<String, BankAccSubVO> bankAccSubVOVOMap =  (Map<String, BankAccSubVO>) dao.retrieveByClause(BankAccSubVO.class, " pk_bankaccsub in " + BDInSqlUtil.getInSql(pkAccountList.toArray(new String[0]), false)).stream().collect(Collectors.toMap(BankAccSubVO::getPk_bankaccsub, item -> item ));
		
		
		List<Object[]> list = new LinkedList<>();
		for (Map<String, Object> itemMap : qryRetList) {
			Object[] data = new Object[map.size()];
				
			int i = -1; 
			for(String field : fldnames) {
				i++;
				Object obj = itemMap.get(field);
				
				if(null == obj && !field.startsWith("b_")) {
					data[i] = null;
					continue;
				}
				if(null != obj && ("pdef7".equals(field) || "gdef7".equals(field) )) {
					data[i] = "Y".equals(obj) ? "是" : "否";
					continue;
				}
				
				if( obj instanceof String) {
					boolean isSeted = false;
					
					String val = obj.toString();
					if("gpk_org_v_code".equals(field) || "ppk_org_v_code".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(val);
						data[i] = null != vo ? vo.getCode() : null;
						continue;
					}
					if("gpk_org_v".equals(field) || "ppk_org".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					
					//废弃
//					if("gpk_gatherbill".equals(field) || "ppk_paybill".equals(field)) {
//						SettlementHeadVO vo = settlVOMap.get(val);
//						data[i] = null != vo ? vo.getCode() : null;
//						continue;
//					}
					
					
					if("gdef19".equals(field) || "pdef9".equals(field)) {
						DefdocVO vo = gqqkDefDocVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					
					if("pdef8".equals(field) || "gdef17".equals(field)) {
						DefdocVO vo = ssbkDefDocVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					
					if("gdef16".equals(field) || "pdef6".equals(field)) {
						DefdocVO vo = ssqyDefDocVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					if("gdef4".equals(field) || "pdef18".equals(field)) {
						FundOrgVO vo = fundOrgVOcVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
				
					if("gdef13".equals(field) || "pdef77".equals(field)) {
						DefdocVO vo = ywlxDefDocVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					
					if("ppk_currtype".equals(field) || "gcurrtype".equals(field)) {
						CurrtypeVO vo = currtypeVOVOMap.get(val);
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					
//					if("payaccount".equals(field)) {
//						CustbankVO vo = custbankVOVOMap.get(val);
//						data[i] = null != vo ? vo.get : null;
//						continue;
//					}
					
				}
		
				
				
				
				String b_pk_org_v = (String)whereMap.get("b_pk_org_v") ;
				b_pk_org_v = null == b_pk_org_v ? ""  : b_pk_org_v;
				
				//处理本方财务组织基础信息
				if(b_pk_org_v.equals(itemMap.get("gpk_org_v"))) {
					if("b_gqqk".equals(field)) {
						DefdocVO vo = gqqkDefDocVOMap.get(itemMap.get("gdef19"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					if("b_ssbk".equals(field)) {
						DefdocVO vo = ssbkDefDocVOMap.get(itemMap.get("gdef17"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}	
					if("b_ssqy".equals(field)) {
						DefdocVO vo = ssqyDefDocVOMap.get(itemMap.get("gdef16"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					if("b_pk_org_v_code".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(itemMap.get("gpk_org_v"));
						data[i] = null != vo ? vo.getCode() : null;
						continue;
					}
					if("b_pk_org_v".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(itemMap.get("gpk_org_v"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
				}else {
					if("b_gqqk".equals(field)) {
						DefdocVO vo = gqqkDefDocVOMap.get(itemMap.get("pdef9"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					if("b_ssbk".equals(field)) {
						DefdocVO vo = ssbkDefDocVOMap.get(itemMap.get("pdef8"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}	
					if("b_ssqy".equals(field)) {
						DefdocVO vo = ssqyDefDocVOMap.get(itemMap.get("pdef6"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
					if("b_pk_org_v_code".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(itemMap.get("ppk_org"));
						data[i] = null != vo ? vo.getCode() : null;
						continue;
					}
					if("b_pk_org_v".equals(field)) {
						FinanceOrgVersionVO vo = fOrgVVOMap.get(itemMap.get("ppk_org"));
						data[i] = null != vo ? vo.getName() : null;
						continue;
					}
				}
				
				if("grecaccount".equals(field) || "payaccount".equals(field)) {
					BankAccSubVO vo = bankAccSubVOVOMap.get(obj);
					data[i] = null != vo ? vo.getCode() : null;
					continue;
				}
				
				
				
				if("geffectstatus".equals(field) || "peffectstatus".equals(field)) {
					//未生效=0,已生效=10
					data[i] =  obj.equals(new Integer(0)) ? "未生效" : (obj.equals(new Integer(10)) ? "已生效" : null) ;
					continue;
				}
				
				if("gbusistatus".equals(field) || "pbusistatus".equals(field)) {
					//保存=-1,暂存=-99,审批通过=1,签字=8,未确认=9
					String content = null;
					if(new Integer(-1).equals(obj)) {
						content = "保存";
					}
					if(new Integer(-99).equals(obj)) {
						content = "暂存";
					}
					if(new Integer(1).equals(obj)) {
						content = "审批通过";
					}
					if(new Integer(8).equals(obj)) {
						content = "签字";
					}
					if(new Integer(9).equals(obj)) {
						content = "签字";
					}
					data[i] =  content;
					continue;
				}
				
				if(( "gsettlestatus".equals(field) || "psettlestatus".equals(field)) && null != obj) {
					//0=未结算;1=支付中;2=支付失败;3=收款中;4=收款失败;5=结算成功;6=部分成功;7=结算红冲;9=手工结算;10=划账中;11=划账失败;12=待变更;
					String content = null;
				
					Integer val = (Integer)obj; 
					switch(val) {
					 case 0 :
						 content="未结算";
						 break;
					 case 1 :
						 content="支付中";
						 break;
					 case 2 :
						 content="支付失败";
						 break;
					 case 3 :
						 content="首款中";
						 break;
					 case 4 :
						 content="收款失败";
						 break;
					 case 5 :
						 content="结算成功";
						 break;
					 case 6 :
						 content="部分成功";
						 break;
					 case 7 :
						 content="手工结算";
						 break;
					 case 10 :
						 content="划账中";
						 break;
					 case 11 :
						 content="划账失败";
						 break;
					 case 12 :
						 content="待变更";
						 break;
					
					}
					data[i] =  content;
					continue;
				}
				
				if(obj instanceof java.math.BigDecimal) {
					UFDouble numberTemp = new UFDouble(obj.toString(), 2);
					data[i] =  numberTemp.toString();
					continue;
				}
		
				data[i] =  null != obj ? (   obj instanceof java.lang.String ? obj.toString().length() > 250  ? obj.toString().substring(0, 240)+"..."  : obj  :  obj ) : null ;
			}
			list.add(data);
		}
		
		Object[][] datas = new Object[qryRetList.size()][map.size()];
		
		for(int i = 0; i < qryRetList.size(); i++ ) {
			datas[i] = list.get(i);
		}

		DataSet ds = new DataSet(meta, datas);
		
		return ds;
	}
	
	public void joinMustSqlWhere(final StringBuilder sql, final Map<String, String> whereMap) {

		
//		//股权情况
		String b_gqqk = (String) whereMap.get("b_gqqk");
//		//所属区域
		String area = (String) whereMap.get("area");
//		//所属板块
		String b_ssbk = (String) whereMap.get("b_ssbk");
//		
		String b_pk_org_v = (String)whereMap.get("b_pk_org_v");
		String b_pk_org_v_code = (String)whereMap.get("b_pk_org_v_code");
			
		if(!StringUtil.isEmptyWithTrim(b_gqqk)) {
			sql.append(" and ( gdef19 = '"+b_gqqk+"' or pdef9 ='"+b_gqqk+"' ) ");
		}
		if(!StringUtil.isEmptyWithTrim(b_ssbk)) {
			sql.append(" and ( gdef17 ='"+b_ssbk+"' or pdef8 = '"+b_ssbk+"' ) ");
		}
//		
		if(!StringUtil.isEmptyWithTrim(area)) {
			sql.append(" and (gdef16 = '" + area + "' or pdef6 = '"+area+"' ) ");
		}
		if(!StringUtil.isEmptyWithTrim(b_pk_org_v)) {
			sql.append(" and (gpk_org_v = '" + b_pk_org_v + "' or ppk_org = '" + b_pk_org_v + "' ) ");
		}
		if(!StringUtil.isEmptyWithTrim(b_pk_org_v_code)) {
			sql.append(" and (gpk_org_v = '" + b_pk_org_v_code + "' or ppk_org = '" + b_pk_org_v_code + "' ) ");
		}
	}
	
	public void joinGatherSqlWhere(final StringBuilder sql, final Map<String, String> whereMap) {
		
		String sfzjzx = (String) whereMap.get("sfzjzx");
		//非必填字段
		//收方资金中心
		if(!StringUtil.isEmptyWithTrim(sfzjzx)) {
			sql.append(" and gdef4='"+ sfzjzx +"'");
		}
		
		//收方股权情况
		String sfgqqk = (String) whereMap.get("sfgqqk");
		if(!StringUtil.isEmptyWithTrim(sfgqqk)) {
			sql.append(" and gdef19 = '" + sfgqqk + "'" );
		}
//		
//		//收方所属板块
		String sfssbk = (String) whereMap.get("sfssbk");
		if(!StringUtil.isEmptyWithTrim(sfssbk)) {
			sql.append(" and gdef17 = '" + sfssbk + "'" );
		}
//		
//		//收方所属区域
		String sfssqy = (String) whereMap.get("sfssqy");
		if(!StringUtil.isEmptyWithTrim(sfssqy)) {
			sql.append(" and gdef16 = '" + sfssqy + "'" );
		}
//		
//		//收方账套编码
		String gpk_org_v = (String) whereMap.get("gpk_org_v");
		if(!StringUtil.isEmptyWithTrim(gpk_org_v)) {
			sql.append(" and gpk_org_v = '"+gpk_org_v+"'");
			
		}
//		//收方账套名称		
		String gpk_org_v_code = (String) whereMap.get("gpk_org_v_code");
		if(!StringUtil.isEmptyWithTrim(gpk_org_v_code)) {
			sql.append(" and gpk_org_v = '"+gpk_org_v_code+"'");
			
		}
		
//		//收方单据类型
		String sfdjlx = (String) whereMap.get("sfdjlx");
		if(!StringUtil.isEmptyWithTrim(sfdjlx)) {
			sql.append(" and gdjlx = '"+sfdjlx+"'");
		}
//		
//		//收方业务类型
		String sfywlx = (String) whereMap.get("sfywlx");
		if(!StringUtil.isEmptyWithTrim(sfywlx)) {
			sql.append(" and gdef13 = '"+sfywlx+"' ");
		}
//		
		//收方单据日期
		String sfdjrq = (String)whereMap.get("sfdjrq");
		if(!StringUtil.isEmptyWithTrim(sfdjrq)) {
			String[] dates = sfdjrq.split(",");
			sql.append(" and  ( '"+dates[0]+"' <=  gbilldate and gbilldate  <= '"+dates[1]+"'  ) ");
		}

//		//收方结算日期
		String sfjsrq = (String) whereMap.get("gsettledate");
		if(!StringUtil.isEmptyWithTrim(sfjsrq)) {
			String[] dates = sfjsrq.split(",");
			sql.append(" and  ( '"+dates[0]+"' <=  gsettledate and gsettledate  <= '"+dates[1]+"'  ) ");
		}
		
		//收方单据状态
		String gbillstatus = (String) whereMap.get("gbillstatus");
		if(!StringUtil.isEmptyWithTrim(gbillstatus)) {
			sql.append(" and gbusistatus = '"+gbillstatus+"'");
		}
		
//		//收方结算状态
		String gsettlestatus = (String) whereMap.get("gsettlestatus");
		if(!StringUtil.isEmptyWithTrim(gsettlestatus)) {
			sql.append(" and gsettlestatus = '" + gsettlestatus + "'");
		}
		
//		//收方生效状态
		String geffectstatus = (String) whereMap.get("geffectstatus");
		if(!StringUtil.isEmptyWithTrim(geffectstatus)) {
			sql.append(" and geffectstatus = '"+geffectstatus+"'");
		}
//		
//		//收方内部账户
//		//收方摘要
//		//收方单据号
		String gbillno = (String) whereMap.get("gbillno");
		if(!StringUtil.isEmptyWithTrim(gbillno)) {
			sql.append(" and gbillno = '"+gbillno+"'");
		}
//		//收方币种
		String gcurrtype = (String) whereMap.get("gcurrtype");
		if(!StringUtil.isEmptyWithTrim(gcurrtype)) {
			sql.append(" and gcurrtype = '"+gcurrtype+"'");
		}
//		//收方本币收入
		String glocal_money = (String) whereMap.get("glocal_money");
		if(!StringUtil.isEmptyWithTrim(glocal_money)) {
			sql.append(" and glocal_money = '"+glocal_money+"'");
		}
//		收方本币支出（改字段存在问题，需求）
		String gbbzc = (String) whereMap.get("gbbzc");
		if(!StringUtil.isEmptyWithTrim(gbbzc)) {
			sql.append(" and gbbzc = '"+gbbzc+"'");
		}
		
		//收方原币收入
		String gmoney = (String) whereMap.get("gmoney");
		if(!StringUtil.isEmptyWithTrim(gmoney)) {
			sql.append(" and gmoney = '"+gmoney+"'");
		}
		
		//收方原币支出
		String sfybzc = (String) whereMap.get("sfybzc");
		if(!StringUtil.isEmptyWithTrim(sfybzc)) {
			sql.append(" and sfybzc = '"+sfybzc+"'");
		}
		
		//收方是否跨区域
		String gdef7 = (String) whereMap.get("gdef7");
		if(!StringUtil.isEmptyWithTrim(gdef7)) {
			sql.append(" and gdef7 = '"+gdef7+"'");
		}
		
	}
	
	
	public void joinPayBillSqlWhere(final StringBuilder sql, final Map<String, String> whereMap) {
		

		
		//付方资金中心
		String pdef18 = (String) whereMap.get("pdef18");
		if(!StringUtil.isEmptyWithTrim(pdef18)) {
			sql.append(" and pdef18 = '" + pdef18 + "'");
		}
//		
//		//付方股权情况
		String pdef9 = (String) whereMap.get("pdef9");
		if(!StringUtil.isEmptyWithTrim(pdef9)) {
			sql.append(" and pdef9 = '"+pdef9+"'");
		}
		
		//付方所属板块
		String pdef8 = (String) whereMap.get("pdef8");
		if(!StringUtil.isEmptyWithTrim(pdef8)) {
			sql.append(" and pdef8 = '"+pdef8+"'");
		}
		
		//付方所属区域
		String pdef6 = (String) whereMap.get("pdef6");
		if(!StringUtil.isEmptyWithTrim(pdef6)) {
			sql.append(" and pdef6 = '"+pdef6+"'");
		}
		
//		//付方账套编码
//		//付方账套名称
//		String pcustomer = (String) parm.getAttribute("parameter_pcustomer");
//		if(!StringUtil.isEmptyWithTrim(pcustomer)) {
//			sql.append(" and pcustomer = '"+pcustomer+"'");
//		}
//		
//		//付方单据类型
		String pdjlx = (String) whereMap.get("pdjlx");
		if(!StringUtil.isEmptyWithTrim(pdjlx)) {
			sql.append(" and pdjlx = '"+pdjlx+"'");
		}
//		
//		//付方业务类型
		String pdef77 = (String) whereMap.get("pdef77");
		if(!StringUtil.isEmptyWithTrim(pdef77)) {
			sql.append(" and pdef77 = '"+pdef77+"'");
		}
		
		//付方单据日期
		String pbilldate = (String) whereMap.get("pbilldate");
		if(!StringUtil.isEmptyWithTrim(pbilldate)) {
			String[] dates = pbilldate.split(",");
			sql.append(" and  ( '"+dates[0]+"' <=  pbilldate and pbilldate  <= '"+dates[1]+"'  ) ");
		}
		
		//付方结算日期
		String psettledate = (String) whereMap.get("psettledate");
		if(!StringUtil.isEmptyWithTrim(psettledate)) {
			String[] dates = psettledate.split(",");
			sql.append(" and  ( '"+dates[0]+"' <=  psettledate and psettledate  <= '"+dates[1]+"'  ) ");
		}

		//付方单据状态
		String pbusistatus = (String) whereMap.get("pbusistatus");
		if(!StringUtil.isEmptyWithTrim(pbusistatus)) {
			sql.append(" and pbusistatus = '"+pbusistatus+"'");
		}
//		
//		//付方结算状态
		String psettlestatus = (String) whereMap.get("psettlestatus");
		if(!StringUtil.isEmptyWithTrim(psettlestatus)) {
			sql.append(" and psettlestatus = '"+psettlestatus+"'");
		}
//		
//		//付方生效状态
		String peffectstatus = (String) whereMap.get("peffectstatus");
		if(!StringUtil.isEmptyWithTrim(peffectstatus)) {
			sql.append(" and peffectstatus = '"+peffectstatus+"'");
		}
		
		//付方内部账户
		String payaccount = (String) whereMap.get("payaccount");
		if(!StringUtil.isEmptyWithTrim(payaccount)) {
			sql.append(" and payaccount ='"+payaccount+"' ");
		}
//		
//		//付方摘要
//		//付方单据号
		String pbillno = (String) whereMap.get("pbillno");
		if(!StringUtil.isEmptyWithTrim(pbillno)) {
			sql.append(" and pbillno = '"+pbillno+"'");
		}
		
		//付方币种
		String ppk_currtype = (String) whereMap.get("ppk_currtype");
		if(!StringUtil.isEmptyWithTrim(ppk_currtype)) {
			sql.append(" and ppk_currtype = '"+ppk_currtype+"'");
		}
		
		//付方本币收入
		String pbbsr = (String) whereMap.get("pbbsr");
		if(!StringUtil.isEmptyWithTrim(pbbsr)) {
			sql.append(" and pbbsr = '"+pbbsr+"'");
		}
		
		//付方本币支出
		String plocal_money = (String) whereMap.get("plocal_money");
		if(!StringUtil.isEmptyWithTrim(plocal_money)) {
			sql.append(" and plocal_money = '"+ plocal_money+"'");
		}
		
		//付方原币收入
		String pybsr = (String) whereMap.get("pybsr");
		if(!StringUtil.isEmptyWithTrim(pybsr)) {
			sql.append(" and pybsr = '"+pybsr+"'");
		}
		
		//付方原币支出
		String pmoney = (String) whereMap.get("pmoney");
		if(!StringUtil.isEmptyWithTrim(pmoney)) {
			sql.append(" and pmoney = '" + pmoney+ "'");
		}
		
		//付方是否跨区域
		String pdef7 = (String) whereMap.get("pdef7");
		if(!StringUtil.isEmptyWithTrim(pdef7)) {
			sql.append(" and pdef7 = '"+pdef7+"'");
		}
	}
	
	
	
	private static void setFieldByType(Field fd, JavaType type, int ilength) {
		if (type == JavaType.UFDouble || type == JavaType.BigDecimal) {
			fd.setDbColumnType(Types.DECIMAL);
			fd.setPrecision(28);
			fd.setScale(8);
		} else if (type == JavaType.UFBoolean) {
			fd.setDbColumnType(Types.CHAR);
			fd.setPrecision(1);
		} else if (type == JavaType.UFDate || type == JavaType.UFDateTime
				|| type == JavaType.UFTime) {
			fd.setDbColumnType(Types.VARCHAR);
			fd.setPrecision(19);
		} else if (type == JavaType.Integer || type == JavaType.UFFlag) {
			fd.setDbColumnType(Types.INTEGER);
		} else if (type == JavaType.UFStringEnum) {
			fd.setDbColumnType(Types.VARCHAR);
			fd.setPrecision(30);
		} else if (type == JavaType.String) {
			fd.setDbColumnType(Types.VARCHAR);
			if (ilength >= 0) {
				fd.setPrecision(ilength);
			} else {
				fd.setPrecision(100);
			}
		} else {
			ExceptionUtils
					.wrappBusinessException(nc.vo.ml.NCLangRes4VoTransl
							.getNCLangRes().getStrByID("4008019_0",
									"04008019-0049")/*
													 * @res "不支持的类型"
													 */);
		}
	}



}
