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("  {WHERE");
            if ("production_time".equals(timeField)) {
                //特殊： 生产时间
                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("  ) ");
            } else {
                //时间条件
                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("}");
        }
        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");
        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 bak = fse.getString("~bak~");
        String trackingTable = "da_t_wip_tracking";
        if ("1".equals(bak)) {
            trackingTable = "da_t_wip_tracking_bak";
        }
        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 bak = fse.getString("~bak~");
        String detailTable = "da_t_wip_detail";
        String trackTable = "da_t_wip_tracking";
        if ("1".equals(bak)) {
            detailTable = "da_t_wip_detail_bak";
            trackTable = "da_t_wip_tracking_bak";
        }
        //制令单号
        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;
    }

}