package nccloud.impl.cmp.fundbalreport;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nc.bs.dao.BaseDAO;
import nc.bs.framework.common.NCLocator;
import nc.bs.logging.Logger;
import nc.cmp.tools.UFDoubleUtils;
import nc.itf.bd.currtype.ICurrtypeQuery;
import nc.itf.gl.contrast.IBankReceiptPrv;
import nc.itf.gl.contrast.IContrastAccountPrv;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.BeanProcessor;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.jdbc.framework.processor.MapProcessor;
import nc.ui.querytemplate.querytree.IQueryScheme;
import nc.vo.bd.currtype.CurrtypeVO;
import nc.vo.cmp.cb.AccountlinkVO;
import nc.vo.cmp.cb.BankReceiptVO;
import nc.vo.cmp.cb.ContrastQueryVO;
import nc.vo.cmp.cb.GLQueryVO;
import nc.vo.cmp.cb.GlContrastVO;
import nc.vo.cmp.nc63.fundbalancereport.FundBalReportBVO_NC63;
import nc.vo.cmp.nc63.fundbalancereport.FundBalReportHVO_NC63;
import nc.vo.hzdev.hz_fundbalancereport.AggFundBalanceReportHVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFBoolean;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pubapp.AppContext;
import nccloud.base.exception.ExceptionUtils;
import nccloud.impl.pub.ace.AceAggbusiFundbalreportPubServiceImpl;
import nccloud.itf.cmp.fundbalreport.IFundbalreportMaintain;
import nccloud.pubitf.cmp.initcontrast.IContrastTools4Web;

public class FundbalreportMaintainImpl extends AceAggbusiFundbalreportPubServiceImpl implements IFundbalreportMaintain {

	@Override
	public void delete(AggFundBalanceReportHVO[] clientFullVOs, AggFundBalanceReportHVO[] originBills)
			throws BusinessException {
		super.pubdeleteBills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] insert(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubinsertBills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] update(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubupdateBills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] query(IQueryScheme queryScheme) throws BusinessException {
		return super.pubquerybills(queryScheme);
	}

	@Override
	public AggFundBalanceReportHVO[] save(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubsendapprovebills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] unsave(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubunsendapprovebills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] approve(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubapprovebills(clientFullVOs, originBills);
	}

	@Override
	public AggFundBalanceReportHVO[] unapprove(AggFundBalanceReportHVO[] clientFullVOs,
			AggFundBalanceReportHVO[] originBills) throws BusinessException {
		return super.pubunapprovebills(clientFullVOs, originBills);
	}

