//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package nccloud.pubimpl.erm.billmanage;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.bs.logging.Logger;
import nc.itf.arap.prv.IBXBillPrivate;
import nc.itf.uap.IUAPQueryBS;
import nc.itf.uap.rbac.IUserManageQuery;
import nc.jdbc.framework.DataSourceCenter;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.page.LimitSQLBuilder;
import nc.jdbc.framework.page.SQLBuilderFactory;
import nc.jdbc.framework.processor.ArrayListProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.pubitf.rbac.IFunctionPermissionPubService;
import nc.ui.querytemplate.querytree.IQueryScheme;
import nc.vo.ep.bx.BXHeaderVO;
import nc.vo.ep.bx.BillManageTradeTypeVO;
import nc.vo.ep.bx.JKBXHeaderVO;
import nc.vo.fi.pub.SqlUtils;
import nc.vo.ml.MultiLangUtil;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.pub.lang.UFDouble;
import nc.vo.sm.UserVO;
import nccloud.dto.baseapp.querytree.dataformat.Condition;
import nccloud.dto.baseapp.querytree.dataformat.PageInfo;
import nccloud.dto.baseapp.querytree.dataformat.QueryCondition;
import nccloud.dto.baseapp.querytree.dataformat.QueryTreeFormatVO;
import nccloud.pubitf.erm.billmanage.IBillManageService;
import nccloud.pubitf.platform.query.INCCloudQueryService;
import org.apache.commons.lang3.StringUtils;

public class BillManageServiceImpl implements IBillManageService {
    private final String APPCODE_DJGL = "201104DJGL";
    private final String SQLKEY_JK = "JK";
    private final String SQLKEY_BX = "BX";
    private final String SQLKEY_HK = "HK";
    private final String SQLKEY_TZ = "TZ";
    private final String SQLKEY_SQ = "SQ";
    private final String SQLKEY_YT = "YT";
    private final String SQLKEY_JZ = "JZ";
    private final String SQLKEY_TX = "TX";

    public BillManageServiceImpl() {
    }

