style24.doc/산출물/4.구축/마이그레이션/쿼리/04.회원.sql

TRUNCATE TABLE tb_customer;
TRUNCATE TABLE tb_customer_hst;
TRUNCATE TABLE tb_customer_sns;
TRUNCATE TABLE tb_dormant_cust;
TRUNCATE TABLE tb_secede_cust;
TRUNCATE TABLE tb_cust_point;
TRUNCATE TABLE tb_cust_point_hst;
TRUNCATE TABLE tb_giftcard_receipt_api;
TRUNCATE TABLE tb_giftcard_receipt;
TRUNCATE TABLE tb_giftcard_api_hst;
TRUNCATE TABLE tb_cust_giftcard;
TRUNCATE TABLE tb_cust_giftcard_hst;
TRUNCATE TABLE tb_batch_birth;
TRUNCATE TABLE tb_cust_account;
TRUNCATE TABLE tb_cust_contact_hst;
TRUNCATE TABLE tb_cust_delivery_addr;
TRUNCATE TABLE tb_cust_grade_hst;
TRUNCATE TABLE tb_cust_coupon;
TRUNCATE TABLE tb_cust_market_hst;
TRUNCATE TABLE TB_ORDER;
TRUNCATE TABLE tb_order_detail;
TRUNCATE TABLE tb_order_detail_item;
TRUNCATE TABLE tb_order_detail_invoice;
TRUNCATE TABLE tb_order_detail_item_hst;
TRUNCATE TABLE tb_order_detail_hst;
TRUNCATE TABLE tb_order_change;
TRUNCATE TABLE tb_order_change_detail;
TRUNCATE TABLE tb_order_change_detail_hst;
TRUNCATE TABLE tb_order_memo;
TRUNCATE TABLE tb_order_delivery_exception;
TRUNCATE TABLE tb_order_delivery_exception_item;
TRUNCATE TABLE tb_order_dormant;

##### 회원, 주문 #####
-- 정상회원
TRUNCATE TABLE tb_customer;

ALTER TABLE TB_CUSTOMER AUTO_INCREMENT = 100000;

-- 비회원
INSERT INTO tb_customer (CUST_NO, CUST_ID, CUST_NM, PASSWD, SITE_CD, FRONT_GB, CUST_GRADE, CUST_STAT, REG_NO, REG_DT, UPD_NO, UPD_DT, ENC_UPD_YN)
VALUES (0, '0', '비회원', 'X', 'G000_10', 'P', 'G110', 'G104', 0, NOW(), 0, NOW(), 'Y');

INSERT INTO tb_customer (CUST_ID, CUST_NM, PASSWD, BIRTH_YMD, SEX_GB, CELL_PHNNO, APP_AGREE_YN, APP_AGREE_DT, SMS_AGREE_YN, SMS_AGREE_DT, EMAIL, EMAIL_AGREE_YN, EMAIL_AGREE_DT, MK_AGREE_YN, MK_AGREE_DT, HOME_ZIPCODE, HOME_BASE_ADDR, HOME_DTL_ADDR, SITE_CD, FRONT_GB, AF_LINK_CD, CUST_GB, CUST_GRADE, JOIN_DT, FOREIGNER_YN, CUST_STAT, PASSWD_CHG_DT, TEMP_PASSWD_YN, LOGIN_LDT, CI, AUTH_DT, SNS_TYPE, MANAGED_RSN, MANAGED_DTL_RSN, MANAGED_DT, SECEDE_RSN, SECEDE_DTL_RSN, SECEDE_DT, REG_NO, REG_DT, UPD_NO, UPD_DT, MEMBER_GUID)
SELECT
    LoginId AS CUST_ID
    , FN_ENC_AES(MemberName) AS CUST_NM -- 암호화
    , Password AS PASSWD
    , FN_ENC_AES(BirthDate) AS BIRTH_YMD -- 암호화
    , CASE WHEN Gender = '남' THEN FN_ENC_AES('G007_M') WHEN Gender = '여' THEN FN_ENC_AES('G007_F') ELSE FN_ENC_AES('G007_X') END AS SEX_GB -- 암호화
    , FN_ENC_AES(CellNum) AS CELL_PHNNO -- 암호화
    , IF((SELECT NOTI_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) IS NULL, 'N', (SELECT NOTI_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1)) AS APP_AGREE_YN
    , (SELECT UPT_DATE FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) AS APP_AGREE_DT
    , IF(IsSMS = 'True', 'Y', 'N') AS SMS_AGREE_YN
    , (SELECT DateModify FROM old_log_membermodify X WHERE X.LoginId = A.LoginId ORDER BY DateModify DESC LIMIT 1) AS SMS_AGREE_DT
    , FN_ENC_AES(Email) AS EMAIL -- 암호화
    , IF(IsEmail = 'True', 'Y', 'N') AS EMAIL_AGREE_YN
    , (SELECT DateModify FROM old_log_membermodify X WHERE X.LoginId = A.LoginId ORDER BY DateModify DESC LIMIT 1) AS EMAIL_AGREE_DT
    , IF((SELECT MKT_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) IS NULL, 'N', (SELECT MKT_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1)) AS MK_AGREE_YN
    , (SELECT UPT_DATE FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) AS MK_AGREE_DT
    , FN_ENC_AES(DefaultPostalCode) AS HOME_ZIPCODE -- 암호화
    , CASE WHEN DefaultAddrNew IS NOT NULL AND DefaultAddrNew != '' THEN FN_ENC_AES(DefaultAddrNew) ELSE FN_ENC_AES(DefaultAddr1) END AS HOME_BASE_ADDR -- 자택기본주소_암호화
    , FN_ENC_AES(DefaultAddr2) AS HOME_DTL_ADDR -- 자택상세주소_암호화
    , 'G000_10' AS SITE_CD
    , IF((SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) IS NULL, 'P', (SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath)) AS FRONT_GB -- 프론트구분(P:PC, M:모바일, A:앱)
    , (SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) AS AF_LINK_CD -- 제휴링크코드
    , 'G100_10' AS CUST_GB -- 회원구분(공통코드G100)
    , 'G110_10' AS CUST_GRADE -- 회원등급(공통코드G110)
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS JOIN_DT  -- 가입일시
    , IF(Nationality = '외국인', 'Y', 'N') AS FOREIGNER_YN -- 외국인여부(외국인:Y)
    , 'G104_10' AS CUST_STAT -- 고객상태(공통코드G104)
    , NULL AS PASSWD_CHG_DT -- 비밀번호변경일시
    , 'N' AS TEMP_PASSWD_YN -- 임시비밀번호여부
    , DateLastVisited AS LOGIN_LDT -- 최종로그인일시
    , CI -- 인증CI
    , NULL AS AUTH_DT -- 인증일시
    , NULL AS SNS_TYPE
    , NULL AS MANAGED_RSN -- 관리대상지정사유(공통코드G103)
    , NULL AS MANAGED_DTL_RSN -- 관리대상지정상세사유
    , NULL AS MANAGED_DT -- 관리대상지정일시
    , NULL AS SECEDE_RSN -- 탈퇴사유(공통코드G102)
    , NULL AS SECEDE_DTL_RSN -- 탈퇴상세사유
    , NULL AS SECEDE_DT-- 탈퇴일시
	, FN_ENC_AES(SUBSTRING(BirthDate, 5, 2)) AS BIRTH_MM
    , 2 AS REG_NO -- 등록자번호
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS REG_DT -- 등록일시
    , 2 AS UPD_NO -- 수정자번호
    , DateLastModified AS UPD_DT-- 수정일시
    , MemberGUID
FROM old_prf_member A
WHERE NOT EXISTS(
    SELECT 1
    FROM old_prf_memberdormant B
    WHERE B.MemberGUID = A.MemberGUID
    )
; -- 1분50초

