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