# OperationPanelQueryDAOImpl.java

    package com.mycim.server.wip.dao.impl;

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.utils.MiscUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.server.wip.dao.OperationPanelQueryDAO;
    import com.mycim.server.wip.dao.mapper.OperationPanelLotMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.LotStatus;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.*;

    /**
     * @author Johnson.Wang
     * @version 6.0.0
     * @date 2019/9/19
     **/
    @Repository
    public class OperationPanelQueryDAOImpl implements OperationPanelQueryDAO {

        private static String STATION_RRN = "stationRrn";

        private static String EQUIPMENT_RRN = "equipmentRrn";

        private static String LOT_STATUS = "lotStatus";

        private static String DUMMY_FLAG = "dummyFlag";

        private final JdbcTemplate jdbcTemplate;

        public OperationPanelQueryDAOImpl(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }

        @Override
        public List<Map<String, Object>> listLotForOperatorPanel(Map condition) {
            String filedSql = "LOT.FACILITY_RRN, LOT.LOT_ID, LOT.BASED_LOT_RRN,LOT.CREATED_PLAN_LOT_RRN,LOT.HOT_FLAG,LOT" +
                    ".PRIORITY,LOT.STARTED_FLAG," + "LOT.STARTED_TIMESTAMP,LOT.CREATE_CATEGORY,LOT" +
                    ".CREATED_TIMESTAMP,LOT.END_TIMESTAMP,LOT" + ".DUE_DATE,LOT.SCHEDULE_DUE_DATE," + "LOT" +
                    ".LATE_SCHEDULED_FLAG,LOT.ESTIMATED_REMAIN_TIME,LOT.DUMMY_FLAG,LOT.LOT_TYPE,LOT" +
                    ".LOT_OWNER,LOT.QTY1,LOT.QTY2,LOT.PREV_OPERATION_RRN," + "LOT.DUMMY_QTY1,LOT.INPUT_QTY1,LOT" +
                    ".INPUT_QTY2,LOT.SUBCONTRACTOR_RRN,LOT.LOT_COMMENTS,LOT" + ".STEP_SEQUENCE,LOT" +
                    ".STEP_NUMBER_IN_PROCESS," + "LOT.HOLD_TIMESTAMP,LOT.REWORK_TRANS_RRN,LOT.REWORK_CATEGORY,LOT" +
                    ".CARRIER_RRN,LOT" + ".BEFORE_STATUS,LOT.LOT_STATUS,LOT.PROCESSING_STATUS," + "LOT" +
                    ".QUEUE_TIMESTAMP,LOT.PRODUCT_RRN,LOT.PRODUCT_VERSION,LOT.PROCESS_RRN,LOT" + ".PROCESS_VERSION,LOT" +
                    ".PROCESS_STEP_VERSION,LOT.PROCESS_STEP_ID_VERSION," + "LOT" +
                    ".OPERATION_RRN,LOT.OPERATION_VERSION,LOT.NEXT_STEP_VERSION1,LOT" + ".NEXT_STEP_ID_VERSION1," +
                    "LOT.NEXT_OPERATION_RRN1,LOT.NEXT_STEP_VERSION2," + "LOT.NEXT_STEP_ID_VERSION2,LOT" +
                    ".NEXT_OPERATION_RRN2,LOT.STAGE_ID,LOT.LAYER_ID,LOT" + ".ROUTE_SEQ,LOT.OPERATION_SEQ,LOT" +
                    ".BOR_RRN,LOT.EQPT_RRN," + "LOT.RECIPE_LOGICAL_RRN,LOT.RECIPE_PHYSICAL_ID,LOT.RECIPE_STRING," +
                    "LOT_EXT" + ".TEMP_RECIPE_STRING,BATCH_LOT_STORE.BATCH_ID," + "LOT_EXT.ATTRIBUTE_DATA1,LOT" +
                    ".NEXT_RECIPE_STRING1,LOT.NEXT_RECIPE_STRING2,LOT.JOB_RRN,LOT" + ".NEXT_JOB_RRN1,LOT" +
                    ".NEXT_JOB_RRN2,LOT_EXT.ATTRIBUTE_DATA3,LOT_EXT.CUSTOMER_ID," + " ROUND(" +
                    "(GETMINREMAINTIMEBYLOTRRN(LOT.LOT_RRN, '0') / 3600), 2) AS Q_TIME," + "LOT.EXECUTION_RRN,LOT" +
                    ".LOT_RRN,LOT.WFL_STEP_PATH,LOT.RETICLE_RRN,TIME_REMAINING,LOT" + ".SHIPPED_QTY1,LOT" +
                    ".SHIPPED_QTY2,LOT.PRODUCT_LAYER,LOT.LOT_PLAN_TYPE," + "CASE WHEN LOT.REWORK_CATEGORY IS NULL" +
                    " OR LOT.REWORK_CATEGORY = '' " + "THEN REGEXP_SUBSTR(LOT.PROCESS_STEP_VERSION, '[^,]+', " +
                    "INSTR(LOT.PROCESS_STEP_VERSION, " + "'|', 1, 1) + 1, 1) " + "ELSE REGEXP_SUBSTR(LOT" +
                    ".PROCESS_STEP_VERSION, '[^,]+', INSTR(LOT.PROCESS_STEP_VERSION, " + "'|', 1, 2) + 1, 1) " +
                    "END AS ROUTE_RRN ";

            String baseSql = buildBaseSql(condition, filedSql);

            StringBuilder contextFiledSql = new StringBuilder();
            contextFiledSql.append(" getinstanceid(getcontextreciperrn(T.PRODUCT_RRN, T.PROCESS_RRN, T.ROUTE_RRN, T" +
                                           ".OPERATION_RRN, " +
                                           "T.PROCESS_VERSION,T.PRODUCT_VERSION)) AS contxt_recipe_id,");
            contextFiledSql.append("getcontextreciperrn(T.PRODUCT_RRN, T.PROCESS_RRN, T.ROUTE_RRN, T.OPERATION_RRN, T" +
                                           ".PROCESS_VERSION,T.PRODUCT_VERSION) AS contxt_recipe_rrn,");
            contextFiledSql.append("getcontextstageid(T.PRODUCT_RRN, T.PROCESS_RRN, T.ROUTE_RRN, T.OPERATION_RRN, T" +
                                           ".PROCESS_VERSION) as context_stage_id,");
            contextFiledSql.append("getflowstepdesc(T.PRODUCT_RRN, T.PROCESS_RRN, T.ROUTE_RRN, T.OPERATION_RRN, T" +
                                           ".PROCESS_VERSION) " + "as context_step_desc,");
            contextFiledSql.append("getflowseq(T.PROCESS_RRN, T.ROUTE_RRN, T.OPERATION_RRN, T.PROCESS_VERSION) as " +
                                           "context_flow_seq,");
            contextFiledSql
                    .append("CONCAT(CONCAT(T.HOT_FLAG, '-'), T.PRIORITY) AS HOTFLAG_PRIORITY,O.MAX_QUEUE_TIME,ROUTE_T" +
                                    ".INSTANCE_DESC AS ROUTE_DESC,WORK_AREA_REF.DATA_1_VALUE as operation_work_area,");
            contextFiledSql.append("CASE WHEN LENGTH(T.ATTRIBUTE_DATA3) > 0 THEN 'Y' ELSE 'N' END AS PI_LOT, CARRIER_T" +
                                           ".INSTANCE_ID " + "AS CARRIER_ID, OPERATION_T.INSTANCE_ID AS OPERATION_ID, ");
            contextFiledSql.append("RETICLE_T.INSTANCE_ID AS RETICLE_ID, PRODUCT_T.INSTANCE_ID AS PRODUCT_ID, PROCESS_T" +
                                           ".INSTANCE_ID" + " AS PROCESS_ID, RECIPE_T.INSTANCE_ID AS LOGICAL_RECIPE_ID, ");
            contextFiledSql
                    .append("RECIPE_DOC.DOCUMENT_FILE AS RECIPE_DOC_FILE, RECIPE_DOC.COMMENTS AS RECIPE_DOC_COMMENTS, " +
                                    "ENTITY_T.INSTANCE_ID AS EQPT_ID, ");
            contextFiledSql.append("getequipmentconstrainmapping(T.LOT_RRN, T.PRODUCT_RRN, T.PROCESS_RRN, T.ROUTE_RRN, T" +
                                           ".OPERATION_RRN, T.ROUTE_SEQ, T.OPERATION_SEQ) AS EQPT_CONSTRAIN,");
            contextFiledSql.append("T.* ");

            String contextSql = "SELECT TT.* FROM (SELECT " + contextFiledSql + " FROM (" + baseSql + ") T, NAMED_OBJECT " +
                    "ROUTE_T, OPERATION O, OPERATION_EXT OE, NAMED_OBJECT " + "CARRIER_T, " + "NAMED_OBJECT OPERATION_T, " +
                    "NAMED_OBJECT RETICLE_T, NAMED_OBJECT PRODUCT_T, " + "NAMED_OBJECT PROCESS_T, NAMED_OBJECT RECIPE_T, " +
                    "OBJECT_DOCUMENT RECIPE_DOC, " + "NAMED_OBJECT ENTITY_T, " + "(SELECT R.KEY_1_VALUE, R.DATA_1_VALUE " +
                    "FROM REFERENCE_FILE_DETAIL R WHERE " + "getinstanceid(R.REFERENCE_FILE_RRN) = '$$WORK_AREA') " +
                    "WORK_AREA_REF " + "WHERE T.ROUTE_RRN = ROUTE_T.INSTANCE_RRN(+) AND T.OPERATION_RRN = O" +
                    ".OPERATION_RRN(+) AND T.OPERATION_RRN = OE.OPERATION_RRN(+) " + "AND OE.ATTRIBUTE_DATA5 = " +
                    "WORK_AREA_REF.KEY_1_VALUE(+) AND T.CARRIER_RRN = " + "CARRIER_T.INSTANCE_RRN(+) AND T.OPERATION_RRN " +
                    "= OPERATION_T.INSTANCE_RRN(+) " + "AND T.RETICLE_RRN = RETICLE_T.INSTANCE_RRN(+) AND T.PRODUCT_RRN =" +
                    " PRODUCT_T" + ".INSTANCE_RRN(+) AND T.PROCESS_RRN = PROCESS_T.INSTANCE_RRN(+) " + "AND T" +
                    ".RECIPE_LOGICAL_RRN = RECIPE_T.INSTANCE_RRN(+) AND T.RECIPE_LOGICAL_RRN" + " = RECIPE_DOC" +
                    ".INSTANCE_RRN(+) AND T.EQPT_RRN = ENTITY_T.INSTANCE_RRN(+)) TT ";

            String constrainWhereSql = "";
            String objectRrn = MapUtils.getString(condition, "objectRrn");
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");
                constrainWhereSql += " WHERE (TT.EQPT_CONSTRAIN IS NULL OR (TT.EQPT_CONSTRAIN IS NOT NULL AND (";
                String[] rrns = StringUtils.split(objectRrn, ",");
                int rrnCount = 0;
                for (String rrn : rrns) {
                    rrnCount += 1;
                    if (rrnCount == 1) {
                        constrainWhereSql += " INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    } else {
                        constrainWhereSql += " OR INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    }
                }
                constrainWhereSql += ")))";
            }

            contextSql += (constrainWhereSql + " order by TT.create_category desc, TT.lot_status, TT.lot_id, TT" +
                    ".TIME_REMAINING, " + "TT.PRIORITY, TT.QUEUE_TIMESTAMP, TT.HOT_FLAG DESC ");
            String pagingSql = contextSql;
            if (MapUtils.getBooleanValue(condition, "pagingFlag")) {
                // 分页处理
                int currentPage = MapUtils.getIntValue(condition, "currentPage");
                int startRow = MapUtils.getIntValue(condition, "startRow");
                int pageSize = MapUtils.getIntValue(condition, "pageSize");
                pagingSql = "select * from (select rownum as rowno, page_t.* from (" + contextSql + ") page_t where " +
                        "rownum <= " + (startRow + pageSize) + ") row_t where rowno > " + startRow;
            }

            return jdbcTemplate.query(pagingSql, new OperationPanelLotMapper());
        }

        @Override
        public int countLotForOperatorPanel(Map<String, Object> condition) {
            String filedSql =
                    "LOT.LOT_RRN, LOT.PRODUCT_RRN, LOT.PROCESS_RRN, LOT.PROCESS_VERSION, LOT.OPERATION_RRN, " + "LOT" +
                            ".ROUTE_SEQ, LOT.OPERATION_SEQ, " + "CASE WHEN LOT.REWORK_CATEGORY IS NULL OR LOT" +
                            ".REWORK_CATEGORY = '' " + "THEN REGEXP_SUBSTR(LOT.PROCESS_STEP_VERSION, '[^,]+', INSTR(LOT" +
                            ".PROCESS_STEP_VERSION, " + "'|', 1, 1) + 1, 1) " +
                            "ELSE REGEXP_SUBSTR(LOT.PROCESS_STEP_VERSION, " + "'[^,]+', INSTR(LOT.PROCESS_STEP_VERSION, " +
                            "'|', 1, 2) + 1, 1) " + "END AS ROUTE_RRN ";

            String sql = buildBaseSql(condition, filedSql);

            String eqptConstrainSql =
                    "SELECT getequipmentconstrainmapping(T.LOT_RRN, T.PRODUCT_RRN, T.PROCESS_RRN, T" + ".ROUTE_RRN, " +
                            "T.OPERATION_RRN, " + "T.ROUTE_SEQ, T.OPERATION_SEQ) AS EQPT_CONSTRAIN FROM (" + sql + ") T ";
            String constrainWhereSql = "";
            String objectRrn = MapUtils.getString(condition, "objectRrn");
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");
                constrainWhereSql += " WHERE (TT.EQPT_CONSTRAIN IS NULL OR (TT.EQPT_CONSTRAIN IS NOT NULL AND (";
                String[] rrns = StringUtils.split(objectRrn, ",");
                int rrnCount = 0;
                for (String rrn : rrns) {
                    rrnCount += 1;
                    if (rrnCount == 1) {
                        constrainWhereSql += " INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    } else {
                        constrainWhereSql += " OR INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    }
                }
                constrainWhereSql += ")))";
            }

            String countSql = "SELECT COUNT(1) FROM  (" + eqptConstrainSql + ") TT " + constrainWhereSql;
            return jdbcTemplate.queryForObjectWithNull(countSql, int.class);
        }

        public String buildBaseSql(Map<String, Object> condition, String filedSql) {
            String tableTime =
                    "SELECT LOT_RRN, DECODE(SIGN(TIME_REMAINING), -1, 0, TIME_REMAINING) AS TIME_REMAINING " + " FROM " +
                            "(select lot_rrn,min((ROUND(TO_NUMBER(TO_DATE(TO_CHAR(T0.START_TIME, 'YYYY-MM-DD " + "HH24:MI" +
                            ":SS'), 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * 86400)) + T0.TIMELIMIT) " + " AS TIME_REMAINING" +
                            " from TIMELIMIT_STATUS t0 where t0.timelimit >= 0 and t0.status = " + "'NORMAL' group by " +
                            "lot_rrn)";

            String sql = "SELECT " + filedSql + " FROM LOT,LOT_EXT, BATCH_LOT_STORE, (" + tableTime + ") T ";

            StringBuffer whereSql = new StringBuffer(
                    " WHERE LOT.LOT_RRN = LOT_EXT.LOT_RRN(+) and lot.lot_rrn=t.lot_rrn(+) and LOT" + ".LOT_RRN" +
                            "=BATCH_LOT_STORE.LOT_RRN(+) and (lot.is_dummy_lot is null or lot.is_dummy_lot = '0')");

            String objectRrn = MapUtils.getString(condition, "objectRrn");
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");
            }
            String talotSql = getTableLotSql(objectRrn);
            if (StringUtils.isNotBlank(talotSql)) {
                sql = sql + "," + talotSql;
                whereSql.append(getTableLotWhereSql(objectRrn));
            }

            if (MapUtils.getObject(condition, "lotStatus") != null) {
                List statusList = (List) condition.get("lotStatus");
                whereSql.append(" AND (LOT.LOT_STATUS='" + statusList.remove(0) + "'");
                for (Iterator iterator = statusList.iterator(); iterator.hasNext(); ) {
                    String status = (String) iterator.next();
                    whereSql.append(" OR LOT.LOT_STATUS='" + status + "'");
                }
                whereSql.append(" ) ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "lotCategory", StringUtils.EMPTY))) {
                whereSql.append(" AND LOT.CREATE_CATEGORY IN (" + MapUtils.getString(condition, "lotCategory") + ") ");
            }

            if (condition.get(DUMMY_FLAG) != null) {
                whereSql.append(" AND  DUMMY_FLAG = '" + condition.get(DUMMY_FLAG) + "'");
            }

            Object stationRrn = MapUtils.getObject(condition, "stationRrn");
            if (stationRrn != null) {
                whereSql.append(
                        " AND ((lot.operation_rrn IN (SELECT distinct to_rrn FROM relation t WHERE t" + ".link_type" +
                                "='STATION_TO_OPERATION' AND t.from_rrn=" + stationRrn + "))) ");
            }

            if (MapUtils.getBoolean(condition, "jobGridFlag") != null &&
                    MapUtils.getBooleanValue(condition, "jobGridFlag") && objectRrn != null) {
                whereSql.append(" AND LOT.JOB_RRN=JOB.JOB_RRN AND JOB.EQPT_RRN in (" + objectRrn + ")");
                sql = sql + "," + DataBaseNames.JOB;
            }

            String sapphire = MapUtils.getString(condition, "Sapphire");
            // 查询Bonding批次
            if (sapphire != null && sapphire.equalsIgnoreCase("isSapphire")) {
                // whereSql.append(" AND LOT.PRODUCT_RRN IN (SELECT INSTANCE_RRN FROM NAMED_OBJECT WHERE
                // OBJECT_TYPE="+MiscUtils.parseSQL(ObjectList.SAPPHIRE)+")");
                whereSql.append(" AND LOT.OPERATION_RRN IN (SELECT OO.OPERATION_RRN FROM OPERATION OO,NAMED_OBJECT N1," +
                                        "NAMED_OBJECT N2 " +
                                        " WHERE OO.MVIN_WFL_RRN=N1.INSTANCE_RRN AND OO.MVOU_WFL_RRN=N2" + ".INSTANCE_RRN " +
                                        " AND N1.INSTANCE_ID='MOVEIN_BONDED_STD' AND N2" +
                                        ".INSTANCE_ID='MOVEOUT_AUTOBONDED_STD')");
            }
            // 查询非Bonding批次
            if (sapphire != null && sapphire.equalsIgnoreCase("notSapphire")) {
                // whereSql.append(" AND LOT.PRODUCT_RRN NOT IN (SELECT INSTANCE_RRN FROM NAMED_OBJECT WHERE
                // OBJECT_TYPE="+MiscUtils.parseSQL(ObjectList.SAPPHIRE)+")");
            }
            String lotId = MapUtils.getString(condition, "lotId");
            // 派工页面查询条件
            if (StringUtils.isNotBlank(lotId)) {
                whereSql.append(" AND lot.LOT_ID LIKE '" + lotId + "'");
            }
            String proId = MapUtils.getString(condition, "proId");
            if (StringUtils.isNotBlank(proId)) {
                whereSql.append(
                        " AND PRODUCT_RRN IN ( SELECT INSTANCE_RRN FROM NAMED_OBJECT WHERE INSTANCE_ID IN " + "(SELECT " +
                                "INSTANCE_ID FROM NAMED_OBJECT where INSTANCE_ID LIKE '" + proId + "'))");
            }

            sql = sql + whereSql.toString();
            return sql;
        }

        @Override
        public List<Map<String, Object>> listLotForOperatorPanelByRuncard(Map<String,Object> condition) {
            String filedSql = "L.FACILITY_RRN, L.LOT_RRN, SL.SHOW_LOT_ID LOT_ID, SL.LOT_ID RC_LOT_ID, L.BASED_LOT_RRN, L.CREATED_PLAN_LOT_RRN,"
                    + " L.HOT_FLAG, L.PRIORITY, L.STARTED_FLAG, L.STARTED_TIMESTAMP, L.CREATE_CATEGORY,"
                    + " L.CREATED_TIMESTAMP, L.END_TIMESTAMP, L.DUE_DATE, L.SCHEDULE_DUE_DATE, L.LATE_SCHEDULED_FLAG,"
                    + " L.ESTIMATED_REMAIN_TIME, L.DUMMY_FLAG, L.LOT_TYPE, L.LOT_OWNER, L.QTY1, L.QTY2, ML.PRODUCT_RRN,ML.PRODUCT_VERSION,"
                    + " ML.PROCESS_RRN, CASE WHEN ML.REWORK_CATEGORY IS NULL OR ML.REWORK_CATEGORY = ''"
                    + " THEN REGEXP_SUBSTR(ML.PROCESS_STEP_VERSION, '[^,]+', INSTR(ML.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)"
                    + " ELSE REGEXP_SUBSTR(ML.PROCESS_STEP_VERSION,'[^,]+',INSTR(ML.PROCESS_STEP_VERSION, '|', 1, 2) + 1,1)"
                    + " END AS ROUTE_RRN, RCS.OPERATION_RRN, L.OPERATION_VERSION, ML.PROCESS_VERSION, SL.CARRIER_RRN,"
                    + " L.BEFORE_STATUS, L.LOT_STATUS, L.QUEUE_TIMESTAMP, L.NEXT_STEP_VERSION1, L.NEXT_STEP_ID_VERSION1,"
                    + " L.NEXT_OPERATION_RRN1, L.NEXT_STEP_VERSION2, L.NEXT_STEP_ID_VERSION2, L.NEXT_OPERATION_RRN2,"
                    + " ML.STAGE_ID, L.LAYER_ID, L.ROUTE_SEQ, L.OPERATION_SEQ, L.BOR_RRN, L.RECIPE_LOGICAL_RRN,"
                    + " L.RECIPE_PHYSICAL_ID, L.RECIPE_STRING, LE.TEMP_RECIPE_STRING, BL.BATCH_ID, LE.ATTRIBUTE_DATA1,"
                    + " L.NEXT_RECIPE_STRING1, L.NEXT_RECIPE_STRING2, L.JOB_RRN, L.NEXT_JOB_RRN1, L.NEXT_JOB_RRN2,"
                    + " LE.ATTRIBUTE_DATA3, LE.CUSTOMER_ID, L.EXECUTION_RRN, L.WFL_STEP_PATH, L.SHIPPED_QTY1,"
                    + " L.SHIPPED_QTY2, L.PRODUCT_LAYER, L.LOT_PLAN_TYPE, ML.PROCESS_STEP_VERSION,"
                    + " ML.PROCESS_STEP_ID_VERSION, SLS.EQPT_RRN, SLS.EQPT_ID, SLS.EQPT_GROUP_RRN, SLS.EQPT_GROUP_ID,"
                    + " RCS.RETICLE_RRN, RCS.RETICLE_GROUP_RRN, L.PREV_OPERATION_RRN, RCS.RECIPE_RRN, RCS.RECIPE_ID,"
                    + " SL.CARRIER_ID, SLS.WORK_AREA, L.DUMMY_QTY1, L.INPUT_QTY1, L.INPUT_QTY2, L.SUBCONTRACTOR_RRN,"
                    + " L.LOT_COMMENTS, L.STEP_SEQUENCE, L.STEP_NUMBER_IN_PROCESS, L.HOLD_TIMESTAMP, L.REWORK_TRANS_RRN,"
                    + " L.REWORK_CATEGORY, L.PROCESSING_STATUS, T.TIME_REMAINING, SLS.CHECK_RCP ";

            String baseSql = buildBaseSqlByRuncard(condition, filedSql);

            StringBuilder contextFiledSql = new StringBuilder();
            contextFiledSql.append(" L.RECIPE_ID AS contxt_recipe_id,");
            contextFiledSql.append("(SELECT INSTANCE_RRN FROM NAMED_OBJECT T WHERE T.OBJECT = 'RECIPE' AND T.INSTANCE_ID = L.RECIPE_ID) AS contxt_recipe_rrn,");
            contextFiledSql.append("L.STAGE_ID as context_stage_id,");
            contextFiledSql.append("getflowstepdesc(L.PRODUCT_RRN, L.PROCESS_RRN, L.ROUTE_RRN, L.OPERATION_RRN, L.PROCESS_VERSION) as context_step_desc,");
            contextFiledSql.append("getflowseq(L.PROCESS_RRN, L.ROUTE_RRN, L.OPERATION_RRN, L.PROCESS_VERSION) as context_flow_seq,");
            contextFiledSql.append("ROUND((GETMINREMAINTIMEBYLOTRRN(L.LOT_RRN, '0') / 3600), 2) AS Q_TIME,");
            contextFiledSql.append("CONCAT(CONCAT(L.HOT_FLAG, '-'), L.PRIORITY) AS HOTFLAG_PRIORITY,O.MAX_QUEUE_TIME,'' AS ROUTE_DESC,L.WORK_AREA as operation_work_area,");
            contextFiledSql.append("LOT_RUNTIME.LOT_RUN_TIME AS LOT_RUN_TIME, REA.HOLD_REASON AS REASON, ");
            contextFiledSql.append("CASE WHEN LENGTH(L.ATTRIBUTE_DATA3) > 0 THEN 'Y' ELSE 'N' END AS PI_LOT, GETINSTANCEID(L.OPERATION_RRN) AS OPERATION_ID, ");
            contextFiledSql.append("GETINSTANCEID(L.RETICLE_RRN) AS RETICLE_ID, GETINSTANCEID(L.PRODUCT_RRN) AS PRODUCT_ID, GETINSTANCEID(L.PROCESS_RRN) AS PROCESS_ID, GETINSTANCEID(L.RECIPE_LOGICAL_RRN) AS LOGICAL_RECIPE_ID, ");
            contextFiledSql.append("RECIPE_DOC.DOCUMENT_FILE AS RECIPE_DOC_FILE, RECIPE_DOC.COMMENTS AS RECIPE_DOC_COMMENTS, ");
            contextFiledSql.append("getequipmentconstrainmapping(L.LOT_RRN, L.PRODUCT_RRN, L.PROCESS_RRN, L.ROUTE_RRN, L.OPERATION_RRN, L.ROUTE_SEQ, L.OPERATION_SEQ) AS EQPT_CONSTRAIN,");
            contextFiledSql.append("L.* ");

            String contextSql = "SELECT TT.* FROM (SELECT "
                    + contextFiledSql
                    + " FROM ("
                    + baseSql
                    + ") L, OPERATION O, OBJECT_DOCUMENT RECIPE_DOC,"
                    + " (SELECT MAX(TLOG.TRANS_START_TIMESTAMP) AS LOT_RUN_TIME, LOT_TRANS.LOT_RRN FROM TRANSACTION_LOG TLOG, (SELECT TRANS_RRN, LOT_RRN FROM LOT_TRANS_HISTORY WHERE TRANS_ID = 'MOVEIN') LOT_TRANS WHERE TLOG.TRANS_RRN IN (LOT_TRANS.TRANS_RRN) GROUP BY LOT_TRANS.LOT_RRN) LOT_RUNTIME,"
                    + " (SELECT TR.TRANS_RRN AS HOLD_RRN, TR.INSTANCE_RRN AS LOT_RRN, MAXMH.HOLD_USER_RRN, TR.RESPONSIBILITY AS HOLD_USER_ID, CASE WHEN LENGTH(SUBSTR(TR.REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1) - 1)) > 0 THEN SUBSTR(TR.REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1) - 1) ELSE 'SYSTEM' END AS HOLD_DEPT, SUBSTR(TR.REASON, INSTR(TR.REASON, 'Reason:', 1, 1) + 7, LENGTH(TR.REASON)) AS HOLD_REASON FROM TRANS_REASON TR, (SELECT M.TRANS_RRN, M.HOLD_BY AS HOLD_USER_RRN, M.INSTANCE_RRN AS LOT_RRN FROM MULTIPLE_HOLD M, (SELECT MAX(MH.HOLD_TIMESTAMP) AS HOLD_DATE, MAX(MH.SEQUENCE_NUMBER) AS HOLD_SEQ, MH.INSTANCE_RRN AS LOT_RRN FROM MULTIPLE_HOLD MH WHERE MH.OBJECT = 'LOT' GROUP BY MH.INSTANCE_RRN) MAXM WHERE M.HOLD_TIMESTAMP = MAXM.HOLD_DATE AND M.SEQUENCE_NUMBER = MAXM.HOLD_SEQ AND M.INSTANCE_RRN = MAXM.LOT_RRN) MAXMH WHERE TR.TRANS_RRN = MAXMH.TRANS_RRN AND TR.INSTANCE_RRN = MAXMH.LOT_RRN) REA"
                    + " WHERE O.OPERATION_RRN(+) = L.OPERATION_RRN AND L.LOT_RRN = LOT_RUNTIME.LOT_RRN(+) AND L.LOT_RRN = REA.LOT_RRN(+)AND L.RECIPE_LOGICAL_RRN = RECIPE_DOC.INSTANCE_RRN(+)) TT";

            String constrainWhereSql = "";
            String objectRrn = MapUtils.getString(condition, "objectRrn");
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");
                constrainWhereSql += " WHERE (TT.EQPT_CONSTRAIN IS NULL OR (TT.EQPT_CONSTRAIN IS NOT NULL AND (";
                String[] rrns = StringUtils.split(objectRrn, ",");
                int rrnCount = 0;
                for (String rrn : rrns) {
                    rrnCount += 1;
                    if (rrnCount == 1) {
                        constrainWhereSql += " INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    } else {
                        constrainWhereSql += " OR INSTR(TT.EQPT_CONSTRAIN, getinstanceid(" + rrn + ")) > 0 ";
                    }
                }
                constrainWhereSql += ")))";
            }

            contextSql += (constrainWhereSql + " ORDER BY TT.CREATE_CATEGORY DESC, TT.LOT_STATUS, TT.LOT_ID, TT.TIME_REMAINING, TT.PRIORITY, TT.QUEUE_TIMESTAMP, TT.HOT_FLAG DESC ");

            return jdbcTemplate.query(contextSql,  new OperationPanelLotMapper());
        }

        public String buildBaseSqlByRuncard(Map<String, Object> condition, String filedSql) {
            String tableTime = "SELECT LOT_RRN, DECODE(SIGN(TIME_REMAINING), -1, 0, TIME_REMAINING) AS TIME_REMAINING "
                    + " FROM (select lot_rrn,min((ROUND(TO_NUMBER(TO_DATE(TO_CHAR(T0.START_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * 86400)) + T0.TIMELIMIT) "
                    + " AS TIME_REMAINING from TIMELIMIT_STATUS t0 where t0.timelimit >= 0 and t0.status = 'NORMAL' group by lot_rrn)";

            String sql = "SELECT "
                    + filedSql
                    + " FROM SRC_LOT_STORE SL, SRC_LOT_SPECIAL_STEP SLS, LOT L, LOT_EXT LE, LOT_RUN_CARD_STEP RCS, LOT ML, BATCH_LOT_STORE BL, ("
                    + tableTime + ") T ";

            StringBuffer whereSql = new StringBuffer(
                    " WHERE L.LOT_RRN = SL.LOT_RRN AND ML.LOT_ID = SL.MAIN_LOT_ID AND L.LOT_RRN = LE.LOT_RRN ");
            whereSql.append(" AND SL.RUNCARD_RRN = SLS.RUNCARD_RRN AND SL.STEP_SEQUENCE = SLS.STEP_SEQUENCE AND SL.LOT_SPECIAL_STEP_RRN = SLS.LOT_SPECIAL_STEP_RRN ");
            whereSql.append(" AND SL.RUNCARD_RRN = RCS.RUN_CARD_RRN AND SLS.STEP_NO = RCS.STEP_SERIAL_NO AND L.LOT_RRN = BL.LOT_RRN(+) AND L.LOT_RRN = T.LOT_RRN(+) ");
            String objectRrn = MapUtils.getString(condition, "objectRrn");
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");
                whereSql.append(" AND (SLS.EQPT_RRN IN ("
                                        + objectRrn
                                        + ") OR SLS.EQPT_GROUP_RRN IN (SELECT DISTINCT TO_RRN FROM RELATION T WHERE T.LINK_TYPE = 'ENTITY_TO_ENTITY_GROUP' AND T.FROM_RRN IN ("
                                        + objectRrn + ")))");
            }

            if (MapUtils.getObject(condition, "lotStatus") != null) {
                ArrayList statusList = (ArrayList) condition.get("lotStatus");
                whereSql.append(" AND (L.LOT_STATUS='" + statusList.remove(0) + "'");
                for (Iterator iterator = statusList.iterator(); iterator.hasNext();) {
                    String status = (String) iterator.next();
                    whereSql.append(" OR L.LOT_STATUS='" + status + "'");
                }
                whereSql.append(" ) ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "lotCategory", StringUtils.EMPTY))) {
                whereSql.append(" AND L.CREATE_CATEGORY IN (" + MapUtils.getString(condition, "lotCategory") + ") ");
            }

            Object stationRrn = MapUtils.getObject(condition, "stationRrn");
            if (stationRrn != null) {
                whereSql.append(" AND ((RCS.OPERATION_RRN IN (SELECT DISTINCT TO_RRN FROM RELATION T WHERE T.LINK_TYPE='STATION_TO_OPERATION' AND T.FROM_RRN="
                                        + stationRrn + "))) ");
            }

            if (MapUtils.getBoolean(condition, "jobGridFlag") != null && MapUtils.getBooleanValue(condition, "jobGridFlag")
                    && objectRrn != null) {
                whereSql.append(" AND L.JOB_RRN=JOB.JOB_RRN AND JOB.EQPT_RRN IN (" + objectRrn + ")");
                sql = sql + "," + DataBaseNames.JOB;
            }

            String sapphire = MapUtils.getString(condition, "Sapphire");
            // 查询Bonding批次
            if (sapphire != null && sapphire.equalsIgnoreCase("isSapphire")) {
                whereSql.append(" AND RCS.OPERATION_RRN IN (SELECT OO.OPERATION_RRN FROM OPERATION OO,NAMED_OBJECT N1,NAMED_OBJECT N2 "
                                        + " WHERE OO.MVIN_WFL_RRN=N1.INSTANCE_RRN AND OO.MVOU_WFL_RRN=N2.INSTANCE_RRN "
                                        + " AND N1.INSTANCE_ID='MOVEIN_BONDED_STD' AND N2.INSTANCE_ID='MOVEOUT_AUTOBONDED_STD')");
            }
            // 查询非Bonding批次
            if (sapphire != null && sapphire.equalsIgnoreCase("notSapphire")) {
                // whereSql.append(" AND LOT.PRODUCT_RRN NOT IN (SELECT INSTANCE_RRN FROM NAMED_OBJECT WHERE OBJECT_TYPE="+MiscUtils.parseSQL(ObjectList.SAPPHIRE)+")");
            }
            String lotId = MapUtils.getString(condition, "lotId");
            // 派工页面查询条件
            if (StringUtils.isNotBlank(lotId)) {
                whereSql.append(" AND SL.SHOW_LOT_ID LIKE '" + lotId + "'");
            }
            String proId = MapUtils.getString(condition, "proId");
            if (StringUtils.isNotBlank(proId)) {
                whereSql.append(" AND ML.PRODUCT_RRN IN ( SELECT INSTANCE_RRN FROM NAMED_OBJECT WHERE INSTANCE_ID IN "
                                        + "(SELECT INSTANCE_ID FROM NAMED_OBJECT where INSTANCE_ID LIKE '" + proId + "'))");
            }

            sql = sql + whereSql.toString();
            return sql;
        }

        private String getTableLotSql(String objectRrn) {
            String sql = "";
            if (objectRrn != null) {
                objectRrn = objectRrn.replace("#$#", ",");

                String tableLot = "(select l.lot_rrn from lot L,(select context_key4  as operation_rrn,context_key5  as " +
                        "lot_rrn,result_value1 as entity_rrn" + "  from context_value c where status = 'ACTIVE' " +
                        "AND context_rrn in " + "     (select instance_rrn from named_object where instance_id = " +
                        "'LOT_SPECIAL_EQUIP_CONTEXT')) T " + "      WHERE L.LOT_RRN = T.lot_rrn(+) AND L" +
                        ".OPERATION_RRN = T.OPERATION_RRN AND T" + ".ENTITY_RRN in ('" + objectRrn.replace(",", "','") +
                        "') " + " union " + " select distinct lot_rrn  from ( select l.lot_rrn from(select DISTINCT " +
                        " process_rrn,process_version,route_rrn, operation_rrn,substr(t.EQUIPMENT_GROUP_rrns, " +
                        "instr(t.EQUIPMENT_GROUP_rrns, ',', 1, c.lv) + 1,instr(t.EQUIPMENT_GROUP_rrns, ',', 1, c.lv + 1)-" +
                        " (instr(t.EQUIPMENT_GROUP_rrns, ',', 1, c.lv) + 1)) AS EQUIPMENT_GROUP_rrn from (select " +
                        "process_rrn,process_version,route_rrn, operation_rrn,',' || EQUIPMENT_GROUP_rrns || ',' AS " +
                        "EQUIPMENT_GROUP_rrns,length(EQUIPMENT_GROUP_rrns || ',') - nvl(length(REPLACE" +
                        "(EQUIPMENT_GROUP_rrns, ',')), 0) AS cnt FROM PROCESS_SPEC_ITEM_ACTIVE) t,(select LEVEL lv " +
                        " from dual CONNECT BY LEVEL <= 512) c where c.lv <= t.cnt) gp, lot l where " +
                        "gp.EQUIPMENT_GROUP_rrn in  (SELECT DISTINCT to_rrn FROM relation t WHERE t.link_type " +
                        "= 'ENTITY_TO_ENTITY_GROUP' AND t.from_rrn IN ( " + objectRrn + " ))" +
                        " and l.process_rrn = gp.process_rrn and l.process_version = gp.process_version " +
                        " and l.operation_rrn = gp.operation_rrn and gp.route_rrn = REGEXP_SUBSTR(L.PROCESS_STEP_VERSION," +
                        " '[^,]+', instr(L.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1) ) where to_Char(LOT_RRN) NOT IN " +
                        " (select to_char(l.LOT_RRN) from lot l, " + "(select context_key4 as operation_rrn, " +
                        "context_key5 as lot_rrn " + "       from " +
                        "context_value c where status = 'ACTIVE' AND context_rrn in " + "        (select " +
                        "instance_rrn from named_object where instance_id = " + "'LOT_SPECIAL_EQUIP_CONTEXT')) T " +
                        "    WHERE to_char(L.LOT_RRN) = T.lot_rrn(+) AND to_char(L.OPERATION_RRN) = T" + ".OPERATION_RRN)" +
                        ") talot";

                sql = tableLot;

            }
            return sql;
        }

        private String getTableLotWhereSql(String objectRrn) {
            String whereSql = "";
            if (objectRrn != null) {
                whereSql = " AND LOT.LOT_RRN = talot.lot_rrn";
            }
            return whereSql;
        }

    }