-- 휴면회원
INSERT INTO tb_customer (CUST_ID, CUST_NM, PASSWD, BIRTH_YMD, SEX_GB, CELL_PHNNO, APP_AGREE_YN, APP_AGREE_DT, SMS_AGREE_YN, SMS_AGREE_DT, EMAIL, EMAIL_AGREE_YN, EMAIL_AGREE_DT, MK_AGREE_YN, MK_AGREE_DT, HOME_ZIPCODE, HOME_BASE_ADDR, HOME_DTL_ADDR, SITE_CD, FRONT_GB, AF_LINK_CD, CUST_GB, CUST_GRADE, JOIN_DT, FOREIGNER_YN, CUST_STAT, PASSWD_CHG_DT, TEMP_PASSWD_YN, LOGIN_LDT, CI, AUTH_DT, SNS_TYPE, MANAGED_RSN, MANAGED_DTL_RSN, MANAGED_DT, SECEDE_RSN, SECEDE_DTL_RSN, SECEDE_DT, REG_NO, REG_DT, UPD_NO, UPD_DT, MEMBER_GUID)
SELECT
    LoginId AS CUST_ID
    , FN_ENC_AES(MemberName) AS CUST_NM -- 암호화
    , Password AS PASSWD
    , FN_ENC_AES(BirthDate) AS BIRTH_YMD -- 암호화
    , CASE WHEN Gender = '남' THEN FN_ENC_AES('G007_M') WHEN Gender = '여' THEN FN_ENC_AES('G007_F') ELSE FN_ENC_AES('G007_X') END AS SEX_GB -- 암호화
    , FN_ENC_AES(CellNum) AS CELL_PHNNO -- 암호화
    , IF((SELECT NOTI_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) IS NULL, 'N', (SELECT NOTI_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1)) AS APP_AGREE_YN
    , (SELECT UPT_DATE FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) AS APP_AGREE_DT
    , IF(IsSMS = 'True', 'Y', 'N') AS SMS_AGREE_YN
    , (SELECT DateModify FROM old_log_membermodify X WHERE X.LoginId = A.LoginId ORDER BY DateModify DESC LIMIT 1) AS SMS_AGREE_DT
    , FN_ENC_AES(Email) AS EMAIL -- 암호화
    , IF(IsEmail = 'True', 'Y', 'N') AS EMAIL_AGREE_YN
    , (SELECT DateModify FROM old_log_membermodify X WHERE X.LoginId = A.LoginId ORDER BY DateModify DESC LIMIT 1) AS EMAIL_AGREE_DT
    , IF((SELECT MKT_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) IS NULL, 'N', (SELECT MKT_FLAG FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1)) AS MK_AGREE_YN
    , (SELECT UPT_DATE FROM old_app_user X WHERE X.CUST_ID = LoginId ORDER BY UPT_DATE DESC LIMIT 1) AS MK_AGREE_DT
    , FN_ENC_AES(DefaultPostalCode) AS HOME_ZIPCODE -- 암호화
    , CASE WHEN DefaultAddrNew IS NOT NULL AND DefaultAddrNew != '' THEN FN_ENC_AES(DefaultAddrNew) ELSE FN_ENC_AES(DefaultAddr1) END AS HOME_BASE_ADDR -- 자택기본주소_암호화
    , FN_ENC_AES(DefaultAddr2) AS HOME_DTL_ADDR -- 자택상세주소_암호화
    , 'G000_10' AS SITE_CD
    , IF((SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) IS NULL, 'P', (SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath)) AS FRONT_GB -- 프론트구분(P:PC, M:모바일, A:앱)
    , (SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) AS AF_LINK_CD -- 제휴링크코드
    , 'G100_10' AS CUST_GB -- 회원구분(공통코드G100)
    , 'G110_10' AS CUST_GRADE -- 회원등급(공통코드G110)
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS JOIN_DT  -- 가입일시
    , IF(Nationality = '외국인', 'Y', 'N') AS FOREIGNER_YN -- 외국인여부(외국인:Y)
    , 'G104_10' AS CUST_STAT -- 고객상태(공통코드G104)
    , NULL AS PASSWD_CHG_DT -- 비밀번호변경일시
    , 'N' AS TEMP_PASSWD_YN -- 임시비밀번호여부
    , DateLastVisited AS LOGIN_LDT -- 최종로그인일시
    , CI -- 인증CI
    , NULL AS AUTH_DT -- 인증일시
    , NULL AS SNS_TYPE
    , NULL AS MANAGED_RSN -- 관리대상지정사유(공통코드G103)
    , NULL AS MANAGED_DTL_RSN -- 관리대상지정상세사유
    , NULL AS MANAGED_DT -- 관리대상지정일시
    , NULL AS SECEDE_RSN -- 탈퇴사유(공통코드G102)
    , NULL AS SECEDE_DTL_RSN -- 탈퇴상세사유
    , NULL AS SECEDE_DT-- 탈퇴일시
	, FN_ENC_AES(SUBSTRING(BirthDate, 5, 2)) AS BIRTH_MM
    , 2 AS REG_NO -- 등록자번호
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS REG_DT -- 등록일시
    , 2 AS UPD_NO -- 수정자번호
    , DateLastModified AS UPD_DT-- 수정일시
    , MemberGUID
FROM old_prf_memberdormant A
; -- 9분20초

-- 탈퇴회원
INSERT INTO tb_customer (CUST_ID, CUST_NM, PASSWD, BIRTH_YMD, SEX_GB, CELL_PHNNO, APP_AGREE_YN, APP_AGREE_DT, SMS_AGREE_YN, SMS_AGREE_DT, EMAIL, EMAIL_AGREE_YN, EMAIL_AGREE_DT, MK_AGREE_YN, MK_AGREE_DT, HOME_ZIPCODE, HOME_BASE_ADDR, HOME_DTL_ADDR, SITE_CD, FRONT_GB, AF_LINK_CD, CUST_GB, CUST_GRADE, JOIN_DT, FOREIGNER_YN, CUST_STAT, PASSWD_CHG_DT, TEMP_PASSWD_YN, LOGIN_LDT, CI, AUTH_DT, SNS_TYPE, MANAGED_RSN, MANAGED_DTL_RSN, MANAGED_DT, SECEDE_RSN, SECEDE_DTL_RSN, SECEDE_DT, REG_NO, REG_DT, UPD_NO, UPD_DT, MEMBER_GUID)
SELECT
    LoginId AS CUST_ID
    , '탈퇴' AS CUST_NM -- 암호화
    , '탈퇴' AS PASSWD -- 암호화
    , NULL AS BIRTH_YMD -- 암호화
    , NULL AS SEX_GB -- 암호화
    , NULL AS CELL_PHNNO -- 암호화
    , 'N' AS APP_AGREE_YN -- ???
    , NULL AS APP_AGREE_DT -- ???
    , 'N' AS SMS_AGREE_YN
    , NULL AS SMS_AGREE_DT -- ???
    , NULL AS EMAIL -- 암호화
    , 'N' AS EMAIL_AGREE_YN
    , NULL AS EMAIL_AGREE_DT -- ???
    , 'N' AS MK_AGREE_YN
    , NULL AS MK_AGREE_DT
    , NULL AS HOME_ZIPCODE -- 암호화
    , NULL AS HOME_BASE_ADDR -- 자택기본주소_암호화
    , NULL AS HOME_DTL_ADDR -- 자택상세주소_암호화
    , 'G000_10' AS SITE_CD
    , 'P' AS FRONT_GB -- 프론트구분(P:PC, M:모바일, A:앱)
    , NULL AS AF_LINK_CD -- 제휴링크코드 AS-IS ???
    , 'G100_10' AS CUST_GB -- 회원구분(공통코드G100)
    , 'G110_10' AS CUST_GRADE -- 회원등급(공통코드G110)
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS JOIN_DT  -- 가입일시
    , 'N' AS FOREIGNER_YN -- 외국인여부(외국인:Y) AS-IS ???
    , 'G104_30' AS CUST_STAT -- 고객상태(공통코드G104) NULL ???
    , NULL AS PASSWD_CHG_DT -- 비밀번호변경일시
    , 'N' AS TEMP_PASSWD_YN -- 임시비밀번호여부
    , DateRegister AS LOGIN_LDT -- 최종로그인일시
    , NULL AS CI -- 인증CI
    , NULL AS AUTH_DT -- 인증일시
    , NULL AS SNS_TYPE
    , NULL AS MANAGED_RSN -- 관리대상지정사유(공통코드G103)
    , NULL AS MANAGED_DTL_RSN -- 관리대상지정상세사유
    , NULL AS MANAGED_DT -- 관리대상지정일시
    , NULL AS SECEDE_RSN -- 탈퇴사유(공통코드G102)
    , WithdrawReason AS SECEDE_DTL_RSN -- 탈퇴상세사유
    , DateWithdraw AS SECEDE_DT-- 탈퇴일시
	, FN_ENC_AES(SUBSTRING(BirthDate, 5, 2)) AS BIRTH_MM
    , 2 AS REG_NO -- 등록자번호
    , DateWithdraw AS REG_DT -- 등록일시
    , 2 AS UPD_NO -- 수정자번호
    , DateWithdraw AS UPD_DT-- 수정일시
    , MemberGUID
