package com.steering.mes.zgmil.coup;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

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

import com.steering.comm.db.DBCommon;

/**
 * 复取样
 * 
 * @author Administrator
 * 
 */
public class FrmMilReSample extends DBCommon {

	/**
	 * 查询
	 * 
	 * @param date
	 * @param endDate
	 * @param judgeStoveNo
	 * @param lotNo
	 * @param reSample
	 * @param pline
	 * @return
	 */
	public CoreReturnObject getFrmMilReSample(String date, String endDate,
			String judgeStoveNo, String lotNo, String reSample, String pline) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmMilReSample.getFrmMilReSample");
		if (!date.equals("")) {
			sql += " AND T1.FEED_TIME BETWEEN TO_DATE('" + date
					+ "' ,'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + endDate
					+ "' ,'YYYY-MM-DD HH24:MI:SS') ";
		}
		if (!judgeStoveNo.equals("")) {
			sql += "  AND T1.JUDGE_STOVE_NO = '" + judgeStoveNo + "-01' ";
		}
		if (!lotNo.equals("")) {
			sql += " AND T1.LOT_NO = '" + lotNo + "' ";
		}

		if (!reSample.equals("")) {
			sql += " AND P.RE_GET_SAMPLE_STATUS = '" + reSample + "' ";
		}

