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

package com.fs.qw.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fs.qw.domain.QwExternalContact;
import com.fs.qw.param.ConversionStatisticsParam;
import com.fs.qw.param.QwCountCustomerParam;
import com.fs.qw.param.QwExternalContactParam;
import com.fs.qw.param.QwExternalContactVOTime;
import com.fs.qw.param.newparam.ExternalContactPageListParam;
import com.fs.qw.result.QwExternalContactByQwResult;
import com.fs.qw.result.QwExternalContactLogVo;
import com.fs.qw.result.QwExternalContactVo;
import com.fs.qw.vo.QwExternalContactFsCrmVO;
import com.fs.qw.vo.QwExternalContactFsUserVO;
import com.fs.qw.vo.QwExternalContactVO;
import com.fs.qw.vo.newvo.ExternalContactDetailsVO;
import com.fs.qw.vo.newvo.ExternalContactListVO;
import com.fs.qw.vo.newvo.ExternalContactNumVO;
import com.fs.qwApi.param.QwExternalContactHParam;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * 企业微信客户Mapper接口
 *
 * @author fs
 * @date 2024-06-20
 */
@SuppressWarnings("ALL")
@Repository
public interface QwExternalContactMapper extends BaseMapper<QwExternalContact> {
    /**
     * 查询企业微信客户
     *
     * @param id 企业微信客户主键
     * @return 企业微信客户
     */
    public QwExternalContact selectQwExternalContactById(Long id);

    @Select("SELECT id,stage_status,fs_user_id from qw_external_contact where id=#{id}")
    public QwExternalContact selectQwExternalContactByIdForStageStatus(@Param("id") Long id);

    @Select("SELECT\n" +
            "\tec.stage_status,\n" +
            "\tec.fs_user_id,\n" +
            "\tfu.jpush_id,\n" +
            "\tfu.login_device \n" +
            "FROM\n" +
            "\tqw_external_contact ec\n" +
            "\tLEFT JOIN fs_user fu ON ec.fs_user_id = fu.user_id \n" +
            "WHERE\n" +
            "\tid = #{id}")
    public QwExternalContactFsUserVO selectQwExternalContactByIdForFsUser(@Param("id") Long id);

    @Select("select * from qw_external_contact where fs_user_id=#{id} ")
    public List<QwExternalContact> selectQwExternalContactByMiniUserId(Long id);


    @Select("select qec.*,cc.user_id as miniUserId  from qw_external_contact qec " +
            "left join  crm_customer cc on qec.customer_id= cc.customer_id " +
            "where qec.id=#{id} ")
    public QwExternalContactFsCrmVO selectQwExternalContactByIdToCrm(Long id);

    /** 根据外部联系人的userid 获取到详细信息 */
    @Select("SELECT * FROM qw_external_contact WHERE external_user_id = #{externalUserId} AND corp_id=#{corpId}")
    public List<QwExternalContact> selectQwExternalContactByExternalUserId(@Param("externalUserId") String externalUserId,@Param("corpId") String corpId);


    public int batchUpdateQwExternalContactStatus(@Param("notInExternalUseridList") List<String> notInExternalUseridList,
                                                  @Param("qwUserId") String qwUserId,
                                                  @Param("corpId") String corpId);


    @Select("SELECT * FROM qw_external_contact WHERE external_user_id = #{externalUserId}  AND corp_id=#{corpId} and user_id=#{qwUserId} limit 1")
    public QwExternalContact selectQwExternalContactByExternalUserIdAndQwUserId(@Param("externalUserId") String externalUserId,@Param("corpId") String corpId,@Param("qwUserId") String qwUserId);