FROM old_prf_memberwithdrawal A
WHERE NOT EXISTS(
    SELECT 1
    FROM tb_customer
    WHERE CUST_ID = A.LoginId
    )
;

UPDATE tb_customer A
SET REG_NO = CUST_NO
    , UPD_NO = CUST_NO
WHERE 1=1
; -- 1분9초

-- 생일 이벤트용
TRUNCATE TABLE tb_batch_birth;

INSERT INTO tb_batch_birth
SELECT
    CUST_NO
    , BIRTH_YMD
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
FROM tb_customer
; -- 36초

-- 마케팅수신동의이력
ALTER TABLE TB_CUST_MARKET_HST AUTO_INCREMENT = 1;

INSERT INTO TB_CUST_MARKET_HST (CUST_NO, APP_AGREE_YN, SMS_AGREE_YN, EMAIL_AGREE_YN, MK_AGREE_YN, REG_NO, REG_DT)
SELECT CUST_NO
    , APP_AGREE_YN
    , SMS_AGREE_YN
    , EMAIL_AGREE_YN
    , MK_AGREE_YN
    , CUST_NO
    , IFNULL((SELECT MAX(AGREE_DT)
        FROM (
            SELECT APP_AGREE_DT AS AGREE_DT FROM tb_customer X WHERE X.CUST_NO = A.CUST_NO
            UNION ALL SELECT SMS_AGREE_DT AS AGREE_DT FROM tb_customer X WHERE X.CUST_NO = A.CUST_NO
            UNION ALL SELECT EMAIL_AGREE_DT AS AGREE_DT FROM tb_customer X WHERE X.CUST_NO = A.CUST_NO
            UNION ALL SELECT MK_AGREE_DT AS AGREE_DT FROM tb_customer X WHERE X.CUST_NO = A.CUST_NO
        ) X ), A.REG_DT)  AS REG_DT
FROM tb_customer A
; -- 1분30초

SELECT MAX(CUST_MARKET_HST_SQ) FROM TB_CUST_MARKET_HST;
ALTER TABLE TB_CUST_MARKET_HST AUTO_INCREMENT = 4241764;

-- 상품평
TRUNCATE TABLE tb_review;

ALTER TABLE tb_review AUTO_INCREMENT = 1;

INSERT INTO tb_review
SELECT
    ReviewNo AS REVIEW_SQ
    , GOODS_CD -- 상품코드
    ,'R' AS REVIEW_GB -- 상품평구분(R:일반상품평, P:프리미엄상품평)
    , (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginId) AS CUST_NO
    , NULL AS ORD_NO
    , NULL AS ORD_DTL_NO
    , Title AS REVIEW_TITLE -- 상품평제목
    , Contents AS REVIEW_CONTENT -- 상품평내용
    , CASE WHEN Rate_Satisfied = '5' THEN '5' -- 매우 만족
            WHEN Rate_Satisfied = '4' THEN '4' -- 만족
            WHEN Rate_Satisfied = '3' THEN '3' -- 보통
            WHEN Rate_Satisfied = '2' THEN '2' -- 불만
            WHEN Rate_Satisfied = '1' THEN '1' -- 매우 불만
            ELSE '3'
        END AS SCORE -- 구매평점
    , NULL AS HEIGHT -- 키
    , NULL AS WEIGHT -- 몸무게
    , CASE WHEN Rate_Size = '1' THEN '2' -- 딱맞음
            WHEN Rate_Size = '2' THEN '1' -- 작음
            WHEN Rate_Size = '3' THEN '3' -- 큼
            ELSE '1'
        END AS SCORE_SIZE
    , CASE WHEN Rate_Color = '1' THEN '2' -- 똑같음
            WHEN Rate_Color = '2' THEN '3' -- 어두움
            WHEN Rate_Color = '3' THEN '1' -- 밝음
            ELSE '1'
        END AS SCORE_COLOR
    , NULL AS SCORE_FIT -- 핏점수(공통코드G042)
    , NULL AS SCORE_THICK -- 두께감점수(공통코드G079)
    , NULL AS SCORE_WEIGHT -- 무게감점수(공통코드G080)
    , NULL AS SCORE_BALL -- 볼넓이점수(공통코드G041)
    , Point AS GIVE_DUE_PNT -- 지급예정포인트
    , 'G043_30' AS PNT_GIVE_STAT -- 포인트지급상태(공통코드G043)
    , IF(IsBestReview = 'True', 'P', 'R') AS BEST_YN -- 베스트여부
    , 0 AS GIVE_DUE_BPNT -- 베스트포인트예정포인트
    , 'N' AS BPNT_GIVE_YN -- 베스트포인트지급여부
    , IF(StatusCd = '게시', 'Y', 'N') AS DISP_YN -- 표시여부
    , 'N' AS DEL_YN -- 삭제여부
    , 'Y' AS CONFIRM_YN -- 확인여부
    , NULL AS CONFIRM_UNO -- 확인자번호
    , NULL AS CONFIRM_DT -- 확인일시
    , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginId) IS NULL, 2, (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginId)) AS REG_NO
    , DateCreated AS REG_DT
    , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginId) IS NULL, 2, (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginId)) AS UPD_NO
    , DateCreated AS UPD_DT
    , NULL AS ADM_RPL
    , NULL AS ADM_RPL_REG_NO
    , NULL AS ADM_RPL_DT
    , 'N' AS RPL_CFM_YN
    , NULL AS RPL_CFM_DT
FROM old_cat_productreview A, tmp_tb_goods B
WHERE A.ProductNo = B.PRODUCT_NO
    AND IssueTypeCd = '구매확정'
    AND Contents IS NOT NULL
; -- 45초

-- 포토(프리미엄) 상품평 (상품평은 게시, 포토는 게시안함 은 일반 상품평으로!!)
UPDATE tb_review X
    SET REVIEW_GB = 'P'
        , GIVE_DUE_PNT = GIVE_DUE_PNT + (SELECT SUM(IF(Point IS NULL, 0, Point)) FROM old_cat_photoproductreview P WHERE P.ReviewNo = X.REVIEW_SQ)
WHERE EXISTS(
    SELECT 1
    FROM old_cat_photoproductreview A
    WHERE A.ReviewNo = X.REVIEW_SQ
        AND A.StatusCd = '게시'
)
; -- 6초

SELECT MAX(REVIEW_SQ) FROM tb_review;
ALTER TABLE tb_review AUTO_INCREMENT = 4956516;

