<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTO Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.icomsys.main_vm.db.mybatis.mapper.StatisticsScenarioMapper">

	<select id="selectStatScenarioDay" resultType="java.util.HashMap">
	/* StatisticsScenarioMapper.selectStatScenarioDay */
		<![CDATA[
		SELECT
			Z.OPR_MNG_CODE         AS oprMngCode
			, Z.SCENARIO_RESULT_CODE AS scenarioResultCode
			, (SELECT IFNULL(u.service_group_name, '') FROM TB_SERVICE_GROUP u WHERE u.service_group = SUBSTRING_INDEX(Z.OPR_MNG_CODE, '_', -1)) AS oprMngCodeName
			, DATE_FORMAT(Y.CALL_INIT_DATE, '%Y-%m-%d') AS callInitDate
        FROM TB_SCENARIO_BREAK_NODE Z
                 INNER JOIN TB_CALL_INFO Y
                            ON Y.TOKEN = Z.TOKEN
		WHERE Y.IN_CHANNEL_TYPE = 'VOICE'
		]]>
		<if test="oprMngCode != null and oprMngCode != ''">
			<choose>
				<when test="oprMngCode == 'all'">
					<![CDATA[
						AND Z.OPR_MNG_CODE IN
					]]>
					<foreach item="item" index="index" collection="oprMngCodeList" open="(" separator="," close=")">
						#{item}
					</foreach>
				</when>
				<otherwise>
					<![CDATA[
						AND Z.OPR_MNG_CODE = #{oprMngCode}
					]]>
				</otherwise>
			</choose>
		</if>
		<if test="callInitDateFrom != null and callInitDateFrom != '' and callInitDateTo != null and callInitDateTo != ''">
			<![CDATA[
				AND Y.CALL_INIT_DATE > #{callInitDateFrom} AND Y.CALL_INIT_DATE < DATE_ADD(#{callInitDateTo}, INTERVAL 1 DAY)
			]]>
		</if>
		ORDER BY callInitDate DESC
	</select>

	<select id="selectStatScenarioItem" resultType="java.util.HashMap">
		<![CDATA[
		SELECT
        	Z.OPR_MNG_CODE         AS oprMngCode
        	, Z.SCENARIO_SEQ         AS scenarioSeq
        	, Z.SCENARIO_NAME        AS scenarioName
        	, Z.SCENARIO_RESULT_CODE AS scenarioResultCode
        	, (SELECT IFNULL(u.service_group_name, '') FROM TB_SERVICE_GROUP u WHERE u.service_group = SUBSTRING_INDEX(Z.OPR_MNG_CODE, '_', -1)) AS oprMngCodeName
        FROM TB_SCENARIO_BREAK_NODE Z
                 INNER JOIN TB_CALL_INFO Y
                            ON Y.TOKEN = Z.TOKEN
		WHERE Y.IN_CHANNEL_TYPE = 'VOICE'
		]]>
		<if test="oprMngCode != null and oprMngCode != ''">
			<choose>
				<when test="oprMngCode == 'all'">
					<![CDATA[
						AND Z.OPR_MNG_CODE IN
					]]>
					<foreach item="item" index="index" collection="oprMngCodeList" open="(" separator="," close=")">
						#{item}
					</foreach>
				</when>
				<otherwise>
					<![CDATA[
						AND Z.OPR_MNG_CODE = #{oprMngCode}
					]]>
				</otherwise>
			</choose>
		</if>
		<choose>
			<when test="scenarioIdList.size > 0 or (chkScenarioAll != null and chkScenarioAll != '')">
				AND Z.SCENARIO_SEQ IN
				<choose>
					<when test='chkScenarioAll == "Y"'>
						<foreach item="item" index="index" collection="allScenarioIdList" open="(" separator="," close=")">
							#{item}
						</foreach>
					</when>
					<otherwise>
						<foreach item="item" index="index" collection="scenarioIdList" open="(" separator="," close=")">
							#{item}
						</foreach>
					</otherwise>
				</choose>
			</when>
			<otherwise>
				AND 1=0
			</otherwise>
		</choose>
		<if test="callInitDateFrom != null and callInitDateFrom != '' and callInitDateTo != null and callInitDateTo != ''">
			<![CDATA[
				AND Y.CALL_INIT_DATE > #{callInitDateFrom} AND Y.CALL_INIT_DATE < DATE_ADD(#{callInitDateTo}, INTERVAL 1 DAY)
			]]>
		</if>
		ORDER BY oprMngCode DESC
	</select>


</mapper>
