根据上级查下级

动态拼接sql，根据不同的条件进行分组

package com.yunfang.jzkz.controller;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.yunfang.common.core.controller.BaseController;
import com.yunfang.common.core.domain.entity.SysDept;
import com.yunfang.common.utils.DateUtils;
import com.yunfang.common.utils.LogUtils;
import com.yunfang.common.utils.ShiroUtils;
import com.yunfang.common.utils.StringUtils;
import com.yunfang.framework.web.service.DictService;
import com.yunfang.jzkz.domain.JzkzCstjDaVO;
import com.yunfang.jzkz.domain.JzkzCstjVO;
import com.yunfang.jzkz.domain.JzkzHouseCity;
import com.yunfang.system.service.ISysDeptService;
import org.apache.tomcat.jni.Mmap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @date 2022/4/18 - 13:44
 */
// 城市区县数据统计表
@Controller
@RequestMapping("/jzkz/yhpz/cstj")
public class JzkzYhpcCstjController extends BaseController {

    private String prefix = "jzkz/yhpc/cstj";

    @Autowired
    ISysDeptService sysDeptService;

    @GetMapping("list")
    public String list(ModelMap mmap) {
        mmap.put("user", getSysUser());
        mmap.put("dept", sysDeptService.getCachedDeptById(getSysUser().getDeptId()));
        mmap.put("year", DateUtils.dateTimeNow("yyyy"));
        return prefix + "/list";
    }

    @RequestMapping("list/data/{deptId}")
    public String listData(@PathVariable Long deptId, @RequestParam Map<String, String> formData, ModelMap mmap) {

        SysDept dept = sysDeptService.getCachedDeptById(deptId);
        String deptName = dept.getDeptName();
        mmap.put("deptName", deptName);
        String field = "concat(city, '000000')"; // 补齐12位代码
        String group = "city"; // 分组条件
        int deptLevel = dept.getDeptLevel(); // 0 省；1 州市；2 区县；3 镇 4 村（暂时不会有村的数据）
        switch(deptLevel){ // 区下一级作为分组条件
            case 0:
                field = "concat(city, '000000')";
                group = "city";
                break;
            case 1:
                field = "concat(xzqdm, '000000')";
                group = "xzqdm";
                break;
            case 2:
                field = "concat(town, '000')";
                group = "town";
                break;
            case 3:
                field = "village";
                group = "village";
                break;
        }
        // 组合查询条件
        String where = " 1 = 1 ";
        List<String> args = new ArrayList<>();
        if(StringUtils.isNotEmpty(formData.get("fwlb"))){
            where += "and fwlb = ? ";
            args.add(formData.get("fwlb"));
        }
        if(StringUtils.isNotEmpty(formData.get("czfwjglx"))){
            where += "and czfwjglx = ? ";
            args.add(formData.get("czfwjglx"));
        }
        if(StringUtils.isNotEmpty(formData.get("jznd_qg"))){
            where += "and jznd_qg = ? ";
            args.add(formData.get("jznd_qg"));
        }
        if(StringUtils.isNotEmpty(formData.get("fwyt"))){
            where += "and fwyt = ? ";
            args.add(formData.get("fwyt"));
        }
        String other = formData.get("other");
        String[] split = other.split(",");
        for (String s : split) {
            switch (s){
                case "1":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfcyjgz = 1 ";
                    }
                    break;
                case "2":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfcyjgz = 2";
                    }
                    break;
                case "3":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfcyjgz = 3 ";
                    }
                    break;
                case "4":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfcyjgz = 4 ";
                    }
                    break;
                case "5":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfszcg = '1' ";
                    }
                    break;
                case "6":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfkzjg = '1'";
                    }
                    break;
                case "7":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and ywlfbxqx = '1'";
                    }
                    break;
                case "8":
                    if(StringUtils.isNotEmpty(s)) {
                        where += " and sfzysjjz = '1' ";
                    }
                    break;
            }
        }

        String sql = "select \n" +
                "\t"+field+" as dept_id, " +
                "sum(fwsl) as total, \n" +
                "\tsum(fwsl) as total, \n" +
                "\tsum(if(sfcyjgz = 1, fwsl, 0)) as jzCount,\n" +
                "\tsum(if(sfcyjgz = 2, fwsl, 0)) as gezhenCount,\n" +
                "\tsum(if(sfcyjgz = 3, fwsl, 0)) as wcyCount,\n" +
                "\tsum(if(sfcyjgz = 4, fwsl, 0)) as jgzCount,\n" +
                "\tsum(if(sfcyjgz = '' or sfcyjgz = null, fwsl, 0)) as noDataCount,\n" +
                "\tsum(if(sfszcg = '1', fwsl, 0)) as gaizaoCount,\n" +
                "\tsum(if(sfkzjg = '1', fwsl, 0)) as jgCount,\n" +
                "\tsum(if(ywlfbxqx = '1', fwsl, 0)) as zdyhCount,\n" +
                "\tsum(if(sfzysjjz = '1', fwsl, 0)) as zyCount\n" +
                "from census_city_house\n" +
                "where "+ where + ShiroUtils.getDeptDataScope(deptId) +
                "group by "+group;

       // LogUtils.getInfoLog().info(sql); // 调试的时候把SQL打印出来看看帮助解决问题，调试完要记得注释掉。
        List<Record> list = Db.find(sql, args.toArray());
        Record totalR = totalMsg(list);
        mmap.put("totalR", totalR);
        mmap.put("list", list);
        return prefix + "/list_data";
    }



    // 合计信息
    private Record totalMsg(List<Record> res) {
        Record totalR = new Record();
        int totalTotal = 0;
        int jzCountTotal = 0;
        int gezhenCountTotal = 0;
        int wcyCountTotal = 0;
        int jgzCountTotal = 0;
        int noDataCountTotal = 0;
        int gaizaoCountTotal = 0;
        int jgCountTotal = 0;
        int zdyhCountTotal = 0;
        int zyCountTotal = 0;
        for (Record re : res) {
            totalTotal += re.getInt("total");
            jzCountTotal += re.getInt("jzCount");
            gezhenCountTotal += re.getInt("gezhenCount");
            wcyCountTotal += re.getInt("wcyCount");
            jgzCountTotal += re.getInt("jgzCount");
            noDataCountTotal += re.getInt("noDataCount");
            gaizaoCountTotal += re.getInt("gaizaoCount");
            jgCountTotal += re.getInt("jgCount");
            zdyhCountTotal += re.getInt("zdyhCount");
            zyCountTotal += re.getInt("zyCount");
        }
        totalR.set("totalTotal", totalTotal);
        totalR.set("jzCountTotal", jzCountTotal);
        totalR.set("gezhenCountTotal", gezhenCountTotal);
        totalR.set("wcyCountTotal", wcyCountTotal);
        totalR.set("jgzCountTotal", jgzCountTotal);
        totalR.set("noDataCountTotal", noDataCountTotal);
        totalR.set("gaizaoCountTotal", gaizaoCountTotal);
        totalR.set("jgCountTotal", jgCountTotal);
        totalR.set("zdyhCountTotal", zdyhCountTotal);
        totalR.set("zyCountTotal", zyCountTotal);
        return totalR;
    }

}