package com.steering.mes.signature;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import CoreFS.SA06.CoreReturnObject;
import CoreFS.SA08.CoreXmlSqlParsersFactory;
import CoreFS.SC01.CoreConstantData;

import com.steering.comm.db.DBCommon;
import com.steering.common.EntityTool;
import com.steering.mes.zgmil.entity.MilFurnacesGroupEntity;
import com.steering.mes.zgmil.entity.MilFurnacesReslutEntity;

/**
 * 荒管组炉管理
 * @author Administrator
 *
 */
public class FrmComposeFurnaces extends DBCommon{

	/***
	 * 查询再加热炉剔除的荒管
	 * @param parm
	 * @param startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryScrap(ArrayList<String> parm,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryScrap");
		if(!startTim.equals(""))
		{
		   sql += " AND T.COL_TIME >= to_date('"+startTim+"','YYYY-MM-DD HH24:MI:SS') AND T.COL_TIME <= to_date('"+endTim+"','YYYY-MM-DD HH24:MI:SS')";
		}
		return this.getData(sql, parm.toArray());
	}
	
	/**
	 * 组炉批
	 * @param parm 原炉号信息
	 * @param parm1 主炉号信息
	 * @param parm2 组炉信息
	 * @return
	 */
	public CoreReturnObject doFurnace(ArrayList<String> parm,ArrayList<String> parm1,ArrayList<String> parm2)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        PreparedStatement psta = null;
        String sql = "";
        ResultSet rs = null;
        int jugeNoCount = 0;
        int jugeNoCount2 = 0; //组炉的总支数
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql1 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.updateFurnaceReslut"); //修改荒管剔除待处理实绩表状态，以及插入子表数据
			PreparedStatement updateFurnaceReslut = connection.prepareStatement(sql1);
			String sql2 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.insertFurnaceGroup"); //插入荒管组炉实绩表
			PreparedStatement insertFurnaceGroup = connection.prepareStatement(sql2);

			String sql4 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.insertMilPlan");//插入计划表、锯切实绩表
			PreparedStatement insertMilPlan = connection.prepareStatement(sql4);
			
			String sql5 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.insertRefurRack");//插入再加热炉及后续工序管号队列跟踪表、插入进程表
			PreparedStatement insertRefurRack = connection.prepareStatement(sql5);
			
			String sql6 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.insertBatch");//插入进程表
			PreparedStatement insertBatch = connection.prepareStatement(sql6);
			
			String sql7 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.updateInputWeigth");//更新进程表投料量
			PreparedStatement updateInputWeigth = connection.prepareStatement(sql7);
			