		if (!pline.equals("")) {
			sql += " AND T1.PLINE_CODE = '" + pline + "' ";
		}
		sql += " GROUP BY REGEXP_REPLACE(T1.JUDGE_STOVE_NO, '-.*', ''),T1.LOT_NO,T1.PLINE_CODE,"
				+ "T1.CONTRACT_NO,T1.VARIETIES,T1.STEELNAME,T1.GRADENAME,REPLACE(T1.PIPE_SIZE, '*', 'x'),"
				+ "T1.BUCKLE_TYPE,T1.LENGTH_RANGE,T1.ROLL_STANDRAD,T1.PRO_PLAN_ID,T1.GX_PLAN_NO,T1.FEED_TIME,T1.ID,T1.NODE_STATUS";
		sql += " ORDER BY FEED_TIME ASC";
		return this.getData(sql);
	}

	/**
	 * 根据炉号、批号、产线查进程表数据
	 * @param judgeStoveNo
	 * @param lotNo
	 * @param pline
	 * @return
	 */
	public CoreReturnObject getPortMilBatchSampleResult(String judgeStoveNo,
			String lotNo, String pline, String proPlanId, String gxPlanNo) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmMilReSample.getPortMilBatchSampleResult");
		if (!judgeStoveNo.equals("")) {
			sql += "  AND REGEXP_REPLACE(T.JUDGE_STOVE_NO, '-.*', '') = '" + judgeStoveNo
					+ "' ";
		}
		if (!lotNo.equals("")) {
			sql += " AND T.LOT_NO = '" + lotNo + "' ";
		}
		if (!pline.equals("")) {
			sql += " AND T.PLINE_CODE = '" + pline + "' ";
		}
		if (!proPlanId.equals("")) {
			sql += " AND T.PRO_PLAN_ID = '" + proPlanId + "' ";
		}
		if (!gxPlanNo.equals("")) {
			sql += " AND T.GX_PLAN_NO = '" + gxPlanNo + "' ";
		}
		sql += " ORDER BY T.MAT_NO";
		return this.getData(sql);
	}

	/**
	 * 标记样管
	 * 
	 * @param judgeStoveNo
	 *            炉号
	 * @param lotNo
	 *            生产批号
	 * @param pline
	 *            产线
	 * @param matNO
	 *            材料号
	 * @return
	 */
	public CoreReturnObject changeSampleState(String judgeStoveNo,
			String lotNo, String pline, String matNO, String name) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 标记进程表样管
		String sql = "update PORT_MIL_BATCH_SAMPLE_RESULT t set t.is_sample_pipe = '1',t.re_get_sample_time = sysdate,t.re_get_sample_name = '"
				+ name
				+ "' where t.lot_no = '"
				+ lotNo
				+ "' and t.mat_no = '"
				+ matNO + "' and t.pline_code = '" + pline + "'";
		// 标记材料表样管
		String sql1 = "update MAT_BC_M t set t.sample_flag = '1' where t.judge_stove_no = REGEXP_REPLACE("
				+ judgeStoveNo
				+ ", '-.*', '') and t.lot_no = '"
				+ lotNo
				+ "' and t.pline_code = '"
				+ pline
				+ "' and t.ZG_MAT_NO = '"
				+ matNO + "'";
		sqllist.add(sql);
		sqllist.add(sql1);

		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	/**
	 * 标记样管回退
	 * 
	 * @param judgeStoveNo
	 *            炉号
	 * @param lotNo
	 *            生产批号
	 * @param pline
	 *            产线
	 * @param matNO
	 *            材料号
	 * @return
	 */
	public CoreReturnObject changeSampleStateBack(String judgeStoveNo,
			String lotNo, String pline, String matNO, String name) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 标记进程表样管
		String sql = "update PORT_MIL_BATCH_SAMPLE_RESULT t set t.is_sample_pipe = '0',t.re_get_sample_time = sysdate,t.re_get_sample_name = '"
				+ name
				+ "' where t.lot_no = '"
				+ lotNo
				+ "' and t.mat_no = '"
				+ matNO + "' and t.pline_code = '" + pline + "'";
		// 标记材料表样管
		String sql1 = "update MAT_BC_M t set t.sample_flag = '0' where t.judge_stove_no = REGEXP_REPLACE("
				+ judgeStoveNo
				+ ", '-.*', '') and t.lot_no = '"
				+ lotNo
				+ "' and t.pline_code = '"
				+ pline
				+ "' and t.ZG_MAT_NO = '"
				+ matNO + "'";
		sqllist.add(sql);
		sqllist.add(sql1);

		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	/**
	 * 废样管
	 * 
	 * @param judgeStoveNo
	 *            炉号
	 * @param lotNo
	 *            生产批号
	 * @param pline
	 *            产线
	 * @param matNO
	 *            材料号
	 * @return
	 */
	public CoreReturnObject scrapSample(String judgeStoveNo, String lotNo,
			String pline, String matNO, String name, String batchNo) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 废进程表对应材料号的样管
		String sql = "update PORT_MIL_BATCH_SAMPLE_RESULT t set t.is_sample_pipe = '1',t.status = '2',T.OP_PROCESS = '14',t.re_get_sample_time = sysdate,t.re_get_sample_name = '"
				+ name
				+ "' where t.lot_no = '"
				+ lotNo
				+ "' and t.JUDGE_STOVE_NO = '"
				+ judgeStoveNo
				+ "' and t.mat_no = '"
				+ matNO + "' and t.pline_code = '" + pline + "'";
		// 标记材料表样管
		String sql11 = "update MAT_BC_M t set t.sample_flag = '1',T.SCRAP_TYPE = '0' where t.judge_stove_no = REGEXP_REPLACE('"
				+ judgeStoveNo
				+ "', '-.*', '') and t.lot_no = '"
				+ lotNo
				+ "' and t.pline_code = '"
				+ pline
				+ "' and t.ZG_MAT_NO = '"
				+ matNO + "'";
		// 废材料表同批号的样管
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("FrmMilReSample.updateMatNO");
		String updateMatNO = sql1.replace("#JUDGE_STOVE_NO#",
				judgeStoveNo.replaceFirst("-.*", "")).replace("#BATCH_NO#", batchNo);

		// 废生产物料入库实绩表对应材料号的管子

		String sql2 = "update YDM_BC_INLIST t set t.QCM_STAUS = '22' where t.judge_stove_no = '"
				+ judgeStoveNo.replaceFirst("-.*", "")
				+ "' and t.instock_type_code = '800701' and t.mat_no = (select m.mat_no from MAT_BC_M m where m.mat_no = t.mat_no and  m.QCM_STAUS = '22' )";
		sqllist.add(sql);
		sqllist.add(sql11);
		sqllist.add(sql1);
		sqllist.add(updateMatNO);
		sqllist.add(sql2);

		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	/**
	 * 废样管回退
	 * 
	 * @param judgeStoveNo
	 *            炉号
	 * @param lotNo
	 *            生产批号
	 * @param pline
	 *            产线
	 * @param matNO
	 *            材料号
	 * @return
	 */
	public CoreReturnObject scrapSampleBack(String judgeStoveNo, String lotNo,
			String pline, String matNO, String name, String batchNo) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 废进程表对应材料号的样管
		String sql = "update PORT_MIL_BATCH_SAMPLE_RESULT t set t.is_sample_pipe = '0',t.status = '0',T.OP_PROCESS = '',t.re_get_sample_time = sysdate,t.re_get_sample_name = '"
				+ name
				+ "' where t.lot_no = '"
				+ lotNo
				+ "' and t.mat_no = '"
				+ matNO + "' and t.pline_code = '" + pline + "'";
		// 废材料表对应材料号的样管
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("FrmMilReSample.updateMatNOBack");
		String updateMatNOBack = sql1.replace("#JUDGE_STOVE_NO#",
				judgeStoveNo.replaceFirst("-.*", "")).replace("#BATCH_NO#", batchNo);

		// 废入库表同批号的样管回退
		String sql2 = CoreXmlSqlParsersFactory
				.getSql("FrmMilReSample.updateInlistBack");
		String updateInlistBack = sql2.replace("#JUDGE_STOVE_NO#", judgeStoveNo
				.replaceFirst("-.*", ""));

		sqllist.add(sql);
		sqllist.add(updateInlistBack);
		sqllist.add(updateMatNOBack);

		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	/**
	 * 结炉
	 * 
	 * @param judgeStoveNo
	 * @param lotNo
	 * @param pline
	 * @param name
	 * @return
	 */
	public CoreReturnObject doNode(String judgeStoveNo, String lotNo,
			String pline, String name, String proPlanId, String gxPlanNo) {

		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql = CoreXmlSqlParsersFactory
					.getSql("FrmMilReSample.updateProcess"); // 修改进程表复取样状态"
			PreparedStatement updateProcess = connection.prepareStatement(sql);
			String deleteSql = CoreXmlSqlParsersFactory
					.getSql("FrmMilReSample.cancelPipeIn"); // 撤销交库,修改交库实绩表
			PreparedStatement deletePsta = connection
					.prepareStatement(deleteSql);

			updateProcess.setString(1, name);
			updateProcess.setString(2, lotNo);
			updateProcess.setString(3, judgeStoveNo);
			updateProcess.setString(4, pline);
			updateProcess.setString(5, proPlanId);
			updateProcess.setString(6, gxPlanNo);
			updateProcess.addBatch();

			deletePsta.setString(1, judgeStoveNo);
			deletePsta.setString(2, lotNo);
			deletePsta.setString(3, pline);
			deletePsta.addBatch();

			updateProcess.executeBatch();
			// deletePsta.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 judgeStoveNo
	 * @param lotNo
	 * @param pline
	 * @param name
	 * @return
	 */
	public CoreReturnObject doNodeBack(String judgeStoveNo, String lotNo,
			String pline, String name, String proPlanId, String gxPlanNo) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 废进程表对应材料号的样管
		String sql = " update PORT_MIL_BATCH_SAMPLE_RESULT t set t.re_get_sample_status = '0' where t.lot_no = '"
				+ lotNo
				+ "' and REGEXP_REPLACE(T.JUDGE_STOVE_NO, '-.*', '') = '"
				+ judgeStoveNo
				+ "' and t.pline_code = '"
				+ pline
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ proPlanId
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ gxPlanNo
				+ "','/')";
		sqllist.add(sql);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

}
