4-研控报表组装

1.产能报表需求

根据清洗出来的数据进行产能获取和计算。产能获取就是调用SAP接口。

计算相应的效率，通过SQL来进行连笔操作，同一工单下的多个操作，选取最少的那条操作，类似做过的微电看班需求。

要求定时任务每晚获取当天产能数据，更新插入后，计算效率，进行前端报表展示

具体的SQL 设计：

with A AS(
    SELECT
	ID as ID,LINE as LINE,
	SHOP_ORDER as SHOP_ORDER,
	INDUCTIVE_DATE as INDUCTIVE_DATE,
	START_TIME as START_TIME,
	END_TIME as END_TIME,
	ALL_COUNT as ALL_COUNT,
	PROCESS as PROCESS,
	OPERATION as OPERATION,
    TO_CHAR(START_TIME, 'HH24:MI') || '~' || TO_CHAR(END_TIME, 'HH24:MI') AS TIME_RANGE,
	SUM(ALL_COUNT) OVER (PARTITION BY LINE,SHOP_ORDER,START_TIME,END_TIME,INDUCTIVE_DATE) AS SUM_COUNT,
	ROW_NUMBER() OVER (PARTITION BY LINE,SHOP_ORDER,PROCESS,START_TIME,END_TIME ORDER BY ALL_COUNT ASC) AS RN,
	REMARK as REMARK
    FROM  MOM_SFC_INDUCTION WHERE SITE = 'S801'),
 B AS(
    SELECT
	A.ID as ID,A.LINE as LINE,
	A.SHOP_ORDER as SHOP_ORDER,
	A.INDUCTIVE_DATE as INDUCTIVE_DATE,
	A.START_TIME as START_TIME,
	A.END_TIME as END_TIME,
	A.TIME_RANGE as TIME_RANGE,
	A.ALL_COUNT as ALL_COUNT,
	A.PROCESS as PROCESS,
	A.SUM_COUNT as SUM_COUNT,
	S.QTY_TO_BUILD,
	split(S.planned_item_bo,2) as ITEM,
	A.REMARK as REMARK
	FROM SHOP_ORDER S JOIN A ON A.SHOP_ORDER = S.SHOP_ORDER and A.RN = 1
)
select
	B.ID as ID,    
	B.LINE as LINE,
	B.SHOP_ORDER as SHOP_ORDER,
	B.ITEM as ITEM,
	M.INTRODUCTION as INTRODUCTION,
	B.QTY_TO_BUILD,
	B.INDUCTIVE_DATE as INDUCTIVE_DATE,
	B.TIME_RANGE as TIME_RANGE,
	B.PROCESS as PROCESS,
	B.SUM_COUNT as SUM_COUNT,
	M.STAND_PEOPLE as STAND_PEOPLE,
	M.STAND_PRODUCT as STAND_PRODUCT,
	B.ALL_COUNT as ALL_COUNT,
	(B.ALL_COUNT - M.STAND_PRODUCT) AS DIFF_COUNT,
	TO_CHAR((B.ALL_COUNT) / (M.STAND_PRODUCT)*100, 'FM9999990.00') || '%' AS EFFICIENCY,
	B.REMARK as REMARK
FROM
    B JOIN MOM_STANDWORK_HOURS M ON B.ITEM = M.ITEM AND B.PROCESS = M.PROCESS ORDER BY INDUCTIVE_DATE,START_TIME 

2.接口设计

2.1 处理工序为空

清洗数据获取到当天的工单和工序(一般有10和20)，正常来说清洗出来都会有，但是拼装的时候没有，所以做出了后续的修改：

//新增，处理process
            Map<String,String> processMap = new HashMap<>();
            for (int i = 0; i < allConfig.size(); i++) {
                String key = allConfig.get(i).getSite()+","+allConfig.get(i).getLine()+","+allConfig.get(i).getOperation();
                processMap.put(key, allConfig.get(i).getProcess());
            }
            for (int i = 0; i < saveDataList.size(); i++) {
                String key = saveDataList.get(i).getSite()+","+saveDataList.get(i).getLine()+","+saveDataList.get(i).getOperation();
                String process = processMap.get(key);
                //当取到的工序存在的时候
                if(process!=null){
                    saveDataList.get(i).setProcess(process);
                }
                //不存在的时候
                else{
                    saveDataList.get(i).setProcess(null);
                }
            }

获取到工单和工序后，调用SAP接口即可。

2.2 实体设计

使用html的前端设计，前后端交互使用JSON格式，由于有分页要求，设计DTO类

public class ProductStatiticDTO {
    private final String regex = "[0-9]+";

    //工厂
    @NotBlank(message = "MesExtController.site.notEmpty")
    private String site;
    //线体
    private String line;
    //工单
    private String shopOrder;
    //物料编码
    private String item;
    //产品描述
    private String productDesc;
    //工单批量
    private String shopOrderQty;
    //起始日期
    private String startDate;
    //结尾日期
    private String endDate;
    //时间段
    private String timeRange;
    //工序
    private String process;
    //累计产出
    private String sumCount;
    //标准人数
    private String standPeople;
    //标准产出
    private String standProduct;
    //实际产出
    private String allCount;
    //差异数量
    private String diffCount;
    //效率
    private String efficiency;
    //原因备注
    private String remark;
    //选择Ids
    private String selectIds;
    //选择Ids
    private List<String> exportIds;
    //编辑数据Json (新增/修改)
    private String dataJson;
    //导出类型(select/selectPage/selectAll)
    private String exportType;
    //当前页
    private String  page = "1";
    //每页条数
    private String  limit = "30";
    //日期
    private String createDate;
}

前端使用layui，初始化grid：

这是初始化展示数据，调用接口，获取data，后端数据封装使用OperationResult正常返回即可，参数传入使用

...layui.form.val('#searchForm')

2.3 数据导出

前端选取数据导出传入的是ID组，后端处理拼接SQL，使用LIKE子句判断：

if(StringUtils.isNotBlank(exportIds)){
            sql.append(" AND B.ID IN ('"+exportIds+"')");
        }

前端页面以sfc_config_list.html为模板，基本能够包含所有需求。