search

eunja511005 / AutoCoding

0 stars 0 forks source link

경기도 숙박업체 현황 메뉴 추가 #99

Open eunja511005 opened 12 months ago

eunja511005 commented 12 months ago

마스터 데이터 

INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41820', '가평군', 1);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41000', '경기도', 2);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41280', '고양시', 3);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41290', '과천시', 4);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41210', '광명시', 5);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41610', '광주시', 6);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41310', '구리시', 7);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41410', '군포시', 8);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41570', '김포시', 9);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41360', '남양주시', 10);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41250', '동두천시', 11);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41190', '부천시', 12);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41130', '성남시', 13);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41110', '수원시', 14);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41390', '시흥시', 15);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41270', '안산시', 16);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41550', '안성시', 17);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41170', '안양시', 18);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41630', '양주시', 19);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41830', '양평군', 20);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41670', '여주시', 21);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41800', '연천군', 22);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41370', '오산시', 23);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41460', '용인시', 24);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41430', '의왕시', 25);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41150', '의정부시', 26);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41500', '이천시', 27);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41480', '파주시', 28);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41220', '평택시', 29);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41650', '포천시', 30);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41450', '하남시', 31);
INSERT INTO COMMON_CODE(CODE_GROUP, CODE, VALUE, CODE_ORDER) VALUES('SIGUNCD', '41590', '화성시', 32);
eunja511005 commented 12 months ago

API 로그 테이블 추가

DROP TABLE zthh_apiHistory;
CREATE TABLE zthh_apiHistory (
    id VARCHAR2(255),
    SYSTEM_NAME VARCHAR2(200),
    API_NAME VARCHAR2(50),
    HTTP_METHOD VARCHAR2(30),
    REQ_DATA   VARCHAR2(4000),
    RES_DATA   VARCHAR2(4000),
    del_yn CHAR(1),
    create_id VARCHAR2(50),
    create_dt TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    update_id VARCHAR2(50),
    update_dt TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_zthh_apiHistory PRIMARY KEY (id)
);
eunja511005 commented 12 months ago

요청/응답 데이터 길이가 1000자 넘어가면 자르도록 수정, 테이블에 4000바이트지만 한글이 포함될수 있으므로 1000으로 제한

eunja511005 commented 12 months ago

데이터 이관

INSERT INTO WASADMIN.ZTHH_SYSTEMMASTER
(ID, SYSTEM_NAME, SYSTEM_DESCRIPTION, SYSTEM_ADMIN, HOMEPAGE, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT)
VALUES('systemMaster_c32ee4b4-b686-4f6f-ada3-06672335368f', 'Gyeonggi', '[경기데이터드림] 경기도가 보유한 공공데이터를 체계화하고 통합·개방하여 도민과 공유하고 민간의 활용 촉진과 개발자 맞춤형 서비스를 제공하기 위해 구축된 공공데이터 개방 포털입니다.', 'AI빅데이터산업과', 'https://data.gg.go.kr/portal/mainPage.do', 'N', 'autoCoding1', TIMESTAMP '2023-07-05 13:19:25.135245', 'autoCoding1', TIMESTAMP '2023-07-05 13:19:25.135245');
INSERT INTO WASADMIN.ZTHH_APIMASTER
(ID, API_NAME, API_DESCRIPTION, CALL_URL, DIRECTION, AUTHOR, CALL_MAX, HTTP_METHOD, LOG_YN, SYSTEM_NAME, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT)
VALUES('apiMaster_f78d0ea2-e59f-47f6-9b1f-e4af9e020f71', 'tourismStaying', '경기 데이터 드림에서 제공하는 숙박업체 현황 조회', 'https://openapi.gg.go.kr/TourismStaying', 'OUT', 'e748e858d7c1450993b6278875a3936b', 10000, 'GET', 'Y', 'Gyeonggi', 'N', 'autoCoding1', TIMESTAMP '2023-07-05 13:21:37.562704', 'autoCoding1', TIMESTAMP '2023-07-08 05:11:17.515593');
INSERT INTO WASADMIN.ZTHH_MENU
(ID, CATEGORY, MENU_LEVEL, MENU_ID, MENU_PATH, MENU_ICON, MENU_ORDER, PARENT_MENU_ID, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT, MENU_AUTH)
VALUES('menu_bb0d382f-7215-4bc5-8480-eaef251a1b33', 'interface', 2, 'tourismStaying', '/tourismStaying/list', 'N/A', 4, 'layout', 'N', NULL, TIMESTAMP '2023-07-06 13:48:13.275209', NULL, TIMESTAMP '2023-07-06 13:48:13.275209', 'ROLE_ANY');
INSERT INTO WASADMIN.ZTHH_MENUCONTROL
(ID, URL, "METHOD", ROLE_ID, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT, LOG_YN, LOG_DATA_YN)
VALUES('menuControl_9cdd6897-9fa5-485a-a319-92af3c0a8a33', '/tourismStaying/list', 'POST', 'ANY', 'N', 'autoCoding1', TIMESTAMP '2023-07-06 13:51:30.176203', 'autoCoding1', TIMESTAMP '2023-07-06 13:51:30.176203', 'Y', 'N');

INSERT INTO WASADMIN.ZTHH_MENUCONTROL
(ID, URL, "METHOD", ROLE_ID, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT, LOG_YN, LOG_DATA_YN)
VALUES('menuControl_89d7215d-95f5-4a8b-80b8-d9a967bf6119', '/tourismStaying/list', 'GET', 'ANY', 'N', 'autoCoding1', TIMESTAMP '2023-07-06 13:51:29.206955', 'autoCoding1', TIMESTAMP '2023-07-06 13:51:29.206955', 'Y', 'N');

INSERT INTO WASADMIN.ZTHH_MENUCONTROL
(ID, URL, "METHOD", ROLE_ID, DEL_YN, CREATE_ID, CREATE_DT, UPDATE_ID, UPDATE_DT, LOG_YN, LOG_DATA_YN)
VALUES('menuControl_edadbfda-0976-4652-8049-129523316050', '/commonCode/SIGUNCD', 'GET', 'ANY', 'N', 'anonymous', TIMESTAMP '2023-07-08 05:57:43.251315', 'anonymous', TIMESTAMP '2023-07-08 05:57:43.251315', 'Y', 'N');
eunja511005 commented 12 months ago

이관 확인 필요 테이블

SELECT * FROM COMMON_CODE cc ORDER BY CREATED_AT DESC;
SELECT * FROM ZTHH_SYSTEMMASTER zs ORDER BY CREATE_DT desc;
SELECT * FROM ZTHH_APIMASTER za ORDER BY CREATE_DT desc;
SELECT * FROM ZTHH_MENU zm  ORDER BY CREATE_DT desc;
SELECT * FROM ZTHH_MENUCONTROL zm  ORDER BY CREATE_DT desc;