mescloud/src/main/java/com/jeesite/modules/mes/service/MesScrapService.java

package com.jeesite.modules.mes.service;

import com.jeesite.common.entity.Page;
import com.jeesite.common.lang.StringUtils;
import com.jeesite.common.mybatis.mapper.query.QueryType;
import com.jeesite.common.service.CrudService;
import com.jeesite.common.web.CookieUtils;
import com.jeesite.modules.file.utils.FileUploadUtils;
import com.jeesite.modules.mes.dao.MesScrapDao;
import com.jeesite.modules.mes.entity.MesProductBfrk;
import com.jeesite.modules.mes.entity.MesScrap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * 报废记录Service
 * @author mes
 * @version 2023-11-27
 */
@Service
public class MesScrapService extends CrudService<MesScrapDao, MesScrap> {

	/** 生产报废数据库模板列数 */
	private static final int COL_COUNT = 53;
	/** 压铸车间缺陷列 */
	private static final int[] DEFECT_COLS_TYPE1 = {9, 10, 11, 12, 13, 14, 15, 16, 17, 18};
	/** 精加工缺陷列 */
	private static final int[] DEFECT_COLS_TYPE2 = {21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38};
	/** 装配全检包装缺陷列 */
	private static final int[] DEFECT_COLS_TYPE3 = {41, 42, 43, 44, 45, 46};

	@Autowired
	private MesProductBfrkService mesProductBfrkService;

	/**
	 * 获取单条数据
	 * @param mesScrap
	 * @return
	 */
	@Override
	public MesScrap get(MesScrap mesScrap) {
		return super.get(mesScrap);
	}

	/**
	 * 查询分页数据
	 * @param mesScrap 查询条件
	 * @param mesScrap.page 分页对象
	 * @return
	 */
	@Override
	public Page<MesScrap> findPage(MesScrap mesScrap) {
		return super.findPage(mesScrap);
	}

	/**
	 * 查询列表数据
	 * @param mesScrap
	 * @return
	 */
	@Override
	public List<MesScrap> findList(MesScrap mesScrap) {
		return super.findList(mesScrap);
	}

	/**
	 * 保存数据（插入或更新）
	 * @param mesScrap
	 */
	@Override
	@Transactional
	public void save(MesScrap mesScrap) {
		super.save(mesScrap);
		// 保存上传图片
		FileUploadUtils.saveFileUpload(mesScrap, mesScrap.getId(), "mesScrap_image");
		// 保存上传附件
		FileUploadUtils.saveFileUpload(mesScrap, mesScrap.getId(), "mesScrap_file");
	}

	/**
	 * 更新状态
	 * @param mesScrap
	 */
	@Override
	@Transactional
	public void updateStatus(MesScrap mesScrap) {
		super.updateStatus(mesScrap);
	}

	/**
	 * 删除数据
	 * @param mesScrap
	 */
	@Override
	@Transactional
	public void delete(MesScrap mesScrap) {
		super.delete(mesScrap);
	}

	/**
	 * 统计指定时间段内的报废记录数量
	 */
	public Long countByDateRange(String sdate, String edate){
		MesScrap q = new MesScrap();
		q.getSqlMap().getWhere()
				.and("create_date", QueryType.GTE, sdate)
				.and("create_date", QueryType.LT, edate);
		return super.findCount(q);
	}

