智能外呼统计查询修改，详情查询修改

zhongrj

2023-06-21 135c4983fd132e3ddb24cf449417c589a5d67a0a

智能外呼统计查询修改，详情查询修改

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

@@ -114,11 +114,23 @@
     */
    private String adCode;


    /**
     * 区域级别 1:省级 2：市级
     */
    private Integer grad;

    /**
     * 连续三天未接通数量
     */
    private Integer threeNoConnectCount;

    /**
     * 连续两天未接通数量
     */
    private Integer twoNoConnectCount;

    /**
     * 前一天拒接的数量
     */
    private Integer refuseConnectCount;
}

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

@@ -124,4 +124,21 @@
     */
    @DS("znwh")
    List<String> getBeforeThreeDayCallNotConnect(@Param("params") Map<String, Object> map);

    /**
     *  查询 按区域统计连续三天，连续两天未接通，前一天拒接对应的数量
     * @param callTaskStatistic
     * @param list
     * @param threeDay
     * @param twoDay
     * @param oneDay
     * @return
     */
    @DS("zt")
    @InterceptorIgnore(tenantLine = "true")
    List<CallTaskStatistic> getNotCallConnectStatisticList(@Param("callTaskStatistic") CallTaskStatistic callTaskStatistic,
                                                           @Param("list") List<String> list,
                                                           @Param("threeDay") Map<String, Object> threeDay,
                                                           @Param("twoDay") Map<String, Object> twoDay,
                                                           @Param("oneDay") Map<String, Object> oneDay);
}

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

