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 com.steering.comm.db.DBCommon;

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

import com.steering.common.EntityTool;
import com.steering.mes.zgmil.entity.MilSlabScrapSeqEntity;

public class FrmStraighteningResult extends DBCommon {

	public CoreReturnObject getSingleMainPlanTC1(
			com.steering.mes.zgmil.entity.MilPlan MilPlan, String flagstatus) {
		// return this.getDataBySqlID(
		// "MilStraight.PlanQueryCKTC1",
		// new Object[] {MilPlan.getJudgeStoveNo(),flagstatus});

		return this.getDataBySqlID("MilStraight.PlanQueryCKTC1",
				new Object[] { MilPlan.getJudgeStoveNo() });
	}

	public CoreReturnObject getSingleMainPlanTC2(
			com.steering.mes.zgmil.entity.MilPlan MilPlan, String flagstatus) {
		// return this.getDataBySqlID(
		// "MilStraight.PlanQueryCKTC2",
		// new Object[] {MilPlan.getJudgeStoveNo(),flagstatus});

		return this.getDataBySqlID("MilStraight.PlanQueryCKTC2",
				new Object[] { MilPlan.getJudgeStoveNo() });
	}

	public CoreReturnObject getSingleMainPlanTC3(
			com.steering.mes.zgmil.entity.MilPlan MilPlan, String flagstatus) {
		// return this.getDataBySqlID(
		// "MilStraight.PlanQueryCKTC3",
		// new Object[] {MilPlan.getJudgeStoveNo(),flagstatus});
		return this.getDataBySqlID("MilStraight.PlanQueryCKTC3",
				new Object[] { MilPlan.getJudgeStoveNo() });
	}

	public CoreReturnObject getPerResult(String JudgeStoveNo, String proPlanId,
			String gxPlanNo) {
		return this.getDataBySqlID("MilStraight.SingleMilStraightQuery",
				new Object[] { JudgeStoveNo, proPlanId, gxPlanNo });
	}

