##### 회원, 주문 ##### -- 정상회원 TRUNCATE TABLE tb_customer; TRUNCATE TABLE TB_CUSTOMER_HST; 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, 'guest', '비회원', '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, BIRTH_MM, 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:앱) , IFNULL((SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.RegisterPath), 'AF001') 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, BIRTH_MM, 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:앱) , IFNULL((SELECT AF_LINK_CD FROM MIG_AF WHERE ASIS_CD = A.RegisterPath), 'AF001') 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 ORDER BY MEMBERGUID LIMIT 0, 500000 -- LIMIT 500000, 500000 -- LIMIT 1000000, 500000 -- LIMIT 1500000, 500000 -- LIMIT 2000000, 500000 ; -- 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, BIRTH_MM, 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) , NULL 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-- 탈퇴일시 , NULL 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초 */ -- 마케팅수신동의이력 TRUNCATE TABLE TB_CUST_MARKET_HST; 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 CUST_NO 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 = 19856387; -- 주문메모 -- 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 = 16389; -- 보증보험 TRUNCATE TABLE TB_INSURANCE; INSERT INTO TB_INSURANCE SELECT ORD_NO , CUST_NO , BIRTH_GEN , IF_YN , INS_NO , RESULT_CD , RESULT_MSG , REG_DT FROM ( 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 , ROW_NUMBER() OVER (PARTITION BY OID ORDER BY OID) AS RNK FROM old_ord_insurance WHERE result_code = 0 ) X WHERE RNK = 1 ; -- 포인트 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 Y.CUST_NO , (SELECT (POINT + MILEAGE) FROM OLD_PRF_MEMBER X WHERE X.MEMBERGUID = Y.MEMBER_GUID) AS GV_PNT_AMT , 0 AS US_PNT_AMT , (SELECT (POINT + MILEAGE) FROM OLD_PRF_MEMBER X WHERE X.MEMBERGUID = Y.MEMBER_GUID) AS 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 tb_customer Y WHERE Y.CUST_NO > 0 ; /* 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 AND Y.CUST_NO > 0 GROUP BY CUST_NO ) D ; */ SELECT MAX(CUST_PNT_SQ) FROM tb_cust_point; ALTER TABLE tb_cust_point AUTO_INCREMENT = 1307307; 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 = 2244858; /* 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 = 23632; 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 , NULL AS DELV_FEE_SQ , 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 = 203416; -- 휴면처리 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 ORDER BY A.MEMBERGUID LIMIT 0, 1000000 -- LIMIT 1000000, 1000000 -- LIMIT 2000000, 1000000 ; 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 ;