skjcmanager/skjcmanager-service-api/skjcmanager-sm-api/src/main/java/cn/gistack/sm/sg/VO/SgStatisProVO.java
New file @@ -0,0 +1,28 @@ package cn.gistack.sm.sg.VO; import lombok.Data; @Data public class SgStatisProVO { private String pcode; private String pname; private String code; private String adGrad; private String name; private int projectSum; private int commenceNum; private String projectProgress; }
New file @@ -0,0 +1,16 @@ package cn.gistack.sm.sg.VO; import lombok.Data; @Data public class SgStatistccVO { private String typeName; private String code;//设备代码 private int deviceNum; private String typeProgress; } skjcmanager/skjcmanager-service-api/skjcmanager-sm-api/src/main/java/cn/gistack/sm/sg/VO/SgStatisticsVO.java
New file @@ -0,0 +1,13 @@ package cn.gistack.sm.sg.VO; import lombok.Data; @Data public class SgStatisticsVO { private String projectSum;//总数 private String noCommenceNum;//未开工 private String commenceNum;//开工数 } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sg/controller/SgProjectInfoController.java
@@ -3,6 +3,9 @@ import cn.gistack.sm.sg.DO.SgProjectInfoDO; import cn.gistack.sm.sg.DTO.ProjectSearchDTO; import cn.gistack.sm.sg.VO.SgProjectInfoVO; import cn.gistack.sm.sg.VO.SgStatisProVO; import cn.gistack.sm.sg.VO.SgStatistccVO; import cn.gistack.sm.sg.VO.SgStatisticsVO; import cn.gistack.sm.sg.excel.ProjectExcel; import cn.gistack.sm.sg.excel.ProjectImport; import cn.gistack.sm.sg.service.SgProjectInfoService; @@ -101,4 +104,31 @@ ExcelUtil.save(file, projectImport, ProjectExcel.class); return R.success("操作成功"); } /** * 统计项目饼状图 */ @GetMapping("statisticsce") public R projectStatisSum(String code) { SgStatisticsVO sgStatisticsVO = sgProjectInfoService.getsgStatistics(code); return R.data(sgStatisticsVO); } /** * 统计项目图列表 */ @GetMapping("statisticsBarlist") public R projectStatisList(String code) { List<SgStatisProVO> sgStatisProVOS = sgProjectInfoService.getsgStatisticsList(code); return R.data(sgStatisProVOS); } /** * 统计项目柱状图 */ @GetMapping("statisticsBar") public R projectStatis(String code) { List<SgStatistccVO> sgStatisProVOS = sgProjectInfoService.getsgStatisticscc(code); return R.data(sgStatisProVOS); } } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sg/mapper/SgProjectInfoMapper.java
@@ -4,6 +4,9 @@ import cn.gistack.sm.sg.DO.SgProjectInfoDO; import cn.gistack.sm.sg.DTO.ProjectSearchDTO; import cn.gistack.sm.sg.VO.SgProjectInfoVO; import cn.gistack.sm.sg.VO.SgStatisProVO; import cn.gistack.sm.sg.VO.SgStatistccVO; import cn.gistack.sm.sg.VO.SgStatisticsVO; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import org.apache.ibatis.annotations.Param; @@ -27,4 +30,18 @@ List<SgProjectInfoVO> progressList(@Param("dto") ProjectSearchDTO searchDTO); List<SgProjectInfoVO> progressList(IPage<SgProjectInfoVO> page, @Param("dto") ProjectSearchDTO searchDTO); SgStatisticsVO getSgstatiss(@Param("grad") String grad, String code); List<SgStatisProVO> getsgStatisticsList(String code); List<SgStatisProVO> getsgStatisticsZero(String code); List<SgStatisProVO> getsgStatisticsOne(String code); List<String> getProjectId(@Param("grad") String grad,@Param("code") String code); List<SgStatistccVO> getSgdevices(@Param("code")String code,@Param("grad") String adGrad); //List<String> getProjectIds(String code,@Param("grad") String grad ); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sg/mapper/SgProjectInfoMapper.xml
@@ -85,4 +85,145 @@ and pro.project_name like CONCAT(CONCAT('%',#{dto.projectName}),'%') </if> </select> <select id="getSgstatiss" resultType="cn.gistack.sm.sg.VO.SgStatisticsVO"> SELECT SUM( CASE WHEN info.STATUS = 0 THEN 1 ELSE 0 END ) AS noCommenceNum, SUM( CASE WHEN info.STATUS IN ( 1, 2 ) THEN 1 ELSE 0 END ) AS commenceNum, count( info.STATUS ) projectSum FROM YWXT.SG_PROJECT_INFO info LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = info.sk_id WHERE 1 = 1 <if test="grad == 0"> AND dria."bas_name" = #{code} </if> <if test="grad == 2"> AND dria."city_cd" = #{code} </if> <if test="grad == 3"> AND dria."county_cd" = #{code} </if> </select> <select id="getsgStatisticsList" resultType="cn.gistack.sm.sg.VO.SgStatisProVO"> SELECT a."ad_code" CODE, a."ad_name" NAME, SUM( CASE WHEN b.STATUS IN ( 1, 2 ) THEN 1 ELSE 0 END ) AS commenceNum, count( b.STATUS ) projectSum FROM ( SELECT dria."county_cd" AS countyCode, att."guid" AS cityCode, ad."ad_code" AS provinceCode, info.STATUS FROM YWXT.SG_PROJECT_INFO info LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = info.sk_id LEFT JOIN YWXT."att_ad_base" att ON att."ad_code" = dria."city_cd" LEFT JOIN YWXT."att_ad_base" ad ON att."p_ad_code" = ad."ad_code" LEFT JOIN YWXT."att_ad_base" aad ON aad."ad_code" = ad."p_ad_code" ) b LEFT JOIN YWXT."att_ad_base" a ON ( ( b.countyCode = a."guid" ) OR ( b.cityCode = a."guid" ) OR ( b.provinceCode = a."guid" )) WHERE a."p_ad_code" = #{code} GROUP BY a."ad_code", a."ad_name" </select> <select id="getsgStatisticsZero" resultType="cn.gistack.sm.sg.VO.SgStatisProVO"> SELECT dria."city_cd" code, dria."city_nm" name , sum( CASE WHEN info.STATUS IN ( 1, 2 ) THEN 1 ELSE 0 END ) AS commenceNum, count( info.STATUS ) projectSum FROM YWXT.SG_PROJECT_INFO info LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = info.sk_id WHERE 1 = 1 AND dria."bas_name" =#{code} Group By dria."city_cd",dria."city_nm" HAVING dria."city_cd" IS NOT NULL AND dria."city_nm" IS NOT NULL </select> <select id="getsgStatisticsOne" resultType="cn.gistack.sm.sg.VO.SgStatisProVO"> SELECT dria."bas_name" AS code, dria."bas_guid" AS name, sum( CASE WHEN info.STATUS IN ( 1, 2 ) THEN 1 ELSE 0 END ) AS commenceNum, count( info.STATUS ) AS projectSum FROM YWXT.SG_PROJECT_INFO info LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = info.sk_id WHERE 1 = 1 GROUP BY dria."bas_guid", dria."bas_name" HAVING dria."bas_guid" IS NOT NULL AND dria."bas_name" IS NOT NULL </select> <select id="getProjectId" resultType="java.lang.String"> select id from YWXT.SG_PROJECT_INFO info LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = info.sk_id where info.STATUS<![CDATA[<>]]>'0' and 1=1 <if test="grad == 0"> AND dria."bas_name" = #{code} </if> <if test="grad == 2"> AND dria."city_cd" = #{code} </if> <if test="grad == 3"> AND dria."county_cd" = #{code} </if> </select> <select id="getSgdevices" resultType="cn.gistack.sm.sg.VO.SgStatistccVO"> SELECT dict."DICT_VALUE" AS typeName, dict.CODE code, count(ds.ID) as deviceNum FROM YWXT."SG_DEVICE" AS ds LEFT JOIN YWXT."SG_PROJECT_INFO" AS project ON ds.program_id = project.id LEFT JOIN YWXT."BLADE_DICT_BIZ" AS dict ON ds.device_type = dict.DICT_KEY AND dict.CODE = 'SG_DEVICE_TYPE' LEFT JOIN SJZT_DW."dim_res_info_a" dria ON dria."res_cd" = project.sk_id WHERE 1 =1 <if test="grad == 0"> AND dria."bas_name" = #{code} </if> <if test="grad == 2"> AND dria."city_cd" = #{code} </if> <if test="grad == 3"> AND dria."county_cd" = #{code} </if> Group By dict.CODE,dict."DICT_VALUE" </select> <!-- <select id="getProjectIds" resultType="java.lang.String">--> <!-- </select>--> </mapper> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sg/service/SgProjectInfoService.java
@@ -3,6 +3,9 @@ import cn.gistack.sm.sg.DO.SgProjectInfoDO; import cn.gistack.sm.sg.DTO.ProjectSearchDTO; import cn.gistack.sm.sg.VO.SgProjectInfoVO; import cn.gistack.sm.sg.VO.SgStatisProVO; import cn.gistack.sm.sg.VO.SgStatistccVO; import cn.gistack.sm.sg.VO.SgStatisticsVO; import cn.gistack.sm.sg.excel.ProjectExcel; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.IService; @@ -33,4 +36,10 @@ IPage<SgProjectInfoVO> progressPage(IPage<SgProjectInfoVO> page, ProjectSearchDTO searchDTO); List<SgProjectInfoDO> listAll(ProjectSearchDTO searchDTO); SgStatisticsVO getsgStatistics(String code); List<SgStatisProVO> getsgStatisticsList(String code); List<SgStatistccVO> getsgStatisticscc(String code); } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sg/service/impl/SgProjectInfoServiceImpl.java
@@ -5,11 +5,13 @@ import cn.gistack.sm.sg.DO.SgGxStepDO; import cn.gistack.sm.sg.DO.SgProjectInfoDO; import cn.gistack.sm.sg.DTO.ProjectSearchDTO; import cn.gistack.sm.sg.VO.SgProjectInfoVO; import cn.gistack.sm.sg.VO.SgTypeVO; import cn.gistack.sm.sg.VO.*; import cn.gistack.sm.sg.excel.ProjectExcel; import cn.gistack.sm.sg.mapper.SgProjectInfoMapper; import cn.gistack.sm.sg.service.SgProjectInfoService; import cn.gistack.sm.sjztmd.entity.AttAdBase; import cn.gistack.sm.sjztmd.service.IAttAdBaseService; import cn.hutool.core.lang.copier.Copier; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.toolkit.CollectionUtils; import com.baomidou.mybatisplus.core.toolkit.StringUtils; @@ -18,9 +20,11 @@ import org.jetbrains.annotations.NotNull; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.StopWatch; import java.math.BigDecimal; import java.math.MathContext; import java.math.RoundingMode; import java.util.*; import java.util.function.Function; @@ -38,6 +42,8 @@ public class SgProjectInfoServiceImpl extends ServiceImpl<SgProjectInfoMapper, SgProjectInfoDO> implements SgProjectInfoService { @Autowired private SgProjectInfoMapper sgProjectInfoMapper; @Autowired private IAttAdBaseService iAttAdBaseService; @Override public IPage<SgProjectInfoDO> queryPageList(IPage<SgProjectInfoDO> page, ProjectSearchDTO searchDTO) { List<SgProjectInfoDO> sgProjectInfoDOS = baseMapper.queryPageList(page,searchDTO); @@ -181,6 +187,175 @@ } /** * 项目统计饼状图 * @param code * @return */ @Override public SgStatisticsVO getsgStatistics(String code) { AttAdBase ywxtAdDetail = iAttAdBaseService.getYwxtAdDetail(code); if (ywxtAdDetail == null){ return sgProjectInfoMapper.getSgstatiss("0",code); } return sgProjectInfoMapper.getSgstatiss(ywxtAdDetail.getAdGrad(),code); } /** * 项目统计表格 * @param code * @return */ @Override @Transactional public List<SgStatisProVO> getsgStatisticsList(String code) { AttAdBase ywxtAdDetail = iAttAdBaseService.getYwxtAdDetail(code); List<SgStatisProVO> sgStatisProVOS; List<BigDecimal> bigDecimals = new ArrayList<>(); ProjectSearchDTO projectSearchDTO = new ProjectSearchDTO(); //片区 if (ywxtAdDetail == null){ sgStatisProVOS = sgProjectInfoMapper.getsgStatisticsZero(code); sgStatisProVOS.forEach(s->{ if (s.getCommenceNum() == 0) { s.setProjectProgress("0%"); s.setPcode("420000000000"); s.setPname("湖北省"); s.setAdGrad("2"); }else { List<String> ids = sgProjectInfoMapper.getProjectId("2",s.getCode()); ids.forEach(id->{ projectSearchDTO.setProjectId(id); List<SgProjectInfoVO> list =sgProjectInfoMapper.queryStepByProject(projectSearchDTO); getProjectProgress(list).forEach(ss->{ bigDecimals.add(ss.getProjectProgressNum()); }); }); BigDecimal reduce = bigDecimals.stream().reduce(BigDecimal.ZERO, BigDecimal::add); String bigDecimalPercent1 = getBigDecimalPercent(reduce.divide(BigDecimal.valueOf(s.getProjectSum()), 2, RoundingMode.HALF_UP)); s.setProjectProgress(s.getCommenceNum()==0?"0%": bigDecimalPercent1); s.setPcode("420000000000"); s.setPname("湖北省"); s.setAdGrad("2"); }}); return sgStatisProVOS; } //默认 else if (ywxtAdDetail.getAdGrad().equals("1")) { sgStatisProVOS = sgProjectInfoMapper.getsgStatisticsOne(code); sgStatisProVOS.forEach(s->{ if (s.getCommenceNum() == 0) { s.setProjectProgress("0%"); s.setPcode(ywxtAdDetail.getPAdCode()); s.setPname(ywxtAdDetail.getPAdName()); s.setAdGrad("0"); }else { List<String> ids = sgProjectInfoMapper.getProjectId(ywxtAdDetail.getAdGrad(),s.getCode()); ids.forEach(id->{ projectSearchDTO.setProjectId(id); List<SgProjectInfoVO> list =sgProjectInfoMapper.queryStepByProject(projectSearchDTO); getProjectProgress(list).forEach(ss->{ bigDecimals.add(ss.getProjectProgressNum()); }); }); BigDecimal reduce = bigDecimals.stream().reduce(BigDecimal.ZERO, BigDecimal::add); String bigDecimalPercent1 = getBigDecimalPercent(reduce.divide(BigDecimal.valueOf(s.getProjectSum()), 2, RoundingMode.HALF_UP)); s.setProjectProgress(s.getCommenceNum()==0?"0%": bigDecimalPercent1); s.setPcode(ywxtAdDetail.getPAdCode()); s.setPname(ywxtAdDetail.getPAdName()); s.setAdGrad("0"); }}); return sgStatisProVOS; } //区县 sgStatisProVOS = sgProjectInfoMapper.getsgStatisticsList(code); sgStatisProVOS.forEach(s->{ if (s.getCommenceNum() == 0) { s.setProjectProgress("0%"); s.setPcode(ywxtAdDetail.getAdCode()); s.setPname(ywxtAdDetail.getAdName()); s.setAdGrad("3"); }else { List<String> ids = sgProjectInfoMapper.getProjectId("3",s.getCode()); ids.forEach(id->{ projectSearchDTO.setProjectId(id); List<SgProjectInfoVO> list =sgProjectInfoMapper.queryStepByProject(projectSearchDTO); getProjectProgress(list).forEach(ss->{ bigDecimals.add(ss.getProjectProgressNum()); }); }); BigDecimal reduce = bigDecimals.stream().reduce(BigDecimal.ZERO, BigDecimal::add); String bigDecimalPercent1 = getBigDecimalPercent(reduce.divide(BigDecimal.valueOf(s.getProjectSum()), 2, RoundingMode.HALF_UP)); s.setProjectProgress(s.getCommenceNum()==0?"0%": bigDecimalPercent1); s.setPcode(ywxtAdDetail.getAdCode()); s.setPname(ywxtAdDetail.getAdName()); s.setAdGrad("3"); }}); return sgStatisProVOS; } @Override public List<SgStatistccVO> getsgStatisticscc(String code) { AttAdBase ywxtAdDetail = iAttAdBaseService.getYwxtAdDetail(code); List<String> ids = sgProjectInfoMapper.getProjectId(ywxtAdDetail==null?"0":ywxtAdDetail.getAdGrad(),code); Map<String,Integer> mun = new HashMap<>(); List<SgStatistccVO> sgdevices = sgProjectInfoMapper.getSgdevices(code,ywxtAdDetail==null?"0":ywxtAdDetail.getAdGrad()); sgdevices.forEach(sgStatistccVO -> { mun.put(sgStatistccVO.getTypeName(),sgStatistccVO.getDeviceNum()); } ); Map<String,BigDecimal> bigDecimals = new HashMap<>(); ProjectSearchDTO projectSearchDTO = new ProjectSearchDTO(); ids.forEach(id->{ projectSearchDTO.setProjectId(id); List<SgProjectInfoVO> list =sgProjectInfoMapper.queryStepByProject(projectSearchDTO); List<SgProjectInfoDO> projectProgress = getProjectProgress(list); projectProgress.forEach(p->{ List<SgTypeVO> sgGxDOS = p.getSgGxDOS(); sgGxDOS.forEach(sgTypeVO -> { String typeProgress = sgTypeVO.getTypeProgress().replace("%", ""); BigDecimal percentageBigDecimal = new BigDecimal(typeProgress).divide(new BigDecimal("100"),2, RoundingMode.HALF_UP); bigDecimals.merge(sgTypeVO.getTypeName(),percentageBigDecimal,BigDecimal::add); }); }); }); for (String name : bigDecimals.keySet()) { if (mun.containsKey(name)) { BigDecimal bigDecimal = bigDecimals.get(name); Integer intValue = mun.get(name); BigDecimal result = bigDecimal.divide(new BigDecimal(intValue), 2, BigDecimal.ROUND_HALF_UP); sgdevices.forEach(sgStatistccVO -> { if(sgStatistccVO.getTypeName().equals(name)){ sgStatistccVO.setTypeProgress(getBigDecimalPercent(result)); } }); } } return sgdevices; } /** * 获取百分比 * @param bigDecimal * @return
