<?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.TsbMonitoringDao">
	
	<!-- 알람메시지 조회 -->
	<select id="getAlarmMessage" parameterType="Alarm" resultType="String">
		/* TsbMonitoring.getAlarmMessage */
		SELECT <choose>
		           <when test="batchNm != null and batchNm != ''">
		       CONCAT(ALARM_MSG,'-',#{batchNm}) AS ALARM_MSG
		           </when>
		           <otherwise>
		       ALARM_MSG
		           </otherwise>
		       </choose>
		FROM   TB_ALARM
		WHERE  ALARM_ID = #{alarmId}
		AND    USE_YN = 'Y'
	</select>
	
	<!-- 알람SMS 생성 -->
	<insert id="createAlarmSmsDev" parameterType="Alarm">
		/* TsbMonitoring.createAlarmSmsDev */
		INSERT INTO SSG_SEND_TRAN_SMS (
		       FSEQ
		     , FUSERID
		     , FSECTIONCODE
		     , FCAMPCODE
		     , FMSGTYPE
		     , FMESSAGE
		     , FSENDDATE
		     , FDESTINE
		     , FCALLBACK
		)
		SELECT NULL          AS FSEQ
		     , #{fuserid}    AS FUSERID
		     , ''            AS FSECTIONCODE
		     , ''            AS FCAMPCODE
		     , '0'           AS FMSGTYPE
		     , #{alarmMsg}   AS FMESSAGE
		     , NOW()         AS FSENDDATE
		     , AR.CELL_PHNNO AS FDESTINE
		     , #{fcallback}  AS FCALLBACK
		FROM   TB_ALARM_RECEIVER AR
		WHERE  AR.ALARM_ID = #{alarmId}
		AND    AR.USE_YN = 'Y'
	</insert>
	
	<!-- 알람SMS 생성 -->
	<insert id="createAlarmSms" parameterType="Alarm">
		/* TsbMonitoring.createAlarmSms */
		INSERT INTO style24_msg.SSG_SEND_TRAN_SMS (
		       FSEQ
		     , FUSERID
		     , FSECTIONCODE
		     , FCAMPCODE
		     , FMSGTYPE
		     , FMESSAGE
		     , FSENDDATE
		     , FDESTINE
		     , FCALLBACK
		)
		SELECT NULL          AS FSEQ
		     , #{fuserid}    AS FUSERID
		     , ''            AS FSECTIONCODE
		     , ''            AS FCAMPCODE
		     , '0'           AS FMSGTYPE
		     , #{alarmMsg}   AS FMESSAGE
		     , NOW()         AS FSENDDATE
		     , AR.CELL_PHNNO AS FDESTINE
		     , #{fcallback}  AS FCALLBACK
		FROM   TB_ALARM_RECEIVER AR
		WHERE  AR.ALARM_ID = #{alarmId}
		AND    AR.USE_YN = 'Y'
	</insert>
	
	<!-- 1일내 회원가입건수 조회 -->
	<select id="getJoinCountWithinOneDay" parameterType="String" resultType="int">
		/* TsbMonitoring.getJoinCountWithinOneDay */
		SELECT COUNT(*) AS CNT
		FROM   TB_CUSTOMER
		WHERE  JOIN_DT <![CDATA[>=]]> DATE_ADD(NOW(), INTERVAL -1 DAY) /*1일내*/
		AND    SITE_CD = #{siteCd}
	</select>
	
	<!-- 1시간내 로그인건수 조회 -->
	<select id="getLoginCountWithinOneHour" parameterType="String" resultType="int">
		/* TsbMonitoring.getLoginCountWithinOneHour */
		SELECT COUNT(*) AS CNT
		FROM   TB_LOGIN_HST
		WHERE  SITE_CD = #{siteCd}
		AND    LOGIN_DT <![CDATA[>]]> DATE_ADD(NOW(), INTERVAL -1 HOUR) /*1시간내*/
	</select>
	
	<!-- 1시간내 유입건수 조회 -->
	<select id="getInthrowCountWithinOneHour" parameterType="String" resultType="int">
		/* TsbMonitoring.getInthrowCountWithinOneHour */
		SELECT COUNT(*) AS CNT
		FROM   TB_INFLOW_HST
		WHERE  INFLOW_DT <![CDATA[>]]> DATE_ADD(NOW(), INTERVAL -1 HOUR) /*1시간내*/
		AND    SITE_CD = #{siteCd}
	</select>
	
	<!-- 2시간내 결제건수 조회 -->
	<select id="getPayCountWithinTwoHour" resultType="int">
		/* TsbMonitoring.getPayCountWithinTwoHour */
		SELECT COUNT(*) AS CNT
		FROM   TB_PAYMENT
		WHERE  PAY_DT <![CDATA[>]]> DATE_ADD(NOW(), INTERVAL -2 HOUR) /*2시간내*/
		AND    PAY_STAT IN ('00','20','30') --결제대기, 결제성공, 결제완료
	</select>
	
	<!-- 1일내 외부몰주문수집건수 조회 -->
	<select id="getExtmallOrderCollectionCountWithinOneDay" resultType="int">
		/* TsbMonitoring.getExtmallOrderCollectionCountWithinOneDay */
		SELECT COUNT(*) AS CNT
		FROM   TB_EXTMALL_ORDER_UPLOAD
		WHERE  REG_DT <![CDATA[>=]]> DATE_ADD(NOW(), INTERVAL -1 DAY) /*1일내*/
	</select>
	
</mapper>