uas-eis/src/main/java/com/uas/eis/serviceImpl/MESDataServiceImpl.java

package com.uas.eis.serviceImpl;

import com.uas.eis.core.config.SpObserver;
import com.uas.eis.dao.BaseDao;
import com.uas.eis.entity.ErrorMessage;
import com.uas.eis.exception.ApiSystemException;
import com.uas.eis.sdk.entity.ApiResult;
import com.uas.eis.sdk.resp.ApiResponse;
import com.uas.eis.service.MESDataService;
import com.uas.eis.utils.*;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.*;

@Service
public class MESDataServiceImpl implements MESDataService {

	@Autowired
	private BaseDao baseDao;

	@Value("${spring.datasource.username}")
	private String username;

	@Override
	public List<Map<Object,Object>> snStepPass(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_STEPPASSED where sp_id=? and nvl(t_status,'W')='W' ",Integer.class,map.get("SP_ID"));
			remap.put("id",map.get("SP_ID"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_STEPPASSED"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		//baseDao.execute(SqlUtil.getInsertSqlbyGridStore(maps, "TEMP_STEPPASSED"));
		//baseDao.execute("update temp_steppassed set t_status='D',T_REMAKR='重复' where t_id in (select t_id from (select temp_steppassed.*,row_number()over(partition by sp_id order by t_id desc) cn from temp_steppassed where nvl(t_status,'W')='W' ) where cn>1)");
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public List<Map<Object,Object>> snInfo(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_SNINFO where SI_ID=? and nvl(t_status,'W')='W' ",Integer.class,map.get("SI_ID"));
			remap.put("id",map.get("SI_ID"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_SNINFO"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public List<Map<Object,Object>> makeSnRelation(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_MAKESNRELATION where ID=? and nvl(t_status,'W')='W' ",Integer.class,map.get("ID"));
			remap.put("id",map.get("ID"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_MAKESNRELATION"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public List<Map<Object,Object>> MAKEBAD(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_MAKEBAD where MB_CODE=? and nvl(t_status,'W')='W' ",Integer.class,map.get("MB_CODE"));
			remap.put("id",map.get("MB_CODE"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_MAKEBAD"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public List<Map<Object,Object>> makeBadReason(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_MAKEBADREASON where MBR_ID=? and nvl(t_status,'W')='W' ",Integer.class,map.get("MBR_ID"));
			remap.put("id",map.get("MBR_ID"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_MAKEBADREASON"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public List<Map<Object,Object>> packageDetail(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		List<Map<Object, Object>> maps = BaseUtil.parseGridStoreToMaps(data);
		SpObserver.putSp(AE_MASTER);
		List<Map<Object, Object>> relist = new ArrayList<>();
		for (Map<Object, Object> map : maps) {
			Map<Object, Object> remap = new HashMap<>();
			String remark = "";
			int cn = baseDao.getJdbcTemplate().queryForObject("select count(1) from TEMP_PACKAGEDETAIL where PD_ID=? and nvl(t_status,'W')='W' ",Integer.class,map.get("MBR_ID"));
			remap.put("id",map.get("PD_ID"));
			if(cn>0){
				map.put("T_STATUS","D");
				map.put("T_REMAKR","重复");
				remark = "重复";
			}else{
				map.put("T_STATUS","W");
			}
			map.put("T_KEY",accessKey);
			map.put("T_INDATE", Timestamp.valueOf(DateUtil.currentDateString(Constant.YMD_HMS)));
			try {
				baseDao.execute(SqlUtil.getInsertSqlByMap(map,"TEMP_PACKAGEDETAIL"));
				remap.put("result","OK");
				remap.put("remark",remark);
			}catch (Exception e){
				remap.put("result","NG");
				remap.put("remark",e.getMessage());
				e.printStackTrace();
			}
			relist.add(remap);
		}
		SpObserver.putSp(username);
		return relist;
	}

	@Override
	public ApiResult<List<Map<String,Object>>> getQRCode(String data) {
		Map<Object, Object> map = BaseUtil.parseFormStoreToMap(data);
		String veCode = StringUtil.nvl(map.get("veCode"), "");
		if ("".equals(veCode)){
			return ApiResponse.failRsp("10011","供应商代码不能为空！");
		}
		String prodCode = StringUtil.nvl(map.get("prodCode"), "");
		if ("".equals(prodCode)){
			return ApiResponse.failRsp("10012","物料代码不能为空！");
		}
		String machineCode = StringUtil.nvl(map.get("machineCode"), "");
		if ("".equals(machineCode)){
			return ApiResponse.failRsp("10013","机台号不能为空！");
		}
		String version = StringUtil.nvl(map.get("version"), "");
		if ("".equals(version)){
			return ApiResponse.failRsp("10014","版本号不能为空！");
		}
		String indate = StringUtil.nvl(map.get("indate"), "");
		if ("".equals(indate)){
			return ApiResponse.failRsp("10015","请确认获取条码的具体时间！");
		}
		if (baseDao.checkIf("ProdQRCode","qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)=0")) {
			List<Map<String, Object>> maps = baseDao.queryForList("select to_char(qr_date,'yyyy-MM-dd') qr_date, qr_prodcode, qr_vecode, qr_version, qr_machinecode, qr_qty, qr_serianum, qr_code from ProdQRCode where qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)=0");
			baseDao.execute("update ProdQRCode set qr_isobtained=-1 where qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)=0");
			return ApiResponse.successRsp("0", "Success", "", maps);
		}else
			return ApiResponse.failRsp("10015","获取失败，请检查参数！");
	}

	@Override
	public ApiResult<String> checkQRCode(String data) {
		Map<Object, Object> map = BaseUtil.parseFormStoreToMap(data);
		String veCode = StringUtil.nvl(map.get("veCode"), "");
		if ("".equals(veCode)){
			return ApiResponse.failRsp("10011","供应商代码不能为空！");
		}
		String prodCode = StringUtil.nvl(map.get("prodCode"), "");
		if ("".equals(prodCode)){
			return ApiResponse.failRsp("10012","物料代码不能为空！");
		}
		String machineCode = StringUtil.nvl(map.get("machineCode"), "");
		if ("".equals(machineCode)){
			return ApiResponse.failRsp("10013","机台号不能为空！");
		}
		String version = StringUtil.nvl(map.get("version"), "");
		if ("".equals(version)){
			return ApiResponse.failRsp("10014","版本号不能为空！");
		}
		String indate = StringUtil.nvl(map.get("indate"), "");
		if ("".equals(indate)){
			return ApiResponse.failRsp("10015","请确认获取条码的具体时间！");
		}
		String QRCode = StringUtil.nvl(map.get("QRCode"), "");
		if ("".equals(QRCode)){
			return ApiResponse.failRsp("10016","条码不能为空！");
		}
		if (baseDao.checkIf("ProdQRCode","qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0")){
			baseDao.execute("update ProdQRCode set qr_ldischeck=-1,qr_ldcheckres='OK',qr_ldcheckdate=sysdate where qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0");
			return ApiResponse.successRsp("0","Success","","OK");
		}else {
			baseDao.execute("update ProdQRCode set qr_ldischeck=-1,qr_ldcheckres='NG',qr_ldcheckdate=sysdate where qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0");
			return ApiResponse.successRsp("0", "Success", "", "NG");
		}

	}

	@Override
	public ApiResult<String> checkDCRQRCode(String accessKey, String requestId, String data) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		SpObserver.putSp(AE_MASTER);
		Map<Object, Object> map = BaseUtil.parseFormStoreToMap(data);
		String veCode = StringUtil.nvl(map.get("veCode"), "");
		if ("".equals(veCode)){
			return ApiResponse.failRsp("10011","供应商代码不能为空！");
		}
		String prodCode = StringUtil.nvl(map.get("prodCode"), "");
		if ("".equals(prodCode)){
			return ApiResponse.failRsp("10012","物料代码不能为空！");
		}
		String machineCode = StringUtil.nvl(map.get("machineCode"), "");
		if ("".equals(machineCode)){
			return ApiResponse.failRsp("10013","机台号不能为空！");
		}
		String version = StringUtil.nvl(map.get("version"), "");
		if ("".equals(version)){
			return ApiResponse.failRsp("10014","版本号不能为空！");
		}
		String indate = StringUtil.nvl(map.get("indate"), "");
		if ("".equals(indate)){
			return ApiResponse.failRsp("10015","请确认获取条码的具体时间！");
		}
		String QRCode = StringUtil.nvl(map.get("QRCode"), "");
		if ("".equals(QRCode)){
			return ApiResponse.failRsp("10016","条码不能为空！");
		}
		if (baseDao.checkIf("ProdQRCode","qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0")){
			baseDao.execute("update ProdQRCode set qr_dcrischeck=-1,qr_dcrcheckres='OK',qr_dcrcheckdate=sysdate where qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0");
			return ApiResponse.successRsp("0","Success",requestId,"OK");
		}else {
			baseDao.execute("update ProdQRCode set qr_dcrischeck=-1,qr_dcrcheckres='OK',qr_dcrcheckdate=sysdate where qr_code='"+QRCode+"' and qr_vecode='" + veCode + "' and qr_prodcode='" + prodCode + "' and qr_machinecode='" + machineCode + "' and qr_version='" + version + "' and to_char(qr_date,'yyyy-MM-dd')='"+indate+"' and nvl(qr_isobtained,0)<>0");
			return ApiResponse.successRsp("0", "Success", requestId, "NG");
		}
	}

	@Override
	@Transactional
	public int saveFilePath(String accessKey, String requestId,String path, int size, String filename) {
		String AE_MASTER = checkAccessKey(accessKey, requestId);
		SpObserver.putSp(AE_MASTER);
		int id = baseDao.getSeqId("filepath_seq");
		/**
		 * 文件名含单引号无法下载*/
		filename=filename.replaceAll(",", "，");
		baseDao.execute("INSERT INTO filepath(fp_id,fp_path,fp_size,fp_man,fp_date,fp_name) values(" + id + ",'" + path + "'," + size
				+ ",'" + accessKey+ "'," + DateUtil.parseDateToOracleString(Constant.YMD_HMS, new Date()) + ",'" + filename
				+ "')");
		baseDao.execute("insert into DCRFILEDATA (DF_ID ,DF_DATE,DF_INMAN,DF_ATTACH,DF_FILENAME)" +
				" select DCRFILEDATA_seq.nextval,sysdate,?,?,? from dual ",accessKey,id,filename);
		return id;
	}

	@Override
	public ApiResult<String> uploadDCRFile(String accessKey, String requestId, MultipartFile file) throws IOException {
		try {
			String filename = file.getOriginalFilename();
			String path = saveFile(file);
			int id = saveFilePath(accessKey, requestId,path, (int) file.getSize(), filename);
			return ApiResponse.successRsp("0", "Success", requestId, "");
		}catch (Exception e) {
			e.printStackTrace();
			return ApiResponse.failRsp( "10011","上传失败"+(e.getMessage().length()>400 ? e.getMessage().substring(0,400):e.getMessage()));
		}
	}

	@Override
	public ApiResult<String> DCRLogExcel(String accessKey, String requestId, String data) {
		try {
			String AE_MASTER = checkAccessKey(accessKey, requestId);
			SpObserver.putSp(AE_MASTER);
			Map<Object, Object> store = BaseUtil.parseFormStoreToMap(data);
			store.put("dle_id",baseDao.getSeqId("dcrlogexcel_seq"));
			String formSql = SqlUtil.getInsertSqlByMap(store, "DCRLogExcel");
			baseDao.execute(formSql);
			return ApiResponse.successRsp("0", "Success", requestId, "传输成功!");
		}catch (Exception e) {
			e.printStackTrace();
			return ApiResponse.failRsp( "10020","传输失败"+(e.getMessage().length()>400 ? e.getMessage().substring(0,400):e.getMessage()));
		}
	}

	private String saveFile(MultipartFile file) throws IOException {
	   String path = "/dcr";
	   File filep = new File(path);
	   String fileName = file.getOriginalFilename();
	   if (!filep.isDirectory()) {
		   filep.mkdir();
	   }
	   path = path+File.separator +fileName;
	   FileUtils.copyInputStreamToFile(file.getInputStream(),new File(path));
	   return path;
   }


	private String checkAccessKey(String accessKey,String requestId){
		Object accessSecret_O = baseDao.getFieldDataByCondition("APIEMPLOYEE", "AE_SECRET", "AE_KEY='" + accessKey + "'");
		Object AE_MASTER = baseDao.getFieldDataByCondition("APIEMPLOYEE", "AE_MASTER", "AE_KEY='" + accessKey + "'");
		String accessSecret = accessSecret_O == null ? "" : accessSecret_O.toString();
		// 检查KEY是否合理
		if (StringUtils.isEmpty(accessKey) || StringUtils.isEmpty(accessSecret) || AE_MASTER== null || "".equals(AE_MASTER.toString())) {
			ApiResult apiResult = new ApiResult();
			apiResult.setCode(ErrorMessage.ACCESSKEY_ILLEGAL.getCode());
			apiResult.setMessage(ErrorMessage.ACCESSKEY_ILLEGAL.getMessage());
			apiResult.setRequestId(requestId);
			throw new ApiSystemException(apiResult);
		}
		return AE_MASTER.toString();
	}

}