1.业务需求描述
报表前端显示的列不确定根据实际业务变动,所以不能采取传统的boostrap-table.js默认的方法进行配置,包括后端的sql语句也不是固定的,
2.实现的思路,boostraptable默认属性columns为后台动态获取,sql中的列也为动态拼接的sql语句
3.java端mybaits相关代码
xml,业务代码较多,配置文件说明 使用${sql}的方式传入sql语句,支持${}和#{}混用的方式(不要添加statementType="STATEMENT"),sql业务简单说明,各项指标打分,汇总,排名按人员,机构进行分类
<select id="listTableCondition" resultMap="BaseResultMap"
parameterType="Map" >
SELECT
obj_new.*
FROM
(SELECT
obj.*,
@rownum := @rownum + 1 AS num_tmp,
@incrnum :=
CASE WHEN @rowtotal = obj.sumCnt THEN
@incrnum
WHEN @rowtotal := obj.sumCnt THEN
@rownum
END AS orderNo
FROM(
select sumt.* from (
select t1.leader_code,t1.leader_name,t2.org_name leadergroup_name,t1.leader_Station leader_Station_value,t4.fd_param_val leader_Station,t5.fd_param_val leader_job_category,sum(t.score) sumCnt,
${sql}
from
eval_leader_report_sum t
left join eval_leaders t1 on t.leader_id =
t1.id
left join eval_leadergroup t2 on t1.leadergroup_id=t2.id
left join eval_manage_year_detail t3 on t.eval_year_detail_id = t3.uuid
left join sys_param t4 on t4.fd_param_typcde='leader_station_param' and t1.leader_station = t4.fd_param_cde
left join sys_param t5 on t5.fd_param_typcde='leader_job_category' and t1.leader_job_category = t5.fd_param_cde
<where>
<if test="evalName != null and '' != evalName">
and t3.eval_year_id = #{evalName}
</if>
</where>
group by t1.leader_code,t1.leader_name,t2.org_name
) as sumt order by sumt.sumcnt desc
) AS obj,
(
SELECT
@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
) r
) AS obj_new
<where>
<if test="leadergroupId != null and '' != leadergroupId">
and leadergroup_name like #{leadergroupId}
</if>
<if test="leaderName != null and '' != leaderName">
and leader_name like #{leaderName}
</if>
<if test="leaderStation != null and '' != leaderStation">
and leader_Station_value = #{leaderStation}
</if>
<if test="rankingStart != null and '' != rankingStart">
<![CDATA[and cast(orderNo as unsigned int) >= ]]>#{rankingStart,jdbcType=INTEGER}
</if>
<if test="rankingEnd != null and '' != rankingEnd">
<![CDATA[and cast(orderNo as unsigned int) <= ]]> #{rankingEnd,jdbcType=INTEGER}
</if>
</where>
</select>
4.java端部分主要代码
controler层,conditionMap.put("sql", sql);为关键代码
/**
* 获取table数据
*
* @param pageSize
* @param pageNumber
* @return
*/
@PostMapping("/listTableConditionRanking")
public @ResponseBody Object listTableCondition(HttpServletRequest request,String infoId, Integer pageSize, Integer pageNumber) {
// 获取参数信息
List<ReportQueryPara> listParas = iDynamicQueryService.queryFormConditionList(infoId);
// 从request获取参数信息 封装到map中
Map<String, Object> conditionMap = new HashMap<String, Object>();
for (int i = 0; i < listParas.size(); i++) {
ReportQueryPara reportQueryPara = listParas.get(i);
String name = reportQueryPara.getFdName();
if (0 == reportQueryPara.getFdParaLike()) {
conditionMap.put(name, request.getParameter(name));
} else {
conditionMap.put(name,
StringUtil.isEmpty(request.getParameter(name))? "" : ("%" + request.getParameter(name) + "%"));
}
}
String sql =iDynamicQueryService.constructDynamicQuotaSql("","leader","1");
conditionMap.put("sql", sql);
Object returnList = iLeaderPerformanceRankingService.listTableCondition(pageSize, pageNumber, conditionMap);
return returnList;
}
字段拼接,这里可以拼接任意sql语句,根据大家的业务需求
public String constructDynamicQuotaSql(String batch,String type,String level){
List<ReportDynamicField> dynamicFieldList = dynamicQueryServiceMapper.listDynamicQuota("20180725", type, level);
StringBuilder sb = new StringBuilder();
//sb.deleteCharAt(0);
for(int i=0 ;i<dynamicFieldList.size();i++){
sb.append(" sum(case when t.quota_id=");
sb.append(dynamicFieldList.get(i).getQuotaId());
sb.append(" then t.score else 0 end)as quota"+(i+1));
if(i!=dynamicFieldList.size()-1){
sb.append(",");
}
}
return sb.toString();
}
impl实现层,@ExtPageHelper为自己扩展的分页插件,详情可以查看https://blog.csdn.net/xiewenfeng520/article/details/80783584
@Override
@ExtPageHelper
public Object listTableCondition(Integer pageSize,Integer pageNumber,Map<String, Object> conditionMap) {
List<LeaderPerformanceRanking> list = leaderQuotaRankingMapper.listTableCondition(conditionMap);
return list;
}
sql语句拼接完成效果如下
SELECT
obj_new.*
FROM
(
SELECT
obj.*, @rownum := @rownum + 1 AS num_tmp,
@incrnum := CASE
WHEN @rowtotal = obj.sumCnt THEN
@incrnum
WHEN @rowtotal := obj.sumCnt THEN
@rownum
END AS orderNo
FROM
(
SELECT
sumt.*
FROM
(
SELECT
t1.leader_code,
t1.leader_name,
t2.org_name leadergroup_name,
t4.fd_param_val leader_Station_value,
t4.fd_param_val leader_Station,
t5.fd_param_val leader_job_category,
sum(t.score) sumCnt,
sum(
CASE
WHEN t.quota_id = 10010 THEN
t.score
ELSE
0
END
) AS quota1,
sum(
CASE
WHEN t.quota_id = 10012 THEN
t.score
ELSE
0
END
) AS quota2,
sum(
CASE
WHEN t.quota_id = 10013 THEN
t.score
ELSE
0
END
) AS quota3,
sum(
CASE
WHEN t.quota_id = 10029 THEN
t.score
ELSE
0
END
) AS quota4
FROM
eval_leader_report_sum t
LEFT JOIN eval_leaders t1 ON t.leader_id = t1.id
LEFT JOIN eval_leadergroup t2 ON t1.leadergroup_id = t2.id
LEFT JOIN eval_manage_year_detail t3 ON t.eval_year_detail_id = t3.uuid
LEFT JOIN sys_param t4 ON t4.fd_param_typcde = 'leader_station_param'
AND t1.leader_station = t4.fd_param_cde
LEFT JOIN sys_param t5 ON t5.fd_param_typcde = 'leader_job_category'
AND t1.leader_job_category = t5.fd_param_cde
WHERE
t3.eval_year_id = '10016'
GROUP BY
t1.leader_code,
t1.leader_name,
t2.org_name
) AS sumt
ORDER BY
sumt.sumcnt DESC
) AS obj,
(
SELECT
@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
) r
) AS obj_new
mybatis xml配置文件 resultMap属性如下
<resultMap id="BaseResultMap"
type="com.huajie.entity.report.LeaderPerformanceRanking">
<result column="leader_code" property="leaderCode" jdbcType="VARCHAR" />
<result column="leader_name" property="leaderName" jdbcType="VARCHAR" />
<result column="leader_station" property="leaderStation"
jdbcType="VARCHAR" />
<result column="leader_job_category" property="leaderJobCategory"
jdbcType="VARCHAR" />
<result column="leadergroup_id" property="leadergroupId"
jdbcType="VARCHAR" />
<result column="leadergroup_name" property="leadergroupName"
jdbcType="VARCHAR" />
<result column="sumCnt" property="sumCnt" jdbcType="INTEGER" />
<result column="orderNo" property="orderNo" jdbcType="INTEGER" />
<result column="quota1" property="quota1" jdbcType="INTEGER" />
<result column="quota2" property="quota2" jdbcType="INTEGER" />
<result column="quota3" property="quota3" jdbcType="INTEGER" />
<result column="quota4" property="quota4" jdbcType="INTEGER" />
<result column="quota5" property="quota5" jdbcType="INTEGER" />
<result column="quota6" property="quota6" jdbcType="INTEGER" />
<result column="quota7" property="quota7" jdbcType="INTEGER" />
<result column="quota8" property="quota8" jdbcType="INTEGER" />
<result column="quota9" property="quota9" jdbcType="INTEGER" />
<result column="quota10" property="quota10" jdbcType="INTEGER" />
<result column="quota11" property="quota11" jdbcType="INTEGER" />
<result column="quota12" property="quota12" jdbcType="INTEGER" />
<result column="quota13" property="quota13" jdbcType="INTEGER" />
<result column="quota14" property="quota14" jdbcType="INTEGER" />
<result column="quota15" property="quota15" jdbcType="INTEGER" />
<result column="quota16" property="quota16" jdbcType="INTEGER" />
<result column="quota17" property="quota17" jdbcType="INTEGER" />
<result column="quota18" property="quota18" jdbcType="INTEGER" />
<result column="quota19" property="quota19" jdbcType="INTEGER" />
<result column="quota20" property="quota20" jdbcType="INTEGER" />
</resultMap>
前端对应javaBean
package com.huajie.entity.report;
import lombok.Data;
@Data
public class LeaderPerformanceRanking {
private String leaderCode;// 员工编号
private String leaderName;// 员工姓名
private String leaderStation;// 岗位
private String leaderJobCategory;// 职位类别
private String leadergroupId;// 编号
private String leadergroupName;// 名称
private Integer sumCnt;//合计
private Integer orderNo;//排名
private Integer quota1;
private Integer quota2;
private Integer quota3;
private Integer quota4;
private Integer quota5;
private Integer quota6;
private Integer quota7;
private Integer quota8;
private Integer quota9;
private Integer quota10;
private Integer quota11;
private Integer quota12;
private Integer quota13;
private Integer quota14;
private Integer quota15;
private Integer quota16;
private Integer quota17;
private Integer quota18;
private Integer quota19;
private Integer quota20;
}
4.js前端核心封装代码
js经过几层封装,与bootstrap-table.js有所不同,目前只粘贴核心实现代码
动态列获取,将属性替换为columns : columnArr即可,ajax获取动态列的逻辑和后台java拼接的逻辑一致即可
/**
* 初始化form表单
* @param infoId 报表编号
*/
function getTableField() {
var tableFields;
$.ajax({
url : ctlr_tableField_list_url,
type : 'post',
data : {
infoId : infoId
},
async : false,
success : function(data) {
tableFields = data.data;
}
});
var columnArr = [];
$.each(tableFields, function(index, value) {
var val = value.resultFormatter ? eval("(" + value.resultFormatter + ")") : undefined
var cloumn = {
field : value.resultField,
title : value.resultTitle,
//halign : "center",
//valign : "middle",
align : value.resultAlign,
//rowspan : value.rowSpan,
//colspan : value.colSpan,
//sortable : true,
width : value.resultWidth,
//visible : value.resultDisplayYn == 'Y' ? true : false,
formatter : val
};
columnArr.push(cloumn);
});
return columnArr;
}
5.说明
本文只是提出一种实现的方式