	@Override
	public Object[] doConfirm(String confirmor, UFDate confirmDate, UFBoolean confirmState, String pk)
			throws BusinessException {
		BaseDAO baseDao = new BaseDAO();
		String sql = "update hzdev_fundbalancereport_h set vdef1 = ?,vdef17 = ?,confirmstatus=?,vdef3 ='',vdef2 = ''  where pk_fundreport_h = ?";
		SQLParameter params = new SQLParameter();
		params.addParam(confirmor);
		params.addParam(confirmDate);
		params.addParam(confirmState);
		params.addParam(pk);
		baseDao.executeUpdate(sql, params);
		/** 传递NC63 谢文如 begin **/
//		BaseDAO nccDao = new BaseDAO();
//		sql = "select t.value from pub_sysinit t where t.initcode = 'NCDs'";
//		String nc63Ds = (String)baseDao.executeQuery(sql, new ColumnProcessor());
//		BaseDAO nc63Dao = new BaseDAO(nc63Ds);
//		params.clearParams();
//		params.addParam(pk);
////    		表头
//		StringBuffer sqlstr = new StringBuffer("");
//		sqlstr.append("select h.vbillcode,").append("\r\n");
//		sqlstr.append("       'NCadmin' billmaker,").append("\r\n");
//		sqlstr.append("       'NCadmin' creator,").append("\r\n");
//		sqlstr.append("       h.creationtime,").append("\r\n");
//		sqlstr.append("       h.billmaker_date,").append("\r\n");
//		sqlstr.append("       org.code pk_org,").append("\r\n");
//		sqlstr.append("       h.dbilldate,").append("\r\n");
//		sqlstr.append("       h.vdef17 vdef1,").append("\r\n");
//		sqlstr.append("       'Y' confirmstatus,").append("\r\n");
//		sqlstr.append("       0 dr,").append("\r\n");
//		sqlstr.append("       h.ts,").append("\r\n");
//		sqlstr.append("       h.pk_fundreport_h,").append("\r\n");
//		sqlstr.append("       h.pk_fundreport_h vdef2").append("\r\n");
//		sqlstr.append("  from hzdev_fundbalancereport_h h").append("\r\n");
//		sqlstr.append("  left join org_financeorg org").append("\r\n");
//		sqlstr.append("    on org.pk_financeorg = h.pk_org").append("\r\n");
//		sqlstr.append(" where nvl(h.dr, 0) = 0").append("\r\n");
//		sqlstr.append(" and h.pk_fundreport_h = ?");
//
//		FundBalReportHVO_NC63 hVO_NC63 = (FundBalReportHVO_NC63) nccDao.executeQuery(sqlstr.toString(), params,
//				new BeanProcessor(FundBalReportHVO_NC63.class));
//		if (hVO_NC63 == null) {
//			throw new BusinessException("该单据已被删除");
//		}
//		// 表体
//		sqlstr = new StringBuffer("");
//		sqlstr.append("select 'Y' vdef11,").append("\r\n");
//		sqlstr.append("       b.crowno crowno,").append("\r\n");
//		sqlstr.append("       bsb.accnum pk_bankaccsub,").append("\r\n");
//		sqlstr.append("       b.accnum accnum,").append("\r\n");
//		sqlstr.append("       b.accname accname,").append("\r\n");
//		sqlstr.append("       b.pk_currtype pk_currtype,").append("\r\n");
//		sqlstr.append("       b.vdef15 vdef15,").append("\r\n");
//		sqlstr.append("       b.fundmny fundmny,").append("\r\n");
//		sqlstr.append("       b.noinaccount noinaccount,").append("\r\n");
//		sqlstr.append("       b.noinaccount vdef17,").append("\r\n");
//		sqlstr.append("       b.vdef18 vdef12,").append("\r\n");
//		sqlstr.append("       b.vdef13 vdef13,").append("\r\n");
//		sqlstr.append("       b.vdef14 vdef14,").append("\r\n");
//		sqlstr.append("       b.afteradjustmny afteradjustmny,").append("\r\n");
//		sqlstr.append("       0 maintainfund,").append("\r\n");
//		sqlstr.append("       0 assetinjectionfreeze,").append("\r\n");
//		sqlstr.append("       0 vdef25,").append("\r\n");
//		sqlstr.append("       0 vdef8,").append("\r\n");
//		sqlstr.append("       decode(vdef1, '~', 0, null, 0, vdef1) +").append("\r\n");
//		sqlstr.append("       decode(vdef2, '~', 0, null, 0, vdef2) +").append("\r\n");
//		sqlstr.append("       decode(vdef11, '~', 0, null, 0, vdef11) +").append("\r\n");
//		sqlstr.append("       decode(vdef12, '~', 0, null, 0, vdef12) +").append("\r\n");
//		sqlstr.append("       decode(vdef20, '~', 0, null, 0, vdef20) +").append("\r\n");
//		sqlstr.append("       decode(vdef21, '~', 0, null, 0, vdef21) +").append("\r\n");
//		sqlstr.append("       decode(vdef22, '~', 0, null, 0, vdef22) +").append("\r\n");
//		sqlstr.append("       decode(vdef23, '~', 0, null, 0, vdef23) +").append("\r\n");
//		sqlstr.append("       decode(vdef24, '~', 0, null, 0, vdef24) +").append("\r\n");
//		sqlstr.append("       decode(vdef25, '~', 0, null, 0, vdef25) +").append("\r\n");
//		sqlstr.append("       decode(vdef26, '~', 0, null, 0, vdef26) +").append("\r\n");
//		sqlstr.append("       decode(vdef27, '~', 0, null, 0, vdef27) +").append("\r\n");
//		sqlstr.append("       decode(vdef28, '~', 0, null, 0, vdef28) +").append("\r\n");
//		sqlstr.append("       decode(assetinjectionfreeze, '~', 0, null, 0, assetinjectionfreeze) bankmonitor,").append("\r\n");
//		sqlstr.append("       0 govmonitormny,").append("\r\n");
//		sqlstr.append("       0 pledgemny,").append("\r\n");
//		sqlstr.append("       b.vdef3 vdef3,").append("\r\n");
//		sqlstr.append("       b.vdef29 vdef9,").append("\r\n");
//		sqlstr.append("       b.vdef5 vdef5,").append("\r\n"); 
//		sqlstr.append("       0 vdef10,").append("\r\n");
//		sqlstr.append("       b.vdef7 vdef7,").append("\r\n");
//		sqlstr.append("       b.vdef30 vdef30,").append("\r\n");
//		sqlstr.append("       0 dr,").append("\r\n");
//		sqlstr.append("       b.ts,").append("\r\n");
//		sqlstr.append("       b.vdef6 vdef1,").append("\r\n");
//		sqlstr.append("       b.vdef8 vdef2,").append("\r\n");
//		sqlstr.append("       b.pk_fundreport_b,").append("\r\n");
//		sqlstr.append("       b.pk_fundreport_h").append("\r\n");
//		sqlstr.append("  from hzdev_fundbalancereport_b b").append("\r\n");
//		sqlstr.append("  left join bd_bankaccsub bsb").append("\r\n");
//		sqlstr.append("    on bsb.pk_bankaccsub = b.pk_bankaccsub").append("\r\n");
//		sqlstr.append(" where nvl(b.dr, 0) = 0").append("\r\n");
//		sqlstr.append(" and b.pk_fundreport_h = ?");
//		List<FundBalReportBVO_NC63> bVOList_NC63 = (List<FundBalReportBVO_NC63>) nccDao.executeQuery(sqlstr.toString(),
//				params, new BeanListProcessor(FundBalReportBVO_NC63.class));
//		if (bVOList_NC63 != null && bVOList_NC63.size() > 0) {
//			// 查询63主键
//			String orgCode = hVO_NC63.getPk_org();
//			String billmaker = hVO_NC63.getBillmaker();
//			params.clearParams();
//			params.addParam(billmaker);
//			sqlstr = new StringBuffer("select cuserid from sm_user where user_code=?");
//			String userid_nc63 = (String) nc63Dao.executeQuery(sqlstr.toString(), params, new ColumnProcessor());
//			sqlstr = new StringBuffer("select pk_financeorg,pk_vid,pk_group from org_financeorg where code=?");
//			params.clearParams();
//			params.addParam(orgCode);
//			Map<String, String> orgInfoMap_nc63 = (Map<String, String>) nc63Dao.executeQuery(sqlstr.toString(), params,
//					new MapProcessor());
//			if (userid_nc63 == null) {
//				throw new BusinessException(String.format("NC63没有该用户[%s]", billmaker));
//			}
////			if (orgInfoMap_nc63 == null || orgInfoMap_nc63.get("pk_financeorg") == null) {
////				throw new BusinessException(String.format("NC63没有该财务组织[%s]", orgCode));
////			}
//			String pk_financeorg_nc63 = orgInfoMap_nc63.get("pk_financeorg");
//			// 查询63表体银行账户子户PK
//			for (FundBalReportBVO_NC63 bVO_NC63 : bVOList_NC63) {
//				String pk_bankaccsub = bVO_NC63.getPk_bankaccsub();
//				sqlstr = new StringBuffer("");
//				sqlstr.append("select bsb.pk_bankaccsub").append("\r\n");
//				sqlstr.append("  from bd_bankaccsub bsb").append("\r\n");
//				sqlstr.append(" inner join bd_bankaccbas bs").append("\r\n");
//				sqlstr.append("    on bsb.pk_bankaccbas = bs.pk_bankaccbas").append("\r\n");
//				sqlstr.append(" where bs.controlorg = ?").append("\r\n");
//				sqlstr.append("   and bsb.accnum = ?");
//				params.clearParams();
//				params.addParam(pk_financeorg_nc63);
//				params.addParam(pk_bankaccsub);
//				String pk_bankaccsub_nc63 = (String) nc63Dao.executeQuery(sqlstr.toString(), params,
//						new ColumnProcessor());
//				//update by guojx 去掉以下校验  2021年12月23日10:20:20
////				if (pk_bankaccsub_nc63 == null) {
////					throw new BusinessException(String.format("NC63没有该银行账户子户[%s],核算组织[%S]", pk_bankaccsub, orgCode));
////				}
//				// 重新赋值nc63
//				bVO_NC63.setPk_bankaccsub(pk_bankaccsub_nc63);
//			}
//			// 重新赋值nc63
//			hVO_NC63.setPk_org(pk_financeorg_nc63);
//			hVO_NC63.setPk_org_v(orgInfoMap_nc63.get("pk_vid"));
//			hVO_NC63.setPk_group(orgInfoMap_nc63.get("pk_group"));
//			hVO_NC63.setBillmaker(userid_nc63);
//			hVO_NC63.setCreator(userid_nc63);
//			// 插入nc63
//			nc63Dao.insertVOWithPK(hVO_NC63);
//			nc63Dao.insertVOArrayWithPK(bVOList_NC63.toArray(new FundBalReportBVO_NC63[0]));
//		}
		/** 传递NC63 谢文如 end **/
		return null;

	}

