13-6-首页数据--线索转化龙虎榜（实现）
大约 3 分钟
13-6-首页数据--线索转化龙虎榜（实现）
需求：
在一段时间范围内，统计哪些部门员工将线索转商机的最多，并计算转化率
对于线索总数来说，这里的时间范围指的是线索的创建时间
对于每个人的转化率来说：这里的时间范围指的是线索转商机的时间
难度级别 A+级
接口名/index/salesStatistic
请求方式Get
参数列表
传入参数：
/index/salesStatistic?beginCreateTime=2021-02-02&endCreateTime=2022-02-17
beginCreateTime 开始时间
endCreateTime 结束时间
返回值：
{
"msg":"操作成功",
"code":200,
"data":[
{
"create_by":"admin", 用户名称
"deptName":"研发部门", 部门名称
"num":70, 转化条数
"radio":9.32 转化率
},
{
"create_by":"xiansuo",
"deptName":"新人创建演示用部门",
"num":22,
"radio":2.93
},
{
"create_by":"zhuoyihang",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"jack",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"liuxingyu",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"zhangxiaoyan",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"zhangzhang",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"zhangyifan",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"zhanger",
"deptName":"市场部",
"num":20,
"radio":2.66
},
{
"create_by":"zhangguan",
"deptName":"市场部",
"num":20,
"radio":2.66
}
]
}
步骤：
1.阅读产品文档（接口名，请求方式，参数列表）
2.根据产品的返回值和接收参数构建VO类
3.编写mapper层操作数据库
4.编写service层操作数据
5.编写controller层接收参数和返回数据
线索转化龙虎榜接口
线索转化龙虎榜不要求按照用户，需要查询出所有人中排名最高的前10名
统计出所有的线索数量
（1）huike-clues工程 TbClueMapper.xml添加SQL
<select id="countAllClues" resultType="int">
select count(id) from tb_clue
<where>
<if test="beginCreateTime != null and beginCreateTime != ''">
and date_format(create_time,'%y-%m-%d') >= date_format(#{beginCreateTime},'%y-%m-%d')
</if>
<if test="endCreateTime != null and endCreateTime != ''"><!-- -->
and date_format(create_time,'%y-%m-%d') <= date_format(#{endCreateTime},'%y-%m-%d')
</if>
</where>
</select>
（2）huike-clues工程的TbClueMapper添加方法定义
public int countAllClues(@Param("beginCreateTime") String beginCreateTime, @Param("endCreateTime") String endCreateTime);
线索转化龙虎榜列表查询
（1）huike-clues工程 TbClueMapper.xml添加SQL
<select id="countAllClueByUser" resultType="java.util.Map">
SELECT DISTINCT
(a.user_name) AS create_by,
COUNT(DISTINCT(a.assign_id)) AS num,
c.`dept_name` AS deptName
FROM
`tb_assign_record` a
LEFT JOIN `sys_user` b
ON a.`user_name` = b.`user_name`
LEFT JOIN `sys_dept` c
ON b.`dept_id` = c.`dept_id`
WHERE a.`type` = 0
<if test="indexVo.beginCreateTime != null and indexVo.beginCreateTime != ''"><!-- 开始创建时间 -->
AND a.create_time BETWEEN #{indexVo.beginCreateTime} AND #{indexVo.endCreateTime}
</if>
GROUP BY a.user_name
ORDER BY num DESC
LIMIT 10
</select>
（2）huike-clues工程的TbClueMapper添加方法定义
public List<Map<String,Object>> countAllContractByUser(@Param("indexVo")IndexStatisticsVo vo);
龙虎榜转换率计算
（1）IReportService新增方法定义
public List<Map<String, Object>> clueChangeStatisticsForIndex(IndexStatisticsVo request);
（2）ReportServiceImpl新增方法
/**
* 线索转化龙虎榜
*/
@Override
public List<Map<String, Object>> clueChangeStatisticsForIndex(IndexStatisticsVo request) {
int allclues= clueMapper.countAllClues(request.getBeginCreateTime(),request.getEndCreateTime());
List<Map<String,Object>> list= clueMapper.countAllClueByUser(request);
//计算转换率
for (Map<String, Object> datum : list) {
Long num= (Long) datum.get("num");
datum.put("radio",getRadio(allclues,num));
}
return list;
}
private BigDecimal getRadio(Integer all,Long num) {
if(all.intValue()==0){
return new BigDecimal(0);
}
BigDecimal numBigDecimal = new BigDecimal(num);
BigDecimal allBigDecimal = new BigDecimal(all);
BigDecimal divide = numBigDecimal.divide(allBigDecimal,4,BigDecimal.ROUND_HALF_UP);
return divide.multiply(new BigDecimal(100));
}
IndexController新增方法
@GetMapping("/salesStatistic")
public AjaxResult salesStatistics(IndexStatisticsVo request){
request.setBeginCreateTime(request.getBeginCreateTime()+" 00:00:00");
request.setEndCreateTime(request.getEndCreateTime()+" 23:59:59");
List<Map<String,Object>> list= reportService.clueChangeStatisticsForIndex(request);
return AjaxResult.success(list);
}