근태관리 및 급여 프로그램 작업중...

현재 근로자들은 출퇴근 카드 OR 지문인식기 단말기를 통해 출퇴근시 체크를 하고 있다.

그러나 출근을 빠트리거나 퇴근을 빠트리는 경우가 빈번히 발생하고 또는 출근을 퇴근으로 잘못 체크하거나 반대되는 경우도 있다. 더해서 출근을 연속으로 두번 체크하는 경우도 있다.
근태관리자가 일일히 손으로 입력을 해주는 수밖에 없다.
여기서 나는 곰곰히 생각을 해보니...
근로자들이 체크를 안한경우를 제외하고 즉, 잘못 체크된 항목에 대해서는 자동으로 수정이 되면
좋겠다고 생각한다.
그래서 다소 시간이 오래걸리고 지체되더라도 최대한 완성도 높은 프로그램을 만들고자 한다.

이러한 정보를 토대로 프로그램을 작성한다. 우선 결제를 받는 출퇴근리스트 부터 데이터를 뽑아보기로 한다.

결제를 받는 근태리스트에 들어가는 데이터에는 근로자들 각각 한달동안 출,퇴근 기록과 잔업시간
,심야시간, 통근갯수, 식수현황, 기본일수,유급일수,주차일수 등이 들어간다.

그럼 우선 필요한 주요 정보(테이블)들은 다음과 같다
사원정보(EMP)
달력(CARENDAR)
근태정보(COMMUTE_1)

사원들 정보를 알아야 한다. 언제.어디서,누가,무엇을,어떻게,왜 하는지를 담고 있어야 하겠다.
달력은 날짜 정보를 담고 있어야한다. 회사의 창립기념일,하계휴가등과 같은 자체 휴일이 있을경우에 이 테이블에 무슨날이 휴일인지만 체크를 해주면 보다 손쉽게 근태리스트를 출력할수 있다.
(휴일인 경우 특근으로 시간계산)

근태정보는 출퇴근 단말기에서 가져오는 데이터가 되겠다. 사번,일자,시간,출퇴근구분 컬럼으로 데이터를 가져온다.

해당 테이블 외에 부수적으로 테이블들이 있는데 생략하겠다. 생략한다고 하여 필요없는 테이블들은 아니며 일일히 열거하기가 불편함으로 생략한다.
내가 만든 ERD는 아래 그림과 같다. 우선은 근태,급여 프로그램을 위해 만든 ERD이다.

큰 흐름은 COMMUTE_1에 단말기에서 가져온 엑셀로 된 근태데이터를 밀어넣는다. 여기에서 다시 보기좋게 데이터를 정렬하여 나타낸 테이블이 COMMUTE_2가 된다.
COMMUTE_2 테이블에서 누락되거나 잘못입력된 데이터는 직접 입력,수정,삭제를 할수 있어야 된다.
따라서 데이터가 입력이 다된후에는 검증이 이루어져야한다.
검증은 우선 출근,퇴근이 null이 없어야 하고 출근과 퇴근은 서로 짝을 이루도록 한다. 또한 퇴근시간이 출근보다 빠른경우가 있으면 당연히 오류데이터 이므로 수정이 되어야 한다. 이러한 것들을 검증하는 쿼리를 만들어 오류데이터가 한건이상 나오면 프로시져에서 더이상 작업이 안되도록 막아둔다. 검증쿼리 건수가 0개로 나오면 데이터가 맞다고 보고 다음 프로시져를 실행시키도록 한다.
검증이 완료되면 COMMUTE_3테이블로 데이터를 뽑아낸다.
이때 WORK_TIME 테이블을 참조하여 잔업과 심야 통근을 구하고 CARENDAR 테이블을 이용하여 기본
,유급,주차를 구한다. 나머지 공상,휴직등은 COMMUTE_TP 테이블을 참조하여 구한다.
 
그리하여 COMMUTE_3 테이블은 사원별로 그룹을 이루고 마지막 행은 SUMMARY DATA가 나오도록 해야한다.

우선은 각각 테이블에 기초데이터를 다 넣는다.
즉, COMMUTE_2,COMMUTE_3 을 제외한 모든 테이블에는 데이터가 있어야 한다.
그뒤에 COMMUTE_2 에 데이터를 가공하여 넣어주고 다시 COMMUTE_3 에 데이터를 가공하여 넣어줘야한다.
현재 COMMUTE_1까진 데이터를 밀어넣엇다.(가공되지 않은 상태)
이 테이블에서 관리자가 최소한의 수정만 할수 있도록 수정할수 있는 사항은 수정한다.
우선 근로자가 출근할때 출근을 찍고 퇴근할때도 실수로 출근을 찍은 경우다. 이와 반대되는 경우도 마찬가지다.
이러한 사소한 실수가 관리자를 힘들게 한다. 이러한 작업은 SQL로도 충분히 자동 수정이 가능하다.
우선 고치기 전 데이터를 보자

DECLARE @STR_DT AS CHAR(10)
DECLARE @END_DT AS CHAR(10)
SET @STR_DT = '2011-08-01'
SET @END_DT = '2011-08-31'

