<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cokr.xit.fims.crdn.dao.ImportMapper">

<select id="selectSggList">/* 기관, 부서별 시군구 코드 조회(importMapper.selectSggList) */
SELECT CASE WHEN A.SGG_CD = B.SGG_CD THEN A.SGG_CD ELSE 'DIFF' END SGG_CD
     , INST_CD
     , DEPT_CD
     , A.SGG_CD A_SGG
     , B.SGG_CD B_SGG
  FROM (SELECT SGG_CD, INST_CD
          FROM TB_SGG
         WHERE INST_CD IN (<foreach collection="institutes" item="institute" separator=",">#{institute}</foreach>)
       ) A
  LEFT OUTER JOIN (SELECT SGG_CD, DEPT_CD
          FROM TB_DEPT
         WHERE DEPT_CD IN (<foreach collection="depts" item="dept" separator=",">#{dept}</foreach>)
       ) B
    ON A.SGG_CD = B.SGG_CD</select>

<select id="selectViolationCodes" parameterType="map" resultType="dataobject">/* importMapper.selectViolationCodes(위반내용 관련 코드 조회) */
SELECT CASE WHEN VLTN LIKE CONCAT('%', '전용차로', '%') THEN 'BPV'
            WHEN VLTN LIKE CONCAT('%', '장애인', '%')
             AND VLTN NOT LIKE CONCAT('%', '장애인보호구역', '%') THEN 'DPV'
            WHEN VLTN LIKE CONCAT('%', '전기차', '%')
              OR VLTN LIKE CONCAT('%', '친환경차', '%') THEN 'ECA'
            WHEN VLTN LIKE CONCAT('%', '밤샘', '%') THEN 'PES'
            ELSE 'PVS'
       END TASK_CD <!-- 업무구분(FIM054) -->
     , CASE WHEN VLTN LIKE CONCAT('%', '방해', '%') THEN '03'
            WHEN VLTN LIKE CONCAT('%', '표지', '%') THEN '05'
            ELSE '01'
       END VLTN_CD <!-- 위반코드 -->
  FROM (SELECT #{violation} VLTN
       ) A</select>

<select id="selectCarMdlCd" resultType="dataobject">/* importMapper.selectCarMdlCd(차종별 과태료 차종 조회) */
SELECT '1'   VHCTY_ASORT_CODE, '11' FFNLG_CARMDL_CD UNION <!-- 승용 -->
SELECT '2'   VHCTY_ASORT_CODE, '12' FFNLG_CARMDL_CD UNION <!-- 승합 -->
SELECT '3-1' VHCTY_ASORT_CODE, '13' FFNLG_CARMDL_CD UNION <!-- 화물 -->
SELECT '3-2' VHCTY_ASORT_CODE, '14' FFNLG_CARMDL_CD UNION <!-- 화물 -->
SELECT '4'   VHCTY_ASORT_CODE, '15' FFNLG_CARMDL_CD  <!-- 특수 --></select>

</mapper>