src/main/java/com/zy/asrs/controller/ReportDownloadController.java
New file @@ -0,0 +1,181 @@ package com.zy.asrs.controller; import com.alibaba.excel.EasyExcel; import com.alibaba.fastjson.JSON; import com.baomidou.mybatisplus.mapper.EntityWrapper; import com.baomidou.mybatisplus.plugins.Page; import com.core.common.Cools; import com.core.common.DateUtils; import com.core.common.R; import com.zy.asrs.entity.AgvWrkMastLog; import com.zy.asrs.entity.ViewWorkCountInView; import com.zy.asrs.mapper.ReportQueryMapper; import com.zy.asrs.service.AgvWrkMastLogService; import com.zy.common.web.BaseController; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.HashMap; import java.util.List; import java.util.Map; @RestController @RequestMapping("/report/download") public class ReportDownloadController extends BaseController { @Autowired private ReportQueryMapper reportQueryMapper; @Autowired private AgvWrkMastLogService wrkMastLogService; /** * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel） * 日入库汇总查询 * * @since 2.1.1 */ @GetMapping("/in") public void download1(HttpServletResponse response, @RequestParam Map<String, Object> param) throws IOException { try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("日入库汇总", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), ViewWorkCountInView.class).autoCloseStream(Boolean.FALSE).sheet("sheet1") .doWrite(getInData(param)); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<>(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } private List<ViewWorkCountInView> getInData(Map<String, Object> param) { String startTime = "1970.1.2"; String endTime = "2099.1.2"; if (!Cools.isEmpty(param.get("query_date"))) { String queryDate = (String) param.get("query_date"); String[] split = queryDate.split(" - "); startTime = split[0].split(" ")[0].replace("-", "."); endTime = split[1].split(" ")[0].replace("-", "."); } List<ViewWorkCountInView> allCountIn = reportQueryMapper.selectWorkCountIn(null, null, (String) param.get("matnr"), startTime, endTime); return allCountIn; } /** * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel） * 日入库汇总查询 * * @since 2.1.1 */ @RequestMapping("/out") public void download2(HttpServletResponse response, @RequestParam Map<String, Object> param) throws IOException { try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("日出库汇总", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), ViewWorkCountInView.class).autoCloseStream(Boolean.FALSE).sheet("sheet1") .doWrite(getOutData(param)); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<>(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } private List<ViewWorkCountInView> getOutData(Map<String, Object> param) { String startTime = "1970.1.2"; String endTime = "2099.1.2"; if (!Cools.isEmpty(param.get("query_date"))) { String queryDate = (String) param.get("query_date"); String[] split = queryDate.split(" - "); startTime = split[0].split(" ")[0].replace("-", "."); endTime = split[1].split(" ")[0].replace("-", "."); } List<ViewWorkCountInView> allCountIn = reportQueryMapper.selectWorkCountOut(Integer.valueOf((String) param.get("pageNumber")), Integer.valueOf((String) param.get("pageSize")), (String) param.get("matnr"), startTime, endTime); return allCountIn; } /** * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel） * 日入库汇总查询 * * @since 2.1.1 */ @RequestMapping("/wrkMastLog") public void wrkMastLog(HttpServletResponse response, @RequestParam Map<String, Object> param) throws IOException { try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("工作档维护日志", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), ViewWorkCountInView.class).autoCloseStream(Boolean.FALSE).sheet("sheet1") .doWrite(getWrkMastLogData(param)); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<>(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } private List<AgvWrkMastLog> getWrkMastLogData(Map<String, Object> param) { excludeTrash(param); EntityWrapper<AgvWrkMastLog> wrapper = new EntityWrapper<>(); convert(param, wrapper); return wrkMastLogService.selectList(wrapper); } private <T> void convert(Map<String, Object> map, EntityWrapper<T> wrapper) { for (Map.Entry<String, Object> entry : map.entrySet()) { String val = String.valueOf(entry.getValue()); if (val.contains(RANGE_TIME_LINK)) { String[] dates = val.split(RANGE_TIME_LINK); wrapper.ge(entry.getKey(), DateUtils.convert(dates[0])); wrapper.le(entry.getKey(), DateUtils.convert(dates[1])); } else { if (entry.getKey().equals("manu_type")) { wrapper.like(entry.getKey(), val); } else { wrapper.eq(entry.getKey(), val); } } } } } src/main/java/com/zy/asrs/entity/ViewWorkCountInView.java
@@ -1,11 +1,19 @@ package com.zy.asrs.entity; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; @Data public class ViewWorkCountInView { @ExcelProperty("入库日期") private String oneday; @ExcelProperty("物料号") private String matnr; @ExcelProperty("日期标题") private String maktx; @ExcelProperty("数量") private String anfme; } src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java
@@ -89,6 +89,7 @@ List<ViewWorkCountInView> selectWorkCountIn(@Param("curr") Integer curr, @Param("limit") Integer limit, @Param("matnr")String matnr, @Param("start") String startTime, @Param("end") String endTime); Integer selectWorkCountInTotal(String matnr, @Param("start") String startTime, @Param("end") String endTime); List<ViewWorkCountInView> selectWorkCountOut(@Param("curr") Integer pageNumber, @Param("limit") Integer pageSize, String matnr, @Param("start") String startTime, @Param("end") String endTime); src/main/java/com/zy/common/task/ActivateScheduler.java
@@ -18,8 +18,8 @@ public class ActivateScheduler { // @Scheduled(cron = "0/1 * 8 * * ? ") // 每天8点 @Scheduled(cron = "* 0/1 * * * ? ") // 每分钟 @PostConstruct //@Scheduled(cron = "* 0/1 * * * ? ") // 每分钟 //@PostConstruct private void execute(){ // 获取激活码 String activationCode = SystemProperties.getActivationCode(OSinfo.getOSname().getActivationCodePath()); src/main/resources/application.yml
@@ -10,8 +10,8 @@ enabled: false datasource: driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://127.0.0.1:1433;databasename=phyzasrs # url: jdbc:sqlserver://192.168.4.15:1433;databasename=phyzasrs #url: jdbc:sqlserver://127.0.0.1:1433;databasename=phyzasrs url: jdbc:sqlserver://192.168.108.248:1433;databasename=phyzasrs username: sa password: sa@123 mvc: @@ -34,7 +34,7 @@ # log-impl: org.apache.ibatis.logging.stdout.StdOutImpl logging: path: /stock/out/@pom.build.finalName@/logs path: stock/out/@pom.build.finalName@/logs super: pwd: xltys1995 src/main/resources/mapper/ViewStockUseMapper.xml
@@ -152,7 +152,10 @@ </if> ) a ) b WHERE 1=1 and b.row between ((#{curr}-1)*#{limit}+1) and (#{curr}*#{limit}) WHERE 1=1 <if test="curr != null and curr !=''"> and b.row between ((#{curr}-1)*#{limit}+1) and (#{curr}*#{limit}) </if> </select> <select id="selectWorkCountInTotal" resultType="java.lang.Integer"> @@ -183,7 +186,10 @@ and matnr = #{matnr} </if> ) a ) b WHERE 1=1 and b.row between ((#{curr}-1)*#{limit}+1) and (#{curr}*#{limit}) WHERE 1=1 <if test="curr != null and curr !=''"> and b.row between ((#{curr}-1)*#{limit}+1) and (#{curr}*#{limit}) </if> </select> <select id="selectWorkCountOutTotal" resultType="java.lang.Integer"> SELECT COUNT(*) FROM asr_wrkout_count_view src/main/webapp/static/js/agvHandWrkMast/agvHandWrkMast.js
@@ -109,6 +109,7 @@ 'wrkMast': exportData, 'fields': fields }; $.ajax({ url: baseUrl+"/wrkMast/export/auth", headers: {'token': localStorage.getItem('token')}, src/main/webapp/static/js/handWrkMast/handWrkMast.js
@@ -101,32 +101,40 @@ fields.push(col.field); } }); // var exportData = {}; // $.each($('#search-box [name]').serializeArray(), function() { // exportData[this.name] = this.value; // }); // var param = { // 'wrkMast': exportData, // 'fields': fields // }; var param = '?'; var exportData = {}; $.each($('#search-box [name]').serializeArray(), function() { exportData[this.name] = this.value; param += this.name + '=' + this.value + '&'; }); var param = { 'wrkMast': exportData, 'fields': fields }; $.ajax({ url: baseUrl+"/wrkMast/export/auth", headers: {'token': localStorage.getItem('token')}, data: JSON.stringify(param), dataType:'json', contentType:'application/json;charset=UTF-8', method: 'POST', success: function (res) { window.location.href = baseUrl + "/report/download/wrkMastLog" + param; layer.closeAll(); if (res.code === 200) { table.exportFile(titles,res.data,'xls'); } else if (res.code === 403) { top.location.href = baseUrl+"/"; } else { layer.msg(res.msg) } } }); // $.ajax({ // url: baseUrl+"/wrkMast/export/auth", // headers: {'token': localStorage.getItem('token')}, // data: JSON.stringify(param), // dataType:'json', // contentType:'application/json;charset=UTF-8', // method: 'POST', // success: function (res) { // layer.closeAll(); // if (res.code === 200) { // table.exportFile(titles,res.data,'xls'); // } else if (res.code === 403) { // top.location.href = baseUrl+"/"; // } else { // layer.msg(res.msg) // } // } // }); }); break; } src/main/webapp/static/js/report/workCountIn.js
@@ -1,4 +1,5 @@ var pageCurr; function getCol() { var cols = [ {field: 'oneday', align: 'center', title: '入库日期', width: 200} @@ -92,32 +93,40 @@ fields.push(col.field); } }); var param = '?'; var exportData = {}; $.each($('#search-box [name]').serializeArray(), function() { exportData[this.name] = this.value; param += this.name + '=' + this.value + '&'; }); var param = { fields: fields, exportData: exportData }; $.ajax({ url: baseUrl+"/report/viewWorkInExport.action", headers: {'token': localStorage.getItem('token')}, data: JSON.stringify(param), dataType:'json', contentType:'application/json;charset=UTF-8', method: 'POST', success: function (res) { window.location.href = baseUrl + "/report/download/in" + param; layer.closeAll(); if (res.code === 200) { table.exportFile(titles,res.data,'xls'); } else if (res.code === 403) { top.location.href = baseUrl+"/"; } else { layer.msg(res.msg) } } }); // var param = { // fields: fields, // exportData: exportData // }; // $.ajax({ // url: baseUrl + "/report/download/in", // headers: {'token': localStorage.getItem('token')}, // data: JSON.stringify(param), // responseType: "blob", // contentType: 'application/json;charset=UTF-8', // method: 'POST', // success: function (res) { // layer.closeAll(); // if (res.code === 200) { // var blob = new Blob([response], { type: 'application/vnd.ms-excel' }); // var link = document.createElement('a'); // link.href = URL.createObjectURL(blob); // link.download = 'exported_file.xls'; // 设置下载文件名 // link.click(); // } else if (res.code === 403) { // top.location.href = baseUrl + "/"; // } else { // layer.msg(res.msg) // } // } // }); }); break; } src/main/webapp/static/js/report/workCountOut.js
@@ -91,32 +91,36 @@ fields.push(col.field); } }); var param = '?'; var exportData = {}; $.each($('#search-box [name]').serializeArray(), function() { exportData[this.name] = this.value; param += this.name + '=' + this.value + '&'; }); var param = { fields: fields, exportData: exportData }; $.ajax({ url: baseUrl+"/report/viewWorkOutExport.action", headers: {'token': localStorage.getItem('token')}, data: JSON.stringify(param), dataType:'json', contentType:'application/json;charset=UTF-8', method: 'POST', success: function (res) { window.location.href = baseUrl + "/report/download/in" + param; layer.closeAll(); if (res.code === 200) { table.exportFile(titles,res.data,'xls'); } else if (res.code === 403) { top.location.href = baseUrl+"/"; } else { layer.msg(res.msg) } } }); // var param = { // fields: fields, // exportData: exportData // }; // $.ajax({ // url: baseUrl+"/report/download/out", // headers: {'token': localStorage.getItem('token')}, // data: JSON.stringify(param), // dataType:'json', // contentType:'application/json;charset=UTF-8', // method: 'POST', // success: function (res) { // layer.closeAll(); // if (res.code === 200) { // table.exportFile(titles,res.data,'xls'); // } else if (res.code === 403) { // top.location.href = baseUrl+"/"; // } else { // layer.msg(res.msg) // } // } // }); }); break; } src/main/webapp/views/handWrkMast/handWrkMast.html
@@ -104,8 +104,8 @@ </div> <script type="text/html" id="toolbar"> <div class="layui-btn-container"> <button class="layui-btn layui-btn-normal" id="btn-pri-add" lay-event="priAdd"><i class="layui-icon"></i>增加优先级</button> <button class="layui-btn layui-btn-normal" id="btn-pri-red" lay-event="priRed"><i class="layui-icon"></i>降低优先级</button> <!-- <button class="layui-btn layui-btn-normal" id="btn-pri-add" lay-event="priAdd"><i class="layui-icon"></i>增加优先级</button>--> <!-- <button class="layui-btn layui-btn-normal" id="btn-pri-red" lay-event="priRed"><i class="layui-icon"></i>降低优先级</button>--> <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData">导出</button> </div> </script> @@ -127,7 +127,7 @@ <script type="text/javascript" src="../../static/layui/layui.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/common.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/cool.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/handWrkMast/handWrkMast.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/handWrkMast/handWrkMast.js?v=1" charset="utf-8"></script> <iframe id="detail-iframe" scrolling="auto" style="display:none;"></iframe>
@@ -46,7 +46,7 @@ <table class="layui-hide" id="workIn" lay-filter="workIn"></table> <script type="text/html" id="toolbar"> <div class="layui-btn-container"> <!-- <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" >导出</button>--> <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" >导出</button> </div> </script> @@ -57,7 +57,7 @@ <script type="text/javascript" src="../../static/layui/layui.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/common.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/cool.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/report/workCountIn.js" charset="utf-8"></script> <script type="text/javascript" src="../../static/js/report/workCountIn.js?v=3" charset="utf-8"></script> </body> </html>
@@ -47,7 +47,7 @@ <table class="layui-hide" id="workOut" lay-filter="workOut"></table> <script type="text/html" id="toolbar"> <div class="layui-btn-container"> <!-- <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" >导出</button>--> <button class="layui-btn layui-btn-primary layui-btn-sm" id="btn-export" lay-event="exportData" >导出</button> </div> </script>
