完整mybatis应用
<?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.xy.xyd.rest.crm.repository.CrmClientRepository" >
<resultMap id="ResultMap" type="com.xy.xyd.rest.crm.entity.CrmClient" >
<id column="ID" property="id" jdbcType="BIGINT" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="LATITUDE" property="latitude" jdbcType="DOUBLE" />
<result column="IS_DEL" property="isDel" jdbcType="INTEGER" />
<result column="CREATE_BY" property="createBy" jdbcType="BIGINT" />
<result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
<result column="MODIFY_BY" property="modifyBy" jdbcType="BIGINT" />
<result column="LAST_CHANGE_TIME" property="lastChangeTime" jdbcType="TIMESTAMP" />
</resultMap>
<sql id="Base_Column_List" >
ID, NAME, SHORT_NAME, COMMENT, CITY_ID, AREA_ID, PRO_ID,LAST_CHANGE_TIME
</sql>
<!--find查询列 -->
<sql id="Find_Column_List">
ID
</sql>
<sql id="Where_Clause">
<if test="id != null" >
and a.ID = #{id,jdbcType=BIGINT}
</if>
<if test="inClientIds != null">
and a.ID in (${inClientIds})
</if>
<if test="outClientIds != null">
and a.ID not in (${outClientIds})
</if>
<if test="proId != null" >
and PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="lastChangeTime != null" >
and a.LAST_CHANGE_TIME = #{lastChangeTime,jdbcType=TIMESTAMP}
</if>
</sql>
<sql id="Order_By">
order by
<if test="sort!=null">
<if test="sort=="id"">
a.ID ${order}
</if>
<if test="sort=="name"">
a.NAME ${order}
</if>
<if test="sort=="sortOrder"">
a.SORT_ORDER ${order}
</if>
</if>
<if test="sort==null">
a.ID
</if>
</sql>
<sql id="Paging">
<if test="firstResult!=null"> limit #{firstResult,jdbcType=DECIMAL} ,
#{offset,jdbcType=DECIMAL}
</if>
</sql>
<!--按Id查询 -->
<select id="selectById" resultMap="ResultMap" parameterType="Long">
select
<include refid="Base_Column_List" />
from crm_client a where id=#{id}
</select>
<!--按Id删除 -->
<delete id="deleteById" parameterType="Long">
delete a from crm_client a where id=#{id}
</delete>
<!--条件查询，只查询ID -->
<select id="selectToId" resultType="long" parameterType="map">
select
<include refid="Find_Column_List" />
from crm_client a where 1=1
<include refid="Where_Clause" />
<include refid="Order_By"/>
<include refid="Paging" />
</select>
<select id="select" resultMap="ResultMap" parameterType="map">
select
<include refid="Base_Column_List" />
from crm_client a
where 1=1
<include refid="Where_Clause" />
</select>
<!-- 同一家公司，在省的范围内，简称查重 ,如有重复，返回所有重复的客户简称及归属人姓名 -->
<select id="findRepeatListInProvince" resultType="java.util.Map" parameterType="map">
select
a.ID AS id,
a.NAME AS name,
a.SHORT_NAME AS shortName,
a.SALE_MAN AS saleMan
from crm_client a
where 1=1
<include refid="Where_Clause" />
<include refid="Paging" />
</select>
<select id="findClientInfo" resultType="java.util.Map" parameterType="map" >
select
a.NAME AS name,
a.SHORT_NAME AS shortName,
a.LATITUDE AS latitude,
b.DICT_VALUE AS typeValue,
a.SALE_MAN AS saleMan,
c.NAME AS saleManName,
a.LEVEL AS level,
d.DICT_VALUE AS levelValue,
a.INTRODUCTION AS introduction
from crm_client a
LEFT JOIN cm_dict_type b on a.TYPE = b.DICT_CODE AND b.type ='Client_Type'
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.type ='Client_Level'
where 1=1
<include refid="Where_Clause" />
</select>
<delete id="delete" parameterType="java.lang.Long" >
delete from crm_client
where ID = #{id,jdbcType=BIGINT}
</delete>
<insert id="save" parameterType="com.xy.xyd.rest.crm.entity.CrmClient" useGeneratedKeys="true" keyProperty="id">
insert into crm_client
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="name != null" >
NAME,
</if>
<if test="cityId != null" >
CITY_ID,
</if>
<if test="areaId != null" >
AREA_ID,
</if>
<if test="proId != null" >
PRO_ID,
</if>
<if test="lastChangeTime != null" >
LAST_CHANGE_TIME,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="cityId != null" >
#{cityId,jdbcType=BIGINT},
</if>
<if test="areaId != null" >
#{areaId,jdbcType=BIGINT},
</if>
<if test="proId != null" >
#{proId,jdbcType=BIGINT},
</if>
<if test="lastChangeTime != null" >
#{lastChangeTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="update" parameterType="com.xy.xyd.rest.crm.entity.CrmClient" >
update crm_client
<set >
<if test="name != null" >
NAME = #{name,jdbcType=VARCHAR},
</if>
<if test="cityId != null" >
CITY_ID = #{cityId,jdbcType=BIGINT},
</if>
<if test="areaId != null" >
AREA_ID = #{areaId,jdbcType=BIGINT},
</if>
<if test="proId != null" >
PRO_ID = #{proId,jdbcType=BIGINT},
</if>
<if test="lastChangeTime != null" >
LAST_CHANGE_TIME = #{lastChangeTime,jdbcType=TIMESTAMP},
</if>
</set>
where ID = #{id,jdbcType=BIGINT}
</update>
<!-- 批量修改 -->
<update id="updateBatch" parameterType="List" >
<foreach collection="list" item="obj" index="index" separator=" ; " >
update crm_client
<set >
<if test="obj.name != null" >
NAME = #{obj.name,jdbcType=VARCHAR},
</if>
<if test="obj.shortName != null" >
SHORT_NAME = #{obj.shortName,jdbcType=VARCHAR},
</if>
<if test="obj.ownCompanyId != null" >
OWN_COMPANY_ID = #{obj.ownCompanyId,jdbcType=BIGINT},
</if>
<if test="obj.proId != null" >
PRO_ID = #{obj.proId,jdbcType=BIGINT},
</if>
<if test="obj.lastChangeTime != null" >
LAST_CHANGE_TIME = #{obj.lastChangeTime,jdbcType=TIMESTAMP},
</if>
</set>
where ID = #{obj.id,jdbcType=BIGINT}
</foreach>
</update>
<select id="count" resultType="java.lang.Integer" parameterType="map" >
select count(1) from crm_client a
where 1=1
<include refid="Where_Clause" />
</select>
<select id="countShareClientNUmber" resultType="java.lang.Integer" parameterType="map" >
select count(*) from crm_client a
where 1=1
<if test="isShared != null" >
and a.IS_SHARED = #{isShared,jdbcType=INTEGER}
</if>
<if test="saleMan != null" >
and a.SALE_MAN = #{saleMan,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="levelArray" index="index" collection="level"
open="(" separator="," close=")">
#{levelArray}
</foreach>
</if>
</select>
<select id="findClientList" resultType="java.util.Map" parameterType="map">
select
distinct(a.ID) AS id,
a.NAME AS name,
b.MOBILE AS mobile,
a.SALE_MAN AS saleMan,
c.NAME AS saleManName,
a.IS_SHARED AS isShared,
a.LEVEL AS level,
d.DICT_VALUE AS levelValue,
date_format(a.CREATE_TIME,'%Y%m%d%H%i%S') as createTime,
date_format(a.LAST_TRACK_TIME,'%Y%m%d%H%i%S') as lastTrackTime
from crm_client a
LEFT JOIN crm_contact b on a.ID = b.CLIENT_ID AND b.IS_DEFAULT_CONTACT = 1
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.TYPE = 'Client_Level'
<if test="mainBusiness != null" >
inner JOIN crm_main_business_client e on a.ID = e.CLIENT_ID
</if>
where 1=1 and a.IS_DEL = 0 and a.SALE_MAN>-3
<!-- 省份筛选 -->
<if test="addProvince != null" >
and a.ADD_PROVINCE IN
<foreach item="typeArray" index="index" collection="addProvince"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 主营行业筛选 -->
<if test="mainBusiness != null" >
and e.MAIN_BUSINESS_ID IN
<foreach item="typeArray" index="index" collection="mainBusiness"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 归属筛选 -->
<if test="ids != null" >
and a.SALE_MAN IN
<foreach item="typeArray" index="index" collection="ids"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="typeArray" index="index" collection="level"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 类型筛选 -->
<if test="type != null" >
and a.TYPE IN
<foreach item="typeArray" index="index" collection="type"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 跟踪日期 -->
<if test="endTrackTime != null" >
<!-- and ( a.LAST_TRACK_TIME IS NULL OR a.LAST_TRACK_TIME <![CDATA[ < ]]> #{endTrackTime,jdbcType=TIMESTAMP}) -->
and a.LAST_TRACK_TIME <= #{endTrackTime,jdbcType=TIMESTAMP}
</if>
order by a.CREATE_TIME desc
<include refid="Paging" />
</select>
<select id="countClientListNumber" resultType="java.lang.Integer" parameterType="map">
select
count(distinct(a.ID))
from crm_client a
LEFT JOIN crm_contact b on a.ID = b.CLIENT_ID AND b.IS_DEFAULT_CONTACT = 1
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.TYPE = 'Client_Level'
<if test="mainBusiness != null" >
inner JOIN crm_main_business_client e on a.ID = e.CLIENT_ID
</if>
where 1=1 and a.IS_DEL = 0 and a.SALE_MAN>-3
<if test="item != null" >
and a.ITEM = #{item,jdbcType=VARCHAR}
</if>
<!-- 省份筛选 -->
<if test="addProvince != null" >
and a.ADD_PROVINCE IN
<foreach item="typeArray" index="index" collection="addProvince"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 主营行业筛选 -->
<if test="mainBusiness != null" >
and e.MAIN_BUSINESS_ID IN
<foreach item="typeArray" index="index" collection="mainBusiness"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 归属筛选 -->
<if test="ids != null" >
and a.SALE_MAN IN
<foreach item="typeArray" index="index" collection="ids"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="typeArray" index="index" collection="level"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 跟踪日期 -->
<if test="endTrackTime != null" >
and (a.LAST_TRACK_TIME IS NULL OR a.LAST_TRACK_TIME <![CDATA[ < ]]> #{endTrackTime,jdbcType=BIGINT})
</if>
<!-- 类型筛选 -->
<if test="type != null" >
and a.TYPE IN
<foreach item="typeArray" index="index" collection="type"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
</select>
<!-- 客户搜索 begin zyf -->
<select id="selectSearchToId" resultType="long" parameterType="map">
select
a.ID
from crm_client a
<if test="tell != null" >
LEFT JOIN crm_contact b ON a.ID = b.CLIENT_ID
</if>
where 1=1
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<if test="userIds != null" >
and a.SALE_MAN in(${userIds})
</if>
<if test="isDel != null" >
and a.IS_DEL = #{isDel,jdbcType=INTEGER}
</if>
and(
<if test="name != null" >
a.NAME like '%${name}%'
or a.SHORT_NAME like '%${name}%'
</if>
<if test="tell != null" >
or a.TELL like '${tell}%'
OR (a.SALE_MAN > 0 and b.MOBILE like '${tell}%')
</if>
)
<include refid="Order_By"/>
<include refid="Paging" />
</select>
<select id="selectSearchCount" resultType="java.lang.Integer" parameterType="map" >
select COUNT(DISTINCT a.ID) from crm_client a
<if test="tell != null" >
LEFT JOIN crm_contact b ON a.ID = b.CLIENT_ID
</if>
where 1=1 and a.SALE_MAN > -3
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<if test="userIds != null" >
and a.SALE_MAN in(${userIds})
</if>
<if test="isDel != null" >
and a.IS_DEL = #{isDel,jdbcType=INTEGER}
</if>
and (
<if test="name != null" >
a.NAME like '%${name}%'
or a.SHORT_NAME like '%${name}%'
</if>
<if test="tell != null" >
or a.TELL like '${tell}%'
OR (a.SALE_MAN > 0 and b.MOBILE like '${tell}%')
</if>
)
</select>
<!-- 客户搜索 end -->
<select id="findClientContactList" resultType="java.util.HashMap" parameterType="map">
select
c.ID as clientId,
c.NAME clientName,
c.short_name shortName,
c.OWN_COMPANY_ID companyId,
c.ADD_PROVINCE as province,
d.dict_value as type,
dt.dict_value as level,
c.SALE_MAN as saleMan,
c.IS_SHARED as isShared,
co.ID as contactId,
co.`NAME` as contactName,
co.MOBILE as mobile,
co.SEX as sex,
co.POSITION as position
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
left JOIN cm_dict_type d on d.DICT_CODE=c.type and d.type='Client_Type'
left join cm_dict_type dt on dt.DICT_CODE=c.level and dt.type='Client_Level'
where co.IS_DEFAULT_CONTACT =1 and c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
ORDER BY c.CREATE_TIME desc
<include refid="Paging" />
</select>
<select id="findClientContactListCount" resultType="java.lang.Integer" parameterType="map">
SELECT count(1)
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
left JOIN cm_dict_type d on d.DICT_CODE=c.type and d.type='Client_Level'
left join cm_dict_type dt on dt.DICT_CODE=c.level and dt.type='Client_Level'
where co.IS_DEFAULT_CONTACT =1 and c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
</select>
<resultMap id="ResultMapExport" type="com.xy.xyd.rest.crm.entity.CrmClientExport" >
<id column="ID" property="id" jdbcType="BIGINT" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="CITY_ID" property="cityId" jdbcType="BIGINT" />
<result column="AREA_ID" property="areaId" jdbcType="BIGINT" />
<result column="PRO_ID" property="proId" jdbcType="BIGINT" />
<!-- 新增字段 -->
<result column="contactId" property="contactId" jdbcType="BIGINT" />
<result column="contactName" property="contactName" jdbcType="VARCHAR" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="mobile" property="mobile" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="INTEGER" />
<result column="position" property="position" jdbcType="VARCHAR" />
<result column="org" property="org" jdbcType="VARCHAR" />
<result column="mainRes" property="mainRes" jdbcType="VARCHAR" />
<result column="Qq" property="Qq" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
</resultMap>
<select id="exportClient" resultMap="ResultMapExport" parameterType="map">
select
c.ID ,
c.NAME ,
c.IS_SHARED ,
c.`LEVEL`,
co.ID as contactId,
co.`NAME` as contactName,
co.TENCENT_QQ as Qq,
co.EMAIL as email
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
where c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
ORDER BY c.CREATE_TIME desc
</select>
<!-- 根据客户自动降级设置的条件：查询客户 {某个企业、多少天为跟踪的、指定级别的客户}c.CREATE_TIME < '' 过滤掉新创建的客户，其也没有符合条件的跟进记录-->
<select id="selectNotTrack" resultMap="ResultMap" parameterType="map">
select c.*
from crm_client c
where c.OWN_COMPANY_ID=#{ownCompanyId,jdbcType=BIGINT} and c.LEVEL=#{level,jdbcType=VARCHAR}
<!--铁单、重点客户： 有上级无下属的员工｛才执行跑批：自动降级跑批使 -->
<if test="saleMan != null">
and c.SALE_MAN=#{saleMan,jdbcType=BIGINT}
</if>
<!-- 客户创建时间未达到指定天数，对应的日期，其必然不符合要求 -->
and date(c.LAST_CHANGE_TIME) <![CDATA[ < ]]> date(#{createTime,jdbcType=TIMESTAMP})
and not exists( <!-- 不存在跟踪记录，则说明，30天没有跟踪了 -->
<!-- 30天内的跟踪记录 -->
SELECT 1 from crm_track_recording r
where r.CLIENT_ID=c.ID and r.NEW_LEVEL=#{level,jdbcType=VARCHAR}
and r.NEW_SALE_MAN=c.SALE_MAN and
date( r.CREATE_TIME) <![CDATA[ > ]]> date(#{createTime,jdbcType=TIMESTAMP}) and r.TYPE=1
)
</select>
<!-- 根据客户自动降级设置的条件：查询客户 {某个企业、多少天未升级的、指定级别的客户}c.CREATE_TIME < '' 过滤掉新创建的客户，其也没有符合条件的跟进记录
r.OLD_LEVEL!=r.NEW_LEVEL 查询出升级记录
-->
<select id="selectNotUp" resultMap="ResultMap" parameterType="map">
select c.*
from crm_client c
where c.OWN_COMPANY_ID=#{ownCompanyId,jdbcType=BIGINT} and c.LEVEL=#{level,jdbcType=VARCHAR}
<!-- 客户创建时间未达到指定天数，对应的日期，其必然不符合要求 -->
and date(#{createTime,jdbcType=TIMESTAMP}) <![CDATA[ > ]]>date(c.LAST_CHANGE_TIME )
</select>
</mapper>
<?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.xy.xyd.rest.crm.repository.CrmClientRepository" >
<resultMap id="ResultMap" type="com.xy.xyd.rest.crm.entity.CrmClient" >
<id column="ID" property="id" jdbcType="BIGINT" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="LATITUDE" property="latitude" jdbcType="DOUBLE" />
<result column="IS_DEL" property="isDel" jdbcType="INTEGER" />
<result column="CREATE_BY" property="createBy" jdbcType="BIGINT" />
<result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
<result column="MODIFY_BY" property="modifyBy" jdbcType="BIGINT" />
<result column="LAST_CHANGE_TIME" property="lastChangeTime" jdbcType="TIMESTAMP" />
</resultMap>
<sql id="Base_Column_List" >
ID, NAME, SHORT_NAME, COMMENT, CITY_ID, AREA_ID, PRO_ID,LAST_CHANGE_TIME
</sql>
<!--find查询列 -->
<sql id="Find_Column_List">
ID
</sql>
<sql id="Where_Clause">
<if test="id != null" >
and a.ID = #{id,jdbcType=BIGINT}
</if>
<if test="inClientIds != null">
and a.ID in (${inClientIds})
</if>
<if test="outClientIds != null">
and a.ID not in (${outClientIds})
</if>
<if test="proId != null" >
and PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="lastChangeTime != null" >
and a.LAST_CHANGE_TIME = #{lastChangeTime,jdbcType=TIMESTAMP}
</if>
</sql>
<sql id="Order_By">
order by
<if test="sort!=null">
<if test="sort=="id"">
a.ID ${order}
</if>
<if test="sort=="name"">
a.NAME ${order}
</if>
<if test="sort=="sortOrder"">
a.SORT_ORDER ${order}
</if>
</if>
<if test="sort==null">
a.ID
</if>
</sql>
<sql id="Paging">
<if test="firstResult!=null"> limit #{firstResult,jdbcType=DECIMAL} ,
#{offset,jdbcType=DECIMAL}
</if>
</sql>
<!--按Id查询 -->
<select id="selectById" resultMap="ResultMap" parameterType="Long">
select
<include refid="Base_Column_List" />
from crm_client a where id=#{id}
</select>
<!--按Id删除 -->
<delete id="deleteById" parameterType="Long">
delete a from crm_client a where id=#{id}
</delete>
<!--条件查询，只查询ID -->
<select id="selectToId" resultType="long" parameterType="map">
select
<include refid="Find_Column_List" />
from crm_client a where 1=1
<include refid="Where_Clause" />
<include refid="Order_By"/>
<include refid="Paging" />
</select>
<select id="select" resultMap="ResultMap" parameterType="map">
select
<include refid="Base_Column_List" />
from crm_client a
where 1=1
<include refid="Where_Clause" />
</select>
<!-- 同一家公司，在省的范围内，简称查重 ,如有重复，返回所有重复的客户简称及归属人姓名 -->
<select id="findRepeatListInProvince" resultType="java.util.Map" parameterType="map">
select
a.ID AS id,
a.NAME AS name,
a.SHORT_NAME AS shortName,
a.SALE_MAN AS saleMan
from crm_client a
where 1=1
<include refid="Where_Clause" />
<include refid="Paging" />
</select>
<select id="findClientInfo" resultType="java.util.Map" parameterType="map" >
select
a.NAME AS name,
a.SHORT_NAME AS shortName,
a.LATITUDE AS latitude,
b.DICT_VALUE AS typeValue,
a.SALE_MAN AS saleMan,
c.NAME AS saleManName,
a.LEVEL AS level,
d.DICT_VALUE AS levelValue,
a.INTRODUCTION AS introduction
from crm_client a
LEFT JOIN cm_dict_type b on a.TYPE = b.DICT_CODE AND b.type ='Client_Type'
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.type ='Client_Level'
where 1=1
<include refid="Where_Clause" />
</select>
<delete id="delete" parameterType="java.lang.Long" >
delete from crm_client
where ID = #{id,jdbcType=BIGINT}
</delete>
<insert id="save" parameterType="com.xy.xyd.rest.crm.entity.CrmClient" useGeneratedKeys="true" keyProperty="id">
insert into crm_client
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="name != null" >
NAME,
</if>
<if test="cityId != null" >
CITY_ID,
</if>
<if test="areaId != null" >
AREA_ID,
</if>
<if test="proId != null" >
PRO_ID,
</if>
<if test="lastChangeTime != null" >
LAST_CHANGE_TIME,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="cityId != null" >
#{cityId,jdbcType=BIGINT},
</if>
<if test="areaId != null" >
#{areaId,jdbcType=BIGINT},
</if>
<if test="proId != null" >
#{proId,jdbcType=BIGINT},
</if>
<if test="lastChangeTime != null" >
#{lastChangeTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="update" parameterType="com.xy.xyd.rest.crm.entity.CrmClient" >
update crm_client
<set >
<if test="name != null" >
NAME = #{name,jdbcType=VARCHAR},
</if>
<if test="cityId != null" >
CITY_ID = #{cityId,jdbcType=BIGINT},
</if>
<if test="areaId != null" >
AREA_ID = #{areaId,jdbcType=BIGINT},
</if>
<if test="proId != null" >
PRO_ID = #{proId,jdbcType=BIGINT},
</if>
<if test="lastChangeTime != null" >
LAST_CHANGE_TIME = #{lastChangeTime,jdbcType=TIMESTAMP},
</if>
</set>
where ID = #{id,jdbcType=BIGINT}
</update>
<!-- 批量修改 -->
<update id="updateBatch" parameterType="List" >
<foreach collection="list" item="obj" index="index" separator=" ; " >
update crm_client
<set >
<if test="obj.name != null" >
NAME = #{obj.name,jdbcType=VARCHAR},
</if>
<if test="obj.shortName != null" >
SHORT_NAME = #{obj.shortName,jdbcType=VARCHAR},
</if>
<if test="obj.ownCompanyId != null" >
OWN_COMPANY_ID = #{obj.ownCompanyId,jdbcType=BIGINT},
</if>
<if test="obj.proId != null" >
PRO_ID = #{obj.proId,jdbcType=BIGINT},
</if>
<if test="obj.lastChangeTime != null" >
LAST_CHANGE_TIME = #{obj.lastChangeTime,jdbcType=TIMESTAMP},
</if>
</set>
where ID = #{obj.id,jdbcType=BIGINT}
</foreach>
</update>
<select id="count" resultType="java.lang.Integer" parameterType="map" >
select count(1) from crm_client a
where 1=1
<include refid="Where_Clause" />
</select>
<select id="countShareClientNUmber" resultType="java.lang.Integer" parameterType="map" >
select count(*) from crm_client a
where 1=1
<if test="isShared != null" >
and a.IS_SHARED = #{isShared,jdbcType=INTEGER}
</if>
<if test="saleMan != null" >
and a.SALE_MAN = #{saleMan,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="levelArray" index="index" collection="level"
open="(" separator="," close=")">
#{levelArray}
</foreach>
</if>
</select>
<select id="findClientList" resultType="java.util.Map" parameterType="map">
select
distinct(a.ID) AS id,
a.NAME AS name,
b.MOBILE AS mobile,
a.SALE_MAN AS saleMan,
c.NAME AS saleManName,
a.IS_SHARED AS isShared,
a.LEVEL AS level,
d.DICT_VALUE AS levelValue,
date_format(a.CREATE_TIME,'%Y%m%d%H%i%S') as createTime,
date_format(a.LAST_TRACK_TIME,'%Y%m%d%H%i%S') as lastTrackTime
from crm_client a
LEFT JOIN crm_contact b on a.ID = b.CLIENT_ID AND b.IS_DEFAULT_CONTACT = 1
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.TYPE = 'Client_Level'
<if test="mainBusiness != null" >
inner JOIN crm_main_business_client e on a.ID = e.CLIENT_ID
</if>
where 1=1 and a.IS_DEL = 0 and a.SALE_MAN>-3
<!-- 省份筛选 -->
<if test="addProvince != null" >
and a.ADD_PROVINCE IN
<foreach item="typeArray" index="index" collection="addProvince"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 主营行业筛选 -->
<if test="mainBusiness != null" >
and e.MAIN_BUSINESS_ID IN
<foreach item="typeArray" index="index" collection="mainBusiness"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 归属筛选 -->
<if test="ids != null" >
and a.SALE_MAN IN
<foreach item="typeArray" index="index" collection="ids"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="typeArray" index="index" collection="level"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 类型筛选 -->
<if test="type != null" >
and a.TYPE IN
<foreach item="typeArray" index="index" collection="type"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 跟踪日期 -->
<if test="endTrackTime != null" >
<!-- and ( a.LAST_TRACK_TIME IS NULL OR a.LAST_TRACK_TIME <![CDATA[ < ]]> #{endTrackTime,jdbcType=TIMESTAMP}) -->
and a.LAST_TRACK_TIME <= #{endTrackTime,jdbcType=TIMESTAMP}
</if>
order by a.CREATE_TIME desc
<include refid="Paging" />
</select>
<select id="countClientListNumber" resultType="java.lang.Integer" parameterType="map">
select
count(distinct(a.ID))
from crm_client a
LEFT JOIN crm_contact b on a.ID = b.CLIENT_ID AND b.IS_DEFAULT_CONTACT = 1
LEFT JOIN cm_xyduser c on a.SALE_MAN = c.ID
LEFT JOIN cm_dict_type d on a.LEVEL = d.DICT_CODE AND d.TYPE = 'Client_Level'
<if test="mainBusiness != null" >
inner JOIN crm_main_business_client e on a.ID = e.CLIENT_ID
</if>
where 1=1 and a.IS_DEL = 0 and a.SALE_MAN>-3
<if test="item != null" >
and a.ITEM = #{item,jdbcType=VARCHAR}
</if>
<!-- 省份筛选 -->
<if test="addProvince != null" >
and a.ADD_PROVINCE IN
<foreach item="typeArray" index="index" collection="addProvince"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 主营行业筛选 -->
<if test="mainBusiness != null" >
and e.MAIN_BUSINESS_ID IN
<foreach item="typeArray" index="index" collection="mainBusiness"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 归属筛选 -->
<if test="ids != null" >
and a.SALE_MAN IN
<foreach item="typeArray" index="index" collection="ids"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<!-- 级别筛选 -->
<if test="level != null" >
and a.LEVEL IN
<foreach item="typeArray" index="index" collection="level"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
<!-- 跟踪日期 -->
<if test="endTrackTime != null" >
and (a.LAST_TRACK_TIME IS NULL OR a.LAST_TRACK_TIME <![CDATA[ < ]]> #{endTrackTime,jdbcType=BIGINT})
</if>
<!-- 类型筛选 -->
<if test="type != null" >
and a.TYPE IN
<foreach item="typeArray" index="index" collection="type"
open="(" separator="," close=")">
#{typeArray}
</foreach>
</if>
</select>
<!-- 客户搜索 begin zyf -->
<select id="selectSearchToId" resultType="long" parameterType="map">
select
a.ID
from crm_client a
<if test="tell != null" >
LEFT JOIN crm_contact b ON a.ID = b.CLIENT_ID
</if>
where 1=1
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<if test="userIds != null" >
and a.SALE_MAN in(${userIds})
</if>
<if test="isDel != null" >
and a.IS_DEL = #{isDel,jdbcType=INTEGER}
</if>
and(
<if test="name != null" >
a.NAME like '%${name}%'
or a.SHORT_NAME like '%${name}%'
</if>
<if test="tell != null" >
or a.TELL like '${tell}%'
OR (a.SALE_MAN > 0 and b.MOBILE like '${tell}%')
</if>
)
<include refid="Order_By"/>
<include refid="Paging" />
</select>
<select id="selectSearchCount" resultType="java.lang.Integer" parameterType="map" >
select COUNT(DISTINCT a.ID) from crm_client a
<if test="tell != null" >
LEFT JOIN crm_contact b ON a.ID = b.CLIENT_ID
</if>
where 1=1 and a.SALE_MAN > -3
<if test="ownCompanyId != null" >
and a.OWN_COMPANY_ID = #{ownCompanyId,jdbcType=BIGINT}
</if>
<if test="userIds != null" >
and a.SALE_MAN in(${userIds})
</if>
<if test="isDel != null" >
and a.IS_DEL = #{isDel,jdbcType=INTEGER}
</if>
and (
<if test="name != null" >
a.NAME like '%${name}%'
or a.SHORT_NAME like '%${name}%'
</if>
<if test="tell != null" >
or a.TELL like '${tell}%'
OR (a.SALE_MAN > 0 and b.MOBILE like '${tell}%')
</if>
)
</select>
<!-- 客户搜索 end -->
<select id="findClientContactList" resultType="java.util.HashMap" parameterType="map">
select
c.ID as clientId,
c.NAME clientName,
c.short_name shortName,
c.OWN_COMPANY_ID companyId,
c.ADD_PROVINCE as province,
d.dict_value as type,
dt.dict_value as level,
c.SALE_MAN as saleMan,
c.IS_SHARED as isShared,
co.ID as contactId,
co.`NAME` as contactName,
co.MOBILE as mobile,
co.SEX as sex,
co.POSITION as position
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
left JOIN cm_dict_type d on d.DICT_CODE=c.type and d.type='Client_Type'
left join cm_dict_type dt on dt.DICT_CODE=c.level and dt.type='Client_Level'
where co.IS_DEFAULT_CONTACT =1 and c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
ORDER BY c.CREATE_TIME desc
<include refid="Paging" />
</select>
<select id="findClientContactListCount" resultType="java.lang.Integer" parameterType="map">
SELECT count(1)
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
left JOIN cm_dict_type d on d.DICT_CODE=c.type and d.type='Client_Level'
left join cm_dict_type dt on dt.DICT_CODE=c.level and dt.type='Client_Level'
where co.IS_DEFAULT_CONTACT =1 and c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
</select>
<resultMap id="ResultMapExport" type="com.xy.xyd.rest.crm.entity.CrmClientExport" >
<id column="ID" property="id" jdbcType="BIGINT" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
<result column="CITY_ID" property="cityId" jdbcType="BIGINT" />
<result column="AREA_ID" property="areaId" jdbcType="BIGINT" />
<result column="PRO_ID" property="proId" jdbcType="BIGINT" />
<!-- 新增字段 -->
<result column="contactId" property="contactId" jdbcType="BIGINT" />
<result column="contactName" property="contactName" jdbcType="VARCHAR" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="mobile" property="mobile" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="INTEGER" />
<result column="position" property="position" jdbcType="VARCHAR" />
<result column="org" property="org" jdbcType="VARCHAR" />
<result column="mainRes" property="mainRes" jdbcType="VARCHAR" />
<result column="Qq" property="Qq" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
</resultMap>
<select id="exportClient" resultMap="ResultMapExport" parameterType="map">
select
c.ID ,
c.NAME ,
c.IS_SHARED ,
c.`LEVEL`,
co.ID as contactId,
co.`NAME` as contactName,
co.TENCENT_QQ as Qq,
co.EMAIL as email
from crm_client c
left join crm_contact co on c.ID=co.CLIENT_ID
where c.OWN_COMPANY_ID=#{companyId,jdbcType=BIGINT}
<if test="outLevel != null" >
and c.`LEVEL` not in(${outLevel})
</if>
<if test="proId != null" >
and c.PRO_ID = #{proId,jdbcType=BIGINT}
</if>
<if test="addProvince != null" >
and c.ADD_PROVINCE like concat('%',#{addProvince,jdbcType=VARCHAR},'%')
</if>
<if test="mainBusiness != null" >
and (select count(b.ID) from crm_main_business_client b where b.CLIENT_ID=c.ID and b.MAIN_BUSINESS_ID=#{mainBusiness,jdbcType=VARCHAR})>0
</if>
<if test="type != null" >
and c.TYPE = #{type,jdbcType=VARCHAR}
</if>
<if test="level != null" >
and c.LEVEL = #{level,jdbcType=VARCHAR}
</if>
<if test="likeName != null" >
and ( c.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') or co.NAME like concat('%',#{likeName,jdbcType=VARCHAR},'%') )
</if>
ORDER BY c.CREATE_TIME desc
</select>
<!-- 根据客户自动降级设置的条件：查询客户 {某个企业、多少天为跟踪的、指定级别的客户}c.CREATE_TIME < '' 过滤掉新创建的客户，其也没有符合条件的跟进记录-->
<select id="selectNotTrack" resultMap="ResultMap" parameterType="map">
select c.*
from crm_client c
where c.OWN_COMPANY_ID=#{ownCompanyId,jdbcType=BIGINT} and c.LEVEL=#{level,jdbcType=VARCHAR}
<!--铁单、重点客户： 有上级无下属的员工｛才执行跑批：自动降级跑批使 -->
<if test="saleMan != null">
and c.SALE_MAN=#{saleMan,jdbcType=BIGINT}
</if>
<!-- 客户创建时间未达到指定天数，对应的日期，其必然不符合要求 -->
and date(c.LAST_CHANGE_TIME) <![CDATA[ < ]]> date(#{createTime,jdbcType=TIMESTAMP})
and not exists( <!-- 不存在跟踪记录，则说明，30天没有跟踪了 -->
<!-- 30天内的跟踪记录 -->
SELECT 1 from crm_track_recording r
where r.CLIENT_ID=c.ID and r.NEW_LEVEL=#{level,jdbcType=VARCHAR}
and r.NEW_SALE_MAN=c.SALE_MAN and
date( r.CREATE_TIME) <![CDATA[ > ]]> date(#{createTime,jdbcType=TIMESTAMP}) and r.TYPE=1
)
</select>
<!-- 根据客户自动降级设置的条件：查询客户 {某个企业、多少天未升级的、指定级别的客户}c.CREATE_TIME < '' 过滤掉新创建的客户，其也没有符合条件的跟进记录
r.OLD_LEVEL!=r.NEW_LEVEL 查询出升级记录
-->
<select id="selectNotUp" resultMap="ResultMap" parameterType="map">
select c.*
from crm_client c
where c.OWN_COMPANY_ID=#{ownCompanyId,jdbcType=BIGINT} and c.LEVEL=#{level,jdbcType=VARCHAR}
<!-- 客户创建时间未达到指定天数，对应的日期，其必然不符合要求 -->
and date(#{createTime,jdbcType=TIMESTAMP}) <![CDATA[ > ]]>date(c.LAST_CHANGE_TIME )
</select>
</mapper>
