package com.steering.mes.signature;

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

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

import com.steering.comm.converter.CoreConverter;
import com.steering.comm.db.DBCommon;

public class FrmColUpdate extends DBCommon{

	/***
	 * 查询操作人
	 * @return
	 */
	public CoreReturnObject doQueryColUser(String plineCode,String stationId,String group)
	{
		return this.getDataBySqlID("FrmColUpdate.doQueryColUser", new Object[]{plineCode,stationId,group});
	}
	/***
	 * 查询锯切计划
	 * @param parm
	 * @param plineCode
	 * @param startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryPlan(ArrayList<String> parm,String[] plineCode,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.doQueryPlan");
		sql += 	CoreConverter.toSqlConditionString("T.PLINE_CODE", plineCode);
		if (!startTim.equals("")) {
			sql += " AND T.END_TIME >= to_date('" + startTim
					+ "','yyyy-MM-dd HH24:mi:ss')AND T.END_TIME <= to_date('"
					+ endTim + "','yyyy-MM-dd HH24:mi:ss') ";
		}
		return this.getData(sql, parm.toArray());
	}
	/***
	 * 查询上料行信息
	 * @param parm
	 * @param plineCode
	 * @param startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryFeedPlan(ArrayList<String> parm,String[] plineCode,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.doQueryFeedPlan");
		sql += 	CoreConverter.toSqlConditionString("PLINE_CODE", plineCode);
		sql += "ORDER BY FEED_ROW_NO";
		return this.getData(sql, parm.toArray());
	}
	/***
	 * 查询锯切Rack
	 * @param stoveNo
	 * @param lotNo
	 * @param feedNo
	 * @return
	 */
	public CoreReturnObject queryFeedRack(String stoveNo,String lotNo,String feedNo)
	{
		return this.getDataBySqlID("FrmColUpdate.queryFeedRack", new Object[]{stoveNo,lotNo,feedNo});
	}
	/**
	 * 查询计划
	 * @param parm
	 * @param plineCode
	 * @param startTim
	 * @param endTim
	 * @param stationId
	 * @return
	 */
	public CoreReturnObject doQueryRack(ArrayList<String> parm,String[] plineCode,String startTim,String endTim,String stationId)
	{
		 
		String sql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.doQueryRack");
		sql += 	CoreConverter.toSqlConditionString("T.PLINE_CODE", plineCode);

		if (!startTim.equals("")) {
			sql += " AND #END_TIME# >= to_date('" + startTim
					+ "','yyyy-MM-dd HH24:mi:ss')AND #END_TIME# <= to_date('"
					+ endTim + "','yyyy-MM-dd HH24:mi:ss') ";
		}
		sql += " GROUP BY T.JUDGE_STOVE_NO,T.LOT_NO,T.RESULT_NO,M.NODE_STATUS";
		if(stationId.equals("2"))
		{
			sql = sql.replace("#RACK#", "MIL_PERFORATION_RACK").replace("#START_TIME#", "T.START_PERFORATION_TIME").replace("#END_TIME#", "T.END_PERFORATION_TIME").replace("#RESULT#", "MIL_PERFORATION_RESULT").replace("#JZB_NUM#","S.JZB_NUM").replace("#JZB_NUM2#", "0").replace("#END_TIME1#", "END_PERFORATION_TIME");
		}else if(stationId.equals("3"))
		{
			sql = sql.replace("#RACK#", "MIL_ROLLING_RACK").replace("#START_TIME#", "T.START_ROLLING_TIME").replace("#END_TIME#", "T.END_ROLLING_TIME").replace("#RESULT#", "MIL_ROLLING_RESULT").replace("#JZB_NUM#", "S.CHANGECLASSNUM").replace("#JZB_NUM2#", "0").replace("#END_TIME1#", "END_ROLLING_TIME");
		}else if(stationId.equals("1"))
		{
			sql = sql.replace("#RACK#", "MIL_FUR_RACK").replace("#END_TIME#", "T.OUT_FUR_TIME").replace("#RESULT#", "MIL_FUR_RESULT").replace("#JZB_NUM#", "S.CHANGE_NUM").replace("#JZB_NUM2#", "S.OUT_CHANGE_NUM").replace("#END_TIME1#", "OUT_FUR_TIME");
		}else if(stationId.equals("4"))
		{
			sql = sql.replace("#RACK#", "MIL_REFUR_RACK").replace("#END_TIME#", "T.OUT_REFUR_TIME").replace("#RESULT#", "MIL_REFUR_RESULT").replace("#JZB_NUM#", "S.CHANGECLASSNUM").replace("#JZB_NUM2#", "S.OUT_CHANGE_NUM").replace("#END_TIME1#", "OUT_REFUR_TIME");
		}
		else if(stationId.equals("5"))
		{
			sql = sql.replace("#RACK#", "MIL_SIZING_RACK").replace("#START_TIME#", "T.START_SIZING_TIME").replace("#END_TIME#", "T.END_SIZING_TIME").replace("#RESULT#", "MIL_SIZING_RESULT").replace("#JZB_NUM#", "S.CHANGE_NUM").replace("#JZB_NUM2#", "S.CHANGE_NUM").replace("#END_TIME1#", "END_SIZING_TIME");
		}
		return this.getData(sql, parm.toArray());
	}
	/***
	 * 查询历史信息
	 * @param parm
	 * @param plineCode
	 * @param startTim
	 * @param endTim
	 * @param stationId
	 * @return
	 */
	public CoreReturnObject doQueryModifyData(ArrayList<String> parm,String[] plineCode,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.doQueryModifyData");
		sql += 	CoreConverter.toSqlConditionString("PLINE_CODE", plineCode);
		if (!startTim.equals("")) {
			sql += " AND MODIFY_TIME >= to_date('" + startTim
					+ "','yyyy-MM-dd HH24:mi:ss')AND MODIFY_TIME <= to_date('"
					+ endTim + "','yyyy-MM-dd HH24:mi:ss') ";
		}
		sql += "ORDER BY MODIFY_TIME DESC";
		return this.getData(sql, parm.toArray());
	}
	/***
	 * 查询管序队列
	 * @param stoveNo
	 * @param lotNo
	 * @param stationId
	 * @return
	 */
	public CoreReturnObject queryRack(String stoveNo,String lotNo,String stationId)
	{
		String sql = "";
		if(stationId.equals("1")||stationId.equals("4"))
		{
			sql= CoreXmlSqlParsersFactory.getSql("FrmColUpdate.queryFurRack");
		}else
		{
		    sql= CoreXmlSqlParsersFactory.getSql("FrmColUpdate.queryRack");
		}
		if(stationId.equals("2"))
		{
			sql = sql.replace("#RACK#", "MIL_PERFORATION_RACK").replace("#START_TIME#", "T.START_PERFORATION_TIME").replace("#END_TIME#", "T.END_PERFORATION_TIME").replace("#FLAG#", "T.CHANGE_FLAG").replace("#FLAGS#", "2");
		}else if(stationId.equals("3"))
		{
			sql = sql.replace("#RACK#", "MIL_ROLLING_RACK").replace("#START_TIME#", "T.START_ROLLING_TIME").replace("#END_TIME#", "T.END_ROLLING_TIME").replace("#FLAG#", "T.CHANGE_FLAG").replace("#FLAGS#", "2");
		}else if(stationId.equals("5"))
		{
			sql = sql.replace("#RACK#", "MIL_SIZING_RACK").replace("#START_TIME#", "T.START_SIZING_TIME").replace("#END_TIME#", "T.END_SIZING_TIME").replace("#FLAG#", "T.CHANGE_FLAG").replace("#FLAGS#", "2");
		}else if(stationId.equals("1"))
		{
			sql = sql.replace("#RACK#", "MIL_FUR_RACK").replace("#START_TIME#", "T.IN_FUR_TIME").replace("#END_TIME#", "T.OUT_FUR_TIME").replace("#FLAG#", "T.IN_CHANGE_FLAG").replace("#FLAGS#", "T.OUT_CHANGE_FLAG");
		}else if(stationId.equals("4"))
		{
			sql = sql.replace("#RACK#", "MIL_REFUR_RACK").replace("#START_TIME#", "T.IN_REFUR_TIME").replace("#END_TIME#", "T.OUT_REFUR_TIME").replace("#FLAG#", "T.IN_CHANGE_FLAG").replace("#FLAGS#", "T.OUT_CHANGE_FLAG");
		}
		return this.getData(sql, new Object[]{stoveNo,lotNo});
	}
	
	
	/***
	 * 重置交接班
	 * @param parm
	 * @return
	 */
	public CoreReturnObject updateData(ArrayList<String> parm)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        PreparedStatement psta = null;
        String sql = "";
        ResultSet rs = null;
        String updateSql = "";
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			if(parm.get(12).toString().equals("1"))
			{
				updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateDataHeating1");
				if(parm.get(0).equals("1"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_FUR_RACK").replace("#END_TIME#", "T.OUT_FUR_TIME").replace("#START_TIME#", "T.IN_FUR_TIME").replace("#COL_USER#", "T.IN_USER").replace("#COL_SHIFT# ", "T.IN_SHIFT").replace("#COL_GROUP#", "T.IN_GROUP");
				}else if(parm.get(0).equals("4"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_REFUR_RACK").replace("#END_TIME#", "T.OUT_REFUR_TIME").replace("#START_TIME#", "T.IN_REFUR_TIME").replace("#COL_USER#", "T.IN_USER").replace("#COL_SHIFT# ", "T.IN_SHIFT").replace("#COL_GROUP#", "T.IN_GROUP");
				}
			}else if(parm.get(12).toString().equals("2"))
			{
				updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateDataHeating4");
				if(parm.get(0).equals("1"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_FUR_RACK").replace("#END_TIME#", "T.OUT_FUR_TIME").replace("#START_TIME#", "T.IN_FUR_TIME").replace("#COL_USER#", "T.COL_USER").replace("#COL_SHIFT# ", "T.COL_SHIFT").replace("#COL_GROUP#", "T.COL_GROUP");
				}else if(parm.get(0).equals("4"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_REFUR_RACK").replace("#END_TIME#", "T.OUT_REFUR_TIME").replace("#START_TIME#", "T.IN_REFUR_TIME").replace("#COL_USER#", "T.COL_USER").replace("#COL_SHIFT# ", "T.COL_SHIFT").replace("#COL_GROUP#", "T.COL_GROUP");
				}
			}else if(parm.get(0).equals("0"))
			{
				updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateFeedData");
			}
			else
			{
			     updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateData");
			     if(parm.get(0).equals("2"))
					{
						updateSql = updateSql.replace("#RACK#", "MIL_PERFORATION_RACK").replace("#END_TIME#", "T.END_PERFORATION_TIME").replace("#START_TIME#", "T.START_PERFORATION_TIME");
					}else if(parm.get(0).equals("3"))
					{
						updateSql = updateSql.replace("#RACK#", "MIL_ROLLING_RACK").replace("#END_TIME#", "T.END_ROLLING_TIME").replace("#START_TIME#", "T.START_ROLLING_TIME");
					}else if(parm.get(0).equals("5"))
					{
						updateSql = updateSql.replace("#RACK#", "MIL_SIZING_RACK").replace("#END_TIME#", "T.END_SIZING_TIME").replace("#START_TIME#", "T.START_SIZING_TIME");
					}
					else
					{
						cro.setV_errMsg("请选择工序点!");
						return cro;
					}
			}
			
			PreparedStatement updatePsta = connection.prepareStatement(updateSql);
			updatePsta.setString(1, parm.get(1));
			updatePsta.setString(2, parm.get(2));
			updatePsta.setString(3, parm.get(4));
			updatePsta.setString(4, parm.get(3));

			updatePsta.setString(5, parm.get(1));
			updatePsta.setString(6, parm.get(2));
			updatePsta.setString(7, parm.get(0));
			if(parm.get(0).equals("0"))
			{
				updatePsta.setString(8, parm.get(13));
			}else
			{
			    updatePsta.setString(8, "");
			}
			updatePsta.setString(9, "");
			updatePsta.setString(10, parm.get(7));
			updatePsta.setString(11, parm.get(8));
			updatePsta.setString(12, "");
			updatePsta.setString(13, parm.get(10));
			//updatePsta.setString(13, "C009");
			updatePsta.setString(14, parm.get(4));
			updatePsta.setString(15, parm.get(3));

			updatePsta.setString(16, parm.get(8));
			updatePsta.setString(17, parm.get(4));
			updatePsta.setString(18, parm.get(3));
			updatePsta.setString(19, parm.get(9));
			updatePsta.setString(20, parm.get(11));
			
			updatePsta.setString(21, parm.get(1));
			updatePsta.setString(22, parm.get(2));
			updatePsta.setInt(23, Integer.parseInt(parm.get(5)));
			updatePsta.setInt(24, Integer.parseInt(parm.get(6)));
			if(parm.get(0).equals("0"))
			{
				updatePsta.setString(25, parm.get(13));
			}
			updatePsta.addBatch();
			
			updatePsta.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 parm
	 * @return
	 * @throws Exception 
	 */
	public CoreReturnObject updateFlag(ArrayList<String> parm,String stationId,String colGroup,String colOrder)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        PreparedStatement psta = null;
        String sql = "";
        ResultSet rs = null;
        String _colGroup = "";
		String _colOrder = "";
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateFlag");
			String updateResultSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateResult");
			if (stationId.equals("2")) {
				updateSql = updateSql.replace("#RACK#", "MIL_PERFORATION_RACK").replace("#FLAG#", "T.CHANGE_FLAG");
				updateResultSql = updateResultSql.replace("#RESULT#", "MIL_PERFORATION_RESULT").replace("#RACK#", "MIL_PERFORATION_RACK").replace("#JZB_NUM#", "JZB_NUM");
			} 
			else if (stationId.equals("3")) {
				updateSql = updateSql.replace("#RACK#", "MIL_ROLLING_RACK").replace("#FLAG#", "T.CHANGE_FLAG");
				updateResultSql = updateResultSql.replace("#RESULT#", "MIL_ROLLING_RESULT").replace("#RACK#", "MIL_ROLLING_RACK").replace("#JZB_NUM#", "CHANGECLASSNUM");
			} 
			else if (stationId.equals("5")) {
				updateSql = updateSql.replace("#RACK#", "MIL_SIZING_RACK").replace("#FLAG#", "T.CHANGE_FLAG");
				updateResultSql = updateResultSql.replace("#RESULT#", "MIL_SIZING_RESULT").replace("#RACK#", "MIL_SIZING_RACK").replace("#JZB_NUM#", "CHANGE_NUM");
			} 
			else if(stationId.equals("1"))
			{
				if(parm.get(4).equals("1"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_FUR_RACK").replace("#FLAG#", "T.IN_CHANGE_FLAG");
					updateResultSql = updateResultSql.replace("#RESULT#", "MIL_FUR_RESULT").replace("#RACK#", "MIL_FUR_RACK").replace("#JZB_NUM#", "CHANGE_NUM");
				}else if(parm.get(4).equals("2"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_FUR_RACK").replace("#FLAG#", "T.OUT_CHANGE_FLAG");
					updateResultSql = updateResultSql.replace("#RESULT#", "MIL_FUR_RESULT").replace("#RACK#", "MIL_FUR_RACK").replace("#JZB_NUM#", "OUT_CHANGE_NUM");
				}
			}
			else if(stationId.equals("4"))
			{
				if(parm.get(4).equals("1"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_REFUR_RACK").replace("#FLAG#", "T.IN_CHANGE_FLAG");
					updateResultSql = updateResultSql.replace("#RESULT#", "MIL_REFUR_RESULT").replace("#RACK#", "MIL_REFUR_RACK").replace("#JZB_NUM#", "CHANGECLASSNUM");
				}else if(parm.get(4).equals("2"))
				{
					updateSql = updateSql.replace("#RACK#", "MIL_FUR_RACK").replace("#FLAG#", "T.OUT_CHANGE_FLAG");
					updateResultSql = updateResultSql.replace("#RESULT#", "MIL_REFUR_RESULT").replace("#RACK#", "MIL_REFUR_RACK").replace("#JZB_NUM#", "OUT_CHANGE_NUM");
				}
			}
			PreparedStatement updatePsta = connection.prepareStatement(updateSql);
			PreparedStatement updateResultPsta = connection.prepareStatement(updateResultSql);
			sql = CoreXmlSqlParsersFactory.getSql("updateFlag.selectRack");
			if (stationId.equals("2")) {
				sql = sql.replace("#RACK#", "MIL_PERFORATION_RACK");
			} else if (stationId.equals("3")) {
				sql = sql.replace("#RACK#", "MIL_ROLLING_RACK");
			} else if (stationId.equals("5")) {
				sql = sql.replace("#RACK#", "MIL_SIZING_RACK");
			}else if(stationId.equals("1"))
			{
				sql = sql.replace("#RACK#", "MIL_FUR_RACK");
			}else if(stationId.equals("4"))
			{
				sql = sql.replace("#RACK#", "MIL_REFUR_RACK");
			}
			psta = connection.prepareStatement(sql);
			psta.setString(1, parm.get(1));
			psta.setString(2, parm.get(2));
			psta.setString(3, String
					.valueOf((Integer.parseInt(parm.get(3)) + 1)));
			rs = psta.executeQuery();
			if (rs.next()) {
				 _colOrder = rs.getString("COL_SHIFT");
				 _colGroup = rs.getString("COL_GROUP");
			}
			rs.close();
			psta.close();
			if(!_colGroup.equals(""))
			{
				if (_colGroup.equals(colGroup) && _colOrder.equals(colOrder)) {
					cro.setV_errMsg("下一支与该支班次班组一样,不允许修改交班点!");
					return cro;
				}
			}
			updatePsta.setString(1, parm.get(0));
			updatePsta.setString(2, parm.get(1));
			updatePsta.setString(3, parm.get(2));
			updatePsta.setString(4, parm.get(3));
			updatePsta.addBatch();
			
			updateResultPsta.setString(1, parm.get(1));
			updateResultPsta.setString(2, parm.get(2));
			updateResultPsta.setString(3, parm.get(1));
			updateResultPsta.setString(4, parm.get(2));
			updateResultPsta.addBatch();
			
			updatePsta.executeBatch();
			updateResultPsta.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 parm
	 * @param stationId
	 * @param colGroup
	 * @param colOrder
	 * @return
	 */
	public CoreReturnObject updateFlagFeed(ArrayList<String> parm,String stationId,String colGroup,String colOrder)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        PreparedStatement psta = null;
        String sql = "";
        ResultSet rs = null;
        String _colGroup = "";
		String _colOrder = "";
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updateSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateFlagFeed");
			String updateResultSql = CoreXmlSqlParsersFactory.getSql("FrmColUpdate.updateResultFeed");
			PreparedStatement updatePsta = connection.prepareStatement(updateSql);
			PreparedStatement updateResultPsta = connection.prepareStatement(updateResultSql);
			sql = CoreXmlSqlParsersFactory.getSql("updateFlag.selectRackFeed");
			psta = connection.prepareStatement(sql);
			psta.setString(1, parm.get(1));
			psta.setString(2, parm.get(2));
			psta.setString(3, String
					.valueOf((Integer.parseInt(parm.get(3)) + 1)));
			psta.setString(4, parm.get(5));
			rs = psta.executeQuery();
			if (rs.next()) {
				 _colOrder = rs.getString("COL_SHIFT");
				 _colGroup = rs.getString("COL_GROUP");
			}
			rs.close();
			psta.close();
			if(!_colGroup.equals(""))
			{
				if (_colGroup.equals(colGroup) && _colOrder.equals(colOrder)) {
					cro.setV_errMsg("下一支与该支班次班组一样,不允许修改交班点!");
					return cro;
				}
			}
			updatePsta.setString(1, parm.get(0));
			updatePsta.setString(2, parm.get(1));
			updatePsta.setString(3, parm.get(2));
			updatePsta.setString(4, parm.get(3));
			updatePsta.setString(5, parm.get(5));
			updatePsta.addBatch();
			
			updateResultPsta.setString(1, parm.get(1));
			updateResultPsta.setString(2, parm.get(2));
			updateResultPsta.setString(3, parm.get(1));
			updateResultPsta.setString(4, parm.get(2));
			updateResultPsta.setString(5, parm.get(5));
			updateResultPsta.setString(6, parm.get(1));
			updateResultPsta.setString(7, parm.get(2));
			updateResultPsta.setString(8, parm.get(1));
			updateResultPsta.setString(9, parm.get(2));
			updateResultPsta.setString(10, parm.get(5));
			updateResultPsta.addBatch();
			
			updatePsta.executeBatch();
			updateResultPsta.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;
	}
}
