hazmat-admin/src/main/java/com/gkhy/hazmat/admin/controller/web/HzStatisticController.java
@@ -1,6 +1,7 @@ package com.gkhy.hazmat.admin.controller.web; import com.gkhy.hazmat.common.annotation.Anonymous; import com.gkhy.hazmat.common.api.CommonResult; import com.gkhy.hazmat.system.service.HzStatisticService; import io.swagger.annotations.Api; @@ -9,8 +10,12 @@ import io.swagger.annotations.ApiOperation; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.IOException; /** * <p> @@ -65,4 +70,14 @@ return CommonResult.success(statisticService.useStatic(startTime,endTime)); } @ApiOperation(value = "危化品使用记录导出") @ApiImplicitParams({ @ApiImplicitParam(paramType = "query", name = "startTime", dataType = "string", required = false, value = "开始时间,格式xxxx-xx-xx 00:00:00"), @ApiImplicitParam(paramType = "query", name = "endTime", dataType = "string", required = false, value = "结束时间,格式xxxx-xx-xx 23:59:59") }) @GetMapping("/importBaiscUse") public void ImportBasicUse(HttpServletResponse response, String startTime, String endTime) throws IOException { statisticService.importBaiscUse(response,startTime,endTime); } } hazmat-common/src/main/java/com/gkhy/hazmat/common/excel/HazmatBasicUseExcelData.java
对比新文件 @@ -0,0 +1,41 @@ package com.gkhy.hazmat.common.excel; import com.alibaba.excel.annotation.ExcelProperty; import io.swagger.annotations.ApiModelProperty; import lombok.Getter; import lombok.Setter; import java.math.BigDecimal; @Getter @Setter public class HazmatBasicUseExcelData { @ExcelProperty("序号") private Integer index; @ExcelProperty("名称") private String name; @ExcelProperty("CAS") private String cas; @ExcelProperty("试剂类型") private String productType; @ExcelProperty("危险性质") private String productCharacter; @ExcelProperty("供应商") private String supplier; @ExcelProperty("规格") private String productFormat; @ExcelProperty("最小包装类型") private String minPackage; @ExcelProperty("用量") private Integer count; } hazmat-common/src/main/java/com/gkhy/hazmat/common/excel/HazmatBasicUseExcelDataListener.java
对比新文件 @@ -0,0 +1,17 @@ package com.gkhy.hazmat.common.excel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; public class HazmatBasicUseExcelDataListener extends AnalysisEventListener<HazmatBasicUseExcelData> { @Override public void invoke(HazmatBasicUseExcelData hazmatBasicUseExcelData, AnalysisContext analysisContext) { System.out.println("invoke"); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { //所有数据解析完成后的操作 System.out.println("doAfterAllAnalysed"); } } hazmat-system/src/main/java/com/gkhy/hazmat/system/domain/HzHazmatBasic.java
@@ -165,5 +165,14 @@ @TableField(exist = false) private String companyName; @ApiModelProperty("危化品特性类别") @TableField(exist = false) private String riskType; @ApiModelProperty("危化品分类信息id") @TableField("secientific_id") private Integer secientificId; } hazmat-system/src/main/java/com/gkhy/hazmat/system/domain/HzProductBasic.java
@@ -142,5 +142,13 @@ @TableField(exist = false) private String companyName; @ApiModelProperty("危化品特性类别") @TableField(exist = false) private String riskType; @ApiModelProperty("危化品分类信息id") @TableField("secientific_id") private Integer secientificId; } hazmat-system/src/main/java/com/gkhy/hazmat/system/domain/vo/HzSecientificVo.java
@@ -5,11 +5,16 @@ import lombok.Getter; import lombok.Setter; @Getter @Setter public class HzSecientificVo extends HzSecientific { private String peculiarityType; public String getPeculiarityType() { return peculiarityType; } public void setPeculiarityType(String peculiarityType) { this.peculiarityType = peculiarityType; } } hazmat-system/src/main/java/com/gkhy/hazmat/system/service/HzStatisticService.java
@@ -6,6 +6,8 @@ import com.gkhy.hazmat.system.domain.vo.HzHazmatUseVO; import com.gkhy.hazmat.system.domain.vo.HzHomeDataVO; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** @@ -43,4 +45,6 @@ * @return */ CommonPage useStatic(String startTime, String endTime); void importBaiscUse(HttpServletResponse response, String startTime, String endTime) throws IOException; } hazmat-system/src/main/java/com/gkhy/hazmat/system/service/impl/HzHazmatBasicServiceImpl.java
@@ -83,6 +83,7 @@ HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(hazmatBasic.getName()); hazmatBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : null); hazmatBasic.setPeculiarityNumber(hzSecientificVo!=null? hzSecientificVo.getPeculiarityNumber() : 0); hazmatBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); int row = baseMapper.insert(hazmatBasic); if (row < 1) { @@ -99,6 +100,10 @@ SysUser currentUser = SecurityUtils.getLoginUser().getUser(); checkUserAllowed(hazmatBasic,currentUser); hazmatBasic.setUpdateBy(currentUser.getUsername()); HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(hazmatBasic.getName()); hazmatBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : null); hazmatBasic.setPeculiarityNumber(hzSecientificVo!=null? hzSecientificVo.getPeculiarityNumber() : 0); hazmatBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); int row=baseMapper.updateById(hazmatBasic); if(row<1){ throw new ApiException("更新危化品基础信息失败"); @@ -170,6 +175,7 @@ HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(hazmatBasic.getName()); hazmatBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : ""); hazmatBasic.setPeculiarityNumber(hzSecientificVo!=null? hzSecientificVo.getPeculiarityNumber() : 0); hazmatBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); hazmatBasic.setKind(kind); hazmatBasic.setMinPackage(minPackage); hazmat-system/src/main/java/com/gkhy/hazmat/system/service/impl/HzHazmatServiceImpl.java
@@ -195,6 +195,7 @@ HzWarehouseRecord warehouseRecord = new HzWarehouseRecord() .setWarehouseId(hazmat.getWarehouseId()) .setBasicId(hazmat.getBasicId()) .setCupboardId(hazmat.getCupboardId()) .setCreateId(currentUser.getId()) .setNum(-1) .setState(OperateStatusEnum.USING.getCode()) @@ -249,6 +250,7 @@ HzWarehouseRecord warehouseRecord = new HzWarehouseRecord() .setWarehouseId(dbhazmat.getWarehouseId()) .setBasicId(dbhazmat.getBasicId()) .setCupboardId(hazmat.getCupboardId()) .setCreateId(currentUser.getId()) .setNum(1) .setState(OperateStatusEnum.RETURN.getCode()) @@ -318,6 +320,7 @@ HzWarehouseRecord warehouseRecord = new HzWarehouseRecord() .setWarehouseId(hazmat.getWarehouseId()) .setBasicId(hazmat.getBasicId()) .setCupboardId(hazmat.getCupboardId()) .setCreateId(currentUser.getId()) .setNum(-1) .setState(OperateStatusEnum.DISCARD.getCode()) hazmat-system/src/main/java/com/gkhy/hazmat/system/service/impl/HzProductBasicServiceImpl.java
@@ -79,6 +79,7 @@ HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(productBasic.getName()); productBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : null); productBasic.setPeculiarityNumber(hzSecientificVo!=null ? hzSecientificVo.getPeculiarityNumber() : 0); productBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); if (!checkProductSnUnique(productBasic)) { throw new ApiException("产品编号已存在"); @@ -99,6 +100,10 @@ SysUser currentUser = SecurityUtils.getLoginUser().getUser(); checkUserAllowed(productBasic,currentUser); productBasic.setUpdateBy(currentUser.getUsername()); HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(productBasic.getName()); productBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : null); productBasic.setPeculiarityNumber(hzSecientificVo!=null ? hzSecientificVo.getPeculiarityNumber() : 0); productBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); int row=baseMapper.updateById(productBasic); if(row<1){ throw new ApiException("更新成品基础信息失败"); @@ -169,6 +174,7 @@ HzSecientificVo hzSecientificVo = secientificMapper.selectBySecientificName(productBasic.getName()); productBasic.setPeculiarityType(hzSecientificVo!=null? hzSecientificVo.getPeculiarityType() : null); productBasic.setPeculiarityNumber(hzSecientificVo!=null ? hzSecientificVo.getPeculiarityNumber() : 0); productBasic.setSecientificId(hzSecientificVo!=null? hzSecientificVo.getId() : null); productBasic.setKind(kind); productBasic.setMinPackage(minPackage); productBasic.setCompanyId(currentUser.getCompanyId()); hazmat-system/src/main/java/com/gkhy/hazmat/system/service/impl/HzSecientificServiceImpl.java
@@ -113,16 +113,16 @@ private void checkData(SecientificExcelData secientificExcelDatum) { if(StringUtils.isBlank(secientificExcelDatum.getSecientificName())){ throw new ApiException("序号"+secientificExcelDatum.getIndex()+"品名为空"); throw new ApiException("序号"+secientificExcelDatum.getIndex()+"品名为空"+"危险性类别:"+secientificExcelDatum.getRiskType()); } if(StringUtils.isBlank(secientificExcelDatum.getCas())){ throw new ApiException("序号"+secientificExcelDatum.getIndex()+"CAS码为空"); } if(StringUtils.isBlank(secientificExcelDatum.getRiskType())){ throw new ApiException("序号"+secientificExcelDatum.getIndex()+"危险性类别为空"); } if(secientificExcelDatum.getPeculiarityNumber() == null){ throw new ApiException("序号"+secientificExcelDatum.getIndex()+"危化品特性编码为空"); } // if(StringUtils.isBlank(secientificExcelDatum.getCas())){ // throw new ApiException("序号"+secientificExcelDatum.getIndex()+"CAS码为空"); // } // if(StringUtils.isBlank(secientificExcelDatum.getRiskType())){ // throw new ApiException("序号"+secientificExcelDatum.getIndex()+"危险性类别为空"); // } // if(secientificExcelDatum.getPeculiarityNumber() == null){ // throw new ApiException("序号"+secientificExcelDatum.getIndex()+"危化品特性编码为空"); // } } } hazmat-system/src/main/java/com/gkhy/hazmat/system/service/impl/HzStatisticServiceImpl.java
@@ -4,12 +4,16 @@ import cn.hutool.core.date.DateTime; import cn.hutool.core.date.DateUnit; import cn.hutool.core.date.DateUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.gkhy.hazmat.common.api.CommonPage; import com.gkhy.hazmat.common.config.IdTableNameHandler; import com.gkhy.hazmat.common.domain.entity.SysUser; import com.gkhy.hazmat.common.enums.HazmatPackageEnum; import com.gkhy.hazmat.common.enums.HazmatStatusEnum; import com.gkhy.hazmat.common.enums.UserTypeEnum; import com.gkhy.hazmat.common.excel.HazmatBasicUseExcelData; import com.gkhy.hazmat.common.exception.ApiException; import com.gkhy.hazmat.common.utils.PageUtils; import com.gkhy.hazmat.common.utils.SecurityUtils; @@ -20,9 +24,16 @@ import com.gkhy.hazmat.system.domain.vo.HzHomeDataVO; import com.gkhy.hazmat.system.mapper.*; import com.gkhy.hazmat.system.service.HzStatisticService; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.util.*; import java.util.stream.Collectors; @@ -186,7 +197,7 @@ }else{ startDate=startTime; endDate=endTime; if(DateUtil.between(new Date(endDate),new Date(startTime), DateUnit.DAY)>90){ if(DateUtil.between(DateUtil.parse(startDate),DateUtil.parse(startTime), DateUnit.DAY)>90){ throw new ApiException("只能查询90天以内数据"); } } @@ -210,6 +221,88 @@ return CommonPage.restPage(hazmatUseVOList); } @Override public void importBaiscUse(HttpServletResponse response, String startTime, String endTime) throws IOException { SysUser currentUser= SecurityUtils.getLoginUser().getUser(); checkUserAllowed(currentUser); OutputStream outputStream = response.getOutputStream(); try { String startDate=""; String endDate=""; if(StringUtils.isBlank(startTime)&&StringUtils.isBlank(endTime)){ Date currentDate=new Date(); startDate= DateUtil.format(DateUtil.beginOfDay(DateUtil.offsetDay(DateUtil.endOfDay(currentDate), -29)), DatePattern.NORM_DATETIME_FORMAT); endDate=DateUtil.format(DateUtil.endOfDay(currentDate),DatePattern.NORM_DATETIME_FORMAT); } else if (StringUtils.isBlank(startTime)&&StringUtils.isNotBlank(endTime)) { startDate= DateUtil.format(DateUtil.beginOfDay(DateUtil.offsetDay(DateUtil.endOfDay(new Date(endTime)), -29)), DatePattern.NORM_DATETIME_FORMAT); endDate=endTime; }else if (StringUtils.isNotBlank(startTime)&&StringUtils.isBlank(endTime)){ startDate=startTime; endDate=DateUtil.format(DateUtil.beginOfDay(DateUtil.offsetDay(DateUtil.endOfDay(new Date(endTime)), 29)), DatePattern.NORM_DATETIME_FORMAT); }else{ startDate=startTime; endDate=endTime; if(DateUtil.between(DateUtil.parse(startDate),DateUtil.parse(startTime), DateUnit.DAY)>90){ throw new ApiException("只能查询90天以内数据"); } } //设置分表id IdTableNameHandler.setCurrentId(currentUser.getCompanyId()); // PageUtils.startPage(); List<HzHazmatUseVO> hazmatUseVOList= hazmatMapper.useCountStatic(startDate, endDate,currentUser.getCompanyId()); IdTableNameHandler.removeCurrentId(); List<HazmatBasicUseExcelData> hazmatBasicUseExcelDatas = new ArrayList<>(); if(!hazmatUseVOList.isEmpty()) { List<Long> basicIds = hazmatUseVOList.stream().map(HzHazmatUseVO::getBasicId).collect(Collectors.toList()); List<HzHazmatBasic> hzHazmatBasicList = hazmatBasicMapper.selectHazmatBasicListByIds(basicIds); Map<Long, HzHazmatBasic> hazmatBasicMap = hzHazmatBasicList.stream().collect(Collectors.toMap(HzHazmatBasic::getId, item -> item)); Integer index=1; for (HzHazmatUseVO hu : hazmatUseVOList) { HazmatBasicUseExcelData hazmatBasicUseExcelData = new HazmatBasicUseExcelData(); hazmatBasicUseExcelData.setCount(hu.getCount()); HzHazmatBasic hazmatBasic = hazmatBasicMap.get(hu.getBasicId()); if (hazmatBasic != null) { BeanUtils.copyProperties(hazmatBasic,hazmatBasicUseExcelData); hazmatBasicUseExcelData.setIndex(index); hazmatBasicUseExcelData.setProductType(hazmatBasic.getHazmatType()); hazmatBasicUseExcelData.setProductCharacter(hazmatBasic.getHazmatCharacter()); hazmatBasicUseExcelData.setProductFormat(hazmatBasic.getHazmatFormat()); hazmatBasicUseExcelData.setMinPackage(HazmatPackageEnum.getInfoByCode(hazmatBasic.getMinPackage())); } index++; hazmatBasicUseExcelDatas.add(hazmatBasicUseExcelData); } } Date date = new Date(); long time = date.getTime(); String fileName = "危化品用量统计_"+time; this.setExcelResponseProp(response,fileName); //使用 EasyExcel 导出数据 EasyExcel.write(response.getOutputStream(), HazmatBasicUseExcelData.class) .excelType(ExcelTypeEnum.XLSX) .sheet("危化品使用记录统计表") // 设置 Sheet 名称 .doWrite(hazmatBasicUseExcelDatas); // 写入数据 } catch (IOException e) { throw new RuntimeException(e); } } private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException { //设置内容类型 response.setContentType("application/vnd.vnd.ms-excel"); //设置编码格式 response.setCharacterEncoding("utf-8"); //设置导出文件名称(避免乱码) String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), "UTF-8"); // 设置响应头 response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName); } public void checkUserAllowed(SysUser user) { if (user.getUserType().equals(UserTypeEnum.SYSTEM_USER.getCode())) { throw new ApiException("管理员不能操作"); hazmat-system/src/main/resources/mapper/system/HzEntryRecordMapper.xml
@@ -79,6 +79,12 @@ <if test="state != null"> AND a.state= #{state} </if> <if test="params.warehouseId !=null"> and a.warehouse_id = #{params.warehouseId} </if> <if test="params.cupboardId !=null"> and a.cupboard_id = #{params.cupboardId} </if> </where> order by a.id desc </select> hazmat-system/src/main/resources/mapper/system/HzHazmatBasicMapper.xml
@@ -31,14 +31,16 @@ <result property="remark" column="remark" /> <result property="maxEntry" column="max_entry" /> <result property="companyName" column="company_name" /> <result property="riskType" column="risk_type"/> </resultMap> <sql id="selectHazmatBasicVo"> select a.id, a.name, a.cas, a.hazmat_type,a.min_package, a.kind,a.hazmat_character,a.supplier,a.manufacturer,a.hazmat_format,a.metering, a.unit,a.price,a.per_box,a.safe_num,a.threshold,a.product_sn,a.company_id,a.version,a.peculiarity_type,a.peculiarity_number, a.create_by, a.create_time, a.update_by, a.update_time, a.remark,a.max_entry,b.name as company_name a.update_by, a.update_time, a.remark,a.max_entry,b.name as company_name,hs.risk_type from hz_hazmat_basic a left join sys_company b on b.id=a.company_id LEFT JOIN hz_secientific hs ON a.secientific_id = hs.id </sql> <update id="deleteHazmatBasicById" parameterType="java.lang.Long"> hazmat-system/src/main/resources/mapper/system/HzProductBasicMapper.xml
@@ -29,13 +29,16 @@ <result property="remark" column="remark" /> <result property="maxEntry" column="max_entry" /> <result property="companyName" column="company_name" /> <result property="riskType" column="risk_type"/> </resultMap> <sql id="selectProductBasicVo"> select a.id, a.name, a.cas, a.product_type, a.min_package,a.kind,a.product_character,a.supplier,a.manufacturer,a.product_format,a.metering, a.unit,a.price,a.per_box,a.product_sn,a.company_id,a.version,a.peculiarity_type,a.peculiarity_number, a.create_by, a.create_time, a.update_by, a.update_time, a.remark,a.max_entry,b.name as company_name a.unit,a.price,a.per_box,a.product_sn,a.company_id,a.version,a.peculiarity_type,a.peculiarity_number, a.create_by, a.create_time, a.update_by, a.update_time, a.remark,a.max_entry,b.name as company_name,hs.risk_type from hz_product_basic a left join sys_company b on b.id=a.company_id LEFT JOIN hz_secientific hs ON a.secientific_id = hs.id </sql> <update id="deleteProductBasicById" parameterType="java.lang.Long"> hazmat-system/src/main/resources/mapper/system/HzProductEntryRecordMapper.xml
@@ -70,6 +70,12 @@ <if test="companyId != null"> AND a.company_id =#{companyId} </if> <if test="params.warehouseId !=null"> and a.warehouse_id =#{params.warehouseId} </if> <if test="params.cupboardId !=null"> and a.cupboard_id = #{params.cupboardId} </if> </where> order by a.id desc </select> hazmat-system/src/main/resources/mapper/system/HzSecientificMapper.xml
@@ -22,7 +22,7 @@ LEFT JOIN hz_peculiarity hp ON hs.peculiarity_number = hp.peculiarity_number <where> <if test="secientificName!=null and secientificName!=''"> and secientific_name =#{secientificName} and hs.secientific_name like concat('%',#{secientificName},'%') </if> and hs.del_flag = 0 </where>