/*
 * SplitPageUtil.java Created on 2005-4-7 16:44:40
 *
 * Copyright (c) 2001-2004 泛微软件, 版权所有.
 */
package weaver.general;

import com.weaver.formmodel.util.SQLParseHelper;
import com.weaver.formmodel.util.StringHelper;
import org.apache.commons.lang3.StringUtils;
import org.checkerframework.checker.units.qual.A;
import weaver.conn.RecordSet;
import weaver.conn.constant.DBConstant;
import weaver.interfaces.datasource.DataSource;
import weaver.servicefiles.DataSourceXML;

import java.math.BigDecimal;
import java.util.*;

/**
 * Description: SplitPageUtil.java
 *
 * @author dongping
 * @version 1.0 2005-4-7
 */
public class SplitPageUtil extends BaseBean {
    private SplitPageParaBean spp = null;
    private int recordCount = 0;

    public SplitPageUtil(SplitPageParaBean spp) {
        this.spp = spp;
    }

    public SplitPageUtil() {
    }

    /**
     * @return 得到记录的总数
     */
    public int getRecordCount() {
        if (this.recordCount!=0) return this.recordCount;

        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            int returnInt = 0;
            rs = getRs(getRcordCount());
            if (rs.next()) {
                returnInt = Util.getIntValue(rs.getString(1), 0);
            }
            this.recordCount = returnInt;
            return returnInt;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return 0;
        }
    }

    public void setRecordCount(int recordCount) {
        this.recordCount = recordCount;
    }
    /**
     * @return 得到记录的总数
     */
    public int getRecordCount(Object...params) {
        if (this.recordCount!=0) return this.recordCount;

        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            int returnInt = 0;
            rs = getRs(getRcordCount(),params);
            if (rs.next()) {
                returnInt = Util.getIntValue(rs.getString(1), 0);
            }
            this.recordCount = returnInt;
            return returnInt;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return 0;
        }
    }
    public String  getPageSqlByDBType(int pageSize,int pageIndex,String dbtype){
        String sql = "";
        BaseBean bb = new BaseBean();
        bb.writeLog("getPageSqlByDBType-dbtype:"+dbtype);
        if (dbtype.equals("oracle")) {
            sql = getCurrentPageOralce(pageSize, pageIndex);
        }else if (dbtype.equals("db2")) {
            sql = getCurrentPageDB2(pageSize, pageIndex);
        } else if (dbtype.equals("mysql")) {
            sql = getCurrentPageMysql(pageSize, pageIndex);
        }else if (dbtype.equals("postgresql")) {
            sql = getCurrentPagePostgresql(pageSize, pageIndex);
        }else if(dbtype.equals("informix")){
            sql = getCurrentPageInformix(pageSize, pageIndex);
        }
        else {
//			sql = getCurrentPageSql(pageSize, pageIndex);
            sql = getPageSql(pageSize, pageIndex);
        }
        return sql;
    }
    /**
     * @param pageSize
     * @param pageIndex
     * @return 所需要的页面的rs
     */
    public RecordSet getCurrentPageRs(int pageIndex, int pageSize) {
        if (this.recordCount==0) {
            this.recordCount = getRecordCount();
        }

        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            String poolname = Util.null2String(spp.getPoolname());
            String dbType = rs.getDBType();
            if(!"".equals(poolname)){
                dbType = rs.getDBTypeByPoolName(poolname);
            }

            BaseBean bb = new BaseBean();
            bb.writeLog("getCurrentPageRs-dbType:"+dbType);
            if(StringUtils.isBlank(dbType)){
                try{
                    if(!"".equals(poolname)){
                        bb.writeLog("poolname:"+poolname);
                        DataSource datasource2 = (DataSource) StaticObj.getServiceByFullname("datasource."+poolname, DataSource.class);
                        String dbtype_static = datasource2.getType();
                        bb.writeLog("dbtype_static1:"+dbtype_static);
                        if(StringUtils.isNotBlank(dbtype_static)){
                            if("informix".equalsIgnoreCase(dbtype_static)){
                                dbType = dbtype_static ;
                            }
                        }
                    }
                }catch (Exception e){
                    writeLog("getCurrentPageRs:e:"+e);
                }
            }else{
                try{
                    if(!"".equals(poolname)){
                        bb.writeLog("poolname:"+poolname);
                        DataSource datasource = (DataSource) StaticObj.getServiceByFullname("datasource."+poolname, DataSource.class);
                        String dbtype_static = datasource.getType();
                        bb.writeLog("dbtype_static2:"+dbtype_static);
                        if(StringUtils.isNotBlank(dbtype_static)){
                            if("informix".equalsIgnoreCase(dbtype_static)){
                                dbType = dbtype_static ;
                            }
                        }
                    }
                }catch (Exception e){
                    writeLog("getCurrentPageRs:e:"+e);
                }
            }
            bb.writeLog("getCurrentPageRs-dbType2:"+dbType);

            if (dbType.equals("oracle")) {
                rs = getRs(getCurrentPageOralce(pageSize, pageIndex));
            }else if (dbType.equals("db2")) {
                rs = getRs(getCurrentPageDB2(pageSize, pageIndex));
            } else if(dbType.equals("mysql")){
                rs = getRs(getCurrentPageMysql(pageSize, pageIndex));
            } else if(dbType.equals("postgresql")){
                rs = getRs(getCurrentPagePostgresql(pageSize, pageIndex));
            } else if(dbType.equals("informix")){
                rs = getRs(getCurrentPageInformixsql(pageSize, pageIndex));
            }else{
                rs = getRs(getCurrentPageSql(pageSize, pageIndex));
            }
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
        }
        return rs;
    }
    /**
     * @param pageSize
     * @param pageIndex
     * @return 所需要的页面的rs
     */
    public RecordSet getCurrentPageRs(int pageIndex, int pageSize, Object... params) {
        if (this.recordCount==0) {
            this.recordCount = getRecordCount(params);
        }

        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            String poolname = Util.null2String(spp.getPoolname());
            String dbType = rs.getDBType();
            if(!"".equals(poolname)){
                dbType = rs.getDBTypeByPoolName(poolname);
            }
            if (dbType.equals("oracle")) {
                rs = getRs(getCurrentPageOralce(pageSize, pageIndex),params);
            }else if (dbType.equals("db2")) {
                rs = getRs(getCurrentPageDB2(pageSize, pageIndex),params);
            } else if(dbType.equals("mysql")){
                rs = getRs(getCurrentPageMysql(pageSize, pageIndex),params);
            }
            else if(dbType.equals("postgresql")){
                rs = getRs(getCurrentPagePostgresql(pageSize, pageIndex),params);
            }
            else {
                rs = getRs(getCurrentPageSql(pageSize, pageIndex),params);
            }
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
        }
        return rs;
    }
    public RecordSet getCurrentPageRsNew(int pageIndex, int pageSize) {

        if (this.recordCount==0) {
            this.recordCount = getRecordCount();
        }

        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            if (rs.getDBType().equals("oracle")) {
                rs = getRs(getCurrentPageOralce(pageSize, pageIndex));
            }else if (rs.getDBType().equals("db2")) {
                rs = getRs(getCurrentPageDB2(pageSize, pageIndex));
            } else if (rs.getDBType().equals("mysql")) {
                rs = getRs(getCurrentPageMysql(pageSize, pageIndex));
            }
            else if (rs.getDBType().equals("postgresql")) {
                rs = getRs(getCurrentPagePostgresql(pageSize, pageIndex));
            }
            else {
                String sql = getPageSql(pageSize, pageIndex);
                rs = getRs(sql);
            }
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
        }
        return rs;

    }

    public RecordSet getAllRs(){
        return getAllRs(false);
    }

    /**
     * @param isSum
     * @return 所需要的页面的rs
     */
    public RecordSet getAllRs(boolean isSum) {
        RecordSet rs = new RecordSet();
        try {
            checkExcption();
            rs = getRs(getAllPage(isSum));
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
        }
        return rs;
    }

    /**
     * @return
     */
    public String getAllPage(boolean isSum) {
        try {
            checkExcption();
            String returnStr = "";

            //形成语句头 (构成 父查询的top)
            //returnStr = "select " + spp.getBackFields();
            returnStr = "select "+spp.getSqlhint() + spp.getBackFields();
            //添加distinct
            if (spp.getIsDistinct()) {
                //returnStr = "select distinct " + spp.getBackFields();
                returnStr = "select distinct "+spp.getSqlhint() + spp.getBackFields();
            }

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 5 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + spp.getSqlWhere();
                } else {
                    returnStr += " where " + spp.getSqlWhere();
                }
            }

            String sqlGroup = spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroup)) {
                if (sqlGroup.length() > 5 && sqlGroup.substring(0, 5).equalsIgnoreCase("group"))
                {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }
            if(!isSum){
                //添回 内层 order by 语句
                String sqlOrderBy = spp.getSqlOrderBy().trim();
                if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                    throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
                } else {
                    if(spp.getOrderByMap().size()==0){
                        if (spp.getSortWay() == spp.ASC) {
                            returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                        } else {
                            returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                        }
                    }else {
                        returnStr+=" order by "+getOrderByStr(spp.getOrderByMap(),0);
                    }
                }
            }else{
                String countColumns = spp.getCountColumns();
                if("".equals(countColumns)){
                    writeLog("未设置需要统计的列，如：sumColumns=\"doccount,notreplydoccount,replydoccount\"");
                    return "";
                }else{
                    String[] countColumnArr = countColumns.split(",");

                    String poolname = spp.getPoolname();
                    String dbtype = "";
                    if(!StringHelper.isEmpty(poolname)&&!DataSourceXML.SYS_LOCAL_POOLNAME.equals(poolname)){
                        DataSource datasource = (DataSource) StaticObj.getServiceByFullname("datasource."+poolname, DataSource.class);
                        dbtype = datasource.getType();
                    }else{
                        RecordSet recordSet = new RecordSet();
                        dbtype = recordSet.getDBType();
                    }

                    Map<String, Integer> countColumnsDbType = spp.getCountColumnsDbType();
                    String backfields = "";
                    for(int i=0;i<countColumnArr.length;i++){
                        if (countColumnsDbType!=null && countColumnsDbType.size()>0) {
                            if (countColumnsDbType.get(countColumnArr[i].toLowerCase())==1) {
                                if(dbtype.equals("oracle"))
                                    backfields += "sum(to_number(replace(replace("+countColumnArr[i].toLowerCase()+",',',''),' ',''))) as "+countColumnArr[i];
                                else if (DBConstant.DB_TYPE_SQLSERVER.equals(dbtype))
                                    backfields += "sum(convert(float,replace("+countColumnArr[i].toLowerCase()+",',',''))) as "+countColumnArr[i];
                                else
                                    //backfields += "sum(convert(replace("+countColumnArr[i].toLowerCase()+",',','')), DECIMAL(30,10)) as "+countColumnArr[i];
                                    backfields += "sum(CAST(replace("+countColumnArr[i].toLowerCase()+",',','') AS DECIMAL(30,10))) as "+countColumnArr[i];//qc451484,mysql字段类型转换
                            }else {
                                if(dbtype.equals("sqlserver")){
                                    backfields += "sum(convert(float,"+countColumnArr[i].toLowerCase()+")) as "+countColumnArr[i];//qc362250
                                }else{
                                    backfields += "sum("+countColumnArr[i].toLowerCase()+") as "+countColumnArr[i];
                                }
                            }
                        }else {
                            backfields += "sum("+countColumnArr[i].toLowerCase()+") as "+countColumnArr[i];
                        }
                        if(i!=countColumnArr.length-1){
                            backfields+=",";
                        }
                    }
                    returnStr = "select "+backfields+" from ("+returnStr+") r";
                }
            }

            BaseBean bb = new BaseBean();
            bb.writeLog("getAllPage--returnStr:"+returnStr);

            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }

    public String getCountSql(){
        return getRcordCount();
    }

    /**
     * @return 当数据库是sql 并且排序时的字段并不只是主键时时候的求记录总数sql语句
     */
    private String getRcordCount() {
        try {
            checkExcption();
            String returnStr = "";
            boolean isCountGroup = !"".equals(spp.getSqlGroupBy());

            String strDiscinct="";
            //	添回 distinct 关键字
            if (spp.getIsDistinct()) {
                strDiscinct =" distinct ";
            }

            //形成语句头 (构成 父查询的top)
            //if (isCountGroup) {
            //    returnStr = "select count(1) from (select " + spp.getBackFields();
            //} else {
            //    returnStr = "select count("+strDiscinct + spp.getPrimaryKey() + ")";
            //}
            if (isCountGroup) {
                returnStr = "select count("+strDiscinct+"*) from (select "+spp.getSqlhint() + spp.getBackFields();
            } else {
                returnStr = "select count("+strDiscinct+spp.getSqlhint() +("".equals(spp.getCountField())? spp.getPrimaryKey():spp.getCountField())+ ")";
            }

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 5 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }
            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + spp.getSqlWhere();
                } else {
                    returnStr += " where " + spp.getSqlWhere();
                }
            }

            if (isCountGroup) {
                String sqlGroup = spp.getSqlGroupBy().trim();
                if (sqlGroup.length() > 5 && sqlGroup.substring(0, 5).equalsIgnoreCase("group"))
                {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
                returnStr += " ) tblTmp";
            }

            return returnStr;

        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }

    /**
     * @param pageSize
     *            页面大小
     * @param pageIndex
     *            当前的页码
     * @return 数据库为Sql并且不光按主键排序的指定页的sql语句
     */
    private String getCurrentPageSql(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";
            int topInnerRows = pageSize * pageIndex;
            int topOutterRows = pageSize;
            //形成语句头 (构成 父查询的top)
            if (topInnerRows > this.recordCount) {
                topInnerRows = this.recordCount;
                topOutterRows = this.recordCount - (pageSize * (pageIndex - 1));
            }

            if (pageIndex == 1) {
                returnStr = "select  ";
            } else {
                returnStr = "select top " + topOutterRows + " * from ( " + "   select top  " + topOutterRows
                        + " * from ( " + "       select  ";
            }
            if(!spp.getOutfields().equals(""))
            {
                returnStr = "select r.*,"+spp.getOutfields()+" from (  "+returnStr;
            }

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的top
            if(pageSize>this.recordCount){
                returnStr += " top " + pageSize + " " + spp.getBackFields();
            }else{
                returnStr += " top " + topInnerRows + " " + spp.getBackFields();
            }

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where")) {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if(spp.getOrderByMap().size()==0){
                    if (spp.getSortWay() == spp.ASC) {
                        returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    } else {
                        returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    }
                }else{
                    returnStr+=" order by "+getOrderByStr(spp.getOrderByMap(),0);
                }
            }
            if (pageIndex != 1) {
                //添回外层 order by 语句
                if(spp.getOrderByMap().size()==0){
                    if (spp.getSortWay() == spp.ASC) {
                        returnStr += " ) tbltemp1  order by "
                                + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                    } else {
                        // 处理置顶逻辑
                        String topOrderby_ = "";
                        String ordersql_ = spp.getSqlOrderBy();
                        if(ordersql_.indexOf("mode_top_")!=-1 &&ordersql_.indexOf("desc,")!=-1){//置顶逻辑处理
                            topOrderby_ = " "+ ordersql_.substring(0,ordersql_.indexOf("desc,")) +" asc," ;
                            topOrderby_ = topOrderby_.replace("t1.","");
                            ordersql_=  ordersql_.substring(ordersql_.indexOf("desc,")+5);

                        }
                        String realOrderBy_= topOrderby_ +" " + getOrderByAsc(ordersql_, spp.getPrimaryKey(), false);
                        if(realOrderBy_.endsWith(",") && ordersql_.indexOf("mode_top_")!=-1){//置顶逻辑处理
                            realOrderBy_ =realOrderBy_.substring(0,realOrderBy_.length()-1);
                        }


                        returnStr += " ) tbltemp1  order by "
                                + realOrderBy_;
                    }
                }else
                    returnStr+=" ) tbltemp1  order by "+getOrderByStr(spp.getOrderByMap(),1);
                //添回最外层 order by 语句
                if(spp.getOrderByMap().size()==0){
                    String ordersql = spp.getSqlOrderBy();
                    ordersql = ordersql.replace(" asc", "SQLASC");
                    ordersql = ordersql.replace(" desc", "SQLDESC");
                    ordersql = ordersql.replace("SQLASC", " desc");
                    ordersql = ordersql.replace("SQLDESC", " asc");
                    if (spp.getSortWay() == spp.ASC) {
                        returnStr += " ) tbltemp2  order by "
                                + getOrderByAsc(ordersql, spp.getPrimaryKey(), false);
                    } else {
                        String topOrderby = " ";

                        if(ordersql.indexOf("mode_top_")!=-1 &&ordersql.indexOf("asc,")!=-1){//置顶逻辑处理
                            topOrderby = " "+ ordersql.substring(0,ordersql.indexOf("asc,")) +" asc," ;
                            topOrderby = topOrderby.replace("asc,","desc,");
                            topOrderby = topOrderby.replace("t1.","");
                            ordersql=  ordersql.substring(ordersql.indexOf("asc,")+4);

                        }
                        String realOrderBy= topOrderby +" " + getOrderByDesc(ordersql, spp.getPrimaryKey(), false);
                        if(realOrderBy.endsWith(",") && ordersql.indexOf("mode_top_")!=-1){//置顶逻辑处理
                            realOrderBy =realOrderBy.substring(0,realOrderBy.length()-1);
                        }
                        returnStr += " ) tbltemp2  order by "+ realOrderBy;
                    }
                }else
                    returnStr+=" ) tbltemp2  order by "+getOrderByStr(spp.getOrderByMap(),0);
            }
            if(!spp.getOutfields().equals(""))
            {
                returnStr += ") r ";
            }
            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere();
            }
            if(!spp.getOutfields().equals(""))
            {
                if (spp.getSortWay() == spp.ASC)
                {
                    returnStr += " order by " + getOuterOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                }
                else
                {
                    returnStr += " order by " + getOuterOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                }
            }
            return returnStr;

        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }

    /**
     * @param pageSize
     *            页面大小
     * @param pageIndex
     *            当前的页码
     * @return 数据库为Sql并且不光按主键排序的指定页的sql语句
     */
    private String getPageSql(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";
            int topInnerRows = pageSize * pageIndex;
            int topOutterRows = (pageIndex-1)*pageSize;
            long t = 1409477546046l;//new Date().getTime();
            String outtemp = "outtemp"+t;
            String tempcolumn = "tempcolumn"+t;
            String temprownumber = "temprownumber"+t;
            String innertemp = "innertemp"+t;

            returnStr = "select "+outtemp+".* from (select row_number()over(order by "+tempcolumn+") "+temprownumber+",* from ( select ";
            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += " top  " + topInnerRows + " "+tempcolumn+"=0,";

    		/*if(!spp.getOutfields().equals(""))
    		{
    			returnStr = "select r.*,"+spp.getOutfields()+" from (  "+returnStr;
    		}*/

//    		returnStr += spp.getSqlhint();

            //添加查询字段
            returnStr +=  spp.getBackFields();

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回  order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if(spp.getOrderByMap().size()==0){
                    if (spp.getSortWay() == spp.ASC) {
                        returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    } else {
                        returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    }
                }else{
                    returnStr+=" order by "+getOrderByStr(spp.getOrderByMap(),0);
                }
            }
            //添加结束
            returnStr += ") "+innertemp+" ) "+outtemp+"  where "+temprownumber+">"+topOutterRows;
            boolean needOrderby = true;

            //外部相关添加
            if(!spp.getOutfields().equals(""))
            {
                returnStr = "select r.*,"+spp.getOutfields()+" from (  "+returnStr+") r";
                needOrderby = false;
            }
            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere();
                needOrderby = false;
            }
            if(!spp.getOutfields().equals(""))
            {
                if (spp.getSortWay() == spp.ASC)
                {
                    returnStr += " order by " + getOuterOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                }
                else
                {
                    returnStr += " order by " + getOuterOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                }
                needOrderby = false;
            }
            if(needOrderby){
                returnStr += " order by "+temprownumber;
            }
            return returnStr;

        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }
    /**
     * @param pageSize
     *            页面大小
     * @param pageIndex
     *            当前的页码
     * @return 数据库为oralce指定页的sql语句
     */
    private String getCurrentPageOralce(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";

            //形成语句头 (构成 父查询的top)
            int rows = pageSize * pageIndex;

            String oracleRow = "oracle_rownum";

            //returnStr = "select * from (  " + "   select my_table.*, rownum as my_rownum from (" + "    select tableA.*,rownum  as r from (   select ";
            if(!spp.getOutfields().equals(""))
            {
                returnStr = "select r.*,"+spp.getOutfields()+" from (  " + "   select my_table.*, rownum as my_rownum from (" + "    select tableA.*,rownum  as "+oracleRow+" from (   select ";
            }
            else
                returnStr = "select r.* from (  " + "   select my_table.*, rownum as my_rownum from (" + "    select tableA.*,rownum  as "+oracleRow+" from (   select ";

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的返回语句
            returnStr += spp.getBackFields() ;

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                }
            }

            //returnStr += ") tableA  ) my_table where rownum < " + (pageIndex * pageSize+1) + " ) where my_rownum > "
            //        + (pageIndex - 1) * pageSize;
            //}
            returnStr += ") tableA  ) my_table where "+oracleRow+" < " + (pageIndex * pageSize+1) + " and "+oracleRow+">"+ (pageIndex - 1) * pageSize+") r ";
            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere()+" order by my_rownum";
            }
            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }

    /**
     * @param sqlOrderBy
     *            order by 的条件 是用逗号分隔的 没有asc,desc等升序,降序的符号 如:"t1.id,t2.name"
     * @param primayKey
     *            此次查询中的主键是多少
     * @param isIN
     *            是否是子查询语句中的order by语句
     * @return 一个升序的order by 子句
     */
    private String getOuterOrderByAsc(String sqlOrderBy, String primayKey, boolean isIN)
    {
        String returnStr = "";
        ArrayList tempList = Util.TokenizerString(sqlOrderBy, ",");
        for (int i = 0; i < tempList.size(); i++)
        {
            tempList.set(i, ((String) tempList.get(i)).trim());
            String strTemp = (String) tempList.get(i);
            int pos = strTemp.indexOf(".");
            if (pos != -1)
            {
                strTemp = ((String) tempList.get(i)).substring(pos + 1);
            }
            returnStr += "r."+strTemp + " asc,";
        }

        if (tempList.contains(primayKey))
        {
            returnStr = returnStr.substring(0, returnStr.length() - 1);
        }
        else
        {
            // 如果没有分组语句则需要加上主键的排序
            int pos2 = primayKey.indexOf(".");
            if (pos2 == -1)
            {
                returnStr += "r."+primayKey + " asc";
            }
            else
            {
                returnStr += "r."+primayKey.substring(pos2 + 1) + " asc";
            }
        }
        return returnStr;
    }
    /**
     * @param sqlOrderBy
     *            order by 的条件 是用逗号分隔的 没有asc,desc等升序,降序的符号 如:"id,name"
     * @param primayKey
     *            此次查询中的主键是多少
     * @param isIN
     *            是否是子查询语句中的order by语句
     * @return 一个降序的order by 子句
     */
    private String getOuterOrderByDesc(String sqlOrderBy, String primayKey, boolean isIN)
    {
        String returnStr = "";
        ArrayList tempList = Util.TokenizerString(sqlOrderBy, ",");
        for (int i = 0; i < tempList.size(); i++)
        {
            tempList.set(i, ((String) tempList.get(i)).trim());
            String strTemp = (String) tempList.get(i);
            int pos = strTemp.indexOf(".");
            if (pos != -1)
            {
                strTemp = ((String) tempList.get(i)).substring(pos + 1);
            }
            returnStr += "r."+strTemp + " desc,";
        }

        if (tempList.contains(primayKey))
        {
            returnStr = returnStr.substring(0, returnStr.length() - 1);
        }
        else
        {
            //如果没有分组语句则需要加上主键的排序
            int pos2 = primayKey.indexOf(".");
            if (pos2 == -1)
            {
                returnStr += "r."+primayKey + " desc";
            }
            else
            {
                returnStr += "r."+primayKey.substring(pos2 + 1) + " desc";
            }
        }
        return returnStr;
    }
    private String getCurrentPageDB2(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";
            int topInnerRows = pageSize * pageIndex;
            int topOutterRows = pageSize;
            //形成语句头 (构成 父查询的top)

            if (topInnerRows > this.recordCount) {
                topInnerRows = this.recordCount;
                topOutterRows = this.recordCount - (pageSize * (pageIndex - 1));
            }

            if (pageIndex == 1) {
                returnStr = "select  ";
            } else {
                returnStr = "select * from ( " + "   select  * from ( " + "       select  ";
            }

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }

            //构成子查询的top
            returnStr +=  spp.getBackFields();

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where")) {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句 " top " + topInnerRows + " " +
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    returnStr += " fetch first "+topInnerRows+"  rows only ";
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                    returnStr += " fetch first "+topInnerRows+"  rows only ";
                }
            }


            if (pageIndex != 1) {
                //添回外层 order by 语句 top  " + topOutterRows   + "
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " ) tbltemp1  order by "
                            + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                    returnStr += " fetch first "+topOutterRows+"  rows only ";

                } else {
                    returnStr += " ) tbltemp1  order by "
                            + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                    returnStr += " fetch first "+topOutterRows+"  rows only ";
                }

                //添回最外层 order by 语句 top " + topOutterRows + "
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " ) tbltemp2  order by "
                            + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                    returnStr += " fetch first "+topOutterRows+"  rows only ";
                } else {
                    returnStr += " ) tbltemp2  order by "
                            + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), false);
                    returnStr += " fetch first "+topOutterRows+"  rows only ";
                }
            }





            return returnStr;

        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }


    /**
     * @param sqlOrderBy
     *            order by 的条件 是用逗号分隔的 没有asc,desc等升序,降序的符号 如:"t1.id,t2.name"
     * @param primayKey
     *            此次查询中的主键是多少
     * @param isIN
     *            是否是子查询语句中的order by语句
     * @return 一个升序的order by 子句
     */
    private String getOrderByAsc(String sqlOrderBy, String primayKey, boolean isIN) {
        String returnStr = "";
        String  topOrderBy = "";
        if(sqlOrderBy.indexOf("mode_top_")!=-1 &&sqlOrderBy.indexOf("desc,")!=-1){
            topOrderBy = sqlOrderBy.substring(0,sqlOrderBy.indexOf("desc,")) +" desc," ;
            topOrderBy = topOrderBy.replace("t1.", "");
            sqlOrderBy=  sqlOrderBy.substring(sqlOrderBy.indexOf("desc,")+5);

        }

        //处理置排序字段
        if(topOrderBy.indexOf("-modetop0")!=-1){
            topOrderBy =  topOrderBy.replaceAll("-modetop0",",0");
        }

        if(sqlOrderBy.indexOf("mode_top_")!=-1 &&sqlOrderBy.indexOf("desc,")==-1){
            returnStr = sqlOrderBy;
        }
        ArrayList tempList = Util.TokenizerString(sqlOrderBy, ",");
        ArrayList orderList = new ArrayList();
        if (isIN) {
            for (int i = 0; i < tempList.size(); i++) {
                tempList.set(i, ((String) tempList.get(i)).trim());
                String strTemp = ((String) tempList.get(i)).toLowerCase().trim();
                if(!(strTemp.endsWith(" desc")||strTemp.endsWith(" asc"))){
                    if(strTemp.toLowerCase().indexOf("mode_top_")==-1){
                        returnStr += strTemp + " asc,";
                    }
                }else{
                    returnStr += strTemp + " ,";
                }
                orderList.add(strTemp.toLowerCase().replace(" desc", "").replace(" asc", ""));
            }
            if (tempList.contains(primayKey)||orderList.contains(primayKey.toLowerCase())) {
                returnStr = returnStr.substring(0, returnStr.length() - 1);
            } else {
                //如果没有分组语句则需要加上主键的排序
                String sqlGroup = spp.getSqlGroupBy().trim();
                if ("".equalsIgnoreCase(sqlGroup))
                    returnStr += primayKey + " asc";
                else
                    returnStr = returnStr.substring(0, returnStr.length() - 1);
            }
        } else {
            for (int i = 0; i < tempList.size(); i++) {
                tempList.set(i, ((String) tempList.get(i)).trim());
                String strTemp = (String) tempList.get(i);
                int pos = strTemp.indexOf(".");
                if (pos != -1) {
                    strTemp = ((String) tempList.get(i)).substring(pos + 1);
                }
                strTemp = strTemp.toLowerCase().trim();
                orderList.add(strTemp.toLowerCase().replace(" desc", "").replace(" asc", ""));
                if(!(strTemp.endsWith(" desc")||strTemp.endsWith(" asc"))){
                    returnStr += strTemp + " asc,";
                }else{
                    if(strTemp.endsWith(" desc")){
                        strTemp = strTemp.replace(" desc", " asc");
                    }else{
                        strTemp = strTemp.replace(" asc", " desc");
                    }
                    returnStr +=strTemp + " ,";
                }
            }

            String primayKeyStr = primayKey;
            int pos = primayKey.indexOf(".");
            if (pos != -1) {
                primayKeyStr = primayKey.substring(pos+1);
            }
            if (tempList.contains(primayKey)||orderList.contains(primayKeyStr.toLowerCase())) {
                returnStr = returnStr.substring(0, returnStr.length() - 1);
            } else {
                // 如果没有分组语句则需要加上主键的排序
                String sqlGroup = spp.getSqlGroupBy().trim();
                if ("".equalsIgnoreCase(sqlGroup)) {
                    int pos2 = primayKey.indexOf(".");
                    if (pos2 == -1) {
                        returnStr += primayKey + " asc";
                    } else {
                        returnStr += primayKey.substring(pos2 + 1) + " asc";
                    }
                } else {
                    returnStr = returnStr.substring(0, returnStr.length() - 1);
                }
            }
        }
        if(returnStr.trim().endsWith(",")){
            returnStr = returnStr.substring(0,returnStr.lastIndexOf(","));
        }
        //处理置排序字段
        if(returnStr.indexOf("-modetop0")!=-1){
            returnStr =  returnStr.replaceAll("-modetop0",",0");
        }
        if(!"".equals(topOrderBy)){
            returnStr = topOrderBy+" "+returnStr;
        }

        return returnStr;
    }

    /**
     * @param sqlOrderBy
     *            order by 的条件 是用逗号分隔的 没有asc,desc等升序,降序的符号 如:"id,name"
     * @param primayKey
     *            此次查询中的主键是多少
     * @param isIN
     *            是否是子查询语句中的order by语句
     * @return 一个降序的order by 子句
     */
    private String getOrderByDesc(String sqlOrderBy, String primayKey, boolean isIN) {
        String returnStr = "";
        String  topOrderBy = "";
        if(sqlOrderBy.indexOf("mode_top_")!=-1 &&sqlOrderBy.indexOf("desc,")!=-1){
            topOrderBy = sqlOrderBy.substring(0,sqlOrderBy.indexOf("desc,")) +" desc," ;
            sqlOrderBy=  sqlOrderBy.substring(sqlOrderBy.indexOf("desc,")+5);

        }

        //处理置排序字段
        if(topOrderBy.indexOf("-modetop0")!=-1){
            topOrderBy =  topOrderBy.replaceAll("-modetop0",",0");
        }

        if(sqlOrderBy.indexOf("mode_top_")!=-1 &&sqlOrderBy.indexOf("desc,")==-1){
            returnStr = sqlOrderBy;
        }

        ArrayList tempList = Util.TokenizerString(sqlOrderBy, ",");
        ArrayList orderList = new ArrayList();
        if (isIN) {
            for (int i = 0; i < tempList.size(); i++) {
                tempList.set(i, ((String) tempList.get(i)).trim());
                String strTemp = ((String) tempList.get(i)).toLowerCase().trim();
                if(!(strTemp.endsWith(" desc")||strTemp.endsWith(" asc"))){
                    if(strTemp.toLowerCase().indexOf("mode_top_")==-1){
                        returnStr += strTemp + " desc,";
                    }
                }else{
                    returnStr += strTemp + " ,";
                }
                orderList.add(strTemp.toLowerCase().replace(" desc", "").replace(" asc", ""));
            }
            if (tempList.contains(primayKey)||orderList.contains(primayKey.toLowerCase())) {
                returnStr = returnStr.substring(0, returnStr.length() - 1);
            } else {
                //如果没有分组语句则需要加上主键的排序
                String sqlGroup = spp.getSqlGroupBy().trim();
                if ("".equalsIgnoreCase(sqlGroup)){
                    if(returnStr.indexOf(primayKey)==-1){
                        returnStr += primayKey + " desc";
                    }
                }else
                    returnStr = returnStr.substring(0, returnStr.length() - 1);
            }
        } else {
            for (int i = 0; i < tempList.size(); i++) {
                tempList.set(i, ((String) tempList.get(i)).trim());
                String strTemp = (String) tempList.get(i);
                int pos = strTemp.indexOf(".");
                if (pos != -1) {
                    strTemp = ((String) tempList.get(i)).substring(pos + 1);
                }
                strTemp = strTemp.toLowerCase().trim();
                orderList.add(strTemp.toLowerCase().replace(" desc", "").replace(" asc", ""));
                if(!(strTemp.endsWith(" desc")||strTemp.endsWith(" asc"))){
                    returnStr += strTemp + " desc,";
                }else{
                    if(strTemp.endsWith(" desc")){
                        strTemp = strTemp.replace(" desc", " asc");
                    }else{
                        strTemp = strTemp.replace(" asc", " desc");
                    }
                    returnStr += strTemp + " ,";
                }
            }

            String primayKeyStr = primayKey;
            int pos = primayKey.indexOf(".");
            if (pos != -1) {
                primayKeyStr = primayKey.substring(pos+1);
            }
            if (tempList.contains(primayKey)||orderList.contains(primayKeyStr.toLowerCase())){
                returnStr = returnStr.substring(0, returnStr.length() - 1);
            } else {
                //如果没有分组语句则需要加上主键的排序
                String sqlGroup = spp.getSqlGroupBy().trim();
                if ("".equalsIgnoreCase(sqlGroup)){
                    int pos2 = primayKey.indexOf(".");
                    if (pos2 == -1) {
                        returnStr += primayKey + " desc";
                    } else {
                        returnStr += primayKey.substring(pos2 + 1) + " desc";
                    }
                } else {
                    returnStr = returnStr.substring(0, returnStr.length() - 1);
                }
            }
        }
        if(returnStr.trim().endsWith(",")){
            returnStr = returnStr.substring(0,returnStr.lastIndexOf(","));
        }
        //处理置排序字段
        if(returnStr.indexOf("-modetop0")!=-1){
            returnStr =  returnStr.replaceAll("-modetop0",",0");
        }
        if(!"".equals(topOrderBy)){
            returnStr = topOrderBy+" "+returnStr;
        }
        return returnStr;
    }

    /**
     * 根据Sql得到生成的rs对象
     */
    private RecordSet getRs(String sql) {
        RecordSet rs = new RecordSet();
        ////System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n");
        if (spp.getIsPrintExecuteSql()) {
            //System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n");
            writeLog("分页部分执行如下SQL语句: \n" + sql + " \n");
        }
        if(spp.isReturnDecryptData()){//脱敏数据需要明文返回
            rs.isReturnDecryptData(true);
        }
        boolean status=false;
        if("".equals(spp.getPoolname())){
            status=rs.executeSql(sql);
        }else {
            status=rs.executeSql(sql,spp.getPoolname());
        }
        if (!status) {
            //System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n失败,请检查上面的SQL语句.");
            writeLog("分页部分执行如下SQL语句: \n" + sql + " \n失败,请检查上面的SQL语句.");
        }
        return rs;
    }
    /**
     * 根据Sql得到生成的rs对象
     */
    private RecordSet getRs(String sql, Object...params) {
        RecordSet rs = new RecordSet();
        ////System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n");
        if (spp.getIsPrintExecuteSql()) {
            //System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n");
            writeLog("分页部分执行如下SQL语句: \n" + sql + " \n");
        }

        boolean status=false;
        if("".equals(spp.getPoolname())){
            status=rs.executeSql(sql, true, params);
        }else {
            status=rs.executeSql(sql, true, spp.getPoolname(), params);
        }
        if (!status) {
            //System.out.println("分页部分执行如下SQL语句: \n" + sql + " \n失败,请检查上面的SQL语句.");
            writeLog("分页部分执行如下SQL语句: \n" + sql + " \n失败,请检查上面的SQL语句.");
        }
        return rs;
    }
    private void checkExcption() throws Exception {
        if (this.spp == null) {
            throw new Exception("页面参数没有设置,请设置PageParaBean的参数");
        }
        if (spp.getPrimaryKey() == null || spp.getPrimaryKey().equals("")) {
            throw new Exception("没有设置主键,不能查询!");
        }
        if (spp.getBackFields() == null || spp.getBackFields().equals("")) {
            throw new Exception("没有设置返回字段,不能查询!");
        }
        if (spp.getSqlFrom() == null || spp.getSqlFrom().equals("")) {
            throw new Exception("没有设置from语句,不能查询!");
        }
    }

    /**
     * @return Returns the spp.
     */
    public SplitPageParaBean getSpp() {
        return spp;
    }

    /**
     * @param spp
     *            The spp to set.
     */
    public void setSpp(SplitPageParaBean spp) {
        this.spp = spp;
    }

    /**
     * 获得map中排序条件
     * @param orderByMap
     * @param flag
     * @return
     */
    private String getOrderByStr(Map orderByMap,int flag ) {
        String returnStr = "";
        Iterator iterator=orderByMap.keySet().iterator();
        while(iterator.hasNext()){
            String orderField=(String)iterator.next();
            int orderWay=(Integer)orderByMap.get(orderField);
            if(flag==0)
                returnStr=returnStr+","+orderField+" "+(orderWay==0?"asc":"desc");
            else
                returnStr=returnStr+","+orderField+" "+(orderWay==0?"desc":"asc");
        }
        if(returnStr.length()>0) returnStr=returnStr.substring(1);
        return returnStr;
    }

    public Map<String, String> setCountCV(RecordSet countRs) {
        return setCountCV(countRs,false);
    }

    public Map<String, String> setCountCV(RecordSet countRs,boolean isPage) {
        String countColumns = spp.getCountColumns();
        Map<String,String> countCV = new HashMap<String,String>();
        if(countColumns.equals(""))return null;
        String[] countColumnArr = countColumns.split(",");
        if(!isPage){
            if(countRs.next()){
                for(int i=0;i<countColumnArr.length;i++){
                    String key = "sum_"+countColumnArr[i].toLowerCase();
                    double sum = Util.getDoubleValue(countCV.get(key),0.0);
                    sum=Util.getDoubleValue(countRs.getString(countColumnArr[i].toLowerCase()),0);
                    countCV.put(key, ""+sum);
                }
            }
        }else{
            while(countRs.next()){
                for(int i=0;i<countColumnArr.length;i++){
                    String key = "sum_"+countColumnArr[i].toLowerCase();
                    if(isPage)key = key + "_page";
                    double sum =  Util.getDoubleValue(countCV.get(key),0.0);
                    if (countRs.getString(countColumnArr[i].toLowerCase()).indexOf(",")>-1) {
                        sum+=Util.getDoubleValue(countRs.getString(countColumnArr[i].toLowerCase()).replaceAll(",", ""),0);
                    }else {
                        sum+=Util.getDoubleValue(countRs.getString(countColumnArr[i].toLowerCase()),0);
                    }
                    countCV.put(key, ""+sum);
                }
            }
        }
        return countCV;
    }

    public Map<String, String> setCountCVMap(List<Map<String, String>> dataAll,boolean isPage) {
        String countColumns = spp.getCountColumns();
        Map<String,String> countCV = new HashMap<String,String>();
        if(countColumns.equals(""))return null;
        String[] countColumnArr = countColumns.split(",");
        for(ListIterator<Map<String,String>> it = dataAll.listIterator();it.hasNext();){
            Map<String,String> data = it.next();
            for(int i=0;i<countColumnArr.length;i++){
                String key = "sum_"+countColumnArr[i].toLowerCase();
                if(isPage)key = key + "_page";
                double sum = Util.getDoubleValue(countCV.get(key),0);
                Object obj = data.get(countColumnArr[i].toLowerCase());
                String numberColumnValue = "";
                if(obj instanceof BigDecimal){
                    numberColumnValue = ((BigDecimal)obj).doubleValue()+"";
                }else if(obj instanceof Integer){
                    numberColumnValue = ((Integer)obj).intValue()+"";
                }else{
                    numberColumnValue = Util.null2String(obj);
                }
                if(numberColumnValue.indexOf(",")>-1){
                    sum+=Util.getDoubleValue(numberColumnValue.replaceAll(",", ""),0);
                }else{
                    sum+=Util.getDoubleValue(numberColumnValue,0);
                }
                countCV.put(key, ""+sum);
            }
        }
        return countCV;
    }


    private String getCurrentPagePostgresql(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";

            //形成语句头 (构成 父查询的top)
            if(!spp.getOutfields().equals(""))
            {
                returnStr = "select r.*,"+spp.getOutfields()+" from (  " + "   select my_table.* from (   select ";
            }
            else
                returnStr = "select r.* from (  " + "   select my_table.*  from (   select ";

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的返回语句
            returnStr += spp.getBackFields() ;

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                }
            }


            returnStr += " ) my_table  limit "+pageSize +" offset " +   (pageIndex - 1) * pageSize + " ) r ";
            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere();
            }
            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }


    /**
     * mysql分页查询
     * @param pageSize
     * @param pageIndex
     * @return
     */
    private String getCurrentPageMysql(int pageSize, int pageIndex) {
        try {
            checkExcption();
            String returnStr = "";

            //形成语句头 (构成 父查询的top)
            if(!spp.getOutfields().equals(""))
            {
                returnStr = "select r.*,"+spp.getOutfields()+" from (  " + "   select my_table.* from (   select ";
            }
            else
                returnStr = "select r.* from (  " + "   select my_table.*  from (   select ";

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的返回语句
            returnStr += spp.getBackFields() ;

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
				if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                }
            }


            returnStr += " ) my_table  limit "+ (pageIndex - 1) * pageSize+"," +  pageSize + " ) r ";
            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere();
            }
            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }


    private String getCurrentPageInformix(int pageSize, int pageIndex) {
        BaseBean bb = new BaseBean();
        try {
            checkExcption();
            String returnStr = "";

            returnStr = " select r.* from ( select skip "+(pageIndex - 1) * pageSize+" first "+  pageSize+ " tableA.* from (   select ";

            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的返回语句
            returnStr += spp.getBackFields() ;

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
                if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                }
            }

            returnStr += ") as tableA ) as r ";

