package com.product.data.center.service;
|
|
|
import java.io.IOException;
|
import java.net.URLEncoder;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.core.util.NumberUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.data.center.config.CmnConst;
|
import com.product.data.center.entity.TraceSNItemEntity;
|
import com.product.data.center.utils.QuerySqlParseUtil;
|
import com.product.util.BaseUtil;
|
|
import cn.hutool.json.JSONObject;
|
|
|
/**
|
* SN追溯查询
|
*
|
* @author du
|
*/
|
@Service
|
public class TraceToSourceService extends AbstractBaseService {
|
|
@Autowired
|
BaseDao baseDao;
|
|
/**
|
* 物料SN反追溯历史查询
|
*
|
* @param querySNSN 主件SN
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public JSONObject antitraceReport(String querySN, String isBakTable) {
|
JSONObject jsonInfo = new JSONObject();
|
|
//查询关键件，获取机号SN
|
String currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp}{}{WHERE pk_keyp_sn = '" + querySN + "' }=]";
|
|
String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), currentSql);
|
FieldSetEntity fse = baseDao.getFieldSetBySQL(parseSql, new Object[]{}, false);
|
if (fse != null) {
|
jsonInfo = traceReport(fse.getString(CmnConst.PK_PRODUCT_SN), isBakTable);
|
}
|
return jsonInfo;
|
}
|
|
/**
|
* SN追溯历史查询
|
*
|
* @param 机器SN 机器序列号
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public JSONObject traceReport(String querySN, String isBakTable) {
|
JSONObject jsonInfo = new JSONObject();
|
|
jsonInfo.set("orderInfo", BaseUtil.fieldSetEntityToJson(getManufacturingOrderInformation(querySN, isBakTable)));
|
jsonInfo.set("materialInfo", BaseUtil.dataTableEntityToJson(getWorkCenterOperationInfomation(querySN, isBakTable)));
|
jsonInfo.set("crossInfo", BaseUtil.dataTableEntityToJson(getHistoricalStationInformation(querySN, isBakTable)));
|
jsonInfo.set("repairInfo", BaseUtil.dataTableEntityToJson(getHistoricalMaintenanceRecords(querySN)));
|
jsonInfo.set("querySN", querySN);
|
|
return jsonInfo;
|
}
|
|
/**
|
* 获取制令单信息
|
*
|
* @param querySN
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public FieldSetEntity getManufacturingOrderInformation(String querySN, String isBakTable) {
|
//订单信息
|
StringBuilder sb1 = new StringBuilder(1024);
|
sb1.append("SELECT ")
|
.append(" t.mo_number,t.model_name,t.area_name,t.container_sn, ")
|
.append(" t.next_station,t.station_name,t.display_group, ")
|
.append(" t.old_begin_time,t.old_end_time,w.pi_sn, ")
|
.append(" DATE_FORMAT(p.mo_start_date, '%Y-%m-%d %H:%i:%s') mo_start_date, ")
|
.append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') in_station_time, ")
|
.append(" (CASE t.group_name WHEN 'N/A' THEN t.next_station ELSE t.group_name END) group_name, ")
|
.append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
|
.append("")
|
.append(" FROM [=da_t_pm_mo_base=] p,[={");
|
sb1.append("da_t_wip_tracking");
|
|
sb1.append("}{}{WHERE serial_number = '")
|
.append(querySN)
|
.append("'}=] t ")
|
.append(" LEFT JOIN [={da_t_wip_packaging_infor}{}{WHERE pi_product_sn='")
|
.append(querySN)
|
.append("'}=] w ON t.serial_number = w.pi_product_sn ")
|
.append(" WHERE p.mo_number = t.mo_number AND t.serial_number = ?");
|
|
//报表解析SQL，生成WITH
|
String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb1.toString());
|
FieldSetEntity orderInfo = baseDao.getFieldSetEntityBySQL(finalSql, new Object[]{querySN}, false);
|
if (orderInfo != null) {
|
orderInfo.setValue("outPack", this.getOutermostContainer(querySN));
|
}
|
return orderInfo;
|
}
|
|
|
/**
|
* 获取最外层容器SN
|
*
|
* @param querySN
|
* @return
|
*/
|
public String getOutermostContainer(String querySN) {
|
//单独查询包装信息
|
StringBuilder sb = new StringBuilder();
|
sb.append("SELECT pi_sn,pi_pack_level,pi_child_sn")
|
.append(" FROM [={da_t_wip_packaging_infor}{pi_sn,ROUND(pi_pack_level)pi_pack_level,pi_child_sn}{WHERE pi_product_sn='")
|
.append(querySN)
|
.append("'}=] ");
|
// .append(" WHERE pi_product_sn=? ");
|
String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb.toString());
|
DataTableEntity dtPackingInfo = baseDao.listTable(finalSql, new Object[]{});
|
if (!BaseUtil.dataTableIsEmpty(dtPackingInfo)) {
|
|
//目标SN集合（索引等于深度，索引对应值等于同深度的任意一个SN，即索引最大的SN为目标SN）
|
List<Map<String, FieldSetEntity>> listLevel = new ArrayList<>();
|
for (int i = 0; i < dtPackingInfo.getRows(); i++) {
|
FieldSetEntity fseBaseData = dtPackingInfo.getFieldSetEntity(i);
|
//cheng 2022年12月29日14:16:52
|
int level = NumberUtil.parseInt(fseBaseData.getString("pi_pack_level"));
|
String ID = fseBaseData.getString("pi_sn");
|
while (listLevel.size() < level) {
|
listLevel.add(new HashMap<>());
|
}
|
listLevel.get(level - 1).put(ID, fseBaseData);
|
}
|
|
//双循环遍历去掉非上下级的包装数据
|
for (int i = 1; i < listLevel.size(); i++) {
|
Map<String, FieldSetEntity> mapPreCommonLevel = listLevel.get(i - 1);
|
Map<String, FieldSetEntity> mapCommonLevel = listLevel.get(i);
|
for (Map.Entry<String, FieldSetEntity> entry : mapCommonLevel.entrySet()) {
|
String key = entry.getKey();
|
FieldSetEntity val = entry.getValue();
|
if (!mapPreCommonLevel.containsKey(val.getString("pi_child_sn"))) {
|
mapCommonLevel.remove(key);
|
}
|
}
|
if (mapCommonLevel.isEmpty()) {
|
listLevel.remove(listLevel.subList(i, listLevel.size() - 1));
|
}
|
}
|
|
//返回最外层包装条码
|
for (Map.Entry<String, FieldSetEntity> entry : listLevel.get(listLevel.size() - 1).entrySet()) {
|
String key = entry.getKey();
|
if (key != null) {
|
return key;
|
}
|
}
|
}
|
return null;
|
}
|
|
|
/**
|
* 获取工作中心物料作业信息
|
*
|
* @param querySN
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public DataTableEntity getWorkCenterOperationInfomation(String querySN, String isBakTable) {
|
//物料作业信息
|
StringBuilder sb2 = new StringBuilder(1024);
|
sb2.append("SELECT ")
|
.append(" DISTINCT pk_station_name AS stationName, ")
|
.append(" l.pk_product_lot AS lot_number,l.pk_item_code AS keyPartNo, ")
|
.append(" c.co_item_name AS assistantToolName,c.co_item_spec AS itemSpec, ")
|
.append(" pk_keyp_sn AS itemSN,pk_keyp_sn_qty AS itemCount, ")
|
.append(" DATE_FORMAT(pk_loadtime,'%Y-%m-%d %H:%i:%s') AS loadTime, ")
|
.append(" (SELECT d.emp_name FROM da_t_co_emp_desc d WHERE d.emp_no=l.pk_empno)empName, ")
|
.append(" (CASE 0 WHEN 0 THEN '上料' WHEN '1' THEN '下料' WHEN '2' THEN '换料' WHEN '3' THEN '异常换料' ELSE '' END) actionType ")
|
.append(" FROM [={");
|
sb2.append("da_t_wip_product_keyp");
|
sb2.append("}{}{ WHERE pk_replace_flag='N' AND pk_product_sn='")
|
.append(querySN)
|
.append("'}=] l ")
|
.append(" LEFT JOIN [=da_t_co_item=] c ON c.co_item_code=l.pk_item_code ")
|
.append(" WHERE l.pk_replace_flag='N' AND l.pk_product_sn=? ")
|
.append(" ORDER BY loadtime ");
|
//报表解析SQL，生成WITH
|
String finalSql2 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb2.toString());
|
DataTableEntity materialInfo = baseDao.listTable(finalSql2, new Object[]{querySN});
|
return materialInfo;
|
}
|
|
/**
|
* 获取历史过站信息
|
*
|
* @param querySN
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public DataTableEntity getHistoricalStationInformation(String querySN, String isBakTable) {
|
//过站信息
|
StringBuilder sb3 = new StringBuilder();
|
sb3.append(" SELECT ")
|
.append(" t.mo_number,t.lot_number,t.model_name,t.area_name, ")
|
.append(" t.group_name,t.next_station,t.station_name, ")
|
.append(" t.customer_no,t.emp_no,c.emp_name,t.item_count,t.serial_number, ")
|
.append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') AS in_station_time, ")
|
.append(" (CASE t.error_flag WHEN '0' THEN '正常' WHEN '1' THEN '不良' WHEN '3' THEN '报废' ELSE 'N/A' END) error_flag ")
|
.append(" FROM [={");
|
sb3.append("da_t_wip_detail");
|
sb3.append("}{}{")
|
.append(" WHERE serial_number = ( ")
|
.append(" SELECT '")
|
.append(querySN)
|
.append("' old_sn ")
|
.append(" UNION ")
|
.append(" SELECT DISTINCT old_sn ")
|
.append(" FROM da_t_co_sn_relation ")
|
.append(" WHERE new_sn = '")
|
.append(querySN)
|
.append("' ")
|
.append(" ) ")
|
.append("}=] t ")
|
.append(" LEFT JOIN da_t_co_emp_desc c ON t.emp_no = c.emp_no ")
|
.append(" ORDER BY t.in_station_time ");
|
|
//报表解析SQL，生成WITH
|
String finalSql3 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb3.toString());
|
DataTableEntity crossInfo = baseDao.listTable(finalSql3, new Object[]{});
|
return crossInfo;
|
}
|
|
/**
|
* 获取历史维修记录
|
*
|
* @param querySN
|
* @return
|
*/
|
public DataTableEntity getHistoricalMaintenanceRecords(String querySN) {
|
//维修记录
|
StringBuilder sb4 = new StringBuilder();
|
sb4.append(" SELECT ")
|
.append(" c.error_desc,d.reason_desc,m.repair_moth, ")
|
.append(" e.test_code,e.ng_count,e.test_point, ")
|
.append(" r.reason_code,r.duty_type,r.repair_station, ")
|
.append(" r.repair_group,r.error_item,r.item_desc, ")
|
.append(" r.date_code,r.repair_desc,r.repairer, ")
|
.append(" r.repair_time,s.emp_name,t.duty_desc ")
|
.append(" FROM [=da_t_wip_repair=] r ")
|
.append(" LEFT JOIN [={da_t_wip_error}{test_code,ng_count,test_point,ng_record_id,serial_number}{WHERE serial_number='")
|
.append(querySN)
|
.append("'}=] e ON e.ng_record_id = r.ng_record_id ")
|
.append(" LEFT JOIN da_t_co_error_code c ON e.test_code = c.error_code ")
|
.append(" LEFT JOIN da_t_co_repair_moth m ON r.repair_month_code = m.repair_code ")
|
.append(" LEFT JOIN da_t_co_reason_code d ON r.reason_code = d.reason_code ")
|
.append(" LEFT JOIN da_t_co_duty t ON r.duty_type = t.duty_type ")
|
.append(" LEFT JOIN da_t_co_emp_desc s ON r.repairer = s.emp_no ")
|
.append(" WHERE e.serial_number= ?");
|
|
//报表解析SQL，生成WITH
|
String finalSql4 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb4.toString());
|
DataTableEntity repairInfo = baseDao.listTable(finalSql4, new Object[]{querySN});
|
return repairInfo;
|
}
|
|
/**
|
* 生成EXCEL数据
|
*
|
* @param request
|
* @param response
|
* @throws IOException
|
*/
|
public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
|
//获取机号查询信息
|
FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
|
DataTableEntity workCenterOperationInfomationSheet = this.getWorkCenterOperationInfomation(fse.getString(CmnConst.SERIAL_NUMBER), fse.getString(CmnConst.IS_BAK_TABLE));
|
|
//初始EXCEL信息
|
response.setContentType("multipart/form-data");
|
response.setCharacterEncoding("utf-8");
|
//这里URLEncoder.encode可以防止中文乱码
|
String fileName = URLEncoder.encode("SN历史追溯" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
|
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
|
|
List<TraceSNItemEntity> traceSNItemEntityList = new ArrayList<>();
|
for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
|
FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
|
Map<Object, Object> values = fieldSetEntity.getValues();
|
JSONObject jsonObject = new JSONObject((Map) values);
|
TraceSNItemEntity manufacturingOrderEntity = jsonObject.toBean(TraceSNItemEntity.class);
|
traceSNItemEntityList.add(manufacturingOrderEntity);
|
}
|
ServletOutputStream outputStream = response.getOutputStream();
|
ExcelWriter excelWriter = EasyExcel.write(outputStream, TraceSNItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
|
try {
|
WriteSheet writeSheet = EasyExcel.writerSheet(1, "物料作业信息").build();
|
excelWriter.write(traceSNItemEntityList, writeSheet);
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
excelWriter.finish();
|
}
|
}
|
}
|