    public List<JKBXHeaderVO> queryBillInfoByPks(String[] pks) throws Exception {
        if (pks != null && pks.length != 0) {
            String whereSql = SqlUtils.getInStr("pk_jkbx", pks, true);
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("select *").append("        from (select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_jkzb where dr = 0 ").append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb where dr = 0 ").append("                union all").append("                select pk_accrued_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype    as djlxbm,").append("                pk_billtype    as pk_billtype,").append("                billno          as djbh,").append("                billdate        as djrq,").append("                pk_currtype     as bzbm,").append("                org_amount      as ybje,").append("                billstatus      as djzt,").append("                apprstatus      as spzt,").append("                operator_dept   as deptid_v,").append("                operator        as jkbxr,").append("                approver        as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                redflag     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime     as shrq").append("                from er_accrued where dr=0 ").append("                union all").append("                select pk_mtapp_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype  as djlxbm,").append("                pk_billtype  as pk_billtype,").append("                billno        as djbh,").append("                billdate      as djrq,").append("                pk_currtype   as bzbm,").append("                orig_amount   as ybje,").append("                billstatus    as djzt,").append("                apprstatus    as spzt,").append("                apply_dept    as deptid_v,").append("                billmaker     as jkbxr,").append("                approver      as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                null     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime   as shrq").append("                from er_mtapp_bill where dr = 0 ").append("                union all").append("                select pk_costshare  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno     as djbh,").append("                billdate     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                billstatus     as djzt,").append(" case when billstatus=2 or billstatus=3 then 1 else -1 end as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                null     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvedate     as shrq").append("                from er_costshare").append("               where dr = 0 ").append("                union all").append("                select e.pk_jkbx  as pk_jkbx,").append("                t.pk_billtypeid   as pk_tradetypeid,").append("                e.bx_pk_billtype   as djlxbm,").append("                t.parentbilltype   as pk_billtype,").append("                e.bx_billno     as djbh,").append("                e.bx_djrq     as djrq,").append("                e.bzbm     as bzbm,").append("                e.total_amount     as ybje,").append("                3     as djzt,").append(" \t\t\t\t 1\t\t as spzt,").append("                e.bx_deptid as deptid_v,").append("                e.bx_jkbxr    as jkbxr,").append("                null as approver,").append("                e.pk_org     as pk_org, ").append("                e.creator     as creator, ").append("                null     as red_status, ").append("                e.saga_frozen     as saga_frozen, ").append("                e.saga_btxid     as saga_btxid, ").append("                e.saga_gtxid     as saga_gtxid, ").append("                e.saga_status     as saga_status, ").append("                null     as shrq").append("                from er_expamtinfo e").append(" left join bd_billtype t on t.PK_BILLTYPECODE=e.bx_pk_billtype and t.PK_GROUP=e.PK_GROUP").append("               where e.dr = 0 ").append("                ) temp where ").append(whereSql);
            sqlBuilder.append(" order by temp.djrq desc");
            String sql = sqlBuilder.toString();
            SQLParameter sqlParam = new SQLParameter();
            IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
            List<JKBXHeaderVO> resultList = (List)iuapQryService.executeQuery(sql, sqlParam, new JKBXHeaderResultSetProcessor());
            return resultList;
        } else {
            return null;
        }
    }

    public List<BillManageTradeTypeVO> queryPermBillType(String userId, String currentDate, String pkGroup) {
        List<BillManageTradeTypeVO> permBillTypeList = new ArrayList();

        try {
            String sql = " select distinct pub_publishapp.pk_billtypecode as pk_billtypecode,sm_appregister.code as apppcode,sm_apppage.pagecode as pagecode,sm_apppage.pageurl as url, bd_billtype.parentbilltype as parentbilltype, bd_billtype.pk_billtypeid as pk_billtypeid, bd_billtype.billtypename" + MultiLangUtil.getCurrentLangSeqSuffix() + " as billtypename   from sm_appregister  inner join pub_publishapp on sm_appregister.pk_appregister=pub_publishapp.publishappid  inner join sm_apppage on sm_apppage.parent_id=sm_appregister.pk_appregister  inner join bd_billtype on pub_publishapp.pk_billtypecode=bd_billtype.pk_billtypecode  where sm_appregister.pk_appregister in (    select distinct ra.pk_app from sm_resp_app ra, sm_perm_func pf, sm_appregister app    where ra.pk_responsibility = pf.ruleid and app.pk_appregister = ra.pk_app and exists (       select 1 from sm_user_role ur where pf.subjectid = ur.pk_role and cuserid = ?       and enabledate <= ? and ( isnull(cast(disabledate as char),'~')='~' or disabledate > ?))       and app.isenable = 'Y' and ( isnull(app.pk_group,'~') = '~' or app.pk_group = ?)        and pf.pk_group = ? and app.own_module='2011') and pub_publishapp.sence=10 and pub_publishapp.dr=0 and bd_billtype.parentbilltype in('261X','262X','263X','264X','265X','266X') and bd_billtype.pk_billtypecode != '264X-Cxx-SKBG' and sm_apppage.isdefault='Y' and bd_billtype.pk_group= ? ";
            SQLParameter sqlParam = new SQLParameter();
            sqlParam.addParam(userId);
            sqlParam.addParam(currentDate);
            sqlParam.addParam(currentDate);
            sqlParam.addParam(pkGroup);
            sqlParam.addParam(pkGroup);
            sqlParam.addParam(pkGroup);
            IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
            permBillTypeList = (List)iuapQryService.executeQuery(sql, sqlParam, new ResultSetProcessor() {
                private static final long serialVersionUID = 1L;

                public Object handleResultSet(ResultSet rs) throws SQLException {
                    List<BillManageTradeTypeVO> resultList;
                    BillManageTradeTypeVO billManageTradeTypeVO;
                    for(resultList = new ArrayList(); rs.next(); resultList.add(billManageTradeTypeVO)) {
                        billManageTradeTypeVO = new BillManageTradeTypeVO();
                        String pk_billtypecode = rs.getString("pk_billtypecode");
                        String appcode = rs.getString("apppcode");
                        String pagecode = rs.getString("pagecode");
                        String url = rs.getString("url");
                        String parentbilltype = rs.getString("parentbilltype");
                        billManageTradeTypeVO.setPk_billtypecode(pk_billtypecode);
                        billManageTradeTypeVO.setAppcode(appcode);
                        billManageTradeTypeVO.setPagecode(pagecode);
                        billManageTradeTypeVO.setUrl(url);
                        billManageTradeTypeVO.setParentbilltype(parentbilltype);
                        billManageTradeTypeVO.setPk_billtypeid(rs.getString("pk_billtypeid"));
                        billManageTradeTypeVO.setBilltypename(rs.getString("billtypename"));
                        if ("263X".equals(parentbilltype)) {
                            billManageTradeTypeVO.setJkbx("jk");
                        } else if ("264X".equals(parentbilltype)) {
                            billManageTradeTypeVO.setJkbx("bx");
                        }
                    }

                    return resultList;
                }
            });
        } catch (BusinessException e) {
            Logger.error(e.getMessage(), e);
        }

        return permBillTypeList;
    }

    private String[] queryPkPsndocByNameLike(String keyWord) throws Exception {
        String sql = "select pk_psndoc from bd_psndoc where ( dr = 0 or dr is null ) and name like ? ";
        SQLParameter sqlParam = new SQLParameter();
        sqlParam.addParam("%" + keyWord + "%");
        IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
        String[] resultPsndoc = (String[])iuapQryService.executeQuery(sql, sqlParam, new ResultSetProcessor() {
            private static final long serialVersionUID = 1L;

            public Object handleResultSet(ResultSet rs) throws SQLException {
                List<String> resultList = new ArrayList();

                while(rs.next()) {
                    String pkPsndoc = rs.getString("pk_psndoc");
                    resultList.add(pkPsndoc);
                }

                return resultList.size() == 0 ? new String[0] : resultList.toArray(new String[resultList.size()]);
            }
        });
        return resultPsndoc;
    }

    private String[] queryPkDeptByNameLike(String keyWord) throws Exception {
        String sql = "select pk_dept, name from org_dept where ( dr = 0 or dr is null ) and name like ? ";
        SQLParameter sqlParam = new SQLParameter();
        sqlParam.addParam("%" + keyWord + "%");
        IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
        String[] resultPkDept = (String[])iuapQryService.executeQuery(sql, sqlParam, new ResultSetProcessor() {
            private static final long serialVersionUID = 1L;

            public Object handleResultSet(ResultSet rs) throws SQLException {
                List<String> resultList = new ArrayList();

                while(rs.next()) {
                    String pkDept = rs.getString("pk_dept");
                    resultList.add(pkDept);
                }

                return resultList.size() == 0 ? new String[0] : resultList.toArray(new String[resultList.size()]);
            }
        });
        return resultPkDept;
    }

    private Map<String, String> geConditionSqlByConditions(QueryTreeFormatVO queryTreeFormatVO) throws BusinessException {
        Map<String, String> whereSqlMap = new HashMap();
        QueryCondition queryCondition = queryTreeFormatVO.getQuerycondition();
        if (queryCondition != null) {
            List<Condition> conditions = queryCondition.getConditions();
            if (conditions != null && conditions.size() > 0) {
                StringBuilder conditionBuilderJK = new StringBuilder("");
                StringBuilder conditionBuilderBX = new StringBuilder(" and djlxbm!='264a' and djlxbm!='2647' and djlxbm != '264X-Cxx-SKBG'");
                StringBuilder conditionBuilderHK = new StringBuilder(" and djlxbm='2647' ");
                StringBuilder conditionBuilderTZ = new StringBuilder(" and djlxbm='264a' ");
                StringBuilder conditionBuilderSQ = new StringBuilder("");
                StringBuilder conditionBuilderYT = new StringBuilder("");
                StringBuilder conditionBuilderJZ = new StringBuilder("");
                StringBuilder conditionBuilderTX = new StringBuilder("");
                String appCode = queryTreeFormatVO.getAppcode();
                if ("201104DJGL".equals(appCode)) {
                    String userId = InvocationInfoProxy.getInstance().getUserId();
                    String pk_group = InvocationInfoProxy.getInstance().getGroupId();
                    String jkbxr = "";
                    String[] psnInfos = ((IBXBillPrivate)NCLocator.getInstance().lookup(IBXBillPrivate.class)).queryPsnidAndDeptid(userId, pk_group);
                    if (psnInfos != null && psnInfos.length > 0) {
                        jkbxr = psnInfos[0];
                    }

                    conditionBuilderJK.append(" and (creator='").append(userId).append("' or jkbxr='").append(jkbxr).append("' ) ");
                    conditionBuilderBX.append(" and (creator='").append(userId).append("' or jkbxr='").append(jkbxr).append("' ) ");
                    conditionBuilderHK.append(" and creator='").append(userId).append("' ");
                    conditionBuilderTZ.append(" and creator='").append(userId).append("' ");
                    conditionBuilderSQ.append(" and creator='").append(userId).append("' ");
                    conditionBuilderYT.append(" and creator='").append(userId).append("' ");
                    conditionBuilderJZ.append(" and (creator='").append(userId).append("' or jkbxr='").append(jkbxr).append("' ) ");
                    conditionBuilderTX.append(" and (e.creator='").append(userId).append("' or e.bx_jkbxr='").append(jkbxr).append("' ) ");
                }

                boolean ifHasTradeType = false;
                boolean filterByPkOrg = false;
                String reconSQ = "1=2";
                String reconYT = "1=2";
                String reconJK = "1=2";
                String reconBX = "1=2";
                String reconHK = "1=2";
                String reconTZ = "1=2";
                String reconJZ = "1=2";
                String reconTX = "1=2";
                boolean hasRecon = false;

                for(Condition condition : conditions) {
                    if ("261X".equals(condition.getField())) {
                        reconSQ = SqlUtils.getInStr("pk_mtapp_bill", condition.getValue().getFirstvalue().split(","), true);
                        hasRecon = true;
                    } else if ("262X".equals(condition.getField())) {
                        reconYT = SqlUtils.getInStr("pk_accrued_bill", condition.getValue().getFirstvalue().split(","), true);
                        hasRecon = true;
                    } else if ("263X".equals(condition.getField())) {
                        reconJK = SqlUtils.getInStr("pk_jkbx", condition.getValue().getFirstvalue().split(","), true);
                        hasRecon = true;
                    } else if ("264X".equals(condition.getField())) {
                        reconBX = reconHK = reconTZ = SqlUtils.getInStr("pk_jkbx", condition.getValue().getFirstvalue().split(","), true);
                        hasRecon = true;
                    } else if ("265X".equals(condition.getField())) {
                        hasRecon = true;
                        reconJZ = SqlUtils.getInStr("pk_costshare", condition.getValue().getFirstvalue().split(","), true);
                    } else if ("266X".equals(condition.getField())) {
                        hasRecon = true;
                        reconTX = SqlUtils.getInStr("e.pk_expamtinfo", condition.getValue().getFirstvalue().split(","), true);
                    } else if ("isReconEmpty".equals(condition.getField())) {
                        hasRecon = true;
                    } else {
                        if ("pk_org".equals(condition.getField())) {
                            filterByPkOrg = true;
                            if (StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                                String[] orgs = condition.getValue().getFirstvalue().split(",");
                                String inSql = SqlUtils.getInStr("pk_org", orgs, true);
                                conditionBuilderJK.append(" and ").append(inSql);
                                conditionBuilderBX.append(" and ").append(inSql);
                                conditionBuilderHK.append(" and ").append(inSql);
                                conditionBuilderTZ.append(" and ").append(inSql);
                                conditionBuilderSQ.append(" and ").append(inSql);
                                conditionBuilderYT.append(" and ").append(inSql);
                                conditionBuilderJZ.append(" and ").append(inSql);
                                conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("e.pk_org", orgs, true));
                            }
                        }
                        if ("defitem11".equals(condition.getField())) {
                            filterByPkOrg = true;
                            if (StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                                String[] orgs = condition.getValue().getFirstvalue().split(",");
//                                String inSql = SqlUtils.getInStr("pk_org", orgs, true); er_mtapp_bill  assume_dept
                                conditionBuilderJK.append(" and ").append(SqlUtils.getInStr("fydeptid", orgs, true));
                                conditionBuilderBX.append(" and ").append(SqlUtils.getInStr("fydeptid", orgs, true));
                                conditionBuilderHK.append(" and ").append(SqlUtils.getInStr("fydeptid", orgs, true));
                                conditionBuilderTZ.append(" and ").append(SqlUtils.getInStr("fydeptid", orgs, true));
                                conditionBuilderSQ.append(" and ").append(SqlUtils.getInStr("assume_dept", orgs, true));
                                conditionBuilderYT.append(" and ").append(SqlUtils.getInStr("operator_dept", orgs, true));
                                conditionBuilderJZ.append(" and ").append(SqlUtils.getInStr("fydeptid", orgs, true));
                                conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("e.bx_deptid", orgs, true));
                            }
                        }

                        if ("djrq".equals(condition.getField())) {
                            if (StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                                conditionBuilderJK.append(" and djrq >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderBX.append(" and djrq >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderHK.append(" and djrq >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderTZ.append(" and djrq >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderSQ.append(" and billdate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderYT.append(" and billdate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderJZ.append(" and billdate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderTX.append(" and e.bx_djrq >= '").append(condition.getValue().getFirstvalue()).append("' ");
                            }

                            if (StringUtils.isNotEmpty(condition.getValue().getSecondvalue())) {
                                conditionBuilderJK.append(" and djrq <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderBX.append(" and djrq <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderHK.append(" and djrq <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderTZ.append(" and djrq <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderSQ.append(" and billdate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderYT.append(" and billdate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderJZ.append(" and billdate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderTX.append(" and e.bx_djrq <= '").append(condition.getValue().getSecondvalue()).append("' ");
                            }
                        }


                        if ("paydate".equals(condition.getField())) {
                            if (StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                                conditionBuilderJK.append(" and paydate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderBX.append(" and paydate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderHK.append(" and paydate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderTZ.append(" and paydate >= '").append(condition.getValue().getFirstvalue()).append("' ");
                            }

                            if (StringUtils.isNotEmpty(condition.getValue().getSecondvalue())) {
                                conditionBuilderJK.append(" and paydate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderBX.append(" and paydate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderHK.append(" and paydate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderTZ.append(" and paydate <= '").append(condition.getValue().getSecondvalue()).append("' ");
                            }
                        }

                        if ("djbh".equals(condition.getField()) && StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                            conditionBuilderJK.append(" and djbh like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderHK.append(" and djbh like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderBX.append(" and djbh like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderTZ.append(" and djbh like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderSQ.append(" and billno like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderYT.append(" and billno like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderJZ.append(" and billno like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                            conditionBuilderTX.append(" and e.bx_billno like '%").append(condition.getValue().getFirstvalue()).append("%' ");
                        }

                        if ("pk_tradetypeid".equals(condition.getField()) && StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                            ifHasTradeType = true;
                            String[] tradetypes = condition.getValue().getFirstvalue().split(",");
                            String inSql = SqlUtils.getInStr("pk_tradetypeid", tradetypes, true);
                            conditionBuilderJK.append(" and ").append(inSql);
                            conditionBuilderBX.append(" and ").append(inSql);
                            conditionBuilderHK.append(" and ").append(inSql);
                            conditionBuilderTZ.append(" and ").append(inSql);
                            conditionBuilderSQ.append(" and ").append(inSql);
                            conditionBuilderYT.append(" and ").append(inSql);
                            conditionBuilderJZ.append(" and ").append(inSql);
                            conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("t.pk_billtypeid", tradetypes, true));
                        }

                        if ("ybje".equals(condition.getField())) {
                            if (StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                                conditionBuilderJK.append(" and ybje >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderBX.append(" and ybje >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderHK.append(" and hkybje >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderTZ.append(" and ybje >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderSQ.append(" and orig_amount\t >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderYT.append(" and org_amount >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderJZ.append(" and ybje >= '").append(condition.getValue().getFirstvalue()).append("' ");
                                conditionBuilderTX.append(" and e.total_amount >= '").append(condition.getValue().getFirstvalue()).append("' ");
                            }

                            if (StringUtils.isNotEmpty(condition.getValue().getSecondvalue())) {
                                conditionBuilderJK.append(" and ybje <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderBX.append(" and ybje <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderHK.append(" and hkybje <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderTZ.append(" and ybje <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderSQ.append(" and orig_amount <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderYT.append(" and org_amount <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderJZ.append(" and ybje <= '").append(condition.getValue().getSecondvalue()).append("' ");
                                conditionBuilderTX.append(" and e.total_amount <= '").append(condition.getValue().getSecondvalue()).append("' ");
                            }
                        }

                        if ("bzbm".equals(condition.getField()) && StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                            String[] bzbms = condition.getValue().getFirstvalue().split(",");
                            conditionBuilderJK.append(" and ").append(SqlUtils.getInStr("bzbm", bzbms, true));
                            conditionBuilderBX.append(" and ").append(SqlUtils.getInStr("bzbm", bzbms, true));
                            conditionBuilderHK.append(" and ").append(SqlUtils.getInStr("bzbm", bzbms, true));
                            conditionBuilderTZ.append(" and ").append(SqlUtils.getInStr("bzbm", bzbms, true));
                            conditionBuilderSQ.append(" and ").append(SqlUtils.getInStr("pk_currtype", bzbms, true));
                            conditionBuilderYT.append(" and ").append(SqlUtils.getInStr("pk_currtype", bzbms, true));
                            conditionBuilderJZ.append(" and ").append(SqlUtils.getInStr("bzbm", bzbms, true));
                            conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("e.bzbm", bzbms, true));
                        }

                        if ("djzt".equals(condition.getField()) && StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                            List<String> list = Arrays.asList(condition.getValue().getFirstvalue().split(","));
                            conditionBuilderJK.append(" and ").append(SqlUtils.getInStr("djzt", list, true));
                            conditionBuilderBX.append(" and ").append(SqlUtils.getInStr("djzt", list, true));
                            conditionBuilderHK.append(" and ").append(SqlUtils.getInStr("djzt", list, true));
                            conditionBuilderJZ.append(" and ").append(SqlUtils.getInStr("billstatus", list, true));
                            list = new ArrayList(list);
                            if (list.contains("3")) {
                                list.remove("3");
                            }

                            if (list.contains("2")) {
                                Collections.replaceAll(list, "2", "3");
                            }

                            conditionBuilderTZ.append(" and ").append(SqlUtils.getInStr("djzt", list, true));
                            conditionBuilderSQ.append(" and ").append(SqlUtils.getInStr("billstatus", list, true));
                            conditionBuilderYT.append(" and ").append(SqlUtils.getInStr("billstatus", list, true));
                            conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("3", list, true));
                        }

                        if ("spzt".equals(condition.getField()) && StringUtils.isNotEmpty(condition.getValue().getFirstvalue())) {
                            List<String> list = Arrays.asList(condition.getValue().getFirstvalue().split(","));
                            conditionBuilderJK.append(" and ").append(SqlUtils.getInStr("spzt", list, true));
                            conditionBuilderBX.append(" and ").append(SqlUtils.getInStr("spzt", list, true));
                            conditionBuilderHK.append(" and ").append(SqlUtils.getInStr("spzt", list, true));
                            conditionBuilderTZ.append(" and ").append(SqlUtils.getInStr("spzt", list, true));
                            conditionBuilderSQ.append(" and ").append(SqlUtils.getInStr("apprstatus", list, true));
                            conditionBuilderYT.append(" and ").append(SqlUtils.getInStr("apprstatus", list, true));
                            conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("1", list, true));
                        }
                    }
                }

                if (hasRecon) {
                    conditionBuilderJK.append(" and ").append(reconJK);
                    conditionBuilderBX.append(" and ").append(reconBX);
                    conditionBuilderHK.append(" and ").append(reconHK);
                    conditionBuilderTZ.append(" and ").append(reconTZ);
                    conditionBuilderSQ.append(" and ").append(reconSQ);
                    conditionBuilderYT.append(" and ").append(reconYT);
                    conditionBuilderJZ.append(" and ").append(reconJZ);
                    conditionBuilderTX.append(" and ").append(reconTX);
                } else {
                    if (!ifHasTradeType) {
                        String groupPk = InvocationInfoProxy.getInstance().getGroupId();
                        String userId = InvocationInfoProxy.getInstance().getUserId();
                        String currentDate = (new UFDateTime()).toLocalString();
                        List<BillManageTradeTypeVO> permBillTypeList = ((IBillManageService)NCLocator.getInstance().lookup(IBillManageService.class)).queryPermBillType(userId, currentDate, groupPk);
                        if (permBillTypeList != null && permBillTypeList.size() > 0) {
                            String[] hasPermissionTradeTypecode = new String[permBillTypeList.size()];

                            for(int i = 0; i < permBillTypeList.size(); ++i) {
                                hasPermissionTradeTypecode[i] = ((BillManageTradeTypeVO)permBillTypeList.get(i)).getPk_billtypeid();
                            }

                            String inSql = SqlUtils.getInStr("pk_tradetypeid", hasPermissionTradeTypecode, true);
                            conditionBuilderJK.append(" and ").append(inSql);
                            conditionBuilderBX.append(" and ").append(inSql);
                            conditionBuilderHK.append(" and ").append(inSql);
                            conditionBuilderTZ.append(" and ").append(inSql);
                            conditionBuilderSQ.append(" and ").append(inSql);
                            conditionBuilderYT.append(" and ").append(inSql);
                            conditionBuilderJZ.append(" and ").append(inSql);
                            conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("t.pk_billtypeid", hasPermissionTradeTypecode, true));
                        } else {
                            conditionBuilderJK.append(" and ").append("1=2");
                            conditionBuilderBX.append(" and ").append("1=2");
                            conditionBuilderHK.append(" and ").append("1=2");
                            conditionBuilderTZ.append(" and ").append("1=2");
                            conditionBuilderSQ.append(" and ").append("1=2");
                            conditionBuilderYT.append(" and ").append("1=2");
                            conditionBuilderJZ.append(" and ").append("1=2");
                            conditionBuilderTX.append(" and ").append("1=2");
                        }
                    }

                    if (!filterByPkOrg) {
                        IFunctionPermissionPubService service = (IFunctionPermissionPubService)NCLocator.getInstance().lookup(IFunctionPermissionPubService.class);
                        String[] orgByIDs = service.getUserPermissionPkOrgs(InvocationInfoProxy.getInstance().getUserId(), (String)null, InvocationInfoProxy.getInstance().getGroupId());
                        String inSql = SqlUtils.getInStr("pk_org", orgByIDs, true);
                        conditionBuilderJK.append(" and ").append(inSql);
                        conditionBuilderBX.append(" and ").append(inSql);
                        conditionBuilderHK.append(" and ").append(inSql);
                        conditionBuilderTZ.append(" and ").append(inSql);
                        conditionBuilderSQ.append(" and ").append(inSql);
                        conditionBuilderYT.append(" and ").append(inSql);
                        conditionBuilderJZ.append(" and ").append(inSql);
                        conditionBuilderTX.append(" and ").append(SqlUtils.getInStr("e.pk_org", orgByIDs, true));
                    }
                }

                whereSqlMap.put("JK", conditionBuilderJK.toString());
                whereSqlMap.put("BX", conditionBuilderBX.toString());
                whereSqlMap.put("HK", conditionBuilderHK.toString());
                whereSqlMap.put("TZ", conditionBuilderTZ.toString());
                whereSqlMap.put("SQ", conditionBuilderSQ.toString());
                whereSqlMap.put("YT", conditionBuilderYT.toString());
                whereSqlMap.put("JZ", conditionBuilderJZ.toString());
                whereSqlMap.put("TX", conditionBuilderTX.toString());
            }
        }

        return whereSqlMap;
    }

    public List<JKBXHeaderVO> queryBillInfoByPage(QueryTreeFormatVO queryTreeFormatVO) throws Exception {
        INCCloudQueryService ncCloudQueryService = (INCCloudQueryService)NCLocator.getInstance().lookup(INCCloudQueryService.class);
        PageInfo pageInfo = null;
        if (queryTreeFormatVO != null) {
            pageInfo = queryTreeFormatVO.getPageInfo();
        }

        IQueryScheme queryscheme = ncCloudQueryService.convertCondition(queryTreeFormatVO);
        if (queryscheme == null) {
            return null;
        } else {
            SQLParameter sqlParam = new SQLParameter();
            StringBuilder conditionBuilder = new StringBuilder();
            String conditionSql = null;
            QueryCondition queryCondition = queryTreeFormatVO.getQuerycondition();
            List<Condition> conditions = queryCondition.getConditions();
            if (conditions != null && conditions.size() > 0) {
                for(Condition condition : conditions) {
                    if (condition != null && condition.getValue() != null && "search".equals(condition.getField()) && !StringUtils.isEmpty(condition.getValue().getFirstvalue())) {
                        String keyWord = condition.getValue().getFirstvalue();
                        conditionBuilder.append("and (  djbh like ? ");
                        sqlParam.addParam("%" + keyWord + "%");
                        String[] pkDepts = this.queryPkDeptByNameLike(keyWord);
                        String[] pkPsndocs = this.queryPkPsndocByNameLike(keyWord);
                        if (pkDepts != null && pkDepts.length > 0) {
                            conditionBuilder.append(" or " + SqlUtils.getInStr("deptid_v", pkDepts, true));
                        }

                        if (pkPsndocs != null && pkPsndocs.length > 0) {
                            conditionBuilder.append(" or " + SqlUtils.getInStr("jkbxr", pkPsndocs, true));
                        }

                        conditionBuilder.append(")");
                    }
                }
            }

            conditionSql = conditionBuilder.toString();
            Map<String, String> sqlMap = this.geConditionSqlByConditions(queryTreeFormatVO);
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("select *").append("        from (select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_jkzb").append("               where dr = 0 ").append(null == sqlMap.get("JK") ? "" : (String)sqlMap.get("JK")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("    \t\t\t ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("BX") ? "" : (String)sqlMap.get("BX")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                hkybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("HK") ? "" : (String)sqlMap.get("HK")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                red_status     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("TZ") ? "" : (String)sqlMap.get("TZ")).append("                union all").append("                select pk_accrued_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype    as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno          as djbh,").append("                billdate        as djrq,").append("                pk_currtype     as bzbm,").append("                org_amount      as ybje,").append("                billstatus      as djzt,").append("                apprstatus      as spzt,").append("                operator_dept   as deptid_v,").append("                operator        as jkbxr,").append("                approver        as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                redflag     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime     as shrq").append("                from er_accrued").append("               where dr = 0 ").append(null == sqlMap.get("YT") ? "" : (String)sqlMap.get("YT")).append("                union all").append("                select pk_mtapp_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype  as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno        as djbh,").append("                billdate      as djrq,").append("                pk_currtype   as bzbm,").append("                orig_amount   as ybje,").append("                billstatus    as djzt,").append("                apprstatus    as spzt,").append("                apply_dept    as deptid_v,").append("                billmaker     as jkbxr,").append("                approver      as approver,").append("                pk_org      as pk_org, ").append("                creator     as creator, ").append("                null     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime   as shrq").append("                from er_mtapp_bill").append("                where dr = 0 ").append(null == sqlMap.get("SQ") ? "" : (String)sqlMap.get("SQ")).append("                union all").append("                select pk_costshare  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno     as djbh,").append("                billdate     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                billstatus     as djzt,").append(" case when billstatus=2 or billstatus=3 then 1 else -1 end as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                null     as red_status, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvedate     as shrq").append("                from er_costshare").append("               where dr = 0 ").append(null == sqlMap.get("JZ") ? "" : (String)sqlMap.get("JZ")).append("                union all").append("                select e.pk_jkbx  as pk_jkbx,").append("                t.pk_billtypeid   as pk_tradetypeid,").append("                e.bx_pk_billtype   as djlxbm,").append("                t.parentbilltype   as pk_billtype,").append("                e.bx_billno     as djbh,").append("                e.bx_djrq     as djrq,").append("                e.bzbm     as bzbm,").append("                e.total_amount     as ybje,").append("                3     as djzt,").append(" \t\t\t\t 1\t\t as spzt,").append("                e.bx_deptid as deptid_v,").append("                e.bx_jkbxr    as jkbxr,").append("                null as approver,").append("                e.pk_org     as pk_org, ").append("                e.creator     as creator, ").append("                null     as red_status, ").append("                e.saga_frozen     as saga_frozen, ").append("                e.saga_btxid     as saga_btxid, ").append("                e.saga_gtxid     as saga_gtxid, ").append("                e.saga_status     as saga_status, ").append("                null     as shrq").append("                from er_expamtinfo e").append(" left join bd_billtype t on t.PK_BILLTYPECODE=e.bx_pk_billtype and t.PK_GROUP=e.PK_GROUP").append("               where e.dr = 0 ").append(null == sqlMap.get("TX") ? "" : (String)sqlMap.get("TX")).append(" ) temp where 1=1  ");
            if (!conditionSql.isEmpty()) {
                sqlBuilder.append(conditionSql);
            }

            String sql = sqlBuilder.toString();
            if (pageInfo != null) {
                if (pageInfo.getTotal() == 0) {
                    Integer total = this.countBillInfoByCondition(queryTreeFormatVO, conditionSql, sqlParam, sqlMap);
                    pageInfo.setTotal(total);
                }

                Integer pageSize = Integer.valueOf(pageInfo.getPageSize());
                Integer totalPage = pageInfo.getTotal() % pageSize == 0 ? pageInfo.getTotal() / pageSize : pageInfo.getTotal() / pageSize + 1;
                pageInfo.setTotalPage(totalPage);
                int databaseType = DataSourceCenter.getInstance().getDatabaseType();
                LimitSQLBuilder sqlBuilderPage = SQLBuilderFactory.getInstance().createLimitSQLBuilder(databaseType);
                int pageIndex = 0;
                if (!StringUtils.isEmpty(pageInfo.getPageIndex())) {
                    pageIndex = Integer.valueOf(pageInfo.getPageIndex());
                }

                sql = sqlBuilderPage.build(sql + " order by temp.djrq desc", pageIndex + 1, Integer.valueOf(pageInfo.getPageSize()));
            } else {
                sql = sql + " order by temp.djrq desc";
            }

            IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
            List<JKBXHeaderVO> resultList = (List)iuapQryService.executeQuery(sql, sqlParam, new JKBXHeaderResultSetProcessor());
            return resultList;
        }
    }

    public int countBillInfoByCondition(QueryTreeFormatVO queryTreeFormatVO, String conditionSql, SQLParameter sqlParam, Map<String, String> sqlMap) throws Exception {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select count(1) as count from(");
        sqlBuilder.append("select *").append("        from (select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_jkzb").append("               where dr = 0 ").append(null == sqlMap.get("JK") ? "" : (String)sqlMap.get("JK")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("BX") ? "" : (String)sqlMap.get("BX")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                hkybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("HK") ? "" : (String)sqlMap.get("HK")).append("                union all").append("                select pk_jkbx  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                djlxbm   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                djbh     as djbh,").append("                djrq     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                djzt     as djzt,").append("                spzt     as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                shrq     as shrq").append("                from er_bxzb").append("               where dr = 0 ").append(null == sqlMap.get("TZ") ? "" : (String)sqlMap.get("TZ")).append("                union all").append("                select pk_accrued_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype    as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno          as djbh,").append("                billdate        as djrq,").append("                pk_currtype     as bzbm,").append("                org_amount      as ybje,").append("                billstatus      as djzt,").append("                apprstatus      as spzt,").append("                operator_dept   as deptid_v,").append("                operator        as jkbxr,").append("                approver        as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime     as shrq").append("                from er_accrued").append("               where dr = 0 ").append(null == sqlMap.get("YT") ? "" : (String)sqlMap.get("YT")).append("                union all").append("                select pk_mtapp_bill as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype  as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno        as djbh,").append("                billdate      as djrq,").append("                pk_currtype   as bzbm,").append("                orig_amount   as ybje,").append("                billstatus    as djzt,").append("                apprstatus    as spzt,").append("                apply_dept    as deptid_v,").append("                billmaker     as jkbxr,").append("                approver      as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvetime   as shrq").append("                from er_mtapp_bill").append("                where dr = 0 ").append(null == sqlMap.get("SQ") ? "" : (String)sqlMap.get("SQ")).append("                union all").append("                select pk_costshare  as pk_jkbx,").append("                pk_tradetypeid   as pk_tradetypeid,").append("                pk_tradetype   as djlxbm,").append("                pk_billtype   as pk_billtype,").append("                billno     as djbh,").append("                billdate     as djrq,").append("                bzbm     as bzbm,").append("                ybje     as ybje,").append("                billstatus     as djzt,").append(" case when billstatus=2 or billstatus=3 then 1 else -1 end as spzt,").append("                deptid as deptid_v,").append("                jkbxr    as jkbxr,").append("                approver as approver,").append("                pk_org     as pk_org, ").append("                creator     as creator, ").append("                saga_frozen     as saga_frozen, ").append("                saga_btxid     as saga_btxid, ").append("                saga_gtxid     as saga_gtxid, ").append("                saga_status     as saga_status, ").append("                approvedate     as shrq").append("                from er_costshare").append("               where dr = 0 ").append(null == sqlMap.get("JZ") ? "" : (String)sqlMap.get("JZ")).append("                union all").append("                select e.pk_jkbx  as pk_jkbx,").append("                t.pk_billtypeid   as pk_tradetypeid,").append("                e.bx_pk_billtype   as djlxbm,").append("                t.parentbilltype   as pk_billtype,").append("                e.bx_billno     as djbh,").append("                e.bx_djrq     as djrq,").append("                e.bzbm     as bzbm,").append("                e.total_amount     as ybje,").append("                3     as djzt,").append(" \t\t\t\t 1\t\t as spzt,").append("                e.bx_deptid as deptid_v,").append("                e.bx_jkbxr    as jkbxr,").append("                null as approver,").append("                e.pk_org     as pk_org, ").append("                e.creator     as creator, ").append("                e.saga_frozen     as saga_frozen, ").append("                e.saga_btxid     as saga_btxid, ").append("                e.saga_gtxid     as saga_gtxid, ").append("                e.saga_status     as saga_status, ").append("                null     as shrq").append("                from er_expamtinfo e").append(" left join bd_billtype t on t.PK_BILLTYPECODE=e.bx_pk_billtype and t.PK_GROUP=e.PK_GROUP").append("               where e.dr = 0 ").append(null == sqlMap.get("TX") ? "" : (String)sqlMap.get("TX")).append(" )temp where 1=1  ");
        if (!conditionSql.isEmpty()) {
            sqlBuilder.append(conditionSql);
        }

        sqlBuilder.append(") a ");
        String sql = sqlBuilder.toString();
        IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
        int count = (Integer)iuapQryService.executeQuery(sql, sqlParam, new ResultSetProcessor() {
            private static final long serialVersionUID = 1L;

            public Object handleResultSet(ResultSet rs) throws SQLException {
                int count;
                for(count = 0; rs.next(); count = rs.getInt("count")) {
                }

                return count;
            }
        });
        return count;
    }

    public Map<String, String> qryCurApproveman(List<String> billIds) throws BusinessException {
        StringBuilder sqlExe = new StringBuilder();
        sqlExe.append("select n.billid as billid, n.checkman as checkman, n.ischeck as ischeck");
        sqlExe.append(" from pub_wf_task t inner join pub_workflownote n on t.pk_wf_task=n.pk_wf_task");
        sqlExe.append(" where ").append(SqlUtils.getInStr("n.billid", billIds, true));
        sqlExe.append(" and t.taskstatus=0 ");
        sqlExe.append(" and n.approvestatus =0 ");
        IUAPQueryBS iuapQryService = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class);
        SQLParameter sqlParam = new SQLParameter();
        List<Object[]> approveInfos = (List)iuapQryService.executeQuery(sqlExe.toString(), sqlParam, new ArrayListProcessor());
        List<String[]> busiidUserList = new ArrayList();
        List<String> userid = new ArrayList();

        for(Object[] approveInfo : approveInfos) {
            String[] busiidUser = new String[]{(String)approveInfo[0], (String)approveInfo[1]};
            busiidUserList.add(busiidUser);
            userid.add((String)approveInfo[1]);
        }

        IUserManageQuery userQry = (IUserManageQuery)NCLocator.getInstance().lookup(IUserManageQuery.class);
        UserVO[] userVOs = userQry.queryUserByClause(SqlUtils.getInStr("cuserid", userid, true));
        Map<String, String> userid_username = new HashMap();

        for(UserVO vo : userVOs) {
            userid_username.put(vo.getCuserid(), vo.getUser_name());
        }

        for(String[] busiidUser : busiidUserList) {
            busiidUser[1] = (String)userid_username.get(busiidUser[1]);
        }

        Map<String, String> busiid_username = new HashMap();

        for(String[] busiidUser : busiidUserList) {
            if (!busiid_username.containsKey(busiidUser[0])) {
                busiid_username.put(busiidUser[0], busiidUser[1]);
            } else {
                String var10000 = (String)busiid_username.get(busiidUser[0]);
                String multiName = var10000 + ", " + busiidUser[1];
                busiid_username.put(busiidUser[0], multiName);
            }
        }

        return busiid_username;
    }

    private class JKBXHeaderResultSetProcessor implements ResultSetProcessor {
        private static final long serialVersionUID = 1L;

        private JKBXHeaderResultSetProcessor() {
        }

        public Object handleResultSet(ResultSet rs) throws SQLException {
            List<JKBXHeaderVO> resultList = new ArrayList();

            while(rs.next()) {
                JKBXHeaderVO tempHeader = new BXHeaderVO();
                tempHeader.setPk_jkbx(rs.getString("pk_jkbx"));
                tempHeader.setPk_tradetypeid(rs.getString("pk_tradetypeid"));
                tempHeader.setDjlxbm(rs.getString("djlxbm"));
                tempHeader.setPk_billtype(rs.getString("pk_billtype"));
                tempHeader.setDjbh(rs.getString("djbh"));
                tempHeader.setDjrq(StringUtils.isEmpty(rs.getString("djrq")) ? null : new UFDate(rs.getString("djrq")));
                tempHeader.setBzbm(rs.getString("bzbm"));
                tempHeader.setYbje(new UFDouble(rs.getDouble("ybje")));
                tempHeader.setDjzt(rs.getInt("djzt"));
                tempHeader.setSpzt(rs.getInt("spzt"));
                tempHeader.setDeptid_v(rs.getString("deptid_v"));
                tempHeader.setJkbxr(rs.getString("jkbxr"));
                tempHeader.setApprover(rs.getString("approver"));
                tempHeader.setPk_org(rs.getString("pk_org"));
                tempHeader.setCreator(rs.getString("creator"));
                tempHeader.setRed_status(rs.getInt("red_status"));
                tempHeader.setShrq(StringUtils.isEmpty(rs.getString("shrq")) ? null : new UFDateTime(rs.getString("shrq")));
                tempHeader.setSaga_frozen(rs.getInt("saga_frozen"));
                tempHeader.setSaga_btxid(rs.getString("saga_btxid"));
                tempHeader.setSaga_gtxid(rs.getString("saga_gtxid"));
                tempHeader.setSaga_status(rs.getInt("saga_status"));
                resultList.add(tempHeader);
            }

            return resultList;
        }
    }
}