    @Select("<script>" +
            "SELECT " +
            " COUNT( DISTINCT qec.external_user_id ) AS count " +
            " FROM " +
            " qw_external_contact qec " +
            "<if test='map.selectType ==2 and map.groupChatSelectList.size()!=0 '> " +
            "left join qw_group_chat_user qcu ON qec.user_id = qcu.invitor   " +
            "</if>" +
            " WHERE " +
            " qec.user_id IN " +
            " <foreach collection='map.userIdsSelectList'  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach> " +
            " AND qec.corp_id = #{map.corpId} " +
            "<if test ='map.selectType ==2 and map.gender!=99 '> " +
            "and qec.gender = #{map.gender} " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.groupChatSelectList.size()!=0 '> " +
            " and qcu.chat_id in  <foreach collection='map.groupChatSelectList'  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.tagsIdsSelectList.size()!=0 '> " +
            " and <foreach collection='map.tagsIdsSelectList'  item='item' index='index'  open='( 1=2 ' separator='' close=')'> or find_in_set(  #{item} , REGEXP_REPLACE ( qec.tag_ids, '[\"\\\\[\\\\]]', '' ) )  </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.outTagsIdsSelectList.size()!=0 '> " +
            " and <foreach collection='map.outTagsIdsSelectList'  item='item' index='index'  open='( 1=2 ' separator='' close=')'> or find_in_set(  #{item} , REGEXP_REPLACE ( qec.tag_ids, '[\"\\\\[\\\\]]', '' ) )  </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.timeScreenCharge.size()!=0 and map.timeScreenCharge != null and map.timeScreenCharge.size() == 2 '> " +
            " and qec.create_time BETWEEN #{map.timeScreenCharge[0]} and #{map.timeScreenCharge[1]}" +
            "</if> " +

            "</script>")
    public int expectQwGroupMsgCountCustomer(@Param("map")QwCountCustomerParam userIdList);

    @Select("<script>" +
            "SELECT " +
            " DISTINCT qec.external_user_id AS externalUserIdS " +
            " FROM " +
            " qw_external_contact qec " +
            "<if test='map.selectType ==2 and map.groupChatSelectList.size()!=0 '> " +
            "left join qw_group_chat_user qcu ON qec.user_id = qcu.invitor   " +
            "</if>" +
            " WHERE " +
            " qec.user_id IN " +
            " <foreach collection='map.userIdsSelectList'  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach> " +

            " AND qec.corp_id = #{corpId} " +
            "<if test ='map.selectType ==2 and map.gender!=99 '> " +
            "and qec.gender = #{map.gender} " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.groupChatSelectList.size()!=0 '> " +
            " and qcu.chat_id in  <foreach collection='map.groupChatSelectList'  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.tagsIdsSelectList.size()!=0 '> " +
            " and <foreach collection='map.tagsIdsSelectList'  item='item' index='index'  open='( 1=2 ' separator='' close=')'> or find_in_set(  #{item} , REGEXP_REPLACE ( qec.tag_ids, '[\"\\\\[\\\\]]', '' ) )  </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.outTagsIdsSelectList.size()!=0 '> " +
            " and <foreach collection='map.outTagsIdsSelectList'  item='item' index='index'  open='( 1=2 ' separator='' close=')'> or not find_in_set(  #{item} , REGEXP_REPLACE ( qec.tag_ids, '[\"\\\\[\\\\]]', '' ) )  </foreach> " +
            "</if> " +

            "<if test ='map.selectType ==2 and map.timeScreenCharge.size()!=0 and map.timeScreenCharge != null and map.timeScreenCharge.size() == 2 '> " +
            " and qec.create_time BETWEEN #{map.timeScreenCharge[0]} and #{map.timeScreenCharge[1]}" +
            "</if> " +

            "</script>")
    public ArrayList<String> selectQwGroupMsgExpectCustomerList(@Param("map")QwCountCustomerParam userIdList, @Param("corpId") String corpId);


    /**
     * 查询企业微信客户列表
     *
     * @param qwExternalContact 企业微信客户
     * @return 企业微信客户集合
     */
    public List<QwExternalContact> selectQwExternalContactList(QwExternalContact qwExternalContact);


    @Select("select external_user_id from qw_external_contact where user_id=#{qwUserId} and corp_id=#{corpId}")
    public List<String> selectQwExternalContactListAll(@Param("qwUserId") String qwUserId ,@Param("corpId") String corpId);

    @Select("select external_user_id from qw_external_contact where user_id=#{qwUserId} and corp_id=#{corpId} and status!=4")
    public List<String> selectQwExternalContactListAllNoDel(@Param("qwUserId") String qwUserId ,@Param("corpId") String corpId);

    @Select("select external_user_id from qw_external_contact where user_id=#{qwUserId} and corp_id=#{corpId} and status= 4 ")
    public List<String> selectQwExternalContactListAllByStatus(@Param("qwUserId") String qwUserId ,@Param("corpId") String corpId);