			MilFurnacesReslutEntity tity = EntityTool.getEntityByJSONStr(parm1.get(0).toString(), MilFurnacesReslutEntity.class);
			for(String str : parm)
			{
				sql = CoreXmlSqlParsersFactory.getSql("doFurnace.doQueryMilPlanJugeNo");
				psta = connection.prepareStatement(sql);
				psta.setString(1, parm2.get(0));
				rs = psta.executeQuery();
				if(rs.next())
				{
					jugeNoCount = Integer.parseInt(rs.getString("ACOUNT"));
				}
				
				rs.close();
				psta.close();
				if(jugeNoCount>0)
				{
					cro.setV_errMsg("此新炉号已存在计划中,不允许进行组炉批!");
					return cro;
				}
				
				
				//修改荒管剔除待处理实绩表状态，以及插入子表数据
				MilFurnacesReslutEntity milFurnacesReslutEntity = EntityTool.getEntityByJSONStr(str, MilFurnacesReslutEntity.class);
				//查询该炉号是否是回炉坯或者荒管组炉
				sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryZP");
				psta = connection.prepareStatement(sql);
				psta.setString(1, milFurnacesReslutEntity.getJudgeStoveNo());
				rs = psta.executeQuery();
				if(rs.next())
				{
					jugeNoCount = Integer.parseInt(rs.getString("ACOUNT"));
				}
				
				rs.close();
				psta.close();
				if(jugeNoCount>0)
				{
					cro.setV_errMsg("炉号"+milFurnacesReslutEntity.getJudgeStoveNo()+"是回炉坯或者荒管组炉炉号,不允许进行再次组炉批!");
					return cro;
				}
				
				updateFurnaceReslut.setString(1, milFurnacesReslutEntity.getColNum());
				updateFurnaceReslut.setString(2, milFurnacesReslutEntity.getOfflineNum());
				updateFurnaceReslut.setString(3, milFurnacesReslutEntity.getGroupNum());
				updateFurnaceReslut.setString(4, milFurnacesReslutEntity.getJudgeStoveNo());
				updateFurnaceReslut.setString(5, milFurnacesReslutEntity.getLotNo());
				updateFurnaceReslut.setString(6, milFurnacesReslutEntity.getPlineCode());
				updateFurnaceReslut.setString(7, milFurnacesReslutEntity.getProPlanId());
				updateFurnaceReslut.setString(8, milFurnacesReslutEntity.getGxPlanNo());
				
				updateFurnaceReslut.setString(9, milFurnacesReslutEntity.getStoveNo());
				updateFurnaceReslut.setString(10, milFurnacesReslutEntity.getDiameter());
				updateFurnaceReslut.setString(11, milFurnacesReslutEntity.getThickness());
				updateFurnaceReslut.setString(12, milFurnacesReslutEntity.getLength());
				updateFurnaceReslut.setString(13, milFurnacesReslutEntity.getPlineCode());
				updateFurnaceReslut.setString(14, milFurnacesReslutEntity.getRemark());
				updateFurnaceReslut.setString(15, parm2.get(2));
				updateFurnaceReslut.setString(16, parm2.get(4));
				updateFurnaceReslut.setString(17, parm2.get(3));
				updateFurnaceReslut.setString(18, milFurnacesReslutEntity.getProducname());
				updateFurnaceReslut.setString(19, milFurnacesReslutEntity.getSteelcode());
				updateFurnaceReslut.setString(20, milFurnacesReslutEntity.getSteelname());
				updateFurnaceReslut.setString(21, milFurnacesReslutEntity.getGradecode());
				updateFurnaceReslut.setString(22, milFurnacesReslutEntity.getGradename());
				updateFurnaceReslut.setString(23, milFurnacesReslutEntity.getSpecCode());
				updateFurnaceReslut.setString(24, milFurnacesReslutEntity.getSpecName());
				updateFurnaceReslut.setString(25, milFurnacesReslutEntity.getModelCode());
				updateFurnaceReslut.setString(26, milFurnacesReslutEntity.getModelDesc());
				updateFurnaceReslut.setString(27, milFurnacesReslutEntity.getStdCode());
				updateFurnaceReslut.setString(28, milFurnacesReslutEntity.getStdName());
				updateFurnaceReslut.setString(29, milFurnacesReslutEntity.getStdStyle());
				updateFurnaceReslut.setString(30, milFurnacesReslutEntity.getStdStyleDesc());
				updateFurnaceReslut.setString(31, milFurnacesReslutEntity.getContractNo());
				updateFurnaceReslut.setString(32, milFurnacesReslutEntity.getProduccode());
				updateFurnaceReslut.setString(33, milFurnacesReslutEntity.getActLenRange());
				updateFurnaceReslut.setString(34, parm2.get(1));
				updateFurnaceReslut.setString(35, parm2.get(0));
				updateFurnaceReslut.setString(36, milFurnacesReslutEntity.getTarget());
				updateFurnaceReslut.addBatch();

				//插入再加热炉及后续工序管号队列跟踪表
				insertRefurRack.setString(1, milFurnacesReslutEntity.getJudgeStoveNo());
				insertRefurRack.setString(2, milFurnacesReslutEntity.getLotNo());
				insertRefurRack.setString(3, milFurnacesReslutEntity.getPlineCode());
				insertRefurRack.setString(4, parm2.get(0));
				insertRefurRack.setString(5, parm2.get(1));
				insertRefurRack.setString(6, milFurnacesReslutEntity.getColNum());
				insertRefurRack.setString(7, parm2.get(5));
				insertRefurRack.setString(8, tity.getJudgeStoveNo());
				insertRefurRack.setString(9, tity.getLotNo());
				insertRefurRack.addBatch();

				//插入进程表
				insertBatch.setString(1, parm2.get(0));
				insertBatch.setString(2, parm2.get(1));
				insertBatch.setString(3, milFurnacesReslutEntity.getJudgeStoveNo());
				insertBatch.setString(4, milFurnacesReslutEntity.getLotNo());
				insertBatch.setString(5, milFurnacesReslutEntity.getPlineCode());
				insertBatch.setString(6, milFurnacesReslutEntity.getColNum());
				insertBatch.setString(7, milFurnacesReslutEntity.getProPlanId());
				insertBatch.setString(8, milFurnacesReslutEntity.getGxPlanNo());
				insertBatch.setString(9, tity.getProPlanId());
				insertBatch.setString(10, tity.getGxPlanNo());
				insertBatch.setString(11, tity.getContractNo());
				insertBatch.addBatch();
				
				jugeNoCount2 += Integer.parseInt(milFurnacesReslutEntity.getColNum()) ;
				
			}
			    
