콘텐츠로 건너뛰기
Home » Blog » 채권 RPA 시스템 구축기 (2)

채권 RPA 시스템 구축기 (2)

  • work

메일 자동화

이번 프로젝트를 진행하면서 가장 중점적으로 다뤄야할 부분이었다.
기존 플로우에서 사람이 하기에 가장 공수가 많이 드는 작업이라 효율적인 처리가 필요했다.

메일 발송 자동화에서의 큰 목표는 다음과 같았다.

  • 메일 제목과 본문 내용은 해당 업체에 맞게 작성되어야 한다.
  • xlsx 템플릿을 해당 업체 정보에 맞게 커스텀하게 구성한뒤, pdf로 변환하여 첨부해야 한다.
  • “이 모든 작업이 버튼 클릭 한두번 만으로 자동화가 되어야 한다.”

기본적으로 제공받은 파일은 4개가 있었다.

  1. 통장 사본 이미지 파일
  2. 회사 사업자 등록증 pdf 파일
  3. 서비스 이용료 납부 안내 xlsx 파일
  4. 메일 템플릿 html

선제 조건

메일 전송을 위한 조건은 다음과 같다.

  • 메일 템플릿을 기준으로 내용을 작성을 해야한다. 지정할 변수는 제목과 본문에 하나씩 있다.
    • 메일 제목 – 카카오톡 비즈메세지 서비스 이용대급 미납 안내드립니다_{업체명}
    • 본문 – 납부 및 제출 기한 : {yyyy.mm.dd(E)}
  • 통장사본과 사업자 등록증 파일은 디폴트로 첨부를 해야한다.
  • 3번 파일에서 특정 cell 을 해당 미납 업체에 맞춰서 수정해야한다.
    • 문서 번호
    • 문서 생성 날짜
    • 수신자
    • 청구월
    • 미납 금액
    • 납부 기한
  • 수정한 xlsx 파일을 pdf로 변환하여 첨부를 해야한다.

기술 선정

신규 프로젝트였기 때문에 스택은 자유롭게 선정할 수 있었다.
(서버에 운영 중인 Java 버전이 있어서, JDK는 tar로 묶어 함께 배포하는 방식으로 처리했다)

  • Java 17
  • Spring 3.5.0
  • MyBatis
  • MySQL
  • Java-Mail
  • Apache POI
  • Libre-Office

MyBatis는 기존 테이블과의 결합을 용이하게 하기 위해 선택했다.
JPQL로 DTO Projection을 하거나, QueryDSL을 사용하는 방법도 고려해볼 수 있었지만,
기존 시스템이 MyBatis 기반이라 참고할 수 있는 쿼리가 많았다.
또한 사내 대부분의 시스템이 JPA를 사용하지 않고 있어, 향후 인수인계 시에도 MyBatis가 더 수월할 것이라 판단했다.

나아가, 메일 발송을 위해 Java-Mail을, 엑셀의 cell 값을 수정하기위해 Apache POI 라이브러리를 추가했고,
엑셀을 pdf로 변환하기 위해 Aspose를 도입했었다.
(그러나 무료 라이센스로 사용하니 pdf 상단에 빨간색으로 텍스트가 떴다.)

결국 Libre-Office로 교체해서 테스트를 해보고 정상 동작하는 것을 확인했다.
그러나 서버에 설치할 수 있는 권한은 없었기에, AppImage로 업로드해서 포터블하게 활용했다.
(WAS 내에서 headless 옵션을 붙여서 프로세스 빌더로 처리했다)

그러나 pdf 산출물을 살펴보니 글자가 다 깨져있었고, 아니나 다를까 폰트가 없어서 깨지는 이슈가 있었다.
(맥이라 윈도우 폰트가 없다. 리눅스 서버에도 당연히 없었다.)
결국 추가로 Malgun 폰트까지 함께 업로드 해주어야 했다.

ERD

기존 데이터베이스에 추가된 테이블은 다음과 같다.

  • 미납 업체 목록에서 추가한 채권목록은 ‘AUTOBOND’ 테이블에 저장한다.
  • 메일 발송에 첨부하기위해 생성된 파일은 ‘AUTOBOND_DOCUMENT_FILE’ 테이블에 로그로 남긴다.
    • 동일한 조건의 파일을 매번 새로 생성하지 않기 위해, 생성 이력을 저장하는 로그 테이블을 활용했다.
      • 이를 통해 기존에 생성된 파일이 존재하는 경우에는 재활용이 가능하도록 구성했다.
    • pk는 16자리의 해시값과 확장자를 활용하여 복합키로 지정했는데, 해당 해시는 다음 값들을 기반으로 생성된다.
      (이를 통해 해당 조건 마다의 고유성을 보장했다.)
      • 사이트 아이디
      • 문서 생성일
      • 청구월
      • 납부기한
      • 미납 금액
  • 최종적인 메일 및 알림톡 발송 로그는 ‘AUTOBOND_SEND_LOG’ 테이블에 로그를 저장한다.

추가로, 첨부되는 static한 이미지 및 pdf 파일과 템플릿 xlsx 파일은(3종) ‘./autobond/data/src/’ 경로에 저장했고,
파생된 파일은 ‘./autobond/data/doc/{hash}/’ 디렉토리에 xlsx, pdf 파일을 각각 저장하도록 했다.
(IDC 서버라 S3를 쓸 수 없어서 서버 업로드를 할 수 밖에 없었다.)

발송 플로우

최종적으로 개발한 메일 발송 플로우는 다음과 같다.

  1. 클라이언트 측에서 ‘사이트 아이디’, ‘청구월’, ‘발송대상’ 을 json 으로 담아서 api 서버로 POST 요청을 전송한다.
  2. 서버는 DB에서 AUTOBOND 테이블을 조회하여 해당 채권을 특정한다.
  3. Java Mail을 html 템플릿을 기반으로 작성한다. 제목과 본문을 해당 업체에 맞게 구성한다.
  4. 해당 채권의 컬럼 값들을 기준으로 xlsx, pdf 파일이 존재하는지 로그 테이블을 조회한다. (존재한다면 해당 경로의 파일을 활용한다)
    • 파일 및 디렉토리가 존재하지 않는다면 src 경로에 있는 xlsx 템플릿을 가져온다.
    • 해당 엑셀 템플릿에서 enum으로 지정해놓은 cell에 apache-poi를 통해 미납업체에 맞는 정보를 기입한 다음 해당 hash 값의 디렉토리에 저장한다.
    • 해당 xlsx 파일 생성 로그를 AUTOBOND_DOCUMENT_FILE 테이블에 남긴다.
    • 생성된 xlsx 파일을, Libre-office를 process builder를 통해 pdf로 변환하여 해당 hash 값의 디렉토리에 저장한다.
    • 해당 pdf 파일 생성 로그를 AUTOBOND_DOCUMENT_FILE 테이블에 남긴다.
  5. 메일의 attachmentPart에 해당 첨부파일을 등록한다.
  6. 발송 대상에게 메일을 전송한다.
  7. AUTOBOND_SEND_LOG에 발송 로그를 저장한다.
  8. 클라이언트 측에 발송 결과 및 발송 시간을 전달한다.

결과

단체 메일 발송

타겟 메일 발송

수신 결과

잘 동작한다.