    /**
     * 新增企业微信客户
     *
     * @param qwExternalContact 企业微信客户
     * @return 结果
     */
    public int insertQwExternalContact(QwExternalContact qwExternalContact);

    /**
     * 修改企业微信客户
     *
     * @param qwExternalContact 企业微信客户
     * @return 结果
     */
    public int updateQwExternalContact(QwExternalContact qwExternalContact);

    public int batchUpdateQwExternalContact(List<QwExternalContact> qwExternalContact);

    @Update("update qw_external_contact set qw_user_id=#{qwUserId} , company_user_id =#{companyUserId} " +
            "where user_id=#{userId}  and corp_id=#{corpId} ")
    public int updateQwExternalContactByQwUserId(QwExternalContact qwExternalContact);
    /**
    * 修改CRM客户
    */
    @Update("update qw_external_contact set customer_id=#{customerId} where external_user_id=#{externalUserId} and corp_id=#{corpId}")
    public int updateQwExternalContactByExternalUserId(QwExternalContact qwExternalContact);

    @Update("update qw_external_contact set fs_user_id=null where id =#{id}")
    public int updateQwExternalContactUnBindUserId(@Param("id") Long id);

    /**
     * 删除企业微信客户
     *
     * @param id 企业微信客户主键
     * @return 结果
     */
    public int deleteQwExternalContactById(Long id);

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

    @Select({"<script> " +
            "select ec.*,qu.qw_user_name,qd.dept_name as departmentName from qw_external_contact ec " +
            "left join qw_user qu on ec.user_id=qu.qw_user_id and qu.corp_id=ec.corp_id " +
            "left join qw_dept qd on qd.dept_id=qu.department and qd.corp_id=qu.corp_id " +
            "<where>  \n" +
            "            <if test=\"userId != null  and userId != ''\"> and ec.user_id   like concat( #{userId}, '%') </if>\n" +
            "            <if test=\"qwUserName != null  and qwUserName != ''\"> and qu.qw_user_name   like concat( #{qwUserName}, '%') </if>\n" +
            "            <if test=\"externalUserId != null  and externalUserId != ''\"> and ec.external_user_id = #{externalUserId}</if>\n" +
            "            <if test=\"name != null  and name != ''\"> and ec.name like concat( #{name}, '%')</if>\n" +
            "            <if test=\"type != null \"> and ec.type = #{type}</if>\n" +
            "            <if test=\"gender != null \"> and ec.gender = #{gender}</if>\n" +
            "            <if test=\"description != null  and description != ''\"> and ec.description = #{description}</if>\n" +

            "<if test ='tagIds!=null and tagIds.size()!=0 '> " +
            " and <foreach collection='tagIds'  item='item' index='index'  open='( 1=2 ' separator='' close=')'> or find_in_set(  #{item} , REGEXP_REPLACE ( ec.tag_ids, '[\"\\\\[\\\\]]', '' ) )  </foreach> " +
            "</if> " +

            "            <if test=\"remarkMobiles != null  and remarkMobiles != ''\"> and ec.remark_mobiles like concat( #{remarkMobiles}, '%')</if>\n" +
            "            <if test=\"remark != null  and remark != ''\"> and ec.remark like concat('%', #{remark}, '%')</if>\n" +
            "            <if test=\"remarkCorpName != null  and remarkCorpName != ''\"> and ec.remark_corp_name like concat('%', #{remarkCorpName}, '%')</if>\n" +
            "            <if test=\"addWay != null \"> and ec.add_way = #{addWay}</if>\n" +
            "            <if test=\"operUserid != null  and operUserid != ''\"> and ec.oper_userid = #{operUserid}</if>\n" +
            "            <if test=\"corpId != null  and corpId != ''\"> and ec.corp_id = #{corpId}</if>\n" +
            "            <if test=\"companyId != null \"> and qu.company_id = #{companyId}</if>\n" +
            "            <if test=\"companyUserId != null \"> and ec.company_user_id = #{companyUserId}</if>\n" +
            "            <if test=\"customerId != null \"> and ec.customer_id = #{customerId}</if>\n" +
            "            <if test=\"status != null \"> and ec.status = #{status}</if>\n" +
            "            <if test=\"stageStatus != null \"> and ec.stage_status = #{stageStatus}</if>\n" +
            "            <if test=\"transferStatus != null \"> and ec.transfer_status = #{transferStatus}</if>\n" +
            "            <if test=\"qwUserId != null \"> and ec.qw_user_id = #{qwUserId}</if>\n" +
            "            <if test=\"level != null \"> and ec.level = #{level}</if>\n" +
            "            <if test=\"levelType != null \"> and ec.level_type = #{levelType}</if>\n" +
            "            <if test=\"isBind == 'isBind' \"> and ec.customer_id is not null </if> \n" +
            "            <if test=\"isBind == 'noBind' \"> and ec.customer_id is null </if> \n" +
            "            <if test=\"isBindMini == 'isBindMini' \"> and ec.fs_user_id is not null </if> \n" +
            "            <if test=\"isBindMini == 'noBindMini' \"> and ec.fs_user_id is null </if> \n" +
            "            <if test=\"lossTime != null \"> and DATE(ec.loss_time) = DATE(#{lossTime})</if>\n" +
            "            <if test=\"createTime != null \">and DATE(ec.create_time) = DATE(#{createTime})</if>\n" +
            "            <if test=\"delTime != null \"> and DATE(ec.del_time) = DATE(#{delTime})</if>\n" +
            "            <if test=\"sTime != null \">  and DATE(ec.create_time) &gt;= DATE(#{sTime})</if>\n" +
            "            <if test=\"eTime != null \">  and DATE(ec.create_time) &lt;= DATE(#{eTime})</if>\n" +
            "        </where>"+
            "order by ec.id desc"+
            "</script>"})
    List<QwExternalContactVO> selectQwExternalContactListVO(QwExternalContactParam qwExternalContact);


