package com.steering.mes.zgmil.coup;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.transaction.interceptor.MatchAlwaysTransactionAttributeSource;

import CoreFS.SA06.CoreReturnObject;
import CoreFS.SA08.CoreXmlSqlParsersFactory;
import CoreFS.SC01.CoreConstantData;

import com.steering.comm.converter.CoreConverter;
import com.steering.comm.db.DBCommon;
import com.steering.common.EntityTool;
import com.steering.common.StringTool;
import com.steering.mes.zgmil.entity.MatBcMEntity;
import com.steering.mes.zgmil.entity.MilPlanEntity3;
import com.steering.mes.zgmil.entity.MilSlabScrapEntity;

public class FrmQueryRidResult extends DBCommon {

	/**
	 * 查询
	 * 
	 * @param parm
	 * @param startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryData(ArrayList<String> parm,
			String startTim, String endTim, String flag, String[] plineCodes) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmQueryRidResult.doQueryData");
		if (flag.equals("0")) {
			sql += " AND T.FLAG = '0'";
		} else if (flag.equals("1")) {
			sql += " AND T.FLAG = '1'";
		}
		if (!startTim.equals("")) {
			sql += " AND TIME >= to_date('" + startTim
					+ "','YYYY-MM-DD HH24:MI:SS') AND TIME <= to_date('"
					+ endTim + "','YYYY-MM-DD HH24:MI:SS')";
		}
		sql += CoreConverter.toSqlConditionString("T.PLINE_CODE", plineCodes);
		return this.getData(sql, parm.toArray());
	}

	/**
	 * 返库
	 * 
	 * @param parm
	 * @param userName
	 * @param userGroup
	 * @param userOrder
	 * @return
	 */
	public CoreReturnObject updateResult(ArrayList<String> parm,
			ArrayList<String> parm2) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
		String sql = "";
		int acount = 0;
		
		ResultSet rs = null;
		Date now = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat dateYearMat = new SimpleDateFormat("yyyyMM");
		String systime = dateFormat.format(now);
		String bathYear = dateYearMat.format(now);
		String yearMoth = "";//改25号结转
		String wgt = "";//重量
		String matNo = "";
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updateSql = CoreXmlSqlParsersFactory
					.getSql("FrmQueryRidResult.updateResult"); // 修改管坯剔除表状态
			PreparedStatement updatePsta = connection
					.prepareStatement(updateSql);

			String sql1 = CoreXmlSqlParsersFactory
					.getSql("FrmQueryRidResult.returnLibrary"); // 返库
			PreparedStatement returnLibrary = connection.prepareStatement(sql1);
			
