# OcapQueryDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.MiscUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.CollectionUtils;
    import com.mycim.server.wip.dao.OcapQueryDAO;
    import com.mycim.server.wip.dao.mapper.OcapCardRowMapper;
    import com.mycim.server.wip.dao.mapper.OcapStepOwnerRowMapper;
    import com.mycim.server.wip.dao.mapper.OcapTransDataRowMapper;
    import com.mycim.server.wip.dao.mapper.OcapTransLogRowMapper;
    import com.mycim.valueobject.consts.OcapTypeEnum;
    import com.mycim.valueobject.ocap.OcapCard;
    import com.mycim.valueobject.ocap.OcapStepOwner;
    import com.mycim.valueobject.ocap.OcapTransData;
    import com.mycim.valueobject.ocap.OcapTransLog;
    import com.mycim.valueobject.ocap.dto.OcapCardDTO;
    import com.mycim.valueobject.ocap.dto.OcapChartInfoDTO;
    import com.mycim.valueobject.ocap.dto.OcapQueryConditionDTO;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.StringJoiner;

    /**
     * @author songpy
     * @version 1.0.0
     * @date 2021/1/21
     **/
    @Repository
    public class OcapQueryDAOImpl implements OcapQueryDAO {
        final JdbcTemplate jdbcTemplate;

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

        @Override
        public List<String> getWorkflowIds() {
            String sql = "select distinct WORKFLOW_ID from OCAP_CARD ";
            return jdbcTemplate.query(sql, String.class);
        }

        @Override
        public Page getOcapCardPage(Page page, OcapQueryConditionDTO queryConditionDTO) {
            List<Object> paramList = new ArrayList<>();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT OCAP_RRN,OCAP_ID,FACILITY_RRN,OCAP_TYPE,STATUS,CREATE_USER_RRN,CREATE_USER_ID,CREATE_TIME,");
            sql.append("LOT_RRN,LOT_ID,LOT_STEP_SEQUENCE,PARAMETER_SET_RRN,PARAMETER_SET_ID,PARAMETER_SET_VERSION,");
            sql.append("PROCESS_EQUIPMENT_RRN,PROCESS_EQUIPMENT_ID,MEASURING_EQUIPMENT_RRN,MEASURING_EQUIPMENT_ID,");
            sql.append("PROCESS_STATION_RRN,PROCESS_STATION_ID,MEASURING_STATION_RRN,MEASURING_STATION_ID,AREA_ID,");
            sql.append("STAGE_ID,CHART_ID,CHART_NAME,START_TIME,CLOSE_TIME,DCOL_RRN,WORKFLOW_ID,WORKFLOW_RRN,");
            sql.append("EXECUTION_RRN,OCAP_BATCH_ID,OCAP_MAIN_LOT_ID,OCAP_MAIN_LOT_RRN,SIGNER_RRN,");
            sql.append("(select listagg(STEP_OWNER_ID,',') WITHIN GROUP ( order by STEP_OWNER_ID) SIGNER_ID from  " +
                               "OCAP_STEP_OWNER step where step.OCAP_RRN = OCAP.OCAP_RRN) SIGNER_ID,");
            sql.append("STEP_OWNER_TYPE,STEP_OWNER_ID,STEP_OWNER_RRN,CURRENT_STEP_ID ");
            sql.append("FROM (");

            // signerId
            sql.append("SELECT INSTANCE_RRN AS user_rrn,CASE WHEN (");
            sql.append("SELECT COUNT(*) FROM RELATION WHERE LINK_TYPE='USER_TO_ROLE' AND to_rrn=(");
            sql.append("SELECT instance_rrn FROM named_object WHERE instance_id='ROLE_ADMIN' AND OBJECT='ROLE') ");
            sql.append("AND FROM_RRN=INSTANCE_RRN)> 0 THEN 'Y' ELSE 'N' END AS ADMIN_ROLE FROM NAMED_OBJECT WHERE ");
            sql.append("OBJECT='USER' AND INSTANCE_ID=? ");
            paramList.add(StringUtils.isBlank(queryConditionDTO.getSignerId()) ? "ADMIN" : queryConditionDTO.getSignerId());

            sql.append(") u,OCAP_CARD OCAP ");
            // where
            sql.append("WHERE ocap.FACILITY_RRN= ? ");
            paramList.add(queryConditionDTO.getFacilityRrn());

            sql.append(
                    "AND (ocap.OCAP_MAIN_LOT_RRN IS NULL OR ocap.OCAP_MAIN_LOT_RRN = 0 OR ocap.OCAP_MAIN_LOT_RRN = ocap" +
                            ".LOT_RRN) ");

            if (StringUtils.isNotEmpty(queryConditionDTO.getOcapId())) {
                sql.append(" AND ocap.OCAP_ID like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getOcapId()));
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getOcapType())) {
                sql.append(" AND ocap.OCAP_TYPE = ? ");
                paramList.add(queryConditionDTO.getOcapType());
            }

            if (CollectionUtils.isNotEmpty(queryConditionDTO.getStatuses())) {
                StringJoiner condition = new StringJoiner(",", "(", ")");
                for (String s : queryConditionDTO.getStatuses()) {
                    condition.add("?");
                    paramList.add(s);
                }
                sql.append("AND ocap.STATUS in " + condition.toString());
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getLotId())) {
                sql.append(" AND ocap.LOT_ID like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getLotId()));
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getProcessEquipmentId())) {
                sql.append(" AND ocap.PROCESS_EQUIPMENT_ID like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getProcessEquipmentId()));
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getMeasuringEquipmentId())) {
                sql.append(" AND ocap.MEASURING_EQUIPMENT_ID like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getMeasuringEquipmentId()));
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getParameterSetId())) {
                sql.append(" AND ocap.PARAMETER_SET_ID like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getParameterSetId()));
            }

            if (StringUtils.isNotEmpty(queryConditionDTO.getChartName())) {
                sql.append(" AND ocap.CHART_NAME like ? ");
                paramList.add(MiscUtils.parseToFuzzySQL(queryConditionDTO.getChartName()));
            }

            if (CollectionUtils.isNotEmpty(queryConditionDTO.getAreaIds())) {
                StringJoiner condition = new StringJoiner(",", "(", ")");
                for (String s : queryConditionDTO.getAreaIds()) {
                    condition.add("?");
                    paramList.add(s);
                }
                sql.append("AND ocap.AREA_ID in " + condition.toString());
            }

            if (CollectionUtils.isNotEmpty(queryConditionDTO.getWorkflowIds())) {
                StringJoiner condition = new StringJoiner(",", "(", ")");
                for (String s : queryConditionDTO.getWorkflowIds()) {
                    condition.add("?");
                    paramList.add(s);
                }
                sql.append("AND ocap.WORKFLOW_ID in " + condition.toString());
            }

            if (queryConditionDTO.getStartTime() != null) {
                sql.append(" AND ocap.START_TIME >= ? ");
                paramList.add(queryConditionDTO.getStartTime());
            }

            if (queryConditionDTO.getEndTime() != null) {
                sql.append(" AND ocap.START_TIME <= ? ");
                paramList.add(queryConditionDTO.getEndTime());
            }

            sql.append("AND (" + buildStepOwnerSql() + ")='Y' ORDER BY ocap.OCAP_ID ");
            sql.append("DESC,ocap.LOT_ID DESC");

            return jdbcTemplate.queryForPage(page, sql.toString(), paramList.toArray(), new OcapCardRowMapper());
        }

        @Override
        public List<String> getOcapIds(Long equipmentRrn, String chartName, String planType, String status) {
            StringBuilder sql = new StringBuilder("select ");
            sql.append(" ocap_id ");
            sql.append(" from OCAP_CARD ");
            sql.append(" where PROCESS_EQUIPMENT_RRN = ? AND CHART_NAME = ? ");

            List<Object> args = new ArrayList<>();
            args.add(equipmentRrn);
            args.add(chartName);

            if (StringUtils.isNotEmpty(planType)) {
                sql.append(" AND ocap_type = ? ");
                args.add(planType);
            }
            if (StringUtils.isNotEmpty(status)) {
                sql.append(" AND STATUS = ? ");
                args.add(status);
            }
            sql.append(" ORDER BY ocap_id DESC, LOT_ID DESC ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), String.class);
        }

        @Override
        public String getMaxOcapIdOfToday(String planIdPattern) {
            String sql = "SELECT MAX(OCAP_ID) AS MAX_OCAP_ID FROM OCAP_CARD WHERE OCAP_ID LIKE ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{planIdPattern}, String.class);
        }

        @Override
        public OcapCard getOcapCardByUniqueKey(Long facilityRrn, String ocapId, String lotId) {
            List<Object> args = new ArrayList<>();
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" OCAP_RRN, OCAP_ID, FACILITY_RRN, ocap_type, STATUS, ");
            sql.append(" CREATE_USER_RRN, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" LOT_RRN, LOT_ID, LOT_STEP_SEQUENCE, PARAMETER_SET_RRN, PARAMETER_SET_ID, " +
                               "PARAMETER_SET_VERSION, ");
            sql.append(" PROCESS_EQUIPMENT_RRN, PROCESS_EQUIPMENT_ID, ");
            sql.append(" MEASURING_EQUIPMENT_RRN, MEASURING_EQUIPMENT_ID, ");
            sql.append(" PROCESS_STATION_RRN, PROCESS_STATION_ID, ");
            sql.append(" MEASURING_STATION_RRN, MEASURING_STATION_ID, ");
            sql.append(" AREA_ID, STAGE_ID, CHART_ID, CHART_NAME, START_TIME, CLOSE_TIME, DCOL_RRN, WORKFLOW_ID, " +
                               "WORKFLOW_RRN,EXECUTION_RRN,OCAP_BATCH_ID, OCAP_MAIN_LOT_ID, OCAP_MAIN_LOT_RRN, " +
                               "SIGNER_RRN,SIGNER_ID,STEP_OWNER_TYPE,STEP_OWNER_ID,STEP_OWNER_RRN,CURRENT_STEP_ID");
            sql.append(" from OCAP_CARD ");
            sql.append(" where OCAP_ID = ? and facility_rrn = ? ");

            args.add(ocapId);
            args.add(facilityRrn);
            if (StringUtils.isNotBlank(lotId)) {
                sql.append(" and lot_id = ? ");
                args.add(lotId);
            }

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args.toArray(), new OcapCardRowMapper());
        }

        @Override
        public List<OcapCard> getOcapCardsByBatchId(Long facilityRrn, String ocapBatchId) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" OCAP_RRN, OCAP_ID, FACILITY_RRN, ocap_type, STATUS, ");
            sql.append(" CREATE_USER_RRN, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" LOT_RRN, LOT_ID, LOT_STEP_SEQUENCE, PARAMETER_SET_RRN, PARAMETER_SET_ID, " +
                               "PARAMETER_SET_VERSION, ");
            sql.append(" PROCESS_EQUIPMENT_RRN, PROCESS_EQUIPMENT_ID, ");
            sql.append(" MEASURING_EQUIPMENT_RRN, MEASURING_EQUIPMENT_ID, ");
            sql.append(" PROCESS_STATION_RRN, PROCESS_STATION_ID, ");
            sql.append(" MEASURING_STATION_RRN, MEASURING_STATION_ID, ");
            sql.append(" AREA_ID, STAGE_ID, CHART_ID, CHART_NAME, START_TIME, CLOSE_TIME, DCOL_RRN, WORKFLOW_ID, " +
                               "WORKFLOW_RRN,EXECUTION_RRN,OCAP_BATCH_ID, OCAP_MAIN_LOT_ID, OCAP_MAIN_LOT_RRN, " +
                               "SIGNER_RRN,SIGNER_ID,STEP_OWNER_TYPE,STEP_OWNER_ID,STEP_OWNER_RRN,CURRENT_STEP_ID");
            sql.append(" FROM OCAP_CARD ");
            sql.append(" WHERE FACILITY_RRN = ? AND ocap_type = ? AND  OCAP_BATCH_ID = ? ");

            return jdbcTemplate.query(sql.toString(),
                                      new Object[]{facilityRrn, OcapTypeEnum.INLINE.toString(), ocapBatchId},
                                      new OcapCardRowMapper());
        }

        @Override
        public List<String> getOcapIdsByProcessEquipment(Long equipmentRrn, String planType, String status) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" ocap_id ");
            sql.append(" from OCAP_CARD ");
            sql.append(" where PROCESS_EQUIPMENT_RRN = ? ");

            List<Object> args = new ArrayList<>();

            args.add(equipmentRrn);

            if (StringUtils.isNotEmpty(planType)) {
                sql.append(" AND ocap_type = ? ");
                args.add(planType);
            }

            if (StringUtils.isNotEmpty(status)) {
                sql.append(" AND STATUS = ? ");
                args.add(status);
            }

            sql.append(" ORDER BY ocap_id DESC, LOT_ID DESC ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), String.class);
        }

        @Override
        public List<String> getOcapIds(Long lotRrn, String planType, String status) {
            StringBuilder sql = new StringBuilder("select ");
            sql.append(" OCAP_ID ");
            sql.append(" from OCAP_CARD ");
            sql.append(" where LOT_RRN = ? ");

            List<Object> args = new ArrayList<>();

            args.add(lotRrn);

            if (StringUtils.isNotEmpty(planType)) {
                sql.append(" AND ocap_type = ? ");
                args.add(planType);
            }

            if (StringUtils.isNotEmpty(status)) {
                sql.append(" AND STATUS = ? ");
                args.add(status);
            }

            return jdbcTemplate.query(sql.toString(), args.toArray(), String.class);
        }

        @Override
        public OcapCard getOcapCardByPrimaryKey(Long ocapRrn) {
            StringBuilder sql = new StringBuilder("select ");
            sql.append(" OCAP_RRN, OCAP_ID, FACILITY_RRN, ocap_type, STATUS, ");
            sql.append(" CREATE_USER_RRN, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" LOT_RRN, LOT_ID, LOT_STEP_SEQUENCE, PARAMETER_SET_RRN, PARAMETER_SET_ID, " +
                               "PARAMETER_SET_VERSION, ");
            sql.append(" PROCESS_EQUIPMENT_RRN, PROCESS_EQUIPMENT_ID, ");
            sql.append(" MEASURING_EQUIPMENT_RRN, MEASURING_EQUIPMENT_ID, ");
            sql.append(" PROCESS_STATION_RRN, PROCESS_STATION_ID, ");
            sql.append(" MEASURING_STATION_RRN, MEASURING_STATION_ID, ");
            sql.append(" AREA_ID, STAGE_ID, CHART_ID, CHART_NAME, START_TIME, CLOSE_TIME, DCOL_RRN, WORKFLOW_ID," +
                               "WORKFLOW_RRN,EXECUTION_RRN,OCAP_BATCH_ID, OCAP_MAIN_LOT_ID, OCAP_MAIN_LOT_RRN, " +
                               "SIGNER_RRN,SIGNER_ID,STEP_OWNER_TYPE,STEP_OWNER_ID,STEP_OWNER_RRN,CURRENT_STEP_ID," +
                               "CURRENT_STEP_ID ");
            sql.append(" from OCAP_CARD ");
            sql.append(" where OCAP_RRN = ? ");

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{ocapRrn}, new OcapCardRowMapper());
        }

        @Override
        public List<OcapChartInfoDTO> queryOcapChartInfoList(OcapChartInfoDTO dto) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT ocap_rrn, JOB_ID, JOB_NAME, URL FROM OCAP_CARD_CHART_INFO WHERE ocap_rrn = ?");
            ArrayList<Object> param = new ArrayList<Object>();
            param.add(dto.getOcapRrn());
            if (StringUtils.isNotBlank(dto.getJobId())) {
                String jobId = dto.getJobId();
                String[] jobIds = StringUtils.split(jobId, ",");
                sql.append(" AND JOB_ID in ( ");
                for (String id : jobIds) {
                    sql.append("'" + id + "',");
                }
                sql.deleteCharAt(sql.lastIndexOf(","));
                sql.append(" )");
            }
            if (StringUtils.isNotBlank(dto.getJobName())) {
                String jobName = dto.getJobName();
                String[] jobNames = StringUtils.split(jobName, ",");
                sql.append(" AND JOB_NAME in ( ");
                for (String name : jobNames) {
                    sql.append("'" + name + "',");
                }
                sql.deleteCharAt(sql.lastIndexOf(","));
                sql.append(" )");
            }
            if (StringUtils.isNotBlank(dto.getUrl())) {
                sql.append(" AND URL = ?");
                param.add(dto.getUrl());
            }
            return jdbcTemplate.query(sql.toString(), param.toArray(), new RowMapper<OcapChartInfoDTO>() {
                @Override
                public OcapChartInfoDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                    OcapChartInfoDTO ocapInfo = new OcapChartInfoDTO();
                    ocapInfo.setJobId(rs.getString("JOB_ID"));
                    ocapInfo.setJobName(rs.getString("JOB_NAME"));
                    ocapInfo.setOcapRrn(rs.getLong("ocap_rrn"));
                    ocapInfo.setUrl(rs.getString("URL"));
                    return ocapInfo;
                }
            });

        }

        @Override
        public OcapTransData getTransDataByUniqueKey(Long ocapRrn, String dataName) {
            StringBuilder sql = new StringBuilder("select ");
            sql.append(" DATA_RRN, DATA_NAME, DATA_VALUE, OCAP_RRN ");
            sql.append(" from OCAP_TRANS_DATA ");
            sql.append(" where DATA_NAME = ? AND OCAP_RRN = ? ");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{dataName, ocapRrn},
                                                       new OcapTransDataRowMapper());
        }

        @Override
        public List<OcapTransData> getTransDatasByOcapRrn(Long ocapRrn) {
            StringBuilder sql = new StringBuilder("select ");
            sql.append(" DATA_RRN, DATA_NAME, DATA_VALUE, OCAP_RRN ");
            sql.append(" from OCAP_TRANS_DATA ");
            sql.append(" where OCAP_RRN = ? ");
            return jdbcTemplate.query(sql.toString(), new Object[]{ocapRrn}, new OcapTransDataRowMapper());
        }

        @Override
        public List<OcapTransLog> getTransLogsByOcapRrn(Long ocapRrn) {
            String sql = "SELECT TRANS_RRN,TRANS_SEQ,TRANS_ID,TRANS_TIME,TRANS_COMMENT,OCAP_RRN,OCAP_ID,FACILITY_RRN," +
                    "STATUS,ACTION_ID,SIGNER_RRN,SIGNER_ID FROM OCAP_TRANS_LOG where OCAP_RRN = ? order by TRANS_TIME desc";
            return jdbcTemplate.query(sql, new Object[]{ocapRrn}, new OcapTransLogRowMapper());
        }

        @Override
        public List<OcapStepOwner> getOcapStepOwners(OcapCardDTO ocapCardDTO) {
            String sql = "select STEP_OWNER_ID,STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP_ID = ? and OCAP_RRN = ? and" +
                    " step_id = ? ";

            return jdbcTemplate.query(sql, new Object[]{ocapCardDTO.getOcapId(), ocapCardDTO.getOcapRrn(),
                                                        ocapCardDTO.getCurrentStepId()}, new OcapStepOwnerRowMapper());
        }

        private String buildStepOwnerSql() {
            StringBuilder sql = new StringBuilder("CASE ");

            sql.append("WHEN ADMIN_ROLE = 'Y' THEN 'Y' ");
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE is null then 'Y' ");

            //USER
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'USER' ");
            sql.append("THEN CASE WHEN U.USER_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN) ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            //USER_GROUP
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'USER_GROUP' ");
            sql.append("THEN CASE WHEN (SELECT COUNT(*) FROM RELATION WHERE LINK_TYPE = 'USER_TO_USER_GROUP' ");
            sql.append("AND FROM_RRN = U.USER_RRN AND TO_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN)) > 0 ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            //ROLE
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'ROLE' ");
            sql.append("THEN CASE WHEN (SELECT COUNT(*) FROM RELATION WHERE LINK_TYPE = 'USER_TO_ROLE' ");
            sql.append("AND FROM_RRN = U.USER_RRN AND TO_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN)) > 0 ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            //CREATOR
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'CREATOR' ");
            sql.append("THEN CASE WHEN U.USER_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN) ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            //DYNAMIC_SIGNER
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'DYNAMIC_SIGNER' ");
            sql.append("THEN CASE WHEN U.USER_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN) ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            //DYNAMIC_STATION_USERGROUP
            sql.append("WHEN ADMIN_ROLE <> 'Y' AND STEP_OWNER_TYPE = 'DYNAMIC_STATION_USERGROUP' ");
            sql.append("THEN CASE WHEN (SELECT COUNT(*) FROM RELATION WHERE LINK_TYPE = 'USER_TO_USER_GROUP' ");
            sql.append("AND FROM_RRN = U.USER_RRN AND TO_RRN in ");
            sql.append("(select STEP_OWNER_RRN from OCAP_STEP_OWNER where OCAP.OCAP_RRN = OCAP_STEP_OWNER.OCAP_RRN)) > 0 ");
            sql.append("THEN 'Y' ELSE 'N' END ");

            sql.append("ELSE 'N' END ");
            return sql.toString();
        }

    }