package com.product.data.center.service;
|
|
import java.io.IOException;
|
import java.net.URLEncoder;
|
import java.text.DateFormat;
|
import java.util.*;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
|
import cn.hutool.core.date.DateTime;
|
import com.product.core.entity.SQLEntity;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
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.SNAssociatedItemEntity;
|
import com.product.data.center.utils.QuerySqlParseUtil;
|
import com.product.util.BaseUtil;
|
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.json.JSONObject;
|
|
/**
|
* SN关联物料
|
*
|
* @author 86151
|
*/
|
@Component
|
public class SNAssociatedItemService extends AbstractBaseService {
|
|
|
@Autowired
|
BaseDao baseDao;
|
|
/**
|
* 过站信息
|
*
|
* @param fse
|
* @return
|
*/
|
public JSONObject listSNItemInfo(FieldSetEntity fse) {
|
|
JSONObject jsonReturn = new JSONObject();
|
|
String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE); //是否切表
|
boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//是否近期数据
|
Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //页数大小
|
Integer cpage = fse.getInteger(CmnConst.CPAGE); //目标页
|
|
//创建tracking表SQL，并获取过滤的数据
|
String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
|
DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
|
if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
|
|
//获取总条数
|
// String totalTrackingSql="SELECT COUNT(*)count_total FROM ("+parseTrackingSql+")t";
|
// FieldSetEntity fseTotalTracking=baseDao.getFieldSetBySQL(totalTrackingSql, new Object[] {}, false);
|
// jsonReturn.set("total", fseTotalTracking.getInteger("count_total"));
|
int totalTrackingSql = dtTracking.getSqle().getTotalCount();
|
jsonReturn.set("total", totalTrackingSql);
|
|
//通过tracking过滤keyp
|
//Method1:使用OR查询
|
Object[] serialNumbers = dtTracking.getUuids();
|
String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
|
DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});
|
|
if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
|
//将tracking表的数据转为(key，value)形式存储
|
JSONObject json = convertDataTableToJson(dtTracking);
|
addKeypInfo(json, dtKeyp);
|
jsonReturn.set("keyp_info", BaseUtil.dataTableEntityToJson(dtKeyp));
|
return jsonReturn;
|
}
|
|
//Method2:使用左关联查询（考虑到SQL的长度标准，建议不使用）
|
|
} else {
|
jsonReturn.set("total", 0);
|
}
|
return null;
|
}
|
|
|
/**
|
* 根据tacking表的SN解析keyp表
|
*
|
* @param serialNumbers
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public String createKeypSql(Object[] serialNumbers, String isBakTable, boolean isRecentData) {
|
String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);
|
|
StringBuilder keypSql = new StringBuilder(2056);
|
keypSql.append(" SELECT ")
|
.append(" t.pk_product_sn AS pk_product_sn,b1.zj,c.dj,b.ysj, ")
|
.append(" d1.ysjmn,d.dk,e1.xsq,x1.ykq,c1.xfzj, ")
|
.append(" REGEXP_SUBSTR(d.dk, '[^|]+', 1, 5) zbrj, ")
|
.append(" e.mk,f.fjd,h.ccpg ")
|
.append(" FROM(SELECT DISTINCT pk_product_sn FROM [={");
|
keypSql.append("da_t_wip_product_keyp");
|
keypSql.append("}{pk_product_sn,pk_keyp_sn,pk_item_source_type}{")
|
.append(" WHERE instr( pk_mo,'DK' )=0 AND ")
|
.append(keypFilter)
|
.append("}=]) t ")
|
.append(" LEFT JOIN(SELECT u.pk_product_sn, u.pk_keyp_sn ysj FROM ").append("da_t_wip_product_keyp").append(" u WHERE u.pk_item_source_type= 'YSJ' AND u.pk_keyp_sn NOT LIKE 'R_%') b ON t.pk_product_sn = b.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT v.pk_product_sn, v.pk_keyp_sn dj FROM ").append("da_t_wip_product_keyp").append(" v WHERE v.pk_item_source_type= 'DJ' AND v.pk_keyp_sn NOT LIKE 'R_%') c ON t.pk_product_sn = c.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT w.pk_product_sn, w.pk_keyp_sn dk FROM ").append("da_t_wip_product_keyp").append(" w WHERE w.pk_item_source_type= 'DK' AND w.pk_keyp_sn NOT LIKE 'R_%') d ON t.pk_product_sn = d.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT x.pk_product_sn, x.pk_keyp_sn mk FROM ").append("da_t_wip_product_keyp").append(" x WHERE x.pk_item_source_type= 'MK' AND x.pk_keyp_sn NOT LIKE 'R_%') e ON t.pk_product_sn = e.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT y.pk_product_sn, y.pk_keyp_sn fjd FROM ").append("da_t_wip_product_keyp").append(" y WHERE y.pk_item_source_type= 'FJD' AND y.pk_keyp_sn NOT LIKE 'R_%') f ON t.pk_product_sn = f.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT z.pk_product_sn, z.pk_keyp_sn zj FROM ").append("da_t_wip_product_keyp").append(" z WHERE (z.pk_item_source_type= 'ZJNJMN' or z.pk_item_source_type= 'ZJWJMN') AND z.pk_keyp_sn NOT LIKE 'R_%') b1 ON t.pk_product_sn = b1.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT v1.pk_product_sn, v1.pk_keyp_sn ysjmn FROM ").append("da_t_wip_product_keyp").append(" v1 WHERE v1.pk_item_source_type= 'YSJMN' AND v1.pk_keyp_sn NOT LIKE 'R_%') d1 ON t.pk_product_sn = d1.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT w1.pk_product_sn, w1.pk_keyp_sn xsq FROM ").append("da_t_wip_product_keyp").append(" w1 WHERE w1.pk_item_source_type= 'XSQ' AND w1.pk_keyp_sn NOT LIKE 'R_%') e1 ON t.pk_product_sn = e1.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn ykq FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'YKQ' AND x1.pk_keyp_sn NOT LIKE 'R_%') x1 ON t.pk_product_sn = x1.pk_product_sn ")
|
.append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn xfzj FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'XFZJ' AND x1.pk_keyp_sn NOT LIKE 'R_%') c1 ON t.pk_product_sn = c1.pk_product_sn ")
|
.append(" LEFT JOIN (SELECT x1.pk_product_sn, x1.pk_keyp_sn ccpg FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type = 'CCPG' AND x1.pk_keyp_sn NOT LIKE 'R_%') h ON t.pk_product_sn = h.pk_product_sn ");
|
|
String parseKeypSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), keypSql.toString(), isRecentData ? 2 : 0, false);
|
return parseKeypSql;
|
}
|
|
/**
|
* 根据过滤条件解析tacking的SQL
|
*
|
* @param fse
|
* @param isBakTable 是否BAK表
|
* @return
|
*/
|
public String createTrackingSql(FieldSetEntity fse, String isBakTable, boolean isRecentData) {
|
|
StringBuilder trackingSql = new StringBuilder(256);
|
trackingSql.append("SELECT a.mo_number AS moNumber,a.in_line_time AS inLineTime,a.area_name AS areaName,a.serial_number AS uuid,a.technicsid ")
|
.append(" FROM [={da_t_wip_tracking}");
|
trackingSql.append("{mo_number,in_line_time,area_name,serial_number,technicsid}{")
|
.append(parseCondition(fse, 0))
|
.append("}=] a")
|
.append(" JOIN da_t_smo_technics h ON a.technicsid =h.technicsid ")
|
.append(parseCondition(fse, 1));
|
|
//解析SQL
|
String parseTrackingSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), trackingSql.toString(), isRecentData ? 2 : 0, false);
|
return parseTrackingSql;
|
}
|
|
/**
|
* tracking信息转化
|
*
|
* @param dtTracking
|
* @return
|
*/
|
public JSONObject convertDataTableToJson(DataTableEntity dtTracking) {
|
JSONObject json = new JSONObject();
|
for (int i = 0; i < dtTracking.getRows(); i++) {
|
String serialNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.UUID);
|
String areaName = dtTracking.getFieldSetEntity(i).getString(CmnConst.AREANAME);
|
String moNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.MONUMBER);
|
String inLineTime = dtTracking.getFieldSetEntity(i).getString(CmnConst.INLINETIME);
|
List<String> trackInfo = new ArrayList<>();
|
trackInfo.add(areaName);
|
trackInfo.add(moNumber);
|
trackInfo.add(inLineTime);
|
json.set(serialNumber, trackInfo);
|
}
|
return json;
|
}
|
|
/**
|
* 将tracking信息添加至keyp
|
*
|
* @param json
|
* @param dtKeyp
|
*/
|
public void addKeypInfo(JSONObject json, DataTableEntity dtKeyp) {
|
for (int i = 0; i < dtKeyp.getRows(); i++) {
|
FieldSetEntity fseKeyp = dtKeyp.getFieldSetEntity(i);
|
String pkProductSN = fseKeyp.getString("pk_product_sn");
|
fseKeyp.setValue(CmnConst.PKPRODUCTSN, pkProductSN);
|
@SuppressWarnings("unchecked")
|
List<String> trackInfo = (List<String>) json.get(pkProductSN);
|
if (trackInfo.size() == 3) {
|
fseKeyp.setValue("area_name", trackInfo.get(0));
|
fseKeyp.setValue("mo_number", trackInfo.get(1));
|
fseKeyp.setValue("in_line_time", trackInfo.get(2));
|
}
|
}
|
}
|
|
/**
|
* 解析报表过滤条件
|
*
|
* @param fse
|
* @return
|
*/
|
public String parseCondition(FieldSetEntity fse, int type) {
|
|
StringBuilder sqlFilter = new StringBuilder(128);
|
|
if (type == 1) {
|
String poka_yoke = fse.getString(CmnConst.POKA_YOKE); //防呆规则（小米，非小米）
|
if (!BaseUtil.strIsNull(poka_yoke)) {
|
if ("0".equals(poka_yoke)) {
|
sqlFilter.append(" WHERE h.poka_yoke=1 ");
|
} else {
|
sqlFilter.append(" WHERE h.poka_yoke!=1 ");
|
}
|
}
|
} else {
|
sqlFilter.append(" WHERE scrap_flag IS NULL ");
|
String moNumber = fse.getString(CmnConst.MO_NUMBER); //工单号
|
String factory = fse.getString(CmnConst.FACTORY); //厂别
|
String areasn = fse.getString(CmnConst.AREA_SN); //线体
|
Date startTime = fse.getDate(CmnConst.START_TIME); //开始时间
|
Date endTime = fse.getDate(CmnConst.END_TIME); //结束时间
|
|
if (!BaseUtil.strIsNull(moNumber)) {
|
sqlFilter.append(" AND mo_number = '")
|
.append(moNumber)
|
.append("'");
|
}
|
|
// if (!BaseUtil.strIsNull(factory)) {
|
// sqlFilter.append("");
|
// }
|
|
if (!BaseUtil.strIsNull(areasn)) {
|
String[] areasns = areasn.split(",");
|
if (areasns.length > 1) {
|
sqlFilter.append(" AND ");
|
sqlFilter.append(BaseUtil.buildQuestionMarkFilter(CmnConst.AREA_SN, areasns, true));
|
} else {
|
sqlFilter.append(" AND area_sn = '")
|
.append(areasn)
|
.append("'");
|
}
|
}
|
|
if (startTime != null) {
|
sqlFilter.append(" AND in_station_time >=STR_TO_DATE('")
|
.append(DateUtil.format(startTime, "yyyy-MM-dd HH:mm:ss"))
|
.append("','%Y-%m-%d %H:%i:%s')");
|
}
|
|
if (endTime != null) {
|
//判断结束时间是否为当天的开始时间 00:00:00
|
DateTime dateTime = new DateTime(endTime);
|
Calendar calendar = dateTime.toCalendar();
|
if (calendar.get(Calendar.HOUR_OF_DAY) == 0 && calendar.get(Calendar.MINUTE) == 0 && calendar.get(Calendar.SECOND) == 0) {
|
//设置为23:59:59
|
calendar.set(Calendar.HOUR_OF_DAY, 23);
|
calendar.set(Calendar.MINUTE, 59);
|
calendar.set(Calendar.SECOND, 59);
|
endTime = calendar.getTime();
|
}
|
sqlFilter.append(" AND in_station_time <=STR_TO_DATE('")
|
.append(DateUtil.format(endTime, "yyyy-MM-dd HH:mm:ss"))
|
.append("','%Y-%m-%d %H:%i:%s')");
|
}
|
}
|
|
return sqlFilter.toString();
|
}
|
|
/**
|
* 生成EXCEL信息
|
*
|
* @param request
|
* @param response
|
* @throws IOException
|
*/
|
public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
|
//获取机号查询信息
|
FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
|
Boolean isAll = fse.getBoolean("is_all");
|
if (isAll) {
|
fse.setValue(CmnConst.CPAGE, 1);
|
fse.setValue(CmnConst.PAGESIZE, 999);
|
}
|
//初始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);
|
ServletOutputStream outputStream = response.getOutputStream();
|
ExcelWriter excelWriter = EasyExcel.write(outputStream, SNAssociatedItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
|
|
DataTableEntity workCenterOperationInfomationSheet;
|
try {
|
do {
|
workCenterOperationInfomationSheet = this.dtSNItemInfo(fse);
|
if (DataTableEntity.isEmpty(workCenterOperationInfomationSheet)) {
|
continue;
|
}
|
List<SNAssociatedItemEntity> 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);
|
SNAssociatedItemEntity manufacturingOrderEntity = jsonObject.toBean(SNAssociatedItemEntity.class);
|
traceSNItemEntityList.add(manufacturingOrderEntity);
|
}
|
|
WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build();
|
excelWriter.write(traceSNItemEntityList, writeSheet);
|
|
|
fse.setValue(CmnConst.CPAGE, fse.getInteger(CmnConst.CPAGE) + 1);
|
} while (
|
isAll && workCenterOperationInfomationSheet != null && workCenterOperationInfomationSheet.getSqle().isCountLoad());
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
excelWriter.finish();
|
}
|
}
|
|
/**
|
* 过站信息
|
*
|
* @param fse
|
* @return
|
*/
|
public DataTableEntity dtSNItemInfo(FieldSetEntity fse) {
|
|
String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);
|
boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//是否近期数据
|
Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //页数大小
|
Integer cpage = fse.getInteger(CmnConst.CPAGE); //目标页
|
SQLEntity sqle = new SQLEntity();
|
sqle.setCountLoad(false);
|
//创建tracking表SQL，并获取过滤的数据
|
String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
|
DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
|
if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
|
if (dtTracking.getRows() == pageSize) {
|
sqle.setCountLoad(true);
|
}
|
//获取总条数
|
String totalTrackingSql = "SELECT COUNT(*)count_total FROM (" + parseTrackingSql + ")t";
|
// FieldSetEntity fseTotalTracking = baseDao.getFieldSetBySQL(totalTrackingSql, new Object[]{}, false);
|
|
//通过tracking过滤keyp
|
//Method1:使用OR查询
|
Object[] serialNumbers = dtTracking.getUuids();
|
String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
|
DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});
|
|
if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
|
//将tracking表的数据转为(key，value)形式存储
|
JSONObject json = convertDataTableToJson(dtTracking);
|
addKeypInfo(json, dtKeyp);
|
dtKeyp.setSqle(sqle);
|
return dtKeyp;
|
}
|
}
|
DataTableEntity dataTableEntity = new DataTableEntity();
|
dataTableEntity.setSqle(sqle);
|
return dataTableEntity;
|
}
|
}
|
