Merge remote-tracking branch 'origin/jtdev' into jtdev

guoshilong

2023-08-04 86c547dd80a96a05d8b448b4512eaf38899b02ad

Merge remote-tracking branch 'origin/jtdev' into jtdev

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

@@ -174,6 +174,7 @@
     * @param page
     * @return
     */
    @InterceptorIgnore(tenantLine = "true")
    List<CallTaskStatistic> getCallResultList(@Param("page") IPage<CallTaskStatistic> page,
                                              @Param("list") List<String> list,
                                              @Param("callTaskStatistic") CallTaskStatistic callTaskStatistic);

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

@@ -204,24 +204,17 @@
        select  aab."guid",
        aab."ad_name" as adName,
        createTime,
        ifnull(sum(totalCount),0) calleeNumberCount,
        ifnull(sum(connectCount),0) + ifnull(sum(connectCount1),0) + ifnull(sum(connectCount2),0) + ifnull(sum(isSmsFillCount),0) as connectionCount,
        ifnull(sum(resConnectCount1),0) - ifnull(sum(resConnectCount2),0) refuseConnectCount,
        ifnull(sum(twoNotConnectCount),0) twoNoConnectCount,
        ifnull(sum(threeNotConnectCount),0) - ifnull(sum(threeNotConnectCount1),0) threeNoConnectCount
        ifnull(sum(totalCount),0) as calleeNumberCount,
        ifnull(sum(connectCount),0) as connectionCount,
        ifnull(sum(resConnectCount),0) as refuseConnectCount,
        ifnull(sum(threeNotConnectCount),0) as threeNoConnectCount
        from SJZT_MD."att_ad_base"  aab
        left join (
            select
            ifnull(sum(case when ctc1."call_result"!='' then 1 else 0 end),0) as totalCount,
            ifnull(sum(case when ctc1."call_result"='200000' then 1 else 0 end),0) as connectCount,
            ifnull(sum(case when ctc2."call_result"='200000' then 1 else 0 end),0) as connectCount1,
            ifnull(sum(case when ctc3."call_result"='200000' then 1 else 0 end),0) as connectCount2,
            ifnull(sum(case when ctc3.task_id!='' then 1 else 0 end),0) as isSmsFillCount,
            ifnull(sum(case when ctc2."call_result"='200003' then 1 else 0 end),0) as twoNotConnectCount,
            ifnull(sum(case when ctc3."call_result"='200003' then 1 else 0 end),0) as threeNotConnectCount,
            ifnull(sum(case when ctc3.task_id!='' and ctc3."call_result"='200003' then 1 else 0 end),0) as threeNotConnectCount1,
            ifnull(sum(case when ctc3."call_result"='200005' then 1 else 0 end),0) as resConnectCount1,
            ifnull(sum(case when ctc3.task_id!='' and ctc3."call_result"='200005' then 1 else 0 end),0) as resConnectCount2,
            ifnull(sum(case when ctc1."call_result"='200000' then 1 when scnfr.id is not null then 1 else 0 end),0) as connectCount,
            ifnull(sum(case when scnfr.id is not null then 0 when ctc1."call_result"='200003' then 1 else 0 end),0) as threeNotConnectCount,
            ifnull(sum(case when scnfr.id is not null then 0 when ctc1."call_result"='200005' then 1 else 0 end),0) as resConnectCount,
            DATE_FORMAT(ctc1."gmt_create",'%Y-%m-%d') as createTime,
            aab."guid",
            aab."ad_grad",
@@ -229,30 +222,22 @@
            aab."ad_name" as adName
            from
            (
            select ctc."gmt_create",ctc."call_id",ctc."task_id",JSON_VALUE(ctc."call_task_callee_content",'$."$水库编码$"') as waterCode,ctr."call_result" from "SJZT_ODS"."call_task_callee" ctc
            left join SJZT_ODS."call_task" ct on ct."id" = ctc."task_id"
            left join SJZT_ODS."call_task_result" ctr on ctr."call_id" = ctc."call_id"
            left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
            where date_format(ct."task_schedule_time",'%Y-%m-%d') = #{callTaskStatistic.createTime} and sdct.type ='1'
                select ctr.*,waterCode from SJZT_ODS."call_task_result" ctr right join
                (
                    select max("gmt_create") "gmt_create",waterCode,"callee_number" from (
                        select ctr.*,ctc.waterCode from SJZT_ODS."call_task_result" ctr right join (
                        select date_format(create_time,'%Y-%m-%d') time,wm_concat(task_id) task_id from YWXT.sm_daily_call_task group by date_format(create_time,'%Y-%m-%d')
                        ) sdct on locate(ctr."task_id",sdct.task_id) >0
                        left join (
                        select "call_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
                        ) ctc on ctr."call_id" = ctc."call_id"
                        where 1=1 and sdct.time = #{callTaskStatistic.createTime}
                    ) a group by date_format("gmt_create",'%Y-%m-%d'),waterCode,"callee_number"
                ) b on ctr."gmt_create" = b."gmt_create" and ctr."callee_number" = b."callee_number"
            ) ctc1
            left join (
            select ctc."gmt_create",ctc."call_id",ctc."task_id",JSON_VALUE(ctc."call_task_callee_content",'$."$水库编码$"') as waterCode,ctr."call_result" from "SJZT_ODS"."call_task_callee" ctc
            left join SJZT_ODS."call_task" ct on ct."id" = ctc."task_id"
            left join SJZT_ODS."call_task_result" ctr on ctr."call_id" = ctc."call_id"
            left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
            where date_format(ct."task_schedule_time",'%Y-%m-%d') = #{callTaskStatistic.createTime} and sdct.type ='2'
            ) ctc2 on ctc1.waterCode= ctc2.waterCode
            left join (
            select ctc."gmt_create",ctc."call_id",ctc."task_id",JSON_VALUE(ctc."call_task_callee_content",'$."$水库编码$"') as waterCode,ctr."call_result",scnfr.task_id from "SJZT_ODS"."call_task_callee" ctc
            left join SJZT_ODS."call_task" ct on ct."id" = ctc."task_id"
            left join SJZT_ODS."call_task_result" ctr on ctr."call_id" = ctc."call_id"
            left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = ctr."call_id"
            left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
            where date_format(ct."task_schedule_time",'%Y-%m-%d') = #{callTaskStatistic.createTime} and sdct.type ='3'
            ) ctc3 on ctc1.waterCode= ctc3.waterCode

            left join "SJZT_MD"."att_res_base" arb on ctc1.waterCode = arb."code"
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000') or aab."guid" = arb."interior_ad_guid"
            left join "SJZT_MD"."att_ad_base" aab on aab."guid" = arb."interior_ad_guid" or aab."guid" = concat(arb."ad_guid",'000000')
            left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = ctc1."call_id"
            where 1=1 and aab."ad_name" is not null
            <if test="callTaskStatistic.createTime!=null and callTaskStatistic.createTime!=''">
                and date_format(ctc1."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
@@ -266,7 +251,7 @@
            group by DATE_FORMAT(ctc1."gmt_create",'%Y-%m-%d'),aab."ad_name",aab."guid",aab."ad_grad",aab."p_ad_code"
        ) a
        <if test="callTaskStatistic.grad==1">
            on aab."guid" = a."p_ad_code" where aab."p_ad_code" = #{callTaskStatistic.adCode}
            on aab."guid" = a."guid" or aab."guid" = a."p_ad_code" where aab."p_ad_code" = #{callTaskStatistic.adCode}
        </if>
        <if test="callTaskStatistic.grad==2">
            on aab."guid" = a."guid" where aab."p_ad_code" = #{callTaskStatistic.adCode}
@@ -374,7 +359,7 @@
                group by DATE_FORMAT(ctr."gmt_create",'%Y-%m-%d'),aab."ad_name",aab."guid",aab."ad_grad",aab."p_ad_code"
            ) a
            <if test="callTaskStatistic.grad==1">
                on aab."guid" = a."p_ad_code" where aab."p_ad_code" = #{callTaskStatistic.adCode}
                on aab."guid" = a."guid" or aab."guid" = a."p_ad_code" where aab."p_ad_code" = #{callTaskStatistic.adCode}
            </if>
            <if test="callTaskStatistic.grad==2">
                on aab."guid" = a."guid" where aab."p_ad_code" = #{callTaskStatistic.adCode}
