style24.doc/산출물/4.구축/데이터처리/넷퍼시메일_회원정보동기화.sql

################################################################################
# 넷퍼시 활동회원
################################################################################
DROP TABLE IF EXISTS NETPATHY_MEMBER RESTRICT;
CREATE TABLE NETPATHY_MEMBER
(
    CUST_NO         INT UNSIGNED NOT NULL COMMENT '회원번호(회원)',
    CUST_ID         VARCHAR(200) COMMENT '회원ID',
    CUST_NM         VARCHAR(200) NOT NULL COMMENT '회원명',
    BIRTH_YMD       VARCHAR(200) COMMENT '생년월일',
    EMAIL           VARCHAR(200) COMMENT '이메일',
    EMAIL_AGREE_YN  CHAR(1) NOT NULL DEFAULT 'N' COMMENT '이메일수신동의여부',
    SEX_GB          VARCHAR(200) COMMENT '성별구분(공통코드G007)',
    ZIPCODE         VARCHAR(20) COMMENT '우편번호',
    JOIN_DT         DATETIME NOT NULL COMMENT '가입일시',
    UPD_DT          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시'
)
COMMENT='넷퍼시활동회원. 넷퍼시메일 솔루션에 연동할 활동회원 정보. 매일 1회 생성 시 발생'
;

/* 활동회원 생성 */
TRUNCATE TABLE STYLE24_MAILBATCH.NETPATHY_MEMBER;
INSERT INTO STYLE24_MAILBATCH.NETPATHY_MEMBER (
       CUST_NO
     , CUST_ID
     , CUST_NM
     , BIRTH_YMD
     , EMAIL
     , EMAIL_AGREE_YN
     , SEX_GB
     , ZIPCODE
     , JOIN_DT
     , UPD_DT
)
SELECT C.CUST_NO                                 /*회원GUID(=회원번호)*/
     , C.CUST_ID                                 /*로그인ID(=회원ID)*/
     , C.CUST_NM                                 /*회원명(암호화된값)*/
     , C.BIRTH_YMD                               /*생년월일(암호화된값)*/
     , C.EMAIL                                   /*이메일(암호화된값)*/
     , C.EMAIL_AGREE_YN                          /*메일수신여부*/
     , C.SEX_GB                                  /*성별(암호화된값)*/
     , CDA.RECIP_ZIPCODE              AS ZIPCODE /*우편번호*/
     , IFNULL(C.JOIN_DT,'1900-01-01') AS JOIN_DT /*가입일시*/
     , C.UPD_DT                                  /*수정일시*/
FROM   TB_CUSTOMER C
LEFT OUTER JOIN TB_CUST_DELIVERY_ADDR CDA ON C.CUST_NO = CDA.CUST_NO
                                         AND CDA.DEL_YN = 'N' /*삭제안된넘*/
                                         AND CDA.DEFAULT_YN = 'Y' /*기본배송지*/
WHERE  CUST_STAT = 'G104_10' /*활동회원*/
;

################################################################################
# 넷퍼시 탈퇴/휴면회원
################################################################################
DROP TABLE IF EXISTS NETPATHY_WITHDRAWAL RESTRICT;
CREATE TABLE NETPATHY_WITHDRAWAL
(
    CUST_NO  INT UNSIGNED NOT NULL COMMENT '회원번호(회원)',
    CUST_ID  VARCHAR(200) COMMENT '회원ID',
    CUST_NM  VARCHAR(200) NOT NULL COMMENT '회원명',
    JOIN_DT  DATETIME NOT NULL COMMENT '가입일시',
    REG_DT   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시'
)
COMMENT='넷퍼시 탈퇴/휴면회원. 넷퍼시메일 솔루션에 연동할 탈퇴/휴면회원 정보. 매일 1회 생성 시 발생'
;

/* 탈퇴/휴면회원 생성 */
TRUNCATE TABLE STYLE24_MAILBATCH.NETPATHY_WITHDRAWAL;
INSERT INTO STYLE24_MAILBATCH.NETPATHY_WITHDRAWAL (
       CUST_NO
     , CUST_ID
     , CUST_NM
     , JOIN_DT
     , REG_DT
)
SELECT CUST_NO                              /*회원GUID(=회원번호)*/
     , CUST_ID                              /*로그인ID(=회원ID)*/
     , CUST_NM                              /*회원명(암호화된값)*/
     , IFNULL(JOIN_DT,SECEDE_DT) AS JOIN_DT /*가입일시*/
     , SECEDE_DT                 AS REG_DT  /*등록일시*/
FROM   TB_SECEDE_CUST
UNION ALL
SELECT CUST_NO              /*회원GUID(=회원번호)*/
     , CUST_ID              /*로그인ID(=회원ID)*/
     , CUST_NM              /*회원명(암호화된값)*/
     , JOIN_DT              /*가입일시*/
     , DORMANT_DT AS REG_DT /*등록일시*/
FROM   TB_DORMANT_CUST
;

################################################################################
# 넷퍼시 포인트
################################################################################
DROP TABLE IF EXISTS NETPATHY_POINT RESTRICT;
CREATE TABLE NETPATHY_POINT
(
    CUST_NO     INT UNSIGNED NOT NULL COMMENT '회원번호(회원)',
    CUST_ID     VARCHAR(200) COMMENT '회원ID',
    RM_PNT_AMT  INT DEFAULT 0 NOT NULL COMMENT '잔여포인트금액',
    PREV_DT     DATETIME NOT NULL COMMENT '전일'
)
COMMENT='넷퍼시 포인트. 넷퍼시메일 솔루션에 연동할 포인트 정보. 매일 1회 생성 시 발생'
;

/* 회원의 포인트 생성 (전일자에 변경된 값만) */
TRUNCATE TABLE STYLE24_MAILBATCH.NETPATHY_POINT;
INSERT INTO STYLE24_MAILBATCH.NETPATHY_POINT (
       CUST_NO
     , CUST_ID
     , RM_PNT_AMT
     , PREV_DT
)
SELECT CP.CUST_NO                                                                       /*회원번호*/
     , C.CUST_ID                                                                        /*회원ID*/
     , SUM(CASE WHEN CP.EXP_CMP_DT IS NULL THEN CP.RM_PNT_AMT ELSE 0 END) AS RM_PNT_AMT /*남은포인트금액*/
     , DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)                          AS PREV_DT    /*전일*/
FROM   TB_CUST_POINT CP
     , TB_CUSTOMER C
WHERE  CP.CUST_NO = C.CUST_NO
AND    CP.CUST_NO IN (SELECT CUST_NO
                      FROM   TB_CUST_POINT
                      WHERE  UPD_DT >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
                      AND    UPD_DT < CURRENT_DATE()
                     )
AND    C.CUST_STAT = 'G104_10' /*활동회원만*/
GROUP  BY CP.CUST_NO
;