	public CoreReturnObject UpdateStraighteningStatue(
			com.steering.mes.zgmil.entity.MilStraighteningResultEntity StraighteningResult) {
		ArrayList<String> sqllist = new ArrayList<String>();

		String sql = CoreXmlSqlParsersFactory
				.getSql("MilStraight.Addstraight_result");
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdatestraightStatue");
		sql1 = EntityTool.fillSqlByEntity(sql1, StraighteningResult);
		sql = EntityTool.fillSqlByEntity(sql, StraighteningResult);
		sqllist.add(sql);
		sqllist.add(sql1);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject scrapFurMat(MilSlabScrapSeqEntity MilScrapSlab,
			String Flage) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 新增剔除管号序列
		String sql = CoreXmlSqlParsersFactory
				.getSql("MilStraight.AddScrapSlabSeq");
		// 修改加热炉表物料状态
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateFurStatue");
		// 修改加热炉实绩表剔除支数
		String sql2 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateFurScrapNum");
		String sql3 = "";
		if (Flage.equals("1")) {
			sql3 = CoreXmlSqlParsersFactory
					.getSql("MilStraight.UpdateFurScrapNum1");
		}
		if (Flage.equals("2")) {
			sql3 = CoreXmlSqlParsersFactory
					.getSql("MilStraight.UpdateFurScrapNum2");
		}
		if (Flage.equals("3")) {
			sql3 = CoreXmlSqlParsersFactory
					.getSql("MilStraight.UpdateFurScrapNum3");
		}
		sql = EntityTool.fillSqlByEntity(sql, MilScrapSlab);
		sql1 = EntityTool.fillSqlByEntity(sql1, MilScrapSlab);
		sql2 = EntityTool.fillSqlByEntity(sql2, MilScrapSlab);
		sql3 = EntityTool.fillSqlByEntity(sql3, MilScrapSlab);
		sqllist.add(sql);
		sqllist.add(sql1);
		sqllist.add(sql2);
		sqllist.add(sql3);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject saveStraightening(
			com.steering.mes.zgmil.entity.MilStraighteningResultEntity MilStraighteningResultEntity,
			String saveflag) {

		String sql = "";
		if (saveflag.equals("0")) {
			sql = CoreXmlSqlParsersFactory
					.getSql("MilStraight.UpdateSTRAIGHTENINGStatue");
		}
		if (saveflag.equals("1")) {
			sql = CoreXmlSqlParsersFactory
					.getSql("MilStraight.UpdateSTRAIGHTENINGStatuesaveflag");
		}
		sql = EntityTool.fillSqlByEntity(sql, MilStraighteningResultEntity);
		return this.setData(sql);

	}

	public CoreReturnObject saveStraighteningNODE(
			com.steering.mes.zgmil.entity.MilStraighteningResultEntity MilStraighteningResultEntity) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 保存实绩
		String sql = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateSTRAIGHTENINGStatue");
		// 修改结炉状态及时间
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateNodeFurStatue");
		// 修改主表状态
		String sql2 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateMainStatus");
		String sql3 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateRackShift");
		String sql4 = CoreXmlSqlParsersFactory
				.getSql("MilStraight.UpdateResultShift");
		// 更新进程表mil_processes数据
		String updateProcess = CoreXmlSqlParsersFactory
				.getSql("FrmInOnlineStore.insertMilProcess2");
		sql = EntityTool.fillSqlByEntity(sql, MilStraighteningResultEntity);
		sql1 = EntityTool.fillSqlByEntity(sql1, MilStraighteningResultEntity);
		sql2 = EntityTool.fillSqlByEntity(sql2, MilStraighteningResultEntity);
		sql3 = EntityTool.fillSqlByEntity(sql3, MilStraighteningResultEntity);
		sql4 = EntityTool.fillSqlByEntity(sql4, MilStraighteningResultEntity);
		updateProcess = EntityTool.fillSqlByEntity(updateProcess,
				MilStraighteningResultEntity);
		sqllist.add(sql);
		sqllist.add(sql1);
		sqllist.add(sql2);
		sqllist.add(sql3);
		sqllist.add(sql4);
		sqllist.add(updateProcess);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);

	}

	public CoreReturnObject GetSingleJRLResult(String JudgeStoveNo,
			String proPlanId, String gxPlanNo) {
		return this.getDataBySqlID("MilStraight.SingleFeedSawResult",
				new Object[] { JudgeStoveNo, proPlanId, gxPlanNo });
	}

	public CoreReturnObject GetStraightResultNodeQuery(String PliceCode,
			String Shift, String Group, String StartTime, String EndTime) {
		return this.getDataBySqlID("MilStraight.GetStraightResultNodeQuery",
				new Object[] { PliceCode, Shift, Group, StartTime, EndTime });
	}

	public CoreReturnObject GetRackCounts(String heatno, Integer seq,
			String colno, String proPlanId, String gxPlanNo) {
		String sql = "select count(*) AS COUNTSS from mil_straigh_rack t where t.judge_stove_no ='";
		sql += heatno + "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
				+ "','/')";
		sql += " AND T.SEQ< " + seq + " AND T.COL_NO= '" + colno + "'";
		return this.getData(sql);
	}

	public CoreReturnObject GetStraightKG_Flag(String heatno, String proPlanId,
			String gxPlanNo) {
		String sql = "SELECT T.KG_FLAG AS KG_FLAG FROM MIL_STRAIGHTENING_RESULT T WHERE T.JUDGE_STOVE_NO='";
		sql += heatno + "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
				+ "','/')";
		return this.getData(sql);
	}

	public CoreReturnObject Qunum(
			com.steering.mes.zgmil.entity.MilStraighteningResultEntity StraighteningResult) {
		String sql = "";
		sql = CoreXmlSqlParsersFactory.getSql("MilStraight.Qunum");
		sql = EntityTool.fillSqlByEntity(sql, StraighteningResult);
		return this.setData(sql);
	}

	/***
	 * 查询探伤是否有下线
	 * 
	 * @param jugeNo
	 * @return
	 */
	public CoreReturnObject doQueryOffline(String jugeNo, String proPlanId,
			String gxPlanNo) {
		return this.getDataBySqlID("FrmStraighteningResult.doQueryOffline",
				new Object[] { jugeNo, proPlanId, gxPlanNo });
	}

	/**
	 * 查询矫直离线数据是否返线
	 * 
	 * @param jugeNo
	 * @return
	 */
	public CoreReturnObject doQueryStraighteningOfflineBack(String jugeNo,
			String proPlanId, String gxPlanNo) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmStraighteningResult.doQueryStraighteningOfflineBack");
		return this.getData(sql, new Object[] { jugeNo, proPlanId, gxPlanNo });
	}

	/***
	 * 结炉赋值材料号到剔废表中
	 * 
	 * @param JudgeStoveNo
	 * @param ProcessNo
	 * @return
	 */
	public CoreReturnObject updateMilSlab(String JudgeStoveNo,
			String processNo, String proPlanId, String gxPlanNo) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
		String sql = "";
		ResultSet rs = null;
		Date now = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		String systime = dateFormat.format(now);
		int totalCount = 0;// 异常支数
		int processCount = 0;// 进程表未交库支数
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			// String updateRackMatNoSql =
			// CoreXmlSqlParsersFactory.getSql("FrmStraighteningResult.updateRackMatNo");//更换剔废，离线材料号
			// PreparedStatement updateRackMatNoPsta =
			// connection.prepareStatement(updateRackMatNoSql);

			sql = CoreXmlSqlParsersFactory.getSql("updateMilSlab.SelectRack");// 查询异常支数
			if (processNo.equals("9")) {
				sql = sql.replace("#RACK#", "MIL_STRAIGH_RACK");
			} else if (processNo.equals("10")) {
				sql = sql.replace("#RACK#", "MIL_TESTING_RACK");
			} else if (processNo.equals("11")) {
				sql = sql.replace("#RACK#", "MIL_QA_RACK");
			}
			psta = connection.prepareStatement(sql);
			psta.setString(1, JudgeStoveNo);
			psta.setString(2, proPlanId);
			psta.setString(3, gxPlanNo);
			rs = psta.executeQuery();
			if (rs.next()) {
				totalCount = Integer.parseInt(rs.getString("ACOUNT"));
			}
			rs.close();
			psta.close();

			sql = CoreXmlSqlParsersFactory
					.getSql("updateMilSlab.SelectProcess");// 查询进程表支数
			psta = connection.prepareStatement(sql);
			psta.setString(1, JudgeStoveNo);
			psta.setString(2, proPlanId);
			psta.setString(3, gxPlanNo);
			rs = psta.executeQuery();
			if (rs.next()) {
				processCount = Integer.parseInt(rs.getString("ACOUNT"));
			}
			rs.close();
			psta.close();

			/*
			 * if (totalCount > processCount) {
			 * cro.setV_errMsg("异常支数大于进程表合格未交库支数,请交库回退部分支数!"); return cro; }
			 */

			String updateMilSlabSql = CoreXmlSqlParsersFactory
					.getSql("FrmStraighteningResult.updateMilSlab");
			if (processNo.equals("9")) {
				updateMilSlabSql = updateMilSlabSql.replace("#RACK#",
						"MIL_STRAIGH_RACK");
			} else if (processNo.equals("10")) {
				updateMilSlabSql = updateMilSlabSql.replace("#RACK#",
						"MIL_TESTING_RACK");
			} else if (processNo.equals("11")) {
				updateMilSlabSql = updateMilSlabSql.replace("#RACK#",
						"MIL_QA_RACK");
			}
			PreparedStatement updateMilSlabPsta = connection
					.prepareStatement(updateMilSlabSql);
			updateMilSlabPsta.setString(1, JudgeStoveNo);
			updateMilSlabPsta.setString(2, proPlanId);
			updateMilSlabPsta.setString(3, gxPlanNo);
			updateMilSlabPsta.setString(4, processNo);
			updateMilSlabPsta.addBatch();

			updateMilSlabPsta.executeBatch();
			cro.setV_errMsg("修改成功!");
			connection.commit();
		} 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;
	}

	/***
	 * 结炉写进程表
	 * 
	 * @return
	 */
	public CoreReturnObject updateProcessL(String JudgeStoveNo,
			String ProcessNo, String opProcessNo, String proPlanId,
			String gxPlanNo, String lotNo, String plineCode) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
		String sql = "";
		ResultSet rs = null;
		Date now = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		String systime = dateFormat.format(now);
		String m_matNo = "";
		String portQualifNum = ""; // 可交库支数
		String QualifNum = ""; // 已交库支数
		try {
			connection.setAutoCommit(false); // 取消自动提交事务

			sql = CoreXmlSqlParsersFactory.getSql("updateProcessL.selectScrap");
			psta = connection.prepareStatement(sql);
			psta.setString(1, JudgeStoveNo);
			psta.setString(2, ProcessNo);
			psta.setString(3, proPlanId);
			psta.setString(4, gxPlanNo);
			rs = psta.executeQuery();
			List<MilSlabScrapSeqEntity> list = EntityTool.getEntitysByRs(rs,
					MilSlabScrapSeqEntity.class);

			rs.close();
			psta.close();

			// 可交库支数
			sql = CoreXmlSqlParsersFactory
					.getSql("updateProcessL.SelectPortBacthSampleNum");
			psta = connection.prepareStatement(sql);
			psta.setString(1, JudgeStoveNo);
			psta.setString(2, proPlanId);
			psta.setString(3, gxPlanNo);
			rs = psta.executeQuery();
			if (rs.next()) {
				portQualifNum = rs.getString("ACTCOUNT");
			}
			rs.close();
			psta.close();

			// 已交库支数
			sql = CoreXmlSqlParsersFactory
					.getSql("updateProcessL.SelectQualifNum");
			psta = connection.prepareStatement(sql);
			psta.setString(1, JudgeStoveNo);
			psta.setString(2, proPlanId);
			psta.setString(3, gxPlanNo);
			rs = psta.executeQuery();
			if (rs.next()) {
				QualifNum = rs.getString("QUALIFIED_NUM");
			}
			rs.close();
			psta.close();
			if(QualifNum != null &&  !QualifNum.equals("")){
				if (Integer.parseInt(QualifNum) > Integer.parseInt(portQualifNum)) {
					cro.setV_errMsg("离线/剔废支数已不足,需交库点撤回部分支数!");
					return cro;
				}
			}

			for (int i = 0; i < list.size(); i++) {
				MilSlabScrapSeqEntity milSlabSeq = list.get(i);
				if (milSlabSeq.getOfflineType().equals("")) {
					milSlabSeq.setOfflineType("0");
				}
				String updateProcessSql = CoreXmlSqlParsersFactory
						.getSql("updateProcessL.UpdateFlag");// 结炉剔除进程表中数据
				updateProcessSql = updateProcessSql.replace("#OP_PROCESS#",
						opProcessNo);
				updateProcessSql = EntityTool.fillSqlByEntity(updateProcessSql,
						milSlabSeq);
				PreparedStatement updateProcessPsta = connection
						.prepareStatement(updateProcessSql);
				updateProcessPsta.executeUpdate();
				updateProcessPsta.close();

			}

			String insertMatScrapSql = CoreXmlSqlParsersFactory
					.getSql("FrmOffLine.insertMatScrap"); // 废品入半成品库存
			PreparedStatement insertMatScrapPsta = connection
					.prepareStatement(insertMatScrapSql);
			insertMatScrapPsta.setString(1, JudgeStoveNo);
			insertMatScrapPsta.setString(2, proPlanId);
			insertMatScrapPsta.setString(3, gxPlanNo);
			insertMatScrapPsta.setString(4, plineCode);
			insertMatScrapPsta.setString(5, ProcessNo);
			insertMatScrapPsta.addBatch();

			insertMatScrapPsta.executeBatch();

			cro.setV_errMsg("修改成功!");
			connection.commit();

		} 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 jugeNo
	 * @param ProcessNo
	 * @return
	 */
	public CoreReturnObject assignmentMatNo(String jugeNo, String ProcessNo,
			String plineCode, String proPlanId, String gxPlanNo) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
		String sql = "";
		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 insertScrapSql = CoreXmlSqlParsersFactory
					.getSql("FrmStraighteningResult.insertScrapSlab");
			String updateMatNoSql = CoreXmlSqlParsersFactory
					.getSql("FrmStraighteningResult.assignmentMatNo");
			PreparedStatement updateMatNoPsta = connection
					.prepareStatement(updateMatNoSql);
			PreparedStatement insertScrapPsta = connection
					.prepareStatement(insertScrapSql);
			updateMatNoPsta.setString(1, jugeNo);
			updateMatNoPsta.setString(2, proPlanId);
			updateMatNoPsta.setString(3, gxPlanNo);
			updateMatNoPsta.setString(4, jugeNo);
			updateMatNoPsta.setString(5, proPlanId);
			updateMatNoPsta.setString(6, gxPlanNo);
			updateMatNoPsta.addBatch();

			insertScrapPsta.setString(1, jugeNo);
			insertScrapPsta.setString(2, proPlanId);
			insertScrapPsta.setString(3, gxPlanNo);
			insertScrapPsta.setString(4, plineCode);
			insertScrapPsta.setString(5, proPlanId);
			insertScrapPsta.setString(6, gxPlanNo);
			insertScrapPsta.addBatch();
			insertScrapPsta.executeBatch();
			updateMatNoPsta.executeBatch();
			cro.setV_errMsg("修改成功!");
			connection.commit();
		} 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 jugeNo
	 * @return
	 */
	public CoreReturnObject getSingePlanFlag(String jugeNo, String proPlanId,
			String gxPlanNo) {
		return this.getDataBySqlID("FrmStraighteningResult.getSingePlanFlag",
				new Object[] { jugeNo, proPlanId, gxPlanNo });
	}

	/***
	 * 矫直剔废时占用进程表
	 * 
	 * @param MilScrapSlab
	 * @param opcessNo
	 * @return
	 */
	public CoreReturnObject updatePorcessOcupStaus(
			MilSlabScrapSeqEntity MilScrapSlab, String opcessNo) {
		ArrayList<String> sqllist = new ArrayList<String>();
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmStraighteningResult.updatePorcessOcupStaus");
		sql = EntityTool.fillSqlByEntity(sql, MilScrapSlab);
		sqllist.add(sql);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}
}