@@ -593,44 +578,39 @@
    <!--按日期行政区呼叫结果详情列表信息-->
    <select id="getCallResultList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select
        a.*,
        aab."guid",aab."ad_grad",aab."p_ad_code",aab."ad_name" as adName,
        e.*,
        aab."guid",aab."ad_name" as adName,
        bu.real_name as realName,
        if(ifnull(scnfr.call_id,0)='0','','已反馈') isSmsFill,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,2) as cityName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,3) as countyName,
        case when aab."ad_grad"= 3 then aab."p_ad_name"
            when e."ad_grad"= 2 then e."ad_name"
            when e."ad_grad"= 3 then e."p_ad_name"
            end as cityName,
        case when e."ad_grad"= 4 then e."p_ad_name"
            when e."ad_grad"= 3 then e."ad_name"
            end as countyName,
        arb."name" as waterName
        from (
                select date_format(ctc."gmt_create",'%Y-%m-%d') "gmt_create",ctc."call_id",
                ctc."task_id",JSON_VALUE(ctc."call_task_callee_content",'$."$水库编码$"') as waterCode ,ctr."call_result" as callResult,ctr."callee_number"
                from "SJZT_ODS"."call_task_callee" ctc
                select date_format(ctc."gmt_create",'%Y-%m-%d') "gmt_create",ctc."call_id",ctr."ad_grad",ctr."ad_name",ctr."p_ad_name",
                ctc."task_id",JSON_VALUE(ctc."call_task_callee_content",'$."$水库编码$"') as waterCode ,ctr."call_result" as callResult,ctr."callee_number"
                from "SJZT_ODS"."call_task_callee" ctc
                join (
                    select d.* from (
                        select * from (
                        select ctr."call_id",ctr."callee_number",ctr."call_result" from SJZT_ODS."call_task_result" ctr
                        left join SJZT_ODS."call_task" ct on ct."id" = ctr."task_id"
                        left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
                        where date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                        and sdct.type = '1' and ctr."call_result" = '200000'
                        ) a
                        union all
                        select * from (
                        select ctr."call_id",ctr."callee_number",ctr."call_result"  from SJZT_ODS."call_task_result" ctr
                        left join SJZT_ODS."call_task" ct on ct."id" = ctr."task_id"
                        left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
                        where date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                        and sdct.type = '2' and ctr."call_result" = '200000'
                        ) b
                        union all
                        select * from (
                        select ctr."call_id",ctr."callee_number",ctr."call_result"  from SJZT_ODS."call_task_result" ctr

                        left join SJZT_ODS."call_task" ct on ct."id" = ctr."task_id"
                        left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
                        where date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                        and sdct.type = '3'
                        ) c
                    select d.*,aab."ad_grad",aab."ad_name",aab."p_ad_name" from (
                    select ctr.*,waterCode from SJZT_ODS."call_task_result" ctr right join
                    (
                    select max("gmt_create") "gmt_create",waterCode,"callee_number" from (
                    select ctr.*,ctc.waterCode from SJZT_ODS."call_task_result" ctr right join (
                    select date_format(create_time,'%Y-%m-%d') time,wm_concat(task_id) task_id from YWXT.sm_daily_call_task group by date_format(create_time,'%Y-%m-%d')
                    ) sdct on locate(ctr."task_id",sdct.task_id) >0 left join (
                    select "call_id",JSON_VALUE("call_task_callee_content",'$."$水库编码$"') as waterCode from "SJZT_ODS"."call_task_callee"
                    ) ctc on ctr."call_id" = ctc."call_id"
                    where 1=1
                    and sdct.time = #{callTaskStatistic.createTime}
                    ) a group by date_format("gmt_create",'%Y-%m-%d'),waterCode,"callee_number"
                    ) b on ctr."gmt_create" = b."gmt_create" and ctr."callee_number" = b."callee_number"
                    ) d left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = d."call_id"
                    left join "SJZT_MD"."att_res_base" arb on d.waterCode = arb."code"
                    left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
                    where 1=1
                    <if test="callTaskStatistic.count==2">
                        and d."call_result" = '200000' or scnfr.task_id is not null
@@ -643,12 +623,17 @@
                    </if>
                    union all

                    select ctr."call_id",ctr."callee_number",ctr."call_result" from SJZT_ODS."call_task_result" ctr
                    left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = ctr."call_id"
                    left join SJZT_ODS."call_task" ct on ct."id" = ctr."task_id"
                    left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."id"
                    where date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                    and sdct.task_id is null
                    select ctr.*,waterCode,aab."ad_grad",aab."ad_name",aab."p_ad_name" from SJZT_ODS."call_task_result" ctr
                    left join (
                    select "call_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 "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = ctr."call_id"
                    left join SJZT_ODS."call_task" ct on ct."id" = ctr."task_id"
                    left join YWXT.sm_daily_call_task sdct on sdct.task_id = ct."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 date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                    and sdct.task_id is null
                    <if test="callTaskStatistic.count==2">
                        and ctr."call_result" = '200000' and scnfr.id is not null
                    </if>
@@ -659,11 +644,11 @@
                        and ctr."call_result" = '200005' and scnfr.id is null
                    </if>
                )ctr on ctc."call_id" = ctr."call_id"
        )a
        left join "SJZT_MD"."att_res_base" arb on locate(arb."code",a.waterCode)>0
        left join "SJZT_MD"."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = a."call_id"
        left join "YWXT".blade_user bu on bu.phone = a."callee_number" and bu.is_deleted = 0
        )e
        left join "SJZT_MD"."att_res_base" arb on locate(arb."code",e.waterCode)>0
        left join "SJZT_MD"."att_ad_base" aab on aab."guid" = concat(arb."ad_guid",'000000')
        left join "YWXT".sm_call_not_fill_record scnfr on scnfr.call_id = e."call_id"
        left join "YWXT".blade_user bu on bu.phone = e."callee_number" and bu.is_deleted = 0
        where aab."ad_name" is not null
        <if test="list != null and list.size()>0">
            and aab."ad_code" in

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

@@ -477,21 +477,21 @@
        Integer totalCalleeNumberCount = 0;
        Integer totalConnectionCount = 0;
        Integer totalThreeNoConnectCount = 0;
        Integer totalTwoNoConnectCount = 0;
//        Integer totalTwoNoConnectCount = 0;
        Integer totalRefuseConnectCount = 0;
        // 遍历
        for (CallTaskStatistic taskStatistic : callTaskStatistics) {
            totalCalleeNumberCount += Integer.parseInt(taskStatistic.getCalleeNumberCount());
            totalConnectionCount += Integer.parseInt(taskStatistic.getConnectionCount());
            totalThreeNoConnectCount += taskStatistic.getThreeNoConnectCount();
            totalTwoNoConnectCount += taskStatistic.getTwoNoConnectCount();
//            totalTwoNoConnectCount += taskStatistic.getTwoNoConnectCount();
            totalRefuseConnectCount += taskStatistic.getRefuseConnectCount();
        }
        // 设置数据
        callTaskStatistic.setCalleeNumberCount(totalCalleeNumberCount.toString());
        callTaskStatistic.setConnectionCount(totalConnectionCount.toString());
        callTaskStatistic.setThreeNoConnectCount(totalThreeNoConnectCount);
        callTaskStatistic.setTwoNoConnectCount(totalTwoNoConnectCount);
//        callTaskStatistic.setTwoNoConnectCount(totalTwoNoConnectCount);
        callTaskStatistic.setRefuseConnectCount(totalRefuseConnectCount);
        callTaskStatistic.setCreateTime(callTaskStatistics.get(0).getCreateTime());
        // 查询当前行政区名称