zk_java/fs-service-system/src/main/java/com/fs/qw/mapper/QwGroupMsgMapper.java

package com.fs.qw.mapper;


import com.fs.qw.domain.QwGroupMsg;
import com.fs.qw.param.QwGroupMsgDetailsParam;
import com.fs.qw.vo.QwGroupMsgDetailsVO;
import com.fs.qw.vo.QwGroupMsgVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

/**
 * 客户群发记录主Mapper接口
 *
 * @author fs
 * @date 2024-06-20
 */
@Repository
public interface QwGroupMsgMapper
{
    /**
     * 查询客户群发记录主
     *
     * @param id 客户群发记录主主键
     * @return 客户群发记录主
     */
    public QwGroupMsg selectQwGroupMsgById(Long id);

    @Select("SELECT qgm.*,gmi.* FROM qw_group_msg qgm left join qw_group_msg_item gmi on qgm.id=gmi.group_msg_id  " +
            "where qgm.id=#{id}")
    public QwGroupMsgVO selectQwGroupMsgByIdVO(@Param("id") Long id);

    /**
     * 查询客户群发记录主列表
     *
     * @param qwGroupMsg 客户群发记录主
     * @return 客户群发记录主集合
     */
    @Select("<script> " +
            "select gm.*, gmt.msg_type from qw_group_msg gm left join qw_group_msg_item gmt on gm.id=gmt.group_msg_id " +
            "<where>" +
            "<if test='map.msgid != null  and map.msgid != \"\" '> and gm.msgid = #{map.msgid}</if> " +
            "<if test='map.chatType != null  and map.chatType != \"\" '> and gm.chat_type = #{map.chatType}</if> " +
            "<if test='map.selectType != null  and map.selectType != \"\" '> and gm.select_type = #{map.selectType}</if> " +
            "<if test='map.sender != null  and map.sender != \"\" '> and gm.sender = #{map.sender}</if> " +
            "<if test='map.allowSelect != null  and map.allowSelect !=\"\" '> and gm.allow_select = #{map.allowSelect}</if> " +
            "<if test='map.textContent != null  and map.textContent != \"\" '> and gm.text_content like concat( #{map.textContent}, '%')</if> " +
            "<if test='map.corpId != null  and map.corpId != \"\" '> and gm.corp_id = #{map.corpId}</if> " +
            "<if test='map.companyId != null '> and gm.company_id = #{map.companyId}</if> " +
            "<if test='map.tagNames != null  and map.tagNames != \"\" '> and gm.tag_names like concat(#{map.tagNames}, '%')</if> " +
            "<if test='map.groupChatNames != null  and map.groupChatNames != \"\" '> and gm.group_chat_names like concat( #{map.groupChatNames}, '%')</if> " +
            "<if test='map.fromUserCount != null '> and gm.from_user_count = #{map.fromUserCount}</if> " +
            "<if test='map.toUserCount != null '> and gm.to_user_count = #{map.toUserCount}</if> " +
            "<if test='map.fromUserNum != null '> and gm.from_user_num = #{map.fromUserNum}</if> " +
            "<if test='map.toUserNum != null '> and gm.to_user_num = #{map.toUserNum}</if> " +
            "<if test='map.createName != null  and map.createName != \"\" '> and gm.create_name like concat( #{map.createName}, '%')</if> " +
            "<if test='map.isSend != null '> and gm.is_send = #{map.isSend}</if> " +
            "</where> " +
            "order by  gm.create_time  desc"+
            "</script>")
    public List<QwGroupMsgVO> selectQwGroupMsgListVO(@Param("map") QwGroupMsg qwGroupMsg);