			//查询  荒管组炉实绩表 是否已有此炉号
			sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryFuracesGroup");
			psta = connection.prepareStatement(sql);
			psta.setString(1, parm2.get(0));
			rs = psta.executeQuery();
			if(rs.next())
			{
				jugeNoCount = Integer.parseInt(rs.getString("ACOUNT"));
			}
			rs.close();
			psta.close();
			if(jugeNoCount>0)
			{
				cro.setV_errMsg("此新炉号已存在组炉实绩表中,不允许进行组炉批!");
				return cro;
			}
				//插入荒管组炉实绩表
			    insertFurnaceGroup.setString(1, parm2.get(0));
			    insertFurnaceGroup.setString(2, parm2.get(1));
			    insertFurnaceGroup.setString(3, tity.getPlineCode());
			    insertFurnaceGroup.setString(4, tity.getStdName());
			    insertFurnaceGroup.setString(5, tity.getSteelname());
			    insertFurnaceGroup.setString(6, tity.getGradename());
			    insertFurnaceGroup.setString(7, tity.getLength());
			    insertFurnaceGroup.setString(8, tity.getActLenRange());
			    insertFurnaceGroup.setString(9, tity.getSpecName());
			    insertFurnaceGroup.setString(10, tity.getRemark());
			    insertFurnaceGroup.setString(11, parm2.get(2));
			    insertFurnaceGroup.setString(12, parm2.get(4));
			    insertFurnaceGroup.setString(13, parm2.get(3));
			    insertFurnaceGroup.setString(14, tity.getDiameter());
			    insertFurnaceGroup.setString(15, tity.getThickness());
			    insertFurnaceGroup.setString(16, tity.getProducname());
			    insertFurnaceGroup.setString(17, tity.getPlineCode());
			    insertFurnaceGroup.setString(18, tity.getModelDesc());
			    insertFurnaceGroup.setString(19, tity.getProPlanId());
			    insertFurnaceGroup.setString(20, tity.getGxPlanNo());
			    insertFurnaceGroup.addBatch();
			    
			    
			  //插入计划
			    insertMilPlan.setString(1, tity.getJudgeStoveNo());
			    insertMilPlan.setString(2, tity.getLotNo());
			    insertMilPlan.setString(3, tity.getPlineCode());
			    insertMilPlan.setString(4, parm2.get(0));
			    insertMilPlan.setString(5, parm2.get(1));
			    insertMilPlan.setInt(6, jugeNoCount2);
			    insertMilPlan.setString(7, parm2.get(5));
			    insertMilPlan.setString(8, tity.getContractNo());
			    insertMilPlan.setString(9, tity.getContractNo());
			    insertMilPlan.setString(10, tity.getProducname());
			    if(tity.getSpecName().contains("x")){
			    	insertMilPlan.setString(11, tity.getSpecName().replace("x", "*"));
			    }else{
			    	insertMilPlan.setString(11, tity.getSpecName());
			    }
			    insertMilPlan.setString(12, tity.getModelDesc());
			    insertMilPlan.addBatch();
			    
