违纪罚款页面有“查看报告”按钮，点击这个按钮系统会自动跳转到新的标签页面，把违纪罚款的各项信息用图表显示出来。

在前端项目中，我用Echarts技术实现了图表功能。好在Echarts对Vue3.0支持的比较好，没有什么问题。

在标签页面上，一共有两类图表：环形图和面积图。三个环形图分别统计的是罚款种类、罚款金额比例、已付款和未付款比例。面积图是用来显示当年每个月违纪罚款的情况，绿色为未缴纳，蓝色为已缴纳。这些前端图表用到的数据，都需要复杂的SQL语句查询出来。

一、编写持久层代码

在TbAmectDao.xml文件中，声明若干SQL语句。因为有些SQL语句需要重复调用，所以用标签加以封装。

<sql id="view_1">
    WHERE 1=1
    <if test="deptId!=null">
        AND u.dept_id=#{deptId}
    </if>
    <if test="typeId!=null">
        AND a.type_id=#{typeId}
    </if>
   <if test="startDate!=null & endDate!=null">
       AND a.create_time BETWEEN #{startDate} AND #{endDate}
   </if>
</sql>
<sql id="view_2">
    FROM tb_amect a
        JOIN tb_amect_type `at` ON a.type_id=`at`.id
        JOIN tb_user u ON a.user_id=u.id
</sql>
<sql id="view_3">
    <if test="deptId!=null">
        AND u.dept_id=#{deptId}
    </if>
    <if test="typeId!=null">
        AND a.type_id=#{typeId}
    </if>
    <if test="startDate!=null & endDate!=null">
        AND a.create_time BETWEEN #{startDate} AND #{endDate}
    </if>
</sql>
<select id="searchChart_1" parameterType="HashMap" resultType="HashMap">
    SELECT COUNT(`at`.id) AS ct,
           `at`.type
    FROM tb_amect a
    JOIN tb_amect_type `at` ON a.type_id=`at`.id
    JOIN tb_user u ON a.user_id=u.id
    <include refid="view_1"></include>
    GROUP BY `at`.id
</select>
<select id="searchChart_2" parameterType="HashMap" resultType="HashMap">
    SELECT "20元以内" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.amount >=0 AND a.amount < 20
    <include refid="view_3"></include>
    UNION
    SELECT "50元以内" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.amount >=20 AND a.amount < 50
    <include refid="view_3"></include>
    UNION
    SELECT "100元以内" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.amount >=50 AND a.amount < 100
    <include refid="view_3"></include>
    UNION
    SELECT "200元以内" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.amount >=100 AND a.amount < 200
    <include refid="view_3"></include>
    UNION
    SELECT "其他" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.amount >=200
    <include refid="view_3"></include>
</select>
<select id="searchChart_3" parameterType="HashMap" resultType="HashMap">
    SELECT "未付款" AS title,
           COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.`status`=1
    <include refid="view_3"></include>
    UNION
    SELECT "已付款" AS title,
    COUNT(*) AS ct
    <include refid="view_2"/>
    WHERE a.`status`=2
    <include refid="view_3"></include>
</select>
<select id="searchChart_4" parameterType="HashMap" resultType="HashMap">
    SELECT MONTH(create_time) AS month,
           COUNT(*) AS ct FROM tb_amect
    WHERE YEAR(create_time)=#{year} AND `status`=#{status}
    GROUP BY MONTH(create_time)
</select>

在TbAmectDao.java接口中，实现DAO方法。

public interface TbAmectDao {
    ……
    public ArrayList<HashMap> searchChart_1(HashMap param);
    public ArrayList<HashMap> searchChart_2(HashMap param);
    public ArrayList<HashMap> searchChart_3(HashMap param);
    public ArrayList<HashMap> searchChart_4(HashMap param);
}

二、编写业务层代码

在AmectService.java接口中，定义抽象方法。

public interface AmectService {
    ……
    public HashMap searchChart(HashMap param);
}

在AmectServiceImpl.java类中，实现抽象方法。

public class AmectServiceImpl implements AmectService {
    ……
    @Override
    public HashMap searchChart(HashMap param) {
        ArrayList<HashMap> chart_1 = amectDao.searchChart_1(param);
        ArrayList<HashMap> chart_2 = amectDao.searchChart_2(param);
        ArrayList<HashMap> chart_3 = amectDao.searchChart_3(param);
        param.clear();
        int year = DateUtil.year(new Date());
        param.put("year", year);
        param.put("status", 1);
        ArrayList<HashMap> list_1 = amectDao.searchChart_4(param);
        param.replace("status", 2);
        ArrayList<HashMap> list_2 = amectDao.searchChart_4(param);

        ArrayList<HashMap> chart_4_1 = new ArrayList<>();
        ArrayList<HashMap> chart_4_2 = new ArrayList<>();
        for (int i = 1; i <= 12; i++) {
            HashMap map = new HashMap();
            map.put("month", i);
            map.put("ct", 0);
            chart_4_1.add(map);
            chart_4_2.add((HashMap) map.clone());
        }
        list_1.forEach(one -> {
            chart_4_1.forEach(temp -> {
                if (MapUtil.getInt(one, "month") == MapUtil.getInt(temp, "month")) {
                    temp.replace("ct", MapUtil.getInt(one, "ct"));
                }
            });
        });

        list_2.forEach(one -> {
            chart_4_2.forEach(temp -> {
                if (MapUtil.getInt(one, "month") == MapUtil.getInt(temp, "month")) {
                    temp.replace("ct", MapUtil.getInt(one, "ct"));
                }
            });
        });


        HashMap map = new HashMap() {{
            put("chart_1", chart_1);
            put("chart_2", chart_2);
            put("chart_3", chart_3);
            put("chart_4_1", chart_4_1);
            put("chart_4_2", chart_4_2);
        }};
        return map;
    }
}

三、编写Web层代码

创建SearchChartForm.java类，用于封装Ajax提交的数据。

@Data
@Schema(description = "查询Chart图表表单")
public class SearchChartForm {
    @Min(value = 1, message = "deptId不能小于1")
    @Schema(description = "部门编号")
    private Integer deptId;

    @Min(value = 1, message = "typeId不能小于1")
    @Schema(description = "罚款类型编号")
    private Integer typeId;

    @Pattern(regexp = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$", message = "startDate内容不正确")
    @Schema(description = "开始日期")
    private String startDate;

    @Pattern(regexp = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$", message = "endDate内容不正确")
    @Schema(description = "截止日期")
    private String endDate;
}

在AmectController.java类中，定义Web方法。

public class AmectController {
    ……
    @PostMapping("/searchChart")
    @Operation(summary = "查询Chart图表")
    @SaCheckPermission(value = {"ROOT", "AMECT:SELECT"}, mode = SaMode.OR)
    public R searchChart(@Valid @RequestBody SearchChartForm form) {
        HashMap param = JSONUtil.parse(form).toBean(HashMap.class);
        HashMap map = amectService.searchChart(param);
        return R.ok(map);
    }
}