package com.fs.qw.mapper;

import com.alibaba.fastjson.JSONObject;
import com.fs.qw.domain.QwUserbehaviorData;
import com.fs.qw.param.QwUserbehaviorDataParams;
import com.fs.qw.vo.QwUserbehaviorDataVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 企微联系客户统计数据Mapper接口
 *
 * @author fs
 * @date 2024-08-05
 */
public interface QwUserbehaviorDataMapper
{
    /**
     * 查询企微联系客户统计数据
     *
     * @param id 企微联系客户统计数据主键
     * @return 企微联系客户统计数据
     */
    public QwUserbehaviorData selectQwUserbehaviorDataById(Long id);

    /**
     * 查询企微联系客户统计数据列表
     *
     * @param qwUserbehaviorData 企微联系客户统计数据
     * @return 企微联系客户统计数据集合
     */
    @Select("<script>" +
            "select qud.*,cu.nick_name,DATE(FROM_UNIXTIME(qud.stat_time)) AS converted_date from qw_userbehavior_data qud " +
            "RIGHT join qw_user qu on qud.userid=qu.qw_user_id and qu.is_del=0 " +
            "left join company_user cu on qu.id=cu.qw_user_id " +
            "where  qud.companyId=#{maps.companyid} " +
            "and qud.userid in " +
            "<foreach  item='item' index='index' collection='maps.users' open='(' separator=',' close=')'> #{item}    </foreach> " +
            "<if test='maps.type != null and maps.type == 1'> " +
            "AND DATE(FROM_UNIXTIME(qud.stat_time)) = CURDATE() " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 2'> " +
            "AND DATE(FROM_UNIXTIME(qud.stat_time)) = CURDATE() - INTERVAL 1 DAY " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 3'> " +
            "AND YEARWEEK(FROM_UNIXTIME(qud.stat_time), 1) = YEARWEEK(CURDATE(), 1) " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 4'> " +
            "AND YEARWEEK(FROM_UNIXTIME(qud.stat_time), 1) = YEARWEEK(CURDATE(), 1) - 1 " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 5'> " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) " +
            "AND MONTH(FROM_UNIXTIME(qud.stat_time)) = MONTH(CURDATE()) " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 6'> " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) " +
            "AND MONTH(FROM_UNIXTIME(qud.stat_time)) = MONTH(CURDATE()) - 1 " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 7'> " +
            "AND QUARTER(FROM_UNIXTIME(qud.stat_time)) = QUARTER(CURDATE()) " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 8'> " +
            "AND QUARTER(FROM_UNIXTIME(qud.stat_time)) = QUARTER(CURDATE()) - 1 " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 9'> " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) " +
            "</if> " +
            "<if test='maps.type != null and maps.type == 10'> " +
            "AND YEAR(FROM_UNIXTIME(qud.stat_time)) = YEAR(CURDATE()) - 1 " +
            "</if> " +
            "<if test='maps.startTime != null and maps.startTime != \"\" '> " +
            "AND DATE(FROM_UNIXTIME(qud.stat_time)) >= DATE(#{maps.startTime}) " +
            "</if> " +
            "<if test='maps.endTime != null and maps.endTime != \"\" '> " +
            "AND DATE(#{maps.endTime}) >= DATE(FROM_UNIXTIME(qud.stat_time)) " +
            "</if> " +
            "</script>" )
    public List<QwUserbehaviorDataVO> selectQwUserbehaviorDataListVO(@Param("maps") QwUserbehaviorDataParams qwUserbehaviorData);

    @Select("<script> " +
            "      SELECT COUNT(*) AS qudCount,  t.time, " +
            "sum(IFNULL(t.chat_cnt,0)) as chatCntAll, " +
            "sum(IFNULL(t.message_cnt,0)) as messageCntAll," +
            "sum(IFNULL(t.reply_percentage,0)) as replyPercentageAll, " +
            "sum(IFNULL(t.avg_reply_time,0)) as avgReplyTimeAll, " +
            "sum(IFNULL(t.negative_feedback_cnt,0)) as negativeFeedbackCntAll," +
            "sum(IFNULL(t.new_apply_cnt,0)) as newApplyCntAll, " +
            "sum(IFNULL(t.new_contact_cnt,0)) as newContactCntAll " +
            "        FROM " +
            "        (" +
            "        SELECT DATE_FORMAT( FROM_UNIXTIME(q.stat_time), #{maps.sqlDateFormat} ) AS time,  q.chat_cnt, q.message_cnt,q.reply_percentage, q.avg_reply_time, " +
            "q.negative_feedback_cnt, q.new_apply_cnt, q.new_contact_cnt " +
            "        FROM " +
            "        qw_userbehavior_data  AS q " +
            "        WHERE " +
            "        (DATE_FORMAT(FROM_UNIXTIME(q.stat_time),#{maps.sqlDateFormat}) between #{maps.beginTime} and #{maps.finalTime}) " +
            "        <if test=\"qwUserIds != null and qwUserIds.length>0\"> " +
            "            AND q.userid IN " +
                        "<foreach  item='item' index='index' collection='qwUserIds' open='(' separator=',' close=')'> #{item}   </foreach> " +
            "        </if> " +
            "        <if test=\"maps.companyId != null and  maps.companyId != '' \"> " +
            "            AND q.companyId = #{maps.companyId} " +
            "        </if> " +
            "        ) t " +
            "        GROUP BY t.time" +
            "</script>")
    public List<JSONObject> selectQwUserbehaviorDataListVOCount(@Param("maps") Map<String, Object> map,@Param("qwUserIds") String[] userIds);

    /**
     * 新增企微联系客户统计数据
     *
     * @param qwUserbehaviorData 企微联系客户统计数据
     * @return 结果
     */
    public int insertQwUserbehaviorData(QwUserbehaviorData qwUserbehaviorData);

    /**
     * 修改企微联系客户统计数据
     *
     * @param qwUserbehaviorData 企微联系客户统计数据
     * @return 结果
     */
    public int updateQwUserbehaviorData(QwUserbehaviorData qwUserbehaviorData);

    /**
     * 删除企微联系客户统计数据
     *
     * @param id 企微联系客户统计数据主键
     * @return 结果
     */
    public int deleteQwUserbehaviorDataById(Long id);

    /**
     * 批量删除企微联系客户统计数据
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteQwUserbehaviorDataByIds(Long[] ids);

    /**
    * 更新或者插入
    */
    public int insertOrUpdateQwUserbehaviorData(QwUserbehaviorData qwUserbehaviorData);
}