-- 포토 첨부파일
TRUNCATE TABLE tb_review_attach;

ALTER TABLE tb_review_attach AUTO_INCREMENT = 1;

INSERT INTO tb_review_attach
SELECT
    AttachNo AS RV_ATC_SQ
    , REVIEW_SQ
    , 'I' AS FILE_GB
    , REPLACE(AttachUrl, '/Upload', '') AS ORG_FILE_NM
    , REPLACE(AttachUrl, '/Upload', '') AS SYS_FILE_NM
    , NULL AS KMC_KEY
    , NULL AS KUF_KEY
    , 'N' AS DEL_YN
    , REG_NO
    , REG_DT
    , UPD_NO
    , UPD_DT
FROM old_cat_photoproductreviewattach A, old_cat_photoproductreview B, tb_review C
WHERE A.PhotoReviewNo = B.PhotoReviewNo
    AND B.ReviewNo = C.REVIEW_SQ
; -- 5초

SELECT MAX(RV_ATC_SQ) FROM tb_review_attach;
ALTER TABLE tb_review_attach AUTO_INCREMENT = 224568;

-- 상품문의
TRUNCATE TABLE tb_counsel;

ALTER TABLE tb_counsel AUTO_INCREMENT = 1;

INSERT INTO tb_counsel
SELECT
    COUNSEL_SQ     -- 상담일련번호
  , SITE_CD        -- 사이트코드(공통코드G000)
  , COUNSEL_CLSF   -- 상담분류코드(공통코드G059)
  , COUNSEL_DCLSF
  , CUST_NO
  , CELL_PHNNO
  , EMAIL
  , EMAIL_REQ_YN
  , EMAIL_SEND_YN
  , SMS_REQ_YN
  , SMS_SEND_YN
  , REL_ORD_NO
  , REL_GOODS_CD
  , COUNSEL_TYPE   -- 문의타입(C:1:1문의 / G:상품문의)
  , QUEST_TITLE
  , QUEST_CONTENT
  , QUEST_DT
  , ORG_FILE_NM1
  , SYS_FILE_NM1
  , ORG_FILE_NM2
  , SYS_FILE_NM2
  , ANS_STAT       -- 답변상태(공통코드G060)
  , ANS_TRANS_YN   -- 답변의뢰여부(Y:입점업체에 답변 의뢰)
  , ANS_COMP_CD
  , ANS_TRANS_NO
  , ANS_TRANS_DT
  , ASSIGNED_CS_NO -- 할당된CS담당자번호(사용자)
  , ASSIGNED_YMD
  , ASSIGNED_HMS
  , ANS_TITLE      -- 답변제목
  , ANS_CONTENT    -- 답변내용
  , ANS_NO         -- 답변자번호(사용자)
  , ANS_DT         -- 답변일시
  , SECRET_YN
  , DEL_YN
  , REG_NO
  , REG_DT
  , UPD_NO
  , UPD_DT
FROM (
         SELECT RANK() over (PARTITION BY A.InquireNo ORDER BY B.ResponseNo DESC) AS RNK
              , A.InquireNo                                                         AS COUNSEL_SQ     -- 상담일련번호
              , 'G000_10'                                                           AS SITE_CD        -- 사이트코드(공통코드G000)
              , 'G596'                                                              AS COUNSEL_CLSF   -- 상담분류코드(공통코드G059)
              , NULL                                                                AS COUNSEL_DCLSF
              , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId) IS NULL, 0,
                   (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId)) AS CUST_NO
              , A.CellNum                                                           AS CELL_PHNNO
              , A.Email                                                             AS EMAIL
              , 'N'                                                                 AS EMAIL_REQ_YN
              , 'N'                                                                 AS EMAIL_SEND_YN
              , IF(A.IsReceiveSMS = 'True', 'Y', 'N')                               AS SMS_REQ_YN
              , IF(A.IsReceiveSMS = 'True', 'Y', 'N')                               AS SMS_SEND_YN
              , NULL                                                                AS REL_ORD_NO
              , (SELECT GOODS_CD FROM tmp_tb_goods X WHERE X.PRODUCT_NO = A.ProductNo) AS REL_GOODS_CD
              , 'G'                                                                 AS COUNSEL_TYPE   -- 문의타입(C:1:1문의 / G:상품문의)
              , A.Title                                                             AS QUEST_TITLE
              , A.Contents                                                          AS QUEST_CONTENT
              , A.Date_FirstCreated                                                 AS QUEST_DT
              , NULL                                                                AS ORG_FILE_NM1
              , NULL                                                                AS SYS_FILE_NM1
              , NULL                                                                AS ORG_FILE_NM2
              , NULL                                                                AS SYS_FILE_NM2
              , CASE
                    WHEN A.StatusCd = '답변대기' THEN 'G060_10'
                    WHEN A.StatusCd = '답변완료' THEN 'G060_20'
                    ELSE 'G060_30' END                                              AS ANS_STAT       -- 답변상태(공통코드G060)
              , 'N'                                                                 AS ANS_TRANS_YN   -- 답변의뢰여부(Y:입점업체에 답변 의뢰)
              , NULL                                                                AS ANS_COMP_CD
              , NULL                                                                AS ANS_TRANS_NO
              , NULL                                                                AS ANS_TRANS_DT
              , NULL                                                                AS ASSIGNED_CS_NO -- 할당된CS담당자번호(사용자)
              , NULL                                                                AS ASSIGNED_YMD
              , NULL                                                                AS ASSIGNED_HMS
              , B.Title                                                             AS ANS_TITLE      -- 답변제목
              , B.Contents                                                          AS ANS_CONTENT    -- 답변내용
              , 0                                                                   AS ANS_NO         -- 답변자번호(사용자)
              , B.Date_LastModified                                                 AS ANS_DT         -- 답변일시
              , CASE WHEN B.OpenedTypeCd = '공개' THEN 'N' ELSE 'Y' END               AS SECRET_YN
              , CASE WHEN A.DeletedCd = '삭제' THEN 'Y' ELSE 'N' END                  AS DEL_YN
              , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId) IS NULL, 2,
                   (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId)) AS REG_NO
              , A.Date_FirstCreated                                                 AS REG_DT
              , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId) IS NULL, 2,
                   (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.LoginId)) AS UPD_NO
              , IF(B.Date_LastModified IS NULL, A.Date_LastModified, B.Date_LastModified)   AS UPD_DT
         FROM old_sys_productinquiry A
            LEFT OUTER JOIN
              old_sys_productinquiryresponse B
            ON A.InquireNo = B.InquireNo
     ) A
WHERE A.RNK = 1
; -- 11초

