consum-base/src/main/java/com/consum/base/controller/DepFormScrappedController.java
@@ -13,6 +13,7 @@ import com.walker.db.page.GenericPager; import com.walker.infrastructure.utils.CollectionUtils; import com.walker.infrastructure.utils.DateUtils; import com.walker.infrastructure.utils.StringUtils; import com.walker.web.ResponseValue; import io.swagger.annotations.Api; import io.swagger.annotations.ApiImplicitParam; @@ -26,12 +27,8 @@ import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; @@ -188,61 +185,83 @@ } } public static void main(String[] args) { TemplateExportParams exportParams = new TemplateExportParams("D:\\MyProjects\\jmy\\低值易耗品\\low-consum-manage\\deploy-jar-single\\src\\main\\resources\\import\\deptScrapped.xlsx"); exportParams.setHeadingStartRow(2); @GetMapping("/deptListByModel") public ResponseValue export() { LDeptFormScrappedQry param = CommonUtil.getObjFromReq(LDeptFormScrappedQry.class); LDeptFormScrappedQry query = new LDeptFormScrappedQry(); CommonUtil.copyProperties(param, query); param = query; List<Map<String, Object>> export = new ArrayList<>(); // 创建第一条数据 Map<String, Object> data1 = new HashMap<>(); data1.put("agencyName", 1); data1.put("baseGoodsTemplateName", "Bob"); data1.put("scrappedName", "Bob"); data1.put("bz", "Bob"); data1.put("baseGoodsModelsName", 30); data1.put("counts", 30); data1.put("totalPrice", 30); export.add(data1); // 创建第二条数据 Map<String, Object> data2 = new HashMap<>(); data2.put("agencyName", 2); data2.put("baseGoodsTemplateName", "Bob"); data2.put("scrappedName", "Bob"); data2.put("bz", "Bob"); data2.put("baseGoodsModelsName", 30); data2.put("counts", 30); data2.put("totalPrice", 30); export.add(data2); Map<String, Object> map = new HashMap<>(); map.put("code", "2023"); map.put("date", "2023"); map.put("name", "operatorName"); map.put("countNum", 0); map.put("totalAmount", 0); map.put("maplist", export); Workbook workbook = ExcelExportUtil.exportExcel(exportParams, map); File file = new File("C:\\Users\\cy\\Desktop\\新建文件夹"); OutputStream stream = null; try { SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd"); stream = new FileOutputStream(new File(file, "机构客户信息表.xls")); //document.write(stream); workbook.write(stream); } catch (Exception e) { e.printStackTrace(); } finally { if (stream != null) ; try { stream.close(); } catch (IOException e) { e.printStackTrace(); } FinSysTenantUser sysInfo = getSysInfo(); if (sysInfo == null) { throw new RuntimeException("登录用户信息不存在"); } /*当前登录人只能看到自己机构下的列表*/ String tenantId = sysInfo.getTenantId(); String paramAgencyId = param.getAgencyId(); if (StringUtils.isEmpty(paramAgencyId) || !paramAgencyId.startsWith(tenantId)) { param.setAgencyId(tenantId); } List<Map<String, Object>> export = depFormScrappedService.listByModel(param); return ResponseValue.success(export); } // public static void main(String[] args) { // TemplateExportParams exportParams = new TemplateExportParams("D:\\MyProjects\\jmy\\低值易耗品\\low-consum-manage\\deploy-jar-single\\src\\main\\resources\\import\\deptScrapped.xlsx"); // exportParams.setHeadingStartRow(2); // // List<Map<String, Object>> export = new ArrayList<>(); // // // 创建第一条数据 // Map<String, Object> data1 = new HashMap<>(); // data1.put("agencyName", 1); // data1.put("baseGoodsTemplateName", "Bob"); // data1.put("scrappedName", "Bob"); // data1.put("bz", "Bob"); // data1.put("baseGoodsModelsName", 30); // data1.put("counts", 30); // data1.put("totalPrice", 30); // export.add(data1); // // // 创建第二条数据 // Map<String, Object> data2 = new HashMap<>(); // data2.put("agencyName", 2); // data2.put("baseGoodsTemplateName", "Bob"); // data2.put("scrappedName", "Bob"); // data2.put("bz", "Bob"); // data2.put("baseGoodsModelsName", 30); // data2.put("counts", 30); // data2.put("totalPrice", 30); // export.add(data2); // // // Map<String, Object> map = new HashMap<>(); // map.put("code", "2023"); // map.put("date", "2023"); // map.put("name", "operatorName"); // map.put("countNum", 0); // map.put("totalAmount", 0); // map.put("maplist", export); // // Workbook workbook = ExcelExportUtil.exportExcel(exportParams, map); // // File file = new File("C:\\Users\\cy\\Desktop\\新建文件夹"); // OutputStream stream = null; // try { // SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd"); // stream = new FileOutputStream(new File(file, "机构客户信息表.xls")); // //document.write(stream); // workbook.write(stream); // } catch (Exception e) { // e.printStackTrace(); // } finally { // if (stream != null) ; // try { // stream.close(); // } catch (IOException e) { // e.printStackTrace(); // } // } // } } consum-base/src/main/java/com/consum/base/pojo/query/LDeptFormScrappedQry.java
@@ -1,5 +1,6 @@ package com.consum.base.pojo.query; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** @@ -11,6 +12,10 @@ **/ @Data public class LDeptFormScrappedQry { /** * 报废单id */ private Long id; /** * 报废单号 */ @@ -37,6 +42,12 @@ */ private String agencyId; @ApiModelProperty("规格型号") private Long baseGoodsModelsId; @ApiModelProperty("部门ID") private Long departmentId; private Integer pageSize = 10; private Integer pageNum = 1;
@@ -202,8 +202,8 @@ paramts.put("businessFormCode", param.getBusinessFormCode()); } //机构 sql.append(" and scrap.AGENCY_ID = :agencyId "); if (param.getAgencyId() != null) { sql.append(" and scrap.AGENCY_ID = :agencyId "); paramts.put("agencyId", param.getAgencyId()); } //物品名称 @@ -253,15 +253,21 @@ DepFormScrappedModel depFormScrappedModel = new DepFormScrappedModel(); depFormScrappedModel.setDepFormScrappedGoodsId(scrappedGoodsId); List<DepFormScrappedModel> scrappedModelList = select(depFormScrappedModel); scrappedGoodsMap.put("models", scrappedModelList); List<List<DepFormScrappedModel>> modelList = scrappedModelList.stream() .collect(Collectors.groupingBy(DepFormScrappedModel::getBaseGoodsModelsId)) .values().stream() .map(list -> list.stream().collect(Collectors.toList())) .collect(Collectors.toList()); scrappedGoodsMap.put("models", modelList); goodsList.add(scrappedGoodsMap); } scrappedExtend.setGoods(goodsList); return scrappedExtend; } private static String EXPORT_SQL = "SELECT scrap.BUSINESS_FORM_CODE,scrap.DEAL_TIME,scrap.OPERATOR_NAME,scrap.AGENCY_NAME,model.BASE_GOODS_TEMPLATE_NAME,model.BASE_GOODS_MODELS_NAME,scrap.OUT_WAREHOUSE_NAME,model.COUNTS,2 AS TOTAL_PRICE,model.SCRAPPED_NAME,NULL AS bz FROM DEP_FORM_SCRAPPED scrap LEFT JOIN DEP_FORM_SCRAPPED_MODEL model ON scrap.id=model.DEP_FORM_SCRAPPED_ID WHERE 1=1 "; private static String EXPORT_SQL_END = "order by scrap.BUSINESS_FORM_CODE desc "; private static String EXPORT_SQL = "SELECT scrap.id,scrap.BUSINESS_FORM_CODE,model.BASE_GOODS_TEMPLATE_NAME,model.BASE_GOODS_MODELS_NAME,model.COUNTS,scrap.AGENCY_NAME,scrap.DEPARTMENT_NAME,scrap.OPERATOR_NAME,scrap.DEAL_TIME,scrap.OUT_WAREHOUSE_NAME,2 AS TOTAL_PRICE,model.SCRAPPED_NAME,NULL AS bz FROM DEP_FORM_SCRAPPED scrap LEFT JOIN DEP_FORM_SCRAPPED_MODEL model ON scrap.id=model.DEP_FORM_SCRAPPED_ID WHERE 1=1 "; private static String EXPORT_SQL_END = "order by scrap.DEAL_TIME DESC"; public List<Map<String, Object>> export(Long id, String agencyId) { HashMap<String, Object> paramts = new HashMap<>(); @@ -278,4 +284,54 @@ } return select(sql.append(EXPORT_SQL_END).toString(), paramts, new MapperUtil()); } public List<Map<String, Object>> listByModel(LDeptFormScrappedQry param) { HashMap<String, Object> paramts = new HashMap<>(); StringBuilder sql = new StringBuilder(EXPORT_SQL); //报废单id if (param.getId() != null) { sql.append(" and scrap.id = :id"); paramts.put("id", param.getId()); } //报废单号 if (param.getBusinessFormCode() != null) { sql.append(" and scrap.BUSINESS_FORM_CODE= :businessFormCode"); paramts.put("businessFormCode", param.getBusinessFormCode()); } //物品名称 if (StringUtils.isNotEmpty(param.getGoodsTemplateName())){ sql.append(" and model.BASE_GOODS_TEMPLATE_NAME LIKE :goodsTemplateName "); paramts.put("goodsTemplateName", StringUtils.CHAR_PERCENT + param.getGoodsTemplateName() + StringUtils.CHAR_PERCENT); } //规格型号 if (param.getBaseGoodsModelsId() != null) { sql.append("and model.BASE_GOODS_MODELS_ID =:baseGoodsModelsId "); paramts.put("baseGoodsModelsId", param.getBaseGoodsModelsId()); } //机构 if (param.getAgencyId() != null) { sql.append(" and scrap.AGENCY_ID = :agencyId "); paramts.put("agencyId", param.getAgencyId()); } // 部门 此处按的是物品使用部门 而不是 操作人所在部门 if (param.getDepartmentId() != null) { sql.append(" and scrap.OUT_WAREHOUSE_ID= :departmentId"); paramts.put("departmentId", param.getDepartmentId()); } //创建人 if (!StringUtils.isEmpty(param.getOperatorName())) { sql.append(" and scrap.OPERATOR_NAME LIKE :operatorName "); paramts.put("operatorName", StringUtils.CHAR_PERCENT + param.getOperatorName() + StringUtils.CHAR_PERCENT); } //报废时间 if (param.getStartTime() != null) { sql.append(" and scrap.DEAL_TIME >=:dealTimeStart "); paramts.put("dealTimeStart", param.getStartTime() * 1000000); } if (param.getEndTime() != null) { sql.append(" and scrap.DEAL_TIME <:dealTimeEnd "); paramts.put("dealTimeEnd", param.getEndTime() * 1000000 + 240000); } return select(sql.append(EXPORT_SQL_END).toString(), paramts, new MapperUtil()); } }
