package com.product.data.center.service;
|
|
import cn.hutool.core.date.DateField;
|
import cn.hutool.core.date.DateTime;
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.core.util.NumberUtil;
|
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.alibaba.fastjson.JSONObject;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.data.center.config.ErrorCode;
|
import com.product.data.center.entity.ManufacturingOrderEntity;
|
import com.product.data.center.entity.ProductionDetailEntity;
|
import com.product.data.center.utils.QuerySqlParseUtil;
|
import com.product.datasource.dao.Dao;
|
import com.product.datasource.entity.DataBaseEntity;
|
import com.product.util.BaseUtil;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.stereotype.Service;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.net.URLEncoder;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.util.*;
|
|
/**
|
* @Author cheng
|
* @Date 2022/11/14 18:03
|
* @Desc 生产实时信息 report
|
*/
|
@Service
|
public class ProductionRealTimeInfoService extends AbstractBaseService {
|
|
|
public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws BaseException, IOException {
|
FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
|
DataTableEntity manufacturingCommandSheet = this.getManufacturingCommandSheet(fse);
|
if (DataTableEntity.isEmpty(manufacturingCommandSheet)) {
|
throw new BaseException(ErrorCode.NOT_EXPORT_DATA);
|
}
|
response.setContentType("multipart/form-data");
|
response.setCharacterEncoding("utf-8");
|
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
|
String fileName = URLEncoder.encode("制令单生产信息" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
|
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
|
List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
|
for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
|
FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
|
Map<Object, Object> values = fieldSetEntity.getValues();
|
JSONObject jsonObject = new JSONObject((Map) values);
|
|
ManufacturingOrderEntity manufacturingOrderEntity = jsonObject.toJavaObject(ManufacturingOrderEntity.class);
|
manufacturingOrderEntityList.add(manufacturingOrderEntity);
|
}
|
ServletOutputStream outputStream = response.getOutputStream();
|
ExcelWriter excelWriter = EasyExcel.write(outputStream, ManufacturingOrderEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
|
try {
|
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
|
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
|
WriteSheet writeSheet = EasyExcel.writerSheet(1, "制令单信息").build();
|
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
|
excelWriter.write(manufacturingOrderEntityList, writeSheet);
|
// writeSheet = EasyExcel.writerSheet(2, "制令单信息").build();
|
// excelWriter.write(manufacturingOrderEntityList, writeSheet);
|
// writeSheet = EasyExcel.writerSheet(3, "制令单信息").build();
|
// excelWriter.write(manufacturingOrderEntityList, writeSheet);
|
String process = fse.getString("process");
|
|
String[] moNumbers = manufacturingCommandSheet.getData().stream().map(item -> item.getString("mo_number")).toArray(String[]::new);
|
for (int i = 1; i <= moNumbers.length; i++) {
|
String moNumber = moNumbers[i - 1];
|
FieldSetEntity ff = new FieldSetEntity();
|
ff.setTableName("temp");
|
ff.setValue("process", process);
|
ff.setValue("mo_number", moNumber);
|
DataTableEntity processSheet = getProcessSheet(ff);
|
if (DataTableEntity.isEmpty(processSheet)) {
|
continue;
|
}
|
fileName = moNumber + "的生产明细";
|
writeSheet = EasyExcel.writerSheet(i + 1, fileName).head(ProductionDetailEntity.class).build();
|
List<ProductionDetailEntity> productionDetailEntities = new ArrayList<>();
|
for (int k = 0; k < processSheet.getRows(); k++) {
|
JSONObject jsonObject = new JSONObject((Map) processSheet.getFieldSetEntity(k).getValues());
|
ProductionDetailEntity productionDetail = jsonObject.toJavaObject(ProductionDetailEntity.class);
|
productionDetailEntities.add(productionDetail);
|
}
|
excelWriter.write(productionDetailEntities, writeSheet);
|
}
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
excelWriter.finish();
|
outputStream.flush();
|
outputStream.close();
|
}
|
}
|
|
/**
|
* 工序列表获取
|
*
|
* @return
|
*/
|
public DataTableEntity getProcessList() {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" SELECT ");
|
sql.append(" group_next ");
|
sql.append(" FROM da_t_co_route_control where state_flag <> '2' AND group_next <> '0' AND isrotegroup = 'Y' ");
|
sql.append(" GROUP BY group_next ");
|
return getBaseDao().listTable(sql.toString(), new Object[]{});
|
}
|
|
// T_CO_ROUTE_CONTROL T_PM_MO_BASE T_CO_CUSTOMER T_CO_AREA T_PM_PROJECT_BASE T_CO_ITEM T_WIP_STATION_REC
|
// T_PM_PRODUCT T_WIP_TRACKING T_CO_EMP_DESC T_SMO_TECHNICS
|
// T_WIP_ERROR_SCRAP T_WIP_DETAIL
|
|
public void test() {
|
DataBaseEntity dataBaseEntity = new DataBaseEntity("ef7b1348-b2ec-40d1-a0b8-76cd29b99a1232d");
|
Dao dao = dataBaseEntity.getDao();
|
double ceil = Math.ceil(48548455 / 500);
|
try {
|
Connection connection = dao.getConnection();
|
PreparedStatement pst = connection.prepareStatement("\n" +
|
"\n" +
|
" update T_PM_PRODUCT_SN SET CREATE_DATE=to_date((select '20'||(select trunc(dbms_random.value(17,22)) from dual)||'-'||(select trunc(dbms_random.value(1,10)) from dual)||'-'||(select trunc(dbms_random.value(1,28)) from dual)||' 00:00:00'\n" +
|
"\t from dual),'yyyy-mm-dd hh24:mi:ss')\n" +
|
"\t where product_sn in (\n" +
|
"\t\tSELECT PRODUCT_SN from T_PM_PRODUCT_SN where CREATE_DATE=TO_DATE('2022-11-17 16:30:28','yyyy-mm-dd hh24:mi:ss') and rownum<=500\n" +
|
"\t )");
|
for (int i = 0; i < ceil; i++) {
|
|
pst.execute();
|
System.out.println("剩余--" + (ceil - i + 1));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 获取制令单信息
|
*
|
* @param fse
|
* @return
|
* @throws BaseException
|
*/
|
public DataTableEntity getManufacturingCommandSheet(FieldSetEntity fse) throws BaseException {
|
Integer cpage = fse.getInteger("cpage");
|
if (cpage == null || cpage <= 0) {
|
cpage = 1;
|
}
|
Integer pageSize = fse.getInteger("page_size");
|
if (pageSize == null || pageSize <= 0) {
|
pageSize = Integer.MAX_VALUE;
|
}
|
//近期
|
String queryTime = fse.getString("query_time");
|
String timeValue = fse.getString("time_value");
|
String areaName = fse.getString("areaname");
|
String productMaterial = fse.getString("product_material");
|
String moNumber = fse.getString("mo_number");
|
// String projectId = fse.getString("project_id");
|
String closeFlag = fse.getString("close_flag");
|
String timeFilter = fse.getString("time_filter");
|
String timeField = fse.getString("timeField");
|
if (StringUtils.isAllEmpty(areaName, productMaterial, moNumber, productMaterial)
|
&& ((!"1".equals(timeFilter) || StringUtils.isEmpty(timeValue)) && !"1".equals(queryTime))) {
|
|
throw new BaseException(ErrorCode.REPORT_SELECT_TIME_FILTER_CANNOT_EMPTY);
|
}
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\n select distinct ");
|
sql.append("\n p.project_id as project_name , ");//--工单号,
|
sql.append("\n p.destroy_no , ");//--订单号,
|
sql.append("\n p.product_material , ");//--机种,
|
sql.append("\n m.co_item_name as product_name , ");//--品名,
|
sql.append("\n m.co_item_spec as product_standard , ");//--规格,
|
// sql.append("\n c.customer , ");//--客户名称,
|
sql.append("\n ifnull(p.product_count,0) product_count ,");//--工单数量,
|
sql.append("\n t.mo_number , ");//--制令单号,
|
sql.append("\n case ");
|
sql.append("\n when process_face = 'a' then '单面' ");
|
sql.append("\n when process_face = 's' then '正面' ");
|
sql.append("\n when process_face = 'c' then '反面' ");
|
sql.append("\n else '' end process_face , ");//--加工面,
|
sql.append("\n case ");
|
sql.append("\n when master_flag='Y' then '是' ");
|
sql.append("\n else '否' end master_flag , ");//--产出标识,
|
sql.append("\n a.areaname , ");//--区域名称,
|
sql.append("\n t.target_qty , ");//--目标数量,
|
sql.append("\n t.input_qty ,");//--投入数量,
|
sql.append("\n t.output_qty , ");//--产出数量,
|
sql.append("\n t.input_qty-t.output_qty wip_qty , ");//--在制品数量,
|
sql.append("\n t.total_scrap_qty , ");//--作废数量,
|
sql.append("\n ifnull(t.tp_number,0) tp_number , ");//--单片点数,
|
sql.append("\n ifnull(t.output_qty *t.tp_number,0) totoaltp_number, ");//--总点数,
|
sql.append("\n t.mo_create_date , ");//--开立时间,
|
sql.append("\n t.mo_start_date , ");//--实际投入时间,
|
sql.append("\n t.mo_due_date , ");//--预关结时间,
|
sql.append("\n t.mo_close_date ");//--关结时间
|
sql.append("\n from ");
|
sql.append("\n [={da_t_pm_mo_base}{areaid,project_id,master_flag,process_face,target_qty,input_qty,output_qty,total_scrap_qty");
|
sql.append(",tp_number,mo_create_date,mo_start_date,mo_due_date,mo_close_date");
|
sql.append(",close_flag,mo_number}");
|
boolean isF = false;
|
// if ("1".equals(queryTime) && (StringUtils.isEmpty(timeValue) || !"1".equals(timeFilter))) {
|
// //没有时间条件且勾选了只查看近两年的数据
|
//// DateTime offset = DateUtil.offset(new Date(), DateField.YEAR, -2);
|
//// sql.append(" {where ( mo_create_date >= str_to_date('" + DateUtil.format(offset, "yyyy-MM-dd") + "','%Y-%m-%d') )}");
|
//// filterParams.add(DateUtil.format(offset, "yyyy-MM-dd"));
|
// } else
|
if (!StringUtils.isEmpty(timeValue) && "1".equals(timeFilter)) {
|
//有时间条件根据用户选择的时间过滤
|
String[] times = timeValue.split(",");
|
String startTime = times[0];
|
String endTime = times[1];
|
// sql.append(" (mo_number in (SELECT mo_number from [={da_t_wip_station_rec}{mo_number}");
|
// sql.append("{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] ");
|
// sql.append(" ) ");
|
//时间条件
|
if (!"production_time".equals(timeField)) {
|
sql.append(" {WHERE");
|
sql.append("\n (").append(timeField).append(">= str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s')");
|
sql.append("\n and ").append(timeField).append("<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s') )");
|
|
}
|
sql.append("}=] t");
|
if ("production_time".equals(timeField)) {
|
//特殊： 生产时间
|
sql.append(" JOIN [={da_t_wip_station_rec}{mo_number}{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] rec ON rec.mo_number = t.mo_number ");
|
}
|
} else {
|
sql.append("=] t ");
|
}
|
|
// sql.append("\n left join [=da_t_co_customer=] c on t.cust_code = c.cust_code ");
|
sql.append("\n left join da_t_co_area a on t.areaid = a.areaid ");
|
sql.append("\n left join [={da_t_pm_project_base}{project_base_id,product_material");
|
sql.append(",project_id,destroy_no,product_count");
|
sql.append("}=] p on p.project_base_id = t.project_id ");
|
sql.append("\n left join [={da_t_co_item}{co_item_code,co_item_name,co_item_spec}=] m on p.product_material = m.co_item_code ");
|
boolean isFilter = false;
|
StringBuilder sqlFilter = new StringBuilder();
|
List<Object> filterParams = new ArrayList<>();
|
|
isFilter = appendLikeFilter(areaName, "a.areaname", sqlFilter, filterParams, isFilter);
|
isFilter = appendLikeFilter(productMaterial, "p.product_material", false, sqlFilter, filterParams, isFilter);
|
isFilter = appendLikeFilter(moNumber, "t.mo_number", sqlFilter, filterParams, isFilter);
|
if (!"-1".equals(closeFlag)) {
|
isFilter = appendLikeFilter(closeFlag, "t.close_flag", false, sqlFilter, filterParams, isFilter);
|
}
|
|
if (isFilter) {
|
sql.append(" where (").append(sqlFilter).append(") ");
|
}
|
sql.append(" order by mo_create_date");
|
System.out.println(sql);
|
String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
|
DataTableEntity dataTableEntity = getBaseDao().listTable(parseSql, filterParams.toArray(), pageSize, cpage);
|
|
return dataTableEntity;
|
}
|
|
public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
|
String trackingTable = "da_t_wip_tracking";
|
String groupNext = fse.getString("group_next");
|
String moNumber = fse.getString("mo_number");
|
StringBuilder sql = new StringBuilder(64);
|
sql.append("select number_type from [={" + trackingTable + "}{number_type,mo_number}{ where mo_number='" + moNumber + "' and number_type!=0}=] ");
|
String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString());
|
FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(parseSql, new Object[]{}, false);
|
if (fs != null) {
|
List<Object> params = new ArrayList<>();
|
|
Integer numberType = NumberUtil.isNumber(fs.getString("number_type")) ? NumberUtil.parseInt(fs.getString("number_type")) : 0;
|
if (numberType == null || numberType < 1) {
|
return null;
|
}
|
sql.setLength(0);
|
|
String scrap = "报废";
|
String lend = "借出";
|
if (1 == numberType && !"流程结束".equals(groupNext)) {
|
sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + " d1}{emp_no,number_type,mo_number,display_group,item_count,routeendflage,depanel_flag,wait_scrap_qty");
|
sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn,in_station_time");
|
sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and display_group='" + groupNext + "' and t.item_count!=0 and routeendflage<>'Y'}=] t ");
|
sql.append("\n left join da_t_co_emp_desc e ");
|
sql.append("\n on t.emp_no=e.emp_no ");
|
// sql.append("\n where t.number_type=? and t.mo_number=? and t.display_group=? ");
|
// sql.append("\n and t.item_count!=0 and routeendflage<>'Y' ");
|
sql.append("\n union all ");
|
sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + " d2}{");
|
sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn");
|
sql.append(",number_type,mo_number,item_count,routeendflage,in_station_time,depanel_flag,wait_scrap_qty,serial_number,emp_name}");
|
sql.append("{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "' ");
|
sql.append(" and item_count!=0 and routeendflage<>'Y' and error_flag='1'} ");
|
sql.append("=] t ");
|
sql.append("\n left join da_t_co_emp_desc e ");
|
sql.append("\n on t.emp_no=e.emp_no ");
|
sql.append("\n order by error_flag asc,in_station_time asc ");
|
} else if (2 == numberType && "流程结束".equals(groupNext)) {
|
sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n ifnull(c.product_count,0) product_count, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + "}");
|
sql.append(" {lot_number,in_station_time,serial_number,group_name,error_flag,mo_number,group_name,item_count,repair");
|
sql.append(",container_sn,serial_number,wait_scrap_qty,depanel_flag,number_type,emp_no}");
|
|
sql.append("\n {where number_type='" + numberType + "' and mo_number='" + moNumber + "' and routeendflage='Y' ");
|
sql.append("}=] t ");
|
sql.append("\n left join ");
|
sql.append("\n da_t_co_emp_desc e ");
|
sql.append("\n on t.emp_no=e.emp_no ");
|
sql.append("\n LEFT JOIN ");
|
sql.append("\n(SELECT SUM(COUNT) product_count,product_sn FROM [={da_t_pm_product_sn}{COUNT(1) count,product_sn}{WHERE product_sn in (select serial_number from " + trackingTable + ") group by product_sn }=] group by product_sn ) C ");
|
sql.append("\n ON t.serial_number=c.product_sn ");
|
sql.append("\n order by error_flag asc,in_station_time asc ");
|
|
} else if (2 == numberType && !"流程结束".equals(groupNext)) {
|
sql.append("\n select t.serial_number,t.in_station_time, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + " d1}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
|
sql.append(",emp_no,number_type,display_group,routeendflage}");
|
sql.append("{ WHERE number_type='" + numberType + "' and display_group='" + groupNext + "' and mo_number='" + moNumber + "' and routeendflage<>'Y' }");
|
sql.append("=] t ");
|
sql.append("\n left join ");
|
sql.append("\n da_t_co_emp_desc e ");
|
sql.append("\n on t.emp_no=e.emp_no ");
|
// sql.append("\n where t.number_type=? ");
|
// sql.append("\n and t.mo_number=? and t.display_group=? ");
|
// sql.append("\n and routeendflage<>'Y' ");
|
sql.append("\n union all ");
|
sql.append("\n select t.serial_number,t.in_station_time, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + " d2}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
|
sql.append(",emp_no,number_type,display_group,routeendflage}{ where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
|
sql.append(" and routeendflage<>'Y' and error_flag='1'}");
|
sql.append("=] t");
|
sql.append("\n left join ");
|
sql.append("\n da_t_co_emp_desc e ");
|
sql.append("\n on t.emp_no=e.emp_no ");
|
// sql.append("\n where t.number_type=? ");
|
// sql.append("\n and t.mo_number=? and t.group_name=? ");
|
// sql.append("\n and routeendflage<>'Y' and error_flag='1' ");
|
sql.append("\n order by error_flag asc,in_station_time asc ");
|
|
} else if (1 == numberType && "流程结束".equals(groupNext)) {
|
sql.append("\n select t.serial_number,t.in_station_time, ");
|
sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
|
sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
|
sql.append("\n t.error_flag, ");
|
sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
|
sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
|
sql.append("\n from [={" + trackingTable + "}");
|
sql.append("{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,emp_name,container_sn,");
|
sql.append("wait_scrap_qty,depanel_flag,emp_no,number_type}{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and routeendflage='Y'}");
|
sql.append("=] t ");
|
sql.append("\n left join ");
|
sql.append("\n da_t_co_emp_desc e on t.emp_no=e.emp_no ");
|
// sql.append("\n where t.number_type=? ");
|
// sql.append("\n and t.mo_number=? and routeendflage='Y' ");
|
sql.append("\n order by error_flag asc,in_station_time asc ");
|
|
} else {
|
return null;
|
}
|
if (!"流程结束".equals(groupNext)) {
|
params.add(scrap);
|
params.add(lend);
|
// params.add(numberType);
|
// params.add(moNumber);
|
// params.add(groupNext);
|
params.add(scrap);
|
params.add(lend);
|
// params.add(numberType);
|
// params.add(moNumber);
|
// params.add(groupNext);
|
} else {
|
params.add(scrap);
|
params.add(lend);
|
// params.add(moNumber);
|
// params.add(numberType);
|
}
|
System.out.println(sql);
|
String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
|
return getBaseDao().listTable(parseSql1, params.toArray());
|
}
|
return null;
|
}
|
|
/**
|
* 工序报表
|
*
|
* @param fse
|
* @return
|
* @throws BaseException
|
*/
|
public DataTableEntity getProcessSheet(FieldSetEntity fse) throws BaseException {
|
String detailTable = "da_t_wip_detail";
|
String trackTable = "da_t_wip_tracking";
|
//制令单号
|
String moNumber = fse.getString("mo_number");
|
//工序
|
String process = fse.getString("process");
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\n select ");
|
sql.append("\n * ");
|
sql.append("\n from ");
|
sql.append("\n ( ");
|
sql.append("\n select ");
|
sql.append("\n c.* ");
|
sql.append("\n from ");
|
sql.append("\n ( ");
|
sql.append("\n select ");
|
sql.append("\n a.*, ");
|
sql.append("\n ifnull( c1.wait_scrap_qty_all, 0 ) wait_scrap_qty_all, ");
|
sql.append("\n ifnull( l.pass_itemcount, 0 ) pass_itemcount, ");
|
sql.append("\n ifnull( k.second_passcount, 0 ) second_passcount, ");
|
sql.append("\n ifnull( m.use_itemcount, 0 ) use_itemcount, ");
|
sql.append("\n ifnull( n.error_itemcount, 0 ) error_itemcount ");
|
sql.append("\n from ");
|
sql.append("\n ( ");
|
sql.append("\n select ");
|
sql.append("\n v.route_code, ");
|
sql.append("\n v.group_next, ");
|
sql.append("\n min( v.step_sequence ) as step_sequence, ");
|
sql.append("\n min( state_flag ) as state_flag, ");
|
sql.append("\n v.mo_number ");
|
sql.append("\n from ");
|
sql.append("\n ( ");
|
sql.append("\n select ");
|
sql.append("\n a.route_groupid, ");
|
sql.append("\n a.route_code, ");
|
sql.append("\n a.group_code, ");
|
sql.append("\n a.group_name, ");
|
sql.append("\n a.group_next, ");
|
sql.append("\n a.state_flag, ");
|
sql.append("\n a.step_sequence, ");
|
sql.append("\n a.ismustpass, ");
|
sql.append("\n a.isrotegroup, ");
|
sql.append("\n a.ms_code, ");
|
sql.append("\n a.route_inout_flag, ");
|
sql.append("\n a.route_cycle_time, ");
|
sql.append("\n b.technicsid, ");
|
sql.append("\n c.mo_number ");
|
sql.append("\n from ");
|
sql.append("\n da_t_co_route_control a ");
|
sql.append("\n left join da_t_smo_technics b on a.route_code = b.route_code ");
|
sql.append("\n join [={da_t_pm_mo_base b1}{technicsid,mo_number}{ where mo_number = '" + moNumber + "' }=] c on b.technicsid = c.technicsid ");
|
sql.append("\n ) v ");
|
sql.append("\n left join da_t_co_group g on v.group_next = g.group_name ");
|
sql.append("\n where ");
|
// sql.append("\n v.mo_number = '"+moNumber+"' ");
|
sql.append("\n v.state_flag <> '2' ");
|
sql.append("\n and v.group_next <> '0' ");
|
sql.append("\n and v.isrotegroup = 'Y' ");
|
sql.append("\n group by ");
|
sql.append("\n v.route_code, ");
|
sql.append("\n v.group_next, ");
|
sql.append("\n v.mo_number ");
|
sql.append("\n order by ");
|
sql.append("\n v.route_code, ");
|
sql.append("\n v.group_next, ");
|
sql.append("\n v.mo_number ");
|
sql.append("\n ) a ");
|
sql.append("\n left join ( select scrap_process, mo_number, sum( ng_count ) wait_scrap_qty_all from da_t_wip_error_scrap where mo_number = '" + moNumber + "' group by scrap_process, mo_number ) c1 on a.group_next = c1.scrap_process ");
|
sql.append("\n and a.mo_number = c1.mo_number ");
|
sql.append("\n left join ( ");
|
sql.append("\n select ");
|
sql.append("\n count( distinct a.serial_number ) pass_itemcount, ");
|
sql.append("\n a.group_name, ");
|
sql.append("\n a.mo_number ");
|
sql.append("\n from ");
|
sql.append("\n [={" + detailTable + " d1}{serial_number,group_name,mo_number,reflux_flag}{ where mo_number='" + moNumber + "' and reflux_flag='N' }=] a ");
|
// sql.append("\n where ");
|
// sql.append("\n a.mo_number = '"+moNumber+"' ");
|
// sql.append("\n and a.reflux_flag = 'N' ");
|
sql.append("\n group by ");
|
sql.append("\n group_name, ");
|
sql.append("\n mo_number ");
|
sql.append("\n ) l on a.mo_number = l.mo_number ");
|
sql.append("\n and a.group_next = l.group_name ");
|
sql.append("\n left join ( ");
|
sql.append("\n select ");
|
sql.append("\n sum( item_count ) second_passcount, ");
|
sql.append("\n group_name, ");
|
sql.append("\n mo_number ");
|
sql.append("\n from ");
|
sql.append("\n ( ");
|
sql.append("\n select ");
|
sql.append("\n a.group_name, ");
|
sql.append("\n a.mo_number, ");
|
sql.append("\n a.serial_number, ");
|
sql.append("\n min( item_count ) item_count ");
|
sql.append("\n from ");
|
sql.append("\n [={" + detailTable + " d2}{group_name,mo_number,serial_number,item_count,reflux_flag}{where mo_number='" + moNumber + "' and reflux_flag='Y'}=] a ");
|
// sql.append("\n where ");
|
// sql.append("\n a.mo_number = '"+moNumber+"' ");
|
// sql.append("\n and reflux_flag = 'Y' ");
|
sql.append("\n group by ");
|
sql.append("\n a.group_name, ");
|
sql.append("\n a.mo_number, ");
|
sql.append("\n a.serial_number ");
|
sql.append("\n ) tttt ");
|
sql.append("\n group by ");
|
sql.append("\n group_name, ");
|
sql.append("\n mo_number ");
|
sql.append("\n ) k on a.mo_number = k.mo_number ");
|
sql.append("\n and a.group_next = k.group_name ");
|
sql.append("\n left join ( ");
|
sql.append("\n select ");
|
sql.append("\n display_group, ");
|
sql.append("\n mo_number, ");
|
sql.append("\n sum( item_count ) use_itemcount ");
|
sql.append("\n from ");
|
sql.append("\n [={" + trackTable + " t1}{display_group,mo_number,item_count,routeendflage,depanel_flag}{ where mo_number='" + moNumber + "' and routeendflage <> 'Y' and depanel_flag <> 'Y' }=] ");
|
// sql.append("\n where ");
|
// sql.append("\n mo_number = '"+moNumber+"' ");
|
// sql.append("\n and routeendflage <> 'Y' ");
|
// sql.append("\n and depanel_flag <> 'Y' ");
|
sql.append("\n group by ");
|
sql.append("\n display_group, ");
|
sql.append("\n mo_number ");
|
sql.append("\n ) m on a.group_next = m.display_group ");
|
sql.append("\n and a.mo_number = m.mo_number ");
|
sql.append("\n left join ( select group_name, mo_number, sum( repair ) error_itemcount from [={" + trackTable + " t2}{group_name,mo_number,repair,error_flag}{where mo_number = '" + moNumber + "' and error_flag = '1'}=] group by group_name, mo_number ) n on a.group_next = n.group_name ");
|
sql.append("\n and a.mo_number = n.mo_number ");
|
sql.append("\n ) c union all ");
|
sql.append("\n select ");
|
sql.append("\n 0 route_code, ");
|
sql.append("\n '流程结束' group_next, ");
|
sql.append("\n 150 step_sequence, ");
|
sql.append("\n 5 state_flag, ");
|
sql.append("\n '" + moNumber + "' mo_number, ");
|
sql.append("\n ifnull( ( select sum( wait_scrap_qty ) from [={" + trackTable + " t3}{wait_scrap_qty,mo_number}{ where mo_number = '" + moNumber + "' }=] as tt ), 0 ) wait_scrap_qty_all, ");
|
sql.append("\n 0 pass_itemcount, ");
|
sql.append("\n 0 second_passcount, ");
|
sql.append("\n ifnull( ( select sum( item_count ) from [={" + trackTable + " t4}{item_count,mo_number,routeendflage}{where mo_number = '" + moNumber + "' and routeendflage = 'Y'}=] as ccc ), 0 ) use_itemcount, ");
|
sql.append("\n 0 error_itemcount ");
|
sql.append("\n from ");
|
sql.append("\n dual ");
|
sql.append("\n ) a ");
|
|
if (!StringUtils.isEmpty(process)) {
|
String[] processArray = process.split(",");
|
sql.append("\n where ").append(BaseUtil.buildQuestionMarkFilter("group_next", processArray, true));
|
}
|
sql.append("\n ORDER BY ");
|
sql.append("\n state_flag ASC, ");
|
sql.append("\n step_sequence ASC ");
|
|
String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
|
|
return getBaseDao().listTable(parseSql, new Object[]{});
|
}
|
|
private boolean appendLikeFilter(String value, String field, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
|
return appendLikeFilter(value, field, true, sqlFilter, filterParams, isFilter);
|
}
|
|
private boolean appendLikeFilter(String value, String field, boolean like, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
|
if (!StringUtils.isEmpty(value)) {
|
if (isFilter) {
|
sqlFilter.append(" AND \n");
|
}
|
sqlFilter.append("\n ( ").append(field);
|
if (like) {
|
sqlFilter.append(" like concat(?,'%') ");
|
} else {
|
sqlFilter.append("= ?");
|
}
|
sqlFilter.append(" ) ");
|
filterParams.add(value);
|
isFilter = true;
|
}
|
return isFilter;
|
}
|
|
}
|
