智能外呼导出，告警内容查询导出修改（加水库基本信息）

zhongrj

2023-09-23 eb1464e4dd3aca6289b42f060ce5360de921fc97

智能外呼导出，告警内容查询导出修改（加水库基本信息）

 skjcmanager/skjcmanager-service-api/skjcmanager-alerts-api/src/main/java/cn/gistack/alerts/alarmRule/vo/AlarmRecordVO.java

@@ -45,4 +45,39 @@
     * 是否导出
     */
    private Integer isExport;

    /**
     * 开始时间
     */
    private String startTime;

    /**
     * 结束时间
     */
    private String endTime;

    /**
     * 水库规模
     */
    private String engScal;

    /**
     * 水库注册码
     */
    private String resRegCode;

    /**
     * 市州名称
     */
    private String cityName;

    /**
     * 区县
     */
    private String countyName;

    /**
     * 乡镇
     */
    private String townName;
}

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

@@ -75,6 +75,11 @@
     * 所在区县
     */
    private String countyName;

    /**
     * 所在乡镇
     */
    private String townName;
    /**
     * 是否巡查责任人 0：未知或空 1：不是巡查责任人 2：是巡查责任人
     */

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

@@ -175,6 +175,21 @@
    private String countyName;

    /**
     * 乡镇名称
     */
    private String townName;

    /**
     * 水库规模
     */
    private String engScal;

    /**
     * 水库注册码
     */
    private String resRegCode;

    /**
     * 是否短信反馈
     */
    private String isSmsFill;

 skjcmanager/skjcmanager-service/skjcmanager-alerts/src/main/java/cn/gistack/alerts/alarmRule/mapper/AlarmRecordMapper.xml