INSERT INTO tb_counsel
SELECT
    A.CounselNo                                                         AS COUNSEL_SQ     -- 상담일련번호
  , 'G000_10'                                                           AS SITE_CD        -- 사이트코드(공통코드G000)
  , CASE WHEN A.CounselTypeCd = '교환문의' THEN 'G594'
      WHEN A.CounselTypeCd IN ('반품문의', '취소문의', '환불문의') THEN 'G595'
      WHEN A.CounselTypeCd = '배송문의' THEN 'G593'
      WHEN A.CounselTypeCd IN ('주문/결제문의', '단체주문 문의') THEN 'G592'
      WHEN A.CounselTypeCd IN ('쿠폰/아이머니/아이포인트', '서비스/이벤트 문의', '쿠폰/YES포인트') THEN 'G597'
      WHEN A.CounselTypeCd = '회원문의' THEN 'G591'
      WHEN A.CounselTypeCd = '상품문의' THEN 'G596'
      ELSE 'G599'
      END AS COUNSEL_CLSF   -- 상담분류코드(공통코드G059)
  , NULL                                                                AS COUNSEL_DCLSF
  , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy) IS NULL, 0,
       (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy)) AS CUST_NO
  , A.ReplySMS                                                           AS CELL_PHNNO
  , A.ReplyEmail                                                             AS EMAIL
  , IF(A.IsReplyEmail = 'True', 'Y', 'N')                               AS EMAIL_REQ_YN
  , IF(A.IsReplyEmail = 'True', 'Y', 'N')                               AS EMAIL_SEND_YN
  , IF(A.IsReplySMS = 'True', 'Y', 'N')                               AS SMS_REQ_YN
  , IF(A.IsReplySMS = 'True', 'Y', 'N')                               AS SMS_SEND_YN
  , IF(A.OrderNo = 0, NULL, A.OrderNo)                                   AS REL_ORD_NO
  , NULL                                                                AS REL_GOODS_CD
  , 'C'                                                                 AS COUNSEL_TYPE   -- 문의타입(C:1:1문의 / G:상품문의)
  , A.Title                                                             AS QUEST_TITLE
  , IF(A.Contents IS NULL, '내용없음', A.Contents)                       AS QUEST_CONTENT
  , A.DateCreated                                                 AS QUEST_DT
  , NULL                                                                AS ORG_FILE_NM1
  , NULL                                                                AS SYS_FILE_NM1
  , NULL                                                                AS ORG_FILE_NM2
  , NULL                                                                AS SYS_FILE_NM2
  , CASE
        WHEN A.StatusCd = '접수' THEN 'G060_10'
        WHEN A.StatusCd = '답변완료' THEN 'G060_20'
        ELSE 'G060_30' END                                              AS ANS_STAT       -- 답변상태(공통코드G060)
  , 'N'                                                                 AS ANS_TRANS_YN   -- 답변의뢰여부(Y:입점업체에 답변 의뢰)
  , NULL                                                                AS ANS_COMP_CD
  , NULL                                                                AS ANS_TRANS_NO
  , NULL                                                                AS ANS_TRANS_DT
  , NULL                                                                AS ASSIGNED_CS_NO -- 할당된CS담당자번호(사용자)
  , NULL                                                                AS ASSIGNED_YMD
  , NULL                                                                AS ASSIGNED_HMS
  , NULL                                                             AS ANS_TITLE      -- 답변제목
  , NULL                                                          AS ANS_CONTENT    -- 답변내용
  , 0                                                                AS ANS_NO         -- 답변자번호(사용자)
  , NULL                                                 AS ANS_DT         -- 답변일시
  , 'N'               AS SECRET_YN
  , CASE WHEN A.StatusCd = '삭제' THEN 'Y' ELSE 'N' END                  AS DEL_YN
  , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy) IS NULL, 2,
       (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy)) AS REG_NO
  , A.DateCreated                                                 AS REG_DT
  , IF((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy) IS NULL, 2,
       (SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = A.UserIdCreatedBy)) AS UPD_NO
  , A.DateReply   AS UPD_DT
FROM old_sys_mantomancounsel A
WHERE CounselNo = PCounselNo
; -- 32초

UPDATE tb_counsel A
    SET ORG_FILE_NM1 = (SELECT AttachUrl  FROM old_sys_mantomancounselattach X WHERE X.CounselNo = A.COUNSEL_SQ ORDER BY AttachNo LIMIT 1)
        , SYS_FILE_NM1 = (SELECT AttachUrl  FROM old_sys_mantomancounselattach X WHERE X.CounselNo = A.COUNSEL_SQ ORDER BY AttachNo LIMIT 1)
        , ORG_FILE_NM2 = (SELECT AttachUrl  FROM old_sys_mantomancounselattach X WHERE X.CounselNo = A.COUNSEL_SQ ORDER BY AttachNo LIMIT 1, 1)
        , SYS_FILE_NM2 = (SELECT AttachUrl  FROM old_sys_mantomancounselattach X WHERE X.CounselNo = A.COUNSEL_SQ ORDER BY AttachNo LIMIT 1, 1)
        , ANS_TITLE = (SELECT Title FROM old_sys_mantomancounsel X WHERE X.PCounselNo = A.COUNSEL_SQ ORDER BY CounselNo DESC LIMIT 1)
        , ANS_CONTENT = (SELECT Contents FROM old_sys_mantomancounsel X WHERE X.PCounselNo = A.COUNSEL_SQ ORDER BY CounselNo DESC LIMIT 1)
        , ANS_DT = (SELECT DateReply FROM old_sys_mantomancounsel X WHERE X.CounselNo = A.COUNSEL_SQ)
WHERE A.COUNSEL_CLSF != 'G596'
; -- 1분2초

SELECT MAX(COUNSEL_SQ) FROM tb_counsel;
ALTER TABLE tb_counsel AUTO_INCREMENT = 3010932;

-- 주문정보
/*
DELETE FROM old_ord_orderitem A
WHERE EXISTS(
    SELECT 1
    FROM old_ord_order B
    WHERE B.OrderGUID = A.OrderGUID
        AND OrderStatusCd = '주문파기'
)
;

DELETE FROM old_ord_order WHERE OrderStatusCd = '주문파기';
SELECT COUNT(1) FROM old_ord_order WHERE OrderStatusCd = '주문파기';
*/
TRUNCATE TABLE TB_ORDER;

INSERT INTO tb_order
SELECT OrderNo AS ORD_NO  -- 주문번호
    -- , IF(ChannelCd IN('ist_auction','ist_faplus','ist_gmarket','ist_gsshop','ist_halfclub','ist_hmall','ist_ssg','ist_st11th','ist_timon','ist_wemape','ist_wemape2'), 'G011_20', 'G011_10') AS MALL_GB
    , IF((SELECT COUNT(1) FROM mig_extmall X WHERE X.CHANNEL_CD = A.ChannelCd) > 0, 'G011_20', 'G011_10') AS MALL_GB
    , DateOrdered AS ORD_DT
    , (SELECT DatePaid FROM old_ord_payment X WHERE OrderGUID = A.OrderGUID AND PaymentStatusCd = '결제완료' ORDER BY PaymentNo ASC LIMIT 1) AS PAY_DT
    , CASE WHEN CustomerTypeCd = '비회원' THEN 0 ELSE (SELECT CUST_NO FROM TB_CUSTOMER X WHERE X.CUST_ID = A.LoginId) END AS CUST_NO
    , CustomerName AS ORD_NM
    , A.CellNum AS ORD_PHNNO
    , A.TelNum AS ORD_TELNO
    , CustomerEmail AS ORD_EMAIL
    , 'G000_10' AS SITE_CD
    , NULL AS NPAY_ORD_NO
    , 'P' AS FRONT_GB
    , 'Y' AS DISP_YN
    , B.CUST_NO AS REG_NO
    , DateOrdered AS REG_DT
    , B.CUST_NO AS UPD_NO
    , DateOrdered AS REG_DT
FROM old_ord_order A, tb_customer B
WHERE A.LoginId = B.CUST_ID
    AND OrderStatusCd != '주문파기'
; -- 8분17초

-- 비회원 주문정보
INSERT INTO tb_order
SELECT OrderNo AS ORD_NO  -- 주문번호
    , IF((SELECT COUNT(1) FROM mig_extmall X WHERE X.CHANNEL_CD = A.ChannelCd) > 0, 'G011_20', 'G011_10') AS MALL_GB
    , DateOrdered AS ORD_DT
    , (SELECT DatePaid FROM old_ord_payment X WHERE OrderGUID = A.OrderGUID AND PaymentStatusCd = '결제완료' ORDER BY PaymentNo ASC LIMIT 1) AS PAY_DT
    , 0 AS CUST_NO
    , CustomerName AS ORD_NM
    , A.CellNum AS ORD_PHNNO
    , A.TelNum AS ORD_TELNO
    , CustomerEmail AS ORD_EMAIL
    , 'G000_10' AS SITE_CD
    , NULL AS NPAY_ORD_NO
    , 'P' AS FRONT_GB
    , 'Y' AS DISP_YN
    , 0 AS REG_NO
    , DateOrdered AS REG_DT
    , 0 AS UPD_NO
    , DateOrdered AS REG_DT
