package cn.myapps.runtime.report.dao;


import static net.sf.dynamicreports.report.builder.DynamicReports.col;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import cn.myapps.authtime.common.dao.PersistenceUtils;
import cn.myapps.runtime.report.model.DRDataSource;
import org.springframework.stereotype.Component;

import cn.myapps.common.data.ParamsTable;
import cn.myapps.common.exception.CommonException;
import cn.myapps.common.exception.OBPMValidateException;
import cn.myapps.common.model.report.QueryColumnInfo;
import cn.myapps.common.model.report.Report;
import cn.myapps.common.util.StringUtil;
import net.sf.dynamicreports.report.builder.column.ColumnBuilder;
import net.sf.dynamicreports.report.builder.datatype.DataTypes;
import net.sf.dynamicreports.report.definition.datatype.DRIDataType;

@Component
public class RuntimeOpenQueryDaoJdbcImpl implements RuntimeOpenQueryDao {

    public List<QueryColumnInfo> getQueryColumnInfos(String sql, String dbDriver, String dbUrl, String dbUser, String dbPass ) throws CommonException {
        if (StringUtil.isBlank(dbDriver) || StringUtil.isBlank(dbUrl) || StringUtil.isBlank(dbUser)) {
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("数据源连接信息不全！");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        List<QueryColumnInfo> resultColumnList = new ArrayList<QueryColumnInfo>();

        Connection conn = null;
        try {
            conn = PersistenceUtils.getDruidDataSource(
                            "RuntimeOpenQueryDaoJdbcImpl",
                            dbUser,dbPass,dbDriver,dbUrl,"100","3000").getConnection();
        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("连接数据库出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("执行sql语句出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }

        try {
            ResultSetMetaData data = rs.getMetaData();
            for (int i = 1; i <= data.getColumnCount(); i ++ ) {
                // 获得指定列的列名
                String columnName = data.getColumnName(i);
                // 获得指定列的数据类型名
                String columnTypeName = data.getColumnTypeName(i);
                // 对应数据类型的类
                String columnClassName = data.getColumnClassName(i);
                // 默认的列的标题
                String columnLabel = data.getColumnLabel(i);

                // the class type of java.sql.Timestamp and java.sql.Date is unsupported by jasper
                if (columnClassName.equals("java.sql.Timestamp") || columnClassName.equals("java.sql.Date")) {
                    columnClassName = "java.util.Date";
                }
                int typeValue = data.getColumnType(i);
                //过滤一些大文本字段
                if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                    QueryColumnInfo queryColumnInfo = new QueryColumnInfo();
                    queryColumnInfo.setColumnName(columnName);
                    queryColumnInfo.setColumnTypeName(columnTypeName);
                    queryColumnInfo.setColumnClassName(columnClassName);
                    queryColumnInfo.setColumnLabel(columnLabel);
                    resultColumnList.add(queryColumnInfo);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("解析列信息出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }

        // 关闭数据库连接
        try {
            stmt.close();
            rs.close();
            conn.close();
        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("关闭数据库连接出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        return resultColumnList;

    }

    @Override
    public List<QueryColumnInfo> getProcedureColumnsInfos(String procedureScript, String dbDriver, String dbUrl, String dbUser, String dbPass) throws Exception {
        if (StringUtil.isBlank(dbDriver) || StringUtil.isBlank(dbUrl) || StringUtil.isBlank(dbUser)) {
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("数据源连接信息不全！");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        List<QueryColumnInfo> resultColumnList = new ArrayList<QueryColumnInfo>();

        Connection conn = null;
        try {
            conn = PersistenceUtils.getDruidDataSource(
                            "RuntimeOpenQueryDaoJdbcImpl",
                            dbUser, dbPass, dbDriver, dbUrl, "100", "3000")
                    .getConnection();

        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("连接数据库出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        CallableStatement statement = null;
        ResultSet rs = null;
        Map<Integer,Object> map = null;
        try {
            // 解析procedure字符串,解析成{call procedureName(?,?,?)}形式,mssql解析成exec procedureName(?,?,?)形式
            String proce;
            if("net.sourceforge.jtds.jdbc.Driver".equals(dbDriver)){
                //sqlServer解析存储过程脚本
                proce = parseProcedureInSqlServer(procedureScript);
            }else{
                proce = parseProcedure(procedureScript);
            }
            // 预编译存储过程proce
            statement = conn.prepareCall(proce);
            // 解析参数
            if (proce.indexOf('(') > -1 && proce.indexOf(')') > -1) {
                //当数据源为oracle时,解析参数,返回的Map<参数index,返回类型>
                if("oracle.jdbc.driver.OracleDriver".equals(dbDriver)){
                    map = parseParametersInOracle(statement, procedureScript, new ParamsTable(), 1, Integer.MAX_VALUE,null);
                }else{
                    parseParameters(statement, procedureScript, new ParamsTable(), 1, Integer.MAX_VALUE,null);
                }
            }
            // 运行该存储过程
            boolean re = statement.execute();
            // 处理返回结果

            //当数据源为oracle时
            if("oracle.jdbc.driver.OracleDriver".equals(dbDriver)){
                if(map != null && map.size() > 0){
                    Set<Map.Entry<Integer, Object>> set = map.entrySet();
                    for (Iterator<Map.Entry<Integer, Object>> it = set.iterator(); it.hasNext();) {
                        Map.Entry<Integer, Object> entry = it.next();
                        if("package".equals(entry.getValue())){
                            // 处理返回结果
                            rs = (ResultSet) statement.getObject(entry.getKey());
                        }
                    }
                }
            }else{
                rs = statement.getResultSet();
            }
            ResultSetMetaData data = rs.getMetaData();
            for (int i = 1; i <= data.getColumnCount(); i ++ ) {
                // 获得指定列的列名
                String columnName = data.getColumnName(i);
                // 获得指定列的数据类型名
                String columnTypeName = data.getColumnTypeName(i);
                // 对应数据类型的类
                String columnClassName = data.getColumnClassName(i);
                // 默认的列的标题
                String columnLabel = data.getColumnLabel(i);

                // the class type of java.sql.Timestamp and java.sql.Date is unsupported by jasper
                if (columnClassName.equals("java.sql.Timestamp") || columnClassName.equals("java.sql.Date")) {
                    columnClassName = "java.util.Date";
                }
                int typeValue = data.getColumnType(i);
                //过滤一些大文本字段
                if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                    QueryColumnInfo queryColumnInfo = new QueryColumnInfo();
                    queryColumnInfo.setColumnName(columnName);
                    queryColumnInfo.setColumnTypeName(columnTypeName);
                    queryColumnInfo.setColumnClassName(columnClassName);
                    queryColumnInfo.setColumnLabel(columnLabel);
                    resultColumnList.add(queryColumnInfo);
                }

            }


        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("执行sql语句出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }finally {
            statement.close();
            rs.close();
            conn.close();
        }
        return resultColumnList;
    }

    /**
     * 创建DynamicReport所需要的数据源
     */
    public void generateDynamicReportDataSource(Report report, String sql, String dbDriver, String dbUrl, String dbUser, String dbPass ) throws CommonException {
        if (StringUtil.isBlank(dbDriver) || StringUtil.isBlank(dbUrl) || StringUtil.isBlank(dbUser)) {
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("数据源连接信息不全！");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        Connection conn = null;
        try {
            conn = PersistenceUtils.getDruidDataSource(
                            "RuntimeOpenQueryDaoJdbcImpl",
                            dbUser, dbPass, dbDriver, dbUrl, "100", "3000")
                    .getConnection();
        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("连接数据库出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("执行sql语句出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }

        try {
            // 创建DRDataSource
            List<QueryColumnInfo> columnList = new ArrayList<QueryColumnInfo>();       // columnList用于创建列信息
            final List<String> columnNameList = new ArrayList<String>();
            ResultSetMetaData metaDatas = rs.getMetaData();
            int colCount = metaDatas.getColumnCount();
            for (int i = 1; i <= colCount; i ++ ) {
                // 获得指定列的列名
                String columnName = metaDatas.getColumnName(i);
                // 获得指定列的数据类型名
                String columnTypeName = metaDatas.getColumnTypeName(i);
                // 对应数据类型的类
                String columnClassName = metaDatas.getColumnClassName(i);
                // 默认的列的标题
                String columnLabel = metaDatas.getColumnLabel(i);

                int typeValue = metaDatas.getColumnType(i);
                //过滤一些大文本字段
                if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                    QueryColumnInfo queryColumnInfo = new QueryColumnInfo();
                    //解决多表联查字段重复
                    for(QueryColumnInfo qc : columnList){
                        if(qc.getColumnName().equals(columnName)){
                            columnName += "1";
                        }
                    }
                    queryColumnInfo.setColumnName(columnName);
                    queryColumnInfo.setColumnTypeName(columnTypeName);
                    queryColumnInfo.setColumnClassName(columnClassName);
                    queryColumnInfo.setColumnLabel(columnLabel);
                    columnList.add(queryColumnInfo);

                    columnNameList.add(columnName);
                }
            }
            final String[] columns = new String[columnNameList.size()];
            columnNameList.toArray(columns);
            DRDataSource drDataSource = new DRDataSource(columns);

            // 填充数据
            boolean hasResultDatas = false;
            while (rs.next()) {

                final List<Object> columnDatas = new ArrayList<Object>();
                for (int i = 1; i <= colCount; i++) {
                    int typeValue = metaDatas.getColumnType(i);
                    //过滤一些大文本字段
                    if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                        columnDatas.add(rs.getObject(i));
                    }
                }
                drDataSource.add(columnDatas.toArray());
                hasResultDatas = true;
            }
            // 设置数据源，若数据源无值的话用一行空白填充
            if (hasResultDatas == true ) {
                report.setDrDataSource(drDataSource);

            }
            else {
                //如果无数据时，给一行空值
                final List<Object> columnDatas = new ArrayList<Object>();
                drDataSource.add(columnDatas.toArray());
                report.setDrDataSource(drDataSource); //null);
            }

            // 创建列信息
            if (columnList.size() > 0 ) {
                List<ColumnBuilder> colms = new ArrayList<>();
                Iterator<QueryColumnInfo> iter = columnList.iterator();
                while (iter.hasNext()) {
                    QueryColumnInfo queryColumnInfo = iter.next();
                    String columnClassName = queryColumnInfo.getColumnClassName();
                    // the class type of java.sql.Timestamp and java.sql.Date is unsupported by jasper
                    if (columnClassName.equals("java.sql.Timestamp") || columnClassName.equals("java.sql.Date")) {
                        columnClassName = "java.util.Date";
                        // 一些数据库字段类型例如TYNYBLOB等，由于JDBC找不到对应的Java类型，会显示为“[B”导致jasper在做类型转换时出错
                    } else if (columnClassName.equals("[B")) {
                        columnClassName = "java.lang.String";
                    }
                    colms.add(col.column(queryColumnInfo.getColumnLabel(), queryColumnInfo.getColumnName(), (DRIDataType)DataTypes.detectType(columnClassName)));    //colms.add(col.column("Item", "item", (DRIDataType)DataTypes.detectType("java.lang.String")));//type.stringType()));
                }
                report.setReportColumnInfos(colms);

            } else {
                report.setReportColumnInfos(null);
            }

        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("创建DRDataSource出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }

        // 关闭数据库连接
        try {
            stmt.close();
            rs.close();
            conn.close();
        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("关闭数据库连接出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

    }
    //存储过程数据源
    public void generateDynamicReportDataSourceByProcedure(Report report,String procedureScript, String dbDriver, String dbUrl, String dbUser, String dbPass) throws Exception {
        if (StringUtil.isBlank(dbDriver) || StringUtil.isBlank(dbUrl) || StringUtil.isBlank(dbUser)) {
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("数据源连接信息不全！");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        Connection conn = null;
        try {
            conn = PersistenceUtils.getDruidDataSource(
                            "RuntimeOpenQueryDaoJdbcImpl",
                            dbUser, dbPass, dbDriver, dbUrl, "100", "3000")
                    .getConnection();
        } catch (Exception e ) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("连接数据库出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;
        }

        CallableStatement stmt = null;
        ResultSet rs = null;
        Map<Integer,Object> map = null;
        try {
            // 解析procedure字符串,解析成{call procedureName(?,?,?)}形式,mssql解析成exec procedureName(?,?,?)形式
            String proce;
            if("net.sourceforge.jtds.jdbc.Driver".equals(dbDriver)){
                //sqlServer解析存储过程脚本
                proce = parseProcedureInSqlServer(procedureScript);
            }else{
                proce = parseProcedure(procedureScript);
            }
            // 预编译存储过程proce
            stmt = conn.prepareCall(proce);
            // 解析参数
            if (proce.indexOf('(') > -1 && proce.indexOf(')') > -1) {
                //当数据源为oracle时,解析参数,返回的Map<参数index,返回类型>
                if("oracle.jdbc.driver.OracleDriver".equals(dbDriver)){
                    map = parseParametersInOracle(stmt, procedureScript, new ParamsTable(), 1, Integer.MAX_VALUE,null);
                }else{
                    parseParameters(stmt, procedureScript, new ParamsTable(), 1, Integer.MAX_VALUE,null);
                }
            }
            // 运行该存储过程
            boolean re = stmt.execute();
            // 处理返回结果

            //当数据源为oracle时
            if("oracle.jdbc.driver.OracleDriver".equals(dbDriver)){
                if(map != null && map.size() > 0){
                    Set<Map.Entry<Integer, Object>> set = map.entrySet();
                    for (Iterator<Map.Entry<Integer, Object>> it = set.iterator(); it.hasNext();) {
                        Map.Entry<Integer, Object> entry = it.next();
                        if("package".equals(entry.getValue())){
                            // 处理返回结果
                            rs = (ResultSet) stmt.getObject(entry.getKey());
                        }
                    }
                }
            }else{
                rs = stmt.getResultSet();
            }

            // 创建DRDataSource
            List<QueryColumnInfo> columnList = new ArrayList<QueryColumnInfo>();       // columnList用于创建列信息
            final List<String> columnNameList = new ArrayList<String>();
            ResultSetMetaData metaDatas = rs.getMetaData();
            int colCount = metaDatas.getColumnCount();
            for (int i = 1; i <= colCount; i ++ ) {
                // 获得指定列的列名
                String columnName = metaDatas.getColumnName(i);
                // 获得指定列的数据类型名
                String columnTypeName = metaDatas.getColumnTypeName(i);
                // 对应数据类型的类
                String columnClassName = metaDatas.getColumnClassName(i);
                // 默认的列的标题
                String columnLabel = metaDatas.getColumnLabel(i);

                int typeValue = metaDatas.getColumnType(i);
                //过滤一些大文本字段
                if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                    QueryColumnInfo queryColumnInfo = new QueryColumnInfo();
                    queryColumnInfo.setColumnName(columnName);
                    queryColumnInfo.setColumnTypeName(columnTypeName);
                    queryColumnInfo.setColumnClassName(columnClassName);
                    queryColumnInfo.setColumnLabel(columnLabel);
                    columnList.add(queryColumnInfo);

                    columnNameList.add(columnName);
                }
            }
            final String[] columns = new String[columnNameList.size()];
            columnNameList.toArray(columns);
            DRDataSource drDataSource = new DRDataSource(columns);

            // 填充数据
            boolean hasResultDatas = false;
            while (rs.next()) {

                final List<Object> columnDatas = new ArrayList<Object>();
                for (int i = 1; i <= colCount; i++) {
                    int typeValue = metaDatas.getColumnType(i);
                    //过滤一些大文本字段
                    if(typeValue != Types.CLOB && typeValue != Types.BLOB && typeValue != Types.LONGVARBINARY &&typeValue != Types.JAVA_OBJECT && typeValue != Types.SQLXML){
                        columnDatas.add(rs.getObject(i));
                    }
                }
                drDataSource.add(columnDatas.toArray());
                hasResultDatas = true;
            }
            // 设置数据源，若数据源无值的话用一行空白填充
            if (hasResultDatas == true ) {
                report.setDrDataSource(drDataSource);

            }
            else {
                //如果无数据时，给一行空值
                final List<Object> columnDatas = new ArrayList<Object>();
                //
                drDataSource.add(columnDatas.toArray());
                report.setDrDataSource(drDataSource); //null);
            }

            // 创建列信息
            if (columnList.size() > 0 ) {
                List<ColumnBuilder> colms = new ArrayList<>();
                Iterator<QueryColumnInfo> iter = columnList.iterator();
                while (iter.hasNext()) {
                    QueryColumnInfo queryColumnInfo = iter.next();
                    String columnClassName = queryColumnInfo.getColumnClassName();
                    // the class type of java.sql.Timestamp and java.sql.Date is unsupported by jasper
                    if (columnClassName.equals("java.sql.Timestamp") || columnClassName.equals("java.sql.Date")) {
                        columnClassName = "java.util.Date";
                        // 一些数据库字段类型例如TYNYBLOB等，由于JDBC找不到对应的Java类型，会显示为“[B”导致jasper在做类型转换时出错
                    } else if (columnClassName.equals("[B")) {
                        columnClassName = "java.lang.String";
                    }
                    colms.add(col.column(queryColumnInfo.getColumnLabel(), queryColumnInfo.getColumnName(), (DRIDataType)DataTypes.detectType(columnClassName)));    //colms.add(col.column("Item", "item", (DRIDataType)DataTypes.detectType("java.lang.String")));//type.stringType()));
                }
                report.setReportColumnInfos(colms);

            } else {
                report.setReportColumnInfos(null);
            }

        } catch (Exception e) {
            e.printStackTrace();
            CommonException irptException = new CommonException();
            StringBuffer exceptionMsgSB = new StringBuffer();
            exceptionMsgSB.append("执行sql语句出现异常！");
            exceptionMsgSB.append(e.getClass().getName());
            exceptionMsgSB.append(StringUtil.isBlank(e.getMessage()) ? e.getMessage() : "");
            irptException.setExceptionMsg(exceptionMsgSB.toString());
            throw irptException;

        }
    }

    /**
     * 解析iscript的存储过程脚本
     *
     * 2.6新增的方法
     *
     * @param procedure
     * @return {call procedureName(?,?)}形式的表达字符串
     */
    protected String parseProcedure(String procedure) throws Exception {
        StringBuffer p = new StringBuffer();
        if (!procedure.trim().startsWith("{")) {
            if (!procedure.trim().startsWith("call "))
                throw new OBPMValidateException(
                        "missing 'call' at the beginning of [" + procedure
                                + "]");
            p.append('{');
        } else if (!procedure.trim().startsWith("call", 1)) {
            throw new OBPMValidateException(
                    "missing 'call' at the beginning of [" + procedure + "]");
        }
        int index1 = procedure.indexOf('(');
        int index2 = procedure.lastIndexOf(')');
        if (index1 < 0 && index2 > -1) {
            throw new OBPMValidateException("missing '(' on [" + procedure
                    + "]");
        } else if (index1 > -1 && index2 < 0) {
            throw new OBPMValidateException("missing ')' on [" + procedure
                    + "]");
        } else if (index1 > -1 && index2 > -1) {
            p.append(procedure.substring(procedure.indexOf('{') + 1, index1)
                    .trim());
            p.append('(');
            String[] parametersAsArray = procedure
                    .substring(index1 + 1, index2).split(",");
            for (int i = 0; i < parametersAsArray.length; i++) {
                p.append('?');
                if (i < parametersAsArray.length - 1)
                    p.append(',');
            }
            p.append(')');
        } else {
            p.append(procedure.substring(procedure.indexOf('{') + 1).trim());
        }
        if (!procedure.trim().endsWith("}")) {
            p.append('}');
        }

        return p.toString();
    }
    /**
     * 解析存储过程脚本的参数
     *
     * 2.6版本新增的方法
     *
     * @param statement
     * @param procedure
     * @param params
     * @param page
     * @param lines
     * @param domainid
     * @throws Exception
     */
    protected void parseParameters(CallableStatement statement,
                                   String procedure, ParamsTable params, int page, int lines,
                                   String domainid) throws Exception {
        int index1 = procedure.indexOf("(");
        int index2 = procedure.lastIndexOf(")");
        String parameters = procedure.substring(index1 + 1, index2);
        String[] paramsAsArray = parameters.split(",");
        if (paramsAsArray != null && paramsAsArray.length > 0) {
            for (int i = 0; i < paramsAsArray.length; i++) {
                String[] p = paramsAsArray[i].split(":");
                if (p != null && p.length == 2) {
                    String type = p[0].trim();
                    String value = p[1].trim();
                    if ("String".equalsIgnoreCase(type)) {
                        // #curDomain,#sortCol,#sortStatus,#orderby为系统自带变量,用户定义时不能与此相冲突
                        if ("#curDomain".equals(value)) {
                            statement.setString(i + 1, domainid);
                        } else if ("#sortCol".equals(value)) {
                            statement.setString(i + 1,
                                    params.getParameterAsString("_sortCol"));
                        } else if ("#sortStatus".equals(value)) {
                            statement.setString(i + 1,
                                    params.getParameterAsString("_sortStatus"));
                        } else if ("#orderby".equals(value)) {
                            statement.setString(i + 1,
                                    params.getParameterAsString("_orderby"));
                        } else {
                            statement.setString(i + 1, value);
                        }
                    } else if ("int".equalsIgnoreCase(type)) {
                        // #curPage为系统自带变量,用户定义时不能与此相冲突
                        if ("#curPage".equals(value)) {
                            statement.setInt(i + 1, page);
                        } else if ("#lines".equals(value)) {// #lines为系统自带变量,用户定义时不能与此相冲突
                            statement.setInt(i + 1, lines);
                        } else {
                            statement.setInt(i + 1, Integer.valueOf(value));
                        }
                    } else if ("Date".equalsIgnoreCase(type)
                            || "java.sql.Date".equalsIgnoreCase(type)) {
                        statement.setDate(i + 1, java.sql.Date.valueOf(value));
                    } else if ("Time".equalsIgnoreCase(type)
                            || "java.sql.Time".equalsIgnoreCase(type)) {
                        statement.setTime(i + 1, java.sql.Time.valueOf(value));
                    } else if ("Timestamp".equalsIgnoreCase(type)
                            || "java.sql.Timestamp".equalsIgnoreCase(type)) {
                        statement.setTimestamp(i + 1,
                                java.sql.Timestamp.valueOf(value));
                    } else if ("double".equalsIgnoreCase(type)) {
                        statement.setDouble(i + 1, Double.valueOf(value));
                    } else if ("float".equalsIgnoreCase(type)) {
                        statement.setFloat(i + 1, Float.valueOf(value));
                    } else if ("long".equalsIgnoreCase(type)) {
                        statement.setLong(i + 1, Long.valueOf(value));
                    } else {
                        throw new OBPMValidateException("not support type ["
                                + type + "]");
                    }
                }
            }
        }
    }

    /**
     * sqlServer解析iscript的存储过程脚本
     *
     * 2.6新增的方法
     *
     * @param procedure
     * @return exec procedureName(?,?)形式的表达字符串
     */

    protected String parseProcedureInSqlServer(String procedure) throws Exception {
        StringBuffer p = new StringBuffer();
        if (!procedure.trim().startsWith("exec "))
            throw new OBPMValidateException("missing 'exec' at the beginning of [" + procedure + "]");
        int index1 = procedure.indexOf('(');
        int index2 = procedure.lastIndexOf(')');
        if (index1 < 0 && index2 > -1) {
            throw new OBPMValidateException("missing '(' on [" + procedure + "]");
        } else if (index1 > -1 && index2 < 0) {
            throw new OBPMValidateException("missing ')' on [" + procedure + "]");
        } else if (index1 > -1 && index2 > -1) {
            p.append(procedure.substring(procedure.indexOf('{') + 1, index1).trim());
            p.append('(');
            String[] parametersAsArray = procedure.substring(index1 + 1, index2).split(",");
            for (int i = 0; i < parametersAsArray.length; i++) {
                p.append('?');
                if (i < parametersAsArray.length - 1)
                    p.append(',');
            }
            p.append(')');
        } else {
            p.append(procedure.substring(procedure.indexOf('{') + 1).trim());
        }

        return p.toString();
    }

    /**
     * oracle解析存储过程脚本的参数
     *
     * 2.6版本新增的方法
     *
     * @param statement
     * @param procedure
     * @param params
     * @param page
     * @param lines
     * @param domainid
     * @throws Exception
     */
    private Map<Integer,Object> parseParametersInOracle(CallableStatement statement, String procedure, ParamsTable params, int page,
                                                        int lines, String domainid) throws Exception {
        Map<Integer,Object> map = new HashMap<Integer, Object>();
        int index1 = procedure.indexOf("(");
        int index2 = procedure.lastIndexOf(")");
        String parameters = procedure.substring(index1 + 1, index2);
        String[] paramsAsArray = parameters.split(",");
        if (paramsAsArray != null && paramsAsArray.length > 0) {
            for (int i = 0; i < paramsAsArray.length; i++) {
                String[] p = paramsAsArray[i].split(":");
                if (p != null && p.length == 2) {
                    String type = p[0].trim();
                    String value = p[1].trim();
                    if ("String".equalsIgnoreCase(type)) {
                        // #curDomain,#sortCol,#sortStatus,#orderby为系统自带变量,用户定义时不能与此相冲突
                        if ("#curDomain".equals(value)) {
                            statement.setString(i + 1, domainid);
                        } else if ("#sortCol".equals(value)) {
                            statement.setString(i + 1, params.getParameterAsString("_sortCol"));
                        } else if ("#sortStatus".equals(value)) {
                            statement.setString(i + 1, params.getParameterAsString("_sortStatus"));
                        } else if ("#orderby".equals(value)) {
                            statement.setString(i + 1, params.getParameterAsString("_orderby"));
                        } else {
                            statement.setString(i + 1, value);
                        }
                    } else if ("int".equalsIgnoreCase(type)) {
                        // #curPage为系统自带变量,用户定义时不能与此相冲突
                        if ("#curPage".equals(value)) {
                            statement.setInt(i + 1, page);
                        } else if ("#lines".equals(value)) {// #lines为系统自带变量,用户定义时不能与此相冲突
                            statement.setInt(i + 1, lines);
                        } else {
                            statement.setInt(i + 1, Integer.valueOf(value));
                        }
                    } else if ("Date".equalsIgnoreCase(type) || "java.sql.Date".equalsIgnoreCase(type)) {
                        statement.setDate(i + 1, java.sql.Date.valueOf(value));
                    } else if ("Time".equalsIgnoreCase(type) || "java.sql.Time".equalsIgnoreCase(type)) {
                        statement.setTime(i + 1, java.sql.Time.valueOf(value));
                    } else if ("Timestamp".equalsIgnoreCase(type) || "java.sql.Timestamp".equalsIgnoreCase(type)) {
                        statement.setTimestamp(i + 1, java.sql.Timestamp.valueOf(value));
                    } else if ("double".equalsIgnoreCase(type)) {
                        statement.setDouble(i + 1, Double.valueOf(value));
                    } else if ("float".equalsIgnoreCase(type)) {
                        statement.setFloat(i + 1, Float.valueOf(value));
                    } else if ("long".equalsIgnoreCase(type)) {
                        statement.setLong(i + 1, Long.valueOf(value));
                    } else if("out".equalsIgnoreCase(type)){
                        //注册输出参数
                        if("package".equalsIgnoreCase(value)){
                            statement.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.CURSOR);
                            map.put(i + 1, "package");
                        }
                    } else {
                        throw new OBPMValidateException("not support type [" + type + "]");
                    }
                }else if(p != null){
                    throw new OBPMValidateException("parameter [" + paramsAsArray[i] + "] format is not correct");
                }
            }
        }
        return map;
    }
}
