package com.sanzhisoft.mybatis.boc.customer.mapper;
import com.sanzhisoft.mybatis.boc.customer.entity.CustomerMarketing;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
/**
* <p>
* 客户营销主表 Mapper 接口
* </p>
*
* @author syetem
* @since 2020-09-18
*/
public interface CustomerMarketingMapper extends BaseMapper<CustomerMarketing> {
@Select("<script>"+
"SELECT " +
" d.`name` as positionName, " +
" a.user_name as userName, " +
" a.org_name AS orgName, " +
" count(a.id) countNumber, " +
" ( " +
" SELECT " +
" count(1) " +
" FROM " +
" t_customer_marketing a2 " +
" WHERE " +
" a.user_name = a2.user_name " +
" AND a.org_id = a2.org_id " +
" AND ( " +
" a2.`status` = 'FALL' " +
" OR a2.`status` = 'FINISH' " +
" ) " +
" ) AS finishNumber, " +
" IFNULL( " +
" ( " +
" SELECT " +
" SUM(a3.create_integral) " +
" FROM " +
" t_customer_marketing a3 " +
" WHERE " +
" a3.user_name = a.user_name " +
" ), " +
" 0 " +
" ) + IFNULL( " +
" ( " +
" SELECT " +
" SUM(a4.share_integral) " +
" FROM " +
" t_customer_marketing a4 " +
" WHERE " +
" a4.share_user_name = a.user_name " +
" ), " +
" 0 " +
" ) AS integral " +
"FROM " +
" t_customer_marketing a " +
"LEFT JOIN t_system_dictionary d ON a.position = d.keyword WHERE 1=1 " +
" <if test='keywords != null'> "+
" and (a.user_name LIKE CONCAT('%',#{keywords},'%') OR a.org_name LIKE CONCAT('%',#{keywords},'%') )"+
" </if> "+
" <if test='position != null'> "+
" and a.position = #{position} "+
" </if> "+
" <if test='startTime != null and endTime != null' > "+
" and a.create_time BETWEEN #{startTime} AND #{endTime} "+
" </if> "+
" GROUP BY " +
" d.`name`, " +
" a.user_name, " +
" a.org_id, " +
" a.org_name " +
"ORDER BY " +
" field( " +
" d.`name`, " +
" '支行正职', " +
" '支行公司副职', " +
" '支行个金副职', " +
" '部门正职', " +
" '部门副职' " +
" ), " +
" countNumber DESC, " +
" finishNumber DESC" +
" limit #{pageIndex},#{pageSize}" +
"</script>" )
List<Map<String,Object>> getCustomerMarketingCountPageList(
@Param("keywords")String keywords,
@Param("position")String position,
@Param("pageIndex") int pageIndex,
@Param("pageSize") int pageSize,
@Param("startTime") String startTime,
@Param("endTime") String endTime);
}
@Select("<script>" +
" SELECT DISTINCT a.user_name as userName,(select u.mobile from t_user u where u.`name` = a.user_name LIMIT 1) as mobile," +
" IFNULL(b.hourCount, 0) as hourCount, IFNULL(b.dayCount, 0) as dayCount, IFNULL(b.weekCount, 0) as weekCount FROM t_assembly_receiver a LEFT JOIN " +
"( SELECT a.user_name, ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / 60, 1 ) AS hourCount," +
" ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / (60 * 8), 1 ) AS dayCount," +
" ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / (60 * 8 * 5), 1 ) AS weekCount " +
"FROM t_assembly_receiver a WHERE a.apply_status = 1 AND a.create_time BETWEEN #{startTime} AND #{endTime} GROUP BY a.user_name ) b " +
"ON a.user_name = b.user_name WHERE a.user_type = 'APPLY_USER' AND a.org_id IN " +
" <foreach item='item' index='index' collection='orgIds' open='(' separator=',' close=')'>" +
" #{item}" +
" </foreach>" +
"</script>")
List<LinkedHashMap<String,Object>> getAssemblyCountList(String startTime, String endTime,List<Long> orgIds);
需要注意的就是
1.如果有动态参数,在sql语句的前后需要加入<script></script>
2.如果有使用like不能直接写 LIKE '%#{keywords}%',这样识别不出来keywords这个参数,会任务少了一个参数,会报错:Parameter index out of range (1 > number of parameters, which is 0),可以修改为 LIKE CONCAT('%',#{keywords},'%')
也可以直接传输Page对象来进行分页
@Override
public IPage<AssemblyOverView> getAssemblyOvertimePageList(Integer pageIndex, Integer pageSize, String keywords, String startTime, String endTime, UserRolePrincipal userRolePrincipal, String status) {
Page<AssemblyOverView> page = new Page<>(pageIndex, pageSize);
IPage<AssemblyOverView> returnPage = assemblyMapper.getAssemblyOvertimePageList(page,keywords,startTime,endTime);
return returnPage;
}
@Select("<script>"+
" SELECT" +
" a.id," +
" a.`name`," +
" a.create_user_name as 'createUserName'," +
" a.create_time," +
" a.end_time," +
" b.user_id as applyUserId,"+
" b.user_name as applyUserName,"+
" b.user_type," +
" a.`status`," +
" b.apply_status" +
" FROM" +
" t_assembly a," +
" t_assembly_receiver b WHERE " +
" a.id = b.assembly_id " +
" AND NOW() > a.end_time " +
" AND b.user_type = 'APPLY_USER' " +
" AND b.apply_status = 0" +
" AND a.`status` = 'UN_ARCHIVE' "+
" <if test='startTime != null and endTime != null' > "+
" and a.create_time BETWEEN #{startTime} AND #{endTime} "+
" </if> "+
" <if test='keywords != null'> "+
" and (a.name LIKE CONCAT('%',#{keywords},'%'))"+
" </if> "+
"</script>" )
IPage<AssemblyOverView> getAssemblyOvertimePageList(Page<AssemblyOverView> page,@Param("keywords") String keywords,@Param("startTime") String startTime,@Param("endTime") String endTime);
