skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/AttResBaseController.java
@@ -4,6 +4,7 @@ import cn.gistack.sm.sjztmd.entity.AttAdBase; import cn.gistack.sm.sjztmd.entity.AttResBase; import cn.gistack.sm.sjztmd.entity.AttResStagChar; import cn.gistack.sm.sjztmd.excel.AttResBaseUserExcel; import cn.gistack.sm.sjztmd.service.IAttAdBaseService; import cn.gistack.sm.sjztmd.service.IAttResBaseService; import cn.gistack.sm.sjztmd.vo.AttAdBaseVO; @@ -15,14 +16,17 @@ import lombok.AllArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springblade.core.boot.ctrl.BladeController; import org.springblade.core.excel.util.ExcelUtil; import org.springblade.core.mp.support.Condition; import org.springblade.core.mp.support.Query; import org.springblade.core.tool.api.R; import org.springblade.core.tool.node.ForestNodeMerger; import org.springblade.core.tool.utils.DateUtil; import org.springblade.core.tool.utils.StringUtil; import org.springframework.web.bind.annotation.*; import springfox.documentation.annotations.ApiIgnore; import javax.servlet.http.HttpServletResponse; import java.util.List; import java.util.Map; @@ -76,6 +80,22 @@ return R.data(attResBaseService.getAttResBaseUserPage(Condition.getPage(query),params)); } /** * 导出责任人数据 * @param params 条件查询参数对象 * @return */ @ApiOperation(value = "中台水库-导出责任人数据", notes = "中台水库-导出责任人数据") @GetMapping(value = "/exportAttResBaseUser") public void exportAttResBaseUser(@RequestParam Map<String, Object> params, HttpServletResponse response) { List<AttResBaseUserExcel> list = attResBaseService.exportAttResBaseUser(params); ExcelUtil.export(response, "水库责任人数据数据" + DateUtil.time(), "水库责任人数据表", list, AttResBaseUserExcel.class); } /** * 编辑 * skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/entity/AttResBase.java
@@ -354,4 +354,8 @@ @TableField("\"is_show\"") private String isShow; //这个字段为SJZT_ODS."yg_rsvr_b"的字段 @TableField("\"height_base_std_tp\"") private String heightBaseStdTp; } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/excel/AttResBaseUserExcel.java
New file @@ -0,0 +1,105 @@ package cn.gistack.sm.sjztmd.excel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; @Data @ColumnWidth(30) @HeadRowHeight(25) @ContentRowHeight(18) public class AttResBaseUserExcel { @ColumnWidth(20) @ExcelProperty("水库名称") private String name; @ColumnWidth(20) @ExcelProperty("市") private String cityName; @ColumnWidth(20) @ExcelProperty("区县") private String countyName; @ColumnWidth(20) @ExcelProperty("街道乡镇") private String townName; @ColumnWidth(20) @ExcelProperty("管理行政区") private String adName; @ColumnWidth(20) @ExcelProperty("工程规模") private String engScal; @ColumnWidth(20) @ExcelProperty("注册登记码") private String resRegCode; @ExcelProperty({"行政责任人", "姓名"}) private String areaUsername; @ExcelProperty({"行政责任人", "职务"}) private String areaPosition; @ExcelProperty({"行政责任人", "所属单位"}) private String areaUnitName; @ExcelProperty({"行政责任人", "联系电话"}) private String areaPhone; @ExcelProperty({"主管部门责任人", "姓名"}) private String customsUsername; @ExcelProperty({"主管部门责任人", "职务"}) private String customsPosition; @ExcelProperty({"主管部门责任人", "所属单位"}) private String customsUnitName; @ExcelProperty({"主管部门责任人", "联系电话"}) private String customsPhone; @ExcelProperty({"管理单位责任人", "姓名"}) private String manageUnitUsername; @ExcelProperty({"管理单位责任人", "职务"}) private String manageUnitPosition; @ExcelProperty({"管理单位责任人", "所属单位"}) private String manageUnitUnitName; @ExcelProperty({"管理单位责任人", "联系电话"}) private String manageUnitPhone; @ExcelProperty({"技术责任人", "姓名"}) private String technologyUsername; @ExcelProperty({"技术责任人", "职务"}) private String technologyPosition; @ExcelProperty({"技术责任人", "所属单位"}) private String technologyUnitName; @ExcelProperty({"技术责任人", "联系电话"}) private String technologyPhone; @ExcelProperty({"巡查责任人", "姓名"}) private String patrolUsername; @ExcelProperty({"巡查责任人", "职务"}) private String patrolPosition; @ExcelProperty({"巡查责任人", "所属单位"}) private String patrolUnitName; @ExcelProperty({"巡查责任人", "联系电话"}) private String patrolPhone; } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.java
@@ -3,6 +3,7 @@ import cn.gistack.sm.sjztmd.dto.AttResBaseUserDTO; import cn.gistack.sm.sjztmd.entity.AttAdBase; import cn.gistack.sm.sjztmd.entity.AttResBase; import cn.gistack.sm.sjztmd.excel.AttResBaseUserExcel; import cn.gistack.sm.sjztmd.vo.AttResAdVO; import cn.gistack.sm.sjztmd.vo.AttResBaseGeneralInvestigationVO; import cn.gistack.sm.sjztmd.vo.AttResBaseVO; @@ -41,6 +42,9 @@ */ List<AttResBaseUserDTO> getAttResBaseUserPage(IPage<AttResBaseUserDTO> page, @Param("params") Map<String, Object> params); List<AttResBaseUserExcel> getAttResBaseUserExcel(@Param("params") Map<String, Object> params); /** * 查询各类型责任人 @@ -84,4 +88,8 @@ AttResAdVO getResAd(@Param("resId") String resId); AttAdBase getYwxtResInfo(@Param("cityName") String cityName,@Param("countyName") String countyName); Boolean updateRsvr(@Param("attResBase") AttResBase attResBase); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml
@@ -154,12 +154,16 @@ 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, trmp3."user_unit" manageUnitUnitName,trmp3."user_name" manageUnitUsername,trmp3."user_phone" manageUnitPhone,trmp3."position" manageUnitPosition,trmp3."guid" manageUnitUserGuid, trmp4."user_unit" technologyUnitName,trmp4."user_name" technologyUsername,trmp4."user_phone" technologyPhone,trmp4."position" technologyPosition,trmp4."guid" technologyUserGuid, trmp5."user_unit" patrolUnitName,trmp5."user_name" patrolUsername,trmp5."user_phone" patrolPhone,trmp5."position" patrolPosition,trmp5."guid" patrolUserGuid from SJZT_MD."att_res_base" arb @@ -241,6 +245,14 @@ and (aab."ad_code" = #{params.adCode} or aa."ad_code" = #{params.adCode} or ab."ad_code" = #{params.adCode} or ac."ad_code" = #{params.adCode}) </if> <if test="params.userName !=null and params.userName != ''"> AND CONCAT(trmp1."user_name",trmp2."user_name",trmp3."user_name",trmp4."user_name",trmp5."user_name") LIKE CONCAT('%',#{params.userName},'%') </if> <if test="params.userPhone != null and params.userPhone !=''"> AND CONCAT(trmp1."user_phone",trmp2."user_phone",trmp3."user_phone",trmp4."user_phone",trmp5."user_phone") LIKE CONCAT('%',#{params.userPhone},'%') </if> </select> <!--查询各类型责任人--> @@ -265,8 +277,16 @@ <if test="attResBase.label ==null or attResBase.label == ''"> "label" = '', </if> "height_base_std_tp" = #{attResBase.heightBaseStdTp}, "is_gate" = #{attResBase.isGate}, "update_time" = #{attResBase.updateTime} WHERE "guid" = #{attResBase.guid} </update> <update id="updateRsvr"> UPDATE SJZT_ODS."yg_rsvr_b" SET "tb_height_base_std_tp" = #{attResBase.tbHeightBaseStdTp} WHERE "guid" = #{attResBase.guid} </update> @@ -329,6 +349,7 @@ </update> <!--根据水库名称、所在区域查询改水库的数量--> <select id="getWaterCountByResNameAndCountyName" resultType="java.lang.Integer"> SELECT count(1) @@ -388,6 +409,114 @@ aab."p_ad_name" = #{cityName} and aab."ad_name" = #{countyName} </select> <select id="getAttResBaseUserExcel" resultType="cn.gistack.sm.sjztmd.excel.AttResBaseUserExcel"> 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, trmp3."user_unit" manageUnitUnitName,trmp3."user_name" manageUnitUsername,trmp3."user_phone" manageUnitPhone,trmp3."position" manageUnitPosition,trmp3."guid" manageUnitUserGuid, trmp4."user_unit" technologyUnitName,trmp4."user_name" technologyUsername,trmp4."user_phone" technologyPhone,trmp4."position" technologyPosition,trmp4."guid" technologyUserGuid, trmp5."user_unit" patrolUnitName,trmp5."user_name" patrolUsername,trmp5."user_phone" patrolPhone,trmp5."position" patrolPosition,trmp5."guid" patrolUserGuid from SJZT_MD."att_res_base" arb left join "SJZT_MD"."att_ad_base" aab on aab."guid" = arb."interior_ad_guid" left join "SJZT_MD"."att_ad_base" aa on aab."p_ad_code" = aa."ad_code" left join "SJZT_MD"."att_ad_base" ab on aa."p_ad_code" = ab."ad_code" left join "SJZT_MD"."att_ad_base" ac on ab."p_ad_code" = ac."ad_code" left join ( select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position" from "SJZT_MD"."att_res_manage_person" where "type"=1 GROUP BY "res_guid" ) trmp1 on arb."guid" = trmp1."res_guid" left join ( select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position" from "SJZT_MD"."att_res_manage_person" where "type"=2 GROUP BY "res_guid" ) trmp2 on arb."guid" = trmp2."res_guid" left join ( select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position" from "SJZT_MD"."att_res_manage_person" where "type"=3 GROUP BY "res_guid" ) trmp3 on arb."guid" = trmp3."res_guid" left join ( select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position" from "SJZT_MD"."att_res_manage_person" where "type"=4 GROUP BY "res_guid" ) trmp4 on arb."guid" = trmp4."res_guid" left join ( select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position" from "SJZT_MD"."att_res_manage_person" where "type"=5 GROUP BY "res_guid" ) trmp5 on arb."guid" = trmp5."res_guid" where 1=1 <if test="params.name !=null and params.name !=''"> AND arb."name" LIKE CONCAT('%',#{params.name},'%') </if> <if test="params.resRegCode !=null and params.resRegCode !=''"> AND arb."res_reg_code" LIKE CONCAT('%',#{params.resRegCode},'%') </if> <if test="params.engScal !=null and params.engScal !=''"> AND arb."eng_scal" LIKE CONCAT('%',#{params.engScal},'%') </if> <if test="params.isWorkable==1"> AND ( ( (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型') and trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null ) or ( (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型') and trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null and trmp4."user_unit" is not null and trmp5."user_unit" is not null ) ) </if> <if test="params.isWorkable==2"> AND ( (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型') and (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null) or ( (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型') and (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null or trmp4."user_unit" is null or trmp5."user_unit" is null) ) ) </if> <if test="params.adCode != null and params.adCode != '' "> and (aab."ad_code" = #{params.adCode} or aa."ad_code" = #{params.adCode} or ab."ad_code" = #{params.adCode} or ac."ad_code" = #{params.adCode}) </if> <if test="params.userName !=null and params.userName != ''"> AND CONCAT(trmp1."user_name",trmp2."user_name",trmp3."user_name",trmp4."user_name",trmp5."user_name") LIKE CONCAT('%',#{params.userName},'%') </if> <if test="params.userPhone != null and params.userPhone !=''"> AND CONCAT(trmp1."user_phone",trmp2."user_phone",trmp3."user_phone",trmp4."user_phone",trmp5."user_phone") LIKE CONCAT('%',#{params.userPhone},'%') </if> </select> </mapper> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml
@@ -415,7 +415,7 @@ where 1=1 and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> and trgis."tb_year" = 2024 and trgis."tb_year" = #{params.tbYear} </if> and trgis."check_state" = 1 @@ -433,7 +433,7 @@ where 1=1 and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> and trgis."tb_year" = 2024 and trgis."tb_year" = #{params.tbYear} </if> and trgis."check_state" = 2 @@ -488,15 +488,6 @@ aab."p_ad_code" = #{params.adCode} </if> order by aab."guid" </select>
@@ -3,6 +3,7 @@ import cn.gistack.sm.sjztmd.dto.AttResBaseUserDTO; import cn.gistack.sm.sjztmd.entity.AttAdBase; import cn.gistack.sm.sjztmd.entity.AttResBase; import cn.gistack.sm.sjztmd.excel.AttResBaseUserExcel; import cn.gistack.sm.sjztmd.vo.*; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.IService; @@ -72,4 +73,6 @@ AttResAdVO getResAd(String resId); AttAdBase getYwxtResInfo(String cityName, String countyName); List<AttResBaseUserExcel> exportAttResBaseUser(Map<String, Object> params); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/AttResBaseServiceImpl.java
@@ -5,6 +5,7 @@ import cn.gistack.sm.sjztmd.entity.AttAdBase; import cn.gistack.sm.sjztmd.entity.AttResBase; import cn.gistack.sm.sjztmd.entity.RelResLabel; import cn.gistack.sm.sjztmd.excel.AttResBaseUserExcel; import cn.gistack.sm.sjztmd.mapper.AttResBaseMapper; import cn.gistack.sm.sjztmd.service.IAttResBaseService; import cn.gistack.sm.sjztmd.service.IRelResLabelService; @@ -85,6 +86,15 @@ return page.setRecords(attResBaseUserDTOList); } @Override public List<AttResBaseUserExcel> exportAttResBaseUser(Map<String, Object> params) { List<AttResBaseUserExcel> attResBaseUserDTOList = baseMapper.getAttResBaseUserExcel(params); return attResBaseUserDTOList; } @Override @Transactional(rollbackFor = Exception.class) public Boolean updateResBaseByGuid(AttResBase attResBase) { @@ -93,7 +103,6 @@ //向水库标签表中更新数据 relResLabelService.updateEntity(attResBase.getGuid(),attResBase.getLabel()); return baseMapper.updateResBaseByGuid(attResBase); } @@ -148,4 +157,5 @@ public AttAdBase getYwxtResInfo(String cityName, String countyName) { return baseMapper.getYwxtResInfo(cityName,countyName); } } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/statisticsVO/StatisticsTable.java
@@ -30,4 +30,10 @@ //已校核座数 private int checkDykeNum; //堤防总长度 private double dykeTotalLength; //堤防危害长度 private double dangerTotalLength; }
