ipo list 조회 관련 쿼리문 수정 (특정일이 아닌 기간을 기준으로 동작하도록 수정)

[chhs2131]

from #61 and #67
아래 targetDate 부분을 제거하면서 로직을 유지하는 방향으로 진행

    <select id="selectIpos" parameterType="Map" resultType="com.dbsgapi.dbsgapi.api.ipo.dto.IpoSummaryDto">
        SELECT ipo.ipo_index,
               stock_name,
               IF(INSTR(stock_name,'스팩')=0 AND INSTR(stock_name, '기업인수목적')=0, stock_kinds, '스팩주') AS stock_kinds,
               stock_exchange,
               DATE_FORMAT(ipo_forecast_start, '%Y-%m-%d') ipo_forecast_start,
               DATE_FORMAT(ipo_forecast_end, '%Y-%m-%d') ipo_forecast_end,
               DATE_FORMAT(ipo_start_date, '%Y-%m-%d') ipo_start_date,
               DATE_FORMAT(ipo_end_date, '%Y-%m-%d') ipo_end_date,
               DATE_FORMAT(ipo_refund_date, '%Y-%m-%d') ipo_refund_date,
               DATE_FORMAT(ipo_debut_date, '%Y-%m-%d') ipo_debut_date,
               under.underwriter,

               ipo_price_low,
               ipo_price_high,
               ipo_price,

               ipo_cancel_bool,
               DATE_FORMAT(ipo_cancel_date, '%Y-%m-%d') ipo_cancel_date
        FROM ipo LEFT OUTER JOIN
             (
                 SELECT iu.ipo_index, GROUP_CONCAT(iu.under_name ORDER BY iu.ind_total_max DESC) AS underwriter
                 FROM(
                         SELECT DISTINCT
                                         ipo_underwriter.ipo_index,
                                         ipo_underwriter.ind_total_max,
                                         IFNULL(uc2.underwriter_name, ipo_underwriter.under_name) AS under_name
                         FROM
                             underwriter_code AS uc1
                                 JOIN underwriter_code AS uc2
                                     ON uc1.parents_code = uc2.underwriter_code
                                 RIGHT OUTER JOIN ipo_underwriter
                                     ON ipo_underwriter.under_name LIKE CONCAT('%', uc1.underwriter_name, '%')
                     ) AS iu
                 GROUP BY iu.ipo_index
             ) AS under
             ON ipo.ipo_index = under.ipo_index
        WHERE
              stock_exchange IS NOT NULL
              <if test="!withCancelItem">
                 AND ipo_cancel_bool = 'N'
              </if>
              <choose>
                  <when test="ipoSequence == 'CANCEL'">
                      AND ipo_cancel_bool = 'Y'
                    ORDER BY ipo_cancel_date DESC
                  </when>
                  <when test="ipoSequence == 'TODAY'">
                      AND
                    ( #{targetDate} = ipo_cancel_date
                      OR #{targetDate} = ipo_debut_date
                      OR #{targetDate} = ipo_refund_date
                      OR #{targetDate} BETWEEN ipo_forecast_start AND ipo_forecast_end
                      OR #{targetDate} BETWEEN ipo_start_date AND ipo_end_date
                    )
                    ORDER BY ipo_start_date DESC
                  </when>
                  <when test="ipoSequence == 'BEFORE_FORECAST'">
                      AND #{targetDate} &lt; ipo.ipo_forecast_start
                    ORDER BY ipo_forecast_start ASC
                  </when>
                  <when test="ipoSequence == 'BEFORE_IPO' or ipoSequence == 'AFTER_FORECAST'">
                      AND #{targetDate} BETWEEN DATE_ADD(IFNULL(ipo_forecast_end, regist_date), INTERVAL 1 DAY) AND DATE_SUB(ipo_start_date, INTERVAL 1 DAY)
                    ORDER BY ipo_start_date ASC
                  </when>
                  <when test="ipoSequence == 'BEFORE_REFUND' or ipoSequence == 'AFTER_IPO'">
                      AND #{targetDate} BETWEEN DATE_ADD(ipo_end_date, INTERVAL 1 DAY) AND DATE_SUB(ipo_refund_date, INTERVAL 1 DAY)
                    ORDER BY ipo_refund_date ASC
                  </when>
                  <when test="ipoSequence == 'BEFORE_DEBUT' or ipoSequence == 'AFTER_REFUND'">
                      AND #{targetDate} BETWEEN DATE_ADD(ipo_refund_date, INTERVAL 1 DAY) AND DATE_SUB(ipo_debut_date, INTERVAL 1 DAY)
                    ORDER BY ipo_debut_date ASC
                  </when>
                  <when test="ipoSequence == 'AFTER_DEBUT'">
                      AND #{targetDate} >= ipo_debut_date
                    ORDER BY ipo_debut_date DESC
                  </when>
                  <otherwise>
                    ORDER BY
                        (ifnull(terminate_date, ipo_debut_date) IS NULL) DESC,
                        ifnull(terminate_date, ipo_debut_date) DESC,
                        ipo_start_date DESC,
                        ipo.regist_date DESC
                  </otherwise>
              </choose>
        LIMIT #{limit} OFFSET #{offset};
    </select>

• ipo list 테스트 필요항목
  • TODAY (이전 schedule)
    • 기간 확인 (schedule이 해당으로 대체)
    • 특정일 확인
  • BEFORE FORECAST
    • 기간 확인
    • 특정일 확인
  • BEFORE IPO
    • 기간 확인
    • 특정일 확인
  • BEFORE REFUND
    • 기간 확인
    • 특정일 확인
  • BEFORE DEBUT
    • 기간 확인
    • 특정일 확인
  • AFTER DEBUT
    • 기간 확인
    • 특정일 확인