project/team project

[DAL] 포인트/적립금 관련 쿼리 (스케줄러 설정)

박허디 2024. 2. 6. 15:16

 

이 화면에서 필요한 건 

사용 가능한 적립금, 30일 이내 소멸 예정 적립금, 그리고 유효기간이 만료되면 자동으로 사용가능 포인트, 30일 이내 소멸 예정 적립금에서 사라지고 2번째 사진과 같이 유효기간 만료로 바뀌어야 했다.

 

 

1. 사용 가능한 적립금
SELECT
        SUM(CASE WHEN pt.point_rdate IS NOT NULL THEN ot.od_sell_price * 0.01 ELSE 0 END) -
        SUM(CASE WHEN pt.point_stat = 0 THEN ot.od_sell_price * 0.01 ELSE 0 END) -
        SUM(CASE WHEN pt.point_stat = 2 THEN pt.od_use_point ELSE 0 END) AS point_calu
FROM
    point_tb pt
JOIN
        od_tb ot
ON
    pt.od_id = ot.od_id
WHERE
    pt.user_id = 1;

 

적립과 사용을 동시에 했을 경우에  od_id가 두 개가 잡혀서 중복계산이 되기 때문에 point_rdate는 사용상 태일 때는 값이 null이기 때문에 null 이 아닌 것으로 설정해 주고 판매 가격의 1퍼센트만 적립해 주기 때문에 판매가격에 0.01을 곱해준걸 전부 더한 값에

 

point_stat가 0이면 만료된거 1이면 사용가능한 상태 2이면 사용한 상태이기 때문에 0일 때는 똑같이 포인트가 쌓이는 값을 빼주고

2 즉 사용상태일때는 use_point가 포인트 쓴 칼럼이기 때문에 그것을 빼주었다.

 

현재 시큐리티가 완성되지 않아서 세션값이 없어 일단 User_id는 하드코딩으로 넣어주었다.

 

2. 30일 이내 소멸 예정 적립금

 

<![CDATA[
SELECT
        SUM(CASE WHEN pt.point_rdate IS NOT NULL THEN ot.od_sell_price * 0.01 ELSE 0 END) -
        SUM(CASE WHEN pt.point_stat = 0 THEN ot.od_sell_price * 0.01 ELSE 0 END) -
        SUM(CASE WHEN pt.point_stat = 2 THEN pt.od_use_point ELSE 0 END) AS point_calu
FROM
    point_tb pt
JOIN
        od_tb ot
ON
    pt.od_id = ot.od_id
WHERE
    pt.point_stat =1
  AND
    (pt.point_rdate + INTERVAL 60 DAY) < (CURRENT_DATE + INTERVAL 30 DAY)
]]>

 

모든 적립금의 유효기간은 60일로 설정해 놨기 때문에

30일이 남았을때 보여주어야 하기 때문에 사용가능한 적립금과 쿼리가 비슷하지만 마지막에

조건절에서 적립된날에 60일을 더해준 게 현재날짜에 30일을 더해준 날보다 작으면 보이게 했다.

 

 

3.  적립 상세 내용
SELECT
    ot.od_stts,
    pt.point_stat,
    CAST(ot.od_sell_price * 0.01 AS UNSIGNED) AS point_price,
    pt.point_rdate,
    pt.point_edate ,
    pt.od_id,
    ot.prod_id,
    prt.prod_tit,
    DATE_ADD(point_rdate, INTERVAL 60 DAY) AS point_till,
    pt.od_use_point
FROM
    point_tb pt
        JOIN
    od_tb ot
    ON
        pt.od_id = ot.od_id
        JOIN
    prod_tb prt
    ON
        ot.prod_id = prt.prod_id