    @Select("<script> " +
            "select gm.*, gmt.msg_type from qw_group_msg gm " +
            "left join qw_group_msg_item gmt on gm.id=gmt.group_msg_id " +
            "left join qw_user qu ON FIND_IN_SET(qu.qw_user_id,REPLACE(REPLACE(REPLACE(gm.sender, '[', ''), ']', ''), ' ', '')) > 0" +
            "<where>" +
            "<if test='map.msgid != null  and map.msgid != \"\" '> and gm.msgid = #{map.msgid}</if> " +
            "<if test='map.chatType != null  and map.chatType != \"\" '> and gm.chat_type = #{map.chatType}</if> " +
            "<if test='map.selectType != null  and map.selectType != \"\" '> and gm.select_type = #{map.selectType}</if> " +
            "<if test='map.sender != null  and map.sender != \"\" '> and gm.sender = #{map.sender}</if> " +
            "<if test='map.allowSelect != null  and map.allowSelect !=\"\" '> and gm.allow_select = #{map.allowSelect}</if> " +
            "<if test='map.textContent != null  and map.textContent != \"\" '> and gm.text_content like concat('%', #{map.textContent}, '%')</if> " +
            "<if test='map.corpId != null  and map.corpId != \"\" '> and gm.corp_id = #{map.corpId}</if> " +
            "<if test='map.companyId != null '> and gm.company_id = #{map.companyId}</if> " +
            "<if test='map.tagNames != null  and map.tagNames != \"\" '> and gm.tag_names like concat('%', #{map.tagNames}, '%')</if> " +
            "<if test='map.groupChatNames != null  and map.groupChatNames != \"\" '> and gm.group_chat_names like concat('%', #{map.groupChatNames}, '%')</if> " +
            "<if test='map.fromUserCount != null '> and gm.from_user_count = #{map.fromUserCount}</if> " +
            "<if test='map.toUserCount != null '> and gm.to_user_count = #{map.toUserCount}</if> " +
            "<if test='map.fromUserNum != null '> and gm.from_user_num = #{map.fromUserNum}</if> " +
            "<if test='map.toUserNum != null '> and gm.to_user_num = #{map.toUserNum}</if> " +
            "<if test='map.createName != null  and map.createName != \"\" '> and gm.create_name like concat('%', #{map.createName}, '%')</if> " +
            "<if test='map.isSend != null '> and gm.is_send = #{map.isSend}</if> " +
            "<if test='map.companyUserId != null '> and qu.company_user_id = #{map.companyUserId}</if> " +
            "</where> " +
            "order by  gm.create_time  desc"+
            "</script>")
    public List<QwGroupMsgVO> selectQwGroupMsgListMyVO(@Param("map") QwGroupMsg qwGroupMsg);

    @Select("<script> " +
            " SELECT " +
            "    cu.nick_name, ec.NAME, ec.avatar, mu.* " +
            "<if test='map.chatType == \"group\" '> " +
            " , qgc.name as chatName " +
            "</if> " +
            "FROM " +
            "    qw_group_msg_user mu " +
            "    LEFT JOIN qw_user qu ON mu.user_id = qu.qw_user_id  and mu.corp_id=qu.corp_id " +
            "    LEFT JOIN company_user cu ON qu.company_user_id = cu.user_id " +
            "    LEFT JOIN qw_external_contact ec ON mu.external_userid = ec.external_user_id and mu.corp_id=ec.corp_id  " +
            "    AND mu.user_id = ec.user_id  " +
            "<if test='map.chatType == \"group\" '> " +
            "        LEFT JOIN qw_group_chat qgc ON  mu.chat_id = qgc.chat_id and mu.user_id=qgc.owner and mu.corp_id=qgc.corp_id " +
            "</if> " +
            "WHERE 1=1 and " +
            "    mu.group_msg_id = #{map.msgId} " +
            "<if test='map.nickName != null  and map.nickName != \"\" '> " +
            "and cu.nick_name like concat( #{map.nickName}, '%') " +
            "</if> " +
            " <if test='map.chatType == \"group\" and  map.chatName != null  and map.chatName != \"\" '> " +
            "and qgc.name like concat('%', #{map.chatName}, '%') " +
            "</if> " +
            " <if test='map.name != null  and map.name != \"\" '> " +
            "and ec.name like concat('%', #{map.name}, '%') " +
            "</if> " +
            " <if test='map.status != null '> " +
            "and mu.status = #{map.status} " +
            "</if> " +
            " <if test='map.sendStatus != null  '> " +
            "and mu.send_status = #{map.sendStatus} " +
            "</if> " +
            " <if test='map.chatType != null  and map.chatType != \"\" '> " +
            "and mu.chat_type = #{map.chatType} " +
            "</if> " +
            "</script>")
    public List<QwGroupMsgDetailsVO> getCountGroupMsgUserDetails(@Param("map") QwGroupMsgDetailsParam qwGroupMsgDetailsParam);