SELECT *
FROM (SELECT DISTINCT * FROM COMMUTE_1)S
WHERE EMP_NO = '22004'

SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
FROM (SELECT DISTINCT * FROM COMMUTE_1)S
WHERE EMP_NO = '22004'
AND DT BETWEEN @STR_DT AND @END_DT
GROUP BY EMP_NO,DT,CMMT_TP
HAVING COUNT(*) > 1

 

두개의 SQL문을 실행시킨 결과다.

CMMT_TP 가 '01' 이면 출근이고 '02' 면 퇴근이다.
10일에 '01' 이 두번 연속으로 있다. 시간을 보면 출근 퇴근이 찍혀야 하지만 잘못 찍은것이다.
마찬가지로 26일도 그러한것을 볼수 있다.
이것을 바로 잡기 위해 다음과 같은 SQL을 작성하였다.

DECLARE @STR_DT AS CHAR(10)
DECLARE @END_DT AS CHAR(10)
SET @STR_DT = '2011-08-01'
SET @END_DT = '2011-08-31'

SELECT
    A.EMP_NO,A.DT,A.TIME
    ,CASE WHEN B.CNT IS NULL

              THEN A.CMMT_TP
              ELSE CASE WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '01'
                                THEN '02'
                                 WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '02'
                                 THEN '01'
                                 ELSE A.CMMT_TP 
                                 END 
              END CMMT_TP
FROM
(SELECT DISTINCT * FROM COMMUTE_1) A
LEFT OUTER JOIN
(
SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
FROM (SELECT DISTINCT * FROM COMMUTE_1)S
WHERE EMP_NO = '22004'
AND DT BETWEEN @STR_DT AND @END_DT
GROUP BY EMP_NO,DT,CMMT_TP
HAVING COUNT(*) > 1
)B ON A.EMP_NO = B.EMP_NO AND A.DT = B.DT AND A.CMMT_TP = B.CMMT_TP
WHERE A.EMP_NO = '22004'

ORDER BY 1,2,3

결과를 보자.

CMMT_TP 가 '02' 로 바뀌었다.
조건은 단순하다. EMP_NO,DT,CMMT_TP 가 같고 TIME 이 틀릴경우 위에 잘못 된 데이터가 된다.
따라서 위 조건으로 데이터를 추출하여 시간이 더 늦은 행의 CMMT_TP를 반대되는 구분값으로 넣어주는 것이다.

이렇게 데이터를 우선 가공하면 프로그램적으로 할수 있는 가공은 다 한것이라 할수 있겠다.
여기까지 가공한 SQL을 토대로 그대로 COMMUTE_2 테이블로 INSERT 해주면 한단계는 완료한 것이다.

아래는 COMMUTE_2 테이블에 넣어줄 데이터를 만들어 내는 쿼리다. 좀 복잡하게 보이지만
차근차근 살펴보면 아주 간단한 SQL 이다.

DECLARE @STR_DT CHAR(10)
DECLARE @END_DT CHAR(10)
SET @STR_DT = '2011-08-01'
SET @END_DT = '2011-08-31'

