skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java
@@ -4,6 +4,7 @@ import cn.gistack.sm.sjztods.excel.SkxsQkTjExcel; import cn.gistack.sm.sjztods.vo.SkxsQkTjVO; import com.baomidou.dynamic.datasource.annotation.DS; import com.baomidou.mybatisplus.annotation.InterceptorIgnore; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import org.apache.ibatis.annotations.Param; @@ -33,12 +34,27 @@ * @param skxsQkTj * @return */ Object getStatisticList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj); List<SkxsQkTjVO> getStatisticList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj); /** * 查询导出数据 * @param skxsQkTj * @return */ @InterceptorIgnore(tenantLine = "true") List<SkxsQkTjExcel> exportSkxsQkTjList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj); /** * 通过adCode 获取adGrad * @param adCode * @return */ String getAdGradByAdCode(@Param("adCode") String adCode); /** * 查询详情数据 * @param skxsQkTjExcel * @return */ SkxsQkTj selectSkxsQkTj(@Param("skxsQkTjExcel") SkxsQkTjExcel skxsQkTjExcel); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml
@@ -27,20 +27,76 @@ left join SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid" where 1=1 <if test="skxsQkTj.statisticTime!=null"> and date_format(sqt."statistic_time","%Y-%m-%d") = #{skxsQkTj.statisticTime} and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.statisticTime} </if> <if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''"> and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%') </if> ) x <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!=''"> <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'"> and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode}) </if> </select> <!--按行政区统计--> <select id="getStatisticList" resultType="cn.gistack.sm.sjztods.vo.SkxsQkTjVO"> select <if test="skxsQkTj.adGrad==1"> cityName, </if> <if test="skxsQkTj.adGrad==2"> countyName, </if> <if test="skxsQkTj.adGrad==3"> townName, </if> ifnull(sum("ws"),0) as ws, ifnull(sum("last_year_ws"),0) as lastYearWs, ifnull(sum("his_ws"),0) as hisWs from ( select sqt.*, arb."res_reg_code" resRegCode,arb."eng_scal" engScal, case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS townName, case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS townCode, 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 SJZT_ODS."skxs_qk_tj" sqt left join SJZT_MD."att_res_base" arb on arb."guid" = sqt."res_code" 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" where 1=1 <if test="skxsQkTj.queryTime!=null and skxsQkTj.queryTime!=''"> and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.queryTime} </if> <if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''"> and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%') </if> ) a <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'"> and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode}) </if> <if test="skxsQkTj.adGrad==1"> group by cityName,cityCode order by cityCode </if> <if test="skxsQkTj.adGrad==2"> group by countyName,countyCode order by countyCode </if> <if test="skxsQkTj.adGrad==3"> group by townName,townCode order by townCode </if> </select> @@ -69,14 +125,27 @@ left join SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid" where 1=1 <if test="skxsQkTj.statisticTime!=null"> and date_format(sqt."statistic_time","%Y-%m-%d") = #{skxsQkTj.statisticTime} and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.statisticTime} </if> <if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''"> and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%') </if> ) x <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!=''"> <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'"> and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode}) </if> </select> <!--通过adCode 获取adGrad--> <select id="getAdGradByAdCode" resultType="java.lang.String"> select "ad_grad" from SJZT_MD."att_ad_base" where "guid" = #{adCode} </select> <!--查询详情数据--> <select id="selectSkxsQkTj" resultType="cn.gistack.sm.sjztods.entity.SkxsQkTj"> select * from SJZT_ODS."skxs_qk_tj" where 1=1 and date_format("statistic_time",'%Y-%m-%d') = #{skxsQkTjExcel.statisticTime} and "res_code" = #{skxsQkTjExcel.resCode} </select> </mapper> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java
@@ -41,4 +41,11 @@ * @return */ List<XsfsQktjExcel> exportXsfsQktjList(@Param("xsfsQktj") XsfsQktjVO xsfsQktj); /** * 查询详情信息 * @param xsfsQktjExcel * @return */ XsfsQktj selectXsfsQktj(@Param("xsfsQktjExcel") XsfsQktjExcel xsfsQktjExcel); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml
@@ -23,7 +23,7 @@ from SJZT_ODS."xsfs_qktj" xq where 1=1 <if test="xsfsQktj.statisticTime!=null"> and date_format(xq."statistic_time","%Y-%m-%d") = #{xsfsQktj.statisticTime} and date_format(xq."statistic_time",'%Y-%m-%d') = #{xsfsQktj.statisticTime} </if> <if test="xsfsQktj.adCode!=null and xsfsQktj.adCode!=''"> and xq."ad_code" = #{xsfsQktj.adCode} @@ -59,10 +59,18 @@ from SJZT_ODS."xsfs_qktj" xq where 1=1 <if test="xsfsQktj.statisticTime!=null"> and date_format(xq."statistic_time","%Y-%m-%d") = #{xsfsQktj.statisticTime} and date_format(xq."statistic_time",'%Y-%m-%d') = #{xsfsQktj.statisticTime} </if> <if test="xsfsQktj.adCode!=null and xsfsQktj.adCode!=''"> and xq."ad_code" = #{xsfsQktj.adCode} </if> </select> <!--查询详情数据--> <select id="selectXsfsQktj" resultType="cn.gistack.sm.sjztods.entity.XsfsQktj"> select * from SJZT_ODS."skxs_qk_tj" where 1=1 and date_format("statistic_time",'%Y-%m-%d') = #{xsfsQktjExcel.statisticTime} and "ad_code" = #{xsfsQktjExcel.adCode} </select> </mapper> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java
@@ -35,7 +35,7 @@ @Override @DS("ztznwh") public boolean exportSkxsQkTj(SkxsQkTj skxsQkTj) { // 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增 // 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增 ---有问题 QueryWrapper<SkxsQkTj> wrapper = new QueryWrapper<>(); wrapper.eq("\"statistic_time\"",skxsQkTj.getStatisticTime()) .eq("\"res_code\"",skxsQkTj.getResCode()); @@ -78,6 +78,11 @@ */ @Override public Object getStatisticList(SkxsQkTjVO skxsQkTj) { // 通过adCode 获取adGrad String adGrad = baseMapper.getAdGradByAdCode(skxsQkTj.getAdCode()); if (null!= adGrad && !adGrad.equals("")){ skxsQkTj.setAdGrad(adGrad); } return baseMapper.getStatisticList(skxsQkTj); } @@ -92,10 +97,7 @@ data.forEach(skxsQkTjExcel -> { SkxsQkTj skxsQkTj = Objects.requireNonNull(BeanUtil.copy(skxsQkTjExcel, SkxsQkTj.class)); // 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增 QueryWrapper<SkxsQkTj> wrapper = new QueryWrapper<>(); wrapper.eq("\"statistic_time\"",skxsQkTj.getStatisticTime()) .eq("\"res_code\"",skxsQkTj.getResCode()); SkxsQkTj qkTj = baseMapper.selectOne(wrapper); SkxsQkTj qkTj = baseMapper.selectSkxsQkTj(skxsQkTjExcel); // 设置更新人员，更新时间，来源信息 skxsQkTj.setUpdateTime(new Date()); skxsQkTj.setUpdateUser(AuthUtil.getUserId()); skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java
@@ -94,7 +94,7 @@ QueryWrapper<XsfsQktj> wrapper = new QueryWrapper<>(); wrapper.eq("\"statistic_time\"",xsfsQktj.getStatisticTime()) .eq("\"ad_code\"",xsfsQktj.getAdCode()); XsfsQktj qkTj = baseMapper.selectOne(wrapper); XsfsQktj qkTj = baseMapper.selectXsfsQktj(xsfsQktjExcel); // 设置更新人员，更新时间，来源信息 xsfsQktj.setUpdateTime(new Date()); xsfsQktj.setUpdateUser(AuthUtil.getUserId()); skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java
@@ -7,9 +7,19 @@ public class SkxsQkTjVO extends SkxsQkTj { /** * 按行政区划统计 * 行政区划编号 */ private String adCode; /** * 行政区划名称 */ private String adName; /** * 行政区划等级 */ private String adGrad; /** * 水库规模 @@ -40,4 +50,9 @@ * 乡镇名称 */ private String townName; /** * 查询时间 */ private String queryTime; }
