<?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="numbering">

<sql id="key">
SELECT LPAD(CAST(
     CASE 
     WHEN (SELECT MAX(${TABLE_KEY}) + 1 FROM ${TABLE_NAME}) IS NULL OR 
          (SELECT MAX(${TABLE_KEY}) + 1 FROM ${TABLE_NAME}) = '' 
     THEN 1 
     ELSE (SELECT MAX(${TABLE_KEY}) + 1 FROM ${TABLE_NAME}) 
     END
     AS INT), ${pad}, '0')
AS NEW_ID
FROM DUAL
</sql>

<sql id="keyByYear">
SELECT CONCAT(<include refid="utility.currentYear" />, LPAD(CAST(
     CASE 
     WHEN (SELECT MAX(SUBSTRING(${TABLE_KEY}, 4+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(<include refid="utility.currentYear" />, '%')) IS NULL OR 
          (SELECT MAX(SUBSTRING(${TABLE_KEY}, 4+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(<include refid="utility.currentYear" />, '%')) = '' 
     THEN 1 
     ELSE (SELECT MAX(SUBSTRING(${TABLE_KEY}, 4+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(<include refid="utility.currentYear" />, '%')) 
     END
     AS INT), ${pad}-4, '0'))
AS NEW_ID
FROM DUAL
</sql>

<sql id="keyBySggAndYear">
SELECT CONCAT(${sggCd}, <include refid="utility.currentYear" />, LPAD(CAST(
     CASE 
     WHEN (SELECT MAX(SUBSTRING(${TABLE_KEY}, 9+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(${sggCd}, <include refid="utility.currentYear" />, '%')) IS NULL OR 
          (SELECT MAX(SUBSTRING(${TABLE_KEY}, 9+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(${sggCd}, <include refid="utility.currentYear" />, '%')) = '' 
     THEN 1 
     ELSE (SELECT MAX(SUBSTRING(${TABLE_KEY}, 9+1)) + 1 FROM ${TABLE_NAME} 
            WHERE ${TABLE_KEY} LIKE CONCAT(${sggCd}, <include refid="utility.currentYear" />, '%')) 
     END
     AS INT), ${pad}-9, '0'))
AS NEW_ID
FROM DUAL
</sql>

</mapper>