			    //更新进程表投料量
			    updateInputWeigth.setString(1, parm2.get(0));
			    updateInputWeigth.setString(2, parm2.get(1));
			    updateInputWeigth.setString(3, tity.getPlineCode());
			    updateInputWeigth.addBatch();

			    
			  //修改荒管剔除待处理实绩表状态，以及插入子表数据
			    updateFurnaceReslut.executeBatch();
			  //插入再加热炉及后续工序管号队列跟踪表
			    insertRefurRack.executeBatch();
			  //插入进程表
			    //insertBatch.executeBatch();
			  //插入荒管组炉实绩表
			    insertFurnaceGroup.executeBatch();
			  //插入计划
			    insertMilPlan.executeBatch();
			    //更新进程表投料量
			    updateInputWeigth.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
	 */
	public CoreReturnObject doCancelFurnace(ArrayList<String> parm)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        PreparedStatement psta = null;
        String sql = "";
        ResultSet rs = null;
        int acount = 0;
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql1 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelFurnaceReslut");  //修改荒管剔除待处理实绩表状态，以及删除子表数据
			PreparedStatement cancelFurnaceReslut = connection.prepareStatement(sql1);
			
			String sql2 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelFurnaceGroup");  //删除荒管组炉实绩表数据
			PreparedStatement cancelFurnaceGroup = connection.prepareStatement(sql2);
			
			String sql4 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelMilPlan");//删除计划表、管坯锯切实绩表、连轧脱管实绩表
			PreparedStatement cancelMilPlan = connection.prepareStatement(sql4);
			
			String sql5 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelRefurRack");//删除再加热炉及后续工序管号队列跟踪表
			PreparedStatement cancelRefurRack = connection.prepareStatement(sql5);
			
			String sql6 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelBatch");//删除进程表
			PreparedStatement cancelBatch = connection.prepareStatement(sql6);
			
			/*String sql7 = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.cancelFeedSaw");//删除管坯锯切实绩表
			PreparedStatement cancelFeedSaw = connection.prepareStatement(sql7);*/
			
			for(String str:parm)
			{
				MilFurnacesGroupEntity milFurnacesGroupEntity = EntityTool.getEntityByJSONStr(str, MilFurnacesGroupEntity.class);
				sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryMilPlan");
				psta = connection.prepareStatement(sql);
				psta.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
				psta.setString(2, milFurnacesGroupEntity.getZpLotNo());
				psta.setString(3, milFurnacesGroupEntity.getPlineCode());
				rs = psta.executeQuery();
				if(rs.next())
				{
					acount = Integer.parseInt(rs.getString("ACOUNT"));
				}
			    rs.close();
			    psta.close();
			    if(acount>0)
			    {
			    	cro.setV_errMsg("该炉信息已进行其他操作,不允许撤销!");
			    	return cro;
			    }

			  //修改荒管剔除待处理实绩表状态，以及删除子表数据
			    cancelFurnaceReslut.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelFurnaceReslut.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelFurnaceReslut.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelFurnaceReslut.addBatch();

			  //删除荒管组炉实绩表数据
			    cancelFurnaceGroup.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelFurnaceGroup.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelFurnaceGroup.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelFurnaceGroup.addBatch();

			  //删除计划表、管坯锯切实绩表、连轧脱管实绩表
			    cancelMilPlan.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelMilPlan.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelMilPlan.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelMilPlan.addBatch();
			    
			  /*//删除管坯锯切实绩表
			    cancelFeedSaw.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelFeedSaw.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelFeedSaw.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelFeedSaw.addBatch();*/

			  //删除再加热炉及后续工序管号队列跟踪表
			    cancelRefurRack.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelRefurRack.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelRefurRack.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelRefurRack.addBatch();

			  //删除进程表
			    cancelBatch.setString(1, milFurnacesGroupEntity.getZpJudgeStoveNo());
			    cancelBatch.setString(2, milFurnacesGroupEntity.getZpLotNo());
			    cancelBatch.setString(3, milFurnacesGroupEntity.getPlineCode());
			    cancelBatch.addBatch();

			}
			//删除进程表
			cancelBatch.executeBatch();
			//修改荒管剔除待处理实绩表状态，以及删除子表数据
			cancelFurnaceReslut.executeBatch();
			//删除荒管组炉实绩表数据
			cancelFurnaceGroup.executeBatch();
			//删除计划表、管坯锯切实绩表、连轧脱管实绩表
			cancelMilPlan.executeBatch();
			/*//删除管坯锯切实绩表
			cancelFeedSaw.executeBatch();*/
			//删除再加热炉及后续工序管号队列跟踪表
			cancelRefurRack.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 startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryFurnace(ArrayList<String> parm,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryFurnace");
		if(!startTim.equals(""))
		{
			sql += " AND T.ZP_TIME >= to_date('"+startTim+"','YYYY-MM-DD HH24:MI:SS') AND T.ZP_TIME <= to_date('"+endTim+"','YYYY-MM-DD HH24:MI:SS')";
		}
		return this.getData(sql,parm.toArray());
	}
	