    List<QwExternalContactVOTime> selectQwExternalContactListVOByIds(@Param("ids") List<Long> ids);

    @Select("select * from qw_external_contact where external_user_id=#{externalUserid} and user_id=#{userId} and corp_id=#{corpId} limit 1")
    QwExternalContact selectQwExternalContactUserIdAndExternalIdAndCompanyId(@Param("externalUserid")String externalUserid,@Param("userId") String userId,@Param("corpId") String corpId);

    @Select("select id,user_id,external_user_id,name,avatar,fs_user_id,remark,description,qw_user_id " +
            "from qw_external_contact " +
            "where external_user_id=#{externalUserid} " +
            "and user_id=#{userId} " +
            "and corp_id=#{corpId} limit 1")
    QwExternalContact selectQwExternalByExternalIdAndCompanyIdToIdAndFs(@Param("externalUserid")String externalUserid,@Param("userId") String userId,@Param("corpId") String corpId);

    /**
    * 修改 状态
    */

    @Update("update qw_external_contact set status=#{map.status} where user_id=#{map.userId} and corp_id= #{map.corpId} and  external_user_id=#{map.externalUserId} ")
    public int updateQwExternalContactByUseridBlock(@Param("map") QwExternalContact qwExternalContact);

    @Update("update qw_external_contact set status=#{map.status},transfer_status=#{map.transferStatus} " +
            "where user_id=#{map.userId} and corp_id= #{map.corpId} and  external_user_id=#{map.externalUserId} ")
    public int updateQwExternalContactByUseridTransfer(@Param("map") QwExternalContact qwExternalContact);


    @Select("select q.id,q.external_user_id,q.user_id from qw_external_contact q " +
            "left join fs_user u on u.user_id = q.fs_user_id where u.user_id = #{userId} limit 1 ")
    QwExternalContact selectQwExternalContactByUserId(@Param("userId")Long userId);

    @Select("select external_user_id from qw_external_contact q " +
            "left join crm_customer c on c.customer_id  = q.customer_id " +
            "left join fs_user u on u.user_id = c.user_id where u.user_id = #{userId} ")
    String selectExternalUserIdByUserId(@Param("userId")Long userId);

    @Select("select * from qw_external_contact where fs_user_id = #{userId} ")
    QwExternalContact selectExternalByFsUserId(@Param("userId")Long userId);