ORDER BY
    pt.od_id DESC;

 

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout"
      layout:decorate="~{layouts/my-layout}">


  <!-- 2. Page JS, CSS -->
  <th:block layout:fragment="css-js">
    <link rel="stylesheet" type="text/css" th:href="@{/my/css/my-layout.css}">
    <link rel="stylesheet" type="text/css" th:href="@{/my/css/myPoint.css}">
    <link rel="stylesheet"  th:href="@{https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@100;300;400;500;700;900&display=swap}">
  </th:block>


  <th:block layout:fragment="content">
    <!-- 4.2 Content -->
    <section>
      <div class="ct_content-wrap">
        <div class="title">적립금</div>
          <div class="content-top">
            <div class="content-top-info">
              사용 가능한 적립금
            </div>
            <div class="p-flex1">
                <div class="content-top-view" th:text="${pointGross}"></div>
                <span>P</span>
            </div>
            <div class="content-top-till-wrap">
                <div class="content-top-till">30일 이내 소멸 예정 적립금</div>
                <div class="p-flex2">
                  <div class="content-top-till-view" th:text="${pointGross30}"></div>
                  <span>P</span>
                </div>
            </div>
          </div>
       </div>
      <div class="content-middle">적립금 내역</div>
        <div class="middle-wrap" th:each="point : ${pointList}">
            <div class="content-middle-box">
              <div class="content-middle-box-date" th:if="${point.point_stat != 2}" th:text="${point.point_rdate}"></div>
                <div class="content-middle-box-date" th:if="${point.point_stat == 2}" th:text="${point.point_edate}"></div>
                <div class="content-middle-box-info">
                <div class="box-info-wrap">
                    <div class="box-gone" th:if="${point.point_stat == 0}">유효기간 만료</div>
                    <div class="box-point" th:if="${point.point_stat == 1}">적립</div>
                    <div class="box-use" th:if="${point.point_stat == 2}">사용</div>
                    <div class="box-stts" th:if="${point.point_stat != 0 and point.point_stat != 2}">구매</div>
                </div>
                <div class="box-info-name" th:text="${point.prod_tit}">상품명</div>
                  <div class="f-flex">
                    <div class="box-info-till" th:if="${point.point_stat != 2}"  th:text="${point.point_till}"></div>
                    <span th:if="${point.point_stat != 2}">까지</span>
                  </div>
               </div>
              <span class="color" th:if="${point.point_stat == 1}">+</span>
              <span class="color-use" th:if="${point.point_stat == 2}">-</span>
              <div class="content-middle-box-point" th:if="${point.point_stat == 1}" th:text="${point.point_price}"></div>
              <div class="box-gone" th:if="${point.point_stat == 0}" th:text="${point.point_price}"></div>
              <div class="color-use" th:if="${point.point_stat == 2}" th:text="${point.od_use_point}"></div>
              <span class="color" th:if="${point.point_stat == 1}">P</span>
              <span class="color-use" th:if="${point.point_stat == 2}">P</span>
              <span class="box-gone" th:if="${point.point_stat == 0}">P</span>
            </div>
         </div>
    </section>
  </th:block>

</html>

 

위와 같이 쿼리를 짜고 List 형식으로 model을 이용해서 Html로 보내주어서 필요한 부분에 데이터를 넣어주었다.

point_stat의 값에 따라 보이는 게 달라지는 건 타임리프 if 문법을 이용해 주었다.

4. 스케줄러
     CREATE EVENT tillUpdate
    ON SCHEDULE
        EVERY 1 DAY
        STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 00:00:00')
    DO
		UPDATE point_tb
		SET point_stat = 0
		WHERE DATE_ADD(point_rdate, INTERVAL 60 DAY) = CURDATE();

 

유효기간이 지난 적립금은 point_stat가 0으로 바뀌게

매일 자정에 Update쿼리가 돌아가도록 스케줄러를 설정해 줬다

등록일에서 60일 더한 게 현재일과 같은 조건절을 넣어줬다.

 

 

 

 

+++ 스케줄러 관련 +++

show variables like 'event%'; -- 스케줄러 등록 가능한지 아닌지 확인 on 되어있으면 가능한거고 off 되어있으면 아래 명령어 실행

set global event_scheduler=on; -- off 되어있는 경우 실행

CREATE EVENT tillUpdate -- 이벤트 이름 설정 
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 00:00:00') -- 반복 일 설정 현재는 (매일 자정12시에 실행되게 해놓음) 
DO 
UPDATE point_tb
SET point_stat = 0 WHERE DATE_ADD(point_rdate, INTERVAL 60 DAY) = CURDATE(); -- 실행시킬 쿼리 작성


select * from information_schema.EVENTS; -- 이벤트 생성된것들 확인
저작자표시

'project > team project' 카테고리의 다른 글

[DAL] 1:1 상담 네이버톡톡 연결하기  (0) 2024.02.15
[DAL] 1:1상담 카카오챗 연결하기  (1) 2024.02.14
[DAL] 받아온 데이터 나누기 (핸드폰 번호 나누기)  (0) 2024.02.03
[DAL] 체크박스에 데이터 값 넣기  (1) 2024.02.03

'project/team project'의 다른글

  • 현재글[DAL] 포인트/적립금 관련 쿼리 (스케줄러 설정)

관련글

티스토리툴바