@@ -7,14 +7,24 @@
        select
        sar.*,
        sar1.type,sar1.alarm_rule_type as alarmRuleType,sar1.create_alarm_type as createAlarmType,sar1.rule_name as ruleName,
        arb."name" as reservoirName,aab."ad_name" as areaName
        a."name" as reservoirName,a."res_reg_code" as resRegCode,a."eng_scal" as engScal,
        case when b."ad_grad" = 4 THEN b."ad_name"
            ELSE NULL END AS townName,
        case when b."ad_grad" = 3 THEN b."ad_name"
            when c."ad_grad" = 3 THEN C."ad_name"
            END AS countyName,
        case when b."ad_grad" = 2 THEN b."ad_name"
            when c."ad_grad" = 2 THEN c."ad_name"
            ELSE d."ad_name" END AS cityName
        from YWXT.sm_alarm_record sar
        left join YWXT.sm_alarm_rule sar1 on sar.alarm_rule_id = sar1.id and sar1.is_deleted = 0
        left join SJZT_MD."att_res_base" arb on arb."guid" = sar.reservoir_number
        left join SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        left join SJZT_MD."att_res_base" a on a."guid" = sar.reservoir_number
        left join SJZT_MD."att_ad_base" b on b."guid" = a."interior_ad_guid"
        LEFT JOIN SJZT_MD."att_ad_base" c ON b."p_ad_code" = c."guid"
        LEFT JOIN SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid"
        where sar.is_deleted = 0
        <if test="alarmRecord.reservoirName!=null and alarmRecord.reservoirName!=''">
            and arb."name" like concat('%',#{alarmRecord.reservoirName},'%')
            and a."name" like concat('%',#{alarmRecord.reservoirName},'%')
        </if>
        <if test="alarmRecord.type!=null and alarmRecord.type!=''">
            and sar1.type = #{alarmRecord.type}
@@ -28,6 +38,12 @@
        <if test="alarmRecord.ruleName!=null and alarmRecord.ruleName!=''">
            and sar1.rule_name = #{alarmRecord.ruleName}
        </if>
        <if test="alarmRecord.startTime!=null and alarmRecord.startTime!=''">
            and date_format(sar.create_time,'%Y-%m-%d') >= #{alarmRecord.startTime}
        </if>
        <if test="alarmRecord.endTime!=null and alarmRecord.endTime!=''">
            and date_format(sar.create_time,'%Y-%m-%d') &lt;= #{alarmRecord.endTime}
        </if>
        order by sar.id desc
    </select>


 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml

@@ -204,6 +204,7 @@
        info."guid" AS resCd,
        info."name" AS resName,
        info."eng_scal" AS engScal,
        info."res_reg_code" AS resRegCode,
        info."town_ad_code" AS townCode,
        info."town_ad_name" AS townName,
        info."county_ad_code" AS countyCode,
@@ -221,6 +222,7 @@
        a."guid",
        a."name",
        a."eng_scal",
        a."res_reg_code",
        case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS "town_ad_code",
        case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS "town_ad_name",
        case when b."ad_grad" = 4 THEN b."order_id" ELSE NULL END AS "town_order_id",

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamStationStatistics.java

@@ -13,12 +13,18 @@
     */
    private String resCd;


    /**
     * 水库名称
     */
    private String resName;

    /**
     * 水库注册码
     */
    private String resRegCode;

    /**
     * 地市
     */
    private String cityName;

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

@@ -176,7 +176,6 @@
     */
    @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

@@ -202,7 +202,7 @@
    <!--查询智能外呼统计数据-按日期-行政区统计-->
    <select id="getOutgoingStatisticList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select
        aab."guid",aab."ad_name",
        aab."guid",aab."ad_name",aab."ad_grad" as grad,
        ifnull(totalCount,0) as calleeNumberCount,
        ifnull(connectCount,0) as connectionCount,
        ifnull(threeNotConnectCount,0) as threeNoConnectCount,
@@ -549,7 +549,7 @@
    <!--查询当前天除去日常呼叫外的统计数据-->
    <select id="getNoDayOutgoingStatisticList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select
        aab."guid",aab."ad_name",
        aab."guid",aab."ad_name",aab."ad_grad" as grad,
        ifnull(totalCount,0) as calleeNumberCount,
        ifnull(connectCount,0) as connectionCount,
        ifnull(threeNotConnectCount,0) as threeNoConnectCount,
@@ -634,25 +634,29 @@

    <!--按日期行政区呼叫结果详情列表信息-->
    <select id="getCallResultList" resultType="cn.gistack.sm.intelligentCall.vo.CallTaskStatistic">
        select x.* from (
        select
        e."gmt_create" as createTime,e."call_id",e.waterCode,e.callResult,e."callee_number",
        aab."guid",aab."ad_name" as adName,
        bu.real_name as realName,
        if(ifnull(scnfr.call_id,0)='0','','已反馈') isSmsFill,
        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
            arb."name" as waterName,arb."eng_scal" as engScal,arb."res_reg_code" as resRegCode,
            case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS townName,
            case when b."ad_grad" = 3 THEN b."ad_name"
                when c."ad_grad" = 3 THEN c."ad_name" END AS countyName,
            case when b."ad_grad" = 3 THEN b."ad_code"
                when c."ad_grad" = 3 THEN c."ad_code" END AS countyCode,
            case when b."ad_grad" = 2 THEN b."ad_code"
                when c."ad_grad" = 2 THEN c."ad_code"
                ELSE d."ad_code" END AS cityCode,
            case when b."ad_grad" = 2 THEN b."ad_name"
                when c."ad_grad" = 2 THEN c."ad_name"
                ELSE d."ad_name" END AS cityName
        from (
                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"
            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
                join (
                    select d.*,aab."ad_grad",aab."ad_name",aab."p_ad_name" from (
            select d.* from (
                    select ctr.*,waterCode from SJZT_ODS."call_task_result" ctr right join
                    (
                    select max("gmt_create") "gmt_create",waterCode,"callee_number" from (
@@ -666,8 +670,6 @@
                    ) 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
@@ -680,15 +682,13 @@
                    </if>
                    union all

                    select ctr.*,waterCode,aab."ad_grad",aab."ad_name",aab."p_ad_name" from SJZT_ODS."call_task_result" ctr
            select ctr.*,waterCode 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.startTime}
                    and date_format(ctr."gmt_create",'%Y-%m-%d') &lt;= #{callTaskStatistic.endTime}
                    and sdct.task_id is null
@@ -703,22 +703,25 @@
                    </if>
                )ctr on ctc."call_id" = ctr."call_id"
        )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
            <foreach collection="list" item="adCode" separator ="," open="("  close=")">
                #{adCode}
            </foreach>
        </if>
            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
            left join SJZT_MD."att_res_base" arb on locate(arb."code",e.waterCode)>0
            left join SJZT_MD."att_ad_base" b ON arb."interior_ad_guid" = b."guid"
            left join SJZT_MD."att_ad_base" c ON b."p_ad_code" = c."guid"
            left join SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid"
        <if test="callTaskStatistic.realName!=null and callTaskStatistic.realName!=''">
            and bu.real_name like concat('%',#{callTaskStatistic.realName},'%')
        </if>
        <if test="callTaskStatistic.waterName!=null and callTaskStatistic.waterName!=''">
            and arb."name" like concat('%',#{callTaskStatistic.waterName},'%')
            </if>
        ) x
        where 1=1
        <if test="callTaskStatistic.grad==2">
            and x.cityCode = #{callTaskStatistic.adCode}
        </if>
        <if test="callTaskStatistic.grad==3">
            and x.countyCode = #{callTaskStatistic.adCode}
        </if>
    </select>

@@ -872,6 +875,7 @@
        arb."rv_name" rvName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,2) as cityName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,3) as countyName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,4) as townName,
        if(ifnull(scnfr.call_id,0)='0','','已反馈') isSmsFill,
        case when ctr."call_result"!='200000' then if(ifnull(scnfr.is_danger,0)>'0',if(scnfr.is_danger='是',2,1),0)
        else if(ifnull(cas."tag_title",0)>'0',if(instr(cas."tag_title",'未发现险情')>0,1,if(instr(cas."tag_title",'发现险情')>0,2,0)),0) end as isDange,

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

@@ -1201,16 +1201,11 @@
     */
    @Override
    public Object getCallResultList(IPage<CallTaskStatistic> page, CallTaskStatistic callTaskStatistic) {
        List<String> list = new ArrayList<>();
        if (null!= callTaskStatistic.getAdCode() && !callTaskStatistic.getAdCode().equals("")){
            // 查询所有下级区域code
            list = getAllChildrenAreaByAreaCode(callTaskStatistic.getAdCode());
        }
        if (null!=callTaskStatistic.getIsPage()){
            // 不分页
            return callTaskMapper.getCallResultList(null, list,callTaskStatistic);
            return callTaskMapper.getCallResultList(null,callTaskStatistic);
        }
        List<CallTaskStatistic> callNotConnectResultList = callTaskMapper.getCallResultList(page,list, callTaskStatistic);
        List<CallTaskStatistic> callNotConnectResultList = callTaskMapper.getCallResultList(page, callTaskStatistic);
        // 返回
        return page.setRecords(callNotConnectResultList);
    }

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/materials/mapper/MaterialsManageMapper.xml

@@ -10,6 +10,8 @@
        <result property="cityName" column="cityName"/>
        <result property="county" column="county"/>
        <result property="countyName" column="countyName"/>
        <result property="engScal" column="engScal"/>
        <result property="resRegCode" column="resRegCode"/>
        <result property="town" column="town"/>
        <result property="townName" column="townName"/>
        <result property="materialsType" column="MATERIALS_TYPE"/>
@@ -33,6 +35,7 @@

    <select id="selectMyPage" resultMap="vo">
        SELECT MM.*,
            arb."res_reg_code" as resRegCode,arb."eng_scal" as engScal,
            aab1."ad_name" cityName,
            aab2."ad_name" countyName,
            aab3."ad_name" townName,
@@ -42,6 +45,7 @@
        LEFT JOIN SJZT_MD."att_ad_base" aab2 ON aab2."guid" = MM.county
        LEFT JOIN SJZT_MD."att_ad_base" aab3 ON aab3."guid" = MM.town
        LEFT JOIN BLADE_DICT_BIZ BDB ON MM.MATERIALS_TYPE = BDB.DICT_KEY
        LEFT JOIN SJZT_MD."att_res_base" arb ON MM.res_guid = arb."guid"
        WHERE MM.IS_DELETED = 0 AND BDB.CODE='MATERIALS_TYPE'
        <if test="vo.resName !=null and vo.resName !=''">
            AND MM.RES_NAME LIKE CONCAT('%',#{vo.resName},'%')

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/materials/mapper/RescueTeamManageMapper.xml

@@ -4,6 +4,7 @@
    <!--namespace根据自己需要创建的的mapper的路径和名称填写-->
    <select id="selectMyPage" resultType="cn.gistack.sm.materials.vo.RescueTeamManageVO">
        SELECT RM.*,
        arb."res_reg_code" as resRegCode,arb."eng_scal" as engScal,
        aab1."ad_name" cityName,
        aab2."ad_name" countyName,
        aab3."ad_name" townName
@@ -11,6 +12,7 @@
        LEFT JOIN SJZT_MD."att_ad_base" aab1 ON aab1."guid" = RM.city
        LEFT JOIN SJZT_MD."att_ad_base" aab2 ON aab2."guid" = RM.county
        LEFT JOIN SJZT_MD."att_ad_base" aab3 ON aab3."guid" = RM.town
        LEFT JOIN SJZT_MD."att_res_base" arb ON RM.res_guid = arb."guid"
        WHERE RM.IS_DELETED = 0
        <if test="vo.resName !=null and vo.resName !=''">
            AND RM.RES_NAME LIKE CONCAT('%',#{vo.resName},'%')

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/materials/vo/MaterialsManageVO.java

@@ -7,6 +7,16 @@
public class MaterialsManageVO extends MaterialsManage {

    /**
     * 水库注册码
     */
    private String resRegCode;

    /**
     * 工程规模
     */
    private String engScal;

    /**
     * 市名
     */
    private String cityName;

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/materials/vo/RescueTeamManageVO.java

@@ -7,6 +7,16 @@
@Data
public class RescueTeamManageVO extends RescueTeamManage {

    /**
     * 水库注册码
     */
    private String resRegCode;

    /**
     * 工程规模
     */
    private String engScal;

    /**
     * 市名
     */

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

@@ -185,10 +185,29 @@
     */
    @ApiModelProperty(value = "巡查责任人-职务")
    private String patrolPosition;

    /**
     * 巡查责任人-所属单位
     */
    @ApiModelProperty(value = "巡查责任人-所属单位")
    private String patrolUnitName;

    /**
     * 市州名称
     */
    @ApiModelProperty(value = "市州名称")
    private String cityName;

    /**
     * 区县名称
     */
    @ApiModelProperty(value = "区县名称")
    private String countyName;

    /**
     * 乡镇名称
     */
    @ApiModelProperty(value = "乡镇名称")
    private String townName;

}

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

@@ -43,13 +43,6 @@
    private String resGuid;

    /**
     * 运行状态
     */
    @ApiModelProperty(value = "运行状态")
    @TableField("\"run_status\"")
    private String runStatus;

    /**
     * 是否有水雨情测报雨量
     */
    @ApiModelProperty(value = "是否有水雨情测报雨量")

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

@@ -34,6 +34,9 @@
    <select id="getAttResBaseUserPage" resultType="cn.gistack.sm.sjztmd.dto.AttResBaseUserDTO">
        select
        arb."guid", arb."name", arb."res_loc" as place, arb."res_reg_code" as resRegCode, arb."eng_scal" as engScal,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,2) as cityName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,3) as countyName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,4) as townName,
        aab."ad_name" as adName,
        trmp1."user_unit" areaUnitName,trmp1."user_name" areaUsername,trmp1."user_phone" areaPhone,trmp1."position" areaPosition,trmp1."guid" areaUserGuid,
        trmp2."user_unit" customsUnitName,trmp2."user_name" customsUsername,trmp2."user_phone" customsPhone,trmp2."position" customsPosition,trmp2."guid" customsUserGuid,