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

guoshilong

2023-05-18 314aeb39f049f26d02611d8a426a2322c7ccfac0

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

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

@@ -145,48 +145,103 @@
        CONNECT BY PRIOR "ad_code" =  "p_ad_code"
    </select>

<!--    &lt;!&ndash;查询智能外呼统计数据-按日期-行政区统计&ndash;&gt;-->
<!--    <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>    -->

    <!--查询智能外呼统计数据-按日期-行政区统计-->
    <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,
        select
        b.*,
        case when b.calleeNumberCount>0 then round(b.connectionCount/b.calleeNumberCount*100,2) else 0 end callCompletingRate
        from (
            select  aab."guid",
            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}
            createTime,
            ifnull(sum(totalCount),0) calleeNumberCount,
            ifnull(sum(connectCount),0) connectionCount
            from SJZT_MD."att_ad_base"  aab
            left join (
                select
                count(*) as totalCount,
                ifnull(sum(case when ctr."call_result"='200000' then 1 else 0 end),0) as connectCount,
                DATE_FORMAT(ctr."gmt_create",'%Y-%m-%d') as createTime,
                aab."guid",
                aab."ad_grad",
                aab."p_ad_code",
                aab."ad_name" as adName
                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.createTime!=null and callTaskStatistic.createTime!=''">
                    and date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                </if>
                <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>
                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}
            </if>
            <if test="callTaskStatistic.endTime!=null and callTaskStatistic.endTime!=''">
                and date_format(ctr."gmt_create",'%Y-%m-%d') &lt;= #{callTaskStatistic.endTime}
            <if test="callTaskStatistic.grad==2">
                on aab."guid" = a."guid" where aab."p_ad_code" = #{callTaskStatistic.adCode}
            </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 test="callTaskStatistic.grad==3">
                on aab."guid" = a."guid" where aab."ad_code" = #{callTaskStatistic.adCode}
            </if>
            group by DATE_FORMAT(ctr."gmt_create",'%Y-%m-%d'),aab."ad_name",aab."guid"
        ) a
        order by createTime desc,callCompletingRate desc
            group by createTime,aab."ad_name",aab."guid"
            order by aab."guid"
        ) b
    </select>

    <!--查询智能外呼统计数据-行政区统计-->
    <select id="getOutgoingStatisticByArea" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select b.*,case when b.totalCount>0 then round(b.connectCount/b.totalCount*100,2) else 0 end callCompletingRate from(
        select b.*,case when b.connectionCount>0 then round(b.connectionCount/b.calleeNumberCount*100,2) else 0 end callCompletingRate from(
            select  aab."guid",
                    aab."ad_name" as adName,
                    ifnull(sum(totalCount),0) totalCount,
                    ifnull(sum(connectCount),0) connectCount
                    createTime,
                    ifnull(sum(totalCount),0) calleeNumberCount,
                    ifnull(sum(connectCount),0) connectionCount
                    from SJZT_MD."att_ad_base"  aab
            left join (
                select
@@ -208,6 +263,12 @@
                <if test="callTaskStatistic.createTime!=null and callTaskStatistic.createTime!=''">
                    and date_format(ctr."gmt_create",'%Y-%m-%d') = #{callTaskStatistic.createTime}
                </if>
                <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>
                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">
@@ -216,7 +277,10 @@
            <if test="callTaskStatistic.grad==2">
                on aab."guid" = a."guid" where aab."p_ad_code" = #{callTaskStatistic.adCode}
            </if>
            group by aab."ad_name",aab."guid"
            <if test="callTaskStatistic.grad==3">
                on aab."guid" = a."guid" where aab."ad_code" = #{callTaskStatistic.adCode}
            </if>
            group by createTime,aab."ad_name",aab."guid"
            order by aab."guid"
        ) b
    </select>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/AttAdBaseController.java

@@ -66,6 +66,29 @@
    }

    /**
     * 查询区域菜单树-带层级
     * @param parentCode
     * @param obj
     * @return
     */
    @GetMapping("/selectAttAdBaseTree")
    @ApiImplicitParams({
        @ApiImplicitParam(name = "code", value = "区划编号", paramType = "query", dataType = "string"),
        @ApiImplicitParam(name = "name", value = "区划名称", paramType = "query", dataType = "string")
    })
    @ApiOperation(value = "加载树列表", notes = "传入code")
    public R selectAttAdBaseTree(String parentCode, @ApiIgnore @RequestParam Map<String, Object> obj) {
        String code = obj.get("code").toString();
        if (code.substring(0,4).indexOf("00") > -1) {
            obj.put("code",code.substring(0,2));
        } else if (code.substring(0,6).indexOf("00") > -1) {
            obj.put("code",code.substring(0,4));
        }
        // 查询并返回
        return R.data(attAdBaseService.selectAttAdBaseTree(parentCode, obj));
    }

    /**
     * 懒加载列表 懒加载待优化
     */
    @GetMapping("/lazy-tree")

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttAdBaseMapper.java

@@ -1,8 +1,10 @@
package cn.gistack.sm.sjztmd.mapper;

import cn.gistack.common.node.TreeNode;
import cn.gistack.sm.sjztmd.entity.AttAdBase;
import cn.gistack.sm.sjztmd.vo.AttAdBaseVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.MapKey;

import java.util.List;
import java.util.Map;
@@ -16,4 +18,12 @@

    List<AttAdBaseVO> lazyTree(String parentCode, Map<String, Object> param);

    /**
     * 查询区域菜单树-带层级
     * @param parentCode
     * @param param
     * @return
     */
    @MapKey("id")
    Map<String, TreeNode> selectAttAdBaseTree(String parentCode, Map<String, Object> param);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttAdBaseMapper.xml

@@ -17,6 +17,7 @@
        region."p_ad_code" AS "parent_id",
        region."ad_name" AS "title",
        region."ad_code" AS "value",
        region."ad_grad" AS "adGrad",
        region."ad_code" AS "key",
        ( SELECT CASE WHEN count( 1 ) > 0 THEN 1 ELSE 0 END FROM "SJZT_MD"."att_ad_base" WHERE "p_ad_code" = region."ad_code" ) AS "has_children"
        FROM
@@ -35,4 +36,28 @@
        </where>
        ORDER BY region."ad_code"
    </select>

    <select id="selectAttAdBaseTree" resultType="cn.gistack.common.node.TreeNode">
        SELECT
        region."ad_code" AS "id",
        region."p_ad_code" AS "parent_id",
        region."ad_name" AS "name",
        region."ad_grad" AS "level",
        ( SELECT CASE WHEN count( 1 ) > 0 THEN 1 ELSE 0 END FROM "SJZT_MD"."att_ad_base" WHERE "p_ad_code" = region."ad_code" ) AS "has_children"
        FROM
        "SJZT_MD"."att_ad_base" region
        <where>
            <if test="param1!=null">
                and region."p_ad_code" = #{param1}
            </if>
            <if test="param2.code!=null and param2.code!=''">
                and region."ad_code" like concat(concat('%', #{param2.code}),'%')
            </if>
            <if test="param2.name!=null and param2.name!=''">
                and region."ad_name" like concat(concat('%', #{param2.name}),'%')
            </if>
            and region."ad_grad" != 4
        </where>
        ORDER BY region."ad_code"
    </select>
</mapper>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/IAttAdBaseService.java

@@ -17,4 +17,12 @@
public interface IAttAdBaseService extends IService<AttAdBase> {

    List<AttAdBaseVO> lazyTree(String parentCode, Map<String, Object> param);

    /**
     * 查询区域菜单树-带层级
     * @param parentCode
     * @param obj
     * @return
     */
    Object selectAttAdBaseTree(String parentCode, Map<String, Object> obj);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/AttAdBaseServiceImpl.java

@@ -1,5 +1,7 @@
package cn.gistack.sm.sjztmd.service.impl;

import cn.gistack.common.node.TreeNode;
import cn.gistack.common.utils.NodeTreeUtil;
import cn.gistack.sm.sjztmd.entity.AttAdBase;
import cn.gistack.sm.sjztmd.mapper.AttAdBaseMapper;
import cn.gistack.sm.sjztmd.service.IAttAdBaseService;
@@ -8,8 +10,11 @@
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @ClassName AttAdBaseServiceImpl
@@ -25,4 +30,37 @@
    public List<AttAdBaseVO> lazyTree(String parentCode, Map<String, Object> param) {
        return baseMapper.lazyTree(parentCode, param);
    }

    /**
     * 查询区域菜单树-带层级
     * @param parentCode
     * @param obj
     * @return
     */
    @Override
    public Object selectAttAdBaseTree(String parentCode, Map<String, Object> obj) {
        List<TreeNode> nodeTree = NodeTreeUtil.getNodeTree(baseMapper.selectAttAdBaseTree(parentCode, obj));
        // 排序并返回
        return sortTree(nodeTree);
    }

    /**
     * 排序--只排第一，二个子集
     * @param nodeTree
     */
    private List<TreeNode> sortTree(List<TreeNode> nodeTree) {
        for (TreeNode node : nodeTree) {
            if (node.getHasChildren()){
                List<TreeNode> children = node.getChildren();
                // 遍历子节点
                for (TreeNode child : children) {
                    if (child.getHasChildren()){
                        child.setChildren(child.getChildren().stream().sorted(Comparator.comparing(TreeNode::getId)).collect(Collectors.toList()));
                    }
                }
                node.setChildren(node.getChildren().stream().sorted(Comparator.comparing(TreeNode::getId)).collect(Collectors.toList()));
            }
        }
        return nodeTree;
    }
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/AttAdBaseVO.java

@@ -83,4 +83,9 @@
     */
    private String pAdName;

    /**
     * 层级
     */
    private String adGrad;

}