SELECT V.EMP_NO 사번,V.A_DT 출근일,V.A_TIME 출근시간 ,V.B_DT 퇴근일,V.B_TIME 퇴근시간,V.SEQ 순번
FROM
 (
 SELECT A.EMP_NO ,A.CMMT_TP A_TP,A.DT A_DT,A.TIME A_TIME ,B.CMMT_TP B_TP,B.DT B_DT ,B.TIME B_TIME,A.SEQ
 FROM
  (
  SELECT EMP_NO,CMMT_TP ,DT,TIME,ROW_NUMBER() OVER(PARTITION BY EMP_NO ORDER BY DT,TIME) SEQ
  FROM (SELECT A.EMP_NO,A.DT,A.TIME,CASE WHEN B.CNT IS NULL
                                 THEN A.CMMT_TP
                                 ELSE CASE WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '01'
                                           THEN '02'
                                           WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '02'
                                           THEN '01'
                                           ELSE A.CMMT_TP
                                           END
                                 END CMMT_TP
    FROM
    (SELECT DISTINCT * FROM COMMUTE_1) A
    LEFT OUTER JOIN
     (
     SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
     FROM (SELECT DISTINCT * FROM COMMUTE_1)S
     WHERE DT BETWEEN @STR_DT AND @END_DT
     GROUP BY EMP_NO,DT,CMMT_TP
     HAVING COUNT(*) > 1
     )B ON A.EMP_NO = B.EMP_NO AND A.DT = B.DT AND A.CMMT_TP = B.CMMT_TP
    )S
  )A LEFT OUTER JOIN
  (
  SELECT EMP_NO,CMMT_TP,DT,TIME,ROW_NUMBER() OVER(PARTITION BY EMP_NO ORDER BY DT,TIME) SEQ
  FROM (SELECT A.EMP_NO,A.DT,A.TIME,CASE WHEN B.CNT IS NULL
                                 THEN A.CMMT_TP
                                 ELSE CASE WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '01'
                                           THEN '02'
                                           WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '02'
                                           THEN '01'
                                           ELSE A.CMMT_TP
                                           END
                                 END CMMT_TP
    FROM
    (SELECT DISTINCT * FROM COMMUTE_1) A
    LEFT OUTER JOIN
     (
     SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
     FROM (SELECT DISTINCT * FROM COMMUTE_1)S
     WHERE DT BETWEEN @STR_DT AND @END_DT
     GROUP BY EMP_NO,DT,CMMT_TP
     HAVING COUNT(*) > 1
     )B ON A.EMP_NO = B.EMP_NO AND A.DT = B.DT AND A.CMMT_TP = B.CMMT_TP
    )S
  )B ON A.EMP_NO = B.EMP_NO
  AND A.SEQ + 1=B.SEQ
  AND B.CMMT_TP = '02' --퇴근
 WHERE A.CMMT_TP = '01' --출근
 AND (A.DT BETWEEN  @STR_DT AND @END_DT OR (A.DT IS NULL AND B.DT BETWEEN @STR_DT AND @END_DT))
 
 UNION

 SELECT A.EMP_NO,B.CMMT_TP,B.DT,B.TIME,A.CMMT_TP,A.DT,A.TIME ,A.SEQ
 FROM
  (
  SELECT EMP_NO,CMMT_TP,DT,TIME,ROW_NUMBER() OVER(PARTITION BY EMP_NO ORDER BY DT,TIME) SEQ
  FROM (SELECT A.EMP_NO,A.DT,A.TIME,CASE WHEN B.CNT IS NULL
                                 THEN A.CMMT_TP
                                 ELSE CASE WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '01'
                                           THEN '02'
                                           WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '02'
                                           THEN '01'
                                           ELSE A.CMMT_TP
                                           END
                                 END CMMT_TP
    FROM
    (SELECT DISTINCT * FROM COMMUTE_1) A
    LEFT OUTER JOIN
     (
     SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
     FROM (SELECT DISTINCT * FROM COMMUTE_1)S
     WHERE DT BETWEEN @STR_DT AND @END_DT
     GROUP BY EMP_NO,DT,CMMT_TP
     HAVING COUNT(*) > 1
     )B ON A.EMP_NO = B.EMP_NO AND A.DT = B.DT AND A.CMMT_TP = B.CMMT_TP
    )S
  )A LEFT OUTER JOIN
  (
  SELECT EMP_NO,CMMT_TP,DT,TIME,ROW_NUMBER() OVER(PARTITION BY EMP_NO ORDER BY DT,TIME) SEQ
  FROM (SELECT A.EMP_NO,A.DT,A.TIME,CASE WHEN B.CNT IS NULL
                                 THEN A.CMMT_TP
                                 ELSE CASE WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '01'
                                           THEN '02'
                                           WHEN B.CNT IS NOT NULL AND B.TIME = A.TIME AND A.CMMT_TP = '02'
                                           THEN '01'
                                           ELSE A.CMMT_TP
                                           END
                                 END CMMT_TP
    FROM
    (SELECT DISTINCT * FROM COMMUTE_1) A
    LEFT OUTER JOIN
     (
     SELECT EMP_NO,DT,CMMT_TP,COUNT(*) CNT,MAX(TIME) TIME
     FROM (SELECT DISTINCT * FROM COMMUTE_1)S
     WHERE DT BETWEEN @STR_DT AND @END_DT
     GROUP BY EMP_NO,DT,CMMT_TP
     HAVING COUNT(*) > 1
     )B ON A.EMP_NO = B.EMP_NO AND A.DT = B.DT AND A.CMMT_TP = B.CMMT_TP
    )S
  )B ON A.EMP_NO = B.EMP_NO
   AND A.SEQ - 1=B.SEQ
   AND B.CMMT_TP = '01'
 WHERE A.CMMT_TP = '02'
 AND (B.DT BETWEEN @STR_DT AND @END_DT OR (B.DT IS NULL AND A.DT BETWEEN @STR_DT AND @END_DT))
 )V
 ,EMP A

WHERE V.EMP_NO = A.EMP_NO
AND A.TP_NO IN ('2','3')
AND (V.SEQ%2=0 OR V.A_DT IS NULL OR V.B_DT IS NULL)
ORDER BY 1,V.SEQ

 

 

구분값을 수정하지 않은 결과를 보면 다음과 같다.

잘못된 구분값을 자동 수정되도록 작성한 SQL을 실행시킨 결과다.

음...많은 예외상항중 하나가 자동수정되도록 바뀌었다.

이제 위에 내용을 COMMUTE_2 테이블로 데이터를 밀어넣은 후
관리자가 직접 데이터를 맞도록 수정을 해야한다.

수정이 완료되면 해당 데이터를 토대로 COMMUTE_3 테이블에
넣을 데이터를 추출하여 밀어넣는다.

모든 단계가 완료되면 보고서 프로그램을 통해 해당 데이터를 보기
좋게 나오도록 배치하여 실제 시스템에 도입하기 위한 준비를
해야한다.