	@Override
	public Object[] doAntiConfirm(String Anticonfirmor, UFDate AnticonfirmDate, UFBoolean confirmState, String pk)
			throws BusinessException {
		BaseDAO baseDao = new BaseDAO();
		String sql = "update hzdev_fundbalancereport_h set vdef2 = ?,vdef3 = ?,confirmstatus=? ,vdef17 = '',vdef1 = '' where pk_fundreport_h = ?";
		SQLParameter params = new SQLParameter();
		params.addParam(Anticonfirmor);
		params.addParam(AnticonfirmDate);
		params.addParam(confirmState);
		params.addParam(pk);
		baseDao.executeUpdate(sql, params);
		/** 传递NC63 谢文如 begin **/
//		sql = "select t.value from pub_sysinit t where t.initcode = 'NCDs'";
//		String nc63Ds = (String)baseDao.executeQuery(sql, new ColumnProcessor());
//		BaseDAO nc63Dao = new BaseDAO(nc63Ds);
//		params.clearParams();
//		params.addParam(pk);
//
//		StringBuffer sqlstr = new StringBuffer("");
//		sqlstr.append("delete hzdev_fundbalancereport_b where pk_fundreport_h =?");
//		nc63Dao.executeUpdate(sqlstr.toString(), params);
//		sqlstr = new StringBuffer("");
//		sqlstr.append("delete hzdev_fundbalancereport_h where pk_fundreport_h =?");
//		nc63Dao.executeUpdate(sqlstr.toString(), params);
		/** 传递NC63 谢文如 end **/
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> queryOrg(String pk_org) throws BusinessException {
		BaseDAO baseDao = new BaseDAO();
		//update by guojx 现需要取定期户  s.acctype <> 1 2022年2月24日15:41:54
		String sql = "select pk_bankaccsub from bd_bankaccbas p left join bd_bankaccsub s on p.pk_bankaccbas = s.pk_bankaccbas where p.isinneracc='N' and nvl(p.accstate,99) in (0,1,2) and p.controlorg = ? and p.enablestate = 2 and p.accclass=2";
		SQLParameter params = new SQLParameter();
		params.addParam(pk_org);
		List<String> resList = (List<String>) baseDao.executeQuery(sql, params, new ColumnListProcessor());
		return resList;
	}

	// 理财产品赎回与理财金额
	@Override
	public Map<String,UFDouble> queryIfmMoney(String accountsub) throws BusinessException {
		Map<String,UFDouble> map = new HashMap<String,UFDouble>();
		map.put("applymoney_out", UFDouble.ZERO_DBL);
		map.put("applymoney_no_out", UFDouble.ZERO_DBL);
		map.put("redeemmoney_out", UFDouble.ZERO_DBL);
		map.put("redeemmoney_no_out", UFDouble.ZERO_DBL);
		
		BaseDAO baseDao = new BaseDAO();
		//登记 
		String sqlApply = "select cast(sum(money) as number(28,2)) money,decode(vdef8,'1','Y','N') fundtransferout from ifm_apply "
				+ "where settleaccount = ? and nvl(dr,0) = 0 and vbillstatus = 1 group by decode(vdef8,'1','Y','N')";
		//赎回
		String sqlRedeem = "select cast(sum(redeemmoney) as number(28,2)) money,decode(vdef8,'1','Y','N') fundtransferout from ifm_redeem "
				+ "where gatheringaccount = ? and nvl(dr,0) = 0 and vbillstatus = 1 group by decode(vdef8,'1','Y','N')";
		SQLParameter params = new SQLParameter();
		params.addParam(accountsub);
		
		List<Map<String,Object>> applyMoneyMapList = (List<Map<String,Object>>) baseDao.executeQuery(sqlApply, params, new MapListProcessor());
		List<Map<String,Object>> redeemMoneyMapList = (List<Map<String,Object>>) baseDao.executeQuery(sqlRedeem, params, new MapListProcessor());
		if(applyMoneyMapList!=null && applyMoneyMapList.size()>0) {
			for(Map<String,Object> applyMoneyMap:applyMoneyMapList) {
				BigDecimal applyMoney = (BigDecimal) applyMoneyMap.get("money");
				String fundTransferOut = (String)applyMoneyMap.get("fundtransferout");
				UFDouble money = new UFDouble(applyMoney);
				if("Y".equals(fundTransferOut)) {
					map.put("applymoney_out", money);
				}else {
					map.put("applymoney_no_out", money);
				}
			}
		}
		
		if(redeemMoneyMapList!=null && redeemMoneyMapList.size()>0) {
			for(Map<String,Object> redeemMoneyMap:redeemMoneyMapList) {
				BigDecimal applyMoney = (BigDecimal) redeemMoneyMap.get("money");
				String fundTransferOut = (String)redeemMoneyMap.get("fundtransferout");
				UFDouble money = new UFDouble(applyMoney);
				if("Y".equals(fundTransferOut)) {
					map.put("redeemmoney_out", money);
				}else {
					map.put("redeemmoney_no_out", money);
				}
			}

		}
		return map;
	}

	@Override
	public String querydef7(String accnum) throws BusinessException {
		BaseDAO baseDao = new BaseDAO();
		String sql = " select code from bd_defdoc where pk_defdoc in(select def7 from bd_bankaccbas where accnum=?)";
		SQLParameter params = new SQLParameter();
		params.addParam(accnum); 
		String result = (String) baseDao.executeQuery(sql, params, new ColumnProcessor());
		return result;
	}

	@SuppressWarnings({ "unchecked" })
	@Override
	public Map<String, Object> queryBankaccMny(String pk_org, UFDate dbilldate) throws BusinessException {
		BaseDAO baseDao = new BaseDAO();
//    		String sql5 = " select pk_contrastaccount\r\n" + 
//    				"  from (select distinct gl_contrastaccount.pk_contrastaccount  pk_contrastaccount,\r\n" + 
//    				"                        gl_contrastaccount.contrastaccountname contrastaccountname,\r\n" + 
//    				"                        gl_contrastaccount.contrastaccountname contrastaccountcode,\r\n" + 
//    				"                        bd_currtype.name                       currtypename,\r\n" + 
//    				"                        gl_contrastaccount.contrastsource      contrastsource,\r\n" + 
//    				"                        gl_contrastaccount.pk_org              pkOrg,\r\n" + 
//    				"                        gl_contrastaccount.contrastaspect      contrastaspect\r\n" + 
//    				"          from gl_contrastaccount, bd_currtype\r\n" + 
//    				"         where (gl_contrastaccount.pk_org IN (?))\r\n" + 
//    				"           and gl_contrastaccount.pk_currtype = bd_currtype.pk_currtype\r\n" + 
//    				"           and gl_contrastaccount.pk_contrastaccount in\r\n" + 
//    				"               (select gl_accountlink.pk_contrastaccount\r\n" + 
//    				"                  from gl_accountlink\r\n" + 
//    				"                 inner join gl_accountstart\r\n" + 
//    				"                    on gl_accountlink.pk_contrastaccount =\r\n" + 
//    				"                       gl_accountstart.pk_contrastaccount\r\n" + 
//    				"                 inner join gl_contrastperm\r\n" + 
//    				"                    on gl_accountlink.pk_contrastaccount =\r\n" + 
//    				"                       gl_contrastperm.pk_contrastacc\r\n" + 
//    				"                 where nvl(gl_accountlink.years, '~') <> '~'\r\n" + 
//    				"                   and gl_accountstart.years = ?\r\n" + 
//    				"                   and gl_accountstart.startdate >= ?\r\n" + 
//    				"                   and gl_accountstart.startdate < ?\r\n" + 
//    				"                   and gl_accountstart.status != '2'\r\n" + 
//    				"                   and (gl_contrastperm.pk_user = '1001H9100000000010UP' or\r\n" + 
//    				"                        iscorppublic = 'Y'))) contrastInfo\r\n" + 
//    				" where 1 = 1\r\n" + 
//    				" ";
		StringBuffer sqlstr = new StringBuffer("");
		sqlstr.append("select pk_contrastaccount").append("\r\n");
		sqlstr.append("  from (select distinct gl_contrastaccount.pk_contrastaccount  pk_contrastaccount,")
				.append("\r\n");
		sqlstr.append("                        gl_contrastaccount.contrastaccountname contrastaccountname,")
				.append("\r\n");
		sqlstr.append("                        gl_contrastaccount.contrastaccountname contrastaccountcode,")
				.append("\r\n");
		sqlstr.append("                        bd_currtype.name                       currtypename,").append("\r\n");
		sqlstr.append("                        gl_contrastaccount.contrastsource      contrastsource,").append("\r\n");
		sqlstr.append("                        gl_contrastaccount.pk_org              pkOrg,").append("\r\n");
		sqlstr.append("                        gl_contrastaccount.contrastaspect      contrastaspect").append("\r\n");
		sqlstr.append("          from gl_contrastaccount, bd_currtype").append("\r\n");
		sqlstr.append("         where (gl_contrastaccount.pk_org =?)").append("\r\n");// 组织
		sqlstr.append("           and gl_contrastaccount.pk_currtype = bd_currtype.pk_currtype").append("\r\n");
		sqlstr.append("           and gl_contrastaccount.pk_contrastaccount in").append("\r\n");
		sqlstr.append("               (select gl_accountlink.pk_contrastaccount").append("\r\n");
		sqlstr.append("                  from gl_accountlink").append("\r\n");
		sqlstr.append("                 inner join gl_accountstart").append("\r\n");
		sqlstr.append("                    on gl_accountlink.pk_contrastaccount =").append("\r\n");
		sqlstr.append("                       gl_accountstart.pk_contrastaccount").append("\r\n");
		sqlstr.append("                 inner join gl_contrastperm").append("\r\n");
		sqlstr.append("                    on gl_accountlink.pk_contrastaccount =").append("\r\n");
		sqlstr.append("                       gl_contrastperm.pk_contrastacc").append("\r\n");
		sqlstr.append("                 where nvl(gl_accountlink.years, '~') <> '~'").append("\r\n");
		sqlstr.append("                   and gl_accountstart.years = ?").append("\r\n");// 年份
		sqlstr.append("                   and gl_accountstart.startdate >= ?").append("\r\n");// 开始日期
		sqlstr.append("                   and gl_accountstart.startdate < ?").append("\r\n");// 结束日期
		sqlstr.append("                   and gl_accountstart.status != '2'").append("\r\n");
		sqlstr.append("                   and (gl_contrastperm.pk_user = ? or").append("\r\n");// 用户
		sqlstr.append("                        iscorppublic = 'Y'))) contrastInfo");

		String pk_user = AppContext.getInstance().getPkUser();
		String year = String.valueOf(dbilldate.getYear());
		UFDate nowDate = AppContext.getInstance().getBusiDate();
		SQLParameter sqlParameter = new SQLParameter();
		sqlParameter.addParam(pk_org);
		sqlParameter.addParam(year);
		sqlParameter.addParam(year + "-01-01");
		sqlParameter.addParam(year + "-12-31");
		sqlParameter.addParam(pk_user);
		List<String> pk_contrastaccounts = (List<String>) baseDao.executeQuery(sqlstr.toString(), sqlParameter,
				new ColumnListProcessor());
		Map<String, Object> rtnMap = new HashMap<>();
		if (pk_contrastaccounts.size() > 0) {
			IContrastAccountPrv accountService = NCLocator.getInstance().lookup(IContrastAccountPrv.class);
			IBankReceiptPrv bankService = NCLocator.getInstance().lookup(IBankReceiptPrv.class);
			IContrastTools4Web contrastTools = NCLocator.getInstance().lookup(IContrastTools4Web.class);
			ICurrtypeQuery currtypeService = NCLocator.getInstance().lookup(ICurrtypeQuery.class);
			// 获取指定组织业务日期对应的会计年
			String accountYear = contrastTools.getBusiDateAccountYear(pk_org, null, nowDate);
			for (String pk_contrastaccount : pk_contrastaccounts) {
				// 查询对账账户的基础数据
				GlContrastVO vo = new GlContrastVO();
				try {
					GLQueryVO queryVO = new GLQueryVO();
					queryVO.setPK_contrastaccount(new String[] { pk_contrastaccount });
					// 设置用户pk参数是为了权限的过滤
					queryVO.setPk_user(pk_user);
					queryVO.setYears(year);
					GlContrastVO[] vos = accountService.queryContrastAccount(queryVO);
					if (null != vos && vos.length > 0) {
						vo = vos[0];
					}
				} catch (BusinessException e) {
					Logger.error(e.getMessage(), e);
					ExceptionUtils.wrapBusinessException(e.getMessage());
				}
				// 得到目前的结转日期
				String nowYear = accountService.getCarry(pk_contrastaccount);
				if (nowYear == null) {
					ExceptionUtils.wrapBusinessException(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes()
							.getStrByID("3607nccloud_ncc_0", "03607nccloud_ncc-0053")/* @res "对账账户账户未启用或不存在！" */);
				}
				if (!nowYear.equals(vo.getYears())) {
					ExceptionUtils.wrapBusinessException(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes()
							.getStrByID("3607con_0", "03607con-0372")/* @res "账户已经结转！不能操作！" */);
					return null;
				}
				//
				ContrastQueryVO qvo = new ContrastQueryVO();
				qvo.setContrastaccount(pk_contrastaccount);
				qvo.setInit(ContrastQueryVO.NOTINIT); // 非期初数据
				qvo.setFlag(ContrastQueryVO.VERIFY); // 包含核销数据
				qvo.setVoucher(vo.getSource().intValue());
				qvo.setOnlyWrongRec(UFBoolean.FALSE);// 只查异常记录
				// info.setYear(newYear)
				// 设置查询时间，例如：将 2018-05-20 00:00:00 转换成 2018-05-20 23:59:59
				qvo.setDate1(new UFDate(dbilldate.toString().substring(0, 10), UFBoolean.TRUE.booleanValue()));
				qvo.setDate2(new UFDate(dbilldate.toString().substring(0, 10), UFBoolean.FALSE.booleanValue()));

				AccountlinkVO[] linkVO = null;
				try {
					// 得到此对账账户此启用年度所有关联的实体，既所有关联的银行账户等
					linkVO = accountService.queryAccountLink(pk_contrastaccount, accountYear);
					int ifManyUnit = 1; // 账户是否多单位对账
					if (vo.getMemo() != null && vo.getMemo().trim().equals("one")) { // 非多单位对账
						ifManyUnit = 1;
						// info.setM_pk_link(linkVO != null && linkVO.length > 0 ?
						// linkVO[0].getPk_accountlink() : "");
					} else if (vo.getMemo() != null
							&& (vo.getMemo().trim().equals("many") || vo.getMemo().trim().equals("merge"))) { // 多单位对账
						ifManyUnit = 2;
					} else {
						ifManyUnit = 1;
					}
					qvo.setManyCorp(ifManyUnit); // 设置是否多单位对账
					qvo.setAspect(-1); // 金额方向 0借方 1贷方 -1全部
					// 根据以上设置的查询条件查询对账单
					// yangxds 查询银行对账数据 增加 交易流水号
					BankReceiptVO[] vt = bankService.queryBankConditionInit(qvo);
					// 查询期初日期到起始日期的发生额
					UFDouble yearStartDateOccurMnt = accountService.getBankMoney(pk_contrastaccount, accountYear,
							qvo.getDate1(), 1);
					// 计算对账账户期初余额
					UFDouble nowB = UFDouble.ZERO_DBL;
					if (new UFDouble(vo.getCreditamount().toString()).doubleValue() == 0.0
							&& new UFDouble(vo.getDebitamount().toString()).doubleValue() == 0.0) {
						if (vo.getContrastaspect().intValue() == 1) {
							yearStartDateOccurMnt = new UFDouble(0).sub(yearStartDateOccurMnt);
						}
						// 年度期初金额 + 年度期初日期到查询起始日期的发生额
						UFDouble dTemp = vo.getDebitamount().add(yearStartDateOccurMnt);
						nowB = calcInitMnt(dTemp, vt, vo);
					} else if (vo.getContrastaspect().intValue() == 0 && vo.getDebitamount() != null
							&& new UFDouble(vo.getCreditamount().toString()).doubleValue() == 0.0) {
						UFDouble dTemp = vo.getDebitamount().add(yearStartDateOccurMnt);
						nowB = calcInitMnt(dTemp, vt, vo);
					} else if (vo.getContrastaspect().intValue() == 1 && vo.getCreditamount() != null
							&& new UFDouble(vo.getDebitamount().toString()).doubleValue() == 0.0) {
						UFDouble dTemp = vo.getCreditamount().sub(yearStartDateOccurMnt);
						nowB = calcInitMnt(dTemp, vt, vo);
					} else {
						nowB = UFDouble.ZERO_DBL;
					}
					// 精度处理
					int precision = 2;
					// 得到银行账户的币种
					CurrtypeVO currtypeVO = currtypeService.findCurrtypeVOByPK(vo.getPk_currtype());
					if (currtypeVO != null) {
						precision = currtypeVO.getCurrdigit();
					}
					// 收款合计
					UFDouble m_debitamount = UFDouble.ZERO_DBL;
					// 付款合计
					UFDouble m_creditamount = UFDouble.ZERO_DBL;
					if (vt != null && vt.length > 0) {
						// 设置银行对账单余额
						// vt = setBalanceVO(vt, vo, nowB, precision);
						// vt = fileterBankReceipt(vt, info);
						// 规范界面显示数据
						// normBankReceiptVO(vt, nowB, precision);
						for (int i = 0; i < vt.length; i++) {
							if (vt[i].getM_creditamount().equals(UFDouble.ZERO_DBL)) {
								// vt[i].setM_creditamount(null);
								vt[i].setM_debitamount(
										vt[i].getM_debitamount().setScale(precision, UFDouble.ROUND_HALF_UP));
							}
							if (vt[i].getM_debitamount().equals(UFDouble.ZERO_DBL)) {
								// vt[i].setM_debitamount(null);
								vt[i].setM_creditamount(
										vt[i].getM_creditamount().setScale(precision, UFDouble.ROUND_HALF_UP));
							}
							m_debitamount = m_debitamount.add(vt[i].getM_debitamount());
							m_creditamount = m_creditamount.add(vt[i].getM_creditamount());
						}
					}
					// 控制期初余额界面显示精度
					nowB = nowB.setScale(precision, UFDouble.ROUND_HALF_UP);
					String pk_bankaccount = linkVO != null && linkVO.length > 0 ? linkVO[0].getM_bankaccount() : "";
					// 组装查询结果
					Map<String, Object> rtnMapE = new HashMap<>();
					rtnMapE.put("debitamount", m_debitamount);
					rtnMapE.put("creditamount", m_creditamount);
					rtnMapE.put("balamount", nowB);
					rtnMap.put(pk_bankaccount, rtnMapE);
				} catch (Throwable er) {
					Logger.error(er.getMessage(), er);
					ExceptionUtils.wrapBusinessException(er.getMessage());
				}
			}
		}
		return rtnMap;
	}

	/**
	 * 计算期初金额,查询出来的数据会包含期初金额以前的金额. 使用期初金额-发生额
	 * 
	 * @param yearMnt
	 * @param vt
	 * @param vo
	 * @return
	 */
	private UFDouble calcInitMnt(UFDouble yearMnt, BankReceiptVO[] vt, GlContrastVO vo) {
		String year = vo.getYears();
		UFDouble totalOccurMnt = UFDouble.ZERO_DBL;
		for (BankReceiptVO bankReceiptVO : vt) {
			// 发生金额,收入不为空时,取收入金额,收入为空取负的支出金额
			// 目前来看，这个if判断是没有用的，因为我们查出来的银行对账单
			// 已经在前面有过过滤了（最早的时间就是账户的启用时间），所以不可能能查出来账单的发生时间在对账账户的启用年限之前的
			if (bankReceiptVO.getYears().compareTo(year) < 0) {
				UFDouble occurMnt = !UFDoubleUtils.isNullOrZero(bankReceiptVO.getDebitamount())
						? bankReceiptVO.getDebitamount()
						: UFDoubleUtils.sub(UFDouble.ZERO_DBL, bankReceiptVO.getCreditamount());
				totalOccurMnt = UFDoubleUtils.add(totalOccurMnt, occurMnt);
			}
		}
		// 判断对账方向
		if (vo.getContrastaspect().intValue() == 1) {
			totalOccurMnt = UFDoubleUtils.sub(UFDouble.ZERO_DBL, totalOccurMnt);
		}
		return UFDoubleUtils.sub(yearMnt, totalOccurMnt);
	}

}
