<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.style24.admin.biz.dao.TsaStatisticsDao">

	<!-- 일자별주문 목록 -->
	<select id="getDailyOrderList" parameterType="Statistics" resultType="Statistics">
		/* TsaStatistics.getDailyOrderList */
		SELECT SUM(SOD.SELF_AMT + SOD.CHANNEL_AMT + SOD.EXTMALL_AMT) AS TOT_ORD_AMT     /*매출액*/
		     , IFNULL((SELECT SUM(SELF_AMT + CHANNEL_AMT + EXTMALL_AMT)
		               FROM   TB_STAT_ORD_DAY
		               WHERE  1 = 1
		               AND    DAY <![CDATA[>=]]> DATE_ADD(STR_TO_DATE(#{startDt},'%Y-%m-%d'), INTERVAL -1 YEAR)
		               AND    DAY <![CDATA[<]]> DATE_ADD(DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'), INTERVAL 1 DAY), INTERVAL -1 YEAR)
		               AND    (
		                       1 = 1
		               <if test='frontGbP != null and frontGbP != ""'>
		                       OR FRONT_GB = 'P'
		               </if>
		               <if test='frontGbM != null and frontGbM != ""'>
		                       OR FRONT_GB = 'M'
		               </if>
		               <if test='frontGbA != null and frontGbA != ""'>
		                       OR FRONT_GB = 'A'
		               </if>
		                      )
		               <if test='mallGb != null and mallGb != ""'>
		               AND    IFNULL(EXTMALL_ID,'') != '' /*제휴몰만*/
		                   <if test='multiExtmallId != null and multiExtmallId != ""'>
		               AND    EXTMALL_ID IN
		                       <foreach collection="multiExtmallId" item="item" index="index"  open="(" close=")" separator=",">
		                       #{item}
		                       </foreach>
		                   </if>
		               </if>
		               AND    (
		                       1 = 1
		               <if test='distributionGbWms != null and distributionGbWms != ""'>
		                       OR DISTRIBUTION_GB = 'WMS'
		               </if>
		               <if test='distributionGbScm != null and distributionGbScm != ""'>
		                       OR DISTRIBUTION_GB = 'SCM'
		               </if>
		                      )
		               <if test='multiSupplyCompCd != null and multiSupplyCompCd != ""'>
		               AND    SUPPLY_COMP_CD IN
		                      <foreach collection="multiSupplyCompCd" item="item" index="index"  open="(" close=")" separator=",">
		                      #{item}
		                      </foreach>
		               </if>
		               <if test='multiAfLinkCd != null and multiAfLinkCd != ""'>
		               AND    AF_LINK_CD IN
		                      <foreach collection="multiAfLinkCd" item="item" index="index"  open="(" close=")" separator=",">
		                      #{item}
		                      </foreach>
		               </if>
		               <if test='multiBrandCd != null and multiBrandCd != ""'>
		               AND    BRAND_CD IN
		                      <foreach collection="multiBrandCd" item="item" index="index"  open="(" close=")" separator=",">
		                      #{item}
		                      </foreach>
		               </if>
		               <if test='multiItemkindCd != null and multiItemkindCd != ""'>
		               AND    ITEMKIND_CD IN
		                      <foreach collection="multiItemkindCd" item="item" index="index"  open="(" close=")" separator=",">
		                      #{item}
		                      </foreach>
		               </if>
		              ),0)                                           AS YOY_TOT_ORD_AMT /*전년동기대비매출액*/
		     , SUM(SOD.SELF_AMT)                                     AS SELF_AMT        /*자사직방문매출액*/
		     , SUM(SOD.CHANNEL_AMT)                                  AS CHANNEL_AMT     /*채널매출액*/
		     , SUM(SOD.EXTMALL_AMT)                                  AS EXTMALL_AMT     /*제휴몰매출액*/
		     , SUM(SOD.CNCL_AMT + SOD.RTN_AMT)                       AS CNCL_AMT        /*취/반품액*/
		     , SUM(SOD.ORD_CNT)                                      AS ORD_CNT         /*주문건수*/
		     , SUM(SOD.SELL_QTY)                                     AS SELL_QTY        /*판매수량*/
		     , SUM(SOD.CNCL_QTY + SOD.RTN_QTY)                       AS CNCL_QTY        /*취/반품수량*/
		     <choose>
		         <when test='dayGb == "W"'> <!-- 주별 -->
		     , CONCAT(MONTH(SOD.DAY), '월 ', WEEK(SOD.DAY,5) - WEEK(DATE_SUB(SOD.DAY, INTERVAL DAYOFMONTH(SOD.DAY) - 1 DAY), 5) + 1, '주') AS DAY
		         </when>
		         <when test='dayGb == "M"'> <!-- 월별 -->
		     , DATE_FORMAT(SOD.DAY,'%Y-%m')                          AS DAY
		         </when>
		         <otherwise> <!-- 일별 -->
		     , DATE_FORMAT(SOD.DAY,'%Y-%m-%d')                       AS DAY
		         </otherwise>
		     </choose>
		FROM   TB_STAT_ORD_DAY SOD
		WHERE  1 = 1
		AND    SOD.DAY <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		AND    SOD.DAY <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'), INTERVAL 1 DAY)
		AND    (
		        1 = 1
		<if test='frontGbP != null and frontGbP != ""'>
		        OR SOD.FRONT_GB = 'P'
		</if>
		<if test='frontGbM != null and frontGbM != ""'>
		        OR SOD.FRONT_GB = 'M'
		</if>
		<if test='frontGbA != null and frontGbA != ""'>
		        OR SOD.FRONT_GB = 'A'
		</if>
		       )
		<if test='mallGb != null and mallGb != ""'>
		AND    IFNULL(SOD.EXTMALL_ID,'') != '' /*제휴몰만*/
		    <if test='multiExtmallId != null and multiExtmallId != ""'>
		AND    SOD.EXTMALL_ID IN
		        <foreach collection="multiExtmallId" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		        </foreach>
		    </if>
		</if>
		AND    (
		        1 = 1
		<if test='distributionGbWms != null and distributionGbWms != ""'>
		        OR SOD.DISTRIBUTION_GB = 'WMS'
		</if>
		<if test='distributionGbScm != null and distributionGbScm != ""'>
		        OR SOD.DISTRIBUTION_GB = 'SCM'
		</if>
		       )
		<if test='multiSupplyCompCd != null and multiSupplyCompCd != ""'>
		AND    SOD.SUPPLY_COMP_CD IN
		       <foreach collection="multiSupplyCompCd" item="item" index="index"  open="(" close=")" separator=",">
		       #{item}
		       </foreach>
		</if>
		<if test='multiAfLinkCd != null and multiAfLinkCd != ""'>
		AND    SOD.AF_LINK_CD IN
		       <foreach collection="multiAfLinkCd" item="item" index="index"  open="(" close=")" separator=",">
		       #{item}
		       </foreach>
		</if>
		<if test='multiBrandCd != null and multiBrandCd != ""'>
		AND    SOD.BRAND_CD IN
		       <foreach collection="multiBrandCd" item="item" index="index"  open="(" close=")" separator=",">
		       #{item}
		       </foreach>
		</if>
		<if test='multiItemkindCd != null and multiItemkindCd != ""'>
		AND    SOD.ITEMKIND_CD IN
		       <foreach collection="multiItemkindCd" item="item" index="index"  open="(" close=")" separator=",">
		       #{item}
		       </foreach>
		</if>
		<choose>
		         <when test='dayGb == "W"'> <!-- 주별 -->
		GROUP  BY CONCAT(MONTH(SOD.DAY), '월 ', WEEK(SOD.DAY,5) - WEEK(DATE_SUB(SOD.DAY, INTERVAL DAYOFMONTH(SOD.DAY) - 1 DAY), 5) + 1, '주')
		         </when>
		         <when test='dayGb == "M"'> <!-- 월별 -->
		GROUP  BY DATE_FORMAT(SOD.DAY,'%Y-%m')
		         </when>
		         <otherwise> <!-- 일별 -->
		GROUP  BY DATE_FORMAT(SOD.DAY,'%Y-%m-%d')
		         </otherwise>
		     </choose>
	</select>

</mapper>