数据库动态视图和存储过程报表数据管理功能设计

需求：需要将ERP的报表数据挪到OA中，但是OA表单设计不支持存储过程动态传参，所以需要设计一个系统，可以手动配置，动态显示原本ERP的报表数据，ERP报表是存在数据库的视图和存储过程中

思路：因为ERP数据库只有一个，所以不需要考虑多数据源问题（当然这个也很好解决），动态sql拼接，存储过程原本想用临时表的方式解决结果集字段查询和分页问题，但是后来觉得太麻烦了，选择程序中解决，毕竟数据量最多的也就一两万条，使用list的截取实现分页

后端：spring
前端：element

效果截图：
管理界面：

新增界面：

修改界面：
视图没有必要参数，因为视图可以通过where查询，默认都是结果集筛选参数

展示页面效果（用于外挂在OA系统里）：
（展示页面只有一个文件，动态展示数据，根据管理页面中注释字段区分）

所有筛选参数和展示字段也都是动态显示

针对不同字段的动态处理：
日期：

客户和供应商，采购员，员工，销售员都是对应数据的下拉带搜索：


后台配置筛选参数的时候，字段名叫日期会去调用日期选择，字段叫客户会调用所有客户信息选择，字段名叫供应商会去调用所有供应商信息，字段名带员的（人员，员工，采购员，销售员）会去调用员工信息选择（这些都是前端设置的，不想后端在加一个类型字段来区分了，直接用名字区分）

数据库表结构：
主表：

从表（用于筛选字段）：

部分代码：
页面获取参数信息（根据展示页面url的参数）：

@RequestMapping("/getcanshu")public R getcanshu(@RequestParam("name") String zhushi){QueryWrapper<XinxiShituzhuEntity> query = new QueryWrapper();query.eq("zhushi", zhushi);XinxiShituzhuEntity zhudata = xinxiShituzhuService.getOne(query);Integer zhuid = zhudata.getId();QueryWrapper<XinxiShitucongEntity> congquery = new QueryWrapper();congquery.eq("fatherid", zhuid);List<XinxiShitucongEntity> congdata = xinxiShitucongService.list(congquery);zhudata.setCanshu(congdata);return R.ok().put("data", zhudata);}

获取展示页面查询数据：

