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.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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);	//是否切表
		Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//是否近期数据
		Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);	//页数大小
		Integer cpage = fse.getInteger(CmnConst.CPAGE);			//目标页
	
		//创建tracking表SQL，并获取过滤的数据
		String parseTrackingSql=createTrackingSql(fse, isBakTable);
		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);
			System.out.println(keypSql);
			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的SQL
	 * @param fse
	 * @param isBakTable 是否BAK表
	 * @return
	 */
	public String createTrackingSql(FieldSetEntity fse, String isBakTable) {
		
		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 [={");
		if ("1".equals(isBakTable)) {
			trackingSql.append("da_t_wip_tracking_bak");
		}else {
			trackingSql.append("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(), 2, false);
		return parseTrackingSql;
	}
	
	/**
	 * 	根据tacking表的SN解析keyp表
	 * @param serialNumbers
	 * @param isBakTable 是否BAK表
	 * @return
	 */
	public String createKeypSql(Object[] serialNumbers, String isBakTable) {
		String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);
		
		StringBuilder keypSql = new StringBuilder(2056);
		keypSql.append(" SELECT ")
		.append(" t.pk_product_sn AS pkProductSn,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 [={");
		if ("1".equals(isBakTable)) {
			keypSql.append("da_t_wip_product_keyp_bak");
		}else {
			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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"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(), 2, false);
		return parseKeypSql;
	}
	
	/**
	 * 	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(CmnConst.PKPRODUCTSN);
			@SuppressWarnings("unchecked")
			List<String> trackInfo = (List<String>) json.get(pkProductSN);
			if (trackInfo.size()==3) {
				fseKeyp.setValue(CmnConst.AREANAME, trackInfo.get(0));
				fseKeyp.setValue(CmnConst.MONUMBER, trackInfo.get(1));
				fseKeyp.setValue(CmnConst.INLINETIME, 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);		//线体
			String startTime = fse.getString(CmnConst.START_TIME);	//开始时间
			String endTime = fse.getString(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(!BaseUtil.strIsNull(startTime)) {
				sqlFilter.append(" AND in_station_time >STR_TO_DATE('")
				.append(startTime)
				.append("','%Y-%m-%d %H:%i:%s')");
			}
			
			if (!BaseUtil.strIsNull(endTime)) {
				sqlFilter.append(" AND in_station_time <STR_TO_DATE('")
				.append(endTime)
				.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);
		DataTableEntity workCenterOperationInfomationSheet = this.dtSNItemInfo(fse);
		
		//初始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<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);
		}
		ServletOutputStream outputStream = response.getOutputStream();
		ExcelWriter excelWriter = EasyExcel.write(outputStream, SNAssociatedItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
		try {
			WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build();
		    excelWriter.write(traceSNItemEntityList, writeSheet);
		} catch (Exception e) {
			throw e;
		} finally {
			excelWriter.finish();
		}
	}
 	
 	/**
	 * 	过站信息
	 * @param fse
	 * @return
	 */
	public DataTableEntity dtSNItemInfo(FieldSetEntity fse) {
		
		String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);
		Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//是否近期数据
		Integer pageSize = fse.getInteger(CmnConst.PAGESIZE);	//页数大小
		Integer cpage = fse.getInteger(CmnConst.CPAGE);			//目标页
	
		//创建tracking表SQL，并获取过滤的数据
		String parseTrackingSql=createTrackingSql(fse, isBakTable);
		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);
			
			//通过tracking过滤keyp
			//Method1:使用OR查询
			Object [] serialNumbers = dtTracking.getUuids();
			String keypSql = createKeypSql(serialNumbers, isBakTable);
			System.out.println(keypSql);
			DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {});
			
			if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
				//将tracking表的数据转为(key，value)形式存储
				JSONObject json = convertDataTableToJson(dtTracking);
				addKeypInfo(json, dtKeyp);
				return dtKeyp;
			}
		}			
		return null;
	}
}