# function 분리
## function 분리 이유
- GET_HR_TOTAL_ANNUAL_USED_COUNT
- GET_HR_TOTAL_ANNUAL_REQ_COUNT
- 위 두 개의 펑션 때문에 속도가 느리다.
- 펑션을 해당 쿼리에서 제거하고 서비스 레이어에서 조합하고 병렬 처리를 한다.
### function이 사용된 쿼리
```xml=
SELECT @rownum:=@rownum+1 AS RNUM
, (PAGE_COUNT - PAGE_OFFSET + 1 - @rownum) AS NUM_ASC
, (PAGE_OFFSET + @rownum) AS NUM_DESC
, NULL AS MANIPULATION_TYPE
, NULL AS CHECKBOX_SELECT
, NULL AS CHECKBOX_DELETE
, T.LEAVE_ANNUAL_ID
, T.EMPLOYEE_ID
, T.COMPANY_ORIGINAL_ID
, GET_COMMON_CODE_VALUE('ORG_COMPANY_CODE', T.COMPANY_ORIGINAL_ID, IFNULL('ko', 'KO'), T.RETIRE_YMD, NULL) AS COMPANY_ORIGINAL_ID_NAME /* 법인명 */
, T.ORGANIZATION_ID
, T.ORGANIZATION_NAME
, T.DUTY_CODE
, GET_COMMON_CODE_VALUE('PHM_DUTY_CD', T.DUTY_CODE, IFNULL('ko', 'KO'), NOW(), IFNULL(127515, '')) AS DUTY_NAME
, T.EMPLOYEE_NUMBER
, T.EMPLOYEE_NAME
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.HIRE_YMD, '%Y-%m-%d')
ELSE T.HIRE_YMD
END AS HIRE_YMD
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.GROUP_HIRE_YMD, '%Y-%m-%d')
ELSE T.GROUP_HIRE_YMD
END AS GROUP_YMD
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.RETIRE_YMD, '%Y-%m-%d')
ELSE T.RETIRE_YMD
END AS RETIRE_YMD
, T.STANDARD_YEAR
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.WORK_YMD, '%Y-%m-%d')
ELSE T.WORK_YMD
END AS WORK_YMD
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.START_YMD, '%Y-%m-%d')
ELSE T.START_YMD
END AS START_YMD
, CASE WHEN IFNULL(null, 'N') = 'Y' THEN DATE_FORMAT(T.END_YMD, '%Y-%m-%d')
ELSE T.END_YMD
END AS END_YMD
, T.CARRIED_FORWARD_DAYS
, CASE WHEN T.ANNUAL = 0 OR 15 > T.CREATE_DAYS_1
THEN T.CREATE_DAYS_1
ELSE 15
END AS CREATE_DAYS_1 -- 기본연차
, CASE WHEN T.ANNUAL = 0 OR 15 > T.CREATE_DAYS_1 THEN 0
ELSE T.CREATE_DAYS_1 - 15
END AS ADD_YEAR_NUM -- 가산연차
, T.CREATE_DAYS_2
, T.CREATE_START_YMD
, T.CREATE_END_YMD
, T.USED_NUM AS USE_DAYS -- 사용일수
, T.USE_CORRECTION_DAYS -- 사용조정
, T.REQ_NUM
, T.YEAR_NUM_CHANGE -- 이월 (연차보정)
, T.CREATE_DAYS_1 + T.YEAR_NUM_CHANGE + T.CARRIED_FORWARD_DAYS AS TOTAL_YEAR_NUM_1 -- 발생연차
, T.CREATE_DAYS_2 AS TOTAL_YEAR_NUM_2
, (T.CREATE_DAYS_1 + T.YEAR_NUM_CHANGE + T.CARRIED_FORWARD_DAYS) - T.USED_NUM - T.USE_CORRECTION_DAYS AS REMAINDER_DAYS_1 -- 잔여일수
, T.CREATE_DAYS_2 - T.USED_NUM - T.USE_CORRECTION_DAYS AS REMAINDER_DAYS_2
, (T.CREATE_DAYS_1 + T.YEAR_NUM_CHANGE + T.CARRIED_FORWARD_DAYS) - T.USED_NUM - T.USE_CORRECTION_DAYS - T.REQ_NUM AS POSSIBLE_DAYS_1 -- 사용가능일수
, T.CREATE_DAYS_2 - T.USED_NUM - T.USE_CORRECTION_DAYS - T.REQ_NUM AS POSSIBLE_DAYS_2
, T.HIRE_SERVICE_DAYS
, T.HIRE_SERVICE_MONTHS
, T.HIRE_SERVICE_YEARS
, CASE WHEN T.ANNUAL = 0 THEN DATE_FORMAT(T.GROUP_HIRE_YMD, '%Y-%m-%d')
ELSE STR_TO_DATE(CONCAT('2025', '0101'), '%Y%m%d')
END AS USE_BASE_START_YMD
, STR_TO_DATE(CONCAT('2025', '1231'), '%Y%m%d') AS USE_BASE_END_YMD
, T.NOTE
, T.REMAIN_NUM -- 연차정산
FROM (
SELECT @rownum:=0
, A.LEAVE_ANNUAL_ID
, A.EMPLOYEE_ID
, E.COMPANY_CODE
, H.COMPANY_ORIGINAL_ID
, H.ORGANIZATION_ID
, H.DUTY_CODE
, O.ORGANIZATION_SORT
, GET_HR_ORGANIZATION_NAME(IFNULL('10', '10'),
H.ORGANIZATION_ID, E.RETIRE_YMD, IFNULL('ko', 'KO')) AS ORGANIZATION_NAME
, E.EMPLOYEE_NUMBER
, E.EMPLOYEE_NAME
, E.HIRE_YMD
, '2025' - DATE_FORMAT(E.GROUP_HIRE_YMD, '%Y') AS ANNUAL
, E.GROUP_HIRE_YMD
, E.RETIRE_YMD
, A.STANDARD_YEAR
, A.WORK_YMD
, A.START_YMD
, A.END_YMD
, A.CARRIED_FORWARD_DAYS
, A.CREATE_DAYS_1
, A.CREATE_DAYS_2
, A.CREATE_START_YMD
, A.CREATE_END_YMD
, GET_HR_TOTAL_CHANGE_YEAR_NUM(A.EMPLOYEE_ID, A.STANDARD_YEAR) AS YEAR_NUM_CHANGE
, IFNULL(GET_HR_TOTAL_ANNUAL_USED_COUNT(A.COMPANY_ORIGINAL_ID, A.EMPLOYEE_ID, A.STANDARD_YEAR), 0) AS USED_NUM
, IFNULL(GET_HR_TOTAL_ANNUAL_REQ_COUNT(A.COMPANY_ORIGINAL_ID, A.EMPLOYEE_ID, A.STANDARD_YEAR), 0) AS REQ_NUM
, TIMESTAMPDIFF(DAY,E.GROUP_HIRE_YMD,NOW()) AS HIRE_SERVICE_DAYS
, TIMESTAMPDIFF(MONTH,E.GROUP_HIRE_YMD,NOW()) AS HIRE_SERVICE_MONTHS
, TIMESTAMPDIFF(YEAR,E.GROUP_HIRE_YMD,NOW()) AS HIRE_SERVICE_YEARS
, A.NOTE
, IFNULL(T2.REMAIN_NUM, 0) AS REMAIN_NUM
, IFNULL(0, 0) AS PAGE_OFFSET
, IFNULL(432, 0) AS PAGE_COUNT
, (
SELECT COALESCE(SUM(HLAUC.AMOUNT), 0)
FROM HTM_LEAVE_ANNUAL_USE_CORRECTION HLAUC
WHERE HLAUC.STANDARD_YEAR = A.STANDARD_YEAR
AND HLAUC.EMPLOYEE_ID = A.EMPLOYEE_ID
AND HLAUC.IS_DELETED = FALSE
) AS USE_CORRECTION_DAYS
FROM HTM_LEAVE_ANNUAL A
INNER JOIN PER_EMPLOYEES E
ON A.EMPLOYEE_ID = E.EMPLOYEE_ID
INNER JOIN ASS_ASSIGNMENT_HISTORYS H
ON A.EMPLOYEE_ID = H.EMPLOYEE_ID
AND CURRENT_DATE() BETWEEN H.START_YMD AND H.END_YMD
AND H.ASSIGNMENT_DIVISION_CODE = '1'
AND H.LAST_YN = 'Y'
LEFT OUTER JOIN ORG_ORGANIZATION_HISTORYS O
ON H.ORGANIZATION_ID = O.ORGANIZATION_ID
AND A.WORK_YMD BETWEEN O.START_YMD AND O.END_YMD
LEFT JOIN HTM_LEAVE_ANNUAL_ADJUSTMENT HLAA
ON HLAA.EMPLOYEE_ID = A.EMPLOYEE_ID
AND HLAA.STANDARD_YEAR = A.STANDARD_YEAR
LEFT JOIN (SELECT HLAA.COMPANY_ORIGINAL_ID, HLAA.EMPLOYEE_ID, SUM(HLAA.YEAR_NUM) - IFNULL(SUM(HLAA.USED_NUM), 0) - IFNULL((SELECT sum(HLAU.BEFORE_NUM)
FROM HTM_LEAVE_ANNUAL_USE HLAU
WHERE HLAU.COMPANY_ORIGINAL_ID = HLAA.COMPANY_ORIGINAL_ID
AND HLAU.EMPLOYEE_ID = HLAA.EMPLOYEE_ID
GROUP BY HLAU.COMPANY_ORIGINAL_ID, HLAU.EMPLOYEE_ID),
0) AS REMAIN_NUM
FROM HTM_LEAVE_ANNUAL_ADJUSTMENT HLAA
GROUP BY HLAA.COMPANY_ORIGINAL_ID, HLAA.EMPLOYEE_ID) T2
ON T2.EMPLOYEE_ID = HLAA.EMPLOYEE_ID -- 연차정산
WHERE A.COMPANY_ORIGINAL_ID = '127515'
AND A.STANDARD_YEAR = '2025'
) T
ORDER BY T.ORGANIZATION_SORT, T.EMPLOYEE_NAME, T.START_YMD
---
```
## function: GET_HR_TOTAL_ANNUAL_USED_COUNT
```xml=
create
definer = aipers_app@`10.198.0.0/255.255.0.0` function GET_HR_TOTAL_ANNUAL_COUNT(PARAM_COMPANY_ORIGINAL_ID int,
PARAM_EMPLOYEE_ID int,
PARAM_EFFECTIVE_YEAR varchar(4),
PARAM_DRAFT_ID int,
ELA_STAT_CD_LOOKUP_CODE varchar(4)) returns decimal(7, 3)
comment '결재상태 룩업코드 및 연도 기준 연차휴가 사용일수'
BEGIN
DECLARE RETURN_VALUE DECIMAL(7, 3);
SELECT IFNULL(SUM(TT.FIRST_USED_NUM) + SUM(TT.USED_NUM) + SUM(TT.LAST_USED_NUM) + SUM(TT.LAST_DAY_USED_NUM) -
SUM(IFNULL(TT.RETIRE_USED_NUM, 0)), 0)
INTO RETURN_VALUE
FROM (SELECT CASE
-- 전년도에 시작하고 해당연도에 끝나는 연차중 올해만 사용된 갯수 계산
WHEN T.START_YMD < CONCAT(PARAM_EFFECTIVE_YEAR, '0101')
AND T.END_YMD >= CONCAT(PARAM_EFFECTIVE_YEAR, '0101')
THEN
CASE GET_COMMON_CODE_LOOKUPCODE('DTM_HOLIDAY_APPLY_TYPE', B.HOLIDAY_APPLY_TYPE, 'ko', now(),
null)
WHEN '1' THEN
IFNULL((SELECT SUM(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN CONCAT(PARAM_EFFECTIVE_YEAR, '0101') AND T.END_YMD
AND C.MINUS_DD = 1), 0)
ELSE
IFNULL((SELECT COUNT(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN CONCAT(PARAM_EFFECTIVE_YEAR, '0101') AND T.END_YMD),
0)
END -
CASE
WHEN T.TYPE_CODE =
GET_COMMON_CODE_ID('DTM_TYPE_CD', 103, 'ko', NOW(), NULL)
THEN 0.5
ELSE 0
END
ELSE 0
END AS FIRST_USED_NUM
-- 작년도와 내년도에 사용되는 연차는 0으로 처리
-- 하루 연차면 1 반차면 0.5
-- 여럿연차면 영업일 계산
, CASE
WHEN T.START_YMD < CONCAT(PARAM_EFFECTIVE_YEAR, '0101') THEN 0
WHEN T.END_YMD >= CONCAT(PARAM_EFFECTIVE_YEAR, '1231') THEN 0
WHEN T.START_YMD = T.END_YMD
THEN
CASE
WHEN T.TYPE_CODE IN
(GET_COMMON_CODE_ID('DTM_TYPE_CD', 102, 'ko', NOW(), NULL),
GET_COMMON_CODE_ID('DTM_TYPE_CD', 103, 'ko', NOW(), NULL))
THEN 0.5
WHEN GET_COMMON_CODE_ID('DTM_REQ_UNIT', 'T', 'ko', NOW(),
NULL) = B.REQUEST_UNIT_ID
THEN T.TOT_USE_NUM / 480
ELSE T.TOT_USE_NUM
END
ELSE
CASE GET_COMMON_CODE_LOOKUPCODE('DTM_HOLIDAY_APPLY_TYPE', B.HOLIDAY_APPLY_TYPE, 'ko', now(),
null)
WHEN '1' THEN
IFNULL((SELECT SUM(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND T.END_YMD
AND C.MINUS_DD = 1), 0)
ELSE
IFNULL((SELECT COUNT(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND T.END_YMD), 0)
END -
CASE
WHEN T.TYPE_CODE IN (
GET_COMMON_CODE_ID('DTM_TYPE_CD', 102, 'ko', NOW(), NULL),
GET_COMMON_CODE_ID('DTM_TYPE_CD', 103, 'ko', NOW(), NULL))
THEN 0.5
WHEN T.TYPE_CODE = GET_COMMON_CODE_ID('DTM_TYPE_CD', 104, 'ko', NOW(), NULL)
THEN 0.75
ELSE 0
END
END AS USED_NUM
-- 해당연도에 시작하고 내년도에 끝나는 연차중 올해만 사용된 갯수 계산
, CASE
WHEN T.END_YMD >= CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
AND T.START_YMD < CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
THEN
CASE GET_COMMON_CODE_LOOKUPCODE('DTM_HOLIDAY_APPLY_TYPE', B.HOLIDAY_APPLY_TYPE, 'ko', now(),
null)
WHEN '1' THEN
IFNULL((SELECT SUM(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
AND C.MINUS_DD = 1), 0)
ELSE
IFNULL((SELECT COUNT(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND CONCAT(PARAM_EFFECTIVE_YEAR, '1231')),
0)
END -
CASE
WHEN T.TYPE_CODE =
GET_COMMON_CODE_ID('DTM_TYPE_CD', 102, 'ko', NOW(), NULL)
THEN 0.5
ELSE 0
END
ELSE 0
END AS LAST_USED_NUM
-- 12워 31일 휴가 하루 계산
, CASE
WHEN T.START_YMD = CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
THEN
CASE GET_COMMON_CODE_LOOKUPCODE('DTM_HOLIDAY_APPLY_TYPE', B.HOLIDAY_APPLY_TYPE, 'ko', now(),
null)
WHEN '1' THEN
IFNULL((SELECT SUM(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
AND C.MINUS_DD = 1), 0)
ELSE
IFNULL((SELECT COUNT(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN T.START_YMD AND CONCAT(PARAM_EFFECTIVE_YEAR, '1231')),
0)
END -
CASE
WHEN T.TYPE_CODE IN
(GET_COMMON_CODE_ID('DTM_TYPE_CD', 102, 'ko', NOW(), NULL),
GET_COMMON_CODE_ID('DTM_TYPE_CD', 103, 'ko', NOW(), NULL))
THEN 0.5
WHEN T.TYPE_CODE = GET_COMMON_CODE_ID('DTM_TYPE_CD', 104, 'ko', NOW(), NULL)
THEN 0.75
ELSE 0
END
ELSE 0
END AS LAST_DAY_USED_NUM
, CASE
WHEN R.RETIRE_YMD IS NOT NULL THEN -- 25.01.15 퇴직일 후의 연차사용일수 계산(사용일수에서 차감목적)
CASE GET_COMMON_CODE_LOOKUPCODE('DTM_HOLIDAY_APPLY_TYPE', B.HOLIDAY_APPLY_TYPE, 'ko', now(),
null)
WHEN '1' THEN -- 25.01.15 근무일수 기준 계산
IFNULL((SELECT SUM(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN
GREATEST(
ADDDATE(R.RETIRE_YMD, INTERVAL 1 DAY), -- 25.01.15 퇴직일은 근무일로 처리, 퇴직일 다음날 부터 계산 적용
GREATEST( -- 25.01.15 조회 년도 내의 연차만 계산하기 위한 목적(조회년도 1월 1일부터 범위에 포함)
T.START_YMD,
CONCAT(PARAM_EFFECTIVE_YEAR, '0101')
)
)
AND LEAST( -- 25.01.15 조회 년도 내의 연차만 계산하기 위한 목적(조회년도 12월 31일 까지만 범위에 포함)
T.END_YMD,
CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
)
AND C.MINUS_DD = 1), 0)
ELSE -- 25.01.15 달력일수 기준 계산
IFNULL((SELECT COUNT(MINUS_DD)
FROM HTM_CALENDAR C
WHERE C.COMPANY_ORIGINAL_ID = T.COMPANY_ORIGINAL_ID
AND C.SOLAR_YMD BETWEEN
GREATEST(
ADDDATE(R.RETIRE_YMD, INTERVAL 1 DAY), -- 25.01.15 퇴직일은 근무일로 처리, 퇴직일 다음날 부터 계산 적용
GREATEST( -- 25.01.15 조회 년도 내의 연차만 계산하기 위한 목적(조회년도 1월 1일부터 범위에 포함)
T.START_YMD,
CONCAT(PARAM_EFFECTIVE_YEAR, '0101')
)
)
AND LEAST( -- 25.01.15 조회 년도 내의 연차만 계산하기 위한 목적(조회년도 12월 31일 까지만 범위에 포함)
T.END_YMD,
CONCAT(PARAM_EFFECTIVE_YEAR, '1231')
)), 0)
END * -- 25.01.15 곱하기: 연차 종류에 따른 사용일수 보정 복적
CASE
WHEN T.TYPE_CODE IN (
GET_COMMON_CODE_ID('DTM_TYPE_CD', 102, 'ko', NOW(), NULL), -- 25.01.15 오전반차
GET_COMMON_CODE_ID('DTM_TYPE_CD', 103, 'ko', NOW(), NULL)) -- 25.01.15 오후반차
THEN 0.5 -- 25.01.15 반차
WHEN T.TYPE_CODE =
GET_COMMON_CODE_ID('DTM_TYPE_CD', 104, 'ko', NOW(), NULL) -- 25.01.15 반반차
THEN 0.25 -- 25.01.15 반반차
ELSE 1 -- 25.01.15 연차
END
ELSE 0
END AS RETIRE_USED_NUM
FROM HTM_HOLIDAY_APPLICATION T
INNER JOIN HTM_DAILY_BASE B
ON T.COMPANY_ORIGINAL_ID = B.COMPANY_ORIGINAL_ID
AND T.TYPE_CODE = B.TYPE_CODE
AND B.END_YMD >= T.START_YMD
AND B.START_YMD <= T.START_YMD
LEFT JOIN (SELECT EMPLOYEE_ID, RETIRE_YMD
FROM PER_EMPLOYEES
WHERE RETIRE_YMD is not null
UNION
SELECT EMPLOYEE_ID, RETIRE_YMD
FROM PER_EMPLOYEE_RETIREMENTS
WHERE RETIRE_YMD <= CURRENT_DATE) R
ON T.EMPLOYEE_ID = R.EMPLOYEE_ID
AND
T.END_YMD > IFNULL(R.RETIRE_YMD, '19001231') -- 25.01.15 퇴직일은 근무일로 처리, 퇴직일 다음날부터 조인 처리
WHERE T.COMPANY_ORIGINAL_ID = PARAM_COMPANY_ORIGINAL_ID
AND T.EMPLOYEE_ID = PARAM_EMPLOYEE_ID
AND T.WORK_CODE IN (GET_COMMON_CODE_ID('DTM_WORK_CD', 100, 'ko', now(), null))
AND T.REQUEST_STATUS_CODE = GET_COMMON_CODE_ID('ELA_STAT_CD', ELA_STAT_CD_LOOKUP_CODE, 'ko', now(), null)
AND (T.DRAFT_ID != PARAM_DRAFT_ID OR T.DRAFT_ID IS NULL)) TT;
RETURN (RETURN_VALUE);
END;
```
Sign in with Wallet
Connect another wallet