@@ -121,6 +121,12 @@
        <if test="callTaskResult.waterName!=null and callTaskResult.waterName!=''">
            and arb."name" like concat('%',#{callTaskResult.waterName},'%')
        </if>
        <if test="callTaskResult.isSmsFill!=null and callTaskResult.isSmsFill=='已反馈'">
            and scnfr.call_id is not null
        </if>
        <if test="callTaskResult.isSmsFill!=null and callTaskResult.isSmsFill=='未反馈'">
            and scnfr.call_id is null and ctr."call_result"!='200000'
        </if>
        <if test="callTaskResult.userName!=null and callTaskResult.userName!=''">
            and ctc.userName like concat('%',#{callTaskResult.userName},'%')
        </if>
@@ -361,4 +367,100 @@
        )b where num=3
    </select>

    <!--查询三天都未接通的人员-mysql-->
    <select id="getNotCallConnectStatisticList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select  aab."guid",
        sum(case when ifnull(cc.num,0)='0' then 0 else 1 end) refuseConnectCount,
        sum(case when ifnull(bb.num,0)='0' then 0 else 1 end) twoNoConnectCount,
        sum(case when ifnull(aa.num,0)='0' then 0 else 1 end) threeNoConnectCount,
        aab."ad_name" as adName
        from SJZT_MD."att_ad_base" aab
        left join (
            select b.*,aab."guid",aab."ad_grad",aab."p_ad_code",aab."ad_name" as adName from (
                select "callee_number",waterCodes,LENGTH(waterCodes)- LENGTH(REPLACE(waterCodes,',','')) num from (
                    select  ctr."callee_number",WM_Concat(ctc.waterCode) waterCodes from (
                        select "call_id","task_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
                    ) ctc join (
                    select "call_id","callee_number","call_result",date_format("gmt_create",'%Y-%m-%d') "gmt_create" from SJZT_ODS."call_task_result"
                    where date_format("gmt_create",'%Y-%m-%d') >= #{threeDay.startTime} and date_format("gmt_create",'%Y-%m-%d') &lt;= #{threeDay.endTime}
                    and "call_result"='200003'
                    )ctr on ctc."call_id" = ctr."call_id" group by ctr."callee_number"
                ) a where LENGTH(waterCodes)- LENGTH(REPLACE(waterCodes,',',''))>1
            )b
            left join "SJZT_MD"."att_res_base" arb on locate(arb."code",b.waterCodes)>0
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
            where aab."ad_name" is not null
        ) aa
        <if test="callTaskStatistic.grad==1">
            on aab."guid" = aa."p_ad_code"
        </if>
        <if test="callTaskStatistic.grad==2">
            on aab."guid" = aa."guid"
        </if>
        <if test="callTaskStatistic.grad==3">
            on aab."guid" = aa."guid"
        </if>
        left join (
            select b.*,aab."guid",aab."ad_grad",aab."p_ad_code",aab."ad_name" as adName from (
                select "callee_number",waterCodes,LENGTH(waterCodes)- LENGTH(REPLACE(waterCodes,',','')) num from (
                    select  ctr."callee_number",WM_Concat(ctc.waterCode) waterCodes from (
                    select "call_id","task_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
                    ) ctc join (
                    select "call_id","callee_number","call_result",date_format("gmt_create",'%Y-%m-%d') "gmt_create" from SJZT_ODS."call_task_result"
                    where date_format("gmt_create",'%Y-%m-%d') >= #{twoDay.startTime} and date_format("gmt_create",'%Y-%m-%d') &lt;= #{twoDay.endTime}
                    and "call_result"='200003'
                    )ctr on ctc."call_id" = ctr."call_id" group by ctr."callee_number"
                ) a where LENGTH(waterCodes)- LENGTH(REPLACE(waterCodes,',',''))=1
            )b
            left join "SJZT_MD"."att_res_base" arb on locate(arb."code",b.waterCodes)>0
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
            where aab."ad_name" is not null
        ) bb
        <if test="callTaskStatistic.grad==1">
            on aab."guid" = bb."p_ad_code"
        </if>
        <if test="callTaskStatistic.grad==2">
            on aab."guid" = bb."guid"
        </if>
        <if test="callTaskStatistic.grad==3">
            on aab."guid" = bb."guid"
        </if>
        left join (
            select b.*,aab."guid",aab."ad_grad",aab."p_ad_code",aab."ad_name" as adName from (
                select "callee_number",waterCodes,LENGTH(waterCodes)- LENGTH(REPLACE(waterCodes,',','')) num from (
                    select  ctr."callee_number",WM_Concat(ctc.waterCode) waterCodes from (
                    select "call_id","task_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
                    ) ctc join (
                    select "call_id","callee_number","call_result",date_format("gmt_create",'%Y-%m-%d') "gmt_create" from SJZT_ODS."call_task_result"
                    where date_format("gmt_create",'%Y-%m-%d') >= #{oneDay.startTime} and date_format("gmt_create",'%Y-%m-%d') &lt;= #{oneDay.endTime}
                    and "call_result"='200005'
                    )ctr on ctc."call_id" = ctr."call_id" group by ctr."callee_number"
                ) a where 1=1
            )b
            left join "SJZT_MD"."att_res_base" arb on locate(arb."code",b.waterCodes)>0
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
            where aab."ad_name" is not null
        ) cc
        <if test="callTaskStatistic.grad==1">
            on aab."guid" = cc."p_ad_code"
        </if>
        <if test="callTaskStatistic.grad==2">
            on aab."guid" = cc."guid"
        </if>
        <if test="callTaskStatistic.grad==3">
            on aab."guid" = cc."guid"
        </if>
        <if test="callTaskStatistic.grad==1">
            where aab."p_ad_code" = #{callTaskStatistic.adCode}
        </if>
        <if test="callTaskStatistic.grad==2">
            where aab."p_ad_code" = #{callTaskStatistic.adCode}
        </if>
        <if test="callTaskStatistic.grad==3">
            where aab."ad_code" = #{callTaskStatistic.adCode}
        </if>
        group by aab."ad_name",aab."guid"
        order by aab."guid"
    </select>

</mapper>

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

@@ -22,6 +22,7 @@
import org.springframework.util.MultiValueMap;
import org.springframework.web.client.RestTemplate;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
@@ -272,6 +273,11 @@
     */
    @Override
    public Object getOutgoingStatisticList(IPage<CallTaskStatistic> page,CallTaskStatistic callTaskStatistic) {
        if (null==callTaskStatistic.getCreateTime() || callTaskStatistic.getCreateTime().equals("")){
            long l = System.currentTimeMillis();
            // 设置当前时间的前一天
            callTaskStatistic.setCreateTime(new SimpleDateFormat("yyyy-MM-dd").format(new Date(l - 60*60*24*1000)));
        }
        List<String> list = new ArrayList<>();
        if (null!= callTaskStatistic.getAdName() && !callTaskStatistic.getAdName().equals("")){
            // 查询所有下级区域code
@@ -285,7 +291,26 @@
        if (null!= callTaskStatistic.getIsPage()){
            return callTaskMapper.getOutgoingStatisticList(null,callTaskStatistic,list);
        }
        return page.setRecords(callTaskMapper.getOutgoingStatisticList(page,callTaskStatistic,list));
        // 按天查询数据
        List<CallTaskStatistic> outgoingStatisticList = callTaskMapper.getOutgoingStatisticList(page, callTaskStatistic, list);
        // 查询 按区域统计连续三天，连续两天未接通，前一天拒接对应的数量
        Map<String, Object> threeDay = computationTime(callTaskStatistic.getCreateTime(), 2);
        Map<String, Object> twoDay = computationTime(callTaskStatistic.getCreateTime(), 1);
        Map<String, Object> oneDay = computationTime(callTaskStatistic.getCreateTime(), 0);
        // 查询
        List<CallTaskStatistic> notCallConnectStatisticList = callTaskMapper.getNotCallConnectStatisticList(callTaskStatistic,list,threeDay,twoDay,oneDay);
        // 遍历
        for (CallTaskStatistic taskStatistic : outgoingStatisticList) {
            for (CallTaskStatistic statistic : notCallConnectStatisticList) {
                if (taskStatistic.getGuid().equals(statistic.getGuid())){
                    taskStatistic.setThreeNoConnectCount(statistic.getThreeNoConnectCount());
                    taskStatistic.setTwoNoConnectCount(statistic.getTwoNoConnectCount());
                    taskStatistic.setRefuseConnectCount(statistic.getRefuseConnectCount());
                }
            }
        }
        // 返回
        return page.setRecords(outgoingStatisticList);
    }

    /**
@@ -533,6 +558,32 @@
    }

    /**
     * 计算时间
     * @param time 传入的时间
     * @param day 天数
     */
    private Map<String,Object> computationTime(String time,Integer day) {
        HashMap<String, Object> map = new HashMap<>(2);
        long l = 0;
        try {
            l = new SimpleDateFormat("yyyy-MM-dd").parse(time).getTime();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long m = 60*60*24*1000*day;
        // 相减得到时间
        Long x = l-m;
        // 格式化日期
        String startTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date(x));
        String endTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date(l));
        // 加入到参数中
        map.put("startTime",startTime);
        map.put("endTime",endTime);
        // 返回
        return map;
    }

    /**
     * 调用中台接口查询出所有需要发任务的水库巡查责任人相关信息（该处需要将是否超汛限条件传入）
     * @param params
     * @return