<?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.batch.biz.dao.TsbStatisticsDao">
	
	<!-- 사이트 제휴코드 유입집계 -->
	<insert id="createInflowDaily" parameterType="String">
		/* TsbStatistics.createInflowDaily */
		INSERT INTO TB_INFLOW_DAILY (
		    INFLOW_YMD
		  , SITE_CD
		  , AF_LINK_CD
		  , PC_INFLOW_TCNT
		  , MOB_INFLOW_TCNT
		  , AOS_INFLOW_TCNT
		  , IOS_INFLOW_TCNT
		  , PC_INFLOW_CNT
		  , MOB_INFLOW_CNT
		  , AOS_INFLOW_CNT
		  , IOS_INFLOW_CNT
		  , REG_NO
		  , REG_DT
		)
		(
		SELECT * FROM (
		       SELECT INFLOW_YMD
		            , SITE_CD
		            , AF_LINK_CD
		            , SUM(CASE WHEN FRONT_GB = 'P' AND APP_YN = 'N' THEN INFLOW_TCNT ELSE 0 END)                   AS PC_INFLOW_TCNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'N' THEN INFLOW_TCNT ELSE 0 END)                   AS MOB_INFLOW_TCNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'Y' AND OS_TYPE = 'A' THEN INFLOW_TCNT ELSE 0 END) AS AOS_INFLOW_TCNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'Y' AND OS_TYPE = 'I' THEN INFLOW_TCNT ELSE 0 END) AS IOS_INFLOW_TCNT
		            , SUM(CASE WHEN FRONT_GB = 'P' AND APP_YN = 'N' THEN INFLOW_CNT ELSE 0 END)                    AS PC_INFLOW_CNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'N' THEN INFLOW_CNT ELSE 0 END)                    AS MOB_INFLOW_CNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'Y' AND OS_TYPE = 'A' THEN INFLOW_CNT ELSE 0 END)  AS AOS_INFLOW_CNT
		            , SUM(CASE WHEN FRONT_GB = 'M' AND APP_YN = 'Y' AND OS_TYPE = 'I' THEN INFLOW_CNT ELSE 0 END)  AS IOS_INFLOW_CNT
		            , 0                                                                                            AS REG_NO
		            , NOW()                                                                                        AS REG_DT
		       FROM   (
		               SELECT DATE_FORMAT(INFLOW_DT,'%Y%m%d') AS INFLOW_YMD
		                    , SITE_CD
		                    , AF_LINK_CD
		                    , FRONT_GB
		                    , APP_YN
		                    , OS_TYPE
		                    , COUNT(DISTINCT JSESSION_ID)   AS INFLOW_TCNT
		                    , COUNT(DISTINCT IP_ADDR)       AS INFLOW_CNT
		               FROM   TB_INFLOW_HST
		               WHERE 1 = 1
		               <if test="inflowDt == 'today'">
		               AND   INFLOW_DT >= DATE_FORMAT(DATE_FORMAT(NOW(), '%Y%m%d'), '%Y%m%d%H%i%S')
		               <![CDATA[
		               AND   INFLOW_DT < DATE_FORMAT(DATE_ADD(DATE_FORMAT(NOW(), '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d%H%i%S')
		               ]]>
		               </if>
		               <if test="inflowDt == 'yesterday'">
		               AND   INFLOW_DT >= DATE_FORMAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d'), '%Y%m%d%H%i%S')
		               <![CDATA[
		               AND   INFLOW_DT < DATE_FORMAT(DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d%H%i%S')
		               ]]>
		               </if>
		               GROUP  BY DATE_FORMAT(INFLOW_DT,'%Y%m%d'), AF_LINK_CD, SITE_CD, FRONT_GB, APP_YN, OS_TYPE
		              ) Z
		       GROUP  BY INFLOW_YMD, AF_LINK_CD, SITE_CD
		      ) B 
		)
		ON DUPLICATE KEY UPDATE
		   PC_INFLOW_TCNT  = B.PC_INFLOW_TCNT
		 , MOB_INFLOW_TCNT = B.MOB_INFLOW_TCNT
		 , AOS_INFLOW_TCNT = B.AOS_INFLOW_TCNT
		 , IOS_INFLOW_TCNT = B.IOS_INFLOW_TCNT
		 , PC_INFLOW_CNT   = B.PC_INFLOW_CNT
		 , MOB_INFLOW_CNT  = B.MOB_INFLOW_CNT
		 , AOS_INFLOW_CNT  = B.AOS_INFLOW_CNT
		 , IOS_INFLOW_CNT  = B.IOS_INFLOW_CNT
		 , REG_NO          = B.REG_NO
		 , REG_DT          = B.REG_DT
	</insert>
		
</mapper>