//            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
//            {
//                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere()+" ";
//            }

            bb.writeLog("returnStr333:"+returnStr);
            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            //System.out.println(ex.getMessage());
            return "";
        }
    }


    private String getCurrentPageInformixsql(int pageSize, int pageIndex) {
        BaseBean bb = new BaseBean();
        try {
            checkExcption();
            String returnStr = "";

            if(!spp.getOutfields().equals(""))
            {
                returnStr = " select r.*,"+spp.getOutfields()+"  from ( select skip "+(pageIndex - 1) * pageSize+" first "+  pageSize+ " tableA.* from (   select ";
            }
            else
                returnStr = " select r.* from ( select skip "+(pageIndex - 1) * pageSize+" first "+  pageSize+ " tableA.* from (   select ";


            //添回 distinct 关键字
            if (spp.getIsDistinct()) {
                returnStr += " distinct ";
            }
            returnStr += spp.getSqlhint();
            //构成子查询的返回语句
            returnStr += spp.getBackFields() ;

            // 添加from语句
            String sqlFrom = spp.getSqlFrom().trim();
            if (sqlFrom.length() > 4 && sqlFrom.substring(0, 4).equalsIgnoreCase("from")) {
                returnStr += " " + spp.getSqlFrom();
            } else {
                returnStr += " from " + spp.getSqlFrom();
            }

            //添回where语句
            String sqlWhere=spp.getSqlWhere().trim();
            if (!"".equalsIgnoreCase(sqlWhere)) {
                if(sqlWhere.startsWith("and")){
                    sqlWhere = " 1 = 1 "+sqlWhere;
                }
                if (sqlWhere.length() > 5 && sqlWhere.substring(0, 5).equalsIgnoreCase("where"))
                {
                    returnStr += " " + sqlWhere;
                } else {
                    returnStr += " where " + sqlWhere;
                }
            }
            //添回group by 语句
            String sqlGroupBy=spp.getSqlGroupBy().trim();
            if (!"".equalsIgnoreCase(sqlGroupBy)) {
                if (sqlGroupBy.length() > 5 && sqlGroupBy.substring(0, 5).equalsIgnoreCase("group")) {
                    returnStr += " " + spp.getSqlGroupBy();
                } else {
                    returnStr += " group by " + spp.getSqlGroupBy();
                }
            }

            //添回 内层 order by 语句
            String sqlOrderBy = spp.getSqlOrderBy().trim();
            if (sqlOrderBy.length() > 5 && sqlOrderBy.substring(0, 5).equalsIgnoreCase("order")) {
                throw new Exception("在orderby条件中不用带上order by 关键字 如: order by id desc,name desc 只能写成 id,name ");
            } else {
                if (spp.getSortWay() == spp.ASC) {
                    returnStr += " order by " + getOrderByAsc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                } else {
                    returnStr += " order by " + getOrderByDesc(spp.getSqlOrderBy(), spp.getPrimaryKey(), true);
                }
            }

            returnStr += ") as tableA ) as r ";

            if(!spp.getOutsqlform().equals("")&&!spp.getOutsqlwhere().equals(""))
            {
                returnStr += ","+spp.getOutsqlform()+" "+spp.getOutsqlwhere()+" order by my_rownum";
            }

            bb.writeLog("getCurrentPageInformixsql:"+returnStr);

            return returnStr;
        } catch (Exception ex) {
            writeLog(ex.getMessage());
            return "";
        }
    }



}
