<?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.ruoyi.excel.wecom.mapper.CustomerContactDataMapper">

    <!-- 查询客户联系统计数据列表 -->
    <select id="selectCustomerContactDataList" resultType="com.ruoyi.excel.wecom.domain.CustomerContactData">
        SELECT
            id,
            stat_date,
            userid,
            chat_cnt,
            message_cnt,
            reply_percentage,
            avg_reply_time,
            negative_feedback_cnt,
            new_apply_cnt,
            new_contact_cnt
        FROM customer_contact_data
        <where>
            corp_id = #{corpId}
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="userid != null and userid != ''">
                AND userid = #{userid}
            </if>
        </where>
        ORDER BY stat_date DESC
    </select>

    <!-- 查询客户联系统计数据VO列表(用于导出) -->
    <select id="selectCustomerContactDataVOList" resultType="com.ruoyi.excel.wecom.vo.CustomerContactDataVO">
        SELECT
            stat_date as statDate,
            userid,
            chat_cnt as chatCnt,
            message_cnt as messageCnt,
            reply_percentage as replyPercentage,
            avg_reply_time as avgReplyTime,
            negative_feedback_cnt as negativeFeedbackCnt,
            new_apply_cnt as newApplyCnt,
            new_contact_cnt as newContactCnt
        FROM customer_contact_data
        <where>
            corp_id = #{corpId}

            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="userid != null and userid != ''">
                AND userid = #{userid}
            </if>
        </where>
        ORDER BY stat_date DESC
    </select>

    <!-- 根据日期查询客户联系统计数据 -->
    <select id="selectByStatDate" resultType="com.ruoyi.excel.wecom.domain.CustomerContactData">
        SELECT * FROM customer_contact_data WHERE stat_date = #{statDate} and  corp_id = #{corpId}
    </select>

    <!-- 根据成员ID和日期查询客户联系统计数据 -->
    <select id="selectByUseridAndStatDate" resultType="com.ruoyi.excel.wecom.domain.CustomerContactData">
        SELECT * FROM customer_contact_data WHERE userid = #{userid} AND stat_date = #{statDate} and  corp_id = #{corpId}
    </select>

</mapper>
