[KRS event data Scheduler]
1.[KRS Scheduler] schedulerKRSGoods (편의점), [ADMIN] 스토리 오더 관리 > 상품 관리 (/storeProduct) > 편의점 KRS 수동 업데이트 (KRS manual update) button
- add event data create logic
2.logic
0) DB (st_krs_event)
a) get KRS data
SELECT EV.EVENT_CD, EV.EVENT_NM, EV.EVENT_KIND, EV.EVENT_BGNDE, EV.EVENT_EDDE, EV.DEL_YN
FROM VI_APP_EVENT EV
WHERE EV.EVENT_KIND IN ( '001', '003') // 001: event discount, 003: 1+1 event
AND EV.TRANS_YN = 'N'
AND EV.EVENT_BGNDE <= TO_DATE('20231201','YYYYMMDD') // '20231201' -> today
AND EV.EVENT_EDDE >= TO_DATE('20231201','YYYYMMDD') // '20231201' -> today
b) process data
b-1) if EV.DEL_YN = 'Y'
- DELETE st_krs_event WHERE event_cd = {the EVENT_CD}
b-2) else
b-2-1) INSERT/UPDATE st_krs_event
b-2-2) UPDATE VI_APP_EVENT
SET TRANS_YN = 'Y'
WHERE EVENT_CD = {the EVENT_CD}
1) DB (st_krs_event_store)
a) get KRS data
SELECT EV.EVENT_CD
,ES.DEPT_CD, ES.DEL_YN
FROM VI_APP_EVENT EV
LEFT JOIN VI_APP_EVENT_STORE ES ON ES.EVENT_CD = EV.EVENT_CD
WHERE EV.EVENT_KIND IN ( '001', '003') // 001: event discount, 003: 1+1 event
AND ES.TRANS_YN = 'N'
AND EV.EVENT_BGNDE <= TO_DATE('20231201','YYYYMMDD') // '20231201' -> today
AND EV.EVENT_EDDE >= TO_DATE('20231201','YYYYMMDD') // '20231201' -> today
b) process data
b-1) if ES.DEL_YN = 'Y'
- DELETE st_krs_event_store WHERE dept_cd= {the DEPT_CD}
b-2) else
b-2-1) INSERT/UPDATE st_krs_event_store
b-2-2) UPDATE VI_APP_EVENT_STORE
SET TRANS_YN = 'Y'
WHERE EVENT_CD = {the EVENT_CD}
2) DB (st_krs_event_store_item)
a) get KRS data
SELECT EV.EVENT_CD
,ES.DEPT_CD
,EI.ITEM_CD, EI.DC_SALE_AMT, EI.DEL_YN
FROM VI_APP_EVENT EV
LEFT JOIN VI_APP_EVENT_STORE ES ON ES.EVENT_CD = EV.EVENT_CD
LEFT JOIN VI_APP_EVENT_ITEM EI ON ES.EVENT_CD = EI.EVENT_CD
WHERE EV.EVENT_KIND IN ( '001', '003') // 001: event discount, 003: 1+1 event
AND EV.TRANS_YN = 'N'
AND EV.EVENT_BGNDE <= TO_DATE('20231201','YYYYMMDD')
AND EV.EVENT_EDDE >= TO_DATE('20231201','YYYYMMDD')
b) process data
b-1) if EI.DEL_YN = 'Y'
- DELETE st_krs_event_store_item WHERE event_cd={the EVENT_CD} AND item_cd= {the ITEM_CD}
b-2) else
b-2-1) INSERT/UPDATE st_krs_event_store_item
product_code : "{dept_cd}-{item_cd}"
b-2-2) UPDATE VI_APP_EVENT_STORE_ITEM
SET TRANS_YN = 'Y'
WHERE EVENT_CD = {the EVENT_CD} AND ITEM_CD = {the ITEM_CD}