	/**
	 * 组炉实绩查询（从表）
	 * @param parm
	 * @param startTim
	 * @param endTim
	 * @return
	 */
	public CoreReturnObject doQueryFurnaceChildRow(ArrayList<String> parm,String startTim,String endTim)
	{
		String sql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.doQueryFurnaceChildRow");
		if(!startTim.equals(""))
		{
			sql += " AND T.COL_TIME >= to_date('"+startTim+"','YYYY-MM-DD HH24:MI:SS') AND T.COL_TIME <= to_date('"+endTim+"','YYYY-MM-DD HH24:MI:SS')";
		}
		return this.getData(sql,parm.toArray());
	}
	
	
	
	/**
	 * 修改主炉信息
	 * @param parm
	 * @return
	 */
	public CoreReturnObject updateOrderMain(ArrayList<String> parm)
	{
		CoreReturnObject cro = new CoreReturnObject();
        Connection connection = this.getDao(CoreConstantData.sysbeanstr)
                .getConnection();
        try {
			connection.setAutoCommit(false); // 取消自动提交事务
			 Statement st= connection.createStatement();
			String updateLenSql = CoreXmlSqlParsersFactory.getSql("FrmComposeFurnaces.updateOrderMain");
			for(String str:parm)
			{
				MilFurnacesReslutEntity milFurnacesReslutEntity = EntityTool.getEntityByJSONStr(str, MilFurnacesReslutEntity.class);
				//milOffResult.setSpecName(milOffResult.getSpecName().replace("x", "*"));
				updateLenSql = EntityTool.fillSqlByEntity(updateLenSql, milFurnacesReslutEntity);
				st.addBatch(updateLenSql);
			}
			st.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;
	}

	/**
	 * 查询当月炉号数 并生成炉号 炉号为“E XX XX XXXX”.
	 * @return
	 */
	public CoreReturnObject queryCountJudgeStoveNo() {
		String sql = "SELECT 'E'||TO_CHAR(SYSDATE, 'YYMM')|| LPAD(NVL(SUBSTR(MAX(NUM),5, 9),0) + 1, 4, 0) AS NUM" +
				"FROM (" +
				"  SELECT SUBSTR(T.JUDGE_STOVE_NO, 1, 9) AS NUM" +
				"  FROM MIL_PLAN T" +
				"  WHERE T.PLINE_CODE = 'C072'" +
				"    AND T.JUDGE_STOVE_NO LIKE 'E'||TO_CHAR(SYSDATE, 'YYMM')||'%'" +
				"  UNION" +
				"  SELECT T.FEED_HEAT_NO AS NUM" +
				"  FROM PLN_ZY_ZG_M T" +
				"  WHERE T.PLINE_CODE = 'C072'" +
				"    AND T.FEED_HEAT_NO LIKE 'E'||TO_CHAR(SYSDATE, 'YYMM')||'%'" +
				")";
		return this.getData(sql);
	}
}