    /** 统计数据详情，已发送，未发送，已接收，未接收等 */
    @Select("SELECT " +
            "    COALESCE(SUM(CASE WHEN status = 0  THEN 1 ELSE 0 END), 0) AS '未发送成员', " +
            "    COALESCE(SUM(CASE WHEN send_status = '0' THEN 1 ELSE 0 END), 0) AS '未送达客户', " +
            "    COALESCE(SUM(CASE WHEN status = 2  THEN 1 ELSE 0 END), 0) AS '已发送成员', " +
            "    COALESCE(SUM(CASE WHEN send_status = '1' THEN 1 ELSE 0 END), 0) AS '送达客户', " +
            "    COALESCE(SUM(CASE WHEN send_status = '2' THEN 1 ELSE 0 END), 0) AS '不是好友发送失败', " +
            "    COALESCE(SUM(CASE WHEN send_status = '3' THEN 1 ELSE 0 END), 0) AS '接收已达上限' " +
            "FROM qw_group_msg_user where group_msg_id=#{groupMsgId}")
    public HashMap<String,Integer> getCountGroupMsgUser(@Param("groupMsgId") Long groupMsgId);

    @Select("SELECT " +
            "    COALESCE(SUM(CASE WHEN status = 0  THEN 1 ELSE 0 END), 0) AS '未发送群主', " +
            "    COALESCE(SUM(CASE WHEN status = 2  THEN 1 ELSE 0 END), 0) AS '已发送群主', " +
            "    COALESCE(SUM(CASE WHEN send_status = '1' THEN 1 ELSE 0 END), 0) AS '送达群聊' " +
            "FROM qw_group_msg_user where group_msg_id=#{groupMsgId}")
    public HashMap<String,Integer> getCountGroupMsgBaseUser(@Param("groupMsgId") Long groupMsgId);
    /**
     * 新增客户群发记录主
     *
     * @param qwGroupMsg 客户群发记录主
     * @return 结果
     */
    public int insertQwGroupMsg(QwGroupMsg qwGroupMsg);

    /**
     * 修改客户群发记录主
     *
     * @param qwGroupMsg 客户群发记录主
     * @return 结果
     */
    public int updateQwGroupMsg(QwGroupMsg qwGroupMsg);

    /**
     * 删除客户群发记录主
     *
     * @param id 客户群发记录主主键
     * @return 结果
     */
    public int deleteQwGroupMsgById(Long id);

    /**
     * 批量删除客户群发记录主
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteQwGroupMsgByIds(Long[] ids);

    @Select("SELECT qgm.*,gmi.id as itemId, gmi.group_msg_id, gmi.msg_id, gmi.msg_type, gmi.media_id, gmi.media_pic_url, gmi.title, " +
            "gmi.picurl, gmi.description, gmi.url, gmi.miniprogram_pic_media_id, gmi.miniprogram_pic_url, gmi.miniprogram_appid, " +
            "gmi.miniprogram_title, gmi.miniprogram_page, gmi.video_media_id, gmi.video_url, gmi.file_media_id, gmi.file_url " +
            "FROM qw_group_msg qgm " +
            "LEFT JOIN qw_group_msg_item gmi ON qgm.id = gmi.group_msg_id  " +
            "WHERE " +
            "qgm.is_send = 3 " +
            "AND qgm.is_timer_send = 1 " +
            "AND timer_send <= NOW()")
    public List<QwGroupMsgVO> getQwGroupMsgTask();
}