智能外呼统计查询修改

zhongrj

2023-05-15 97d3e08e74b9387bb5ecc8b77ab7557fd0b8443b

智能外呼统计查询修改

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/controller/CallController.java

@@ -85,4 +85,16 @@
        return R.data(callService.getCallTaskResultList(callTaskResult));
    }

    /**
     * 查询智能外呼统计数据-按日期-行政区统计
     * @param callTaskStatistic
     * @param query
     * @return
     */
    @GetMapping("/getOutgoingStatisticList")
    public R getOutgoingStatisticList(CallTaskStatistic callTaskStatistic, Query query){
        // 返回
        return R.data(callService.getOutgoingStatisticList(Condition.getPage(query),callTaskStatistic));
    }

}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/mapper/CallTaskMapper.java

@@ -63,4 +63,14 @@
     */
    @DS("zt")
    List<String> getAllChildrenAreaByAreaCode(@Param("areaCode") String areaCode);

    /**
     * 查询智能外呼统计数据-按日期-行政区统计
     * @param callTaskStatistic
     * @return
     */
    @DS("zt")
    List<CallTaskStatistic> getOutgoingStatisticList(@Param("page") IPage<CallTaskStatistic> page,
                                                     @Param("callTaskStatistic") CallTaskStatistic callTaskStatistic,
                                                     @Param("list") List<String> list);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/mapper/CallTaskMapper.xml

@@ -144,4 +144,39 @@
        start with "ad_code" = #{areaCode}
        CONNECT BY PRIOR "ad_code" =  "p_ad_code"
    </select>

    <!--查询智能外呼统计数据-按日期-行政区统计-->
    <select id="getOutgoingStatisticList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select a.*,round(a.connectionCount/a.calleeNumberCount*100,2) callCompletingRate from
        (
            select
            count(*) as calleeNumberCount,
            ifnull(sum(case when ctr."call_result"='200000' then 1 else 0 end),0) as connectionCount,
            DATE_FORMAT(ctr."gmt_create",'%Y-%m-%d') as createTime,
            aab."ad_name" as adName,
            aab."guid"
            from "SJZT_ODS"."call_task_result" ctr
            left join
            (
            select "call_id","task_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
            ) ctc on ctr."call_id" = ctc."call_id"
            left join "SJZT_MD"."att_res_base" arb on ctc.waterCode = arb."code"
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
            where 1=1 and aab."ad_name" is not null
            <if test="callTaskStatistic.startTime!=null and callTaskStatistic.startTime!=''">
                and date_format(ctr."gmt_create",'%Y-%m-%d') >= #{callTaskStatistic.startTime}
            </if>
            <if test="callTaskStatistic.endTime!=null and callTaskStatistic.endTime!=''">
                and date_format(ctr."gmt_create",'%Y-%m-%d') &lt;= #{callTaskStatistic.endTime}
            </if>
            <if test="list != null and list.size()>0">
                and aab."ad_code" in
                <foreach collection="list" item="adCode" separator ="," open="("  close=")">
                    #{adCode}
                </foreach>
            </if>
            group by DATE_FORMAT(ctr."gmt_create",'%Y-%m-%d'),aab."ad_name",aab."guid"
        ) a
        order by createTime desc,callCompletingRate desc
    </select>
</mapper>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/service/CallService.java

@@ -47,4 +47,11 @@
     * @return
     */
    Object getCallTaskResultList(CallTaskResultVO callTaskResult);

    /**
     * 查询智能外呼统计数据-按日期-行政区统计
     * @param callTaskStatistic
     * @return
     */
    Object getOutgoingStatisticList(IPage<CallTaskStatistic> page,CallTaskStatistic callTaskStatistic);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/service/impl/CallServiceImpl.java

@@ -252,6 +252,29 @@
    }

    /**
     * 查询智能外呼统计数据-按日期-行政区统计
     * @param callTaskStatistic
     * @return
     */
    @Override
    public Object getOutgoingStatisticList(IPage<CallTaskStatistic> page,CallTaskStatistic callTaskStatistic) {
        List<String> list = new ArrayList<>();
        if (null!= callTaskStatistic.getAdName() && !callTaskStatistic.getAdName().equals("")){
            // 查询所有下级区域code
            list = getAllChildrenAreaByAreaCode(callTaskStatistic.getAdName());
        }
        // 勾选导出行政区 id 组装
        if (null!= callTaskStatistic.getIds() && !callTaskStatistic.getIds().equals("")){
            list = Arrays.asList(callTaskStatistic.getIds().split(","));
        }
        // 判断是否分页
        if (null!= callTaskStatistic.getIsPage()){
            return callTaskMapper.getOutgoingStatisticList(null,callTaskStatistic,list);
        }
        return page.setRecords(callTaskMapper.getOutgoingStatisticList(page,callTaskStatistic,list));
    }

    /**
     * 查询所有下级区域
     * @param areaCode
     * @return

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/intelligentCall/vo/CallTaskStatistic.java

@@ -84,5 +84,30 @@
     */
    private String endTime;

    /**
     * 区域名称
     */
    private String adName;

    /**
     * 区域guid
     */
    private String guid;

    /**
     * 接通率
     */
    private String callCompletingRate;

    /**
     * 是否分页 1:是 （不为空代表不分页，查询导出全部）
     */
    private Integer isPage;

    /**
     * 导出勾选的区域id
     */
    private String ids;


}