            ArrayList<String> sqllist = new ArrayList<String>();
			for (String str : parm) {
				MilSlabScrapEntity milSlabTity = EntityTool.getEntityByJSONStr(
						str, MilSlabScrapEntity.class);
				sql = CoreXmlSqlParsersFactory.getSql("FrmQueryRidResult.selectMilPlan");//查询炉计划信息
				sql = EntityTool.fillSqlByEntity(sql, milSlabTity);
				psta = connection.prepareStatement(sql);
				rs = psta.executeQuery();
				List<MilPlanEntity3> milPlan = EntityTool.getEntitysByRs(rs, MilPlanEntity3.class);
				rs.close();
				psta.close();
				
				yearMoth = StringTool.GetBalYear(now,StringTool.GetSupplyUnit(milSlabTity.getPlineCode()));//改25号结转
				/*sql = CoreXmlSqlParsersFactory.getSql("FrmQueryRidResult.selectMatNoZg");//生成废品材料号
				psta = connection.prepareStatement(sql);
				psta.setString(1, milSlabTity.getJudgeStoveNo().substring(0,6));
				psta.setString(2, milSlabTity.getJudgeStoveNo().substring(0,6));
				psta.setString(3, milSlabTity.getJudgeStoveNo().substring(0,6));
				psta.setString(4, milSlabTity.getJudgeStoveNo().substring(0,6));
				rs = psta.executeQuery();
				if(rs.next())
				{
					matNo = rs.getString("MAT_NO");
				}
				rs.close();
				psta.close();*/
				
				
				//查询重量
				/*if(milSlabTity.getProcessNoCode().equals("0"))//母坯
				{
					sql = CoreXmlSqlParsersFactory.getSql("FrmQueryRidResult.selectWgtM");
					
				}else //单倍坯(母管)
				{
					sql = CoreXmlSqlParsersFactory.getSql("FrmQueryRidResult.selectWgt");
				}
				sql = EntityTool.fillSqlByEntity(sql, milSlabTity);
				psta = connection.prepareStatement(sql);
				rs = psta.executeQuery();
				if(rs.next())
				{
					wgt = rs.getString("WEIGHT");
				}
				rs.close();
				psta.close();*/
				
				
				MatBcMEntity matBcEntity = new MatBcMEntity();
				matBcEntity.setJudgeStoveNo(milPlan.get(0).getJudgeStoveNo().substring(0,6));
				matBcEntity.setBatchNo("F");
				matBcEntity.setBatchGroudNo("001");
				matBcEntity.setActCount("1");
				matBcEntity.setActWeight(wgt);
				matBcEntity.setActTheoryWeight(wgt);
				matBcEntity.setMaterialNo(milPlan.get(0).getOutwlId());
				matBcEntity.setMaterialName(milPlan.get(0).getOutwlDesc());
				matBcEntity.setCreateTime(systime);
				matBcEntity.setMatNo(matNo);
				matBcEntity.setProductFlag("801401");
				matBcEntity.setMatStatus("80150101");
				matBcEntity.setPlineCode(milSlabTity.getPlineCode());
				if(milSlabTity.getPlineCode().equals("C008"))
				{
					matBcEntity.setPlineName("250机组");
					matBcEntity.setBelongCode("002001016003");
					matBcEntity.setBelongName("250机组");
					matBcEntity.setBelongCodeResponse("002001016");
					matBcEntity.setBelongNameResponse("轧管一部");
					matBcEntity.setStorageNo("XCC2");
					
				}else if(milSlabTity.getPlineCode().equals("C009"))
				{
					matBcEntity.setPlineName("258机组");
					matBcEntity.setBelongCode("002001016002");
					matBcEntity.setBelongName("258机组");
					matBcEntity.setBelongCodeResponse("002001016");
					matBcEntity.setBelongNameResponse("轧管一部");
					matBcEntity.setStorageNo("XCC1");
				}else if(milSlabTity.getPlineCode().equals("C010"))
				{
					matBcEntity.setPlineName("168机组");
					matBcEntity.setBelongCode("002001017003");
					matBcEntity.setBelongName("168机组");
					matBcEntity.setBelongCodeResponse("002001017");
					matBcEntity.setBelongNameResponse("轧管二部");
					matBcEntity.setStorageNo("XCC4");
				}else if(milSlabTity.getPlineCode().equals("C017"))
				{
					matBcEntity.setPlineName("460机组");
					matBcEntity.setBelongCode("002001018003");
					matBcEntity.setBelongName("460机组");
					matBcEntity.setBelongCodeResponse("002001018");
					matBcEntity.setBelongNameResponse("轧管三部");
					matBcEntity.setStorageNo("XCC3");
				}else if(milSlabTity.getPlineCode().equals("C072"))
				{
					matBcEntity.setPlineName("天淮508机组");
					matBcEntity.setBelongCode("002023018001");
					matBcEntity.setBelongName("天淮508机组");
					matBcEntity.setBelongCodeResponse("002023018");
					matBcEntity.setBelongNameResponse("热轧作业区");
					matBcEntity.setStorageNo("BZJ3");
				}
				matBcEntity.setQcmStaus("22");
				matBcEntity.setHeatPlanNo(milPlan.get(0).getId());
				matBcEntity.setZgStoveSqe(milSlabTity.getJudgeStoveNo());
				matBcEntity.setProduccode(milPlan.get(0).getVarietiesCode());
				matBcEntity.setProducname(milPlan.get(0).getVarieties());
				matBcEntity.setSteelcode(milPlan.get(0).getSteelcode());
				matBcEntity.setSteelname(milPlan.get(0).getSteelname());
				matBcEntity.setStdStyle(milPlan.get(0).getStdStyle());
				matBcEntity.setStdStyleDesc(milPlan.get(0).getStdStyleDesc());
				matBcEntity.setSpecCode(milPlan.get(0).getPipeSizeCode());
				matBcEntity.setSpecName(milPlan.get(0).getPipeSize().replace("*", "x"));
				matBcEntity.setModelCode(milPlan.get(0).getBuckleTypeCode());
				matBcEntity.setModelDesc(milPlan.get(0).getBuckleType());
				matBcEntity.setGradecode(milPlan.get(0).getGradecode());
				matBcEntity.setGradename(milPlan.get(0).getGradename());
				matBcEntity.setStdCode(milPlan.get(0).getRollStandradCode());
				matBcEntity.setStdName(milPlan.get(0).getRollStandrad());
				matBcEntity.setActDimater(milPlan.get(0).getOutdiameter());
				matBcEntity.setActHeight(milPlan.get(0).getWallthick());
				matBcEntity.setActLenMin(milPlan.get(0).getLenRollMin());
				matBcEntity.setActLenMax(milPlan.get(0).getLenRollMax());
				matBcEntity.setActLen(milPlan.get(0).getAimlength());
				matBcEntity.setLotNo(milPlan.get(0).getLotNo());
				matBcEntity.setStoveNo(milPlan.get(0).getStoveNo());
				matBcEntity.setProOrderNo(milPlan.get(0).getContractNo());
				matBcEntity.setTarget(milPlan.get(0).getTarget());
				matBcEntity.setProcessStatus(milPlan.get(0).getProcessStatus());
				matBcEntity.setFlagStoveNoZg("000000");
				matBcEntity.setInstockTime(systime);
				matBcEntity.setProcessCdoe("D");
				matBcEntity.setProcessDesc("轧制");
				matBcEntity.setJudgeResultCode("40740705");
				matBcEntity.setJudgeResult("废品");
				if(milSlabTity.getProcessNoCode().equals("0 ") || milSlabTity.getProcessNoCode().equals("1 ")||milSlabTity.getProcessNoCode().equals("2 "))
				{
					matBcEntity.setScrapType("1");
				}else
				{
					matBcEntity.setScrapType("0");
				}
				
				matBcEntity.setLocationNo(matBcEntity.getStorageNo() + "10101");
				matBcEntity.setLaryNo("1");
				matBcEntity.setCreateName(parm2.get(0));
				
				
				updatePsta.setString(1, parm2.get(2));
				updatePsta.setString(2, parm2.get(1));
				updatePsta.setString(3, parm2.get(0));
				updatePsta.setString(4, "500202");
				updatePsta.setString(5, systime);
				updatePsta.setString(6, milSlabTity.getActLen());
				updatePsta.setString(7, milSlabTity.getJudgeStoveNo());
				updatePsta.setString(8, milSlabTity.getMatNo());
				updatePsta.addBatch();

				//返库
				//if (milSlabTity.getHandleResult().equals("500202")) {

					sql = CoreXmlSqlParsersFactory
							.getSql("FrmQueryRidResult.SelectAcount");// 查询支数
					psta = connection.prepareStatement(sql);
					psta.setString(1, milSlabTity.getJudgeStoveNo());
					psta.setString(2, milSlabTity.getProPlanId());
					psta.setString(3, milSlabTity.getGxPlanNo());
					rs = psta.executeQuery();
					if (rs.next()) {
						acount = Integer.parseInt(rs.getString("ACOUNT"));
					}
					rs.close();
					psta.close();
					if (acount == 0) {
						cro.setV_errMsg("返库失败！");
						return cro;
					}
					
					returnLibrary.setString(1, milSlabTity.getJudgeStoveNo());
					returnLibrary.setString(2, milSlabTity.getProPlanId());
					returnLibrary.setString(3, milSlabTity.getGxPlanNo());
					returnLibrary.setString(4, milSlabTity.getPlineCode());
					returnLibrary.setString(5, parm2.get(0));
					returnLibrary.setString(6, milSlabTity.getMatNo());
					returnLibrary.setString(7, milSlabTity.getProcessNoCode());
					returnLibrary.setString(8, parm2.get(4));
					returnLibrary.setString(9, parm2.get(5));
					returnLibrary.setString(10, parm2.get(6));
					returnLibrary.setString(11, milSlabTity.getActLen());
					returnLibrary.setString(12, parm2.get(7));
					returnLibrary.setString(13, yearMoth);
					returnLibrary.addBatch();
				/*}else if(milSlabTity.getHandleResult().equals("500201"))//剔废
				{
					String insertMatBcSql = CoreXmlSqlParsersFactory.getSql("FrmQueryRidResult.insertMatBc");
					insertMatBcSql = EntityTool.fillSqlByEntity(insertMatBcSql, matBcEntity);
					sqllist.add(insertMatBcSql);
				}*/
			}
			updatePsta.executeBatch();
			returnLibrary.executeBatch();
			cro = this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(sqllist);
			if(cro.getV_errMsg().equals(""))
			{
				connection.commit();
				cro.setV_errMsg("返库成功!");
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
			if (connection != null) {
				try {
					cro.setV_errMsg(e.getMessage());
					connection.rollback(); // 回滚事务
				} catch (Exception e2) {
				}
			}
		} finally {
			try {
				if (connection != null && !connection.isClosed()) {
					connection.close();
				}
			} catch (Exception e2) {
			}
		}
		return cro;
	}

	/**
	 * 撤销返库
	 * 
	 * @param parm
	 * @return
	 */
	public CoreReturnObject regoodBack(ArrayList<String> parm,
			ArrayList<String> parm2) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
		String sql = "";
		int acount = 0;
		ResultSet rs = null;
		Date now = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		String systime = dateFormat.format(now);
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updateSql = CoreXmlSqlParsersFactory
					.getSql("FrmQueryRidResult.updateResultBack"); // 修改管坯剔除表状态
			PreparedStatement updateResultBack = connection
					.prepareStatement(updateSql);

			String sql1 = CoreXmlSqlParsersFactory
					.getSql("FrmQueryRidResult.returnLibraryBack"); // 撤销返库
			PreparedStatement returnLibraryBack = connection
					.prepareStatement(sql1);

			for (String str : parm) {
				MilSlabScrapEntity milSlabTity = EntityTool.getEntityByJSONStr(
						str, MilSlabScrapEntity.class);

				sql = CoreXmlSqlParsersFactory
						.getSql("FrmQueryRidResult.SelectIsFC");// 验证是否是返库的炉号
				psta = connection.prepareStatement(sql);
				psta.setString(1, milSlabTity.getJudgeStoveNo());
				psta.setString(2, milSlabTity.getProPlanId());
				psta.setString(3, milSlabTity.getGxPlanNo());
				psta.setString(4, milSlabTity.getMatNo());
				rs = psta.executeQuery();
				if (rs.next()) {
					acount = Integer.parseInt(rs.getString("ACOUNT"));
				}
				rs.close();
				psta.close();
				if (acount == 0) {
					cro.setV_errMsg("选择的炉号：【" + milSlabTity.getJudgeStoveNo()
							+ "】没有返库，不能撤销！");
					return cro;
				}

				sql = CoreXmlSqlParsersFactory
						.getSql("FrmQueryRidResult.SelectIsNoChang");// 验证返库的炉号是否还在库里或者用作其它
				psta = connection.prepareStatement(sql);
				psta.setString(1, milSlabTity.getNewMatNo());
				rs = psta.executeQuery();
				if (rs.next()) {
					acount = Integer.parseInt(rs.getString("ACOUNT"));
				}
				rs.close();
				psta.close();
				if (acount == 0) {
					cro.setV_errMsg("返库管坯已经用掉了，不能撤销！");
					return cro;
				}

				updateResultBack.setString(1, parm2.get(2));
				updateResultBack.setString(2, parm2.get(1));
				updateResultBack.setString(3, parm2.get(0));
				updateResultBack.setString(4, systime);
				updateResultBack.setString(5, milSlabTity.getJudgeStoveNo());
				updateResultBack.setString(6, milSlabTity.getMatNo());
				updateResultBack.addBatch();
				
				returnLibraryBack.setObject(1, milSlabTity.getNewMatNo());
				returnLibraryBack.addBatch();
			}
			updateResultBack.executeBatch();
			returnLibraryBack.executeBatch();

			connection.commit();
			cro.setV_errMsg("撤销返库成功!");
		} catch (Exception e) {
			System.out.println(e.getMessage());
			if (connection != null) {
				try {
					cro.setV_errMsg(e.getMessage());
					connection.rollback(); // 回滚事务
				} catch (Exception e2) {
				}
			}
		} finally {
			try {
				if (connection != null && !connection.isClosed()) {
					connection.close();
				}
			} catch (Exception e2) {
			}
		}
		return cro;
	}
	
	/**
	 * 修改备注
	 */
	public CoreReturnObject doSaveMemo(ArrayList<String> parm) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updateSql = CoreXmlSqlParsersFactory
					.getSql("FrmQueryRidResult.doSaveMemo"); // 修改管坯剔除表状态
			PreparedStatement updateResultBack = connection
					.prepareStatement(updateSql);
			for (String str : parm) {
				MilSlabScrapEntity milSlabTity = EntityTool.getEntityByJSONStr(
						str, MilSlabScrapEntity.class);
				updateResultBack.setString(1, milSlabTity.getRemark());
				updateResultBack.setString(2, milSlabTity.getProcessNoCode());
				updateResultBack.setString(3, milSlabTity.getJudgeStoveNo());
				updateResultBack.setString(4, milSlabTity.getMatNo());
				updateResultBack.addBatch();
			}
			updateResultBack.executeBatch();
			connection.commit();
			cro.setV_errMsg("操作成功!");
		}
		catch (Exception e) {
			// TODO: handle exception
		}
		finally {
			try {
				if (connection != null && !connection.isClosed()) {
					connection.close();
				}
			} catch (Exception e2) {
			}
		}
		return cro;
	}
}