    @Update("UPDATE qw_external_contact SET company_id = #{companyId},company_user_id=#{companyUserId} WHERE corp_id = #{corpId} and user_id =#{qwUserId}")
    void updateBindUserByQwUser(@Param("corpId")String corpId, @Param("qwUserId")String qwUserId, @Param("companyId")Long companyId, @Param("companyUserId")Long companyUserId);

    @Select("SELECT * FROM  qw_external_contact " +
            " WHERE user_id = #{map.userId}   " +
            "AND external_user_id = #{map.externalUserId} " +
            "AND corp_id =#{map.corpId} " +
            "limit 1 ")
    QwExternalContact getQwExternalContactDetails(@Param("map")QwExternalContactHParam param);


//    @Select("SELECT *\n" +
//            "FROM qw_external_contact\n" +
//            "WHERE user_id IN ('LiuQin_1')\n" +
//            "  AND DATE(create_time) = '2025-01-04'\n" +
//            "  AND `status` = 0\n" +
//            "  AND transfer_status = 0 \n" +
//            "\tAnd type=1\n" +
//            "\tand corp_id='ww5a88c4f879f204c5' ")
//    List<QwExternalContact> getQwExternalContactDetailsText();

    @Select("SELECT id FROM  qw_external_contact " +
            " WHERE user_id = #{map.userId}   " +
            "AND external_user_id = #{map.externalUserId} " +
            "AND corp_id =#{map.corpId} limit 1 ")
    Long getQwExternalContactId(@Param("map")QwExternalContactHParam param);

    @Select("SELECT \n" +
            "    ANY_VALUE(qec.external_user_id) AS external_user_id,\n" +
            "    qec.name\n" +
            "FROM qw_external_contact qec\n" +
            "JOIN qw_user qu ON qec.qw_user_id = qu.id\n" +
            "WHERE \n" +
            "    qu.app_key = #{appKey}\n" +
            "    AND qec.type = 1\n" +
            "    AND qec.status = 0\n" +
            "    AND qec.name REGEXP '^[一-龥]+$'\n" +
            "GROUP BY qec.name\n" +
            "HAVING COUNT(*) = 1\n" +
            "ORDER BY LENGTH(qec.name) DESC\n" +
            "LIMIT 1;\n")
    QwExternalContactByQwResult getExternalContactByAppKey(@Param("appKey") String appKey);

    List<QwExternalContactVo> selectAllQwJoinAdClickLog(@Param("type") Integer type);

    List<QwExternalContactLogVo> selectAllFormAd(ConversionStatisticsParam param);

    @Select("select * from qw_external_contact where  date(create_time) ='2025-02-10' and (`status` =0 or `status` =3) and qw_user_id=#{id}  ")
    List<QwExternalContact> selectExternalUserIdByQwUserId(Long id);

    @Select("select id from qw_external_contact where qw_user_id=#{id}  ")
    List<Long> selectExternalUserByQwUserId(Long id);

    @Select("select id,external_user_id,tag_ids from qw_external_contact where  qw_user_id=#{id} ")
    List<QwExternalContact> selectExternalUserIdsByQwUserId(Long id);

    @Select("select id,first_time,qw_user_id,create_time from qw_external_contact where  id=#{id} ")
    QwExternalContact selectQwExternalContactTimeById(Long id);

    List<ExternalContactListVO> selectExternalContactPageList(ExternalContactPageListParam param);

    List<ExternalContactNumVO> getContactNumber(@Param("userId") Long userId);

    ExternalContactDetailsVO getCountAnswer (@Param("userId") Long userId, @Param("contactId") Long contactId, @Param("dateTag") String dateTag);

    ExternalContactDetailsVO getCountRedPacket (@Param("userId") Long userId, @Param("contactId") Long contactId, @Param("dateTag") String dateTag);

    List<QwExternalContact> selectContactByIds(@Param("ids") List<String> ids);

    int updateContactByIds(@Param("ids") List<String> ids,@Param("status") Integer status);

    int batchUpdateContactByIds(@Param("list") List<Map<Long, Integer>> list);

    List<QwExternalContactVOTime> selectQwExternalContactListVOByUserIds(@Param("ids") List<String> ids);

    /**
     * 查询外部联系人是否宠粉
     * @param id
     * @return
     */
    @Select("select is_repeat from qw_external_contact where user_id=${userId} limit 1")
    Integer selectQwIsRepeat(@Param("userId") Long id);
}