FROM old_ord_order A
WHERE CustomerTypeCd = '비회원'
    AND OrderStatusCd != '주문파기'
; -- 11분4초

SELECT MAX(ORD_NO) FROM tb_order;
ALTER TABLE tb_order AUTO_INCREMENT = 19713624;

-- 주문메모
TRUNCATE TABLE tb_order_memo;
ALTER TABLE tb_order_memo AUTO_INCREMENT = 1;

INSERT INTO tb_order_memo (ORD_NO, MEMO, ORG_FILE_NM, SYS_FILE_NM, DEL_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    B.ORD_NO
    , AdminMemo AS MEMO
    , NULL AS ORG_FILE_NM
    , NULL AS SYS_FILE_NM
    , 'N' AS DEL_YN
    , 99999 AS REG_NO
    , NOW() AS REG_DT
    , 99999 AS UPD_NO
    , NOW() AS UPD_DT
FROM old_ord_order A, tb_order B
WHERE A.OrderNo = B.ORD_NO
    AND OrderStatusCd != '주문파기'
    AND AdminMemo IS NOT NULL
    AND AdminMemo != ''
; -- 6초

SELECT MAX(ORD_MEMO_SQ) FROM tb_order_memo;
ALTER TABLE tb_order_memo AUTO_INCREMENT = 14969;

-- 보증보험
TRUNCATE TABLE TB_INSURANCE;

INSERT INTO TB_INSURANCE
SELECT
    Oid AS ORD_NO
    , IFNULL((SELECT CUST_NO FROM tb_customer X WHERE X.CUST_ID = LoginID), 0) AS CUST_NO
    , Pid AS BIRTH_GEN
    , 'Y' AS IF_YN
    , result_msg AS INS_NO
    , result_code AS RESULT_CD
    , NULL AS RESULT_MSG
    , DateCreated AS REG_DT
FROM old_ord_insurance
WHERE result_code = 0
;

-- 포인트
TRUNCATE TABLE tb_cust_point;

ALTER TABLE tb_cust_point AUTO_INCREMENT = 1;

INSERT INTO tb_cust_point (CUST_NO, GV_PNT_AMT, US_PNT_AMT, RM_PNT_AMT, EXP_BE_DT, EXP_CMP_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    CUST_NO
    , GV_PNT_AMT
    , US_PNT_AMT
    , RM_PNT_AMT
    , DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59'), INTERVAL 2 YEAR) AS EXP_BE_DT
    , NULL AS EXP_CMP_DT
    , 99999 AS REG_NO
    , NOW() AS REG_DT
    , 99999 AS UPD_NO
    , NOW() AS UPD_DT
FROM (
    SELECT
        Y.CUST_NO
        -- , (SELECT SUM(Mileage) FROM old_prf_membermileage C WHERE C.MemberGUID = Y.MEMBER_GUID)
        -- + (SELECT SUM(Point) FROM old_prf_memberpoint C WHERE C.MemberGUID = Y.MEMBER_GUID) AS GV_PNT_AMT
        , SUM(Remains) AS GV_PNT_AMT
        , 0 AS US_PNT_AMT
        , SUM(Remains) AS RM_PNT_AMT
    FROM tb_customer Y
    , (
        SELECT MemberGUID
            , Remains
        FROM (
            SELECT MemberGUID, Remains
                , RANK() over (PARTITION BY MemberGUID ORDER BY ItemNo DESC) AS RNK
            FROM old_prf_membermileage A
        ) X
        WHERE RNK = 1
        UNION ALL
        SELECT MemberGUID
            , Remains
        FROM (
            SELECT MemberGUID, Remains
                , RANK() over (PARTITION BY MemberGUID ORDER BY ItemNo DESC) AS RNK
            FROM old_prf_memberpoint A
        ) X
        WHERE RNK = 1
    ) Z
    WHERE Y.MEMBER_GUID = Z.MemberGUID
    GROUP BY CUST_NO
) D
;

SELECT MAX(CUST_PNT_SQ) FROM tb_cust_point;
ALTER TABLE tb_cust_point AUTO_INCREMENT = 1307415;

TRUNCATE TABLE tb_cust_point_hst;

ALTER TABLE tb_cust_point_hst AUTO_INCREMENT = 1;

INSERT INTO tb_cust_point_hst (CUST_NO, OCCUR_GB, OCCUR_DTL_DESC, PNT_AMT, CUST_PNT_SQ, ORD_NO, ORD_DTL_NO, REVIEW_SQ, SWITCH_DUE_DT, PNT_UPLOAD_STAT, PNT_UPLOAD_DT, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT *
FROM (
         SELECT CUST_NO
              , CASE
                    WHEN IssueTypeCd = '환전' THEN 'G069_12'
                    WHEN IssueTypeCd = '지급' THEN 'G069_40'
                    WHEN IssueTypeCd = '환불' THEN 'G069_41'
                    WHEN IssueTypeCd = '주문' THEN 'G069_12'
                    ELSE 'G069_90' END                                                AS OCCUR_GB
              , AdminMemo                                                             AS OCCUR_DTL_DESC
              , Mileage                                                               AS PNT_AMT
              , (SELECT CUST_PNT_SQ FROM tb_cust_point X WHERE X.CUST_NO = B.CUST_NO) AS CUST_PNT_SQ
              , OrderNo                                                               AS ORD_NO
              , NULL                                                                  AS ORD_DTL_NO
              , NULL                                                                  AS REVIEW_SQ
              , DateCreated                                                           AS SWITCH_DUE_DT
              , 'G070_30'                                                             AS PNT_UPLOAD_STAT
              , DateCreated                                                           AS PNT_UPLOAD_DT
              , 99999                                                                     AS REG_NO
              , DateCreated                                                           AS REG_DT
              , 99999                                                                     AS UPD_NO
              , DateCreated                                                           AS UPD_DT
         FROM old_prf_membermileage A,
              tb_customer B
         WHERE A.MemberGUID = B.MEMBER_GUID
        UNION ALL
         SELECT
            CUST_NO
            , CASE WHEN IssueTypeCd = '주문' THEN 'G069_12'
                WHEN IssueTypeCd = '멤버쉽혜택' THEN 'G069_12'
                WHEN IssueTypeCd = '환전' THEN 'G069_90'
                WHEN IssueTypeCd = '주문예외' THEN 'G069_13'
                WHEN IssueTypeCd = '상품평' AND Point >= 0 THEN 'G069_20'
                WHEN IssueTypeCd = '상품평' AND Point < 0 THEN 'G069_21'
                WHEN IssueTypeCd = '알림 이벤트' THEN 'G069_30'
                WHEN IssueTypeCd = '출석체크' THEN 'G069_37'
                WHEN IssueTypeCd = '포토상품평' AND Point >= 0 THEN 'G069_20'
                WHEN IssueTypeCd = '포토상품평' AND Point < 0 THEN 'G069_21'
                WHEN IssueTypeCd = '이벤트' THEN 'G069_30'
                WHEN IssueTypeCd = '우수상품평' AND Point >= 0 THEN 'G069_20'
                WHEN IssueTypeCd = '우수상품평' AND Point < 0 THEN 'G069_21'
                WHEN IssueTypeCd = '이벤트참여' THEN 'G069_30'
                WHEN IssueTypeCd = '총알배송지연보상' THEN 'G069_47'
                WHEN IssueTypeCd = '추가적립(프로모션)' THEN 'G069_30'
                WHEN IssueTypeCd = '기간소멸' THEN 'G069_99'
            ELSE 'G069_90' END AS OCCUR_GB
            , AdminMemo AS OCCUR_DTL_DESC
            , Point AS PNT_AMT
            , (SELECT CUST_PNT_SQ FROM tb_cust_point X WHERE X.CUST_NO = B.CUST_NO) AS CUST_PNT_SQ
            , OrderNo AS ORD_NO
            , NULL AS ORD_DTL_NO
            , NULL AS REVIEW_SQ
            , DateCreated AS SWITCH_DUE_DT
            , 'G070_30' AS PNT_UPLOAD_STAT
            , DateCreated AS PNT_UPLOAD_DT
            , 99999 AS REG_NO
            , DateCreated AS REG_DT
            , 99999 AS UPD_NO
            , DateCreated AS UPD_DT
        FROM old_prf_memberpoint A, tb_customer B
        WHERE A.MemberGUID = B.MEMBER_GUID
) BB
ORDER BY REG_DT
;

SELECT MAX(PNT_HST_SQ) FROM tb_cust_point_hst;
ALTER TABLE tb_cust_point_hst AUTO_INCREMENT = 2245229;

/*
SELECT DISTINCT IssueTypeCd FROM old_prf_memberpoint;
주문 G069_12
멤버쉽혜택 G069_12
환전 G069_90
주문예외 G069_13
상품평 G069_20 Point >= 0 , G069_21 Point < 0
알림 이벤트 G069_30
출석체크 G069_37
포토상품평 G069_20 Point >= 0 , G069_21 Point < 0
이벤트 G069_30
우수상품평 G069_20 Point >= 0 , G069_21 Point < 0
이벤트참여 G069_30
총알배송지연보상 G069_47
추가적립(프로모션) G069_30
기간소멸 G069_99

SELECT * FROM tb_common_code WHERE CD_GB = 'G069';
G069_12	예정포인트적립
G069_13	예정포인트적립취소
G069_20	상품평포인트지급
G069_21	상품평포인트지급취소
G069_30	온라인이벤트
G069_31	회원가입축하
G069_32	생일축하
G069_33	재방문
G069_34	패밀리세일
G069_35	APP다운로드
G069_36	사은품구매
G069_37	출석체크
G069_40	구매포인트사용
G069_41	구매포인트사용취소
G069_42	사은품포인트사용
G069_43	사은품포인트사용취소
G069_44	관리자지급
G069_45	관리자취소
G069_46	적립취소
G069_90	기타
G069_99	포인트소멸
*/

-- 상품권
TRUNCATE TABLE tb_cust_giftcard;

ALTER TABLE tb_cust_giftcard AUTO_INCREMENT = 1;

INSERT INTO tb_cust_giftcard
SELECT
    TokenNo AS CUST_GFCD_SQ
    , CUST_NO
    , TokenSerial AS GFCD_NO
    , TokenName AS GFCD_NM
    , Amount AS CHG_GFCD_AMT
    , UseAmount AS US_GFCD_AMT
    , TotalAmount AS RM_GFCD_AMT
    , REPLACE(DateIssueStart, '-', '') AS RM_GFCD_AMT
    , REPLACE(DateIssueEnd, '-', '') AS USE_EXP_DATE
    , REG_NO
    , DateRegister AS REG_DT
    , REG_NO AS UPD_NO
    , DateModify AS UPD_DT
FROM old_prf_token A, tb_customer B
WHERE A.LoginId = B.CUST_ID
ORDER BY A.TokenNo
;

SELECT MAX(CUST_GFCD_SQ) FROM tb_cust_giftcard;
ALTER TABLE tb_cust_giftcard AUTO_INCREMENT = 23631;

TRUNCATE TABLE tb_cust_giftcard_hst;

INSERT INTO tb_cust_giftcard_hst
SELECT
    TokenIssueNo AS GFCD_HST_SQ
    , CUST_NO
    , CONCAT('G074_1', UseTypeCd) AS OCCUR_GB
    , AdminMemo AS OCCUR_DTL_DESC
    , CASE WHEN UseTypeCd = '1' THEN SourceAmt
        WHEN UseTypeCd = '2' THEN UseAmt * -1
        WHEN UseTypeCd = '3' THEN UseAmt
        WHEN UseTypeCd = '4' THEN UseAmt * -1
        WHEN UseTypeCd = '5' THEN UseAmt * -1
        WHEN UseTypeCd = '6' THEN UseAmt * -1 -- ???
        WHEN UseTypeCd = '7' THEN 0
        WHEN UseTypeCd = '8' THEN UseAmt
        WHEN UseTypeCd = '9' THEN UseAmt * -1
    ELSE 0 END AS GFCD_AMT
    , A.TokenNo
    , A.OrderId
    , NULL AS ORD_DTL_NO
    , CUST_NO
    , DateUse AS REG_DT
    , CUST_NO
    , DateUse AS UPD_DT
FROM old_prf_tokenissue A, tb_cust_giftcard B
WHERE A.TokenNo = B.CUST_GFCD_SQ
ORDER BY A.TokenIssueNo
;

SELECT MAX(GFCD_HST_SQ) FROM tb_cust_giftcard_hst;
ALTER TABLE tb_cust_giftcard_hst AUTO_INCREMENT = 68177616;

--        UseTypeCd='1' -- 상품권 등록
--        UseTypeCd='2' -- 주문에 의한 상품권 결제
--        UseTypeCd='3' -- 주문 취소로 인한 상품권 사용 환불
--        UseTypeCd='4' -- 상품권 유효기간 종료
--        UseTypeCd='5' -- 상품권 파기
--        UseTypeCd='6' -- 전액 현금 전환 환불
--        UseTypeCd='7' -- 품절 환불로 인한 유효기간 연장
--        UseTypeCd='8' -- 오환불로 인한 금액 추가
--        UseTypeCd='9' -- 오환불로 인한 금액 차감

-- SNS회원
-- TB_CUSTOMER.SNS_TYPE
-- 네이버 NV
-- YES24 YS
TRUNCATE TABLE TB_CUSTOMER_SNS;

INSERT INTO TB_CUSTOMER_SNS
SELECT
    CUST_NO
    , CASE WHEN Type = '1' THEN 'NV'
        WHEN Type = '6' THEN 'YS'
        ELSE NULL END AS SNS_TYPE
    , ServiceId AS SNS_ID
    , CUST_NO
    , MIN(DateRegister) AS REG_DT
FROM old_prf_membersns A, tb_customer B
WHERE A.MemberGUID = B.MEMBER_GUID
    AND ServiceId IS NOT NULL
GROUP BY CUST_NO, Type, ServiceId
; -- 15초

-- 환불계좌
TRUNCATE TABLE tb_cust_account;

ALTER TABLE tb_cust_account AUTO_INCREMENT = 1;

INSERT INTO tb_cust_account (CUST_NO, BANK_CD, ACCOUNT_NO, ACCOUNT_NM, DEFAULT_YN, DEL_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT
    CUST_NO
    , CASE WHEN BankId_Code1 = '26' THEN '21'
        ELSE BankId_Code1
    END AS BANK_CD
    , FN_ENC_AES(AccountId_No) AS ACCOUNT_NO
    , AccountUser_Name AS ACCOUNT_NM
    , 'Y' AS DEFAULT_YN
    , 'N' AS DEL_YN
    , CUST_NO AS REG_NO
    , Date_FirstCreated AS REG_DT
    , CUST_NO AS UPD_NO
    , Date_LastModified AS UPD_DT
FROM old_prf_memberrefundaccount A, tb_customer B
WHERE A.MemberGuid = B.MEMBER_GUID
    AND BankId_Code1 != ''
; -- 6초

SELECT MAX(CUST_ACCOUNT_SQ) FROM tb_cust_account;
ALTER TABLE tb_cust_account AUTO_INCREMENT = 203385;

-- 암호화 후!!!!

-- 휴면처리
TRUNCATE TABLE tb_dormant_cust;

INSERT INTO tb_dormant_cust
SELECT
      CUST_NO
    , B.CUST_ID
    , B.CUST_NM AS CUST_NM -- 암호화
    , Password AS PASSWD
    , B.BIRTH_YMD AS BIRTH_YMD -- 암호화
    , B.SEX_GB -- 암호화
    , B.CELL_PHNNO -- 암호화
    , APP_AGREE_YN
    , APP_AGREE_DT
    , SMS_AGREE_YN
    , SMS_AGREE_DT
    , B.EMAIL -- 암호화
    , EMAIL_AGREE_YN
    , EMAIL_AGREE_DT
    , MK_AGREE_YN
    , MK_AGREE_DT
    , B.HOME_ZIPCODE -- 암호화
    , B.HOME_BASE_ADDR -- 자택기본주소_암호화
    , B.HOME_DTL_ADDR -- 자택상세주소_암호화
    , 'G000_10' AS SITE_CD
    , IF((SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) IS NULL, 'P', (SELECT FRONT_GB FROM MIG_AF WHERE ASIS_CD = A.RegisterPath)) AS FRONT_GB -- 프론트구분(P:PC, M:모바일, A:앱)
    , (SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.RegisterPath) AS AF_LINK_CD -- 제휴링크코드
    , 'G100_10' AS CUST_GB -- 회원구분(공통코드G100)
    , 'G110_10' AS CUST_GRADE -- 회원등급(공통코드G110)
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS JOIN_DT  -- 가입일시
    , IF(Nationality = '외국인', 'Y', 'N') AS FOREIGNER_YN -- 외국인여부(외국인:Y)
    , 'G104_20' AS CUST_STAT -- 고객상태(공통코드G104)
    , NULL AS PASSWD_CHG_DT -- 비밀번호변경일시
    , 'N' AS TEMP_PASSWD_YN -- 임시비밀번호여부
    , DateLastVisited AS LOGIN_LDT -- 최종로그인일시
    , A.CI -- 인증CI
    , NULL AS AUTH_DT -- 인증일시
    , NULL AS SNS_TYPE
    , NULL AS MANAGED_RSN -- 관리대상지정사유(공통코드G103)
    , NULL AS MANAGED_DTL_RSN -- 관리대상지정상세사유
    , NULL AS MANAGED_DT -- 관리대상지정일시
    , NULL AS SECEDE_RSN -- 탈퇴사유(공통코드G102)
    , NULL AS SECEDE_DTL_RSN -- 탈퇴상세사유
    , NULL AS SECEDE_DT-- 탈퇴일시
	, BIRTH_MM
    , 99999 AS REG_NO -- 배치 등록자번호로
    , IF(DateRegister IS NULL, DateLastModified, DateRegister) AS REG_DT -- 등록일시
    , 99999 AS UPD_NO -- 배치 수정자번호로
    , DateLastModified AS UPD_DT-- 수정일시
    , DateDormant AS DORMANT_DT
FROM old_prf_memberdormant A, tb_customer B
WHERE A.LoginId = B.CUST_ID
;

UPDATE tb_customer A, tb_dormant_cust B
SET -- A.CUST_NM = '/Sj2xBNOjrWVsTN8+IDeEg=='
	 , A.CUST_NM = 'AE53EBFF769245B6672C3942F56CEC39' -- 휴면
     , A.BIRTH_YMD = NULL
     , A.SEX_GB = NULL
     , A.CELL_PHNNO = NULL
     , A.SMS_AGREE_YN = 'N'
     , A.SMS_AGREE_DT = NULL
     , A.EMAIL = NULL
     , A.EMAIL_AGREE_YN = 'N'
     , A.EMAIL_AGREE_DT = NULL
     , A.APP_AGREE_YN = 'N'
     , A.APP_AGREE_DT = NULL
     , A.MK_AGREE_YN = 'N'
     , A.MK_AGREE_DT = NULL
     , A.HOME_ZIPCODE = NULL
     , A.HOME_BASE_ADDR = NULL
     , A.HOME_DTL_ADDR = NULL
     , A.CUST_STAT = 'G104_20'
     , A.RM_DORMANT_DT = (
            SELECT DateDormantUnlock
            FROM old_prf_memberdormantunlocklog X
            WHERE X.LoginId = A.CUST_ID
        )
WHERE A.CUST_NO = B.CUST_NO
;

-- 탈퇴처리
TRUNCATE TABLE TB_SECEDE_CUST;

INSERT INTO TB_SECEDE_CUST
SELECT
    CUST_NO
    , CUST_ID
    , CUST_NM -- 암호화
    , PASSWD
    , BIRTH_YMD -- 암호화
    , SEX_GB -- 암호화
    , CELL_PHNNO -- 암호화
    , APP_AGREE_YN
    , APP_AGREE_DT
    , SMS_AGREE_YN
    , SMS_AGREE_DT
    , B.EMAIL -- 암호화
    , EMAIL_AGREE_YN
    , EMAIL_AGREE_DT
    , MK_AGREE_YN
    , MK_AGREE_DT
    , HOME_ZIPCODE -- 암호화
    , HOME_BASE_ADDR -- 자택기본주소_암호화
    , HOME_DTL_ADDR -- 자택상세주소_암호화
    , SITE_CD
    , FRONT_GB -- 프론트구분(P:PC, M:모바일, A:앱)
    , AF_LINK_CD -- 제휴링크코드
    , CUST_GB -- 회원구분(공통코드G100)
    , CUST_GRADE -- 회원등급(공통코드G110)
    , JOIN_DT  -- 가입일시
    , FOREIGNER_YN -- 외국인여부(외국인:Y)
    , 'G104_30' AS CUST_STAT -- 고객상태(공통코드G104)
    , PASSWD_CHG_DT -- 비밀번호변경일시
    , TEMP_PASSWD_YN -- 임시비밀번호여부
    , LOGIN_LDT -- 최종로그인일시
    , CI -- 인증CI
    , AUTH_DT -- 인증일시
    , SNS_TYPE
    , NULL AS MANAGED_RSN -- 관리대상지정사유(공통코드G103)
    , NULL AS MANAGED_DTL_RSN -- 관리대상지정상세사유
    , NULL AS MANAGED_DT -- 관리대상지정일시
    , NULL AS SECEDE_RSN -- 탈퇴사유(공통코드G102)
    , WithdrawReason AS SECEDE_DTL_RSN -- 탈퇴상세사유
    , DateWithdraw AS SECEDE_DT-- 탈퇴일시
	, BIRTH_MM
    , B.CUST_NO AS REG_NO -- 등록자번호
    , DateWithdraw AS REG_DT -- 등록일시
    , B.CUST_NO AS UPD_NO -- 수정자번호
    , DateWithdraw AS UPD_DT-- 수정일시
FROM old_prf_memberwithdrawal A, tb_customer B
WHERE A.LoginId = B.CUST_ID
;

UPDATE tb_customer A, TB_SECEDE_CUST B
SET A.CUST_ID = CONCAT('secede_cust_',A.CUST_NO)
	 , A.CUST_NM = 'FB9B4C8F559D0E7DF22886C982A34A96' -- 탈퇴
     , A.BIRTH_YMD = NULL
     , A.SEX_GB = NULL
     , A.CELL_PHNNO = NULL
     , A.SMS_AGREE_YN = 'N'
     , A.SMS_AGREE_DT = NULL
     , A.EMAIL = NULL
     , A.EMAIL_AGREE_YN = 'N'
     , A.EMAIL_AGREE_DT = NULL
     , A.APP_AGREE_YN = 'N'
     , A.APP_AGREE_DT = NULL
     , A.MK_AGREE_YN = 'N'
     , A.MK_AGREE_DT = NULL
     , A.HOME_ZIPCODE = NULL
     , A.HOME_BASE_ADDR = NULL
     , A.HOME_DTL_ADDR = NULL
     , A.CUST_STAT = 'G104_30'
     -- , UPD_DT = NOW()
WHERE A.CUST_NO = B.CUST_NO
;