@RequestMapping("/getalldatas")public R getalldatas(@RequestBody XinxiShituzhuEntity xinxiShituzhu){// 视图if(xinxiShituzhu.getBtype()==1){// 拼接sqlString querySql = "SELECT * FROM "+xinxiShituzhu.getName() +" WHERE 1=1 ";String countsql = "SELECT COUNT(1) FROM "+xinxiShituzhu.getName() +" WHERE 1=1 ";List<XinxiShitucongEntity> congdatas = xinxiShituzhu.getCanshu();for(XinxiShitucongEntity data: congdatas){if(!Objects.equals(data.getZdzhi(), "")&& data.getZdzhi()!=null){querySql = querySql + " AND ["+data.getName()+"]= '"+data.getZdzhi()+"'";countsql = countsql + " AND ["+data.getName()+"]= '"+data.getZdzhi()+"'";}}// 添加分页querySql = querySql + " order by ["+ xinxiShituzhu.getPaixuzd() +"] offset "+(xinxiShituzhu.getPageNum()-1)*xinxiShituzhu.getPageSize()+" row fetch next "+xinxiShituzhu.getPageSize()+" row only;";List<Map<String, Object>> zhudatas = xinxiShituzhuService.getAllShituDatas(querySql);// 总数Integer zhucount = xinxiShituzhuService.getAllShituCount(countsql);// 获取所有表头，用于展示页表头信息List<Map<String, Object>> newdatas = new ArrayList<>();for(Map<String, Object> data : zhudatas){for(String key: data.keySet()){Map<String, Object> newmapdata = new LinkedHashMap<>();newmapdata.put("key", key);newdatas.add(newmapdata);}//Collections.reverse(newdatas);break;}return R.ok().put("count", zhucount).put("data", zhudatas).put("headdata", newdatas);}// 存储过程else{// 拼接sqlString querySql = "EXEC ["+xinxiShituzhu.getName()+"]";List<XinxiShitucongEntity> congdatas = xinxiShituzhu.getCanshu();List<String> canshus = new ArrayList<>();// 遍历所有参数for(XinxiShitucongEntity data: congdatas){// 视图必要参数添加if(Objects.equals(data.getZdtype(), "1")){if(!Objects.equals(data.getZdzhi(), "")&& data.getZdzhi()!=null){canshus.add(" @"+data.getName()+"= '"+data.getZdzhi()+"'");// querySql = querySql + " @"+data.getName()+"= '"+data.getZdzhi()+"'";}else{return R.error("必要参数不能为空");}}}if(canshus.size()>0){querySql = querySql + StringUtils.join(canshus, ",");}// 获取所有数据List<Map<String, Object>> zhudatas = xinxiShituzhuService.getAllShituDatas(querySql);List<Map<String, Object>> newdatas = new ArrayList<>();// 遍历结果集，用于结果集的字段筛选for(Map<String, Object> dataxx: zhudatas){// 用于标记筛选字段有效性List<Boolean> biaojis = new ArrayList<>();for(XinxiShitucongEntity data: congdatas){if(Objects.equals(data.getZdtype(), "2")){if(!Objects.equals(data.getZdzhi(), "")&& data.getZdzhi()!=null){if(ObjectUtil.isNotNull(dataxx.get(data.getName())) && dataxx.get(data.getName()).equals(data.getZdzhi())){// 如果被其中一个条件筛选出来就给整条数据标记truebiaojis.add(true);}else{// 如果被其中一个条件没有筛选出来就给整条数据标记falsebiaojis.add(false);}}else{// 筛选条件没有填的是都要筛选的biaojis.add(true);}}}// 只有没有false标记的数据才是被筛选出来的if(!biaojis.contains(false)){newdatas.add(dataxx);}}// 总数Integer zhucount = newdatas.size();int pagenum = (xinxiShituzhu.getPageNum()-1)*xinxiShituzhu.getPageSize();int pagesize = pagenum + xinxiShituzhu.getPageSize();// list截取List<Map<String, Object>> newzhudatas = CollectionUtil.sub(newdatas, pagenum, pagesize);List<Map<String, Object>> newheaddatas = new ArrayList<>();// 获取表头信息for(Map<String, Object> data : newzhudatas){for(String key: data.keySet()){Map<String, Object> newmapdata = new LinkedHashMap<>();newmapdata.put("key", key);newheaddatas.add(newmapdata);}break;}return R.ok().put("count", zhucount).put("data", newzhudatas).put("headdata", newheaddatas);}}

sql拼接xml(要用LinkedHashMap类型，不然字段顺序不固定，数据展示就很难看)：

 <select id="getAllShituDatas" resultType="java.util.LinkedHashMap">${shitusql}</select><select id="getAllShituCount" resultType="java.lang.Integer">${shitusql}</select>

主Entity：

从Entity：

展示页面的vue代码：

<template><div class="sanfangcangkuliang-main"><el-form :model="form" :inline="true" ><el-form-item :label="item.zdname" v-for="item of canshulist" :key="item.name" ><el-input v-model="item.zdzhi" v-if="item.zdname!='供应商' && item.zdname!='客户' && item.zdname.indexOf('员')==-1 && item.zdname!='日期'" :placeholder="item.zdtype==1" />"></el-input><!--  --><el-select v-model="item.zdzhi" filterable clearable :placeholder="item.zdtype==1?'必填':'选填'" v-if="item.zdname=='供应商'"><el-optionv-for="item in gongyingshanglist":key="item.gys":label="item.gys":value="item.gys"></el-option></el-select><el-select v-model="item.zdzhi" filterable clearable :placeholder="item.zdtype==1?'必填':'选填'" v-if="item.zdname=='客户'"><el-optionv-for="item in kehulist":key="item.kh":label="item.kh":value="item.kh"></el-option></el-select><el-select v-model="item.zdzhi" filterable clearable :placeholder="item.zdtype==1?'必填':'选填'" v-if="item.zdname.indexOf('员')!=-1"><el-optionv-for="item in renyuanlist":key="item.yg":label="item.yg":value="item.yg"></el-option></el-select><el-date-pickerv-model="item.zdzhi"type="date"value-format="yyyy-MM-dd":placeholder="item.zdtype==1?'必填':'选填'"v-if="item.zdname=='日期'"></el-date-picker></el-form-item><el-form-item><el-button type="primary" style="display:inline-block; width: 80px;height: 40px;" @click="getList(1)">搜索</el-button><el-button style="display:inline-block; width: 80px;height: 40px;" @click="chongzhi()">重置</el-button><!-- 批量更新 --></el-form-item></el-form><el-paginationstyle="margin:30px"@size-change="handleSizeChange"@current-change="handleCurrentChange":current-page="mapdatas.pageNum":page-sizes="[50, 100, 200, 500, 1000, 2000, 5000]":page-size="mapdatas.pageSize"layout="total, sizes, prev, pager, next, jumper":total=total></el-pagination><el-table style="width: 100%; margin-left:40px" :data="tableData"><template v-for="(item,index) in maphead"><el-table-column :prop="item.key" :label="item.key" :key="index"></el-table-column></template></el-table><el-paginationstyle="margin:30px"@size-change="handleSizeChange"@current-change="handleCurrentChange":current-page="mapdatas.pageNum":page-sizes="[50, 100, 200, 500, 1000, 2000, 5000]":page-size="mapdatas.pageSize"layout="total, sizes, prev, pager, next, jumper":total=total></el-pagination></div></template><script>export default {data () {return {renyuanlist:[],gongyingshanglist:[],kehulist:[],form:{},maphead:[],mapdatas:{pageNum:1,pageSize:100,},canshulist:[],pageSize:100,currentPage:1,total:0,tableData: [],xiangqingshow:false,xiangqingdatas:[],tableDatazong:[],ckmclist: [],ywymc:""}},mounted(){this.getTableCanshu();this.getRenyuanDatas();this.getGyingshangDatas();this.getKehuDatas();},methods: {handleSizeChange(val) {this.mapdatas.pageSize = valthis.getList(2);},handleCurrentChange(val) {this.mapdatas.pageNum = valthis.getList(2);},getList(type){if(type==1){this.mapdatas.pageNum = 1this.mapdatas.pageSize = 100;}this.mapdatas['canshu'] = this.canshulistthis.$axios({method:'post',url:"/renren-fast/xinxi/xinxishituzhu/getalldatas",data:JSON.stringify(this.mapdatas),headers:{"Content-Type": "application/json"}}).then((response) =>{//这里使用了ES6的语法if(response.data.code==0 || response.data.code=='0'){this.tableData = response.data.data;this.total = response.data.count;this.maphead = response.data.headdata;}else{this.$message.error(response.data.msg);this.total = 0this.mapdatas.pageNum = 1this.mapdatas.pageSize = 100;}}).catch((error) =>{console.log(error) //请求失败返回的数据})},chongzhi(){for(let data of this.canshulist){data.zdzhi=""}this.mapdatas.pageNum = 1this.mapdatas.pageSize = 100;this.tableData = []this.getList(1)},// 获取表的参数getTableCanshu(){this.$axios({url: '/xinxi/xinxishituzhu/getcanshu',method: 'GET',params: {name: this.$route.query.name}}).then((res) => {if(res.data.code==0 || res.data.code=='0'){this.mapdatas = {btype: res.data.data.btype,deleted: res.data.data.deleted,id: res.data.data.id,name: res.data.data.name,pageNum: res.data.data.pageNum || 1,pageSize: res.data.data.pageSize || 100,paixuzd: res.data.data.paixuzd,reationtime: res.data.data.reationtime,updatetime: res.data.data.updatetime,zhushi: res.data.data.zhushi}this.canshulist = res.data.data.canshuif(res.data.data.btype==1){this.getList(1);}}})},// 获取人员getRenyuanDatas(){this.$axios({url: '/xinxi/xinxishituzhu/getyuangongdatas',method: 'GET',// params: {// name: this.$route.query.name// }}).then((res) => {if(res.data.code==0 || res.data.code=='0'){this.renyuanlist = res.data.data;}})},// 获取供应商getGyingshangDatas(){this.$axios({url: '/xinxi/xinxishituzhu/getgongyingshangdatas',method: 'GET',// params: {// name: this.$route.query.name// }}).then((res) => {if(res.data.code==0 || res.data.code=='0'){this.gongyingshanglist = res.data.data;}})},// 获取客户getKehuDatas(){this.$axios({url: '/xinxi/xinxishituzhu/getkehudatas',method: 'GET',// params: {// name: this.$route.query.name// }}).then((res) => {if(res.data.code==0 || res.data.code=='0'){this.kehulist = res.data.data;}})},}}</script><style scoped>*{margin: 0;padding: 0;}</style>

因为ERP有几十个报表要迁移，之前领导都是上级领导想看报表但是他们不使用ERP只使用OA，就会我们做进OA报表，只有OA实现不了的我才自己写后台接口和前端页面，这次说要全部迁移，我看一下几十个报表，如果像原来那样一个个写后台接口和前端页面的话不知道要写到什么时候，于是花了两天写了这个功能出来，目前正常使用没问题（因为存储过程必须要填参数才可以出数据，所以存储过程的展示页面需要输入必填参数以后点查询出数据，视图则是可以直接出数据）

后续：
添加下载功能，因为我之前设计的参数数据结构有点复杂，get传值有问题，所以只能下载全部数据而不是筛选后的数据

截图：

后端代码：

@GetMapping("/getalldatasExcel")public void getalldatasExcel(HttpServletResponse response, @RequestParam Map<String, Object> params) throws IOException {String name = (String) params.get("name");String zhushi = (String) params.get("zhushi");String btype = (String) params.get("btype");List canshus = new ArrayList();if(ObjectUtil.isNotNull(params.get("canshus"))){canshus = Arrays.asList(((String)params.get("canshus")).split(","));}// 视图if(Objects.equals(btype, "1")){// 拼接sqlString querySql = "SELECT * FROM "+name +" WHERE 1=1 ";List<Map<String, Object>> zhudatas = xinxiShituzhuService.getAllShituDatas(querySql);if(zhudatas.size()>0) {List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();for (String key : zhudatas.get(0).keySet()) {ExcelExportEntity colEntity = new ExcelExportEntity(key, key);colEntity.setNeedMerge(true);colList.add(colEntity);}Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, zhudatas);if (workbook != null) {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("数据报表", "UTF-8") + ".xls");workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}}}// 存储过程else{// 拼接sqlString querySql = "EXEC ["+name+"]";List<String> canshudatas = xinxiShituzhuService.getcanshunames(zhushi);List<String> canshusxx = new ArrayList<>();// 遍历所有参数for(int i=0; i < canshudatas.size(); i++){// 视图必要参数添加canshusxx.add(" @"+canshudatas.get(i)+"= '"+canshus.get(i)+"'");}if(canshusxx.size()>0){querySql = querySql + StringUtils.join(canshusxx, ",");}// 获取所有数据List<Map<String, Object>> zhudatas = xinxiShituzhuService.getAllShituDatas(querySql);if(zhudatas.size()>0){List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();for(String key: zhudatas.get(0).keySet()){ExcelExportEntity colEntity = new ExcelExportEntity(key, key);colEntity.setNeedMerge(true);colList.add(colEntity);}Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, zhudatas);if (workbook != null) {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("数据报表", "UTF-8") + ".xls");workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}}}}

前端代码：

xiazaiDatas(){console.log("this.canshulist::",this.canshulist)let canshu = []for(let data of this.canshulist){if(data['zdtype']=='1' && (data['zdzhi']!=null || data['zdzhi']!='')){canshu.push(data['zdzhi'])}}let canshusrt = ''if(canshu.length>0){canshusrt = canshu.join(',')}this.$axios({url: '/renren-fast/xinxi/xinxishituzhu/getalldatasExcel',method: 'get',params:{zhushi:this.$route.query.name,canshus:canshusrt,btype:this.mapdatas['btype'],name:this.mapdatas['name']},responseType: 'blob'}).then((res) => {// console.log(res)const content = resconst blob = new Blob([content.data])const fileName = '报表数据.xls'if ('download' in document.createElement('a')) { // 非IE下载const elink = document.createElement('a')elink.download = fileNameelink.style.display = 'none'elink.href = URL.createObjectURL(blob)document.body.appendChild(elink)elink.click()URL.revokeObjectURL(elink.href) // 释放URL 对象document.body.removeChild(elink)} else { // IE10+下载navigator.msSaveBlob(blob, fileName)}})},