	/**
	 * 按「生产报废数据库」模板导出报废统计（一行汇总各报废原因数量）
	 */
	public void exportScrapDatabase(List<MesScrap> list, HttpServletResponse response, String fileName) {
		try (InputStream in = new ClassPathResource("templates/mes/scrap_export.xls").getInputStream();
			 Workbook workbook = WorkbookFactory.create(in)) {
			Sheet sheet = workbook.getSheetAt(0);
			Row headerRow = sheet.getRow(1);
			String[] headers = new String[COL_COUNT];
			for (int c = 0; c < COL_COUNT; c++) {
				headers[c] = getCellString(headerRow, c);
			}

			// 清除模板示例数据，保留前两行表头
			for (int r = sheet.getLastRowNum(); r >= 2; r--) {
				Row row = sheet.getRow(r);
				if (row != null) {
					sheet.removeRow(row);
				}
			}

			// 汇总到一行：各报废原因列累计数量
			double[] values = new double[COL_COUNT];
			if (list != null) {
				for (MesScrap scrap : list) {
					int col = matchDefectColumn(scrap.getProductCate(), scrap.getReason(), headers);
					if (col >= 0) {
						values[col] += 1;
					}
				}
			}

			// 各车间报废数
			values[19] = sumRange(values, 9, 18);
			values[39] = sumRange(values, 21, 38);
			values[47] = sumRange(values, 41, 46);
			// 料工报废：工=精加工+装配，料=压铸
			values[49] = values[39] + values[47];
			values[51] = values[19];

			Row row = sheet.createRow(2);
			setCellString(row, 0, "");
			setCellString(row, 1, "合计");
			setCellString(row, 2, "/");
			// 成品/毛坯入库与报废、报废率：来自 MesProductBfrk 汇总，不按报废原因计数
			fillBfrkSummary(row);
			for (int c = 3; c < COL_COUNT; c++) {
				if (c >= 3 && c <= 8) {
					continue;
				}
				setCellNumber(row, c, values[c]);
			}

			response.reset();
			response.setContentType("application/vnd.ms-excel; charset=utf-8");
			String encodedName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replace("+", "%20");
			response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedName);
			CookieUtils.setCookie(response, "fileDownload", "true");
			try (OutputStream out = response.getOutputStream()) {
				workbook.write(out);
				out.flush();
			}
		} catch (Exception e) {
			throw new RuntimeException("导出生产报废数据库失败：" + e.getMessage(), e);
		}
	}

	/**
	 * 从 MesProductBfrk 汇总并填入：
	 * 成品入库数、成品报废总数、成品总报废率、毛坯入库数、毛坯报废总数、毛坯总报废率
	 * 报废率 = (报废总数 / 入库总数) * 100%，保留两位小数
	 */
	private void fillBfrkSummary(Row row) {
		double cpQty = 0;      // 成品入库数
		double cpBfQty = 0;    // 成品报废总数
		double bcpQty = 0;     // 毛坯(半成品)入库数
		double bcpBfQty = 0;   // 毛坯(半成品)报废总数
		List<MesProductBfrk> bfrkList = mesProductBfrkService.findList(new MesProductBfrk());
		if (bfrkList != null) {
			for (MesProductBfrk item : bfrkList) {
				if (item == null) {
					continue;
				}
				cpQty += parseQty(item.getCpQty());
				cpBfQty += parseQty(item.getCpBfQty());
				bcpQty += parseQty(item.getBcpQty());
				bcpBfQty += parseQty(item.getBcpBfQty());
			}
		}
		setCellNumber(row, 3, cpQty);
		setCellNumber(row, 4, cpBfQty);
		setCellString(row, 5, calcScrapRate(cpBfQty, cpQty));
		setCellNumber(row, 6, bcpQty);
		setCellNumber(row, 7, bcpBfQty);
		setCellString(row, 8, calcScrapRate(bcpBfQty, bcpQty));
	}

	private double parseQty(String qty) {
		if (StringUtils.isBlank(qty)) {
			return 0;
		}
		try {
			return Double.parseDouble(qty.trim());
		} catch (NumberFormatException e) {
			return 0;
		}
	}

	/**
	 * (报废数 / 入库数) * 100%，保留两位小数；入库为0时返回空
	 */
	private String calcScrapRate(double scrapQty, double inboundQty) {
		if (inboundQty <= 0) {
			return "";
		}
		BigDecimal rate = BigDecimal.valueOf(scrapQty)
				.multiply(BigDecimal.valueOf(100))
				.divide(BigDecimal.valueOf(inboundQty), 2, RoundingMode.HALF_UP);
		return rate.toPlainString() + "%";
	}

	/**
	 * 按报废类型 + 报废原因匹配模板缺陷列
	 * productCate: 1=压铸车间, 2=精加工, 3=装配全检包装
	 */
	private int matchDefectColumn(String productCate, String reason, String[] headers) {
		int[] cols = defectColsByType(productCate);
		int defaultOther = defaultOtherCol(productCate);
		if (StringUtils.isBlank(reason)) {
			return defaultOther;
		}
		String target = reason.trim();
		for (int col : cols) {
			String header = headers[col];
			if (StringUtils.isNotBlank(header) && header.equals(target)) {
				return col;
			}
		}
		for (int col : cols) {
			String header = headers[col];
			if (StringUtils.isNotBlank(header) && (header.contains(target) || target.contains(header))) {
				return col;
			}
		}
		return defaultOther;
	}

	private int[] defectColsByType(String productCate) {
		if ("1".equals(productCate)) {
			return DEFECT_COLS_TYPE1;
		}
		if ("3".equals(productCate)) {
			return DEFECT_COLS_TYPE3;
		}
		return DEFECT_COLS_TYPE2;
	}

	private int defaultOtherCol(String productCate) {
		if ("1".equals(productCate)) {
			return 18;
		}
		if ("3".equals(productCate)) {
			return 46;
		}
		return 38;
	}

	private double sumRange(double[] values, int from, int to) {
		double sum = 0;
		for (int i = from; i <= to; i++) {
			sum += values[i];
		}
		return sum;
	}

	private String getCellString(Row row, int col) {
		if (row == null) {
			return "";
		}
		Cell cell = row.getCell(col);
		if (cell == null) {
			return "";
		}
		return StringUtils.trimToEmpty(cell.toString());
	}

	private void setCellString(Row row, int col, String value) {
		Cell cell = row.getCell(col);
		if (cell == null) {
			cell = row.createCell(col);
		}
		cell.setCellValue(value == null ? "" : value);
	}

	private void setCellNumber(Row row, int col, double value) {
		Cell cell = row.getCell(col);
		if (cell == null) {
			cell = row.createCell(col);
		}
		cell.setCellValue(value);
	}

}