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();
		}
	}
}